July 2016 - users-list

Announcement: New Jun2016-SP1 Bugfix release of MonetDB suite
by Sjoerd Mullender 22 Jul '16

22 Jul '16

The MonetDB team at CWI/MonetDB BV is pleased to announce the Jun2016-SP1 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/>. Jun 2016-SP1 bugfix release . * Lots of memory leaks have been plugged across the whole system. Java Module * Corrected PROCEDURE_TYPE output value of method DatabaseMetaData.getProcedures(). It used to return procedureReturnsResult. Now it returns procedureNoResult. Corrected ORDINAL_POSITION output value of method DatabaseMetaData.getProcedureColumns(). It used to start with 0, but as procedures do not return a result value it now starts with 1 for all the procedure arguments, as defined by the JDBC API. * Improved output of method DatabaseMetaData.getProcedures(). The REMARKS column now contains the procedure definition as stored in sys.functions.func. The SPECIFIC_NAME column now contains the procedure unique identifier as stored in sys.functions.id. This allows the caller to retrieve the specific overloaded procedure which has the same name, but different arguments. Also improved output of method DatabaseMetaData.getProcedureColumns(). The SPECIFIC_NAME column now contains the procedure unique identifier as stored in sys.functions.id. This allows the caller to retrieve the proper arguments of the specific overloaded procedure by matching the SPECIFIC_NAME value. * Improved output of method DatabaseMetaData.getFunctions(). The REMARKS column now contains the function definition as stored in sys.functions.func. The SPECIFIC_NAME column now contains the function unique identifier as stored in sys.functions.id. This allows the caller to retrieve the specific overloaded function which has the same name, but different arguments. Also improved output of method DatabaseMetaData.getFunctionColumns(). The SPECIFIC_NAME column now contains the function unique identifier as stored in sys.functions.id. This allows the caller to retrieve the proper arguments of the specific overloaded function by matching the SPECIFIC_NAME value. Bug Fixes * 4014: KILL signal * 4021: Analyze query does not escape input [security] * 4026: JDBC driver incorrectly converts TINYINT fields to String instead of an integer type. * 4028: inputs not the same size * 4031: mclient doesn't accept - argument to refer to stdin * 4032: no decimal places after update. ODBC driver * 4035: SQL Function call bug * 4036: Possible sql_catalog corruption due to unclean backuped tail

1 0

Determining join order
by Moritz Bruder 20 Jul '16

20 Jul '16

Hi, in the process of writing a MAL backend for a query compiler, I'm trying to optimize the join order of arbitrary theta joins on whole relations (i.e. predicates may be arbitrary). To implement this, I use a simple heuristic for 2-way theta joins on relations: First the cheap predicates are identified, then the necessary joins are performed on the columns. After that all results of the cheap predicates are merged via joins on the OIDs. Finally, expensive predicates (i.e. with a lot of qualifying tuples) are implemented by applying a binary scalar operation and using a *subselect* and a *projection*/*leftfetchjoin*, since all columns are aligned now. To conclude: 2-way joins may not be atomic MAL joins (i.e. not column-wise), but they very well could be. So, what I'm looking for is some facility in MonetDB, with which I am able to break down a n-way join into 2-way joins, with the smallest possible intermediate results, without assuming too much about how those 2-way joins are implemented. The first thing I found was the *scheduler* module, which is also on the official website ( https://www.monetdb.org/Documentation/Manuals/MonetDB/Optimizers/Memoization ). But that seems no more up to date, since most of the functions are missing and the semantics of join operators in MAL has changed quite a bit. Is there anything else I can use for this task (aside from implementing some dynamic programming algorithm within MAL)? Kind regards, Moritz

1 0

Limits on loaded shared libraries
by Anderson, David B 20 Jul '16

20 Jul '16

All, I was planning on loading a shared library within either an R or Python function inside a MonetDB server. Is this supported? What kinds of constraints do I need to worry about? Guessing any memory problems will impact the server, but not sure about thread safety etc. Thanks, Dave

1 0

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