- users-list - monetdb.org

Delete monetdb
by Shmagi Kavtaradze 25 Mar '16

25 Mar '16

Sorry for a stupid questions, but how can I completely delete Monetdb from ubuntu, if I compiled it myself?

4 4

could this be multithreaded? happens after any large import after server stop & restart
by Anthony Damico 25 Mar '16

25 Mar '16

it is dreadfully slow on windows. thanks all # MonetDB 5 server v11.21.13 "Jul2015-SP2" # Serving database 'marketscan', using 40 threads # Compiled for x86_64-pc-winnt/64bit with 64bit OIDs dynamically linked # Found 223.875 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:50100/ # Start processing logs sql/sql_logs version 52200 # Start reading the write-ahead log 'sql_logs\sql\log.14' # still reading write-ahead log "sql_logs\sql\log.14" (0% done) # still reading write-ahead log "sql_logs\sql\log.14" (0% done) # still reading write-ahead log "sql_logs\sql\log.14" (0% done) # still reading write-ahead log "sql_logs\sql\log.14" (0% done) # still reading write-ahead log "sql_logs\sql\log.14" (0% done) # still reading write-ahead log "sql_logs\sql\log.14" (0% done) # still reading write-ahead log "sql_logs\sql\log.14" (0% done) # still reading write-ahead log "sql_logs\sql\log.14" (0% done) # still reading write-ahead log "sql_logs\sql\log.14" (0% done) # still reading write-ahead log "sql_logs\sql\log.14" (0% done) # still reading write-ahead log "sql_logs\sql\log.14" (0% done) # still reading write-ahead log "sql_logs\sql\log.14" (1% done) # still reading write-ahead log "sql_logs\sql\log.14" (1% done) # still reading write-ahead log "sql_logs\sql\log.14" (1% done) # still reading write-ahead log "sql_logs\sql\log.14" (1% done) # still reading write-ahead log "sql_logs\sql\log.14" (1% done) # still reading write-ahead log "sql_logs\sql\log.14" (1% done) # still reading write-ahead log "sql_logs\sql\log.14" (2% done) # still reading write-ahead log "sql_logs\sql\log.14" (2% done) # still reading write-ahead log "sql_logs\sql\log.14" (2% done) # still reading write-ahead log "sql_logs\sql\log.14" (2% done) # still reading write-ahead log "sql_logs\sql\log.14" (2% done) # still reading write-ahead log "sql_logs\sql\log.14" (3% done) # still reading write-ahead log "sql_logs\sql\log.14" (3% done) # still reading write-ahead log "sql_logs\sql\log.14" (3% done) # still reading write-ahead log "sql_logs\sql\log.14" (3% done) # still reading write-ahead log "sql_logs\sql\log.14" (3% done) # still reading write-ahead log "sql_logs\sql\log.14" (4% done) # still reading write-ahead log "sql_logs\sql\log.14" (4% done) # still reading write-ahead log "sql_logs\sql\log.14" (4% done) # still reading write-ahead log "sql_logs\sql\log.14" (4% done) # still reading write-ahead log "sql_logs\sql\log.14" (4% done) # still reading write-ahead log "sql_logs\sql\log.14" (4% done) # still reading write-ahead log "sql_logs\sql\log.14" (5% done) # still reading write-ahead log "sql_logs\sql\log.14" (5% done) # still reading write-ahead log "sql_logs\sql\log.14" (5% done) # still reading write-ahead log "sql_logs\sql\log.14" (5% done) # still reading write-ahead log "sql_logs\sql\log.14" (5% done) # still reading write-ahead log "sql_logs\sql\log.14" (6% done) # still reading write-ahead log "sql_logs\sql\log.14" (6% done) # still reading write-ahead log "sql_logs\sql\log.14" (6% done) # still reading write-ahead log "sql_logs\sql\log.14" (6% done) # still reading write-ahead log "sql_logs\sql\log.14" (6% done) # still reading write-ahead log "sql_logs\sql\log.14" (7% done) # still reading write-ahead log "sql_logs\sql\log.14" (7% done) # still reading write-ahead log "sql_logs\sql\log.14" (7% done) # still reading write-ahead log "sql_logs\sql\log.14" (7% done) # still reading write-ahead log "sql_logs\sql\log.14" (7% done) # still reading write-ahead log "sql_logs\sql\log.14" (7% done) # still reading write-ahead log "sql_logs\sql\log.14" (8% done) # still reading write-ahead log "sql_logs\sql\log.14" (8% done) # still reading write-ahead log "sql_logs\sql\log.14" (8% done) # still reading write-ahead log "sql_logs\sql\log.14" (8% done) # still reading write-ahead log "sql_logs\sql\log.14" (8% done) # still reading write-ahead log "sql_logs\sql\log.14" (8% done) # still reading write-ahead log "sql_logs\sql\log.14" (8% done) # still reading write-ahead log "sql_logs\sql\log.14" (8% done) # still reading write-ahead log "sql_logs\sql\log.14" (9% done) # still reading write-ahead log "sql_logs\sql\log.14" (9% done) # still reading write-ahead log "sql_logs\sql\log.14" (9% done) # still reading write-ahead log "sql_logs\sql\log.14" (9% done) # still reading write-ahead log "sql_logs\sql\log.14" (9% done) # still reading write-ahead log "sql_logs\sql\log.14" (10% done) # still reading write-ahead log "sql_logs\sql\log.14" (10% done) # still reading write-ahead log "sql_logs\sql\log.14" (10% done) # still reading write-ahead log "sql_logs\sql\log.14" (10% done) # still reading write-ahead log "sql_logs\sql\log.14" (10% done) # still reading write-ahead log "sql_logs\sql\log.14" (11% done) # still reading write-ahead log "sql_logs\sql\log.14" (11% done) # still reading write-ahead log "sql_logs\sql\log.14" (11% done) # still reading write-ahead log "sql_logs\sql\log.14" (11% done) # still reading write-ahead log "sql_logs\sql\log.14" (12% done) # still reading write-ahead log "sql_logs\sql\log.14" (12% done) # still reading write-ahead log "sql_logs\sql\log.14" (12% done) # still reading write-ahead log "sql_logs\sql\log.14" (12% done) # still reading write-ahead log "sql_logs\sql\log.14" (12% done) # still reading write-ahead log "sql_logs\sql\log.14" (13% done) # still reading write-ahead log "sql_logs\sql\log.14" (13% done) # still reading write-ahead log "sql_logs\sql\log.14" (13% done) # still reading write-ahead log "sql_logs\sql\log.14" (13% done) # still reading write-ahead log "sql_logs\sql\log.14" (13% done) # still reading write-ahead log "sql_logs\sql\log.14" (13% done) # still reading write-ahead log "sql_logs\sql\log.14" (14% done) # still reading write-ahead log "sql_logs\sql\log.14" (14% done) # still reading write-ahead log "sql_logs\sql\log.14" (14% done) # still reading write-ahead log "sql_logs\sql\log.14" (14% done) # still reading write-ahead log "sql_logs\sql\log.14" (15% done) # still reading write-ahead log "sql_logs\sql\log.14" (15% done) # still reading write-ahead log "sql_logs\sql\log.14" (15% done) # still reading write-ahead log "sql_logs\sql\log.14" (15% done) # still reading write-ahead log "sql_logs\sql\log.14" (15% done) # still reading write-ahead log "sql_logs\sql\log.14" (15% done) # still reading write-ahead log "sql_logs\sql\log.14" (16% done) # still reading write-ahead log "sql_logs\sql\log.14" (16% done) # still reading write-ahead log "sql_logs\sql\log.14" (16% done) # still reading write-ahead log "sql_logs\sql\log.14" (16% done) # still reading write-ahead log "sql_logs\sql\log.14" (16% done) # still reading write-ahead log "sql_logs\sql\log.14" (17% done) # still reading write-ahead log "sql_logs\sql\log.14" (17% done) # still reading write-ahead log "sql_logs\sql\log.14" (18% done) # still reading write-ahead log "sql_logs\sql\log.14" (18% done) # still reading write-ahead log "sql_logs\sql\log.14" (18% done) # still reading write-ahead log "sql_logs\sql\log.14" (19% done) # still reading write-ahead log "sql_logs\sql\log.14" (19% done) # still reading write-ahead log "sql_logs\sql\log.14" (20% done) # still reading write-ahead log "sql_logs\sql\log.14" (20% done) # still reading write-ahead log "sql_logs\sql\log.14" (20% done) # still reading write-ahead log "sql_logs\sql\log.14" (21% done) # still reading write-ahead log "sql_logs\sql\log.14" (21% done) # still reading write-ahead log "sql_logs\sql\log.14" (22% done) # still reading write-ahead log "sql_logs\sql\log.14" (22% done) # still reading write-ahead log "sql_logs\sql\log.14" (22% done) # still reading write-ahead log "sql_logs\sql\log.14" (23% done) # still reading write-ahead log "sql_logs\sql\log.14" (23% done) # still reading write-ahead log "sql_logs\sql\log.14" (23% done) # still reading write-ahead log "sql_logs\sql\log.14" (24% done) # still reading write-ahead log "sql_logs\sql\log.14" (24% done) # still reading write-ahead log "sql_logs\sql\log.14" (24% done) # still reading write-ahead log "sql_logs\sql\log.14" (24% done) # still reading write-ahead log "sql_logs\sql\log.14" (25% done) # still reading write-ahead log "sql_logs\sql\log.14" (25% done) # still reading write-ahead log "sql_logs\sql\log.14" (25% done) # still reading write-ahead log "sql_logs\sql\log.14" (26% done) # still reading write-ahead log "sql_logs\sql\log.14" (26% done) # still reading write-ahead log "sql_logs\sql\log.14" (27% done) # still reading write-ahead log "sql_logs\sql\log.14" (27% done) # still reading write-ahead log "sql_logs\sql\log.14" (27% done) # still reading write-ahead log "sql_logs\sql\log.14" (28% done) # still reading write-ahead log "sql_logs\sql\log.14" (28% done) # still reading write-ahead log "sql_logs\sql\log.14" (28% done) # still reading write-ahead log "sql_logs\sql\log.14" (28% done) # still reading write-ahead log "sql_logs\sql\log.14" (29% done) # still reading write-ahead log "sql_logs\sql\log.14" (30% done) # still reading write-ahead log "sql_logs\sql\log.14" (30% done) # still reading write-ahead log "sql_logs\sql\log.14" (31% done) # still reading write-ahead log "sql_logs\sql\log.14" (31% done) # still reading write-ahead log "sql_logs\sql\log.14" (31% done) # still reading write-ahead log "sql_logs\sql\log.14" (32% done) # still reading write-ahead log "sql_logs\sql\log.14" (32% done) # still reading write-ahead log "sql_logs\sql\log.14" (32% done) # still reading write-ahead log "sql_logs\sql\log.14" (33% done) # still reading write-ahead log "sql_logs\sql\log.14" (33% done) # still reading write-ahead log "sql_logs\sql\log.14" (34% done) # still reading write-ahead log "sql_logs\sql\log.14" (34% done) # still reading write-ahead log "sql_logs\sql\log.14" (34% done) # still reading write-ahead log "sql_logs\sql\log.14" (35% done) # still reading write-ahead log "sql_logs\sql\log.14" (35% done) # still reading write-ahead log "sql_logs\sql\log.14" (35% done) # still reading write-ahead log "sql_logs\sql\log.14" (35% done) # still reading write-ahead log "sql_logs\sql\log.14" (36% done) # still reading write-ahead log "sql_logs\sql\log.14" (36% done) # still reading write-ahead log "sql_logs\sql\log.14" (36% done) # still reading write-ahead log "sql_logs\sql\log.14" (36% done) # still reading write-ahead log "sql_logs\sql\log.14" (37% done) # still reading write-ahead log "sql_logs\sql\log.14" (37% done) # still reading write-ahead log "sql_logs\sql\log.14" (37% done) # still reading write-ahead log "sql_logs\sql\log.14" (38% done) # still reading write-ahead log "sql_logs\sql\log.14" (39% done) # still reading write-ahead log "sql_logs\sql\log.14" (40% done) # still reading write-ahead log "sql_logs\sql\log.14" (41% done) # still reading write-ahead log "sql_logs\sql\log.14" (42% done) # still reading write-ahead log "sql_logs\sql\log.14" (44% done) # still reading write-ahead log "sql_logs\sql\log.14" (45% done) # still reading write-ahead log "sql_logs\sql\log.14" (46% done) # still reading write-ahead log "sql_logs\sql\log.14" (47% done) # still reading write-ahead log "sql_logs\sql\log.14" (48% done) # still reading write-ahead log "sql_logs\sql\log.14" (49% done) # still reading write-ahead log "sql_logs\sql\log.14" (50% done) # still reading write-ahead log "sql_logs\sql\log.14" (50% done) # still reading write-ahead log "sql_logs\sql\log.14" (51% done) # still reading write-ahead log "sql_logs\sql\log.14" (52% done) # still reading write-ahead log "sql_logs\sql\log.14" (53% done) # still reading write-ahead log "sql_logs\sql\log.14" (54% done) # still reading write-ahead log "sql_logs\sql\log.14" (55% done) # still reading write-ahead log "sql_logs\sql\log.14" (55% done) # still reading write-ahead log "sql_logs\sql\log.14" (56% done) # still reading write-ahead log "sql_logs\sql\log.14" (57% done) # still reading write-ahead log "sql_logs\sql\log.14" (57% done) # still reading write-ahead log "sql_logs\sql\log.14" (58% done) # still reading write-ahead log "sql_logs\sql\log.14" (59% done) # still reading write-ahead log "sql_logs\sql\log.14" (59% done) # still reading write-ahead log "sql_logs\sql\log.14" (60% done) # still reading write-ahead log "sql_logs\sql\log.14" (60% done)

2 2

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

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

will monetdb work well if it needs to swap onto a SSD?
by Anthony Damico 15 Mar '16

15 Mar '16

pretty sure that monetdb hits a huge performance wall when you use up all your ram, but i'm curious if anyone has experience with swapping onto a SSD? is the speed-loss nearly equally bad, or is swapping onto a SSD a reasonable choice? thanks all

1 0

Backslash n interpreted as newline.
by Nicolas Stalder 15 Mar '16

15 Mar '16

Hi all I have noticed that when loading tables from CSV files via COPY INTO ... USING DELIMITERS '\t','\n', the ASCII characters '\' followed by 'n' are interpreted as a newline, leading to the loading being aborted due to an incorrect number of columns in this row. This seems to me to be a bug? Also, it would be most helpful if the error message contained a line number (as is the case if there is unicode in the CSV file). Thanks, Nicolas Stalder.

1 0

Debugging: !SELECT: no such operator 'normalize2'
by Willem Ligtenberg 15 Mar '16

15 Mar '16

I have created a function in R for MonetDB. My first function works fine: dbGetQuery(conn, "DROP FUNCTION normalize;") functionDef <- paste( "CREATE FUNCTION normalize(welltype STRING, data_column DOUBLE) RETURNS TABLE (i DOUBLE) LANGUAGE R {", "idx <- which(welltype == 'LC')", "100 * data_column / median(data_column[idx])", "};", sep = "\n") dbGetQuery(conn, functionDef) # example use stmt <- "SELECT barcode, normalize(welltype_code, data_column1) FROM hcs;" dbGetQuery(conn, stmt) # Works! Yay! So I wanted to create a more advanced version: dbGetQuery(conn, "DROP FUNCTION normalize2;") functionDef <- paste( "CREATE FUNCTION normalize2(barcode DOUBLE, welltype STRING, data_column DOUBLE) RETURNS TABLE (i DOUBLE) LANGUAGE R {", "idx <- which(welltype == 'LC')", "100 * data_column / median(data_column[idx])", "};", sep = "\n") dbGetQuery(conn, functionDef) As you see, the only thing that I have changed so far, is the addition of an extra argument to the function. However, this fails: # example use stmt <- "SELECT normalize2(barcode, welltype_code, data_column1) FROM hcs;" dbGetQuery(conn, stmt) I checked, and the types are all fine: barcode = DOUBLE welltype_code = CHARACTER LARGE OBJECT data_column1 = DOUBLE So what is going wrong? Kind regards, Willem

2 3

why "INSERT INTO ... SELECT" is concurrency conflicts with "CREATE TABLE"
by Pierre-Adrien Coustillas 15 Mar '16

15 Mar '16

Hi, Why is that the query INSERT INTO io__selection_top (id_navigateur) SELECT id_navigateur FROM bdm__index_client_cookies_id_navigateur; has a "COMMIT: transaction is aborted because of concurrency conflicts, will ROLLBACK instead" whith this query : DROP TABLE pierre; What is the table that a conflict ? SYS.TABLES ? Thanks Pierre -- 1G6 52 route de bischwiller 67300 Schiltigheim Société de Services et de Formations en Logiciels Libres http://1g6.biz Tél : 06 64 63 70 35 // 09 54 38 70 90

3 2

Monetdb crashes.
by Souri Ratnaparkhi 14 Mar '16

14 Mar '16

Hello, We have a main table with 126 columns and approx. 94000 rows. We are trying to load approx 2500 records from temporary table into main table using query “insert into select” query. The database crashes upon query execution. We get error “*monetdbd: internal error while starting mserver, please refer to the logs*” when to try to connect again. No specific error is created in log file. 1500 records successfully loads from temp table to main table. We also created another main table with same DDL, that table is empty & inserted all the records from temp successfully without any issue. What could be the reason? Is 126 cols an issue? Please help. We are stuck. (btw: We have machine about 200GB of hard disk & 16 GB RAM (14 GB free)) OS* - Ubuntu 14.04.2 LTS (GNU/Linux 3.13.0-55-generic x86_64)* Monetdb version* - MonetDB v11.21.13 (Jul2015-SP2)* Thank you [image: Description: Description: Description: Description: logo] * Souri Ratnaparkhi United Software Associates Pvt. Ltd. Pune, India Mob: 8975677255* * Phone (India Lines): (91) 020-25445550 **www.usain.com* <http://www.usain.com/>

4 10

User Defined Function and regular Function
by Shmagi Kavtaradze 13 Mar '16

13 Mar '16

I read documentation, but have some questions: 1) What is the real difference between UDF and just creating function in sql? I mean the speed of execution. 2) If I create a function, is it implicitly C? I am asking because there is python binding also. 3) If I have already installed Monetdb and want to create UDF, should I recompile whole monetdb? I am not good enough yet to do something like this. I am asking, because I want to take a text type column full of 0s and 1s ("110101010"), represent it as a "int" or "bit" and do bitwise AND against other columns. When I am saying represent as int/bit, I mean not to change ("110101010") into bit, just let C know that it should interpret that number already as int or bit.

2 2

Function
by Shmagi Kavtaradze 12 Mar '16

12 Mar '16

I want to compare two integer columns (filled with 1s and 0s) to each other with bitwise and, then count number of 1s and divide by length of column entries. I wrote a function, but not sure if it is right or how to create in in Monetdb from console. create function BAnd(int a, int b) returns float begin declare count int; int length = floor(log10(abs(a))) + 1; temp = a & b; while (temp != 0) { if (n % 10 == 1) count++; temp /= 10; } return count/length; end;

1 0

Bitwise operations in Monetdb
by Shmagi Kavtaradze 11 Mar '16

11 Mar '16

I am new to Monetdb. I am using Postgresql mainly, but want to check Monetdb performace. In Postres I have column of type bit() filled with 0s and 1s. Then I am comparing each row to all other rows with bitwise AND on that column. Monetdb does not have bit() type so I used text. Any ideas how to do bitwise AND in monetdb and what type of column should I use for this? The query I tried: select a.sentenecid, b.sentenecid, a.sentence AND b.sentence from test a, test b;

4 11

Monitoring JDBC Connections
by Arno Mittelbach 11 Mar '16

11 Mar '16

Hi all, I am looking for a way to monitor open remote connections (i.e., JDBC connections) and possibly to force close them. As far as I can tell, MonetDB restricts the number of simultaneous connections to 64 by default. Via select * from sys.queue I can monitor running queries but I haven't yet figured out a way to list all open connections. Consider e.g. the case that a JDBC connection is for some reason not properly closed. Any hints would be much appreciated. Best regards, Arno

1 0

R session crashes every time with MonetDBLite package
by David Jankoski 08 Mar '16

08 Mar '16

Hi everone, I am new to this help list so before I post my problem I just want to ask how to provide as much information as possible ( in terms of log files and similar) The problem : Using the MonetDBLite package for R i pushed a relatively big chunk of data (~ 11 Mil rows x 14 cols) in a monetDB lite on disk. Now _every_ time i try connecting to it and running some commands, it crashes my R session. Happens with RStudio and Rgui. So, what additional information to this should i provide in order to best describe the problem : - Machine & OS info, R version, sessionInfo(), R script i'm trying to run. Are there any additional logs kept by monetDB which would be helpful ? Thanks in front, David -- [image: hellotrip] <http://www.hellotrip.fr/> *David Jankoski* <http://www.hellotrip.fr/> Teerketelsteeg 1 1012TB Amsterdam www.hellotrip.com [image: Twitter] <https://twitter.com/hellotrip_nl>[image: facebook] <http://facebook.com/HellotripNederland>[image: Linkedin] <https://www.linkedin.com/company/hellotrip>

2 1

Re: MonetDB: leftmart - trying to fix Windows compilation: do not "i...
by Lefteris 08 Mar '16

08 Mar '16

Dear Souri, please direct these kind of questions not at the developers-list but at the users-list (users-list(a)monetdb.org) Now for the issue at hand. Which version of monetdb are you using and which operating system? In order for us to reproduce the error, we need to know the schema of both the main and temp table (you can hide attribute names if it is sensitive information) and also the *exact* query you are using to copy rows from one table to the other. If you could also share the values of the records would also help. In general you have to provide us with more information on your setup, and a way to reproduce the problem:) Also, did you check the merovegnian.log ? no errors there? Maybe you can attach a debugger and see where monetdb fails? Thank you, Lefteris On Tue, Mar 8, 2016 at 6:08 AM, Souri Ratnaparkhi < souri.ratnaparkhi(a)usain.com> wrote: > Hello monetdb team, > > A gentle reminder. > We have a main table with 126 columns and approx. 94000 rows. > We are trying to load approx 2500 records from temporary table into main > table using query “insert into select” query. The database crashes upon > query execution. No specific error is created in log file. > When tried with 1500 records, it works. > We also created another main table with same DDL, that table is empty & > inserted all the records from temp successfully without any issue. > > What could be the reason? Is 126 cols an issue? Please help. We are stuck. > > (btw: We have machine about 200GB of hard disk & 16 GB RAM (14 GB free)) > > Thank you > > > Souri Ratnaparkhi > United Software Associates Pvt. Ltd. > Pune, India > Mob: 8975677255 > Phone (India Lines): (91) 020-25445550 > www.usain.com > > > -----Original Message----- > From: developers-list > [mailto:developers-list-bounces+souri.ratnaparkhi=usain.com@monetdb.org] > On Behalf Of Stefan Manegold > Sent: Tuesday, March 08, 2016 5:05 AM > To: Lefteris > Cc: Communication channel for developers of the MonetDB > Subject: Re: MonetDB: leftmart - trying to fix Windows compilation: do not > "i... > > > My mistake. Sorry! > > Stefan > > ----- On Mar 8, 2016, at 12:12 AM, Lefteris lsidir(a)gmail.com wrote: > > > Stefan, > > > > this needs to be inlined because it is used many times. I will deal > > with compilation issues later on. This is a dev branch and we are > > running different experiments every night. But with this change if > > measured times are different I would not know if it is because of > > other changes or because of the inline statement removal. > > > > I would appreciate if you did not make any changes in this branch at > > the moment. > > > > thank you, > > > > lefteris > > > > > > On Mon, Mar 7, 2016 at 8:34 PM, Stefan Manegold < > > commits+Stefan.Manegold=cwi.nl(a)monetdb.org> wrote: > > > >> Changeset: ea1aed8beff8 for MonetDB > >> URL: > >> http://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=ea1aed8beff8 > >> Modified Files: > >> gdk/gdk_bloom.c > >> Branch: leftmart > >> Log Message: > >> > >> trying to fix Windows compilation: do not "inline" BLOOMask() > >> > >> On Windows, compilation fails with > >> failed invocation: cl -GF -W3 -WX -MD -nologo -Ox -Zi -Oi -I. -I.\.. > >> -I.\..\..\gdk -I..\common\options -I.\..\..\gdk\..\common\options > >> -I..\common\stream -I.\..\..\gdk\..\common\stream -I..\common\utils > >> -I.\..\..\gdk\..\common\utils -I.\..\..\buildtools\conf -DHAVE_ICONV > >> -IC:\Libraries\iconv-1.11.1.win64-vs2010\include -DHAVE_LIBATOMIC_OPS > >> -IC:\Libraries\atomic_ops-7.4.2 -DNDEBUG -LD -Felibbat.dll > >> gdk_batop.obj gdk_select.obj gdk_search.obj gdk_hash.obj gdk_tm.obj > >> gdk_orderidx.obj gdk_align.obj gdk_bbp.obj gdk_heap.obj gdk_utils.obj > >> gdk_atoms.obj gdk_qsort.obj gdk_ssort.obj gdk_storage.obj gdk_bat.obj > >> gdk_delta.obj gdk_cross.obj gdk_system.obj gdk_value.obj > >> gdk_posix.obj gdk_logger.obj gdk_sample.obj gdk_calc.obj gdk_aggr.obj > >> gdk_group.obj gdk_imprints.obj gdk_bloom.obj gdk_join.obj > >> gdk_project.obj gdk_unique.obj gdk_firstn.obj libbat.res /link > >> ..\common\options\monet_options.obj > >> ..\common\stream\libstream.lib ..\common\utils\mutils.obj > >> ..\common\utils\prompt.obj wsock32.lib Ws2_32.l ib > >> C:\Libraries\zlib-1.2.8.win64-vs2010\lib\zdll.lib > >> C:\Libraries\bzip2-1.0.6.win64-vs2010\lib\libbz2.lib psapi.lib > >> Creating library libbat.lib and object libbat.exp gdk_join.obj : > >> error LNK2019: unresolved external symbol BLOOMask referenced in > >> function hashjoin libbat.dll : fatal error LNK1120: 1 unresolved > >> externals > >> > >> > >> Maybe, the fact that BLOOMask() is "inline"d triggers this problem ... > >> > >> > >> diffs (11 lines): > >> > >> diff --git a/gdk/gdk_bloom.c b/gdk/gdk_bloom.c > >> --- a/gdk/gdk_bloom.c > >> +++ b/gdk/gdk_bloom.c > >> @@ -240,7 +240,6 @@ BATbloom(BAT *b) > >> return GDK_SUCCEED; > >> } > >> > >> -inline > >> int BLOOMask(BUN v, Bloomfilter *bloom) { > >> BUN hv,mv,x,y,z; > >> _______________________________________________ > >> checkin-list mailing list > >> checkin-list(a)monetdb.org > >> https://www.monetdb.org/mailman/listinfo/checkin-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) | > _______________________________________________ > developers-list mailing list > developers-list(a)monetdb.org > https://www.monetdb.org/mailman/listinfo/developers-list >

1 0

BATfetchjoin does not hit always => can't use fetchjoin
by John Burgess 07 Mar '16

07 Mar '16

Hi, I'm completely new to monetdb and have been dropped in at the deep end with an application support issue at a customer site. The release running is FEB2013 SP6 on Windows Server 2012 64 bit. There are customizations including aggregate functions written in MAL so the issue may not derive from monetdb source. I have searched bug lists but not found an obvious match for the error that is being reported, so I'm wondering whether anyone has experienced similar symptoms and found a solution. (Bug 3375 is the closest match and was fixed in the release in use) The reported problem is that the application works normally for a period until something causes monetdb to consistently report errors: Error: GDK reported error. SQLState: 22000 ErrorCode: 0 Error: BATfetchjoin(tmp_3662,tmp_4362) does not hit always (|bn|=0 != 2660745=|l|) => can't use fetchjoin. SQLState: 22000 ErrorCode: 0 It has been noted that the error appears to be in response to some SQL which up to that point had been working : select count(*) as "_m1_", avg("B"."C") as "_m2_" from "A" left outer join "B" on "A"."A_ID" = "B"."A_ID" Further, there is a foreign key relationship defined: ALTER TABLE "B" ADD CONSTRAINT fk_B FOREIGN KEY ("A_ID") REFERENCES "A"("A_ID") and dropping the index corresponding to the foreign key causes monetdb to recover. i.e. drop index fk_B All suggestions would be gratefully appreciated. In the meantime, I will try to reproduce the issue in house. Thanks, John Burgess ________________________________

3 5