November 2015 - users-list

Trying to understand select performance when returning many columns
by Ben Jeffery 09 Nov '15

09 Nov '15

I have a table of 93 columns and ~90M rows which is a 60GB "bat" directory on disk. Mostly I'm doing things with a few columns - Monet has been _amazing_ for those use cases so thanks for all your hard work. However sometimes I need to return all columns for a small subset of rows and I can't quite understand what Monet is doing or why it is taking a long time. Here's some timings for 'select * from table order by "unsorted_numerical_column" limit N;' caches all flushed before each query: N=1 … [View More]

1 0

MonetDB JDBC driver and IntelliJ IDEA 15
by Endre Palatinus 09 Nov '15

09 Nov '15

Dear MonetDB users, I would like to access my MonetDB (latest version: v1.7 (Jul2015-SP1)) database through JDBC (MonetDB JDBC driver version 2.18) using IntelliJ IDEA 15.0 under Mac OS X 10.9.5. The "Test connection" function succeeds, but the "Refresh" function crashes in the Database tool of the IDE with the Exception below. The same problem happened when I was trying to connect to PostgreSQL 9.4 with a PostgreSQL JDBC driver older than JDBC version 4.2. I guess that MonetDB's JDBC … [View More]driver has JDBC version 4.0 only. Any ideas on how to fix this? Best regards, Endre Palatinus ===== Exception: ====== TABLE_TYPE unavailable java.lang.NullPointerException at nl.cwi.monetdb.jdbc.MonetResultSet.getObject(MonetResultSet.java:1945) at com.intellij.database.remote.jdbc.impl.RemoteResultSetImpl.getObject(RemoteResultSetImpl.java:77) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:497) at sun.rmi.server.UnicastServerRef.dispatch(UnicastServerRef.java:323) at sun.rmi.transport.Transport$1.run(Transport.java:200) at sun.rmi.transport.Transport$1.run(Transport.java:197) at java.security.AccessController.doPrivileged(Native Method) at sun.rmi.transport.Transport.serviceCall(Transport.java:196) at sun.rmi.transport.tcp.TCPTransport.handleMessages(TCPTransport.java:568) at sun.rmi.transport.tcp.TCPTransport$ConnectionHandler.run0(TCPTransport.java:826) at sun.rmi.transport.tcp.TCPTransport$ConnectionHandler.lambda$run$254(TCPTransport.java:683) at sun.rmi.transport.tcp.TCPTransport$ConnectionHandler$$Lambda$2/490517473.run(Unknown Source) at java.security.AccessController.doPrivileged(Native Method) at sun.rmi.transport.tcp.TCPTransport$ConnectionHandler.run(TCPTransport.java:682) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) at java.lang.Thread.run(Thread.java:745) at sun.rmi.transport.StreamRemoteCall.exceptionReceivedFromServer(StreamRemoteCall.java:276) at sun.rmi.transport.StreamRemoteCall.executeCall(StreamRemoteCall.java:253) at sun.rmi.server.UnicastRef.invoke(UnicastRef.java:162) at java.rmi.server.RemoteObjectInvocationHandler.invokeRemoteMethod(RemoteObjectInvocationHandler.java:194) at java.rmi.server.RemoteObjectInvocationHandler.invoke(RemoteObjectInvocationHandler.java:148) at com.sun.proxy.$Proxy130.getObject(Unknown Source) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:497) at com.intellij.execution.rmi.RemoteUtil.invokeRemote(RemoteUtil.java:124) at com.intellij.execution.rmi.RemoteUtil.access$100(RemoteUtil.java:36) at com.intellij.execution.rmi.RemoteUtil$RemoteInvocationHandler.invoke(RemoteUtil.java:229) at com.sun.proxy.$Proxy131.getObject(Unknown Source) at com.intellij.database.util.JdbcUtil.resultSetToListSafe(JdbcUtil.java:61) at com.intellij.database.dataSource.DatabaseModelLegacyLoader.getAllTableTypes(DatabaseModelLegacyLoader.java:966) at com.intellij.database.dataSource.DatabaseModelLegacyLoader.loadDataSourceStateInner(DatabaseModelLegacyLoader.java:127) at com.intellij.database.dataSource.DatabaseModelLegacyLoader.loadInner(DatabaseModelLegacyLoader.java:88) at com.intellij.database.dataSource.DatabaseModelLoader.performLegacyIntrospection(DatabaseModelLoader.java:197) at com.intellij.database.dataSource.DatabaseModelLoader.performIntrospection(DatabaseModelLoader.java:99) at com.intellij.database.dataSource.AbstractDataSource.refreshMetaData(AbstractDataSource.java:61) at com.intellij.database.dataSource.AbstractDataSource$1.perform(AbstractDataSource.java:36) at com.intellij.database.dataSource.AbstractDataSource$1.perform(AbstractDataSource.java:34) at com.intellij.database.dataSource.AbstractDataSource.performJdbcOperation(AbstractDataSource.java:116) at com.intellij.database.dataSource.AbstractDataSource.refreshMetaData(AbstractDataSource.java:34) at com.intellij.database.dataSource.DataSourceUiUtil$2.run(DataSourceUiUtil.java:188) at com.intellij.openapi.progress.impl.CoreProgressManager$TaskRunnable.run(CoreProgressManager.java:563) at com.intellij.openapi.progress.impl.CoreProgressManager$2.run(CoreProgressManager.java:142) at com.intellij.openapi.progress.impl.CoreProgressManager.a(CoreProgressManager.java:446) at com.intellij.openapi.progress.impl.CoreProgressManager.executeProcessUnderProgress(CoreProgressManager.java:392) at com.intellij.openapi.progress.impl.ProgressManagerImpl.executeProcessUnderProgress(ProgressManagerImpl.java:54) at com.intellij.openapi.progress.impl.CoreProgressManager.runProcess(CoreProgressManager.java:127) at com.intellij.openapi.progress.impl.ProgressManagerImpl$1.run(ProgressManagerImpl.java:126) at com.intellij.openapi.application.impl.ApplicationImpl$8.run(ApplicationImpl.java:365) at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511) at java.util.concurrent.FutureTask.run(FutureTask.java:266) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) at java.lang.Thread.run(Thread.java:745) at org.jetbrains.ide.PooledThreadExecutor$1$1.run(PooledThreadExecutor.java:55) and 8 duplicate reports [View Less]

1 0

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 "… [View More]

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://… [View More]

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 … [View More]

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" ("… [View More]

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 … [View More]

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 … [View More]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* [View Less]

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 … [View More]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) [View Less]

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