- users-list - monetdb.org

[MonetDB-users] MonetDb slowing down
by Cory Isaacson 07 Sep '12

07 Sep '12

We have MonetDb running in the AWS cloud, with about 32GB of physical RAM. Our process uses COPY INTO every few minutes to load small chunks of data (usually 1000 rows at a time). That seems to work fine. There are a few users running queries against MonetDb, and now at least once/day we have to start/stop MonetDb. We see very high disk I/O (mainly I/O wait), so it appears that memory usage is high and does not get released. We are using the April 2012 release. The JDBC client is used for these queries. Overall MonetDb is doing a very good job, but this slowdown makes it appear unstable to end users. If there are any suggestions on how to improve this please let us know. cmi --

2 1

[MonetDB-users] Connecting from DBLite.com
by Medha Atre 06 Sep '12

06 Sep '12

Hello, I have successfully installed and used MonetDB in the past as a standalone server, by connecting/querying through "mclient" running on the same machine where server is running. I was trying out web-based access, in which I was trying DBLite's simple web interface http://user.dblite.com (running on the browser on the same machine). My MonetDB server was compiled with "enable-sql", "enable-mserver5", and other optimization options. Not compiled with JDBC/ODBC though (but DBLite is a PHP based interface). "Control" is set to YES and "passphrase" is set to "monetdb" too. While the server is running, I can very well connect and query through command line mclient, but if I give "localhost", port as 50000 and default user/passwd "monetdb/monetdb" on the http://user.dblite.com interface, it keeps giving me "access denied" error. I am wondering what I am missing! Thanks. Medha

2 1

[MonetDB-users] source code structure
by guangliang peng 06 Sep '12

06 Sep '12

hi, i prepare to look at the source code of monnetdb.Can you guys give me some advices which can help reduce the time on the source code .And what's the source code structure? thanks, guangliang

1 0

[MonetDB-users] costmodel
by guangliang peng 06 Sep '12

06 Sep '12

hello, how do i show costmodel where i run a query ?because i want to know the internal execution of a query . thanks, guangliang

2 1

[MonetDB-users] monetdb join path
by guangliang peng 05 Sep '12

05 Sep '12

I now still feel confused about join_path .I have tried the trace ,but the results show many lines with MAL language.It is very hard for me to find out the join path and order.can anyone help me?

2 1

[MonetDB-users] Is there IF(), IFNULL() in SELECT?
by Dmitry Insta 31 Aug '12

31 Aug '12

Sorry if it was asked before, I can't find a comprehensive list of functions available. Is it possible to somehow implement something like this: SELECT IF(col1>0,'Negative','Positive') as Sign, IFNULL(col2,'Empty',col2) as col2 FROM table; // it's just an example, not the exact query :) Thanx!

2 1

[MonetDB-users] how to set see mserver5 config
by guangliang peng 31 Aug '12

31 Aug '12

hi, I want to see mserver5 configure for example dbfarm,dbname. I don't know the command and hwo i set the gdk-vmtrim =no .I don't know hwo to set the GDK PARAMETERS .Can you guys help me? best reguards, guangliang

2 1

[MonetDB-users] Migration DW to MonetDB
by R＠quel 28 Aug '12

28 Aug '12

Hi, I have a question, i need comparison the execution query times between MonetDB and SQL 2008 in Data Warehouse (applying a different orientations database systems). But i have a problem, i used the AdventureWorkR2 2008 but i don't know how i can migrate the information to MonetDB. To compare the same data. Someone help me? Please. It's very important. Thanks, Raquel -- View this message in context: http://old.nabble.com/Migration-DW-to-MonetDB-tp34348184p34348184.html Sent from the monetdb-users mailing list archive at Nabble.com.

2 1

[MonetDB-users] what's the trigger of monetdb releasing memory
by guangliang peng 27 Aug '12

27 Aug '12

hi , when I ran some queries on monetdb,I found that the memory was automatically released by monetdb.And it needs extra time to load data into memory again.I don't know why and if there are some triggers in monetdb? Thank you for helps. best reguards, guangliang

1 0

[MonetDB-users] New Grouping Implementation
by David893 26 Aug '12

26 Aug '12

Hi Folks, I've seen that you re-implemented the grouping functions. Will it have effect on performance (on non-ordered columns)? Right now we're running MonetDB on a 1billion data set, mostly for grouping and aggregating, we're very happy with the performance, but of-course, any improvement is highly welcome :). If it does affect performance, we'll be the first to test and report. you guys rock!, keep up the great work. -- View this message in context: http://old.nabble.com/New-Grouping-Implementation-tp34348175p34348175.html Sent from the monetdb-users mailing list archive at Nabble.com.

1 0

[MonetDB-users] monetdb crashed when using copy into .... locked
by li, xiaohua 24 Aug '12

24 Aug '12

Hi: does anybody know what's the problem with the copy into of locked? we want to use concurrent copy into to a table, seems montdb not good at this. some body says add locked, we try, but the database crached. we just test like this copy 200 OFFSET 1 RECORDS into f_activity from 'd:\bcp.txt' USING DELIMITERS '|','\n' NULL AS '' LOCKED seems need single user mode, but how to set single user mode? Lixiaohua Shanghai,China

2 2

[MonetDB-users] help
by guangliang peng 23 Aug '12

23 Aug '12

hello, can I use mserver5 to control loading data into memory or removing data from memory ? I don't know how to configure or the command.I am fresh new about Monetdb so please forgive my unknowing.Thank you very much for the commands. best regards, guangliang

2 1

[MonetDB-users] Announcement: New Jul2012-SP1 Bugfix release of MonetDB suite
by Sjoerd Mullender 23 Aug '12

23 Aug '12

The MonetDB team at CWI/MonetDB BV is pleased to announce the Jul2012-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/>. Jul 2012-SP1 bugfix release Java Module * Fixed adaptive cache size used when retrieving results, not to cause divide by zero errors when memory gets short, bug #3119. Client Package * mclient no longer prints the SQLSTATE at the start of each error returned by the SQL-server. MonetDB5 Server * The server now distinguishes between starting and started states, such that monetdbd can wait for it to finish starting. Merovingian * Starting a server now waits for as long as the server needs to possibly recover, bug #3134. In case of a long wait, the monetdbd logfile gives extra information on what the server is doing to recover. * Fixed a crash of monetdbd when local databases were unshared, bug #3135 * Resolved a problem where automatic starting of a database initiated by multiple clients at the same time could cause failed starts. Bug #3107 Bug Fixes * 3075: inconsistent declaration of algebra.markH with 3 input arguments * 3090: crashed if using single identifier for where condition * 3093: sql fail if use scalar subquery with alias * 3107: concurrent connections to the same stopped database yield in multiple mserver5 starts by monetdbd * 3119: MonetConnection$ResultSetResponse throws java.lang.ArithmeticException: divide by zero * 3132: test/BugTracker-2011/func_iter_vs_bulk.Bug-2826 fails * 3134: Database gets killed by timeout during startup * 3135: monetdbd crash while creating & loading database

1 0

[MonetDB-users] case insensitive
by li, xiaohua 22 Aug '12

22 Aug '12

Does MonetDB has some parameter to modify the case insensitive option? when end user write sql, usually we want to case insensitive. lixiaohua Shanghai,China

2 3

[MonetDB-users] perl dbd error:
by Marc Salomon 20 Aug '12

20 Aug '12

Hello, I am able to install MonetDB-11.11.5 and set up a database just fine. However when I install the perl modules from CPAN, I encounter this error when I try to connect: 2012-08-20 09:02:54 ERR merovingian[56170]: client error: client localhost:35633 sent challenge in incomplete block: Any idea what's wrong here? Are the CPAN modules and the distro perl modules stepping on one another? best, -marc

2 1

[MonetDB-users] MonetDB GDK cache
by Allen Zhang 17 Aug '12

17 Aug '12

Hi, there, We noticed when query a big set of data, the BAT storage grows in the same directory as the database itself, is that mean the temporary result gets written to disk? Is it possible we can assign the location for this cache to point to a SSD to speed up things? Best Regards Allen

1 0

[MonetDB-users] Limit memory usage
by Christian Braun 09 Aug '12

09 Aug '12

Hello, i would like to limit the memory usage of MonetDB to half of the physical available memory. This is on Debian 6.0 64-bit with 48GB memory. I added to /etc/default/monetdb: ulimit -v 25165824 On startup MonetDB still reports: 2012-08-08 13:21:30 MSG db[15678]: # Found 47.262 GiB available main-memory. cat /proc/15687/limits shows the limit: Limit Soft Limit Hard Limit Units Max address space 25769803776 25769803776 bytes Unfortunately on the first query i run the database gets killed: 2012-08-08 13:21:54 MSG merovingian[15663]: database 'db' (15678) was killed by signal SIGSEGV Is there a way to limit the memory usage of MonetDB on Linux? Package: monetdb5-server Version: 11.11.5-20120710 Regards and thanks, Christian.

3 3

Re: [MonetDB-users] MonetDB Python client
by Минц Алексей 07 Aug '12

07 Aug '12

Dear Fabian! Attached is the code, that reproduces the bug. SQL file creates tables needed, python file hangs on trying to receive data. My version of mapi2.py is free of this problem - it raises an exception instead. Regards, Alexey Mints

1 0

[MonetDB-users] Incorrect Results from Very Simple Queries
by Henry Addington 07 Aug '12

07 Aug '12

We are seeing a very strange behavior from MonetDB from simple queries on a single table. In monthly_tou_avg_price table, we have monthly average aggregate data that were inserted from a very large table (> 2 billion) with hourly data. We have created the aggregate table to speed up certain queries. Strangely, we are getting incorrect results from very simple queries on the aggregate table. Q1 below returns 9,890 records with yyyy_mm column having empty value for the 2012-07 records. All of the 2012-07 records are missing the yyyy_mm value. We ran the SQL statement that inserted those records and it does not show any empty yyyy_mm values. If we check for empty string or null value in yyyy_mm column (Q2 and Q3) against the aggregate table, we do not get any hits. If we query for yyyy_mm= '2012-07' as in Q4, we get exactly 9890 records showing yyyy_mm populated with value 2012-07. How could this be possible, Q1 showing empty yyyy_mm values for the same 9,890 records and Q4 showing 2012-07 yyyy_mm values? Q1: select * from monthly_tou_avg_price where header_id = 6 Q2: select * from monthly_tou_avg_price where header_id = 6 and yyyy_mm = '' Q3: select * from monthly_tou_avg_price where header_id = 6 and yyyy_mm is null Q4: select * from monthly_tou_avg_price where header_id = 6 and yyyy_mm = '2012-07' Q5 below returns empty value for yyyy_mm even though yyyy_mm column does not have any empty or null values. Q6 does not return any empty or null yyyy_mm values and contradicts Q5 result by returning 2012-07. Furthermore, Q7 contradicts Q5 with 9,890 records with 2012-07 value in yyyy_mm column. Q5: select distinct yyyy_mm from monthly_tou_avg_price where header_id = 6 Q6: select distinct yyyy_mm from monthly_tou_avg_price Q7: select * from monthly_tou_avg_price where header_id = 6 and years = 2012 and months = 7 yyyy_mm L15 9890 2009-04 7574 2009-05 7574 2009-06 7750 2009-07 7786 2009-08 7788 2009-09 7828 2009-10 7814 2009-11 7844 2009-12 8322 2010-01 8317 2010-02 8363 2010-03 8361 2010-04 9214 2010-05 9124 2010-06 9156 2010-07 9165 2010-08 9195 2010-09 9197 2010-10 9198 2010-11 9234 2010-12 9232 2011-01 9246 2011-02 9238 2011-03 9696 2011-04 9672 2011-05 9670 2011-06 9668 2011-07 9656 2011-08 9698 2011-09 9682 2011-10 9724 2011-11 9706 2011-12 9768 2012-01 9766 2012-02 9764 2012-03 9832 2012-04 9824 2012-05 9826 2012-06 9902 We also ran an UPDATE statement to set yyyy_mm to 2012-07 for rows with header_id = 6 and years = 2012 and months = 7. And, 9,890 records were successfully updated. However, Q1 through Q8 returned same results as above with no improvement. We have not seen this kind of issues in other tables. This problem seems to be limited to a table that is populated with an INSERT INTO statement. We checked the result returned by the select portion of Q8 and yyyy_mm is populated with 2012-07. But, the aggregate table seems to end up in a weird state where it returns incorrect results. Q8: insert into "monthly_tou_avg_price" select a.header_id, extract(year from a.opr_date) as y, extract(month from a.opr_date) as m, b.yyyymm as yyyy_mm, b.svalue as TOU, a.resource_name, round(avg(a.lmp_cong_prc),5), round(avg(a.lmp_ene_prc),5), round(avg(a.lmp_loss_prc),5), round(avg(a.lmp_prc),5), round(avg(a.ns_clr_prc),5), round(avg(a.rd_clr_prc),5), round(avg(a.ru_clr_prc),5), round(avg(a.sp_clr_prc),5), round(avg(a.shadow_prc),5), round(avg(a.fuel_prc),5), count(lmp_cong_prc) as actual_lmpcong_tou_count from oasislive.price a join oasislive.time b on a.yyyymmddhh = b.yyyymmddhh where a.opr_date between '2012-07-01' and '2012-07-31' group by a.header_id, y, m, yyyy_mm, TOU, a.resource_name order by y, m, a.header_id, TOU, a.resource_name; We can see this INSERT INTO related issue with other queries. Q9 shows that we do not have any values for 2011-03 ON period which is incorrect. In fact, if we specify '2011-03' as in Q10, it shows that we do in fact have values for the 2011-03 ON period! Q9: select yyyy_mm, tou, avg(lmp_prc) from monthly_tou_avg_price group by yyyy_mm, tou order by yyyy_mm, tou yyyy_mm tou L6 . . 2011-03 OFF 16.721532797520894 2011-03 ON (null) 2011-04 OFF 15.427080074671187 . . Q10: select yyyy_mm, tou, avg(lmp_prc) from monthly_tou_avg_price where yyyy_mm = '2011-03' group by yyyy_mm, tou order by yyyy_mm, tou yyyy_mm tou L11 2011-03 OFF 16.72153279752101 2011-03 ON 30.276486575893575 I apologize for the long-winded email. I just wasn't sure how to describe what I was seeing.... Please let me know if there is any additional information that I can provide you with. By the way, we are currently using August 2011 -SP2 release, but saw this issue in older releases as well. Best regards, Henry We are currently running MonetDB August 2011 SP2 release on CentOS

2 4

[MonetDB-users] Trigger: BEFORE INSERT
by Tapomay Dey 02 Aug '12

02 Aug '12

create table tt(id int, name varchar(20), value int); create table t2(id int); CREATE TRIGGER trg BEFORE INSERT ON tt REFERENCING NEW as newrow FOR EACH ROW BEGIN ATOMIC INSERT INTO t2 (id) values (newrow.id); END insert into tt values(1, 'a', 10); insert into tt values(2, 'b', 20); insert into tt values(3, 'a', 30); insert into tt values(4, 'a', 40); The result vales in t2 are: After insert#1: EMPTY After insert#2: {1} After insert#3: {1, 1, 2} After insert#4: {1, 1, 2,1,2,3} Tested this in Mysql and the same trigger works well there. Expected t2 values after insert#4: {1,2,3,4} Regards, Tapomay.

1 1

[MonetDB-users] Monetdb compilation from source on 32 bit machine
by Tapomay Dey 01 Aug '12

01 Aug '12

I checked the code at location and found that "value" is of type long and its bit precision is 61. I am trying to perform make on an Ubuntu 10.04 i686 architecture which is 32-bit In file included from xid.c:23: xid.h:42: error: width of ‘value’ exceeds its type xid.c: In function ‘XIDcompress’: xid.c:138: warning: format ‘%ld’ expects type ‘long int’, but argument 4 has type ‘BUN’ xid.c:161: warning: format ‘%ld’ expects type ‘long int’, but argument 4 has type ‘BUN’ xid.c: In function ‘XIDcolumndump’: xid.c:298: warning: format ‘%lo’ expects type ‘long unsigned int’, but argument 4 has type ‘oid’ make[7]: *** [libmal_la-xid.lo] Error 1

2 1

[MonetDB-users] group by cube
by DADOU 01 Aug '12

01 Aug '12

hello, Have monetdb operator OLAP like group by cube as oracle have i don't find any operator from CUBE or ROLLUP. Thunks -- View this message in context: http://old.nabble.com/group-by-cube-tp34241097p34241097.html Sent from the monetdb-users mailing list archive at Nabble.com.

2 1

[MonetDB-users] Slowdown in Jul2012 release
by Viktor Rosenfeld 01 Aug '12

01 Aug '12

Hi, I've noticed a slowdown in the Jul2012 which appears to be due to disk I/O. Printed below is the output of TRACE on the Jul2012 (11.11.5) release and the Apr2012-SP2 release (11.9.7) of the following query: #+BEGIN_EXAMPLE WITH span1 AS ( SELECT node.id FROM node ), solutions AS ( SELECT DISTINCT span1.id AS id1 FROM span1 ) SELECT count(*) FROM solutions #+END_EXAMPLE Jul2012: #+BEGIN_EXAMPLE +---------+-----------------------------------------------------------------------------------+ | ticks | stmt | +---------+-----------------------------------------------------------------------------------+ | 3 | X_1 := sql.mvc(); | | 11 | X_2:bat[:oid,:int] =<tmp_1230>[1262014] := sql.bind(X_1=0,"annis","node","id",0); | | 5 | X_7:bat[:oid,:oid] =<tmp_1366>[0] := sql.bind_dbat(X_1=0,"annis","node",1); | | 6 | X_9=<tmpr_1366>[0] := bat.reverse(X_7=nil:bat[:oid,:oid]); | | 13 | X_10=<tmp_2126>[1262014] := algebra.kdifference(X_2=nil:bat[:oid,:int],X_9=nil); | | 3543662 | (ext22=<tmp_2127>[1262014],grp20) := group.done(X_10=<tmp_2126>[1262014]); | | 18 | X_13=<tmp_2130>[1262014] := bat.mirror(ext22=nil); | | 82193 | X_14=<tmp_2131>[1262014] := algebra.leftjoin(X_13=nil,X_10=nil); | | 2185 | X_15 := aggr.count(X_14=nil); | | 15 | sql.exportValue(1,"annis.solutions","L1","wrd",64,0,6,X_15=1262014:wrd,""); | | 8 | end s0_1; | | 18 | function user.s0_1(); | | 3629118 | X_5:void := user.s0_1(); | +---------+-----------------------------------------------------------------------------------+ #+END_EXAMPLE This query is accompanied by a lot of disk I/O (both reading and writing) Apr2012-SP2: #+BEGIN_EXAMPLE +-------+-----------------------------------------------------------------------------------+ | ticks | stmt | +-------+-----------------------------------------------------------------------------------+ | 4 | X_1 := sql.mvc(); | | 12 | X_2:bat[:oid,:int] =<tmp_1471>[1262014] := sql.bind(X_1=0,"annis","node","id",0); | | 6 | X_7:bat[:oid,:oid] =<tmp_1365>[0] := sql.bind_dbat(X_1=0,"annis","node",1); | | 6 | X_9=<tmpr_1365>[0] := bat.reverse(X_7=nil:bat[:oid,:oid]); | | 14 | X_10=<tmp_2074>[1262014] := algebra.kdifference(X_2=nil:bat[:oid,:int],X_9=nil); | | 12116 | (ext22=<tmp_2107>[1262014],grp20) := group.done(X_10=<tmp_2074>[1262014]); | | 2427 | X_13=<tmp_2106>[1262014] := bat.mirror(ext22=nil); | | 46 | X_14=<tmp_2111>[1262014] := algebra.leftjoin(X_13=nil,X_10=nil); | | 6 | X_15 := aggr.count(X_14=nil); | | 11 | sql.exportValue(1,"annis.solutions","L1","wrd",64,0,6,X_15=1262014:wrd,""); | | 6 | end s0_1; | | 16 | function user.s0_1(); | | 15566 | X_5:void := user.s0_1(); | +-------+-----------------------------------------------------------------------------------+ #+END_EXAMPLE There is no disk I/O during this query. Notice the difference in runtime of the group.done statement in line 5. This statement is introduced by the DISTINCT in the query. Cheers, Viktor

1 0

[MonetDB-users] Multiplex-funnel cluster failover: master-master replication
by Tapomay Dey 31 Jul '12

31 Jul '12

Hi, I have setup a cluster of 3 machines with following tags: mydb/1/master, mydb/2/master, mydb/3/master On a fourth machine I have created a multiplex funnel as follows: monetdb create -m monetdb+monetdb@*/mydb/1/master,monetdb+monetdb@*/mydb/2/master,monetdb+monetdb@*/mydb/3/master mydbmaster My objective here is that I use this multiplex for only DDL/INSERTS/UPDATES. This would ensure replication on all the 3 masters thus achieving a hacked form of master-master replication. Then I could use any one of the masters for analytics querying. However I am stuck with the following issues while handling fail-over. 1. I expect that if one of the boxes die, others keep getting the updates until I realize the failure. The I could lock the multiplex, sync the dbfarms to catch up the failing db and then release the multiplex. 2. If the box with tag mydb/3/master dies, then the updates go to 1 and 2. But if mydb/1/master dies the none of the dbs get any more updates. Looks like the multiplex returns on the first failure without trying rest of the dbs. Any pointers as to where do I change this sequential failure logic? Also what do you think about my replication approach? Thanks and Regards, Tapomay

1 0

[MonetDB-users] Incorrect Results from Very Simple Queries ---- Updated
by Henry Addington 31 Jul '12

31 Jul '12

Please ignore my earlier email. I accidently sent the email before it was completed! We are seeing a very strange behavior from MonetDB from simple queries on a single table. In monthly_tou_avg_price table, we have monthly average aggregate data that were inserted from a very large table (> 2 billion) with hourly data. We have created the aggregate table to speed up certain queries. Strangely, we are getting incorrect results from very simple queries on the aggregate table. Q1 below returns 9,890 records with yyyy_mm column having empty value for the 2012-07 records. All of the 2012-07 records are missing the yyyy_mm value. We ran the SQL statement that inserted those records and it does not show any empty yyyy_mm values. If we check for empty string or null value in yyyy_mm column (Q2 and Q3) against the aggregate table, we do not get any hits. If we query for yyyy_mm= '2012-07' as in Q4, we get exactly 9890 records showing yyyy_mm populated with value 2012-07. How could this be possible, Q1 showing empty yyyy_mm values for the same 9,890 records and Q4 showing 2012-07 yyyy_mm values? Q1: select * from monthly_tou_avg_price where header_id = 6 Q2: select * from monthly_tou_avg_price where header_id = 6 and yyyy_mm = '' Q3: select * from monthly_tou_avg_price where header_id = 6 and yyyy_mm is null Q4: select * from monthly_tou_avg_price where header_id = 6 and yyyy_mm = '2012-07' Q5 below returns empty value for yyyy_mm even though yyyy_mm column does not have any empty or null values. Q6 does not return any empty or null yyyy_mm values and contradicts Q5 result by returning 2012-07. Furthermore, Q7 contradicts Q5 with 9,890 records with 2012-07 value in yyyy_mm column. Q5: select distinct yyyy_mm from monthly_tou_avg_price where header_id = 6 Q6: select distinct yyyy_mm from monthly_tou_avg_price Q7: select * from monthly_tou_avg_price where header_id = 6 and years = 2012 and months = 7 We also ran an UPDATE statement to set yyyy_mm to 2012-07 for rows with header_id = 6 and years = 2012 and months = 7. And, 9,890 records were successfully updated. However, Q1 through Q8 returned same results as above with no improvement. We have not seen this kind of issues in other tables. This problem seems to be limited to a table that is populated with an INSERT INTO statement. We checked the result returned by the select portion of Q8 and yyyy_mm is populated with 2012-07. But, the aggregate table seems to end up in a weird state where it returns incorrect results. Q8: insert into "monthly_tou_avg_price" select a.header_id, extract(year from a.opr_date) as y, extract(month from a.opr_date) as m, b.yyyymm as yyyy_mm, b.svalue as TOU, a.resource_name, round(avg(a.lmp_cong_prc),5), round(avg(a.lmp_ene_prc),5), round(avg(a.lmp_loss_prc),5), round(avg(a.lmp_prc),5), round(avg(a.ns_clr_prc),5), round(avg(a.rd_clr_prc),5), round(avg(a.ru_clr_prc),5), round(avg(a.sp_clr_prc),5), round(avg(a.shadow_prc),5), round(avg(a.fuel_prc),5), count(lmp_cong_prc) as actual_lmpcong_tou_count from oasislive.price a join oasislive.time b on a.yyyymmddhh = b.yyyymmddhh where a.opr_date between '2012-07-01' and '2012-07-31' group by a.header_id, y, m, yyyy_mm, TOU, a.resource_name order by y, m, a.header_id, TOU, a.resource_name; We can see this INSERT INTO related issue with other queries. Q9 shows that we do not have any values for 2011-03 ON period which is incorrect. In fact, if we specify '2011-03' as in Q10, it shows that we do in fact have values for the 2011-03 ON period! Q9: select yyyy_mm, tou, avg(lmp_prc) from monthly_tou_avg_price group by yyyy_mm, tou order by yyyy_mm, tou yyyy_mm tou L6 . . 2011-03 OFF 16.721532797520894 2011-03 ON (null) 2011-04 OFF 15.427080074671187 . . Q10: select yyyy_mm, tou, avg(lmp_prc) from monthly_tou_avg_price where yyyy_mm = '2011-03' group by yyyy_mm, tou order by yyyy_mm, tou yyyy_mm tou L11 2011-03 OFF 16.72153279752101 2011-03 ON 30.276486575893575 I apologize for the long-winded email. I just wasn't sure how to describe what I was seeing.... Please let me know if there is any additional information that I can provide you with. By the way, we are currently using August 2011 -SP2 release, but saw this issue in older releases as well. Best regards, Henry We are currently running MonetDB August 2011 SP2 release on CentOS

1 0

[MonetDB-users] Repeatedly writing to MonetDB
by Jerry Ramey 29 Jul '12

29 Jul '12

We are encountering a frequent error when writing to MonetDb repeatedly using the Java MAPI socket. We are running the April 2012 release. The error is: [7/29/12 12:10:26 PM] Cory Isaacson: 000!TypeException:user.s0_2[2]:'calc.str' undefined in: _3:any := calc.str(_4:str) 22000!TypeException:user.s0_2[3]:'calc.str' undefined in: _5:any := calc.str(_6:str) 22000!TypeException:user.s0_2[4]:'calc.str' undefined in: _7:any := calc.str(_8:str) 22000!TypeException:user.s0_2[5]:'calc.str' undefined in: _9:any := calc.str(_10:str) 22000!TypeException:user.s0_2[6]:'calc.str' undefined in: _11:any := calc.str(_12:str) The only solution is to stop the farm and restart the farm/database. We have a single Java process writing to the database using MAPI, in batches of 1000 rows at a time (or less), using the COPY INTO syntax based on the example Java loader.We have a single Java process writing to the database using MAPI, in batches of 1000 rows at a time (or less), using the COPY INTO syntax based on the example Java loader. Here are the important snippets of the Java code. You can see that we are opening/closing a connection for each batch, and we have only a single thread performing this function, no other processes are writing to the database. We do have other connections reading from the database (but in the most recent case we only had idle connections). This is running on CentOS 5.4, with Java 1.6. There is no swap space configured on this server (we read that this may be a requirement). The data volumes are very small at this point, only a few million rows. String query = "COPY " + (dataRowList.size() + 10) + " RECORDS INTO " + tableName + " FROM STDIN USING DELIMITERS '\\t','\\n' NULL AS '\\\\N';"; if(TRACE) logger.trace("commit: query : " + query); // the leading 's' is essential, since it is a protocol // marker that should not be omitted, likewise the // trailing semicolon out.write('s'); out.write(query); out.newLine(); // Write all data rows. for(String dataRow : dataRowList) { if(TRACE) logger.trace("commit: Writing data row: " + dataRow + " dataRow ends in newline: " + dataRow.endsWith("\n")); // The dataRow is \n terminated, so no need to output newLine to output stream. out.write(dataRow); } out.writeLine(""); // need this one for synchronisation over flush() error = in.waitForPrompt(); if (error != null) throw new Exception(error); out.writeLine(""); // server wants more, we're going to tell it, this is it error = in.waitForPrompt(); if (error != null) throw new Exception(error); // // disconnect from server // server.close(); } catch (IOException e) { logger.error("Unable to connect", e); throw new RuntimeException("Unable to connect", e); } catch (Throwable th) { logger.error("Commit error", th); ... throw new RuntimeException("Commit error: tableName: " + tableName, th); } finally { try { if(server != null) { server.close(); server = null; } } catch (Throwable th) { logger.error("Error closing MonetDb server connection", th); } } Thanks, Jerry

1 0

[MonetDB-users] Repeatedly writing to MonetDB
by Jerry Ramey 29 Jul '12

29 Jul '12

We are encountering a frequent error when writing to MonetDb repeatedly using the Java MAPI socket. We are running the April 2012 release. The error is: [7/29/12 12:10:26 PM] Cory Isaacson: 000!TypeException:user.s0_2[2]:'calc.str' undefined in: _3:any := calc.str(_4:str) 22000!TypeException:user.s0_2[3]:'calc.str' undefined in: _5:any := calc.str(_6:str) 22000!TypeException:user.s0_2[4]:'calc.str' undefined in: _7:any := calc.str(_8:str) 22000!TypeException:user.s0_2[5]:'calc.str' undefined in: _9:any := calc.str(_10:str) 22000!TypeException:user.s0_2[6]:'calc.str' undefined in: _11:any := calc.str(_12:str) The only solution is to stop the farm and restart the farm/database. We have a single Java process writing to the database using MAPI, in batches of 1000 rows at a time (or less), using the COPY INTO syntax based on the example Java loader.We have a single Java process writing to the database using MAPI, in batches of 1000 rows at a time (or less), using the COPY INTO syntax based on the example Java loader. Here are the important snippets of the Java code. You can see that we are opening/closing a connection for each batch, and we have only a single thread performing this function, no other processes are writing to the database. We do have other connections reading from the database (but in the most recent case we only had idle connections). This is running on CentOS 5.4, with Java 1.6. There is no swap space configured on this server (we read that this may be a requirement). The data volumes are very small at this point, only a few million rows. String query = "COPY " + (dataRowList.size() + 10) + " RECORDS INTO " + tableName + " FROM STDIN USING DELIMITERS '\\t','\\n' NULL AS '\\\\N';"; if(TRACE) logger.trace("commit: query : " + query); // the leading 's' is essential, since it is a protocol // marker that should not be omitted, likewise the // trailing semicolon out.write('s'); out.write(query); out.newLine(); // Write all data rows. for(String dataRow : dataRowList) { if(TRACE) logger.trace("commit: Writing data row: " + dataRow + " dataRow ends in newline: " + dataRow.endsWith("\n")); // The dataRow is \n terminated, so no need to output newLine to output stream. out.write(dataRow); } out.writeLine(""); // need this one for synchronisation over flush() error = in.waitForPrompt(); if (error != null) throw new Exception(error); out.writeLine(""); // server wants more, we're going to tell it, this is it error = in.waitForPrompt(); if (error != null) throw new Exception(error); // // disconnect from server // server.close(); } catch (IOException e) { logger.error("Unable to connect", e); throw new RuntimeException("Unable to connect", e); } catch (Throwable th) { logger.error("Commit error", th); ... throw new RuntimeException("Commit error: tableName: " + tableName, th); } finally { try { if(server != null) { server.close(); server = null; } } catch (Throwable th) { logger.error("Error closing MonetDb server connection", th); } } Thanks, Jerry

1 0

[MonetDB-users] substing made monetdb very very slow
by li, xiaohua 27 Jul '12

27 Jul '12

Hi all: we are testing monetdb on a sql select substring(fact_bk,1,1) as aa,sum(1) as recordcount from f_activity group by aa the table f_activity have about 5 million rows. when i run this sql, monetdb need about 30 seconds . (if we use another no calculated column, it need only 1-3 seconds) when I run this sql on sql server, it needs only 7 seconds. is monetdb not good at calculated column? or we have made some mistake? please tell me the reason, thank you. lixiaohua Shanghai,China

2 4

[MonetDB-users] how to access remote server in windows
by li, xiaohua 26 Jul '12

26 Jul '12

Hi all: we want to copy a text file into a remote server, we don't know how to set the mapi_open = yes on windows. we try add --set "mapi_open= yes" in the M5server.bat . but we don't know whether it take effect. how can we know? when we use jdbc or odbc to access a remote monetdb database. we try to use jdbc:monetdb://dvs001:5000/demo. or change the odbc host to an ip address: 192.168.1.100. we can't connect to the database. only we use localhost can we connect to the database. does monetdb support remote access in windows? lixiaohua Shanghai,China

3 7

[MonetDB-users] can't copy into a remote server in windows
by li, xiaohua 26 Jul '12

26 Jul '12

Hi all: we want to copy a txt file into a remote monetdb (windows). if we copy into local monetdb via ipaddress, it's OK. but if we copy the same txt file into a remote monetdb, it can't. Error: could not open file 'd:\\share\\book1.csv': Operation not permitted SQLState: 22000 ErrorCode: 0 what's the problem? is it a bug? or something we don't know? lixiaohua Shanghai,China

3 3

[MonetDB-users] where is demo database physiquely in my SERVER
by DADOU 25 Jul '12

25 Jul '12

hello, i don't find in my PC the demo database, i had setup monetdb (MonetDB5-SQL-Installer-x86_64-20120607.msi) in my PC and in load the server with C:\Program Files\MonetDB\MonetDB5\M5server.bat I create user and connect with him in default database "demo", i created tables and loaded data. but i don't know where are data is stored in my machine, I want the path of database demo. thunks -- View this message in context: http://old.nabble.com/where-is-demo-database-physiquely-in-my-SERVER-tp3421… Sent from the monetdb-users mailing list archive at Nabble.com.

2 1

[MonetDB-users] COPY INTO: insufficient privileges: binary COPY INTO requires administrator rights
by 万萌 25 Jul '12

25 Jul '12

Hi guys, i got the following error when COPY INTO TABLE test: sql>COPY INTO test from ('/home/svom/log3'); COPY INTO: insufficient privileges: binary COPY INTO requires administrator rights the file "log3" is in mode 777, so i dont know why this happend, thanks for help! Wan meng SVOM, NAOC

5 6

[MonetDB-users] Billions of rows
by Benjie Chen 20 Jul '12

20 Jul '12

Hi >From one big dataset, we already have 0.7 billion rows. And we are looking at having 100 datasets eventually over the period of a year. That's 70 billion rows. From what I understand, column data are loaded into virtual address space, then dynamically indexed for querying? Does that mean that on a query "SELECT a FROM table WHERE b = N", it'd have to load all of 70 billion values of b, index, then provide results for a? Is this a case where statically created index would help? Does MonetDB use static created index? Thanks, Benjie

2 1

[MonetDB-users] CREATE AGGREGATE error
by Asterios Katsifodimos 18 Jul '12

18 Jul '12

Hello, I am trying to define an aggregate function as I have seen in an old message in this list: http://sourceforge.net/mailarchive/message.php?msg_id=20582949 I am trying to define it as: CREATE AGGREGATE group_concat (BASETYPE = text, SFUNC = _group_concat, STYPE = text); And I get this error: 42000!syntax error, unexpected '=' in: "create aggregate group_concat (basetype =" Any ideas on what I'm doing wrong? I have already defined the _group_concat function as described in the same message mentioned earlier. Running MonetDB v11.9.5 (Apr2012-SP1) on Linux. thank you, Asterios

3 4

[MonetDB-users] Announcement: New Jul2012 Feature release of MonetDB suite
by Fabian Groffen 17 Jul '12

17 Jul '12

The MonetDB team at CWI/MonetDB BV is pleased to announce the Jul2012 feature release of the MonetDB suite of programs. More information about MonetDB can be found on our website at <http://www.monetdb.org/>. For details on this release, please see the release notes at <http://www.monetdb.org/Downloads/ReleaseNotes>. As usual, the download location is <http://dev.monetdb.org/downloads/>. Jul 2012 feature release Build Environment * Created seperate RPM and DEB packages for MonetDB/JAQL. * Removed --enable-bits option from configure. * Split the MonetDB-client-ruby RPM package into two and named them in accordance with the Fedora packaging guidelines as rubygem-<gem-name>. SQL * COPY INTO now accepts optional parenthesis for file argument. Binary COPY INTO now requires 'COPY BINARY INTO'. Client Package * ODBC: Fixed a bug where SQLNativeSql expected a statment handle instead of a connection handle. MonetDB5 Server * Crackers code has been removed. Development continues in the holindex branch. Merovingian * Fixed misc memory leaks, which caused monetdbd to grow in memory size over time. * Removed erroneously (re-)added master and slave properties, this functionality is currently not working. MonetDB Common * Fixed intermittent problem that joins and selects return incorrect results and possibly inconsistent databases. The problems only occurred after a series of queries and updates, therefore it was hard to reproduce. * The sorted property, which was used to maintain whether a column in a BAT was sorted or reverse sorted, has been replaced by a pair of properties, sorted and revsorted. These new properties can be set independently (unlike the old sorted property), and so if both are set, the column must be constant. In addition, internal property checking has been overhauled. Now, when a property is set incorrectly, and when assertions are enabled, an assertion will go off. There is also a function which can derive properties. * Implemented proper overflow checking on all arithmetic operations. Bug Fixes * 3016: Overflowing double multiplication leads to incorrect result * 3091: Since Apr2012-SP1 !TypeException:user.s11_1[59]:'bat.reverse' undefined in: _89:any := bat.reverse(_86:wrd) * 3099: ambiguous attributes in query with WITH clause and EXISTS clause * 3100: charset problem with the odbc driver for windows * 3103: display_size and internal_size of Cursor.description or incorrect * 3105: Leaking using Python * 3109: Wrong order in autoload mal script causes warning on startup * 3111: memory leak, monetdb set/get command * 3112: Median over temporal domains * 3113: Cannot set value of indexed column when doing UPDATE * 3114: Foreign Key prblems * 3116: Segfault in SQL

1 0

[MonetDB-users] how to use DataCell
by Greg Aumann 17 Jul '12

17 Jul '12

I would like to start experimenting with MonetDB/DataCell but am having trouble getting started. I read M. Kersten, E. Liarou, and R. Goncalves. A Query Language for a Data Refinery Cell. In EDA-PS, 2007. In section 2.1 of that paper it has the following syntax for creating a basket: create basket X( tag timestamp default now(), id serial, payload integer ); When I input this to mclient (Apr2012-SP2 version) I get: 42000!syntax error, unexpected IDENT, expecting INDEX in: "create basket" In the source code in the file sql/test/BugTracker-2011/Tests/UDF_in_schema.Bug-2817.sql begins with: create schema datacell; create table datacell.basket_X( id integer auto_increment, tag timestamp default now(), payload integer ); This obviously resembles the above but seems like it is creating a normal table rather than a basket. Has the syntax for creating a basket changed? Or should I load/enable the datacell extension in some way? thanks Greg

2 1

[MonetDB-users] Arrays or BATs as SQL column?
by Stefan de Konink 13 Jul '12

13 Jul '12

-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA512 Is there currently any possibility to store arrays or full BATs in a MonetDB5/SQL column which can be referenced later on? I'm looking for a support "database native" serialisation. Stefan -----BEGIN PGP SIGNATURE----- Version: GnuPG v2.0.18 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEAREKAAYFAlAAGyUACgkQYH1+F2Rqwn0mgwCdEBGeN+ULO0wH3nOG5SVhuna8 OqwAniKRa9qq6pr5pUnr90m+9MtlWbtH =XLkD -----END PGP SIGNATURE-----

2 3

[MonetDB-users] MonetDB connector for Matlab
by BOOM Bas 13 Jul '12

13 Jul '12

Hi, I developed a monetdb connector for matlab based on the mysql connector for matlab. If you have comments or suggestion how to improve it let me know. Cheers, Bas Below are the instruction how to compile and use the connector (hopes this is clear) Install Monetdb: ./configure --prefix=/usr/local/MonetDB --exec-prefix=/usr/local/MonetDB make make install Linux (not tested for Window yet): In matlab mex -I/usr/local/MonetDB/include/monetdb -L/usr/local/MonetDB/lib -lmapi monetdbv2.cpp monetdbv2(host,user,password,dbName,'OPEN'); monetdbv2(host,user,password,dbName,'INSERT INTO table VALUES (value1, 1)' ); [value1 value2] = monetdbv2(host,user,password,dbName,'SELECT * FROM table' ); monetdbv2(host,user,password,dbName,'CLOSE'); Simple matlab interface to MonetDB server Bastiaan Boom, University of Edinburgh, 13 July 2012 Based on the MySQL connector of Matlab (www.mathworks.com/matlabcentral/fileexchange/8663-mysql-database-connector) -- The University of Edinburgh is a charitable body, registered in Scotland, with registration number SC005336.

1 0

[MonetDB-users] Master-slave replication support
by Tapomay Dey 12 Jul '12

12 Jul '12

I found monetdb options in the docs to setup master and slave. However no documentation explaining what to do after setting the properties. I have an older monetdb setup with lot of data, set master=yes. I installed monetdb on another machine(nightly build says slave option not found, so using stable build for the new installation) set slave=mapi:monetdb://host:port/db I have no idea what to do next. I saw http://www.monetdb.org/Documentation/Cookbooks/Recipes/Cluster, but that's not what I want. I am guessing its a work in progress. If it is so, are you planning to release it in July? Regards, Tapomay.

3 3

Re: [MonetDB-users] MonetDB Python client
by Alexey Mints 12 Jul '12

12 Jul '12

Dear Fabian! The problem appears with "classical" mapi2.py when server drops the connection without closing it correctly. This happens with some incorrect SQLs (sometimes error diagnostics simply fails). recv(bytes) hangs in this case. I have faced this problem before with sockets, and the only solution I know so far is select() method. I raise socket.error only in the case of such hard connection failure. So it does not seem too harsh. At least it is better than thread-blocking. Regards, Alexey Mints

2 1

[MonetDB-users] Scale
by Benjie Chen 11 Jul '12

11 Jul '12

Hi We are currently setup to use MonetDB to capture data from our mass spec machines. We generate about 30GB of data per day, so looking at a few TB of data per year. We are curious to hear what others who have used MonetDB, handle storage scaling. What kind of hardware do you use? Is there distributed/federated support in MonetDB? Are there map/reduce techniques you've used over the data, etc. Thanks, Benjie

1 0

[MonetDB-users] mserver crash under load -16G data, 10 queries each from 2 remote clients
by Tapomay Dey 10 Jul '12

10 Jul '12

I have a database with Single table. 15 million rows. 16G of data. 58 columns. MonetDB stores it consuming 32G of data. mserver Machine specs: Main memory: 4G Processor: Intel(R) Xeon(R) CPU E5645 @ 2.40GHz. Single core. I am performing a test to see how much time it takes if I fire this query multiple times from different machines. Case 1: I fire an aggregation query 10 times from a remote machine with 5sec interval between each firing. The result hs 17000 rows with around 4 MB of data. Memory usage of mserver process reahes a peak of 73% mclients finish within 43 to 48 seconds. Case 2: I do the same as above but now from 2 remote machines simultaneously. Memory usage of mserver process reahes a peak of 73% again. It keeps fluctuating between 70 and 73%. But after some time mserver restarts and the clients terminate with all the 10 clients on each of both machines saying: ERROR = !Connection terminated MAPI = tapomay@54.251.11.181:3306 ACTION= read_line QUERY = select id1, id2, id3, id4, id5, count(*), sum(metric1), avg(metric2), sum(metric3), sum(metric4), sum(metric5), sum(metric6), sum(metric7), sum(metric8), sum(metric9), sum(metric9), sum(metric10), sum(metric11), sum(metric12), sum(metric13) from tablename group by id1, id2, id3, id4, id5; Each query terminates within 5 to 50 seconds with the above error. In both cases intial state of mserver consumes around 0.5% memory. Also tested the same under other competing columnar DBs. They complete the case 2 in around 350 or more seconds average. They complete case 1 in 200 seconds average. Following is a last few lines of tail of merovingian.log 2012-07-05 06:30:42 MSG merovingian[31153]: proxying client 1.compute.amazonaws.com:45476 for database 'tablename' to mapi:monetdb:/$ 2012-07-05 06:30:42 MSG merovingian[31153]: target connection is on local UNIX domain socket, passing on filedescriptor instead of proxying 2012-07-05 06:30:45 MSG merovingian[31153]: proxying client 2.amazonaws.com:46225 for database 'tablename' to mapi:monetdb:///mnt/tapomay$ 2012-07-05 06:30:45 MSG merovingian[31153]: target connection is on local UNIX domain socket, passing on filedescriptor instead of proxying 2012-07-05 06:30:47 MSG merovingian[31153]: proxying client 1.compute.amazonaws.com:45477 for database 'tablename' to mapi:monetdb:/$ 2012-07-05 06:30:47 MSG merovingian[31153]: target connection is on local UNIX domain socket, passing on filedescriptor instead of proxying 2012-07-05 06:30:50 MSG merovingian[31153]: proxying client 2.amazonaws.com:46226 for database 'tablename' to mapi:monetdb:///mnt/tapomay$ 2012-07-05 06:30:50 MSG merovingian[31153]: target connection is on local UNIX domain socket, passing on filedescriptor instead of proxying 2012-07-05 06:30:52 MSG merovingian[31153]: proxying client 1.compute.amazonaws.com:45478 for database 'tablename' to mapi:monetdb:/$ 2012-07-05 06:30:52 MSG merovingian[31153]: target connection is on local UNIX domain socket, passing on filedescriptor instead of proxying 2012-07-05 06:30:55 MSG merovingian[31153]: proxying client 2.amazonaws.com:46241 for database 'tablename' to mapi:monetdb:///mnt/tapomay$ 2012-07-05 06:30:55 MSG merovingian[31153]: target connection is on local UNIX domain socket, passing on filedescriptor instead of proxying 2012-07-05 06:30:59 MSG merovingian[31153]: database 'tablename' (1048) was killed by signal SIGKILL Is there a config that would ask mserver to not kill itself and keep processing at the cost of query execution time. A few more tens of seconds won't harm much. Thanks and Regards, Tapomay.

4 10

Re: [MonetDB-users] MonetDB Python client
by Alexey Mints 09 Jul '12

09 Jul '12

Hi, Fabian! Yeh, I have shifted try outside of loop. I am not a big expert in this, but does it make any difference? Alexey.

2 1

[MonetDB-users] Expected query to be much faster, but is actually two orders of magnitude slower!
by Shahbaz 05 Jul '12

05 Jul '12

Hi All, I did some experiments with monetdb over the weekend and found some strange and unexpected behavior. My database has a single table which stores stock trades. The table is defined as follows: create table trades( td varchar(2), // just a dummy field date int, time int, ticker varchar(17), price double, qty int, vol int, conditioncode int, exch varchar(8)); This table has a little under 1 Billion rows and takes up around 52 gigabytes. The source data is also around 48 gigabytes. I expected monetdb to aggressively compress the columns and reduce the disk usage by 10 fold or so! In any case, that is not the main issue. [select sum(date), sum(time), count(ticker) from trades where date = 20120501;] This query took 1.1 seconds the first time and around 173 ms the second two times (count(ticker) returned 31,268,285). [select sum(date), sum(time), count(ticker) from trades where date = 20120501 and time > 34200000;] This query took about 250 ms the first, second and third times I ran it. count(ticker) returned 31,176,190. (I doing the same thing as previous query, but asking to ignore all trades before the market opened...obviously very very few trades occur before the market opens). [select sum(date), sum(time), count(ticker) from trades where date = 20120501 and time > 34200000 and time < 37800000;] This query took 1 minute and 15 seconds! The second time I ran it, it took 1 minute and 11 seconds. Notice that count(ticker) in this query only returned 5,969,337. This query is essentially the same as the previous one, but now I am only looking to process trades between 9:30 am and 10:30 am. So this query processes 1 hours' worth of trades while previous queries process the whole day. Notice that all three queries process the same columns. Between the second and third columns, all the columns used in the select clause and where clause are exactly the same. The second query just add an additional filter on the time column. Why is the third query two orders of magnitude slower than the second query? This experiment was done on an ubuntu server: >uname -a Linux abcdefg 3.2.0-25-generic #40-Ubuntu SMP Wed May 23 20:30:51 UTC 2012 x86_64 x86_64 x86_64 GNU/Linux >cat /proc/meminfo |fgrep Mem MemTotal: 8168280 kB MemFree: 3588848 kB >hdparm -t ...disk... Timing buffered disk reads: 510 MB in 3.00 seconds = 169.80 MB/sec Please let me know if you require more information. Thanks, Shahbaz

4 3

[MonetDB-users] MonetDB Python client
by Alexey Mints 05 Jul '12

05 Jul '12

Hello! I had a problem with Python client hanging up if MonetDB connection was closed unexpectedly. This happened for example, when I have passed a request that caused "ERROR = !Connection terminated" in mclient. Such things should not occur in a ideal life, but they do =( The problem was that socket.recv cannot handle this situation properly. The solution is to use select.select statement. I attach the diff to mapi2.py file that solves the problem. Regards, Alexey

2 1

[MonetDB-users] importing data from csv
by Flavio Coelho 30 Jun '12

30 Jun '12

I am trying to import data from a CSV file separated by ';' and with string values surrounded by double quotes: " but I keep getting the following error messages: missing separator ';' line 225387 expecting 8 got 7 fields missing separator ';' line 75129 expecting 8 got 7 fields missing separator ';' line 0 expecting 8 got 7 fields missing separator ';' line 450774 expecting 8 got 7 fields missing separator ';' line 300516 expecting 8 got 7 fields missing separator ';' line 150258 expecting 8 got 7 fields missing separator ';' line 525903 expecting 8 got 7 fields missing separator ';' line 375645 expecting 8 got 7 fields failed to import table I am using the following command: copy 300000000 offset 2 records into mobilidade.ligacoes from '/Users/fccoelho/Documents/dados_mobilidade/bilhetagem.txt' using delimiters ';','\n','"' ; after creating the table like this: sql dump of my table: CREATE TABLE "mobilidade"."ligacoes" ( "id" INTEGER NOT NULL DEFAULT next value for "mobilidade"."seq_5840", "DT_INI_ATIVIDADE_REDE" VARCHAR(10), "HR_INI_ATIVIDADE_REDE" VARCHAR(7), "NU_SETOR_CELULA_ORIG" INTEGER, "NU_AREA_CELULA_ORIG" INTEGER, "NU_DDD_ORIG" INTEGER, "NU_DDD_DESTINO" INTEGER, "CD_TELEFONE_ORIG_ALTER" INTEGER DEFAULT NULL, "CD_TELEFONE_DESTINO_ALTER" VARCHAR(16) DEFAULT NULL, CONSTRAINT "ligacoes_id_pkey" PRIMARY KEY ("id") ); Naturally, I checked line 0 and it does have 8 fields. What am I doing wrong? -- Flávio Codeço Coelho

2 2

[MonetDB-users] COPY INTO
by DADOU 29 Jun '12

29 Jun '12

COPY INTO test from '/tmp/bla'; COPY INTO: insufficient privileges: COPY INTO from file(s) requires administrator rights, use 'COPY INTO "test" FROM STDIN' instead can you help me -- View this message in context: http://old.nabble.com/COPY-INTO-tp34088907p34088907.html Sent from the monetdb-users mailing list archive at Nabble.com.

3 2

[MonetDB-users] COPY INTO
by DADOU 29 Jun '12

29 Jun '12

I have a Problem with copy into copy into "LINEORDER" from ('E:\TEST.tbl'); COPY INTO: insufficient privileges: binary COPY INTO requires administrator rights TEST.tbl is a file with 6 000 000 rows. these a format for two rows in file TEST.tbl 1|1|7381|155190|4137|19960102|TRUCK| 1|2|7381|67310|815|19960102|MAIL| can help me please -- View this message in context: http://old.nabble.com/COPY-INTO-tp34088847p34088847.html Sent from the monetdb-users mailing list archive at Nabble.com.

2 1

[MonetDB-users] Basic sum aggregation returning incorrect value
by Tapomay Dey 28 Jun '12

28 Jun '12

I performed the following operations sequentially. Please look at the result when I use SUM. Sum values are incorrect. They should be 60/600/6000/60000.AVG returns correct values though. What could be going wrong? Is it something I can fix? sql> sql>create table table1 (id int, value int); operation successful (64.427ms) sql>create table table2 (id int, value int); operation successful (78.679ms) sql>insert into table1 values(1, 10); 1 affected row (25.389ms) sql>insert into table1 values(1, 20); 1 affected row (28.254ms) sql>insert into table1 values(1, 30); 1 affected row (27.512ms) sql>insert into table1 values(2, 300); 1 affected row (28.255ms) sql>insert into table1 values(2, 200); 1 affected row (28.658ms) sql>insert into table1 values(2, 100); 1 affected row (29.606ms) sql>insert into table2 values(1, 1000); 1 affected row (25.144ms) sql>insert into table2 values(1, 2000); 1 affected row (26.130ms) sql>insert into table2 values(1, 3000); 1 affected row (27.490ms) sql>insert into table2 values(2, 30000); 1 affected row (18.623ms) sql>insert into table2 values(2, 20000); 1 affected row (40.441ms) sql>insert into table2 values(2, 20000); 1 affected row (29.716ms) sql>insert into table2 values(2, 10000); 1 affected row (25.907ms) sql>select t1.id, sum(t1.value) as v1, sum(t2.value) as v2 from table1 t1, table2 t2 where t1.id=t2.id group by t1.id; +------+------+--------+ | id | v1 | v2 | +======+======+========+ | 1 | 180 | 18000 | | 2 | 2400 | 240000 | +------+------+--------+ 2 tuples (1.769ms) sql>select t1.id, avg(t1.value) as v1, avg(t2.value) as v2 from table1 t1, table2 t2 where t1.id=t2.id group by t1.id; +------+--------------------------+--------------------------+ | id | v1 | v2 | +======+==========================+==========================+ | 1 | 20 | 2000 | | 2 | 200 | 20000 | +------+--------------------------+--------------------------+ 2 tuples (2.275ms) sql> Regards, Tapomay.

2 1

[MonetDB-users] last inserted id
by Tomas Mikula 26 Jun '12

26 Jun '12

Hi, how can I get the ID (value of an AUTO_INCREMENT column) of the last inserted row, from within an SQL script? I am able to get it through the Python API, so I suppose there is a way to get it inside an SQL script, too. Thanks, Tomas

2 1

[MonetDB-users] strange decrease in performance after some deletes
by Majid Azimi 25 Jun '12

25 Jun '12

Hi guys, I have loaded 80 million records in database. a very complex aggregate query(multiple COUNT, SUM, AVG, GROUP BY combined) runs deadly fast in about 30 seconds. But after deleting 700 records performance has been decreased alot. the same query runs in 8 minutes now. Is this usual? Thank you

1 0

[MonetDB-users] Changing SysV parameteres
by Majid Azimi 19 Jun '12

19 Jun '12

Hi guys, Does changing SysV parameters such as SHMMAX, SHMMIN, SHMSEG , ... affects MonetDB performance? How they affect? I mean the vanilla installation of MonetDB on a very powerful hardware needs massive optimization like what we see on other relational DBMS products?

1 0

[MonetDB-users] Announcement: New Apr2012-SP2 Bugfix release of MonetDB suite
by Sjoerd Mullender 19 Jun '12

19 Jun '12

The MonetDB team at CWI/MonetDB BV is pleased to announce the Apr2012-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/>. Apr 2012-SP2 bugfix release Client Package * Resolved a cosmetical error where tab-characters would cause misalignments in tabular result views. For the time being, tabs are now represented as a single space in tabular view. MonetDB Common * Limit number of detected CPU cores to 16 on 32-bits systems to avoid running quickly out of addressable resources followed by a kill from the OS. Bug Fixes * 2594: NULL cannot automatically be casted to every other type * 3085: assertion fails upon execution of a valid SQL query * 3086: Modulo function does not work in select from table * 3087: query causes "Not yet implemented" message * 3089: mserver5 crashed if join condition has two column and incorrect order * 3090: crashed if using single identifier for where condition * 3096: Type of median error

1 0

Re: [MonetDB-users] python-monetdb and pip
by Alexey Mints 18 Jun '12

18 Jun '12

Hallo! As far as Python question has been raised. There is a tiny thing that is (probably) missing in the client. Connection class (and other classes as well) are not inherited from the "object" class, therefore further inheritance and method overriding is problematic. I think, this might be corrected in future. Regards, Alexey Mints

3 2

[MonetDB-users] Locality Sensitive Hashing method for Image Retrieval
by BOOM Bas 18 Jun '12

18 Jun '12

Hi, Sorry, I am new to this mailinglist and not sure this question fits, but have I question how to optimize my queries I am experimenting with monetdb for image retrieval, having an image database of 300000 image. At the moment, I am using the Locality Sensitive Hashing method and because I understood from the monetdb documenation that monetdb supports indexing/hash tables, which makes the implementation quiet easy in monetdb. I have for each image 30 buckets with 24 bit strings (int) (should be indexed, assume automatically done by monetdb) I have for each image a feature vector of 1000 real values. I have for each image a unique id together with some info like file name. At the moment, my table is as follows image_id, filename, bucket_1, bucket_2, bucket_3 ... bucket_30, feature_value_1, feature_value_2, ..., feature_value_1000 For Locality Sensitive Hashing, I am performing the following sql query: select * from table where bucket_1 = bitstring or bucket_2 = bitstring or bucket_3 = bitstring or ... bucket_30 = bitstring (limit 1000) These bitstrings are quiet unique, giving me around the 500 images from the entire database. I would also like to obtain the feature vector (so all feature values). At the moment, query and data retrieval times depends but often take around 0.5-1 minute, what are ways to improve this. Does using 2 tables help or storing the feature vector in one column? Ideas are welcome. I am using the MAPI client to retrieve the data. Happy to provide more information if necessary Cheers, Bas Boom University of Edinburgh part of Fish4Knowledge consortium -- The University of Edinburgh is a charitable body, registered in Scotland, with registration number SC005336.

1 0

[MonetDB-users] charset problem with the odbc driver for windows
by liangmeng 18 Jun '12

18 Jun '12

hello, all, i installed monetdb in windows server 2008(64bit) , and installed the odbc for windows( 32bit intalled firstly, and 64bit the next), after insert some Chinese characters, i use oracle biee to show the table data, but biee can't show all the characters, just like below: 1��monetdb data showed in biee i also install mysql to test this, the database character set is utf8 too, and biee can show the data correctly: 2��mysql data showed in biee the data showed in command line tool are the same from monetdb and mysql monetdb command line tool return: sql>select * from test2; +------+ | nam | +======+ | 广州 | | 梅州 | +------+ 2 tuples (2.189ms) mysql commad line tool return: mysql> select * from test; +--------+ | nam | +--------+ | 广州 | | 梅州 | +--------+ 2 rows in set (0.00 sec) Does this mean that: the odbc driver of monetdb has some problems?

2 1

[MonetDB-users] monetdb--query is very slow on table about 1 billion rows
by liangmeng 16 Jun '12

16 Jun '12

hi, guys, i create a simple table , and the table is up to 1 billion rows, its structure as follow: create table test(Month_ID int not null, C_NAME varchar(10) not null, C_ID int not null, MSISDN varchar(15) not null, C_FLUX double not null); and the data looks like: “201201，name1, 123213, 13723213213, 12.12321" when i execute the query "select c_name,c_flux from test where c_id=**** order by c_flux desc", it takes more than 3 minute to get the result(more than 100 thousands rows returned, if limit 100 is used, the execute time is about 40s), is there any ways to optimize? Hardwares: intel xeon 2.12*8, 32G memory, 4*1T SATA(raid 0)

2 3

[MonetDB-users] How to see what query is running? kill long running queries?
by Majid Azimi 15 Jun '12

15 Jun '12

Hi guys, Is it possible to see what queries are running on the database? How to kill a query? I have a issued a SELECT * query and then looked at sys.querylog and sys.queryhistory, But they are empty. Is there any solution?

4 7

[MonetDB-users] RDF3x to MonetDB
by Hancel Gonzalez 15 Jun '12

15 Jun '12

Hi people, i have installed rdf-3x <http://www.mpi-inf.mpg.de/~neumann/rdf3x/>. This software have *buildmonetdb *that is for translate rdf queries to monetdb. The problem is that i dont know which arguments i have send in command line. I appreciate your help! Greettings, Hancel Gonzalez.

3 2

[MonetDB-users] monetdb do not release the memory
by liangmeng 15 Jun '12

15 Jun '12

hi, everyone i'am new here, i install monetdb of windows 64bit version, after copy into table from several very large files(about 10G each), i found that monetdb occupied too much momery, almost 100% of total 32G , and it keeps occupying, after 24hours, it still don't release the memory, what's the matter? Also, i found the same problem after query several times with large amount of records returned;

4 6

[MonetDB-users] how to find the foreign key relationships
by Molenaar, Gijs 14 Jun '12

14 Jun '12

Hi monetdb people, How do I find all foreign key relationships with MonetDB using SQL? I now have: "select idxs.name, tables.name from sys.tables, sys.idxs where tables.id = idxs.table_id and idxs.type=1;" But that doesn't give me the related table. Any suggestion? I can't find documentation of the sys tables on the website, does it exist? Thanks, -- Gijs Molenaar http://www.astro.uva.nl/people/gijs-molenaar/

3 7

[MonetDB-users] python-monetdb and pip
by Molenaar, Gijs 14 Jun '12

14 Jun '12

Hi Monetdb people, python-monetdb is now also in the pypi repo, which means you can easily install it with pip: $ pip install python-monetdb more info: http://pypi.python.org/pypi/python-monetdb/11.9.5 -- Gijs Molenaar http://www.astro.uva.nl/people/gijs-molenaar/

1 0

[MonetDB-users] python-monetdb and pip
by Molenaar, Gijs 13 Jun '12

13 Jun '12

Hi Monetdb people, python-monetdb is now also in the pypi repo, which means you can easily install it with pip: $ pip install python-monetdb -- Gijs Molenaar http://www.astro.uva.nl/people/gijs-molenaar/

1 1

Re: [MonetDB-users] hold cursors over commit in JDBC
by Majid Azimi 12 Jun '12

12 Jun '12

Let me ask it this way: How can we configure driver so it doesn't fetch all records at one time. for example 100 records per each fetch, So the application server doesn't get OutOfMemoryException. Thank you. >> hi guys, >> >> I have a very large table, which I should scan it and generate reports. My code is like this: >> >> Class.forName("nl.cwi.monetdb.jdbc.MonetDriver"); >> Connection conn = DriverManager.getConnection("jdbc:monetdb://localhost/online", "monetdb", "monetdb"); >> >> conn.setAutoCommit(false); >> conn.setHoldability(ResultSet.HOLD_CURSORS_OVER_COMMIT); >> >> Statement stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY); >> stmt.setFetchSize(10); >> >> ResultSet rs = stmt.executeQuery("SELECT * FROM denorm"); >> >> while (rs.next()) { >> /* process line */ >> } >> >> conn.commit(); >> >> I do not use much ram inside while loop. I get OutOfMemeryException from JVM. >> So I think the driver ignores HOLD_CURSORS_OVER_COMMIT and fetches all records. Is there any solution? > HOLD_CURSORS_OVER_COMMIT means it keeps the cursor open, right? So what > else can the driver do but keep them open (including the memory > associated to that)? What would you have expected to happen with this > setting?

2 1

[MonetDB-users] hold cursors over commit in JDBC
by Majid Azimi 12 Jun '12

12 Jun '12

hi guys, I have a very large table, which I should scan it and generate reports. My code is like this: Class.forName("nl.cwi.monetdb.jdbc.MonetDriver"); Connection conn = DriverManager.getConnection("jdbc:monetdb://localhost/online", "monetdb", "monetdb"); conn.setAutoCommit(false); conn.setHoldability(ResultSet.HOLD_CURSORS_OVER_COMMIT); Statement stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY); stmt.setFetchSize(10); ResultSet rs = stmt.executeQuery("SELECT * FROM denorm"); while (rs.next()) { /* process line */ } conn.commit(); I do not use much ram inside while loop. I get OutOfMemeryException from JVM. So I think the driver ignores HOLD_CURSORS_OVER_COMMIT and fetches all records. Is there any solution?

2 1

[MonetDB-users] Setting up replication
by Majid Azimi 12 Jun '12

12 Jun '12

Hi guys, I', trying to configure replication on single node(different ports). What I did is: 1. I have created 2 dbfarms 2. set port=50000 for master node and set port=50001 for slave node 3. set control=yes for both 4. set passphrase=master for master node and set passphrase=slave for second node My master node has already data in it, I just changed the configuration. then: moentdb -p 50000 set master=yes maindb --> for master node monetdb -p 50001 set slave=mapi:monetdb://127.0.0.1:50000/maindb slavedb --> on slave node(slavedb is empty database) Now there is no data in slave node. Can anyone help?

2 1

[MonetDB-users] Server socket stays 'open' after crash
by Stefan de Konink 11 Jun '12

11 Jun '12

Instead of using monetdbd I am using a very simple while [ 1 ]; do monetdb; sleep 2; done command to restart the database after an assertion failed or segmentation fault. For some reason even after a crash MonetDB complains at the start of the application that the port is already in use. Would could -unix wise- be the reason for this, that even after a crash and the program has been terminated resources are still claimed for the terminated program? Stefan

2 3

[MonetDB-users] MonetDB Clustering
by Majid Azimi 11 Jun '12

11 Jun '12

Hi guys, What types of clustering does monetdb support? Sharding? Master/Slave? if it is Master/Slave does it support multi master? or all writes must go to master node? In clustering documentation (http://www.monetdb.org/Documentation/Cookbooks/Recipes/Cluster) there is paragraph: willem% monetdb -h cruquius -P mypassphraseset shared=pump/2/harvey pump willem% monetdb -h lijnden -P mypassphraseset shared=pump/2/fox pump willem% monetdb -h leeghwater -P mypassphraseset shared=pump/1/harvey pump What are those 1, 2, fox, harvey?

2 1

[MonetDB-users] monetdb clustering
by Majid Azimi 10 Jun '12

10 Jun '12

Hi guys, What types of clustering does monetdb support? Sharding? Master/Slave? if it is Master/Slave does it support multi master? or all writes must go to master node? In clustering documentation (http://www.monetdb.org/Documentation/Cookbooks/Recipes/Cluster) there is paragraph: willem% monetdb -h cruquius -P mypassphraseset shared=pump/2/harvey pump willem% monetdb -h lijnden -P mypassphraseset shared=pump/2/fox pump willem% monetdb -h leeghwater -P mypassphraseset shared=pump/1/harvey pump What are those 1, 2, fox, harvey?

1 0

[MonetDB-users] Install MonetDB in CentOS 5
by Hancel Gonzalez 08 Jun '12

08 Jun '12

Hi people, i'm new here and i hope your help. I wanna install MonetDB in a computer with CentOS 5: download rpm packages from http://code.google.com/p/monetdb-rhel-5/downloads/list but i not work. Then, i tried compile the sourcecode http://dev.monetdb.org/downloads/sources/Latest/MonetDB-11.9.5.zip, command ./configure work but when i 'make' i have error of compilation. I dont know what to do! Please help me! Greetings, Hancel Gonzalez.

2 1

[MonetDB-users] Djonet
by Gijs Molenaar 08 Jun '12

08 Jun '12

Hi, I've updated the MonetDB Django backend created by Mark Bucciarelli and renamed it to Djonet. https://github.com/gijzelaerr/djonet basic stuff is working, but many Django tests are still failing, which i'm working on. Still it is already quite usable. If people want to help make it better, let me know. -- Gijs Molenaar http://www.astro.uva.nl/people/gijs-molenaar/

5 5

[MonetDB-users] Any plan to support Ubuntu Server 12.04
by Majid Azimi 28 May '12

28 May '12

hi guys, Is there any plan to build binaries for Ubuntu server 12.04(Precise Pangolin)? I didn't find debs for this release from repository?

2 1

[MonetDB-users] Changing time zone after getting GMT time from database
by Majid Azimi 27 May '12

27 May '12

Hi guys, I have MonetDB database with table containing user table: CREATE TABLE user ( birth_date TIMESTAMP NOT NULL ); birth_date is saved in GMT without DST. (This is the default behavior of MonetDB). So I should change the TimeZone in my application. Here is my code: Class.forName("nl.cwi.monetdb.jdbc.MonetDriver"); Connectioncon =DriverManager.getConnection("jdbc:monetdb://localhost/online","monetdb","monetdb"); Statementst =con.createStatement(); ResultSetrs; Calendar c = Calendar.getInstance(TimeZone.getTimeZone("UTC")); rs =st.executeQuery("SELECT * FROM user"); while(rs.next()){ c.setTime(rs.getTimestamp("birth_date")); c.setTimeZone(TimeZone.getTimeZone("Asia/Tehran")); System.out.println(c.get(Calendar.YEAR)+"-"+c.get(Calendar.MONTH)+"-"+c.get(Calendar.DAY_OF_MONTH)+" "+c.get(Calendar.HOUR_OF_DAY)+":"+c.get(Calendar.MINUTE)+":"+c.get(Calendar.SECOND)); } But there is no difference between timestamps in database and timestamps after converting time zone in application. Could anyone help? Is this the wrong way to convert TimeZone? I'm using MonetDB version 11.9.5-20120516 on Debian 6 with openjdk 6. Here is the monetdbd getall /home/dbfarm: dbfarm /home/dbfarm/ status monetdbd[4187]1.6(Apr2012-SP1)is serving thisdbfarm mserver /usr/bin/mserver5 logfile /home/dbfarm//merovingian.log pidfile /home/dbfarm//merovingian.pid sockdir /tmp port 50000 exittimeout 60 forward proxy discovery yes discoveryttl 600 control yes passphrase {SHA512}ba3253876aed6bc22d4a6ff53d8406c6ad864195ed144ab5c87621b6c233b548baeae6956df346ec8c17f5ea10f35ee3cbc514797ed7ddd3145464e2a0bab413 mapisock /tmp/.s.monetdb.50000 controlsock /tmp/.s.merovingian.50000

2 1

[MonetDB-users] Tuning MonetDB
by Majid Azimi 27 May '12

27 May '12

Hi guys, As MonetDB does rely on OS features to be tuned, Which settings should we optimize on OS specially on Linux? 1. Tuning network a. using jumbo b. socket buffer space c. TCP setting 2. Tuning VM a. huge page b. swappiness 3. Tuning File system a. which file system? ext3? ext4? XFS? b. using large blocks if database is almost read only does using ext2 make any benefit?

2 1

[MonetDB-users] Announcement: New Apr2012-SP1 Bugfix release of MonetDB suite
by Fabian Groffen 25 May '12

25 May '12

The MonetDB team at CWI/MonetDB BV is pleased to announce the Apr2012-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/>. Apr 2012-SP1 bugfix release Build Environment * Windows: we now use OpenSSL 1.0.1b. SQL * Databases that were upgraded from the Aug2011 release have an error in the catalog for SQL procedures. This is now fixed. Merovingian * Fixed a bug where connecting to a stopped multiplex-funnel would result in a 'there are no available connections' error. MonetDB Common * Implemented MT_getrss for Mac OS X systems, this allows the server to know about how much memory is currently in use. Bug Fixes * 2454: SQL: add various statistical functions * 2916: non admin user cannot create temporary tables * 2949: SQL exception during execution of a correct user defined function that just returns a relation table constructed by a simple SQL select query * 2958: Grant does't work with schema syntax * 2960: mclient terminates connection during insertion of a large number of records from a file * 2972: SQL URL functionality contains errors * 2987: Broken type inference when using sql round * 2992: SQL module truncates DECIMALS when passing integers to MAL module * 3000: Inconsistency between read-only and read-write modes after dropping a schema * 3028: Segmentation fault in seqscan_eq_bte_bte_tloc_void_void * 3046: mserver5 terminates with segmentation fault * 3063: datacell functions no longer work after restart of database * 3072: Database upgrade from Dec2011 to Apr2012 broken for storage() * 3073: VERY slow foreign key check (due to missing batcalc.isnil() ?) * 3074: Name resolution in procedures * 3077: No errors on UDF returning table with too many columns * 3079: Converting an Oracle schema DDL for MonetDB fails to create a column named "no".

1 0

Re: [MonetDB-users] Using monetdb and sqlalchemy
by Sabst 23 May '12

23 May '12

Still stuck. If you know the answer please share it. Also if you think the two are not meant to work together, share it too. Anyone? > ----- Message transféré de sabst(a)free.fr ----- > Date : Thu, 10 May 2012 15:07:17 +0200 > De : sabst(a)free.fr > Adresse de retour :sabst@free.fr > Sujet : Using monetdb and sqlalchemy > À : monetdb-users(a)lists.sourceforge.net > > Hi, > I am just starting using monetdb and, even if I could find discussions about > Python/SqlAlchemy integration, I could not make it work. > > I installed python-monetdb module and have a running monetdb server. > > Now I expect my client application to work with monetdb as it does with > postgresql and instead of passing a connection string like: > postgres://user:password@somehost:port/db > I use: > monetdb://user:password@somehost/db > > But I get this error: > ArgumentError: Could not determine dialect for 'monetdb'. > > What are the steps required to integrate monetdb into SqlAlchemy? > > Certainly missing the obvious but I'm currently stuck. > > Thank you in advance for any help, > Stephane. > > ----- Fin du message transféré ----- > > > >

2 4

[MonetDB-users] Monet on SUSE
by Hualin Wang 20 May '12

20 May '12

Dear All, I have trouble compliing the Monet on my SUSE 11 box. The server has an old PCRE installed, like 7.8. I download my own version of pcre 8.3, and complie it with utf-8 enabled. I set up the following three environment vars: pcre_LIBS, pcre_CFLAGS, PKG_CONFIG_PATH to point to the right place. But the Monet ./configure still complain utf-8 support no. By the way, the pcre check is yes. Which version of PCRE package I need install on SUSE? By the way, what this sentence means from monetdb.org "Make sure you install the xxx-dev versions on binary distributions of the necessary packages as well!" Thanks for any help/hints! Best Regards, Hualin

2 1

[MonetDB-users] Bad SELECT performance after deletion of 1 record
by suncpp 17 May '12

17 May '12

Hi everyone, I had added about 30,000,000 records in one MonetDB table. The performance of executing SELECT-queries was really great. But after the deletion of only 1 record from the table, the performance had slowed down considerably. I have seen the topics here with alike problem but I have not found the solution. Could anyone help me to understand what the problem is? -- View this message in context: http://old.nabble.com/Bad-SELECT-performance-after-deletion-of-1-record-tp3… Sent from the monetdb-users mailing list archive at Nabble.com.

2 2

[MonetDB-users] strange trigger behaviour
by Majid Azimi 16 May '12

16 May '12

Hi guys, I have created a table named users: CREATE TABLE names ( name VARCHAR(255) NOT NULL ); another table named names2 which is same as users. only table names are different. I have created a after insert trigger like this: CREATE TRIGGER trg_names_ai AFTER INSERT ON names REFERENCING NEW ROW AS new_row FOR EACH ROW WHEN new_row.name = '<some name>' INSERT INTO names2 VALUES(new_row.name); The problem is it doesn't care about WHEN clause. Any INSERT query will appear in names2. Can anyone help?

2 1

[MonetDB-users] timeout while waiting for database 'xxx' to initialise the sql scenario
by Brian J. Cardiff 11 May '12

11 May '12

Hi all, I'm trying monetdb among with other vertical databases. After some experiments I am unable to start the database again. I'm not sure what the things I did cause the issue, but happens twice already. I have restart monetdbd and server without luck. Is there any way to solve this issue? $ monetdb start xxx starting database 'xxx'... FAILED start: starting 'xxx' failed: timeout while waiting for database 'xxx' to initialise the sql scenario The experiments consist on: 1. Bulk load .csv file with ~ 20*10^6 records 2. Do some aggregate functions with "SELECT AVG(..) FROM TBL WHERE ID IN (...)" with different amount of ids in list (10 - 5000) (yeap, ugly). 3. Add columns to TBL, Massive update columns in TBL, Drop columns in TBL. [version] MonetDB Database Server Toolkit v1.0 (Apr2012) MonetDB Database Server v1.6 (Apr2012) [morovingian.log] 2012-05-11 15:05:55 MSG xxx[637]: # Listening for UNIX domain connection requests on mapi:monetdb:///Users/bcardiff/Work/Xxx/db/xxx/.mapi.sock 2012-05-11 15:05:55 MSG xxx[637]: # MonetDB/GIS module loaded 2012-05-11 15:05:55 MSG xxx[637]: # MonetDB/SQL module loaded 2012-05-11 15:06:05 MSG merovingian[588]: sending process 637 (database 'xxx') the TERM signal 2012-05-11 15:06:05 MSG merovingian[588]: database 'xxx' has shut down 2012-05-11 15:06:05 MSG merovingian[588]: database 'xxx' (637) has exited with exit status 0 2012-05-11 15:06:05 ERR control[588]: (local): failed to fork mserver: timeout while waiting for database 'xxx' to initialise the sql scenario -- Brian J. Cardiff - Manas Technology Solutions [ar.phone] 4796.0232 #BCR(227) [us.phone] 312.612.1050 #BCR(227) [email] bcardiff(a)manas.com.ar [web] www.manas.com.ar [weblog] http://weblogs.manas.com.ar/bcardiff/

1 0

[MonetDB-users] Using monetdb and sqlalchemy
by sabst＠free.fr 10 May '12

10 May '12

Hi, I am just starting using monetdb and, even if I could find discussions about Python/SqlAlchemy integration, I could not make it work. I installed python-monetdb module and have a running monetdb server. Now I expect my client application to work with monetdb as it does with postgresql and instead of passing a connection string like: postgres://user:password@somehost:port/db I use: monetdb://user:password@somehost/db But I get this error: ArgumentError: Could not determine dialect for 'monetdb'. What are the steps required to integrate monetdb into SqlAlchemy? Certainly missing the obvious but I'm currently stuck. Thank you in advance for any help, Stephane.

1 0

Re: [MonetDB-users] Strange things...
by Alexey Mints 07 May '12

07 May '12

Dear All! My old problem http://sourceforge.net/mailarchive/message.php?msg_id=29168305 strikes back on the new version. The situation is just the same - combination of two conditions gives empty result (that is wrong), while each condition alone gives correct resultset. The problem shows up only in the SECOND run of quite a huge set of queries (the first one runs Ok). Even more, there has to be some delay (~10 sec) before the second run, otherwise everything works. Deleting all records does not help, problem disappears only if I recreate the table. What on Earth can this be? It seems rather hard to pin down the problem with SQL-methods only. Just in case - I attach traces of correct and incorrect queries. Regards, Alexey Mints

1 0

[MonetDB-users] problem after upgrading (probably)
by Gijs Molenaar 04 May '12

04 May '12

Hi MonetDB people, greetings from the other side of the street. Long time no see. I just upgraded my monetdb installation to the April release. I use the Ubuntu repo. my database is running, but when I try to connect with the Python API or mclient I get the error: CREATE AGGREGATE: name 'median' (tinyint(8)) already in use Any suggestion how to figure out how to solve this problem? greetings, -- Gijs Molenaar http://www.astro.uva.nl/people/gijs-molenaar/

5 15

[MonetDB-users] bug tracker problems?
by Gijs Molenaar 04 May '12

04 May '12

Me again, I have some issues with the ubuntu packages that I would like to report, but I can't get access to the bug tracker. First of all, the machines seems to respond not really fast. But when I open a new account after the token verification the webserver gives an empty response (according to my browser). I tried this a couple of times now. Is it me? -- Gijs Molenaar http://www.astro.uva.nl/people/gijs-molenaar/

2 2

[MonetDB-users] MonetDB clustering
by Maik Strewe 03 May '12

03 May '12

Hi and good morning from Germany. My name is Maik Strewe and I want to test the performance of the monetDB in a cluster. My application needs millions of rows but few columns. I have to set up an monetDB cluster on 2 servers at an external server hoster. The single instances on the hosts are installed and running. Then I tried to use the "monetdb discover" command but the servers cant see each other. As far as I know the discovery is based on a UDP broadcast, right? Is it possible to configure the cluster members in a configuration file so that the monetdb instance can discover the cluster member by configuration?

4 5

[MonetDB-users] MonetDB clustering
by Maik Strewe 03 May '12

03 May '12

Hi and good morning from Germany. My name is Maik Strewe and I want to test the performance of the monetDB in a cluster. My application needs millions of rows but few columns. I have to set up an monetDB cluster on 2 servers at an external server hoster. The single instances on the hosts are installed and running. Then I tried to use the "monetdb discover" command but the servers cant see each other. As far as I know the discovery is based on a UDP broadcast, right? Is it possible to configure the cluster members in a configuration file so that the monetdb instance can discover the cluster member by configuration?

1 0

Re: [MonetDB-users] Strange things...
by Alexey Mints 02 May '12

02 May '12

Problem solved itself (for now) with the upgrade to the latest version.

1 0

[MonetDB-users] Selecting cracker update algorithms
by Stef Sijben 02 May '12

02 May '12

Hello, We are a group of students trying to repeat the experiments described in the paper Updating a Cracked Database [1]. We know how to use the cracking optimizer, but what we can't figure out is how to select one of the described algorithms to be used for updates. We found implementations of all the algorithms in the source code and I have been looking in documentation and the source code for a considerable time without any luck. Can someone please point us in the right direction? Thanks, Stef Sijben References [1] S. Idreos, M.L. Kersten, and S. Manegold. Updating a cracked database. In Proceedings of the 2007 ACM SIGMOD international conference on Management of data, pages 413–424. ACM, 2007.

1 0

[MonetDB-users] Announcement: New Apr2012 Feature release of MonetDB suite
by Sjoerd Mullender 27 Apr '12

27 Apr '12

The MonetDB team at CWI/MonetDB BV is pleased to announce the Apr2012 feature release of the MonetDB suite of programs. More information about MonetDB can be found on our website at http://www.monetdb.org/. For details on this release, please see the release notes at <http://www.monetdb.org/Downloads/ReleaseNotes>. As usual, the download location is <http://dev.monetdb.org/downloads/>. Apr 2012 feature release Build Environment * Fixed some of the package names for Debian/Ubuntu. Packages for libraries should contain the major number of the library version number. This was not always the case. * On Debian and Ubuntu, install Python modules in dist-packages instead of site-packages. This fixed bug 2997. SQL * Fixed a crash that happened at the end of a database upgrade to the Dec2011 database scheme. The crash happened during cleanup after the database was upgraded, so it was merely inconvenient. * Stripped off implementation-specific parts from error messages before they get presented to the user. Java Module * Password reading by JdbcClient no longer results in strange artifacts * JdbcClient now returns exit code 1 in case of failures * Implemented missing Number types support in PreparedStatement.setObject() * Fixed bug in DatabaseMetaData.getSchemas() method that caused an SQL error when called with catalog and schema argument. * Resolved a bug where JDBC and Control connections could terminate abruptly with 'Connection closed' messages Client Package * ODBC: Implemented the SQL_ATTR_CONNECTION_TIMEOUT attribute. * mclient now has a -a (--autocommit) option to turn off autocommit mode. MonetDB5 Server * The type "chr" has been removed. chr has long been superseded by bte for 1 byte arithmetic plus it is pretty useless to hold single characters since we use Unicode and thus only a tiny subset of the supported character set would fit. * Fixed potential crash by dealing better with non-standard types. * A couple of memory leaks have been plugged. Merovingian * The logfile and pidfile monetdbd properties are now displayed with dbfarm path when relative * Fixed a bug in the multiplex-funnel where certain clients would abort on responses for update queries. * Fixed problem where version and mserver properties for monetdbd were not always successfully retrieved. Bug #2982. * Fixed problem where shutdown of monetdbd would lead to shutting down database 'control' which does not exist. Bug #2983. * Fixed issue causing (harmless) 'error reading from control channel' messages. Bug #2984. * Resolved problem where remote start/stop/etc. commands with monetdb would report error 'OK'. Bug #2984. MonetDB Common * The type "chr" has been removed. chr has long been superseded by bte for 1 byte arithmetic plus it is pretty useless to hold single characters since we use Unicode and thus only a tiny subset of the supported character set would fit. Bug Fixes

1 0

Re: [MonetDB-users] Strange things...
by Alexey Mints 26 Apr '12

26 Apr '12

Hi Stefan! condition1 is like this: exists (select 1 from temp_assoc_counts tc where tc.xtrsrc_id = temp_associations.xtrsrc_id and tc.assoc_count = 1) condition 2 is like: kind is null I have also bumped in the same situation, when condition2 was like "kind = 2". But when "2" was replaced to a column name (with all values in this column were 2) - it worked fine. I.e. there is something wrong when constant appears in the condition. Traces can be seen in attachment.

1 0

[MonetDB-users] Strange things...
by Alexey Mints 23 Apr '12

23 Apr '12

Hello! I have a strange behavior of my MonetDB database. After some actions I run into situation, when queries select * from table1 where {condition1}; and select * from table1 where {condition2}; return the same results (same rows), but select * from table1 where {condition1} and {condition2}; is empty, which is definitely wrong. I cannot see any problems from SQL-side. I am still struggling to get a minimal code producing this bug. Even worse - it is not always occurring under the same conditions... How can I get more information on what's happened? Alexey

2 1

[MonetDB-users] Performance Degredation
by Joseph Brower 13 Apr '12

13 Apr '12

We discussed this a little bit before, but I was called away. Now that I have some time, I'm hoping to discuss this again and hopefully find resolution. Normally our MonetDB instance is running happily and doesn't have any problems. We've limited the number of queries being performed concurrently to the number of cores in the machine, and we do all of our inserts using COPY INTO statements. The problem is that, for no reason that we can see, suddenly MonetDB starts to perform incredibly slowly. This causes queries to stop returning, or, what used to return after less than a second starts to take over 30 seconds, and causes a timeout on our system that produces the graphs based on this information. We restart MonetDB and the problem goes away for a while. Sometimes it drops again after a short time (as little as a few hours) other times it will run fine for a few days. Does anyone have any ideas? What other information can I provide to get a closer look at why it is behaving this way? Thanks, Joseph Brower

1 0

[MonetDB-users] MonetDB driver for node.js?
by Robert Kosara 05 Apr '12

05 Apr '12

Hello, I am looking for a better database for a project where I am dealing with large numbers of data points, and MonetDB seems like a really interesting choice for that. This is for a web application that is written in JavaScript/node.js, though, so I'm wondering if anybody might be working on a MonetDB driver for node. I know that your focus is on scientific data, and JavaScript might not be a big factor there, but node is gaining a lot of momentum for web applications. Also, is there a way to search the mailing list archive? I can't seem to find a way to do that on the sourceforge website. Best, Robert

3 2

[MonetDB-users] Funnel
by Joseph Brower 02 Apr '12

02 Apr '12

We're looking at setting up the funnel. We've got 2 database servers that we're planning on using, and we simply want to balance the requests between the two. We don't want any of the multiplexing to take effect. I'm not seeing much as far as guides or how to set this up in the man pages. Does anyone have any good pointers on how to set things up this way? Thanks, Joseph Brower

3 7

[MonetDB-users] MonetDB - Mondrian connection
by Pedro Salgueiro 30 Mar '12

30 Mar '12

Hi guys! I have been trying MonetDB and it's awesome. I tried to connect MonetDB to Mondrian using olap4j. I managed to establish the connection using the MonetDB JDBC, but I have a problem: There are no errors, but it presents the wrong results. Every MDX query returns the right tables, but with all values "zeroed", something like this: ------------------------ Unit Sales ========== 0 ------------------------ I tried to make a simple JDBC connection, and it worked out ok. I am using the following connection string: String connectionProperties = "jdbc:mondrian:JdbcDrivers=nl.cwi.monetdb.jdbc.MonetDriver;" + "Jdbc=jdbc:monetdb://localhost/foodmart;" + "JdbcUser=monetdb;" + "JdbcPassword=monetdb;" + "Catalog=FoodMart.xml;"; Does anyone knows if I am doing anything wrong? Has anyone managed to make a working connection between Mondrian and Monet? If so, did you have to make something special? It would be great to have a working connection between Mondrian and MonetDB, since its much faster than MySQL or LucidDB, which I have already tried. Best regards, Pedro

2 5

[MonetDB-users] Fwd: Re: DB Corruption
by Joseph Brower 27 Mar '12

27 Mar '12

This is what one of my coworkers reports about the problem. both columns are varchar(24). We have confirmed that there are no null values. We have a theory… Approximately every 6 hours, monetdb stops responding to queries and starts thrashing the disk. This behavior has been confirmed without any inserts. Because of this behavior, we routinely stop and start the service. After a restart, the service returns to a normal state. This appears to be the core problem. Our guess is that we restarted the service in the middle of a massive import. We will make sure this doesn't happen again, however, we still cannot find any sort of resolution as to why monetdb routinely starts thrashing the disk (on the order of writing 150MB/s) without any inserts, only select queries. Sorry to change the topic, but we're moving on the assumption we corrupted our db by restarting the service in the middle of a massive "INSERT INTO FROM" query. Can you think of any reason why monetdb would need to write to the disk in such an aggressive manner while ONLY serving select queries? This appears to be the core problem. Thanks On Mar 26, 2012, at 9:23 AM, Joseph Brower wrote: > What would you like me to tell them? > > > -------- Original Message -------- > Subject: Re: [MonetDB-users] DB Corruption > Date: Sun, 25 Mar 2012 09:15:24 +0200 > From: Martin Kersten <Martin.Kersten(a)cwi.nl> > Reply-To: Martin.Kersten(a)cwi.nl, Communication channel for MonetDB > users <monetdb-users(a)lists.sourceforge.net> > To: Communication channel for MonetDB users > <monetdb-users(a)lists.sourceforge.net> > > > > What is the column type? > Is the situation repeatable using a fresh database? > > regards, Martin > > On 3/25/12 6:34 AM, Joseph Brower wrote: > > We just checked. There are no nulls in the database at all. > > Additionally, there is only one column that has a different count. > > > > Thanks, > > > > Joseph Brower > > > > On 03/24/2012 09:58 AM, Stefan Manegold wrote: > >> Joseph, > >> > >> since you did not specify your queries in more detail, I assume they are as > >> simple as > >> (1) select count(*) from<your_table>; > >> and > >> (2) select count(single_column) from<your_table>; > >> > >> In that case, you could try the following sanity check: > >> > >> (3) select count(*) from<your_table> where single_column is null; > >> > >> and check whether that returns the difference between your counts. > >> > >> If that indeed returns the difference between your global count(*) and > >> count(single_column), this would confirm the existence of NULL values, and > >> you'd need to invest which of the values in your CVS file have been > >> interpreted as NULL and why. > >> > >> In case the result of queries (2) and (3) do not add up to the number > >> returned by query (1), you might want to check the output(s) of > >> > >> TRACE select count(*) from<your_table>; > >> TRACE select count(single_column) from<your_table>; > >> TRACE select count(*) from<your_table> where single_column is null; > >> > >> (or have us check them, provided/once we find the time). > >> > >> Stefan > >> > >> On Sat, Mar 24, 2012 at 09:37:38AM -0600, Joseph Brower wrote: > >>> There shouldn't be any nulls. The records were all added by a "Copy > >>> Into" command. The files that we imported had no nulls in it at > >>> all. > >>> > >>> On 03/24/2012 01:57 AM, Sjoerd Mullender wrote: > >>>> On 2012-03-24 00:14, Joseph Brower wrote: > >>>>> We were troubleshooting some odd results coming back from our database, > >>>>> and found that it's missing some entries from a column. This appears to > >>>>> be causing some sort of offset. Does anyone have any ideas on what can > >>>>> be done to resolve it or what may have caused it? Is there any > >>>>> information we could send that would be helpful in troubleshooting this > >>>>> or for a bug report? > >>>>> > >>>>> If we do a count(*) and then a count('single_column') we are getting two > >>>>> different results (when they should be indentical) > >>>> count (*) counts all rows, whereas count(single_column) only count > >>>> non-null values. Are there any nulls in the single_column? > >>>> > >>>> > >>>> > >>>> > >>>> ------------------------------------------------------------------------------ > >>>> This SF email is sponsosred by: > >>>> Try Windows Azure free for 90 days Click Here > >>>> http://p.sf.net/sfu/sfd2d-msazure > >>>> > >>>> > >>>> _______________________________________________ > >>>> MonetDB-users mailing list > >>>> MonetDB-users(a)lists.sourceforge.net > >>>> https://lists.sourceforge.net/lists/listinfo/monetdb-users > >>> ------------------------------------------------------------------------------ > >>> This SF email is sponsosred by: > >>> Try Windows Azure free for 90 days Click Here > >>> http://p.sf.net/sfu/sfd2d-msazure > >>> _______________________________________________ > >>> MonetDB-users mailing list > >>> MonetDB-users(a)lists.sourceforge.net > >>> https://lists.sourceforge.net/lists/listinfo/monetdb-users > >> > > > > > > ------------------------------------------------------------------------------ > > This SF email is sponsosred by: > > Try Windows Azure free for 90 days Click Here > > http://p.sf.net/sfu/sfd2d-msazure > > _______________________________________________ > > MonetDB-users mailing list > > MonetDB-users(a)lists.sourceforge.net > > https://lists.sourceforge.net/lists/listinfo/monetdb-users > > ------------------------------------------------------------------------------ > This SF email is sponsosred by: > Try Windows Azure free for 90 days Click Here > http://p.sf.net/sfu/sfd2d-msazure > _______________________________________________ > MonetDB-users mailing list > MonetDB-users(a)lists.sourceforge.net > https://lists.sourceforge.net/lists/listinfo/monetdb-users

2 3

[MonetDB-users] DB Corruption
by Joseph Brower 26 Mar '12

26 Mar '12

We were troubleshooting some odd results coming back from our database, and found that it's missing some entries from a column. This appears to be causing some sort of offset. Does anyone have any ideas on what can be done to resolve it or what may have caused it? Is there any information we could send that would be helpful in troubleshooting this or for a bug report? If we do a count(*) and then a count('single_column') we are getting two different results (when they should be indentical) Thanks, Joseph Brower

5 6

[MonetDB-users] Example of GUI on top of MonetDB
by Ryad Ben-El-Kezadri 22 Mar '12

22 Mar '12

Hello, We've updated our Java GUI for analyzing network traces on top of MonetDB A HD video trailer is available here : http://www.youtube.com/watch?v=P8EEhttP-NU GPL Source is available here: https://sites.google.com/site/yavista/ Unfortunately it only works on top of MonetDB Xquery but we hope to port it on MonetDB SQL Regards Ryad

1 0

[MonetDB-users] Failure reading in negative bigint
by Nikhil Padmanabhan 22 Mar '12

22 Mar '12

Hello, I have recently started trying out monetdb and have been very impressed by its speed so far. However, I am hitting a failure trying to read in a large negative number (bitmask) into a bigint field; sql>\<test.sql operation successful value '-9223372036854775808' from line 1 field 17 not inserted, expecting type bigint failed to import table I've attached the sql commands to create the table and a test file to this message. As far as I can tell, this should be fine for being represented by a 64 bit integer.... I am running this on an OS X 10.6 64-bit system, this is the Dec-2011 SP2 version of the 64 bit binaries dowloaded from www.monetdb.org. Any help is much appreciated. Thanks in advance, -- Nikhil --------------------------------- Nikhil Padmanabhan nikhil.padmanabhan(a)yale.edu

2 1

[MonetDB-users] Problem with installation from sources
by Stathis Gkotsis 17 Mar '12

17 Mar '12

Hi all, trying to install the latest version of monetdb from sources, I just did: * ./configure --enable-debug --enable-monetdb5 --enable-sql * make and the make install I created a database and now that I try to connect to it, in the log I get: 2012-03-16 17:33:44 MSG merovingian[1729]: starting a proxy failed: cannot get address for hostname '10-04-64bit': Unknown host 2012-03-16 17:33:44 ERR merovingian[1729]: client error: cannot get address for hostname '10-04-64bit': Unknown host I tried defining 10-04-64bit in /etc/hosts as localhost / IP of the machine, but then I get: "Too many redirects" error. Any ideas on how to solve this? BR, Stathis

2 1