- users-list - monetdb.org

server-side scripting in MonetDB
by Leon-Constantin Fopa 13 Nov '14

13 Nov '14

Hi ! I read Here (http://db-engines.com/en/system/MonetDB%3BOracle%3BSQLite) that MonetDB support server-side script (like PG/SQL for Oracle). However I'm not able to found any related documentation on the MonetDB website Can some one have any pointers, documentation, tutorial about server-side scripting in monetdb ? thanks Best regards, Fopa Léon Constantin

2 2

New Oct2014 - JDBC unable to connect to the database
by Radovan Bičiště 11 Nov '14

11 Nov '14

Hello, Today I've started to test Oct2014 release. I upgraded the database with no issues and downloaded latest JDBC driver monet-jdbc-2.12.jar . When I try to connect to the upgrade database I get an error about tracelog. Here is the log file: 2014-11-11 08:54 INFO Creating new connection for [{Default group}/DRMAX - DEV] for driver=nl.cwi.monetdb.jdbc.MonetDriver and URL=[jdbc:monetdb://lux-dev1-ana.dev.ceosdata.com:50000/zkdev1] 2014-11-11 08:54 INFO Adding ClassLoader URL=file:/home/rbiciste/tools/lib/monetdb-jdbc-2.12.jar 2014-11-11 08:54 ERROR Error connecting to the database using URL=jdbc:monetdb://lux-dev1-ana.dev.ceosdata.com:50000/zkdev1, username=drmaxtst SELECT: no such operator 'tracelog' [SQL State=08001] java.sql.SQLException: SELECT: no such operator 'tracelog' at nl.cwi.monetdb.jdbc.MonetConnection.<init>(MonetConnection.java:254) at nl.cwi.monetdb.jdbc.MonetDriver.connect(MonetDriver.java:171) at workbench.db.DbDriver.connect(DbDriver.java:429) at workbench.db.ConnectionMgr.connect(ConnectionMgr.java:228) at workbench.db.ConnectionMgr.getConnection(ConnectionMgr.java:158) at workbench.gui.components.ConnectionSelector.doConnect(ConnectionSelector.java:233) at workbench.gui.components.ConnectionSelector$2.run(ConnectionSelector.java:133) Caused by: nl.cwi.monetdb.mcl.MCLException: SELECT: no such operator 'tracelog' at nl.cwi.monetdb.mcl.net.MapiSocket.connect(MapiSocket.java:311) at nl.cwi.monetdb.mcl.net.MapiSocket.connect(MapiSocket.java:398) at nl.cwi.monetdb.mcl.net.MapiSocket.connect(MapiSocket.java:252) at nl.cwi.monetdb.jdbc.MonetConnection.<init>(MonetConnection.java:232) ... 6 more Any help would be appreciated. Thank you, Radovan

3 4

Announcement: New Oct2014 Feature release of MonetDB suite
by Sjoerd Mullender 11 Nov '14

11 Nov '14

The MonetDB team at CWI/MonetDB BV is pleased to announce the Oct2014 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/>. Important consideration when upgrading to this release. In this release we have fixed bug 3474. This bug made it possible that there were duplicates despite there being a unique or primary key constraint present. This could only happen for multi-column constraints. MonetDB maintains an internal column containing a hash for multi-column constraints, and there was a bug in the maintenance code of this column. During an upgrade, this column needs to be recreated. This is done by dropping and adding back the constraints. If there are no duplicates, adding back the constrains should work without problem, but if there are duplicates, adding the constraints will fail. There is unfortunately no easy way to tell the user, so after having made the first connection to the server (when the upgrade happens), check the merovingian log (if you use merovingian -- also known as monetdbd) or the server output window (if you don't use merovingian). Jaql support is removed. The experimental code for Jaql support has been removed. If you have RPM or DEB packages for Jaql installed from an earlier version (MonetDB-jaql and monetdb5_jaql respectively), you will need to uninstall them before the upgrade. Known issue when upgrading old versions. We have noticed that upgrading directly from a version older than the Jan2014 release gave an error. Upgrading to Jan2014 first and then on to Oct2014 should work. Backup. As always, make a backup of your databases before upgrading. Oct 2014 feature release MonetDB5 Server * Removed algebra.materialize. * Removed algebra.kunique and algebra.tunique. They were subsumed by algebra.subunique. * Remove algebra.antiuselect and algebra.thetauselect. They were subsumed by algebra.subselect. * Removed algebra.topN and its imlementation BATtopN. The function was not used. * Removed aggr.histogram and its implementation BAThistogram. The function was not used, and did not produce output in the "headless" (i.e. dense-headed) format. Histograms can be created as a by-product of group.subgroup. Build Environment * Jacqueline, the MonetDB/JAQL frontend, has been removed. The frontend never grew beyond being experimental, and there is no interest anymore to maintain the code. Merovingian * monetdb create: add -p flag to set monetdb user password on creation, and therefore allow creating the database in unlocked state mapilib * Changed mapi_timeout argument from seconds to milliseconds. stream * Changed mnstr_settimeout function so that the specified timeout is now in milliseconds (used to be seconds), and that it also needs an extra argument specifying a callback function (no arguments, int result) that should return TRUE if the timeout should cause the function to abort or continue what it was doing. MonetDB Common * Added "multifarm" capability. It is now possible to separate persistent and transient BATs into different directories (presumably on different disks). This can be done by using the --dbextra option of mserver5 (see the man page). SQL * Added PostgreSQL compatible string TRIM, LTRIM, RTRIM, LPAD and RPAD functions * Stop support for upgrading directly from a database created with a server from the Oct2012 release or older. You can upgrade via the Feb2013 or Jan2014 release. Bug Fixes * 2945: evaluation of SQL "between SYMMETRIC" requires MAL iterator because there is no (bulk) MIN/MAX(a,b) * 3204: monetdb create: allow setting admin password during creation * 3390: Missing definition for pushSht in monetdb5/mal/mal_builder.h * 3402: We should have a C implementation of mal.multiplex. * 3422: Segmentation fault after large table insert * 3459: incomplete implementation of JDBC driver getNumericFunctions(), getStringFunctions(), getSystemFunctions(), getTimeDateFunctions(), getSQLKeywords() methods in MonetDatabaseM * 3471: JDBC driver: incorrect output result of SQL query: SELECT 1 ; * 3474: bulk and scalar versions of mkey.rotate_xor_hash differ * 3484: COPY INTO on a file works fine on Linux/OSX, but not on Windows * 3488: Slow SQL execution for correlated scalar subquery * 3489: SQL query with ORDER BY does not order its result as requested * 3490: SQL query kills the mserver5 (Segmentation fault) * 3491: SQL query kills the mserver5 (Segmentation fault) * 3493: Test monetdb5/modules/mal/Tests/pqueue.mal fails since recent checkins * 3494: Tests monetdb5/modules/mal/Tests/pqueue[23].mal lack correct/expected/intended output * 3495: Test sql/test/centipede/Tests/olap.sql lacks correct/expected/intended output * 3497: monetdb start reports crash in merovingian.log * 3498: SQL throws TypeException if aggregations and limit statements are both present * 3502: Database was killed by signal SIGBUS * 3504: COPY INTO does not allow OFFSET without specifying amount of records * 3505: expression with <boolean> = NOT <boolean> returns a syntax error but NOT <boolean> = <boolean> not * 3506: conversion to varchar terminates mserver * 3508: conversion of string '0 ' to type smallint or integer fails * 3510: timestamp + month interval generates bogus MAL? * 3511: When having multiple selections combined with aliases not all of them seem to be evalauted. * 3512: auto-conversion of string to `sht` type no longer works * 3513: COPY BINARY INTO fails on 6gb file; works fine on 3gb * 3516: inserting '0' into a column of datatype numeric fails * 3518: UNION with subqueries * 3521: large results of function exp() are not automatically returned as double * 3522: SQL catalog table sys.columns lists columns for table ids which do not exist in sys.tables * 3523: Window function over union gives no result * 3524: wrong error on missing aggregation column * 3527: select distinct - order by - limit 2 results in one single result * 3528: segfault at mal_session.c:521 * 3532: several geom tests crash after manifold changes * 3534: missing table name with invalid column in join using (and problems after resolving it) * 3536: program contains error with join using integer and smallint * 3542: gdk/gdk_bat.c:2904: BATassertHeadProps: Assertion `!b->H->revsorted || cmp >= 0' failed. * 3543: invalid behavior and incorrect data results for SQL data type: numeric(4,4) * 3544: sys.reuse() corrupts data * 3546: Division by zero in CASE statement that should avoid it * 3547: Empty query when selecting a field from a view made of UNION ALL * 3551: Wrong ticks in TRACE * 3552: incorrect data results for "WHERE int_col <> 0" * 3554: Issue with subselect and ORDER BY * 3555: Order of evaluation inside CASE WHEN * 3558: numeric values (as strings) are incorrectly parsed/converted and invalid strings are accepted without error * 3560: Error "BATproject: does not match always" with subselect/groupby/having * 3562: mserver5: gdk_bat.c:2855: BATassertHeadProps: Assertion `!b->H->revsorted || cmp >= 0' failed. * 3563: incorrect results for scalar function locate(in_str, search_str, occurrence) * 3565: Wrong/confusing error message when trying to add a FK to a TEMP TABLE * 3572: Table names with escaped double quotes are rejected * 3573: alter table alter_not_null_test alter test set NOT NULL; is accepted when test contains null. This used to be restricted but isn't anymore * 3575: segmentation fault in mserver5 process * 3576: Dropping default value definitions from a table does not work as expected * 3577: SIGSEGV in BATins_kdiff * 3579: segmentation fault in mserver5 process * 3581: mserver5: rel_bin.c:2504: rel2bin_groupby: Assertion `0' failed. * 3582: mserver5: sql_mem.c:48: sql_ref_dec: Assertion `r->refcnt > 0' failed. * 3583: Possible buffer overflow in max(varchar) * 3585: Incorrect query terminates connection * 3586: mserver5: sql/storage/store.c:3610: sys_drop_func: Assertion `rid_func != oid_nil' failed. * 3592: SIGSEGV in MANIFOLDjob * 3593: delta_append_val: Assertion `!c || ((c)->S->count) == bat->ibase' failed. * 3594: gdk/gdk_bat.c:2855: BATassertHeadProps: Assertion `!b->H->revsorted || cmp >= 0' failed. * 3595: Race/heap corruption on thread exit * 3596: gdk_bat.c:2861: BATassertHeadProps: Assertion `!b->H->nonil || cmp != 0' failed. * 3597: SQL to MAL listing looses types * 3598: SQL bulk load should ignore leading/trailing spaces also with type decimal (as with integers & real/double) * 3599: Double-free of imprints * 3601: Trivial typo in debian/monetdb5-sql.init.d * 3603: "monetdb create -p" hangs monetdbd * 3604: Sys.queue ignored upon errors

4 3

MonetDB joins
by baraa Mohamad 10 Nov '14

10 Nov '14

Hello, I'd like to know if MonetDB stores a copy of the whole table in order to reduce the joins between columns when doing select *. In fact, I'm doing a research for PhD and I appreciate the query execution time delivered by MonetDB over huge tables :) Thanks in advance,Baraa

3 3

Querying from Linux Terminal
by Vijay Krishna 09 Nov '14

09 Nov '14

Hi, Can I execute a large number of queries through shell script directly from terminal? In MySql I use, mysql -u root -ppassword -e "select * from table" in my shell script, which returns the result without logging into mysql client. Similarly, I want to execute queries in the terminal without logging into mclient. Is there any equivalent way of achieving the same? I have to do a lot of updates and instead of doing those one by one, I prefer writing a shell script to run hundreds of queries one by one automatically. Any help much appreciated. With Regards, Vijayakrishna.P. Mobile : (+91) 9500402305.

5 4

Arch Linux Packages for MonetDB + Language R
by Brian Hood 07 Nov '14

07 Nov '14

Hi All, I have updated my packages to the latest versions you'll need to install dependencies first there 2 for r-lang 3 for monetdb of which one is r-lang anyway, you also need base-devel installing first otherwise you won't be able to build anything. https://aur.archlinux.org/packages/r-lang - 3.1.2 https://aur.archlinux.org/packages/monetdb - 11.19.3 You should be able to build MonetDB on Arch Linux via AUR ( Arch Linux User Repository) without issues you need the abs package installed ( Arch Linux Build System ) first. Regards, PS: https://wiki.archlinux.org/index.php/Arch_Build_System Brian Hood

1 0

MonetDB - Integrated R Support
by Brian Hood 07 Nov '14

07 Nov '14

Hi All, I built Language R from source install it all went well. However when i try to compile MonetDB from source with enable r integration i get the following. make[7]: Entering directory '/data2/AUR/monetdb/src/MonetDB-11.19.3/monetdb5/extras/rapi' /bin/sh ../../../libtool --tag=CC --mode=compile gcc -DHAVE_CONFIG_H -I. -I../../.. -I. -I../../../common/options -I./../../../common/options -I../../../common/stream -I./../../../common/stream -I../../../gdk -I./../../../gdk -I../../mal -I./../../mal -I../../../sql/include -I./../../../sql/include -D_FORTIFY_SOURCE=2 -DLIBRAPI -O3 -fomit-frame-pointer -pipe -march=x86-64 -mtune=generic -pipe -fstack-protector-strong --param=ssp-buffer-size=4 -D_FORTIFY_SOURCE=2 -D_REENTRANT -c -o lib_rapi_la-rapi.lo `test -f 'rapi.c' || echo './'`rapi.c libtool: compile: gcc -DHAVE_CONFIG_H -I. -I../../.. -I. -I../../../common/options -I./../../../common/options -I../../../common/stream -I./../../../common/stream -I../../../gdk -I./../../../gdk -I../../mal -I./../../mal -I../../../sql/include -I./../../../sql/include -D_FORTIFY_SOURCE=2 -DLIBRAPI -O3 -fomit-frame-pointer -pipe -march=x86-64 -mtune=generic -pipe -fstack-protector-strong --param=ssp-buffer-size=4 -D_FORTIFY_SOURCE=2 -D_REENTRANT -c rapi.c -fPIC -DPIC -o .libs/lib_rapi_la-rapi.o rapi.c:42:23: fatal error: Rembedded.h: No such file or directory #include <Rembedded.h> ^ compilation terminated. Makefile:825: recipe for target 'lib_rapi_la-rapi.lo' failed make[7]: *** [lib_rapi_la-rapi.lo] Error 1 make[7]: Leaving directory '/data2/AUR/monetdb/src/MonetDB-11.19.3/monetdb5/extras/rapi' Makefile:499: recipe for target 'all' failed make[6]: *** [all] Error 2 make[6]: Leaving directory '/data2/AUR/monetdb/src/MonetDB-11.19.3/monetdb5/extras/rapi' Makefile:526: recipe for target 'all-recursive' failed make[5]: *** [all-recursive] Error 1 make[5]: Leaving directory '/data2/AUR/monetdb/src/MonetDB-11.19.3/monetdb5/extras' Makefile:479: recipe for target 'all' failed make[4]: *** [all] Error 2 make[4]: Leaving directory '/data2/AUR/monetdb/src/MonetDB-11.19.3/monetdb5/extras' Makefile:522: recipe for target 'all-recursive' failed make[3]: *** [all-recursive] Error 1 make[3]: Leaving directory '/data2/AUR/monetdb/src/MonetDB-11.19.3/monetdb5' Makefile:475: recipe for target 'all' failed make[2]: *** [all] Error 2 make[2]: Leaving directory '/data2/AUR/monetdb/src/MonetDB-11.19.3/monetdb5' Makefile:586: recipe for target 'all-recursive' failed make[1]: *** [all-recursive] Error 1 make[1]: Leaving directory '/data2/AUR/monetdb/src/MonetDB-11.19.3' Makefile:515: recipe for target 'all' failed make: *** [all] Error 2 My Rembedded.h exists here /usr/lib64/R/include/Rembedded.h Is there some environment variables i can set for the location of the my R header files ? Regards, Brian Hood

2 2

sys.queue()
by Guillaume Bassot 06 Nov '14

06 Nov '14

Hello, On release JAN2014, select * from sys.queue(); is returning all running process. On release OCT2014 the same command is returning SELECT: no such operator 'queue' Do i miss a new way to get process list ? Could you please tell me how to do ? Thanks Guillaume BASSOT Web Developer +33 (0)1 55 48 02 82 gbassot(a)inbox.fr 30, rue Vincent Moris 92240 Malakoff - France Fax : +33 (0)1 55 48 02 79

5 6

Improving performance of bulk loading data
by Dennis Pallett 06 Nov '14

06 Nov '14

Hi all, We're experiencing some problems during bulk loading of new data into our MonetDB database. We use COPY INTO to load several CSV files every day but this loading takes (too) long but more importantly the database is completely unresponsive with regards to any incoming queries therefore completely disabling our production environment. I'm currently looking into improving this proces and the hardware of our MonetDB server is obviously one of the starting points. Does anyone know which specs should be upgraded? Would adding more core's/CPU's have the most positive effect? Or perhaps more memory? Any advice or insight would be greatly appreciated! Best regards, Dennis Pallett

3 4

Can't use ORDER BY in subqueries
by Kotschenreuther, Leo 06 Nov '14

06 Nov '14

Hi all, I tried to use some more complex queries, they contained subqueries. I will shortly desrcibe the situation: I have two tables (e.g. test and foo) and want to join them. Furthermore I want to preselect a subset of one of the two tables by adding some conditions (e.g. order the records by attribute c and only choosing the biggest one). A query could look like the this: sql>select * from test join (select * from foo order by c limit 1); The result I receive is the following: syntax error, unexpected ORDER, expecting INTERSECT or EXCEPT or UNION or ')' in: "select * from test join (select * from foo order” On the other hand, I can run the subquery without the main query and I receive a valid result. Is there a possibility to use subqueries with an ORDER BY statement without separating them into two independent queries? Regards Leo

7 16