April 2016 - users-list

Crystal bindings for MonetDB
by Brian Hood 30 Apr '16

30 Apr '16

Hi Guys, I have got my Crystal Language bindings for MonetDB now working quite well. https://github.com/puppetpies/crystal-monetdb-libmapi I've seen options in the mapi code for a variable called mode that allow you format the response data in XML but it doesn't seem to be implemented fully in the mapi code. Are there any guide lines to processing this RAW query output. % threatmonitor.fruits, threatmonitor.fruits, threatmonitor.fruits, threatmonitor.fruits, threatmonitor.fruits # table_name % name, price, weight, comments, id # name % varchar, varchar, int, clob, int # type % 9, 4, 2, 11, 1 # length [ "Apple", "9.99", 50, NULL, 1 ] [ "Bananna", "3.99", 30, NULL, 2 ] [ "Orange", "7.99", 60, NULL, 3 ] [ "Peach", "5.00", 80, NULL, 4 ] [ "Kiwi", "9.00", 20, NULL, 5 ] [ "Tomato", "2.00", 20, "Yes a fruit", 6 ] [ "Pear", "4.00", 30, "Juicy", 7 ] [ "Nectarine", "6.00", 50, "Juicy", 8 ] Regards, Brian Hood

1 0

Case sensitive
by Ariel Abadi 30 Apr '16

30 Apr '16

Hi All! We are facing a problem, we used to work with mysql with the case insensitive. Now we have moved into MonetDB and we are facing a problem with this issue. Some of our queries are not bringing any data because of the sensitive.. SELECT * FROM Country = 'jamaica' ... 0 Records SELECT * FROM Country = 'Jamaica' ... 10 Records Some cases we solved it using the ILIKE, but we still have some code on our app that cannot be modified. Does anybody know if there is any flag or setup or config for the Database in Monet to make it insensitive ? Thanks in advance for the help!

6 6

Support for multi-column aggregate functions
by Anderson, David B 30 Apr '16

30 Apr '16

All, Does the current version of MonetDB support multi-column aggregate functions? I need to create a function which returns the "quantiles" of one column using another column as a weighting. It looks like PostgreSQL supports multi-column aggregates as of 8.2. I could write an embedded R function to compute this, but want to know if it is even possible to pass in multiple columns. Thanks, Dave

4 9

回复：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 25 Apr '16

25 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