May 2009 - users-list

[MonetDB-users] ADO .net provider & ODBC error
by Zheng Han 18 May '09

18 May '09

Hi, Is there any ADO .net provider for MonetDB? We tried to use odbc driver in C#. When fecth unicode Chinese values, some error ocurred: System.InvalidOperationException Message="Internal .Net Framework Data Provider error 30." Source="System.Data" StackTrace: at System.Data.ProviderBase.DbBuffer.Validate(Int32 offset, Int32 count) at System.Data.ProviderBase.DbBuffer.PtrToStringUni(Int32 offset, Int32 length) at System.Data.Odbc.OdbcDataReader.internalGetString(Int32 i) at System.Data.Odbc.OdbcDataReader.GetString(Int32 i) at MonetDBTest.Form1.button1_Click(_object_ sender, EventArgs e) in E:\D_Bak\~MyWorks\MonetDB\MonetDBTest\Form1.cs:line 41 at System.Windows.Forms.Control.OnClick(EventArgs e) at System.Windows.Forms.Button.OnClick(EventArgs e) at System.Windows.Forms.Button.PerformClick() at System.Windows.Forms.Form.ProcessDialogKey(Keys keyData) at System.Windows.Forms.Control.ProcessDialogKey(Keys keyData) at System.Windows.Forms.Control.PreProcessMessage(Message& msg) at System.Windows.Forms.Control.PreProcessControlMessageInternal(Control target, Message& msg) at System.Windows.Forms.Application.ThreadContext.PreTranslateMessage(MSG& msg) at System.Windows.Forms.Application.ThreadContext.System.Windows.Forms.UnsafeNativeMethods.IMsoComponent.FPreTranslateMessage(MSG& msg) at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(Int32 dwComponentID, Int32 reason, Int32 pvLoopData) at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context) at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context) at System.Windows.Forms.Application.Run(Form mainForm) at MonetDBTest.Program.Main() in E:\D_Bak\~MyWorks\MonetDB\MonetDBTest\Program.cs:line 18 at System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args) at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args) at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly() at System.Threading.ThreadHelper.ThreadStart_Context(_object_ state) at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, _object_ state) at System.Threading.ThreadHelper.ThreadStart() InnerException: Regards, Zheng Han

1 0

[MonetDB-users] MAL samples
by Henri Asseily 18 May '09

18 May '09

Hi, where can I find some MAL samples? I'm trying to do very simple things such as storing and loading a bat, joining bats and iterating through them. I know I can do an explain on SQL statements, but those seem incredibly convoluted even for a simple select on a single column, which should be a straightforward sql.bind(,,,0) with follow-through sql.resultSet and sql.rsColumn if I understand this whole business correctly. Instead, it's doing a couple of algebra kunion and kdifference that I don't understand. I'm not trying to do MAL algebra on a SQL database, I'm trying to go 100% MAL and hand-coding some simple joins and iterators. I would have gone SQL all the way, but I need to iterate through a bat and calculate a result for each row, then return that sorted new bat based on the result. Doing it in SQL is overly complicated when some simple MAL statements should do the job. Thanks. --- Henri Asseily henri.tel

4 6

[MonetDB-users] Freeing memory through Mapi
by Alex Bo. 18 May '09

18 May '09

Hi, Sometimes when memory-heavy operations on MonetDB finish (I run them through Mapi), memory that is used by the MonetDB process isn't freed. Is there a Mapi command I can use too make Monet free up memory? What I do now is restart the process, but I am hoping to be able to avoid that. Thanks. -- View this message in context: http://www.nabble.com/Freeing-memory-through-Mapi-tp23595876p23595876.html Sent from the monetdb-users mailing list archive at Nabble.com.

2 1

[MonetDB-users] (Jdbc?) concurrency issues
by Bram Van Dam 18 May '09

18 May '09

Hi, I'm implementing a bit of a consumer-producer application in Java using the JDBC driver. When I use transactions in the producer & consumer, I'll frequently get the "ERROR: COMMIT: transaction is aborted because of concurency conflicts, will ROLLBACK instead" error. Not quite sure why this keeps happening. It's not like the same row is modified concurrently. The same *table* is (insert in one thread, vs delete in another). Could this be the cause of the problem? And more importantly, how do I get around this? thanks a bunch, - Bram

7 12

[MonetDB-users] MonetDB vs MySQL command list
by Tom H 15 May '09

15 May '09

Hi monetDB collective, I thought I would post to the list my current monetDB vs MySQL command list, as I had a bit of trouble working out how to do some routine admin stuff. I have to go and do some real work now, so I will update this later and post a more complete version. I would be grateful for any comments. Cheers, T Mysql command:CREATE DATABASE test1; The equivilent monetDB command is accessed using the monetDB tool from the command line; [root@localhost ~]$ monetdb create test1 warning: merovingian is not running successfully created database 'test1' Mysql command:SHOW DATABASES; The equivilent monetDB command is accessed using the monetDB tool from the command line; [root@localhost ~]# monetdb status warning: merovingian is not running name state uptime health last crash demo running 8m 38s 50%, 0s 2009-05-08 09:44:05 test1 stopped Mysql command:USE DATABASE test1; I don't think that you can change the database context using SQL within the client, so it would seem the way to access another database is to login to another client session with the alternative database name; shell> mclient -lsql --database=test1 sql> Mysql command:SHOW TABLES; shell> mclient -lsql --database=test1 sql>sql>select * from tables where system = false; I've not yet noted what the direct equivilents of these commands are; DESCRIBE TABLE; SHOW CREATE TABLE; GRANT ALL PRIVILEGES ON DATABASENAME.* to SOMEONE@localhost IDENTIFIED BY "PASSWORD"; mysqldump --opt -u username -p database > database_backup.sql mysql -u username -p database < database_backup.sql And I'm not entirely clear how a schema works, I'm guessing similar to the oracle model.

2 1

[MonetDB-users] rank over, sum over
by Alex Bo. 14 May '09

14 May '09

Hi, I am interested in calculating running sums, and therefor I looked for something like 'SUM OVER'. Since that doesn't exist, I looked into 'RANK OVER', and am trying to implement 'SUM OVER' according to that. I am looking at sql_rank_grp() in sql.c and rel_rankop() in rel_select.c, and am thinking how to modify them to support running sum. However, I can't figure out how to add another column to the parameters that sql_rank_grp gets (the column I want to sum over). I can't figure out how these two functions work together. Where are the incoming parameters for sql_rank_grp() created? I can see that rel_rankop() fills an sql_exp struct with the 'group by' and 'order by' columns, but I cannot find where to go from there. Any kind of guidance in the right direction would be appreciated. Thanks. -- View this message in context: http://www.nabble.com/rank-over%2C-sum-over-tp23543557p23543557.html Sent from the monetdb-users mailing list archive at Nabble.com.

1 0

[MonetDB-users] doc-available unuseable?
by Floris Ouwendijk (DT) 14 May '09

14 May '09

Hi, Given the following code, I'd assume to get an empty b element (for a database not containing the given document). if (doc-available('asdasda')) then doc('asdasda') else element b{} The result happens to be QUERY = if (doc-available('asdasda')) ERROR = !ERROR: [shred_url]: 1 times inserted nil due to errors at tuples 0@0. !ERROR: [shred_url]: first error was: !ERROR: shred: cannot stat `asdasda': No such file or directory !ERROR: CMDshred_url: operation failed. !ERROR: interpret_params: leftfetchjoin(param 2): evaluation error. If I do if (doc-available('asdasda')) then element a{} else element b{} Then i get b. What is the proper way to return some default value when a document is not available in the database? Or is this the proper way and should this be reported as a bug? Kind regards, Floris

4 4

[MonetDB-users] Load times expected
by Karanbir Singh 13 May '09

13 May '09

Is this reasonable : $ time ( echo "copy 587321890 records into sample from STDIN using delimiters '\t','\n','';" ; zcat logs20090511.txt.gz ) | recode iso8859-1..utf-8 | mclient -lsql -ukb -Pkb [ 587321890 ] real 618m29.030s 618m is a bit excessive, and well over what I had expected! Background on the machine : its a dual cpu opteron 252, 6 GB ram, running centos5.3/x86_64 stock - storage is from a 2 disk raid0, local to the machine ( sata 7200 disks, md-raid ). size of this logs file, unzipped is : 56854417129 There is almost nothing else running on the machine ( load avg from pre-kickoff was ~ 0 0 0 ). An aggregate querry that I kicked off last night ( about 14 hrs back ) is still running... Are these levels of performance in line with what most people would expect ? Is there a generic tuning guide or a best-practises doc that I should be looking at about now ? As something to compare against, pgsql-8.3 on the same machine, was able to do its bulk load in under 58 min, and then took just over 10 hours to run the aggregate query ( no index's on data ) : - KB

2 1

[MonetDB-users] copy from stdin oddness
by Karanbir Singh 13 May '09

13 May '09

Hi, This is on CentOS-5/x86_64 running : [kbsingh@koala data]$ rpm -qa | grep -i Monet MonetDB-1.22.0-1.el5.kb.oid64 MonetDB5-server-5.4.0-1.el5.kb.oid64 MonetDB-SQL-2.22.0-1.el5.kb.oid64 MonetDB-client-1.22.0-1.el5.kb.oid64 MonetDB-SQL-server5-2.22.0-1.el5.kb.oid64 server started with : [root@koala ~]# mserver5 --dbinit="include sql;" on the client : [kbsingh@koala data]$ echo "\d sample" | mclient -lsql -ukb -Pkb CREATE TABLE "kb"."sample" ( "id" char(33), "sdate" char(20), "key" varchar(255), "country" char(2), "region" char(2), "city" varchar(50), "pid" int ); [kbsingh@koala data]$ echo "select 'hello';" | mclient -lsql -ukb -Pkb % . # table_name % single_value # name % char # type % 5 # length [ "hello" ] [kbsingh@koala data]$ echo "copy 1000 records into sample from '/home/kbsingh/data/data/1000.utf8' using delimiters '\t','\n','';" | mclient -lsql -ukb -Pkb [ 1000 ] also: [kbsingh@koala data]$ mclient -lsql -ukb -Pkb -s "copy 1000 records into sample from '/home/kbsingh/data/data/1000.utf8' using delimiters '\t','\n','';" [ 1000 ] so, works so far. However: [kbsingh@koala data]$ cat 1000.utf8 | mclient -lsql -ukb -Pkb -s "copy 1000 records into sample from STDIN using delimiters '\t','\n','';" MAPI = kb@localhost:50000 QUERY = copy 1000 records into sample from STDIN using delimiters '\t','\n',''; ERROR = !SQLException:sql:missing sep line 0 field 0 !SQLException:importTable:failed to import table given that mclient is on the right side of the pipe, the data is surely being made available on the stdin, but why does mclient fail like this ? also, adding an OFFSET makes mclient just die quietly like this: [kbsingh@koala data]$ cat 1000.utf8 | mclient -lsql -ukb -Pkb -s "copy 5 offset 1000 records into sample from STDIN using delimiters '\t','\n','';" [kbsingh@koala data]$ so, what am I getting so wrong here ? the load chain I need to setup will look like this : zcat andy1.gz | iconv -f latin1 -t utf8 | mclient ...... that compressed file is 62 GB, the changes of me being able to expand that run the load from a specific filename is zero. -- Karanbir Singh : http://www.karan.org/ : 2522219@icq

2 4

[MonetDB-users] Timestamp performance
by Alex Bo. 13 May '09

13 May '09

Hi, How fast are join operations on timestamp fields compared to joins on int/bigint fields? I thought about using timestamp.unix_epoch to store numbers instead of timestamps to speed up joins, but if timestamp joins are just as fast then that wouldn't be necessary. Thanks. -- View this message in context: http://www.nabble.com/Timestamp-performance-tp23520512p23520512.html Sent from the monetdb-users mailing list archive at Nabble.com.

3 2