- users-list - monetdb.org

Duplicate views problem
by Dennis Pallett 11 Nov '15

11 Nov '15

Hi all, We're having a problem whereby it's somehow possible in a MonetDB database to have multiple views with the same name. See attached screenshot of an example. Any ideas on how this is possible? Seems like a bug but unfortunately we can't reliably reproduce it. It just happens every now and then. Best regards, Dennis

2 2

Usage of pool
by poornima＠apakau.com 11 Nov '15

11 Nov '15

Hi, I am trying to use monetdb with Hikari Pool. I have set the connection timeout to 4500. I have 4 max connections. The query execution takes at the max 20 ms. I am testing with 250 threads and 100000 requests. But I get a connection time out as follows : 11 09:40:24 PST.DEBUG118*HikariPool~logPoolState@339: Timeout failure pool HikariPool-0 stats (total=4, active=3, idle=1, waiting=1) The waiting number increases to a large number as 139. The idle drops to 0 in just 4 such messages. My Hikari pool configuration is as follows _dataSource = new HikariDataSource(); _dataSource.setDriverClassName("nl.cwi.monetdb.jdbc.MonetDriver"); _dataSource.setJdbcUrl("jdbc:monetdb://" + _reg.getStrProp("tdbUrl")); _dataSource.setUsername(_reg.getStrProp("tdbUsr")); _dataSource.setPassword(_reg.getStrProp("tdbPswd")); _dataSource.setMaximumPoolSize(4); _dataSource.setConnectionTimeout(4500); _dataSource.addDataSourceProperty("cachePrepStmts", "true"); _dataSource.addDataSourceProperty("prepStmtCacheSize", "250"); _dataSource.addDataSourceProperty("prepStmtCacheSqlLimit", "2048"); Do I need to change the configuration ? Or am I supposed to change the pool ? Which is the pool that is used with monetdb JDBC driver ? Regards, Poornima.

1 0

thanks for BEST EFFORT
by Anthony Damico 11 Nov '15

11 Nov '15

1 0

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 907ms N=100 3105ms N=200 6782ms N=500 14656ms N=1000 30100ms N=10000 71093ms For the last two my 32GB ram is being completely filled with the cache. I'm clearly missing something in my understanding as I don't see why that should be needed. Surely you only need to read the whole of the sort column and then grab only N rows from all the other columns? Is there anything I can do to reduce the amount of data loaded? The output of "explain" is at https://gist.github.com/benjeffery/30e9f6173b625f5d0c31 <https://gist.github.com/benjeffery/30e9f6173b625f5d0c31if>. Thanks, Ben

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 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

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 "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