July 2016 - users-list

Server crashes when using a prepared statement with a function
by João Victor Risso 12 Jul '16

12 Jul '16

Hello, I have a SQL function (myFunc) that computes the sum of the records on a table (tbl1) and returns the sum (along with other attributes) as a table. tbl1 references tables tbl2 and tbl3 through fk_tbl2_id and fk_tbl3_id's, respectively. Since I want to use this function in a Node.js API and the parameters for the function will come from the users of the API, it is safer to use a prepared statement to bind the parameters. Whenever I try to execute the following prepared statement with the function: > PREPARE SELECT * FROM myFunc(?, ?, ?); > EXEC (2008, 1, 2); The server crashes, as shown in the server log and dmesg: > 2016-07-12 10:56:40 MSG merovingian[3110]: database 'testdb' (19875) was killed by signal SIGSEGV > mserver5[18201]: segfault at 30 ip 00007f2d87be51e4 sp 00007f2d86f63b80 error 4 in lib_sql.so[7f2d87b85000+199000] The SQL function for myFunc is the following: > CREATE FUNCTION myFunc(year int, table2_id int, table3_id int) > RETURNS TABLE(name text, total bigint) > BEGIN > RETURN ( > SELECT > 'Overall', > COALESCE(SUM(t1.num_enrollments), 0) > FROM > tbl1 AS t1 > WHERE > t1.census_year = year > AND > (CASE(table2_id) > WHEN 0 THEN > (t1.fk_table2_id > 0) > ELSE > (t1.fk_table2_id = table2_id) > END) > AND > (CASE(table3_id) > WHEN 0 THEN > (t1.fk_table3_id > 0) > ELSE > (t1.fk_table3_id = table3_id) > END) > ); > END; I'm running MonetDB v11.21.19 (Jul2015-SP4) on Ubuntu 14.04 LTS. I could not find any bug reports related to the issue. How can I debug it further? Any help is deeply appreciated. Best regards, João Victor Risso.

1 0

select * from sys.db_users() SIGSEGV
by Vincent Sheffer 12 Jul '16

12 Jul '16

We are running: [root@monetdb monetdb5]# mserver5 --version MonetDB 5 server v11.21.19 "Jul2015-SP4" (64-bit, 64-bit oids, 128-bit integers) 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 Found 503.6GiB available memory, 28 available cpu cores Libraries: libpcre: 8.32 2012-11-30 (compiled with 8.32) openssl: OpenSSL 1.0.1e 11 Feb 2013 (compiled with ) libxml2: 2.9.1 (compiled with 2.9.1) Compiled by: @9bedafc83c11 (x86_64-unknown-linux-gnu) Compilation: gcc -gdwarf-4 -g3 Linking : /usr/bin/ld -m elf_x86_64 When select * from sys.db_users() is executed when logged in as the monetdb user there is no problem, but when run under any other user we get the following stack trace: [Switching to Thread 0x7f418e7f3700 (LWP 26599)] 0x00007f41bd5cf059 in db_users_wrap (cntxt=0x7f41be899630, mb=0x7f412416e620, stk=0x7f4124114bd0, pci=0x7f41241255d0) at sql_user.c:160 160sql_user.c: No such file or directory. (gdb) where #0 0x00007f41bd5cf059 in db_users_wrap (cntxt=0x7f41be899630, mb=0x7f412416e620, stk=0x7f4124114bd0, pci=0x7f41241255d0) at sql_user.c:160 #1 0x00007f41c519f1f7 in runMALsequence (cntxt=0x7f41be899630, mb=0x7f412416e620, startpc=1, stoppc=0, stk=0x7f4124114bd0, env=0x0, pcicaller=0x0) at mal_interpreter.c:631 #2 0x00007f41c519e819 in callMAL (cntxt=0x7f41be899630, mb=0x7f412416e620, env=0x7f418e7f2a68, argv=0x7f418e7f2af0, debug=0 '\000') at mal_interpreter.c:447 #3 0x00007f41bd5d50d6 in SQLexecutePrepared (c=0x7f41be899630, be=0x7f41242417f0, q=0x7f4124117740) at sql_execute.c:328 #4 0x00007f41bd5d54c9 in SQLengineIntern (c=0x7f41be899630, be=0x7f41242417f0) at sql_execute.c:390 #5 0x00007f41bd5d4257 in SQLengine (c=0x7f41be899630) at sql_scenario.c:1323 #6 0x00007f41c51bcaf7 in runPhase (c=0x7f41be899630, phase=4) at mal_scenario.c:515 #7 0x00007f41c51bccb4 in runScenarioBody (c=0x7f41be899630) at mal_scenario.c:559 #8 0x00007f41c51bcda1 in runScenario (c=0x7f41be899630) at mal_scenario.c:579 #9 0x00007f41c51bdcd8 in MSserveClient (dummy=0x7f41be899630) at mal_session.c:439 #10 0x00007f41c51bd8b2 in MSscheduleClient (command=0x7f4124279b90 "p\233'$A\177", challenge=0x7f418e7f2da0 "Up8bNTal6v", fin=0x7f4124117560, fout=0x7f4124241680) at mal_session.c:319 #11 0x00007f41c526d001 in doChallenge (data=0x7f41a0001690) at mal_mapi.c:184 #12 0x00007f41c4bae17f in thread_starter (arg=0x7f41a00008c0) at gdk_system.c:458 #13 0x00007f41c2814dc5 in start_thread () from /lib64/libpthread.so.0 #14 0x00007f41c2541ced in clone () from /lib64/libc.so.6 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

range request on timestamp column in prepared statement
by Frank Tetzel 11 Jul '16

11 Jul '16

Hello, i have trouble to get a parametrized range request on a timestamp column working. Small example in mclient: create table test(creationDate timestamp); insert into test values('2012-04-02 00:00:00'), ('2012-03-30 00:00:00'),('2012-04-20 00:00:00'); -- works prepare select * from test where creationDate < ?; exec 21(TIMESTAMP '2012-04-01 00:00:00'); -- works select * from test where creationDate between TIMESTAMP '2012-04-01 00:00:00' and TIMESTAMP '2012-04-01 00:00:00' + interval '5' day; -- syntax error, unexpected '?', expecting SCOLON prepare select * from test where creationDate between ? and ? + interval ? day; -- fails too: program contains errors prepare select * from test where creationDate between ? and ? + interval '5' day; exec 22(TIMESTAMP '2012-04-01 00:00:00', TIMESTAMP '2012-04-01 00:00:00'); Does anybody know of a workaround? Is there a builtin function for adding days to a timestamp i missed? I couldn't find anything in the documentation. Btw, i'm on v11.23.3. Regards, Frank

1 1

Is there any scan sharing technique in MonetDB?
by Xu,Wenjian 07 Jul '16

07 Jul '16

Hi all, I understand that MonetDB would recycle intermediate results to accelerate subsequent OLAP queries that involve common sub-queries as previous ones. I am wondering that, 1. Does MonetDB implement any scan sharing techniques (or generally, multi-query optimization techniques) in the context of concurrent workloads? For example, two concurrent queries Q1 and Q2 both require scanning column X. Is it possible for their query plans to share the scan operation? 2. If no, what is the reason? too complex to implement? or any reason related to MonetDB's execution paradigm that prevents such sharing techniques? Any comments would be helpful, thanks. Best regards, Wenjian

2 2

R UDFs
by Anderson, David B 06 Jul '16

06 Jul '16

All, Quick question, how many R interpreters are running within MonetDB if you are using R UDFs? Is there a single shared interpreter or are there multiple copies running? Thanks, Dave

2 16

DATABASE NOT STARTING
by Ariel Abadi 04 Jul '16

04 Jul '16

Guys! Im desperate... the DB is not starting anymore... Can someone help me? Im starting the DB but I receive the following msg: starting database 'sb_traf'... FAILED start: starting 'sb_traf' failed: database 'sb_traf' appears to shut itself down after starting, check monetdbd's logfile for possible hints This is part of the log... maybe somebody has a clue. 2016-07-02 22:15:30 MSG merovingian[28268]: proxying client static.10-10-10-225.clients.your-server.de:47462 for database 'sb_traf' to mapi:monetdb:///opt/monetdb/CUSTOMERnew/sb_traf/.mapi.sock?database=sb_traf 2016-07-02 22:15:30 MSG merovingian[28268]: target connection is on local UNIX domain socket, passing on filedescriptor instead of proxying 2016-07-02 22:15:30 MSG merovingian[28268]: proxying client static.10-10-10-225.clients.your-server.de:47463 for database 'sb_traf' to mapi:monetdb:///opt/monetdb/CUSTOMERnew/sb_traf/.mapi.sock?database=sb_traf 2016-07-02 22:15:30 MSG merovingian[28268]: target connection is on local UNIX domain socket, passing on filedescriptor instead of proxying 2016-07-02 22:20:29 MSG merovingian[28268]: proxying client static.10-10-10-225.clients.your-server.de:47726 for database 'sb_traf' to mapi:monetdb:///opt/monetdb/CUSTOMERnew/sb_traf/.mapi.sock?database=sb_traf 2016-07-02 22:20:29 MSG merovingian[28268]: target connection is on local UNIX domain socket, passing on filedescriptor instead of proxying 2016-07-02 22:20:29 MSG merovingian[28268]: database 'sb_traf' (28351) was killed by signal SIGSEGV 2016-07-02 22:20:36 MSG merovingian[28268]: starting database 'sb_traf', up min/avg/max: 11s/1d/3w, crash average: 1.00 0.40 0.50 (69-25=44) 2016-07-02 22:20:36 MSG sb_traf[29578]: arguments: /usr/bin/mserver5 --dbpath=/opt/monetdb/CUSTOMERnew/sb_traf --set merovingian_uri=mapi:monetdb://CUSTOMER-monetbd:54321/sb_traf --set mapi_open=false --set mapi_port=0 --set mapi_usock=/opt/monetdb/CUSTOMERnew/sb_traf/.mapi.sock --set monet_vault_key=/opt/monetdb/CUSTOMERnew/sb_traf/.vaultkey --set gdk_nr_threads=8 --set max_clients=64 --set sql_optimizer=default_pipe --set monet_daemon=yes 2016-07-02 22:20:36 MSG sb_traf[29578]: # MonetDB 5 server v11.21.19 "Jul2015-SP4" 2016-07-02 22:20:36 MSG sb_traf[29578]: # Serving database 'sb_traf', using 8 threads 2016-07-02 22:20:36 MSG sb_traf[29578]: # Compiled for x86_64-redhat-linux-gnu/64bit with 64bit OIDs and 128bit integers dynamically linked 2016-07-02 22:20:36 MSG sb_traf[29578]: # Found 20.000 GiB available main-memory. 2016-07-02 22:20:36 MSG sb_traf[29578]: # Copyright (c) 1993-July 2008 CWI. 2016-07-02 22:20:36 MSG sb_traf[29578]: # Copyright (c) August 2008-2015 MonetDB B.V., all rights reserved 2016-07-02 22:20:36 MSG sb_traf[29578]: # Visit http://www.monetdb.org/ for further information 2016-07-02 22:20:36 MSG sb_traf[29578]: # Listening for UNIX domain connection requests on mapi:monetdb:///opt/monetdb/CUSTOMERnew/sb_traf/.mapi.sock 2016-07-02 22:20:36 MSG sb_traf[29578]: # Start processing logs sql/sql_logs version 52200 2016-07-02 22:20:36 MSG sb_traf[29578]: # Start reading the write-ahead log 'sql_logs/sql/log.81841' 2016-07-02 22:20:36 MSG sb_traf[29578]: # Finished reading the write-ahead log 'sql_logs/sql/log.81841' 2016-07-02 22:20:36 MSG sb_traf[29578]: # Finished processing logs sql/sql_logs 2016-07-02 22:20:36 MSG merovingian[28268]: database 'sb_traf' (29578) was killed by signal SIGSEGV 2016-07-02 22:20:36 ERR merovingian[28268]: client error: database 'sb_traf' appears to shut itself down after starting, check monetdbd's logfile for possible hints 2016-07-02 22:20:37 MSG merovingian[28268]: starting database 'sb_traf', up min/avg/max: 11s/1d/3w, crash average: 1.00 0.50 0.50 (70-25=45) 2016-07-02 22:20:37 MSG sb_traf[29582]: arguments: /usr/bin/mserver5 --dbpath=/opt/monetdb/CUSTOMERnew/sb_traf --set merovingian_uri=mapi:monetdb://CUSTOMER-monetbd:54321/sb_traf --set mapi_open=false --set mapi_port=0 --set mapi_usock=/opt/monetdb/CUSTOMERnew/sb_traf/.mapi.sock --set monet_vault_key=/opt/monetdb/CUSTOMERnew/sb_traf/.vaultkey --set gdk_nr_threads=8 --set max_clients=64 --set sql_optimizer=default_pipe --set monet_daemon=yes 2016-07-02 22:20:37 MSG sb_traf[29582]: # MonetDB 5 server v11.21.19 "Jul2015-SP4" 2016-07-02 22:20:37 MSG sb_traf[29582]: # Serving database 'sb_traf', using 8 threads 2016-07-02 22:20:37 MSG sb_traf[29582]: # Compiled for x86_64-redhat-linux-gnu/64bit with 64bit OIDs and 128bit integers dynamically linked 2016-07-02 22:20:37 MSG sb_traf[29582]: # Found 20.000 GiB available main-memory. 2016-07-02 22:20:37 MSG sb_traf[29582]: # Copyright (c) 1993-July 2008 CWI. 2016-07-02 22:20:37 MSG sb_traf[29582]: # Copyright (c) August 2008-2015 MonetDB B.V., all rights reserved 2016-07-02 22:20:37 MSG sb_traf[29582]: # Visit http://www.monetdb.org/ for further information 2016-07-02 22:20:37 MSG sb_traf[29582]: # Listening for UNIX domain connection requests on mapi:monetdb:///opt/monetdb/CUSTOMERnew/sb_traf/.mapi.sock 2016-07-02 22:20:37 MSG sb_traf[29582]: # Start processing logs sql/sql_logs version 52200 2016-07-02 22:20:37 MSG sb_traf[29582]: # Start reading the write-ahead log 'sql_logs/sql/log.81841' 2016-07-02 22:20:37 MSG sb_traf[29582]: # Finished reading the write-ahead log 'sql_logs/sql/log.81841' 2016-07-02 22:20:37 MSG sb_traf[29582]: # Finished processing logs sql/sql_logs 2016-07-02 22:20:37 MSG merovingian[28268]: database 'sb_traf' (29582) was killed by signal SIGSEGV 2016-07-02 22:20:37 ERR merovingian[28268]: client error: database 'sb_traf' appears to shut itself down after starting, check monetdbd's logfile for possible hints

4 5

python map
by Stefano Fioravanzo 04 Jul '16

04 Jul '16

Hello, I have a long and complex embedded python function. This function depends on the single rows of the input columns, so I would like to parallelize it. I’ve read about the post on embedded python and python_map. I understand that using python_map should automatically split (in a not specified way) the input columns and fire different (how many?) python processes executing on their input slice. Finally it should pack all the partial results. Well, I have tried to use python_map, but nothing different happened. Is there something more I have to do apart from setting LANGUAGE PYTHON_MAP? Regards, Stefano

2 9