- users-list - monetdb.org

error documentation ALTER USER
by Pierre-Adrien Coustillas 03 Oct '14

03 Oct '14

Hello, There is an error on this page : https://www.monetdb.org/Documentation/SQLreference/Authorisation It's not UNENCRYPYED but UNENCRYPTED sql>ALTER USER SET UNENCRYPYED PASSWORD 'azerty' USING OLD PASSWORD 'monetdb'; syntax error, unexpected IDENT, expecting PASSWORD in: "alter user set unencrypyed" sql>ALTER USER SET UNENCRYPTED PASSWORD 'azerty' USING OLD PASSWORD 'monetdb'; operation successful (19.015ms) 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

2 1

MonetDB bulk load error
by Frik Lubbe 02 Oct '14

02 Oct '14

Hi all, I'm new to MonetDB and ran into a bulk load error. I have a delimited file to be loaded. Column Delimiter : '~' Quote Char : ' " ' (double quote) Row Delimiter: '\n' My load script is this: sql>COPY 1000 RECORDS INTO voc.dim_anonymous_wallets FROM '/tmp/monetdb/201409_BA_ANON_WALLETS.SML' USING DELIMITERS '\~','\\n','\"' NULL AS ''; Error: Incomplete record at end of file. failed to import table This file loads OK in other db engines. Any ideas will help please. Regards, Frik

2 1

Bulk Load error
by Frik Lubbe 02 Oct '14

02 Oct '14

Hi all, I'm new to MonetDB and ran into a bulk load error. I have a delimited file to be loaded. Column Delimiter : '~' Quote Char : ' " ' (double quote) Row Delimiter: '\n' My load script is this: sql>COPY 1000 RECORDS INTO voc.dim_anonymous_wallets FROM '/tmp/monetdb/201409_BA_ANON_WALLETS.SML' USING DELIMITERS '\~','\\n','\"' NULL AS ''; Error: Incomplete record at end of file. failed to import table This file loads OK in other db engines. Any ideas will help please. Regards, Frik

2 1

why is my SAMPLE percentage limiter throwing an error?
by Anthony Damico 30 Sep '14

30 Sep '14

sorry if this is a silly question.. i'm trying to sample a percentage of a table. from https://www.monetdb.org/Documentation/Cookbooks/SQLrecipes/Sampling it looks like these should work, but i must be making a mistake in my syntax somewhere? thanks!! create table test ( a int , b double precision ); insert into test values( 1 , 3.3 ); insert into test values( 13 , 2.3 ); insert into test values( 3 , 0.3 ); select * from test sample 0.1 ; TypeException:user.s12_1[9]:'sample.subuniform' undefined in: _18:any := sample. subuniform(_15:bat[:oid,:int], _16:dbl) program contains errors select * from test uniform sample 0.1 ; TypeException:user.s13_1[9]:'sample.subuniform' undefined in: _18:any := sample. subuniform(_15:bat[:oid,:int], _16:dbl) program contains errors # MonetDB 5 server v11.17.21 "Jan2014-SP3" # Serving database 'demo', using 8 threads # Compiled for x86_64-pc-winnt/64bit with 64bit OIDs dynamically linked # Found 7.860 GiB available main-memory. # Copyright (c) 1993-July 2008 CWI. # Copyright (c) August 2008-2014 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/ # MonetDB/JAQL module loaded # MonetDB/SQL module loaded >

4 10

MEDIAN ???
by Budulinku Dejmihrasku 26 Sep '14

26 Sep '14

Hi, just a quick question regarding to MEDIAN: with x as (select 1.0 as a union all select 2.0 as a) select sys.median(a) as result from x returns result=1 i would expect result=1.5 though any suggestion? thanks milan

4 7

Show create table equivalent
by Guillaume Bassot 23 Sep '14

23 Sep '14

Hello everyone, I'm looking for the mysql show create table equivalent. I need this as a query to get the result in a php script. (I already found how to do this with mclient and msqldumq) I found this query that need to be parsed to recreate the "CREATE TABLE". SELECT * FROM SYS.tables tbl INNER JOIN SYS.columns col ON (tbl.id=col.table_id) WHERE tbl.name='exemple'; But i still miss the table's constraints (primary key, unique key) Could you tell me where to find this information ? Thank you for helping. 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

4 3

table size
by Christian Braun 22 Sep '14

22 Sep '14

Hello, i want to know how much disk space each table uses. How can i find out to which table each of the files in the database directory belong? Thanks in advance, Christian

2 1

Crashing when querying on date column
by Nick Chadwick 18 Sep '14

18 Sep '14

Hi. I'm currently evaluating MonetDB for an upcoming project, and am having some problems when using date criteria in the where clause. I have a very simple table: Create table negs (id varchar(50) not null, xdate date not null, market int, product int, notional double) Id and xdate form a composite primary key, and I have indices on xdate, market and product. If I run the following query: Select * from negs where xdate = current_date the client crashes. Similarly if I run the same query over a JDBC connection from java, the connection is lost. Looking in the log file, it says that the database was killed due to a SIGSEGV. Am I doing something wrong? I am running on Ubuntu 14.04, and built MonetDB from the latest source tarball, 11.17.21. Thanks, Nick www.digiterre.com _________________ Privileged or confidential information may be contained in this message. If you are not the addressee of this message please notify the sender by return and thereafter delete the message, and you may not use, copy, disclose or rely on the information contained in it. Internet e-mail may be susceptible to data corruption, interception and unauthorised amendment for which Digiterre does not accept liability. Whilst we have taken reasonable precautions to ensure that this e-mail and any attachments have been swept for viruses, Digiterre does not accept liability for any damage sustained as a result of viruses. Statements in this message that do not relate to the business of Digiterre are neither given nor endorsed by the company or its directors.

4 12

MonetDB Optimizer
by Steffen B 15 Sep '14

15 Sep '14

Hello, I'm writing a back end for Database Supported Haskell (DSH) to target MonetDB. Can I please get some help with running my generated MAL-code though an optimizer? I do not really understand the concept behind this: https://www.monetdb.org/Documentation/Manuals/MonetDB/Optimizers Do I have to generate optimizer code at the appropriate places in my code? Are the optimizer instructions woven into the MAL-Program? Isn't there a optimizer function `a la: optimize :: MalCode -> MalCode? Please be so kind and give me a little bit of futher explaination.

2 1

index on Monetdb
by Bing Zhang 11 Sep '14

11 Sep '14

Hello: I wonder whether the index will give monetdb query more performance or not. For example, like tpc-h queries, if I put index on the particular fields, then the query on over those fields will faster? Thanks & Regards.

2 2

monetdb performance Time measurement
by Bing Zhang 11 Sep '14

11 Sep '14

hi: I am trying to measure query performance of monetdb, but not sure about the time output by using option -ims includes the query results printing time, or it is just the query time. Is there some way to silence the output of queries? Thanks.

2 2

Fwd: COLD RUNS
by Prashant Patel 11 Sep '14

11 Sep '14

Dear all, as a part of my project, i have to check the performance of monetdb column-store database. I am very new to this technology, so i dont know where the monetdb caches its previous query results.. I want to know how can i take results for cold runs on monetdb ( i have tried closing monetdb client and server every time i fire a query , but doesnt seem to clear the cache and i always get hot runs result) so please can anyone help me regarding these.... suggestions are appreciated.. thanks in advance.. Prashant Rajkotiya M.tech. (ICT) DA-IICT ,Gandhinagar

5 5

MonetDB Cluster Management
by Ali Salem 05 Sep '14

05 Sep '14

Hello all, I followed the tutorial on https://www.monetdb.org/Documentation/Cookbooks/SQLrecipes/ClusterManagement but I still can't get it to work. I followed all instructions, but *monetdb discover *only returns instance(s) running on the same machine, but not others on the network. I am using three machines running debian, with monetdb installed and configured according to the link above. What am I missing? Your assistance is highly appreciated.

2 4

Messed up MonetDB installation? SQL module not initialized
by Steffen B 05 Sep '14

05 Sep '14

Hello, because of some library stuff I had to reinstall MonetDB on my Linux Mint/Ubuntu maschine. I removed all Packages that had anything to do with monetdb and did a fresh sudo apt-get install monetdb5-sql monetdb-client But now when I start mclient -l mal the SQL-module seems to be broken. I can do a sql.init(); without any errors but when I do t := sql.mvc(); I get: MAPI = (monetdb) /tmp/.s.monetdb.50000 QUERY = t := sql.mvc(); ERROR = !SQLException:mvc:SQL module not initialized Please tell me which additional information you need to help me. Thank you very much Steffen

1 0

JDBC driver problem - rounding decimal numbers during binding
by Radovan Bičiště 04 Sep '14

04 Sep '14

Hello, Today I discovered problem in JDBC driver that caused a very ugly problem with rounding decimal numbers. It rounds decimal numbers during binding. I encountered the bug with the latest driver from Jan2014-SP3 distribution. I filed bug 3561 in MonetDB bug system. Regards, Radovan

1 0

Available binary functions
by Dennis Pallett 02 Sep '14

02 Sep '14

Hi all, I'm just wondering what kind of binary functions MonetDB comes with standard? I'm specifically looking for a function to convert a smallint/int/bigint into a byte array (either big or little endian), concat several byte arrays into a single byte array and encode a byte array as a hex string. Do any such functions exist or do I need to write them myself in C? Best regards, Dennis Pallett

2 2

Costly bat.new/bat.append not optimized out
by Frédéric Jolliton 28 Aug '14

28 Aug '14

Hi, Unless I'm missing something, I do not understand why this apparent pattern is not easily optimized out: X_3:bat[:oid,:lng] =<tmp_321264>[0] := bat.new(nil:oid,nil:lng); .. # X_3 is never used before this line. X_19=<tmp_331015>[23068647] := <some computation> X_20=<tmp_321264>[23068647] := bat.append(X_3=<tmp_321264>:bat[:oid,:lng][23068647], X_19=<tmp_331015>[23068647],true); # X_3 is never used after this line. An empty BAT is created for the sole purpose of appending a BAT with millions of rows, discarding the original one.. instead of just using it! This account for up to 30% of the time spent for some SQL queries! Like this one: SELECT name, avg(value) FROM some_view GROUP BY name LIMIT 10; I tried to manually remove these extra operation, and I got the exact same result, in less time. (I took the output of EXPLAIN, removed the lines in question, and added a bit of type information for the function to compile, before playing it in mclient -l mal.) Regarding the setup, for our tests, we have 3 tables (say, a, b and c) of 11M rows each, then a view declared as: CREATE VIEW some_view AS (SELECT * FROM a UNION ALL SELECT * FROM b UNION ALL SELECT * FROM c); I've tested it with v11.17.21 and v11.19.0, with the "default" optimizer. My question: is this extra operation required, or could it be optimized in MonetDB? -- Frédéric Jolliton SecurActive

2 7

ODBC driver and config on the Mac?
by Robert Kosara 19 Aug '14

19 Aug '14

Hello, I’ve been trying to connect to MonetDB via ODBC on Mac OS X, without luck. I’m seeing the ODBC drivers in /usr/local/monetdb/lib/, but I’m confused that they are .so files and not .dylib. It looks like all the others are .dylib, and as far as I know that’s required so they can be loaded dynamically. I’ve tried using the .so files in the ODBC manager, but that has only made it crash. Has anybody used MonetDB with ODBC on the Mac? Can you perhaps show me your configuration? Thanks, Robert

2 1

Table is not unique, corrupt catalog?
by Ali Salem 11 Aug '14

11 Aug '14

Hello all, Last night I imported 500M records into my fact table, it took around 19 hours, but it worked fine. I created my indexes afterwards with no issues as well. However, when I tried to access the rest of the tables in my database, I received the following error: table table_name is not unique, corrupt catalog? Any idea why this happened and how to fix it? Thank you.

1 1

MonetDB partition by day
by Ali Salem 08 Aug '14

08 Aug '14

Hello all, I know MonetDB has window functions, but can I partition a table by day? If you can direct me to a link with examples, I'd appreciate it. Thanks

1 0

MEDIAN(double) not found
by Radovan Bičiště 08 Aug '14

08 Aug '14

Hello, We use MonetDB Jan2014-SP2. When I try MEDIAN function it simply says: MEDIAN: no such operator 'median(double)' [SQL State=22000] Am I missing something? I tried to execute section of 39_analytics.sql containing the MEDIAN function. It says MEDIAN function is already there. Thank you for any tips. Radovan Test Case: I took the sql from bug 2454: select median(id) from sys.tables; result: An error occurred when executing the SQL command: select median(id) from sys.tables MEDIAN: no such operator 'median(int)' [SQL State=22000] Execution time: 0.03s 1 statement failed.

2 2

Connection Reset(mserver still alive?)
by Ali Salem 06 Aug '14

06 Aug '14

Hello, I have recently started using MonetDB for a BI project, and it's doing pretty well. However, I receive a connection reset error each time I run an ETL process (I use Pentaho PDI) to populate my dimensional schema. This error is reported by the MonetDB JDBC driver *Connection reset(mserver still alive?)* This usually happens after a random amount of time, 30 min, 45 min, 1 hour, etc. Any pointers related to this would be very helpful. Specs ------- Ubuntu 12.04 8 GB Ram 500 GB Space Pentaho PDI (community edition) Monetdb5 (installed using apt-get) Thank you.

4 6

SIGSEGV during log flush
by Richard Hughes 01 Aug '14

01 Aug '14

Hello, Can I persuade a developer to help me out with bug 3422 [https://www.monetdb.org/bugzilla/show_bug.cgi?id=3422#c4]? I'm amassing an unhealthy collection of core dumps now (about one a day), and the latest is from Jan2014SP3 which I upgraded to last night. FWIW I'm no longer sure that I am looking at the same SIGSEGV as the one that the original bug reporter was talking about - of course I can create a new Bugzilla entry if it helps. This is our staging platform so I have the ability to run all sorts of custom builds or anything else that will help debug it, I'm just not sure where to start looking; I'd really appreciate some hints. Thanks for your help, Richard.

1 0

Adding conditions slows query 1000x
by Dennis Pallett 31 Jul '14

31 Jul '14

Hi all, When I run the following query the results are computed extremely fast (within 5 ms): SELECT id_str, len,coordinates_x,coordinates_y FROM uk_neogeo_sorted WHERE coordinates_x >= 0.0 AND coordinates_x <= 22.499999996867977 LIMIT 100; However if I add additional conditions to the query so that it becomes the following: SELECT id_str,len,coordinates_x,coordinates_y FROM uk_neogeo_sorted WHERE coordinates_x >= 0.0 AND coordinates_x <= 22.499999996867977 AND coordinates_y >= 0.0 AND coordinates_y <= 61.60639636617352 LIMIT 100; The time it takes to compute the results is approximately 1000x bigger (i.e. 5 seconds). Clearly the additional conditions on the coordinates_y column is forcing MonetDB to take a different query strategy but I don't know how I can solve this. In Postgres I would make sure there is an index on the (coordinates_x, coordinates_y) column but this doesn't seem to have any effect with MonetDB. I've attached traces of both queries. There are approximately 11 million rows in the table. Can anyone tell me why there is such a huge difference in query execution time and how I can prevent it? Best regards, Dennis Pallett

5 15

Announcement: New Jan2014-SP3 Bugfix release of MonetDB suite
by Sjoerd Mullender 31 Jul '14

31 Jul '14

The MonetDB team at CWI/MonetDB BV is pleased to announce the Jan2014-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/>. Jan 2014-SP3 bugfix release Build Environment * Fix configure to continue without Python if the python binary is too old. This instead of always aborting configure if python happens to be too old. Bug Fixes * 3487: dead link to "Professional services" * 3500: MonetDB driver wants an empty string for SQLTables and SQLColumns API calls, where other drivers expect NULL * 3514: mserver5 crash due (assertion failure in gdk_select.c) * 3515: mserver5 crash due (assertion failure in gdk_bat.c) * 3519: Uppercase TRUE/FALSE strings cannot be converted to boolean values

1 0

High amount of VIRT and high amount of writing
by amihay gonen 29 Jul '14

29 Jul '14

Hi all , we are trying to compare oracle vs monetdb. the query we are try to run is : select count( client_code_new ) as s from ( SELECT t.client_code_new, t.bank, t.branch_new, t.ACCOUNT, t.instrument_ident, MAX (t.system_code) FROM transactions t left outer join ( SELECT c.linked_open_transaction_new as linked_open_transaction_new FROM calculated_taxs c GROUP BY c.linked_open_transaction_new) c_temp on t.transaction_no = c_temp.linked_open_transaction_new join clients_list cl on cl.client_code = t.client_code_new AND cl.branch = t.branch_new WHERE t.opg_cls = 'O' AND (t.close_trans_disregard_balance_new) =0 and new_system_code = 1 GROUP BY t.client_code_new, t.bank, t.branch_new, t.ACCOUNT, t.instrument_ident) a ; tables sizes are: transactions = 103M rows calculated_taxs= 79M rows The query is taking 1min to run. we've notice that during the query run the VIRT column (from top utility) is become very high 10G , and during the we see extensive write to disk . why does monet is do so much writing ? thanks, amihay bellow is output of minimal_pipe optimizer +------------------------------------------------------------------------------------------------------------------------------------------------------------+ | mal | +============================================================================================================================================================+ | function user.s4_3{autoCommit=true}(A0:str,A1:int,A2:int):void; | | X_5 := sql.mvc(); | | X_6:bat[:oid,:oid] := sql.tid(X_5,"sys","clients_list"); | | X_9 := sql.bind(X_5,"sys","clients_list","client_code",0); | | (X_12,r1_12) := sql.bind(X_5,"sys","clients_list","client_code",2); | | X_15 := sql.bind(X_5,"sys","clients_list","client_code",1); | | X_17 := sql.delta(X_9,X_12,r1_12,X_15); | | X_18 := algebra.leftfetchjoin(X_6,X_17); | | X_19 := batcalc.int(X_18); | | X_20:bat[:oid,:wrd] := batcalc.hash(X_19); | | X_23 := calc.int(22); | | X_25 := sql.bind(X_5,"sys","clients_list","branch",0); | | (X_27,r1_27) := sql.bind(X_5,"sys","clients_list","branch",2); | | X_29 := sql.bind(X_5,"sys","clients_list","branch",1); | | X_30 := sql.delta(X_25,X_27,r1_27,X_29); | | X_31 := algebra.leftfetchjoin(X_6,X_30); | | X_32:bat[:oid,:wrd] := mkey.bulk_rotate_xor_hash(X_20,X_23,X_31); | | X_33:bat[:oid,:int] := bat.new(nil:oid,nil:int); | | X_36:bat[:oid,:oid] := sql.tid(X_5,"sys","transactions"); | | X_38 := sql.bind(X_5,"sys","transactions","close_trans_disregard_balance_new",0); | | (X_40,r1_40) := sql.bind(X_5,"sys","transactions","close_trans_disregard_balance_new",2); | | X_43 := sql.bind(X_5,"sys","transactions","close_trans_disregard_balance_new",1); | | X_44 := sql.delta(X_38,X_40,r1_40,X_43); | | X_45 := algebra.leftfetchjoin(X_36,X_44); | | X_46 := A1; | | X_47 := sql.bind(X_5,"sys","transactions","new_system_code",0); | | (X_50,r1_50) := sql.bind(X_5,"sys","transactions","new_system_code",2); | | X_53 := sql.bind(X_5,"sys","transactions","new_system_code",1); | | X_55 := sql.delta(X_47,X_50,r1_50,X_53); | | X_56 := algebra.leftfetchjoin(X_36,X_55); | | X_57 := A2; | | X_58 := sql.bind(X_5,"sys","transactions","opg_cls",0); | | (X_60,r1_60) := sql.bind(X_5,"sys","transactions","opg_cls",2); | | X_62 := sql.bind(X_5,"sys","transactions","opg_cls",1); | | X_63 := sql.delta(X_58,X_60,r1_60,X_62); | | X_64 := algebra.leftfetchjoin(X_36,X_63); | | X_65 := A0; | | X_66 := algebra.subselect(X_64,X_65,X_65,true,true,false); | | X_69 := algebra.subselect(X_56,X_66,X_57,X_57,true,true,false); | | X_70 := algebra.subselect(X_45,X_69,X_46,X_46,true,true,false); | | X_71 := sql.bind(X_5,"sys","transactions","transaction_no",0); | | (X_74,r1_74) := sql.bind(X_5,"sys","transactions","transaction_no",2); | | X_77 := sql.bind(X_5,"sys","transactions","transaction_no",1); | | X_78 := sql.delta(X_71,X_74,r1_74,X_77); | | X_79 := algebra.leftfetchjoin(X_36,X_78); | | X_80 := algebra.leftfetchjoin(X_70,X_79); | | X_81:bat[:oid,:oid] := sql.tid(X_5,"sys","calculated_taxs"); | | X_83 := sql.bind(X_5,"sys","calculated_taxs","linked_open_transaction_new",0); | | (X_86,r1_86) := sql.bind(X_5,"sys","calculated_taxs","linked_open_transaction_new",2); | | X_89 := sql.bind(X_5,"sys","calculated_taxs","linked_open_transaction_new",1); | | X_91 := sql.delta(X_83,X_86,r1_86,X_89); | | X_92 := algebra.leftfetchjoin(X_81,X_91); | | (X_93,r1_93,r2_93) := group.subgroupdone(X_92); | | X_96 := algebra.leftfetchjoin(r1_93,X_92); | | X_97 := batcalc.lng(X_96); | | (X_98,r1_98) := algebra.join(X_80,X_97); | | X_100 := sql.bind(X_5,"sys","transactions","client_code_new",0); | | (X_102,r1_102) := sql.bind(X_5,"sys","transactions","client_code_new",2); | | X_104 := sql.bind(X_5,"sys","transactions","client_code_new",1); | | X_106 := sql.delta(X_100,X_102,r1_102,X_104); | | X_107 := algebra.leftfetchjoin(X_36,X_106); | | X_108 := algebra.leftfetchjoin(X_70,X_107); | | X_109 := algebra.leftfetchjoin(X_98,X_108); | | X_110 := bat.append(X_33,X_109,true); | | X_112 := bat.mirror(X_80); | | X_113 := algebra.tdiff(X_112,X_98); | | X_114 := algebra.leftfetchjoin(X_113,X_108); | | X_115 := bat.append(X_110,X_114,true); | | X_116:bat[:oid,:wrd] := batcalc.hash(X_115); | | X_119 := calc.int(22); | | X_121:bat[:oid,:int] := bat.new(nil:oid,nil:int); | | X_124 := sql.bind(X_5,"sys","transactions","branch_new",0); | | (X_128,r1_128) := sql.bind(X_5,"sys","transactions","branch_new",2); | | X_131 := sql.bind(X_5,"sys","transactions","branch_new",1); | | X_133 := sql.delta(X_124,X_128,r1_128,X_131); | | X_134 := algebra.leftfetchjoin(X_36,X_133); | | X_135 := algebra.leftfetchjoin(X_70,X_134); | | X_136 := algebra.leftfetchjoin(X_98,X_135); | | X_137 := bat.append(X_121,X_136,true); | | X_138 := algebra.leftfetchjoin(X_113,X_135); | | X_139 := bat.append(X_137,X_138,true); | | X_140:bat[:oid,:wrd] := mkey.bulk_rotate_xor_hash(X_116,X_119,X_139); | | (X_141,r1_141) := algebra.join(X_32,X_140); | | X_143 := algebra.leftfetchjoin(X_141,X_19); | | X_144 := algebra.leftfetchjoin(r1_141,X_115); | | X_145:bat[:oid,:bit] := batcalc.==(X_143,X_144); | | X_147 := calc.bit(true); | | X_149 := algebra.subselect(X_145,X_147,X_147,true,true,false); | | X_151 := algebra.leftfetchjoin(X_149,X_141); | | X_152 := algebra.leftfetchjoin(X_151,X_31); | | X_153 := algebra.leftfetchjoin(X_149,r1_141); | | X_154 := algebra.leftfetchjoin(X_153,X_139); | | X_155:bat[:oid,:bit] := batcalc.==(X_152,X_154); | | X_157 := calc.bit(true); | | X_158 := algebra.subselect(X_155,X_157,X_157,true,true,false); | | X_159 := algebra.leftfetchjoin(X_158,X_153); | | X_160:bat[:oid,:dbl] := bat.new(nil:oid,nil:dbl); | | X_163 := sql.bind(X_5,"sys","transactions","bank",0); | | (X_168,r1_168) := sql.bind(X_5,"sys","transactions","bank",2); | | X_171 := sql.bind(X_5,"sys","transactions","bank",1); | | X_173 := sql.delta(X_163,X_168,r1_168,X_171); | | X_174 := algebra.leftfetchjoin(X_36,X_173); | | X_175 := algebra.leftfetchjoin(X_70,X_174); | | X_176 := algebra.leftfetchjoin(X_98,X_175); | | X_177 := bat.append(X_160,X_176,true); | | X_178 := algebra.leftfetchjoin(X_113,X_175); | | X_179 := bat.append(X_177,X_178,true); | | X_180 := algebra.leftfetchjoin(X_159,X_179); | | X_181:bat[:oid,:str] := bat.new(nil:oid,nil:str); | | X_183 := sql.bind(X_5,"sys","transactions","account",0); | | (X_185,r1_185) := sql.bind(X_5,"sys","transactions","account",2); | | X_187 := sql.bind(X_5,"sys","transactions","account",1); | | X_188 := sql.delta(X_183,X_185,r1_185,X_187); | | X_189 := algebra.leftfetchjoin(X_36,X_188); | | X_190 := algebra.leftfetchjoin(X_70,X_189); | | X_191 := algebra.leftfetchjoin(X_98,X_190); | | X_192 := bat.append(X_181,X_191,true); | | X_194 := algebra.leftfetchjoin(X_113,X_190); | | X_195 := bat.append(X_192,X_194,true); | | X_196 := algebra.leftfetchjoin(X_159,X_195); | | X_197:bat[:oid,:str] := bat.new(nil:oid,nil:str); | | X_199 := sql.bind(X_5,"sys","transactions","instrument_ident",0); | | (X_204,r1_204) := sql.bind(X_5,"sys","transactions","instrument_ident",2); | | X_207 := sql.bind(X_5,"sys","transactions","instrument_ident",1); | | X_209 := sql.delta(X_199,X_204,r1_204,X_207); | | X_210 := algebra.leftfetchjoin(X_36,X_209); | | X_211 := algebra.leftfetchjoin(X_70,X_210); | | X_212 := algebra.leftfetchjoin(X_98,X_211); | | X_213 := bat.append(X_197,X_212,true); | | X_214 := algebra.leftfetchjoin(X_113,X_211); | | X_215 := bat.append(X_213,X_214,true); | | X_216 := algebra.leftfetchjoin(X_159,X_215); | | X_217 := algebra.leftfetchjoin(X_159,X_115); | | X_218 := algebra.leftfetchjoin(X_159,X_139); | | (X_219,r1_219,r2_219) := group.subgroup(X_218); | | (X_222,r1_222,r2_222) := group.subgroup(X_217,X_219); | | (X_225,r1_225,r2_225) := group.subgroup(X_216,X_222); | | (X_228,r1_228,r2_228) := group.subgroup(X_196,X_225); | | (X_231,r1_231,r2_231) := group.subgroupdone(X_180,X_228); | | X_234 := algebra.leftfetchjoin(r1_231,X_217); | | X_235 := algebra.selectNotNil(X_234); | | X_236 := aggr.count(X_235); | | sql.exportValue(1,".L2","s","wrd",64,0,6,X_236,""); | | end s4_3; | | # querylog.define("explain \nselect count( client_code_new ) as s from \n(\n select t.client_code_new, t.bank, t.branch_new, t.account, t. | : instrument_ident, max (t.system_code)\n from transactions t left outer join \n ( select c.linked_open_transaction_new as linked_open_transa : : ction_new from calculated_taxs c group by c.linked_open_transaction_new) c_temp\n on t.transaction_no = c_temp.linked_open_transaction_new \njoin cli : : ents_list cl on cl.client_code = t.client_code_new and cl.branch = t.branch_new\n where t.opg_cls = \\'O\\' and (t.close_trans_disregard_balance_new) = : : 0 and new_system_code = 1 \n group by t.client_code_new, t.bank, t.branch_new, t.account, t.instrument_ident) a : : ;","minimal_pipe") : +------------------------------------------------------------------------------------------------------------------------------------------------------------+

2 1

monetdb is slow to backup
by Pierre-Adrien Coustillas 29 Jul '14

29 Jul '14

Hello, I'm used to save monetdb using msqldump : msqldump --database=base --table=table > backup.sql This command takes 30 minutes to backup 6 GB , but 4 minutes to restore. How can I increase backup speed ? 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

3 5

GDKerror when using COPY INTO sql commands
by Mahmut Uludag 25 Jul '14

25 Jul '14

Hi, We update our MonetDB databases using COPY INTO commands, we recently have started to get errors similar to the following error. GDKerror:!ERROR: GDKload: cannot read: name=13/52/135274, ext=theap, 56932 bytes missing. !OS: No such file or directory GDKload: cannot read: name=13/56/135610, ext=theap, 906 bytes missing. !OS: No such file or directory Deleting and recreating the tables and the schema haven't helped. If we create a new table with a table name not used in the past then we are able to use COPY INTO commands to update the new table. It is also possible to update the broken tables using 'INSERT INTO' commands. We see the problem only when we use COPY INTO commands. We use MonetDB Jan2014-SP1 release. We have 3 database nodes that 2 of them doesn't show this error. Any help much appreciated. We also don't know yet why we have started getting this error, any notes on how one can start getting this error would also be helpful. Mahmut Uludag

2 3

Developing a C-based UDF
by Dennis Pallett 18 Jul '14

18 Jul '14

Hi all, I'm developing a C-based user defined function at the moment (actually porting an existing Postgres extension) and I was wondering what kinds of tools I should use for this and how I should actually do the development. At the moment I'm using a simple text editor to edit the relevant files (i.e. the c and mal files) and then completely re-compile MonetDB to test my changes. I'm not entirely happy about this workflow though, so is there an easier way? Perhaps with Visual Studio (I'm on Windows7) ? How do the core developers work on MonetDB? Also, if I run into any problems or have any questions, is this the right mailing list or should I use the developers mailing list? Best regards, Dennis

3 3

python3-monetdb cursor.scroll questions
by Chris Richards 17 Jul '14

17 Jul '14

Howdy. I've recently started using monetdb and was testing out the python3-monetdb module with the 'quick start' voc dataset. I'm not grokking the purpose of cursor.scroll() if it's working as intended. I am getting weird (to me) results, which I'm including below. Running 11.17.17-20140514 of the server, client, and python module under Ubuntu 14.04 LTS. My example starts at this point in the quick start guide (but in python): sql>SELECT type, COUNT(*) AS total I loaded this snippet into the python3 interpreter: from monetdb import sql def p(c): print('.rowcnt={}, .__offset={}, .rownumber={}'.format ( c.rowcount, c._Cursor__offset, c.rownumber)) print ('.__rows={}'.format (c._Cursor__rows)) db = sql.connect(database='voc', user='voc', password='voc') cursor = db.cursor() # Set the arraysize small so it traverses result sets, # though it doesn't matter for the problem I'm seeing. cursor.arraysize = 2 cursor.execute("SELECT type, COUNT(*) AS total FROM onboard_people GROUP BY type ORDER BY type;") Then proceeded with this interactive session. Consecutive fetchone()'s seem to work properly; it traverses to the second result set. Where I get confused is when I .scroll(): >>> p(cursor) .rowcnt=6, .__offset=0, .rownumber=0 .__rows=[('craftsmen', 2349), ('impotenten', 938)] >>> cursor.fetchone() ('craftsmen', 2349) >>> p(cursor) .rowcnt=6, .__offset=0, .rownumber=1 .__rows=[('craftsmen', 2349), ('impotenten', 938)] >>> cursor.fetchone() ('impotenten', 938) >>> p(cursor) .rowcnt=6, .__offset=0, .rownumber=2 .__rows=[('craftsmen', 2349), ('impotenten', 938)] >>> cursor.fetchone() ('passengers', 2813) >>> p(cursor) .rowcnt=6, .__offset=2, .rownumber=3 .__rows=[('passengers', 2813), ('seafarers', 4468)] # Here's where I scroll back to the beginning. The # offset changes, but the rownumber doesn't. >>> cursor.scroll (0, mode='absolute') >>> p(cursor) .rowcnt=6, .__offset=0, .rownumber=3 .__rows=[('craftsmen', 2349), ('impotenten', 938), ('passengers', 2813), ('seafarers', 4468), ('soldiers', 4177)] # My expectation was that this would be ('craftsmen', 2349), # but it continues as if I didn't scroll. >>> cursor.fetchone() ('seafarers', 4468) # I try to back to the last item with no luck, it instead # marches forward to the next item as if I didn't scroll. >>> cursor.scroll (-1, mode='relative') >>> p(cursor) .rowcnt=6, .__offset=3, .rownumber=4 .__rows=[('seafarers', 4468), ('soldiers', 4177), ('total', 2467)] >>> cursor.fetchone() ('soldiers', 4177) Am I using the .scroll() wrong? Is this a bug? Thanks, Chris

1 0

Large query taking a long time
by Dennis Pallett 16 Jul '14

16 Jul '14

Hi all, I'm executing a very large query with MonetDB and it takes very long before the result is calculated (e.g. several minutes) whereas PostgreSQL does it in less than 100 ms. The query itself is a very simple select + sum query but the WHERE condition has more than a thousand equality tests on the primary key (ckey) of the table. It basically looks like this: SELECT sum(countAggr) FROM sys.london_hav_neogeo_pa WHERE ckey=16947611436 OR ckey=16947611437 OR ckey=16947611438 OR ckey=16947611439 OR ckey=16947611440 OR ckey=16947617908 OR ckey=16947617909 OR ckey=16947617910 OR ckey=16947617911 OR ckey=16947617912 [.. several hundreds of lines removed ..] OR ckey=289411309830 OR ckey=289411309831 OR ckey=289411309832 OR ckey=289411309833 OR ckey=289411309834 OR ckey=289411309835 OR ckey=289411309836 OR ckey=289411309837; Does anyone know why it's taking so long with MonetDB and if there is a way to significantly improve performance? Best regards, Dennis

4 7

TMP space deallocation
by Radovan Bičiště 08 Jul '14

08 Jul '14

Hi, My database started consuming disk space. Here is how: 1. run procedure. 2. procedure fails with server crash due to insufficient disk space. It eats up all the space and crash. 3. restart the database but the disk space remain allocated. All changes by the procedure are rolled back. There are inserts but not that many to eat up 10 GB of space. When I check sys.storage, it does not show any large table. My guess is that it allocates temporary space and it does not deallocate it after database start. It deallocates a bit but not everything. Is there any way how to shrink it manually? It already ate around 40 GB space. The whole database is around 130 GB so the quick dump and restore is not possible. Thank you for any tips, Radovan Biciste

1 0

MonetDB and cpu resources management
by Dimitris Karampinas 03 Jul '14

03 Jul '14

Hi, I'm trying to set the maximum number of cores using gdk_nr_threads parameter but it doesn't seem to work for me. I've tried "variants" of this command: monetdbd set gdk_nr_threads=4 start /my/dbfarm but it seems like gdk_nr_threads is not a supported property. Additionally to this, I would like to ask how does MonetDB handle concurrent user sessions and the available cpu resources ? The manual says there is support for "inter- and intra- parallelism". Can someone roughly explain me what to expect or give me a pointer to the code that is responsible for resource management ? Cheers! Dimitris

6 12

Index usage
by Danila B. Savchuk 27 Jun '14

27 Jun '14

I started using Monetdb two weeks ago. Since this is not RDBMS , what is the general strategy of using and building indexes on tables ?

2 1

Problem at start dbfarm
by Dimitris Karampinas 18 Jun '14

18 Jun '14

Hi, I've built from source MonetDB and I'm following the steps at the tutorial: https://www.monetdb.org/Documentation/UserGuide/Tutorial I've successfully created the dbfarm but I can't proceed beyond the second step. Monetdb returns after ~30 seconds with the following message: $ dbms/bin/monetdbd start data/ another monetdbd is already running I 'm sure no other monetdb instance is running in the system. For the record, get all looks as follows: $ dbms/bin/monetdbd get all data/ property value hostname sgs-r815-02 dbfarm data status a monetdbd is serving this dbfarm, but a pidfile was not found/is corrupt mserver unknown (failed to connect to monetdbd) logfile data/merovingian.log pidfile data/merovingian.pid sockdir /tmp port 50000 exittimeout 60 forward proxy discovery true discoveryttl 600 control no passphrase <unknown> mapisock /tmp/.s.monetdb.50000 controlsock /tmp/.s.merovingian.50000 I had no problems with the whole process when I installed the system on my desktop a few days ago. Any hints ? Dimitris

4 6

Unresponsive mserver
by Pete Hollobon 18 Jun '14

18 Jun '14

Hi, We have recently seen MonetDB become unresponsive several times. Upon restarting, after mserver has been sent the TERM signal, merovingian.log contains lots of "unknown state: 4" entries, repeated many times without linefeeds. We're running Jan2014-SP2 (official binaries) on Ubuntu server 14.04. We had the same database running on Jan2014-SP1 on Ubuntu 12.10 for several months without this issue. I have submitted as a bug ( https://www.monetdb.org/bugzilla/show_bug.cgi?id=3499) Thanks, Pete.

1 0

odbc driver compliance: NULL strings or "" in SQLTables and SQLColumns API calls
by László Sándor 18 Jun '14

18 Jun '14

Stata fixed how they will talk to the MonetDB driver in the future, but they say the MonetDB driver is less standard, so perhaps we should be aware of this: We were passing a NULL string to the ODBC > api call SQLTables and SQLColumns where the MonetDB driver wants an empty > string > (""). Some other drivers need a NULL string passed in to work (Oracle > being > one) so I believe this is a driver bug, but I have patched the problem in > Stata. > I have added code in -odbc- to check for the MonetDB driver and pass an > empty > string in that case.

2 1

How do I monitor or speed up insert from aggregation query?
by Joe Wass 17 Jun '14

17 Jun '14

I'm running the following query over a database of 1,000,000,000 items. insert into date_aggregate (count, a, date) select count(date) as count, a, date, from input group by date, a order by date; I expect it to take a certain length of time, but I have no way of knowing how long. The system queue table has a 'null' estimate. All I know from that table is that the query is running. Is there a way to get any more information about its progress? I would be happy to just wait, but I don't know if I should be waiting for hours or days or weeks. The monetdbd process is only using 14% CPU (on a 6 core machine) and 14% RAM (the machine has 7GB and lots of SSD swap). My machine is basically idling when I want it to run as fast as possible. Running Jan2014-SP2, 64-bit on Ubuntu 14.04. Any hints about measuring progress and improving resource utilisation? Joe

2 1

MonetDB just reads and reads when restoring a dump
by Bryan Senseman 16 Jun '14

16 Jun '14

All, I know I have asked this before, but I am still plagued by this error. I am restoring a 50GB database from Linux to Windows, the dump file created via mclient is ~53GB. I am reading that file from an SSD and loading the database onto another SSD. This has been running for 2 DAYS! Using almost all available memory 16GB. Using Windows Resoure Monitor, the mserver5.exe process is just reading tail files for hours. The pattern is that the disk usage will grow by some amount say 5gb (I’m assuming the size of the table actually being loaded) and then it will spend 6-12 hours reading what it just wrote. Why? Can this be eliminated? I removed all indexes on the fact tables so it isn’t indexing (although that runs almost instantly anyway). Only about 10% cpu utilization. Thanx, Bryan -- ------------------------------ This transmission is confidential and intended solely for the use of the recipient named above. It may contain confidential, proprietary, or legally privileged information. If you are not the intended recipient, you are hereby notified that any unauthorized review, use, disclosure or distribution is strictly prohibited. If you have received this transmission in error, please contact the sender by reply e-mail and delete the original transmission and all copies from your system.

2 1

"LIMIT + ORDER BY" TypeException
by George Ramonov 15 Jun '14

15 Jun '14

Hi, I am still learning about monetdb internals, so I hope someone can enlighten me here. I was playing around with monetdb, and run into the following problem: Suppose we have a table CREATE TABLE test.simple_table("id" INTEGER); INSERT INTO test.simple_table VALUES (1),(2),(3),(4),(5); Let's say I want to run a simple query: SELECT COUNT(*) AS val FROM test.simple_table ORDER BY val DESC; +----+ | v | +==+ | 5 | +----+ Runs fine. However, if add a *LIMIT* clause: SELECT COUNT(*) AS val FROM test.simple_table ORDER BY val DESC LIMIT 5; I get the following TypeException:user.s37_7[17]:'algebra.markT' undefined in: _30:any := algebra.markT(_27:oid, _29:oid) program contains errors Now things let's add a primitive *HAVING* clause SELECT COUNT(*) AS val FROM test.simple_table HAVING 1 > 0 ORDER BY val DESC LIMIT 5; +----+ | v | +==+ | 5 | +----+ And things are magically fine again. Can someone explain what's going on? Thanks, George Ramonov UC Berkeley B.S. Electrical Engineering & Computer Science '13 <http://www.linkedin.com/in/georgeramonov>

2 2

odbc driver 11.17.17 (64-bit) memory allocation error on Windows Server 2012
by László Sándor 10 Jun '14

10 Jun '14

Upon some further reflection, the Stata error message still suggest the error happened within MonetDB's ODBC driver. Anybody else using MonetDB with ODBC on Windows? What shall I check? Thanks! Laszlo

1 2

Re: Getting "BATproject: does not match always"
by Joe Wass 10 Jun '14

10 Jun '14

Update to my previous message. Looks like something went wrong with an mmap. 2014-06-09 17:10:58 ERR my_database[32949]: = gdk_posix.c:560: MT_mremap(bat/11/1150.theap,c0000000,538312704,1076625408): p == MAP_FAILED Looks like the server ran out of memory but just kept going with a corrupted table? Should this have been allowed to happen or should the process have crashed? It looks like this bug is related, although it has a different cause: https://www.monetdb.org/bugzilla/show_bug.cgi?id=3450 Joe

1 0

Getting "BATproject: does not match always"
by Joe Wass 10 Jun '14

10 Jun '14

My schema is: create table my_table (col_a date, col_b varchar(1024), col_c varchar(128), col_d varchar(128), col_e varchar(128)); I have approx 5,000,000 entries. The following queries not work: select count(col_b) from my_table; select distinct(col_b) from my_table; select 1 from my_table group by col_b; Giving the error: GDK reported error. BATproject: does not match always They work fine on all other fields. I don't know precisely what triggers this. I've tried to create some small sample values but I can only reproduce this on my large data set. I'm actually trying to do more interesting aggregation, but this is a minimal way to trigger the error. I'm using Jan2014-SP2 on Mac OS X 10.9.2 . Thanks Joe

1 0

Re: MonetDB problem with mserver.exe
by Hannes Mühleisen 10 Jun '14

10 Jun '14

On 06/10/2014 09:04 AM, Kaspar Burger wrote: > You are right, Anthony, when I run the file independent of R, > mserver.exe shuts down immediately. Could you please share your test.bat file? > > *De :*users-list > [mailto:users-list-bounces+kaspar.burger=iukb.ch@monetdb.org] *De la > part de* Anthony Damico > *Envoyé :* mardi 10 juin 2014 08:51 > *À :* Communication channel for MonetDB users > *Objet :* Re: MonetDB problem with mserver.exe > > kaspar has something changed? when you and i screenshared to try to > resolve this issue, mserver.exe was shutting down immediately before R > ever came into the picture? > > what happens when you run this file independent of R? > > > C:\Users\Kaspar Burger\MonetDB\test.bat > > > > On Tue, Jun 10, 2014 at 2:42 AM, Hannes Mühleisen > <Hannes.Muehleisen(a)cwi.nl <mailto:Hannes.Muehleisen@cwi.nl>> wrote: > > This seems to fall into my backyard... > > > On 10.06.2014, at 08:32, Kaspar Burger <kaspar.burger(a)iukb.ch > <mailto:kaspar.burger@iukb.ch>> wrote: > > I am working in R, configuring a MonetDB test database and trying to > > access MonetDB with the following code: > > > > batfile <- "C:/Users/Kaspar Burger/MonetDB/test.bat" > > pid <- monetdb.server.start( batfile ) > > dbname <- "test" > > dbport <- 50000 > > monet.url <- paste0( "monetdb://localhost:" , dbport , "/" , dbname ) > > db <- dbConnect( MonetDB.R() , monet.url , wait = TRUE ) > > > > When I run the “pid <- monetdb.server.start( batfile )” line, the > > mserver5.exe opens briefly but shuts down immediately afterwards (for a > > more extensive description of the R code, see: > > > https://raw.githubusercontent.com/ajdamico/usgsd/master/MonetDB/monetdb%20d…) > > By contrast, running the batch file from C:\Program > > Files\MonetDB\MonetDB5\M5server.bat or from the Start menu works fine. I > > tried when both drivers were installed as well as when only one > driver was > > installed. Could that problem be related to R or to my code? > > Would not be surprised if the space in the batfile path causes the > problem. Try setting up the DB into another directory without a space in > it and try again. > > Best, > > Hannes > > > > > > > > >> > >> > >> On 2014-06-10 00:06, Stefan Manegold wrote: > >>>> On 09/06/14 16:28, Kaspar Burger wrote: > >>>>> > >>>>> Hello, > >>>>> > >>>>> I seem to have difficulties with mserver.exe. I would like to use > >>>>> MonetDB > >>>>> on a Windows 64 bit computer, so... > >>>>> > >>>>> 1) ...I went to > >>>>> https://www.monetdb.org/downloads/Windows/Jan2014-SP2/ > >>>>> > >>>> Most likely, you need only 1 ODBC driver. > >>>> Otherwise causing a clash. > >>> > >>> For the records, as by our own documentation, on a 64-bit Windows > >>> system, > >>> both the 32-bit and the 64-bit ODBC driver *must* be installed. > >>> (As far as I understand, this is required to support both 32-bit and > >>> 64-bit > >>> applications, or simply because Windows requires it.) > >> > >> You need both in order to support both 32 and 64 bit applications. > >> That's the only reason to install both. > >> > >>> Stefan > >>> > >>>>> 2) ...and first installed the MonetDB ODBC driver, 32 bit (to > >>>>> C:\Program > >>>>> Files (x86)\MonetDB\MonetDB ODBC Driver\) > >>>>> > >>>>> 3) ...then I installed the MonetDB ODBC driver, 64 bit (to C:\Program > >>>>> Files\MonetDB\MonetDB ODBC Driver\) > >>>>> > >>>>> 4) ...and finally I installed the MonetDB SQL server and client, 64 > >>>>> bit > >>>>> (to C:\Program Files\MonetDB\MonetDB5\). > >>>>> > >>>>> When I open up mserver.exe, it shuts down immediately. Does anyone > >>>>> know > >>>>> what could have gone wrong? > >> > >> Did you start the server using the start menu? That actually runs a cmd > >> script to set a bunch of environment variables before starting the > >> actual server mserver5.exe. > >> > >>>>> I would appreciate any hint. Thanks in advance. > >>>>> > >>>>> Kaspar Burger > >>>>> > >>>>> > >>>>> _______________________________________________ > >>>>> users-list mailing list > >>>>> users-list(a)monetdb.org <mailto:users-list@monetdb.org> > >>>>> https://www.monetdb.org/mailman/listinfo/users-list > >>>>> > >>>> > >>>> _______________________________________________ > >>>> users-list mailing list > >>>> users-list(a)monetdb.org <mailto:users-list@monetdb.org> > >>>> https://www.monetdb.org/mailman/listinfo/users-list > >>>> > >>> > >> > >> -- > >> Sjoerd Mullender > >> > >> _______________________________________________ > >> users-list mailing list > >> users-list(a)monetdb.org <mailto:users-list@monetdb.org> > >> https://www.monetdb.org/mailman/listinfo/users-list > >> > > > > > > _______________________________________________ > > users-list mailing list > > users-list(a)monetdb.org <mailto:users-list@monetdb.org> > > https://www.monetdb.org/mailman/listinfo/users-list > > > _______________________________________________ > users-list mailing list > users-list(a)monetdb.org <mailto:users-list@monetdb.org> > https://www.monetdb.org/mailman/listinfo/users-list > > > > _______________________________________________ > users-list mailing list > users-list(a)monetdb.org > https://www.monetdb.org/mailman/listinfo/users-list >

3 5

MonetDB problem with mserver.exe
by Kaspar Burger 10 Jun '14

10 Jun '14

Hello, I seem to have difficulties with mserver.exe. I would like to use MonetDB on a Windows 64 bit computer, so... 1) ...I went to https://www.monetdb.org/downloads/Windows/Jan2014-SP2/ 2) ...and first installed the MonetDB ODBC driver, 32 bit (to C:\Program Files (x86)\MonetDB\MonetDB ODBC Driver\) 3) ...then I installed the MonetDB ODBC driver, 64 bit (to C:\Program Files\MonetDB\MonetDB ODBC Driver\) 4) ...and finally I installed the MonetDB SQL server and client, 64 bit (to C:\Program Files\MonetDB\MonetDB5\). When I open up mserver.exe, it shuts down immediately. Does anyone know what could have gone wrong? I would appreciate any hint. Thanks in advance. Kaspar Burger

7 8

Re: Could monetdb have a capacity to surpport our case
by Hannes Mühleisen 09 Jun '14

09 Jun '14

Hello, On 06.06.2014, at 12:06, liuguodong <liuguodong(a)jd.com> wrote: > Our case as following: > > 1. There are several big tables(more than billion rows per table, more than 1 TB) ; > 2. Many dimentions in every table( more than 150 dimentions per table), they random do join operation; > 3. Low latency reponse, it is expected to reponse time being less than 1 second. > > Could monetdb have a capacity to surpport our case or any suggestion? This depends first on your queries and second on your hardware. In general, the columnar architecture of MonetDB would be a good match for what you describe (lots of dimensions, random join combinations). I would suggest to simply try your queries with a subset of data on a scaled-down machine (e.g. 100 GB of your data on a 16GB RAM box) and see how it goes. Best, Hannes

1 0

Re: Re: Re: tomograph does not work
by Cheng Xuntao 07 Jun '14

07 Jun '14

Thanks Martin. Xuntao

1 0

MAL scheduling and stethoscope metrics
by Cheng Xuntao 07 Jun '14

07 Jun '14

Hi all, I am new to monetdb. I want to know how Monetdb schedule its MAL statements to threads, or to which CPU socket in a multi-cpu environment. I didn't find any document talking bout this directly. Shall I start from reading the source code? Which part of the source code shall I start with? Any suggestion will do me a great favour! I also have some confusions about the metrics of stethoscope. So I asked these 2 question in stackoverflow. http://stackoverflow.com/questions/24077439/what-does-the-metrics-c-m-r-w-a… http://stackoverflow.com/questions/24076688/how-does-monetdb-schedule-its-m… Any suggestion? Thanks very much! Xuntao

1 0

odbc (64-bit) problems in Windows?
by László Sándor 06 Jun '14

06 Jun '14

Hi, I reinstalled MonetDB as well as the ODBC driver after last week's update to SP2. How could I troubleshoot my system if I get a Memory allocation error SQLSTATE=HY001 if Stata would query the database? Otherwise I do see the data source listed, both in Stata (with -odbc query-) and Microsoft ODBC Data Source Administrator. (The latter lists the database under System DSN with the right name, platform and driver.) Thanks, Laszlo

1 1

Text type vs JDBC driver
by Joe Wass 06 Jun '14

06 Jun '14

I just started looking at MonetDB today, so please excuse me if I've missed something. I'm using the JDBC driver in Clojure. I have a table which has one column of type TEXT. The docs mention TEXT [0] and I have no problem with it from the database CLI. When I'm inserting with JDBC, I get an error message : "Conversion not allowed", pointing to MonetPreparedStatement.java line 1775. Looking at the switch statement it doesn't look like the driver has a mapping of String to TEXT (and possibly others). I'm aware that using Clojure might introduce issues, but I've checked and the type of objects I'm passing in are definitely java.lang.Strings. I don't want to raise a ticket because I'm not confident that is is a bug. But it does look like a mismatch between the MonetDB types and the JDBC driver. A quick look at the bug tracker doesn't mention anything about 'text'. (FWIW I've got my code working by using VARCHAR.) Joe [0] https://www.monetdb.org/Documentation/Manuals/SQLreference/BuiltinTypes [1] http://dev.monetdb.org/hg/MonetDB/file/tip/java/src/nl/cwi/monetdb/jdbc/Mon…

1 0

Re: Re: tomograph does not work
by Cheng Xuntao 06 Jun '14

06 Jun '14

Hi Martin, My Monetdb version is MonetDB Database Server v1.7 (Jan2014-SP2). I'm using Ubuntu 12.04 LTS Precise. My Stethoscope works perfectly displaying valuable information. However, I have some confusion about the metrics. Why some MAL statement has non-zero 'blk.reads' and 'blk.writes' but their rbytes and wbytes are zero? What exactly does these memory related metrics mean? Thanks! Xuntao On 06/06/2014 06:00 PM, users-list-request(a)monetdb.org wrote: > Send users-list mailing list submissions to > users-list(a)monetdb.org > > To subscribe or unsubscribe via the World Wide Web, visit > https://www.monetdb.org/mailman/listinfo/users-list > or, via email, send a message with subject or body 'help' to > users-list-request(a)monetdb.org > > You can reach the person managing the list at > users-list-owner(a)monetdb.org > > When replying, please edit your Subject line so it is more specific > than "Re: Contents of users-list digest..." > > > Today's Topics: > > 1. tomograph does not work (Cheng Xuntao) > 2. Re: tomograph does not work (Martin Kersten) > > > ---------------------------------------------------------------------- > > Message: 1 > Date: Fri, 06 Jun 2014 11:18:29 +0800 > From: Cheng Xuntao <chengxuntao(a)gmail.com> > To: users-list(a)monetdb.org > Subject: tomograph does not work > Message-ID: <53913305.5090803(a)gmail.com> > Content-Type: text/plain; charset=ISO-8859-1; format=flowed > > Hi all, > > I need to have a visual view of the execution detail of each thread in > monetdb so I wish to use Tomograph. I followed this > https://www.monetdb.org/Documentation/Manuals/MonetDB/Profiler/tomograph > and have installed all required pacakges. But Tomograph does not respond > when I finished my queries. All its outputs are empty. However, my > Stethoscope works perfectly. > > Is there anything not listed in the Tomograph documentation that needs > to be done to get it work? > > Thanks! > > Xuntao > > > ------------------------------ > > Message: 2 > Date: Fri, 06 Jun 2014 08:26:00 +0200 > From: Martin Kersten <Martin.Kersten(a)cwi.nl> > To: users-list(a)monetdb.org > Subject: Re: tomograph does not work > Message-ID: <53915EF8.5030202(a)cwi.nl> > Content-Type: text/plain; charset=ISO-8859-1; format=flowed > > Hi > > Could you indicate which platform and MonetDB version you are using. > > regards, Martin > > > On 06/06/14 05:18, Cheng Xuntao wrote: >> Hi all, >> >> I need to have a visual view of the execution detail of each thread in >> monetdb so I wish to use Tomograph. I followed this >> https://www.monetdb.org/Documentation/Manuals/MonetDB/Profiler/tomograph >> and have installed all required pacakges. But Tomograph does not respond >> when I finished my queries. All its outputs are empty. However, my >> Stethoscope works perfectly. >> >> Is there anything not listed in the Tomograph documentation that needs >> to be done to get it work? >> >> Thanks! >> >> Xuntao >> _______________________________________________ >> users-list mailing list >> users-list(a)monetdb.org >> https://www.monetdb.org/mailman/listinfo/users-list > > ------------------------------ > > Subject: Digest Footer > > _______________________________________________ > users-list mailing list > users-list(a)monetdb.org > https://www.monetdb.org/mailman/listinfo/users-list > > > ------------------------------ > > End of users-list Digest, Vol 22, Issue 5 > *****************************************

2 1

tomograph does not work
by Cheng Xuntao 06 Jun '14

06 Jun '14

Hi all, I need to have a visual view of the execution detail of each thread in monetdb so I wish to use Tomograph. I followed this https://www.monetdb.org/Documentation/Manuals/MonetDB/Profiler/tomograph and have installed all required pacakges. But Tomograph does not respond when I finished my queries. All its outputs are empty. However, my Stethoscope works perfectly. Is there anything not listed in the Tomograph documentation that needs to be done to get it work? Thanks! Xuntao

2 1

Announcement: New Jan2014-SP2 Bugfix release of MonetDB suite
by Sjoerd Mullender 02 Jun '14

02 Jun '14

The MonetDB team at CWI/MonetDB BV is pleased to announce the Jan2014-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/>. Jan 2014-SP2 bugfix release Build Environment * Lots of minor fixes were made for potential defects found by Coverity Scan. * On Windows we now build the geom module against version 3.4.2 of the geos library. Client Package * ODBC: Implemented {call procedure-name(...)} escape. The version {?=call ...} is not implemented. Bug Fixes * 3424: numeric values at the front of strings determines whether CAST works successfully * 3439: Python driver drops milliseconds from timestamps * 3446: SET READ ONLY forgets previous changes * 3455: String columns unusable from 64-bit .NET via ODBC * 3456: Insert fails * 3457: When kernel of remote client crashes, the connection remains established on server side * 3458: mserver5 crash on SQL: SELECT COUNT(*) FROM SYS.TABLES HAVING COUNT(*) > 0 * 3461: mserver5 crash on SQL: SELECT * FROM SYS.ARGS WHERE FUNC_ID NOT IN (SELECT ID FROM SYS.FUNCTIONS) OR FUNC_ID NOT IN (SELECT * FROM SYS.FUNCTIONS) * 3462: Invalid SQL (IN with subquery which returns multiple columns) is accepted * 3463: Crash on SELECT with SERIAL aggregation and GROUP BY column alias's * 3468: Local temporary table persists across sessions * 3469: Absolute network paths considered invalid for COPY INTO ... FROM statement. * 3473: Various memory leaks in SQL compilation * 3477: ODBC driver raises "unexpected end of input" for prepared string parameter from .NET application * 3481: Cannot run multiple COPY INTO statements in one 's'-command * 3482: Crossproduct error

3 9

how to count the number of query results in monetdb
by Bing Zhang 29 May '14

29 May '14

Hi: Is there a fast way to get the number of results, instead of looping on the ResultSet.next()? Thanks.

1 0

Processing web logs in monetdb
by Christopher Nelson 27 May '14

27 May '14

Hello, I've been looking at monetdb for a while, and I've been very impressed with the project. I would like to use monetdb to analyze web log data from a very active site. I have no doubt that it can handle the quantity of data, but I'm not sure how to perform some of the queries that I have used in other systems. For example, I would like in particular to be able to perform click-stream analysis of the data. On other systems I've used proprietary extensions which allowed me to create a window by pattern matching a certain column. Do you have any suggestions about where I should look to perform similar kinds of queries? Thanks! -={C}=-

2 1

question: efficient use of partitioned tables
by László Sándor 26 May '14

26 May '14

Hi, This might be less of MonetDB-specific question about features than about RDBMS (or just SQL) in general, but I would still appreciate your advice in these matters. I have data with a few "cross-sectional" tables (e.g. the parents of an individual) and many longitudinal/panel tables in annual chunks (e.g. tax records for each year separately). I wonder if it is a good idea to merge/union the annual tables into long panels. The benefits seems to be a much cleaner structure for the consistent columns, correspondingly easier code to write, fewer loops, tables names, lines of code. Also, fast joins on individual IDs and years across the tables without intermediary steps. However, the downsides are that not all columns are consistent across years, and there are simply some that appear only in certain years. Storing missing values for other years for these variables is a waste of disk space and memory. The merged tables could be too big anyway — if it matters in MonetDB (think of tables ~70 GB in some cases, while my server has 128 GB RAM), while many use cases would focus on a subset of years anyway. Do you have any thoughts about this? Maybe more concrete questions would be: — How cumbersome would it be (for a novice!) to code joining data from the annual tables separately, for multiple series of tables? (E.g. union all tax returns for 1997-2008, then join all residential data for these 12 years year-by-year, then employment records year-by-year etc.) — How much slower would this *run* than simply selecting years from the longitudinal tables? — Shall I be worried about the longitudinal tables having the size on the same order of magnitude as my physical memory, or MonetDB handles it equally efficiently (it would need to access many of the small annual tables anyway, that also takes memory). Thanks, Laszlo

1 0

Strange query performance
by N.H. Schuiling 24 May '14

24 May '14

Hi all, Can someone explain me why this simple query takes so long to execute on MonetDB 5 server v11.17.13 "Jan2014-SP1"? sql>select * from fp_tree limit 1; +-----------------------------+ | path | +=============================+ | ;8;60;379;519;1241;174;692; | +-----------------------------+ 1 tuple (1.2s) Table fp_tree has one column path with data type string. Some useful statistics of table fp_tree: sql>select count(*) from fp_tree; +--------+ | L1 | +========+ | 416499 | +--------+ 1 tuple (2.801ms) sql>select max(length(path)) from fp_tree; +------+ | L1 | +======+ | 1275 | +------+ 1 tuple (254.742ms) sql>select min(length(path)) from fp_tree; +------+ | L1 | +======+ | 3 | +------+ 1 tuple (272.186ms) sql>select avg(length(path)) from fp_tree; +--------------------------+ | L1 | +==========================+ | 202.73579288305606 | +--------------------------+ 1 tuple (331.306ms) Query trace: +---------+-------------------------------------------------------------------+ | ticks | stmt | +=========+===================================================================+ | 3 | X_2 := sql.mvc(); | | 29 | X_35:bat[:oid,:oid] =<tmp_440>[104124] := sql.tid(X_2=0,"sys","fp | : : _tree",0,4); : | 6 | X_8=<tmp_3501>[0] := sql.bind(X_2=0,"sys","fp_tree","path",1); | | 40 | (X_52:bat[:oid,:oid] =<tmp_2110>[0],X_53:bat[:oid,:str] =<tmp_350 | : : 1>[0]) := sql.bind(X_2=0,"sys","fp_tree","path",2,3,4); : | 8 | X_45:bat[:oid,:str] =<tmp_1357>[104127] := sql.bind(X_2=0,"sys"," | : : fp_tree","path",0,3,4); : | 3 | X_57=<tmp_1357>[104127] := sql.delta(X_45=<tmp_1357>:bat[:oid,:st | : : r][104127],X_52=<tmp_2110>:bat[:oid,:oid][0],X_53=<tmp_3501>:bat[ : : : :oid,:str][0],X_8=<tmp_3501>[0]); : | 4 | X_41:bat[:oid,:oid] =<tmp_1256>[104127] := sql.tid(X_2=0,"sys","f | : : p_tree",3,4); : | 8 | X_61=<tmp_302>[104127] := algebra.leftfetchjoin(X_41=<tmp_1256>:b | : : at[:oid,:oid][104127],X_57=<tmp_1357>[104127]); : | 10 | X_65=<tmpr_1256>[1] := algebra.subslice(X_61=<tmp_302>[104127],0: | : : wrd,0:wrd); : | 6 | X_71=<tmp_1357>[1] := algebra.leftfetchjoin(X_65=<tmpr_1256>[1],X | : : _61=<tmp_302>[104127]); : | 1 | language.pass(X_61=<tmp_302>[104127]); | | 24 | (X_50:bat[:oid,:oid] =<tmp_2110>[0],X_51:bat[:oid,:str] =<tmp_350 | : : 1>[0]) := sql.bind(X_2=0,"sys","fp_tree","path",2,2,4); : | 5 | X_44:bat[:oid,:str] =<tmp_1560>[104124] := sql.bind(X_2=0,"sys"," | : : fp_tree","path",0,2,4); : | 3 | X_56=<tmp_1560>[104124] := sql.delta(X_44=<tmp_1560>:bat[:oid,:st | : : r][104124],X_50=<tmp_2110>:bat[:oid,:oid][0],X_51=<tmp_3501>:bat[ : : : :oid,:str][0]); : | 3 | X_39:bat[:oid,:oid] =<tmp_1760>[104124] := sql.tid(X_2=0,"sys","f | : : p_tree",2,4); : | 5 | X_60=<tmp_302>[104124] := algebra.leftfetchjoin(X_39=<tmp_1760>:b | : : at[:oid,:oid][104124],X_56=<tmp_1560>[104124]); : | 8 | X_64=<tmpr_1760>[1] := algebra.subslice(X_60=<tmp_302>[104124],0: | : : wrd,0:wrd); : | 4 | X_70=<tmp_1560>[1] := algebra.leftfetchjoin(X_64=<tmpr_1760>[1],X | : : _60=<tmp_302>[104124]); : | 1 | language.pass(X_60=<tmp_302>[104124]); | | 21 | (X_48:bat[:oid,:oid] =<tmp_2110>[0],X_49:bat[:oid,:str] =<tmp_350 | : : 1>[0]) := sql.bind(X_2=0,"sys","fp_tree","path",2,1,4); : | 5 | X_43:bat[:oid,:str] =<tmp_1660>[104124] := sql.bind(X_2=0,"sys"," | : : fp_tree","path",0,1,4); : | 1 | X_55=<tmp_1660>[104124] := sql.delta(X_43=<tmp_1660>:bat[:oid,:st | : : r][104124],X_48=<tmp_2110>:bat[:oid,:oid][0],X_49=<tmp_3501>:bat[ : : : :oid,:str][0]); : | 3 | X_37:bat[:oid,:oid] =<tmp_1054>[104124] := sql.tid(X_2=0,"sys","f | : : p_tree",1,4); : | 5 | X_59=<tmp_302>[104124] := algebra.leftfetchjoin(X_37=<tmp_1054>:b | : : at[:oid,:oid][104124],X_55=<tmp_1660>[104124]); : | 8 | X_63=<tmpr_1054>[1] := algebra.subslice(X_59=<tmp_302>[104124],0: | : : wrd,0:wrd); : | 5 | X_69=<tmp_1660>[1] := algebra.leftfetchjoin(X_63=<tmpr_1054>[1],X | : : _59=<tmp_302>[104124]); : | 1 | language.pass(X_59=<tmp_302>[104124]); | | 23 | (X_46:bat[:oid,:oid] =<tmp_2110>[0],X_47:bat[:oid,:str] =<tmp_350 | : : 1>[0]) := sql.bind(X_2=0,"sys","fp_tree","path",2,0,4); : | 9 | X_42:bat[:oid,:str] =<tmp_462>[104124] := sql.bind(X_2=0,"sys","f | : : p_tree","path",0,0,4); : | 2 | X_54=<tmp_462>[104124] := sql.delta(X_42=<tmp_462>:bat[:oid,:str] | : : [104124],X_46=<tmp_2110>:bat[:oid,:oid][0],X_47=<tmp_3501>:bat[:o : : : id,:str][0]); : | 6 | X_58=<tmp_764>[104124] := algebra.leftfetchjoin(X_35=<tmp_440>:ba | : : t[:oid,:oid][104124],X_54=<tmp_462>[104124]); : | 8 | X_62=<tmpr_440>[1] := algebra.subslice(X_58=<tmp_764>[104124],0:w | : : rd,0:wrd); : | 10 | X_72=<tmp_462>[1] := mat.packIncrement(X_62=<tmpr_440>[1],4); | | 3 | X_74=<tmp_462>[2] := mat.packIncrement(X_72=<tmp_462>[2],X_63=<tm | : : pr_1054>[1]); : | 1 | language.pass(X_63=<tmpr_1054>[1]); | | 2 | X_75=<tmp_462>[3] := mat.packIncrement(X_74=<tmp_462>[3],X_64=<tm | : : pr_1760>[1]); : | 1 | language.pass(X_64=<tmpr_1760>[1]); | | 3 | X_66=<tmp_462>[4] := mat.packIncrement(X_75=<tmp_462>[4],X_65=<tm | : : pr_1256>[1]); : | 1 | language.pass(X_65=<tmpr_1256>[1]); | | 9 | X_11=<tmpr_1206>[1] := algebra.subslice(X_66=<tmp_462>[4],0:wrd,0 | : : :wrd); : | 5 | X_68=<tmp_1760>[1] := algebra.leftfetchjoin(X_62=<tmpr_440>[1],X_ | : : 58=<tmp_764>[104124]); : | 1 | language.pass(X_62=<tmpr_440>[1]); | | 6 | language.pass(X_58=<tmp_764>[104124]); | | 1019824 | X_77=<tmp_1256>[1] := mat.packIncrement(X_68=<tmp_1760>[1],4); | | 8 | X_78=<tmp_1256>[2] := mat.packIncrement(X_77=<tmp_1256>[2],X_69=< | : : tmp_1660>[1]); : | 7 | X_79=<tmp_1256>[3] := mat.packIncrement(X_78=<tmp_1256>[3],X_70=< | : : tmp_1560>[1]); : | 18 | X_67=<tmp_1256>[4] := mat.packIncrement(X_79=<tmp_1256>[4],X_71=< | : : tmp_1357>[1]); : | 18 | X_12=<tmp_1357>[1] := algebra.leftfetchjoin(X_11=<tmpr_1206>[1],X | : : _67=<tmp_1256>[4]); : | 1095479 | barrier X_89 := language.dataflow(); | | 13 | X_13 := sql.resultSet(1,1,X_12=<tmp_1357>[1]); | | 7 | sql.rsColumn(X_13=7,"sys.fp_tree","path","clob",0,0,X_12=<tmp_135 | : : 7>[1]); : | 2 | X_17 := io.stdout(); | | 33 | sql.exportResult(X_17=="104d2":streams,X_13=7); | | 0 | end s2_2; | | 1095725 | function user.s2_2(); | | 1095772 | X_5:void := user.s2_2(); | +---------+-------------------------------------------------------------------+ 56 tuples (1.1s) Why does mat.packIncrement takes so much time? Thanks in advance, Nik

1 0

How to use SciQL on MonetDB
by Yingwei Yu 23 May '14

23 May '14

Hello, I am new to MonetDB, and I am very interested in the SciQL functionality. I downloaded and installed the windows build of MonetDB. When I tried some SciQL on the client, it seems that it does not work. I followed the commands in paper Kersten et. al (2011) as follows: sql>start transaction; auto commit mode: off sql>CREATE ARRAY A1 ( more> x INTEGER DIMENSION[4], more> v FLOAT DEFAULT 0.0); syntax error, unexpected IDENT, expecting INDEX in: "create array" Do I miss something important? Please advise. Thanks, yingwei

2 1

ODBC driver cannot be removed (or repaired, upgraded), Jan2014-SP2 .msi files
by László Sándor 22 May '14

22 May '14

Hi, With today's Jan2014-SP2 update, I wanted to uninstall and reinstall using the .msi files in Windows Server 2012. (I also wanted to change the install locations, as I have no access to the default location on C to change the bat files and edit where my dbfarm should be.) Maybe because I ran MonetDB5-SQL-Installer-x86_64-20140514.msi before MonetDB-ODBC-Installer-x86_64-20140514.msi, but now the ODBC remove fails, repairing also. A desperate deletion of the entire C:/Program Files/MonetDB, did not make things better, maybe it made worse. Could you let me know how I could proceed with uninstalling the ODBC driver? I do want to have a clean, stable reinstall (under the new location) which I can trust. Thanks! Laszlo

1 2

ascii encoding error in block input (COPY INTO)
by László Sándor 21 May '14

21 May '14

Hi, I found previous notes<https://www.monetdb.org/pipermail/users-list/2010-November/004529.html>here that the server would expect UTF-8 input from the client or COPY INTO, so I need to work on my my Western ISO Latin 1 files. However, the error message is confusing about, COPY INTO complains about "'ascii' codec can't decode byte 0xc4 in position 8: ordinal not in range(128)." Shall I convert the data to UTF-8 then, or is that futile? What has the ascii codec to do with UTF-8? Thanks, Laszlo

1 0

COPY INTO on Windows
by László Sándor 21 May '14

21 May '14

Hi, I investigated further, and actually I cannot get any CSV file working with monetDB's COPY INTO on Windows. Hannes filed a bug report today: https://www.monetdb.org/bugzilla/show_bug.cgi?id=3484 However, I tried other things since his/our attempts, e.g. removing the final end-of-line (EOL, still CRLF on Windows) or switching EOL to LF only for the temporary file (and changing the COPY INTO call's delimiters too, of course). Neither helped. Still the same error: Incomplete record at end of file. (Which I am not sure whether it literally means the end of the source textfile or simply the last line before breaking.) Basically, the question is, is there a way to get this working on Windows in Jan14-SP1? Or could someone please verify it's worth downgrading to a previous because it definitely can do COPY INTO? Thanks! Laszlo

2 3

vacuum safe to use
by Bryan Senseman 20 May '14

20 May '14

All, Is the sys.vacuum call safe, anybody using it? I notice it is marked experimental. We have times when an entire data set is reprocessed and reloaded forcing a delete to occur, we have added this call after the delete and it seems to be working, however we still get a frequent number of database crashes. Thanx, Bryan -- ------------------------------ This transmission is confidential and intended solely for the use of the recipient named above. It may contain confidential, proprietary, or legally privileged information. If you are not the intended recipient, you are hereby notified that any unauthorized review, use, disclosure or distribution is strictly prohibited. If you have received this transmission in error, please contact the sender by reply e-mail and delete the original transmission and all copies from your system.

1 0

COPY INTO robust to empty row at end of CSV
by László Sándor 19 May '14

19 May '14

Hi all, Hannes Mühleisen (cc'd) generously shared his homepages.cwi.nl/~hannes/importer.py Python script with me last week, which automates the creation of tables from text files: Python's csv-reader helps recognize the dialect of the text file as well as grab the header for column names and identify the types for the columns, and then a single COPY %i OFFSET %i RECORDS INTO %s FROM \'%s\' USING DELIMITERS … is issued to MonetDB (see line 404 of the script). I had some issues with this over the last three days, but now I see the source of the error I get: There is nothing wrong with Hannes's Python code but when COPY INTO turns back to the text file, MonetDB still complains about the last row containing an "incomplete record" (it is empty) even if Python was smart enough not to count that row into the number of records beforehand. Is there a way to call COPY INTO that is robust to the text file ending in an extra empty row? It is common with some generated CSV or TSV files. By the way, I also have a follow-up about how to merge some of the tables together. I am happy to work on this, of course, I don't expect anyone to script this for me, but I am confused about how much I can use SQL variables for this (and how) versus how much I need to use another language to script the repeated calls to MonetDB about each year for each series of tables (I have dozens of dozens). I hope this is a reasonably well-posed question: http://dba.stackexchange.com/questions/65329/dynamic-sql-merge-partitioned-… Thanks for any help, Laszlo

2 7

type recognition, *creating* tables from CSVs
by László Sándor 15 May '14

15 May '14

Hi, I asked a question on DBA of StackExchange about MonetDB, but as there is not even a monetdb tag in use, maybe it wasn't the right forum for it. Please have a look over there, or I can repeat it below: http://dba.stackexchange.com/q/65126/38399 I am confused whether you can copy records into a *new* table in MonetDB. The official documentation seems[1] to talk only about copying into existing tables. I have quite a large universe of billions of records in dozens of tables, and I would much prefer a solution that inferred the type from the CSV itself, and the column names from the first rows. A StackOverflow answer[2] seems to work for SQL Server 2000 with `OpenRowset`, though even they are not concerned about data-type inferencing. Another answer[3] here on DBA recommends `BCP` before calling `BULK INSERT` in Server 2000, neither of which is discussed in the MonetDB documentation. Other answers[4] also suggest that this is impossible in MySQL without external scripts. The background of my issues are that I realized that managing my raw data (which came in text files from government agencies, with scarce documentation) should be separate from the front-end of my analysis still kept in Stata. This is similar to what has been achieved with MonetDB.R (e.g. hannes.muehleisen.org/SSDBM2013-databases-and-statistics.pdf) though probably limited to an odbc link (so missing some of the real benefits coming from merging and calculations done in MonetDB). That said, this is the only sign of a Stata and MonetDB link I could find: http://www.stata.com/statalist/archive/2012-08/msg01363.html Disclaimer: I am an SQL and MonetDB newbie, but thanks for not letting me miss the obvious. [1]: https://www.monetdb.org/Documentation/Manuals/SQLreference/CopyInto [2]: http://stackoverflow.com/a/10421034/938408 [3]: http://dba.stackexchange.com/a/22763/38399 [4]: http://dba.stackexchange.com/a/61969/38399

3 5

SELECT failure: "insufficient space for HEAP ..."
by Clarkson, Edward C. 14 May '14

14 May '14

Hi, We have an application (http://tmt.gtri.gatech.edu/lytic.html) based on a relatively modest, single (~1-10M row, ~20 column) MonetDB table (Win7, 64 bit, Jan14-SP1 server release, quad-core, 8GB RAM) and have been encountering sporadic SELECT query failures with the error: "Insufficient space for HEAP of 1017856 bytes". Since I've been trying in earnest to diagnose this, that byte value has been consistent, FWIW. The failures are frustratingly intermittent; if I rerun a failing query (by hand, using mclient) it sometimes fails again, and sometimes works correctly. I've been unable to figure out any sort of pattern. Unfortunately, I can't post the exact table and query, but the general form of the queries (which have ~5-10 case clauses) are: SELECT CASE WHEN Foo = 'ValueA' and Bar LIKE 'ValueB%' THEN 100*(Baz-1234)/1234 WHEN Foo = 'ValueC' and Bar LIKE 'ValueD%' THEN 100*(Baz-3456)/3456 ... END FROM table WHERE <same case statement as above> IS NOT NULL LIMIT 1 Any ideas what I might do to narrow this down or other information I might provide? I captured an explain of a failing query--it's many hundreds (1000s?) of lines, so I didn't want to include it here in full. All suggestions appreciated. Thanks, Ed P.S. MonetDB has been a fantastic complement to our application, thanks to everyone involved!

2 3

thank you for monetdb
by Anthony Damico 02 May '14

02 May '14

i love it this much: http://2.bp.blogspot.com/-ogFP6EHJ6h8/U1E225vANvI/AAAAAAAAww8/9wOEp95iekk/s…

2 1

why the same procedure (in shell or in sql) cost so different time?
by integrity 30 Apr '14

30 Apr '14

Hi All, I want to execute a batch of small sql stored procedures, for convenience, i put them totally in another big stored procedure: gwac_uniquecatalog, it cost 58 minutes: #!/bin/bash time mclient -d mydb -s "CALL gwac_uniquecatalog(99);" real 58m3.518s The time is so long that I tried to execute the small stored procedures one by one in SHELL, it only cost 6.5 minutes /usr/bin/time ./gwac_uniquecatalog.sh 99 2.82user 3.25system 6:30.39elapsed 1%CPU (0avgtext+0avgdata 10880maxresident)k 0inputs+0outputs (0major+980011minor)pagefaults 0swaps my question is why did this dramatic difference happen and how to reduce the time of big stored procedure? =========================================================== the content of the big stored procedure: gwac_uniquecatalog is CREATE PROCEDURE gwac_uniquecatalog(imgid int) BEGIN DECLARE imgid_t int; SET imgid_t =1; CALL insert_1_to_1_assoc(imgid_t); --对第一幅图 only insert into assoc is enough SET imgid_t = imgid_t +1; WHILE (imgid_t<=imgid) DO CALL insert_tempuniquecatalog(imgid_t,10.0); --10 is match radius 对第二幅图及以后的图 CALL find_n_to_m(); CALL insert_1_to_n_unique(); CALL insert_new_1_to_n_assoc(); CALL insert_1_to_n_assoc(); CALL delete_1_to_n_inactive_assoc(); CALL flag_1_to_n_inactive_uniq(); CALL flag_1_to_n_inactive_tempuniq(); CALL insert_1_to_1_assoc(imgid_t); CALL update_1_to_1_uniq(); CALL insert_new_uniq(imgid_t); CALL insert_new_assoc(imgid_t); SET imgid_t = imgid_t +1; END WHILE; END; =========================================== the SHELL code is: #!/bin/bash imgid_t=1 /usr/bin/time -f %e mclient -d mydb -s "CALL insert_1_to_1_assoc(imgid_t -le $1 ] do /usr/bin/time -f %e mclient -d mydb -s " CALL insert_tempuniquecatalog($imgid_t,10.0);" mclient -d mydb -s " CALL find_n_to_m();" mclient -d mydb -s " CALL insert_1_to_n_unique(); " mclient -d mydb -s " CALL insert_new_1_to_n_assoc();" mclient -d mydb -s " CALL insert_1_to_n_assoc();" mclient -d mydb -s " CALL delete_1_to_n_inactive_assoc();" mclient -d mydb -s " CALL flag_1_to_n_inactive_uniq();" mclient -d mydb -s " CALL flag_1_to_n_inactive_tempuniq();" mclient -d mydb -s " CALL insert_1_to_1_assoc($imgid_t);" mclient -d mydb -s " CALL update_1_to_1_uniq();" mclient -d mydb -s " CALL insert_new_uniq($imgid_t);" mclient -d mydb -s " CALL insert_new_assoc(imgid_t ((++imgid_t)) done Thanks very much! Best regards, Meng

4 8

procedure to follow when changing the sql-optimizer settings
by Mahmut Uludag 25 Apr '14

25 Apr '14

Hi, We were getting database crashes when we were using our database with read/write mode and using the default sql_optimizer settings. We decided to run our database in read-only mode and also has changed the sql_optimizer setting to sequential_pipe. With new settings we don't see any crashes but have started to see error messages in the log files similar to the following lines. 2014-04-25 08:21:52 ERR db1[14554]: #mvc_bind_schema schema1 2014-04-25 08:21:52 ERR db1[14554]: #mvc_bind_table schema1.table1 2014-04-25 08:21:52 ERR db1[14554]: #mvc_bind_column table1.col1 2014-04-25 08:21:52 ERR db1[14554]: #mvc_rollback 2014-04-25 08:21:52 ERR db1[14554]: #mvc_rollback done 2014-04-25 08:21:52 ERR db1[14554]: #mvc_commit 2014-04-25 08:21:52 ERR db1[14554]: #mvc_commit done My question is there a procedure to follow when we change sql-optimizer settings? I assume MonetDB has some history of the past and because of the change of the sql_optimizer settings the bits in the history causing these error messages but i would not be surprised if this is completely wrong. Mahmut Uludag

3 4

concurrency and use of temporary tables
by Niall Rooney 23 Apr '14

23 Apr '14

Hello, I am running a java program which connects to a MonetDB database and runs a thread where each thread creates a number of local temporary tables using the and populates these tables in certain data processing steps. On termination the temporary tables are deleted. These temporary tables are utilised in a number of select statements in combination with the tables in the database. Tables are created using a table name where a UUID is generated for each thread so the use of local tables is unique to the thread e.g. create local temporary table tempquerywords0e5a0e08c1d24cf6b9529426463b6191(word_word varchar(255)) ON COMMIT PRESERVE ROWS. All needed temporary tables are created by the thread as an initial process. The connection to the database is set with autoCommit set to true. If each thread is run sequentially , there is no problem. However if I run the process concurrently with a maximum of 2 concurrent threads, some threads terminate early with an Exception message e.g. nested exception is java.sql.SQLException: SELECT: no such table 'tempweightedclustersa7f7f47e7c184175ac8ab5ee2dc76a03'. This message is confusing as the table is populated ok before the select. Although other participants have posted messages concerning problems based on concurrent updates and temporary tables , is there an issue concerning concurrent reads and temporary tables? Best Regards Niall

1 0

TPC-H in MALs
by Qing Guo 23 Apr '14

23 Apr '14

Hi, I was trying to run “mserver” (standalone) in a simulator to do some performance study. As mserver only reads MALs, are there any ready-to-use MAL scripts for TPC-H? I tried to use “explain” in “mclient”, but the translated MALs seem to be too detailed and cannot simply copy and run. Any idea on this? Thank you! -Kevin

2 2

Build for Ubuntu 14.04 "Trusty Tahr"?
by Robert Kosara 19 Apr '14

19 Apr '14

Hi, I see Ubuntu 14.04 (which was just officially released a few days ago) listed on the Ubuntu/Debian packages list: http://dev.monetdb.org/downloads/deb/ However, the “trusty” directory doesn’t exist, and I can’t install MonetDB through apt-get. Will that build be enabled in the near future? Thanks, Robert

2 1

Overflow calculation in tpch Q1
by Tsai Li Ming 17 Apr '14

17 Apr '14

Hi, I’m getting an overflow calculation when SF=100 for Q1. The rest of the queries are fine. There’s an old thread on this from Sep 2013: https://www.monetdb.org/pipermail/users-list/2013-September/006817.html I’m using 11.17.13. Will this be fixed soon? Thanks!

1 0

Blob and Clob not implement in JDBC v 2.10?
by Phat Loc 11 Apr '14

11 Apr '14

Hello, I am not able to get Blob and Clob to work in JDBC v 2.10? I get a SQLFeatureNotSupportedException? Is this the correct behavior? Thanks, Phat

2 5

fastest way of having a number of rows of a table in monetdb ?
by Leon-Constantin Fopa 11 Apr '14

11 Apr '14

Helo everybody, To have the exact number of rows of a table t I actually run the following query SELECT COUNT(*) AS n FROM t; Is there a faster way to do this ? Best regards, Fopa

1 0

How to have the list of all tables names of a monetdb database ?
by Leon-Constantin Fopa 11 Apr '14

11 Apr '14

Hi everyone In postgresql one can have all tables names by running the following query SELECT table_name FROM information_schema.tables WHERE table_schema='public'; Is there something similar in monetdb to have the list of tables ? Best regards, Fopa

3 3

UPDATE fails with BATproject: does not match always
by Radovan Bičiště 11 Apr '14

11 Apr '14

Hello, I have a problem with following update and suspect data corruption. UPDATE crmkarta SET rodne_cislo = (SELECT MAX(rodne_cislo) FROM verka_klient WHERE crmkarta.cislo_karty = verka_klient.cislo_karty AND rodne_cislo IS NOT NULL) WHERE cislo_karty IS NOT NULL --AND aktivita > 0 AND EXISTS (SELECT verka_klient.cislo_karty FROM verka_klient WHERE crmkarta.cislo_karty = verka_klient.cislo_karty AND rodne_cislo IS NOT NULL); Can not create object [SQL State=22000] Next: BATproject: does not match always [SQL State=22000] Next: BATproject: does not match always [SQL State=22000] Would dump and restore of tables help ? We are on version Jan2014-SP1. Thank you, Radovan

1 0

Update with subquery
by George Kollias 09 Apr '14

09 Apr '14

Hello, Let's assume the script below (version Jan-2014-SP1): 1> select * from checking where id = (select id from account where name='ipzwhsqk'); | id | value | +==+========+ | 1 | 7580.67 | 2> select * from saving where id = (select id from account where name='ipzwhsqk'); | id | value | +==+========+ | 1 | 2599.01 | 3> select case when (select ((select value from saving where saving.id=account.id) + (select value from checking where checking.id=account.id)) from account where name='ipzwhsqk') < 1000000 then checking.value - 1000000 - 1 else checking.value - 1000000 end from checking where checking.id = (select id from account where name='ipzwhsqk'); | ifthenelse_isnull_<_L7 | +==============+ | -992420.33 | -- Update + same select as step 3. It's executed successfully. 4> update checking set value = ( select case when (select ((select value from saving where saving.id=account.id) + (select value from checking where checking.id=account.id)) from account where name='ipzwhsqk') < 1000000 then checking.value - 1000000 - 1 else checking.value - 1000000 end) where checking.id = (select id from account where name='ipzwhsqk'); 5> select * from checking where id = (select id from account where name='ipzwhsqk'); | id | value | +==+========+ | 1 | 7580.67 | So, it seems that the step 4's update has no impact. Am i missing something? Thank you, George

2 2

MonetDB usage of openssl and recently reported Security Vulnerability Issue in Openssl ( refer http://arstechnica.com/security/2014/04/critical-crypto-bug-in-openssl-opens-two-thirds-of-the-web-to-eavesdropping/ )
by Ashish Kumar Singh 09 Apr '14

09 Apr '14

Hi All, Can any one give some guideline on how MonetDB uses openssl library which is a dependency expressed during compile time and what kind of measures should taken to avoid security vulnerability reported recently with openssl. Please refer to links below : http://arstechnica.com/security/2014/04/critical-crypto-bug-in-openssl-open… http://blog.cloudflare.com/staying-ahead-of-openssl-vulnerabilities Regards, Ashish

2 1

decreasing table size
by Radovan Bičiště 09 Apr '14

09 Apr '14

Hello, Thanks to the great new feature sys.storage() I'm able to see table size. As per documentation I would expect that "DELETE FROM table" without any condition will reclaim the disk space. According the sys.storage() it does not. I tried to delete and than select from sys.storage() and the table size was still the same. Am I doing something wrong? Thank you, Radovan

2 2

Properly handling of concurrency conflicts
by Alastair McKinley 09 Apr '14

09 Apr '14

Dear all, I am currently testing an application running on MonetDB that experiences intermittent errors when under high concurrency. I believe these are aborted transactions (the mapi error code is -4). The applications loads a small input table using the mapi interface, runs two queries internally (including writing to an intermediate table) and then reads the result set. I have tried to produce a similar minimal example using the Python API which does produce concurrency errors and I am wondering what the correct thing to do on the client side is. Should I simply resubmit the failed query? The Python script used is shown below: import monetdb.sql import sys from multiprocessing import Process import uuid def f(): dbh = monetdb.sql.Connection(port=int(sys.argv[1]),database=sys.argv[2],hostname=sys.argv[3],autocommit=True) for i in range (0,20): tablename = "input_"+str(uuid.uuid1()).replace("-","_") cursor = dbh.cursor() #cursor.execute('create local temporary table %s (term int, p float) on commit preserve rows;' % tablename) cursor.execute('declare table %s (term int, p float);' % tablename) cursor.execute('insert into %s values (1,0.1);' % tablename) cursor.execute('select * from %s;' % tablename) print(cursor.fetchall()) cursor.execute('drop table %s;' % tablename) dbh.close() procs=[] for t in range(0,int(sys.argv[4])): p = Process(target=f) procs.append(p) p.start() for proc in procs: proc.join()? Running this locally with "python2.7 concurrency.py 50000 test_db 127.0.0.1 10" results in a number of error messages like: ?ProgrammingError: 40000!COMMIT: transaction is aborted because of concurency conflicts, will ROLLBACK instead What is the right thing to do? It seems that anything that writes to the db even when they are separate tables causes these messages. Best regards and thanks, Alastair

2 3

Flushing the cache
by Masood Mortazavi 08 Apr '14

08 Apr '14

Is there a method (available from mclient) to cause MonetDB to evict all cached columns without having to restart? Thanks, - Masood

2 1

Hash ('#') in column aliases
by Clarkson, Edward C. 07 Apr '14

07 Apr '14

Hi, I'm using the 64 bit windows Jan'14 release (v11.17.9 "Jan2014"). I wanted to confirm the following behavior is a bug before I reported it (this is from the mclient terminal): sql>CREATE TABLE test (a int); operation successful (5.230ms) sql>SELECT a AS "#" FROM test; +--+ | | +==+ +--+ 0 tuples (3.320ms) In contrast: sql>SELECT a AS "@" FROM test; +---+ | @ | +===+ +---+ 0 tuples (1.672ms) When there's data in the table, all the *results* are fine, it's just the field names which are wonky. Loosely, it looks like the first alias (I have not tried making columns with '#' in the name, so maybe there too?) to use '#' basically blanks out its and all following column names in the result set--so even if I'm missing something w/r/t escaping "#", it doesn't' seem right that all following columns should also be blank... Thanks, Ed

1 0

Scope of local temporary tables
by Alastair McKinley 05 Apr '14

05 Apr '14

Hi all, I was wondering if I could clarify something about the scope of local temporary tables in MonetDB. I imagined that these would be local to a single client session but it seems that I am wrong about this. If I execute the following Python script multiple times in succession, it fails as the table name is already in use. import monetdb.sql import sys dbh = monetdb.sql.Connection(port=int(sys.argv[1]),database=sys.argv[2],hostname=sys.argv[3],autocommit=True) cursor = dbh.cursor() cursor.execute('create local temporary table input (term int, p float) on commit preserve rows;'); cursor.execute('insert into input values ( 1,0.1);'); cursor.execute('select * from input;'); print(cursor.fetchall()) dbh.close() How is the local temporary table different from a normal table in this case? Best regards, Alastair

2 3

Fastest ('standard") way to load data into MonetDB
by Masood Mortazavi 05 Apr '14

05 Apr '14

What is the fastest way to load data into MonetDB, using its "standard" user APIs? -m.

2 3

How to update one table from another table or from aggregation in subqueries?
by George Kollias 04 Apr '14

04 Apr '14

Hello, On a related note, would it be possible to have a "with" statement inside the subquery, .i.e. "update t1 set data = *(with tmp_table as (select ...) select ... from ... where ...)* where exists (select * from t2 where t1.id= t2.id);"? According to my tests it's not possible. What would be the best way to simulate it in MonetDB? Using a View/Temporary Table? Thank you, George > Thanks for your reply and clarification! > With Jan2014 release, the two functions can be expressed in the following > working forms: > 1) update t1 set data=(select t1.data+t2.data from t2 where t2.id=t1.id) > where exists (select * from t2 where t1.id=t2.id); > 2) update t1 set data = (select sum(t2.data) from t2 where t2.id=t1.idgroup > by t2.id) where exists (select * from t2 where t1.id=t2.id);

1 0

MonetDB Performance and Data Schema
by Dino Veritas 02 Apr '14

02 Apr '14

Hi, My firm is investigating the use of MonetDB as a potential market data store. I am interested in the opinion of those on this list as to how best to structure the data for optimum performance. Basically, our data consists of ~50,000 instruments. One use we are looking at is for the storage of bar data, one for each minute.The resulting data for each bar is like <Timestamp,Open,High,Low,Close,Volume,...>, with one observation per minute. While not all symbols will have an entry for each minute, many observations will share the same Timestamp. Is this an appropriate use for MonetDB? One schema I tried was to put all symbols into the same table, and to separate the tables by month. So for 5 years that would be 5*12=60 tables. A query along the lines of "SELECT * FROM "200801" WHERE symbol='IBM' UNION ALL SELECT * FROM "200802" WHERE symbol='IBM' UNION ALL ...)" The reads for a single table were quite good, however, when reading across multiple tables I encountered a lot of swap space and disk IO which drastically slowed down the query. I also tried one table per symbol but found this to be simply untenable as INSERTS became extremely slow (on the order of minutes) and once again, massive swap space usage. How would you partition this data? We will be inserting once per day in bulk, and I am not super concerned with the insert performance. However, query performance is paramount, with some users querying data very frequently for long time periods for single or groups of symbols. The machine I am testing on has 16GB of RAM and is running Windows. However, the machine MonetDB would ultimately be deployed on would have much more RAM. Even so, the size of the results of a single query are less than 16GB and should fit into memory without issue. So please help me, MonetDB gurus, with any suggestions as to how you would partition and store this data. I would appreciate any input. Thank you!

5 8

Cycling through cursor in a procedure
by Radovan Bičiště 01 Apr '14

01 Apr '14

Hello, Please is there a flow control syntax for cycling through rows in a cursor in a procedure? I can not find any other solution to how to select highest numbers from a table which total falls under certain threshold. :( I have a function that returns sorted rows and can be used as a subquery and I did not get further ... Thank you, Radovan

4 5

Help, took 18 hours to COPY 113 million rows
by Bryan Senseman 28 Mar '14

28 Mar '14

Anybody have any insight into why the Windows performance is so poor? I am trying to restore a database (55GB total) and it took 18 hours to insert into my first big table. The CPU and Disk was less than 25% the entire time; with complete periods of zero activity. Attached is a screen show of the moderate cpu 28% and minor disk and the disk is **only** reading the dump file by mclient. -- ------------------------------ This transmission is confidential and intended solely for the use of the recipient named above. It may contain confidential, proprietary, or legally privileged information. If you are not the intended recipient, you are hereby notified that any unauthorized review, use, disclosure or distribution is strictly prohibited. If you have received this transmission in error, please contact the sender by reply e-mail and delete the original transmission and all copies from your system.

1 0

Restore/Load just hangs for hours (Windows Jan2014)
by Bryan Senseman 27 Mar '14

27 Mar '14

For about 3 hours I saw cyclical activity on memory and disk when loading the 55GB dump file (created on Linux via \D), however for the last 12 hours it has just sat there doing nothing. No CPU activity, no disk; this happened last time and I believe after 17 hours of "nothing" it popped up with a series of "operation completed" messages, what is it doing? -- ------------------------------ This transmission is confidential and intended solely for the use of the recipient named above. It may contain confidential, proprietary, or legally privileged information. If you are not the intended recipient, you are hereby notified that any unauthorized review, use, disclosure or distribution is strictly prohibited. If you have received this transmission in error, please contact the sender by reply e-mail and delete the original transmission and all copies from your system.

2 3

MonetDB on Windows keeps crashing
by Bryan Senseman 26 Mar '14

26 Mar '14

MonetDB (Feb2013SP5 & Jan2014) keep crashing on Windows Server 2008. Multiple times per week, we find the MonetDB process has crashed and needs to be restarted. Is there a log file somewhere that we can see that might give us insight into why it fails? -- ------------------------------ This transmission is confidential and intended solely for the use of the recipient named above. It may contain confidential, proprietary, or legally privileged information. If you are not the intended recipient, you are hereby notified that any unauthorized review, use, disclosure or distribution is strictly prohibited. If you have received this transmission in error, please contact the sender by reply e-mail and delete the original transmission and all copies from your system.

3 3

Extending physical memory via memory cache
by Calum Miller 26 Mar '14

26 Mar '14

Good Morning, Is it possible to extend the amount of physical memory available to monetdb via Memcached/Hazelcast? Thanks Calum Miller Millersoft

2 1

JDBC - how to set autocommit in url as on option
by Radovan Bičiště 21 Mar '14

21 Mar '14

Hello, We are using the JDBC driver from KETTLE (http://kettle.pentaho.com) tool. I could not find an option how to tell the driver that my connections are autocommit. I tried to add option autocommit=true. The tool added the opton at the end of the JDBC URL as follows jdbc:monetdb://172.26.123.14:50000/zkdev1;autocommit=true . But it failed with error that it could not find database "zkdev1;autocommit=true". Thank you for your help. Radovan

3 2

How to execute a MAL function in a monetdb database ?
by Leon-Constantin Fopa 17 Mar '14

17 Mar '14

I'm reading the MonetDB Internal Description because I'm interested to implement a special procedure direct in the MonetDB Algebra Language (MAL) instead of SQL-frontend (I hope to gain more performance by doing so). Here is a sample MAL code provide in the MonetDB documentation function sample(nme:str, val:any_1):bit; c := 2 * 3; b := bbp.bind(nme); #find a BAT h := algebra.select(b,val,val); t := aggr.count(h); x := io.print(t); y := io.print(val); end sample; My question is how to execute such a MAL code upon one of my existing database ? Thans for any replay

2 1

Copy table between monet instances using COPY BINARY ...
by Radovan Bičiště 17 Mar '14

17 Mar '14

Hello, I read the following recipe https://www.monetdb.org/Documentation/Cookbooks/SQLrecipes/BinaryBulkLoad . Please is there a command or a binary that does the dump part "Each attachment file is produced by a program that writes the binary image of the BAT directly, ..." ? (mclient --format=raw?) We are on "MonetDB Database Server v1.7 (Feb2013-SP6)". Thank you for your help. Radovan

2 1

COPY INTO table specify columns
by Wei Zhuo 17 Mar '14

17 Mar '14

Hi, Couldn't find any info/doc if a list of column names, and order of columns, can be specified when using COPY INTO command. If not possible, is this something relatively easy to implement? Thanks!

2 1

possible to improve paging performance on the windows version of monetdb?
by Anthony Damico 17 Mar '14

17 Mar '14

hi monetdb team, could disk paging performance be improved on windows? a query run without versus with any disk paging is kinda like the hulk versus plain old bruce banner. ;) ==background== i'm working on a longer tutorial for http://asdfree.com/ about how to work with census microdata from 74 different countries (yes, 74!) with R and monetdb. i'm planning to release a blog post specifically for the brazilian microdata and then expand that code to work on the other 73 countries <https://international.ipums.org/international-action/countries>. both brazilian census bureau employees and outside users have taken a serious interest in this work, with official links from one<http://www.ibge.gov.br/home/estatistica/populacao/trabalhoerendimento/pnad2…>, two<http://www.ibge.gov.br/home/estatistica/indicadores/trabalhoerendimento/pme…>, three <http://www.ibge.gov.br/home/xml/pof_2008_2009.shtm> of their microdata pages. r and monetdb are obviously great choices for analysts who don't have the resources for expensive computers and/or software. a former employee at insee (the french stat. bureau) who currently consults on international demography projects outlined this problem for me pretty clearly: lower-resource statistical agencies in the developing world need a good, free option like (R + monetdb) for analysis of larger data sets on the (generally cheaper) hardware that they already have. international census bureau employees and other demographers are much more likely to have statistical but not computer programming skills, so as i work on these instructional documents, i'll aim to make the computing side of things as easy as possible. and, most central to this e-mail, much of this new potential audience uses windows. (on march 10th, 2014<http://cran-logs.rstudio.com/2014/2014-03-10.csv.gz>, 67% of MonetDB.R package downloads were windows users) ==problem== certain sql commands - it's hard for me to pin down which ones - tend to hang. i will run a command, look at my "process manager" and see RAM start getting gobbled up (not by mserver.exe but by some invisible process--i think disk paging?), and then wait. mserver.exe will use between 0% and 1% of the total CPU for a few minutes, then something will "wake up" and suddenly the CPU starts lighting up.. with 70%-90% usage across multiple cores. this 1% CPU usage to 75% CPU usage can jump back and forth a few times until the full command finally finishes. all of the time spent with 0% or 1% CPU usage - waiting for commands to execute - seems like an opportunity for improvement of the windows build? here's an example that took 11 minutes for a query run on 20,000,000 records.. > system.time( z <- svymean( ~v0601 , pes.design , byvar = ~v0001 , se = TRUE ) ) user system elapsed 18.47 1.13 *710.86 * ..totally awesome! but can it be improved? ==comparison between two machines== the attached .sql code has been run on two windows machines: on the windows server with 64 gb of ram-- # Serving database 'test', using 4 threads # Compiled for x86_64-pc-winnt/64bit with 64bit OIDs dynamically linked # Found 64.000 GiB available main-memory. on my personal windows laptop-- # Serving database 'test', using 8 threads # Compiled for x86_64-pc-winnt/64bit with 64bit OIDs dynamically linked # Found 7.860 GiB available main-memory. ==processing time comparison== processing time on the 64gb server: 27 tuples *(5.2s)* processing time on my 8gb laptop: 27 tuples *(7m 46s)* *so on a windows machine, if disk paging can be avoided, the processing time is 90x faster for this attached sql code?* ==how to load this reproducible example== ** data file with four columns ** https://drive.google.com/file/d/0B3LuuP081KDYNERWQWx5MEpZU2M/edit?usp=shari… ** commands to load the data ** CREATE TABLE "sys"."d10" ( "idkey" INTEGER, "v0601" VARCHAR(255), "pes_wgt" DOUBLE, "v0001" VARCHAR(255) ); COPY 20635472 RECORDS INTO d10 FROM 's:/temp/example.txt'; [[see .sql attachment for post-import commands]] ==general diagnostics== # MonetDB 5 server v11.17.11 "Jan2014-SP1" # Serving database 'test', using 4 threads # Compiled for x86_64-pc-winnt/64bit with 64bit OIDs dynamically linked # Found 64.000 GiB available main-memory. # Copyright (c) 1993-July 2008 CWI. # Copyright (c) August 2008-2014 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/ # MonetDB/JAQL module loaded # MonetDB/SQL module loaded > ==conclusion question== the windows version of monetdb is intensely slow at rather unexpected times. can and will anything be done about this in the near future? thanks for monetdb!

1 1

MonetDB Slow Query with Index
by Summer Xia 16 Mar '14

16 Mar '14

Hi all, I created a table with 10GB (60M records), added unique index (hidden_id) manually after data insertion. I had the simplest query but it took one minutes to complete. select hidden_id from netflow where hidden_id = 350000; And also the query took tens of minutes "select * from netflow order by hidden_id limit 12500 offset 212500;". It really confuses me. I post the analysis of the first below. Any clue why it's so slow? trace select hidden_id from netflow where hidden_id = 350000; +----------+------------------------------------------------------------------+ | ticks | stmt | +==========+==================================================================+ | 3 | X_3 := sql.mvc(); | | 15 | X_7=<tmp_2510>[69396995] := sql.bind(X_3=0,"sys","netflow","hidd | : : en_id",0); : | 227 | X_4:bat[:oid,:oid] =<tmp_13332>[69396995] := sql.tid(X_3=0,"sys" | : : ,"netflow"); : | 72978741 | X_36=<tmp_4053>[1] := algebra.subselect(X_7=<tmp_2510>[69396995] | : : ,X_4=<tmp_13332>:bat[:oid,:oid][69396995],A0=350000:lng,A0=35000 : : : 0:lng,true,true,false); : | 17 | (X_10=<tmp_2175>[0],r1_10=<tmp_3416>[0]) := sql.bind(X_3=0,"sys" | : : ,"netflow","hidden_id",2); : | 14 | X_37=<tmp_13337>[0] := algebra.subselect(r1_10=<tmp_3416>[0],A0= | : : 350000:lng,A0=350000:lng,true,true,false); : | 6 | X_13=<tmp_3416>[0] := sql.bind(X_3=0,"sys","netflow","hidden_id" | : : ,1); : | 15 | X_38=<tmp_11053>[0] := algebra.subselect(X_13=<tmp_3416>[0],X_4= | : : <tmp_13332>:bat[:oid,:oid][69396995],A0=350000:lng,A0=350000:lng : : : ,true,true,false); : | 4 | X_15=<tmp_4053>[1] := sql.subdelta(X_36=<tmp_4053>[1],X_4=<tmp_1 | : : 3332>:bat[:oid,:oid][69396995],X_10=<tmp_2175>[0],X_37=<tmp_1333 : : : 7>[0],X_38=<tmp_11053>[0]); : | 20 | X_17=<tmp_11053>[1] := sql.projectdelta(X_15=<tmp_4053>[1],X_7=< | : : tmp_2510>[69396995],X_10=<tmp_2175>[0],r1_10=<tmp_3416>[0],X_13= : : : <tmp_3416>[0]); : | 6 | X_18 := sql.resultSet(1,1,X_17=<tmp_11053>[1]); | | 7 | sql.rsColumn(X_18=1,"sys.netflow","hidden_id","bigint",64,0,X_17 | : : =<tmp_11053>[1]); : | 2 | X_23 := io.stdout(); | | 25 | sql.exportResult(X_23=="104d2":streams,X_18=1); | | 1 | end s1_3; | | 73011629 | X_5:void := user.s1_3(350000:lng); | +----------+------------------------------------------------------------------+ This is the table being created. CREATE TABLE "netflow" ( "time_seconds" double DEFAULT NULL, "parsed_date" timestamp DEFAULT NULL, "date_time_str" varchar(45) DEFAULT NULL, "ip_layer_protocol" bigint DEFAULT NULL, "ip_layer_protocol_code" varchar(45) DEFAULT NULL, "first_seen_src_ip" varchar(45) DEFAULT NULL, "first_seen_dest_ip" varchar(45) DEFAULT NULL, "first_seen_src_port" bigint DEFAULT NULL, "first_seen_dest_port" bigint DEFAULT NULL, "more_fragments" varchar(45) DEFAULT NULL, "cont_fragments" varchar(45) DEFAULT NULL, "duration_seconds" bigint DEFAULT NULL, "first_seen_src_payload_bytes" bigint DEFAULT NULL, "first_seen_dest_payload_bytes" bigint DEFAULT NULL, "first_seen_src_total_bytes" bigint DEFAULT NULL, "first_seen_dest_total_bytes" bigint DEFAULT NULL, "first_seen_src_packet_count" bigint DEFAULT NULL, "first_seen_dest_packet_count" bigint DEFAULT NULL, "record_force_out" varchar(45) DEFAULT NULL ); Best regards, Summer

2 1

python 3.2 support
by Simon 13 Mar '14

13 Mar '14

Hi thanks for the great project. I just wonder when there would be python 3.2 support. Is there any way that we could use monetdb in python 3.2 before the official package? Thanks again. Simon

4 5

Announcement: New Jan2014-SP1 Bugfix release of MonetDB suite
by Sjoerd Mullender 10 Mar '14

10 Mar '14

The MonetDB team at CWI/MonetDB BV is pleased to announce the Jan2014-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/>. Jan 2014-SP1 bugfix release Build Environment * Configure now enables the SQL front end by default. Merovingian * monetdb destroy -f now also works on running databases Bug Fixes * 3442: COPY INTO ... LOCKED reports incorrect count * 3443: DROP INDEX crashes server with BATsubselect: invalid argument: b must have a dense head * 3444: AND after ON () of LEFT OUTER JOIN with certain expressions will cause crash * 3452: ODBC driver build fails on Mac OS X due to a conflicting types for the SQLColAttribute with the unixODBC library

1 0

Fwd: [asdfree by anthony damico] New comment on analyze the program for international student asse....
by Anthony Damico 04 Mar '14

04 Mar '14

if there's any chance you might release SP1 soon, i'd appreciate it :) this user is hitting (resolved) bug 3443<http://bugs.monetdb.org/show_bug.cgi?id=3443>. thanks! ---------- Forwarded message ---------- From: lightest <noreply-comment(a)blogger.com> Date: Sun, Feb 23, 2014 at 7:53 PM Subject: [asdfree by anthony damico] New comment on analyze the program for international student asse.... To: ajdamico(a)gmail.com lightest <http://www.blogger.com/profile/12197082513968468249> has left a new comment on your post "analyze the program for international student asse...<http://www.asdfree.com/2013/12/analyze-program-for-international.html>": Hi Anthony, I've tried to run to codes, and when I run lines such as those of MICombine() functions, sometimes there is an error like the following: In R: Error in .mapiRequest(conn, paste0("s", statement, ";"), async = async) : error writing to socket (10054) Error in .mapiRequest(conn, paste0("s", statement, ";"), async = async) : error writing to socket (10054) Error in .mapiRequest(conn, paste0("s", statement, ";"), async = async) : error writing to socket (10054) > ======================================== In the console: # MonetDB 5 server v11.17.9 "Jan2014" # Serving database 'pisa', using 4 threads # Compiled for x86_64-pc-winnt/64bit with 64bit OIDs dynamically linked # Found 7.913 GiB available main-memory. # Copyright (c) 1993-July 2008 CWI. # Copyright (c) August 2008-2014 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:50007/ # MonetDB/JAQL module loaded # MonetDB/SQL module loaded >!FATAL: 40000!COMMIT: transation commit failed (perhaps your disk is full?) exi ting (kernel error: !ERROR: BATsubselect: invalid argument: b must have a dense head. ) Press any key to continue . . . ======================================= Publish<http://www.blogger.com/comment-moderate-confirm.do?blogID=69830612920483473…> Delete<http://www.blogger.com/comment-moderate-confirm.do?blogID=69830612920483473…> Mark as spam<http://www.blogger.com/comment-moderate-confirm.do?blogID=69830612920483473…> Moderate<http://www.blogger.com/blogger.g?blogID=6983061292048347330#pendingcomments>comments for this blog. Posted by lightest to asdfree by anthony damico <http://www.asdfree.com/>at February 23, 2014 at 7:53 PM

2 3