November 2015 - users-list

the majority of my monetdb programs broke between Jul2015 and Jul2015-SP1
by Anthony Damico 17 Nov '15

17 Nov '15

am i alone here? i'm very unhappy about this.. i'm sorry i'm being such a pain in the ass, but this has disrupted all of my work. all of my code worked on both unix and windows on prior versions. both the "american community survey" and "national incident based reporting system" bugs were introduced recently. [1] this bug [2] was also introduced after Jul2015, but luckily fixed before sp1's release. that had affected another two scripts. i am having a hell of a time reproducing a similar bug that's equally difficult to reproduce. it was a bit of a herculean effort for hannes to get 3828 isolated, the bug i am struggling with now is no different. [1] https://www.monetdb.org/bugzilla/buglist.cgi?bug_status=NEW&bug_status=ASSI… [2] https://www.monetdb.org/bugzilla/show_bug.cgi?id=3828

1 0

what could i explore to create a reproducible example for a bug report?
by Anthony Damico 17 Nov '15

17 Nov '15

yellow highlight is the only difference > # this command works fine create table zp13 as select v.* , arf.fips , arf.county , arf.countyst , arf.msacode from v13 as v left join arf on v.state_code = arf.state_cd AND v.bene_county_cd = arf.cnty_cd WITH DATA; > # this command crashes mserver create table zp13 as select v.* , arf.fips , arf.county , arf.countyst , arf.msacode , arf.msaname from v13 as v left join arf on v.state_code = arf.state_cd AND v.bene_county_cd = arf.cnty_cd WITH DATA; i've attached the sys.columns for the two tables, not sure if attachments are allowed on the mailing list but i can send separately if needed. thanks!

1 2

MonetDB concurrent commits exception
by Vijay Krishna 17 Nov '15

17 Nov '15

Hi, I am running a setup on MonetDB where in concurrent transactions take place. Everything works very fine but for a few transactions getting aborted due to SQLExceptions. I have set *auto commit to false* for all these transactions. I am getting frequent SQLExceptions with the message as *java.sql.SQLException: COMMIT: failed* But if I retry the same transaction, this works fine. Any idea on what would be the reason for frequent commit fails? When I set *auto commit to true* for the transactions, I get similarly frequent Exceptions but the message becomes *java.sql.SQLException: COMMIT: transaction is aborted because of concurrency conflicts* Is it like both the exception thrown be for the same cause? Any help much appreciated. Thanks in Advance. Vijayakrishna.P. Mobile : (+91) 9500402305.

4 5

Chinese string and varchar size
by Mathieu Raillard 16 Nov '15

16 Nov '15

Hi all, Last week we find a strange case with chinese character and VARCHAR size. We have in our database a field defined with VARCHAR(5) and a chinese string "不要让早把" to be inserted. If we insert the string with SQL insert everything works find, but if we try to insert via BULK monet throw an error. I made several test and i need to extend the varchar from 5 to 10 to get the bulk insert working. It looks like BULK somehow counts bits and not characters ? I give you some infos if you want to reproduce the case: create table test.varcharsize4 ( id int, varchar105 varchar(4), primary key (id) ); create table test.varcharsize5 ( id int, varchar106 varchar(5), primary key (id) ); create table test.varcharsize9 ( id int, varchar200 varchar(8), primary key (id) ); create table test.varcharsize10 ( id int, varchar200 varchar(10), primary key (id) ); *Insert SQL :* insert into test.varcharsize4 values (1,'不要让早把'); insert into test.varcharsize5 values (1,'不要让早把'); insert into test.varcharsize9 values (1,'不要让早把'); insert into test.varcharsize10 values (1,'不要让早把'); *BULK Line to insert :* "1","不要让早把" *BULK executions :* COPY 10 RECORDS INTO test.varcharsize4 FROM '/root/test_varchar_chinois' USING DELIMITERS ',','\n','\"' NULL AS '\\N' ; Result : Failed to import table line 1 field 2 'varchar(4)' expected in '不要让早把' ------ COPY 10 RECORDS INTO test.varcharsize5 FROM '/root/test_varchar_chinois' USING DELIMITERS ',','\n','\"' NULL AS '\\N' ; Result : Failed to import table line 1 field 2 'varchar(5)' expected in '不要让早把' ------- COPY 10 RECORDS INTO test.varcharsize89 FROM '/root/test_varchar_chinois' USING DELIMITERS ',','\n','\"' NULL AS '\\N' ; Result : Failed to import table line 1 field 2 'varchar(9)' expected in '不要让早把' ------- COPY 10 RECORDS INTO test.varcharsize10 FROM '/root/test_varchar_chinois' USING DELIMITERS ',','\n','\"' NULL AS '\\N' ; OK Regards Mathieu

2 1

Merge table; partition pruning?
by Bryan Senseman 13 Nov '15

13 Nov '15

Ok, I was all excited about MERGE TABLEs moving up into the SQL layer, unfortunately my enthusiasm might be misplaced as I don’t see any improvement in query performance. Let me explain: I have a fact table with ~300 M rows with 5 years of data. I created 5 yearly tables, ordering by my date_key (and also created a year column). Checked storage to verify MonetDB knew they were sorted. Created a Merge table over all 5 yearly tables. I then ran test queries with various year subsets (using direct fact column filter and joining from date dimension), from 1 to all 5 and well I didn’t see any improvement in query speed when a subset of the 5 years was queried. In fact I usually saw 10-20% degradation of performance. I am no MAL expert, but looking at the PLAN and the EXPLAIN/TRACE outputs it appears to me tables that shouldn’t have data for the query are still scanned. I ran this both on a 16GB 4 core Windows box and a 32GB 8 core EC2 Linux box, same behavior both places, using both July2015 and July2015SP1. Is this expected behavior? Am I missing some magic to have MonetDB do the Oracle concept of Partition Elimination? Or are Merge tables really just meant for data maintenance? Thanx, Bryan -- ------------------------------ This transmission is confidential and intended solely for the use of the recipient named above. It may contain confidential, proprietary, or legally privileged information. If you are not the intended recipient, you are hereby notified that any unauthorized review, use, disclosure or distribution is strictly prohibited. If you have received this transmission in error, please contact the sender by reply e-mail and delete the original transmission and all copies from your system.

4 3

Error starting a production database
by Juan Carlos Gastélum 13 Nov '15

13 Nov '15

Hi I'm getting the following error when a I try to start our production database: # builtin opt gdk_dbpath = /var/monetdb5/dbfarm/demo # builtin opt gdk_debug = 0 # builtin opt gdk_vmtrim = no # builtin opt monet_prompt = > # builtin opt monet_daemon = no # builtin opt mapi_port = 50000 # builtin opt mapi_open = false # builtin opt mapi_autosense = false # builtin opt sql_optimizer = default_pipe # builtin opt sql_debug = 0 # cmdline opt gdk_dbpath = /usr/local/monetdb/alm_dat_far # cmdline opt merovingian_uri = mapi:monetdb://farser07:50000/alm_dat_far # cmdline opt mapi_open = false # cmdline opt mapi_port = 0 # cmdline opt mapi_usock = /usr/local/monetdb/alm_dat_far/.mapi.sock # cmdline opt monet_vault_key = /usr/local/monetdb/alm_dat_far/.vaultkey # cmdline opt gdk_nr_threads = 6 # cmdline opt max_clients = 64 # cmdline opt sql_optimizer = default_pipe # cmdline opt monet_daemon = yes # cmdline opt gdk_debug = 10 # MonetDB 5 server v11.21.11 "Jul2015-SP1" # Serving database 'alm_dat_far', using 6 threads # Compiled for x86_64-pc-linux-gnu/64bit with 64bit OIDs and 128bit integers dynamically linked # Found 39.298 GiB available main-memory. # Copyright (c) 1993-July 2008 CWI. # Copyright (c) August 2008-2015 MonetDB B.V., all rights reserved # Visit http://www.monetdb.org/ for further information # Listening for UNIX domain connection requests on mapi:monetdb:///usr/local/monetdb/alm_dat_far/.mapi.sock # Start processing logs sql/sql_logs version 52200 !FATAL: Logger_new: cannot create seqs bats What can I do to solve it? I have been trying to recover a backup of the virtual machine where the instance of monetdb is but until now I have not been able to recover this database. Thanks in advance. Juan Carlos Gastelum

3 9

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

12 Nov '15

Hi, Just adding another finding: Using 1 MERGE TABLE (on Mac) with 2 REMOTE TABLEs (on Linux [physically remote, in another network]) COUNT, MIN, MAX are fine but AVG, MEDIAN, QUANTILE etc. make the server crash. Using 1 MERGE TABLE (on Linux) with 1 REMOTE TABLE (on Linux [physically together, in the same network]) and 1 local TABLE, all the above functions are fine except for the occasional error. sql>select sys.median(doubleDataType) from xxxxxxx; (mapi:monetdb://monetdb@192.168.1.212/xxxxxxx) 'user.l2' undefined in: (rmt3853_X_62_bat_oid_str:bat[:oid,:str],rmt3854_X_65_bat_oid_str:bat[:oid,:str],rmt3855_X_66_bat_oid_str:bat[:oid,:str ],rmt3856_X_67_bat_oid_str:bat[:oid,:str],rmt3857_X_68_bat_oid_dbl:bat[:oid,:dbl],rmt3858_X_70_bat_oid_dbl:bat[:oid,:d bl],rmt3859_X_71_bat_oid_dbl:bat[:oid,:dbl],rmt3860_X_72_bat_oid_dbl:bat[:oid,:dbl],rmt3861_X_73_bat_oid_dbl:bat[:oid ,:dbl],rmt3862_X_74_bat_oid_dbl:bat[:oid,:dbl],rmt3863_X_75_bat_oid_dbl:bat[:oid,:dbl],rmt3864_X_76_bat_oid_dbl:bat[: oid,:dbl],rmt3865_X_77_bat_oid_dbl:bat[:oid,:dbl],rmt3866_X_78_bat_oid_dbl:bat[:oid,:dbl],rmt3867_X_79_bat_oid_dbl:ba t[:oid,:dbl],rmt3868_X_80_bat_oid_dbl:bat[:oid,:dbl],rmt3869_X_81_bat_oid_dbl:bat[:oid,:dbl],rmt3870_X_82_bat_oid_dbl: bat[:oid,:dbl],rmt3871_X_83_bat_oid_dbl:bat[:oid,:dbl],rmt3872_X_84_bat_oid_dbl:bat[:oid,:dbl],rmt3873_X_85_bat_oid_d bl:bat[:oid,:dbl],rmt3874_X_86_bat_oid_dbl:bat[:oid,:dbl],rmt3875_X_87_bat_oid_dbl:bat[:oid,:dbl],rmt3876_X_88_bat_oid _dbl:bat[:oid,:dbl],rmt3877_X_89_bat_oid_dbl:bat[:oid,:dbl],rmt3878_X_90_bat_oid_dbl:bat[:oid,:dbl],rmt3879_X_91_bat_o id_dbl:bat[:oid,:dbl],rmt3880_X_92_bat_oid_dbl:bat[:oid,:dbl],rmt3881_X_93_bat_oid_dbl:bat[:oid,:dbl],rmt3882_X_94_ba t_oid_dbl:bat[:oid,:dbl],rmt3883_X_95_bat_oid_dbl:bat[:oid,:dbl],rmt3884_X_97_bat_oid_dbl:bat[:oid,:dbl],rmt3885_X_98_ bat_oid_dbl:bat[:oid,:dbl],rmt3886_X_99_bat_oid_dbl:bat[:oid,:dbl],rmt3887_X_100_bat_oid_dbl:bat[:oid,:dbl],rmt3888_X_ 101_bat_oid_dbl:bat[:oid,:dbl],rmt3889_X_103_bat_oid_dbl:bat[:oid,:dbl],rmt3890_X_104_bat_oid_dbl:bat[:oid,:dbl],rmt38 91_X_105_bat_oid_dbl:bat[:oid,:dbl],rmt3892_X_106_bat_oid_dbl:bat[:oid,:dbl],rmt3893_X_107_bat_oid_dbl:bat[:oid,:dbl],r mt3894_X_108_bat_oid_dbl:bat[:oid,:dbl],rmt3895_X_109_bat_oid_dbl:bat[:oid,:dbl],rmt3896_X_110_bat_oid_dbl:bat[:oid,: dbl],rmt3897_X_111_bat_oid_dbl:bat[:oid,:dbl],rmt3898_X_112_bat_oid_dbl:bat[:oid,:dbl],rmt3899_X_113_bat_oid_dbl:bat[: oid,:dbl],rmt3900_X_114_bat_oid_dbl:bat[:oid,:dbl],rmt3901_X_115_bat_oid_dbl:bat[:oid,:dbl],rmt3902_X_116_bat_oid_dbl: bat[:oid,:dbl],rmt3903_X_117_bat_oid_dbl:bat[:oid,:dbl],rmt3904_X_118_bat_oid_dbl:bat[:oid,:dbl],rmt3905_X_119_bat_oid _dbl:bat[:oid,:dbl],rmt3906_X_120_bat_oid_dbl:bat[:oid,:dbl],rmt3907_X_121_bat_oid_dbl:bat[:oid,:dbl],rmt3908_X_122_ba t_oid_dbl:bat[:oid,:dbl],rmt3909_X_123_bat_oid_dbl:bat[:oid,:dbl],rmt3910_X_124_bat_oid_dbl:bat[:oid,:dbl],rmt3911_X_12 5_bat_oid_dbl:bat[:oid,:dbl],rmt3912_X_126_bat_oid_bit:bat[:oid,:bit],rmt3913_X_128_bat_oid_bit:bat[:oid,:bit],rmt3914_X_ 130_bat_oid_oid:bat[:oid,:oid]) := user.l2(); Server version on Mac and Linux machines: MonetDB 5 server v11.21.5 "Jul2015" (64-bit, 64-bit oids, 128-bit integers) Thanks. Kind regards, Bart

1 0

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