- users-list - monetdb.org

[MonetDB-users] monetsql except
by Lazaros Koromilas 18 Dec '09

18 Dec '09

i get a weird behavior on sql except. i'm have attached the except.* files from: cat except.sql | mclient -lsql > except.txt notice the tuple: [ 425, 719, "people", "person", NULL ] is returned in all 3 queries; shouldn't except prevent this? any help? i'm runnig on: MonetDB server v5.6.0 (32-bit), based on kernel v1.24.0 (32-bit oids) Copyright (c) 1993-2008 CWI, all rights reserved Visit http://monetdb.cwi.nl/ for further information Configured for prefix: /usr/local Libraries: libpcre: 7.9 2009-04-11 (compiled with 7.9) openssl: OpenSSL 0.9.8k 25 Mar 2009 (compiled with OpenSSL 0.9.8k 25 Mar 2009) Compiled by: root@redrawn Compilation: gcc -O2 -std=c99 Linking : /usr/bin/ld thanx, lazaros. -- http://www.ics.forth.gr/~koromil

3 4

[MonetDB-users] Announcement: New Nov2009-SP1 bugfix release of MonetDB suite
by Sjoerd Mullender 18 Dec '09

18 Dec '09

The MonetDB team at CWI/MonetDB BV is pleased to announce the Nov2009-SP1 bug fix release of the MonetDB suite of programs. More information on this release will be available at <http://monetdb.cwi.nl/Development/Releases/Nov2009/>. Fixes include: - The Windows installers for MonetDB/SQL in the Nov2009 release were incomplete. The missing files have been added. - Fixed performance issue with loading complex schemas in readonly mode in MonetDB/SQL. - Fixed performance issue with grouping in MonetDB/SQL when one of the columns is sorted. - Fixed problems with LIKE joins in MonetDB/SQL. - Load all files in a multi-file COPY INTO query. - Fix error reporting of unexpected input in XQuery queries to be Unicode aware. - Correctly handle circular dependencies in FD property check. (SF bug #2908615.) - Fixes throughout the code base to better cope with unexpected situations (such as memory shortage). -- Sjoerd Mullender

1 0

[MonetDB-users] ODBC Driver
by Nafees Ur Rehman 17 Dec '09

17 Dec '09

Hi, thanks for helping me in enabling history. I want to connect monetDB with Microsoft SQLServer DM tool. I tried to register ODBC driver for monetDB and followed the instructions but no luck. I would appreciate if you can guide me on that. Please note I am running win xp. Regards Nafees Ur Rehman

2 1

[MonetDB-users] commercial support
by Alfred Nordman 16 Dec '09

16 Dec '09

Hi, The home page of Monetdb implies that there is a commercial support. Is there a contact person or email address which I can use in order to learn about this service? Thanks. Alfred. -- View this message in context: http://old.nabble.com/commercial-support-tp26814876p26814876.html Sent from the monetdb-users mailing list archive at Nabble.com.

1 0

[MonetDB-users] MonetDB & memory issues on Windows
by Alex Bo. 16 Dec '09

16 Dec '09

Hi, I have been using MonetDB on windows for a while now, and I have consistent problems with all versions up to Nov2009 with memory handling. I don't know if it's because I'm running on Windows, or because I'm doing something wrong, or maybe the problem is in MonetDB, but I cannot work with Monet for long periods of time without routinely restarting the server to free up memory. It seems that over time, MonetDBs memory consumption goes up and up, and eventually when running a heavy query when there's not much memory left, the server crashes. If, however, I restart the server once in a while, all memory is freed, and I can work for longer periods without any problems. So it seems that Monet on Windows has a problem in freeing resources. Some recources are only cleaned on stopping the server. This is true both for COPY INTO operations and for querying. I would appreciate it if you could point out to me how I should approach this. Is this a problem in Monet? Is this because I'm running on Windows? If so, which files can I look into in order to look for problems? -- View this message in context: http://old.nabble.com/MonetDB---memory-issues-on-Windows-tp26808719p2680871… Sent from the monetdb-users mailing list archive at Nabble.com.

1 0

[MonetDB-users] Remote connections & "merovingian"
by Venks 15 Dec '09

15 Dec '09

Can I connect remotely via JDBC using "merovingian"? In short, how will i set mapi_open = true using "merovingian"? -Venks

2 2

[MonetDB-users] Bulk load problem
by Raghav Kumar Gautam 14 Dec '09

14 Dec '09

Hi, I am a new user and I am trying to bulk load a very largefile. The following is intended for testing only: $ 7z e -so data.7z | head -n 100 | query "copy 100 RECORDS into data_table FROM STDIN USING DELIMITERS tuple_seperator '\r\n' record_seperator '\t';" And am getting the following error: MAPI = rk@localhost:50000 QUERY = copy 100 RECORDS into data_table FROM STDIN USING DELIMITERS tuple_seperator '\r\n' record_seperator '\t'; ERROR = !syntax error, unexpected IDENT, expecting STRING in: "copy 100 records into data_table from stdin using delimiters tuple_seperator" Timer 0.278 msec Can you tell me where am I going wrong ? With Regards, Raghav.

5 5

[MonetDB-users] enabling history
by Nafees Ur Rehman 14 Dec '09

14 Dec '09

Hi, I tried set history=true; statement to enable history, but it throws an exception that two functions are missing.I want to load these two functions but I don't have them in any of the directory of monetdb. Please note that I am running monetdb SQL on windows xp. I would highly appreciate your kind help. Regards Nafees Ur Rehman

3 2

[MonetDB-users] COPY INTO in Nov2009
by Alex Bo. 14 Dec '09

14 Dec '09

Hi, I saw that in the Nov2009 release the COPY INTO now uses multiples CPU/cores. Is this enabled by default, or should I enable this somehow? I am running on windows, and I don't see any difference in CPU behavior nor do I get time improvements. Thanks. -- View this message in context: http://old.nabble.com/COPY-INTO-in-Nov2009-tp26778392p26778392.html Sent from the monetdb-users mailing list archive at Nabble.com.

2 1

[MonetDB-users] Multi Query Optimizer/Drill Down Benchmark
by Martin Dürre 11 Dec '09

11 Dec '09

Hello monetDB-Users, after installing monetDB( see sourceforge's bug tracker ;), I'm finally going to use it. Is there anyone around how can give me a short feedback on following topics: - Multi Query Optimizer - Drill Down Benchmark described in papers found at http://monetdb.cwi.nl/Development/Research/Articles/index.html Concrete questions are 1. Are Multi-Query Optimizing Approaches implemented in current versions of MonetDB(4 or 5). 2. Is the drill down benchmark still to be found somewhere (google says no), Maybe these questions are way to general, but I have to start somewhere. Thanks, Martin

2 1

[MonetDB-users] Concurrent user threads
by Vashti_Ragoonath＠ibi.com 10 Dec '09

10 Dec '09

Hi, I read in the SQL Reference Manual that "The number of concurrent user threads is a configuration parameter.", but no information on the name or location or how to change it. Nor is there any info on what the default might be. I tried running multiple users and noticed that the performance degradation increased dramatically as the load increased. I looked at the Server and SQL manuals without luck. Thanks, Tah

3 3

[MonetDB-users] file system usage
by Matthew Jones 10 Dec '09

10 Dec '09

Hey folks, I'm wondering if you can tell me how MonetDB manages its on-disk database. During normal operation I was seeing the database hovering at around 55G, after I stopped it to take a backup it was at 70G, but immediately after the restart it dropped down to 35G. 73, Matthew W. Jones (KI4ZIB) http://matburt.net

1 0

[MonetDB-users] lib_replication
by Vashti_Ragoonath＠ibi.com 10 Dec '09

10 Dec '09

Hi, I was able to load up and run the MonetDB5-SQL-Installer-x86_64-20091023.msi. However, when I tried the MonetDB5-SQL-Installer-x86_64-20091119.msi I received an error about the server failing to start because it could not find lib_replication.dll. There is no indication in the Readme.html that this has to be built and it's not in the 1119 package. I tried using the 1119 because I created some compressed files (gz files created with gzip) that I wanted to load using COPY INTO and read that this is supported on Windows in the 1119 version. In the 1123 version it generated an error when loading the GZ files about not being able to locate the files - a bogus error it seems since the file path was correct. I looked on Google, forums etc but no other reports. I reloaded the 1123 to make sure that was still working and it is. I hope I did not miss a basic step or something in which case I apologize profusely! Thanks, Tah

2 1

Re: [MonetDB-users] monetsql python and blobs
by Gijs Molenaar 10 Dec '09

10 Dec '09

On 12/09/2009 11:49 AM, Lazaros Koromilas wrote: > just a problem i encountered. > see attachment. > Thanks for the patch. I will test and include it. One question, why is the monetdb python API is such a strange location in your installation? Did you manually move it to MonetSQLdb? - gijs

1 0

[MonetDB-users] monetsql python and blobs
by Lazaros Koromilas 09 Dec '09

09 Dec '09

just a problem i encountered. see attachment. thanx, lazaros. -- http://www.ics.forth.gr/~koromil

1 0

[MonetDB-users] enabling history
by Nafees Ur Rehman 02 Dec '09

02 Dec '09

Hi, I am enabling history (set history=true;) but it gives me an exception that two procedures are not available. Can someone guide me on that? Regards Nafees Ur Rehman University of Konstanz

3 2

[MonetDB-users] Announcement: New Nov2009 feature release of MonetDB suite
by Stefan Manegold 30 Nov '09

30 Nov '09

Dear all, The MonetDB team at CWI/MonetDB BV are pleased to announce the new "Nov2009" feature release of the MonetDB suite of programs. This feature release is backward compatible with the previous releases (Nov2008 through Aug2009-SP2). Caveat 1: In MonetDB/XQuery, we extended and improved the use of value indices on comparisions between a value and an XPath ending in text() or attribute access (e.g., doc(x)//x/y[z/text() = "foo"] and for $x in doc//x/y where $x/@z = "bar" return $x); such queries do not need scanning data, but start at the index and navigate backwards from there. This required a change in the physical database storage. While the Nov2009 release of MonetDB/XQuery can still read older (pre-Nov2009) databases, it is recommended to (back-up and) re-shred all documents to benefit from the new indices and achieve optimal performance. Caveat 2: When upgrading from a release older than May2009 (i.e., Nov2008 through Feb2009-SP2), you need to move your write-ahead log following the "recommended" instructions for SQL (<http://monetdb.cwi.nl/Development/Releases/May2009/#SQL>) and/or XQuery (<http://monetdb.cwi.nl/Development/Releases/May2009/#XQuery>). More information on the new release is available at <http://monetdb.cwi.nl/Development/Releases/Nov2009/>. The sources and binaries are available at <http://monetdb.cwi.nl/Download/>. Enjoy! The MonetDB team at CWI/MonetDB BV. -- | Dr. Stefan Manegold | mailto:Stefan.Manegold@cwi.nl | | CWI, P.O.Box 94079 | http://www.cwi.nl/~manegold/ | | 1090 GB Amsterdam | Tel.: +31 (20) 592-4212 | | The Netherlands | Fax : +31 (20) 592-4312 |

1 0

[MonetDB-users] SABAOTHgetMyStatus Too many open files
by Matthew Jones 29 Nov '09

29 Nov '09

Hey Folks, we are continuing to get deadlocks about once a day and I've noticed this in the logfile: 2009-11-28 14:31:05 ERR reporting_primary[31485]: !SABAOTHgetMyStatus: IOException:sabaoth.getStatus:Unable to open directory /opt/MonetDB5/dbfarm: Too many open files Not sure how many open files it had when it went south, but immediately after a restart I saw this in files-nr: # cat /proc/sys/fs/file-nr 1408 0 3164775 That third number being the 'files-max' on the system currently... I think that must be being dynamically set. This is with the latest release August2009-SP2, the net effect is a deadlock on all connections to merovingian and a segfault of the mserver process. Neils, this is what led up to that 'log' file being truncated in sql_logs a few days ago... this message occurred just before this happened. I've restarted my database and should see the problem reproduce itself again within 24 hours. I am recording the number of file descriptors in use every minute by mserver, merovingian, and overall for the system so I can know what the numbers were leading up to it. Is this a file descriptor leak in Monet or is my files-max set too low (even though it appears to be dynamic)? 73, Matthew W. Jones (KI4ZIB) http://matburt.net

1 0

[MonetDB-users] COPY INTO memory issues
by Andreas Streichardt 26 Nov '09

26 Nov '09

Hi, i had a look at monetdb a while (a year or so) ago and stumbled upon a few crashes/memory issues back then. Apart from that it made a very promising impression to me. Today i thought i could give it a try again to see if these issues have been fixed and found out that copy into still seems to have memory issues and i wonder if i am the only one who hits such issues. I am experimeting with dummy data created by a script. Data is created the following way: 20 smallints: Values 1-10. 100 tinyints: Values 0-1 20 ints: Values 1-10000 40 varchars: md5 of some random value 20 floats: rand(1000000)/1000000 I created a CSV file with ~2.1 million rows and then tried to insert the data. My first attempt was: sql>copy into md_data FROM '/home/mop/performance/hans.csv' delimiters ',','\n' null as ''; MAPI = monetdb@localhost:50000 ACTION= read_line QUERY = copy into md_data FROM '/home/mop/performance/hans.csv' delimiters ',','\n' null as ''; ERROR = Connection terminated This was of course the quick and dirty way. What happened in the background was that monetdb consumed all memory. My system is a debian lenny 64bit. Server has 4GB and decent CPUs. Why does it need all my memory here? I then did the same but loaded only parts of the file (1000000 rows). sql>copy 1000000 records into md_data FROM '/home/mop/performance/hans.csv' delimiters ',','\n' null as '' ; This worked but the server used ~2-3GB of my RAM during the process and what's even more important it used the same amount even though the copy process finished. Why does it use the RAM even though it is not used anymore? Then i tried to insert 1000000 rows more and my expectation was that monetdb would reuse the memory it allocated (and didn't free) in the first loading process. This wasn't the fact. Again monetdb consumed all my memory and when loading finished it indicated that it was still doing something (CPU @ ~20%). When i tried a: sql>SELECT COUNT(*) FROM md_data; it hang completely. Same when trying to establish a new connection. I stopped the server and restarted again. Everything was fine again. I inserted the rest of the data (memory raised again but there were only 100000 rows left so there wasn't any problem this time). Am i the only one hitting such issues? One could argue that i should import less records at once but even then. There seems to be some nasty memleak in the background. Or is this a configuration issue? Or am i doing something completely wrong? I am using the .debs: http://monetdb.cwi.nl/downloads/Debian/ Thanks in advance, Andreas Streichardt

2 3

Re: [MonetDB-users] COPY INTO memory issues
by Jens Oehlschlägel 25 Nov '09

25 Nov '09

> > This wasn't the fact. Again monetdb consumed all my memory and when loading > > finished it indicated that it was still doing something (CPU @ ~20%). > log records have to be written, your OS may kick in to flush dirty pages, > reloading swapped out processes, .... The following might not apply to Linux, but still: under windows, suddenly flushing dirty pages from large memory mapped files can severly degrade the system performance ("stalled"). I know windows has settings to limit the amount of dirty pages that may accumulate without flushing. Maybe Linux has something similar and maybe this helps here. Please keep me in the loop if you try this route. Jens

1 0

[MonetDB-users] 'create table as select...' from union queries doesn't work
by Alex Bo. 25 Nov '09

25 Nov '09

Hi, If I do this: create table t1 (f1 int); create table t2 (f1 int); create table t_union as (select f1 from t1 union all select f1 from t2) with data; Then no table is created and no exception is thrown. Should I open a bug for this? (AugSP2 on Windows) -- View this message in context: http://old.nabble.com/%27create-table-as-select...%27-from-union-queries-do… Sent from the monetdb-users mailing list archive at Nabble.com.

2 1

[MonetDB-users] create function
by Paul Phillips [ gmail ] 25 Nov '09

25 Nov '09

Thank you for the find piped help to the src documentation. I can make the following function using mclient from AUG2009SP2 well: create function f(i integer) returns bool begin return true; end; It seems that the create function is failing only in the SQL editor for SQuirreL client 3.0.2. I am using the compiled jdbc jar of MonetDB-AUG2009SP2 and have tried to use a compiled sourceball version of jar from 11NOV2009 current branch. both provide this error: Error: syntax error, unexpected $end, expecting WHILE in: "create function f(i integer) returns bool begin return true;" SQLState: null ErrorCode: 0 Error occured in: create function f(i integer) returns bool begin return true Should this then be a question for SQuirrel Client? Thanks Paul

1 0

[MonetDB-users] temporary tables
by Alex Bo. 24 Nov '09

24 Nov '09

Hi, How do temporary tables behave? Are they only stored in memory or are they written to the disk too like normal tables? What performance should I expect from using "create temporary table..." and then querying it in comparison to having a nested query? Thanks. -- View this message in context: http://old.nabble.com/temporary-tables-tp26504075p26504075.html Sent from the monetdb-users mailing list archive at Nabble.com.

2 1

[MonetDB-users] Database Failure
by Matthew Jones 24 Nov '09

24 Nov '09

Hey folks, had a database running for several weeks without any problems but early this morning anytime a client would try to connect to it the connection would just hang. After shutting down and restarting I see this: 2009-11-23 09:38:06 MSG merovingian[28036]: starting Merovingian 1.1 for dbfarm /opt/MonetDB5/dbfarm 2009-11-23 09:38:06 MSG merovingian[28036]: listening for TCP connections on 0.0.0.0:50000 2009-11-23 09:38:06 MSG discovery[28036]: listening for UDP messages on 0.0.0.0:50000 2009-11-23 09:38:06 MSG merovingian[28036]: handling commands over UNIX socket /opt/MonetDB5/dbfarm/.merovingian_control 2009-11-23 09:38:06 MSG discovery[28036]: new neighbour 10.1.0.79 2009-11-23 09:38:07 MSG discovery[28036]: new database mapi:monetdb:// hsdw01:50000/reporting_primary (ttl=660s) 2009-11-23 09:39:45 MSG merovingian[28036]: starting database 'reporting_primary' due to control signal 2009-11-23 09:39:45 MSG merovingian[28036]: database 'reporting_primary' has crashed after start on 2009-11-23 09:36:59, attempting restart, up min/avg/max: 2m/2d/1w, crash average: 1.00 0.60 0.27 (18-10=8) 2009-11-23 09:39:46 MSG reporting_primary[28067]: arguments: /usr/bin/mserver5 --config=/etc/monetdb5.conf --dbname=reporting_primary --dbinit=include sql; --set monet_daemon=yes --set mapi_open=false --set mapi_autosense=true --set mapi_port=50001 --set monet_vault_key=/opt/MonetDB5/dbfarm/reporting_primary /.vaultkey 2009-11-23 09:39:46 MSG reporting_primary[28067]: # MonetDB server v5.14.2, based on kernel v1.32.2 2009-11-23 09:39:46 MSG reporting_primary[28067]: # Serving database 'reporting_primary', using 8 threads 2009-11-23 09:39:46 MSG reporting_primary[28067]: # Compiled for x86_64-pc-linux-gnu/64bit with 64bit OIDs dynamically linked 2009-11-23 09:39:46 MSG reporting_primary[28067]: # Copyright (c) 1993-July 2008 CWI. 2009-11-23 09:39:46 MSG reporting_primary[28067]: # Copyright (c) August 2008-2009 MonetDB B.V., all rights reserved 2009-11-23 09:39:46 MSG reporting_primary[28067]: # Visit http://monetdb.cwi.nl / for further information 2009-11-23 09:39:46 MSG reporting_primary[28067]: # Listening for connection requests on mapi:monetdb://127.0.0.1:50001/ 2009-11-23 09:39:46 MSG reporting_primary[28067]: # MonetDB/SQL module v2.32.2 loaded 2009-11-23 09:39:46 ERR reporting_primary[28067]: !mvc_init: unable to create system tables 2009-11-23 09:39:55 ERR merovingian[28036]: failed to fork mserver: database 'reporting_primary' has inconsistent state (running but dead), review merovingian's logfile for any peculiarities The database marks itself as running but any attempt to connect to it returns the following error: monetdb ERROR MALException:setScenario:Scenario not initialized 'sql' I've read other problems reports that had a similar issue and the suggestion was to wait for the sql module to allow connections but even after 5 minutes this is all that is returned. Is there any way to get this database back to a consistent state so that it is usable? 73, Matthew W. Jones (KI4ZIB) http://matburt.net

8 20

[MonetDB-users] Mx documentation
by Paul Phillips [ gmail ] 24 Nov '09

24 Nov '09

Can someone tell where the Mx utility is for creating the documentation? I have seen references in sources to this command and searches are trouble with only two letters like mx. Is this documentation different/more than the nl website? Thank you. Paul

3 3