February 2012 - users-list

[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

[MonetDB-users] Does Monetdb supports Date functions?
by Dongli 16 Feb '12

16 Feb '12

Hi guys, Does Monetdb supports "datediff" function? I didn't found this function while I run "select name from functions". Besides, Does Monetdb supports other Date relevant functions? Any advices will be highly appreciated. Thanks in advance, Tony

4 3

[MonetDB-users] Does Monetdb supports UTF-8 characters when I use 'as' clause?
by Dongli 15 Feb '12

15 Feb '12

Hi folks, Please take a look at following issue that I got today: The following queries I executed aim to test if Monetdb supports GBK characters(Chinese), since I have to use Chinese characters in my system to display the reports. I’m not sure if I missed some setting such as ‘set characters=’utf-8’? The following alias after the ‘as’ is a Chinese character ‘I’. sql>select source as '我' from session; 42000!syntax error, unexpected STRING in: "select source as '我'" sql>select source as 我 from session; unexpected character (U+6211) sql>select source as "我" from session; Invalid identifier '我' Thanks in advance, Tony

3 3

[MonetDB-users] database 'dw' has crashed after starting, manual intervention needed, check monetdbd's logfile for details
by Dongli 15 Feb '12

15 Feb '12

Hi folks, How can I recover from a crashed database? I got this error when I restart a running database 'dw', Do you have any ideas on this? 2012-02-15 13:59:28 MSG control[27007]: (local): served status list 2012-02-15 13:59:28 MSG merovingian[27007]: startup of database under maintenance 'dw' forced 2012-02-15 13:59:28 MSG merovingian[27007]: database 'dw' has crashed after start on 2012-02-15 13:58:47, attempting restart, up min/avg/max: 1d/1d/1d, crash average: 1.00 1.00 0.97 (45-1=44) 2012-02-15 13:59:28 MSG dw[27831]: arguments: /usr/lib/monetdb/bin/mserver5 --set gdk_dbfarm=/data/warehouse --dbname=dw --set merovingian_uri=mapi:monetdb://monet00:50000/dw --set mapi_open=false --set mapi_port=0 --set mapi_usock=/data/warehouse/dw/.mapi.sock --set monet_vault_key=/data/warehouse/dw/.vaultkey --set gdk_nr_threads=24 --set max_clients=64 --set sql_optimizer=default_pipe --set monet_daemon=yes 2012-02-15 13:59:28 MSG dw[27831]: # MonetDB 5 server v11.7.5 "Dec2011" 2012-02-15 13:59:28 MSG dw[27831]: # Serving database 'dw', using 24 threads 2012-02-15 13:59:28 MSG dw[27831]: # Compiled for x86_64-unknown-linux-gnu/64bit with 64bit OIDs dynamically linked 2012-02-15 13:59:28 MSG dw[27831]: # Found 23.533 GiB available main-memory. 2012-02-15 13:59:28 MSG dw[27831]: # Copyright (c) 1993-July 2008 CWI. 2012-02-15 13:59:28 MSG dw[27831]: # Copyright (c) August 2008-2012 MonetDB B.V., all rights reserved 2012-02-15 13:59:28 MSG dw[27831]: # Visit http://www.monetdb.org/ for further information 2012-02-15 13:59:28 MSG dw[27831]: # Listening for UNIX domain connection requests on mapi:monetdb:///data/warehouse/dw/.mapi.sock 2012-02-15 13:59:28 MSG dw[27831]: # MonetDB/SQL module loaded 2012-02-15 13:59:28 MSG merovingian[27007]: database 'dw' (27831) was killed by signal SIGSEGV 2012-02-15 13:59:28 ERR control[27007]: (local): failed to fork mserver: database 'dw' has crashed after starting, manual intervention needed, check monetdbd's logfile for details >>The following is my platform information: MonetDB 5 server v11.7.5 "Dec2011" (64-bit, 64-bit oids) Copyright (c) 1993-July 2008 CWI Copyright (c) August 2008-2012 MonetDB B.V., all rights reserved Visit http://www.monetdb.org/ for further information Found 23.5GiB available memory, 24 available cpu cores Libraries: libpcre: 6.6 06-Feb-2006 (compiled with 6.6) openssl: OpenSSL 0.9.8e-rhel5 01 Jul 2008 (compiled with OpenSSL 0.9.8e-fips-rhel5 01 Jul 2008) libxml2: 2.6.26 (compiled with 2.6.26) Compiled by: root@monet00 (x86_64-unknown-linux-gnu) Compilation: gcc -g -O2 Linking : /usr/bin/ld -m elf_x86_64 Thanks in advance, Tony

1 0

[MonetDB-users] failed to fork mserver: database 'dw' has crashed after starting, manual intervention needed, check monetdbd's logfile for details
by Tony 15 Feb '12

15 Feb '12

Hi folks, How can I recover from a crashed database? I got this error when I restart a running database 'dw', Do you have any ideas on this? 2012-02-15 13:59:28 MSG control[27007]: (local): served status list 2012-02-15 13:59:28 MSG merovingian[27007]: startup of database under maintenance 'dw' forced 2012-02-15 13:59:28 MSG merovingian[27007]: database 'dw' has crashed after start on 2012-02-15 13:58:47, attempting restart, up min/avg/max: 1d/1d/1d, crash average: 1.00 1.00 0.97 (45-1=44) 2012-02-15 13:59:28 MSG dw[27831]: arguments: /usr/lib/monetdb/bin/mserver5 --set gdk_dbfarm=/data/warehouse --dbname=dw --set merovingian_uri=mapi:monetdb://monet00:50000/dw --set mapi_open=false --set mapi_port=0 --set mapi_usock=/data/warehouse/dw/.mapi.sock --set monet_vault_key=/data/warehouse/dw/.vaultkey --set gdk_nr_threads=24 --set max_clients=64 --set sql_optimizer=default_pipe --set monet_daemon=yes 2012-02-15 13:59:28 MSG dw[27831]: # MonetDB 5 server v11.7.5 "Dec2011" 2012-02-15 13:59:28 MSG dw[27831]: # Serving database 'dw', using 24 threads 2012-02-15 13:59:28 MSG dw[27831]: # Compiled for x86_64-unknown-linux-gnu/64bit with 64bit OIDs dynamically linked 2012-02-15 13:59:28 MSG dw[27831]: # Found 23.533 GiB available main-memory. 2012-02-15 13:59:28 MSG dw[27831]: # Copyright (c) 1993-July 2008 CWI. 2012-02-15 13:59:28 MSG dw[27831]: # Copyright (c) August 2008-2012 MonetDB B.V., all rights reserved 2012-02-15 13:59:28 MSG dw[27831]: # Visit http://www.monetdb.org/ for further information 2012-02-15 13:59:28 MSG dw[27831]: # Listening for UNIX domain connection requests on mapi:monetdb:///data/warehouse/dw/.mapi.sock 2012-02-15 13:59:28 MSG dw[27831]: # MonetDB/SQL module loaded 2012-02-15 13:59:28 MSG merovingian[27007]: database 'dw' (27831) was killed by signal SIGSEGV 2012-02-15 13:59:28 ERR control[27007]: (local): failed to fork mserver: database 'dw' has crashed after starting, manual intervention needed, check monetdbd's logfile for details >>The following is my platform information: MonetDB 5 server v11.7.5 "Dec2011" (64-bit, 64-bit oids) Copyright (c) 1993-July 2008 CWI Copyright (c) August 2008-2012 MonetDB B.V., all rights reserved Visit http://www.monetdb.org/ for further information Found 23.5GiB available memory, 24 available cpu cores Libraries: libpcre: 6.6 06-Feb-2006 (compiled with 6.6) openssl: OpenSSL 0.9.8e-rhel5 01 Jul 2008 (compiled with OpenSSL 0.9.8e-fips-rhel5 01 Jul 2008) libxml2: 2.6.26 (compiled with 2.6.26) Compiled by: root@monet00 (x86_64-unknown-linux-gnu) Compilation: gcc -g -O2 Linking : /usr/bin/ld -m elf_x86_64 Thanks in advance, Tony

2 1

[MonetDB-users] Odd Single Thread Only Processing in Dec 2011 Release
by Henry Addington 14 Feb '12

14 Feb '12

Hi: We are testing Dec 2011 Release to upgrade from Oct 2010 SP1 release on CentOS 5.2 64-bit with 100+ GB memory. We have a BI tool that users use to generate various reports. We have access to the SQL statements that get generated by the BI tool and sent to the MonetDB. So, we know exactly the SQL statements that are being sent to MonetDB. One very odd thing that we are seeing the December release is that the reports from the BI tool are very, very slow (x10 - x15 from Oct releasee). It is obvious why looking at the server resource use. It looks like only 1 cpu is being used for all the queries! However, if I take the SQL statements generated by the BI tool and run them in DBVisualizer, all 20 cpu cores are used and the results return quickly. I am at a loss as to why we are seeing such different performance from identical SQL statements... Is there a configuration setting or something in the December release that we overlooked? Can you please help us troubleshoot? Thank you. HS

2 3