users-list February 2012

users-list@monetdb.org

23 participants
26 discussions

[MonetDB-users] "ERROR = !Connection terminated" for some SQL queries

by Viktor Rosenfeld

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

10 years, 4 months

[MonetDB-users] Database crash

by Stathis Gkotsis

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.

10 years, 5 months

[MonetDB-users] Very Slow Selects

by Joseph Brower

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

10 years, 5 months

[MonetDB-users] Announcement: New Dec2011-SP1 Bugfix release of MonetDB suite

by Fabian Groffen

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

10 years, 5 months

[MonetDB-users] max concurrent limit?

by Allen Zhang

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

10 years, 5 months

[MonetDB-users] MonetDB JDBC 1.9 question

by Charalampos Nikolaou

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

10 years, 5 months

[MonetDB-users] Prepared statement not found (error: 07003)

by George Garbis

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

10 years, 5 months

[MonetDB-users] SQL:2003 support

by Gatis Ozoliņš

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

10 years, 5 months

[MonetDB-users] Transaction management in stored procedures

by Vassilis Papakonstantinou

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

10 years, 5 months

[MonetDB-users] Subquery with INNER JOIN

by George Garbis

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

10 years, 5 months

2022

2021

2020

2019

2018

2017

2016

2015

2014

2013

2012

2011

2010

2009

2008

2007

2006

2005

2004

users-list February 2012