- users-list - monetdb.org

[MonetDB-users] Database crash
by Stathis Gkotsis 16 Mar '12

16 Mar '12

Hi all, yesterday my server lost power and when I rebooted my monetdb database would not start. This is what I see in the logs: gdk_dbfarm=/var/monetdb5/dbfarm --dbname=mydatabase --set merovingian_uri=mapi:monetdb://mydatabase:50000/mydatabase --set mapi_open=false --set mapi_port=0 --set mapi_usock=/var/monetdb5/dbfarm/mydatabase/.mapi.sock --set monet_vault_key=/var/monetdb5/dbfarm/mydatabase/.vaultkey --set gdk_nr_threads=8 --set max_clients=64 --set sql_optimizer=default_pipe --set monet_daemon=yes 2012-03-16 01:15:34 MSG merovingian[897]: database 'mydatabase' (1793) has exited with exit status 1 2012-03-16 01:15:34 ERR merovingian[897]: client error: database 'mydatabase' has crashed after starting, manual intervention needed, check monetdbd's logfile for details 2012-03-16 01:15:36 MSG merovingian[897]: database 'mydatabase' has crashed after start on 2012-03-13 17:57:44, attempting restart, up min/avg/max: 32s/32s/32s, crash average: 1.00 0.10 0.03 (2-1=1) 20 Any ideas on how to fix this? Thank you, Stathis

1 0

[MonetDB-users] Announcement: New Dec2011-SP2 Bugfix release of MonetDB suite
by Fabian Groffen 16 Mar '12

16 Mar '12

The MonetDB team at CWI/MonetDB BV is pleased to announce the Dec2011-SP2 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/>. Dec 2011-SP2 bugfix release Java Module * Implemented missing Number types support in PreparedStatement.setObject() Bug Fixes * 2915: Query (mclient or jdbc) with 2 outer joins appears to crash connectionor server * 2947: Queries where a "is null" clause is combined with two equals clauses fail to produce the expected result. * 2968: Memory Leaks * 2974: thetajoin produces invalid/inconsistent bat * 2988: Subquery in select part goes wrong * 2998: Equivalent multi-way join queries give different results * 3002: String plus interval result in program errors upon comparison * 3011: count(*) in subquery results in 1 for query without results * 3012: bitwise comparison fails after a cast * 3029: test sql/test/Dependencies/Tests/Dependencies.sql fails after recent changes * 3031: Virtually all SQL tests fail on Windows * 3036: optimizers are not getting set correctly through set optimizer * 3038: ~190 SQL tests fail on Darwin, FreeBSD & Solaris (only) since introduction of "(extended) relational algebra" * 3043: WITH clause is not allowed in derived table * 3045: algebra.project(0@0,bat) fails at propcheck * 3052: Wrong Error Code on Rollback Exception

1 0

[MonetDB-users] rename table?
by Christian Braun 16 Mar '12

16 Mar '12

Hello! Is it possible to rename a table? Regards, Christian.

1 0

[MonetDB-users] Query ignored
by Mints, Alexey 15 Mar '12

15 Mar '12

Hello! I have an issue with MonetDB query. This one select count(*) from catalogedsources c1 left outer join catalogedsources c2 on ( c2.cat_id = c1.cat_id + 2 AND ((c1.decl - c2.decl) * (c1.decl - c2.decl))< 1e-06) where c1.cat_id = 4 AND c1.decl BETWEEN 77.0 AND 83.0 AND c1.ra BETWEEN 42.0 AND 48.0 ; works fine. But if I replace "c2.cat_id = c1.cat_id + 2" with "c2.cat_id = 6" - i receive nothing. Not zero, not empty dataset, but just nothing at all. "EXPLAIN" does not work as well. Also, with more complex join conditions I have no result as well. I am using: Welcome to mclient, the MonetDB/SQL interactive terminal (Dec2011-SP1) Database: MonetDB v11.7.7 (Dec2011-SP1), 'mapi:monetdb://linux-6d2s.site:50000/*****' What can this be? Regards, Alexey

1 0

[MonetDB-users] MonetDB out of memory errors
by Stathis Gkotsis 14 Mar '12

14 Mar '12

Hi all, I am using MonetDB v11.7.7 (Dec2011-SP1) in an application which constantly feeds data in tables (daily tables). The communication with the server is done through the mapi API. This is accomplished through the "COPY INTO" command. From time to time (every 1-2 days) I see errors in the application logs such as: exception at 00000000004C0109: Connect error (Error code -4): monetdbd: an internal error has occurred, refer to the logs for details, please try again later . exception at 00000000004C0109: Connect error (Error code -4): monetdbd: there are no available connections for 'mydatabase' . exception at 00000000004C0109: Connect error (Error code -4): monetdbd: there are no available connections for 'mydatabase' Then looking at dmesg log: [964932.242080] 251025 pages non-shared [964932.242083] Out of memory: kill process 11585 (monetdbd) score 787911 or a child [964932.302785] Killed process 11781 (mserver5) Sometimes, monetdb can recover from this error and then the loading continues normally, some others I have to manually restart monetdb. Is there anything I can do to troubleshoot this issue? Is there a possibility for me to fall back to an earlier version of MonetDB with potentially less features but more stability? Thanks for all the help, Stathis

2 2

[MonetDB-users] rdf support
by Jürgen Jakobitsch 14 Mar '12

14 Mar '12

hi, first of all congrats to this great project! i came to monetDB via a search for "column stores", since i know that one of the big players in the field of linked data and rdf storage switched to usage of a column store in one of their latest releases. i just built from source without any hassle on openSuse 11.4 with support for rdf since this is my primary interest at the moment. i didn't find much of an info regarding monetDB and rdf besides an old nabble-mail and a small-meeting wiki. is there some more info available? any pointer really appreciated. wkr jürgen -- | Jürgen Jakobitsch, | Software Developer | Semantic Web Company GmbH | Mariahilfer Straße 70 / Neubaugasse 1, Top 8 | A - 1070 Wien, Austria | Mob +43 676 62 12 710 | Fax +43.1.402 12 35 - 22 COMPANY INFORMATION | http://www.semantic-web.at/ PERSONAL INFORMATION | web : http://www.turnguard.com | foaf : http://www.turnguard.com/turnguard | skype : jakobitsch-punkt | xmlns:tg = "http://www.turnguard.com/turnguard#"

1 0

[MonetDB-users] Replication/Redundancy
by Joseph Brower 13 Mar '12

13 Mar '12

We're using MonetDB quite heavily and are really enjoying it. We're now wanting to add replication/redundancy to ensure we have as little downtime as possible. We're also thinking of balancing reads if that is possible. I've heard this is possible, but I'm unsure on the best way to approach it. I've not been able to find many docs that talk about this either. Could someone point me in the right direction? Thanks, Joseph Brower

4 10

[MonetDB-users] monetdbd: binding to datagram socket port 50000 failed: no available address
by Charalampos Nikolaou 09 Mar '12

09 Mar '12

When I start monetdbd I get the following error "monetdbd: binding to datagram socket port 50000 failed: no available address" Prior to this error I had properly terminated the server, so there is no server running. How can I overcome that problem? Also, netstat gives the following: $ netstat -p udp | grep 50000 udp4 1562 0 *.50000 *.* Thanks, Babis P.S.: I am on a Mac if that plays any role.

2 2

[MonetDB-users] some questions regarding queries in MonetDB
by Freddy Priyatna 06 Mar '12

06 Mar '12

Dear all, I've been using MonetDB version 5 on Ubuntu machine for evaluating BSBM queries. The dataset and queries can be found here[1]. It seems that there are some queries that are not supported on MonetDB : 1) SELECT p.nr, p.label FROM product p, producttypeproduct ptp WHERE p.nr=ptp.product AND "productType"=35 AND "propertyNum1">500 AND "propertyNum3"<2000 AND 168 IN (SELECT "productFeature" FROM productfeatureproduct WHERE productfeatureproduct.product=p.nr) AND 147 NOT IN (SELECT "productFeature" FROM productfeatureproduct WHERE product=p.nr) >From the error, the subquery seems can't refer to the table alias "p". 2) SELECT distinct p.nr, p.label FROM product p, product po, (Select distinct pfp1.product FROM productfeatureproduct pfp1, (SELECT "productFeature" FROM productfeatureproduct WHERE product=2) pfp2 WHERE pfp2."productFeature"=pfp1."productFeature") pfp WHERE p.nr=pfp.product AND po.nr=2 AND p.nr <> po.nr AND p."propertyNum1" < (po."propertyNum1"+120) AND p."propertyNum1" > (po."propertyNum1"-120) AND p."propertyNum2" < (po."propertyNum2"+170) AND p."propertyNum2" > (po."propertyNum2"-170) This is strange, because if I just comment one of the last 2 lines, the query works. But the last two lines can't be used together. 3) SELECT nr, label FROM product WHERE label like "%countries%"; It seems that MonetDB doesn't support regex. 4) SELECT * FROM (select label from product where nr=2) p left join ((select o.nr as onr, o.price, v.nr as vnr, v.label from offer o, vendor v where 2=o.product AND o.vendor=v.nr AND v.country='GB' AND o."validTo">'2008-07-01') ov right join (select r.nr as rnr, r.title, pn.nr as pnnr, pn.name, r.rating1, r.rating2 from review r, person pn where r.product=2 AND r.person=pn.nr) rpn on (1=1)) on (1=1); It seems that MonetDB can't do join with true condition because when I tried to run a simple query : SELECT * FROM product p RIGHT JOIN offer o ON (1=1) Then MonetDB gives an error. Could anyone please help me how to run the queries above? Many thanks in advance. Freddy [1] http://www4.wiwiss.fu-berlin.de/bizer/BerlinSPARQLBenchmark/spec/ExploreUse…

5 11

[MonetDB-users] duplicate tables in schema
by Bas Kaptijn 05 Mar '12

05 Mar '12

Hi, repeatedly we get in a situation where tables end up duplicate in the sys.tables table: SELECT count(*) FROM tables WHERE name='mytable'; returns: 2 We think this should never be the case. It seems this gives problems with dumping the database and in other situations. This is an older version of MonetDB (Apr2011 SP1). We want to know whether someone knows this was/is a symptom of any known issue Sincerely, Bas

3 2

[MonetDB-users] Slow range join performance / regression with Dec2011-SP1
by Viktor Rosenfeld 02 Mar '12

02 Mar '12

Hi, I have a few queries that join a table with itself using a ranged predicate on the primary key. The simplest query is: SELECT count(*) AS count FROM rank_copy rank1, rank_copy rank2 WHERE rank1.cat_s = true AND rank2.pos_vvfin = true AND rank1.pre < rank2.pre AND rank2.pre < rank1.post As you can see, the range lookup on rank2.pre is restricted on both sides by rank1.pre and rank1.post. The selectivity of the attributes and the complete query is: - row count : 2431307 - query : 95977 (0.039) - cat_s : 148799 (0.061) - pos_vvfin : 71965 (0.030) PostgreSQL is able to evaluate the query in 2 seconds using an index on pre. (This index is automatically generated because pre is the primary key.) MonetDB Dec2011 requires about 35 seconds. The MAL plan is posted below. I've also tried sorting the table on pre, and created an (advisory) index on pre but this makes no difference. +---------------------------------------------------------------------------+ | mal | +===========================================================================+ | function user.s1_1{autoCommit=true}():void; | | X_2 := sql.mvc(); | | X_9:bat[:oid,:bit] := sql.bind(X_2,"sys","rank_copy","pos_vvfin",0); | | X_10 := algebra.uselect(X_9,true:bit); | | X_11 := algebra.markT(X_10,0@0:oid); | | X_12 := bat.reverse(X_11); | | X_4:bat[:oid,:int] := sql.bind(X_2,"sys","rank_copy","pre",0); | | X_13 := algebra.leftjoin(X_12,X_4); | | X_17:bat[:oid,:bit] := sql.bind(X_2,"sys","rank_copy","cat_s",0); | | X_18 := algebra.uselect(X_17,true:bit); | | X_19 := algebra.markT(X_18,0@0:oid); | | X_20 := bat.reverse(X_19); | | X_23 := algebra.leftjoin(X_20,X_4); | | X_15:bat[:oid,:int] := sql.bind(X_2,"sys","rank_copy","post",0); | | X_21 := algebra.leftjoin(X_20,X_15); | | X_24 := algebra.join(X_13,X_23,X_21,false,false); | | X_26 := algebra.markT(X_24,0@0:oid); | | X_27 := bat.reverse(X_26); | | X_28 := algebra.leftjoin(X_27,X_13); | | X_29 := aggr.count(X_28); | | sql.exportValue(1,"sys.rank2","count":str,"wrd",64,0,6,X_29,""); | | end s1_1; | +---------------------------------------------------------------------------+ 22 tuples If I use a slightly more complicated query that has the boolean attributes pos_vvfin and cat_s on another table that is joined by rank, the situation is even worse. SELECT count(*) AS count FROM node_copy node1 JOIN rank_copy rank1 ON (node1.id = rank1.node_ref), node_copy node2 JOIN rank_copy rank2 ON (node2.id = rank2.node_ref) WHERE node1.cat_s = true AND node2.pos_vvfin = true AND rank1.pre < rank2.pre AND rank2.pre < rank1.post PostgreSQL evaluates the query in 9 seconds, again using the primary key indexes on node.id and rank.pre. MonetDB Dec2011 requires 14 minutes. The MAL plan follows: +-----------------------------------------------------------------------------------------------+ | mal | +===============================================================================================+ | function user.s1_1{autoCommit=true}():void; | | X_2 := sql.mvc(); | | X_19:bat[:oid,:oid] := sql.bind_idxbat(X_2,"sys","rank_copy","FK_rank_copy_node_ref",0); | | X_13:bat[:oid,:bit] := sql.bind(X_2,"sys","node_copy","pos_vvfin",0); | | X_14 := algebra.uselect(X_13,true:bit); | | X_15 := algebra.markT(X_14,0@0:oid); | | X_16 := bat.reverse(X_15); | | X_9:bat[:oid,:int] := sql.bind(X_2,"sys","node_copy","id",0); | | X_11 := bat.mirror(X_9); | | X_17 := algebra.leftjoin(X_16,X_11); | | X_18 := bat.reverse(X_17); | | X_37:bat[:oid,:bit] := sql.bind(X_2,"sys","node_copy","cat_s",0); | | X_39 := algebra.uselect(X_37,true:bit); | | X_40 := algebra.markT(X_39,0@0:oid); | | X_41 := bat.reverse(X_40); | | X_42 := algebra.leftjoin(X_41,X_11); | | X_43 := bat.reverse(X_42); | | X_24 := algebra.join(X_19,X_18); | | X_25 := algebra.markT(X_24,0@0:oid); | | X_26 := bat.reverse(X_25); | | X_4:bat[:oid,:int] := sql.bind(X_2,"sys","rank_copy","pre",0); | | X_27 := algebra.leftjoin(X_26,X_4); | | X_29:bat[:oid,:int] := sql.bind(X_2,"sys","rank_copy","post",0); | | X_30 := algebra.join(X_27,X_4,X_29,false,false); | | X_32 := algebra.markT(X_30,0@0:oid); | | X_33 := bat.reverse(X_32); | | X_44 := bat.reverse(X_30); | | X_45 := algebra.markT(X_44,0@0:oid); | | X_46 := bat.reverse(X_45); | | X_47 := algebra.leftjoin(X_46,X_19); | | X_48 := algebra.join(X_47,X_43); | | X_49 := algebra.markT(X_48,0@0:oid); | | X_50 := bat.reverse(X_49); | | X_51:bat[:oid,:int] := algebra.leftjoinPath(X_50,X_33,X_27); | | X_52 := aggr.count(X_51); | | sql.exportValue(1,"sys.rank2","count","wrd",64,0,6,X_52,""); | | end s1_1; | +-----------------------------------------------------------------------------------------------+ 37 tuples The Dec2011-SP1 release takes even longer, about 18 minutes. The MAL plan is unchanged. The culprit appears to be the ranged lookup because if I replace it with an equi-join on another attribute MonetDB evaluates it in about 100 ms. SELECT count(*) AS count FROM node_copy node1 JOIN rank_copy rank1 ON (node1.id = rank1.node_ref), node_copy node2 JOIN rank_copy rank2 ON (node2.id = rank2.node_ref) WHERE node1.cat_s = true AND node2.pos_vvfin = true AND rank1.pre = rank2.parent I've tried rewriting the query using WITH and had some success. The first query again takes 35 seconds: WITH rank1 AS (SELECT * FROM rank_copy WHERE cat_s = true), rank2 AS (SELECT * FROM rank_copy WHERE pos_vvfin = true) SELECT count(*) FROM rank1, rank2 WHERE rank1.pre < rank2.pre AND rank2.pre < rank1.post The second query also takes 35 seconds when rewritten: WITH rank1 AS (SELECT * FROM rank_copy JOIN node_copy ON (rank_copy.node_ref = node_copy.id) WHERE node_copy.cat_s = true), rank2 AS (SELECT * FROM rank_copy JOIN node_copy ON (rank_copy.node_ref = node_copy.id) WHERE node_copy.pos_vvfin = true) SELECT count(*) FROM rank1, rank2 WHERE rank1.pre < rank2.pre AND rank2.pre < rank1.post MAL plan: +-----------------------------------------------------------------------------------------------+ | mal | +===============================================================================================+ | function user.s1_2{autoCommit=true}():void; | | X_2 := sql.mvc(); | | X_20:bat[:oid,:oid] := sql.bind_idxbat(X_2,"sys","rank_copy","FK_rank_copy_node_ref",0); | | X_13:bat[:oid,:bit] := sql.bind(X_2,"sys","node_copy","pos_vvfin",0); | | X_14 := algebra.uselect(X_13,true:bit); | | X_15 := algebra.markT(X_14,0@0:oid); | | X_16 := bat.reverse(X_15); | | X_9:bat[:oid,:int] := sql.bind(X_2,"sys","node_copy","id",0); | | X_11 := bat.mirror(X_9); | | X_17 := algebra.leftjoin(X_16,X_11); | | X_18 := bat.reverse(X_17); | | X_32:bat[:oid,:bit] := sql.bind(X_2,"sys","node_copy","cat_s",0); | | X_33 := algebra.uselect(X_32,true:bit); | | X_34 := algebra.markT(X_33,0@0:oid); | | X_35 := bat.reverse(X_34); | | X_36 := algebra.leftjoin(X_35,X_11); | | X_37 := bat.reverse(X_36); | | X_22 := algebra.join(X_20,X_18); | | X_23 := algebra.markT(X_22,0@0:oid); | | X_24 := bat.reverse(X_23); | | X_5:bat[:oid,:int] := sql.bind(X_2,"sys","rank_copy","pre",0); | | X_25 := algebra.leftjoin(X_24,X_5); | | X_38 := algebra.join(X_20,X_37); | | X_39 := algebra.markT(X_38,0@0:oid); | | X_40 := bat.reverse(X_39); | | X_43 := algebra.leftjoin(X_40,X_5); | | X_27:bat[:oid,:int] := sql.bind(X_2,"sys","rank_copy","post",0); | | X_41 := algebra.leftjoin(X_40,X_27); | | X_44 := algebra.join(X_25,X_43,X_41,false,false); | | X_46 := algebra.markT(X_44,0@0:oid); | | X_47 := bat.reverse(X_46); | | X_48 := algebra.leftjoin(X_47,X_25); | | X_49 := aggr.count(X_48); | | sql.exportValue(1,"sys.rank2","L1","wrd",64,0,6,X_49,""); | | end s1_2; | +-----------------------------------------------------------------------------------------------+ 35 tuples Is there anything I can do to further speed up the execution of these queries? Thanks, Viktor

1 0

[MonetDB-users] "ERROR = !Connection terminated" for some SQL queries
by Viktor Rosenfeld 01 Mar '12

01 Mar '12

Hi, the following query results in a "Connection terminated error": SELECT count(*) FROM ( SELECT DISTINCT node1.id AS id1, node2.id AS id2, node1.toplevel_corpus FROM node AS node1, rank AS rank1, component AS component1, node AS node2, rank AS rank2, component AS component2 WHERE component1.id = component2.id AND component1.name IS NULL AND component1.type = 'd' AND component2.name IS NULL AND component2.type = 'd' AND rank1.component_ref = component1.id AND rank1.node_ref = node1.id AND rank1.pre = rank2.parent AND rank2.component_ref = component2.id AND rank2.node_ref = node2.id ) AS solutions; The full error message is: MAPI = annis@moos.local:50000 ACTION= read_line QUERY = SELECT count(*) FROM ( SELECT DISTINCT node1.id AS id1, node2.id AS id2, node1.toplevel_corpus FROM node AS node1, rank AS rank1, component AS component1, node AS node2, rank AS rank2, component AS component2 WHERE component1.id = component2.id AND component1.name IS NULL AND component1.type = 'd' AND component2.name IS NULL AND component2.type = 'd' AND rank1.component_ref = component1.id AND rank1.node_ref = node1.id AND rank1.pre = rank2.parent AND rank2.component_ref = component2.id AND rank2.node_ref = node2.id ) AS solutions; ERROR = !Connection terminated I can get it to run if I remove the following line: component1.id = component2.id AND or if I remove the following 4 lines: component1.name IS NULL AND component1.type = 'd' AND component2.name IS NULL AND component2.type = 'd' AND However, if I include all constraints on component, the error appears. Let me know, if you need more information. Cheers, Viktor

2 3

[MonetDB-users] Database crash
by Stathis Gkotsis 29 Feb '12

29 Feb '12

Hi all, I am using MonetDB Dec 2011 version. Seeing that I could not connect to 'mydatabase' from mclient, I tailed the log: 2012-02-29 11:39:32 MSG mydatabase[7045]: # Serving database 'mydatabase', using 2 threads 2012-02-29 11:39:32 MSG mydatabase[7045]: # Compiled for x86_64-pc-linux-gnu/64bit with 64bit OIDs dynamically linked 2012-02-29 11:39:32 MSG mydatabase[7045]: # Found 999.238 MiB available main-memory. 2012-02-29 11:39:32 MSG mydatabase[7045]: # Copyright (c) 1993-July 2008 CWI. 2012-02-29 11:39:32 MSG mydatabase[7045]: # Copyright (c) August 2008-2012 MonetDB B.V., all rights reserved 2012-02-29 11:39:32 MSG mydatabase[7045]: # Visit http://www.monetdb.org/ for further information 2012-02-29 11:39:32 MSG mydatabase[7045]: # Listening for UNIX domain connection requests on mapi:monetdb:///var/monetdb5/dbfarm/mydatabase/.mapi.sock 2012-02-29 11:39:32 MSG mydatabase[7045]: # MonetDB/SQL module loaded 2012-02-29 11:39:32 MSG merovingian[6978]: database 'mydatabase' (7045) was killed by signal SIGSEGV 2012-02-29 11:39:32 ERR merovingian[6978]: client error: database 'mydatabase' has crashed after starting, manual intervention needed, check monetdbd's logfile for details Any ideas? Thank you.

1 0

[MonetDB-users] Very Slow Selects
by Joseph Brower 29 Feb '12

29 Feb '12

We're using MonetDB and hitting it fairly heavily with read queries. We only use importing of text files to insert information, and that happens every 30 seconds or so. When we're only inserting information, we consume less than 1% of the cpu and things run just fine. We are also able to occasionally query the DB for various peices of information (counts, averages, etc) and it returns quite quickly. The problem is that after we've been running for 15-20 minutes of importing and querying heavily, performance drops to where queries take multiple minutes to finish. The queries generally finish in less than half a second. Does anyone have any ideas on how we can best track down this issue? Is there any more information that I need to provide? Thanks, Joseph Brower

6 7

[MonetDB-users] Announcement: New Dec2011-SP1 Bugfix release of MonetDB suite
by Fabian Groffen 29 Feb '12

29 Feb '12

The MonetDB team at CWI/MonetDB BV is pleased to announce the Dec2011-SP1 bugfix release of the MonetDB suite of programs. More information about MonetDB can be found on our website at http://www.monetdb.org/. For details on this release, please see the release notes at <http://www.monetdb.org/Downloads/ReleaseNotes>. As usual, the download location is <http://dev.monetdb.org/downloads/>. Dec 2011-SP1 bugfix release Build Environment * Fixed some of the package names for Debian/Ubuntu. Packages for libraries should contain the major number of the library version number. This was not always the case. * On Debian and Ubuntu, install Python modules in dist-packages instead of site-packages. This fixed bug 2997. SQL * Fixed a crash that happened at the end of a database upgrade to the Dec2011 database scheme. The crash happened during cleanup after the database was upgraded, so it was merely inconvenient. * Stripped off implementation-specific parts from error messages before they get presented to the user. Java Module * Fixed bug in DatabaseMetaData.getSchemas() method that caused an SQL error when called with catalog and schema argument. * Resolved a bug where JDBC and Control connections could terminate abruptly with 'Connection closed' messages MonetDB5 Server * Fixed potential crash by dealing better with non-standard types. Merovingian * Fixed a bug in the multiplex-funnel where certain clients would abort on responses for update queries. * Fixed problem where version and mserver properties for monetdbd were not always successfully retrieved. Bug #2982. * Fixed problem where shutdown of monetdbd would lead to shutting down database 'control' which does not exist. Bug #2983. * Fixed issue causing (harmless) 'error reading from control channel' messages. Bug #2984. * Resolved problem where remote start/stop/etc. commands with monetdb would report error 'OK'. Bug #2984. Bug Fixes * 2862: foreign key self references cause violation upon alter table add constraint... * 2933: "TypeException: algebra.join undefined" when evaluating EXISTS (SELECT ...) predicate * 2946: Invalid BAT if left join condition has only columns from right table * 2964: LEFT OUTER JOIN with constants returns errors * 2969: Result precision issue with Decimal data type * 2973: Date coercion functionality tests * 2979: misc merovingian control problems * 2982: monetdbd status and mserver properties have random values * 2983: monetdbd attempts to stop non-existing database 'control' * 2984: monetdb create/release/start/stop returns error "OK" for remote connections * 2995: Test geom/sql/Tests/example.sql crashes since changeset 6399077ff8a4 * 2997: monetdb-python packages installed in wrong dir on Ubuntu 10.04 LTS (lucid) * 3004: Incorrect evaluation of conditions in WHERE part of SQL statement * 3009: Segmentation fault on simple query * 3013: calc.and is not yet defined for dbl * 3014: dayofweek comment is wrong in mtime.mx:700 * 3017: rel_bin.c:611: exp_bin: Assertion `0' failed. with between on multicolumns * 3020: Assertion error in debugging mode * 3034: INNER JOIN with Subquery produces mal errors * 3041: WHERE condition ignored after 2 LEFT JOINs with subqueries

1 0

[MonetDB-users] max concurrent limit?
by Allen Zhang 28 Feb '12

28 Feb '12

Hi, When connecting to monetdb Aug 2011 SP2 version, we encountered error states : Maximum concurrent client connection (64) reached, any solutions? Best Regards Allen

4 4

[MonetDB-users] MonetDB JDBC 1.9 question
by Charalampos Nikolaou 23 Feb '12

23 Feb '12

Hi, I am getting some strange exceptions using MonetDB JDBC 1.8/1.9, and I decided to look into the code. While inspecting the code I came across these strange lines in MonetConnection.executeQuery(String[] templ, String query) method (http://dev.monetdb.org/hg/MonetDB/file/718da8ca0a1a/java/src/nl/cwi/monetdb…) In my case, these lines are executed for sure when doing a batch insert. So, suppose that the batch contains the following commands (as constructed by the MonetStatement.executeBatch() method): exec 1(1, 957339737330229055); exec 1(2, 278262503670654331); exec 1(805306369, 3763943296910752235) The lines in question take the above commands as a string, prepend a 's' character and append a ';' at the end. The resulting commands, which are written in the server's socket, are the following: sexec 1(1, 957339737330229055); exec 1(2, 278262503670654331); exec 1(805306369, 3763943296910752235); First of all, I am not familiar with the internals of JDBC drivers. Taking this into account, is this what it should be? From a symmetric point of view, I would assume that the correct would be the following: sexec 1(1, 957339737330229055); sexec 1(2, 278262503670654331); sexec 1(805306369, 3763943296910752235); That is, it should prepend a 's' character before an exec command. Last, are these exec commands (with or without a prepending 's') specific to MonetDB? In either case, is there any documentation to get familiar with their meaning? Thanks a lot, Babis

6 20

[MonetDB-users] Prepared statement not found (error: 07003)
by George Garbis 23 Feb '12

23 Feb '12

Hello all, I have a java project that creates a database using more than one connections and I get the following error: > 22000!07003:EXEC: no prepared statement with id: 1 > After digging in log files I created a simplified version of the problematic commands: Class.forName("nl.cwi.monetdb.jdbc.MonetDriver"); > Connection con1 = > DriverManager.getConnection("jdbc:monetdb://localhost/db1", "monetdb", > "monetdb"); > con1.setAutoCommit(true); > Connection con2 = > DriverManager.getConnection("jdbc:monetdb://localhost/db1", "monetdb", > "monetdb"); > con2.setAutoCommit(true); > > PreparedStatement ps2 = con2.prepareStatement("INSERT INTO table1 (a) > VALUES (CAST(? AS INTEGER))"); > ps2.setInt(1,1); ps2.addBatch(); > > Statement s1 = con1.createStatement(); > s1.execute("CREATE TABLE table2 ( a INTEGER )"); > s1.close(); > > ps2.executeBatch(); > During execution of ps2.executeBatch() the aforementioned error is occurred. The problem occurs because between creating the first prepared statement and executing it another statement is created and executed (this odd sequence of commands happens because the project is multi-threaded). The two queries change different tables so i wonder if there is something wrong in the commands above. It seems that MonetDB handles in a special way prepared statements and I have to execute each one before I define a new one. If yes can you give me some hints why this is happening or how i can work around this problem? Thank you in advance, George Garbis

3 3

[MonetDB-users] SQL:2003 support
by Gatis Ozoliņš 22 Feb '12

22 Feb '12

Are there any plans for SQL:2003 support? I am particularly interested in window functions (LAG, LEAD, DENSE_RANK ...) and MERGE statement.

1 0

[MonetDB-users] Transaction management in stored procedures
by Vassilis Papakonstantinou 20 Feb '12

20 Feb '12

Hi all, Does anyone knows how can I handle transactions in a stored procedure? In procedure definition on SQL Reference says that a procedure_statement may consists of transaction_statement etc. How transaction_statement implemented in a stored procedure? I tried (inside the BEGIN ... END block of stored procedure) the: START TRANSACTION; sql statements COMMIT; and: sql statements COMMIT; but both they do not work. Thanks, Vassilis

2 4

[MonetDB-users] Subquery with INNER JOIN
by George Garbis 20 Feb '12

20 Feb '12

Hello to all, I have some problems trying to use subqueries with INNER JOIN. Assume the following schema: CREATE TABLE table1 (a INTEGER); > CREATE TABLE table2 (a INTEGER, b INTEGER); > > INSERT INTO table1 VALUES(1); > INSERT INTO table1 VALUES(2); > INSERT INTO table2 VALUES(2, 2); > I pose this query > SELECT * FROM (SELECT a, 2 AS b FROM table1) t1 INNER JOIN table2 t2 ON > (t1.b = t2.b); > and I get the following mal errors: > TypeException:user.s5_1[44]:'bat.mirror' undefined in: _62:any := > bat.mirror(_61:int) > TypeException:user.s5_1[46]:'algebra.join' undefined in: _64:any := > algebra.join(_62:any, _63:bat[:oid,:oid]) > TypeException:user.s5_1[47]:'algebra.markH' undefined in: _65:any := > algebra.markH(_64:any, _66:oid) > TypeException:user.s5_1[48]:'algebra.markT' undefined in: _67:any := > algebra.markT(_64:any, _66:oid) > TypeException:user.s5_1[49]:'bat.reverse' undefined in: _68:any := > bat.reverse(_67:any) > TypeException:user.s5_1[50]:'algebra.join' undefined in: _69:any := > algebra.join(_68:any, _61:int) > TypeException:user.s5_1[51]:'algebra.join' undefined in: _70:any := > algebra.join(_65:any, _59:bat[:oid,:int]) > TypeException:user.s5_1[55]:'algebra.join' undefined in: _77:any := > algebra.join(_76:bat[:void,:oid], _65:any) > TypeException:user.s5_1[56]:'bat.reverse' undefined in: _78:any := > bat.reverse(_77:any) > TypeException:user.s5_1[57]:'algebra.markT' undefined in: _79:any := > algebra.markT(_78:any, _43:oid) > TypeException:user.s5_1[58]:'bat.reverse' undefined in: _80:any := > bat.reverse(_79:any) > TypeException:user.s5_1[59]:'algebra.leftjoin' undefined in: _81:any := > algebra.leftjoin(_80:any, _42:bat[:oid,:int]) > TypeException:user.s5_1[64]:'algebra.project' undefined in: _86:any := > algebra.project(_81:any, _83:int) > TypeException:user.s5_1[65]:'algebra.join' undefined in: _87:any := > algebra.join(_86:any, _85:bat[:int,:oid]) > TypeException:user.s5_1[66]:'bat.reverse' undefined in: _88:any := > bat.reverse(_87:any) > TypeException:user.s5_1[67]:'algebra.markT' undefined in: _89:any := > algebra.markT(_88:any, _21:oid) > TypeException:user.s5_1[68]:'bat.reverse' undefined in: _90:any := > bat.reverse(_89:any) > TypeException:user.s5_1[69]:'algebra.leftjoin' undefined in: _91:any := > algebra.leftjoin(_90:any, _20:bat[:oid,:int]) > TypeException:user.s5_1[76]:'algebra.markT' undefined in: _103:any := > algebra.markT(_87:any, _101:oid) > TypeException:user.s5_1[77]:'bat.reverse' undefined in: _104:any := > bat.reverse(_103:any) > TypeException:user.s5_1[78]:'algebra.leftjoin' undefined in: _105:any := > algebra.leftjoin(_104:any, _81:any) > TypeException:user.s5_1[79]:'algebra.project' undefined in: _106:any := > algebra.project(_105:any, _96:bte) > TypeException:user.s5_1[86]:'algebra.leftjoin' undefined in: _113:any := > algebra.leftjoin(_80:any, _59:bat[:oid,:int]) > TypeException:user.s5_1[87]:'algebra.leftjoin' undefined in: _114:any := > algebra.leftjoin(_104:any, _113:any) > 39000!program contains errors > If i don't use a subquery and i first create the table i need, the query executes flawlessly and it returns the expected results. CREATE TABLE table3 (a INTEGER, b INTEGER); > INSERT INTO table3(a,b) (SELECT a, 2 AS b FROM table1); > SELECT * FROM (SELECT a, b FROM table3) t1 INNER JOIN table2 t2 ON (t1.b = > t2.b); > Results: > +------+------+------+----+ > | a | b | a | b | > +==+===+===+===+ > | 2 | 2 | 2 | 2 | > | 1 | 2 | 2 | 2 | > +------+------+------+----+ > Can anyone help? Is there something wrong in my first query or it is a bug? Thank you in advance, George Garbis

2 1

[MonetDB-users] mapi get field types
by Stathis Gkotsis 18 Feb '12

18 Feb '12

Hi, I am issuing a query using the mapi using mapi_query. Is there a way to get the returned field types through the api? Thank you, Stathis

2 1

[MonetDB-users] Ending up with a sql/log.XX of 4GB
by Stefan de Konink 17 Feb '12

17 Feb '12

Over the night my database was running. Doing both insert (constant) and select jobs (incidental). It seems that the combination is fatal. Doing each thing on independant databases I don't have problems at all, but doing them within the same database all kind of badness arrises. The next badness is the internal sql log of 4GB, MonetDB reports disk full. Which is actually the case. The actual database contents is only 49MB upon start (after inserting static data). I expect 200.000 extra rows of dynamic data, with a cyclic pattern, to be added over a week time, overwritting eachother. I wonder if it could be related to 'no-autocommit' behavior of select queries outside of transactions. I would assume it does not make sense at all, but that seems the most 'logical' reason... I could recover from disk full to free up space, wait a long time with a high load. SQL log was empty. Please advise, Stefan

1 0

[MonetDB-users] Table column order and type
by Pete Black 16 Feb '12

16 Feb '12

Hi, From what I understand indexes are indicative[1], sortedness is determined at runtime[2] and the storage model should not effect the general schema design[3]. Has anyone had any experience with large time series where the column order and types have effected performance? For example having data with less variance and "uniqueness" earlier in a table and splitting up timestamps and storing their date and daytime components separately. Thanks, Pete [1]http://www.monetdb.org/Documentation/Manuals/SQLreference/Indices [2]http://old.nabble.com/column-sortedness-to33053179.html#a33053179 [3]http://old.nabble.com/General-Questions-on-Columnar-DB-Design-and-Queryin…

2 1

[MonetDB-users] Microsecond timestamp precision?
by Pete Black 16 Feb '12

16 Feb '12

Hi, The documentation doesn't mention the resolution of the timestamp[1] directly, but given the examples for the daytime one can assume that the it holds millisecond resolution. Why then are the trailing zeros below printed out? And is there a way to get microsecond resolution for example using mal instead? sql>DECLARE t timestamp(9); sql>SET t='2012-02-16 01:15:25.123456'; sql>SELECT t; +----------------------------+ | single_value | +============================+ | 2012-02-16 01:15:25.123000 | +----------------------------+ 1 tuple (1.293ms) Thanks, Pete [1]http://www.monetdb.org/Documentation/SQLreference/Temporal

1 0