- users-list - monetdb.org

[MonetDB-users] Large XML instances with MonetDB/XQuery 4.38.5
by Christian Grün 02 Oct '10

02 Oct '10

Hi everyone, is it still possible to shred large XML instances with MonetDB/XQuery, or do I need more memory for shredding? I was just trying to the 1GB instance of XMark with WinXP 32Bit and 2GB RAM, and this is what I got: MonetDB>shred_doc("1gb.xml", "xmark"); #GDKmmap(536870912) fails, try to free up space [memory in use=36143780,virtual memory in use=1015480320] #GDKmmap(536870912) result [mem=26016940,vm=1005387776] !ERROR: [shred_url]: 1 times inserted nil due to errors at tuples 0@0. !ERROR: [shred_url]: first error was: !ERROR: HEAPextend: failed to extend to 536870912 for 06\632theap !ERROR: shredBAT_append_str: APPEND-STR[_prop_text]( follow murders prov shoes p resent eager delivered defy bend viol here marquis powers complexion pillar cran ts agate ), BUNappend fails !ERROR: CMDshred_url: operation failed. pf:add-doc(...) produces the same output; the XML document itself is well-formed. Thanks, Christian

2 6

[MonetDB-users] Embedded SQL Example Hangs?
by Hering Cheng 23 Sep '10

23 Sep '10

Hi, I am trying to run the example embedded MonetDB server using the June 2010 SP2 release: $ ./Mbeddedsql5 --config=monetdb5.conf --time --dbname=test_db After entering a simple SQL statement on the standard input and pressing <ENTER>, the program hangs at mapi_query. Here is the stack trace: [1] _read(0x5, 0xffffffff7fffdf16, 0x2, 0x0, 0x0, 0x0), at 0xffffffff7a6d3d90 [2] read(0x5, 0xffffffff7fffdf16, 0x2, 0x0, 0x0, 0x0), at 0xffffffff7a6c48b8 [3] pipe_read(s = 0x10015fe50, buf = 0xffffffff7fffdf16, elmsize = 2U, cnt = 1U), line 3194 in "stream.c" [4] stream_readSht(s = 0x10015fe50, val = 0xffffffff7fffdf16), line 2679 in "stream.c" [5] bs_read(ss = 0x10015fcd0, buf = 0x100f7a012, elmsize = 1U, cnt = 8190U), line 2368 in "stream.c" [6] stream_read(s = 0x10015fcd0, buf = 0x100f7a012, elmsize = 1U, cnt = 8190U), line 181 in "stream.c" [7] read_line(mid = 0x100133950), line 2515 in "Mapi.c" [8] read_into_cache(hdl = 0x100f73f30, lookahead = 1), line 2993 in "Mapi.c" [9] mapi_execute_internal(hdl = 0x100f73f30), line 3240 in "Mapi.c" [10] mapi_query(mid = 0x100133950, cmd = 0x100147010 "select count(1) from taq.trade\n"), line 3277 in "Mapi.c" [11] main(argc = 4, av = 0xffffffff7fffe628), line 212 in "prog.c" Here is my MonetDB configuration: $ ./mserver5 --version MonetDB server v5.20.5 (64-bit), based on kernel v1.38.5 (64-bit oids) Release Jun2010-SP2 Copyright (c) 1993-July 2008 CWI Copyright (c) August 2008-2010 MonetDB B.V., all rights reserved Visit http://monetdb.cwi.nl/ for further information Found 64.0GiB available memory, 16 available cpu cores Configured for prefix: /GAAL/chenher/share/monetdb/distro-sparc-jun2010-sp2-64bit Libraries: libpcre: 8.10 2010-06-25 (compiled with 8.10) openssl: OpenSSL 1.0.0a 1 Jun 2010 (compiled with ) libxml2: 2.6.23 (compiled with 2.6.23) Compiled by: chenher@sfouxsrv073 (sparc-sun-solaris2.10) Compilation: cc -m64 -xcode=pic32 -I/GAAL/chenher/share/hans_boehm_gc/distro-sparc-7.1-64bit//include/ -g Linking : /usr/ccs/bin/ld -m64 -L/GAAL/chenher/share/openssl/distro-sparc-1.0.0a-64bit//lib/64/ -L/GAAL/chenher/share/pcre/distro-sparc-8.10-64bit//lib -L/GAAL/chenher/share/hans_boehm_gc/distro-sparc-7.1-64bit//lib/ The database test_db was created by a non-embedded mserver5 using the same configuration file. The non-embedded mserver5 was gracefully shut down just before running the embedded version. Thanks. Hering

1 0

Re: [MonetDB-users] newbies question
by Peter Boncz 21 Sep '10

21 Sep '10

Hello Olivier, MonetDB/XQuery and MonetDB/SQL are currently different servers, based on different MonetDB kernel versions (4 and 5). The new python library you are probably referring to, however, only works for MonetDB5 (thus SQL). MonetDB/XQuery can be accessed using JDBC, XRPC and C (mapi), as per the MonetDB/XQuery API documentation. Peter > -----Original Message----- > From: o.fournier(a)iktios.fr [mailto:o.fournier@iktios.fr] > Sent: Tuesday, September 21, 2010 5:57 PM > To: monetdb-users(a)lists.sourceforge.net > Subject: [MonetDB-users] newbies question > > Hi, > > I find the concept of MonnetDb interresting... > > So, I would like use MonnetDB in SQL and XML mode with python > interface. > Could I use the python MAPI module to request XML mode? > If not, have you plan to do it? > Or what method could I use to access XML Monnetdb from python? > > Thank you > > Olivier Founier > > > > ---------------------------------------------------------------------- > -------- > Start uncovering the many advantages of virtual appliances > and start using them to simplify application deployment and > accelerate your shift to cloud computing. > http://p.sf.net/sfu/novell-sfdev2dev > _______________________________________________ > MonetDB-users mailing list > MonetDB-users(a)lists.sourceforge.net > https://lists.sourceforge.net/lists/listinfo/monetdb-users

1 0

[MonetDB-users] newbies question
by o.fournier＠iktios.fr 21 Sep '10

21 Sep '10

Hi, I find the concept of MonnetDb interresting... So, I would like use MonnetDB in SQL and XML mode with python interface. Could I use the python MAPI module to request XML mode? If not, have you plan to do it? Or what method could I use to access XML Monnetdb from python? Thank you Olivier Founier

1 0

[MonetDB-users] about create index command
by kun ren 17 Sep '10

17 Sep '10

Dear all, I have a problem on create index command, my table is t1,and have two column, c1 and c2. when I execute the sql command "create index index_c1 on t1(c1);" but the MonetDB said "Segmentation fault", what the question? Best regards!

2 2

Re: [MonetDB-users] MonetDB Copy File INTO
by Fabian Groffen 14 Sep '10

14 Sep '10

On 14-09-2010 01:04:41 +0000, mikeschill(a)users.sourceforge.net wrote: > Username: MikeSchill > > I really want to use the MonetDB for our processing but getting > problems trying to do a COPY INTO: Always fails on last line stating > missing '(field delimeter) line (last line) field (last field) > failed to import table > > COPY into STATEDATA from > C:\\Books\\RED.txt' using > delimiters '\t','\n'; > > No idea what I am doing wrong....fails on record 101 if I say COPY 100 > Records... Can you give us any details on what version of MonetDB you are using (such as the release, e.g. Jun2010-SP2)? Also is your data public, or can we get a copy of it to see if we can reproduce the problem? Regards, Fabian Groffen

1 0

[MonetDB-users] 64 bit build with 64 bit OIDs - Invalid data for large string table
by Alex Bo. 07 Sep '10

07 Sep '10

Hi, I built June-SP1 on Windows 64 bit 64 bit OIDs. I loaded a table with one string field with approximately 500M strings. I am running 'Select * from table limit 10', and what I see is this: #1: myVal1 #2: null #3: myVal2 #4: null #5: myVal3 #6: null ... It seems that nulls are inserted between every two consecutive values in the source. The rowcount of the table is correct. I looked inside the dbfarm folders and saw that the theap file is ~26GB, and the tail ~4.6GB. Inserting only 1M of the same data worked fine. Please advise, Thanks. -- View this message in context: http://old.nabble.com/64-bit-build-with-64-bit-OIDs---Invalid-data-for-larg… Sent from the monetdb-users mailing list archive at Nabble.com.

1 0

[MonetDB-users] Building June10-SP1 64 with 64 OID on Windows
by Alex Bo. 06 Sep '10

06 Sep '10

When using MONET_OID64=1 to build June10-SP1 on Windows 64, I get this in the MonetDB5 module: .\..\..\..\..\src\modules\mal\tokenizer.mx(562) : warning C4267: '=' : conversion from 'size_t' to 'int', possible loss of data How can I overcome this? (This did not happen with 32 bit OIDs) Thanks. -- View this message in context: http://old.nabble.com/Building-June10-SP1-64-with-64-OID-on-Windows-tp29632… Sent from the monetdb-users mailing list archive at Nabble.com.

2 2

[MonetDB-users] strPut error with large string tables
by Alex Bo. 02 Sep '10

02 Sep '10

Hi, (June2010 SP1 built on Windows) I loaded a table with a single string column successfully with approximately 500M string values. Running 'Select * from table limit 10' gives me this error: 'strPut: string heaps gets larger than 15GB.' What does this mean? -- View this message in context: http://old.nabble.com/strPut-error-with-large-string-tables-tp29593089p2959… Sent from the monetdb-users mailing list archive at Nabble.com.

3 3

[MonetDB-users] June2010 SP2 failed to build on Windows 64
by Alex Bo. 02 Sep '10

02 Sep '10

Hi, I have successfully built June2010 SP2 on Windows 32 bit. However, the same on 64 bit fails in the Monet module while trying to build 'monet_options.c'. I am getting a bunch of errors related to ws2tcpip.h: C:\Program Files (x86)\Microsoft Visual Studio 8\VC\PlatformSDK\include\ws2tcpip.h(29) : error C2079: 'imr_multiaddr' uses undefined struct 'in_addr' C:\Program Files (x86)\Microsoft Visual Studio 8\VC\PlatformSDK\include\ws2tcpip.h(30) : error C2079: 'imr_interface' uses undefined struct 'in_addr' C:\Program Files (x86)\Microsoft Visual Studio 8\VC\PlatformSDK\include\ws2tcpip.h(38) : error C2079: 'imr_multiaddr' uses undefined struct 'in_addr' C:\Program Files (x86)\Microsoft Visual Studio 8\VC\PlatformSDK\include\ws2tcpip.h(39) : error C2079: 'imr_sourceaddr' uses undefined struct 'in_addr' C:\Program Files (x86)\Microsoft Visual Studio 8\VC\PlatformSDK\include\ws2tcpip.h(40) : error C2079: 'imr_interface' uses undefined struct 'in_addr' C:\Program Files (x86)\Microsoft Visual Studio 8\VC\PlatformSDK\include\ws2tcpip.h(46) : error C2079: 'imsf_multiaddr' uses undefined struct 'in_addr' Please advise. Thanks. -- View this message in context: http://old.nabble.com/June2010-SP2-failed-to-build-on-Windows-64-tp29593272… Sent from the monetdb-users mailing list archive at Nabble.com.

2 3

[MonetDB-users] Where is the right place to fill bug report ?
by Krzysztof Kowalczyk 02 Sep '10

02 Sep '10

Where is the right place to fill a bug report ? I am using sophisticated xquery on MonetDB on Windows x64 but my work has been stopped by a critical bug - probably in Pathfinder. The problem is that Monet fails to compile a query, instead it just take whole available memory and system needs hard restart. Sometimes I can rewrite query to different form with same functionality and this help, but it take a lot of time to find which code part is cause the error. Regards, Krzysztof Kowalczyk

3 4

[MonetDB-users] Joining Across Multiple Databases
by Hering Cheng 31 Aug '10

31 Aug '10

Hi, Is it possible to join two tables residing in different databases in a query? Here, by "database" I mean the result of running "monetdb create <database>". If so, what syntax should we use for the table names? Thanks. Hering Cheng

2 1

[MonetDB-users] Bulk Data Extraction & Embedded mserver5
by Hering Cheng 30 Aug '10

30 Aug '10

Hi, I was wondering if you can give me some ideas on how to retrieve data from MonetDB at very high throughput. The query will be very simple, issued against a single table. This table contains billions of records and I'd like to retrieve the data at a rate of at least one million records per second. From my naive tests with mclient running on the same 16-core machine as mserver5, I am only able to extract the data at about 20,000 records per second with the Feb2010 release. As a baseline case, with the same data in compressed (gzip'd) ASCII form stored in a regular file on the same (SAN) file system as MonetDB, I am able to read at the desired speed of one million records per second. I understand that there is communication overhead between mserver5 and mclient (or whatever client I use). Therefore, one possibility is to embed my application within mserver5. The embedded application basically just needs to be able to issue a SQL (or even MAL) query against the enclosing mserver5 and process the result set. If this is a viable approach, I'd like some guidance on where the hooks are. Thanks. Hering Cheng

5 8

[MonetDB-users] Installation error
by Alexander Rutkowski 30 Aug '10

30 Aug '10

Hi, I tried to install MonetDB on a Debian Linux system with ./monetdb-install.sh --prefix=/export/local-1/public/node/MonetDB --enable-sql --enable-xquery --enable-optimize but I got the error message ... test -z "/export/local-1/public/node/MonetDB/lib" || /bin/mkdir -p "/export/local-1/public/node/MonetDB/lib" /bin/sh ../../libtool --mode=install /usr/bin/install -c libstream.la '/export/local-1/public/node/MonetDB/lib' libtool: install: error: cannot install `libstream.la' to a directory not ending in /usr/local/lib make[6]: *** [install-streamLTLIBRARIES] Error 1 ... installing MonetDB failed I have no root rights so I need the --prefix argument. Thank you very much Alexander

3 3

[MonetDB-users] Announcement: New Jun2010-SP2 bugfix release of MonetDB suite
by Sjoerd Mullender 30 Aug '10

30 Aug '10

The MonetDB team at CWI/MonetDB BV is pleased to announce the Jun2010-SP2 bug fix release of the MonetDB suite of programs. More information (including release notes) on this release is available at <http://monetdb.cwi.nl/Development/Releases/Jun2010/>. The download location is <http://dev.monetdb.org/downloads/>. -- Sjoerd Mullender

1 0

[MonetDB-users] Response time
by Mitja Kleider 14 Aug '10

14 Aug '10

Hi, I am experiencing a huge difference between query response times of "mserver5 --readonly" and plain "mserver5". The difference is in the order of 25 sec vs 25 min. The disks are rather slow and there is a lot of disk write going on. (Not in readonly, obviously.) How can I investigate this further? Regards, Mitja

2 1

[MonetDB-users] Problems with huge SQL queries.
by Manuel Mayr 10 Aug '10

10 Aug '10

Hello everyone, I'm currently using Monet5 for some experiments with ActiveRecord but some SQL queries seem to be too big for the default configuration of monet. The prominent characteristic of the query is a huge IN part in the WHERE clause (nearly 13Kb). $ mclient -d tpch01 -h localhost < q1.sql MAPI = monetdb@localhost:50000 ACTION= read_line QUERY = SELECT states.* FROM states WHERE (states.country_id IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100,101,102,103,104,105,106,107,108,109,110,111,112,113,114,115,116,117,118,119,120,121,122,123,124,125,126,127,128,129,130,131,132,133,134,135,136,137,138,139,140,141 ERROR = !Connection terminated Is there a parameter that allows me to increase the SQL query buffer? Beste regards, Manuel PS.: I'm not sure if it is really the size of the SQL buffer since the query works when I modify it to run it against sys.tables, but it doesn't seem to return any result. Even prefix the query with "plan" doesn't come back within minutes. Maybe a performance bottleneck in the compiler or planner? -- Manuel Mayr (http://www-db.informatik.uni-tuebingen.de/team/mayr) Lehrstuhl Datenbanksysteme Wilhelm-Schickard Institut für Informatik Eberhard-Karls-Universität Tübingen

7 18

[MonetDB-users] geom performance
by Mitja Kleider 06 Aug '10

06 Aug '10

Hi, I am having trouble with the geom module (Jun2010-SP1). A query constructing a Point(x,y) from longitude (x) and latitude (y) takes much longer than expected, see below: # MonetDB server v5.20.3, based on kernel v1.38.3 # Serving database 'germany3', using 2 threads # Compiled for x86_64-pc-linux-gnu/64bit with 64bit OIDs dynamically linked # MonetDB/SQL module v2.38.3 loaded # MonetDB/GIS module v0.18.3 loaded sql>\d nodes CREATE TABLE "sys"."nodes" ( "id" int NOT NULL DEFAULT next value for "sys"."s_nodes", "long" decimal(10,7), "lat" decimal(10,7), CONSTRAINT "pk_nodes_id" PRIMARY KEY ("id") ); sql>select long,lat from nodes limit 1; +--------------+--------------+ | long | lat | +==============+==============+ | 8.9766191 | 47.7913748 | +--------------+--------------+ 1 tuple (1.0s) sql>select point(8.9766191, 47.7913748); +------------------------------------------------+ | point_single_value | +================================================+ | POINT (8.9766191000000006 47.7913747999999998) | +------------------------------------------------+ 1 tuple (0.897ms) sql>select point(long,lat) from nodes limit 1; +------------------------------------------------+ | point_long | +================================================+ | POINT (8.9766191000000006 47.7913747999999998) | +------------------------------------------------+ 1 tuple (1m 23s) And executing something similar for the whole table caused a segfault after eating a lot of virtual memory: sql>ALTER TABLE nodes ADD geom POINT DEFAULT NULL; sql>UPDATE nodes SET geom = Point(long,lat) WHERE geom IS NULL; In case you would like to try yourself, here is the import script and the parser to get such a database: http://gitorious.org/osm-poi-tools/monetdb/blobs/master/scripts/import.sh http://gitorious.org/osm-poi-tools/monetdb/blobs/master/osmparser/src/osmpa… Thanks for your help, Mitja

2 1

[MonetDB-users] Example of XRPC/Java application
by Ryad Ben-El-Kezadri 05 Aug '10

05 Aug '10

Hello, For whom interested by XRPC and Java GUI, I have posted a video http://www.youtube.com/watch?v=3WzM0ZOJw9k showing the great things that can be done with MonetDB/XQuery Regards Ryad PS: this is a networking application

3 2

[MonetDB-users] How to log every sql query?
by Nicolas Lemay 02 Aug '10

02 Aug '10

Hello, I've been using MonetDB for quite some weeks, but I never find the way to log every sql query my database receive. I've tried googling this, but did not find a lot of docs about that. Is it possible to do this? If yes, how can I accomplish this? Thank you!

1 0

[MonetDB-users] Loading directly into memory.
by Nozhup 31 Jul '10

31 Jul '10

Hi, The first query after a load action takes longer because the new data is still 'cold'. Is there a way in MonetDB to load data directly with a 'hot' status? Thank in advance. -- View this message in context: http://old.nabble.com/Loading-directly-into-memory.-tp29105569p29105569.html Sent from the monetdb-users mailing list archive at Nabble.com.

3 2

Re: [MonetDB-users] MonetDB-users Digest, Vol 50, Issue 11
by Shailendra Mishra 29 Jul '10

29 Jul '10

Hi Niels: I don't care if the log gets written on table creation. I tried the flag but it didn't do anything. The default value is 0. In the FebPS2 version it's a debug flag - actually setting it to 128 doesn't help. On closer examination it appears that it checks for if (debug & 1) then it would write a log. Setting debug to 128, would imply that debug & 1 = 0 and so no debug output is written. Can you tell me where the log writer code is ? Is it gdk_logger.c ? if yes, which is the function that writes the log_write_format, log_write_string, if not which ones. -- Shailendra ------------------------------ > > Message: 5 > Date: Wed, 28 Jul 2010 08:40:06 +0200 > From: Niels Nes <Niels.Nes(a)cwi.nl> > Subject: Re: [MonetDB-users] Creating table with nologging. > To: Communication channel for MonetDB users > <monetdb-users(a)lists.sourceforge.net> > Message-ID: <20100728064006.GA6626(a)cwi.nl> > Content-Type: text/plain; charset=utf-8 > > On Tue, Jul 27, 2010 at 04:28:56PM -0700, Shailendra Mishra wrote: > > I have the need for creating table with nologging enabled. I do > > understand that MonetDB is not meant to be used this way. However, I am > > trying to use it to ingest data off data-feeds. My db lives on a SSD, > > and the only DML that I execute is INSERT. I would appreciate if folks > > familiar with the server code could point me source files ?where I > > could make the change to achieve this. Thanks in advance for your help. > > > > -- Shailendra > > Shailendra > > I haven't tried this in a while but it used to be possible to switch > logging of inserts of using a debug flag (passed to the logger code). > > See the file src/storage/bat/bat_logger.mx > function bl_create. The first argument to logger_create should > be 128 to switch off logging. > > This will only disable that every insert will also write the inserted > values to the log. It still creates a log for your initial create table > statement. > > Indeed this is un documented and far from normal supported usage of > monetdb. > > Niels > >

2 2

[MonetDB-users] MonetDB XQuery newbie
by Stephen Thomas 28 Jul '10

28 Jul '10

I'm a newbie MonetDB user and have two issues out of the box. (I have downloaded and installed the latest stable version, on Mac OS X 10.6.3.) 1. I cannot get the command line history function to work with XQuery. I invoke the server with: Mserver --dbinit="module(pathfinder);" and invoke a client with: mclient -lx --history However, the command line history does not work; hitting the up arrow after a command results in "^[[A" being displayed: shell> mclient -lx --history Welcome to mclient, the MonetDB/XQuery interactive terminal Type \q to quit, \? for a list of available commands xquery>\<test1.xq <hello>World</hello> xquery>^[[A 2. When loading namespaces and modules, and making an error, the error can only be corrected *after* I restart the client and server processes. For example, when I try to import a module with namespace "http://www.foo.edu" but accidentally type "http://www.food.edu", I receive an error message. This is expected. However, if I try again to import the module again, this time with the correct namespace, I get the same exact error message (complete with "http://www.food.edu"). This continues unless I restart both the client and server. Is this a "feature", or am I doing something incorrectly? Thanks for your help, Steve

2 1

[MonetDB-users] Creating table with nologging.
by Shailendra Mishra 28 Jul '10

28 Jul '10

I have the need for creating table with nologging enabled. I do understand that MonetDB is not meant to be used this way. However, I am trying to use it to ingest data off data-feeds. My db lives on a SSD, and the only DML that I execute is INSERT. I would appreciate if folks familiar with the server code could point me source files where I could make the change to achieve this. Thanks in advance for your help. -- Shailendra

2 1

[MonetDB-users] XRPC/execute at not working from mclient
by Ryad Ben-El-Kezadri 27 Jul '10

27 Jul '10

Hello, I found some problems to run a remote Xquery from mclient. The query is the following (it works fine with the Java XRPC API but not with mclient). mclient -lx -s "execute at {'192.168.0.12'} {pf:collections()}" I get the following error. user(win32): password: MAPI = win32@localhost:50000 QUERY = execute at {'192.168.0.12'} {pf:collections()} ERROR = !ERROR: interpret: no matching MIL operator to 'reverse(void)'. !MAYBE YOU MEAN: ! reverse(BAT[any::1,any::2]) : BAT[any::2,any::1] !ERROR: interpret_params: sort(param 1): evaluation error. !ERROR: interpret_params: reverse(param 1): evaluation error. Did anyone had already successfully run such kind of query? PS1: there are 2 Mserver running: one on the local host/one on the remote host. The Java XRPC API does not need to run a Mserver on the local host, but mclient do (else I get a "initiating connection socket failed" error ) PS2:I have Mserver 4.39.0 on the remote linux server and Mserver 4.36.5 on the windows client (The query also fails with client/linux/4.39.0 & server/linux/4.39.0) PS3: the default example provided in the XRPC doc fails (no MIL operator) the same way: import module namespace test = "xrpc-test-function" at " http://192.168.0.12:50001/export/xrpc-mod.xq"; execute at {"192.168.0.12"} {test:add(100, 200)} Regards Ryad

2 1

Re: [MonetDB-users] about the loadfile size
by Stefan Manegold 20 Jul '10

20 Jul '10

Dear Kun Ren, first of all, I would highly appreciate if you could stick to using the MonetDB mailing lists for such questions about MonetDB instead of sending me private email. On the one hand, this increases the chances for you to get quick and suitable answers, as more people read the mailing lists. On the other hand, other users might have similar questions and benefit from the answers sent to the mailing lists. Having said that, given that you mention neither the version of MonetDB you're using or any specification of your Linux & Windows machine(s) (in particular whether MonetDB, the OS, and the hardware architecture are 32- or 64-bit), nor whether you get any error or not (What exactly does "auto stop mean"? What exactly happens?) I can only guess that your Linux system is 64-bit while your Windows system (or at least the MonetDB version you installed on it) is 32-bit. For a detailed discussion of the impact of 32-bit vs. 64-bit on the scalability of MonetDB, please see http://sourceforge.net/mailarchive/message.php?msg_name=20100319145945.GA91… Hope this helps. Stefan On Tue, Jul 20, 2010 at 05:19:40PM +0800, kun ren wrote: > Dear professer Stefan, > Recently,I use TPC-H test the Monetdb, On windows ,so I use "copy > ,, records,,,into,,from" to load data to Monetdb,when the size of the file > is about 700M,It is OK.But when the size is 1.4G,the Monetdb server is auto > stop when loading. But when I am in Linux, it is no problem. So I want to > ask why it is wrong on windows when the file is so large? > Best regards, -- | 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-4199 |

2 1

[MonetDB-users] Announcement: New Jun2010-SP1 bugfix release of MonetDB suite
by Sjoerd Mullender 16 Jul '10

16 Jul '10

The MonetDB team at CWI/MonetDB BV is pleased to announce the Jun2010-SP1 bug fix release of the MonetDB suite of programs. Lots of problems have been fixed, the most important one being the fix in the handling of database upgrades of databases created with the Feb2010 release to the current version. More information (including release notes) on this release is available at <http://monetdb.cwi.nl/Development/Releases/Jun2010/>. The download location has changed to <http://dev.monetdb.org/downloads/>. Please fix any bookmarks you may have. -- Sjoerd Mullender

1 0

[MonetDB-users] php mapi
by dariuszs 15 Jul '10

15 Jul '10

Hi, PHP does not return rows from the query when the result set reaches the size about 30KB, returns fine when it's less. Thanks.

4 8

[MonetDB-users] SQL text normalisation principles in relation to MonetDB
by Stefan de Konink 15 Jul '10

15 Jul '10

-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA512 Hi, I rather have a general question and I would like to know something about normalisation in MonetDB in relation to the underlying storage model. Since my first courses in SQL, that was PostgreSQL at the time. It was a strong advise always try to normalise duplications of data, because it would always increase your search preformance to look up numerical keys over string comparisons. Given that in MonetDB, text-strings are deduplicated in a best effort way, factually become numbers, how does this compare to the additional costs of: - enums - foreign key relations For example, some database schema's use enums (or worse: varchars) for two values. For example 'accessible', 'inaccessible'. It is clear that the storage size for this field BOOLEAN NOT NULL, would be sufficient and of the length of one bit (best case). Now within MonetDB the string data is currently optimised, would it therefore cost additional time to create a secondary table to join to vs using a string field. Thus, does normalisation for 'manual' deduplication, hurt or not? Stefan -----BEGIN PGP SIGNATURE----- Version: GnuPG v2.0.15 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEAREKAAYFAkw9uQAACgkQYH1+F2Rqwn2sjACeNnvDQS+cXjzs1USpomkL6rz8 7wkAn2rF2ZRvEJbQ1cX+oxJsWPcMoTk6 =RzDh -----END PGP SIGNATURE-----

3 2

[MonetDB-users] how to write function like oracle nvl()?
by Mike Jason 13 Jul '10

13 Jul '10

Hi, I need some help on how to write some special functions that monetdb currently doesn't have. E.g., I want to use the oralce funtion nvl(), which is described as follows: http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions105… Then, how can I write a function that has the some ability as the oralce's? By the way, now I use MonetDB-Feb2010.

2 5

[MonetDB-users] MonetDB - Function Sequence Error
by r8rdarklord 13 Jul '10

13 Jul '10

I have written a multithreaded ODBC program which tries to insert data to 11 tables in MonetDB. Each thread has a connection handle, a statment handle (comprising of insert into blah!....). Each thread on instantiation creates the handles (connection, statement etc.), connects to the db, binds meta-data, prepares query and executes the insert in a loop after reading the bind values from a file. The program runs fine to a point following which: SQLExecute: Error: SQLstate HY010, Errnr 0, Message [MonetDB][ODBC Driver 1.0]Function sequence error On closer examination it's the statement handle that is not in Prepared state. I thought that this maybe because the asynchronous execution of the previous execution of the commit isn't complete yet. I recoded my commit as follows: ret_sts = SQLEndTran(SQL_HANDLE_DBC, dbch, SQL_COMMIT); while (ret_sts == SQL_STILL_EXECUTING) ret_sts = SQLEndTran(SQL_HANDLE_DBC, dbch, SQL_COMMIT); check(ret_sts, SQL_HANDLE_DBC, dbch, "SQLEndTran"); However, the ret_sts returns -1 which means a SQL_ERROR This has me perplexed. Clearly the SQL is good else it wouldn't have executed a no. of times in the first place. The stmth also look good, so there is no stepping by threads on each others memory. I can post the whole program if needed. -- View this message in context: http://old.nabble.com/MonetDB---Function-Sequence-Error-tp29148194p29148194… Sent from the monetdb-users mailing list archive at Nabble.com.

1 0

Re: [MonetDB-users] [Monetdb-developers] about import *.csv
by Stefan de Konink 12 Jul '10

12 Jul '10

-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA512 You must reply to the mailinglist, and not to me directly. In order to help you with your segmentation fault you also must provide what version you are using. Op 12-07-10 06:57, kun ren schreef: > Thank you, when I am trying,something is wrong, > when i use command," mclient -l sql -d demo" > and then use command "create table t1(name char(100),number int);" to > create a table; > and then I create a csv file, the content of the file is: > renkun,1 > shaolong,2 > laoluo,3 > and then i use command as you said,"copy 3 records into t1 from 1.csv > delimiters ',','\n';" but the server said:"Segmentation fault > ",what wrong with me?thanks > Best regards; > > > > > > 2010/7/12 Stefan de Konink <stefan(a)konink.de <mailto:stefan@konink.de>> > > On Mon, 12 Jul 2010, kun ren wrote: > > Dear all, > I want to import *.csv into MonetDB,what interface do i will > use？ > > > Just mclient, create your table. And use copy into so for example > > copy i<<numofrows>> records into <<justcreatedtable>> from > 'filename' delimiters ',', '\n', null as ''; > > > Stefan > > -----BEGIN PGP SIGNATURE----- Version: GnuPG v2.0.15 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEAREKAAYFAkw6oSYACgkQYH1+F2Rqwn1OzwCglSIGOl+tKxMk+bbDPPaLUX+i plwAn0aeeJKs3TLiNk4cHPURovJqh8T7 =+Tr/ -----END PGP SIGNATURE-----

3 4

[MonetDB-users] hash accelerator
by Miguel Ángel Águila Lorente 09 Jul '10

09 Jul '10

Hello, I'm working with mal and I've seen in the web documentation, in the part of the inner core that there are built-in accelerator functions. As I can see, there is one search accelerator: hashing. ¿Could you tell me how to use this accelerator using mal language? Looking forward to hearing from you. Thank you. Mike

2 2

[MonetDB-users] MonetDB scalability
by Chris 09 Jul '10

09 Jul '10

Hi, I'm currently evaluating MonetDB for business reporting, and looking for information on scalability, and I can't seem to find any information on clustering options. Is there any support in MonetDB for clustering across multiple physical servers to improve performance? Thanks, Chris

6 10

[MonetDB-users] sql_debug magic number?
by Mark Bucciarelli 06 Jul '10

06 Jul '10

What's a good value for sql_debug in monetdb5.conf? I tried 1 and 17, but got nothing in merovingian.log. Looks like perhaps something was written to dbfarm/<db>/sql_logs/sql/log, but I couldn't make any sense of it. Thanks, m

1 0

Re: [MonetDB-users] SQL: count() counts distinct?
by Ying Zhang 04 Jul '10

04 Jul '10

Hi Niels, I have just updated my stable branch with your backport. It's fixed. Thanks! Jennie On Jul 04, 2010, at 18:42 , Niels Nes wrote: > On Sun, Jul 04, 2010 at 02:49:11PM +0200, Ying Zhang wrote: >> Dear all, >> >> I have a table "a" as the following: >> >> sql>select * from a; >> +------+------+ >> | x | y | >> +======+======+ >> | 1 | 1 | >> | 1 | 2 | >> | 1 | 3 | >> | 2 | 1 | >> | 2 | 2 | >> | 2 | 3 | >> | 2 | 1 | >> | 1 | 2 | >> | 2 | 3 | >> +------+------+ >> 9 tuples >> >> I want to count the number of distinct tuples in the table, so I did the following: >> >> sql>select count(*) from (select distinct x,y from a) as b; >> +------+ >> | L17 | >> +======+ >> | 2 | >> +------+ >> 1 tuple >> sql>select count(x) from (select distinct x,y from a) as b; >> +------+ >> | L20 | >> +======+ >> | 2 | >> +------+ >> 1 tuple >> sql>select count(y) from (select distinct x,y from a) as b; >> +------+ >> | L21 | >> +======+ >> | 3 | >> +------+ >> 1 tuple >> >> It looks like that count() automatically counts the distinct values of x and y, while "select distinct x,y from b" does give me what I want: >> >> sql>select distinct x, y from b; >> +------+------+ >> | x | y | >> +======+======+ >> | 1 | 1 | >> | 1 | 2 | >> | 1 | 3 | >> | 2 | 1 | >> | 2 | 2 | >> | 2 | 3 | >> +------+------+ >> 6 tuples >> >> Is this an expected behaviour of count()? If yes, how can I get the #tuples of "select distinct x,y from b"? >> >> Thanks in advance! >> >> Jennie >> >> PS> info about mserver5: > Jenny it seems one of the m5 optimizers breaks the count (select distinct) case. > In current this seems fixed again. > > Niels >> >> $ mserver5 >> # MonetDB server v5.18.6, based on kernel v1.36.6 >> # Serving database 'demo', using 4 threads >> # Compiled for x86_64-unknown-linux-gnu/64bit with 64bit OIDs dynamically linked >> # Found 7.751 GiB available main-memory. >> # Copyright (c) 1993-July 2008 CWI. >> # Copyright (c) August 2008-2010 MonetDB B.V., all rights reserved >> # Visit http://monetdb.cwi.nl/ for further information >> #warning: please don't forget to set your vault key! >> #(see [prefix]/stable/debug/etc/monetdb5.conf) >> # Listening for connection requests on mapi:monetdb://127.0.0.1:50000/ >>> include sql; >> # MonetDB/SQL module v2.36.6 loaded >> >> >> ------------------------------------------------------------------------------ >> This SF.net email is sponsored by Sprint >> What will you do first with EVO, the first 4G phone? >> Visit sprint.com/first -- http://p.sf.net/sfu/sprint-com-first >> _______________________________________________ >> MonetDB-users mailing list >> MonetDB-users(a)lists.sourceforge.net >> https://lists.sourceforge.net/lists/listinfo/monetdb-users > > -- > Niels Nes, Centrum Wiskunde & Informatica (CWI) > Science Park 123, 1098 XG Amsterdam, The Netherlands > room L3.14, phone ++31 20 592-4098 sip:4098@sip.cwi.nl > url: http://www.cwi.nl/~niels e-mail: Niels.Nes(a)cwi.nl

1 0

[MonetDB-users] SQL: count() counts distinct?
by Ying Zhang 04 Jul '10

04 Jul '10

Dear all, I have a table "a" as the following: sql>select * from a; +------+------+ | x | y | +======+======+ | 1 | 1 | | 1 | 2 | | 1 | 3 | | 2 | 1 | | 2 | 2 | | 2 | 3 | | 2 | 1 | | 1 | 2 | | 2 | 3 | +------+------+ 9 tuples I want to count the number of distinct tuples in the table, so I did the following: sql>select count(*) from (select distinct x,y from a) as b; +------+ | L17 | +======+ | 2 | +------+ 1 tuple sql>select count(x) from (select distinct x,y from a) as b; +------+ | L20 | +======+ | 2 | +------+ 1 tuple sql>select count(y) from (select distinct x,y from a) as b; +------+ | L21 | +======+ | 3 | +------+ 1 tuple It looks like that count() automatically counts the distinct values of x and y, while "select distinct x,y from b" does give me what I want: sql>select distinct x, y from b; +------+------+ | x | y | +======+======+ | 1 | 1 | | 1 | 2 | | 1 | 3 | | 2 | 1 | | 2 | 2 | | 2 | 3 | +------+------+ 6 tuples Is this an expected behaviour of count()? If yes, how can I get the #tuples of "select distinct x,y from b"? Thanks in advance! Jennie PS> info about mserver5: $ mserver5 # MonetDB server v5.18.6, based on kernel v1.36.6 # Serving database 'demo', using 4 threads # Compiled for x86_64-unknown-linux-gnu/64bit with 64bit OIDs dynamically linked # Found 7.751 GiB available main-memory. # Copyright (c) 1993-July 2008 CWI. # Copyright (c) August 2008-2010 MonetDB B.V., all rights reserved # Visit http://monetdb.cwi.nl/ for further information #warning: please don't forget to set your vault key! #(see [prefix]/stable/debug/etc/monetdb5.conf) # Listening for connection requests on mapi:monetdb://127.0.0.1:50000/ >include sql; # MonetDB/SQL module v2.36.6 loaded

1 0

[MonetDB-users] Announcement: New Jun2010 feature release of MonetDB suite
by Sjoerd Mullender 02 Jul '10

02 Jul '10

It is not June anymore, but nevertheless, the MonetDB team at CWI/MonetDB BV is pleased to announce the Jun2010 feature release of the MonetDB suite of programs. More information (including release notes) on this release is available at <http://monetdb.cwi.nl/Development/Releases/Jun2010/>. The download location has changed to <http://dev.monetdb.org/downloads/>. Please fix any bookmarks you may have. -- Sjoerd Mullender

1 2

[MonetDB-users] mclient --password
by Matthieu Guamis 01 Jul '10

01 Jul '10

Hello, Wow! the "--password" option is no more accepted by mclient utility :) Is there any alternative for automatic login? Thanks a lot -- *Matthieu Guamis* *Logo Axège <http://www.axege.com/>* /Axège// 23,rue Saint-Simon 63000 Clermont-Ferrand/ Tél: +33 (0)4 63 05 95 40 Fax: +33 (0)4.73.70.65.29 Email: matthieu.guamis(a)axege.com <mailto:matthieu.guamis@axege.com>

3 2

[MonetDB-users] Server requires 'SHA512' hash, but client support was not compiled in
by Matthieu Guamis 01 Jul '10

01 Jul '10

Hello, I get this message "Server requires 'SHA512' hash, but client support was not compiled in" when trying to connect to database. I installed MonetDB on RHEL5 system (more precisely Oracle Entreprise Linux with SHA256 not available in Openssl) from the script "monetdb-install.sh". In a previous post <http://sourceforge.net/mailarchive/forum.php?thread_name=20090731161927.GC2…> Fabian Groffen wrote: > Adding --with-password-backend=XXX to the configure call should be > enough. > But I'm not sure what does it mean (sorry) so my question is, how do you do that? Thanks a lot for your help PS: mserver5 --version MonetDB server v5.18.6 (64-bit), based on kernel v1.36.6 (64-bit oids) Copyright (c) 1993-July 2008 CWI Copyright (c) August 2008-2010 MonetDB B.V., all rights reserved Visit http://monetdb.cwi.nl/ for further information Found 2.9GiB available memory, 8 available cpu cores Configured for prefix: /home/admaxg/MonetDB Libraries: libpcre: 6.6 06-Feb-2006 (compiled with 6.6) openssl: OpenSSL 0.9.8e-rhel5 01 Jul 2008 (compiled with OpenSSL 0.9.8e-fips-rhel5 01 Jul 2008) Compiled by: admaxg(a)localhost.localdomain (x86_64-unknown-linux-gnu) Compilation: gcc -O2 Linking : ld -IPA -m elf_x86_64 -- *Matthieu Guamis* *Logo Axège <http://www.axege.com/>* /Axège// 23,rue Saint-Simon 63000 Clermont-Ferrand/ Tél: +33 (0)4 63 05 95 40 Fax: +33 (0)4.73.70.65.29 Email: matthieu.guamis(a)axege.com <mailto:matthieu.guamis@axege.com>

1 0

[MonetDB-users] sql: getting (or specifying) sequence name
by Mark Bucciarelli 01 Jul '10

01 Jul '10

Hi, Given a table with a sequence that controls the primary id, is there a SQL command I can run to get the name of that sequence? Alternatively, is there a way to specify the sequence name when creating the table? Thanks, m

3 5

[MonetDB-users] full text index?
by Patrick Schäfer 30 Jun '10

30 Jun '10

Hi, is there any support for a full text index? In the documentation it says: "port PF/Tijah to support the XQuery Full-Text specification." Thank you Patrick

1 0

[MonetDB-users] How to recover data from a crash?
by 李坤 30 Jun '10

30 Jun '10

Hi all. I plan to deploy monetdb into production environment, but i don't know how to recover from a crash. I find some sql log files in var\sql_logs\$dbname\sql\, can i use these log files to recover, and how? Or are there any other methods to recover from crashes?

3 3

[MonetDB-users] problem with monetdb
by Andrea Panetta 28 Jun '10

28 Jun '10

Hi at All. I've a problem with monetdb... I need to use it with jdbcDriver eith iSQL viewer but, when I user mi driver, and i load it with my credential, I've the following error Error: ResultSet is closed. It's strange because, when I try the driver with eclipse, my programs work well but, when I use this driver with an external software, like isql viewer, it don't work. How I can do to fix this error? Thank you so much. (PS: I've the monetdb jdbc driver version 1.15, and it should be the laste updated driver) -- Panetta Andrea Via Pertini 22 87055 - San Giovanni in Fiore (CS) tf - 329 / 3554684

3 3

[MonetDB-users] MonetDB aborts when importing over 10 million rows
by Luis Fernando Kauer 26 Jun '10

26 Jun '10

Hi, I'm still trying to evaluate MonetDB but I keep getting errors when trying to import over 10 million rows to a table. The server simply aborts and closes. I'm using MonetDB on Windows XP. Whenener I restart after the server had aborted it shows messages like this: #GDKmmap(71827456) fails, try to free up space [memory in use=18552560,virtual m emory in use=1837957120] #GDKmmap(71827456) result [mem=18552560,vm=1837957120] #GDKmmap(36175872) fails, try to free up space [memory in use=16499872,virtual m emory in use=1844903936] #GDKmmap(36175872) result [mem=16499872,vm=1844903936] #GDKmmap(143130624) fails, try to free up space [memory in use=15697728,virtual memory in use=1744764928] #GDKmmap(143130624) result [mem=15697728,vm=1744764928] #GDKmmap(143130624) fails, try to free up space [memory in use=13554656,virtual memory in use=1654456320] #GDKmmap(143130624) result [mem=13554656,vm=1654456320] #GDKmmap(143130624) fails, try to free up space [memory in use=8747960,virtual m emory in use=1650917376] #GDKmmap(143130624) result [mem=8747960,vm=1650917376] #GDKmmap(143130624) fails, try to free up space [memory in use=7946840,virtual m emory in use=1531052032] #GDKmmap(143130624) result [mem=7946840,vm=1531052032] !mvc_init: unable to create system tables !SQLException:SQLinit:Catalogue initialization failed !ERROR: HEAPextend: failed to extend to 71722596 for 14\1475tail !ERROR: HEAPextend: failed to extend to 35861298 for 15\1537tail !ERROR: HEAPextend: failed to extend to 142816048 for 15\1543tail !ERROR: HEAPextend: failed to extend to 142816048 for 15\1550tail !ERROR: HEAPextend: failed to extend to 142816048 for 15\1556tail !ERROR: HEAPextend: failed to extend to 142816048 for 15\1557tail #function user.main():void; # sql.prelude(); #end main; First I was trying to import files with about 3 million rows each but it started giving errors after the 3rd file. Then I tried to import through files with about 1 million rows each and I was able to import about 14 million rows, but after about 10 million rows I was only able to import one or two files in a row without MonetDB resetting. Is this a bug or a size limit for the available memory. Anything I could try? With regards, Luis Fernando

2 5

[MonetDB-users] "Parent Directory" links in download area give 404.
by Mark Bucciarelli 25 Jun '10

25 Jun '10

(1) open http://monetdb.cwi.nl/downloads/Ubuntu/ (2) click "Parent Directory" link (3) 404 I saw same behavior after opening http://monetdb.cwi.nl/downloads/sources/Feb2010-SP2 so it may be a global issue. Thanks, m

2 1

[MonetDB-users] Copy Into and .new files
by Alex Bo. 23 Jun '10

23 Jun '10

Hi, I am doing a lot of COPY INTOs, and I have noticed that sometimes while doing this MonetDB seems to hang while its process memory is slowly rising. I then noticed that while this is happening, files with a '.new' extension are added to the dbfarm folders. Now suddenly I reached a state in which no '.new' files are created at all, and the COPY INTOs work much faster because no 'hanging' occurs in which something is loaded into memory. What's the explanation for this? What can I do in order to always have COPY INTOs write directly to the final files without the '.new' intermediates? Thanks. -- View this message in context: http://old.nabble.com/Copy-Into-and-.new-files-tp28973215p28973215.html Sent from the monetdb-users mailing list archive at Nabble.com.

1 0

Re: [MonetDB-users] Embedded server in Java with JDBC
by Luis Fernando Kauer 23 Jun '10

23 Jun '10

Hi, Thanks for the quick reply. When I say embedded I mean like in HSQLDB, which allows to embed the db server in the application instead of starting the db server in a separete process accepting connections from all clients. My requirements are that the end user should not have to deal with the database directly, like start and stop it, not even need to install/configure it separetely. But I would like to access the data through JDBC driver because the libraries I'm working with require it. I also would like a small footprint. If that's not possible, then I would like to be able to start and stop the server through the application, even if it runs in a separete process. The application allows the user to analyse and audit a large volume of data on different tables and MonetDB seems to be really fast for this. Each user project will have its own set of tables so it would be nice to be able to set the database files where the user chooses for each project. If that's not possible I'll have to stick with different schemas for each project. I'm still having some problems importing large tables (> 10 million rows divided in 10 files with 1 million rows each) when MonetDB is reporting the following error SQLException:sql:Failed to extend the BAT, perhaps disk full SQLException:importTable:failed to import table and on the console appears many times the follwing: #GDKmmap(111673344) fails, try to free up space [memory in use=56663400,virtual memory in use=1521352704] #GDKmmap(111673344) result [mem=56663160,vm=1127612416] #GDKmmap: recovery ok. Continuing.. Is there any configuration or a way to make MonetDB work with large tables without having more memory available? Thanks for your help! Regards, Luis Fernando

2 1

[MonetDB-users] Embedded server in Java with JDBC
by Luis Fernando Kauer 22 Jun '10

22 Jun '10

Hi, I would like to use the embedded server within a Java application connecting through JDBC. Is it possible? I would probably need to start the embedded server before trying to connect through JDBC, right? Or is there a connection string that makes the jdbc driver start and use an embedded server? That would be great! If not, what's the best way to start the embedded server in Java? Thanks for this great and fast dabatase. Regards, Luis Fernando

2 1

[MonetDB-users] Mapiclient password prompt
by jz＠uva 21 Jun '10

21 Jun '10

I use the Mclient in conjunction with XQuery. When I do a CVS checkout, I get the version of Mapiclient where the -P option has disappeared, yet it still asks me for a password. My question is: how do I disable the password prompt? It is a bit too much to enter the password for thousands of queries, although I can type quite quickly. ;-) junte

3 4

[MonetDB-users] Get average value for intervals
by Gustav Koller 18 Jun '10

18 Jun '10

Hello, I've some time series data (time + a few value columns), now I want to generate average values for each hour. Currently I'm using the following query: SELECT (time - (time % 3600)) AS timegroup, AVG(value0) AS avgvalue0 FROM dbbench WHERE time > x AND time <= y GROUP BY timegroup; I've a few GB of data and the query is running for several seconds. Does anyone have any recommendations on how to improve the query for faster execution time? Greetings Gustav

2 2

[MonetDB-users] Limit memory
by Miguel Ángel Águila Lorente 17 Jun '10

17 Jun '10

Hello, I'm trying to limit the memory that monetdb can use during its executions. I'm using mal language and I've found this operation in order to do it. command mem_maxsize(v:lng) :void address set_mem_maxsize comment "Set the maximum usable amount of physical swapspace in KB."; When I use this operation the connection is terminated. I let here some examples: (I'm working with a machine with 32 GB) mal>status.mem_maxsize(4294967296:lng); MAPI = monetdb@localhost:50000 ACTION= read_line QUERY = status.mem_maxsize(4294967296:lng); ERROR = !Connection terminated mal>status.mem_maxsize(1073741824:lng); MAPI = monetdb@localhost:50000 ACTION= read_line QUERY = status.mem_maxsize(1073741824:lng); ERROR = !Connection terminated mal>status.mem_maxsize(1048576:lng); MAPI = monetdb@localhost:50000 ACTION= read_line QUERY = status.mem_maxsize(1048576:lng); ERROR = !Connection terminated Looking forward to hearing from you. Thank you. Mike

4 6

Re: [MonetDB-users] Meaning of: Scenario not initialized 'sql'
by Yel 17 Jun '10

17 Jun '10

Hi, I know this topic has been discussed last year, but with the MonetDB-Feb2010-SuperBall-SP2 installed, I repeatedly got the error like MALException:setScenario:Scenario not initialized 'sql' almost every time when I try to start mclient with "mclient -l sql -d dbname", even when i try it later on. Moreover, only "mclient -l mal -d dbname" is OK, sql, xquery, mil all got the error above. Any idea ? BTW: 1. MonetDB is installed by ./monetdb-install.sh --enable-sql --enable-debug 2. mapi_port is set to 50003 in etc/monetdb5.conf; Thanks. ---- Best Regards Ye Liang Sjoerd Mullender-2 wrote: > Alex Bo. wrote: > > Hi, > > > > Sometimes I get !MALException:setScenario:Scenario not initialized 'sql' > > from the server when I try to start it in scenarios that usually work > > without any problems. I can't reproduce it consistently, but sometimes I get > > it. What does this exception mean? > > > > Thanks. > > This exception means that the server is not yet ready for accepting SQL > connections. The solution is to try again later. > > When the server starts up, it pretty quickly starts accepting > connections, but initially only for MAL. When the SQL engine is > initialized (which may take a while if there was any recovery to be > done), it starts accepting SQL connections as well. You get this > exception when you connect before the SQL engine is ready. > > -- > Sjoerd Mullender

4 4

Re: [MonetDB-users] MonetDB XML - slow execution of query, with test on attribute
by Patrick Schäfer 17 Jun '10

17 Jun '10

Hi Jan, thank you for your response. I thought that all documents within a collection are stored within one (or several) relational tables? I am not sure, if merging all documents into one big document is an option. The documents are supposed to be updateable ... does that give a good performance for one large document? Patrick > Message: 7 > Date: Tue, 15 Jun 2010 21:58:44 +0200 > From: Jan Rittinger <jan.rittinger(a)uni-tuebingen.de> > Subject: Re: [MonetDB-users] MonetDB XML - slow execution of query > with test on attribute > To: Communication channel for MonetDB users > <monetdb-users(a)lists.sourceforge.net> > Message-ID: <702CE627-774E-47C5-B3B7-34A5431F20CF(a)uni-tuebingen.de> > Content-Type: text/plain; charset="windows-1252" > > Hi Patrick, > > the evaluation plan of your query looks quite bad -- with respect to what could be possible... > > If you could use 'doc("tu")' (with a single big document containing all small documents), you would get the result in *no* time, as then the attribute index is used. Unfortunately I'm not aware of the difference in the attribute index with respect to collections... > > Regards, > Jan >

2 1

Re: [MonetDB-users] MonetDB XML - slow execution of query with test on attribute
by Patrick Schäfer 17 Jun '10

17 Jun '10

Hi Peter, thank you for your answer. using pf:collection gives me a "Speicherzugriffsfehler" (/Segmentation fault)/ when executing the query you proposed. Is that a bug in MonetDB XML? replacing "pf:collection" by "fn:collection" leads to Trans 55.436 msec Shred 19.083 msec Query 29241.029 msec Print 0.207 msec Timer 29582.455 msec So it is basically the same query time :( Patrick > Hi, > > How about: > > smiles> { > for $i in pf:collection("tu") > where $i//xq:molecule/@id = "1" > return > <smile>{$i}</smile> > } </smiles> > > is that any faster? > > Peter > > >

1 0

Re: [MonetDB-users] Meaning of: Scenario not initialized 'sql'
by yel 17 Jun '10

17 Jun '10

Hi, I know this topic has been discussed last year, but with the MonetDB-Feb2010-SuperBall-SP2 installed, I repeatedly got the error like MALException:setScenario:Scenario not initialized 'sql' almost every time when I try to start mclient with "mclient -l sql -d dbname", even when i try it later on. Moreover, only "mclient -l mal -d dbname" is OK, sql, xquery, mil all got the error above. Any idea ? BTW: 1. MonetDB is installed by ./monetdb-install.sh --enable-sql --enable-debug 2. mapi_port is set to 50003 in etc/monetdb5.conf; Thanks. ---- Best Regards Ye Liang > > Sjoerd Mullender-2 wrote: > Alex Bo. wrote: > > Hi, > > > > Sometimes I get !MALException:setScenario:Scenario not initialized 'sql' > > from the server when I try to start it in scenarios that usually work > > without any problems. I can't reproduce it consistently, but sometimes I get > > it. What does this exception mean? > > > > Thanks. > > This exception means that the server is not yet ready for accepting SQL > connections. The solution is to try again later. > > When the server starts up, it pretty quickly starts accepting > connections, but initially only for MAL. When the SQL engine is > initialized (which may take a while if there was any recovery to be > done), it starts accepting SQL connections as well. You get this > exception when you connect before the SQL engine is ready. > > -- > Sjoerd Mullender >

1 0

[MonetDB-users] Meaning of: Scenario not initialized 'sql'
by Alex Bo. 17 Jun '10

17 Jun '10

Hi, Sometimes I get !MALException:setScenario:Scenario not initialized 'sql' from the server when I try to start it in scenarios that usually work without any problems. I can't reproduce it consistently, but sometimes I get it. What does this exception mean? Thanks. -- View this message in context: http://www.nabble.com/Meaning-of%3A-Scenario-not-initialized-%27sql%27-tp26… Sent from the monetdb-users mailing list archive at Nabble.com.

3 2

[MonetDB-users] MonetDB XML - slow execution of query with test on attribute
by Patrick Schäfer 15 Jun '10

15 Jun '10

Hi, I am trying to write a xquery-expression that does a test on an attribute (id="1"). Unfortunately the query execution time for this query is really bad (>30 seconds). Is there any way to find out why this query takes so long? 30 seconds for a test on one single attribute is just way to high for our use-case. I read that MonetDB automatically creates indices on all attribute - is there a way to test, if there was an index created for that attribute and if that index was used for query execution? The query (in File q3.xp) is supposed to return the molecule with id "1" declare namespace xq="http://www.xml-cml.org/schema"; <smiles> { for $i in collection("tu") where $i/xq:molecule/@id = "1" return <smile>{$i}</smile> } </smiles> The query is executed like this: # mclient -t -lx -G q3.xp ... Trans 55.684 msec Shred 18.818 msec Query 29335.766 msec Print 0.258 msec The database contains 1.000.000 XML-documents in a single collection, is updatable and is about 2601 MiB. A document looks similar to this one: <?xml version="1.0" encoding="UTF-8"?> <molecule id="1" xmlns="http://www.xml-cml.org/schema"> <bondArray atomRef1="..." order="..."/> <atomArray atomID="..." elementType="..." x3="..."/> <identifier convention="iupac:smile" value="..."/> <identifier convention="iupac:inchi" value="..."/> </molecule> We are using: "MonetDB/XQuery 0.36.5, based on the pathfinder <http://www.pathfinder-xquery.org> compiler and MonetDB 4.36.1 <http://monetdb.cwi.nl>" Thank you very much for any help! Patrick

3 2

[MonetDB-users] Decimal(p,s)
by Nozhup 15 Jun '10

15 Jun '10

What is the reason that MonetDB only supports a maximum p value of 18 while for example SQL Server (see link) supports up to 38? http://www.w3schools.com/SQL/sql_datatypes.asp -- View this message in context: http://old.nabble.com/Decimal%28p%2Cs%29-tp28892721p28892721.html Sent from the monetdb-users mailing list archive at Nabble.com.

2 1

[MonetDB-users] MIL and GROUP BY
by Hannes Rauhe 14 Jun '10

14 Jun '10

Hello together, I installed MonetDB with the help of the latest monetdb-install.sh and played around with MIL. Now I'm stuck with translating a simple SQL-Query containing a GROUPBY. After starting the server with # Mserver --dbname test --dbinit 'module(mapi); mil_start();' and the client with # ./mclient -lmil -utest -dname=test I tried the following: var a:= bat(oid,int); a.insert(1@0, 1995); a.insert(2@0, 1992); a.insert(3@0, 1993); a.insert(4@0, 1994); a.insert(5@0, 1995); a.insert(6@0, 1996); CTgroup(a).print(); the answer is: ERROR: const operation on a BAT ERROR: CMDconstCopy: operation failed. ERROR: interpret_params: print(param 1): evaluation error. Another try after loading the xtables-Module: mil>module("xtables"); mil>CTgroup(a).print(); +-------------------------------------------------------+ | � | +-------------------------------------------------------+ 1 tuple I used a few papers and http://monetdb.cwi.nl/MonetDB/Version4/Documentation/mil/index.html (Chapter 3.7 is what I'm looking for) as tutorial but didn't find anything helpful. Where's my mistake? Best Regards, Hannes

2 1

[MonetDB-users] Performance degradation
by Michael W Schatzow 11 Jun '10

11 Jun '10

hello, I am having performance degradation issues with repeated queries of monetdb. I am attempting to use monetdb for financial tick data and make sql queries requesting all quotes for a symbol on date x where time > y and time <z. Previously monetdb had worked great for this task and was extremely quick. However, I had a crash last week of the server it was built on. After this monetdb ran incredibly slow on the order of 25 times slower than it was running before. I rebuilt monetdb and it ran quickly for a day or two and started degrading in speed. Could someone give a hint or two on how I should go about debugging this? Is there a optimization task that I need to run? I am really at a loss of how I should handle this. I have been running queries via mclient -s from my program written in python. I previously found the python bindings too slow. Stats: cpu: 2 opteron 2222 os:cent os 5.4 memory 8gb database Size: only testing with csv data of 1.3 GB indexed by date and time built with options:--enable-optimise --enable-sql Thanks for any help, Michael

1 0

[MonetDB-users] libbat.dll crash
by Alex Bo. 09 Jun '10

09 Jun '10

Hi, I am experiencing a consistent Monet crash on an XP 32 bit machine, because of libbat.dll. The exact problem is mentioned here: http://old.nabble.com/--monetdb-Bugs-2982639---Crash-during-server-startup-… However, the link over the there to sourceforge doesn't work, so I can't comment or see what's going on with that bug. I tried it on Aug09-SP1 and Feb2010-SP2 (built from CSV), and both crashed. The link suggest that maybe older Monet dlls are laying around in the system32 folder, but I do not have any of the suggested dlls in there. Any suggestions on this issue? -- View this message in context: http://old.nabble.com/libbat.dll-crash-tp28826934p28826934.html Sent from the monetdb-users mailing list archive at Nabble.com.

4 3

[MonetDB-users] PF/Tijah Question: BM25F?
by jz＠uva 07 Jun '10

07 Jun '10

I was wondering whether it would be possible to use BM25F? http://trec.nist.gov/pubs/trec13/papers/microsoft-cambridge.web.hard.pdf Anyone else tried this before with an XQuery? junte

4 7

Re: [MonetDB-users] Server Crash Upon Connect
by Hering Cheng 28 May '10

28 May '10

Resent, with some of thread details removed due to limits on message length. On Thu, May 27, 2010 at 2:49 PM, Hering Cheng <hering.cheng(a)computer.org>wrote: > Hi, > > MonetDB core dumped after I had 10 Java threads retrieving a total of 23 > million records from a single table via JDBC. The threads actually all > completed successfully. The crash seems to occur when another process tried > to open a connection to the same database. I can reproduce the crash > consistently. > > After the core file is produced, processes can connect to MonetDB > successfully, with Merovingian starting up the dead mserver5 automatically. > > $ tail > ~/gaal/rdcuxsrv220-local-disk/chenher/monetdb/feb2010/var/log/MonetDB/merovingian.log > 2010-05-27 14:27:39 MSG merovingian[19478]: client rdcuxsrv220:61292 has > disconnected from proxy > 2010-05-27 14:27:42 MSG merovingian[19478]: client rdcuxsrv220:61296 has > disconnected from proxy > 2010-05-27 14:27:42 MSG merovingian[19478]: client rdcuxsrv220:61294 has > disconnected from proxy > 2010-05-27 14:27:42 MSG merovingian[19478]: client rdcuxsrv220:61298 has > disconnected from proxy > 2010-05-27 14:27:42 MSG merovingian[19478]: client rdcuxsrv220:61288 has > disconnected from proxy > 2010-05-27 14:27:42 MSG merovingian[19478]: client rdcuxsrv220:61304 has > disconnected from proxy > 2010-05-27 14:27:42 MSG merovingian[19478]: client rdcuxsrv220:61302 has > disconnected from proxy > 2010-05-27 14:32:33 MSG merovingian[19478]: proxying client > rdcuxsrv220:61784 for database 'taq' to mapi:monetdb://127.0.0.1:50001/taq > 2010-05-27 14:33:01 MSG merovingian[19478]: client rdcuxsrv220:61784 has > disconnected from proxy > 2010-05-27 14:34:06 MSG merovingian[19478]: database 'taq' (17289) has > crashed (dumped core) > > This is a Solaris SPARC server and MonetDB was built from the Feb2010 > source code using Sun Studio 12.1: > > $ ( ulimit -d $[32*1024*1024] && ulimit -n $[10*1024]; export > LD_PRELOAD_64=/usr/lib/64/libumem.so:${LD_PRELOAD_64}; export > LD_PRELOAD=/usr/lib/libumem.so:${LD_PRELOAD}; export > MONETDB5CONF=/GAAL/chenher/rdcuxsrv220-local-disk/chenher/monetdb/feb2010/etc/monetdb5.conf; > /GAAL/chenher/share/monetdb/distro-sparc-feb2010-64bit-debug/bin/mserver5 > --version; ) > MonetDB server v5.18.1 (64-bit), based on kernel v1.36.1 (64-bit oids) > Copyright (c) 1993-July 2008 CWI > Copyright (c) August 2008-2010 MonetDB B.V., all rights reserved > Visit http://monetdb.cwi.nl/ for further information > Found 32.0GiB available memory, 16 available cpu cores > Configured for prefix: > /GAAL/chenher/share/monetdb/distro-sparc-feb2010-64bit-debug > Libraries: > libpcre: 8.01 2010-01-19 (compiled with 8.01) > openssl: OpenSSL 0.9.8k 25 Mar 2009 (compiled with ) > libxml2: 2.6.23 (compiled with 2.6.23) > Compiled by: chenher@rdcuxsrv220 (sparc-sun-solaris2.10) > Compilation: cc -m64 -xcode=pic32 > -I/GAAL/chenher/share/hans_boehm_gc/distro-sparc-6.8-64bit/include/ -g > Linking : /usr/ccs/bin/ld -m64 -L/GAAL/chenher/share/openssl-64bit/lib > -L/GAAL/chenher/share/pcre-64bit/lib > -L/GAAL/chenher/share/hans_boehm_gc/distro-sparc-6.8-64bit/lib/ > > $ dbx ~/gaal/share/monetdb/distro-sparc-feb2010-64bit-debug/bin/mserver5 > ~/gaal/rdcuxsrv220-local-disk/chenher/monetdb/feb2010/var/MonetDB5/dbfarm/taq/core > For information about new features see `help changes' > To remove this message, put `dbxenv suppress_startup_message 7.7' in your > .dbxrc > Reading mserver5 > ... > Reading lib_logger.so.5.18.1 > Reading libuuid.so.1 > t@4 (l@4) terminated by signal SEGV (no mapping at the fault address) > Current function is putName > 174 for(l= nme[0]; l && namespace.nme[l]; l= > namespace.link[l]){ > (dbx) where > current thread: t@4 > =>[1] putName(nme = 0xffffffff6aec3928 "exportValue", len = 11U), line 174 > in "mal_namespace.c" > [2] initSQLreferences(), line 49 in "sql_gencode.c" > [3] SQLinitClient(c = 0xffffffff7f352628), line 379 in "sql_scenario.c" > [4] runPhase(c = 0xffffffff7f352628, phase = 5), line 363 in > "mal_scenario.c" > [5] runScenarioBody(c = 0xffffffff7f352628), line 392 in "mal_scenario.c" > [6] runScenario(c = 0xffffffff7f352628), line 438 in "mal_scenario.c" > [7] MSserveClient(dummy = 0xffffffff7f352628), line 368 in > "mal_session.c" > (dbx) threads > t@1 a l@1 ?() LWP suspended in __pollsys() > t@2 a l@2 SERVERlistenThread() LWP suspended in __pollsys() > t@3 a l@3 mvc_logmanager() LWP suspended in __pollsys() > o> t@4 a l@4 ?() signal SIGSEGV in putName() > t@5 a l@5 runDFLOWworker() sleep on 0x100c14b08 in > __lwp_park() > t@6 a l@6 runDFLOWworker() sleep on 0x100c14b08 in > __lwp_park() > t@7 a l@7 runDFLOWworker() sleep on 0x100c14b08 in > __lwp_park() > t@8 a l@8 runDFLOWworker() sleep on 0x100c14b08 in > __lwp_park() > t@9 a l@9 runDFLOWworker() sleep on 0x100c14b08 in > __lwp_park() > t@10 a l@10 runDFLOWworker() sleep on 0x100c14b08 in > __lwp_park() > t@11 a l@11 runDFLOWworker() sleep on 0x100c14b08 in > __lwp_park() > t@12 a l@12 runDFLOWworker() sleep on 0x100c14b08 in > __lwp_park() > t@13 a l@13 runDFLOWworker() sleep on 0x100c14b08 in > __lwp_park() > t@14 a l@14 runDFLOWworker() sleep on 0x100c14b08 in > __lwp_park() > t@15 a l@15 runDFLOWworker() sleep on 0x100c14b08 in > __lwp_park() > t@16 a l@16 runDFLOWworker() sleep on 0x100c14b08 in > __lwp_park() > t@17 a l@17 runDFLOWworker() sleep on 0x100c14b08 in > __lwp_park() > t@18 a l@18 runDFLOWworker() sleep on 0x100c14b08 in > __lwp_park() > t@19 a l@19 runDFLOWworker() sleep on 0x100c14b08 in > __lwp_park() > t@20 a l@20 runDFLOWworker() sleep on 0x100c14b08 in > __lwp_park() > t@21 a l@21 ?() sleep on 0xffffffff7f352a98 in __lwp_park() > t@22 a l@22 ?() sleep on 0xffffffff7f352d58 in __lwp_park() > t@23 a l@23 ?() sleep on 0xffffffff7f353018 in __lwp_park() > t@24 a l@24 ?() sleep on 0xffffffff7f3532d8 in __lwp_park() > t@25 a l@25 ?() sleep on 0xffffffff7f353598 in __lwp_park() > t@26 a l@26 ?() sleep on 0xffffffff7f353858 in __lwp_park() > t@27 a l@27 ?() sleep on 0xffffffff7f353b18 in __lwp_park() > t@28 a l@28 ?() sleep on 0xffffffff7f353dd8 in __lwp_park() > t@29 a l@29 ?() sleep on 0xffffffff7f354098 in __lwp_park() > t@30 a l@30 ?() sleep on 0xffffffff7f354358 in __lwp_park() > t@31 a l@31 runDFLOWworker() sleep on 0x1012f8988 in > __lwp_park() > t@32 a l@32 runDFLOWworker() sleep on 0x1012f8988 in > __lwp_park() > t@33 a l@33 runDFLOWworker() sleep on 0x1012f8988 in > __lwp_park() > ... > t@174 a l@174 runDFLOWworker() sleep on 0x102c9f908 in > __lwp_park() > t@175 a l@175 runDFLOWworker() sleep on 0x102c9f908 in > __lwp_park() > > Thanks. > Hering >

1 0

[MonetDB-users] Loading problems since FEB2010+SP1 release
by Nozhup 26 May '10

26 May '10

Since the FEB2010+SP1 release we're encountering loading issues. With the FEB2010 release we were able to load records until the HDD was full (500+ million records) on PCs with 2,3 or 24 GB ram memory without going to the SWAP. This was done by loading CSV-files with 1 million records each through the COPY INTO command (in the mclient and through a c-script). Since the FEB2010+SP1 release the system starts to SWAP after loading the first couple of million records. This results in increased loadtimes per loaded CSV-file: 1th million: 14 sec 2nd million: 26 sec 3rd million: 44 sec 4th million: 50 sec 5th million: 69 sec etc. We have tested this on several PCs and all PCs encounter the same problem. Test files used are the same for both releases as is the OS (Ubuntu 9.10). The only variable that has changed is MonetDB. -- View this message in context: http://old.nabble.com/Loading-problems-since-FEB2010%2BSP1-release-tp282876… Sent from the monetdb-users mailing list archive at Nabble.com.

5 7

[MonetDB-users] June Candidate release
by Nozhup 26 May '10

26 May '10

Hi, I'm having trouble making the June Candidate release. OS Used Ubuntu 10.04 Versions Tried 20100516/ 20100525/ 20100526/ Command Used ./monetdb-install.sh --enable-sql Part Of Error Log SQLExtendedFetch.c:43: error: conflicting types for 'SQLExtendedFetch' /usr/include/sqlext.h:1853: note: previous declaration of 'SQLExtendedFetch' was here make[7]: *** [libMonetODBC_la-SQLExtendedFetch.lo] Error 1 make[7]: Leaving directory `/var/tmp/MonetDB-1274859923/MonetDB-client/clients/src/odbc/driver' make[6]: *** [all] Error 2 make[6]: Leaving directory `/var/tmp/MonetDB-1274859923/MonetDB-client/clients/src/odbc/driver' make[5]: *** [all-recursive] Error 1 make[5]: Leaving directory `/var/tmp/MonetDB-1274859923/MonetDB-client/clients/src/odbc' make[4]: *** [all] Error 2 make[4]: Leaving directory `/var/tmp/MonetDB-1274859923/MonetDB-client/clients/src/odbc' make[3]: *** [all-recursive] Error 1 make[3]: Leaving directory `/var/tmp/MonetDB-1274859923/MonetDB-client/clients/src' make[2]: *** [all] Error 2 make[2]: Leaving directory `/var/tmp/MonetDB-1274859923/MonetDB-client/clients/src' make[1]: *** [all-recursive] Error 1 make[1]: Leaving directory `/var/tmp/MonetDB-1274859923/MonetDB-client/clients' -- View this message in context: http://old.nabble.com/June-Candidate-release-tp28677743p28677743.html Sent from the monetdb-users mailing list archive at Nabble.com.

3 5

[MonetDB-users] Mapi - mal
by Petros Tsialiamanis 20 May '10

20 May '10

Hello, I would like to get the oid of a stored uri using the Mapi library. The mal commands that need to be executed are: tokenizer.open("rdf"); id := tokenizer.locate("http://uri"); io.print(); tokenizer.close(); I tried to run the following program ...tokenizer.open("rdf");... ... if ((hdl = mapi_query(dbh, "tokenizer.locate(\"http://uri\")")) == NULL || mapi_error(dbh) != MOK) die(dbh, hdl); if (mapi_close_handle(hdl) != MOK) die(dbh, hdl); while (mapi_fetch_row(hdl)) result=mapi_fetch_field(hdl, 0); ... ...tokenizer.close();... .. but I get the following error Mapi.mx:5141: mapi_fetch_row: Assertion `(hdl)->mid' failed. Could you tell me how can I solve this problem? Kind regards, Petros

2 2

[MonetDB-users] Loading and querying on the same database.
by Nozhup 19 May '10

19 May '10

Loading and querying simultaneously seems to slowdown the database significantly. I can understand if the queries became slower during loading, but it seems that only the first query before loading is finished is slow and all queries coming after. I can only fix the slowdown by using "killall mserver5" in a terminal, which is not an acceptable solution. I start 1 "copy into" action with one connection and at the same time a series of simple queries on another connection. The Simple query looks something like this: SELECT x,y FROM table WHERE x = '519'; Used OS: Ubuntu 9.10 and 10.04 MonetDB: Feb2010 release (SP! and SP2 are no option due to them giving loading problems) Results Query: 1 --> 0.000191 SECONDS : : : Query: 10501 --> 0.000170 SECONDS Query: 10502 --> 0.000169 SECONDS Query: 10503 --> 0.000169 SECONDS Query: 10504 --> 0.000172 SECONDS Query: 10505 --> 0.000172 SECONDS Query: 10506 --> 0.000174 SECONDS Query: 10507 --> 0.000178 SECONDS Query: 10508 --> 1.199149 SECONDS Load : 1 --> 3.238532 SECONDS Query: 1-10509 --> 1.238804 SECONDS Query: 1-10510 --> 1.175408 SECONDS Query: 1-10511 --> 1.141172 SECONDS Query: 1-10512 --> 1.132525 SECONDS Query: 1-10513 --> 1.159554 SECONDS Query: 1-10514 --> 1.158303 SECONDS Query: 1-10515 --> 1.160997 SECONDS How do I solve this or is this an error in MonetDB? Thanks in advance. -- View this message in context: http://old.nabble.com/Loading-and-querying-on-the-same-database.-tp28605728… Sent from the monetdb-users mailing list archive at Nabble.com.

2 5

[MonetDB-users] updates in a transaction
by Min Lu 19 May '10

19 May '10

Hi, I tried to update a column of a record more than once within a transaction. But it seemed that only the first update took effect. For example, for a table "CREATE TABLE people (id TINYINT PRIMARY KEY, name VARCHAR(128) NOT NULL)", sql>select * from people; +------+-----------------------------+ | id | name | +======+=============+ | 0 | Phil Ivey | | 1 | Michael Jordan | | 2 | Lionel Messi | +------+-----------------------------+ sql>start transaction; auto commit mode: off sql>update people set id = -1 where name='Phil Ivey'; 1 affected row sql>select * from people; +------+-----------------------------+ | id | name | +======+=============+ | 1 | Michael Jordan | | 2 | Lionel Messi | | -1 | Phil Ivey | +------+-----------------------------+ sql>update people set id = -2 where name='Phil Ivey'; 1 affected row sql>select * from people; +------+-----------------------------+ | id | name | +======+=============+ | 1 | Michael Jordan | | 2 | Lionel Messi | | -1 | Phil Ivey | +------+-----------------------------+ sql>commit; auto commit mode: on sql>select * from people; +------+-----------------------------+ | id | name | +======+=============+ | -1 | Phil Ivey | | 1 | Michael Jordan | | 2 | Lionel Messi | +------+-----------------------------+ It is not what we would expect, isn't it? My MonetDB version is v5.18.3 Min

3 5

[MonetDB-users] crash when doing count(distinct col)
by Sam Mason 18 May '10

18 May '10

Hi, I've just pulled a largish dataset into monetdb (419851473 rows, 8 columns) and it's crashing when I try to do the following: select count(distinct a) from calldata; "a" is an integer column and monetdb gets the right answer of 886693 when I run either: select count(a) from (select a from calldata group by a) x; or select count(a) from (select distinct a from calldata) x; I'm running the latest 64bit .deb packages under ubuntu 10.4. The following is what I get back from mclient: sql>select count(distinct a) from calldata; MALException:!ERROR: HEAPalloc: Insufficient space for HEAP of 3358812160 bytes. ERROR: HEAPalloc: Insufficient space for HEAP of 3358812160 bytes. ERROR: HEAPalloc: Insufficient space for HEAP of 3358812160 bytes. Timer 93948.763 msec 1 rows I've not got much swap space, does it just happen to need it for the first version but not the other versions? -- Sam http://samason.me.uk/

2 2

[MonetDB-users] MonetDB performance
by dariuszs 18 May '10

18 May '10

Hi, So what happens (performance wise) when you have 50GB of memory, 300GB of data and on average 100 concurrent users using all the tables and columns in your database? Thanks. Dariusz.

7 13

[MonetDB-users] MonetDB/SQL: bulk-loading large data sets & adding key constraints
by Stefan Manegold 15 May '10

15 May '10

Dear all, derived from recent experiments and intensive testing, here are some recommendations for bulk-loading large data sets (in particular those larger than your available main memory) in MonetDB/SQL and adding constraints, i.e., primary and foreign keys. First of all, bulk loading should be done from (possibly gzip or bzip2 compressed) CSV files using the SQL `COPY INTO` command; cf., http://monetdb.cwi.nl/SQL/Documentation/Data-Manipulation.html In case your tables come with primary or foreign key constraints, it is recommended to omit these constrains when initially creating the tables. Then bulk-load (`COPY INTO`) your data. Once the data is loaded add the key constraints one by one using the SQL `ALTER TABLE ... ADD CONSTRAINT ...` command. Note, though, that the current Feb2010 release of MonetDB/SQL might experience some performance problems when adding constraints on data sets larger than main memory on multi-core machines. The reason it a non-optimal query translation and plan parallelization. For the time being, i.e., until we manage to fix this problem properly, it is recommended to resort to the optimizer pipeline of the Nov2009 release (for insiders, this effectively means disabling the "mitosis" optimizer), (only) while adding the key constraints. To do so, simply activate the Nov2009 optimizer pipeline via SET optimizer='nov2009_pipe'; just before adding the key constraints via `ALTER TABLE ... ADD CONSTRAINTS` and then restore the default optimizer pipeline via SET optimizer='default_pipe'; once you're done with adding constraints. For TPCH scale factor 100 (100 GB) on a 8 GB 4-core machine, this reduces the total time to add all key constraints from ~1.5h to ~1h. For SSB scale factor 100 (100 GB) on a 8 GB 4-core machine, this reduces the total time to add all key constraints from ~4h to ~1h. Hope this helps. Please don't hesitate to comment or ask questions. We'll let you know, one we managed to fix this problem properly. Kind regards, Stefan -- | 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-4199 |

1 0

[MonetDB-users] Help,how to create hash index on table
by 冯亮 15 May '10

15 May '10

Hello, Current I am using MonetDB server v5.18.3 , and as i see in Wiki , MonetDB support hash index ,I intended to create a hash index on a table. But how to do it , i have referred to sqlmanual downloaded from monetdb.cwi.nl, but it simply wrote following format: CREATE [UNIQUE] INDEX <index_name> ON <table_name> ( <column_name>, ...) ; Is that Btree index or hash index or others? Does MonetDB support multiple column hash index? I am looking forward to your reply. Thanks a lot. Andrew. Feng

4 3

[MonetDB-users] Help,how to create hash index on table
by fll 09 May '10

09 May '10

Hello, Current I am using MonetDB server v5.18.3 , and as i see in Wiki , MonetDB support hash index ,I intended to create a hash index on a table. But how to do it , i have referred to sqlmanual downloaded from monetdb.cwi.nl, but it simply wrote following format: CREATE [UNIQUE] INDEX <index_name> ON <table_name> ( <column_name>, ...) ; Is that Btree index or hash index or others? Does MonetDB support multiple column hash index? I am looking forward to your reply. Thanks a lot. Andrew. Feng

2 1

[MonetDB-users] Move of SourceForge bugtracker to MonetDB Bugzilla
by Fabian Groffen 07 May '10

07 May '10

This message is important for all that used to report bugs on our SourceForge tracker, or are planning to report bugs in the future. With the move of our CVS repository to a new Mercurial repository, we have also migrated the bugtracker from SourceForge to a Bugzilla instance. The old SourceForge trackers are no longer in use. Please DO NOT file any bugs there, as they will get lost. We have removed their presence as far as possible to avoid any confusion here. The new location to search, read and file new bugs is here: http://dev.monetdb.org/bugzilla/ (Please bear with us while we try to get the interface/skin well suited for our project.) All bugs that were previously on the SourceForge trackers have been migrated to the new Bugzilla instance. The bug numbers could not be retained, hence all have been renumbered. See [1] for dealing with this in Bugzilla. All users that have ever reported a bug, commented on a bug, or been an assignee for a bug on SourceForge have been imported in Bugzilla. This means that for all those users a <sf-nickname>@users.sourceforge.net account has been created. Exception to this are users who work at CWI, whose addresses have been mapped to their CWI address. If you want to use your migrated account, simply click the "Forgot Password" link on the Bugzilla main page. It will send an email to your (SourceForge) email address with a token to allow you to set a password. If you are a new user, you can simply sign up on our Bugzilla using the "Open a New Account" button. In both cases, make sure you look through the preferences, in particular the "Email Preferences". For migrated accounts these preferences are set to exclude sending any bug email, which may or may not be what you prefer. Feel free to ask for any help if problems arise. [1] Searching an old bug using its bugnumber: If you have an old SourceForge bug number and need to find tha associated bug in Bugzilla, open up an advanced search, and enter the old bug number in the URL field. Make sure that you select the appropriate bug resolutions (e.g. include RESOLVED) and search. Each migrated bug has an URL set to the original bug on SourceForge, hence is easily found this way.

3 2

[MonetDB-users] Parallel Processing
by Nafees Ur Rehman 04 May '10

04 May '10

Hi, Does monetdb has support for parallel processing? If some can guide me on that. Regards Nafees Ur Rehman

4 8

[MonetDB-users] trigger ambiguous issue
by Huabing 03 May '10

03 May '10

I have following trigger statement: CREATE TRIGGER delete_pos_cp AFTER DELETE ON cp referencing old row as o FOR EACH ROW WHEN o.new_id_ IS NOT NULL UPDATE cp SET old_id_ = o.old_id_ WHERE rec_id_ = o.rec_id_; Following error is issued: SELECT: identifier 'rec_id_' ambiguous The last "o.rec_id_" gives rise to this ambigious. Any suggestion on this.

2 1

Re: [MonetDB-users] sql query size
by dariuszs 27 Apr '10

27 Apr '10

Hi, This still does not work in the latest version - Feb2010+SP2 - on Windows XP 32bit, Windows Server 2003 64bit,Ubuntu 9.10 64 bit,Fedora 12 64bit. Dariusz. Martin Kersten wrote: > Thanks for the example, we will look into it. > > dariuszs wrote: >> Martin, >> 1) Table schema >> sql>\d zip_codes >> CREATE TABLE "sys"."zip_codes" ( >> "zip_code" varchar(5) >> ); >> sql> >> >> 2) From mclient >> sql>\< /tmp/test.sql >> MAPI = monetdb@localhost:50000 >> ACTION= read_line >> QUERY = select count(*) from zip_codes WHERE zip_code IN >> ('00501','00544','01001','01002','01003','01004','01005','01007','01008','01009','01010','01011','01012','01013','01014','01020','01021','01022','01026','01027','01028','01029','01030','01031','01032','01033','01034','01035','01036','01037','01038','01039','01040','01041','01050','01053','01054','01056','01057','01059','01060','01061','01062','01063','01066','01068','01069','01070','01071','01072','01073','01074','01075','01077','01079','01080','01081','01082 >> >> ERROR = Connection terminated >> sql> >> 3) From shell >> root@monetdb:/tmp# mclient -lsql < test.sql >> MAPI = monetdb@localhost:50000 >> ACTION= read_line >> QUERY = select count(*) from zip_codes WHERE zip_code IN >> ('00501','00544','01001','01002','01003','01004','01005','01007','01008','01009','01010','01011','01012','01013','01014','01020','01021','01022','01026','01027','01028','01029','01030','01031','01032','01033','01034','01035','01036','01037','01038','01039','01040','01041','01050','01053','01054','01056','01057','01059','01060','01061','01062','01063','01066','01068','01069','01070','01071','01072','01073','01074','01075','01077','01079','01080','01081','01082 >> >> ERROR = Connection terminated >> root@monetdb:/tmp# >> 4) SQL >> >> >> Dariusz >> >> Martin Kersten wrote: >>> Hi Dariusz, >>> >>> A bug or a system limitation. It should at least never crash. >>> >>> How did you submit the query? To mclient? or mserver? or through >>> a mapi libary call? >>> >>> I would have to look into the SQL parser. The underlying stream would >>> expand upon need. It is, however, an interesting query. What kind of >>> application generates it? >>> >>> regards, Martin >>> >>> ps. we should keep a profile of users to avoid asking platform >>> details. >>> >>> dariuszs wrote: >>>> Hi Martin, >>>> Then I guess it's a bug. Please try running a query over 44k, >>>> you'll get >>>> MAPI = monetdb@localhost:50000 >>>> ACTION= read_line. >>>> QUERY = select count(*) from plk100 WHERE c15 IN >>>> ('00501','00544','01001','01002','01003'.......... >>>> ERROR = Connection terminated >>>> Any query under 44k runs fine, so I'm assuming the limit is 64k >>>> (query + other packet data) >>>> >>>> Thanks. Dariusz. >>>> >>>> >>>> >>>> Martin Kersten wrote: >>>>> dariuszs wrote: >>>>> >>>>>> Hi, >>>>>> Is there any limit on how big the sql query can be (in bytes)? >>>>>> Thanks. Dariusz. >>>>>> >>>>>> >>>>> As far as i am aware no. >>>>> >>>>>> >>>>>> ------------------------------------------------------------------------------ >>>>>> >>>>>> Download Intel® Parallel Studio Eval >>>>>> Try the new software tools for yourself. Speed compiling, find bugs >>>>>> proactively, and fine-tune applications for parallel performance. >>>>>> See why Intel Parallel Studio got high marks during beta. >>>>>> http://p.sf.net/sfu/intel-sw-dev >>>>>> _______________________________________________ >>>>>> MonetDB-users mailing list >>>>>> MonetDB-users(a)lists.sourceforge.net >>>>>> https://lists.sourceforge.net/lists/listinfo/monetdb-users >>>>>> >>>>> >>>>> >>>>> ------------------------------------------------------------------------------ >>>>> >>>>> Download Intel® Parallel Studio Eval >>>>> Try the new software tools for yourself. Speed compiling, find bugs >>>>> proactively, and fine-tune applications for parallel performance. >>>>> See why Intel Parallel Studio got high marks during beta. >>>>> http://p.sf.net/sfu/intel-sw-dev >>>>> _______________________________________________ >>>>> MonetDB-users mailing list >>>>> MonetDB-users(a)lists.sourceforge.net >>>>> https://lists.sourceforge.net/lists/listinfo/monetdb-users >>>>> >>>>> >>>>> >>>>> >>>> >>>> >>>> >>> >>> >>> >>> >> > > > >

1 0

[MonetDB-users] ODBC connection problem.
by Nozhup 27 Apr '10

27 Apr '10

I get the following error when trying to connect a MonetDB database with 'OpenOffice.org Database' through the ODBC driver. [unixODBC][MonetDB][ODBC Driver 1.0]Client unable to establish connection .ini-files used: odbc.ini: [MonetDB_User] Description = Default MonetDB Data Source Driver = MonetDB Host = localhost Port = 50000 Database = TestDB User = monetdb Password = monetdb Debug = odbcinst.ini [MonetDB] Description = MonetDB Driver Driver = Driver64 = /home/breplu/MonetDB/lib/libMonetODBC.so Setup = Setup64 = /home/breplu/MonetDB/lib/libMonetODBCs.so UsageCount = 1 CPTimeout = CPReuse = MonetDB version: Feb2010 release A ODBC connection between MySql and OpenOffice works correctly, so I don't think the problem lies with OpenOffice. Am I doing something wrong or is this a know issue with MonetDB? Thanks in advance. -- View this message in context: http://old.nabble.com/ODBC-connection-problem.-tp28364397p28364397.html Sent from the monetdb-users mailing list archive at Nabble.com.

1 1

[MonetDB-users] SMELLIEST data types???
by Steve Schmechel 27 Apr '10

27 Apr '10

I'm guessing that this was meant to be "SMALLINT". From: http://monetdb.cwi.nl/SQL/Documentation/SQL-Features.html Feature ID Feature name S/N B011-B017 Embedded Language support. Core SQL:1999 says that at least one of Embedded Ada, Embedded C, Embedded Cobol, Embedded Fortran, Embedded MUMPS, Embedded Pascal or Embedded PL/I 1 should be supported. N E011 Numeric data types (FULL support) S E011-01 INTEGER and SMELLIEST data types ^^^^^^^^^ I know I read that page before, funny I never noticed that. (I have to admit, I did check the standard just to make sure it wasn't in there and I had just missed it all these years.) I have seen a few database tables that had some really stinky data types, but I'm glad I never came across one that contained the smelliest. :-) Steve

2 1

Re: [MonetDB-users] SQL Case Statement
by Steve Schmechel 27 Apr '10

27 Apr '10

--- On Mon, 4/26/10, Mark Bucciarelli <mkbucc(a)gmail.com> wrote: > From: Mark Bucciarelli <mkbucc(a)gmail.com> > $ cd src/monet_2009Nov/ > $ find . -name "*sql*.y" > ./sql/src/server/sql_parser.y > $ > > Well commented YACC source. > > I use that when I have a SQL question. This file is not in any of the tarballs, including Nov2009. Do you have to generate it? If so, that would be a horrible way to document an external API: 1) Download source code. 2) Run lexical analyzer on source code. 3) Try to understand code comments. 4) Retry your query in the client application. Is there another document somewhere? If the comments are very good, maybe that could be quickly refined to become the official documentation. Thanks, Steve

3 2

[MonetDB-users] SQL Case Statement
by Hering Cheng 26 Apr '10

26 Apr '10

Hi, Is there any plan to add support for CASE in SELECT clauses to MonetDB? Thanks. Hering

4 6

[MonetDB-users] how to grant privileges to creating tables?
by Min Lu 25 Apr '10

25 Apr '10

Dear MonetDB users, Is it all possible to grant the privilege of "creating tables" to some users in MonetDB? For example, something like "grant create table to user_name" or similar? Thanks, Min

2 2

[MonetDB-users] how to grant privileges to creating tables? (more specific version)
by Min Lu 25 Apr '10

25 Apr '10

Dear all, I just want to address my question in last message in a more specific way. Actually I am wondering if I can grant privileges to creating local/global temporary tables to a normal user other than monetdb. And what is the complete list of priveleges known to MonetDB? Thanks, Min

2 2

[MonetDB-users] Fedora 12 cannot install public key
by dariuszs 23 Apr '10

23 Apr '10

Hi, Just trying to install public key on another server [root@monetdb ~]# rpm --import http://monetdb.cwi.nl/downloads/MonetDB-GPG-KEY error: http://monetdb.cwi.nl/downloads/MonetDB-GPG-KEY: import failed. [root@monetdb ~]#

1 1

[MonetDB-users] mal index
by Miguel Ángel Águila Lorente 23 Apr '10

23 Apr '10

Hello, I'm working in a research problem and we need to create a graph database. We think that MonetDB can be useful to represent this graph since the use of bats may make the graph traversal efficient. Nevertheless, SQL is limited for the type of operations that we need to do, and that is why we have decided to resort to MAL. Our database is like this: - Nodes: a) node ident b) name - Edges: a) edge ident b) head node ident c) tail node ident One of the operations is to find the node with maximum out degree and, after that, to do a BFS using this node as the source. I have stored all the information in bats: batNode1=[oid,node ident] batNode2=[oid,name] batEdge=[oid, edge ident] batEdgeHead=[oid, head node ident] batEdgeTail=[oid,tail node ident] For getting the node with the maximum out degree, I access batEdgeHead and store the number of occurrences of every node id in a batAux. batAux=[node ident, times] After that I only select the node id with the largest number of occurrences. Now, we are ready to do the BFS. In every iteration, I have to save all the tail nodes for exploring them in the next generation. This is the pseudocode explanation: Put the node id to NodesToVisit While there are NodesToVisit currentNode=NodesToVisit; edgeToVisit=get edges idents from batEdgeHead from currentNode ident While there are edgesToVisit currentEdge=edgesToVisit nodeFinal=get tail node ident from batEdgeTail from currentEdge ident If notExist nodeFinal in NodesToVisit put nodeFinal in NodesToVisit endIf endWhile endWhile The main problem happens when I perform a select from batEdgeHead from the ident node. batEdgeHead might become very large and accessing it might be very expensive in terms of time. Is there any kind of indexing? I tried with crackers and AVL index but, if I am not wrong, this is only useful if I had to do the same selection more than once. The number of the nodes is 325.000 and the number of edges 925.000. At the end of this e-mail you will find the code. May any one help in improving the performance of this code? Best regards, Miquel Àngel Àguila Bat equivalence: batEdge, batNode1 --> b1:= bat.new(:oid,:lng); batNode2 --> b2:= bat.new(:oid,:str); batEdgeHead --> rh:= bat.new(:oid,:lng); batEdgeTail --> rt:= bat.new(:oid,:lng); batType --> b4:= bat.new(:oid, int) (Type: 1. nodeType1, 2. nodeType2 , 3. edgeType1, 4. edgeType2, 5. edgeType3) Loaded the bats, here is the code: Maxium out degree:----------------------------------------------------- edart:=algebra.select(b4,3); nodOut:=algebra.semijoin(rh,edart); res:=bat.new(:lng,:int); valor:=0; barrier (mloop16:lng ,h16:oid ,tl16:lng ) := bat.newIterator(nodOut); bite1:=algebra.exist(res,tl16); barrier ifpart:= bite1; valor:=algebra.find(res, tl16); valor:=calc.+(valor,1); res:=bat.replace(res,tl16,valor); exit ifpart; barrier elsepart:= calc.!=(bite1,true); bat.insert(res,tl16,1); exit elsepart; redo (mloop16:lng ,h16:oid ,tl16:lng ) := bat.hasMoreElements(nodOut); exit mloop16:lng ; maxOutDegree:=aggr.max(res); node:=algebra.select(res,maxOutDegree); io.print(node); nodart:=algebra.select(b4,1); nodart2:=algebra.semijoin(b1,nodart); ident2:=algebra.join(nodart2,node); name2:=algebra.semijoin(b3,ident2); secondtime:=alarm.time(); #Traversals------------------------------------------------------------ node2:=algebra.semijoin(b1,name2); aux:=0:lng; idoid:=0@0; barrier (mloop17:lng ,h17:oid ,tl17:lng) := bat.newIterator(node2); aux:=tl17; idoid:=h17; exit mloop17:lng ; nod:=bat.new(:lng,:lng); dif:=bat.new(:lng,:lng); vis:=bat.new(:lng,:lng); ar:=bat.new(:oid,:oid); tr:=bat.new(:oid,:oid); bat.insert(nod,aux,aux); dif:=algebra.difference(nod,vis); outer6:= aggr.count(dif); rhtaux:=bat.new(:oid,:int); rhaux:=bat.new(:oid,:lng); rtaux:=bat.new(:oid,:lng); rhtaux:=algebra.select(b4,3); rtaux:=algebra.semijoin(rt,rhtaux); rhaux:=algebra.semijoin(rh,rhtaux); barrier outer:=true; barrier (mloop18:lng ,h18:lng ,tl18:lng ) := bat.newIterator(dif); # This is the problem, I need to do a lot of selects rhaux2:=algebra.select(rhaux,tl18); rtaux2:=algebra.semijoin(rtaux,rhaux2); barrier (mloop19:lng ,h19:oid,tl19:lng ) := bat.newIterator(rtaux2); bite1:=algebra.exist(nod,tl19); barrier ifpart:= bite1; bat.insert(tr,h19,h19); exit ifpart; barrier elsepart:= calc.!=(bite1,true); bat.insert(ar,h19,h19); bat.insert(nod,tl19,tl19); exit elsepart; redo (mloop19:lng ,h19:oid ,tl19:lng ) := bat.hasMoreElements(rtaux2); exit mloop19:lng ; bat.insert(vis,h18,tl18); redo (mloop18:lng ,h18:lng ,tl18:lng ) := bat.hasMoreElements(dif); exit mloop18:lng ; dif:=algebra.difference(nod,vis); outer6:= aggr.count(dif); outer:=calc.!=(outer6,0); redo outer:=calc.!=(outer6,0); exit outer;

4 4

Re: [MonetDB-users] Core Dump upon COPY on SPARC
by Hering Cheng 23 Apr '10

23 Apr '10

Using Feb2010 release resulted in a different error. This time, mserver5 did not crash: MAPI = chenher@rdcuxsrv220:50000 QUERY = COPY 34274958 RECORDS INTO app.t_compute_all_closeprice FROM '/home/chenher/gaal/tmp/tar/t_compute_all_closeprice.txt' DELIMITERS '|', '\n'; ERROR = !MALException:!ERROR: HEAPextend: failed to extend to 274199664 for 17/1745tail Here is the configuration from log file: 2010-04-21 13:11:51 MSG tar[537]: # MonetDB server v5.18.1, based on kernel v1.36.1 2010-04-21 13:11:51 MSG tar[537]: # Serving database 'tar', using 16 threads 2010-04-21 13:11:51 MSG tar[537]: # Compiled for sparc-sun-solaris2.10/64bit with 64bit OIDs dynamically linked 2010-04-21 13:11:51 MSG tar[537]: # Found 32.000 GiB available main-memory. 2010-04-21 13:11:51 MSG tar[537]: # Copyright (c) 1993-July 2008 CWI. 2010-04-21 13:11:51 MSG tar[537]: # Copyright (c) August 2008-2010 MonetDB B.V., all rights reserved 2010-04-21 13:11:51 MSG tar[537]: # Visit http://monetdb.cwi.nl/ for further information 2010-04-21 13:11:51 MSG tar[537]: # Listening for connection requests on mapi:monetdb://127.0.0.1:50001/ 2010-04-21 13:11:52 MSG tar[537]: # MonetDB/SQL module v2.36.1 loaded Any suggestion? On Wed, Apr 21, 2010 at 1:16 PM, Hering Cheng <hering.cheng(a)computer.org>wrote: > The statement I gave is the only statement that I passed to mclient, using > --statement argument. > > > On Wed, Apr 21, 2010 at 11:32 AM, Niels Nes <Niels.Nes(a)cwi.nl> wrote: > >> On Wed, Apr 21, 2010 at 11:08:46AM -0700, Hering Cheng wrote: >> > Here is the single COPY statement I was using: >> > >> > COPY 34274958 RECORDS INTO app.t_compute_all_closeprice FROM '/home/ >> > chenher/gaal/tmp/tar/t_compute_all_closeprice.txt' DELIMITERS '|', '\ >> > n'; >> >> I was wondering because the crash you have seems to indicate its >> in the commit, ie your load finished but then the commit (auto or not) >> crashes. Probably because of some out of memory problem. >> >> niels >> > >> > On Wed, Apr 21, 2010 at 11:02 AM, Niels Nes <Niels.Nes(a)cwi.nl> wrote: >> > >> > On Wed, Apr 21, 2010 at 10:48:48AM -0700, Hering Cheng wrote: >> > > Hi, >> > > >> > > I am trying to load 77 GB of data (or 34,274,958 records) into >> > MonetDB, >> > > using the Feb2010 SP1 source code compiled on Solaris SPARC with >> > Sun >> > > Studio 12.1 compiler with debugging enabled, and the database >> > crashes 5 >> > > hours into the loading. Here is some information about my >> > > configuration: >> > > >> > > I start up with Merovingian like this: >> > > >> > > $ ( ulimit -d $[32*1024*1024]; export LD_PRELOAD_64=/usr/lib/64/ >> > > libumem.so:${LD_PRELOAD_64}; export LD_PRELOAD=/usr/lib/ >> > libumem.so:$ >> > > {LD_PRELOAD}; export MONETDB5CONF=/GAAL/chenher/ >> > rdcuxsrv220-local-disk/ >> > > chenher/monetdb/feb2010sp1/etc/monetdb5.conf; /GAAL/chenher/share >> > / >> > > monetdb/distro-sparc-feb2010-sp1-64bit/bin/merovingian; ) >> > > >> > > Here are the relevent log sections: >> > > >> > > 2010-04-16 13:25:28 MSG merovingian[4302]: starting database >> > 'tar', up >> > > min/avg/max: 4m/4m/4m, crash average: 0.00 0.00 0.00 (1-1=0) >> > > 2010-04-16 13:25:29 MSG tar[15594]: arguments: /GAAL/chenher/ >> > share/ >> > > monetdb/distro-sparc-feb2010-sp1-64bit/bin/mserver5 --config=/ >> > GAAL/ >> > > chenher/rdcuxsrv220-local-disk/chenher/monetdb/feb2010sp1/etc/ >> > > monetdb5.conf --dbname=tar --dbinit=include sql; --set >> > merovingian_uri= >> > > mapi:monetdb://rdcuxsrv220:50000/tar --set monet_daemon=yes --set >> > > mapi_open=false --set mapi_autosense=true --set mapi_port=50001 >> > --set >> > > monet_vault_key=/GAAL/chenher/rdcuxsrv220-local-disk/chenher/ >> > monetdb/ >> > > feb2010sp1/var/MonetDB5/dbfarm/tar/.vaultkey --set sql_optimizer= >> > > default_pipe >> > > 2010-04-16 13:25:34 MSG tar[15594]: # MonetDB server v5.18.3, >> > based on >> > > kernel v1.36.3 >> > > 2010-04-16 13:25:34 MSG tar[15594]: # Serving database 'tar', >> > using 16 >> > > threads >> > > 2010-04-16 13:25:34 MSG tar[15594]: # Compiled for >> > > sparc-sun-solaris2.10/64bit with 64bit OIDs dynamically linked >> > > 2010-04-16 13:25:34 MSG tar[15594]: # Found 32.000 GiB available >> > > main-memory. >> > > 2010-04-16 13:25:34 MSG tar[15594]: # Copyright (c) 1993-July >> > 2008 CWI. >> > > 2010-04-16 13:25:34 MSG tar[15594]: # Copyright (c) August >> > 2008-2010 >> > > MonetDB B.V., all rights reserved >> > > 2010-04-16 13:25:34 MSG tar[15594]: # Visit http://monetdb.cwi.nl >> > / for >> > > further information >> > > 2010-04-16 13:25:34 MSG tar[15594]: # Listening for connection >> > requests >> > > on mapi:monetdb://127.0.0.1:50001/ >> > > 2010-04-16 13:25:35 MSG tar[15594]: # MonetDB/SQL module v2.36.3 >> > loaded >> > > 2010-04-16 13:25:35 MSG control[4302]: rdcuxsrv220:58146: started >> > > database 'tar' >> > > ... >> > > 2010-04-21 10:23:37 MSG merovingian[4302]: database 'tar' (15594) >> > has >> > > crashed (dumped core) >> > > >> > > Using Sun's DBX tool to examine the crash: >> > > >> > > ... >> > > t@212 (l@212) terminated by signal SEGV (no mapping at the fault >> > > address) >> > > Current function is temp_create >> > > 87 temp_dup(b->batCacheid); >> > > (dbx) where >> > > current thread: t@212 >> > > =>[1] temp_create(b = (nil)), line 87 in "bat_utils.c" >> > > [2] ebat2real(b = 5714, ibase = 0), line 162 in "bat_utils.c" >> > > [3] delta_append_bat(bat = 0x104923c98, i = 0x10143ec98), line >> > 234 in >> > > "bat_storage.c" >> > > [4] append_col(tr = 0x104fe1e98, c = 0x1045c7af8, i = >> > 0x10143ec98, >> > > tpe = 5), line 267 in "bat_storage.c" >> > > [5] mvc_append_wrap(cntxt = 0xffffffff7f352c00, mb = >> > 0x1050d5d98, stk >> > > = 0x101816018, pci = 0x1056a7c18), line 1120 in "sql.c" >> > > [6] runMALsequence(cntxt = 0xffffffff7f352c00, mb = >> > 0x1050d5d98, >> > > startpc = 1, stoppc = 0, stk = 0x101816018, env = (nil), >> > pcicaller = >> > > (nil)), line 2908 in "mal_interpreter.c" >> > > [7] callMAL(cntxt = 0xffffffff7f352c00, mb = 0x1050d5d98, env = >> > > 0xfffffffddabff908, argv = 0xfffffffddabff968, debug = '\0'), >> > line 402 >> > > in "mal_interpreter.c" >> > > [8] SQLexecutePrepared(c = 0xffffffff7f352c00, be = >> > 0x1050f0ec8, q = >> > > 0x104fe1b18), line 1196 in "sql_scenario.c" >> > > [9] SQLengineIntern(c = 0xffffffff7f352c00, be = 0x1050f0ec8), >> > line >> > > 1249 in "sql_scenario.c" >> > > [10] SQLengine(c = 0xffffffff7f352c00), line 1349 in >> > "sql_scenario.c" >> > > [11] runPhase(c = 0xffffffff7f352c00, phase = 4), line 363 in >> > > "mal_scenario.c" >> > > [12] runScenarioBody(c = 0xffffffff7f352c00), line 412 in >> > > "mal_scenario.c" >> > > [13] runScenario(c = 0xffffffff7f352c00), line 438 in >> > > "mal_scenario.c" >> > > [14] MSserveClient(dummy = 0xffffffff7f352c00), line 368 in >> > > "mal_session.c" >> > > (dbx) threads >> > > t@1 a l@1 ?() LWP suspended in __pollsys() >> > > t@2 a l@2 SERVERlistenThread() LWP suspended in >> > __pollsys >> > > () >> > > t@3 a l@3 mvc_logmanager() LWP suspended in >> > __pollsys() >> > > t@4 b l@4 umem_update_thread() sleep on (unknown) in >> > > __lwp_park() >> > > t@13 a l@13 runDFLOWworker() sleep on 0x101ea9a48 in >> > > __lwp_park() >> > > t@14 a l@14 runDFLOWworker() sleep on 0x101ea9a48 in >> > > __lwp_park() >> > > t@15 a l@15 runDFLOWworker() sleep on 0x101ea9a48 in >> > > __lwp_park() >> > > t@16 a l@16 runDFLOWworker() sleep on 0x101ea9a48 in >> > > __lwp_park() >> > > t@17 a l@17 runDFLOWworker() sleep on 0x101ea9a48 in >> > > __lwp_park() >> > > t@18 a l@18 runDFLOWworker() sleep on 0x101ea9a48 in >> > > __lwp_park() >> > > t@19 a l@19 runDFLOWworker() sleep on 0x101ea9a48 in >> > > __lwp_park() >> > > t@20 a l@20 runDFLOWworker() sleep on 0x101ea9a48 in >> > > __lwp_park() >> > > t@21 a l@21 runDFLOWworker() sleep on 0x101ea9a48 in >> > > __lwp_park() >> > > t@22 a l@22 runDFLOWworker() sleep on 0x101ea9a48 in >> > > __lwp_park() >> > > t@23 a l@23 runDFLOWworker() sleep on 0x101ea9a48 in >> > > __lwp_park() >> > > t@24 a l@24 runDFLOWworker() sleep on 0x101ea9a48 in >> > > __lwp_park() >> > > t@25 a l@25 runDFLOWworker() sleep on 0x101ea9a48 in >> > > __lwp_park() >> > > t@26 a l@26 runDFLOWworker() sleep on 0x101ea9a48 in >> > > __lwp_park() >> > > t@27 a l@27 runDFLOWworker() sleep on 0x101ea9a48 in >> > > __lwp_park() >> > > t@28 a l@28 runDFLOWworker() sleep on 0x101ea9a48 in >> > > __lwp_park() >> > > o> t@212 a l@212 ?() signal SIGSEGV in temp_create() >> > > >> > > With Feb2010 release, I managed to load a much larger database >> > without >> > > a crash. I will try loading this smaller data set again with the >> > > Feb2010 release and report what happens. In the meantime, can >> > you give >> > > me some suggestions? >> > > >> > > Thanks. >> > > Hering >> > > >> > >> > Are you loading with a single COPY into or with several insert >> > into? >> > >> > Niels >> > >> > > >> > >> ------------------------------------------------------------------------------ >> > >> > > _______________________________________________ >> > > MonetDB-users mailing list >> > > MonetDB-users(a)lists.sourceforge.net >> > > https://lists.sourceforge.net/lists/listinfo/monetdb-users >> > >> > >> > -- >> > Niels Nes, Centrum Wiskunde & Informatica (CWI) >> > Science Park 123, 1098 XG Amsterdam, The Netherlands >> > room L3.14, phone ++31 20 592-4098 sip:4098@sip.cwi.nl<sip%3A4098(a)sip.cwi.nl> >> > url: http://www.cwi.nl/~niels <http://www.cwi.nl/%7Eniels> >> e-mail: Niels.Nes(a)cwi.nl >> > >> > >> >> -- >> Niels Nes, Centrum Wiskunde & Informatica (CWI) >> Science Park 123, 1098 XG Amsterdam, The Netherlands >> room L3.14, phone ++31 20 592-4098 sip:4098@sip.cwi.nl<sip%3A4098(a)sip.cwi.nl> >> url: http://www.cwi.nl/~niels <http://www.cwi.nl/%7Eniels> e-mail: >> Niels.Nes(a)cwi.nl >> > >

2 3

[MonetDB-users] copy into file
by dariuszs 22 Apr '10

22 Apr '10

Hi, It looks like the same problem exists no matter what the OS (Fedora 12 or Ubuntu 9.10 or Windows) and this worked in Nov2009-SP1 release. When you try: sql>copy select * from table1 limit 1000 into '/tmp/output.txt'; MAPI = monetdb@localhost:50000 ACTION= read_line QUERY = copy select * from table1 limit 1000 into '/tmp/output.txt'; ERROR = !Connection terminated Timer 1105.876 msec You have mail in /var/spool/mail/root [root@monetdb ~]# [root@monetdb /]# mserver5 --version MonetDB server v5.18.3 (64-bit), based on kernel v1.36.3 (64-bit oids) Copyright (c) 1993-July 2008 CWI Copyright (c) August 2008-2010 MonetDB B.V., all rights reserved Visit http://monetdb.cwi.nl/ for further information Found 47.3GiB available memory, 16 available cpu cores Configured for prefix: /usr Libraries: libpcre: 7.8 2008-09-05 (compiled with 7.8) openssl: OpenSSL 1.0.0-beta4 10 Nov 2009 (compiled with OpenSSL 1.0.0-fips-beta4 10 Nov 2009) libxml2: 2.7.6 (compiled with 2.7.6) Compiled by: mockbuild@ (x86_64-redhat-linux-gnu) Compilation: gcc -O2 -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector --param=ssp-buffer-size=4 -m64 -mtune=generic -O6 -fomit-frame-pointer -finline-functions -falign-loops=4 -falign-jumps=4 -falign-functions=4 -fexpensive-optimizations -funroll-loops -frerun-cse-after-loop -frerun-loop-opt -ftree-vectorize Linking : ld -IPA -m elf_x86_64 Please help. Thanks. Dariusz.

4 27

[MonetDB-users] SQL constraints and performance
by Isidor Zeuner 22 Apr '10

22 Apr '10

Dear MonetDB developers, from my own experiences regarding database engine design, I know that SQL constraints can be used to increase performance because the query planner can use them to reason about certain regularities in the data. On the other hand, they come with a cost as the engine is supposed to enforce them. Are there any common rules on how constraints and performance correlate in MonetDB, which could be used to adapt a schema to work especially fast with MonetDB? Best regards, Isidor

1 0

[MonetDB-users] Core Dump upon COPY on SPARC
by Hering Cheng 21 Apr '10

21 Apr '10

Hi, I am trying to load 77 GB of data (or 34,274,958 records) into MonetDB, using the Feb2010 SP1 source code compiled on Solaris SPARC with Sun Studio 12.1 compiler with debugging enabled, and the database crashes 5 hours into the loading. Here is some information about my configuration: I start up with Merovingian like this: $ ( ulimit -d $[32*1024*1024]; export LD_PRELOAD_64=/usr/lib/64/libumem.so:${LD_PRELOAD_64}; export LD_PRELOAD=/usr/lib/libumem.so:${LD_PRELOAD}; export MONETDB5CONF=/GAAL/chenher/rdcuxsrv220-local-disk/chenher/monetdb/feb2010sp1/etc/monetdb5.conf; /GAAL/chenher/share/monetdb/distro-sparc-feb2010-sp1-64bit/bin/merovingian; ) Here are the relevent log sections: 2010-04-16 13:25:28 MSG merovingian[4302]: starting database 'tar', up min/avg/max: 4m/4m/4m, crash average: 0.00 0.00 0.00 (1-1=0) 2010-04-16 13:25:29 MSG tar[15594]: arguments: /GAAL/chenher/share/monetdb/distro-sparc-feb2010-sp1-64bit/bin/mserver5 --config=/GAAL/chenher/rdcuxsrv220-local-disk/chenher/monetdb/feb2010sp1/etc/monetdb5.conf --dbname=tar --dbinit=include sql; --set merovingian_uri=mapi:monetdb://rdcuxsrv220:50000/tar --set monet_daemon=yes --set mapi_open=false --set mapi_autosense=true --set mapi_port=50001 --set monet_vault_key=/GAAL/chenher/rdcuxsrv220-local-disk/chenher/monetdb/feb2010sp1/var/MonetDB5/dbfarm/tar/.vaultkey --set sql_optimizer=default_pipe 2010-04-16 13:25:34 MSG tar[15594]: # MonetDB server v5.18.3, based on kernel v1.36.3 2010-04-16 13:25:34 MSG tar[15594]: # Serving database 'tar', using 16 threads 2010-04-16 13:25:34 MSG tar[15594]: # Compiled for sparc-sun-solaris2.10/64bit with 64bit OIDs dynamically linked 2010-04-16 13:25:34 MSG tar[15594]: # Found 32.000 GiB available main-memory. 2010-04-16 13:25:34 MSG tar[15594]: # Copyright (c) 1993-July 2008 CWI. 2010-04-16 13:25:34 MSG tar[15594]: # Copyright (c) August 2008-2010 MonetDB B.V., all rights reserved 2010-04-16 13:25:34 MSG tar[15594]: # Visit http://monetdb.cwi.nl/ for further information 2010-04-16 13:25:34 MSG tar[15594]: # Listening for connection requests on mapi:monetdb://127.0.0.1:50001/ 2010-04-16 13:25:35 MSG tar[15594]: # MonetDB/SQL module v2.36.3 loaded 2010-04-16 13:25:35 MSG control[4302]: rdcuxsrv220:58146: started database 'tar' ... 2010-04-21 10:23:37 MSG merovingian[4302]: database 'tar' (15594) has crashed (dumped core) Using Sun's DBX tool to examine the crash: ... t@212 (l@212) terminated by signal SEGV (no mapping at the fault address) Current function is temp_create 87 temp_dup(b->batCacheid); (dbx) where current thread: t@212 =>[1] temp_create(b = (nil)), line 87 in "bat_utils.c" [2] ebat2real(b = 5714, ibase = 0), line 162 in "bat_utils.c" [3] delta_append_bat(bat = 0x104923c98, i = 0x10143ec98), line 234 in "bat_storage.c" [4] append_col(tr = 0x104fe1e98, c = 0x1045c7af8, i = 0x10143ec98, tpe = 5), line 267 in "bat_storage.c" [5] mvc_append_wrap(cntxt = 0xffffffff7f352c00, mb = 0x1050d5d98, stk = 0x101816018, pci = 0x1056a7c18), line 1120 in "sql.c" [6] runMALsequence(cntxt = 0xffffffff7f352c00, mb = 0x1050d5d98, startpc = 1, stoppc = 0, stk = 0x101816018, env = (nil), pcicaller = (nil)), line 2908 in "mal_interpreter.c" [7] callMAL(cntxt = 0xffffffff7f352c00, mb = 0x1050d5d98, env = 0xfffffffddabff908, argv = 0xfffffffddabff968, debug = '\0'), line 402 in "mal_interpreter.c" [8] SQLexecutePrepared(c = 0xffffffff7f352c00, be = 0x1050f0ec8, q = 0x104fe1b18), line 1196 in "sql_scenario.c" [9] SQLengineIntern(c = 0xffffffff7f352c00, be = 0x1050f0ec8), line 1249 in "sql_scenario.c" [10] SQLengine(c = 0xffffffff7f352c00), line 1349 in "sql_scenario.c" [11] runPhase(c = 0xffffffff7f352c00, phase = 4), line 363 in "mal_scenario.c" [12] runScenarioBody(c = 0xffffffff7f352c00), line 412 in "mal_scenario.c" [13] runScenario(c = 0xffffffff7f352c00), line 438 in "mal_scenario.c" [14] MSserveClient(dummy = 0xffffffff7f352c00), line 368 in "mal_session.c" (dbx) threads t@1 a l@1 ?() LWP suspended in __pollsys() t@2 a l@2 SERVERlistenThread() LWP suspended in __pollsys() t@3 a l@3 mvc_logmanager() LWP suspended in __pollsys() t@4 b l@4 umem_update_thread() sleep on (unknown) in __lwp_park() t@13 a l@13 runDFLOWworker() sleep on 0x101ea9a48 in __lwp_park() t@14 a l@14 runDFLOWworker() sleep on 0x101ea9a48 in __lwp_park() t@15 a l@15 runDFLOWworker() sleep on 0x101ea9a48 in __lwp_park() t@16 a l@16 runDFLOWworker() sleep on 0x101ea9a48 in __lwp_park() t@17 a l@17 runDFLOWworker() sleep on 0x101ea9a48 in __lwp_park() t@18 a l@18 runDFLOWworker() sleep on 0x101ea9a48 in __lwp_park() t@19 a l@19 runDFLOWworker() sleep on 0x101ea9a48 in __lwp_park() t@20 a l@20 runDFLOWworker() sleep on 0x101ea9a48 in __lwp_park() t@21 a l@21 runDFLOWworker() sleep on 0x101ea9a48 in __lwp_park() t@22 a l@22 runDFLOWworker() sleep on 0x101ea9a48 in __lwp_park() t@23 a l@23 runDFLOWworker() sleep on 0x101ea9a48 in __lwp_park() t@24 a l@24 runDFLOWworker() sleep on 0x101ea9a48 in __lwp_park() t@25 a l@25 runDFLOWworker() sleep on 0x101ea9a48 in __lwp_park() t@26 a l@26 runDFLOWworker() sleep on 0x101ea9a48 in __lwp_park() t@27 a l@27 runDFLOWworker() sleep on 0x101ea9a48 in __lwp_park() t@28 a l@28 runDFLOWworker() sleep on 0x101ea9a48 in __lwp_park() o> t@212 a l@212 ?() signal SIGSEGV in temp_create() With Feb2010 release, I managed to load a much larger database without a crash. I will try loading this smaller data set again with the Feb2010 release and report what happens. In the meantime, can you give me some suggestions? Thanks. Hering

2 1

[MonetDB-users] Feb2010-SP1/Fedora 12/Ubuntu 9.10/Windows - creating output problem
by dariuszs 21 Apr '10

21 Apr '10

Hi, It looks like the same problem exists no matter what the OS (Fedora 12 or Ubuntu 9.10 or Windows) and this worked in Nov2009-SP1 release. When you try: 'copy select * from table1 limit 1000 into '/tmp/output.txt' You get: sql>copy select * from plk100 limit 1000 into '/tmp/output.txt'; MAPI = monetdb@localhost:50000 ACTION= read_line QUERY = copy select * from plk100 limit 1000 into '/tmp/output.txt'; ERROR = !Connection terminated Timer 1105.876 msec You have mail in /var/spool/mail/root [root@monetdb ~]# [root@monetdb /]# mserver5 --version MonetDB server v5.18.3 (64-bit), based on kernel v1.36.3 (64-bit oids) Copyright (c) 1993-July 2008 CWI Copyright (c) August 2008-2010 MonetDB B.V., all rights reserved Visit http://monetdb.cwi.nl/ for further information Found 47.3GiB available memory, 16 available cpu cores Configured for prefix: /usr Libraries: libpcre: 7.8 2008-09-05 (compiled with 7.8) openssl: OpenSSL 1.0.0-beta4 10 Nov 2009 (compiled with OpenSSL 1.0.0-fips-beta4 10 Nov 2009) libxml2: 2.7.6 (compiled with 2.7.6) Compiled by: mockbuild@ (x86_64-redhat-linux-gnu) Compilation: gcc -O2 -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector --param=ssp-buffer-size=4 -m64 -mtune=generic -O6 -fomit-frame-pointer -finline-functions -falign-loops=4 -falign-jumps=4 -falign-functions=4 -fexpensive-optimizations -funroll-loops -frerun-cse-after-loop -frerun-loop-opt -ftree-vectorize Linking : ld -IPA -m elf_x86_64 Please help. Thanks. Dariusz.

1 0

[MonetDB-users] monetdb/merovingian
by dariuszs 20 Apr '10

20 Apr '10

Hi, I've got this messages after rebooting the server and trying to start merovingian again: unable to open '/var/run/MonetDB/merovingian.pid' for writing: No such file or directory Software: Ubuntu 9.10 MonetDB server v5.18.3 (64-bit), based on kernel v1.36.3 (64-bit oids) Please help. Dariusz.

5 12

[MonetDB-users] Python Error
by Matthew Jones 20 Apr '10

20 Apr '10

Hey folks, I'm seeing an error in the Python API. When connecting to a remote Monet instance with the python client and setting the cursor's arraysize to something significant (100, as an example) then I get the following error: Traceback (most recent call last): File "afile.py", line 9, in <module> cur.execute("SELECT max(val), otherval" File "build/bdist.linux-x86_64/egg/monetdb/sql/cursors.py", line 203, in execute File "build/bdist.linux-x86_64/egg/monetdb/sql/cursors.py", line 445, in __store_result File "build/bdist.linux-x86_64/egg/monetdb/sql/cursors.py", line 529, in __parse_tuple File "build/bdist.linux-x86_64/egg/monetdb/sql/cursors.py", line 569, in __exception_handler monetdb.monetdb_exceptions.InterfaceError: length of row doesn't match header This doesn't happen when the client is used locally on the machine running the database. I will open a bug about this, also. It prevents the .arraysize cursor property from being used remotely. It's default value is 1, which causes the server to take a ridiculously long time to send back large data sets. 73, Matthew W. Jones (KI4ZIB) http://matburt.net

2 1

[MonetDB-users] Foreign key validation issue with "COPY INTO..." ?
by Matthieu Guamis 19 Apr '10

19 Apr '10

Hello, "COPY INTO" command fails with "FOREIGN KEY constraint 'xxx' violated" whereas "INSERT INTO" succeeded. Simple 3 tables example (it works with 2 tables, one referencing the other): CREATE TABLE a ( a1 VARCHAR(3) NOT NULL, CONSTRAINT pka PRIMARY KEY (a1) ); CREATE TABLE b ( b1 VARCHAR(3) NOT NULL, b2 VARCHAR(3) NOT NULL, CONSTRAINT pkb PRIMARY KEY (b1,b2), CONSTRAINT fk1 FOREIGN KEY (b2) REFERENCES a (a1) ); CREATE TABLE c ( c1 varchar(3) NOT NULL, c2 varchar(3) NOT NULL, c3 varchar(3), CONSTRAINT pkc PRIMARY KEY (c1,c2), CONSTRAINT fk2 FOREIGN KEY (c2) REFERENCES a (a1), CONSTRAINT fk3 FOREIGN KEY (c2,c3) REFERENCES b (b1,b2) ); Using INSERT INTO: sql>insert into a values ('aaa'); 1 affected row sql>insert into c values ('cc1','aaa',null); 1 affected row Using COPY INTO with one line input file "test.txt" containing : cc2|aaa| sql>COPY INTO c FROM '/home/mat/test.txt' USING DELIMITERS '|',' more>' NULL as ''; SQLException:assert:INSERT INTO: FOREIGN KEY constraint 'c.fk3' violated Bug or misunderstanding? Thanks a lot, Matthieu PS: mserver5 --version MonetDB server v5.18.3 (64-bit), based on kernel v1.36.3 (64-bit oids) Copyright (c) 1993-July 2008 CWI Copyright (c) August 2008-2010 MonetDB B.V., all rights reserved Visit http://monetdb.cwi.nl/ for further information Found 2.0GiB available memory, 2 available cpu cores Configured for prefix: /usr Libraries: libpcre: 7.8 2008-09-05 (compiled with 7.8) openssl: OpenSSL 0.9.8g 19 Oct 2007 (compiled with OpenSSL 0.9.8g 19 Oct 2007) libxml2: 2.7.5 (compiled with 2.7.5) Compiled by: root@ubuntu-karmic-64 (x86_64-pc-linux-gnu) Compilation: gcc -O2 -Wall -O2 -O6 -fomit-frame-pointer -finline-functions -falign-loops=4 -falign-jumps=4 -falign-functions=4 -fexpensive-optimizations -funroll-loops -frerun-cse-after-loop -frerun-loop-opt -ftree-vectorize Linking : ld -IPA -m elf_x86_64 -Wl,-Bsymbolic-functions

4 4

[MonetDB-users] XRPC queries do not behave like Mclient queries
by Ryad Ben-El-Kezadri 16 Apr '10

16 Apr '10

Hello, 1°)I am querying Monetdb through the XRPC java API. I have implemented a xquery in a function in a XRPC module and I call it from java. I noticed that my XRPC module is not loaded when the xquery compares an attribute to an integer value (for instance $n[@attr=1]) In this case, I get the following error message embedded in the XRPC reply: Error occurred during execution fatal error: function #pf:number is not supported. xquery_module_load: could not load module. Comparing an attribute to a string works fine however (ex $n[@attr="1"]) 2°)What is strange is that the same function (fct) works perfect when I call it from the Mclient (instead of java). Example import module namespace f="xrpc-extract-function" at " http://127.0.0.1:50001/export/extract.xq"; f:fct("file.xml", 0, 1000000) 3°)I don't know if this problem is general or specifically related to my configuration. Indeed I had to load the server modules myself (else the server crashes) with the following command in the bat server script: "%MONETDB%\bin\Mserver.exe" --dbinit="module(pf_support);module(logger);module(xrpc_client);module(xrpc_server);module(pf_standoff);module(mkey);module(pathfinder);module(mapi);" --set "prefix=%MONETDB%" --set "exec_prefix=%MONETDB%" %MONETDBFARM% %* Your help will be really helpful, because right now I can only do string comparisons on attributes (which slows the program a lot,....). My guess is that, when invoked from java, Monetdb looks (at load time) for pf:number instead of fn:number to convert the string value of the attribute, but that this function does not exist Warm regards Ryad ////////////////////////////////////////// Here is my java call, I guess it is exaclty the same signature that with the Mclient (so there is no problem with it) String call = XRPCMessageMine.XRPC_SEQ(XRPCMessageMine.XRPC_ATOM("string", file)) + XRPCMessageMine.XRPC_SEQ(XRPCMessageMine.XRPC_ATOM("double", Double.toString(timeInterval[0]))) + XRPCMessageMine.XRPC_SEQ(XRPCMessageMine.XRPC_ATOM("double", Double.toString(timeInterval[1])));

1 0

[MonetDB-users] monetdb sql - same query different results
by dariuszs 16 Apr '10

16 Apr '10

Hi, Same query produces different results: sql>select count(*) from plk100 WHERE 0=0 and c15 in (select zip from zip_list) and ( c102 in ('1','0')) and ( c28 in ('1','0')) and ( c66 in ('01')); +-------+ | L73 | +=======+ | 14945 | +-------+ 1 tuple Timer 69.446 msec 1 rows sql>select count(*) from plk100 WHERE 0=0 and c15 in (select zip from zip_list) and ( c102 in ('1','0')) and ( c28 in ('1','0')) and ( c66 in ('01')); +-------+ | L73 | +=======+ | 57257 | +-------+ 1 tuple Timer 146.666 msec 1 rows sql> Please help.

2 8

[MonetDB-users] monetdb python API, howto install
by Martin Körner 16 Apr '10

16 Apr '10

Hello Guys, i have installed the MonetDB 5 SQL client-server package from http://monetdb.cwi.nl/downloads/Windows/Feb2010-SP1/ The downloaded file was MonetDB5-SQL-Installer-i386-20100322.msi. So i use a Windows XP 32bit system. My installed Python version is now 2.6. Now i want to access MonetDB with Python. But i found no way to install the MonetDB Python API. I tried it like it is shown on http://monetdb.cwi.nl/SQL/Documentation/The-Python-Library.html But there is no file 'setup.py' to install. Is there any other way to install the API? Where i can download an install package for the API? With best greetings from Germany, Martin -- heidemann added insight GmbH In der Weidbach 2 63477 Maintal Telefon: +49 6181 9080739 Internet: www.added-insight.com USt-Id DE 2130 86 540 Registernummer: HRB 91533 (Amtsgericht Hanau) Geschäftsführer: Dr. Jeffrey Heidemann ********************************************************************* Diese E-Mail kann vertrauliche und/oder rechtlich geschuetzte Informationen enthalten. Wenn Sie nicht der richtige Adressat sind oder diese E-Mail irrtuemlich erhalten haben, informieren Sie bitte sofort den Absender und vernichten Sie diese Mail. Das unerlaubte Kopieren sowie die unbefugte Weitergabe dieser Mail ist nicht gestattet. This e-mail may contain confidential and/or privileged information. If you are not the intended recipient (or have received this e-mail in error) please notify the sender immediately and destroy this e-mail. Any unauthorized copying, disclosure or distribution of the material in this e-mail is strictly forbidden. **********************************************************************

1 1

[MonetDB-users] MonetDB/XQuey - how to ignore DTDs?
by Roberto Cornacchia 15 Apr '10

15 Apr '10

Hi all, I wonder is there is an option to make MonetDB/XQuery shredder ignore DTD files. What triggered this question is the need to shred about 20M files, each of them including *the same* online DTD. What happens is that, for each xml file, the DTD is downloaded and shredded/used(?). It is not even cached (I tried using xquery_cacherules, but apparently it has no effect on DTDs). This makes shredding speed drop down to more than 3 seconds per document, which is not an option of course, as it would take me roughly 700 days to shred the entire collection :) Removing DTD link from the original xml files is not an option either, the collection is simply too big to be replicated. How can the DTD be ignored? Roberto -- | M.Sc. Roberto Cornacchia | CWI (Centrum voor Wiskunde en Informatica) | Science Park 123, 1098XG Amsterdam, The Netherlands | tel: +31 20 592 4322 , http://www.cwi.nl/~roberto

4 4

[MonetDB-users] grouping sets
by Nafees Ur Rehman 15 Apr '10

15 Apr '10

Hi, Whats is the syntax of grouping sets in monetdb? I am trying Select has_nurs,form,health,class,count(*) from nursery group by GROUPING SETS ((has_nurs,class),(form,class),(health,class)) but it does not work Regards Nafees Ur Rehman

3 3

Re: [MonetDB-users] [Monetdb-developers] MonetDB memory management
by Stefan de Konink 15 Apr '10

15 Apr '10

-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA512 Op 15-04-10 11:00, Xiao Lei schreef: > Then how can i release the memory? By not posting to me directly but reply to the mailinglist ;) It will not directly solve your problem, but keep other people in the loop. Stefan -----BEGIN PGP SIGNATURE----- Version: GnuPG v2.0.14 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEAREKAAYFAkvG238ACgkQYH1+F2Rqwn1xdwCeOxnqWgALhsk4ue2vEv/D/ZsM IC0AnRyRAUabgy7Z2u2UZHGOXl4EJLYS =fsie -----END PGP SIGNATURE-----

2 1