- users-list - monetdb.org

回复：Re: SUM() OVER (ORDER BY..)
by chenkaijiang＠bigknow.com.cn 29 Apr '16

29 Apr '16

An alternative way is to use temp tables to get the same result: select c1, sum(c2) over(partition by c3) from t1; ==> create temp table tmp1 (sum_c2 int, c3 int); insert into tmp1 select sum(c2), c3 from t1 group by c3; select c1, sum_c2, c3 from t1 join tmp1 on t1.c3=tmp1.c3; ----- 原始邮件 ----- 发件人：Roberto Cornacchia <roberto.cornacchia(a)gmail.com> 收件人：Communication channel for MonetDB users <users-list(a)monetdb.org> 主题：Re: SUM() OVER (ORDER BY..) 日期：2016-4-29 15:46:57 No, indeed, this syntax is not supported. On 29 April 2016 at 09:41, Anthony Damico <ajdamico(a)gmail.com> wrote: > hmm, that gives me the same error.. > > SELECT mpg, wt, SUM(wt) OVER (PARTITION BY hp) AS cum_wgt, SUM(wt) over () > AS tot_wgt, hp FROM mtcars; > > > Error in .local(conn, statement, ...) : > Unable to execute statement 'SELECT > mpg, > wt, > SUM(wt) OVER (PARTITION BY hp) AS cum_wgt, > SUM(wt) over () AS tot_wgt, > hp > FROM mtc...'. > Server says 'ParseException:SQLparser:SELECT: function 'sum' not found'. > > > sorry if i'm doing something silly.. > > > > > On Fri, Apr 29, 2016 at 3:37 AM, Roberto Cornacchia < > roberto.cornacchia(a)gmail.com> wrote: > >> I think what you are looking for is SUM(..) OVER(PARTITION BY ..) >> >> Otherwise where would the groups for the sum come from? >> >> Roberto >> >> On 29 April 2016 at 09:28, Anthony Damico <ajdamico(a)gmail.com> wrote: >> >>> hi, monetdb does not support SUM() OVER commands.. does anyone have a >>> smart alternative to implement this? thanks >>> >>> >>> SELECT >>> batch_id, >>> job_count, >>> SUM(job_count) OVER (ORDER BY duration) as cumjobs, >>> SUM(job_count) over () as totjobs, >>> duration >>> FROM >>> test_data ; >>> >>> >>> _______________________________________________ >>> users-list mailing list >>> users-list(a)monetdb.org >>> https://www.monetdb.org/mailman/listinfo/users-list >>> >>> >> >> _______________________________________________ >> users-list mailing list >> users-list(a)monetdb.org >> https://www.monetdb.org/mailman/listinfo/users-list >> >> > > _______________________________________________ > users-list mailing list > users-list(a)monetdb.org > https://www.monetdb.org/mailman/listinfo/users-list > > _______________________________________________ users-list mailing list users-list(a)monetdb.org https://www.monetdb.org/mailman/listinfo/users-list

1 0

The Meaning of Constants in mapi.h
by Brian Hood 28 Apr '16

28 Apr '16

Hi Guys, #define MOK 0 #define MERROR (-1) #define MTIMEOUT (-2) #define MMORE (-3) #define MSERVER (-4) So far i've discovered MOK with value of 0 means connected to MServer and your query went well. MSERVER with value of -4 means that the server doesn't like the query and its bad SQL. I've not been able to discover what the others mean hope someone can help. I've also discovered that mapi_start_talking no longer seems to be implemented in the library guessing its deprecated. Regards, Brian Hood

1 0

Crystal bindings for MonetDB
by Brian Hood 26 Apr '16

26 Apr '16

Hi Guys, Well its been a long, long day putting this together but i actually have working Crystal Language binding for MonetDB. https://github.com/puppetpies/crystal-monetdb-libmapi/blob/4e55863d453be33b… This is the very first stages. I've managed to count 2 rows so far lol. Regards, Brian Hood

2 1

Merge/Remote table causes segfault
by Anderson, David B 26 Apr '16

26 Apr '16

All, I've been able to isolate the seg-fault in gdb, but am not sure how to proceed. Should I file a bug report? There is a NULL pointer access in q_enqueue_(). The q data pointer is NULL. If I look at the flow inside the flow event d, I see the error: "SQLException:sql.bind:unable to find sys.loan201508(purpose)", so it looks like the root cause is higher up/elsewhere. Dave (gdb) where #0 0x00002b39b92fbb2f in q_enqueue_ (q=0x1c0089e0, d=0x1c095b78) at mal_dataflow.c:156 #1 0x00002b39b92fbbbc in q_enqueue (q=0x1c0089e0, d=0x1c095b78) at mal_dataflow.c:164 #2 0x00002b39b92fdd88 in DFLOWscheduler (flow=0x1c094260, w=0x2b39b96a8e40) at mal_dataflow.c:709 #3 0x00002b39b92fe696 in runMALdataflow (cntxt=0x2b39be9b6328, mb=0x1bf02bd0, startpc=1, stoppc=399, stk=0x1c08b7d0) at mal_dataflow.c:892 #4 0x00002b39b93cad1c in MALstartDataflow (cntxt=0x2b39be9b6328, mb=0x1bf02bd0, stk=0x1c08b7d0, pci=0x1c056ac0) at language.c:147 #5 0x00002b39b92f9077 in runMALsequence (cntxt=0x2b39be9b6328, mb=0x1bf02bd0, startpc=1, stoppc=402, stk=0x1c08b7d0, env=0x1be28a30, pcicaller=0x1c08b710) at mal_interpreter.c:631 #6 0x00002b39b92f9418 in runMALsequence (cntxt=0x2b39be9b6328, mb=0x1bda6560, startpc=1, stoppc=0, stk=0x1be28a30, env=0x1be28a30, pcicaller=0x0) at mal_interpreter.c:743 #7 0x00002b39b92f824c in runMAL (cntxt=0x2b39be9b6328, mb=0x1bda6560, mbcaller=0x0, env=0x1be28a30) at mal_interpreter.c:365 #8 0x00002b39b9318b63 in MALengine (c=0x2b39be9b6328) at mal_session.c:637 #9 0x00002b39b9316ebd in runPhase (c=0x2b39be9b6328, phase=4) at mal_scenario.c:515 #10 0x00002b39b931707b in runScenarioBody (c=0x2b39be9b6328) at mal_scenario.c:559 #11 0x00002b39b9317164 in runScenario (c=0x2b39be9b6328) at mal_scenario.c:579 #12 0x00002b39b9317f74 in MSserveClient (dummy=0x2b39be9b6328) at mal_session.c:439 #13 0x00002b39b9317b8b in MSscheduleClient (command=0x1be20190 "p\001\342\033", challenge=0x2b39c017b0c0 "GJMKUZJ4", fin=0x1a3fdf90, fout=0x1bda6a50) at mal_session.c:319 #14 0x00002b39b93d0464 in doChallenge (data=0x1bd88880) at mal_mapi.c:184 #15 0x00002b39b9b4697b in thread_starter (arg=0x1a3fdf60) at gdk_system.c:458 #16 0x00002b39bb03683d in start_thread () from /lib64/libpthread.so.0 #17 0x000000330ded4fdd in clone () from /lib64/libc.so.6 ... (gdb) down #0 0x00002b39b92fbb2f in q_enqueue_ (q=0x1c0089e0, d=0x1c095b78) at mal_dataflow.c:156 156 q->data[q->last++] = d; (gdb) p *q $17 = { size = 0, last = 0, exitcount = 0, data = 0x0, l = { lock = 1 }, s = { sema = { __size = '\000' <repeats 31 times>, __align = 0 }, name = 0x0 } } (gdb) p d $18 = (FlowEvent) 0x1c095b78 (gdb) p *d $19 = { flow = 0x1c094260, pc = 55, blocks = 0, state = 1, clk = 0, cost = -1, hotclaim = 0, argclaim = 0 } (gdb) p *d->flow $20 = { cntxt = 0x2b39be9b6328, mb = 0x1bf02bd0, stk = 0x1c08b7d0, start = 2, stop = 399, status = 0x1c094fe0, error = 0x2b39cc00edc0 "SQLException:sql.bind:unable to find sys.loan201508(purpose)", nodes = 0x1c09a750, edges = 0x1c09c480, flowlock = { lock = 1 }, done = 0x1c0942d0 }

1 1

Fwd: JSON Failed to import
by Ariel Abadi 26 Apr '16

26 Apr '16

Now is working! Thank you guys! Rgds > > > ---------- Forwarded message ---------- > From: Sjoerd Mullender <sjoerd(a)monetdb.org> > Date: 2016-04-21 17:51 GMT-03:00 > Subject: Re: JSON Failed to import > To: users-list(a)monetdb.org > > > This works: > > 301|{"data":[{"cdr_dialnumber":"6596871820"}]} > > So the quoting of the json string doesn't help. > > > On 04/21/2016 09:26 PM, Ariel Abadi wrote: > > Hi! > > > > Can someone help me ? Im trying to upload a CSV file with json data, but > > Im receiving an error. Does anybody has an idea how to solve this? > > > > Thks in advance > > > > mclient -s"COPY 1 RECORDS INTO traf.jsontbl FROM STDIN USING > > DELIMITERS '|','\n','\"' NULL AS 'null'" - < j.csv > > > > *The data on j.csv is:* > > 301|"{\"data\":[{\"cdr_dialnumber\":\"6596871820\"}]}" > > > > *TABLE traf.xx > > * > > > sql>\d jsontbl > > CREATE TABLE "traf"."jsontbl" ( > > "a" INTEGER, > > "b" JSON > > ); > > > > > > > > _______________________________________________ > > users-list mailing list > > users-list(a)monetdb.org > > https://www.monetdb.org/mailman/listinfo/users-list > > > > -- > Sjoerd Mullender > > > _______________________________________________ > users-list mailing list > users-list(a)monetdb.org > https://www.monetdb.org/mailman/listinfo/users-list > >

1 0

Merge/Remote tables causing remote server to segfault
by Anderson, David B 25 Apr '16

25 Apr '16

All, I am seeing odd behavior with Remote/Merge tables. I have a pair of servers, one of which has a merge table that combines 12 remote & 12 local tables (24 total) into a single large table. Queries from the main server on the merge table cause the remote server to crash with a SIGSEGV. If I create a merge table which only contains the remote tables (loan2) the remote server doesn't crash, but I get different responses for the same query in the same session: /opt/flash1/gnmaloan$ mclient -d loan Welcome to mclient, the MonetDB/SQL interactive terminal (Jul2015-SP4) Database: MonetDB v11.21.19 (Jul2015-SP4), 'mapi:monetdb://malxcs1p:50000/loan' Type \q to quit, \? for a list of available commands auto commit mode: on sql>select asofdate,sum(balance),count(loanseqnum) from loan2 group by asofdate; (mapi:monetdb://monetdb@malxcs2p/loan) unable to find sys.loan201402(loanseqnum) sql>select asofdate,sum(balance),count(loanseqnum) from loan2 group by asofdate; +------------+--------------------------+----------+ | asofdate | L1 | L2 | +============+==========================+==========+ | 2014-03-01 | 124075979043384 | 9177029 | | 2014-05-01 | 1.2634454179935e+14 | 9264814 | | 2014-07-01 | 126798740013672 | 9335352 | | 2014-09-01 | 126526301326951 | 9407070 | | 2014-11-01 | 1.2604434513281e+14 | 9478920 | | 2015-01-01 | 1.2644932715183e+14 | 9529639 | | 2015-03-01 | 127602153039423 | 9579604 | | 2015-05-01 | 126615476286119 | 9654842 | | 2015-07-01 | 125415108251273 | 9756258 | | 2015-09-01 | 124427385478338 | 9855118 | | 2015-11-01 | 126150962049943 | 9945919 | | 2016-01-01 | 129313947667802 | 10011556 | +------------+--------------------------+----------+ 12 tuples (0.9s) The only thing I see in the crashing server's log is: 2016-04-25 16:55:50 MSG merovingian[28226]: target connection is on local UNIX domain socket, passing on filedescriptor instead of proxying 2016-04-25 16:55:50 MSG merovingian[28226]: database 'loan' (2706) was killed by signal SIGSEGV 2016-04-25 16:56:37 MSG merovingian[28226]: starting database 'loan', up min/avg/max: 2m/1h/2h, crash average: 1.00 0.90 0.33 (12-2=10) 2016-04-25 16:56:37 MSG loan[28022]: arguments: /opt/mr/monetdb/bin/mserver5 --dbpath=/opt/flash2/monetdb2/loan --set merovingian_uri=mapi:monetdb://malxcs2p:50000/loan --set mapi_open=false --set mapi_port=0 --set mapi_usock=/opt/flash2/monetdb2/loan/.mapi.sock --set monet_vault_key=/opt/flash2/monetdb2/loan/.vaultkey --set gdk_nr_threads=24 --set max_clients=64 --set sql_optimizer=default_pipe --set monet_daemon=yes Not sure where to start debugging this. I guess I will see if this still occurs with a small number of rows in the base tables. Thanks, Dave

1 0

MonetDB Remote / Merge bug ?
by Brian Hood 25 Apr '16

25 Apr '16

Hi All, It seems when i revert back the original query the first time it breaks and gives a MAL error then the second time it works fine its also very reproduce able. Welcome to mclient, the MonetDB/SQL interactive terminal (Jul2015-SP4) Database: MonetDB v11.21.19 (Jul2015-SP4), 'mapi:monetdb://mdb-master-01:50000/threatmonitor' Type \q to quit, \? for a list of available commands auto commit mode: on sql>select * from ippacket a JOIN tcppacket b on (a.guid = b.guid) limit 1; +-----------+------------+------+------------+------+-------+------+------+------+------+------------+------------+------+------+------+------------+------------+------+-------+------+------+------+------+------+------+------+ | guid | recv_date | ip_d | ip_dst | ip_h | ip_id | ip_l | ip_m | ip_o | ip_p | ip_src | ip_sum | ip_t | ip_t | ip_v | guid | recv_date | tcp_ | tcp_d | tcp_ | tcp_ | tcp_ | tcp_ | tcp_ | tcp_ | tcp_ |> : : : f : : len : : en : f : ff : roto : : : os : tl : er : : : data : port : ack : fin : syn : rst : psh : urg : off :> : : : : : : : : : : : : : : : : : : _len : : : : : : : : :> +===========+============+======+============+======+=======+======+======+======+======+============+============+======+======+======+============+============+======+=======+======+======+======+======+======+======+======+ | 0413e543- | 2015-07-09 | Y | 172.17.0.1 | 5 | 28147 | 60 | N | 0 | 6 | 172.17.42. | 19108 | 0 | 64 | 4 | 0413e543-5 | 2015-07-09 | 0 | 15672 | N | N | Y | N | N | N | 10 | : 52b7-ee...> 20:50:32 : : : : : : : : : 1 : : : : : 2b7-eeda-0 : 20:50:32 : : : : : : : : : : : > +0000 : : : : : : : : : : : : : : a77-a2de...> +0000 : : : : : : : : : : +-----------+------------+------+------------+------+-------+------+------+------+------+------------+------------+------+------+------+------------+------------+------+-------+------+------+------+------+------+------+------+ 1 tuple (250.636ms) !6 columns dropped, 2 fields truncated! sql>select * from ippacket a JOIN tcppacket b on (b.guid = a.guid) limit 1; +-----------+------------+------+------------+------+-------+------+------+------+------+------------+------------+------+------+------+------------+------------+------+-------+------+------+------+------+------+------+------+ | guid | recv_date | ip_d | ip_dst | ip_h | ip_id | ip_l | ip_m | ip_o | ip_p | ip_src | ip_sum | ip_t | ip_t | ip_v | guid | recv_date | tcp_ | tcp_d | tcp_ | tcp_ | tcp_ | tcp_ | tcp_ | tcp_ | tcp_ |> : : : f : : len : : en : f : ff : roto : : : os : tl : er : : : data : port : ack : fin : syn : rst : psh : urg : off :> : : : : : : : : : : : : : : : : : : _len : : : : : : : : :> +===========+============+======+============+======+=======+======+======+======+======+============+============+======+======+======+============+============+======+=======+======+======+======+======+======+======+======+ | 0413e543- | 2015-07-09 | Y | 172.17.0.1 | 5 | 28147 | 60 | N | 0 | 6 | 172.17.42. | 19108 | 0 | 64 | 4 | 0413e543-5 | 2015-07-09 | 0 | 15672 | N | N | Y | N | N | N | 10 | : 52b7-ee...> 20:50:32 : : : : : : : : : 1 : : : : : 2b7-eeda-0 : 20:50:32 : : : : : : : : : : : > +0000 : : : : : : : : : : : : : : a77-a2de...> +0000 : : : : : : : : : : +-----------+------------+------+------------+------+-------+------+------+------+------+------------+------------+------+------+------+------------+------------+------+-------+------+------+------+------+------+------+------+ 1 tuple (248.468ms) !6 columns dropped, 2 fields truncated! sql>select * from ippacket a JOIN tcppacket b on (a.guid = b.guid) limit 1; (mapi:monetdb://monetdb@172.17.0.6/threatmonitor) 'user.l10' undefined in: (rmt2533_X_18_bat_oid_str:bat[:oid,:str],rmt2535_X_21_bat_oid_str:bat[:oid,:str],rmt2537_X_22_bat_oid_int:bat[:oid,:int],rmt2539_X_24_bat_oid_int:bat[:oid,:int],rmt2541_X_25_bat_oid_str:bat[:oid,:str],rmt2543_X_26_bat_oid_str:bat[:oid,:str],rmt2545_X_27_bat_oid_str:bat[:oid,:str],rmt2547_X_28_bat_oid_str:bat[:oid,:str],rmt2549_X_29_bat_oid_str:bat[:oid,:str],rmt2551_X_30_bat_oid_str:bat[:oid,:str],rmt2553_X_31_bat_oid_int:bat[:oid,:int],rmt2555_X_32_bat_oid_int:bat[:oid,:int],rmt2556_X_33_bat_oid_lng:bat[:oid,:lng],rmt2557_X_35_bat_oid_str:bat[:oid,:str],rmt2558_X_36_bat_oid_int:bat[:oid,:int],rmt2559_X_37_bat_oid_str:bat[:oid,:str],rmt2560_X_38_bat_oid_int:bat[:oid,:int],rmt2562_X_39_bat_oid_oid:bat[:oid,:oid]) := user.l10(); sql>select * from ippacket a JOIN tcppacket b on (a.guid = b.guid) limit 1; +-----------+------------+------+------------+------+-------+------+------+------+------+------------+------------+------+------+------+------------+------------+------+-------+------+------+------+------+------+------+------+ | guid | recv_date | ip_d | ip_dst | ip_h | ip_id | ip_l | ip_m | ip_o | ip_p | ip_src | ip_sum | ip_t | ip_t | ip_v | guid | recv_date | tcp_ | tcp_d | tcp_ | tcp_ | tcp_ | tcp_ | tcp_ | tcp_ | tcp_ |> : : : f : : len : : en : f : ff : roto : : : os : tl : er : : : data : port : ack : fin : syn : rst : psh : urg : off :> : : : : : : : : : : : : : : : : : : _len : : : : : : : : :> +===========+============+======+============+======+=======+======+======+======+======+============+============+======+======+======+============+============+======+=======+======+======+======+======+======+======+======+ | 0413e543- | 2015-07-09 | Y | 172.17.0.1 | 5 | 28147 | 60 | N | 0 | 6 | 172.17.42. | 19108 | 0 | 64 | 4 | 0413e543-5 | 2015-07-09 | 0 | 15672 | N | N | Y | N | N | N | 10 | : 52b7-ee...> 20:50:32 : : : : : : : : : 1 : : : : : 2b7-eeda-0 : 20:50:32 : : : : : : : : : : : > +0000 : : : : : : : : : : : : : : a77-a2de...> +0000 : : : : : : : : : : +-----------+------------+------+------------+------+-------+------+------+------+------+------------+------------+------+------+------+------------+------------+------+-------+------+------+------+------+------+------+------+ 1 tuple (213.998ms) !6 columns dropped, 2 fields truncated! sql> Regards, Brian Hood

1 0

A good strategy for loading bulk data into monetdb
by Knezevic Nikola 25 Apr '16

25 Apr '16

Hi all, I'm trying to load large amounts of data into monetdb (migrating from mysql). Since I'm using Jul2015-SP4 release, I can't create local files and I can't use external tools, my approach is as following: For each table, I partition the data based on the day, and then: 10 create one table in monetdb for each day, and a merge table that takes all these tables 20 fetch data from mysql (mysql -B -N -C ...) 30 _pipe_ data to mclient (that is invoked with mclient ... -c "COPY 2000000 RECORDS FROM STDIN INTO ..." -) 40 load data into corresponding table (the right day) 50 sleep some seconds 60 goto 20 Each daily table is around 6'000'000 rows, but I have some that have ~150'000'000 rows. However, I noticed that when I'm doing this, I quickly run out of disk space, as monetdb is creating really big logs (in /data/monetdb/some_schema/sql_logs). Size of that folder is usually 600KB, but once the process of migration starts, it goes to several GBs. Not only that, but /data/monetdb/some_schema/bat also grows a lot. The surprising part, and the motivation for this emails, is how well monetdb compresses the data. If I migrate only one day worth of data, and then restart monetdbd (once or twice) and run 'analyze some_schema', sql_logs size drops to normal levels, and I get a lot of reclaimed space (even in bat directory). I tried to put a 'sleep(120)' after each partition and running 'analyze some_schema' after each iteration, but couldn't get the same behaviour (and it is also quite slow). Is there a way to trigger the compression and reduce disk space overhead without restarting mserver, given the above constraints? Thanks, Nikola

3 4

MonetDB Driver / MAPI Implementation.
by Brian Hood 23 Apr '16

23 Apr '16

Hi All, I am have tried to convert the Ruby MonetDB driver into Crystal with little success as i want to MonetDB with the Crystal Programming Language for higher performance and to handle bigger datasets at more C level performance. I'm currently making a transition from Ruby to Crystal and want to know where the specification of MonetDB's MAPI interface lives. Any help appreciated. Regards, Brian Hood

2 1

Merovingian startup error: rapi.prelude missing
by Anderson, David B 23 Apr '16

23 Apr '16

All, I've built MonetDB from source using 11.21.19, but am having problems starting the Merovingian process. I am getting the following error even though I chose -disable-rintegration in the configure step. Does anyone know what I might have done incorrectly? Thanks, Dave 2016-04-22 23:36:11 MSG loan[7218]: # MonetDB 5 server v11.21.19 "Jul2015-SP4" 2016-04-22 23:36:11 MSG loan[7218]: # Serving database 'loan', using 24 threads 2016-04-22 23:36:11 MSG loan[7218]: # Compiled for x86_64-unknown-linux-gnu/64bit with 64bit OIDs and 128bit integers dynamically linked 2016-04-22 23:36:11 MSG loan[7218]: # Found 188.787 GiB available main-memory. 2016-04-22 23:36:11 MSG loan[7218]: # Copyright (c) 1993-July 2008 CWI. 2016-04-22 23:36:11 MSG loan[7218]: # Copyright (c) August 2008-2015 MonetDB B.V., all rights reserved 2016-04-22 23:36:11 MSG loan[7218]: # Visit http://www.monetdb.org/ for further information 2016-04-22 23:36:11 MSG loan[7218]: !TypeException:user.main[14]:object code for command rapi.prelude missing 2016-04-22 23:36:11 MSG loan[7218]: !TypeException:user.main[14]:'rapi.prelude' undefined in: rapi.prelude(); 2016-04-22 23:36:11 MSG merovingian[6082]: database 'loan' (7218) has exited with exit status 0 2016-04-22 23:36:11 MSG merovingian[6082]: database 'loan' has shut down 2016-04-22 23:36:15 ERR merovingian[6082]: client error: database 'loan' started up, but failed to open up a communication channel

3 4

force monetdb not to write intermediate results
by Shmagi Kavtaradze 22 Apr '16

22 Apr '16

I have 64gb ram on the server and want monetdb to use ram instead of writing intermediate results. Is there any possibility to do that? Thanks in advance.

1 0

hash join - why hashing on the smaller bat?
by Roberto Cornacchia 22 Apr '16

22 Apr '16

Related to my previous question about persisting hashes, I would like to throw another one. BATsubjoin has a series of heuristics to decide what type of join implementation to use. When using hash-join, the latest rule says: if nothing else applied, build a hash on the smaller bat. Could you tell me what is the rationale for this? >From what I could verify: - when sizes are comparable: it doesn't really make much difference which side is hashed - when sizes differ much: sure, building the hash table on that is much cheaper, but the join as a whole becomes 4-5 times slower then when hashing on the larger bat. In which case hashing on the larger bat is a good option? Cheers, Roberto

2 3

JSON Failed to import
by Ariel Abadi 21 Apr '16

21 Apr '16

Hi! Can someone help me ? Im trying to upload a CSV file with json data, but Im receiving an error. Does anybody has an idea how to solve this? Thks in advance mclient -s"COPY 1 RECORDS INTO traf.jsontbl FROM STDIN USING DELIMITERS '|','\n','\"' NULL AS 'null'" - < j.csv *The data on j.csv is:* 301|"{\"data\":[{\"cdr_dialnumber\":\"6596871820\"}]}" *TABLE traf.xx* sql>\d jsontbl CREATE TABLE "traf"."jsontbl" ( "a" INTEGER, "b" JSON );

3 3

The extra optimization option with configure
by Vijay Krishna 19 Apr '16

19 Apr '16

Hi, Today while compiling from MonetDB source code, I found the following option that could be given with the ./configure step of installation. --enable-optimize enable extra optimization (default=no) Will enabling this gives an extra optimization for the database's performance? Or its simply optimizing the installation process? Either cases, what does this optimize? Thanks, Vijay.

1 0

Some performance oddities in numerical comparisons on merge tables
by Marc Ballarin 18 Apr '16

18 Apr '16

Hi, I have a larger *merge table*, of which the relevant columns in this query should be: "timestamp" TIMESTAMP "abort_flag" TINYINT "timestamp" is roughly ascending, but far from monotonous. "abort_flag" has a low cardinality with the following possible values and number of occurrences: | 0 | 735660 | | 1 | 30119 | | 2 | 1486 | | 4 | 15 | Both columns are completely populated. Total row numbers are: merge table: 3,773,559,881 current partition: 30,754,353 I get the the following performance numbers: sql>select count(*) from mytable where timestamp between timestamp '2016-04-15 13:05:05' and timestamp '2016-04-15 13:20:05' and abort_flag in (0); +--------+ | L1 | +========+ | 735660 | +--------+ 1 tuple (1.0s) sql>select count(*) from mytable where timestamp between timestamp '2016-04-15 13:05:05' and timestamp '2016-04-15 13:20:05' and abort_flag = 0; +--------+ | L1 | +========+ | 735660 | +--------+ 1 tuple (34.0s) So there is a very significant, and IMHO surprising, performance difference between the "equals" and the "in" comparison. Doing the same query directly against the sub table has a much more consistent performance: sql>select count(*) from mytable_20160415 where timestamp between timestamp '2016-04-15 13:05:05' and timestamp '2016-04-15 13:20:05' and abort_flag IN (0); +--------+ | L1 | +========+ | 735660 | +--------+ 1 tuple (618.914ms) sql>select count(*) from mytable_20160415 where timestamp between timestamp '2016-04-15 13:05:05' and timestamp '2016-04-15 13:20:05' and abort_flag = 0; +--------+ | L1 | +========+ | 735660 | +--------+ 1 tuple (1.1s) The same can be observed with another column, where ">= 4" is much slower than the (in my case) equivalent "IN (4,5,6)". I guess, for my uses I can always switch to the "in" conditions, because all relevant columns should have very low cardinality, but in general, this seems odd. Is there an explanation for this behaviour, like optimizer problems with merge tables? Or maybe a way to avoid it? Regards, Marc

3 3

回复：Re: Some performance oddities in numerical comparisons on merge tables
by chenkaijiang＠bigknow.com.cn 17 Apr '16

17 Apr '16

I understand that if MonetDB uses the timestamp as a filter (more selective), then it is faster. But does the MonetDB get this speed by purely sequence scan on timestamp column with 3,773,559,881 rows, or does it use some technique such as binary search? > Total row numbers are: > merge table: 3,773,559,881 > current partition: 30,754,353 > > I get the the following performance numbers: > sql>select count(*) from mytable where timestamp between timestamp > '2016-04-15 13:05:05' and timestamp '2016-04-15 13:20:05' and abort_flag > in (0); > +--------+ > | L1 | > +========+ > | 735660 | > +--------+ > 1 tuple (1.0s) ----- 原始邮件 ----- 发件人：Niels Nes <Niels.Nes(a)cwi.nl> 收件人：Communication channel for MonetDB users <users-list(a)monetdb.org> 主题：Re: Some performance oddities in numerical comparisons on merge tables 日期：2016-4-17 18:31:11 On Fri, Apr 15, 2016 at 05:18:27PM +0200, Marc Ballarin wrote: > Hi, > > I have a larger *merge table*, of which the relevant columns in this > query should be: > "timestamp" TIMESTAMP > "abort_flag" TINYINT > > "timestamp" is roughly ascending, but far from monotonous. > "abort_flag" has a low cardinality with the following possible values > and number of occurrences: > | 0 | 735660 | > | 1 | 30119 | > | 2 | 1486 | > | 4 | 15 | > > Both columns are completely populated. > > Total row numbers are: > merge table: 3,773,559,881 > current partition: 30,754,353 > > I get the the following performance numbers: > sql>select count(*) from mytable where timestamp between timestamp > '2016-04-15 13:05:05' and timestamp '2016-04-15 13:20:05' and abort_flag > in (0); > +--------+ > | L1 | > +========+ > | 735660 | > +--------+ > 1 tuple (1.0s) > > sql>select count(*) from mytable where timestamp between timestamp > '2016-04-15 13:05:05' and timestamp '2016-04-15 13:20:05' and abort_flag > = 0; > +--------+ > | L1 | > +========+ > | 735660 | > +--------+ > 1 tuple (34.0s) > > So there is a very significant, and IMHO surprising, performance > difference between the "equals" and the "in" comparison. Well not that surprising. The choice on which operator in MonetDB isn't based on the content of the table but using relatively simpel rules. A equality is done before a range, and a range before an in. So in case of the in, the very selective range query is done first. In case of the equality, the equality is done first. Niels > > Doing the same query directly against the sub table has a much more > consistent performance: > sql>select count(*) from mytable_20160415 where timestamp between > timestamp '2016-04-15 13:05:05' and timestamp '2016-04-15 13:20:05' and > abort_flag IN (0); > +--------+ > | L1 | > +========+ > | 735660 | > +--------+ > 1 tuple (618.914ms) > > sql>select count(*) from mytable_20160415 where timestamp between > timestamp '2016-04-15 13:05:05' and timestamp '2016-04-15 13:20:05' and > abort_flag = 0; > +--------+ > | L1 | > +========+ > | 735660 | > +--------+ > 1 tuple (1.1s) > > The same can be observed with another column, where ">= 4" is much > slower than the (in my case) equivalent "IN (4,5,6)". > > I guess, for my uses I can always switch to the "in" conditions, because > all relevant columns should have very low cardinality, but in general, > this seems odd. > > Is there an explanation for this behaviour, like optimizer problems with > merge tables? Or maybe a way to avoid it? > > Regards, > Marc > _______________________________________________ > users-list mailing list > users-list(a)monetdb.org > https://www.monetdb.org/mailman/listinfo/users-list -- Niels Nes, Manager ITF, Centrum Wiskunde & Informatica (CWI) Science Park 123, 1098 XG Amsterdam, The Netherlands room L3.14, phone ++31 20 592-4098 sip:4098@sip.cwi.nl url: https://www.cwi.nl/people/niels e-mail: Niels.Nes(a)cwi.nl _______________________________________________ users-list mailing list users-list(a)monetdb.org https://www.monetdb.org/mailman/listinfo/users-list

2 1

Use buffer instead of cache if possible
by Shmagi Kavtaradze 17 Apr '16

17 Apr '16

I am doing join and bitwise and on a huge data. Monetdb caches 61 GB of ram and writes 152GB on disk. I use dstat for resource monitoring. I see that buffer is not used at all, monetdb just caches memory and used reads/writes. I know monetdb uses mapped files, but is there a possibility to use buffer more than caching? I was not able to find any information how to force process use more buffer. P.S sorry for stupid question, I am not very strong in linux stuff. Thanks in advance.

1 0

Problem with COPY INTO
by Knezevic Nikola 15 Apr '16

15 Apr '16

Hi all, I've lost a significant time trying to get data from mysql database into monetdb. Since I have a lot of data, I wanted to make it as fast and automated as possible, but to no avail. There are several issues I encountered, and I'd appreciate any help on any of them. I'm running the latest monetDB on CentOS: % mclient --version mclient, the MonetDB interactive terminal (Jul2015-SP4) support for command-line editing compiled-in character encoding: UTF-8 1. I tried following the last example on https://www.monetdb.org/Documentation/SQLreference/Permissions in order to get another user to be able to perform COPY INTO, but it fails: % mclient -u monetdb -d some_db password: Welcome to mclient, the MonetDB/SQL interactive terminal (Jul2015-SP4) Database: MonetDB v11.21.19 (Jul2015-SP4), 'mapi:monetdb://internal:50000/some_db' Type \q to quit, \? for a list of available commands auto commit mode: on sql>GRANT COPY FROM TO copy_role; syntax error, unexpected COPY in: "grant copy" 2. Ok, since I can't run COPY INTO FROM FILE, I decided to go with FROM STDIN version. To do so, I dump the mysql database and import that in monetdb. That also fails: % mysql -N -B -u reader -pxxx some_db -h some_host -e "SELECT * FROM errortable;"|mclient -d some_db -s "COPY 15 RECORDS INTO errortable FROM STDIN USING DELIMITERS '\t','\n','\'';" - Failed to import table Column value 1 missing syntax error, unexpected sqlINT in: "100" % mclient -u writer -d some_db password: Welcome to mclient, the MonetDB/SQL interactive terminal (Jul2015-SP4) Database: MonetDB v11.21.19 (Jul2015-SP4), 'mapi:monetdb://internal:50000/some_db' Type \q to quit, \? for a list of available commands auto commit mode: on sql>\d errortable CREATE TABLE "some_db"."errortable" ( "rejectreason" INTEGER NOT NULL, "text" CHARACTER LARGE OBJECT ); Another try also fails (still using STDIN): % mysql -N -B -u reader -pxxx some_db -h some_host -e "SELECT * FROM errortable;" >test.out % mclient -d some_db -s "COPY 15 RECORDS INTO errortable FROM STDIN USING DELIMITERS '\t','\n','\'';" <test.out Failed to import table Column value 1 missing % mclient -d some_db -s "COPY 15 RECORDS INTO errortable FROM STDIN USING DELIMITERS '\t','\n','\'';" - <test.out Failed to import table Column value 1 missing syntax error, unexpected sqlINT in: "100" (note different error messages, and also note that 100 is a perfectly good sqlINT) If I try the following, this succeeds. However, this is something I'm trying to avoid, as I'd had to create huge files (100+GB), which is an absolute waste of space and time. % mysql -N -B -u reader -pxxx some_db -h some_host -e "SELECT * FROM errortable;" >test.out % mclient -d some_db -s "COPY 15 RECORDS INTO errortable FROM '/home/nikola/test.out' USING DELIMITERS '\t','\n','\'';" COPY INTO: insufficient privileges: COPY INTO from file(s) requires database administrator rights, use 'COPY INTO "errortable" FROM STDIN' instead # obviously, fails due to (1) % mclient -u monetdb -d some_db -s "COPY 15 RECORDS INTO some_db.errortable FROM '/home/nikola/test.out' USING DELIMITERS '\t','\n','\'';" password: 15 affected rows The file in question is super simple, a list of integers with textual description: 100 Some limit exceeded 101 Another limit exceeded 404 Service temporarily not available 405 Service not available 500 Server not available 700 End of world as we know it 701 User already logged in 10000 Book not found 10001 Price reasonability doubtfull 10002 Client is well known 20001 Activation in progress 20002 Pre-load limit exceeded 30000 Pre-load limit exceeded again 1 test 5 Invalid value 0 to divide with Any hints on how to solve this super annoying problem? Thanks, Nikola

6 12

Re: Can MonetDB be slowed down by a single query?
by Vijay Krishna 14 Apr '16

14 Apr '16

Hi Martin, Thanks for the reply. Is there any workaround to restrict the memory used by a single query or client connection - like some configuration variables? I see a lot of worker threads executing a single query in parallel. I studied that the number of worker threads can be controlled by the gdk_threads variable. But is there anyway to restrict the maximum memory that can be used by any particular worker thread? Or can this be controlled at the OS level? We thought of restricting the memory used by the entire mserver with ulimit, but that still doesn't solve the problem of the possibility of a single query causing a trouble. Is there any other way that this can be controlled as the OS level? @Martin and Others, If both are NO, then what are the safety measures to be followed in production to avoid such problems? (Or, I may sound little dumb - is this not a production problem at all?) Has anyone faced any such issues? Thanks in advance. Thanks & Regards, Vijayakrishna.P. Mobile : (+91) 9500402305. On Thu, Apr 14, 2016 at 1:20 PM, Vijay Krishna <vijayakrishna55(a)gmail.com> wrote: > Hi Martin, > > Thanks for the reply. > > Is there any workaround to restrict the memory used by a single query or > client connection - like some configuration variables? > > I see a lot of worker threads executing a single query in parallel (please > refer screenshot attached). I studied that the number of worker threads can > be controlled by the gdk_threads variable. But is there anyway to restrict > the maximum memory that can be used by any particular worker thread? > > Or can this be controlled at the OS level? > We thought of restricting the memory used by the entire mserver with > ulimit, but that still doesn't solve the problem of the possibility of a > single query causing a trouble. Is there any other way that this can be > controlled as the OS level? > > @Martin and Others, > If both are NO, then what are the safety measures to be followed in > production to avoid such problems? > (Or, I may sound little dumb - is this not a production problem at all?) > Has anyone faced any such issues? > > Thanks in advance. > > > > > Thanks & Regards, > > Vijayakrishna.P. > Mobile : (+91) 9500402305. > > On Thu, Apr 14, 2016 at 12:15 PM, Martin Kersten <martin.kersten(a)cwi.nl> > wrote: > >> On 14/04/16 08:30, Vijay Krishna wrote: >> >>> Hi, >>> >>> As per MonetDB's architecture, the memory allocated to a single query >>> (thread) is left with the OS and is not controlled by the mserver. >>> >>> If so, I am wondering how MonetDB would react to the following scenario. >>> >>> I receive a client connection with a single costly query whose execution >>> would require all of the RAM's memory. >>> >> Hi >> >> Both queries will run concurrently and fight for the total memory. >> However, after some time, the oldest query is put on lower priority >> >> >>> Now, when I receive a second connection that runs a similar costly >>> operation, will MonetDB start processing this second query by using the >>> available meagre memory (RAM leftovers or from disk swap) or will MonetDB >>> wait till the first query releases some of >>> its memory? >>> >>> >>> >>> Thanks & Regards, >>> >>> Vijayakrishna.P. >>> Mobile : (+91) 9500402305. >>> >>> >>> _______________________________________________ >>> users-list mailing list >>> users-list(a)monetdb.org >>> https://www.monetdb.org/mailman/listinfo/users-list >>> >>> >> _______________________________________________ >> users-list mailing list >> users-list(a)monetdb.org >> https://www.monetdb.org/mailman/listinfo/users-list >> > >

1 0

Can MonetDB be slowed down by a single query?
by Vijay Krishna 14 Apr '16

14 Apr '16

Hi, As per MonetDB's architecture, the memory allocated to a single query (thread) is left with the OS and is not controlled by the mserver. If so, I am wondering how MonetDB would react to the following scenario. I receive a client connection with a single costly query whose execution would require all of the RAM's memory. Now, when I receive a second connection that runs a similar costly operation, will MonetDB start processing this second query by using the available meagre memory (RAM leftovers or from disk swap) or will MonetDB wait till the first query releases some of its memory? Thanks & Regards, Vijayakrishna.P. Mobile : (+91) 9500402305.

2 1

Strange behaviour of epoch() when converting from Unix time
by Marc Ballarin 12 Apr '16

12 Apr '16

Hi, I am observing a surprising behaviour of the epoch function: The following works as expected: SELECT epoch(now()) AS "NOW", epoch(now() - INTERVAL '5' MINUTE) AS "5 MIN AGO"; +------------+------------+ | NOW | 5 MIN AGO | +============+============+ | 1460453860 | 1460453560 | +------------+------------+ The inverse does not: SELECT epoch(1460453860) AS "NOW", epoch(1460453860-300) AS "300 SECONDS AGO"; +----------------------------+----------------------------+ | NOW | 300 SECONDS AGO | +============================+============================+ | 2016-04-12 09:37:40.000000 | 1970-01-17 21:40:53.560000 | +----------------------------+----------------------------+ The "now" part is fine, the "300 seconds ago" part is obviously not what I expect. Am I missing something or is this a bug? Regards, Marc

2 1

Check reads per sec and CPU utilization
by Shmagi Kavtaradze 11 Apr '16

11 Apr '16

I was not able to find any information how to check CPU utilization and read per sec for a query. Any ideas how to get these results? Thanks in advance.

3 3

Re: How to do profiling in MAL interface?
by Constantine Feng 11 Apr '16

11 Apr '16

Hi Martin, Thanks. stethoscope does do some work. But since it is for online monitoring, it is inconvenient for offline analysis of trace profiles. After reading more docs on the web, I find Tachograph comes closest to what I want. A minor problem is that the schema of the generated files (.trace, .csv, .json) is not given. Somehow I have to guess. And seems that each file format covers a different subset of attributes. - Neo > > ------------------------------ > > Message: 2 > Date: Sat, 9 Apr 2016 08:17:15 +0200 > From: Martin Kersten <martin.kersten(a)cwi.nl> > To: users-list(a)monetdb.org > Subject: Re: How to do profiling in MAL interface? > Message-ID: <57089E6B.7050504(a)cwi.nl> > Content-Type: text/plain; charset=windows-1252; format=flowed > > You better consider using the stethoscope for doing this kind of analysis. > The documentation need some updates. > > > On 09/04/16 06:07, Constantine Feng wrote: > > Hi all, > > > > I know I can use the TRACE SQL command. But I want to do it in the > "-lmal" interface (because I massaged the MAL plans a bit to test my > research ideas). > > > > Particularly, I find the information given on this website ( > https://www.monetdb.org/Documentation/Manuals/MonetDB/Modules/profiler ) > doesn't match the source code. I can't find the profiler.activate() or > profiler.deactivate(), for example. > > > > > > mal>manual.help("profiler.*"); > > profiler.cpuload > > profiler.cpustats > > profiler.cleanup > > profiler.closeStream > > profiler.dumpTrace > > profiler.getSystemTime > > profiler.getUserTime > > profiler.getDiskWrites > > profiler.getDiskReads > > profiler.getTrace > > profiler.noop > > profiler.openStream > > profiler.reset > > profiler.setHeartbeat > > profiler.stop > > profiler.stethoscope > > profiler.tomograph > > > > > > Version: (Jul2015-SP3). Build from source. > > > > - Neo > > > > > > _______________________________________________ > > users-list mailing list > > users-list(a)monetdb.org > > https://www.monetdb.org/mailman/listinfo/users-list > > > >

1 0

Announcement: New Jul2015-SP4 Bugfix release of MonetDB suite
by Sjoerd Mullender 11 Apr '16

11 Apr '16

The MonetDB team at CWI/MonetDB BV is pleased to announce the Jul2015-SP4 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-SP4 bugfix release MonetDB Common * Fixed a bug that caused various instances where old data returned or where crashes occurred. The problem was that internally data wasn't always marked dirty when it was being changed, causing later processing to not deal with the changed data correctly. Bug Fixes * 3905: MonetDB doesn't handle ANY/SOME/ALL operator correctly * 3929: R aggregate not recognized when using 3 or more parameters * 3965: Not possible to quote/escape single quote character in the name of the file to load. * 3968: Missing double use of column names

1 0

Can not enable python
by Shmagi Kavtaradze 10 Apr '16

10 Apr '16

I created function in python, but when tried to call, I receive : Embedded Python has not been enabled. Start server with --set embedded_py=true I stopped farm. used mserver5 --set embedded_py=true and got in mserver5 console output: # MonetDB/SQL module loaded # MonetDB/Python module loaded Then I started farm and still getting same error. Then I did this: monetdb stop dbname monetdb set embedpy=true dbname monetdb start dbname Still getting same. Any ideas what I am doing wrong?

2 2

Fwd: Function does not complete
by Shmagi Kavtaradze 09 Apr '16

09 Apr '16

Thanks for answer. I have bits of size 50, so I need bigint/long long int. Problem is in while loop. When I use bitand(a,b) and numbers of digits of a and b both are 18-19, the system enters endless loop I think. Bigint maximum value is 19 digits long.I have mistakes in code when using unsigned int and I changed, but same problem. As I know from data types page, monetdb uses only signed integers, so I use only signed ones. Any ideas why does it hang on 18-19 digits and not on 1-17 digits long numbers?

1 0

How to do profiling in MAL interface?
by Constantine Feng 09 Apr '16

09 Apr '16

Hi all, I know I can use the TRACE SQL command. But I want to do it in the "-lmal" interface (because I massaged the MAL plans a bit to test my research ideas). Particularly, I find the information given on this website ( https://www.monetdb.org/Documentation/Manuals/MonetDB/Modules/profiler ) doesn't match the source code. I can't find the profiler.activate() or profiler.deactivate(), for example. mal>manual.help("profiler.*"); profiler.cpuload profiler.cpustats profiler.cleanup profiler.closeStream profiler.dumpTrace profiler.getSystemTime profiler.getUserTime profiler.getDiskWrites profiler.getDiskReads profiler.getTrace profiler.noop profiler.openStream profiler.reset profiler.setHeartbeat profiler.stop profiler.stethoscope profiler.tomograph Version: (Jul2015-SP3). Build from source. - Neo

2 1

Controlling --dbextras Using MonetDB(D)
by Alex Manaev 09 Apr '16

09 Apr '16

Hi, I use wrapper shell in such case. May be useful for you. $ mv /usr/bin/mserver5 /usr/bin/mserver5.bin $ vim /usr/bin/mserver5 #!/usr/bin/env bash exec /usr/bin/mserver5.bin --set gdk_vmtrim yes --set gdk_vm_maxsize 200000000000 --dbextra=/mnt/data/monetdb/extra $@ Alexey Manaev, Head of software development CJSC "RUTUBE"

3 4

monetdb crash as start : Logger_new: BBPrename to sql_snapshots_bid failed
by Pierre-Adrien Coustillas 08 Apr '16

08 Apr '16

Hello, after a server crash : # mclient comptage_cookie_telerama monetdbd: internal error while starting mserver, please refer to the logs # cat merovingian.log 2016-04-08 14:28:33 MSG merovingian[13582]: starting database 'comptage_cookie_telerama', up min/avg/max: 28m/4w/10w, crash average: 1.00 1.00 1.00 (246-6=240) 2016-04-08 14:28:34 MSG comptage_cookie_telerama[16350]: arguments: /usr/bin/mserver5 --dbpath=/var/lib/monetdb/comptage_cookie_telerama --set merovingian_uri=mapi:monetdb://groupelemonde-crm:50000/comptage_cookie_telerama --set mapi_open=false --set mapi_port=0 --set mapi_usock=/var/lib/monetdb/comptage_cookie_telerama/.mapi.sock --set monet_vault_key=/var/lib/monetdb/comptage_cookie_telerama/.vaultkey --set gdk_nr_threads=8 --set max_clients=64 --set sql_optimizer=default_pipe --set monet_daemon=yes 2016-04-08 14:28:34 MSG comptage_cookie_telerama[16350]: # MonetDB 5 server v11.21.13 "Jul2015-SP2" 2016-04-08 14:28:34 MSG comptage_cookie_telerama[16350]: # Serving database 'comptage_cookie_telerama', using 8 threads 2016-04-08 14:28:34 MSG comptage_cookie_telerama[16350]: # Compiled for x86_64-pc-linux-gnu/64bit with 64bit OIDs and 128bit integers dynamically linked 2016-04-08 14:28:34 MSG comptage_cookie_telerama[16350]: # Found 64.000 GiB available main-memory. 2016-04-08 14:28:34 MSG comptage_cookie_telerama[16350]: # Copyright (c) 1993-July 2008 CWI. 2016-04-08 14:28:34 MSG comptage_cookie_telerama[16350]: # Copyright (c) August 2008-2015 MonetDB B.V., all rights reserved 2016-04-08 14:28:34 MSG comptage_cookie_telerama[16350]: # Visit http://www.monetdb.org/ for further information 2016-04-08 14:28:34 MSG comptage_cookie_telerama[16350]: # Listening for UNIX domain connection requests on mapi:monetdb:///var/lib/monetdb/comptage_cookie_telerama/.mapi.sock 2016-04-08 14:28:34 MSG comptage_cookie_telerama[16350]: # Start processing logs sql/sql_logs version 52200 2016-04-08 14:28:34 MSG merovingian[13582]: database 'comptage_cookie_telerama' (16350) has exited with exit status 1 2016-04-08 14:28:34 ERR merovingian[13582]: client error: database 'comptage_cookie_telerama' appears to shut itself down after starting, check monetdbd's logfile for possible hints 2016-04-08 14:28:34 MSG merovingian[13582]: starting database 'comptage_cookie_telerama', up min/avg/max: 28m/4w/10w, crash average: 1.00 1.00 1.00 (247-6=241) 2016-04-08 14:28:34 MSG comptage_cookie_telerama[16354]: arguments: /usr/bin/mserver5 --dbpath=/var/lib/monetdb/comptage_cookie_telerama --set merovingian_uri=mapi:monetdb://groupelemonde-crm:50000/comptage_cookie_telerama --set mapi_open=false --set mapi_port=0 --set mapi_usock=/var/lib/monetdb/comptage_cookie_telerama/.mapi.sock --set monet_vault_key=/var/lib/monetdb/comptage_cookie_telerama/.vaultkey --set gdk_nr_threads=8 --set max_clients=64 --set sql_optimizer=default_pipe --set monet_daemon=yes 2016-04-08 14:28:34 MSG comptage_cookie_telerama[16354]: # MonetDB 5 server v11.21.13 "Jul2015-SP2" 2016-04-08 14:28:34 MSG comptage_cookie_telerama[16354]: # Serving database 'comptage_cookie_telerama', using 8 threads 2016-04-08 14:28:34 MSG comptage_cookie_telerama[16354]: # Compiled for x86_64-pc-linux-gnu/64bit with 64bit OIDs and 128bit integers dynamically linked 2016-04-08 14:28:34 MSG comptage_cookie_telerama[16354]: # Found 64.000 GiB available main-memory. 2016-04-08 14:28:34 MSG comptage_cookie_telerama[16354]: # Copyright (c) 1993-July 2008 CWI. 2016-04-08 14:28:34 MSG comptage_cookie_telerama[16354]: # Copyright (c) August 2008-2015 MonetDB B.V., all rights reserved 2016-04-08 14:28:34 MSG comptage_cookie_telerama[16354]: # Visit http://www.monetdb.org/ for further information 2016-04-08 14:28:34 MSG comptage_cookie_telerama[16354]: # Listening for UNIX domain connection requests on mapi:monetdb:///var/lib/monetdb/comptage_cookie_telerama/.mapi.sock 2016-04-08 14:28:34 MSG comptage_cookie_telerama[16354]: # Start processing logs sql/sql_logs version 52200 2016-04-08 14:28:34 MSG merovingian[13582]: database 'comptage_cookie_telerama' (16354) has exited with exit status 1 2016-04-08 14:28:39 ERR merovingian[13582]: client error: database 'comptage_cookie_telerama' appears to shut itself down after starting, check monetdbd's logfile for possible hints # cat .gdk_lock USR=107 PID=16354 TIME=Fri Apr 8 14:28:34 2016 @ LOGON USR=107 PID=16354 TIME=Fri Apr 8 14:28:34 2016 @ !FATAL: Logger_new: BBPrename to sql_snapshots_bid failed USR=107 PID=16354 TIME=Fri Apr 8 14:28:34 2016 @ LOGOFF do you have an idea ? Thank, Pierre -- 1G6 52 route de bischwiller 67300 Schiltigheim Société de Services et de Formations en Logiciels Libres http://1g6.biz Tél : 06 64 63 70 35 // 09 54 38 70 90

1 0

any ideas why CPU usage would never go over 25%?
by Anthony Damico 08 Apr '16

08 Apr '16

hi, we have a powerful windows server with 200GB RAM and 40 cores. i've been using it for a few weeks now, and mserver.exe never seems to use more than 10 of the cores. mserver does say "using 40 threads" so they're all available is it just rare for any SQL query to use more than 10 cores at once, or is it possible i've misconfigured something? thanks

1 0

Function does not complete
by Shmagi Kavtaradze 07 Apr '16

07 Apr '16

When I run the own written function it runs and does no exit. I was waiting for 5-6 hours, checked dstats, no disk writes, only 1 core works and no memory usage. It is like stuck and can not exit. It happens when I use function separately for each variable, without callingm for example: while( *a ){ countA += *a & 1; *a >>= 1; } while( *b ){ countB += *b & 1; *b >>= 1; } while( c ){ countC += c & 1; c >>= 1; } but when I call functions like this monetdb works fine: int countSetBits1(unsigned int n){ unsigned int count = 0; while(n){ count += n & 1; n >>= 1; } return count; } countA = countSetBits1(*a); countB = countSetBits1(*b); countC = countSetBits1(c); I am not strong in C or monetdb internals, but in Netbeans both codes work well. The entire code : str UDFbitanddec(flt *ret, lng *a, lng *b) { int c = 0; int countA = 0; int countB = 0; int countC = 0; int maxCount = 0; unsigned int iA = *a; unsigned int iB = *b; c = iA & iB; while( iA ){ countA += iA & 1; iA >>= 1; } while( iB ){ countB += iB & 1; iB >>= 1; } while( c ){ countC += c & 1; c >>= 1; } if (countA >= countB) { maxCount = countA; } else { maxCount = countB; } *ret = (float)countC /maxCount; return MAL_SUCCEED; }

2 1

Exception Error
by Erie, Rob 07 Apr '16

07 Apr '16

I am in the process of evaluating databases for analysis at my organization. I have installed MonetDB and loaded some data. I was attempting to compare results both in performance and accuracy between MySQL and MonetDB. I have been receiving Overflow in Calculation errors from MonetDB. I am a total novice with MonetDB. Here is the information about my system: Red Hat Enterprise Linux Server release 7.2 (Maipo) (Linux 3.10.0-327.el7.x86_64 #1 SMP Thu Oct 29 17:29:29 EDT 2015 x86_64 x86_64 x86_64 GNU/Linux) MySQL Server version: 5.7.11-log MySQL Community Server Java version "1.8.0_74"; Java(TM) SE Runtime Environment (build 1.8.0_74-b02); Java HotSpot(TM) 64-Bit Server VM (build 25.74-b02, mixed mode). Apache Tomcat 7.0.54 Welcome to mclient, the MonetDB/SQL interactive terminal (Jul2015-SP3) Database: MonetDB v11.21.17 (Jul2015-SP3), 'mapi:monetdb://ip-10-153-0-5.ec2.internal:50000/set_mktng' Issue Description: I would like to know the correlation between two variables group by a variable. SQL: select milageRange, sys.corr(numberOfTheseParts,avgHomeValue) FROM aPartsByZipRange INNER JOIN avgHomeValueZipPeriod ON zipCode = zip where numberOfTheseParts <> 0 and avgHomeValue <>0 group by milageRange; Error: TypeException:user.s78_2[44]:'aggr.subcorr' undefined in: X_75:bat[:any,:int] := aggr.subcorr(X_72:bat[:oid,:int],X_74:bat[:oid,:int],X_67:bat[:oid,:oid],r1_67:bat[:oid,:oid],true:bit); program contains errors -- -------------------------------------------------- This e-mail transmission contains information intended only for the use of the recipient(s) named above. Further, it contains information that may be privileged and confidential. If you are not the intended recipient, you are hereby notified that any dissemination, distribution, or copying of this message (including any attachments) is strictly prohibited. If you have received this e-mail in error, please notify the sender by reply e-mail and then delete this message from your mail system. Thank you for your compliance.

3 4

Question
by Сергей 07 Apr '16

07 Apr '16

Does anyone create replication with MonetDB? I've tried many times but still cannot run MonetDB in master-slave mode(replication). I have such errors when start slave server: !mvc_init: unable to create system tables !SQLException:SQLinit:Catalogue initialization failed I've done all that written in https://www.monetdb.org/Documentation/Cookbooks/SQLrecipes/TransactionRepli… but it does not work. Does anyone can help me? Is transaction-replication included to July2015SP3 release? Or I must build MonetDB from sources? Thanks.

1 0

Create R function that can be applied to an entire table
by Willem Ligtenberg 07 Apr '16

07 Apr '16

Hi, I guess I am pushing the user defined R functions to their limits. I know want to implement some batch correcting in R and then apply that to an entire table. I have written R code, that will generate a function definition that has matching parameters. And then I want to call the function as follows: CREATE TABLE data_bc AS SELECT * FROM batch_correction( (SELECT * FROM data) ) WITH DATA; I then get the following error: Error in .local(conn, statement, ...) : Unable to execute statement 'CREATE TABLE hcs_batch AS SELECT * FROM batch_correction( (SELECT * FROM hcs_norm) ) WITH DATA;'. Server says '!Command too large' Looking at the source, this error is raised in rapi.c on either line 467 or 473. After editing the source to figure out which of the lines was triggered, I figured out is was on line: 467 it is triggered, so the following code raises the issue that the command is too large: pos = 0; for (i = pci->retc + 2; i < pci->argc && pos < sizeof(argnames); i++) { pos += snprintf(argnames + pos, sizeof(argnames) - pos, "%s%s", args[i], i < pci->argc - 1 ? ", " : ""); } if (pos >= sizeof(argnames)) { msg = createException(MAL, "rapi.eval", "Command too large1"); goto wrapup; } (yes, I added the 1 to large, to make it unique) If this is not a bug, any help in getting my code to work is greatly appreciated. Kind regards, Willem

2 3

Disabling do_sort() does not work.
by Xu,Wenjian 06 Apr '16

06 Apr '16

Hi, I would like to replace MonetDB' sorting algorithms (timsort/quicksort) with my own. As the first step, I commented all content of function *do_sort()* in gdk_batop.c to disable the sorting functionality. Then I re-build the monetdb (with no error) and type a SQL statement with ORDER-BY clause in mclient interface: "select o_orderkey, o_custkey, o_orderstatus from orders order by o_custkey limit 10;" I was expecting to see the dis-ordered result. However, it turns out that the result is still ordered by o_custkey. I am confused about the result. Did I miss something? Any help would be great. Best regards, John

1 0

accessing intermediate optimizer results.
by Xu,Wenjian 01 Apr '16

01 Apr '16

Hi all, I would like to see the intermediate result (i.e., MAL instructions) after each optimizer step in a optimizer pipeline. So I searched Monetdb's documents and found the following sentence in https://www.monetdb.org/Documentation/Cookbooks/SQLrecipes/OptimizerPipelin… : "Alternatively, the SQL DEBUG statement modifier in combination with the 'o' command provides access to the intermediate optimizer results." I cannot figure out what this sentence means. What I know about SQL DEBUG statement is to use "debug <SQL statement>" to enter the debug mode and iterate through each (final) MAL instruction one by one. How could I use 'o' command to access intermediate optimizer results? I am using MonetDB 11.21.17 (JUL2015 SP3) and my environment is Ubuntu14.04. Thank you very much. Best regards, XU Wenjian

2 4

No previous prototype for function
by Shmagi Kavtaradze 31 Mar '16

31 Mar '16

I use two versions of Monetdb. 1 is PythonUDF and another Monetdb 11.21.13. Function "bitand" is compiled and used well in Monetdb, but when I try to compile PythonUDF it gives me errors: udf.c:14:1: error: no previous prototype for 'UDFbitand' [-Werror=missing-prototypes] UDFbitand(flt *ret, lng*a, lng *b) ^ cc1: all warnings being treated as errors Makefile:876: recipe for target 'lib_udf_la-udf.lo' failed any ideas? thanks in advance.

4 5

no sorted column?
by Roberto Cornacchia 31 Mar '16

31 Mar '16

Before filing a bug report, I'd like to ask here whether this is intentional/expected. sql>create table t(a int); operation successful (0.650ms) sql>insert into t values (1),(0),(3),(2); 4 affected rows (0.638ms) sql> sql>create table sorted_t as select a from t order by a with data; operation successful (0.807ms) sql> sql>select table,column,sorted from sys.storage() where "table"='sorted_t'; +----------+--------+--------+ | table | column | sorted | +==========+========+========+ | sorted_t | a | false | +----------+--------+--------+ I am pretty sure it use to be that the sorting used in a "CREATE TABLE AS ... WITH DATA" would result in the tsorted property to be true for the (first) sorted column. Now this isn't the case. Is that a bug or a feature? Roberto

4 6

Possible serious Memory leak?
by Vijay Krishna 31 Mar '16

31 Mar '16

Hi, Today, we encountered this serious memory issue with MonetDB. *Data : *10 tables - each with ~600 columns - all tables are EMPTY (No rows). *Query : *One query that involves with 25 joins across all the 10 tables. *MonetDB version :* July2015-SP3 The above query is ran on the same MonetDB version with the same table structure with all tables empty on two different machines as follows. *Scenario 1 - Query ran on Macbook Pro * - RAM - 8 GB (with other processes like Chrome/Eclipse running in parallel) - Free disk space - ~100 GB - MonetDB July2015-SP3 downloaded as installable The query is ran. *Expected result* - return an empty set in a few seconds. *Actual result* - returns an empty set in ~5 minutes RAM - 8 GB utilised completely. Swap space used from disk - 10 GB This swap space is cleared after sometime automatically (around 5 mins) *Scenario 2 - With a CentOS virtual machine* - RAM - 128 GB - Free disk space - ~650 GB - MonetDB July2015-SP3 compiled from source code The query is ran. *Expected result* - return an empty set in a few seconds. *Actual result* - the query rebooted this high end CentOS machine. RAM - 128 GB utilised completely. After reboot, the swap memory was freed. I am puzzled with the following questions. 1. Why should a query that runs on 10 EMPTY tables require memory in GBs? 2. How did this query manage to run on a Mac with just 8 GB RAM, but crashed a machine with 128 GB RAM? Is this a known memory leak issue? Any help much appreciated. Thanks in advance. Regards, Vijay.

3 3

Find all running processes?
by Vijay Krishna 31 Mar '16

31 Mar '16

How to find all running processes and their statuses in MonetDB?? I found this... https://www.monetdb.org/Documentation/Cookbooks/SQLrecipes/monitor But I couldn't get this work... I ran a query (which took some 20 seconds to complete) in a window and connected the same database in another terminal and ran "select * from sys.queue" (several times during those 20 seconds) but I could get only this... sql>select * from sys.queue; +-------+---------+----------------------------+----------------------------+----------+---------+------------+--------------------------+ | qtag | user | started | estimate | progress | status | tag | query | +=======+=========+============================+============================+==========+=========+============+==========================+ | 27339 | monetdb | 2015-08-13 05:35:40.000000 | null | null | running | 12532134@0 | select * from sys.queue; | +-------+---------+----------------------------+----------------------------+----------+---------+------------+--------------------------+ I am using Oct-2014 version of monetdb on CentOS... Is there a way to find all running processes and their statuses in MonetDB?? Thanks & Regards, Vijayakrishna.P. Mobile : (+91) 9500402305.

2 3

Speeding SELECT for a small number of fields OVER large number of record in Perl DBD::monetdb
by Puthick Hok 31 Mar '16

31 Mar '16

Hi, I'm using Perl DBD::monetdb from MonetDB source for close to a year now. Recently, I discovered using SQL SELECT for one or two fields for a large number of records, for example, 100000 records is too slow compared to how faster MonetDB engine is. The same SQL SELECT is way faster in MySQL due to C implementation of DBD::mysql. MonetDB connector in Python is still way faster than Perl DBD::monetdb because it allows user to tell the connector the maximum number of records the user needs and the connector uses Xexport command straightaway and only once. In Perl, DBD::monetdb calls Mapi.pm via getReply which issues Xexport command many times depending on the number of records in the SELECT statement. Each time, it retrieves only 100 records. For 100000 records, it needs to call Xexport 1000 times. I did my own patch to make getReply call Xexport only once a the rest of the records after the initial request. With the patch, the time it takes to select 2 fields for 101024 records drops from more 24 seconds to under 4 seconds. My patch is: <pre> sub getReply { my ($self)= @_; if ($self->{active} == 0) { return $self->getBlock(); } elsif ($self->{next} < $self->{replysize} + $self->{skip}) { return $self->getRow(); } elsif (${self}->{offset} + $self->{replysize} < $self->{count}) { # get next slice my $rs = $self->{replysize}; my $offset = $self->{offset} + $rs; my $therest = $self->{count} - $self->{replysize}; $self->putblock("Xexport $self->{id} $offset $therest"); # FAST #$self->putblock("Xexport $self->{id} $offset $rs"); # SLOW return $self->getBlock(); } else { # close large results, but only send on next query if ($self->{id} > 0 && $self->{count} != $self->{replysize}) { push @{$self->{piggyback}}, "Xclose $self->{id}"; $self->{skip_in}++; } $self->{active} = 0; } return $self->{active}; } </pre> I hope this could help other people. Regards, Puthick

1 0

Re: Temporary duplicated rows
by Alex Manaev 30 Mar '16

30 Mar '16

Effect is reproduced with console on the fresh database. Here is real time console log. Can anybody explain what happens? sql>select id, status from morgoth_reportquery where status <> 4 and id = 6880; +------+--------+ | id | status | +======+========+ | 6880 | 4 | | 6880 | 4 | | 6880 | 4 | +------+--------+ 3 tuples (2.551ms) <--- result of previous experiments sql>select id, status from morgoth_reportquery where id = 6880; +------+--------+ | id | status | +======+========+ | 6880 | 4 | +------+--------+ 1 tuple (2.746ms) sql>update morgoth_reportquery set status = 2 where id = 6880; 1 affected row (3.306ms) sql>select id, status from morgoth_reportquery where id = 6880; +------+--------+ | id | status | +======+========+ | 6880 | 4 | +------+--------+ 1 tuple (2.216ms) sql>select id, status from morgoth_reportquery where status <> 4 and id = 6880; +------+--------+ | id | status | +======+========+ | 6880 | 4 | | 6880 | 4 | | 6880 | 4 | | 6880 | 4 | +------+--------+ 4 tuples (2.634ms) sql>select id, status from morgoth_reportquery where status = 4 and id = 6880; +----+--------+ | id | status | +====+========+ +----+--------+ 0 tuples (2.337ms) sql>select id, status from morgoth_reportquery where status = 1 and id = 6880; +----+--------+ | id | status | +====+========+ +----+--------+ 0 tuples (3.294ms) sql>select id, status from morgoth_reportquery where status = 4 and id = 6880; +----+--------+ | id | status | +====+========+ +----+--------+ 0 tuples (2.416ms) sql>select id, status from morgoth_reportquery where id = 6880; +------+--------+ | id | status | +======+========+ | 6880 | 2 | +------+--------+ 1 tuple (2.186ms) sql>select id, status from morgoth_reportquery where status <> 4 and id = 6880; +------+--------+ | id | status | +======+========+ | 6880 | 2 | +------+--------+ 1 tuple (4.145ms) sql> Alexey Manaev, Head of software development CJSC "RUTUBE" On 30.03.2016 17:43, users-list-request(a)monetdb.org wrote: > Send users-list mailing list submissions to > users-list(a)monetdb.org > > To subscribe or unsubscribe via the World Wide Web, visit > https://www.monetdb.org/mailman/listinfo/users-list > or, via email, send a message with subject or body 'help' to > users-list-request(a)monetdb.org > > You can reach the person managing the list at > users-list-owner(a)monetdb.org > > When replying, please edit your Subject line so it is more specific > than "Re: Contents of users-list digest..." > > > Today's Topics: > > 1. Temporary duplicated rows (Alex Manaev) > 2. UDF returning 0 as a result (Shmagi Kavtaradze) > 3. Re: UDF returning 0 as a result (Mark Raasveldt) > 4. Re: UDF returning 0 as a result (Sjoerd Mullender) > 5. Re: Find all running processes? (Pierre-Adrien Coustillas) > > > ---------------------------------------------------------------------- > > Message: 1 > Date: Wed, 30 Mar 2016 14:18:16 +0300 > From: Alex Manaev <amanaev(a)rutube.ru> > To: <users-list(a)monetdb.org> > Subject: Temporary duplicated rows > Message-ID: <56FBB5F8.7060309(a)rutube.ru> > Content-Type: text/plain; charset="utf-8"; format=flowed > > Hi, > > I have been faced with strange behavior. > > Sometimes after update that changes field "status", a select query > returns multiple identical rows. > > > sql>select * from morgoth_reportquery where not status = 2 and id = > 9472 order by id desc limit 5; > +------+------+-----------------------------+----------------------------+----------------------------+------+-----------------------------+-----------------------------+------+------+------+------+ > | id | repo | name | created_date > | ready_date | stat | hash | > params | user | appr | temp | noti | > : : rt_i : : > : : us : > : : _id : oved : late : fica : > : : d : : > : : : > : : : : _id : tion : > : : : : > : : : > : : : : : _ema : > : : : : > : : : > : : : : : ils : > +======+======+=============================+============================+============================+======+=============================+=============================+======+======+======+======+ > | 9472 | 13 | ??????????? ??????: ??????? | 2015-12-01 12:28:59.000000 > | null | 1 | 5f34b8459f4834867a2621f7d6b | - > !!python/tuple [from_date | 1 | 0 | 8 | | > : : : ???????? 2016-02-01 - 2016- : > : : : 65c8a : , > 2016-02-01] : : : : : > : : : 02-11 : > : : : : - > !!python/tuple [to_date, : : : : : > : : : : > : : : : > 2016-02-11] : : : : : > : : : : > : : : : - > !!python/tuple [update_me : : : : : > : : : : > : : : : diainfo, > 0] : : : : : > : : : : > : : : : - > !!python/tuple : : : : : > : : : : > : : : : - > agreements... > : : : : > | 9472 | 13 | ??????????? ??????: ??????? | 2015-12-01 12:28:59.000000 > | null | 1 | 5f34b8459f4834867a2621f7d6b | - > !!python/tuple [from_date | 1 | 0 | 8 | | > : : : ???????? 2016-02-01 - 2016- : > : : : 65c8a : , > 2016-02-01] : : : : : > : : : 02-11 : > : : : : - > !!python/tuple [to_date, : : : : : > : : : : > : : : : > 2016-02-11] : : : : : > : : : : > : : : : - > !!python/tuple [update_me : : : : : > : : : : > : : : : diainfo, > 0] : : : : : > : : : : > : : : : - > !!python/tuple : : : : : > : : : : > : : : : - > agreements... > : : : : > +------+------+-----------------------------+----------------------------+----------------------------+------+-----------------------------+-----------------------------+------+------+------+------+ > 2 tuples (8.446ms) !2 fields truncated! > > > In same time another queries return only one row (normal state) > > sql>select * from morgoth_reportquery where status = 1 and id = 9472 > order by id desc limit 5; > +------+------+-----------------------------+----------------------------+----------------------------+------+-----------------------------+-----------------------------+------+------+------+------+ > | id | repo | name | created_date > | ready_date | stat | hash | > params | user | appr | temp | noti | > : : rt_i : : > : : us : > : : _id : oved : late : fica : > : : d : : > : : : > : : : : _id : tion : > : : : : > : : : > : : : : : _ema : > : : : : > : : : > : : : : : ils : > +======+======+=============================+============================+============================+======+=============================+=============================+======+======+======+======+ > | 9472 | 13 | ??????????? ??????: ??????? | 2015-12-01 12:28:59.000000 > | null | 1 | 5f34b8459f4834867a2621f7d6b | - > !!python/tuple [from_date | 1 | 0 | 8 | | > : : : ???????? 2016-02-01 - 2016- : > : : : 65c8a : , > 2016-02-01] : : : : : > : : : 02-11 : > : : : : - > !!python/tuple [to_date, : : : : : > : : : : > : : : : > 2016-02-11] : : : : : > : : : : > : : : : - > !!python/tuple [update_me : : : : : > : : : : > : : : : diainfo, > 0] : : : : : > : : : : > : : : : - > !!python/tuple : : : : : > : : : : > : : : : - > agreements... > : : : : > +------+------+-----------------------------+----------------------------+----------------------------+------+-----------------------------+-----------------------------+------+------+------+------+ > 1 tuple (6.474ms) !1 field truncated! > > > Another queries without filter by field `status` give a normal result > (one row). > > Effect is eliminated in several minutes after update by itself. > > > DBMS : MonetDB v11.21.17 (Jul2015-SP3) > OS: CentOS 2.6.32-573.12.1.el6.x86_64 #1 SMP Tue Dec 15 21:19:08 UTC > 2015 x86_64 x86_64 x86_64 GNU/Linux > > Issue was reproduced for the first time with version SP-2. > > Database was created on SP-1. Issue had not appeared with this version. > > > >

1 0

Can no connect to Monetdb database
by Shmagi Kavtaradze 30 Mar '16

30 Mar '16

I compiled Monetdb-python version over regular Monetdb source compilation and after it I receive error: 2016-03-30 20:14:11 MSG merovingian[23154]: starting database 'db', up min/avg/max: 0s/0s/0s, crash average: 0.00 0.00 0.00 (0-0=0) 2016-03-30 20:14:11 MSG db[23176]: arguments: /usr/local/bin/mserver5 --dbpath=/home/shmagi/db/db --set merovingian_uri=mapi:monetdb://infdbserv02:50000/db --set mapi_open=false --set mapi_port=0 --set mapi_usock=/home/shmagi/db/db/.mapi.sock --set monet_vault_key=/home/shmagi/db/db/.vaultkey --set gdk_nr_threads=24 --set max_clients=64 --set sql_optimizer=default_pipe --set monet_daemon=yes 2016-03-30 20:14:11 MSG merovingian[23154]: database 'db' (23176) has crashed (dumped core) 2016-03-30 20:14:16 ERR merovingian[23154]: client error: database 'db' appears to shut itself down after starting, check monetdbd's logfile for possible hints I tried ro ./configure, make clean, make , make install from pure monetdb source (without python), but still receive these errors. I tried to use prefix to install in another directory, tried to delete manually all files I found, but still same error. Any ideas how to fix this problem?

1 0

UDF returning 0 as a result
by Shmagi Kavtaradze 30 Mar '16

30 Mar '16

I wrote a function bitand. When I do "select bitand(1111,1010);" it gives "0" instead of "0.5". My function: --UDF.c-- str UDFbitand(long long int a, long long b) { long long int weirdAnd(unsigned int a, unsigned int b) { long long int result = 0; int coef = 1; while (a || b) { result += ((a % 10) && (b % 10)) * coef; coef *= 10; a /= 10; b /= 10; } return result; } long long int temp = weirdAnd(a, b); long long int temp2 = weirdAnd(a, b); int length; int count; while (temp != 0) { temp/=10; length++; } while (temp2 != 0) { if (temp2 % 10 == 1) count++; temp2 /= 10; } float final = count/(float)length; return MAL_SUCCEED; } --UDF.mal-- command bitand(a:lng,b:lng):flt address UDFbitand comment "bitand"; --80_UDF.sql-- create function bitand(a BIGINT,b BIGINT) returns REAL external name udf.bitand; When I test my C code in netbeans it works as supposed. Any ideas? Thanks in advance.

3 3

Temporary duplicated rows
by Alex Manaev 30 Mar '16

30 Mar '16

Hi, I have been faced with strange behavior. Sometimes after update that changes field "status", a select query returns multiple identical rows. sql>select * from morgoth_reportquery where not status = 2 and id = 9472 order by id desc limit 5; +------+------+-----------------------------+----------------------------+----------------------------+------+-----------------------------+-----------------------------+------+------+------+------+ | id | repo | name | created_date | ready_date | stat | hash | params | user | appr | temp | noti | : : rt_i : : : : us : : : _id : oved : late : fica : : : d : : : : : : : : : _id : tion : : : : : : : : : : : : : _ema : : : : : : : : : : : : : ils : +======+======+=============================+============================+============================+======+=============================+=============================+======+======+======+======+ | 9472 | 13 | Ежемесячные отчеты: Медиате | 2015-12-01 12:28:59.000000 | null | 1 | 5f34b8459f4834867a2621f7d6b | - !!python/tuple [from_date | 1 | 0 | 8 | | : : : хнологии 2016-02-01 - 2016- : : : : 65c8a : , 2016-02-01] : : : : : : : : 02-11 : : : : : - !!python/tuple [to_date, : : : : : : : : : : : : : 2016-02-11] : : : : : : : : : : : : : - !!python/tuple [update_me : : : : : : : : : : : : : diainfo, 0] : : : : : : : : : : : : : - !!python/tuple : : : : : : : : : : : : : - agreements... > : : : : | 9472 | 13 | Ежемесячные отчеты: Медиате | 2015-12-01 12:28:59.000000 | null | 1 | 5f34b8459f4834867a2621f7d6b | - !!python/tuple [from_date | 1 | 0 | 8 | | : : : хнологии 2016-02-01 - 2016- : : : : 65c8a : , 2016-02-01] : : : : : : : : 02-11 : : : : : - !!python/tuple [to_date, : : : : : : : : : : : : : 2016-02-11] : : : : : : : : : : : : : - !!python/tuple [update_me : : : : : : : : : : : : : diainfo, 0] : : : : : : : : : : : : : - !!python/tuple : : : : : : : : : : : : : - agreements... > : : : : +------+------+-----------------------------+----------------------------+----------------------------+------+-----------------------------+-----------------------------+------+------+------+------+ 2 tuples (8.446ms) !2 fields truncated! In same time another queries return only one row (normal state) sql>select * from morgoth_reportquery where status = 1 and id = 9472 order by id desc limit 5; +------+------+-----------------------------+----------------------------+----------------------------+------+-----------------------------+-----------------------------+------+------+------+------+ | id | repo | name | created_date | ready_date | stat | hash | params | user | appr | temp | noti | : : rt_i : : : : us : : : _id : oved : late : fica : : : d : : : : : : : : : _id : tion : : : : : : : : : : : : : _ema : : : : : : : : : : : : : ils : +======+======+=============================+============================+============================+======+=============================+=============================+======+======+======+======+ | 9472 | 13 | Ежемесячные отчеты: Медиате | 2015-12-01 12:28:59.000000 | null | 1 | 5f34b8459f4834867a2621f7d6b | - !!python/tuple [from_date | 1 | 0 | 8 | | : : : хнологии 2016-02-01 - 2016- : : : : 65c8a : , 2016-02-01] : : : : : : : : 02-11 : : : : : - !!python/tuple [to_date, : : : : : : : : : : : : : 2016-02-11] : : : : : : : : : : : : : - !!python/tuple [update_me : : : : : : : : : : : : : diainfo, 0] : : : : : : : : : : : : : - !!python/tuple : : : : : : : : : : : : : - agreements... > : : : : +------+------+-----------------------------+----------------------------+----------------------------+------+-----------------------------+-----------------------------+------+------+------+------+ 1 tuple (6.474ms) !1 field truncated! Another queries without filter by field `status` give a normal result (one row). Effect is eliminated in several minutes after update by itself. DBMS : MonetDB v11.21.17 (Jul2015-SP3) OS: CentOS 2.6.32-573.12.1.el6.x86_64 #1 SMP Tue Dec 15 21:19:08 UTC 2015 x86_64 x86_64 x86_64 GNU/Linux Issue was reproduced for the first time with version SP-2. Database was created on SP-1. Issue had not appeared with this version. -- Alexey Manaev, Head of software development CJSC "RUTUBE"

1 0

Controlling --dbextras Using MonetDB(D)
by Vincent Sheffer 30 Mar '16

30 Mar '16

mserver5 has an argument called —dbextras. I prefer to control mserver5 using monetdbd, but I don't see any way of specifying this option in either monetdb or monetdbd. Is there, say, a property that can be set in monetdbd or monetdb. The latter probably makes more sense, as I think I would want to control it at the DB level not the dbfarm level. Thanks, Vince The information transmitted, including any attachments, is intended only for the individual or entity to which it is addressed, and may contain confidential and/or privileged information. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by individuals or entities other than the intended recipient is prohibited, and all liability arising therefrom is disclaimed. If you have received this communication in error, please delete the information from any computer and notify the sender.

1 0

simultaneously-created tables not generating
by Anthony Damico 29 Mar '16

29 Mar '16

i have three clients connected to one mserver. they are simultaneously running these three commands, which share table `z` but not the x or y tables-- CREATE TABLE y1 AS SELECT x1.* FROM x1 LEFT JOIN SELECT col FROM Z ON x1.id = z.id WITH DATA ; CREATE TABLE y2 AS SELECT x2.* FROM x2 LEFT JOIN SELECT col FROM Z ON x2.id = z.id WITH DATA ; CREATE TABLE y3 AS SELECT x3.* FROM x3 LEFT JOIN SELECT col FROM Z ON x3.id = z.id WITH DATA ; when all three commands complete, tables y2 and y3 do not exist if i run the commands sequentially, everything works. am i making a mistake in how sql transactions should behave, or is this potentially a bug that i should figure out how to reproduce for bugzilla? thanks!

2 4

Ways to avoid OOMKiller problem?
by Vijay Krishna 29 Mar '16

29 Mar '16

Hi, We came across this - https://www.monetdb.org/Documentation/OOMkiller and we were thinking on some ways to overcome this situation. 1. Is there any configuration in MonetDB to restrict the maximum RAM usage of the mserver process? 2. Is there any configuration in MonetDB to restrict the maximum RAM used by a single thread? 3. Is there any suggested solution for the OOMKiller problem? If you have explored anything else on this, please share your experience with MonetDB server Memory management. Any help much appreciated. Thanks in advance. Regards, Vijay.

3 3

speed cost of stacking tables that do not need to be stacked
by Anthony Damico 27 Mar '16

27 Mar '16

just some UNION ALL speed tests that others might find useful.. i have one big table for each year of data. the year-tables can be analyzed separately - because all of my desired results are going to be within-year averages and counts. since each table has a single year of data, these two statements give equivalent results: query #1: SELECT * FROM ( ( SELECT yr , AVG( value ) FROM y1 GROUP BY yr ) UNION ALL ( SELECT yr , AVG( value ) FROM y2 GROUP BY yr ) ) AS zzz ; query #2: SELECT yr , AVG( value ) FROM ( ( SELECT yr , value FROM y1 ) UNION ALL ( SELECT yr , value FROM y2 ) ) AS zzz GROUP BY yr ; query #1: 290 seconds query #2: 390 seconds query #1: 22 seconds query #2: 87 seconds fwiw- this would take about 24 hours in anything other than monetdb ;)

1 0

database has crashed (dumped core)
by Shmagi Kavtaradze 25 Mar '16

25 Mar '16

I recomplied monetdb several times (make & make install). Everything was fine. Then I downloaded monetdb_python. It was not able to install and I decided to recompile and install again regular Monetdb, but when I "mclient -u monetdb -d voc" I get error in the log file: 2016-03-25 17:26:18 MSG merovingian[49368]: starting database 'voc', up min/avg/max: 0s/0s/0s, crash average: 0.00 0.00 0.00 (0-0=0) 2016-03-25 17:26:18 MSG voc[49379]: arguments: /usr/local/bin/mserver5 --dbpath=/home/shmagi/db/voc --set merovingian_uri=mapi:monetdb://infdbserv02:50000/voc --set mapi_open=false --set mapi_port=0 --set mapi_usock=/home/shmagi/db/voc/.mapi.sock --set monet_vault_key=/home/shmagi/db/voc/.vaultkey --set gdk_nr_threads=24 --set max_clients=64 --set sql_optimizer=default_pipe --set monet_daemon=yes 2016-03-25 17:26:18 MSG merovingian[49368]: database 'voc' (49379) has crashed (dumped core) 2016-03-25 17:26:18 ERR merovingian[49368]: client error: database 'voc' appears to shut itself down after starting, check monetdbd's logfile for possible hints 2016-03-25 17:26:18 MSG merovingian[49368]: starting database 'voc', up min/avg/max: 0s/0s/0s, crash average: 0.00 0.00 0.00 (0-0=0) 2016-03-25 17:26:19 MSG voc[49382]: arguments: /usr/local/bin/mserver5 --dbpath=/home/shmagi/db/voc --set merovingian_uri=mapi:monetdb://infdbserv02:50000/voc --set mapi_open=false --set mapi_port=0 --set mapi_usock=/home/shmagi/db/voc/.mapi.sock --set monet_vault_key=/home/shmagi/db/voc/.vaultkey --set gdk_nr_threads=24 --set max_clients=64 --set sql_optimizer=default_pipe --set monet_daemon=yes 2016-03-25 17:26:19 MSG merovingian[49368]: database 'voc' (49382) has crashed (dumped core) 2016-03-25 17:26:23 ERR merovingian[49368]: client error: database 'voc' appears to shut itself down after starting, check monetdbd's logfile for possible hints. Any ideas how to fix or delete all files of Monetdb to install from the scratch?

1 0

Delete monetdb
by Shmagi Kavtaradze 25 Mar '16

25 Mar '16

Sorry for a stupid questions, but how can I completely delete Monetdb from ubuntu, if I compiled it myself?

4 4

could this be multithreaded? happens after any large import after server stop & restart
by Anthony Damico 25 Mar '16

25 Mar '16

it is dreadfully slow on windows. thanks all # MonetDB 5 server v11.21.13 "Jul2015-SP2" # Serving database 'marketscan', using 40 threads # Compiled for x86_64-pc-winnt/64bit with 64bit OIDs dynamically linked # Found 223.875 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 connection requests on mapi:monetdb://127.0.0.1:50100/ # Start processing logs sql/sql_logs version 52200 # Start reading the write-ahead log 'sql_logs\sql\log.14' # still reading write-ahead log "sql_logs\sql\log.14" (0% done) # still reading write-ahead log "sql_logs\sql\log.14" (0% done) # still reading write-ahead log "sql_logs\sql\log.14" (0% done) # still reading write-ahead log "sql_logs\sql\log.14" (0% done) # still reading write-ahead log "sql_logs\sql\log.14" (0% done) # still reading write-ahead log "sql_logs\sql\log.14" (0% done) # still reading write-ahead log "sql_logs\sql\log.14" (0% done) # still reading write-ahead log "sql_logs\sql\log.14" (0% done) # still reading write-ahead log "sql_logs\sql\log.14" (0% done) # still reading write-ahead log "sql_logs\sql\log.14" (0% done) # still reading write-ahead log "sql_logs\sql\log.14" (0% done) # still reading write-ahead log "sql_logs\sql\log.14" (1% done) # still reading write-ahead log "sql_logs\sql\log.14" (1% done) # still reading write-ahead log "sql_logs\sql\log.14" (1% done) # still reading write-ahead log "sql_logs\sql\log.14" (1% done) # still reading write-ahead log "sql_logs\sql\log.14" (1% done) # still reading write-ahead log "sql_logs\sql\log.14" (1% done) # still reading write-ahead log "sql_logs\sql\log.14" (2% done) # still reading write-ahead log "sql_logs\sql\log.14" (2% done) # still reading write-ahead log "sql_logs\sql\log.14" (2% done) # still reading write-ahead log "sql_logs\sql\log.14" (2% done) # still reading write-ahead log "sql_logs\sql\log.14" (2% done) # still reading write-ahead log "sql_logs\sql\log.14" (3% done) # still reading write-ahead log "sql_logs\sql\log.14" (3% done) # still reading write-ahead log "sql_logs\sql\log.14" (3% done) # still reading write-ahead log "sql_logs\sql\log.14" (3% done) # still reading write-ahead log "sql_logs\sql\log.14" (3% done) # still reading write-ahead log "sql_logs\sql\log.14" (4% done) # still reading write-ahead log "sql_logs\sql\log.14" (4% done) # still reading write-ahead log "sql_logs\sql\log.14" (4% done) # still reading write-ahead log "sql_logs\sql\log.14" (4% done) # still reading write-ahead log "sql_logs\sql\log.14" (4% done) # still reading write-ahead log "sql_logs\sql\log.14" (4% done) # still reading write-ahead log "sql_logs\sql\log.14" (5% done) # still reading write-ahead log "sql_logs\sql\log.14" (5% done) # still reading write-ahead log "sql_logs\sql\log.14" (5% done) # still reading write-ahead log "sql_logs\sql\log.14" (5% done) # still reading write-ahead log "sql_logs\sql\log.14" (5% done) # still reading write-ahead log "sql_logs\sql\log.14" (6% done) # still reading write-ahead log "sql_logs\sql\log.14" (6% done) # still reading write-ahead log "sql_logs\sql\log.14" (6% done) # still reading write-ahead log "sql_logs\sql\log.14" (6% done) # still reading write-ahead log "sql_logs\sql\log.14" (6% done) # still reading write-ahead log "sql_logs\sql\log.14" (7% done) # still reading write-ahead log "sql_logs\sql\log.14" (7% done) # still reading write-ahead log "sql_logs\sql\log.14" (7% done) # still reading write-ahead log "sql_logs\sql\log.14" (7% done) # still reading write-ahead log "sql_logs\sql\log.14" (7% done) # still reading write-ahead log "sql_logs\sql\log.14" (7% done) # still reading write-ahead log "sql_logs\sql\log.14" (8% done) # still reading write-ahead log "sql_logs\sql\log.14" (8% done) # still reading write-ahead log "sql_logs\sql\log.14" (8% done) # still reading write-ahead log "sql_logs\sql\log.14" (8% done) # still reading write-ahead log "sql_logs\sql\log.14" (8% done) # still reading write-ahead log "sql_logs\sql\log.14" (8% done) # still reading write-ahead log "sql_logs\sql\log.14" (8% done) # still reading write-ahead log "sql_logs\sql\log.14" (8% done) # still reading write-ahead log "sql_logs\sql\log.14" (9% done) # still reading write-ahead log "sql_logs\sql\log.14" (9% done) # still reading write-ahead log "sql_logs\sql\log.14" (9% done) # still reading write-ahead log "sql_logs\sql\log.14" (9% done) # still reading write-ahead log "sql_logs\sql\log.14" (9% done) # still reading write-ahead log "sql_logs\sql\log.14" (10% done) # still reading write-ahead log "sql_logs\sql\log.14" (10% done) # still reading write-ahead log "sql_logs\sql\log.14" (10% done) # still reading write-ahead log "sql_logs\sql\log.14" (10% done) # still reading write-ahead log "sql_logs\sql\log.14" (10% done) # still reading write-ahead log "sql_logs\sql\log.14" (11% done) # still reading write-ahead log "sql_logs\sql\log.14" (11% done) # still reading write-ahead log "sql_logs\sql\log.14" (11% done) # still reading write-ahead log "sql_logs\sql\log.14" (11% done) # still reading write-ahead log "sql_logs\sql\log.14" (12% done) # still reading write-ahead log "sql_logs\sql\log.14" (12% done) # still reading write-ahead log "sql_logs\sql\log.14" (12% done) # still reading write-ahead log "sql_logs\sql\log.14" (12% done) # still reading write-ahead log "sql_logs\sql\log.14" (12% done) # still reading write-ahead log "sql_logs\sql\log.14" (13% done) # still reading write-ahead log "sql_logs\sql\log.14" (13% done) # still reading write-ahead log "sql_logs\sql\log.14" (13% done) # still reading write-ahead log "sql_logs\sql\log.14" (13% done) # still reading write-ahead log "sql_logs\sql\log.14" (13% done) # still reading write-ahead log "sql_logs\sql\log.14" (13% done) # still reading write-ahead log "sql_logs\sql\log.14" (14% done) # still reading write-ahead log "sql_logs\sql\log.14" (14% done) # still reading write-ahead log "sql_logs\sql\log.14" (14% done) # still reading write-ahead log "sql_logs\sql\log.14" (14% done) # still reading write-ahead log "sql_logs\sql\log.14" (15% done) # still reading write-ahead log "sql_logs\sql\log.14" (15% done) # still reading write-ahead log "sql_logs\sql\log.14" (15% done) # still reading write-ahead log "sql_logs\sql\log.14" (15% done) # still reading write-ahead log "sql_logs\sql\log.14" (15% done) # still reading write-ahead log "sql_logs\sql\log.14" (15% done) # still reading write-ahead log "sql_logs\sql\log.14" (16% done) # still reading write-ahead log "sql_logs\sql\log.14" (16% done) # still reading write-ahead log "sql_logs\sql\log.14" (16% done) # still reading write-ahead log "sql_logs\sql\log.14" (16% done) # still reading write-ahead log "sql_logs\sql\log.14" (16% done) # still reading write-ahead log "sql_logs\sql\log.14" (17% done) # still reading write-ahead log "sql_logs\sql\log.14" (17% done) # still reading write-ahead log "sql_logs\sql\log.14" (18% done) # still reading write-ahead log "sql_logs\sql\log.14" (18% done) # still reading write-ahead log "sql_logs\sql\log.14" (18% done) # still reading write-ahead log "sql_logs\sql\log.14" (19% done) # still reading write-ahead log "sql_logs\sql\log.14" (19% done) # still reading write-ahead log "sql_logs\sql\log.14" (20% done) # still reading write-ahead log "sql_logs\sql\log.14" (20% done) # still reading write-ahead log "sql_logs\sql\log.14" (20% done) # still reading write-ahead log "sql_logs\sql\log.14" (21% done) # still reading write-ahead log "sql_logs\sql\log.14" (21% done) # still reading write-ahead log "sql_logs\sql\log.14" (22% done) # still reading write-ahead log "sql_logs\sql\log.14" (22% done) # still reading write-ahead log "sql_logs\sql\log.14" (22% done) # still reading write-ahead log "sql_logs\sql\log.14" (23% done) # still reading write-ahead log "sql_logs\sql\log.14" (23% done) # still reading write-ahead log "sql_logs\sql\log.14" (23% done) # still reading write-ahead log "sql_logs\sql\log.14" (24% done) # still reading write-ahead log "sql_logs\sql\log.14" (24% done) # still reading write-ahead log "sql_logs\sql\log.14" (24% done) # still reading write-ahead log "sql_logs\sql\log.14" (24% done) # still reading write-ahead log "sql_logs\sql\log.14" (25% done) # still reading write-ahead log "sql_logs\sql\log.14" (25% done) # still reading write-ahead log "sql_logs\sql\log.14" (25% done) # still reading write-ahead log "sql_logs\sql\log.14" (26% done) # still reading write-ahead log "sql_logs\sql\log.14" (26% done) # still reading write-ahead log "sql_logs\sql\log.14" (27% done) # still reading write-ahead log "sql_logs\sql\log.14" (27% done) # still reading write-ahead log "sql_logs\sql\log.14" (27% done) # still reading write-ahead log "sql_logs\sql\log.14" (28% done) # still reading write-ahead log "sql_logs\sql\log.14" (28% done) # still reading write-ahead log "sql_logs\sql\log.14" (28% done) # still reading write-ahead log "sql_logs\sql\log.14" (28% done) # still reading write-ahead log "sql_logs\sql\log.14" (29% done) # still reading write-ahead log "sql_logs\sql\log.14" (30% done) # still reading write-ahead log "sql_logs\sql\log.14" (30% done) # still reading write-ahead log "sql_logs\sql\log.14" (31% done) # still reading write-ahead log "sql_logs\sql\log.14" (31% done) # still reading write-ahead log "sql_logs\sql\log.14" (31% done) # still reading write-ahead log "sql_logs\sql\log.14" (32% done) # still reading write-ahead log "sql_logs\sql\log.14" (32% done) # still reading write-ahead log "sql_logs\sql\log.14" (32% done) # still reading write-ahead log "sql_logs\sql\log.14" (33% done) # still reading write-ahead log "sql_logs\sql\log.14" (33% done) # still reading write-ahead log "sql_logs\sql\log.14" (34% done) # still reading write-ahead log "sql_logs\sql\log.14" (34% done) # still reading write-ahead log "sql_logs\sql\log.14" (34% done) # still reading write-ahead log "sql_logs\sql\log.14" (35% done) # still reading write-ahead log "sql_logs\sql\log.14" (35% done) # still reading write-ahead log "sql_logs\sql\log.14" (35% done) # still reading write-ahead log "sql_logs\sql\log.14" (35% done) # still reading write-ahead log "sql_logs\sql\log.14" (36% done) # still reading write-ahead log "sql_logs\sql\log.14" (36% done) # still reading write-ahead log "sql_logs\sql\log.14" (36% done) # still reading write-ahead log "sql_logs\sql\log.14" (36% done) # still reading write-ahead log "sql_logs\sql\log.14" (37% done) # still reading write-ahead log "sql_logs\sql\log.14" (37% done) # still reading write-ahead log "sql_logs\sql\log.14" (37% done) # still reading write-ahead log "sql_logs\sql\log.14" (38% done) # still reading write-ahead log "sql_logs\sql\log.14" (39% done) # still reading write-ahead log "sql_logs\sql\log.14" (40% done) # still reading write-ahead log "sql_logs\sql\log.14" (41% done) # still reading write-ahead log "sql_logs\sql\log.14" (42% done) # still reading write-ahead log "sql_logs\sql\log.14" (44% done) # still reading write-ahead log "sql_logs\sql\log.14" (45% done) # still reading write-ahead log "sql_logs\sql\log.14" (46% done) # still reading write-ahead log "sql_logs\sql\log.14" (47% done) # still reading write-ahead log "sql_logs\sql\log.14" (48% done) # still reading write-ahead log "sql_logs\sql\log.14" (49% done) # still reading write-ahead log "sql_logs\sql\log.14" (50% done) # still reading write-ahead log "sql_logs\sql\log.14" (50% done) # still reading write-ahead log "sql_logs\sql\log.14" (51% done) # still reading write-ahead log "sql_logs\sql\log.14" (52% done) # still reading write-ahead log "sql_logs\sql\log.14" (53% done) # still reading write-ahead log "sql_logs\sql\log.14" (54% done) # still reading write-ahead log "sql_logs\sql\log.14" (55% done) # still reading write-ahead log "sql_logs\sql\log.14" (55% done) # still reading write-ahead log "sql_logs\sql\log.14" (56% done) # still reading write-ahead log "sql_logs\sql\log.14" (57% done) # still reading write-ahead log "sql_logs\sql\log.14" (57% done) # still reading write-ahead log "sql_logs\sql\log.14" (58% done) # still reading write-ahead log "sql_logs\sql\log.14" (59% done) # still reading write-ahead log "sql_logs\sql\log.14" (59% done) # still reading write-ahead log "sql_logs\sql\log.14" (60% done) # still reading write-ahead log "sql_logs\sql\log.14" (60% done)

2 2

Announcement: New Jul2015-SP3 Bugfix release of MonetDB suite
by Sjoerd Mullender 25 Mar '16

25 Mar '16

The MonetDB team at CWI/MonetDB BV is pleased to announce the Jul2015-SP3 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-SP3 bugfix release MonetDB5 Server * Fixed potential crash in MAL debugger when accessing BATs by index. Functionality dropped as it is also a security leak. Bug Fixes * 2972: SQL URL functionality contains errors * 3549: bulk string operations very slow * 3881: Server crashes on bulk load * 3890: Window function + group by in subselect, rel2bin_project: Assertion `0' failed * 3891: MonetDB crashes when executing SQL with window function * 3900: null handling in some sql statements is incorrect * 3906: Multi-column 1-N table-function with mitosis produces different column counts * 3908: LEFT JOIN with OR conditions triggers assertion * 3909: Incorrect column name in OR condition of LEFT JOIN crashes mserver * 3910: COPY INTO table (column1, column2) got wrong result * 3912: When table/column names conflicts, data ends in multiple tables! * 3917: Date difference returns month_interval instead of day_interval * 3918: MonetDB.R version 1.0.1 incorrectly constructs the batfile script * 3919: Table conflict when the table name and fields are identical * 3921: Creating a table from a complex query crashes mserver or triggers assertion * 3922: AVG( column ) returns NaN rather than Inf when column contains Inf * 3928: When killing a virtual machine, sql_logs/sql/log is empty * 3930: Wrong typecast on character columns in prepared statements when using Umlaute * 3932: CASE expressions with constants are not evaluated correctly * 3933: replace "exit" by "throw new Exception" * 3937: bad BAT properties with binary copy into and NULL values * 3938: Wrong error message on violating foreign key constraint * 3940: Date calculation and comparison produce wrong result * 3941: Wrong coercion priority * 3948: SQL: select * from sys.sys.table_name; is accepted but should return an error * 3951: extern table_funcs not visible from Windows DLL for extensions like vaults (crashes) * 3952: Stream table gives segfault * 3953: MIN/MAX of a UUID column produces wrong results * 3954: Consolidate table assertion error * 3955: (incorrect) MAL loop instead of manifold triggered by simple change in target list

1 0

Fwd: Error after compiling monetdb
by Shmagi Kavtaradze 24 Mar '16

24 Mar '16

I am trying to write a C User Defined Function. It works in Neatbeans. What I want is, to take value from cells, insert them in text or char array and compare each corresponding array position to each other. As values I will have "1"s and "0s". E.g "101010100011". THe code I wrote in udf.c: /* Comparing bag of words */ char BitAnd(char Cell1[], char Cell2[]) { Cell1[10000]; Cell2[10000]; int counter; float result; int strlen = sizeof(Cell1)/sizeof(Cell1[0])-1; int i; for (i=0; i < strlen; i++) { if (Cell1[i] == Cell2[i]) { counter++; } } result = (float)counter / strlen; } /* MAL wrapper */ char UDFBitAnd(char Cell1[], char Cell2[]) { /* assert calling sanity */ assert(Cell1[] != NULL && Cell2[] != NULL); return MAL_SUCCEED; } In udf.mal I have: command BitAnd(Cell1:chr,Cell2:chr):flt address UDFBitAnd comment "Compare bag of words"; In 80_udf.sql: -- Comparing bag of words create function BitAnd(Cell1[] char, Cell2[] char) returns float external name udf.BitAnd; I compiled Monetdb, but when I try to connect with "monetdb" to db it gives me error. In the log file I found: MSG voc[18857]: !SyntaxException:parseError:command BitAnd(Cell1:chr,Cell2:chr):flt MSG voc[18857]: !SyntaxException:parseError: ^Type identifier expected MSG voc[18857]: !SyntaxException:parseError:command BitAnd(Cell1:chr,Cell2:chr):flt MSG voc[18857]: !SyntaxException:parseError: ^',' expected MSG merovingian[8780]: database 'voc' (18857) has exited with exit status 0 MSG merovingian[8780]: database 'voc' has shut down ERR merovingian[8780]: client error: database 'voc' started up, but failed to open up a communication channel

2 7

MonetDB schema for Yii 1.1 (PHP framework)?
by Kevork Vartanian 24 Mar '16

24 Mar '16

Hi, I am working on a mini project and using the Yii 1.1 PHP framework (OOP framework, using MVC) and trying to connect to a MonetDB database (changing from MySQL). I see that there is a MonetDB PHP client (https://dev.monetdb.org/hg/MonetDB/file/tip/clients/php/lib/php_monetdb.php) I believe it is MAPI based, but I don't know if that can be used in Yii by creating a wrapper or so. Yii has special classes to retrieve metadata information from database (the http://www.yiiframework.com/doc/api/1.1/CDbSchema) and it also has subclasses for several databases supported in Yii "out of the box" (cubrid, mssql, mysql, oci, pgsql and sqlite). It uses the CActiveDataProvider class which is based on ActiveRecord (http://www.yiiframework.com/doc/api/1.1/CActiveDataProvider) and has methods to retrieve the data from database along with different criteria. Knowing MonetDB also has an ODBC driver, I installed unixodbc along with the PHP PDO driver for ODBC, the MonetDB ODBC driver, and have done all the needed set up to make ODBC work. By the way, my test server is an Ubuntu VM. Now that I have the ODBC working, I am able to connect to the database, and when using SQL statements (building the sql query, command, datareader, etc) everything works in Yii, and I can retrieve the needed data, but when I want to use the widgets and make use of the framework and MVC, I start getting errors and I believe it's all because of not having the right schema class. I tried "forcing" the driver name and tested all the existing schemas (the ones I specified above, that come with Yii) but to no avail. I'm not a database expert or an advanced PHP (and Yii) developer but have some development experience and was able to work on this project and build a nice web application with MySQL but now that I'm planning to change the DB and use MonetDB, I believe I need expert advice and some help from real experts, and I'm hoping someone on this list will be able to shed some light. I couldn't find much on the Internet. Thank you. Regards, Kevork Vartanian Technical Solutions Consultant kevork.vartanian(a)innotta.com.au | +61 406 776 789 | www.innotta.com.au

2 1

How to quote/escape single quote character in the name of the file?
by Gennadiy 23 Mar '16

23 Mar '16

Hello, I am trying to load data from a text file that has single quote character in its name into a table: > "C:\Program Files\MonetDB\MonetDB5\mclient.bat" --port=50000 --user=monetdb password: Welcome to mclient, the MonetDB/SQL interactive terminal (Jul2015-SP2) Database: MonetDB v11.21.13 (Jul2015-SP2), 'soft10_20160318_172621' Type \q to quit, \? for a list of available commands auto commit mode: on sql> sql> sql>COPY 120 OFFSET 2 RECORDS INTO "observations_to_score1" FROM 'C:\\tmp\\sachin's_data_with_requested_column_03_03_2016.csv' USING DELIMITERS ',', '\n', '"' LOCKED; more> more> Everything works fine if there is no single quote character in the name of the file: sql>COPY 120 OFFSET 2 RECORDS INTO "observations_to_score1" FROM 'C:\\tmp\\sachins_data_with_requested_column_03_03_2016.csv' USING DELIMITERS ',', '\n', '"' LOCKED; 120 affected rows (12.3s) I was trying to precede the single quote character by a \ (backslash) or to double it, but it still did not work: sql>COPY 120 OFFSET 2 RECORDS INTO "observations_to_score1" FROM 'C:\\tmp\\sachin\'s_data_with_requested_column_03_03_2016.csv' USING DELIMITERS ',', '\n', '"' LOCKED; could not open file 'C:\\tmp\\sachin\'s_data_with_requested_column_03_03_2016.csv': No such file or directory sql>COPY 120 OFFSET 2 RECORDS INTO "observations_to_score1" FROM 'C:\\tmp\\sachin''s_data_with_requested_column_03_03_2016.csv' USING DELIMITERS ',', '\n', '"' LOCKED; could not open file 'C:\\tmp\\sachin\'s_data_with_requested_column_03_03_2016.csv': No such file or directory > dir c:\tmp\sa*.csv Volume in drive C is S3A9506D003 Volume Serial Number is 00B9-C775 Directory of c:\tmp 2016-03-18 17:12 144,202 sachin's_data_with_requested_column_03_03_2016.csv 2016-03-18 17:12 144,202 sachins_data_with_requested_column_03_03_2016.csv 2 File(s) 288,404 bytes 0 Dir(s) 377,532,665,856 bytes free I am using the following version of MonetDB: # MonetDB 5 server v11.21.13 "Jul2015-SP2" # Serving database 'soft10_20160318_172621', using 4 threads # Compiled for x86_64-pc-winnt/64bit with 64bit OIDs dynamically linked # Found 3.799 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 connection requests on mapi:monetdb://127.0.0.1:50000/ # Start processing logs sql/sql_logs version 52200 # Start reading the write-ahead log 'sql_logs\sql\log.11' # Finished reading the write-ahead log 'sql_logs\sql\log.11' # Finished processing logs sql/sql_logs # MonetDB/SQL module loaded > Thank you, -- Gennadiy

3 5

Fwd: [Bug 3960] SELECT * with UNION ALL does not check column order
by Anthony Damico 23 Mar '16

23 Mar '16

hi team, should monetdb throw an informative error when SELECT * gets used with UNION? monetdb and postgres both allow users to make the mistake below. from my perspective it should simply fail instead of mis-matching silently. thanks ---------- Forwarded message ---------- From: <bugzilla-daemon(a)monetdb.org> Date: Wed, Mar 23, 2016 at 10:42 AM Subject: [Bug 3960] SELECT * with UNION ALL does not check column order To: ajdamico(a)gmail.com https://www.monetdb.org/bugzilla/show_bug.cgi?id=3960 Niels Nes <niels(a)cwi.nl> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|REOPENED |RESOLVED Resolution|--- |WONTFIX --- Comment #4 from Niels Nes <niels(a)cwi.nl> --- postgres=# postgres=# CREATE TABLE x ( a integer , b integer ); CREATE TABLE postgres=# CREATE TABLE y ( b integer , a integer); CREATE TABLE postgres=# INSERT INTO x VALUES ( 1 , 2 ) ; INSERT 0 1 postgres=# INSERT INTO y VALUES ( 3 , 4 ) ; INSERT 0 1 postgres=# select * from x; a | b ---+--- 1 | 2 (1 row) postgres=# select * from y; b | a ---+--- 3 | 4 (1 row) postgres=# CREATE TABLE z AS ( SELECT * FROM x ) UNION ALL ( SELECT * FROM y ) WITH DATA; SELECT 2 postgres=# SELECT * FROM z; a | b ---+--- 1 | 2 3 | 4 (2 rows) ie what we do is normal behaviour.... -- You are receiving this mail because: You are on the CC list for the bug. You reported the bug.

1 0

Perl DBD::monetdb performance (slow) vs Python (fast)
by Puthick Hok 21 Mar '16

21 Mar '16

Hi, We are using MonetDB (MonetDB-11.19.9) compiled from source in openSUSE 12.2. We use the Perl client package from the source file. It works fine until we need to select 100000 plus records on a single field from a database in MonetDB. It works but it is lot slower than in python and in mclient. Selecting one field with a limit of 100,000 records takes under 1 second using mclient and python. However, it takes more than 23 seconds in Perl. Has anyone observed this performance issue with Perl DBD::monetdb? How Python connector manages to retrieve data very fast? and to make Perl connector work as fast as Python. Thank you in advance. Regards, Puthick

1 0

Re: MonetDB schema for Yii 1.1 (PHP framework)?
by Mark Raasveldt 17 Mar '16

17 Mar '16

Turns out I wasn't subscribed to the list on this email account, let's try that again. ----- Original Message ----- From: "Mark Raasveldt" <m.raasveldt(a)cwi.nl> To: "Kevork Vartanian" <kevork.vartanian(a)innotta.com.au> Cc: "users-list" <users-list(a)monetdb.org> Sent: Thursday, March 17, 2016 11:25:35 AM Subject: Re: MonetDB schema for Yii 1.1 (PHP framework)? Hey Kevork, I don't mind answering questions through email, but I'm afraid I have nothing to do with the PHP client (in fact, I didn't even know we had a PHP client for MonetDB). The author at the top of the page simply states who has performed the last commit to the MonetDB repository, not who created or authored that specific file. You must have clicked on the file after I performed a commit. I have CCed this message to the users' list, hopefully someone that at least knows about the existence of the MonetDB PHP client can help you out there. Regards, Mark ----- Original Message ----- From: "Kevork Vartanian" <kevork.vartanian(a)innotta.com.au> To: "m raasveldt" <m.raasveldt(a)cwi.nl> Sent: Thursday, March 17, 2016 4:31:54 AM Subject: MonetDB schema for Yii 1.1 (PHP framework)? Hi Mark, Hope this email finds you well. Apologies in advance in case you don't like to be contacted directly by email. I saw your name and email address as being the author of the MonetDB PHP client (https://dev.monetdb.org/hg/MonetDB/file/tip/clients/php/lib/php_monetdb.php) and wanted to contact you to ask a few questions, if you don't mind, of course. I am working on a mini project and using the Yii 1.1 PHP framework (OOP framework, using MVC) and trying to connect to a MonetDB database (changing from MySQL). I can see the PHP client is MAPI based, but I don't know if that can be used in Yii by creating a wrapper or so. Yii has special classes to retrieve metadata information from database (the http://www.yiiframework.com/doc/api/1.1/CDbSchema) and it also has subclasses for several databases supported in Yii "out of the box" (cubrid, mssql, mysql, oci, pgsql and sqlite). It uses the CActiveDataProvider class which is based on ActiveRecord (http://www.yiiframework.com/doc/api/1.1/CActiveDataProvider) and has methods to retrieve the data from database along with different criteria. Knowing MonetDB also has an ODBC driver, I installed unixodbc along with the PHP PDO driver for ODBC, the MonetDB ODBC driver, and have done all the needed set up to make ODBC work. By the way, my test server is an Ubuntu VM. Now that I have the ODBC working, I am able to connect to the database, and when using SQL statements (building the sql query, command, datareader, etc) everything works in Yii, and I can retrieve the needed data, but when I want to use the widgets and make use of the framework and MVC, I start getting errors and I believe it's all because of not having the right schema class. I tried "forcing" the driver name and tested all the existing schemas (the ones I specified above, that come with Yii) but to no avail. I'm not a database expert or an advanced PHP (and Yii) developer but have some development experience and was able to work on this project and build a nice web application with MySQL but now that I'm planning to change the DB and use MonetDB, I believe I need expert advice and some help from real experts. I don't know if you're familiar with Yii but do you think what I'm trying to achieve is doable? Also, MonetDB being columnar, I was thinking maybe I should look into the Yii classes for PostgreSQL (main one being http://www.yiiframework.com/doc/api/1.1/CPgsqlSchema with source code: https://github.com/yiisoft/yii/blob/1.1.17/framework/db/schema/pgsql/CPgsql…) and try to make similar ones for MonetDB but I doubt I will be able to do it without some help and guidance from experts. Your advice is greatly appreciated. Thank you. Regards, Kevork Vartanian Technical Solutions Consultant kevork.vartanian(a)innotta.com.au | +61 406 776 789 | www.innotta.com.au

1 0

User Defined Function
by Shmagi Kavtaradze 16 Mar '16

16 Mar '16

I have written a code in c, it works and now I want to implement it in Monetdb. 1) should I append the code in /sql/backends/monet5/UDF/udf.c file? 2) I have gone through udf.c, but still not sure how implement my c code there. I do not know C, just wanted to implement simple code. Can anyone help me adapting it to UDF? I want a function like BAnd(a.chunk, b.chunk), and instead of {"10101011"} in foo and bar, I want values of the columns. #include<stdio.h> main (){ const char foo[] = {"10101011"}; const char bar[] = {"01110111"}; int counter; float result; int strlen = sizeof(foo)/sizeof(foo[0])-1; for (int i=0; i < strlen; i++) { if (foo[i] == bar[i]) { counter++; } } result = (float)counter / strlen; printf("%d::%d::%f", counter, strlen, result); return 0; }

1 0

Re: Duplicate and reappearing tables.
by Nicolas Stalder 16 Mar '16

16 Mar '16

> > Message: 5 > Date: Tue, 15 Mar 2016 21:27:10 +0100 (CET) > From: Stefan Manegold <Stefan.Manegold(a)cwi.nl> > To: Communication channel for MonetDB users <users-list(a)monetdb.org> > Subject: Re: Duplicate and reappearing tables. > > Hi Nicolas, > > could you please share which version of MonetDB you're using on which OS? > > Also, did you initially create your database with the very same version of > MonetDB that you're using now, or did you create it with an earlier version > (which) and then upgrade it to the one you're using now (directly or via > intermetiate release steps)? > Stefan, It's Jul2015-SP2 running on Debian 8 (jessie). We loaded it from scratch, two daily files 100-250MB in size. Earlier we were running Jul2015-SP1 with the same issue. > Message: 7 > Date: Wed, 16 Mar 2016 08:02:47 +0000 > From: Marcel Blok <m.blok(a)chs.nl> > To: Communication channel for MonetDB users <users-list(a)monetdb.org> > Subject: RE: Duplicate and reappearing tables. > > Hi Nicolas, > > We have (and unfortunately sometimes still do) experience similar issues. > The issues appear most often at stopping and starting the database. In an > earlier thread (named "WAL read files") Pierre-Adrien found out that > underscores in table and/or column names could cause conflicts, and cause > similar issues. Maybe this is related to the issue you are experiencing? > > See also the bug report: > https://www.monetdb.org/bugzilla/show_bug.cgi?id=3912 I am aware of this report. However, I do not have any tables where <table_name>_<column_name> conflicts. The WAL files would be in db/sql_logs/sql, yes? I see a 12 byte file "log" and a 513K file "log.791". Can I inspect or understand what those files contain? What is in this numbered log file? I also tried loading a msqldump of the data into a new MonetDB instance, which eventually crashes with "Illegal input sequence". Are there any ways of making mserver5 more verbose? Clearly, something is happening at database startup. Cheers, Nicolas.

1 0

Shipping data / old voyages
by Brian Hood 16 Mar '16

16 Mar '16

Hi Guys, I have spent quite a bit of time with the voc database developing another project. I have where did you acquire this data as its very interesting. Regards Brian Hood

2 2

Duplicate and reappearing tables.
by Nicolas Stalder 16 Mar '16

16 Mar '16

Hi all I am running a currently roughly 400G-sized single Monet instance. We load daily CSV files into corresponding tables, and use merge tables to query the data (e.g., feed_20160301, feed_20160302, ... and feed_mt). We have a problem mainly with duplicate tables (same schema + table name, different table_id in sys.tables). My approach is to "DROP TABLE foo" for N - 1 times if there are N copies (is there even a way to SELECT or DROP by table_id?). This seemingly fixes the problem, however after restarting the database, the dropped tables reappear. Similarly, I have some old empty tables, which reappear after being dropped when the database is restarted. We start and stop the database to roll over connections during maintenance. Is this a known issue? Can it be fixed? Not sure how to make this reproducible. Thanks, Nicolas Stalder.

3 2

will monetdb work well if it needs to swap onto a SSD?
by Anthony Damico 15 Mar '16

15 Mar '16

pretty sure that monetdb hits a huge performance wall when you use up all your ram, but i'm curious if anyone has experience with swapping onto a SSD? is the speed-loss nearly equally bad, or is swapping onto a SSD a reasonable choice? thanks all

1 0

Backslash n interpreted as newline.
by Nicolas Stalder 15 Mar '16

15 Mar '16

Hi all I have noticed that when loading tables from CSV files via COPY INTO ... USING DELIMITERS '\t','\n', the ASCII characters '\' followed by 'n' are interpreted as a newline, leading to the loading being aborted due to an incorrect number of columns in this row. This seems to me to be a bug? Also, it would be most helpful if the error message contained a line number (as is the case if there is unicode in the CSV file). Thanks, Nicolas Stalder.

1 0

Debugging: !SELECT: no such operator 'normalize2'
by Willem Ligtenberg 15 Mar '16

15 Mar '16

I have created a function in R for MonetDB. My first function works fine: dbGetQuery(conn, "DROP FUNCTION normalize;") functionDef <- paste( "CREATE FUNCTION normalize(welltype STRING, data_column DOUBLE) RETURNS TABLE (i DOUBLE) LANGUAGE R {", "idx <- which(welltype == 'LC')", "100 * data_column / median(data_column[idx])", "};", sep = "\n") dbGetQuery(conn, functionDef) # example use stmt <- "SELECT barcode, normalize(welltype_code, data_column1) FROM hcs;" dbGetQuery(conn, stmt) # Works! Yay! So I wanted to create a more advanced version: dbGetQuery(conn, "DROP FUNCTION normalize2;") functionDef <- paste( "CREATE FUNCTION normalize2(barcode DOUBLE, welltype STRING, data_column DOUBLE) RETURNS TABLE (i DOUBLE) LANGUAGE R {", "idx <- which(welltype == 'LC')", "100 * data_column / median(data_column[idx])", "};", sep = "\n") dbGetQuery(conn, functionDef) As you see, the only thing that I have changed so far, is the addition of an extra argument to the function. However, this fails: # example use stmt <- "SELECT normalize2(barcode, welltype_code, data_column1) FROM hcs;" dbGetQuery(conn, stmt) I checked, and the types are all fine: barcode = DOUBLE welltype_code = CHARACTER LARGE OBJECT data_column1 = DOUBLE So what is going wrong? Kind regards, Willem

2 3

why "INSERT INTO ... SELECT" is concurrency conflicts with "CREATE TABLE"
by Pierre-Adrien Coustillas 15 Mar '16

15 Mar '16

Hi, Why is that the query INSERT INTO io__selection_top (id_navigateur) SELECT id_navigateur FROM bdm__index_client_cookies_id_navigateur; has a "COMMIT: transaction is aborted because of concurrency conflicts, will ROLLBACK instead" whith this query : DROP TABLE pierre; What is the table that a conflict ? SYS.TABLES ? Thanks Pierre -- 1G6 52 route de bischwiller 67300 Schiltigheim Société de Services et de Formations en Logiciels Libres http://1g6.biz Tél : 06 64 63 70 35 // 09 54 38 70 90

3 2

Monetdb crashes.
by Souri Ratnaparkhi 14 Mar '16

14 Mar '16

Hello, We have a main table with 126 columns and approx. 94000 rows. We are trying to load approx 2500 records from temporary table into main table using query “insert into select” query. The database crashes upon query execution. We get error “*monetdbd: internal error while starting mserver, please refer to the logs*” when to try to connect again. No specific error is created in log file. 1500 records successfully loads from temp table to main table. We also created another main table with same DDL, that table is empty & inserted all the records from temp successfully without any issue. What could be the reason? Is 126 cols an issue? Please help. We are stuck. (btw: We have machine about 200GB of hard disk & 16 GB RAM (14 GB free)) OS* - Ubuntu 14.04.2 LTS (GNU/Linux 3.13.0-55-generic x86_64)* Monetdb version* - MonetDB v11.21.13 (Jul2015-SP2)* Thank you [image: Description: Description: Description: Description: logo] * Souri Ratnaparkhi United Software Associates Pvt. Ltd. Pune, India Mob: 8975677255* * Phone (India Lines): (91) 020-25445550 **www.usain.com* <http://www.usain.com/>

4 10

User Defined Function and regular Function
by Shmagi Kavtaradze 13 Mar '16

13 Mar '16

I read documentation, but have some questions: 1) What is the real difference between UDF and just creating function in sql? I mean the speed of execution. 2) If I create a function, is it implicitly C? I am asking because there is python binding also. 3) If I have already installed Monetdb and want to create UDF, should I recompile whole monetdb? I am not good enough yet to do something like this. I am asking, because I want to take a text type column full of 0s and 1s ("110101010"), represent it as a "int" or "bit" and do bitwise AND against other columns. When I am saying represent as int/bit, I mean not to change ("110101010") into bit, just let C know that it should interpret that number already as int or bit.

2 2

Function
by Shmagi Kavtaradze 12 Mar '16

12 Mar '16

I want to compare two integer columns (filled with 1s and 0s) to each other with bitwise and, then count number of 1s and divide by length of column entries. I wrote a function, but not sure if it is right or how to create in in Monetdb from console. create function BAnd(int a, int b) returns float begin declare count int; int length = floor(log10(abs(a))) + 1; temp = a & b; while (temp != 0) { if (n % 10 == 1) count++; temp /= 10; } return count/length; end;

1 0

Bitwise operations in Monetdb
by Shmagi Kavtaradze 11 Mar '16

11 Mar '16

I am new to Monetdb. I am using Postgresql mainly, but want to check Monetdb performace. In Postres I have column of type bit() filled with 0s and 1s. Then I am comparing each row to all other rows with bitwise AND on that column. Monetdb does not have bit() type so I used text. Any ideas how to do bitwise AND in monetdb and what type of column should I use for this? The query I tried: select a.sentenecid, b.sentenecid, a.sentence AND b.sentence from test a, test b;

4 11

Monitoring JDBC Connections
by Arno Mittelbach 11 Mar '16

11 Mar '16

Hi all, I am looking for a way to monitor open remote connections (i.e., JDBC connections) and possibly to force close them. As far as I can tell, MonetDB restricts the number of simultaneous connections to 64 by default. Via select * from sys.queue I can monitor running queries but I haven't yet figured out a way to list all open connections. Consider e.g. the case that a JDBC connection is for some reason not properly closed. Any hints would be much appreciated. Best regards, Arno

1 0

R session crashes every time with MonetDBLite package
by David Jankoski 08 Mar '16

08 Mar '16

Hi everone, I am new to this help list so before I post my problem I just want to ask how to provide as much information as possible ( in terms of log files and similar) The problem : Using the MonetDBLite package for R i pushed a relatively big chunk of data (~ 11 Mil rows x 14 cols) in a monetDB lite on disk. Now _every_ time i try connecting to it and running some commands, it crashes my R session. Happens with RStudio and Rgui. So, what additional information to this should i provide in order to best describe the problem : - Machine & OS info, R version, sessionInfo(), R script i'm trying to run. Are there any additional logs kept by monetDB which would be helpful ? Thanks in front, David -- [image: hellotrip] <http://www.hellotrip.fr/> *David Jankoski* <http://www.hellotrip.fr/> Teerketelsteeg 1 1012TB Amsterdam www.hellotrip.com [image: Twitter] <https://twitter.com/hellotrip_nl>[image: facebook] <http://facebook.com/HellotripNederland>[image: Linkedin] <https://www.linkedin.com/company/hellotrip>

2 1

Re: MonetDB: leftmart - trying to fix Windows compilation: do not "i...
by Lefteris 08 Mar '16

08 Mar '16

Dear Souri, please direct these kind of questions not at the developers-list but at the users-list (users-list(a)monetdb.org) Now for the issue at hand. Which version of monetdb are you using and which operating system? In order for us to reproduce the error, we need to know the schema of both the main and temp table (you can hide attribute names if it is sensitive information) and also the *exact* query you are using to copy rows from one table to the other. If you could also share the values of the records would also help. In general you have to provide us with more information on your setup, and a way to reproduce the problem:) Also, did you check the merovegnian.log ? no errors there? Maybe you can attach a debugger and see where monetdb fails? Thank you, Lefteris On Tue, Mar 8, 2016 at 6:08 AM, Souri Ratnaparkhi < souri.ratnaparkhi(a)usain.com> wrote: > Hello monetdb team, > > A gentle reminder. > We have a main table with 126 columns and approx. 94000 rows. > We are trying to load approx 2500 records from temporary table into main > table using query “insert into select” query. The database crashes upon > query execution. No specific error is created in log file. > When tried with 1500 records, it works. > We also created another main table with same DDL, that table is empty & > inserted all the records from temp successfully without any issue. > > What could be the reason? Is 126 cols an issue? Please help. We are stuck. > > (btw: We have machine about 200GB of hard disk & 16 GB RAM (14 GB free)) > > Thank you > > > Souri Ratnaparkhi > United Software Associates Pvt. Ltd. > Pune, India > Mob: 8975677255 > Phone (India Lines): (91) 020-25445550 > www.usain.com > > > -----Original Message----- > From: developers-list > [mailto:developers-list-bounces+souri.ratnaparkhi=usain.com@monetdb.org] > On Behalf Of Stefan Manegold > Sent: Tuesday, March 08, 2016 5:05 AM > To: Lefteris > Cc: Communication channel for developers of the MonetDB > Subject: Re: MonetDB: leftmart - trying to fix Windows compilation: do not > "i... > > > My mistake. Sorry! > > Stefan > > ----- On Mar 8, 2016, at 12:12 AM, Lefteris lsidir(a)gmail.com wrote: > > > Stefan, > > > > this needs to be inlined because it is used many times. I will deal > > with compilation issues later on. This is a dev branch and we are > > running different experiments every night. But with this change if > > measured times are different I would not know if it is because of > > other changes or because of the inline statement removal. > > > > I would appreciate if you did not make any changes in this branch at > > the moment. > > > > thank you, > > > > lefteris > > > > > > On Mon, Mar 7, 2016 at 8:34 PM, Stefan Manegold < > > commits+Stefan.Manegold=cwi.nl(a)monetdb.org> wrote: > > > >> Changeset: ea1aed8beff8 for MonetDB > >> URL: > >> http://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=ea1aed8beff8 > >> Modified Files: > >> gdk/gdk_bloom.c > >> Branch: leftmart > >> Log Message: > >> > >> trying to fix Windows compilation: do not "inline" BLOOMask() > >> > >> On Windows, compilation fails with > >> failed invocation: cl -GF -W3 -WX -MD -nologo -Ox -Zi -Oi -I. -I.\.. > >> -I.\..\..\gdk -I..\common\options -I.\..\..\gdk\..\common\options > >> -I..\common\stream -I.\..\..\gdk\..\common\stream -I..\common\utils > >> -I.\..\..\gdk\..\common\utils -I.\..\..\buildtools\conf -DHAVE_ICONV > >> -IC:\Libraries\iconv-1.11.1.win64-vs2010\include -DHAVE_LIBATOMIC_OPS > >> -IC:\Libraries\atomic_ops-7.4.2 -DNDEBUG -LD -Felibbat.dll > >> gdk_batop.obj gdk_select.obj gdk_search.obj gdk_hash.obj gdk_tm.obj > >> gdk_orderidx.obj gdk_align.obj gdk_bbp.obj gdk_heap.obj gdk_utils.obj > >> gdk_atoms.obj gdk_qsort.obj gdk_ssort.obj gdk_storage.obj gdk_bat.obj > >> gdk_delta.obj gdk_cross.obj gdk_system.obj gdk_value.obj > >> gdk_posix.obj gdk_logger.obj gdk_sample.obj gdk_calc.obj gdk_aggr.obj > >> gdk_group.obj gdk_imprints.obj gdk_bloom.obj gdk_join.obj > >> gdk_project.obj gdk_unique.obj gdk_firstn.obj libbat.res /link > >> ..\common\options\monet_options.obj > >> ..\common\stream\libstream.lib ..\common\utils\mutils.obj > >> ..\common\utils\prompt.obj wsock32.lib Ws2_32.l ib > >> C:\Libraries\zlib-1.2.8.win64-vs2010\lib\zdll.lib > >> C:\Libraries\bzip2-1.0.6.win64-vs2010\lib\libbz2.lib psapi.lib > >> Creating library libbat.lib and object libbat.exp gdk_join.obj : > >> error LNK2019: unresolved external symbol BLOOMask referenced in > >> function hashjoin libbat.dll : fatal error LNK1120: 1 unresolved > >> externals > >> > >> > >> Maybe, the fact that BLOOMask() is "inline"d triggers this problem ... > >> > >> > >> diffs (11 lines): > >> > >> diff --git a/gdk/gdk_bloom.c b/gdk/gdk_bloom.c > >> --- a/gdk/gdk_bloom.c > >> +++ b/gdk/gdk_bloom.c > >> @@ -240,7 +240,6 @@ BATbloom(BAT *b) > >> return GDK_SUCCEED; > >> } > >> > >> -inline > >> int BLOOMask(BUN v, Bloomfilter *bloom) { > >> BUN hv,mv,x,y,z; > >> _______________________________________________ > >> checkin-list mailing list > >> checkin-list(a)monetdb.org > >> https://www.monetdb.org/mailman/listinfo/checkin-list > > -- > | Stefan.Manegold(a)CWI.nl | DB Architectures (DA) | > | www.CWI.nl/~manegold/ | Science Park 123 (L321) | > | +31 (0)20 592-4212 | 1098 XG Amsterdam (NL) | > _______________________________________________ > developers-list mailing list > developers-list(a)monetdb.org > https://www.monetdb.org/mailman/listinfo/developers-list >

1 0

BATfetchjoin does not hit always => can't use fetchjoin
by John Burgess 07 Mar '16

07 Mar '16

Hi, I'm completely new to monetdb and have been dropped in at the deep end with an application support issue at a customer site. The release running is FEB2013 SP6 on Windows Server 2012 64 bit. There are customizations including aggregate functions written in MAL so the issue may not derive from monetdb source. I have searched bug lists but not found an obvious match for the error that is being reported, so I'm wondering whether anyone has experienced similar symptoms and found a solution. (Bug 3375 is the closest match and was fixed in the release in use) The reported problem is that the application works normally for a period until something causes monetdb to consistently report errors: Error: GDK reported error. SQLState: 22000 ErrorCode: 0 Error: BATfetchjoin(tmp_3662,tmp_4362) does not hit always (|bn|=0 != 2660745=|l|) => can't use fetchjoin. SQLState: 22000 ErrorCode: 0 It has been noted that the error appears to be in response to some SQL which up to that point had been working : select count(*) as "_m1_", avg("B"."C") as "_m2_" from "A" left outer join "B" on "A"."A_ID" = "B"."A_ID" Further, there is a foreign key relationship defined: ALTER TABLE "B" ADD CONSTRAINT fk_B FOREIGN KEY ("A_ID") REFERENCES "A"("A_ID") and dropping the index corresponding to the foreign key causes monetdb to recover. i.e. drop index fk_B All suggestions would be gratefully appreciated. In the meantime, I will try to reproduce the issue in house. Thanks, John Burgess ________________________________

3 5

should DROP TABLE work even after a table has been SET READ ONLY?
by Anthony Damico 07 Mar '16

07 Mar '16

hi, this bugzilla ticket is marked as resolved, but this behavior seems unintuitive to me.. is it ok? thanks team CREATE TABLE my_table AS SELECT 1 AS my_column WITH DATA; ALTER TABLE my_table SET READ ONLY; SELECT COUNT(*) FROM my_table; DROP TABLE my_table; from https://www.monetdb.org/bugzilla/show_bug.cgi?id=3895

3 3

extreme performance breakdown after update
by Andreas F. Hoffmann 06 Mar '16

06 Mar '16

Hello List, I'm running monetdb on a Debian server and the software is managed via Debian packages. A few days ago I installed an update for the monetdb5-server package. After that I noticed a problem with queries with the function lower() in the WHERE clause: >>WHERE LOWER(“fieldname”) LIKE '%lower_string%'<< without any change to the data, the requests became much, much slower then before (from some hundred microseconds to some minutes). Not only in my application but also in the monetdb client... I could solve the problem via a switch from using LIKE to ILIKE and no lower()-function, but I'm curious what could be the cause for this dramatic performance breakdown? I searched the release history but I found nothing... Has anybody experienced the same effect? Anny ideas on this phenomenon? Greetings from Germany, Andreas (theafh)

4 6

how to turn off double quotes
by Anton Kravchenko 03 Mar '16

03 Mar '16

Hi there, I wonder if there is a way to "copy select * from some_table into '\path\to\some.csv' delimiters ','; ", so that in output csv there is no double quotes for char vars? (for example, some_string instead of "some_string") P.S. I am using MonetDB v11.21.11 (Jul2015-SP1) on Windows. Thank you, Anton

1 1

(no subject)
by Anthony Damico 03 Mar '16

03 Mar '16

http://stackoverflow.com/questions/35730919/monetdb-select-query-slow-when-…

1 0

how to change the user "monetdb" password?
by Lynn Carol Johnson 02 Mar '16

02 Mar '16

Hi all - I've been googling to try and find a method of changing the default password for user monetdb. The links I've found are all 8-9 years old (e.g. This<https://www.monetdb.org/pipermail/users-list/2007-December/001345.html> one) My commands: 1. monetdbd stop <mydbfarm> 2. cd /Usr/local/monetdb/bin 3. ./mserver5 Then execute a change password command and get these results, which look similar to the email exchange noted in the 8 year old link above: rs-btlcj34lt:bin lcj34$ ./mserver5 # MonetDB 5 server v11.21.11 "Jul2015-SP1" # Serving database 'demo', using 8 threads # Compiled for x86_64-apple-darwin14/64bit with 64bit OIDs and 128bit integers dynamically linked # Found 16.000 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 connection requests on mapi:monetdb://127.0.0.1:50000/ # MonetDB/GIS module loaded # Start processing logs sql/sql_logs version 52200 # Finished processing logs sql/sql_logs # MonetDB/SQL module loaded >changePassword("monetdb","bucklerLab"); !TypeException:user.main[1]:'user.changePassword' undefined in: user.changePassword("monetdb":str,"bucklerLab":str); > We are opening up our database to some outside folks. I've successfully created a read-only user/pwd for them, but want to protect the db by changing the default monetdb user password. I'm practicing on my own local copy before messing with our "real" db, but can't get this to work. Is there some other way I should be going about this? One additional question: What is the "passphrase" parameter I see when I run "monetdbd get all <mydbfarm>"? I tried playing with that but have not found what it effects. Thanks - Lynn

2 2

MySQL unix_timestamp() equivalent?
by Vijay Krishna 02 Mar '16

02 Mar '16

Hi, I am looking for an Epoch (bigint) to date/timestamp converter in MonetDB - something similar to unix_timestamp() in MySQL. I referred this page - https://www.monetdb.org/Documentation/SQLreference/Temporal and tried out a few functions, but it seems to work only with the existing Timestamp columns. Am I missing something here? I am using MonetDB Jan2014 release on a Mac OS-X. Any help much appreciated. Thanks & Regards, Vijayakrishna.P. Mobile : (+91) 9500402305.

4 7

MAPI COPY INTO from stream
by Gustavo Brian 02 Mar '16

02 Mar '16

Hi, I'm trying to bulk load data into monetdb through COPY INTO statement on the fly. I've created an aggregator script that will create a session and send chunk of data using MAPI interface. Like this: "sCOPY INTO foo FROM STDIN USING DELIMITERS ',','\\n';" //+ "\n" + "100,10,0,'10'\n" + "\n" I've been following this example: http://dev.monetdb.org/hg/MonetDB/file/tip/java/example/SQLcopyinto.java (haven't tested) I planned to add as part of the monetdb nodejs module: https://github.com/MonetDB/monetdb-nodejs/issues/1 but seems to be out of the scope of that repository. Sadly I'm not getting any error just doesn't work. What's the correct way ? Thanks.

2 1

Typecasting in prepared statements
by Arno Mittelbach 24 Feb '16

24 Feb '16

Hi all, I am having a strange problem regarding prepared queries with Umlaute (e.g., ä). It seems that the rule for type casting strings correspond to the length of the string. The problem is that an Umlaut seems to count for two "normal" characters. Here is an example. Consider the following simple table: create table A (a varchar(4)); Now if we create a prepared statement (ultimately I want to do this from Java) such as prepare select * from A where a = ?; we can execute the statement and supply 4 characters as the argument: exec 3('1234'); This works fine. However, if I am using an Umlaut within the parameter, then things don't work out. For example exec 4('ä123'); fails with the following error message EXEC: wrong type for argument 1 of prepared statement: char, expected varchar However, if I now shorten the parameter by "1" character, it works again. That is, exec 5('ä12'); is ok. Can anybody explain this behavior? Is this a bug? And is there a workaround? The only thing I came up with so far is to double the size of every column. Best regards, Arno

3 3

Slow query - round 2
by Luciano Sasso 24 Feb '16

24 Feb '16

Hi, Do you think that many ( very many ) inserts / updates on this table , inserts that were aborted by network error could corrupt data and cause it? Remember , table have 100M rows. I have table with 30M rows, and run well work. Att, -- Luciano Sasso Vieira Data Scientist & Solutions Architect luciano(a)gsgroup.com.br <http://www.gsgroup.com.br> | tel: 17 3353-0833 | cel: 17 99706-9335 www.gsgroup.com.br <http://www.gsgroup.com.br> --- Este email foi escaneado pelo Avast antivírus. https://www.avast.com/antivirus

2 1

File system level backup of running database
by Dennis Pallett 23 Feb '16

23 Feb '16

Hi all, Is it possible to create a file system level backup of a running MonetDB database? What are the downsides of doing this? For what it's worth, our databases are generally never updated outside of pre-fixed windows so only SELECT queries will be executed during any file system level backup. Best regards, Dennis Pallett

4 3

do these two commands create an error in the dev version of monetdb?
by Anthony Damico 23 Feb '16

23 Feb '16

they do work in the latest release but they crash monetdblite, making me wonder about the root culprit.. thanks all create table my_table (my_id clob, my_double double); SELECT COUNT( DISTINCT my_id ) AS unique_enrollees , QUANTILE( my_double , 0.25 ) AS some_quantile FROM my_table;

2 2

is there a downloadable nightly build exe installer for windows?
by Anthony Damico 23 Feb '16

23 Feb '16

or do i need to do something with these? http://dev.monetdb.org/hg/MonetDB/file/tip/NT/installer64 never done this before..curious how cumbersome it is i want to check whether this is a bug introduced within mserver or a bug specific to monetdblite https://www.monetdb.org/bugzilla/show_bug.cgi?id=3927 thanks!

2 2

Slow query
by Luciano Sasso 21 Feb '16

21 Feb '16

Hi Stefan About "prepare statements calls/traces ", in paralell select count(*), this service that insert in 10 and 10 minutes. But even without such active service , the time is the same, 90 minutes. My mistake, my server have 6 cores. Hi, (as a side note, I'm afraid I'm too "old fashioned" and do no longer understand "modern" MonetDB traces; at least, I have no idea, why there are so many prepare statements calls/traces in your trace of a simple select count(*) query ... ) Having said that, the trace suggests that your machine either has 6 cores, or MonetDB has decided to split it in 6 slices for processing, although you machine has only 4 cores? Also, the trace reveals that the vast majority of the time is spent in 6 (one per slice) sql.tid() calls: ....... -- Luciano Sasso Vieira Data Scientist & Solutions Architect luciano(a)gsgroup.com.br <http://www.gsgroup.com.br> | tel: 17 3353-0833 | cel: 17 99706-9335 www.gsgroup.com.br <http://www.gsgroup.com.br> --- Este email foi escaneado pelo Avast antivírus. https://www.avast.com/antivirus

1 0

Slow query
by Luciano Sasso 21 Feb '16

21 Feb '16

Hi, Martin Yes, before performance analysis, load is zero, the server is Linix console only. My RAM memory is low for this database size. what do you think ? Att, Hi, about slow query Yes, there is, but still left over resources. While the query (which takes 90 minutes) are running, to perform another in another table (9M rows), this latter returns usually in seconds. Hi Its all about concurrent processes while working with MonetDB. All those processes compete for RAM and disk bandwidth. Even running a browser already takes off a few GB from your RAM. At some point, you end up swapping intermediates to disk, which is a slow process (depending on your HDD). For performance analysis, you better shut down all the processes you don't need. Ideally, the CPU load = 0 before doing a performance analysis. > " > > A simple "select count(*) from table", run on 90 minutes. > > that is not normal. most likely there are a lot of other > processes competing for resources. > > > " -- Luciano Sasso Vieira Data Scientist & Solutions Architect luciano(a)gsgroup.com.br <http://www.gsgroup.com.br> | tel: 17 3353-0833 | cel: 17 99706-9335 www.gsgroup.com.br <http://www.gsgroup.com.br> --- Este email foi escaneado pelo Avast antivírus. https://www.avast.com/antivirus

1 0

Slow query
by Luciano Sasso 21 Feb '16

21 Feb '16

Hi, send now trace for query in link: https://drive.google.com/folderview?id=0B0L6U0kZpUaFNUU3ak9PNUFVZW8&usp=sha… Att, -- Luciano Sasso Vieira Data Scientist & Solutions Architect luciano(a)gsgroup.com.br <http://www.gsgroup.com.br> | tel: 17 3353-0833 | cel: 17 99706-9335 www.gsgroup.com.br <http://www.gsgroup.com.br> --- Este email foi escaneado pelo Avast antivírus. https://www.avast.com/antivirus

1 0

Slow Query
by Luciano Sasso 21 Feb '16

21 Feb '16

Hi, send attached trace for query. Att, --- Este email foi escaneado pelo Avast antivírus. https://www.avast.com/antivirus

3 2

Re: users-list Digest, Vol 42, Issue 17
by Luciano Sasso 21 Feb '16

21 Feb '16

Hi, about slow query Yes, there is, but still left over resources. While the query (which takes 90 minutes) are running, to perform another in another table (9M rows), this latter returns usually in seconds. " A simple "select count(*) from table", run on 90 minutes. that is not normal. most likely there are a lot of other processes competing for resources. " Em 21/02/2016 08:00, users-list-request(a)monetdb.org escreveu: > Send users-list mailing list submissions to > users-list(a)monetdb.org > > To subscribe or unsubscribe via the World Wide Web, visit > https://www.monetdb.org/mailman/listinfo/users-list > or, via email, send a message with subject or body 'help' to > users-list-request(a)monetdb.org > > You can reach the person managing the list at > users-list-owner(a)monetdb.org > > When replying, please edit your Subject line so it is more specific > than "Re: Contents of users-list digest..." > > > Today's Topics: > > 1. Re: Slow Query (Martin Kersten) > 2. Re: Slow Query (Stefan Manegold) > 3. might be of interest (Anthony Damico) > > > ---------------------------------------------------------------------- > > Message: 1 > Date: Sat, 20 Feb 2016 21:34:06 +0100 > From: Martin Kersten <martin(a)monetdb.org> > To: users-list(a)monetdb.org > Subject: Re: Slow Query > Message-ID: <56C8CDBE.3060006(a)monetdb.org> > Content-Type: text/plain; charset=windows-1252; format=flowed > > On 20/02/16 19:39, Luciano Sasso wrote: >> Hi, >> >> I have MonetDB JUL2015-SP2, with table of 100M rows and 20 coluns, >> 4 of number type, others string type. >> >> A simple "select count(*) from table", run on 90 minutes. > that is not normal. most likely there are a lot of other > processes competing for resources. > >> Hardware and SO is: Debian 8, 4 cores, 11G RAM and 20G free on disc. >> >> While monet run the query, command top, show 42G of virtual memory >> >> Its normal ?, any idea ? >> >> thanks. >> >> >> -- >> >> Luciano Sasso Vieira >> Data Scientist & Solutions Architect >> >> <http://www.gsgroup.com.br>luciano(a)gsgroup.com.br | tel: 17 3353-0833 | cel: 17 99706-9335 >> <http://www.gsgroup.com.br>www.gsgroup.com.br >> >> >> Este e-mail foi enviado por um computador sem v?rus e protegido pelo Avast. >> www.avast.com <https://www.avast.com/sig-email> >> >> >> >> _______________________________________________ >> users-list mailing list >> users-list(a)monetdb.org >> https://www.monetdb.org/mailman/listinfo/users-list >> > > > ------------------------------ > > Message: 2 > Date: Sat, 20 Feb 2016 22:16:01 +0100 (CET) > From: Stefan Manegold <Stefan.Manegold(a)cwi.nl> > To: Communication channel for MonetDB users <users-list(a)monetdb.org> > Subject: Re: Slow Query > Message-ID: <1956506938.11256849.1456002961125.JavaMail.zimbra(a)cwi.nl> > Content-Type: text/plain; charset="utf-8" > > Hi, > > this is indeed unexpected. > > To possibly better understand what's happening, > could you please run your query prefixed with keyword TRACE > and share the trace (profiling) result? > > Best, > Stefan > > ----- On Feb 20, 2016, at 7:39 PM, Luciano Sasso luciano(a)gsgroup.com.br wrote: > >> Hi, >> >> I have MonetDB JUL2015-SP2, with table of 100M rows and 20 coluns, >> 4 of number type, others string type. >> >> A simple "select count(*) from table", run on 90 minutes. >> >> Hardware and SO is: Debian 8, 4 cores, 11G RAM and 20G free on disc. >> >> While monet run the query, command top, show 42G of virtual memory. >> >> Its normal ?, any idea ? >> >> thanks. >> >> >> -- >> >> >> Luciano Sasso Vieira >> Data Scientist & Solutions Architect >> >> luciano(a)gsgroup.com.br | tel: 17 3353-0833 | cel: 17 99706-9335 >> www.gsgroup.com.br >> >> >> Este e-mail foi enviado por um computador sem v?rus e protegido pelo Avast. >> www.avast.com >> >> _______________________________________________ >> users-list mailing list >> users-list(a)monetdb.org >> https://www.monetdb.org/mailman/listinfo/users-list -- Luciano Sasso Vieira Data Scientist & Solutions Architect luciano(a)gsgroup.com.br <http://www.gsgroup.com.br> | tel: 17 3353-0833 | cel: 17 99706-9335 www.gsgroup.com.br <http://www.gsgroup.com.br> --- Este email foi escaneado pelo Avast antivírus. https://www.avast.com/antivirus

2 1

might be of interest
by Anthony Damico 21 Feb '16

21 Feb '16

http://stackoverflow.com/questions/32380663/improve-speed-of-queries-using-…

1 0

Slow Query
by Luciano Sasso 20 Feb '16

20 Feb '16

Hi, I have MonetDB JUL2015-SP2, with table of 100M rows and 20 coluns, 4 of number type, others string type. A simple "select count(*) from table", run on 90 minutes. Hardware and SO is: Debian 8, 4 cores, 11G RAM and 20G free on disc. While monet run the query, command top, show 42G of virtual memory. Its normal ?, any idea ? thanks. -- Luciano Sasso Vieira Data Scientist & Solutions Architect luciano(a)gsgroup.com.br <http://www.gsgroup.com.br> | tel: 17 3353-0833 | cel: 17 99706-9335 www.gsgroup.com.br <http://www.gsgroup.com.br> --- Este email foi escaneado pelo Avast antivírus. https://www.avast.com/antivirus

3 2

Error in connecting monetdb server to localhost
by Debangana Sarkar 17 Feb '16

17 Feb '16

when I am connecting to MonetDB server it is showing an error : FATAL logger error there is no logger catalog but a log file is found. Are you sure you are using the same combination of database --dbpath and log directory Press any key to continue. Client error is: couldnot connect to localhost:50000 unknow error. Previously I installed the monetDB server and client and it was working.But for some reason it was uninstalled.Now after installing this error is coming. Please help

1 0

is it possible for a user to turn assertions on/off in mserver easily?
by Anthony Damico 16 Feb '16

16 Feb '16

might help with future testing..thanks all

3 3

Re: Re: Some time monetdbd can't start existing dbfarm
by Haiqing Ying 12 Feb '16

12 Feb '16

Thanks, Lefteris. You might be right and the log did suggest some file (.vaultkey) can't be found before it stopped working. I'll move the dbfarm out of /tmp and give it a try. Regards, HQ

1 0

Re: Re: Some time monetdbd can't start existing dbfarm
by Haiqing Ying 11 Feb '16

11 Feb '16

Hi Brian, Thanks for looking into this issue. Here is some more details in meroveringian.log - The DB was up for about a week. There're a lot of lines of 'proxying client ...', until the line 'client error: cannot start database 'EXM': no .vaultkey found...' 2016-02-07 08:08:13 MSG merovingian[12262]: proxying client localhost:33861 for database 'EXM' to mapi:monetdb:///tmp/exm/EXM/.mapi.sock?database=EXM 2016-02-07 08:08:13 MSG merovingian[12262]: target connection is on local UNIX domain socket, passing on filedescriptor instead of proxying 2016-02-07 08:08:13 MSG merovingian[12262]: proxying client localhost:33862 for database 'EXM' to mapi:monetdb:///tmp/exm/EXM/.mapi.sock?database=EXM 2016-02-07 08:08:13 MSG merovingian[12262]: target connection is on local UNIX domain socket, passing on filedescriptor instead of proxying 2016-02-07 08:08:13 MSG merovingian[12262]: proxying client localhost:33863 for database 'EXM' to mapi:monetdb:///tmp/exm/EXM/.mapi.sock?database=EXM 2016-02-07 08:08:13 MSG merovingian[12262]: target connection is on local UNIX domain socket, passing on filedescriptor instead of proxying 2016-02-07 08:36:20 MSG merovingian[12262]: starting database 'EXM', up min/avg/max: 0s/0s/0s, crash average: 1.00 0.10 0.03 (1-0=1) 2016-02-07 08:36:20 ERR merovingian[12262]: client error: cannot start database 'EXM': no .vaultkey found (did you create the database with `monetdb create EXM`?) 2016-02-07 08:36:20 MSG merovingian[12262]: starting database 'EXM', up min/avg/max: 0s/0s/0s, crash average: 1.00 0.10 0.03 (1-0=1) 2016-02-07 08:36:20 ERR merovingian[12262]: client error: cannot start database 'EXM': no .vaultkey found (did you create the database with `monetdb create EXM`?) 2016-02-07 08:36:20 MSG merovingian[12262]: starting database 'EXM', up min/avg/max: 0s/0s/0s, crash average: 1.00 0.10 0.03 (1-0=1) 2016-02-07 08:36:21 ERR merovingian[12262]: client error: cannot start database 'EXM': no .vaultkey found (did you create the database with `monetdb create EXM`?) And then, a lot of lines like below: 2016-02-07 14:44:18 MSG merovingian[12262]: starting database 'EXM', up min/avg/max: 0s/0s/0s, crash average: 1.00 0.10 0.03 (1-0=1) 2016-02-07 14:44:23 ERR merovingian[12262]: client error: cannot start database 'EXM': no .vaultkey found (did you create the database with `monetdb create EXM`?) Eventually, there're lot of errors like this: 2016-02-08 01:13:58 MSG merovingian[12262]: starting database 'EXM', up min/avg/max: 0s/0s/0s, crash average: 1.00 0.10 0.03 (1-0=1) 2016-02-08 01:14:03 ERR merovingian[12262]: client error: cannot start database 'EXM': no .vaultkey found (did you create the database with `monetdb create EXM`?) 2016-02-08 01:14:11 ERR merovingian[12262]: client error: could not retrieve uplog information: could not open file /tmp/exm/EXM/.uplog: Too many open file s 2016-02-08 01:14:18 ERR merovingian[12262]: client error: could not retrieve uplog information: could not open file /tmp/exm/EXM/.uplog: Too many open file s 2016-02-08 01:14:26 ERR merovingian[12262]: client error: could not retrieve uplog information: could not open file /tmp/exm/EXM/.uplog: Too many open file S Then neither mclient nor monetdbd stop cmd works any more. This is what I have in /tmp/exm folder after reboot: [centos@ip-10-72-0-98 exm]$ pwd /tmp/exm [centos@ip-10-72-0-98 exm]$ ls -al total 8088 drwxr-xr-x. 3 centos centos 62 Feb 9 17:33 . drwxrwxrwt. 11 root root 4096 Feb 11 13:36 .. drwx------. 4 centos centos 77 Feb 9 17:33 EXM -rw-------. 1 centos centos 0 Feb 9 17:33 .merovingian_lock -rw-------. 1 centos centos 8272151 Feb 9 17:33 merovingian.log [centos@ip-10-72-0-98 exm]$ ls -al EXM total 8 drwx------. 4 centos centos 77 Feb 9 17:33 . drwxr-xr-x. 3 centos centos 62 Feb 9 17:33 .. drwx------. 66 centos centos 4096 Feb 2 19:48 bat -rw-------. 1 centos centos 0 Feb 7 08:36 .gdk_lock srwx------. 1 centos centos 0 Jan 27 14:50 .mapi.sock drwx------. 3 centos centos 16 Jan 27 14:50 sql_logs -rw-------. 1 centos centos 22 Feb 9 17:33 .uplog If needed, I can send you the whole merovingian.log file (which is 8GB in size). Thanks, HQ

1 0

copy into stdout and redirect to a file
by Anton Kravchenko 11 Feb '16

11 Feb '16

Hi there, I wonder if one can copy a query result into stdout and redirect it to a file? I've tried: mclient.bat -u monetdb -d somedb -s "copy select * from sometable into stdout" - > .\somefile.csv But, only the table header is saved in somefile.csv... P.S. 'select * from sometable' shows all data rows as expected. I am using MonetDB v11.21.11 (Jul2015-SP1) on Windows. Thank you, Anton

2 2

Some time monetdbd can't start existing dbfarm
by Haiqing Ying 11 Feb '16

11 Feb '16

Hi, I created a dbfarm (/tmp/exm ), then created database (EXM), load data, do queries, etc, everything works fine for a week. Then I found mclient stopped working (can't connect to monetdb). The log file shows 'too many files opened' issue. I guess it might be some connection leak issue. I tried 'monetdbd stop /tmp/exm', but it's not responding. It looks I can't stop the dbfarm now. I then reboot the system and after reboot tried 'monetdbd start /tmp/exm' but I got an error saying ' cannot find file in /tmp/exm'. I verified the /tmp/exm folder did exist and there're still sub folder ( ./EXM) and log file still under /tmp/exm. The only option left seems to be creating a new dbfarm and recreating the whole database again. We're moving to use MonetDB in production and if this is a real bug then it would be too risky for production. The question is, is this a real issue? Or it's just some user error that I made? How do I make sure we won't run into this situation again in production mode. Many thanks, HQ

3 2