July 2016 - users-list

MERGE TABLE ERROR
by Ariel Abadi 20 Jul '16

20 Jul '16

Hi! We are facing a problem with MERGE Tables. We have a process in php which connects to the DB using the mapi ( https://www.monetdb.org/Documentation/Manuals/SQLreference/Programming/MAPI <https://www.google.com/url?q=https%3A%2F%2Fwww.monetdb.org%2FDocumentation%…> ) The process creates a new table everyday as follows: CREATE TABLE cub8_20160717 AS (SELECT * FROM cub8) WITH NO DATA; Then, attaches that new table to the MERGE table called cub8_cubo as follows: ALTER TABLE cub8_cubo ADD TABLE cub8_20160717 ; Once all this is created, it start inserting data. Everything works fine, with NO errors. BUT if I run a query against cub8_cubo I dont see cub8_20160717 data. So I run this query in order to check if the new table was attached to the merge: SELECT a.name, b.name, s.name FROM sys.tables as a JOIN sys.dependencies d ON a.id = d.depend_id JOIN sys.tables as b ON b.id= d.id JOIN sys.schemas as s ON a.schema_id = s.id WHERE a.type= 3 AND a.name ILIKE '%cub8%' ORDER BY 1,2; +-----------+---------------+ | name | name | +===========+===============+ | cub8_cubo | cub8_20160710 | | cub8_cubo | cub8_20160711 | | cub8_cubo | cub8_20160712 | | cub8_cubo | cub8_20160713 | | cub8_cubo | cub8_20160714 | | cub8_cubo | cub8_20160715 | | cub8_cubo | cub8_20160716 | | cub8_cubo | cub8_20160717 | So I try to detached from cub8_cubo, with the command: ALTER TABLE cub8_cubo DROP TABLE cub8_20160717; ALTER TABLE: table 'sb_traf.cub8_20160717' isn't part of the MERGE TABLE 'sb_traf.cub8_cubo' So I ADD it again: ALTER TABLE cub8_cubo ADD TABLE cub8_20160717; and now yes... I can see the data. If I need to detach that table, I need to run ALTER ... DROP twice or three times to get it done. It seems to be a bug?, are we doing something wrong ?? Thks in advance! *PS.:We are using * *MonetDB Database Server v1.7 (Jun2016)* *Welcome to mclient, the MonetDB/SQL interactive terminal (Jun2016)*

3 8

embedded py/R on stable release
by Stefano Fioravanzo 17 Jul '16

17 Jul '16

Hello, Are ambedded python/R included in the latest stable release of MonetDB or do I have to install the dev version from source? Thank you Regards, Stefano Fioravanzo

3 3

jdbc connection problems
by Anderson, David B 16 Jul '16

16 Jul '16

All, Do I need to start the server with any special options in order to connect to it via jdbc? I have installed the driver in DBVisualizer v9.1.7 and am using the URL jdbc:monetdb://mrsvm1d:50000/pytest but I keep getting connection refused errors from the driver (included below). When I connect locally via mclient, I have: Welcome to mclient, the MonetDB/SQL interactive terminal (unreleased) Database: MonetDB v11.24.0 (unreleased), 'mapi:monetdb://mrsvm1d:50000/pytest' Type \q to quit, \? for a list of available commands auto commit mode: on sql> Here is my server command line (do I need to change the mapi items?): /opt/mr/monetdb/bin/mserver5.bin --set gdk_nr_threads 3 --dbpath=/home/da64666/MonetDB/pytest --set merovingian_uri mapi:monetdb://mrsvm1d:50000/pytest --set mapi_open false --set mapi_port 0 --set mapi_usock /home/da64666/MonetDB/pytest/.mapi.sock --set monet_vault_key /home/da64666/MonetDB/pytest/.vaultkey --set gdk_nr_threads 2 --set max_clients 64 --set sql_optimizer default_pipe --set embedded_py true --set monet_daemon yes Thanks, Dave java.sql.SQLException: Unable to connect (mrsvm1d:50000): Connection refused: connect at nl.cwi.monetdb.jdbc.MonetConnection.<init>(MonetConnection.java:244) at nl.cwi.monetdb.jdbc.MonetDriver.connect(MonetDriver.java:166) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) at java.lang.reflect.Method.invoke(Unknown Source) at com.onseven.dbvis.g.B.D.?(Z:1413) at com.onseven.dbvis.g.B.F$A.call(Z:1474) at java.util.concurrent.FutureTask.run(Unknown Source) at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source) at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source) at java.lang.Thread.run(Unknown Source) SQLException: SQLState(08006) registerDriver: nl.cwi.monetdb.jdbc.MonetDriver@130b7ed registerDriver: nl.cwi.monetdb.jdbc.MonetDriver@7e673e registerDriver: nl.cwi.monetdb.jdbc.MonetDriver@115a9a7 registerDriver: nl.cwi.monetdb.jdbc.MonetDriver@1057d25 registerDriver: nl.cwi.monetdb.jdbc.MonetDriver@1abd8e3 registerDriver: nl.cwi.monetdb.jdbc.MonetDriver@63900c registerDriver: nl.cwi.monetdb.jdbc.MonetDriver@1ff6814 registerDriver: nl.cwi.monetdb.jdbc.MonetDriver@9a6101 registerDriver: nl.cwi.monetdb.jdbc.MonetDriver@74e0a3 registerDriver: nl.cwi.monetdb.jdbc.MonetDriver@a0148b registerDriver: nl.cwi.monetdb.jdbc.MonetDriver@1070f4 registerDriver: nl.cwi.monetdb.jdbc.MonetDriver@9701ad registerDriver: nl.cwi.monetdb.jdbc.MonetDriver@1b6da12 registerDriver: nl.cwi.monetdb.jdbc.MonetDriver@1769a58 registerDriver: nl.cwi.monetdb.jdbc.MonetDriver@4ea676 registerDriver: nl.cwi.monetdb.jdbc.MonetDriver@2d0f93 registerDriver: nl.cwi.monetdb.jdbc.MonetDriver@1c2163a registerDriver: nl.cwi.monetdb.jdbc.MonetDriver@1056ee7 registerDriver: nl.cwi.monetdb.jdbc.MonetDriver@1570d7b registerDriver: nl.cwi.monetdb.jdbc.MonetDriver@18b128d registerDriver: nl.cwi.monetdb.jdbc.MonetDriver@fae160 registerDriver: nl.cwi.monetdb.jdbc.MonetDriver@17d9594 registerDriver: nl.cwi.monetdb.jdbc.MonetDriver@cb361 registerDriver: nl.cwi.monetdb.jdbc.MonetDriver@c1f8d registerDriver: nl.cwi.monetdb.jdbc.MonetDriver@8fe97c registerDriver: nl.cwi.monetdb.jdbc.MonetDriver@1108377 registerDriver: nl.cwi.monetdb.jdbc.MonetDriver@faf9a3 registerDriver: nl.cwi.monetdb.jdbc.MonetDriver@1c77aba java.sql.SQLException: Unable to connect (mrsvm1d:50000): Connection refused: connect at nl.cwi.monetdb.jdbc.MonetConnection.<init>(MonetConnection.java:244) at nl.cwi.monetdb.jdbc.MonetDriver.connect(MonetDriver.java:166) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) at java.lang.reflect.Method.invoke(Unknown Source) at com.onseven.dbvis.g.B.D.?(Z:1413) at com.onseven.dbvis.g.B.F$A.call(Z:1474) at java.util.concurrent.FutureTask.run(Unknown Source) at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source) at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source) at java.lang.Thread.run(Unknown Source)

3 5

Re: monet crashes with query to VERY large table
by Sahasranaman M S 15 Jul '16

15 Jul '16

Hello, Just a suggestion, if you think the issue is related to the size of the data, then you could use a random data generator to test it. We were facing a similar issue with shareable data for testing. So I wrote a program called tqgen in Golang (https://github.com/niocs/tqgen). It can be used to generate large amounts of csv data for testing, and to share the data, we just need to share the command we used to generate it, and the other person can run it to generate the exact same data. It produces datewise fictional trade/quote data for fictional stocks. It puts each date's data into a separate file, and each file is around 128MB, so if we run it for a daterange of 1.5 years, it should generate around 70GB of data. It takes a while to finish because the data is not completely random and it is related to other rows. To generate, you could run: $ git clone https://github.com/niocs/tqgen $ cd tqgen $ go build tqgen.go $ ./tqgen --OutFilePat /path/to/data/tq.YYYYMMDD.csv --NumStk 1000 --Seed 234532 --DateBeg 20200101 --DateEnd 20210831 I usually load them one by one so that I could record load time stats, etc, but you could use this bash script ( https://github.com/niocs/hcsv/blob/master/hcat) to concatenate them without repeating the headers, and then load to MonetDB. (I will later add a parameter to tqgen to save to a single file) To do that, $ git clone https://github.com/niocs/hcsv $ hcsv/hcat /path/to/data/tq.20*.csv > /path/to/data/tq.big.csv If this causes the same issue, then others could regenerate the same data to try. Hope this helps. Thanks, Sahas > On Jul 14, 2016 10:20 AM, "Anthony Damico" <ajdamico(a)gmail.com> wrote: > >> can you winnow down the problem enough to construct a reproducible >> example? thanks >> >> On Thursday, July 14, 2016, Stefano Fioravanzo <fioravanzos(a)gmail.com> >> wrote: >> >>> Hello, >>> >>> No the data is not public, and testing in on another machine is not an >>> easy option right now. >>> >>> Regards, >>> >>> Stefano >>> >>> On 14 Jul 2016, at 15:49, Anthony Damico <ajdamico(a)gmail.com> wrote: >>> >>> hi, any chance these data are public? if not, can you test on a >>> separate box and see if the exact same thing happens or if it's >>> hardware-specific? >>> >>> On Thursday, July 14, 2016, Stefano Fioravanzo <fioravanzos(a)gmail.com> >>> wrote: >>> >>>> Hello, >>>> >>>> I currently have a table with 307524407 rows and 52 columns, it was >>>> loaded from a 78GB cvs file, but I do not know how much space the table >>>> actually takes on disk. >>>> If I query the table with a simple query like: >>>> >>>> select count(*) from big_table where field =1; >>>> >>>> mserver5 crashes with ‘segmentation fault' >>>> I tried also the following query (adding the LIMIT statement): >>>> select count(*) from farmap_movimento where farmacia_id =1 >>>> limit 10; >>>> >>>> and the output from mserver5 was: >>>> mserver5: gdk_select.c:869: fullscan_int: Assertion `cnt < >>>> (bn)->S->capacity' failed. >>>> Aborted >>>> >>>> Doing a more complex query (the one I am aiming for), with also a group >>>> by and a few aggregation functions (sum, min) on the fields, the outputs is: >>>> mserver5: gdk_bat.c:2066: BATsetcount: Assertion >>>> `b->S->capacity >= cnt' failed. >>>> Aborted >>>> >>>> This seems to be a table size problem, so…is there anything I can do? >>>> >>>> I am using MonetDB 5 server v11.22.0 >>>> The machine has 30GB of RAM and 60GB of swap. >>>> Available disk space: ~50GB >>>> >>>> Any help would be much appreciated, >>>> >>>> Regards, >>>> >>>> Stefano >>>> >>>> _______________________________________________ >>>> 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 >> >>

3 5

monet crashes with query to VERY large table
by Stefano Fioravanzo 14 Jul '16

14 Jul '16

Hello, I currently have a table with 307524407 rows and 52 columns, it was loaded from a 78GB cvs file, but I do not know how much space the table actually takes on disk. If I query the table with a simple query like: select count(*) from big_table where field =1; mserver5 crashes with ‘segmentation fault' I tried also the following query (adding the LIMIT statement): select count(*) from farmap_movimento where farmacia_id =1 limit 10; and the output from mserver5 was: mserver5: gdk_select.c:869: fullscan_int: Assertion `cnt < (bn)->S->capacity' failed. Aborted Doing a more complex query (the one I am aiming for), with also a group by and a few aggregation functions (sum, min) on the fields, the outputs is: mserver5: gdk_bat.c:2066: BATsetcount: Assertion `b->S->capacity >= cnt' failed. Aborted This seems to be a table size problem, so…is there anything I can do? I am using MonetDB 5 server v11.22.0 The machine has 30GB of RAM and 60GB of swap. Available disk space: ~50GB Any help would be much appreciated, Regards, Stefano

2 3

ROLE copy_role
by Alberto Ferrari 14 Jul '16

14 Jul '16

Hi all: In the new version of Monet (jun/2016), when normal USER (not "monetdb" user) runs "COPY INTO" or "INSERT", it only works with "SET ROLE copy_role;" before the query. Is that really needed? Should I prepend "SET ROLE copy_role" before every INSERT / COPY INTO query?? Doesn't make sense for me. I supposed the user/permissions configuration is enough for that? Thanks in advance.

1 0

adding binary columns to existing table
by Lynn Carol Johnson 14 Jul '16

14 Jul '16

Hi all - I've been using Monetdb's binary bulk loads and love it. Very fast, very efficient. We have a large database (2.3 billion rows) to which we frequently add new columns as new data is curated. To date, I've been re-creating the table each time, adding the new columns, and performing a binary bulk load for data in all columns. My question: Does monetdb allow for altering a table and then loading a binary file just for that column? Or is this not efficient as it requires an update on every row to add the new column's data? As our table columns reach into the hundreds it would be nice to "append" new columns, but perhaps logistically this isn't reasonable. Thanks for your insight - Lynn

1 0

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