- users-list - monetdb.org

update on tqgen
by Sahasranaman M S 07 Sep '16

07 Sep '16

Hi all, Just an update on *tqgen*, in case anyone is interested - The latest master in github.com/niocs/tqgen now runs around 8 times faster than the earlier version. Also, in the OutFilePat parameter, if there is no YYYYMMDD, then all data goes to a single file, unlike earlier when the file kept getting overwritten. The documentation on the gh page has also been updated. If there is anything that can be done to make it more useful, please let me know and I will try to add it. Thanks, Sahas On Thu, Jul 14, 2016 at 9:29 PM, Sahasranaman M S <sahasr(a)naman.ms> wrote: > Actually, I just remembered tqgen has a couple of other package > dependencies from github, so the best way to download and build it is to > use this command: > > $ go get github.com/niocs/tqgen > $ cd $GOPATH/bin > $ ./tqgen --OutFilePat /path/to/data/tq.YYYYMMDD.csv --NumStk 1000 > --Seed 234532 --DateBeg 20200101 --DateEnd 20210831 > > Thanks, > Sahas > > On Thu, Jul 14, 2016 at 9:18 PM, Sahasranaman M S <sahasr(a)naman.ms> wrote: > >> 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 >>>> >>>> >> >

1 0

Merovingian: allow binds to given ip/interface
by Robert Koch 06 Sep '16

06 Sep '16

Referring to comment #13 of this bug report, I would like to know where exactly I can config listening to 0.0.0.0 instead of 127.0.0.1. As of Jun2016 relaease I cannot connect to Port 50000 from foreign hosts any more. https://www.monetdb.org/bugzilla/show_bug.cgi?id=2407#c13 Thank you Robert

2 1

loopback queries in embedded R
by Stefano Fioravanzo 05 Sep '16

05 Sep '16

Hello, A few months ago I was told that loopback queries for embedded R were not ready yet, but would be after some time. Is the feature ready? If so, how does it work? (I am familiar with loopback queries with embedded python) I am currently using the development default branch, compiled a couple of weeks ago. Thank you, Stefano

2 2

error while entering a newly created database
by Xu,Wenjian 01 Sep '16

01 Sep '16

Hi all, I have downloaded the latest version MonetDB-11.23.7 and built it from source. But I have encountered errors while entering a newly created database: =========================================================== $ monetdbd start ~/monetdb_dbfarm2 $ monetdb create topk_synthetic $ monetdb release topk_synthetic $ mclient -dtopk_synthetic *CREATE FUNCTION: name 'md5' (clob) already in use* *ParseException:SQLparser:CREATE FUNCTION: name 'md5' (clob) already in use* =========================================================== Logs are as follows: >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> 2016-09-01 09:45:21 MSG merovingian[30268]: starting database 'topk_synthetic', up min/avg/max: 0s/0s/0s, crash average: 0.00 0.00 0.00 (0-0=0) 2016-09-01 09:45:22 MSG topk_synthetic[30287]: arguments: /usr/local/bin/mserver5 --dbpath=/home/zeroxwj/monetdb_dbfarm2/topk_synthetic --set merovingian_uri=mapi:monetdb://SourceCode:50000/topk_synthetic --set mapi_open=false --set mapi_port=0 --set mapi_usock=/home/zeroxwj/monetdb_dbfarm2/topk_synthetic/.mapi.sock --set monet_vault_key=/home/zeroxwj/monetdb_dbfarm2/topk_synthetic/.vaultkey --set gdk_nr_threads=8 --set max_clients=64 --set sql_optimizer=default_pipe --set monet_daemon=yes 2016-09-01 09:45:22 MSG topk_synthetic[30287]: # MonetDB 5 server v11.23.7 "Jun2016-SP1" 2016-09-01 09:45:22 MSG topk_synthetic[30287]: # Serving database 'topk_synthetic', using 8 threads 2016-09-01 09:45:22 MSG topk_synthetic[30287]: # Compiled for x86_64-unknown-linux-gnu/64bit with 64bit OIDs and 128bit integers dynamically linked 2016-09-01 09:45:22 MSG topk_synthetic[30287]: # Found 15.625 GiB available main-memory. 2016-09-01 09:45:22 MSG topk_synthetic[30287]: # Copyright (c) 1993-July 2008 CWI. 2016-09-01 09:45:22 MSG topk_synthetic[30287]: # Copyright (c) August 2008-2016 MonetDB B.V., all rights reserved 2016-09-01 09:45:22 MSG topk_synthetic[30287]: # Visit http://www.monetdb.org/ for further information 2016-09-01 09:45:22 MSG topk_synthetic[30287]: # Listening for UNIX domain connection requests on mapi:monetdb:///home/zeroxwj/monetdb_dbfarm2/topk_synthetic/.mapi.sock 2016-09-01 09:45:22 MSG topk_synthetic[30287]: # MonetDB/SQL module loaded 2016-09-01 09:45:22 MSG merovingian[30268]: proxying client (local) for database 'topk_synthetic' to mapi:monetdb:///home/zeroxwj/monetdb_dbfarm2/topk_synthetic/.mapi.sock?database=topk_synthetic 2016-09-01 09:45:22 MSG merovingian[30268]: target connection is on local UNIX domain socket, passing on filedescriptor instead of proxying 2016-09-01 09:45:22 MSG topk_synthetic[30287]: # SQL catalog created, loading sql scripts once 2016-09-01 09:45:22 MSG topk_synthetic[30287]: # loading sql script: 09_like.sql 2016-09-01 09:45:22 MSG topk_synthetic[30287]: # loading sql script: 10_math.sql 2016-09-01 09:45:22 MSG topk_synthetic[30287]: # loading sql script: 11_times.sql 2016-09-01 09:45:22 MSG topk_synthetic[30287]: # loading sql script: 12_url.sql 2016-09-01 09:45:22 MSG topk_synthetic[30287]: # loading sql script: 13_date.sql 2016-09-01 09:45:22 MSG topk_synthetic[30287]: # loading sql script: 14_inet.sql 2016-09-01 09:45:22 MSG topk_synthetic[30287]: # loading sql script: 15_querylog.sql 2016-09-01 09:45:22 MSG topk_synthetic[30287]: # loading sql script: 16_tracelog.sql 2016-09-01 09:45:22 MSG topk_synthetic[30287]: # loading sql script: 17_temporal.sql 2016-09-01 09:45:22 MSG topk_synthetic[30287]: # loading sql script: 19_cluster.sql 2016-09-01 09:45:22 MSG topk_synthetic[30287]: # loading sql script: 20_vacuum.sql 2016-09-01 09:45:22 MSG topk_synthetic[30287]: # loading sql script: 21_dependency_functions.sql 2016-09-01 09:45:22 MSG topk_synthetic[30287]: # loading sql script: 22_clients.sql 2016-09-01 09:45:22 MSG topk_synthetic[30287]: # loading sql script: 23_skyserver.sql 2016-09-01 09:45:22 MSG topk_synthetic[30287]: # loading sql script: 24_zorder.sql 2016-09-01 09:45:22 MSG topk_synthetic[30287]: # loading sql script: 25_debug.sql 2016-09-01 09:45:22 MSG topk_synthetic[30287]: # loading sql script: 26_sysmon.sql 2016-09-01 09:45:22 MSG topk_synthetic[30287]: # loading sql script: 27_rejects.sql 2016-09-01 09:45:22 MSG topk_synthetic[30287]: # loading sql script: 39_analytics.sql 2016-09-01 09:45:22 MSG topk_synthetic[30287]: # loading sql script: 39_analytics_hge.sql 2016-09-01 09:45:22 MSG topk_synthetic[30287]: # loading sql script: 40_json.sql 2016-09-01 09:45:22 MSG topk_synthetic[30287]: # loading sql script: 40_json_hge.sql 2016-09-01 09:45:22 MSG topk_synthetic[30287]: # loading sql script: 41_jsonstore.sql 2016-09-01 09:45:22 MSG topk_synthetic[30287]: # loading sql script: 41_md5sum.sql 2016-09-01 09:45:22 MSG merovingian[30268]: proxying client localhost:54853 for database 'topk_synthetic' to mapi:monetdb:///home/zeroxwj/monetdb_dbfarm2/topk_synthetic/.mapi.sock?database=topk_synthetic 2016-09-01 09:45:22 MSG merovingian[30268]: target connection is on local UNIX domain socket, passing on filedescriptor instead of proxying 2016-09-01 09:45:22 MSG topk_synthetic[30287]: # SQL catalog created, loading sql scripts once 2016-09-01 09:45:22 MSG topk_synthetic[30287]: # loading sql script: 09_like.sql 2016-09-01 09:45:22 MSG topk_synthetic[30287]: # loading sql script: 10_math.sql 2016-09-01 09:45:22 MSG topk_synthetic[30287]: # loading sql script: 11_times.sql 2016-09-01 09:45:22 MSG topk_synthetic[30287]: # loading sql script: 12_url.sql 2016-09-01 09:45:22 MSG topk_synthetic[30287]: # loading sql script: 13_date.sql 2016-09-01 09:45:22 MSG topk_synthetic[30287]: # loading sql script: 14_inet.sql 2016-09-01 09:45:22 MSG topk_synthetic[30287]: # loading sql script: 15_querylog.sql 2016-09-01 09:45:22 MSG topk_synthetic[30287]: # loading sql script: 16_tracelog.sql 2016-09-01 09:45:22 MSG topk_synthetic[30287]: # loading sql script: 17_temporal.sql 2016-09-01 09:45:22 MSG topk_synthetic[30287]: # loading sql script: 19_cluster.sql 2016-09-01 09:45:22 MSG topk_synthetic[30287]: # loading sql script: 20_vacuum.sql 2016-09-01 09:45:22 MSG topk_synthetic[30287]: # loading sql script: 21_dependency_functions.sql 2016-09-01 09:45:22 MSG topk_synthetic[30287]: # loading sql script: 22_clients.sql 2016-09-01 09:45:22 MSG topk_synthetic[30287]: # loading sql script: 23_skyserver.sql 2016-09-01 09:45:22 MSG topk_synthetic[30287]: # loading sql script: 24_zorder.sql 2016-09-01 09:45:22 MSG topk_synthetic[30287]: # loading sql script: 25_debug.sql 2016-09-01 09:45:22 MSG topk_synthetic[30287]: # loading sql script: 26_sysmon.sql 2016-09-01 09:45:22 MSG topk_synthetic[30287]: # loading sql script: 27_rejects.sql 2016-09-01 09:45:22 MSG topk_synthetic[30287]: # loading sql script: 39_analytics.sql 2016-09-01 09:45:22 MSG topk_synthetic[30287]: # loading sql script: 39_analytics_hge.sql 2016-09-01 09:45:22 MSG topk_synthetic[30287]: # loading sql script: 40_json.sql 2016-09-01 09:45:22 MSG topk_synthetic[30287]: # loading sql script: 40_json_hge.sql 2016-09-01 09:45:22 MSG topk_synthetic[30287]: # loading sql script: 41_jsonstore.sql 2016-09-01 09:45:22 MSG topk_synthetic[30287]: # loading sql script: 41_md5sum.sql >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> As far as I understand, those sql scripts (e.g., 09_like.sql) are for initialization purpose. What is the reason that it loads those scripts twice? BTW, I can start an existing database successfully. I have never encountered such errors before. Any suggestion would be helpful. Thanks. Best regards, Wenjian

1 0

cannot access db with mserver5
by Stefano Fioravanzo 31 Aug '16

31 Aug '16

Hello, I have been using monetdb starting my db farm with monetdbd and it has always worked fine. I have tried to start manually mserver5 with the command mserver5 --set embedded_py=true --dbpath=/path/to/db --daemon=yes —set mapi_open=true The process starts correctly, but when I try to launch mclient it says InvalidCredentialsException:checkCredentials:invalid credentials for user ‘monetdb’, what can I do? I want to start the database with mserver5 because I need remote access (—set mapi_open=true), is there a way to enable this flag using monetdbd? Thanks, Stefano

3 3

GEOM Module installed in Sys Schema
by Vincent Sheffer 30 Aug '16

30 Aug '16

It seems that the functions of the Geom module is only accessible from the sys schema. That further implies that only users with access to the sys schema can call Geom functions. What is the best way to ensure that all users can access the functions from the Geom module? Geom types seem to work fine. 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.

2 1

segfault remote table count: could not find L1.L1 NOT NULL as L1 L1.L1
by Anton Kravchenko 25 Aug '16

25 Aug '16

Hi there, when I run 'count' on remote table I get a segfault on worker server: 'could not find L1.L1 NOT NULL as L1 L1.L1 Segmentation fault' Here is master(server/client) and worker (server/client), all are living on localhost (as it was shown in example https://www.monetdb.org/Documentation/Cookbooks/SQLrecipes/DistributedQuery… ). master client [akravchenko@hnode1 ~]$ mclient -d mst Welcome to mclient, the MonetDB/SQL interactive terminal (Jun2016-SP1) Database: MonetDB v11.23.7 (Jun2016-SP1), 'mst' Type \q to quit, \? for a list of available commands auto commit mode: on sql>CREATE REMOTE TABLE t1 (i int) on 'mapi:monetdb://localhost:50001/rt1'; operation successful (1.357ms) sql>\d REMOTE TABLE sys.t1 sql>select * from t1; +------+ | i | +======+ | 11 | | 13 | +------+ 2 tuples (16.205ms) *sql>select count(*) from t1;* *an error occurred on connection: Connection lost* *sql>* worker client [akravchenko@hnode1 ~]$ mclient -d rt1 -p 50001 Welcome to mclient, the MonetDB/SQL interactive terminal (Jun2016-SP1) Database: MonetDB v11.23.7 (Jun2016-SP1), 'rt1' Type \q to quit, \? for a list of available commands auto commit mode: on sql>\d TABLE sys.t1 sql>select count(*) from t1; +------+ | L1 | +======+ | 2 | +------+ 1 tuple (0.594ms) worker server [akravchenko@hnode1 ~]$ mserver5 --dbpath=rt1 --set mapi_port=50001 # MonetDB 5 server v11.23.7 "Jun2016-SP1" # Serving database 'rt1', using 4 threads # Compiled for x86_64-redhat-linux-gnu/64bit with 64bit OIDs and 128bit integers dynamically linked # Found 31.357 GiB available main-memory. # Copyright (c) 1993-July 2008 CWI. # Copyright (c) August 2008-2016 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:50001/ # MonetDB/SQL module loaded *>could not find L1.L1 NOT NULL as L1* *L1.L1* *Segmentation fault* master server [akravchenko@hnode1 ~]$ mserver5 --dbpath=mst # MonetDB 5 server v11.23.7 "Jun2016-SP1" # Serving database 'mst', using 4 threads # Compiled for x86_64-redhat-linux-gnu/64bit with 64bit OIDs and 128bit integers dynamically linked # Found 31.357 GiB available main-memory. # Copyright (c) 1993-July 2008 CWI. # Copyright (c) August 2008-2016 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/SQL module loaded Thank you, Anton

2 3

Is it possible to access two databases in a single select?
by Pablo S 25 Aug '16

25 Aug '16

Is this possible in MonetDB? select * from database1.table1 t1, database2.table2 t2 where t1.col1 = t2.col2 where database1 and database2 are two databases running simultaneously.

2 1

Concurrent query execution issue
by Stefano Fioravanzo 24 Aug '16

24 Aug '16

Hello, I am trying to execute in parallel a few queries in MonetDB. I have read in the documentation that Monet uses the OCC transaction model so read operations should not block the table. I have 3 CREATE TABLE queries that read from the same source table, I launch 3 different mclient processes and launch one query on each client. The queries execute in parallel as expected, I can see them running in sys.queue(). The first query that completes, creates the table and commits fine. When the other two queries finish their job I can see the message “operation successful” but right after the client prints "COMMIT: transaction is aborted because of concurrency conflicts, will ROLLBACK instead”. What am I doing wrong? Apart from the newly created table, I did non write anything to the db so it should work fine. Any suggestion would be much appreciated, Thanks, Stefano

2 2

Run two MonetDB databases simoultaneously in Windows
by Pablo S 21 Aug '16

21 Aug '16

I'm trying to run two MonetDB databases simoultaneously in Windows. I named the two databases mydb and mydb2. I changed the database name in M5server.bat to mydb, and added -database=mydb to mclient.bat Then I copied the M5server.bat file and changed the farm to mydb2. I also copied the mclient.bat file and added -database=mydb2. I run the two server bats to start the two databases and that seems to work fine (no errors). Then I run the first mclient.bat and it connects to the server with no issues. But when I run the copied mclient.bat and try to connect using user/password monetdb/monetdb, I get the following error and cannot connect: request for database 'mydb2', but this is database 'mydb', did you mean to connect to monetdbd instead? How to run two databases simoultaneously in Windows? How to connect to two databases at the same time?

3 2

Program contains errors
by Stefano Fioravanzo 19 Aug '16

19 Aug '16

Hello, I will briefly recap the problem I have already reported a few weeks ago: I am using monetdb default branch (I did not compile the stable version because I need embedded python) and I have a large table (~80 GB). A simple query like SELECT * FROM large_table WHERE field=x, fails. mserver5 reports this error: mserver5: gdk_select.c:867: fullscan_int: Assertion `cnt < (bn)->S.capacity' failed. It seems that monet thinks it doesn’t have enough space to do a full scan of the table, maybe? (The machine has 800GB of free disk space and 30GB of RAM - Ubuntu 14.04). I was using a default branch of december 2015, I have tried to use the latest default branch (4/08/16), recompiled, installed but nothing, always the same error. So, as suggested, I have compiled the latest stable release (Jun2016-SP1). Now even a query which does not require monet to read the whole table (like SELECT * FROM large_table LIMIT 10 - This query worked fine in the default branch version) fails with ‘Program contains error’ message by client. Selecting a single column of the table works just fine. Moreover, with the stable release, the same issue arises with the second biggest table of the database (~35 GB). [Here you can find the configuration of MonetDB: http://pastie.org/private/r8gtssg944aqwnigrysa] All queries on all the other tables in the database (they are all much smaller than these two) work fine. I do not understand if the issue is in the size of table or somewhere else. How can I know what ‘error’ the message ‘program contains errors’ is referring to? NOTE: At the beginning, using the default compiled branch everything worked just fine! Every query on my large_table worked as expected. Then I had to recreate the database from scratch so I deleted the dbfarm and created a new one. That’s when this headache started… At this point I don’t know what to do, any help would be MUCH appreciated. Thanks, Stefano

4 7

synchronize data from mysql performance
by wang.tong＠shomop.com 19 Aug '16

19 Aug '16

Hi, I try to synchronize data from mysql to monetdb. Steps: 1. Read the modified rows from mysql table periodically. 2. Delete the rows in monetdb by primary key. (To avoid 'PRIMARY KEY constraint violated' error.) The sql is like 'delete from a where id in (1, 2, 3)'. 3. Insert the rows into monetdb by 'copy into ... from stdin'. I find step 2 may be too slow. When there are 2 million rows, delete 10,000 rows takes about 1 minute. When there are 30 million rows, delete 10,000 rows takes about 20 minute. I have to do join on the 30 million rows table, so I cannot split it. I try to upgrade the server from 2 core 8 G memory to 8 core 32 G memory, but it has no effect. It seems the limitation is CPU, and the deleted operation can only use 1 core. How can I optimize the performance? Is there any better approach to synchronize data? Thanks in advance! OS version: centos-release-7-0.1406.el7.centos.2.5.x86_64 mserver5 --version MonetDB 5 server v11.23.7 "Jun2016-SP1" (64-bit, 64-bit oids, 128-bit integers) Copyright (c) 1993-July 2008 CWI Copyright (c) August 2008-2016 MonetDB B.V., all rights reserved Visit http://www.monetdb.org/ for further information Found 31.0GiB available memory, 8 available cpu cores Libraries: libpcre: 8.32 2012-11-30 (compiled with 8.32) openssl: OpenSSL 1.0.1e 11 Feb 2013 (compiled with OpenSSL 1.0.1e-fips 11 Feb 2013) libxml2: 2.9.1 (compiled with 2.9.1) Compiled by: root@jstu87d9w6zj4u (x86_64-unknown-linux-gnu) Compilation: gcc -g -O2 Linking : /usr/bin/ld -m elf_x86_64 wang.tong(a)shomop.com

2 2

database was killed by signal SIGSEGV
by 王彤 19 Aug '16

19 Aug '16

Hi, I try to synchronize data from mysql to monetdb. Total rows is about 15 million. I insert the rows in batch. Each batch has about 10000 rows. I use 'copy into from stdin' as in http://dev.monetdb.org/hg/MonetDB/file/tip/java/example/SQLcopyinto.java . The virtual memory continuously grows. When it grows to about 80G, the server is killed by signal SIGSEGV. The database size on disk is about 30GB. Below is the server infomation and error log. Any help would be greatly appreciated! OS version: centos-release-7-0.1406.el7.centos.2.5.x86_64 mserver5 --version MonetDB 5 server v11.23.7 "Jun2016-SP1" (64-bit, 64-bit oids, 128-bit integers) Copyright (c) 1993-July 2008 CWI Copyright (c) August 2008-2016 MonetDB B.V., all rights reserved Visit http://www.monetdb.org/ for further information Found 7.4GiB available memory, 2 available cpu cores Libraries: libpcre: 8.32 2012-11-30 (compiled with 8.32) openssl: OpenSSL 1.0.1e 11 Feb 2013 (compiled with OpenSSL 1.0.1e-fips 11 Feb 2013) libxml2: 2.9.1 (compiled with 2.9.1) Compiled by: root@jstu87d9w6zj4u (x86_64-unknown-linux-gnu) Compilation: gcc -g -O2 Linking : /usr/bin/ld -m elf_x86_64 in /data/dbfarm/merovingian.log 2016-08-18 17:29:04 ERR shomopcrm[10246]: #GDKmmap(38862848) fails, try to free up space [memory in use=98280928,virtual memory in use=3779503584] 2016-08-18 17:29:04 ERR shomopcrm[10246]: #GDKmmap(38862848) result [mem=98280928,vm=3779503584] 2016-08-18 17:29:04 MSG merovingian[10194]: proxying client 10.173.87.201:46953 for database 'shomopcrm' to mapi:monetdb:///data/dbfarm/shomopcrm/.mapi.sock?database=shomopcrm 2016-08-18 17:29:04 MSG merovingian[10194]: target connection is on local UNIX domain socket, passing on filedescriptor instead of proxying 2016-08-18 17:29:04 MSG merovingian[10194]: proxying client 10.153.193.249:55915 for database 'shomopcrm' to mapi:monetdb:///data/dbfarm/shomopcrm/.mapi.sock?database=shomopcrm 2016-08-18 17:29:04 MSG merovingian[10194]: target connection is on local UNIX domain socket, passing on filedescriptor instead of proxying 2016-08-18 17:29:04 ERR shomopcrm[10246]: #GDKmmap(38862848) fails, try to free up space [memory in use=185008352,virtual memory in use=3840344288] 2016-08-18 17:29:04 ERR shomopcrm[10246]: #GDKmmap(38862848) result [mem=185008352,vm=3840344288] 2016-08-18 17:29:04 MSG merovingian[10194]: proxying client 10.173.87.201:46963 for database 'shomopcrm' to mapi:monetdb:///data/dbfarm/shomopcrm/.mapi.sock?database=shomopcrm 2016-08-18 17:29:04 MSG merovingian[10194]: target connection is on local UNIX domain socket, passing on filedescriptor instead of proxying 2016-08-18 17:29:04 ERR shomopcrm[10246]: #GDKmmap(51838976) fails, try to free up space [memory in use=99455712,virtual memory in use=3766391520] 2016-08-18 17:29:04 ERR shomopcrm[10246]: #GDKmmap(51838976) result [mem=99455712,vm=3766391520] 2016-08-18 17:29:04 MSG merovingian[10194]: database 'shomopcrm' (10246) was killed by signal SIGSEGV 2016-08-18 17:29:04 MSG merovingian[10194]: starting database 'shomopcrm', up min/avg/max: 17s/15h/5d, crash average: 1.00 0.90 0.40 (23-11=12) 2016-08-18 17:29:05 MSG shomopcrm[984]: arguments: /usr/local/bin/mserver5 --dbpath=/data/dbfarm/shomopcrm --set merovingian_uri=mapi:monetdb://jstu87d9w6zj4u:30001/shomopcrm --set mapi_open=false --set mapi_port=0 --set mapi_usock=/data/dbfarm/shomopcrm/.mapi.sock --set monet_vault_key=/data/dbfarm/shomopcrm/.vaultkey --set gdk_nr_threads=2 --set max_clients=64 --set sql_optimizer=default_pipe --set monet_daemon=yes 2016-08-18 17:29:05 MSG shomopcrm[984]: # MonetDB 5 server v11.23.7 "Jun2016-SP1" 2016-08-18 17:29:05 MSG shomopcrm[984]: # Serving database 'shomopcrm', using 2 threads 2016-08-18 17:29:05 MSG shomopcrm[984]: # Compiled for x86_64-unknown-linux-gnu/64bit with 64bit OIDs and 128bit integers dynamically linked 2016-08-18 17:29:05 MSG shomopcrm[984]: # Found 7.390 GiB available main-memory. 2016-08-18 17:29:05 MSG shomopcrm[984]: # Copyright (c) 1993-July 2008 CWI. 2016-08-18 17:29:05 MSG shomopcrm[984]: # Copyright (c) August 2008-2016 MonetDB B.V., all rights reserved 2016-08-18 17:29:05 MSG shomopcrm[984]: # Visit http://www.monetdb.org/ for further information 2016-08-18 17:29:05 MSG shomopcrm[984]: # Listening for UNIX domain connection requests on mapi:monetdb:///data/dbfarm/shomopcrm/.mapi.sock 2016-08-18 17:29:05 MSG shomopcrm[984]: # MonetDB/SQL module loaded in /var/log/messages, there is an error: kernel: mserver5[981]: segfault at 18 ip 00007fe10f845a89 sp 00007fcf38e79760 error 4 in lib_sql.so[7fe10f817000+175000] wang.tong(a)shomop.com

2 1

Distributed Query Processing questions
by Anton Kravchenko 18 Aug '16

18 Aug '16

Hi there, I have few questions on merge/remote tables. Are there known MonetDB users that use(d) merge/remote tables in production? Are there official MonetDB performance benchmarks for merge/remote vs local tables? Thank you, Anton

2 1

distributed query processing example
by Anton Kravchenko 16 Aug '16

16 Aug '16

Hi there, I am trying to follow a distributed query processing example from https://www.monetdb.org/Documentation/Cookbooks/SQLrecipes/DistributedQuery… and here is my experience: Welcome to mclient, the MonetDB/SQL interactive terminal (Jun2016-SP1) Database: MonetDB v11.23.7 (Jun2016-SP1), 'mapi:monetdb://hnode3.secdev.local:52018/db3' Type \q to quit, \? for a list of available commands auto commit mode: on sql>CREATE MERGE TABLE mt1 (t int); operation successful (2.367ms) sql>CREATE REMOTE TABLE t1 (i int) on 'mapi:monetdb://hnode1.secdev.local:52016/db1'; operation successful (1.287ms) sql>select * from t1; +------+ | i | +======+ | 11 | | 13 | +------+ 2 tuples (4.694ms) *sql>select count(*) from t1;* *Connection lost* *sql>select count(*) from t1;* *illegal input, not a JSON header (got '')* *sql>select count(*) from t1;* *Connection lost* *sql>select count(*) from t1;* *Connection lost* *sql>select count(*) from t1;* *illegal input, not a JSON header (got '')* *sql>select count(*) from t1;* *illegal input, not a JSON header (got '')* *sql>select count(*) from t1;* *an error occurred on connection: Connection lost* *sql>select count(*) from t1;* *Connection lost* sql>select * from t1; +------+ | i | +======+ | 11 | | 13 | +------+ 2 tuples (508.682ms) sql>select * from t1; +------+ | i | +======+ | 11 | | 13 | +------+ 2 tuples (6.227ms) sql>select * from t1; +------+ | i | +======+ | 11 | | 13 | +------+ 2 tuples (5.065ms) sql>select * from t1; +------+ | i | +======+ | 11 | | 13 | +------+ 2 tuples (4.178ms) sql> P.S. Is there something wrong with my monetdb cluster setup or is it a real bug? On hnode3 (CentOS 6.8 machine): [akravchenko@hnode3 ~]$ monetdbd -v MonetDB Database Server v1.7 (Jun2016-SP1) [akravchenko@hnode3 ~]$ monetdb discover location hnode3.secdev.local:52018* mapi:monetdb://hnode3.secdev.local:52018/db3 [akravchenko@hnode3 ~]$ monetdb status name state health remarks db3 R 20h 100% 11m mapi:monetdb://hnode3.secdev.local:52018/db3 [akravchenko@hnode3 ~]$ monetdbd get all hnode3-dbfarm property value hostname hnode3.secdev.local dbfarm hnode3-dbfarm status monetdbd[18052] 1.7 (Jun2016-SP1) is serving this dbfarm mserver /usr/bin/mserver5 logfile hnode3-dbfarm/merovingian.log pidfile hnode3-dbfarm/merovingian.pid sockdir /tmp listenaddr 0.0.0.0 port 52018 exittimeout 60 forward proxy discovery yes discoveryttl 600 control yes passphrase {SHA512}a73f1d86383446438ac64f56e15ada38b41fbb18f029d2181723aeb2acac6a831f60e5fdbd64ac2c8c70e035dd44cbbe3b45565ef2d58feb2821a2078c7fad35 mapisock /tmp/.s.monetdb.52018 controlsock /tmp/.s.merovingian.52018 and on hnode1 (CentOS 6.8 machine): [akravchenko@hnode1 ~]$ monetdb -v MonetDB Database Server Toolkit v1.1 (Jun2016-SP1) [akravchenko@hnode1 ~]$ monetdb discover location hnode1.secdev.local:52016* mapi:monetdb://hnode1.secdev.local:52016/db1 [akravchenko@hnode1 ~]$ monetdb status name state health remarks db1 R 4m 15% 32m mapi:monetdb://hnode1.secdev.local:52016/db1 [akravchenko@hnode1 ~]$ monetdbd get all hnode1-dbfarm/ property value hostname hnode1.secdev.local dbfarm hnode1-dbfarm/ status monetdbd[1355] 1.7 (Jun2016-SP1) is serving this dbfarm mserver /usr/bin/mserver5 logfile hnode1-dbfarm//merovingian.log pidfile hnode1-dbfarm//merovingian.pid sockdir /tmp listenaddr 0.0.0.0 port 52016 exittimeout 60 forward proxy discovery yes discoveryttl 600 control yes passphrase {SHA512}a73f1d86383446438ac64f56e15ada38b41fbb18f029d2181723aeb2acac6a831f60e5fdbd64ac2c8c70e035dd44cbbe3b45565ef2d58feb2821a2078c7fad35 mapisock /tmp/.s.monetdb.52016 controlsock /tmp/.s.merovingian.52016 [akravchenko@hnode1 ~]$ Thank you, Anton

1 0

Add a new optimizer pipeline.
by Xu,Wenjian 10 Aug '16

10 Aug '16

Hi all, MonetDB version: 11.23.7 Ubuntu version: 14.04 I created a new optimizer pipeline by adding an additional optimization step after 'default_pipe', i.e.: '*my_customized_pipe*'='optimizer.inline();optimizer.candidates(); optimizer.remap();optimizer.costModel();optimizer.coercions(); optimizer.evaluate();optimizer.pushselect();optimizer.aliases(); optimizer.mitosis();optimizer.mergetable();optimizer.deadcode(); optimizer.aliases();optimizer.constants();optimizer.commonTerms(); optimizer.projectionpath();optimizer.deadcode();optimizer.reorder(); optimizer.reduce();optimizer.matpack();optimizer.dataflow(); optimizer.querylog();optimizer.multiplex();optimizer.generator(); optimizer.profiler();optimizer.garbageCollector(); *optimizer.customizedstep()*' What I have done is: 1) Create an empty optimizer step: -- 1.1 add *opt_customizedstep.c/h* in package *monetdb/optimizer/**, leave function body *OPTcustomizedstepImplementation() *empty*, *i.e. with one statement **return 0** * -- *1.2 register the step by modifying *opt_wrapper.c *and* optimizer.mal* 2) Add the definition of the new pipeline in *opt_pipes.c* 3) Modify the configuration file to compile the added source files. After re-build the system, I can enable the new optimizer pipeline by *set optimizer='my_customized_pipe'*. However, I have two issues: i) using EXPLAIN statement, I found the MAL plan different from the one generated by 'default_pipe' (which should be the same since I did nothing in the added step?). Specifically, the plan seems to be not affected by *optimizer.reorder() *step, i.e., bat.new() and bat.append() instructions appear in the end of the plan. ii) then I entered mdb mode by DEBUG statement. But it seems that some command did not work as before. For example, *help* command does not display all commands available; *optimizer* command does not display actions performed by each optimizer step. What's wrong with my procedure to add a new optimizer pipeline? Any comment would be helpful. Best regards, Wenjian

3 3

[Low level] Understanding duplicate entries in delta BAT (delete BAT)
by Frédéric Jolliton 08 Aug '16

08 Aug '16

Hi, As per MonetDB Solution suggestion, we are asking here about this topic. We are studying how MonetDB store its database on disk. So far we were able to understand pretty much all the files (BAT, journal, BBP.dir, heap, hash, imprints, and so on). We have custom tools to decode and dump them (experimental at this point). There is one thing however that is unclear: we thought that "delta BAT", which are the BAT assigned to an SQL tables to list the OIDs of rows that were deleted (named "D_<schema>_<table>" internaly), would contains only *uniques* values (since you can never delete twice the same row). But on several databases that we are running, we found that some of theses BATs contain duplicates. Lot of duplicates actually. Especially on D_sys__columns and D_sys__tables BATs (respectively for the sys._columns and sys._tables system tables). Some databases do not have this "issue" (while they all have the same schema, and process the same kind of data as the other ones). Can someone explain if duplicates are expected in theses BATs ? Here is an excerpt of D_sys__tables: # hexdump -C 02/210.tail 00000000 2e 00 00 00 00 00 00 00 2f 00 00 00 00 00 00 00 |......../.......| 00000010 30 00 00 00 00 00 00 00 31 00 00 00 00 00 00 00 |0.......1.......| 00000020 32 00 00 00 00 00 00 00 33 00 00 00 00 00 00 00 |2.......3.......| 00000030 34 00 00 00 00 00 00 00 35 00 00 00 00 00 00 00 |4.......5.......| 00000040 36 00 00 00 00 00 00 00 37 00 00 00 00 00 00 00 |6.......7.......| 00000050 42 00 00 00 00 00 00 00 43 00 00 00 00 00 00 00 |B.......C.......| 00000060 44 00 00 00 00 00 00 00 45 00 00 00 00 00 00 00 |D.......E.......| 00000070 46 00 00 00 00 00 00 00 47 00 00 00 00 00 00 00 |F.......G.......| 00000080 48 00 00 00 00 00 00 00 49 00 00 00 00 00 00 00 |H.......I.......| 00000090 4a 00 00 00 00 00 00 00 4b 00 00 00 00 00 00 00 |J.......K.......| 000000a0 4c 00 00 00 00 00 00 00 4d 00 00 00 00 00 00 00 |L.......M.......| 000000b0 4e 00 00 00 00 00 00 00 4f 00 00 00 00 00 00 00 |N.......O.......| 000000c0 50 00 00 00 00 00 00 00 50 00 00 00 00 00 00 00 |P.......P.......| 000000d0 51 00 00 00 00 00 00 00 50 00 00 00 00 00 00 00 |Q.......P.......| 000000e0 51 00 00 00 00 00 00 00 52 00 00 00 00 00 00 00 |Q.......R.......| 000000f0 50 00 00 00 00 00 00 00 51 00 00 00 00 00 00 00 |P.......Q.......| 00000100 52 00 00 00 00 00 00 00 53 00 00 00 00 00 00 00 |R.......S.......| 00000110 50 00 00 00 00 00 00 00 51 00 00 00 00 00 00 00 |P.......Q.......| 00000120 52 00 00 00 00 00 00 00 53 00 00 00 00 00 00 00 |R.......S.......| ... You can clearly see duplicates OID (the first one being 0x50 at 0xc8). Its entry in BBP.dir (split into sections): 136 32 tmp_210 tmpr_210 02/210 610523782 2 171807 0 0 171807 172032 0 0 0 0 void 0 1 1793 0 0 0 0 0 1000651 0 0 0 oid 8 0 1024 24 25 27 1 46 773603235 1374456 1376256 1 We are using: MonetDB 5 server v11.21.14 (64-bit, 64-bit oids, 128-bit integers). -- Frédéric Jolliton Sécuractive

4 3

Does R run in a sandbox in MonetDB/R?
by Pablo S 07 Aug '16

07 Aug '16

MonetDB allows to create R functions that run embedded in the database. For example, you can do CREATE FUNCTION rapi00() RETURNS TABLE (d INTEGER) LANGUAGE R { seq(1,10); }; Now, I need my users to create dynamically these user defined functions by entering R statements on a screen; the application will create the functions in the database and run them. Problem is that R allows to run operating system level commands, such as list.files(path=".") or even file.remove("some_file") Will MonetDB allow these external interactions with the operating system, or it runs in a sandbox?

1 0

How could I obtain the running time information in the MAL interface of mclient?
by Xu,Wenjian 07 Aug '16

07 Aug '16

Hi all, MonetDB version: 11.23.7 Ubuntu version: 14.04 How could I obtain the running time information in the MAL interface of mclient? I tried to use the *--interactive* option but it did not work. Specifically, I do the following: =============================================== $ *mclient -dnba2009 -lmsql --interactive=ms* Welcome to mclient, the MonetDB interactive terminal (Jun2016-SP1) Type \q to quit, \? for a list of available commands msql>\<query.txt // I save the MAL instructions of a query (obtained from EXPLAIN statement) in this file msql>user.s1_3("1998"); // call the function of that query % sys.player, sys.player, sys.player # table_name % p_lastname, p_lastseason, p_weight # name % char, int, int # type % 100, 4, 3 # length [ "Wheeler", 1998, 165 ] [ "Baker", 1998, 175 ] [ "Garnett", 1998, 186 ] ...... ======================================================== I would like to obtain the running time information after getting the result of that query. How can I do it? Any help would be appreciated. Best, Wenjian

4 6

Strange behaviour with optimizer.pushselect and batalgebra.like
by Moritz Bruder 05 Aug '16

05 Aug '16

Hi, I get a really strange behaviour with the *pushselect* optimizer for this piece of MAL code: http://codepad.org/bqulPEtV . The problem I'm having is, that depending on whether the *pushselect* optimizer is used or not, the query will yield different results. Context info: 1. MonetDB version number: 11.23.3 2. Operating system platform: Linux 4.6.4-1-ARCH #1 SMP PREEMPT Mon Jul 11 19:12:32 CEST 2016 x86_64 GNU/Linux 3. Compiled from source tarball 4. http://codepad.org/bqulPEtV Regards, Moritz

1 0

Using MonetDB with Scala/Slick
by Pablo S 05 Aug '16

05 Aug '16

I develop in Scala, and need to access a MonetDB database. I use the Scala standard, Slick, however Slick doesn't have a driver to access MonetDB. I'm interested in developing a Slick/MonetDB driver (it's a JDBC wrapper) but I'd like to know first if someone already started the process (we could join forces). Please let me know. Thanks.

1 0

When would the function group.multicolumn() used?
by Xu,Wenjian 03 Aug '16

03 Aug '16

Hi all, I found the following new MAL function in the new release of version 11.23.3: ============================================================ pattern group.multicolumn(b:bat[:any]...) (ref:bat[:oid],grp:bat[:oid],hist:bat[:any]) address GROUPmulticolumngroup; comment Derivation of a group index over multiple columns. ============================================================ I thought I may be used by queries with multiple attributes in the GROUP BY clause. So I tried to EXPLAIN such a query (e.g., SELECT ... FROM... GROUP BY ColA, ColB), but it is still compiled with multiple group.subgroup() followed by a group.subgroupdone(). So, when would this function group.multicolumn() used? Thanks, Wenjian

1 0

a few good stackoverflow qs without answers..
by Anthony Damico 30 Jul '16

30 Jul '16

http://stackoverflow.com/questions/tagged/monetdb?sort=newest&pageSize=50

1 0

infinity value
by Stefano Fioravanzo 26 Jul '16

26 Jul '16

Hello, I am creating a table from the result of a python function. One column is composed of double values, some of them are np.inf and are translated in monet as inf I cannot find a way to query for these values, how does monetdb handle infinity? Moreover, if I later do some query on this table and apply an arithmetic operation to the inf values, monetdb throws ‘overflow in calculation’ error, I would expect any operation (except for special cases) to result in inf. Is this a known issue or an intended design? Regards, Stefano

1 0

mapi.class.php
by Alberto Ferrari 26 Jul '16

26 Jul '16

For those who are interested, here is (attached) the "php_mapi.inc" which I changed to a php class (OOP) Feel free to use or even upload to any repository. Regards.

2 1

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

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

Positional placeholders in prepared statement
by Frank Tetzel 29 Jun '16

29 Jun '16

Hi, does MonetDB support positional placeholders like '$1' instead of just '?' in prepared statements? I want to refer to a parameter more than once and possibly also out of order. Is there some alternative available? Regards, Frank

2 1

Resetting monetdb administrative password
by Roman S 29 Jun '16

29 Jun '16

Hello, Can somebody tell me how to reset monetdb administrative password? I’ve tried to stop mserver5, start it manually, oldp:=clients.getPasswordHash(“monetdb"); newp:=clients.backendsum("newpassword"); clients.changePassword(oldp, newp); Stoping it again and start in normal mode But it didn’t help: mclient --database=mydb --user=monetdb password:<usingnewpass> InvalidCredentialsException:checkCredentials:invalid credentials for user 'monetdb'

2 2

MERGE Tables works ?
by Ariel Abadi 28 Jun '16

28 Jun '16

Hi Everybody! Im facing a problem. We register telecomunications calls. As the volume is so big, we decided to store the data in different tables. The criteria we used for splitting the info was to create a table per each Switch and Day. So we have Sw1_20160601, Sw2_20160602...Sw3_20160620..etc etc Each table has aprox 15MM rows. In order to create some reports we have created a *MERGE TABLE *and added to this table the 90 tables (3 switches x 30 days). BUT, now Im trying to run a very simple query, like SELECT cdr_date FROM xxxx LIMIT 3; To get at least 3 rows (just to see that this schema works). It took 21 mins to give me those 3 rows.... Am I doing something wrong? Rgds Ariel

3 4

Announcement: New Jun2016 Feature release of MonetDB suite
by Sjoerd Mullender 23 Jun '16

23 Jun '16

The MonetDB team at CWI/MonetDB BV is pleased to announce the Jun2016 feature 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 feature release MonetDB5 Server * Extended the storage() table producing function to also accept storage([schemaname [, tablename [, columnname]]]) * The :bat[:oid,:any] type descriptor has been turned into its columnar version :bat[:any] * Converted the *.mal scripts into *.malC versions to prepare for removal of the mserver console. * Removed functions mat.hasMoreElements, mat.info, mat.mergepack, mat. newIterator, mat.project, mat.pack2, mat.sortReverse, mat.sort, and mat.slice. * Removed grouped aggregate functions from the aggr module in which the groups were indicated by the head column of the bat to be aggregated. Use the interface with a separate group bat instead. * The server now stops executing a query when the client disconnects. * Removed algebra.join. Use algebra.subjoin instead. * Removed algebra.antijoin. Use algebra.subantijoin or algebra.subthetajoin instead. * The MAL function 'leftfetchjoin' is renamed to its relational algebra version 'projection' * The generic property handling scheme has been removed. It was used in just a few places, for widely different purposes and contained cruft. * Removed functions str.iconv and str.codeset. Internally, strings are always in UTF-8 encoding, so we can't allow code set conversions. * Removed algebra.like with a BAT argument. Use algebra.likesubselect instead. * Removed algebra.leftjoin. Use algebra.subleftjoin or algebra.leftfetchjoin instead. * Removed algebra.tdiff and algebra.tinter. * Removed algebra.sample. Use sample.subuniform instead. * Removed algebra.select. Use algebra.subselect instead. * Removed algebra.revert. * Removed bat.order and bat.orderReverse functions. * Changed client.getUsers to return two bats, one with the user id and one with the user name. * Implemented algebra.subdiff and algebra.subinter. * Removed algebra.tdifference and algebra.tintersect. * Removed algebra.tunion. Build Environment * A new package MonetDB-lidar (Fedora) or libmonetdb5-server-lidar (Debian/Ubuntu) has been created to work with LiDAR data. * Implemented a systemd configuration file for a monetdbd.service on systems that support it (Fedora, newer Ubuntu). Client Package * The Ruby client is now in a separate repository (http://dev.monetdb.org/hg/monetdb-ruby) and released independently. Geom Module * The geom module has been completely overhauled. Types are now specified as GEOMETRY(POINT) instead of POINT, old functions have been removed, new ones introduced. However, even with all the changes, a database upgrade should still be possible (as always, make a backup first). MonetDB Common * Removed BATconst. Use BATconstant instead. * Changed BATconstant. It now has a new first argument with the seqbase for the head column. * Removed BATmmap. It was no longer used. * BUNdelete and BATdel don't accept a foce argument and only allow deleting values that have not yet been committed. BUNdelete exchanges the deleted value with the last value (if the deleted value isn't the last value). BATdel compacts the BAT by shifting values after the deleted values up. The list of to-be-deleted values in BATdel must be sorted and unique. * Removed BUNreplace from list of exported functions. It wasn't used, and there is still BUNinplace and void_inplace that do more-or-less the same thing. * Changed BATderiveHeadProps to BATderiveTailProps (and it now works on the tail column). * Removed unused functions BATalpha, BATdelta, and BATprev. * Removed function VIEWcombine. Use BATdense instead. * Removed "left" parameter from BUNinplace. It wasn't used since the BAT it works on is dense headed. * Created function BATdense to easily create a [void,void] BAT with specified seqbases for head and tail and a count. * Removed function BATmark. When all head columns are dense, BATmark basically only created a new [void,void] BAT. * Renamed BATsubsort to BATsort. * Removed "sub" from the names of the function BATsubselect, BATthetasubselect, BATsubcross, BATsubleftjoin, BATsubouterjoin, BATsubthetajoin, BATsubsemijoin, BATsubdiff, BATsubjoin, BATsubbandjoin, BATsubrangejoin, and BATsubunique. * Removed BATsubleftfetchjoin: it was not used. * Removed BATcross1. Use BATsubcross instead. * Removed all versions of the SORTloop macro. * Removed Batkdiff. Use BATsubdiff instead. * Removed BATselect. Use BATsubselect instead. * Removed BATsemijoin. Use BATsubsemijoin instead. * Removed BATjoin. Use BATsubjoin instead. * Removed BATleftjoin. Use BATsubleftjoin or BATproject instead. * Removed BATleftfetchjoin. Use BATproject instead. * Removed BUNins from the list of exported functions. * Removed legacy functions BATuselect and BATuselect_. * Removed legacy functions BATantijoin, BATbandjoin, BATouterjoin, BATrangejoin, and BATthetajoin. * Removed function BATrevert. * BATordered now works on the TAIL column. * Removed obsolete functions BATorder() and BATorder_rev(). * Implemented BATsubdiff which returns a list of OIDs (sorted, i.e. usable as candidate list) of tuples in the left input whose value does not occur in the right input. * Removed function BATkintersect. It wasn't used anymore. It's functionality can be obtained by using BATsubsemijoin. * Removed the function BATkunion. Java Module * Fixed problem in DatabaseMetaData.getUDTs() when it was called with types parameter filled. It used to throw SQException with message: SELECT: identifier 'DATA_TYPE' unknown. Now it returns the UDTs which match the provided array of data types. * Implemented MonetDatabaseMetaData.supportsConvert() and MonetDatabaseMetaData.supportsConvert(int fromType, int toType) methods. It used to always return false. Now it returns true for the supported conversions. This fixes Bug 3460. * ResultSet.setFetchSize(): added a dummy implementation to get rid of the SQLFeatureNotSupportedException. In MonetDB, it does not make sense to set the fetch size of a result set. If one really wants to set the fetch size, one should use Statement.setFetchSize() instead. * Fixed resource leak in ResultSetMetaData. It created and cached a ResultSet object for each column but never closed the ResultSet objects. * Corrected DatabaseMetaData methods which accept a catalog filter argument. Those methods will now filter the results on the specified catalog name, whereas previously the catalog filter argument was ignored. * Corrected output of column KEY_SEQ of DatabaseMetaData methods: getPrimaryKeys(), getImportedKeys(), getExportedKeys() and getCrossReference(). It now starts at 1 instead of 0 previously. * Corrected DatabaseMetaData.getSchemas() by returning 2 instead of 3 columns. * Improved DatabaseMetaData.getColumns() by returning two additional columns: IS_AUTOINCREMENT and IS_GENERATEDCOLUMN. * Improved DatabaseMetaData.getTypeInfo(). It now returns better information on LITERAL_PREFIX, LITERAL_SUFFIX, CREATE_PARAMS, CASE_SENSITIVE, FIXED_PREC_SCALE and MAXIMUM_SCALE for some data types. Also the returned rows are now ordered by DATA_TYPE, TYPE_NAME, PRECISION as required by the specs. Also corrected output column names "searchable" into "SEARCHABLE" and "MAXIMUM SCALE" into "MAXIMUM_SCALE" to match the JDBC specification. * Corrected DatabaseMetaData.getPseudoColumns(). It used to return 12 empty rows. Now it returns no rows as MonetDB does not have pseudo columns. * Implemented method DatabaseMetaData.getClientProperties(). It used to always return a resultset with 4 completely empty rows. It now returns a resultset with the possible connection properties. * Implemented method DatabaseMetaData.getUDTs(). It used to return an empty resultset. Now it returns the User Defined Types such as inet, json, url and uuid. * Corrected the returned table types in DatabaseMetaData.getTableTypes(). It now returns all 10 table types (as stored in sys.table_types) instead of the previously 8 hardcoded table types. For old MonetDB servers which do not have the sys.table_types table, the old behavior is retained. * Implemented methods DatabaseMetadata.getProcedures() and DatabaseMetadata.getProcedureColumns(). They used to return an empty resultset. Now they return the expected Procedures and ProcedureColumns. Also getProcedureColumns() now returns a resultset with all 20 columns instead of 13 columns previously. * Method getFunctionColumns() in DatabaseMetadata used to throw an SQLException: getFunctionColumns(String, String, String, String) is not implemented. This method is now implemented and returns a resultset. * Method getFunctions() in DatabaseMetadata used to throw an SQLException: SELECT: no such column 'functions.sql' This has been corrected. It now returns a resultset as requested. * The resultsets of DatabaseMetadata methods now no longer return a value for the *_CAT columns as MonetDB does not support Catalogs. * Fixed a memory leak in MonetDatabaseMetaData.java for a static cache which kept references to closed Connection objects. * Improved JdbcClient program when presenting query data to console. It used to send an SQL catalog query for each query result column which slowed down the interactive response considerably. These additional SQL catalog queries have been eliminated. * Corrected MonetResultSet.getObject(String columnName). It no longer throws a NullPointerException in cases where internally a MonetVirtualResultSet is used. * Fixed Connection.isValid(): this method should never attempt to close the connection, even if an error has occurred. SQL * Disallow GRANT <some-user> TO <role-or-use>. Only explicitly created roles can be granted. * Extended grantable schema privileges: when a user is granted the "sysadmin" role, the user now hcan not only create schemas, but also drop schemas. * Introduced COPY INTO/ COPY FROM global privileges. These privileges allows other users than 'monetdb' to be granted the privileges to use the MonetDB bulk data loading/exporting features, i.e., COPY FROM <file> and COPY INTO <file>. Bug Fixes * 2407: Merovingian: allow binds to given ip/interface * 2815: No SRID support * 3361: constants as MAL function parameters prevent intermediate reuse * 3374: UNIQUE constraint does not set tkey property on the corresponding BAT * 3460: incomplete implementation of JDBC driver supportsConvert(), supportsConvert(int fromType, int toType) methods in MonetDatabaseMetaData.java * 3711: JDBC connection using jdbcclient.jar is very slow * 3877: MonetDBLite should allow close then re-open databases? without restarting R * 3911: Invalid connect() call in 'redirect' mode * 3920: query with DISTINCT and correlated scalar subquery in SELECT list causes Assertion failure and crash of mserver5 * 3927: COUNT( distinct my_clob ) , QUANTILE( my_double , number ) fails in dev build * 3956: MonetDBLite unable to execute LIMIT 1 statement * 3972: Drastic Memory leak of 600GBs while generating plan for Query with 25 joins * 3974: Prepared statement rel_bin.c:2378: rel2bin_project: Assertion `0' failed. * 3975: Suspicious code in store_manager() on exit path * 3978: SQL returns TypeException 'aggr.subcorr' undefined for sys.corr function * 3980: JOIN with references on both sides crashes mserver * 3981: Incorrect LEFT JOIN when FROM clause contains nested subqueries * 3983: Creation of a Foreign Key which partially maps to a primary key is accepted without a warning * 3984: Multiple paths in the .profile * 3985: ruby-monetdb-sql gem fails for negative timezone offset (USA, etc.) * 3987: Segfault on malformed csv import * 3991: MonetDBLite feature request: default monetdb.sequential to FALSE on windows * 3994: MonetDBLite dbDisconnect with shutdown=TRUE freezes the console on windows * 3995: NullPointerException when calling getObject() * 3997: calling scalar functions sys.isaUUID(str) or sys.isaUUID(uuid) fail * 3999: length() returns wrong length for strings which have spaces at the end of the string * 4010: RELEASE SAVEPOINT after ALTER TABLE crashes mserver5 * 4011: sys.sessions.user column always shows 'monetdb' * 4013: GDKextendf does not free up memory when it fails due to insufficient resources * 4015: Daemon crashes on database release command

1 0

Announcement: New Jun2016 Feature release of MonetDB suite
by Sjoerd Mullender 23 Jun '16

23 Jun '16

The MonetDB team at CWI/MonetDB BV is pleased to announce the Jun2016 feature 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 feature release MonetDB5 Server * Extended the storage() table producing function to also accept storage([schemaname [, tablename [, columnname]]]) * The :bat[:oid,:any] type descriptor has been turned into its columnar version :bat[:any] * Converted the *.mal scripts into *.malC versions to prepare for removal of the mserver console. * Removed functions mat.hasMoreElements, mat.info, mat.mergepack, mat. newIterator, mat.project, mat.pack2, mat.sortReverse, mat.sort, and mat.slice. * Removed grouped aggregate functions from the aggr module in which the groups were indicated by the head column of the bat to be aggregated. Use the interface with a separate group bat instead. * The server now stops executing a query when the client disconnects. * Removed algebra.join. Use algebra.subjoin instead. * Removed algebra.antijoin. Use algebra.subantijoin or algebra.subthetajoin instead. * The MAL function 'leftfetchjoin' is renamed to its relational algebra version 'projection' * The generic property handling scheme has been removed. It was used in just a few places, for widely different purposes and contained cruft. * Removed functions str.iconv and str.codeset. Internally, strings are always in UTF-8 encoding, so we can't allow code set conversions. * Removed algebra.like with a BAT argument. Use algebra.likesubselect instead. * Removed algebra.leftjoin. Use algebra.subleftjoin or algebra.leftfetchjoin instead. * Removed algebra.tdiff and algebra.tinter. * Removed algebra.sample. Use sample.subuniform instead. * Removed algebra.select. Use algebra.subselect instead. * Removed algebra.revert. * Removed bat.order and bat.orderReverse functions. * Changed client.getUsers to return two bats, one with the user id and one with the user name. * Implemented algebra.subdiff and algebra.subinter. * Removed algebra.tdifference and algebra.tintersect. * Removed algebra.tunion. Build Environment * A new package MonetDB-lidar (Fedora) or libmonetdb5-server-lidar (Debian/Ubuntu) has been created to work with LiDAR data. * Implemented a systemd configuration file for a monetdbd.service on systems that support it (Fedora, newer Ubuntu). Client Package * The Ruby client is now in a separate repository (http://dev.monetdb.org/hg/monetdb-ruby) and released independently. Geom Module * The geom module has been completely overhauled. Types are now specified as GEOMETRY(POINT) instead of POINT, old functions have been removed, new ones introduced. However, even with all the changes, a database upgrade should still be possible (as always, make a backup first). MonetDB Common * Removed BATconst. Use BATconstant instead. * Changed BATconstant. It now has a new first argument with the seqbase for the head column. * Removed BATmmap. It was no longer used. * BUNdelete and BATdel don't accept a foce argument and only allow deleting values that have not yet been committed. BUNdelete exchanges the deleted value with the last value (if the deleted value isn't the last value). BATdel compacts the BAT by shifting values after the deleted values up. The list of to-be-deleted values in BATdel must be sorted and unique. * Removed BUNreplace from list of exported functions. It wasn't used, and there is still BUNinplace and void_inplace that do more-or-less the same thing. * Changed BATderiveHeadProps to BATderiveTailProps (and it now works on the tail column). * Removed unused functions BATalpha, BATdelta, and BATprev. * Removed function VIEWcombine. Use BATdense instead. * Removed "left" parameter from BUNinplace. It wasn't used since the BAT it works on is dense headed. * Created function BATdense to easily create a [void,void] BAT with specified seqbases for head and tail and a count. * Removed function BATmark. When all head columns are dense, BATmark basically only created a new [void,void] BAT. * Renamed BATsubsort to BATsort. * Removed "sub" from the names of the function BATsubselect, BATthetasubselect, BATsubcross, BATsubleftjoin, BATsubouterjoin, BATsubthetajoin, BATsubsemijoin, BATsubdiff, BATsubjoin, BATsubbandjoin, BATsubrangejoin, and BATsubunique. * Removed BATsubleftfetchjoin: it was not used. * Removed BATcross1. Use BATsubcross instead. * Removed all versions of the SORTloop macro. * Removed Batkdiff. Use BATsubdiff instead. * Removed BATselect. Use BATsubselect instead. * Removed BATsemijoin. Use BATsubsemijoin instead. * Removed BATjoin. Use BATsubjoin instead. * Removed BATleftjoin. Use BATsubleftjoin or BATproject instead. * Removed BATleftfetchjoin. Use BATproject instead. * Removed BUNins from the list of exported functions. * Removed legacy functions BATuselect and BATuselect_. * Removed legacy functions BATantijoin, BATbandjoin, BATouterjoin, BATrangejoin, and BATthetajoin. * Removed function BATrevert. * BATordered now works on the TAIL column. * Removed obsolete functions BATorder() and BATorder_rev(). * Implemented BATsubdiff which returns a list of OIDs (sorted, i.e. usable as candidate list) of tuples in the left input whose value does not occur in the right input. * Removed function BATkintersect. It wasn't used anymore. It's functionality can be obtained by using BATsubsemijoin. * Removed the function BATkunion. Java Module * Fixed problem in DatabaseMetaData.getUDTs() when it was called with types parameter filled. It used to throw SQException with message: SELECT: identifier 'DATA_TYPE' unknown. Now it returns the UDTs which match the provided array of data types. * Implemented MonetDatabaseMetaData.supportsConvert() and MonetDatabaseMetaData.supportsConvert(int fromType, int toType) methods. It used to always return false. Now it returns true for the supported conversions. This fixes Bug 3460. * ResultSet.setFetchSize(): added a dummy implementation to get rid of the SQLFeatureNotSupportedException. In MonetDB, it does not make sense to set the fetch size of a result set. If one really wants to set the fetch size, one should use Statement.setFetchSize() instead. * Fixed resource leak in ResultSetMetaData. It created and cached a ResultSet object for each column but never closed the ResultSet objects. * Corrected DatabaseMetaData methods which accept a catalog filter argument. Those methods will now filter the results on the specified catalog name, whereas previously the catalog filter argument was ignored. * Corrected output of column KEY_SEQ of DatabaseMetaData methods: getPrimaryKeys(), getImportedKeys(), getExportedKeys() and getCrossReference(). It now starts at 1 instead of 0 previously. * Corrected DatabaseMetaData.getSchemas() by returning 2 instead of 3 columns. * Improved DatabaseMetaData.getColumns() by returning two additional columns: IS_AUTOINCREMENT and IS_GENERATEDCOLUMN. * Improved DatabaseMetaData.getTypeInfo(). It now returns better information on LITERAL_PREFIX, LITERAL_SUFFIX, CREATE_PARAMS, CASE_SENSITIVE, FIXED_PREC_SCALE and MAXIMUM_SCALE for some data types. Also the returned rows are now ordered by DATA_TYPE, TYPE_NAME, PRECISION as required by the specs. Also corrected output column names "searchable" into "SEARCHABLE" and "MAXIMUM SCALE" into "MAXIMUM_SCALE" to match the JDBC specification. * Corrected DatabaseMetaData.getPseudoColumns(). It used to return 12 empty rows. Now it returns no rows as MonetDB does not have pseudo columns. * Implemented method DatabaseMetaData.getClientProperties(). It used to always return a resultset with 4 completely empty rows. It now returns a resultset with the possible connection properties. * Implemented method DatabaseMetaData.getUDTs(). It used to return an empty resultset. Now it returns the User Defined Types such as inet, json, url and uuid. * Corrected the returned table types in DatabaseMetaData.getTableTypes(). It now returns all 10 table types (as stored in sys.table_types) instead of the previously 8 hardcoded table types. For old MonetDB servers which do not have the sys.table_types table, the old behavior is retained. * Implemented methods DatabaseMetadata.getProcedures() and DatabaseMetadata.getProcedureColumns(). They used to return an empty resultset. Now they return the expected Procedures and ProcedureColumns. Also getProcedureColumns() now returns a resultset with all 20 columns instead of 13 columns previously. * Method getFunctionColumns() in DatabaseMetadata used to throw an SQLException: getFunctionColumns(String, String, String, String) is not implemented. This method is now implemented and returns a resultset. * Method getFunctions() in DatabaseMetadata used to throw an SQLException: SELECT: no such column 'functions.sql' This has been corrected. It now returns a resultset as requested. * The resultsets of DatabaseMetadata methods now no longer return a value for the *_CAT columns as MonetDB does not support Catalogs. * Fixed a memory leak in MonetDatabaseMetaData.java for a static cache which kept references to closed Connection objects. * Improved JdbcClient program when presenting query data to console. It used to send an SQL catalog query for each query result column which slowed down the interactive response considerably. These additional SQL catalog queries have been eliminated. * Corrected MonetResultSet.getObject(String columnName). It no longer throws a NullPointerException in cases where internally a MonetVirtualResultSet is used. * Fixed Connection.isValid(): this method should never attempt to close the connection, even if an error has occurred. SQL * Disallow GRANT <some-user> TO <role-or-use>. Only explicitly created roles can be granted. * Extended grantable schema privileges: when a user is granted the "sysadmin" role, the user now hcan not only create schemas, but also drop schemas. * Introduced COPY INTO/ COPY FROM global privileges. These privileges allows other users than 'monetdb' to be granted the privileges to use the MonetDB bulk data loading/exporting features, i.e., COPY FROM <file> and COPY INTO <file>. Bug Fixes * 2407: Merovingian: allow binds to given ip/interface * 2815: No SRID support * 3361: constants as MAL function parameters prevent intermediate reuse * 3374: UNIQUE constraint does not set tkey property on the corresponding BAT * 3460: incomplete implementation of JDBC driver supportsConvert(), supportsConvert(int fromType, int toType) methods in MonetDatabaseMetaData.java * 3711: JDBC connection using jdbcclient.jar is very slow * 3877: MonetDBLite should allow close then re-open databases? without restarting R * 3911: Invalid connect() call in 'redirect' mode * 3920: query with DISTINCT and correlated scalar subquery in SELECT list causes Assertion failure and crash of mserver5 * 3927: COUNT( distinct my_clob ) , QUANTILE( my_double , number ) fails in dev build * 3956: MonetDBLite unable to execute LIMIT 1 statement * 3972: Drastic Memory leak of 600GBs while generating plan for Query with 25 joins * 3974: Prepared statement rel_bin.c:2378: rel2bin_project: Assertion `0' failed. * 3975: Suspicious code in store_manager() on exit path * 3978: SQL returns TypeException 'aggr.subcorr' undefined for sys.corr function * 3980: JOIN with references on both sides crashes mserver * 3981: Incorrect LEFT JOIN when FROM clause contains nested subqueries * 3983: Creation of a Foreign Key which partially maps to a primary key is accepted without a warning * 3984: Multiple paths in the .profile * 3985: ruby-monetdb-sql gem fails for negative timezone offset (USA, etc.) * 3987: Segfault on malformed csv import * 3991: MonetDBLite feature request: default monetdb.sequential to FALSE on windows * 3994: MonetDBLite dbDisconnect with shutdown=TRUE freezes the console on windows * 3995: NullPointerException when calling getObject() * 3997: calling scalar functions sys.isaUUID(str) or sys.isaUUID(uuid) fail * 3999: length() returns wrong length for strings which have spaces at the end of the string * 4010: RELEASE SAVEPOINT after ALTER TABLE crashes mserver5 * 4011: sys.sessions.user column always shows 'monetdb' * 4013: GDKextendf does not free up memory when it fails due to insufficient resources * 4015: Daemon crashes on database release command

1 0

MonetDB vs. the new MariaDB ColumnStore
by Arno Mittelbach 20 Jun '16

20 Jun '16

Hi all, MariaDB has released a first alpha version of their new ColumnStore (which is essentially InfiniDB). We have done a first tentative performance test and did a comparison of MariaDB ColumnStore vs. MonetDB. For most of the test cases, MonetDB wins hands down. If you are interested in the numbers, have a look at https://reportserver.net/blog/2016/06/20/mariadb-columnstore-vs-innodb-vs-m… Best Regards, Arno

1 0

NULL value with embedded py
by Stefano Fioravanzo 20 Jun '16

20 Jun '16

Hello, I am creating a table using the result of an embedded py function. The result of the function is a dictionary composed of numpy arrays. The function and the CREATE TABLE work well. Inside the arrays of the dictionary, I have a few np.nan values, they seem to be recognized by monet during the creation of the table. In fact, when I query the whole table, I can see ‘null’ in the proper places. But if I try to compare against these null values, I get nothing (SELECT * FROM table where field is null). After a few trials, it seems to me that monet understands that np.nan is a NULL value, but then when it inserts the value into the table the internal representation (?) is somewhat different form a ‘normal’ NULL value. Any suggestion? Thanks, Stefano

3 2

Recover an inconsistent database?
by Vijay Krishna 20 Jun '16

20 Jun '16

Hi, I'm getting the following error while starting a database. *!FATAL: Logger_new: inconsistent database, catalog does not exist* Is there any means to recover this database with data and start it? Or, the only way is to create a new database? (P.S.: There is no backup of data) Thanks & Regards, Vijay.

1 0

aggregation with complex function
by Stefano Fioravanzo 16 Jun '16

16 Jun '16

Hello, I have an embedded python function which, given two integer parameters, after doing some stuff returns a table. I would like to group by a table by these two input parameter, and for each resulting group call the function. The result would be the concatenation of all the tables returned by the function. I understand that a thing like this would be very easy if the function returned just one value e.g.: select my_aggr_function(value1, value2) from table group by value1, value2 But how can I do this if my_aggr_function returns multiple rows?? Thank you, Stefano

4 15

Dynamic Queries
by Ariel Abadi 16 Jun '16

16 Jun '16

Hi Im trying to create a Procedure to create (or not) tables dynamically. Does Monet can do something like the following?? CREATE PROCEDURE(pdate date, tname varchar(10)) BEGIN SET TABLENAME = CONCAT(tname, '_', YEAR(pdate)*100+MONTH(pdate)); CREATE TABLE IF NOT EXIST TABLENAME; END Thanks in advance! Rgds

3 2

Issue with bugzilla
by Roberto Cornacchia 14 Jun '16

14 Jun '16

There seems to be an issue with Bugzilla. If I make a bug report and select Jul2015-SP4, after saving the version is Oct2014-SP2. The first few times that this happened I thought it had been my mistake in selecting the version, but now I'm sure it wasn't. Roberto

2 1

Update JOIN?
by Alberto Ferrari 14 Jun '16

14 Jun '16

Is it possible to Update Join in Monet? I need something like this... (just an example) UPDATE MY_TABLE AS a, ( SELECT (SUM( t.mins * t.acpm ) / t.rcpmax) AS calc1 (SUM( t.mins * t.acxh ) / t.acxmax) AS calc2 FROM MY_TABLE AS t GROUP BY t.source, t.name_id ) AS q SET a.awc = q.calc1, a.lcr = q.calc2 WHERE a.source = q.source AND a.name_id = q.name_id When I try to execute this type of query, I get this error... syntax error, unexpected AS, expecting SET in: "update sb_rmsv.rmsv_nts_nt_sum_report as"

4 5

Announcement: New release candidate of MonetDB suite
by Sjoerd Mullender 14 Jun '16

14 Jun '16

-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA256 This is a heads up announcement. We at CWI and MonetDB have prepared a release candidate for the Jun2016 release. Usually we just foist a new release upon you, our unsuspecting users, but this time, because there are so many changes, we thought it better to give you a little warning. See http://dev.monetdb.org/downloads/testing/ for the release and the preliminary release notes [1]. Among the changes is a complete overhaul of the MonetDB-geom module. If you use that, make very sure you have your backups in order. Also note that since this new version depends on an up-to-date version of the geos library, there is no geom module available for older Linux releases (in particular, Debian 7 (wheezy), Ubuntu 12.04 LTS (Precise Pangolin) and Ubuntu 14.04 LTS (Trusty Tahr), and RedHat Enterprise Linux (CentOS and Scientific Linux) 6). The plan is to turn this release candidate into an actual release before the end of this month (June 2016). If you find a bug in this release candidate, please let us know on the bug tracker [2]. We will then determine whether to go ahead with the release (and hopefully fix the bug in the next bug-fix release) or postpone the release and fix the bug first. [1] https://dev.monetdb.org/downloads/testing/relnotes.html [2] http://bugs.monetdb.org (a.k.a. https://www.monetdb.org/bugzilla/) - -- Sjoerd Mullender -----BEGIN PGP SIGNATURE----- Version: GnuPG v2 iQEcBAEBCAAGBQJXX/EsAAoJEISMxT6LrWYg+AcIAIgIST/dUrL8fDjwp3/G41zc 4g9xWbSxIE/+nZLjlHM6Onr4sETceOVdDVuxUGoIbeu9+DZNQqEakUUrABrldvLx ie/kbNINtdntFvfF3W5Lyo/lWhel3IUZZhEXvsBT9JCSfg5kiLKYr/CPqw5QgOcU XgccIn7RAZYovfVoLo7jLxiMLeT9DJp74gy1OkjrIBwQ/GPVam/CzaO5Ungu0dRY cZiqoRhW1GEtUQHABq0O9J/gJcHfFbSlwDeQcnTouybO320gBRISYqgV8aTt0RdU /MVVkdOU3hLsShjAgWinRbQPNVwCLcZNAfxER9q4Z9tt15n/3E6WtPeKo2Bv030= =rnCK -----END PGP SIGNATURE-----

1 0

Announcement: New release candidate of MonetDB suite
by Sjoerd Mullender 14 Jun '16

14 Jun '16

-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA256 This is a heads up announcement. We at CWI and MonetDB have prepared a release candidate for the Jun2016 release. Usually we just foist a new release upon you, our unsuspecting users, but this time, because there are so many changes, we thought it better to give you a little warning. See http://dev.monetdb.org/downloads/testing/ for the release and the preliminary release notes [1]. Among the changes is a complete overhaul of the MonetDB-geom module. If you use that, make very sure you have your backups in order. Also note that since this new version depends on an up-to-date version of the geos library, there is no geom module available for older Linux releases (in particular, Debian 7 (wheezy), Ubuntu 12.04 LTS (Precise Pangolin) and Ubuntu 14.04 LTS (Trusty Tahr), and RedHat Enterprise Linux (CentOS and Scientific Linux) 6). The plan is to turn this release candidate into an actual release before the end of this month (June 2016). If you find a bug in this release candidate, please let us know on the bug tracker [2]. We will then determine whether to go ahead with the release (and hopefully fix the bug in the next bug-fix release) or postpone the release and fix the bug first. [1] https://dev.monetdb.org/downloads/testing/relnotes.html [2] http://bugs.monetdb.org (a.k.a. https://www.monetdb.org/bugzilla/) - -- Sjoerd Mullender -----BEGIN PGP SIGNATURE----- Version: GnuPG v2 iQEcBAEBCAAGBQJXX/EsAAoJEISMxT6LrWYg+AcIAIgIST/dUrL8fDjwp3/G41zc 4g9xWbSxIE/+nZLjlHM6Onr4sETceOVdDVuxUGoIbeu9+DZNQqEakUUrABrldvLx ie/kbNINtdntFvfF3W5Lyo/lWhel3IUZZhEXvsBT9JCSfg5kiLKYr/CPqw5QgOcU XgccIn7RAZYovfVoLo7jLxiMLeT9DJp74gy1OkjrIBwQ/GPVam/CzaO5Ungu0dRY cZiqoRhW1GEtUQHABq0O9J/gJcHfFbSlwDeQcnTouybO320gBRISYqgV8aTt0RdU /MVVkdOU3hLsShjAgWinRbQPNVwCLcZNAfxER9q4Z9tt15n/3E6WtPeKo2Bv030= =rnCK -----END PGP SIGNATURE-----

1 0

copy into from csv with delimiters in quoted field
by Frank Tetzel 10 Jun '16

10 Jun '16

Hello, i have an issue with the "copy into" statement and a csv file. In the documentation[1] it says: "Field and record separators can be embedded in quoted fields." This does not work in v11.21.19. example.csv: 1,"\,\,",42,, 2,",,",23,, create table issue(id int, str varchar(32), val int, empty int); copy into issue from 'example.csv' using delimiters ',' NULL AS ''; The first line with the escaped delimiters works, the second does not: Failed to import table Leftover data '23,,' Is this a bug i should report or am i doing something wrong with the above statements? Regards, Frank [1] https://www.monetdb.org/Documentation/Manuals/SQLreference/CopyInto

2 2

Segfault when using current_date() in SELECT query
by Dennis Pallett 10 Jun '16

10 Jun '16

Hi all, We seem to be getting a segfault on our databases when we try to use the CURRENT_DATE() function in a simple select query. For example: SELECT CURRENT_DATE(), groep.* FROM groep; Is this a (known) bug in MonetDB? Best regards, Dennis Pallett

1 0

Why aren't hashes preserved by joins?
by Roberto Cornacchia 10 Jun '16

10 Jun '16

I noticed a difference between - a hash-based string selection from a persistent, read-only table - a hash-based join on the same table and same column They both build a hash on the same string column (verified with gdb), but the select can reuse the hash (second call is almost free), while the join keeps rebuilding the hash. Is this expected? Roberto

2 4

function IFNULL equivalent?
by Alberto Ferrari 09 Jun '16

09 Jun '16

Hello everybody, I know this question was alreaday made, but I can´t find an answer... Is there a function equivalent to mysql IFNULL() ? If no... has someone created any similar user function? Thanks in advanced

2 2

Constant segmentation faults
by Nikita Salnikov-Tarnovski 07 Jun '16

07 Jun '16

Hello. I am evaluating MonetDB to use in our project, but so far have some real problems with MonetDB constantly having segmentation faults. Here is my setup: I am using AWS EC2 instance with 16GB of RAM, monetdb farm is location on ESB volume. Welcome to mclient, the MonetDB/SQL interactive terminal (Jul2015-SP4) Database: MonetDB v11.21.19 (Jul2015-SP4), 'mapi:monetdb://ip-10-0-0-47:50000/mysql-copy' In there I have several tables, among them is one table I am most interested in, problemsview, with total columnsize of 120GB, largest column is around 11GB. The moment I execute “select count(1) from problemsview;” I get segmentation fault: mserver5[1300]: segfault at 0 ip 00007f86a0ca0712 sp 00007f869b6faa80 error 4 in lib_sql.so[7f86a0c72000+199000] Next select * from problemsview limit 10; wait for 20-30 seconds, and then segmentation fault: mserver5[1313]: segfault at 18 ip 00007f68cf2a0726 sp 00007f68ce14ea80 error 4 in lib_sql.so[7f68cf272000+199000] So essentially I cannot do anything, I always get segmentation fault. Can this be fixed somehow? Thank you in advance, Nikita Salnikov-Tarnovski Plumbr Founder and Master Developer @JavaPlumbr/@iNikem

3 5

segmentation fault with loopback query
by Stefano Fioravanzo 06 Jun '16

06 Jun '16

Hello, I am trying to execute a loopback query in embedded python: CREATE FUNCTION myfunc() RETURNS TABLE(n STRING) LANGUAGE PYTHON { res = _conn.execute("SELECT max(d) FROM mytable;") result = dict() result['n'] = str(res) return result }; When accessing the object ‘res’, mserver5 outputs ‘segmentation fault’ and exits. I think that the issue is in using the max() function in the query, if I leave that out everything works fine. The function: CREATE FUNCTION myfunc() RETURNS TABLE(n STRING) LANGUAGE PYTHON { res = _conn.execute("SELECT d FROM mytable;") result = dict() result['n'] = str(res) return result }; outputs a correct dictionary. Stefano

3 5

group_concat in MAL not working due to algebra.uselect
by Puthick Hok 04 Jun '16

04 Jun '16

Hi, Last year, I was looking for the MAL definition of group_concat and Nik Schuiling gave me the MAL definition which is below. We compile MonetDB from source and this definition was working on MonetDB-11.19.9 (Oct2014-SP2). Since this version, there has been a number of newer versions of MonetDB. So, I was trying to upgrade our system to the latest version of MonetDB (11.21.13). After the installation of the newer version, I ran: select "DataSetId", group_concat("FieldName") from datasetmarkermeta group by "DataSetId"; from mclient interface and got the following error message: TypeException:kddart.subgroup_concat[5]:'algebra.uselect' undefined in: TIDs:any := algebra.uselect(g:bat[:oid,:oid],grpid:oid); MALException:kddart.subgroup_concat[0]:Error in cloned function TypeException:user.s2_1[15]:'kddart.subgroup_concat' undefined in: X_27:bat[:oid,:str] := kddart.subgroup_concat(X_26:bat[:oid,:str],X_16:bat[:oid,:oid],r1_16:bat[:oid,:oid],true:bit); program contains errors It says algebra.uselect function is not defined. How do I fix this problem? group_concat MAL definition start ------------------------------------------- module kddart; command group_concat(b:bat[:oid,:str]):str address KDDARTgroup_concat comment "Mal kddart"; function group_concat(b:bat[:oid, :str]):str; value := ""; barrier (h, t) := iterator.new(b); value := value + t; value := value + ""; redo (h, t) := iterator.next(b); exit (h, t); return value; end group_concat; function subgroup_concat(b:bat[:oid,:any_1],g:bat[:oid,:oid],e:bat[:oid,:any_2],skip_nils:bit) :bat[:oid,:str]; nw := aggr.count(e); nl := calc.lng(nw); bn := bat.new(:oid, :str, nl); # check 'e' has some values - this is the list of group IDs in the head # 'g' is the group to the data BAT head ID mapping # 'b' is the data column BAT that we are aggregating over barrier (grpid, t) := iterator.new(e); # select GID from TID->GID map to get matching TIDs TIDs := algebra.uselect(g,grpid); # get DATA for matching TIDs b_data := algebra.kintersect(b,TIDs); # aggregate grpval := group_concat(b_data); # Store the result for this group bat.insert(bn, grpid, grpval); redo (grpid, t) := iterator.next(e); exit (grpid, t); return bn; end subgroup_concat;

2 3

MonetDB-11.21.19 Compilation failure on JDBC tests
by Ashish Kumar Singh 04 Jun '16

04 Jun '16

All, I am trying to compile monetdb MonetDB-11.21.19 from sources where its failing with error below: prepare: [echo] Debug is true, optimise is false compile_mcl: [echo] Compiling MCL driver: [copy] Copying 1 file to /opt/MonetDB-11.21.19/java/src/main/java/nl/cwi/monetdb/jdbc [echo] Configured build for the 2.19 (Liberica) edition driver compile_jdbc: [echo] Compiling JDBC driver [javac] Compiling 1 source file to /opt/MonetDB-11.21.19/java/tests/jar_jdbctests [javac] This version of java does not support the classic compiler; upgrading to modern jar_jdbc: BUILD FAILED /opt/MonetDB-11.21.19/java/tests/build.xml:75: The following error occurred while executing this line: /opt/MonetDB-11.21.19/java/build.xml:96: java.io.FileNotFoundException: /opt/MonetDB-11.21.19/java/tests/jar_jdbctests/META-INF/services/java.sql.Driver (No such file or directory) Below is my configure output : I am compiling with JDK 8 latest , and ant 1.7 MonetDB is configured as follows: * Compilation specifics: Host: x86_64-unknown-linux-gnu Compiler: gcc (gcc-4.4.7; gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-17) Copyright (C) 2010 Free Software Foundation, Inc. This is free software; see the source for copying conditions. There is NO warranty; not even for MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.) CFLAGS: -g -O2 $(X_CFLAGS) X_CFLAGS: LDFLAGS: * Enabled/disabled build options: strict is disabled (by default) assert is disabled (by default) debug is disabled (by default) optimize is disabled (by default) developer is disabled (by default) instrument is disabled (by default) profile is disabled (by default) * Enabled/disabled components: gdk is enabled monetdb5 is enabled sql is enabled geom is disabled (geos-config not found) console is enabled gsl is disabled (gsl library not found) fits is disabled (cfitsio library not found) netcdf is disabled (netcdf library not found) microhttpd is disabled (by default) rintegration is disabled (libR library not found) odbc is disabled (unixODBC library required for ODBC driver) jdbc is enabled control is enabled testing is enabled developer is disabled (by default) strict is disabled (by default) debug is disabled (by default) assert is disabled (by default) optimize is disabled (by default) * Available features/extensions: atomic_ops = no (atomic_ops library not found) bz2 = no (bz2 library not found) curl = no (libcurl not found) getaddrinfo = yes gsl = no (gsl library not found) java = yes java_control = yes java_jdbc = yes libxml2 = yes openssl = yes pcre = yes perl = yes pthread = yes python2 = yes python3 = no (Python 3 executable not found) readline = no (readline header file not found) rubygem = no (no rubygem executable found) samtools = no (samtools/bam.h header not found) sphinxclient = no (sphinxclient.h header not found) unixodbc = no (odbcinst.h not found) valgrind = no (by default) zlib = yes * Important options: OID size: 64 bits largest integer type: 128 bits largest decimal type: 38 digits

1 0

query json from embedded py
by Stefano Fioravanzo 01 Jun '16

01 Jun '16

Hello, I am trying to query a table with a JSON field with a loopback query in embedded python. I get this error: SQL Query Failed: MALException:pyapi.eval:Unsupported SQL Type: son Is JSON not supported? Do I have to store the JSON object as a string in case I want to query it from python? Thank you, Stefano

2 1

Query database inside embedded R
by Stefano Fioravanzo 01 Jun '16

01 Jun '16

Hello, We are trying to transition from a Postgres database to MonetDB. We had a few R function calls, so we decided to try embedded R. One of our R functions is designed to query the postgres database to get some more data to process. The problem, now, is how to integrate this function in Monet. We are using embeddedR to reduce latency, so using MonetDB.R inside the embedded function (if it is even possible) to query the database would be a total waste. Clearly the ultimate solution would be to make all the queries outside the function, but it would be really really hard to achieve this result given how the function is designed. What I am asking is the is some solution to this, like having some way to query the database inside embedded R avoiding the overhead of MonetDB.R. Thank you, Stefano

2 5

Query database inside embedded R
by Stefano Fioravanzo 31 May '16

31 May '16

Hello, We are trying to transition from a Postgres database to MonetDB. We had a few R function calls, so we decided to try embedded R. One of our R functions is designed to query the postgres database to get some more data to process. The problem, now, is how to integrate this function in Monet. We are using embeddedR to reduce latency, so using MonetDB.R inside the embedded function (if it is even possible) to query the database would be a total waste. Clearly the ultimate solution would be to make all the queries outside the function, but it would be really really hard to achieve this result given how the function is designed. What I am asking is the is some solution to this, like having some way to query the database inside embedded R avoiding the overhead of MonetDB.R. Thank you, Stefano

1 0

embedded R function not working
by Stefano Fioravanzo 30 May '16

30 May '16

Hello, I have the following query: CREATE TABLE mytable AS ( SELECT <fields….> FROM table1 AS a, my_R_function(a.field1, ...) AS asd WHERE <some conditions> ) WITH DATA; MonetDB says: SELECT: no such column ‘a.field1’ The issue is in how I call the R function, since when I remove it everything’s just fine. My aim is to call the function for each row of table1. The new table will have a couple of fields from table1 and a bunch of other fields computed from the R function. How am I supposed to do that? Thank you, Stefano

2 1

MonetDB and KNIME compatibility
by Roman S 26 May '16

26 May '16

Hello. Maybe someone have experience with this kind of problem: I’ve downloaded drivers from http://dev.monetdb.org/downloads/Java/Jul2015-SP4/ <http://dev.monetdb.org/downloads/Java/Jul2015-SP4/> added them to KNIME and now, when i’m trying to make something more difficult than just counting rows in table, I see an error: ERROR Database Reader 0:3 SQL Exception reading Object of type "2005": Operation getCharacterStream() currently not supported - all further errors are suppressed and reported on debug level only ERROR Database Reader 0:3 Execute failed: Cell at index 1 is null!

1 0

storing -1 in a tinyint
by Lynn Carol Johnson 25 May '16

25 May '16

Hi all - I apologize for all the emails, but I am banging on monetdb a lot lately. Today I've run into a problem storing a -1 in a "tinyint" column. My java code writes bytes to a binary file. This file will be loaded via binary bulk load into a "tinyint" column. The values in this column range from -1 to 3. I am writing the negative value to the byte file as below: byte missingData = (byte)-1; writer.writeByte(missingData); The data loads correctly into monetdb, and I can print it: sql>\d minusonetest CREATE TABLE "sys"."minusonetest" ( "chr" INTEGER, "pos" INTEGER, "coelorachis" TINYINT ); sql> sql>select * from minusonetest where chr=2 limit 10; +------+------+-------------+ | chr | pos | coelorachis | +======+======+=============+ | 2 | 1 | -1 | | 2 | 2 | -1 | | 2 | 3 | -1 | | 2 | 4 | -1 | | 2 | 5 | -1 | | 2 | 6 | -1 | | 2 | 7 | -1 | | 2 | 8 | -1 | | 2 | 9 | -1 | | 2 | 10 | -1 | +------+------+-------------+ 10 tuples (10.7s) sql> BUT, when I run the select statement below, it tosses me out of the DB and prints errors to the log file: select * from minusone test where chr=2 and coelorachis > -1; The select fails for any value in the "coelorachis > x" clause above (i.e., > -1, > 0, > 1, etc) If I run the above select and have NOT written any negative numbers to the column, all is good. It only fails if there are negative numbers stored in the tinyint column. The merovingian.log file has these messages: 2016-05-25 12:47:23 ERR testDB[21730]: *** Error in `/usr/bin/mserver5': free(): invalid next size (fast): 0x00007fb0ac003920 *** 2016-05-25 12:47:23 ERR testDB[21730]: ======= Backtrace: ========= 2016-05-25 12:47:23 ERR testDB[21730]: /lib64/libc.so.6(+0x7d023)[0x7fb0df3bc023] 2016-05-25 12:47:23 ERR testDB[21730]: /lib64/libbat.so.12(GDKfree+0x13)[0x7fb0e1b21233] 2016-05-25 12:47:23 ERR testDB[21730]: /lib64/libbat.so.12(BATsubunique+0x18a9)[0x7fb0e1d23f69] 2016-05-25 12:47:23 ERR testDB[21730]: /lib64/libbat.so.12(BATsubunique+0xbe7)[0x7fb0e1d232a7] 2016-05-25 12:47:23 ERR testDB[21730]: /lib64/libbat.so.12(BATimprints+0x581)[0x7fb0e1cef791] 2016-05-25 12:47:23 ERR testDB[21730]: /lib64/libbat.so.12(BATsubselect+0x4ecf)[0x7fb0e1ac532f] 2016-05-25 12:47:23 ERR testDB[21730]: /lib64/libbat.so.12(BATthetasubselect+0x14b)[0x7fb0e1ac78eb] 2016-05-25 12:47:23 ERR testDB[21730]: /lib64/libmonetdb5.so.19(ALGthetasubselect2+0x1c4)[0x7fb0e210db64] 2016-05-25 12:47:23 ERR testDB[21730]: /lib64/libmonetdb5.so.19(runMALsequence+0xdeb)[0x7fb0e20ac55b] 2016-05-25 12:47:23 ERR testDB[21730]: /lib64/libmonetdb5.so.19(+0x45e5b)[0x7fb0e20ade5b] 2016-05-25 12:47:23 ERR testDB[21730]: /lib64/libpthread.so.0(+0x7dc5)[0x7fb0df707dc5] 2016-05-25 12:47:23 ERR testDB[21730]: /lib64/libc.so.6(clone+0x6d)[0x7fb0df43528d] 2016-05-25 12:47:23 ERR testDB[21730]: ======= Memory map: ======== 2016-05-25 12:47:23 ERR testDB[21730]: 00400000-00405000 r-xp 00000000 08:03 22036485 /usr/bin/mserver5 2016-05-25 12:47:23 ERR testDB[21730]: 00604000-00605000 r--p 00004000 08:03 22036485 /usr/bin/mserver5 2016-05-25 12:47:23 ERR testDB[21730]: 00605000-00606000 rw-p 00005000 08:03 22036485 /usr/bin/mserver5 2016-05-25 12:47:23 ERR testDB[21730]: 00606000-00608000 rw-p 00000000 00:00 0 2016-05-25 12:47:23 ERR testDB[21730]: 00892000-01e3e000 rw-p 00000000 00:00 0 [heap] 2016-05-25 12:47:23 ERR testDB[21730]: 7fabab28a000-7fabab29f000 r-xp 00000000 08:03 22020111 /usr/lib64/libgcc_s-4.8.3-20140911.so.1 2016-05-25 12:47:23 ERR testDB[21730]: 7fabab29f000-7fabab49e000 ---p 00015000 08:03 22020111 /usr/lib64/libgcc_s-4.8.3-20140911.so.1 2016-05-25 12:47:23 ERR testDB[21730]: 7fabab49e000-7fabab49f000 r--p 00014000 08:03 22020111 /usr/lib64/libgcc_s-4.8.3-20140911.so.1 2016-05-25 12:47:23 ERR testDB[21730]: 7fabab49f000-7fabab4a0000 rw-p 00015000 08:03 22020111 /usr/lib64/libgcc_s-4.8.3-20140911.so.1 Any ideas? There is no problem if I use Byte.MIN_VALUE instead of -1. This translates to NULL and is selectable. Lynn

1 1

I WAS WRONG ! Re: additional details on IS NULL problem
by Lynn Carol Johnson 24 May '16

24 May '16

After all this, it seems I was doing something stupid. I had originally used Float.MIN_VALUE, which is the smallest POSITIVE minimum value. I also tried hard coding -3.40282e+38, but this had issues as Stephan pointed out. What I neglected to try, and what works for float is public static float flt_nil = -Float.MAX_VALUE; Sorry for all the confusion, but I learned something. Null values for float types DO work. Thanks for your patience and help - Lynn On 5/24/16, 10:44 AM, "users-list on behalf of Lynn Carol Johnson" <users-list-bounces+lcj34=cornell.edu(a)monetdb.org on behalf of lcj34(a)cornell.edu> wrote: >Yes, we’re using float as 32 bit, storing into a real. > >Still no sunshine with your suggestions below. Java doesn’t allow me to >assign > public static float flt_nil = (float) >-340282346638528859811704183484516925440; >It complains “the literal -340282346638528859811704183484516925440 of type >int is out of range”. This isn’t surprising as 32 bits will only hold up >to -2^31 (-2,147,483,648) > >Using static float flt_nil = (float) 0xff7fffff; gives me the value >-8388609.0, which is incorrect. > >Does storing null work for you using “float” types? Is this just a >java-to-C issue? Still looks like we’ll need to pick a default other than >NULL for our “real” data. > >Thanks - Lynn > > >On 5/24/16, 9:53 AM, "users-list on behalf of Stefan Manegold" ><users-list-bounces+lcj34=cornell.edu(a)monetdb.org on behalf of >Stefan.Manegold(a)cwi.nl> wrote: > >>Hi Lynn, >> >>public static float flt_nil = (float) -3.40282e+38; >> >>does not necessarily create the exact bit pattern >>as C macro -FLT_MAX, mainly as "-3.40282e+38" is only an >>approximation of the exact value: >>"-340282346638528859811704183484516925440" >> >>You can either try >> >>public static float flt_nil = (float) >>-340282346638528859811704183484516925440; >> >>or use the actual bit pattern in hex notation, e.g., >> >>public static float flt_nil = (float) 0xff7fffff; >> >>Please take my Java(?) code snippets with a grain of salt; >>I'm everything but a Java expert --- or better, I'm a Java >>illiterate ... >> >>Be also aware that >>C type float, MAL typr :flt and SQL type REAL are 32-bit, >>while >>C type double, MAL type :dbl, and SQL types FLOAT & DOUBLE are 64-bit >> >>Hence, for 64-bit types you'd need to use one of >> >>public static double dbl_nil = (double) >>-179769313486231570814527423731704356798070567525844996598917476803157260 >>7 >>8002853876058955863276687817154045895351438246423432132688946418276846754 >>6 >>7035375169860499105765512820762454900903893289440758685084551339423045832 >>3 >>6903222948165808559332123348274797826204144723168738177180919299881250404 >>0 >>26184124858368; >> >>public static double dbl_nil = (double) 0xffefffffffffffff; >> >> >>Hope this helps ... >> >>Best, >>Stefan >> >>----- On May 24, 2016, at 3:42 PM, Lynn Carol Johnson lcj34(a)cornell.edu >>wrote: >> >>> Thanks, Stephan. >>> >>> Short response good news: using int_nil = ‹2147483648 (-2^31) is >>> translated properly to NULL for int values. The IS NULL and IS NOT >>>NULL >>> queries are fine. >>> >>> Short response bad news: I am unable to get NULL to work for Float >>> regardless of what value I used. Details below: >>> >>> I replaced my Float.MIN_VALUE with flt_nil defined as below : >>> >>> public static float flt_nil = (float) -3.40282e+38; >>> >>> Ran again with just 50 rows (created as binary files). At this point >>>my >>> belief is NULL does not work at all for float with binary files. There >>>are >>> 16 of the >>> 50 values for column amesbyd_weir that are null, but I can only select >>> those values if I check for the column < -3.40282e+38. Here are the >>>sql >>> results: >>> >>> sql>select count(amesbyd_weir) from weirtestnull; >>> +------+ >>>| L1 | >>> +======+ >>>| 50 | >>> +------+ >>> 1 tuple (1.933ms) >>> sql>select chr,pos,amesbyd_weir from weirtestnull where amesbyd_weir IS >>> NULL; >>> +-----+-----+--------------+ >>>| chr | pos | amesbyd_weir | >>> +=====+=====+==============+ >>> +-----+-----+--------------+ >>> 0 tuples (1.291ms) >>> sql>select chr,pos,amesbyd_weir from weirtestnull where amesbyd_weir = >>> -3.40282e+38; >>> +-----+-----+--------------+ >>>| chr | pos | amesbyd_weir | >>> +=====+=====+==============+ >>> +-----+-----+--------------+ >>> 0 tuples (0.528ms) >>> sql> >>> sql>select chr,pos,amesbyd_weir from weirtestnull where amesbyd_weir < >>> -3.40282e+38; >>> +------+--------+-----------------+ >>>| chr | pos | amesbyd_weir | >>> +======+========+=================+ >>>| 10 | 228864 | -3.40282e+38 | >>>| 10 | 228962 | -3.40282e+38 | >>>| 10 | 228972 | -3.40282e+38 | >>>| 10 | 229011 | -3.40282e+38 | >>>| 10 | 229025 | -3.40282e+38 | >>>| 10 | 229187 | -3.40282e+38 | >>>| 10 | 229367 | -3.40282e+38 | >>>| 10 | 229405 | -3.40282e+38 | >>>| 10 | 229444 | -3.40282e+38 | >>>| 10 | 229593 | -3.40282e+38 | >>>| 10 | 229615 | -3.40282e+38 | >>>| 10 | 229671 | -3.40282e+38 | >>>| 10 | 229829 | -3.40282e+38 | >>>| 10 | 230000 | -3.40282e+38 | >>>| 10 | 230052 | -3.40282e+38 | >>>| 10 | 230120 | -3.40282e+38 | >>> +------+--------+-----------------+ >>> 16 tuples (2.999ms) >>> sql> >>> >>> >>> NOTE: Dumping the 50 row database to a .txt file, dropping the table >>>and >>> reloading into monetdb did NOT fix it as it did when I used Float.NaN >>> values. That is actually irrelevant for us as our db tables are large >>> (2.1 Billion rows in 1 table, 80M in another) and as we add additional >>> columns, binary loading is the only way to go. We love the binary load >>> option, and have been very happy with the speed of monetdb processing. >>> >>> For now, we¹re going to follow PLINK convention and use -99 for our >>>Float >>> null values. >>> >>> Thanks - Lynn >>> >>> >>> On 5/23/16, 4:06 PM, "users-list on behalf of Stefan Manegold" >>> <users-list-bounces+lcj34=cornell.edu(a)monetdb.org on behalf of >>> Stefan.Manegold(a)cwi.nl> wrote: >>> >>>>Hi Lynn, >>>> >>>>thanks for diving into this! >>>> >>>>The information that you loaded your data using from binary files using >>>>copy *binary* into is crucial for us to understand and investigate the >>>>problem! >>>> >>>>In fact, I recently discovered and fixed a similar bug; cf., >>>>https://www.monetdb.org/bugzilla/show_bug.cgi?id=3937 >>>> >>>>This fix is in the Jul2013-SP3 bug-fix release. >>>>Hence, I'm wondering why you (still) experience this (or a similar) >>>>problem. >>>> >>>>Of what type are your data / columns? >>>>Integer? decimal? floating point? string? >>>> >>>>If more then one type, do you experience these problems with all types >>>>or just with some? >>>>If only some, which? >>>> >>>>If the problem occurs only with floating point numbers, >>>>are you sure you're using the exact NULL representation that MonetDB >>>>uses? >>>> >>>>Otherwise a potential source of the problem --- I'm just speculating >>>>--- >>>>could be the following: >>>> >>>>You might have "non-valid" floating point numbers like NaN or INF in >>>>your >>>>data >>>>that are not known to SQL and hence MonetDB. >>>>If so, >>>>When parsed from text, MonetDB does (seems to?) silently turn them into >>>>(MonetDB's >>>>internal representation of) NULL. >>>>However, when loding your binary data, MonetDB stores these values >>>>"as-is" >>>>not recognizing them as NULL; then processing does not recognize them >>>>as >>>>NULL, either; >>>>only rendering produces NULL from the "non-valid" internal >>>>representation. >>>> >>>>For the correct internal representation of NULL values in MonetDB per >>>>type see >>>>https://www.monetdb.org/wiki/MonetDB_type_system >>>> >>>>If my speculation is true, we'd need to consider making our copy binary >>>>into >>>>bulk-loading for floating point numbers (more) fail-save by actually >>>>scanning >>>>each column and checking for "invalid" floating point number --- >>>>whatever >>>>that >>>>might cost ... >>>> >>>>Best, >>>>Stefan >>>> >>>>----- On May 23, 2016, at 8:37 PM, Lynn Carol Johnson lcj34(a)cornell.edu >>>>wrote: >>>> >>>>> Hi Anthony - >>>>> >>>>> I¹ve narrowed down the problem. The bug isn¹t related to size, but >>>>>rather how >>>>> the table data was created/loaded. Loading from a .txt file gives >>>>>good >>>>> behavior, loading from binary does not. >>>>> >>>>> Normally I create binary files for each column and use the binary >>>>>bulk >>>>>loader to >>>>> load. That is what was failing. When I first tested with 50 columns, >>>>>I >>>>>created >>>>> the test file from sql via the command >>>>> copy (select * Š) into Œ/workdir/ŠkellyFirst50.txt using delimiters >>>>>Œ\t¹,¹\n¹; >>>>> >>>>> Then I created a new table called kellyFirst50 and used the ³COPY >>>>>INTO >>>>> kellyfirst50 from Œ/workdirŠkellyFirst50.txt¹ using delimiters >>>>>Œ\t,¹\n¹; ² >>>>> command. Checking for ³IS NULL² worked, so I thought it was a size >>>>>problem. >>>>> >>>>> But, when trying to figure out at what point the file size mattered, >>>>>I >>>>>ran java >>>>> scripts to create binary files of ever decreasing sizes, then loaded >>>>>them via >>>>> the COPY BINARY commands. Checking for IS NULL always failed here, >>>>>even >>>>>when I >>>>> only loaded 50 values. >>>>> >>>>> Here are the results when loading from binary files into monetdb. >>>>>Using >>>>>IS NOT >>>>> NULL works, but IS NULL does not: >>>>> >>>>> >>>>> >>>>> sql>\</workdir/lcj34/monetdbFiles/isNull_test/create_weirFSTTest.sql >>>>> >>>>> operation successful >>>>> >>>>> sql>\</workdir/lcj34/monetdbFiles/isNull_test/copy_weirFSTTest.sql >>>>> >>>>> 50 affected rows >>>>> >>>>> >>>>> >>>>> sql> >>>>> >>>>> >>>>> >>>>> >>>>> sql>select count(*) from weirtestnull; >>>>> >>>>> +------+ >>>>> >>>>>| L1 | >>>>> >>>>> +======+ >>>>> >>>>>| 50 | >>>>> >>>>> +------+ >>>>> >>>>> 1 tuple (2.014ms) >>>>> >>>>> sql>select count(amesbyd_weir) from weirtestnull; >>>>> >>>>> +------+ >>>>> >>>>>| L1 | >>>>> >>>>> +======+ >>>>> >>>>>| 50 | >>>>> >>>>> +------+ >>>>> >>>>> >>>>> >>>>> 1 tuple (0.865ms) >>>>> >>>>> sql>select count(amesbyd_weir) from weirtestnull where amesbyd_weir >>>>>IS >>>>>NOT NULL; >>>>> >>>>> +------+ >>>>> >>>>>| L1 | >>>>> >>>>> +======+ >>>>> >>>>>| 34 | >>>>> >>>>> +------+ >>>>> >>>>> 1 tuple (3.005ms) >>>>> >>>>> >>>>> >>>>> sql> >>>>> >>>>> sql>select * from weirtestnull where amesbyd_weir IS NULL; >>>>> >>>>> >>>>>+-----+-----+---------------+--------------+--------------+----------- >>>>>- >>>>>-- >>>>>--+--------------------+------------------+----------------+ >>>>> >>>>>| chr | pos | amesbycn_weir | amesbyd_weir | amesbyf_weir | >>>>>amesbynam_weir | >>>>>| amesbyturkeyp_weir | dentbyflint_weir | dentbynam_weir |> >>>>> >>>>> >>>>>+=====+=====+===============+==============+==============+=========== >>>>>= >>>>>== >>>>>==+====================+==================+================+ >>>>> >>>>> >>>>>+-----+-----+---------------+--------------+--------------+----------- >>>>>- >>>>>-- >>>>>--+--------------------+------------------+----------------+ >>>>> >>>>> 0 tuples (4.041ms) !4 columns dropped! >>>>> >>>>> >>>>> >>>>> sql> >>>>> >>>>> >>>>> Dump the data into a .txt file: >>>>> >>>>> >>>>> >>>>> sql>copy (select * from weirtestnull) into >>>>> '/workdir/db_outfiles/weirtestnull.txt' using delimiters '\t','\n'; >>>>> >>>>> 50 affected rows (3.809ms) >>>>> >>>>> sql> >>>>> >>>>> Now drop the table, recreate it, but this time load from the txt file >>>>>created >>>>> above: >>>>> >>>>> >>>>> >>>>> sql>drop table weirtestnull; >>>>> >>>>> operation successful (1.948ms) >>>>> >>>>> sql>\</workdir/lcj34/monetdbFiles/isNull_test/create_weirFSTTest.sql >>>>> >>>>> operation successful >>>>> >>>>> sql>copy into weirtestnull from >>>>>'/workdir/db_outfiles/weirtestnull.txt' >>>>>using >>>>> delimiters '\t','\n'; >>>>> >>>>> 50 affected rows (57.643ms) >>>>> >>>>> sql> >>>>> >>>>> >>>>> Re-run the commands I now get correct values for IS NULL: >>>>> >>>>> >>>>> >>>>> sql>select count(*) from weirtestnull; >>>>> >>>>> +------+ >>>>> >>>>>| L1 | >>>>> >>>>> +======+ >>>>> >>>>>| 50 | >>>>> >>>>> +------+ >>>>> >>>>> 1 tuple (1.851ms) >>>>> >>>>> sql>select count(amesbyd_weir) from weirtestnull; >>>>> >>>>> +------+ >>>>> >>>>>| L1 | >>>>> >>>>> +======+ >>>>> >>>>>| 34 | >>>>> >>>>> +------+ >>>>> >>>>> 1 tuple (0.983ms) >>>>> >>>>> sql> >>>>> >>>>> sql>select * from weirtestnull where amesbyd_weir IS NULL; >>>>> >>>>> >>>>>+------+--------+------------+------------+------------+------------+- >>>>>- >>>>>-- >>>>>---------+-------------+-------------+-------------+-------------+---- >>>>>- >>>>>-- >>>>>------+ >>>>> >>>>>| chr | pos | amesbycn_w | amesbyd_we | amesbyf_we | amesbynam_ | >>>>>amesbyturke | >>>>>| dentbyflint | dentbynam_w | dentbyturke | flintbynam_ | flintbyturk >>>>>|> >>>>> >>>>> : : : eir : ir : ir : weir : yp_weir : _weir : eir : ypen_weir : weir >>>>>: >>>>> eypen_weir :> >>>>> >>>>> >>>>>+======+========+============+============+============+============+= >>>>>= >>>>>== >>>>>=========+=============+=============+=============+=============+==== >>>>>= >>>>>== >>>>>======+ >>>>> >>>>>| 10 | 228864 | 2.95496e-0 | null | null | -0.0001263 | null | null | >>>>>-0.00028749 >>>>>| | null | -0.00029885 | null | >>>>> >>>>> : : : 5 : : : 25 : : : 4 : : 1 : : >>>>> >>>>>| 10 | 228962 | null | null | null | null | null | null | null | null >>>>>| >>>>>null | >>>>>| null | >>>>> >>>>>| 10 | 228972 | null | null | null | null | null | null | null | null >>>>>| >>>>>null | >>>>>| null | >>>>> >>>>>| 10 | 229011 | null | null | null | null | null | null | null | null >>>>>| >>>>>null | >>>>>| null | >>>>> >>>>>| 10 | 229025 | null | null | null | null | null | null | null | null >>>>>| >>>>>null | >>>>>| null | >>>>> >>>>>| 10 | 229187 | null | null | null | null | null | null | null | null >>>>>| >>>>>null | >>>>>| null | >>>>> >>>>>| 10 | 229367 | null | null | null | null | null | null | null | null >>>>>| >>>>>null | >>>>>| null | >>>>> >>>>>| 10 | 229405 | null | null | null | null | null | null | null | null >>>>>| >>>>>null | >>>>>| null | >>>>> >>>>>| 10 | 229444 | null | null | null | null | null | null | null | null >>>>>| >>>>>null | >>>>>| null | >>>>> >>>>>| 10 | 229593 | null | null | null | null | null | null | null | null >>>>>| >>>>>null | >>>>>| null | >>>>> >>>>>| 10 | 229615 | null | null | null | null | null | null | null | null >>>>>| >>>>>null | >>>>>| null | >>>>> >>>>>| 10 | 229671 | null | null | null | null | null | null | null | null >>>>>| >>>>>null | >>>>>| null | >>>>> >>>>>| 10 | 229829 | null | null | null | null | null | null | null | null >>>>>| >>>>>null | >>>>>| null | >>>>> >>>>>| 10 | 230000 | null | null | null | null | null | null | null | null >>>>>| >>>>>null | >>>>>| null | >>>>> >>>>>| 10 | 230052 | null | null | null | null | null | null | null | null >>>>>| >>>>>null | >>>>>| null | >>>>> >>>>>| 10 | 230120 | null | null | null | null | null | null | null | null >>>>>| >>>>>null | >>>>>| null | >>>>> >>>>> >>>>>+------+--------+------------+------------+------------+------------+- >>>>>- >>>>>-- >>>>>---------+-------------+-------------+-------------+-------------+---- >>>>>- >>>>>-- >>>>>------+ >>>>> >>>>> >>>>> >>>>> 16 tuples (5.2 >>>>> >>>>> >>>>> My java program uses Float.NaN and Integer.MIN_VALUE, Byte.MIN_VALUE >>>>>to >>>>>get >>>>> ³null² stored in the db. >>>>> >>>>> When we load directly from binary, are the null values created >>>>>differently? Is >>>>> something not marked that gets marked when loading from text files? >>>>> >>>>> With this information, can you reproduce, or do you want me to give >>>>>you >>>>>my >>>>> binary files in a dropbox somewhere? >>>>> >>>>> Thanks - Lynn >>>>> >>>>> >>>>> _______________________________________________ >>>>> users-list mailing list >>>>> users-list(a)monetdb.org >>>>> https://www.monetdb.org/mailman/listinfo/users-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) | >>>>_______________________________________________ >>>>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 >> >>-- >>| 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) | >>_______________________________________________ >>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

additional details on IS NULL problem
by Lynn Carol Johnson 24 May '16

24 May '16

2 7

issue with IS NULL
by Lynn Carol Johnson 23 May '16

23 May '16

Good morning - I am having an issue identifying columns with a null value in the July2015-SP3 monetdb version. A sample of my table with null values is below: sql>select * from kelly limit 10; +------+-------+------------+-------------+-------------+-------------+-------------+ | chr | pos | bkn22vsbkn | bkn22vsbkn3 | bkn22vsbkn4 | bkn22vstemp | bkn22vstrop |> : : : 16_weir_an : 5_weir_and_ : 0_weir_and_ : erate_cinta : ical_cinta_ :> : : : d_cockerha : cockerham_f : cockerham_f : _weir_and_c : weir_and_co :> : : : m_fst : st : st : ockerham_fs : ckerham_fst :> : : : : : : t : :> +======+=======+============+=============+=============+=============+=============+ | 1 | 10004 | null | null | null | null | null | | 1 | 10045 | 0.050576 | 0 | 0.154324 | 0.130628 | 0.0398957 | | 1 | 10077 | null | null | null | null | null | | 1 | 10084 | null | null | null | null | null | | 1 | 10097 | null | null | 0.0823631 | 0 | null | | 1 | 10112 | null | null | null | null | null | | 1 | 10128 | 0.00185251 | 0 | 0 | 0.00272515 | 0.00594128 | | 1 | 10162 | null | null | null | null | null | | 1 | 10270 | null | null | null | null | null | | 1 | 10299 | null | null | null | null | null | +------+-------+------------+-------------+-------------+-------------+-------------+ 10 tuples (18.9s) !144 columns dropped! note: to disable dropping columns and/or truncating fields use \w-1 sql> Verify the number of values in the table: sql>select count(1) from kelly; +----------+ | L1 | +==========+ | 83153144 | +----------+ 1 tuple (479.905ms) sql> I would like to pull all values that are NOT null from a specific column. My understanding is that running a “count” on a column should return the count of the non-null values. When I run a count on bkn22vsbkn40_weir_and_cockerham_fst I get the following result, which is the same value as the total number of entries above: sql>select count(bkn22vsbkn40_weir_and_cockerham_fst) from kelly; +----------+ | L1 | +==========+ | 83153144 | +----------+ 1 tuple (860.533ms) sql> The number returned is the same as the total number of entries, but it should be less. However, if I run a query to count the values that are NOT NULL, it counts them: sql>select count(1) from kelly where bkn22vsbkn40_weir_and_cockerham_fst IS NOT NULL; +----------+ | L1 | +==========+ | 41754484 | +----------+ 1 tuple (1.0s) sql> When I try to select column values based on my selected column having a null value, no results are returned. sql>select * from kelly where bkn22vsbkn40_weir_and_cockerham_fst IS NULL limit 10; +-----+-----+-------------------------------------+-------------------------------------+-------------------------------------+ | chr | pos | bkn22vsbkn16_weir_and_cockerham_fst | bkn22vsbkn35_weir_and_cockerham_fst | bkn22vsbkn40_weir_and_cockerham_fst |> +=====+=====+=====================================+=====================================+=====================================+ +-----+-----+-------------------------------------+-------------------------------------+-------------------------------------+ 0 tuples (1.1s) !146 columns dropped! sql> When I select column values based on my selected column value NOT null, I get values. sql>select * from kelly where bkn22vsbkn40_weir_and_cockerham_fst IS NOT NULL limit 10; +------+-------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+--------------+--------------+ | chr | pos | bkn22vsbkn1 | bkn22vsbkn3 | bkn22vsbkn4 | bkn22vstemp | bkn22vstrop | bkn33vsbkn1 | bkn33vsbkn2 | bkn33vsbkn35 | bkn33vsbkn40 |> : : : 6_weir_and_ : 5_weir_and_ : 0_weir_and_ : erate_cinta : ical_cinta_ : 6_weir_and_ : 2_weir_and_ : _weir_and_co : _weir_and_co :> : : : cockerham_f : cockerham_f : cockerham_f : _weir_and_c : weir_and_co : cockerham_f : cockerham_f : ckerham_fst : ckerham_fst :> : : : st : st : st : ockerham_fs : ckerham_fst : st : st : : :> : : : : : : t : : : : : :> +======+=======+=============+=============+=============+=============+=============+=============+=============+==============+==============+ | 1 | 10045 | 0.050576 | 0 | 0.154324 | 0.130628 | 0.0398957 | 0.0289548 | 0 | 0 | 0.124971 | | 1 | 10097 | null | null | 0.0823631 | 0 | null | 0.0406325 | 0.0226116 | 0.0226116 | 0 | | 1 | 10128 | 0.00185251 | 0 | 0 | 0.00272515 | 0.00594128 | null | 0 | null | null | | 1 | 10707 | 0.380632 | 0 | 0 | 0 | 0 | 0.44799 | 0.0206532 | 0.0111431 | 0.0234156 | | 1 | 10751 | 0.0462002 | 0.0439648 | 0.0084101 | 0.0505068 | 0 | 0.0475206 | 0 | 0.0453142 | 0.00522015 | | 1 | 10755 | 0.00147929 | 0 | 0.00290363 | 0.00560938 | 0 | null | 0 | 0 | null | | 1 | 10760 | 0 | 0.011568 | 0 | 0 | 0.0882033 | 0.00590868 | 0 | 0 | 0 | | 1 | 10814 | null | null | 0 | null | null | null | null | null | 0 | | 1 | 10838 | 0.133482 | 0 | 0 | 0.00616907 | 0.146222 | 0.0587818 | 0 | 0 | 0.0621118 | | 1 | 10839 | 0.133482 | 0 | 0 | 0.00616907 | 0.146222 | 0.0639509 | 0 | 0 | 0.0558049 | +------+-------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+--------------+--------------+ 10 tuples (2m 36s) !140 columns dropped! sql> My question: AM I doing something wrong, or is this a bug? I am running the july2015 sp3: [lcj34@cbsudc01 monetdbFiles]$ monetdb -v MonetDB Database Server Toolkit v1.1 (Jul2015-SP3) [lcj34@cbsudc01 monetdbFiles]$ Thanks - Lynn

2 3

Column level 3 problems
by wilco draijer 23 May '16

23 May '16

Hello, When I try to convert my monetdb sql database to rdf using d2rq I get the following error:Problem accessing /directory/genelocation_genelocation. Reason: TODO: column names of level >= 3:SELECT genelocation"."genelocation"."entrez_id" F 50 (E0) This has to do with the bug described in https://www.monetdb.org/bugzilla/show_bug.cgi?id=2526 Is there a way to get around this? Best regards, Wilco

2 1

BLOB
by Shmagi Kavtaradze 22 May '16

22 May '16

Hello community, currently I am using HUGEINT to store data and using UDF I perform bitwise AND on them. I want to store my data in BLOB and perform same actions, but in data mapping table https://www.monetdb.org/wiki/MonetDB_type_system, I was not able to find BLOB mapping for C and MAL. So: 1) What are C and MAL mappings for UDF, if they exist? 2) Is it possible to perform Bitwise AND on blob?

1 0

MonetDB cannot find file to load
by João Victor Risso 19 May '16

19 May '16

Hi All, I am benchmarking MonetDB against other databases, in special the current version of CitusDB's Postgres, to find out which one is a good fit for our OLAP application. In order to do that, I've decided to use a standard benchmark, TPC-H. However, I did not manage to load the data generated by dbgen (one of the tools included in the benchmark) into MonetDB. Here is the SQL statement I've tried to run: > COPY INTO region FROM '/home/user/tpch/dbgen/region.tbl' USING DELIMITERS '|', '\n' LOCKED; and it generated an error which says the file could not be found: > could not open file '/home/user/tpch/dbgen/region.tbl': No such file or directory even though the file exists. I'm running the MonetDB provided in the official repository and the version reported by mclient is: > MonetDB v11.21.19 (Jul2015-SP4) Any help would be appreciated. -- João Victor Tozatti Risso C3SL - Centro de Computação Científica e Software Livre Universidade Federal do Paraná Please consider using PGP encryption to make our communications more secure. You can find my public key at https://pgp.mit.edu/ | Key ID: 395192B8 If you want to know more about PGP encryption, check the following link: https://emailselfdefense.fsf.org/en/

2 1

Monetdb Keep alive in Pentaho
by Ariel Abadi 18 May '16

18 May '16

Hi All! We are using Pentaho (BA Server) and have created a JNDI connection using Monetdb. We are facing some "errors". It seems that the connection to pentaho sometimes drops, therefore wanted to know which will be the correct sintax (if there is one) to send a "keep alive" in the JNDI connection. Thks in advance

1 0

RE: Passing mapi_open Param to mserver5 via monetdbd (Syed M Raza)
by Syed M Raza 17 May '16

17 May '16

Hi Everyone Any help or pointer on starting monetdb server to accept remote connections? Original message attached below. Regards Raza -----Original Message----- Message: 1 Date: Wed, 11 May 2016 13:28:51 +0000 From: Syed M Raza <smr337(a)cornell.edu> To: "users-list(a)monetdb.org" <users-list(a)monetdb.org> Subject: Passing mapi_open Param to mserver5 via monetdbd Message-ID: <BY1PR0401MB1429201171375B1BD9BB46258E720(a)BY1PR0401MB1429.namprd04.prod.outlook.com> Content-Type: text/plain; charset="us-ascii" Hi All Is there a way to pass config params to mserver5 via monetdbd daemon? I am trying to open database for remote connections. After started the dbfarm using monetdbd I see entries in log file that calls mserver5 are made with mapi_open=false. I couldn't figure out how to pass mapi_open=open instead, or for that matter, other params to mserver5. What I understand from documentation that mserver5 should be managed via monetdbd daemon. Or should I try to start mserver5 separately, for each database in the dbfarm? How would those instances then attach to the monetdbd daemon? For what it worth, I did try - creating a monetdb.conf file in /path/to/dbfarm directory - setting map_open using monetdbd set name=value MonetDB v11.21.19 ( Jul2015-SP4) OS - Ubuntu 14.04 LTS Thanks for your help in advance. Regards Raza

4 3

Data caching in MonetDB
by Vijay Krishna 16 May '16

16 May '16

Hi, We were studying on the parallel execution of MonetDB and memory usage of MonetDB. 1. If two queries access the same table in parallel, is the table loaded twice into memory or its loaded only once? 2. Is the hot dataset from a column of a query cached in memory, so that any further queries accessing that column may use it? 3. Are the interim results of a query cached in memory for further queries? Thanks & Regards, Vijayakrishna.P. Mobile : (+91) 9500402305.

1 0

Crystal Language - MonetDB Guide
by Brian Hood 14 May '16

14 May '16

Hi All, I've created a guide for using MonetDB with the Crystal Programming Language. https://github.com/puppetpies/crystal-monetdb-libmapi/blob/master/examples/… Enjoy! Regards, Brian Hood

1 0

Error when trying to load GO.db
by Willem Ligtenberg 12 May '16

12 May '16

Hi, I am trying to run some R code in database. Most of it is going pretty well, but I seem to have stumbled on a bug. I cannot load the following package, which is a dependency for some of my code. WGCNA I have been chasing it down, and it seems to be due to an error when trying to load GO.db. I get the following error: Error in .local(conn, statement, ...) : Unable to execute statement 'SELECT * FROM tmp_test();'. Server says '!Error running R expression. Error message: Error in as.data.frame((function() { : '. Digging a bit further it seems to be due to the following statement: dbconn <- dbFileConnect(dbfile) Error can be reproduced using: functionDef <- paste( "CREATE FUNCTION tmp_test() ", "RETURNS TABLE(output STRING)", "LANGUAGE R ", "{", "library(AnnotationDbi)", "datacache <- new.env(hash=TRUE, parent=emptyenv())", "pkgname <- 'GO.db'", "libname <- .libPaths()[1]", "dbfile <- system.file('extdata', 'GO.sqlite', package=pkgname, lib.loc=libname)", "assign('dbfile', dbfile, envir=datacache)", "dbconn <- dbFileConnect(dbfile)", "};", sep = "\n") dbGetQuery(conn, functionDef) dbGetQuery(conn, "SELECT * FROM tmp_test();") By the way, installing GO.db from within MonetDB works just fine. And can be done using the following R code: source("https://bioconductor.org/biocLite.R") biocLite("GO.db") Hints on how to resolve this are greatly appreciated. Kind regards, Willem

2 1

creating a table with char[3]
by Lynn Carol Johnson 11 May '16

11 May '16

Hi All - I'm trying to create a table with specific size for text fields. In particular, I am storing a 3 byte character and an 8 byte character. I tried the following but get errors: CREATE TABLE te_LTRTest(chr int, pos int, te_gag_protein tinyint, te_ap_protein tinyint, te_integrase_protein tinyint, te_rt_protein tinyint, te_rnaseh_protein tinyint, te_chromodomain_protein tinyint, te_envelope_protein tinyint, te_ltr_age real, te_numb_ltrtes_inside integer, te_superfamily char'('3')', te_family char'('8')'); Gives the error: sql>\</workdir/lcj34/monetdbFiles/michelle_TE/create_teLTRTest.sql syntax error, unexpected STRING, expecting ')' or ',' in: "create table te_ltrtest(chr int, pos int, te_gag_protein tinyint, te_ap_protein" sql> Switching to this: CREATE TABLE te_LTRTest(chr int, pos int, te_gag_protein tinyint, te_ap_protein tinyint, te_integrase_protein tinyint, te_rt_protein tinyint, te_rnaseh_protein tinyint, te_chromodomain_protein tinyint, te_envelope_protein tinyint, te_ltr_age real, te_numb_ltrtes_inside integer, te_superfamily char[3], te_family char[8]); Gives the following error: sql>\</workdir/lcj34/monetdbFiles/michelle_TE/create_teLTRTest.sql syntax error, unexpected '[', expecting ')' or ',' in: "create table te_ltrtest(chr int, pos int, te_gag_protein tinyint, te_ap_protein" sql> I have 2.1 billion rows so am using the binary bulk loads. My non-numeric binary files were created to contain 3-byte values and 8-byte values. This is what I am trying to load, however I'm not getting past the create table stage. I love the binary loader. This is the first time I've needed to include text data in my tables. Is this supported? I'm hoping it is just my syntax. Any suggestions? Thanks - Lynn

4 6

Can not import 128 bit values
by Shmagi Kavtaradze 11 May '16

11 May '16

I create table with HUGEINT type. When I am trying to import data from text file, Monetdb fires error: Failed to import table line 508 field 3 'hugeint' expected in '170141183460469231731687303715888300032', but this value has exactly 128 bits. Any ideas how to fix the issue? Thanks.

2 2

Passing mapi_open Param to mserver5 via monetdbd
by Syed M Raza 11 May '16

11 May '16

Hi All Is there a way to pass config params to mserver5 via monetdbd daemon? I am trying to open database for remote connections. After started the dbfarm using monetdbd I see entries in log file that calls mserver5 are made with mapi_open=false. I couldn't figure out how to pass mapi_open=open instead, or for that matter, other params to mserver5. What I understand from documentation that mserver5 should be managed via monetdbd daemon. Or should I try to start mserver5 separately, for each database in the dbfarm? How would those instances then attach to the monetdbd daemon? For what it worth, I did try - creating a monetdb.conf file in /path/to/dbfarm directory - setting map_open using monetdbd set name=value MonetDB v11.21.19 ( Jul2015-SP4) OS - Ubuntu 14.04 LTS Thanks for your help in advance. Regards Raza Consultant, GOBII project Genomics & Open-source Breeding Informatics Initiative Boyce Thompson Institute # 224 Cornell University Ithaca, NY 14853

1 0

monetdb is great, thanks everyone who makes it so
by Anthony Damico 09 May '16

09 May '16

4 3

Crystal binding MonetDB / Example project
by Brian Hood 08 May '16

08 May '16

Hi Guys, If your interested in Programming Languages i've recently built the binding for MonetDB in the Crystal Language if you don't know that Crystal is its a Ruby inspired language that is statically typed and compiles in byte code via LLVM yes interesting. Here is the Driver as they used to call them. https://github.com/puppetpies/crystal-monetdb-libmapi Very small example project that i will expand upon. https://github.com/puppetpies/crystal-monetdb-examples Also yes i will write a blog as well Martin i'm not sure what want me to write but i am put together a reference guide for people that might come along in the future to use this. Regards, Brian Hood

1 0

Memory management - MonetDB on CentOS
by Vijay Krishna 07 May '16

07 May '16

Hi, We have been running tests on Memory management with the latest version of MonetDB on a CentOS installation. The machine has 1TB hard disk and 128 GB RAM space. The swap memory configured is 2GB. We were aware that MonetDB completely relies on the OS for Memory Management and is prone to OOM Killer crash and hence we wanted to simulate the crash. Hence we ran hundreds of threads executing various queries and noticed the following pattern of memory handling. 1. Initially, RAM was filled. 2. Upon RAM overflow, swap memory (2 GB) was used. 3. Upon swap overflow, the swap memory got extended and the 1TB memory that was free on the machine was used completely. 4. The OOM killer got invoked only when this 1TB memory got filled. 5. On consecutive executions, swap memory was started to be used even when RAM had 70GB of memory unused and free (not even used for cache files). 6. The /tmp partition was never used. Memory got overfilled only in the current partition. These observations led to the following questions about the memory management with CentOS. 1. Does MonetDB has no influence on the above behaviour and is it that only the OS is responsible for this? 2. Will the swap memory be expanded from the committed size by the OS if swap is running out of memory? In our case it expanded from 2GB to 1 TB. Our OS setting of overcommit is 0 (sysctl vm.overcommit_memory = 0) 3. When will the OOM Killer be invoked usually? Upon Swap fill or upon Hard disk space full? 4. Why was memory swapped even when there is 70GB free space in RAM? We tried reducing the swappiness to 10 (sysctl vm.swappiness=10), even then memory was swapped with 70 GB free space in RAM. 5. Does the OS writes intermediate results to /tmp directory? Or is it that MonetDB is bypassing this? 6. Is there any other OS configurations that we need to cross check regarding memory management? Any help much appreciated. Thanks in advance. Regards, Vijay.

1 0

index clustering
by Shmagi Kavtaradze 06 May '16

06 May '16

When searching for index clustering, I read that no user defined index clustering exists in Monetdb. Does it mean that I can not cluster table according to index? If not, how can I sort table to speed-up query? Thanks in advance.

2 1

Problem with RANK/DENSE_RANK in a subquery (dense_rank_grp undefined)
by Knezevic Nikola 04 May '16

04 May '16

Hi all, I have several tables which represent different sources (sensors) of data. I want to find out which sensors picked up the signal first, and thus I decided to join these tables together, rank on common criteria and then select the first ones. However, that fails with: TypeException:user.s22_3[110]:'calc.dense_rank_grp' undefined in: calc.dense_rank_grp(X_505:date,X_507:str,X_509:int,X_511:int,X_513:lng); program contains errors Any ideas how to fix this? Cheers, Nikola Data: CREATE TABLE taba ( aday DATE, key1 varchar(15), msid int, sid int, eid bigint, r bigint ); CREATE TABLE tabb ( aday DATE, key1 varchar(15), msid int, sid int, aid bigint, eid bigint, r bigint ); CREATE TABLE tabc ( aday DATE, key1 varchar(15), msid int, sid int, aid bigint, m bigint, r bigint ); COPY 3 RECORDS INTO taba FROM stdin USING DELIMITERS '|','\n'; 2016-05-02|inst1|675|10001|200|100 2016-05-02|inst1|675|10002|500|111 2016-05-02|inst1|789|10001|800|135 COPY 3 RECORDS INTO tabb FROM stdin USING DELIMITERS '|','\n'; 2016-05-02|inst1|675|10001|77|200|88 2016-05-02|inst1|675|10002|100|500|112 2016-05-02|inst1|789|10001|112|800|134 COPY 3 RECORDS INTO tabc FROM stdin USING DELIMITERS '|','\n'; 2016-05-02|inst1|675|10001|77|200|100 2016-05-02|inst1|675|10002|100|500|111 2016-05-02|inst1|789|10001|112|800|130 Query: SELECT aday, key1, msid, sid, aid, eid, src, r, rank FROM ( SELECT * ,sys.DENSE_RANK() OVER (PARTITION BY aday, key1, msid, sid, eid ORDER BY r) as rank FROM ( (SELECT aday, key1, msid, sid, NULL as aid, eid, 'TABA' as src, r FROM taba) UNION (SELECT aday, key1, msid, sid, aid, eid, 'TABB' as src, r FROM tabb) UNION (SELECT aday, key1, msid, sid, aid, m as eid, 'TABC' AS src, r FROM tabc) ) AS dummy1 ) AS dummy2 WHERE rank = 1 ; Results: sql>CREATE TABLE taba ( more>aday DATE, more>key1 varchar(15), more>msid int, more>sid int, more>eid bigint, more>r bigint more>); operation successful (4.102ms) sql>CREATE TABLE tabb ( more>aday DATE, more>key1 varchar(15), more>msid int, more>sid int, more>aid bigint, more>eid bigint, more>r bigint more>); operation successful (3.172ms) sql>CREATE TABLE tabc ( more>aday DATE, more>key1 varchar(15), more>msid int, more>sid int, more>aid bigint, more>m bigint, more>r bigint more>); operation successful (3.326ms) sql> sql>COPY 3 RECORDS INTO taba FROM stdin USING DELIMITERS '|','\n'; more>2016-05-02|inst1|675|10001|200|100 more>2016-05-02|inst1|675|10002|500|111 more>2016-05-02|inst1|789|10001|800|135 3 affected rows (88.249ms) sql> sql>COPY 3 RECORDS INTO tabb FROM stdin USING DELIMITERS '|','\n'; more>2016-05-02|inst1|675|10001|77|200|88 more>2016-05-02|inst1|675|10002|100|500|112 more>2016-05-02|inst1|789|10001|112|800|134 3 affected rows (59.917ms) sql> sql>COPY 3 RECORDS INTO tabc FROM stdin USING DELIMITERS '|','\n'; more>2016-05-02|inst1|675|10001|77|200|100 more>2016-05-02|inst1|675|10002|100|500|111 more>2016-05-02|inst1|789|10001|112|800|130 3 affected rows (89.804ms) sql> sql>select * from taba; +------------+-------+------+-------+------+------+ | aday | key1 | msid | sid | eid | r | +============+=======+======+=======+======+======+ | 2016-05-02 | inst1 | 675 | 10001 | 200 | 100 | | 2016-05-02 | inst1 | 675 | 10002 | 500 | 111 | | 2016-05-02 | inst1 | 789 | 10001 | 800 | 135 | +------------+-------+------+-------+------+------+ 3 tuples (4.340ms) sql>select * from tabb; +------------+-------+------+-------+------+------+------+ | aday | key1 | msid | sid | aid | eid | r | +============+=======+======+=======+======+======+======+ | 2016-05-02 | inst1 | 675 | 10001 | 77 | 200 | 88 | | 2016-05-02 | inst1 | 675 | 10002 | 100 | 500 | 112 | | 2016-05-02 | inst1 | 789 | 10001 | 112 | 800 | 134 | +------------+-------+------+-------+------+------+------+ 3 tuples (3.484ms) sql>select * from tabc; +------------+-------+------+-------+------+------+------+ | aday | key1 | msid | sid | aid | eid | m | +============+=======+======+=======+======+======+======+ | 2016-05-02 | inst1 | 675 | 10001 | 77 | 200 | 100 | | 2016-05-02 | inst1 | 675 | 10002 | 100 | 500 | 111 | | 2016-05-02 | inst1 | 789 | 10001 | 112 | 800 | 130 | +------------+-------+------+-------+------+------+------+ 3 tuples (3.215ms) sql>SELECT * more>FROM ( more>SELECT * more> ,sys.DENSE_RANK() OVER (PARTITION BY aday, key1, msid, sid, eid ORDER BY r) as rank more> FROM ( more> (SELECT aday, key1, msid, sid, NULL as aid, eid, 'TABA' as src, r FROM taba) more> UNION more> (SELECT aday, key1, msid, sid, aid, eid, 'TABB' as src, r FROM tabb) more> UNION more> (SELECT aday, key1, msid, sid, aid, m as eid, 'TABC' AS src, r FROM tabc) more> ) AS dummy1 more>) AS dummy2; +------------+-------+------+-------+------+------+------+------+------+ | aday | key1 | msid | sid | aid | eid | src | r | rank | +============+=======+======+=======+======+======+======+======+======+ | 2016-05-02 | inst1 | 675 | 10001 | null | 200 | TABA | 100 | 2 | | 2016-05-02 | inst1 | 675 | 10002 | null | 500 | TABA | 111 | 1 | | 2016-05-02 | inst1 | 789 | 10001 | null | 800 | TABA | 135 | 3 | | 2016-05-02 | inst1 | 675 | 10001 | 77 | 200 | TABB | 88 | 1 | | 2016-05-02 | inst1 | 675 | 10002 | 100 | 500 | TABB | 112 | 2 | | 2016-05-02 | inst1 | 789 | 10001 | 112 | 800 | TABB | 134 | 2 | | 2016-05-02 | inst1 | 675 | 10001 | 77 | 200 | TABC | 100 | 2 | | 2016-05-02 | inst1 | 675 | 10002 | 100 | 500 | TABC | 111 | 1 | | 2016-05-02 | inst1 | 789 | 10001 | 112 | 800 | TABC | 130 | 1 | +------------+-------+------+-------+------+------+------+------+------+ 9 tuples (13.527ms) sql>SELECT aday, key1, msid, sid, aid, eid, src, r, rank more>FROM ( more>SELECT * more> ,sys.DENSE_RANK() OVER (PARTITION BY aday, key1, msid, sid, eid ORDER BY r) as rank more> FROM ( more> (SELECT aday, key1, msid, sid, NULL as aid, eid, 'TABA' as src, r FROM taba) more> UNION more> (SELECT aday, key1, msid, sid, aid, eid, 'TABB' as src, r FROM tabb) more> UNION more> (SELECT aday, key1, msid, sid, aid, m as eid, 'TABC' AS src, r FROM tabc) more> ) AS dummy1 more>) AS dummy2 more>WHERE rank = 1 more>; TypeException:user.s22_3[110]:'calc.dense_rank_grp' undefined in: calc.dense_rank_grp(X_505:date,X_507:str,X_509:int,X_511:int,X_513:lng); program contains errors

2 2

PCRE: references to captures in replace string
by Roberto Cornacchia 04 May '16

04 May '16

After declaring a wrapper for pcre.replace: sql>create function pcre_replace(s string, pattern string, repl string, flags string) returns string external name pcre."replace"; operation successful (6.187ms) I cannot get replacements with references to captures work (but the code in pcre.c seems to support it): sql>select pcre_replace('Apple Pear', '^([a-zA-Z]+)\\s*([a-zA-Z]+)$', '\\1 \\2', ''); +---------------------------+ | pcre_replace_single_value | +===========================+ | \1 \2 | +---------------------------+ 1 tuple (0.309ms) The pattern is matched, but the back references are replaced literally. Am I not getting the syntax correct? >From http://www.pcre.org/original/doc/html/pcresyntax.html I see I could use any of \1, \g1, \g{1] for back references, but none seem to work. Roberto

3 7

Problem: 'ALTER TABLE x ADD TABLE y' does not merge the data in from y.
by Knezevic Nikola 04 May '16

04 May '16

Hi all, I'm splitting my data into per-day tables and importing them into monetdb. Before the import, I first create a per-day table and then use 'ALTER TABLE "table" ADD TABLE "table__2016-05-03";' command (passed to cursor.execute() in python. "table" is defined to be a MERGE TABLE. Now, I had only a single day that was populated on the initial run, when both MERGE TABLE and day table were created. Today, I added a new day table and ran this: sql> SELECT day, count(*) FROM "table" GROUP BY day; +------------+---------+ | day | L1 | +============+=========+ | 2016-04-26 | 2959601 | +------------+---------+ sql>select day, count(*) from "trades__2016-05-02" group by day; +------------+---------+ | day | L1 | +============+=========+ | 2016-05-02 | 2220143 | +------------+---------+ So, the output is missing the current day, and I thought my python code didn't properly tie these tables together. However, inspecting sys.* tables gives me this: sql>\d (snip...) MERGE TABLE my_namespace.trades TABLE my_namespace.trades__2016-04-26 TABLE my_namespace.trades__2016-05-02 (snip...) sql>select * from sys.tables where name like 'trades%'; +-------+--------------------+-----------+-------+------+--------+---------------+--------+-----------+ | id | name | schema_id | query | type | system | commit_action | access | temporary | +=======+====================+===========+=======+======+========+===============+========+===========+ | 13480 | trades | 6497 | null | 3 | false | 0 | 0 | 0 | | 13518 | trades__2016-04-26 | 6497 | null | 0 | false | 0 | 0 | 0 | | 16203 | trades__2016-05-02 | 6497 | null | 0 | false | 0 | 0 | 0 | +-------+--------------------+-----------+-------+------+--------+---------------+--------+-----------+ 3 tuples (3.323ms) sql>select * from sys.dependencies where depend_id=13480; +-------+-----------+-------------+ | id | depend_id | depend_type | +=======+===========+=============+ | 13518 | 13480 | 2 | | 16203 | 13480 | 2 | +-------+-----------+-------------+ Finally, if I manually add the table, it succeeds: sql>alter table "trades" add table "trades__2016-05-02"; operation successful (13.894ms) sql>select day, count(*) from "trades" group by day; +------------+---------+ | day | L1 | +============+=========+ | 2016-04-26 | 2959601 | | 2016-05-02 | 2220143 | +------------+---------+ 2 tuples (44.196ms) sql>select * from sys.tables where name like 'trades%'; +-------+--------------------+-----------+-------+------+--------+---------------+--------+-----------+ | id | name | schema_id | query | type | system | commit_action | access | temporary | +=======+====================+===========+=======+======+========+===============+========+===========+ | 13480 | trades | 6497 | null | 3 | false | 0 | 0 | 0 | | 13518 | trades__2016-04-26 | 6497 | null | 0 | false | 0 | 0 | 0 | | 16203 | trades__2016-05-02 | 6497 | null | 0 | false | 0 | 0 | 0 | +-------+--------------------+-----------+-------+------+--------+---------------+--------+-----------+ 3 tuples (7.445ms) sql>select * from sys.dependencies where depend_id=13480; +-------+-----------+-------------+ | id | depend_id | depend_type | +=======+===========+=============+ | 13518 | 13480 | 2 | | 16203 | 13480 | 2 | +-------+-----------+-------------+ 2 tuples (1.714ms) For completeness, python code looks like this (cleaned up, to present the case, keep in mind that on initial import, this worked): if args.create: if args.partition and has_day: dest_create_merge_table = re.sub('CREATE TABLE', 'CREATE MERGE TABLE', dest_create_table, count=1) print("Creating table with the following content:\n{ddl}".format(ddl=dest_create_merge_table), file=sys.stderr) try: cursor = monet_conn.cursor() cursor.execute(dest_create_merge_table) except monetdb.exceptions.OperationalError as e: if re.search('CREATE TABLE: name .* already in use', e.args[0]): print("Master table already present") else: raise(e) finally: cursor.close() if args.partition and has_day: cursor = monet_conn.cursor() for c in conditions: (text, day, sth) = c dest_create_partition = re.sub('CREATE TABLE "{table}"'.format(table=table), 'CREATE TABLE "{partition}"'.format(partition=partition_name(table, day)), dest_create_table, count=1) print("Creating partition with the following content:\n{ddl}".format(ddl=dest_create_partition), file=sys.stderr) cursor.execute(dest_create_partition) if args.debug: print('ALTER TABLE "{table}" ADD TABLE "{partition}"'.format(table=table, partition=partition_name(table, day))) cursor.execute('ALTER TABLE "{table}" ADD TABLE "{partition}"; COMMIT;'.format(table=table, partition=partition_name(table, day))) cursor.close() Can someone please help me debug this issue? Is this a bug? IIUC, metadata points that "trades__2016-05-02" belongs to "trades", but the queries do not reflect that. Thanks, Nikola

3 5

Crashed DB. cannot start DB
by Ariel Abadi 03 May '16

03 May '16

Hi! Can someone help me please with this issue? Im trying to start my db.. and Im getting the following erro from the log: 2016-05-03 15:08:38 MSG merovingian[14646]: starting database 'bm_traf', up min/avg/max: 3d/5d/1w, crash average: 1.00 0.40 0.13 (6-2=4) 2016-05-03 15:08:38 MSG bm_traf[28169]: arguments: /usr/bin/mserver5 --dbpath=/opt/monetdb/bm/bm_traf --set merovingian_uri=mapi:monetdb://monetdb:50000/bm_traf --set mapi_open=false --set mapi_port=0 --set mapi_usock=/opt/monetdb/bm/bm_traf/.mapi.sock --set monet_vault_key=/opt/monetdb/bm/bm_traf/.vaultkey --set gdk_nr_threads=4 --set max_clients=64 --set sql_optimizer=default_pipe --set monet_daemon=yes 2016-05-03 15:08:38 MSG merovingian[14646]: database 'bm_traf' (28169) has exited with exit status 1 2016-05-03 15:08:43 ERR merovingian[14646]: client error: database 'bm_traf' appears to shut itself down after starting, check monetdbd's logfile for possible hints Does anybody has a clue? Thks in advance!

2 2

Query Histroy & query parsing / translation / compilation / optimization time
by Stefan Manegold 03 May '16

03 May '16

Hi, looking for ways to measure the query parsing / translation / compilation / optimization time in MonetDB, I found these two documentations on the MonetDB website: https://www.monetdb.org/Documentation/Cookbooks/SQLrecipes/QueryHistory and https://www.monetdb.org/Documentation/Cookbooks/SQLrecipes/QueryHistoryNew While the documentation is not entirely clear on whether the latter did, will, or is supposed to replace the former (and if so, when), using the latest Jul2015-SP4 release, I get with the former: sql>set history=true; operation successful (137.545ms) sql>select 1; +--------------+ | single_value | +==============+ | 1 | +--------------+ 1 tuple (155.577ms) sql>select 2; +--------------+ | single_value | +==============+ | 2 | +--------------+ 1 tuple (193.633ms) sql>select * from sys.queryHistory; SELECT: no such table 'queryhistory' sql>select * from sys.callHistory; SELECT: no such table 'callhistory' sql>select * from sys.queryLog; SELECT: no such table 'querylog' sql>call sys.resetHistory(); SELECT: no such operator 'resethistory' I.e, this does not seem to work (anymore?). The latter seems to work fine: sql>call sys.querylog_enable(); sql>select 1; +--------------+ | single_value | +==============+ | 1 | +--------------+ 1 tuple (127.056ms) sql>select 1; +--------------+ | single_value | +==============+ | 1 | +--------------+ 1 tuple (124.303ms) sql>select 2; +--------------+ | single_value | +==============+ | 2 | +--------------+ 1 tuple (148.923ms) sql>select * from sys.querylog_catalog; +------+---------+----------------------------+-------------------------------------+--------------+-----------+------+----------+ | id | owner | defined | query | pipe | plan | mal | optimize | +======+=========+============================+=====================================+==============+===========+======+==========+ | 42@0 | monetdb | 2016-05-03 00:06:44.000000 | call sys.querylog_enable(); | default_pipe | user.s6_1 | 19 | 267 | | 43@0 | monetdb | 2016-05-03 00:06:47.000000 | select 1; | default_pipe | user.s7_1 | 21 | 273 | | 44@0 | monetdb | 2016-05-03 00:06:48.000000 | select 1; | default_pipe | user.s7_1 | 21 | 273 | | 45@0 | monetdb | 2016-05-03 00:06:50.000000 | select 1; | default_pipe | user.s7_1 | 21 | 273 | | 46@0 | monetdb | 2016-05-03 00:06:56.000000 | select * from sys.querylog_catalog; | default_pipe | user.s8_1 | 68 | 665 | +------+---------+----------------------------+-------------------------------------+--------------+-----------+------+----------+ 5 tuples (132.446ms) sql>select * from sys.querylog_calls; +------+----------------------------+----------------------------+------------------------------------------+--------+-------+------+------+------+ | id | start | stop | arguments | tuples | run | ship | cpu | io | +======+============================+============================+==========================================+========+=======+======+======+======+ | 41@0 | 2016-05-03 00:06:38.000000 | 2016-05-03 00:06:39.000000 | user.s5_1{autoCommit=true}():void; | 1 | 85485 | 1 | 3 | 24 | | 42@0 | 2016-05-03 00:06:44.000000 | 2016-05-03 00:06:44.000000 | user.s6_1{autoCommit=true}():void; | 1 | 71 | 0 | 0 | 0 | | 43@0 | 2016-05-03 00:06:47.000000 | 2016-05-03 00:06:47.000000 | user.s7_1{autoCommit=true}(A0:bte):void; | 1 | 120 | 0 | 0 | 0 | | 44@0 | 2016-05-03 00:06:48.000000 | 2016-05-03 00:06:48.000000 | user.s7_1{autoCommit=true}(A0:bte):void; | 1 | 104 | 0 | 0 | 0 | | 45@0 | 2016-05-03 00:06:50.000000 | 2016-05-03 00:06:50.000000 | user.s7_1{autoCommit=true}(A0:bte):void; | 1 | 66 | 1 | 0 | 0 | | 46@0 | 2016-05-03 00:06:56.000000 | 2016-05-03 00:06:56.000000 | user.s8_1{autoCommit=true}():void; | 8 | 886 | 0 | 50 | 0 | +------+----------------------------+----------------------------+------------------------------------------+--------+-------+------+------+------+ 6 tuples (100.344ms) sql>select * from sys.querylog_history; +------+---------+----------------------------+-------------------------------------+--------------+-----------+------+----------+----------------------------+----------------------------+------------------------------------------+--------+------+------+------+------+ | id | owner | defined | query | pipe | plan | mal | optimize | start | stop | arguments | tuples | run | ship | cpu | io | +======+=========+============================+=====================================+==============+===========+======+==========+============================+============================+==========================================+========+======+======+======+======+ | 42@0 | monetdb | 2016-05-03 00:06:44.000000 | call sys.querylog_enable(); | default_pipe | user.s6_1 | 19 | 267 | 2016-05-03 00:06:44.000000 | 2016-05-03 00:06:44.000000 | user.s6_1{autoCommit=true}():void; | 1 | 71 | 0 | 0 | 0 | | 43@0 | monetdb | 2016-05-03 00:06:47.000000 | select 1; | default_pipe | user.s7_1 | 21 | 273 | 2016-05-03 00:06:47.000000 | 2016-05-03 00:06:47.000000 | user.s7_1{autoCommit=true}(A0:bte):void; | 1 | 120 | 0 | 0 | 0 | | 44@0 | monetdb | 2016-05-03 00:06:48.000000 | select 1; | default_pipe | user.s7_1 | 21 | 273 | 2016-05-03 00:06:48.000000 | 2016-05-03 00:06:48.000000 | user.s7_1{autoCommit=true}(A0:bte):void; | 1 | 104 | 0 | 0 | 0 | | 45@0 | monetdb | 2016-05-03 00:06:50.000000 | select 1; | default_pipe | user.s7_1 | 21 | 273 | 2016-05-03 00:06:50.000000 | 2016-05-03 00:06:50.000000 | user.s7_1{autoCommit=true}(A0:bte):void; | 1 | 66 | 1 | 0 | 0 | | 46@0 | monetdb | 2016-05-03 00:06:56.000000 | select * from sys.querylog_catalog; | default_pipe | user.s8_1 | 68 | 665 | 2016-05-03 00:06:56.000000 | 2016-05-03 00:06:56.000000 | user.s8_1{autoCommit=true}():void; | 8 | 886 | 0 | 50 | 0 | | 47@0 | monetdb | 2016-05-03 00:07:33.000000 | select * from sys.querylog_calls; | default_pipe | user.s9_1 | 73 | 282 | 2016-05-03 00:07:33.000000 | 2016-05-03 00:07:34.000000 | user.s9_1{autoCommit=true}():void; | 9 | 2515 | 0 | 0 | 0 | +------+---------+----------------------------+-------------------------------------+--------------+-----------+------+----------+----------------------------+----------------------------+------------------------------------------+--------+------+------+------+------+ 6 tuples (152.709ms) Hence, maybe the documentation should be updated to remove the former? Also, two questions remain unanswered by the current documentation: 1) What is the unit of "mal integer, -- size of MAL plan"? bytes? lines? statements? 2) What exactly does "optimize bigint -- time in microseconds" measure? Only the time taken by MAL optimizers (i.e., after SQL parsing, relational optimization and relational-to-MAL translation, but before actual execution)? If so, is there still a way to measure the time(s) for SQL parsing, relational optimization, and (or) relational-to-MAL translation? Thanks! Stefan -- | 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) |

1 0

Help with json.filters
by Alberto Ferrari 03 May '16

03 May '16

Can anyone help with json.filters? I have this example table ... CREATE TABLE tblJson ( "id" INT, "json_data" JSON, ); ... with these values... INSERT INTO tblJson (id, json_data) VALUES (1, '{"data":{"time":"2015-10-09 10:36:35","others":"xxx"}}'), (2, '{"data":{"time":"2015-10-09 12:18:20","others":"yyy"}}'), (3, '{"data":{"time":"2015-12-30 16:31:22","others":"zzz"}}'); The data is ok, I can select the rows, it's ok. Also I can select only the "time" field, like this... SELECT json.filter(json_data, 'data.time') FROM tblJson; But now I want to filter the content in "json_data" specific field, i.e. SELECT all the rows where "time like '2015-10-09%'" , or maybe "time = '2015-10-09 12:18:20'" I tried but can't do it. Any help? Thanks!

3 5

Problems with aggregate UDF (subaggregate undefined error)
by Anderson, David B 02 May '16

02 May '16

All, I have been able to create and install my own aggregate UDF (weightedpercentile), but when I try to execute it via mclient I am getting the following error: sql>select key, weightedpercentile(a,b) from t group by key; TypeException:user.s9_1[20]:'udf.subweightedpercentile' undefined in: X_34:bat[:any,:dbl] := udf.subweightedpercentile(X_26:bat[:oid,:dbl],X_33:bat[:oid,:dbl],X_16:bat[:oid,:oid],r1_16:bat[:oid,:oid],true:bit); program contains errors Does anyone know what I am doing incorrectly? I am at a total loss now. The error is almost identical to the error I get if I try to execute the corr() aggregate: sql>select key, corr(a,b) from t group by key; TypeException:user.s10_1[20]:'aggr.subcorr' undefined in: X_34:bat[:any,:dbl] := aggr.subcorr(X_26:bat[:oid,:dbl],X_33:bat[:oid,:dbl],X_16:bat[:oid,:oid],r1_16:bat[:oid,:oid],true:bit); program contains errors The corr() error is referenced in a recent bug submission (which I can't find in Bugzilla) If I look in the system function catalog, I see: sql>select * from sys.functions where name like '%weight%'; +------+--------------------+---------------------------------------------------+------+------+------+-------+-------+-------+------+ | id | name | func | mod | lang | type | side_ | varre | varar | sche | : : : : : uage : : effec : s : g : ma_i : : : : : : : : t : : : d : +======+====================+===================================================+======+======+======+=======+=======+=======+======+ | 9313 | weightedpercentile | -- weighted percentile | udf | 1 | 3 | false | false | false | 2000 | : : : create aggregate weightedpercentile(e1 real, e2 r : : : : : : : : : : : eal) returns real : : : : : : : : : : : external name udf.weightedpercentile; : : : : : : : : | 9318 | weightedpercentile | -- weighted percentile | udf | 1 | 3 | false | false | false | 2000 | : : : create aggregate weightedpercentile(e1 double, e2 : : : : : : : : : : : double) returns double : : : : : : : : : : : external name udf.weightedpercentile; : : : : : : : : +------+--------------------+---------------------------------------------------+------+------+------+-------+-------+-------+------+ 2 tuples (1.107ms) So my function appears to be installed. I have in (80_udf.sql): -- weighted percentile create aggregate weightedpercentile(e1 real, e2 real) returns real external name udf.weightedpercentile; -- weighted percentile create aggregate weightedpercentile(e1 double, e2 double) returns double external name udf.weightedpercentile; and in (udf.mal): # BAT MAL signatures module batudf; command weightedpercentile(one:bat[:oid,:bte],two:bat[:oid,:bte]):lng address UDFBATweightedpercentile comment "compute the weighted percentile (bte)" command weightedpercentile(one:bat[:oid,:int],two:bat[:oid,:int]):lng address UDFBATweightedpercentile comment "compute the weighted percentile (int)" command weightedpercentile(one:bat[:oid,:lng],two:bat[:oid,:lng]):lng address UDFBATweightedpercentile comment "compute the weighted percentile (lng)"

2 3

Problem make-install'ing MonetDB to $HOME/opt/monetdb
by Eyal Rozenberg 02 May '16

02 May '16

I'm trying to upgrade to the latest release of MonetDB (Jul2015-SP4) instead of an older one. I'm building and installing as a non-root user, into $HOME/opt/monetdb . Well, * ./bootstrap SUCCEEDED * ./configure --prefix=$HOME/opt/monetdb SUCCEEDED * ./make SUCCEEDED * ./make install FAILED Here's the error message: /usr/bin/install -c -m 644 mapi.h /home/eyalroz/opt/monetdb/include/monetdb/mapi.h /bin/sh ../../libtool --mode=install /usr/bin/install -c libmapi.la '/home/eyalroz/opt/monetdb/lib' libtool: install: error: cannot install `libmapi.la' to a directory not ending in /usr/local/lib I don't see why libtool is telling me that. What business is it of libtool what I want to call my directories? Anyway, it doesn't seem to me I tried to do something esoteric... how do you suggest I get around this? Eyal PS - It's a Fedora 22 machine in case that matters.

2 2

Explain analyze
by Shmagi Kavtaradze 02 May '16

02 May '16

In postgres I could use explain analyze to see actions performed by database and their cost. I was not able to run explain analyze. I ran EXPLAIN, but can not see any plan or cost. Any ideas how to get costs and query plan like in Postgres?

4 7

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