July 2013 - users-list

Re: Could i binary bulk load to a CERTAIN column?
by integrity 21 Jul '13

21 Jul '13

my table test4 is : create table test4(id bigint, a float, b float, c float); so i didnt selectively load to test4 but it crashed. my code is : #include <stdio.h> #include <stdlib.h> #include <time.h> #include <mapi.h> void itoa (int n,char s[]) { int i,j,sign,k; char tmp; if((sign=n)<0)//记录符号 n=-n;//使n成为正数 i=0; do{ s[i++]=n%10+'0';//取下一个数字 }while ((n/=10)>0);//删除该数字 if(sign<0) s[i++]='-'; s[i]='\0'; for(j=i-1,k=0;j>k;j--,k++) { tmp = s[k]; s[k] = s[j]; s[j] = tmp; } } int writetoFile() { unsigned int seed; int k,j,i=0; long al[200000]; double af[200000]; FILE *fp; char fname[5]; fp = fopen("id","wb"); //创建二进制文件 for(k=1;k<=200000;k++) { al[k] = rand(); } fwrite(al, sizeof(long), 200000,fp); //fwrite以二进制形式对文件操作 fclose(fp); for(j=1;j<=20;j++) { for(k=1;k<=200000;k++) { af[k] = (rand() /(double)(RAND_MAX)); } itoa(j,fname); fp=fopen(fname,"wb"); fwrite(af, sizeof(double), 200000,fp); fclose(fp); } } void die(Mapi dbh, MapiHdl hdl) { if (hdl != NULL) { mapi_explain_query(hdl, stderr); do { if (mapi_result_error(hdl) != NULL) mapi_explain_result(hdl, stderr); } while (mapi_next_result(hdl) == 1); mapi_close_handle(hdl); mapi_destroy(dbh); } else if (dbh != NULL) { mapi_explain(dbh, stderr); mapi_destroy(dbh); } else { fprintf(stderr, "command failed\n"); } exit(-1); } MapiHdl query(Mapi dbh, char *q) { MapiHdl ret = NULL; if ((ret = mapi_query(dbh, q)) == NULL || mapi_error(dbh) != MOK) die(dbh, ret); return(ret); } void update(Mapi dbh, char *q) { MapiHdl ret = query(dbh, q); if (mapi_close_handle(ret) != MOK) die(dbh, ret); } int filetoMonetDB() { Mapi dbh; MapiHdl hdl = NULL; dbh = mapi_connect("localhost",50000, "monetdb", "monetdb", "sql","mydb2" ); if(mapi_error(dbh)) die(dbh,hdl); char *sql = NULL; sql = (char *)malloc(100); memset(sql,0,100); //sprintf(sql, "copy binary into tmatch from('/home/data1/writeBinary/id','/home/data1/writeBinary/1','/home/data1/writeBinary/2','/home/data1/writeBinary/3','/home/data1/writeBinary/4')"); // '/home/data1/writeBinary/5','/home/data1/writeBinary/6','/home/data1/writeBinary/7','/home/data1/writeBinary/8','/home/data1/writeBinary/9','/home/data1/writeBinary/10', \ // '/home/data1/writeBinary/11', '/home/data1/writeBinary/12', '/home/data1/writeBinary/13', '/home/data1/writeBinary/14', '/home/data1/writeBinary/15', \ // '/home/data1/writeBinary/16', '/home/data1/writeBinary/17', '/home/data1/writeBinary/18', '/home/data1/writeBinary/19', '/home/data1/writeBinary/20' )"); // sprintf(sql,"insert into test2 values(2)"); sprintf(sql, "copy binary into test3 from('/home/data1/writeBinary/id','/home/data1/writeBinary/1')"); //sprintf(sql,"copy binary into test4 from('/home/data1/writeBinary/id','/home/data1/writeBinary/1','home/data1/writeBinary/2','home/data1/writeBinary/3')"); //sprintf(sql,"copy binary into test4(id) from('/home/data1/writeBinary/id')"); update(dbh, sql); mapi_destroy(dbh); free(sql); return 1; } int main() { struct timeval tpstart,tpend; double timeuse; gettimeofday(&tpstart,NULL); writetoFile(); gettimeofday(&tpend,NULL); timeuse=1000000*(tpend.tv_sec-tpstart.tv_sec)+tpend.tv_usec-tpstart.tv_usec;//unit:us timeuse /= 1000000;//unit:s printf("21 columns write to file used %f seconds.\n",timeuse) ; gettimeofday(&tpstart,NULL); filetoMonetDB(); gettimeofday(&tpend,NULL); timeuse=1000000*(tpend.tv_sec-tpstart.tv_sec)+tpend.tv_usec-tpstart.tv_usec;//unit:us timeuse /= 1000000;//unit:s printf("Files load to MonetDB used %f seconds.\n",timeuse) ; return 1; }

3 3

Could i binary bulk load to a CERTAIN column?
by integrity 21 Jul '13

21 Jul '13

Hi all, when i use : copy binary into test4 from('/home/data1/writeBinary/id','/home/data1/writeBinary/1','home/data1/writeBinary/2','home/data1/writeBinary/3'); it fails: 21 columns write to file used 0.195698 seconds. *** glibc detected *** ./fitscolumntodb: malloc(): memory corruption: 0x0000000001d743e0 *** ======= Backtrace: ========= /lib64/libc.so.6[0x3efde7d982] /lib64/libc.so.6(__libc_malloc+0x63)[0x3efde7f313] /lib64/libc.so.6(__strdup+0x22)[0x3efde85c42] /home/data1/opt/MonetDB/lib/libmapi.so.6(+0xb24f)[0x7f77d4ca724f] /home/data1/opt/MonetDB/lib/libmapi.so.6(mapi_query+0xa0)[0x7f77d4cac410] ./fitscolumntodb[0x400fc5] ./fitscolumntodb[0x40101c] ./fitscolumntodb[0x401198] ./fitscolumntodb[0x40127d] /lib64/libc.so.6(__libc_start_main+0xf5)[0x3efde21a05] ./fitscolumntodb[0x400bc9] ======= Memory map: ======== 00400000-00402000 r-xp 00000000 08:01 18874373 /home/data1/writeBinary/fitscolumntodb 00601000-00602000 r--p 00001000 08:01 18874373 /home/data1/writeBinary/fitscolumntodb 00602000-00603000 rw-p 00002000 08:01 18874373 /home/data1/writeBinary/fitscolumntodb 01d74000-01d95000 rw-p 00000000 00:00 0 [heap] 3efd600000-3efd620000 r-xp 00000000 fd:01 700425 /usr/lib64/ld-2.16.so 3efd820000-3efd821000 r--p 00020000 fd:01 700425 /usr/lib64/ld-2.16.so 3efd821000-3efd822000 rw-p 00021000 fd:01 700425 /usr/lib64/ld-2.16.so 3efd822000-3efd823000 rw-p 00000000 00:00 0 3efde00000-3efdfad000 r-xp 00000000 fd:01 700426 /usr/lib64/libc-2.16.so 3efdfad000-3efe1ad000 ---p 001ad000 fd:01 700426 /usr/lib64/libc-2.16.so 3efe1ad000-3efe1b1000 r--p 001ad000 fd:01 700426 /usr/lib64/libc-2.16.so 3efe1b1000-3efe1b3000 rw-p 001b1000 fd:01 700426 /usr/lib64/libc-2.16.so 3efe1b3000-3efe1b8000 rw-p 00000000 00:00 0 3efe200000-3efe216000 r-xp 00000000 fd:01 700427 /usr/lib64/libpthread-2.16.so 3efe216000-3efe416000 ---p 00016000 fd:01 700427 /usr/lib64/libpthread-2.16.so 3efe416000-3efe417000 r--p 00016000 fd:01 700427 /usr/lib64/libpthread-2.16.so 3efe417000-3efe418000 rw-p 00017000 fd:01 700427 /usr/lib64/libpthread-2.16.so 3efe418000-3efe41c000 rw-p 00000000 00:00 0 3efe600000-3efe603000 r-xp 00000000 fd:01 700430 /usr/lib64/libdl-2.16.so 3efe603000-3efe802000 ---p 00003000 fd:01 700430 /usr/lib64/libdl-2.16.so 3efe802000-3efe803000 r--p 00002000 fd:01 700430 /usr/lib64/libdl-2.16.so 3efe803000-3efe804000 rw-p 00003000 fd:01 700430 /usr/lib64/libdl-2.16.so 3efea00000-3efea15000 r-xp 00000000 fd:01 672671 /usr/lib64/libz.so.1.2.7 3efea15000-3efec14000 ---p 00015000 fd:01 672671 /usr/lib64/libz.so.1.2.7 3efec14000-3efec15000 r--p 00014000 fd:01 672671 /usr/lib64/libz.so.1.2.7 3efec15000-3efec16000 rw-p 00015000 fd:01 672671 /usr/lib64/libz.so.1.2.7 3efee00000-3efee07000 r-xp 00000000 fd:01 700431 /usr/lib64/librt-2.16.so 3efee07000-3eff006000 ---p 00007000 fd:01 700431 /usr/lib64/librt-2.16.so 3eff006000-3eff007000 r--p 00006000 fd:01 700431 /usr/lib64/librt-2.16.so 3eff007000-3eff008000 rw-p 00007000 fd:01 700431 /usr/lib64/librt-2.16.so 3eff600000-3eff615000 r-xp 00000000 fd:01 700435 /usr/lib64/libgcc_s-4.7.2-20121109.so.1 3eff615000-3eff814000 ---p 00015000 fd:01 700435 /usr/lib64/libgcc_s-4.7.2-20121109.so.1 3eff814000-3eff815000 r--p 00014000 fd:01 700435 /usr/lib64/libgcc_s-4.7.2-20121109.so.1 3eff815000-3eff816000 rw-p 00015000 fd:01 700435 /usr/lib64/libgcc_s-4.7.2-20121109.so.1 3effa00000-3effa5d000 r-xp 00000000 fd:01 700439 /usr/lib64/libpcre.so.1.0.1 3effa5d000-3effc5c000 ---p 0005d000 fd:01 700439 /usr/lib64/libpcre.so.1.0.1 3effc5c000-3effc5d000 r--p 0005c000 fd:01 700439 /usr/lib64/libpcre.so.1.0.1 3effc5d000-3effc5e000 rw-p 0005d000 fd:01 700439 /usr/lib64/libpcre.so.1.0.1 3effe00000-3effe1f000 r-xp 00000000 fd:01 700440 /usr/lib64/libselinux.so.1 3effe1f000-3f0001e000 ---p 0001f000 fd:01 700440 /usr/lib64/libselinux.so.1 3f0001e000-3f0001f000 r--p 0001e000 fd:01 700440 /usr/lib64/libselinux.so.1 3f0001f000-3f00020000 rw-p 0001f000 fd:01 700440 /usr/lib64/libselinux.so.1 3f00020000-3f00022000 rw-p 00000000 00:00 0 3f00200000-3f00216000 r-xp 00000000 fd:01 700438 /usr/lib64/libresolv-2.16.so 3f00216000-3f00415000 ---p 00016000 fd:01 700438 /usr/lib64/libresolv-2.16.so 3f00415000-3f00416000 r--p 00015000 fd:01 700438 /usr/lib64/libresolv-2.16.so 3f00416000-3f00417000 rw-p 00016000 fd:01 700438 /usr/lib64/libresolv-2.16.so 3f00417000-3f00419000 rw-p 00000000 00:00 0 3f06200000-3f06203000 r-xp 00000000 fd:01 700443 /usr/lib64/libcom_err.so.2.1 3f06203000-3f06402000 ---p 00003000 fd:01 700443 /usr/lib64/libcom_err.so.2.1 3f06402000-3f06403000 r--p 00002000 fd:01 700443 /usr/lib64/libcom_err.so.2.1 3f06403000-3f06404000 rw-p 00003000 fd:01 700443 /usr/lib64/libcom_err.so.2.1 3f06600000-3f06603000 r-xp 00000000 fd:01 665783 /usr/lib64/libkeyutils.so.1.4 3f06603000-3f06802000 ---p 00003000 fd:01 665783 /usr/lib64/libkeyutils.so.1.4 3f06802000-3f06803000 r--p 00002000 fd:01 665783 /usr/lib64/libkeyutils.so.1.4 3f06803000-3f06804000 rw-p 00003000 fd:01 665783 /usr/lib64/libkeyutils.so.1.4 3f06a00000-3f06a0a000 r-xp 00000000 fd:01 700441 /usr/lib64/libkrb5support.so.0.1 3f06a0a000-3f06c09000 ---p 0000a000 fd:01 700441 /usr/lib64/libkrb5support.so.0.1 3f06c09000-3f06c0a000 r--p 00009000 fd:01 700441 /usr/lib64/libkrb5support.so.0.1 3f06c0a000-3f06c0b000 rw-p 0000a000 fd:01 700441 /usr/lib64/libkrb5support.so.0.1 3f06e00000-3f06e28000 r-xp 00000000 fd:01 700442 /usr/lib64/libk5crypto.so.3.1 3f06e28000-3f07028000 ---p 00028000 fd:01 700442 /usr/lib64/libk5crypto.so.3.1 3f07028000-3f07029000 r--p 00028000 fd:01 700442 /usr/lib64/libk5crypto.so.3.1 3f07029000-3f0702a000 rw-p 00029000 fd:01 700442 /usr/lib64/libk5crypto.so.3.1 3f0702a000-3f0702b000 rw-p 00000000 00:00 0 3f07200000-3f072d9000 r-xp 00000000 fd:01 700444 /usr/lib64/libkrb5.so.3.3 3f072d9000-3f074d9000 ---p 000d9000 fd:01 700444 /usr/lib64/libkrb5.so.3.3 3f074d9000-3f074e3000 r--p 000d9000 fd:01 700444 /usr/lib64/libkrb5.so.3.3 3f074e3000-3f074e5000 rw-p 000e3000 fd:01 700444 /usr/lib64/libkrb5.so.3.3 3f07600000-3f07641000 r-xp 00000000 fd:01 700445 /usr/lib64/libgssapi_krb5.so.2.2 3f07641000-3f07840000 ---p 00041000 fd:01 700445 /usr/lib64/libgssapi_krb5.so.2.2 3f07840000-3f07841000 r--p 00040000 fd:01 700445 /usr/lib64/libgssapi_krb5.so.2.2 3f07841000-3f07843000 rw-p 00041000 fd:01 700445 /usr/lib64/libgssapi_krb5.so.2.2 3f07e00000-3f07f9b000 r-xp 00000000 fd:01 700446 /usr/lib64/libcrypto.so.1.0.1c 3f07f9b000-3f0819a000 ---p 0019b000 fd:01 700446 /usr/lib64/libcrypto.so.1.0.1c 3f0819a000-3f081b4000 r--p 0019a000 fd:01 700446 /usr/lib64/libcrypto.so.1.0.1c 3f081b4000-3f081bf000 rw-p 001b4000 fd:01 700446 /usr/lib64/libcrypto.so.1.0.1c 3f081bf000-3f081c4000 rw-p 00000000 00:00 0 3f08e00000-3f08e5b000 r-xp 00000000 fd:01 700447 /usr/lib64/libssl.so.1.0.1c 3f08e5b000-3f0905a000 ---p 0005b000 fd:01 700447 /usr/lib64/libssl.so.1.0.1c 3f0905a000-3f0905e000 r--p 0005a000 fd:01 700447 /usr/lib64/libssl.so.1.0.1c 3f0905e000-3f09064000 rw-p 0005e000 fd:01 700447 /usr/lib64/libssl.so.1.0.1c 3f0fa00000-3f0fa0f000 r-xp 00000000 fd:01 665786 /usr/lib64/libbz2.so.1.0.6 3f0fa0f000-3f0fc0e000 ---p 0000f000 fd:01 665786 /usr/lib64/libbz2.so.1.0.6 3f0fc0e000-3f0fc0f000 r--p 0000e000 fd:01 665786 /usr/lib64/libbz2.so.1.0.6 3f0fc0f000-3f0fc10000 rw-p 0000f000 fd:01 665786 /usr/lib64/libbz2.so.1.0.6 3f11600000-3f11663000 r-xp 00000000 fd:01 700543 /usr/lib64/libfreebl3.soAborted (core dumped) but if i load fewer columns, it succeed: sprintf(sql, "copy binary into test3 from('/home/data1/writeBinary/id','/home/data1/writeBinary/1')"); so if i can load into table with a certain column ? Thanks! Meng

2 1

XQuery support in MonetDB
by Stephen Cameron 20 Jul '13

20 Jul '13

Hello List, Is there anyone making use of XQuery with MonetDB? Why was this XQuery work terminated/frozen, obviously its now a low priority, but why? People seem to resist saying that something is not as good an idea as first thought! Thanks for your insights. Steve Cameron collinta.com.au

2 1

Must the file be deleted after "copy binary into table from file"？
by integrity 19 Jul '13

19 Jul '13

can i have the file reserved? Thanks Meng

4 3

Re: Must the file be deleted after "copy binary into table from file"？
by integrity 19 Jul '13

19 Jul '13

Thanks very much for explaining these to me, but is it possible to provide two options of move, one do the move and reserve, one just move? That will be very convenient for uses.^_^ Sincerely, Meng PS,google mail in China is very unstable, often got disconnected from internet for some reason, so I have to applie another account here. ------------------ Original ------------------ From: "Stefan Manegold"<Stefan.Manegold(a)cwi.nl>; Date: Fri, Jul 19, 2013 02:15 PM To: "Communication channel for MonetDB users"<users-list(a)monetdb.org>; Cc: "Niels Nes"<Niels.Nes(a)cwi.nl>; "sjoerd"<Sjoerd.Mullender(a)cwi.nl>; Subject: Re: Must the file be deleted after "copy binary into table from file"？ Hi Meng, for efficiently, i.e., to avoid a file copy, MonetDB's binary copy into simply moves(^) the user-provided and to be loaded files into its dbfarm. Thus, if you need to preserve a copy of the files you provide, you need to do so yourself before executing a binary copy into. Best, Stefan (^) 1) The actual behavior (move) is actually different than the copy that the documentation at http://www.monetdb.org/Documentation/Cookbooks/SQLrecipes/BinaryBulkLoad suggests. We need to fix the latter. 2) The actual behavior currently differs between data types; for numerical types, the user-provided files are moved as explained above; for stings, the files are read and processed, i.e., the used-provided file remains in place as-is. We need to consider unifying this behavior, e.g., by deleting also the user provided files for strings once they have been processed/loaded. 3) In case the copy into file, e.g., due to a failing sanity check after the file is moved, that data is factually lost, i.e., the files are gone, but no data is loaded into the database; we need to either document this clearly, or make the implementation behave like a transaction. ----- Original Message ----- > can i have the file reserved? > > > > Thanks > Meng > _______________________________________________ > users-list mailing list > users-list(a)monetdb.org > http://mail.monetdb.org/mailman/listinfo/users-list > -- | Stefan.Manegold(a)CWI.nl | DB Architectures (DA) | | www.CWI.nl/~manegold/ | Science Park 123 (L321) | | +31 (0)20 592-4212 | 1098 XG Amsterdam (NL) | _______________________________________________ users-list mailing list users-list(a)monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list .

2 1

Join index
by Bouslah Ahmed 17 Jul '13

17 Jul '13

Hi, I want to optimize the runtime of this query Select sum(a.C4), b.C1, b.C2, b.C3 from v1 a, v2 b where (b.C1 = a.C1 and b.C2 = a.C2 and b.C3 = a.C3) or (b.C1 = a.C1 and b.C2 = a.C2 and b.C3 = 0) or (b.C1 = a.C1 and b.C2 = 0 and b.C3 = 0) or (b.C1 = 0 and b.C2 = 0 and b.C3 = 0) group by b.C1, b.C2, b.C3; why I thought of creating a BITMAP index join or create a bitmap index for v1 columns (c1, c2, c3) and v2 (c1, c2, c3). I can't do this with MonetDB can you help me.

2 1

failed to open file when 'copy binary into table from binaryformfile'
by integrity 17 Jul '13

17 Jul '13

hi all, when i use the command in sql: sql>copy binary into testtable from('file1'); failed to open file file1 the file1 contents : [svomhpc@localhost /home/data1]$>cat file1 0123[svomhpc@localhost /home/data1]$>hexdump file1 0000000 0000 3000 0000 3100 0000 3200 0000 3300 0000010 can anyone tell me what's wrong with file1 or how to use copy binary with? Meng

5 8

International characters in MonetDB
by aaaaaa ggggggg 15 Jul '13

15 Jul '13

Hello All, I have been trying to do some bulk uploads from CSV files into a MonetDB database. All the fields in the database are VARCHAR. The text in some of the fields contains international characters such as : ü, Ó, é etc. When I execute : copy into database from 'filename.csv' using delimiters ',','\n','"' NULL as ''; I get an error stating that the fields with words containing the international characters could not be imported, and the import process stops. How can I solve this issue? (I am running Monetdb on windows) Thanks, SG

3 5

Giving monetdb a fair chance
by Franck Routier 12 Jul '13

12 Jul '13

Hi, I am benchmarking different DB alternatives for our BI application. We are using Mondrian/JPivot on a dataset with ~300 millions row, and several parent/child dimensions. We are running some 50 real life scenario (ie 50 differents cubes using parts of this dataset) and measuring user response time on a server with several stripped Sata3 SSD, 32 GB ram and a i7-3820 CPU @ 3.60GHz (6 cores). During the tests, Monetdb performed by far the worst of Postgresql, VectorWise and (obvioulsy) MonetDB. Things that take 10 seconds with Postgresql take 450 second with monetdb... This was not expected at all, so looking at the server, I saw that MonetDB was consuming all memory (why not), including swap : bad idea I think. I was expecting MonetDB to memory mapped files when needed, real memory, but not use swap as it it was real memory... So here is my question : with a (quite) big dataset, 32GB ram, is there anything I can do to help MonetDB behave according to expectancies ? (maybe I should use ulimit ?). Thanks for your input, Franck

4 10

Problem with Mtest.py and monetdb command
by Bingyi Cao 11 Jul '13

11 Jul '13

Hey, I am new to MonetDB. I installed MonetDB yesterday. And I run Mtest.py. The results are as below: *!ERROR: Testing FAILED SIGNIFICANTLY (18 out of 1854 tests failed)* * * * 40 out of 1854 tests could not be executed* * 1 out of 1854 tests produced slightly different output* * sql/test/BugTracker-2012/predicate_select.Bug-3090* * 17 out of 1854 tests produced SIGNIFICANTLY different output* * monetdb5/optimizer/inlineFunction* * monetdb5/optimizer/ifthencst* * monetdb5/tests/BugTracker/kunion-and-nil.Bug-1667* * sql/jdbc/tests/Test_PSsomeamount* * sql/jdbc/tests/Test_PSlargebatchval* * sql/jdbc/tests/Test_PSmanycon* * sql/jdbc/tests/Test_PSmetadata* * sql/jdbc/tests/Test_PSsqldata* * sql/jdbc/tests/Test_Rsqldata* * sql/jdbc/tests/BugConcurrent_sequences* * sql/test/BugTracker-2010/incomplete-utf8-sequence.Bug-2575* * sql/test/BugTracker-2012/conditions_when_for_triggers_do_not_work.Bug-2073* * sql/test/BugTracker-2012/create_function.Bug-3172* * sql/test/BugTracker-2012/currenttime.Bug-2781* * sql/test/BugTracker-2012/null_except_null.Bug-3040* * sql/test/BugTracker-2012/day-of-month-localization.Bug-2962* * sql/test/Triggers/recursive_triggers* * * * * * First, check the testing results in /usr/local/mTests/index.html !* * * * Then, fix the problems by:* * - fixing sources and test scripts* * - fixing stable output by hand* * - approving test output by Mapprove.py (cf. Mapprove.py -?)* * * * After that, re-run Mtest.* I hasn't change any source code yet. I am not sure whether I installed it correctly. Also now I have no problem with command monetdbd, mserver5, mclient. However, I cannot use command with monetdb, the error information is always as below: monetdb: cannot find a control socket, use -h and/or -p I did a little bit research, to me it seems that it should automatically find a socket. Tell me if I am wrong. I also try to use the option -h -P, which doesn't work still. Do I actually install it correctly I tried to uninstall and reinstall it,but it hasn't solve the problem at all. Thanks very much!

2 3