- users-list - monetdb.org

Re: [MonetDB-users] JDBC driver, where? (sorry)
by Martin Koch 07 Dec '07

07 Dec '07

Thanks all of you for helping me along with my MonetDB testing environment. My today's build seems to contain more of the parts mentioned in the documentation. I could not test sooner due to some typical Dutch festivity involving a crazy horseman in red riding on my roof. Kind regards, Martin

2 1

[MonetDB-users] XQuery Update Last Call
by Florian Wendland 07 Dec '07

07 Dec '07

Hi, a question concerning XQuery Update Facility. The Last Call is not supported by MonetDB at this time. Are there plans to implement it in the near future or will this be skipped, until the final recommendation is published? Thx mfw -- Marc-Florian Wendland Dipl.-Inform. (FH) http://www.florianwendland.de

2 1

[MonetDB-users] Changing default monetdb password
by Venks 06 Dec '07

06 Dec '07

How can I change the default MonetDB password? ALTER USER on "monetdb" gives me the following error: !InvalidCredentialsException:setPassword:The administrator cannot set its own password, use changePassword instead Thanks, -Venks

3 17

[MonetDB-users] Profiling queries
by m h 06 Dec '07

06 Dec '07

Hello all- First of all thanks to you guys for your great help these past few weeks. I really appreciate it. I've learned a bit, and hopefully your product is improving from my bug reports. I've managed to pull a bit of my data from my proprietary db into monetdb. (Have some 60 million rows in the fact table, pulled in 5 million)! I even ran a query against it and got the same numbers (though in a different order)! So I did a very basic poor mans benchmark. My python code can hit the old db or monetdb by changing one line. So I took timestamps around the query execution portion. The old db takes 0.21 seconds when cold. Subsequent queries take 0.027-0.072 seconds Monetdb takes 0.67 seconds when cold and 0.12-0.23 otherwise. It appears that the old db is faster when cold than monetdb is after being primed. A few more caveats. (Yes, this is a poor benchmark but after reading pdfs saying that monetdb was 7 times faster than a 64 way oracle box, I thought I'd get quite a bit of speedup.) The old db is running under vmware on a Core2 1.86Ghz box. Monetdb is running on my laptop, a Pentium M 1.86 Ghz machine. Since the query is confidential I can't really put it here, but I can describe it. I'm pulling stuff out of my fact table and two other dimension tables. The select clause sums a few columns, and on one sums the product of one column and the sum of a few others. The coalesce function is used to convert NULLs to 0. The where statement joins the tables using the = operator and then has two "IN" clauses. Then there is a group by (since I'm aggregating in the select) and an order by. Any hints on how to increase performance? Do I need to tune my query or tweak other things? (Yes I know the laptop machine is not apples to apples to the vmware machine) Am open to suggestions. thanks much, -matt

3 17

[MonetDB-users] JDBC driver, where? (sorry)
by Martin Koch 06 Dec '07

06 Dec '07

Dear Reader, I compiled a Mars-Superball-SR4 and a SR5. Both apperently succesfull, as I have sql functionality. But in neiter I found a /share/MonetDB/ lib/ directory containing the promised JDBC driver. What am I doing wrong? I use a standard Ubuntu Gutsy 32 bit, with sun JRE 5.0 (but not the JDK) to run SQuirreL SQL Thanks for your attention and for your software, Kind regards, Martin

4 4

[MonetDB-users] update query performance
by Lange Marcus 05 Dec '07

05 Dec '07

Hello, I have a question. I have a table with the columns ( id, name (string), num(int) ). In this table I have lets say 100 rows of data. In a Java app. I am updating the different rows individually with the sql command: "UPDATE "tableName" SET "num" = ('99') WHERE "id" = (5);" The time to run this single query is between 15 to 32 milliseconds, except every once in a while when it can be as much as 110 milliseconds. Since I want to do this several times each second for all rows this is way to long time. Am I doing something wrong, or am I using Monet in a way that it is not meant to be used or what ? Is there another way to solve this (Updating individual cells/variables in individual rows FAST) ? Thanks for any input! Regards, Marcus

1 0

[MonetDB-users] inserting into default schema?
by m h 05 Dec '07

05 Dec '07

If I create a table, by default it goes into the "tmp" schema. But when I try to create a "users" table, it complains, since there is one in the "sys" schema. Why doesn't it create one in the "tmp" schema? ie: CREATE TABLE users (fcol FLOAT(25)); !CREATE TABLE: name 'users' already in use Just wondering about the resolution order. thanks -matt

3 6

[MonetDB-users] Optimal hardware/software configuration?
by m h 04 Dec '07

04 Dec '07

Say one wanted to do some benchmarks for Monetdb. Here would be a few questions: * Does Raid 0+1 help more than RAM? * What about multi cores? Are they currently useful? * Are big processor caches useful? * What about 64 bit linux? Does that help or hurt? Or here's another way to put it. If you had between $2-$5K to spend what hardware would you buy? A bunch of PS3's ;) Any input would be great? thanks matt

3 2

[MonetDB-users] converting strings to dates?
by m h 04 Dec '07

04 Dec '07

In Oracle I can do the following: select * from table where timestampcol BETWEEN to_date('01/01/07', 'MM/DD/RR') AND to_date('12/31'/07'), 'MM/DD/RR') It's not clear that this is possible in monetdb. There's the EXTRACT function which pulls a numeric value (month, day or year) from a string representing a timestamp, but I can't see how to get a timestamp/date object out? -matt

3 2

[MonetDB-users] Portuguese Characters
by Paulo Jorge Dias 04 Dec '07

04 Dec '07

Hi, I am trying to insert data which has Portuguese Characters and I get some errors. Using the copy command: sql>copy into d_concelho from '/monetdb5/bo/dados/d_concelho.txt' delimiters '|'; !SQLException:sql:value ALTER DO CH├O|12|20071202 from line 23 not inserted !SQLException:importTable:failed to import table Using a java program with jdbc: D:\MonetDB5>java Concelho java.sql.SQLException: unexpected end of input at nl.cwi.monetdb.jdbc.MonetConnection$ResponseList.executeQuery(MonetConnection.java:2048) at nl.cwi.monetdb.jdbc.MonetConnection$ResponseList.processQuery(MonetConnection.java:1798) at nl.cwi.monetdb.jdbc.MonetStatement.internalExecute(MonetStatement.java:371) at nl.cwi.monetdb.jdbc.MonetStatement.execute(MonetStatement.java:340) at nl.cwi.monetdb.jdbc.MonetPreparedStatement.execute(MonetPreparedStatement.java:209) at Concelho.main(Concelho.java:25) Is there any way to get this running? Thanks very much, Paulo Jorge Dias -- View this message in context: http://www.nabble.com/Portuguese-Characters-tf4938671.html#a14136589 Sent from the monetdb-users mailing list archive at Nabble.com.

2 3

[MonetDB-users] Question about used Encoding
by Markus Gritsch 04 Dec '07

04 Dec '07

Hi, I would like to know how encoding related issues are handles in MonetDB. For example, when I run the following Python script # -*- coding: utf-8 -*- import MonetSQLdb x = MonetSQLdb.connect(host = 'localhost', user = 'voc', password = 'voc', lang = 'sql') c = x.cursor() c.execute('DROP TABLE test') c.execute('CREATE TABLE test(id int, name varchar(20))') c.execute('INSERT INTO test (id, name) VALUES (%s, %s)', (0, 'ä ö ü')) c.close() x.close() and then look at the inserted data using DbVisualizer I get Ã¤ Ã¶ Ã¼ in the 'name' column. When I try to edit the value in the DbVisualizer, I get an error message "unexpected end of input". (The SQL statements reads update voc.test set name = 'ö' where id = 0). Is this a JDBC driver problem? When I connect to an MySQL database with the MySQL JDBC driver included in DbVisualizer I can insert arbitrary characters, and they get properly displayed in the GUI. Kind regards, Markus

3 29

[MonetDB-users] primary key retrieval
by Lange Marcus 04 Dec '07

04 Dec '07

Hello, How do I retrieve the primary key id value after inserting something new into the database ? I am writing a simple Java app. (using JDBC) and would like to get this info back into my program. Thanks for any help! Regards Marcus

2 2

[MonetDB-users] windows 127.0.0.1
by Miki Muzsi 04 Dec '07

04 Dec '07

Hi there, Anybody knows if the installation of Monet can be changed (on windows) to use localhost instead of 127.0.0.1. It seems that because of that I can not connect to MondetDB from a different machine; I get the error message: Could not open connection to the host, on port 50000: Connect failed. I've lokked into monetdb5.conf, registriey, docs ... nothing helpt. BTW I am using version v5.2.2 on windows. Cheers, Miki Chordiant Software International Ltd. Registered office: Chiswick Park Building 10, 566 Chiswick High Road, London W4 5XS Registered in UK no. 02155722 and VAT no. GB 479 9023 02 The information transmitted herewith is sensitive information of Chordiant Software or its customers and is intended only for use to the individual or entity to which it is addressed. If the reader of this message is not the intended recipient, you are hereby notified that any review, retransmission, dissemination, distribution, copying or other use of, or taking of any action in reliance upon, this information is strictly prohibited. If you have received this communication in error, please contact the sender and delete the material from your computer.

2 1

[MonetDB-users] offset support?
by m h 04 Dec '07

04 Dec '07

Is there support for OFFSET in queries? The syntax doesn't throw an error, but the expected behavior isn't correct... sql>select * from query_users; +---------+----------+ |user_id |user_name | +=========+==========+ | 1 |john | | 2 |jack | | 3 |ed | | 4 |wendy | | 5 |laura | | 6 |ralph | | 7 |fido | +---------+----------+ Timer 1.820 msec 7 rows sql> Timer 0.294 msec 0 rows sql>SELECT query_users.user_id, query_users.user_name more>FROM query_users ORDER BY query_users.user_id more> LIMIT 3 OFFSET 2; +---------+----------+ |user_id |user_name | +=========+==========+ | 1 |john | | 2 |jack | | 3 |ed | +---------+----------+ -matt

3 10

[MonetDB-users] Slow Data Load
by Paulo Jorge Dias 03 Dec '07

03 Dec '07

Hi, I am working on a Windows XP SP2 32bit with MonetDB v5.2.2 kernel v1.20.0 I have built a java program to upload data from an Oracle Database Table with 6milion rows. After about 4milion of rows loaded the performance decreases dramatically. I have tried to use other way, which is generating all insert statements to a file and then using mclient to execute all the insert statements. Slower that the java option because the autocommit=on and parsing of each statement, but the result is the same. After about 4milion of rows loaded the performance decreases dramatically. Can someone give an explanation for this behavior? Thanks, Paulo Jorge Dias -- View this message in context: http://www.nabble.com/Slow-Data-Load-tf4929292.html#a14108678 Sent from the monetdb-users mailing list archive at Nabble.com.

2 2

[MonetDB-users] Server in a weird state....
by m h 01 Dec '07

01 Dec '07

I tried running the SQLAlchemy ORM testsuite. The first time it ran for a while and then mserver segfaulted. (It also grew to using 700+Megs of memory. Am assumming this is a monetdb issue, since after each test the tables are usually dropped). The server now has issues. It appears to start fine: # MonetDB server v5.2.3, based on kernel v1.20.1 # Serving database 'demo' # Compiled for i686-pc-linux-gnu/32bit with 32bit OIDs dynamically linked # Copyright (c) 1993-2007 CWI, all rights reserved # Visit http://monetdb.cwi.nl/ for further information #warning: please don't forget to set your vault key! #(see /root/MonetDB/etc/monetdb5.conf) # Listening for connection requests on mapi:monetdb://127.0.0.1:50000/ # MonetDB/SQL module v2.20.3 loaded When I try to connect with the command line client it complains repeatedly: !ERROR: BUNdelete: access denied to tmp_537, aborting. !ERROR: BUNdelete: access denied to tmp_537, aborting. ....

3 21

[MonetDB-users] Preferred bulk load method?
by m h 01 Dec '07

01 Dec '07

Good afternoon- Just wondering if there is a preferred method to load large datasets into monetdb? Piping SQL through mclient? thanks much -matt

2 14

[MonetDB-users] Difficulty with External C Function
by Thomas J. Schulte 30 Nov '07

30 Nov '07

I'm having difficulty linking a custom external C function into MonetDB without crashing the database connection. If anyone knows of a documented end-to-end example of adding external functions compiled with GCC, please let me know. I've documented a minimal example below that crashes the connection for me each time. Software: Red Hat Linux knock-off CentOS, i386. Most recent MonetDB download from Sourceforge (server 5.2.2, client 1.20.2, common 1.20.0). GCC 4.1.1 1. Create a simple C file named te.c. int TEtest( ) { return 4; } 2. Compile file, move it to the proper directory, and create symlinks. $ gcc -c -fPIC te.c -o te.o $ gcc -shared -Wl,-soname,lib_te.so.0 -o lib_te.so.0.0.0 te.o (also tried with soname = libte.so.0, leaving output as lib_te.so.0.0.0) $ cp lib_te.so.0.0.0 $mhome/lib/MonetDB5/lib $ cd $mhome/lib/MonetDB5/lib $ ln -s lib_te.so.0.0.0 lib_te.so.0 $ ln -s lib_te.so.0.0.0 lib_te.so 3. Create a MAL file, saved as $mhome/lib/MonetDB5/te.mal module te; command test( ):int address TEtest(); command doesnotexist( ):int address TEdoesnotexist; 4. Update mal_init.mal by adding the following 2 lines: library te; include te; 5. Start a random DB and try to invoke the test function. Merovingian log show database dino killed by signal 11 afterwards. $ cd $mhome/bin $ ./merovingian & [1] xxxx $ ./mclient -lmal --database=dino -h COS1 mal> te.test( ); MAPI = monetdb@COS1:50001 ACTION= read_line QUERY = te.test(); ERROR = Connection terminated If I instead invoke te.doesnotexist(), I get a different error (below.) This leads me to think that MonetDB is findng and loading my compiled .so file, but it wasn't compiled correctly or otherwise isn't providing what MonetDB expects. mal> te.doesnotexist(); MAPI = monetdb@COS1:50001 QUERY = te.doesnotexist(); ERROR = !TypeException:user.main[1]:'te.doesnotexist' undefined in: _1:int := te.doesnotexist() Any pointers are appreciated. Thomas Schulte

3 2

[MonetDB-users] Howto query with non ascii in python?
by m h 30 Nov '07

30 Nov '07

How do I run non-ascii queries in python? Or from the command line for that matter? Naive tests on my part fail.... python: RuntimeError: !syntax error, unexpected IDENT, expecting INTERSECT or EXCEPT or UNION in: "select name from tables where system=false and name=unitéble2" commandline: sql>select name from tables where system=false and name=unitéble2; !syntax error, unexpected IDENT, expecting INTERSECT or EXCEPT or UNION in: "select name from tables where system=false and name=unitéble2" Thanks -matt

3 2

[MonetDB-users] incorrect handling of NULL values in "IN" statements?
by m h 29 Nov '07

29 Nov '07

My understanding that is that NULL is never equal to anything including itself. Thus to test for NULL values, one must use the IS operator. I'm wondering if the following is a bug. The NOT IN will return the null row, instead of the empty set. Are NULL values outside that query? CREATE TABLE query_users ( user_id INT NOT NULL AUTO_INCREMENT, user_name VARCHAR(20), PRIMARY KEY (user_id) ); INSERT INTO query_users (user_id, user_name) VALUES (1, 'matt'); INSERT INTO query_users (user_id, user_name) VALUES (2, 'fred'); INSERT INTO query_users (user_id, user_name) VALUES (3, null); SELECT user_name FROM query_users WHERE user_name IN ('matt', 'fred'); -- should return empty set, returns null SELECT user_name FROM query_users WHERE user_name NOT IN ('matt', 'fred');

2 3

[MonetDB-users] use column names as column alias?
by m h 29 Nov '07

29 Nov '07

Is it possible to use the column names as the alias? The select query below fails. CREATE TABLE query_users ( user_id INTEGER NOT NULL, user_name VARCHAR(20), PRIMARY KEY (user_id) ); INSERT INTO query_users (user_id, user_name) VALUES (1,'matt'); select query_users.user_id AS "query_users.user_id", query_users.user_name AS "query_users.user_name" from query_users; Here's the error: !Invalid identifier 'query_users.user_id' -matt

2 1

[MonetDB-users] SQL exception during query resolving. Slow join? How to submit external variables?
by Huib Verweij 28 Nov '07

28 Nov '07

Hi everyone, I'm new to MonetDB XQuery and have written a crude Cocoon generator for MonetDB using the example code for the JDBC client from the website. I am using the latest Bug Fix Release of MonetDB as announced by Sjoerd Mullender on 24-10-2007. When I execute the following query I get an error: --------------------------------------------------------- let $zz := doc('vogels.xml')//Vogels[data(Dwaalgast) = 'zeer zeldzaam'] return <vogeltjes>{$zz}</vogeltjes> --------------------------------------------------------- throws: java.sql.SQLException: ERROR: ERROR: XML Generation: NULL BAT has a 4415 head, but tail is NULL. ERROR: ERROR: xquery_print_result_loop: operation failed. However, when I change the XQuery to: --------------------------------------------------------- let $zz := doc('vogels.xml')//Vogels[data(Dwaalgast) = 'zeer zeldzaam'] return <vogeltjes>{$zz}azaz</vogeltjes> --------------------------------------------------------- everything's OK and I get the proper result. (The previous release threw the same error btw.) Also, when I join the results from the above query with another table to resolve some codes to code-descriptions, the query takes from 8.5 up to 11 seconds to execute, vs. <1 second for a query without the join. The result is a little over 1Mb in size. The query I use is this: --------------------------------------------------------- let $zz := doc('vogels.xml')//Vogels[Ecotoop_x0020_nest] return <vogeltjes> {for $vogel in doc('vogels.xml')//Vogels[Ecotoop_x0020_nest], $vc in doc('vogels-code.xml')//Vogels-code[data(Kolomnaam) = 'Ecotoop nest'][data(Code) = $vogel/Ecotoop_x0020_nest]/Codebeschrijving return <vogel> {$vogel/*} {$vc} </vogel> } </vogeltjes> --------------------------------------------------------- The latest preview release of eXist handles the join in 3.1 seconds, any ideas on why this join takes almost three times as long? I have a final question: how do I specify external variables using the JDBC client? Regards, Huib Verweij.

2 2

[MonetDB-users] Why does MonetDB take indexes not into account?
by Markus Gritsch 28 Nov '07

28 Nov '07

Hi, I basically have this schema: CREATE TABLE "act" ( "id" int NOT NULL auto_increment, "title" text NOT NULL, "is_closed" bool NOT NULL, PRIMARY KEY ("id") ); CREATE INDEX act_isClosedIndex ON act (is_closed); CREATE TABLE "entry" ( "id" int NOT NULL auto_increment, "note" text NOT NULL, "act_id" int NOT NULL, PRIMARY KEY ("id"), CONSTRAINT "entry_act_id_exists" FOREIGN KEY ("act_id") REFERENCES "act" ("id") ); CREATE INDEX entry_actIndex ON entry (act_id); Each "act" has several "entries" and each "entry" belongs to one "act". *) full-text search in the "note" column over all entries: SELECT * FROM entry WHERE entry.note LIKE ('%hui%') MySQL: 4.5 Seconds MonetDB: 0.8 Seconds So this is very good and this motivates me to switch to MonetDB. However, the majority of the acts have the "is_closed" field set. The usual case in my application is to take into account only the entries of open acts: *) full-text search in the "note" column over all entries of the open acts: SELECT * FROM entry, act WHERE entry.act_id = act.id AND act.is_closed = 0 AND entry.note LIKE ('%hui%') MySQL: 0.26 Seconds MonetDB: 0.8 Seconds Here, MySQL can take advantage of the index on the is_closed column, whereas MonetDB does not. Is this intended? Databases without indexes seem strange to me. Markus

4 16

[MonetDB-users] python cursor missing lastrowid attribute?
by m h 27 Nov '07

27 Nov '07

In MonetSQLdb BaseCursor.insert_id, the docstring says to use the lastrowid attribute. When I try to access that lastrowid on the cursor instance I get an AttributeError. Should I file a bug for this? matt

5 21

[MonetDB-users] Another query that works in MySQL and SQLite but not in MonetDB
by Markus Gritsch 26 Nov '07

26 Nov '07

Hi, I have another question with an SQL query that works in SQLite and MySQL but does not in MonetDB. SELECT * FROM test WHERE ((1) AND (id=100)) syntax error, unexpected AND in: "prepare select * from test where ((1) and" The query is forged by an ORM. I programmatically put together the WHERE clause. All the contitions are put together using AND, and in the case of no conditions, the WHERE clause simply reads (1), which yields all entries in the table. However, MonetDB cannot parse this. Markus

4 9