- users-list - monetdb.org

setting up db with storagemodel.sql
by Lynn Carol Johnson 08 Nov '15

08 Nov '15

Hi all - I am new to monetdb. I am running on a MAC (OS X 10.9.5) I have installed monetdb, I have setup a dbfarm, created a couple of databases, and loaded tables. I would like to run some analysis on the amount of storage used for some of my tables. I have seen the description of autoloading scripts here: https://www.monetdb.org/book/export/html/242 And examples of running the storage() command here: https://www.monetdb.org/Documentation/Cookbooks/SQLrecipes/storage-model But I get "no such operator 'storagemodlinit' when I try to execute "call storagemodelinit()" . What command must be run to include the .sql extensions with the database? Is something run when we create the dbfarm? When we create the individual databases? I can see the .sql extensions in my /usr/local/monetdb/lib/monetdb5/createdb folder on my MAC. I'd appreciate any pointers on how to set this up. Googling has not provided me with any answers. Thanks - Lynn

3 7

Merge and remote table errors
by Bart Lenoir 08 Nov '15

08 Nov '15

Hi, I'm running queries on a Mac MERGE table using two Linux REMOTE tables and got for each query occasionally (1/10 times) the following identical error: (mapi:monetdb://monetdb@localhost/whalesdb) 'user.l2' undefined in: (rmt1094_X_2_bat_oid_str:bat[:oid,:str],rmt1095_X_5_bat_oid_wrd:bat[:oid,:wrd]) := user.l2(); The same queries on the same data set but from Linux to Linux produces also occasionally (1/10 times) a similar but different identical error: (mapi:monetdb://monetdb@localhost/whalesdb) 'user.l1' undefined in: (rmt438_X_3_bat_oid_str:bat[:oid,:str],rmt439_X_6_bat_oid_str:bat[:oid,:str],rmt440_X_7_bat_oid_int:bat[:oid,:int]) := user.l1(); Per default the connections are via SSH tunnels though direct access via remote IPs, and also local IPs where possible, gives the same results. The tables are generated using R and are having currently still having CHARACTER LARGE OBJECT as data types (in case that matters). As pointed out already only 1 out of 10 times the queries are run the error messages are shown. Has someone else come across problems like these? Thanks. Kind regards, Bart

1 0

Handling of control characters NUL and DEL
by Sébastien RAILLARD (PASSMAN) 05 Nov '15

05 Nov '15

Dear all, During data validation, we have noticed some strange behavior when working with the control characters NUL (0x00) and DEL (0x7F): * The NUL character seems to be ignored. For example, if we try to insert in a VARCHAR column this text: 'AB' || CODE(0) || 'CD', the stored value will be 'ABCD'. * The DEL character isn't escaped when copying data from table to file, whereas all the other control characters (from 0x01 to 0x1F) are correctly escaped. This may produce some display artifacts where the string 'AB' || CODE(127) || 'CD' will be usually displayed as 'ACD'. Best regards, Sebastien -- Parc d'activité Tolstoï 4 rue Edouard Aynard 69100 Villeurbanne Tél. +33 (0)4 78 95 05 80 Fax +33 (0)4 78 95 00 17 www.passman.fr www.passman-hotels.com www.passman-camping.com www.passman-sante.com <https://www.facebook.com/PASSMAN-187787814053/>

3 6

HUEGINT incorrect value
by Sébastien RAILLARD (PASSMAN) 05 Nov '15

05 Nov '15

Dear all, The value -9223372036854775808 isn't correctly inserted in HUGEINT column, the result of the insertion is a "nil" value. Steps to reproduce the issue: CREATE TABLE "seb"."test_hugeint" ( "ID" INTEGER NOT NULL AUTO_INCREMENT, "cHUGEINT" HUGEINT, CONSTRAINT "pkey2" PRIMARY KEY ("ID") ); INSERT INTO "seb"."test_hugeint" ("cHUGEINT") VALUES (-9223372036854775807); INSERT INTO "seb"."test_hugeint" ("cHUGEINT") VALUES (-9223372036854775808); INSERT INTO "seb"."test_hugeint" ("cHUGEINT") VALUES (-9223372036854775809); And the result of SELECT * FROM "seb"."test_hugeint"; is: +------+----------------------+ | ID | cHUGEINT | +======+======================+ | 1 | -9223372036854775807 | | 2 | nil | | 3 | -9223372036854775809 | +------+----------------------+ Sébastien -- Parc d'activité Tolstoï 4 rue Edouard Aynard 69100 Villeurbanne Tél. +33 (0)4 78 95 05 80 Fax +33 (0)4 78 95 00 17 www.passman.fr www.passman-hotels.com www.passman-camping.com www.passman-sante.com <https://www.facebook.com/PASSMAN-187787814053/>

3 2

Dataype documentation error
by Sébastien RAILLARD (PASSMAN) 05 Nov '15

05 Nov '15

Dear all, There is an error in the builtin datatypes documentation page (https://www.monetdb.org/Documentation/Manuals/SQLreference/BuiltinTypes) The valid interval for the BIGINT type is from -9223372036854775807 to 9223372036854775807. Also, I don't know if it may be useful, but the limits for the HUEGEINT in numeral form are -170141183460469231731687303715884105727 to 170141183460469231731687303715884105727. Sebastien. -- Parc d'activité Tolstoï 4 rue Edouard Aynard 69100 Villeurbanne Tél. +33 (0)4 78 95 05 80 Fax +33 (0)4 78 95 00 17 www.passman.fr www.passman-hotels.com www.passman-camping.com www.passman-sante.com <https://www.facebook.com/PASSMAN-187787814053/>

2 1

Re: Parallel batch insert failure in MonetDB through JDBC
by Siddharth Tyagi 04 Nov '15

04 Nov '15

Hi, I am trying to insert data in multiple tables in parallel through prepared statements batch execution. I am facing a problem , first thread that executes the batch makes other executions to crash because of the underlying exception - prepared statement is no longer available. Is there a limitation of updating schema in parallel jobs ? If not then what may be the cause of this problem ? Any light on how I may be able to implement this will be helpful. >> code that executes batches package monet.test; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.SQLException; import java.sql.Statement; import java.sql.Timestamp; import java.sql.Types; import java.util.ArrayList; import java.util.List; import java.util.Random; public class BatchTest { public static class TestRunner implements Runnable { private final static String problemHere = "jdbc:monetdb://localhost/test-db"; static { try { Class.forName("nl.cwi.monetdb.jdbc.MonetDriver"); } catch (ClassNotFoundException e) { // TODO Auto-generated catch block e.printStackTrace(); } } private static Connection createConnection() throws SQLException { // happens one time per thread return DriverManager.getConnection(problemHere, "monetdb", "monetdb"); } private static int fieldCount = 10; private static int[] colType = new int[fieldCount]; private static final int[] types = { Types.VARCHAR, Types.TIMESTAMP, Types.DECIMAL }; static { Random random = new Random(); // initialize column types for (int i = 0; i < fieldCount; i++) { colType[i] = types[random.nextInt(types.length)]; } } private final String name; private Connection con; private int batchCount; private int batchSize; private String create; private String drop; private String insert; public TestRunner(String string, int bs, int bc) throws SQLException { this.name = string; this.con = createConnection(); this.batchCount = bc; this.batchSize = bs; this.create = "create table " + name + " ("; for (int i = 0; i < fieldCount; i++) { create += (i == 0 ? "" : ",") + "col" + i + " " + getType(colType[i]); } this.create += ")"; this.insert = "insert into " + name + " values ("; for (int i = 0; i < fieldCount; i++) { insert += (i == 0 ? "" : ",") + "?"; } this.insert += ")"; this.drop = "drop table " + name; } private static String getType(int i) { switch (i) { case Types.DECIMAL: return "decimal(18,9)"; case Types.VARCHAR: return "varchar(30000)"; case Types.TIMESTAMP: return "timestamp"; } return null; } protected void finalize() throws Throwable { if (con != null) { con.close(); } }; public void run() { System.out.format("%s started.%ncreate: %s%ninsert: %s%ndrop: %s%n", name, create, insert, drop); try (Statement stmt = con.createStatement()) { // this will throw the exception of concurrency con synchronized (problemHere) { System.out.format("%d created %s%n", stmt.executeUpdate(create), name); if (!con.getAutoCommit()) { con.commit(); } } } catch (SQLException e1) { e1.printStackTrace(); return; } try (PreparedStatement stmt = con.prepareStatement(insert)) { while (batchCount-- > 0) { for (int i = batchSize; i > 0; i--) { setBatchInput(stmt); stmt.addBatch(); } System.out.format("%s - submitting batch ...%n", name); stmt.executeBatch(); if (!con.getAutoCommit()) { con.commit(); } } } catch (Exception e) { e.printStackTrace(); } finally { if (con != null) { try (Statement stmt = con.createStatement()) { synchronized (problemHere) { System.out.format("%d deleted - %s%n", stmt.executeUpdate(drop), name); if (!con.getAutoCommit()) { con.commit(); } } } catch (SQLException e1) { e1.printStackTrace(); } try { con.close(); con = null; } catch (SQLException e) { e.printStackTrace(); } } } System.out.format("%s finished.", name); } private static void setBatchInput(PreparedStatement stmt) throws SQLException { for (int i = 1; i <= fieldCount; i++) { stmt.setObject(i, getRandomFieldValue(colType[i - 1])); } } private static Object getRandomFieldValue(int type) { switch (type) { case Types.DECIMAL: return 0; case Types.VARCHAR: return "null"; case Types.TIMESTAMP: return new Timestamp(System.currentTimeMillis()); } return null; } } public static void main(String[] args) { int num = 2; List<Thread> ts = new ArrayList<>(); while (num-- > 0) { Thread t = null; try { (t = new Thread(new TestRunner("runner_" + num, 10000, 1))).start(); } catch (SQLException e) { e.printStackTrace(); } ts.add(t); } for (Thread t : ts) { try { t.join(); } catch (InterruptedException e) { e.printStackTrace(); } } } } On Wed, Nov 4, 2015 at 3:20 PM, Siddharth Tyagi <inbox.siddharth(a)gmail.com> wrote: > Hi, > I am trying to insert data in multiple tables in parallel through prepared > statements batch execution. > I am facing a problem , > > first thread that executes the batch makes other executions to crash > because of the underlying exception - prepared statement is no longer > available. > > Is there a limitation of updating schema in parallel jobs ? > If not then what may be the cause of this problem ? > > Any light on how I may be able to implement this will be helpful. > > >> code that executes batches > > package monet.test; > > import java.sql.Connection; > import java.sql.DriverManager; > import java.sql.PreparedStatement; > import java.sql.SQLException; > import java.sql.Statement; > import java.sql.Timestamp; > import java.sql.Types; > import java.util.ArrayList; > import java.util.List; > import java.util.Random; > > public class BatchTest { > public static class TestRunner implements Runnable { > private final static String problemHere = > "jdbc:monetdb://localhost/test-db"; > > static { > try { > Class.forName("nl.cwi.monetdb.jdbc.MonetDriver"); > } catch (ClassNotFoundException e) { > // TODO Auto-generated catch block > e.printStackTrace(); > } > } > > private static Connection createConnection() throws SQLException { > // happens one time per thread > return DriverManager.getConnection(problemHere, "monetdb", "monetdb"); > } > > private static int fieldCount = 10; > private static int[] colType = new int[fieldCount]; > private static final int[] types = { Types.VARCHAR, Types.TIMESTAMP, > Types.DECIMAL }; > > static { > Random random = new Random(); > // initialize column types > for (int i = 0; i < fieldCount; i++) { > colType[i] = types[random.nextInt(types.length)]; > } > } > > private final String name; > private Connection con; > private int batchCount; > private int batchSize; > private String create; > private String drop; > private String insert; > > public TestRunner(String string, int bs, int bc) throws SQLException { > this.name = string; > this.con = createConnection(); > this.batchCount = bc; > this.batchSize = bs; > this.create = "create table " + name + " ("; > > for (int i = 0; i < fieldCount; i++) { > create += (i == 0 ? "" : ",") + "col" + i + " " + getType(colType[i]); > } > this.create += ")"; > this.insert = "insert into " + name + " values ("; > for (int i = 0; i < fieldCount; i++) { > insert += (i == 0 ? "" : ",") + "?"; > } > this.insert += ")"; > this.drop = "drop table " + name; > } > > private static String getType(int i) { > switch (i) { > case Types.DECIMAL: > return "decimal(18,9)"; > case Types.VARCHAR: > return "varchar(30000)"; > case Types.TIMESTAMP: > return "timestamp"; > } > return null; > } > > protected void finalize() throws Throwable { > if (con != null) { > con.close(); > } > }; > > public void run() { > System.out.format("%s started.%ncreate: %s%ninsert: %s%ndrop: %s%n", > name, create, insert, drop); > try (Statement stmt = con.createStatement()) { > // this will throw the exception of concurrency con > synchronized (problemHere) { > System.out.format("%d created %s%n", stmt.executeUpdate(create), name); > if (!con.getAutoCommit()) { > con.commit(); > } > } > } catch (SQLException e1) { > e1.printStackTrace(); > return; > } > try (PreparedStatement stmt = con.prepareStatement(insert)) { > while (batchCount-- > 0) { > for (int i = batchSize; i > 0; i--) { > setBatchInput(stmt); > stmt.addBatch(); > } > System.out.format("%s - submitting batch ...%n", name); > stmt.executeBatch(); > if (!con.getAutoCommit()) { > con.commit(); > } > } > } catch (Exception e) { > e.printStackTrace(); > } finally { > if (con != null) { > try (Statement stmt = con.createStatement()) { > synchronized (problemHere) { > System.out.format("%d deleted - %s%n", stmt.executeUpdate(drop), name); > if (!con.getAutoCommit()) { > con.commit(); > } > } > } catch (SQLException e1) { > e1.printStackTrace(); > } > try { > con.close(); > con = null; > } catch (SQLException e) { > e.printStackTrace(); > } > } > } > System.out.format("%s finished.", name); > } > > private static void setBatchInput(PreparedStatement stmt) throws > SQLException { > for (int i = 1; i <= fieldCount; i++) { > stmt.setObject(i, getRandomFieldValue(colType[i - 1])); > } > } > > private static Object getRandomFieldValue(int type) { > switch (type) { > case Types.DECIMAL: > return 0; > case Types.VARCHAR: > return "null"; > case Types.TIMESTAMP: > return new Timestamp(System.currentTimeMillis()); > } > return null; > } > } > > public static void main(String[] args) { > int num = 2; > List<Thread> ts = new ArrayList<>(); > while (num-- > 0) { > Thread t = null; > try { > (t = new Thread(new TestRunner("runner_" + num, 10000, 1))).start(); > } catch (SQLException e) { > e.printStackTrace(); > } > ts.add(t); > } > for (Thread t : ts) { > try { > t.join(); > } catch (InterruptedException e) { > e.printStackTrace(); > } > } > } > > } > > -- > > > *Thanks and RegardsSiddharth Tyagi* > -- *Thanks and RegardsSiddharth Tyagi*

1 0

Announcement: New Jul2015-SP1 Bugfix release of MonetDB suite
by Sjoerd Mullender 03 Nov '15

03 Nov '15

The MonetDB team at CWI/MonetDB BV is pleased to announce the Jul2015-SP1 bugfix release of the MonetDB suite of programs. More information about MonetDB can be found on our website at <http://www.monetdb.org/>. For details on this release, please see the release notes at <http://www.monetdb.org/Downloads/ReleaseNotes>. As usual, the download location is <http://dev.monetdb.org/downloads/>. Jul 2015-SP1 bugfix release Client Package * In the SQL formatter of mclient (the default) we now properly align East Asian wide characters. Bug Fixes * 3789: Query on large string table fails on HEAPextend * 3794: table function sys.rejects() and view sys.rejects() are listed are metadata objects but give an (incorrect) error when they are queried * 3797: COPY INTO with incorrect number columns * 3798: SELECT query with INTERSECT causes assertion failure * 3800: LIKE is broken for many patterns * 3802: Disk space never freed: a logical ref is keeped on a deleted BATs * 3803: SQL query parser fails to parse valid SELECT query with a CASE .. END in it. It fails with parser error: identifier 'x' ambiguous * 3804: `monetdb status` command crashes under certain conditions * 3809: Inefficient plan is generated for queries with many (>= 24) joined tables which take a long time or an HEAPalloc error. I get Error: GDK reported error. HEAPalloc: Insufficie * 3810: Fix statistics gathering * 3811: NOT LIKE not working if the operand doesn't contains wildcards. * 3813: COPY INTO fails on perfectly clean CSV file * 3814: Server crash when using bitwise NOT operation in SQL query * 3816: Server crashes when trying to convert timestamp to str with incorrect format * 3818: Crash when performing UNION/GROUP BY over tables with different columns * 3819: order of tables in FROM-clause has negative impact on generated plan (using crossproducts instead of joins) * 3820: mclient accepts table with repeated constraint which causes crash on insert * 3821: Unexpected error when using a number instead of a boolean * 3822: Yet another LIKE operator issue * 3823: JDBC Connection to a schema - setSchema() error * 3825: MonetDB not cleaning intermediate results which leads to filling up disk space and ultimately server crash * 3827: Certains comparisons between UUID produce a MAL error * 3828: Schema corruption after several ALTER TABLE statements and server restart * 3829: Certains simple WHERE clause cause MonetDB to segfault without explanation * 3830: Coalesce typing inconsistencies * 3833: NULL literals refused at many places * 3834: Date comparison returns incorrect results * 3839: msqldump generates incorrect syntax ON UPDATE (null)

1 0

Join optimization
by imad hajj chahine 02 Nov '15

02 Nov '15

Hi, How can we control the algorithm used for the join operation? In the trace i am always getting algebra.leftfetchjoin, what could possibly be done to use merge join or hash join that will be reflected in the trace by algebra.join. Thanks.

4 5

timestamptz conversion
by Robin Cijvat 29 Oct '15

29 Oct '15

Hi all, I am currently extending the NodeJS connector for MonetDB, that communicates with the mserver process using Mapi. However, I face some unexpected behavior when I insert timestamptz values into a table and then retrieve it. This is the Mapi: TX: sCREATE TABLE bar (a timestamptz); RX: &3 TX: sINSERT INTO bar VALUES ('2015-10-29 11:31:35.000000+01:00'); RX: &2 1 -1 TX: sSELECT * FROM bar; RX: &1 0 1 1 1 % sys.bar # table_name % a # name % timestamptz # type % 32 # length [ 2015-10-29 10:31:35.000000+00:00 ] As you can see, inserting the timestamptz string '2015-10-29 11:31:35.000000+01:00', results in it being stored as its equivalent '2015-10-29 10:31:35.000000+00:00'. If I do the same with mclient, this does not happen, and I get the exact same string back. Does anyone know why this might be happening?

2 2

python module monetdblite
by Ben Jeffery 28 Oct '15

28 Oct '15

Hi, I'm aware the embedded "monetdblite" library isn't released yet - but it seems perfect for my use case so am very excited to try it. I've tried compiling the source from the pyapi branch but run into: "ImportError: /usr/local/lib/python2.7/dist-packages/monetdb5/lib/monetdb5/lib_pyapi.so: undefined symbol: batint_dec2_dbl" when I "import monetdblite" on Linux Mint 17 with python 2.7 or 3.4 Any chance of some build instructions for this module? Or do I just need to be patient for the release? :) Ta, Ben

2 4

Selecting database at runtime?
by Dennis Pallett 28 Oct '15

28 Oct '15

Hi all, I'm pretty sure I already know the answer, but is it possible to select the database at runtime instead of "hardcoded" into the connection string? For example, my (JDBC) connection string would only be the MonetDB host (i.e. jdbc:monetdb://192.168.0.1/) and then I'd be able to execute a query to select the right database (e.g. SET DATABASE dbname). Best regards, Dennis

2 1

Change default role of user
by Dennis Pallett 27 Oct '15

27 Oct '15

Hi, Is it possible to change the default role of a user? I want to avoid the step of having to execute the "SET ROLE role" statement before being able to run my queries. Best regards, Dennis Pallett

2 2

Server crash / lib_sql.so
by Mathieu Raillard 26 Oct '15

26 Oct '15

Hi all, We are using MoentDB since 6 months with quite good results.(thanks to the dev team for their good work) Unfortunately yesterday we came across an issue for which we can't find a solution. Our database crashed for an unknown reason (RAM was fine, Disk space was fine) and now each time we re-use the same table with a query we used during the crash, the database crash with a seg fault Query : select * from storage(); OS Version : CentOS release 6.7 (Final) MonetDB Version : MonetDB Database Server Toolkit v1.1 (Jul2015) tail /var/log/messages : kernel: mserver5[9945]: segfault at 18 ip 00007f9e86fd534b sp 00007f9e862912e0 error 4 in lib_sql.so[7f9e86eb1000+182000] merovingian.log : database 'BI-DEV' (9972) was killed by signal SIGSEGV Log from gbd : Program received signal SIGSEGV, Segmentation fault. [Switching to Thread 0x7ffa24edc700 (LWP 4683)] 0x00007ffa25c1f34b in delta_bind_bat () from /usr/lib64/monetdb5/lib_sql.so (gdb) bt #0 0x00007ffa25c1f34b in delta_bind_bat () from /usr/lib64/monetdb5/lib_sql.so #1 0x00007ffa25b20fee in sql_storage () from /usr/lib64/monetdb5/lib_sql.so #2 0x00007ffa321aea45 in runMALsequence () from /usr/lib64/libmonetdb5.so.19 #3 0x00007ffa321afe29 in callMAL () from /usr/lib64/libmonetdb5.so.19 #4 0x00007ffa25b369f7 in SQLexecutePrepared () from /usr/lib64/monetdb5/lib_sql.so #5 0x00007ffa25b36efc in SQLengineIntern () from /usr/lib64/monetdb5/lib_sql.so #6 0x00007ffa321c87dd in runScenarioBody () from /usr/lib64/libmonetdb5.so.19 #7 0x00007ffa321c886f in runScenario () from /usr/lib64/libmonetdb5.so.19 #8 0x00007ffa321c9738 in MSserveClient () from /usr/lib64/libmonetdb5.so.19 #9 0x00007ffa321ca816 in MSscheduleClient () from /usr/lib64/libmonetdb5.so.19 #10 0x00007ffa32266e0f in doChallenge () from /usr/lib64/libmonetdb5.so.19 #11 0x00007ffa31d2adcf in thread_starter () from /usr/lib64/libbat.so.12 #12 0x00007ffa2f97da51 in start_thread (arg=0x7ffa24edc700) at pthread_create.c:301 #13 0x00007ffa2f6ca93d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:115 Any help to understand and correct what is going on would be nice. Regards Mathieu

3 7

overloaded ram on windows
by Anthony Damico 23 Oct '15

23 Oct '15

hi, this is just a general request for the awesome folks at cwi: could you give some serious thought to optimizing ram usage on windows? this happens to me a lot, i end up just leaving these big joins to run overnight-- http://s30.postimg.org/saeo9ca7l/overloaded.png happy to write up some r-driven reproducible examples if there's willingness to combat these bottlenecks. thanks as always from your biggest fan.

1 1

Offset timestamp field with random interval
by Robin Cijvat 21 Oct '15

21 Oct '15

Hello everyone! I am facing some (probably simple) problems. A snippet of a timestamp column in my table: +----------------------------+ | ts | +============================+ | 1595-04-02 00:00:00.000000 | | 1595-04-02 00:00:00.000000 | | 1595-04-02 00:00:00.000000 | | 1595-04-02 00:00:00.000000 | | 1598-03-25 00:00:00.000000 | +----------------------------+ I want to fill all the times in these fields with a random value (uniformly distributed is fine). This faces me with the following problems: 1) Adding an interval to a timestamp does not seem to be suited for dynamic intervals: This works fine: UPDATE "table" SET ts = (ts + INTERVAL '5000' SECOND); This doesn't: UPDATE "table" SET ts = (ts + INTERVAL (CAST(5000 AS STRING)) SECOND); Does someone maybe know of a way to accomplish this? 2) Does someone know how to normalize the output of the RAND operator available in SQL? It seems to calculate a random BIGINT, and in order for me to normalize it, I would need the maximum value of a BIGINT, which of course is platform dependent. Is there a way to get the max value of BIGINT on the SQL level? Or can I go about this another way? All help is much appreciated! Robin

4 11

Presto Connector for MonetDB
by Stephen P. Morgan 20 Oct '15

20 Oct '15

Does anyone know of a Presto connector for MonetDB? There are connectors for MySQL and PostgreSQL among others, but I don't see one for MonetDB. Thanks, Steve

2 1

Transactions
by Doug Service 19 Oct '15

19 Oct '15

We use MonetDB extensively in an internal system we are developing. We initially had issues getting transactions working so we put it off to make progress in other areas. However we are at the point now were we need to have transactions working. The mapi documentation ( https://www.monetdb.org/Documentation/Manuals/SQLreference/Programming/MAPI) says the following about auto commit. MapiMsg mapi_setAutocommit(Mapi mid, int autocommit) Set the autocommit flag (default is on). This only has an effect when the language is SQL. In that case, the server commits after each statement sent to the server. I infered from this statement that using transactions requires setting auto commit to false. However, when I did that I received the following error when I tried to use transactions. MSERVER(25001!START TRANSACTION: cannot start a transaction within a transaction) If I did not disable auto commit, I did not receive any errors when using transactions, but I did not know if the transaction were actually working. After using hg to enlist in the MonetDb code and searching for the error I found this. str SQLtransaction2(Client cntxt, MalBlkPtr mb, MalStkPtr stk, InstrPtr pci) { mvc *sql = NULL; str msg; (void) stk; (void) pci; if ((msg = getSQLContext(cntxt, mb, &sql, NULL)) != NULL) return msg; if ((msg = checkSQLContext(cntxt)) != NULL) return msg; if (sql->session->auto_commit == 0) throw(SQL, "sql.trans", "25001!START TRANSACTION: cannot start a transaction within a transaction"); if (sql->session->active) { RECYCLEdrop(cntxt); mvc_rollback(sql, 0, NULL); } sql->session->auto_commit = 0; sql->session->ac_on_commit = 1; sql->session->level = 0; (void) mvc_trans(sql); return msg; } which leads me to believe that one should not disable auto commit to use transaction as it appears to be auto disabled by the transaction code. My questions are: 1) Is the correct method of using transactions in MonetDb to leave the auto commit flag alone and just issue START TRANSACTION;, COMMIT;, and ROLLBACK; as needed? 2) If the answer to (1) is yes, is disabling auto commit, issuing updates, and then re enabling auto commit like a transaction? Doug Service

3 3

MonetDB not cleaning intermediate results which leads to filling up disk space
by abraun_75＠yahoo.de 19 Oct '15

19 Oct '15

Hi, Right now I am evaluating MonetDB and try to analyze a dataset that consists of 10 million rows. The data set has 2 tables, table a stores 10 million rows, table b stores 1 million rows. Both tables are being joined and some statistical values are being calculated (QUARTILE, AVG, SUM and GROUP BY). MonetDB is promising fast here. When starting up the database and executing the query the result is being returned within 4 seconds. I tried to execute the same SQL query 1000 times sequentially and to my surprise after the 60th iteration monetDB crashed because of insuffcient disk space. As far as I read intermediate results are being materialized within the bat folder. The .tail files are not being removed after a query was executed successfully and the files are not even removed after the database conenction is being closed. When I restart the server (dbfarm) the files are cleaned up perfectly. I am just wondering if this default behaviour can be changed? Are there any ways to enforce cleaning up the files? How to avoid a server crash because of insufficient memory? For the system I am planning to use monetDB for I am not able to restart the server before I execute the SQL queries. Thank you very much, Alex

5 8

current schema
by Enzo 19 Oct '15

19 Oct '15

Thanks to Jennie I understand that I can get the current schema with the query: select current_schema from sys.var(); While I get the “right” response, I get it as a series of duplicated lines (10 at the last count): For example, after setting a schema as set schema viewer; I get the following 10 lines from the query: Is it the right behaviour? And if yes, do these lines have a specific significance? Thanks Enzo enzo(a)smartinsightsfromdata.com

2 1

Re: users-list Digest, Vol 38, Issue 9
by Luciano Sasso 18 Oct '15

18 Oct '15

Jul 2015 is avaible for DEbian 7 ? At --- Este email foi escaneado pelo Avast antivírus. https://www.avast.com/antivirus

3 2

How to install tachograph?
by Xu,Wenjian 18 Oct '15

18 Oct '15

Hi all, I have installed the latest Jul2015 release, and I would like to use the latest tools tachograph to determine the progress of query execution. But I do not know how to install it. My system is ubuntu 14.04. I have tried to use *sudo apt-get install monetdb-tools* but failed to locate the package *monetdb-tools*. Any idea would be helpful. Thanks. Best, XU Wenjian

3 2

DataCell Stream
by Luciano Sasso 18 Oct '15

18 Oct '15

DataCel stream discontinead in new versions MonetDB ? A Idea, substitute for Proxy Node.JS ? --- Este email foi escaneado pelo Avast antivírus. https://www.avast.com/antivirus

3 3

Re: users-list Digest, Vol 38, Issue 9
by Luciano Sasso 18 Oct '15

18 Oct '15

Im Jul 2015, remote tables can sort in result query ? At --- Este email foi escaneado pelo Avast antivírus. https://www.avast.com/antivirus

2 1

MonetDB scripting
by Doug Service 18 Oct '15

18 Oct '15

Hello Everyone, I need to become more conversant in monetdb scripting and have read the pages on functions and procedures on the website. However, more examples would be very helpful. Does anyone know of a good repository of extensive monetdb scripts that provide good models. Doug Service

2 1

Concurrent Transaction control in MonetDB
by Vijay Krishna 18 Oct '15

18 Oct '15

Hi, I read the following lines in a thesis on MonetDB titled - "Improving Transactional Stability in MonetDB" where in they had explained the concurrency control in MonetDB as "Concurrent updates (writers) are not anticipated and in case of N con􏰃icting transactions, only one of them (the one having the earliest time-stamp) is guaranteed to eventually commit its changes. Concurrent appends (on the same table) are detected as write-to-write conflicts and only the writer carrying the earliest time-stamp will end up committing successfully, the rest will be aborted." Why is that the transaction having the earliest timestamp is allowed to commit, in case of a conflict? Shouldn't it be the latest transaction that should commit on a data, since the latest transaction would be having the latest version of data? Or, what am I getting wrong here? Any help much appreciated. Thanks & Regards, Vijayakrishna.P. Mobile : (+91) 9500402305.

1 0