- users-list - monetdb.org

Announcement: New Jul2015-SP3 Bugfix release of MonetDB suite
by Sjoerd Mullender 25 Mar '16

25 Mar '16

The MonetDB team at CWI/MonetDB BV is pleased to announce the Jul2015-SP3 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/>. Jul 2015-SP3 bugfix release MonetDB5 Server * Fixed potential crash in MAL debugger when accessing BATs by index. Functionality dropped as it is also a security leak. Bug Fixes * 2972: SQL URL functionality contains errors * 3549: bulk string operations very slow * 3881: Server crashes on bulk load * 3890: Window function + group by in subselect, rel2bin_project: Assertion `0' failed * 3891: MonetDB crashes when executing SQL with window function * 3900: null handling in some sql statements is incorrect * 3906: Multi-column 1-N table-function with mitosis produces different column counts * 3908: LEFT JOIN with OR conditions triggers assertion * 3909: Incorrect column name in OR condition of LEFT JOIN crashes mserver * 3910: COPY INTO table (column1, column2) got wrong result * 3912: When table/column names conflicts, data ends in multiple tables! * 3917: Date difference returns month_interval instead of day_interval * 3918: MonetDB.R version 1.0.1 incorrectly constructs the batfile script * 3919: Table conflict when the table name and fields are identical * 3921: Creating a table from a complex query crashes mserver or triggers assertion * 3922: AVG( column ) returns NaN rather than Inf when column contains Inf * 3928: When killing a virtual machine, sql_logs/sql/log is empty * 3930: Wrong typecast on character columns in prepared statements when using Umlaute * 3932: CASE expressions with constants are not evaluated correctly * 3933: replace "exit" by "throw new Exception" * 3937: bad BAT properties with binary copy into and NULL values * 3938: Wrong error message on violating foreign key constraint * 3940: Date calculation and comparison produce wrong result * 3941: Wrong coercion priority * 3948: SQL: select * from sys.sys.table_name; is accepted but should return an error * 3951: extern table_funcs not visible from Windows DLL for extensions like vaults (crashes) * 3952: Stream table gives segfault * 3953: MIN/MAX of a UUID column produces wrong results * 3954: Consolidate table assertion error * 3955: (incorrect) MAL loop instead of manifold triggered by simple change in target list

1 0

Fwd: Error after compiling monetdb
by Shmagi Kavtaradze 24 Mar '16

24 Mar '16

I am trying to write a C User Defined Function. It works in Neatbeans. What I want is, to take value from cells, insert them in text or char array and compare each corresponding array position to each other. As values I will have "1"s and "0s". E.g "101010100011". THe code I wrote in udf.c: /* Comparing bag of words */ char BitAnd(char Cell1[], char Cell2[]) { Cell1[10000]; Cell2[10000]; int counter; float result; int strlen = sizeof(Cell1)/sizeof(Cell1[0])-1; int i; for (i=0; i < strlen; i++) { if (Cell1[i] == Cell2[i]) { counter++; } } result = (float)counter / strlen; } /* MAL wrapper */ char UDFBitAnd(char Cell1[], char Cell2[]) { /* assert calling sanity */ assert(Cell1[] != NULL && Cell2[] != NULL); return MAL_SUCCEED; } In udf.mal I have: command BitAnd(Cell1:chr,Cell2:chr):flt address UDFBitAnd comment "Compare bag of words"; In 80_udf.sql: -- Comparing bag of words create function BitAnd(Cell1[] char, Cell2[] char) returns float external name udf.BitAnd; I compiled Monetdb, but when I try to connect with "monetdb" to db it gives me error. In the log file I found: MSG voc[18857]: !SyntaxException:parseError:command BitAnd(Cell1:chr,Cell2:chr):flt MSG voc[18857]: !SyntaxException:parseError: ^Type identifier expected MSG voc[18857]: !SyntaxException:parseError:command BitAnd(Cell1:chr,Cell2:chr):flt MSG voc[18857]: !SyntaxException:parseError: ^',' expected MSG merovingian[8780]: database 'voc' (18857) has exited with exit status 0 MSG merovingian[8780]: database 'voc' has shut down ERR merovingian[8780]: client error: database 'voc' started up, but failed to open up a communication channel

2 7

MonetDB schema for Yii 1.1 (PHP framework)?
by Kevork Vartanian 24 Mar '16

24 Mar '16

Hi, I am working on a mini project and using the Yii 1.1 PHP framework (OOP framework, using MVC) and trying to connect to a MonetDB database (changing from MySQL). I see that there is a MonetDB PHP client (https://dev.monetdb.org/hg/MonetDB/file/tip/clients/php/lib/php_monetdb.php) I believe it is MAPI based, but I don't know if that can be used in Yii by creating a wrapper or so. Yii has special classes to retrieve metadata information from database (the http://www.yiiframework.com/doc/api/1.1/CDbSchema) and it also has subclasses for several databases supported in Yii "out of the box" (cubrid, mssql, mysql, oci, pgsql and sqlite). It uses the CActiveDataProvider class which is based on ActiveRecord (http://www.yiiframework.com/doc/api/1.1/CActiveDataProvider) and has methods to retrieve the data from database along with different criteria. Knowing MonetDB also has an ODBC driver, I installed unixodbc along with the PHP PDO driver for ODBC, the MonetDB ODBC driver, and have done all the needed set up to make ODBC work. By the way, my test server is an Ubuntu VM. Now that I have the ODBC working, I am able to connect to the database, and when using SQL statements (building the sql query, command, datareader, etc) everything works in Yii, and I can retrieve the needed data, but when I want to use the widgets and make use of the framework and MVC, I start getting errors and I believe it's all because of not having the right schema class. I tried "forcing" the driver name and tested all the existing schemas (the ones I specified above, that come with Yii) but to no avail. I'm not a database expert or an advanced PHP (and Yii) developer but have some development experience and was able to work on this project and build a nice web application with MySQL but now that I'm planning to change the DB and use MonetDB, I believe I need expert advice and some help from real experts, and I'm hoping someone on this list will be able to shed some light. I couldn't find much on the Internet. Thank you. Regards, Kevork Vartanian Technical Solutions Consultant kevork.vartanian(a)innotta.com.au | +61 406 776 789 | www.innotta.com.au

2 1

How to quote/escape single quote character in the name of the file?
by Gennadiy 23 Mar '16

23 Mar '16

Hello, I am trying to load data from a text file that has single quote character in its name into a table: > "C:\Program Files\MonetDB\MonetDB5\mclient.bat" --port=50000 --user=monetdb password: Welcome to mclient, the MonetDB/SQL interactive terminal (Jul2015-SP2) Database: MonetDB v11.21.13 (Jul2015-SP2), 'soft10_20160318_172621' Type \q to quit, \? for a list of available commands auto commit mode: on sql> sql> sql>COPY 120 OFFSET 2 RECORDS INTO "observations_to_score1" FROM 'C:\\tmp\\sachin's_data_with_requested_column_03_03_2016.csv' USING DELIMITERS ',', '\n', '"' LOCKED; more> more> Everything works fine if there is no single quote character in the name of the file: sql>COPY 120 OFFSET 2 RECORDS INTO "observations_to_score1" FROM 'C:\\tmp\\sachins_data_with_requested_column_03_03_2016.csv' USING DELIMITERS ',', '\n', '"' LOCKED; 120 affected rows (12.3s) I was trying to precede the single quote character by a \ (backslash) or to double it, but it still did not work: sql>COPY 120 OFFSET 2 RECORDS INTO "observations_to_score1" FROM 'C:\\tmp\\sachin\'s_data_with_requested_column_03_03_2016.csv' USING DELIMITERS ',', '\n', '"' LOCKED; could not open file 'C:\\tmp\\sachin\'s_data_with_requested_column_03_03_2016.csv': No such file or directory sql>COPY 120 OFFSET 2 RECORDS INTO "observations_to_score1" FROM 'C:\\tmp\\sachin''s_data_with_requested_column_03_03_2016.csv' USING DELIMITERS ',', '\n', '"' LOCKED; could not open file 'C:\\tmp\\sachin\'s_data_with_requested_column_03_03_2016.csv': No such file or directory > dir c:\tmp\sa*.csv Volume in drive C is S3A9506D003 Volume Serial Number is 00B9-C775 Directory of c:\tmp 2016-03-18 17:12 144,202 sachin's_data_with_requested_column_03_03_2016.csv 2016-03-18 17:12 144,202 sachins_data_with_requested_column_03_03_2016.csv 2 File(s) 288,404 bytes 0 Dir(s) 377,532,665,856 bytes free I am using the following version of MonetDB: # MonetDB 5 server v11.21.13 "Jul2015-SP2" # Serving database 'soft10_20160318_172621', using 4 threads # Compiled for x86_64-pc-winnt/64bit with 64bit OIDs dynamically linked # Found 3.799 GiB available main-memory. # 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 # Listening for connection requests on mapi:monetdb://127.0.0.1:50000/ # Start processing logs sql/sql_logs version 52200 # Start reading the write-ahead log 'sql_logs\sql\log.11' # Finished reading the write-ahead log 'sql_logs\sql\log.11' # Finished processing logs sql/sql_logs # MonetDB/SQL module loaded > Thank you, -- Gennadiy

3 5

Fwd: [Bug 3960] SELECT * with UNION ALL does not check column order
by Anthony Damico 23 Mar '16

23 Mar '16

hi team, should monetdb throw an informative error when SELECT * gets used with UNION? monetdb and postgres both allow users to make the mistake below. from my perspective it should simply fail instead of mis-matching silently. thanks ---------- Forwarded message ---------- From: <bugzilla-daemon(a)monetdb.org> Date: Wed, Mar 23, 2016 at 10:42 AM Subject: [Bug 3960] SELECT * with UNION ALL does not check column order To: ajdamico(a)gmail.com https://www.monetdb.org/bugzilla/show_bug.cgi?id=3960 Niels Nes <niels(a)cwi.nl> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|REOPENED |RESOLVED Resolution|--- |WONTFIX --- Comment #4 from Niels Nes <niels(a)cwi.nl> --- postgres=# postgres=# CREATE TABLE x ( a integer , b integer ); CREATE TABLE postgres=# CREATE TABLE y ( b integer , a integer); CREATE TABLE postgres=# INSERT INTO x VALUES ( 1 , 2 ) ; INSERT 0 1 postgres=# INSERT INTO y VALUES ( 3 , 4 ) ; INSERT 0 1 postgres=# select * from x; a | b ---+--- 1 | 2 (1 row) postgres=# select * from y; b | a ---+--- 3 | 4 (1 row) postgres=# CREATE TABLE z AS ( SELECT * FROM x ) UNION ALL ( SELECT * FROM y ) WITH DATA; SELECT 2 postgres=# SELECT * FROM z; a | b ---+--- 1 | 2 3 | 4 (2 rows) ie what we do is normal behaviour.... -- You are receiving this mail because: You are on the CC list for the bug. You reported the bug.

1 0

Perl DBD::monetdb performance (slow) vs Python (fast)
by Puthick Hok 21 Mar '16

21 Mar '16

Hi, We are using MonetDB (MonetDB-11.19.9) compiled from source in openSUSE 12.2. We use the Perl client package from the source file. It works fine until we need to select 100000 plus records on a single field from a database in MonetDB. It works but it is lot slower than in python and in mclient. Selecting one field with a limit of 100,000 records takes under 1 second using mclient and python. However, it takes more than 23 seconds in Perl. Has anyone observed this performance issue with Perl DBD::monetdb? How Python connector manages to retrieve data very fast? and to make Perl connector work as fast as Python. Thank you in advance. Regards, Puthick

1 0

Re: MonetDB schema for Yii 1.1 (PHP framework)?
by Mark Raasveldt 17 Mar '16

17 Mar '16

Turns out I wasn't subscribed to the list on this email account, let's try that again. ----- Original Message ----- From: "Mark Raasveldt" <m.raasveldt(a)cwi.nl> To: "Kevork Vartanian" <kevork.vartanian(a)innotta.com.au> Cc: "users-list" <users-list(a)monetdb.org> Sent: Thursday, March 17, 2016 11:25:35 AM Subject: Re: MonetDB schema for Yii 1.1 (PHP framework)? Hey Kevork, I don't mind answering questions through email, but I'm afraid I have nothing to do with the PHP client (in fact, I didn't even know we had a PHP client for MonetDB). The author at the top of the page simply states who has performed the last commit to the MonetDB repository, not who created or authored that specific file. You must have clicked on the file after I performed a commit. I have CCed this message to the users' list, hopefully someone that at least knows about the existence of the MonetDB PHP client can help you out there. Regards, Mark ----- Original Message ----- From: "Kevork Vartanian" <kevork.vartanian(a)innotta.com.au> To: "m raasveldt" <m.raasveldt(a)cwi.nl> Sent: Thursday, March 17, 2016 4:31:54 AM Subject: MonetDB schema for Yii 1.1 (PHP framework)? Hi Mark, Hope this email finds you well. Apologies in advance in case you don't like to be contacted directly by email. I saw your name and email address as being the author of the MonetDB PHP client (https://dev.monetdb.org/hg/MonetDB/file/tip/clients/php/lib/php_monetdb.php) and wanted to contact you to ask a few questions, if you don't mind, of course. I am working on a mini project and using the Yii 1.1 PHP framework (OOP framework, using MVC) and trying to connect to a MonetDB database (changing from MySQL). I can see the PHP client is MAPI based, but I don't know if that can be used in Yii by creating a wrapper or so. Yii has special classes to retrieve metadata information from database (the http://www.yiiframework.com/doc/api/1.1/CDbSchema) and it also has subclasses for several databases supported in Yii "out of the box" (cubrid, mssql, mysql, oci, pgsql and sqlite). It uses the CActiveDataProvider class which is based on ActiveRecord (http://www.yiiframework.com/doc/api/1.1/CActiveDataProvider) and has methods to retrieve the data from database along with different criteria. Knowing MonetDB also has an ODBC driver, I installed unixodbc along with the PHP PDO driver for ODBC, the MonetDB ODBC driver, and have done all the needed set up to make ODBC work. By the way, my test server is an Ubuntu VM. Now that I have the ODBC working, I am able to connect to the database, and when using SQL statements (building the sql query, command, datareader, etc) everything works in Yii, and I can retrieve the needed data, but when I want to use the widgets and make use of the framework and MVC, I start getting errors and I believe it's all because of not having the right schema class. I tried "forcing" the driver name and tested all the existing schemas (the ones I specified above, that come with Yii) but to no avail. I'm not a database expert or an advanced PHP (and Yii) developer but have some development experience and was able to work on this project and build a nice web application with MySQL but now that I'm planning to change the DB and use MonetDB, I believe I need expert advice and some help from real experts. I don't know if you're familiar with Yii but do you think what I'm trying to achieve is doable? Also, MonetDB being columnar, I was thinking maybe I should look into the Yii classes for PostgreSQL (main one being http://www.yiiframework.com/doc/api/1.1/CPgsqlSchema with source code: https://github.com/yiisoft/yii/blob/1.1.17/framework/db/schema/pgsql/CPgsql…) and try to make similar ones for MonetDB but I doubt I will be able to do it without some help and guidance from experts. Your advice is greatly appreciated. Thank you. Regards, Kevork Vartanian Technical Solutions Consultant kevork.vartanian(a)innotta.com.au | +61 406 776 789 | www.innotta.com.au

1 0

User Defined Function
by Shmagi Kavtaradze 16 Mar '16

16 Mar '16

I have written a code in c, it works and now I want to implement it in Monetdb. 1) should I append the code in /sql/backends/monet5/UDF/udf.c file? 2) I have gone through udf.c, but still not sure how implement my c code there. I do not know C, just wanted to implement simple code. Can anyone help me adapting it to UDF? I want a function like BAnd(a.chunk, b.chunk), and instead of {"10101011"} in foo and bar, I want values of the columns. #include<stdio.h> main (){ const char foo[] = {"10101011"}; const char bar[] = {"01110111"}; int counter; float result; int strlen = sizeof(foo)/sizeof(foo[0])-1; for (int i=0; i < strlen; i++) { if (foo[i] == bar[i]) { counter++; } } result = (float)counter / strlen; printf("%d::%d::%f", counter, strlen, result); return 0; }

1 0

Re: Duplicate and reappearing tables.
by Nicolas Stalder 16 Mar '16

16 Mar '16

> > Message: 5 > Date: Tue, 15 Mar 2016 21:27:10 +0100 (CET) > From: Stefan Manegold <Stefan.Manegold(a)cwi.nl> > To: Communication channel for MonetDB users <users-list(a)monetdb.org> > Subject: Re: Duplicate and reappearing tables. > > Hi Nicolas, > > could you please share which version of MonetDB you're using on which OS? > > Also, did you initially create your database with the very same version of > MonetDB that you're using now, or did you create it with an earlier version > (which) and then upgrade it to the one you're using now (directly or via > intermetiate release steps)? > Stefan, It's Jul2015-SP2 running on Debian 8 (jessie). We loaded it from scratch, two daily files 100-250MB in size. Earlier we were running Jul2015-SP1 with the same issue. > Message: 7 > Date: Wed, 16 Mar 2016 08:02:47 +0000 > From: Marcel Blok <m.blok(a)chs.nl> > To: Communication channel for MonetDB users <users-list(a)monetdb.org> > Subject: RE: Duplicate and reappearing tables. > > Hi Nicolas, > > We have (and unfortunately sometimes still do) experience similar issues. > The issues appear most often at stopping and starting the database. In an > earlier thread (named "WAL read files") Pierre-Adrien found out that > underscores in table and/or column names could cause conflicts, and cause > similar issues. Maybe this is related to the issue you are experiencing? > > See also the bug report: > https://www.monetdb.org/bugzilla/show_bug.cgi?id=3912 I am aware of this report. However, I do not have any tables where <table_name>_<column_name> conflicts. The WAL files would be in db/sql_logs/sql, yes? I see a 12 byte file "log" and a 513K file "log.791". Can I inspect or understand what those files contain? What is in this numbered log file? I also tried loading a msqldump of the data into a new MonetDB instance, which eventually crashes with "Illegal input sequence". Are there any ways of making mserver5 more verbose? Clearly, something is happening at database startup. Cheers, Nicolas.

1 0

Shipping data / old voyages
by Brian Hood 16 Mar '16

16 Mar '16

Hi Guys, I have spent quite a bit of time with the voc database developing another project. I have where did you acquire this data as its very interesting. Regards Brian Hood

2 2