March 2016 - users-list

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

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

16 Mar '16

Hi all I am running a currently roughly 400G-sized single Monet instance. We load daily CSV files into corresponding tables, and use merge tables to query the data (e.g., feed_20160301, feed_20160302, ... and feed_mt). We have a problem mainly with duplicate tables (same schema + table name, different table_id in sys.tables). My approach is to "DROP TABLE foo" for N - 1 times if there are N copies (is there even a way to SELECT or DROP by table_id?). This seemingly fixes the problem, however after restarting the database, the dropped tables reappear. Similarly, I have some old empty tables, which reappear after being dropped when the database is restarted. We start and stop the database to roll over connections during maintenance. Is this a known issue? Can it be fixed? Not sure how to make this reproducible. Thanks, Nicolas Stalder.

3 2