January 2017 - users-list

Announcement: New Dec2016-SP1 Bugfix release of MonetDB suite
by Panagiotis Koutsourakis 26 Jan '17

26 Jan '17

The MonetDB team at CWI/MonetDB BV is pleased to announce the Dec2016-SP1 bugfix release of the MonetDB suite of programs. More information about MonetDB can be found on our website at <http://www.monetdb.org/>. For details on this release, please see the release notes at <http://www.monetdb.org/Downloads/ReleaseNotes>. As usual, the download location is <http://dev.monetdb.org/downloads/>. Dec 2016-SP1 bugfix release Bug Fixes * 4039: Slow mserver5 start after drop of tables (> 1 hour) * 4048: Segfault on vacuum with parallel updates * 6079: pushselect optimizer bug on MAL snippet * 6140: INNER JOIN gives the results of a CROSS JOIN * 6150: Query giving wrong results, extra records are appearing * 6175: The program can't start because python27.dll is missing from your computer. * 6178: AVG + GROUP BY returns NULL for some records that should have results * 6179: mergetable optimizer messes up sample * 6182: sys.shutdown triggers assertion in clients.c * 6184: Incorrect result set - Extra records in result set

1 0

Backup Strategies
by Vincent Sheffer 25 Jan '17

25 Jan '17

I have been attempting to create a viable period backup strategy for MonetDB and have had little success to date. Our database is way too big to use msqldump, so our only option is the one outlined here: https://www.monetdb.org/Documentation/UserGuide/FastDumpRestore. I follow those steps and use rsync to do an incremental backup of the dbfarm to another device (from fast SSD to RAID5 array). That works fine, until it doesn’t, in which case the back up gets corrupted somehow and MonetDB won’t start from the backup. That is the state I am currently in. This is the error that I am getting: !FATAL: logger_load: BBPrename to sql_snapshots_bid failed Any help on 1. Fixing the existing backup, and/or 2. Helping me understand a better way to do period, incremental backups. Thanks, Vince The information transmitted, including any attachments, is intended only for the individual or entity to which it is addressed, and may contain confidential and/or privileged information. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by individuals or entities other than the intended recipient is prohibited, and all liability arising therefrom is disclaimed. If you have received this communication in error, please delete the information from any computer and notify the sender.

4 8

Database crash on executing a big insert
by Shrihari Shastry 25 Jan '17

25 Jan '17

Hi, I' trying to perform a big insert of about 4 mil rows of data: insert into clustertable1(keyword, p0,p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11,p12,p13,p14,p15,p16,p17,p18,p19,p20,p21,p22,p23,p24,p25,p26,p27,p28,p29,p30,p31,p32,p33,p34,p35,p36,p37,p38,p39,p40,p41,p42,p43,p44,p45,p46,p47,p48,p49,p50) select keyword,p0,p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11,p12,p13,p14,p15,p16,p17,p18,p19,p20,p21,p22,p23,p24,p25,p26,p27,p28,p29,p30,p31,p32,p33,p34,p35,p36,p37,p38,p39,p40,p41,p42,p43,p44,p45,p46,p47,p48,p49,p50 from cluster_submaster where cluster_no = '0'; But the moment I fire this query, the server crashes with the below printed in the logs: 2017-01-25 10:05:11 MSG kwresearch[3418]: # Visit http://www.monetdb.org/ for further information 2017-01-25 10:05:11 MSG kwresearch[3418]: # Listening for UNIX domain connection requests on mapi:monetdb:///monetDB/myfarm/kwresearch/.mapi.sock 2017-01-25 10:05:11 MSG kwresearch[3418]: # MonetDB/SQL module loaded 2017-01-25 10:07:06 MSG merovingian[3201]: proxying client (local) for database 'kwresearch' to mapi:monetdb:///monetDB/myfarm/kwresearch/.mapi.sock?database=kwresearch 2017-01-25 10:07:06 MSG merovingian[3201]: target connection is on local UNIX domain socket, passing on filedescriptor instead of proxying 2017-01-25 10:07:06 MSG merovingian[3201]: proxying client (local) for database 'kwresearch' to mapi:monetdb:///monetDB/myfarm/kwresearch/.mapi.sock?database=kwresearch 2017-01-25 10:07:06 MSG merovingian[3201]: target connection is on local UNIX domain socket, passing on filedescriptor instead of proxying 2017-01-25 10:07:12 MSG merovingian[3201]: database 'kwresearch' (3418) was killed by signal SIGSEGV Appreciate any help! Thanks, Shrihari ******************* iquanti.com E-Mail Domain LEGAL DISCLAIMER ******************* This E-Mail may contain confidential and/or legally privileged Information and is meant for the intended recipient(s) only. If you have received this e-mail in error and are not the intended recipient/s, kindly notify us at it(a)iquanti.com and then delete this e-mail immediately from your system. You are also hereby notified that any use, any form of reproduction, dissemination, copying, disclosure, modification, distribution and/or publication of this e-mail, its contents or its attachment/s other than by its intended recipient/s is strictly prohibited and may be unlawful.

2 1

bool from aggregate
by Stefano Fioravanzo 25 Jan '17

25 Jan '17

Hello, I would like to know if there is a way to replicate the behavior of the bool_and function of Postgresql. An example of its use is the following: SELECT bool_and(A) FROM mytable GROUP BY B; bool_and will return true if all values in the group are true, false otherwise. I would like to do the same thing in monetdb. Thanks Stefano

2 1

Case insensitive version of the IN operator?
by Kevork Vartanian 13 Jan '17

13 Jan '17

Hi, I was wondering if there is a case insensitive version of the IN operator, similar to ILIKE. Or is the IN itself case insensitive? It's mainly to avoid using nested OR's and ILIKE's if a case insensitive IN exists, to make the queries much simpler. Also, I don't want to use functions like lower as it's a big data set and the use of functions may make the queries even slower. Please advise. Thank you. Regards, Kevork Vartanian Technical Solutions Consultant kevork.vartanian(a)innotta.com.au | +61 406 776 789 | www.innotta.com.au

3 2

mclient COPY INFO Local files to a remote MonetDB Server Error
by ping gao 高平(0) 11 Jan '17

11 Jan '17

Hi ALL mclient COPY INFO Local files to a remote MonetDB Server Error. Mclient how to copy a local file to a remote MonetDB Server? Known to use: mclient COPY INFO - < file ; Will appear: syntax error, unexpected sqlINT in: Error Error Info :could not open file ‘xxxxx’ : No such file or directory

3 4

COPY INFO ERROR
by ping gao 高平(0) 10 Jan '17

10 Jan '17

Hi ALL I encountered a problem using a COPY INFO. Strange thing is my IP is 59, execute the following statement is not a problem on the machine.So freak! IP:192.168.110.59 (Database Server) [hotel-revenue-online@bjs-hotel-RevenueCompute-02 data_visual_monetdb]$ mclient -u monetdb -d hotel-revenue -s "COPY 2 RECORDS INTO hotel_revenue_octopus_booking_order FROM '/home/hotel-revenue-online/ping.gao/data_visual_monetdb/hotel_revenue_octopus_booking_order.sql' USING DELIMITERS ',','\\n'<smb://n'>" password: 2 affected rows IP:192.168.110.179 Error 1: [hotel-revenue-online@bjs-hotel-RevenueCompute-01 datavisual_service]$ mclient -h 192.168.110.59 -p 50001 -d hotel-revenue -s "COPY 2 RECORDS INTO hotel_revenue_octopus_hotel_info FROM /home/hotel-revenue-online/datavisual_service/data_tmp/hotel_revenue_octopus_hotel_info.data USING DELIMITERS '\\001','\\n'<smb://001','//n'>” syntax error, unexpected '/', expecting STRING or '(' or STDIN in: "copy 2 records into hotel_revenue_octopus_hotel_info from /“ Error 2: [hotel-revenue-online@bjs-hotel-RevenueCompute-01 datavisual_service]$ mclient -h 192.168.110.59 -p 50001 -d hotel-revenue -s "COPY 2 RECORDS INTO hotel_revenue_octopus_hotel_info FROM '/home/hotel-revenue-online/datavisual_service/data_tmp/hotel_revenue_octopus_hotel_info.data' USING DELIMITERS '\\001<smb://001>','\\n'<smb://n'>" could not open file '/home/hotel-revenue-online/datavisual_service/data_tmp/hotel_revenue_octopus_hotel_info.data': No such file or directory Error 3: [hotel-revenue-online@bjs-hotel-RevenueCompute-01 datavisual_service]$ mclient -h 192.168.110.59 -p 50001 -d hotel-revenue -s "COPY 2 RECORDS INTO hotel_revenue_octopus_hotel_info FROM STDIN DELIMITERS '\\001','\\n'<smb://001','//n'>" - < /home/hotel-revenue-online/datavisual_service/data_tmp/hotel_revenue_octopus_hotel_info.data 2 affected rows syntax error, unexpected sqlINT in: "90564318"

2 2

RE: COPY INFO ERROR
by Stefan Manegold 10 Jan '17

10 Jan '17

Not strange at all. Quotes are missing around file path (string). Sent from my Samsung Galaxy smartphone. -------- Original message --------From: "ping gao 高平(0)" <ping.gao(a)corp.elong.com> Date: 1/10/17 08:07 (GMT+01:00) To: Communication channel for MonetDB users <users-list(a)monetdb.org> Subject: COPY INFO ERROR Hi ALL I encountered a problem using a COPY INFO. Strange thing is my IP is 59, execute the following statement is not a problem on the machine.So freak! IP:192.168.110.59 (Database Server) [hotel-revenue-online@bjs-hotel-RevenueCompute-02 data_visual_monetdb]$ mclient -u monetdb -d hotel-revenue -s "COPY 2 RECORDS INTO hotel_revenue_octopus_booking_order FROM '/home/hotel-revenue-online/ping.gao/data_visual_monetdb/hotel_revenue_octopus_booking_order.sql' USING DELIMITERS ',','\\n'" password: 2 affected rows IP:192.168.110.179 Error 1: [hotel-revenue-online@bjs-hotel-RevenueCompute-01 datavisual_service]$ mclient -h 192.168.110.59 -p 50001 -d hotel-revenue -s "COPY 2 RECORDS INTO hotel_revenue_octopus_hotel_info FROM /home/hotel-revenue-online/datavisual_service/data_tmp/hotel_revenue_octopus_hotel_info.data USING DELIMITERS '\\001','\\n'” syntax error, unexpected '/', expecting STRING or '(' or STDIN in: "copy 2 records into hotel_revenue_octopus_hotel_info from /“ Error 2: [hotel-revenue-online@bjs-hotel-RevenueCompute-01 datavisual_service]$ mclient -h 192.168.110.59 -p 50001 -d hotel-revenue -s "COPY 2 RECORDS INTO hotel_revenue_octopus_hotel_info FROM '/home/hotel-revenue-online/datavisual_service/data_tmp/hotel_revenue_octopus_hotel_info.data' USING DELIMITERS '\\001','\\n'" could not open file '/home/hotel-revenue-online/datavisual_service/data_tmp/hotel_revenue_octopus_hotel_info.data': No such file or directory Error 3: [hotel-revenue-online@bjs-hotel-RevenueCompute-01 datavisual_service]$ mclient -h 192.168.110.59 -p 50001 -d hotel-revenue -s "COPY 2 RECORDS INTO hotel_revenue_octopus_hotel_info FROM STDIN DELIMITERS '\\001','\\n'" - < /home/hotel-revenue-online/datavisual_service/data_tmp/hotel_revenue_octopus_hotel_info.data 2 affected rows syntax error, unexpected sqlINT in: "90564318"

2 1

MAL function syntax
by Moritz Bruder 09 Jan '17

09 Jan '17

Hi, I'm having problems with the exact syntax of MAL functions. I used the syntax from the documentation, which is working for some functions, but my hand-written example doesn't work (neither when pasted in the REPL, nor as input file): "^<identifier> expected" at the end of every line. Any idea what could cause this? (I'm using a fairly recent development version.) Regards, Moritz

2 1

C UDF
by imad hajj chahine 06 Jan '17

06 Jan '17

Hi, After reviewing all the other alternatives like SQL and Python UDF, I was either stuck on performance with SQL UDF or on usability with Python UDF (unable to use with aggregation, and not such great performance with dates), so I decided to go the hard way with C functions, as a bonus it will give me the possibility to change the functionalities without worrying about dependencies, which was not the case in other languages. The purpose is to create a set of formatting functions for Year, Quarter, Month, Week and Day brackets, and of course i need to create the bulk version of each function for performance. Starting from the MTIMEdate_extract_year_bulk, now i have the simple function working, and successfully calling it from mclient: *str* *UDFyearbracket(str *ret, const date *v)* *{* * if (*v == date_nil) {* * *ret = GDKstrdup(str_nil);* * } else {* * int year;* * fromdate(*v, NULL, NULL, &year);* * *ret = (str) GDKmalloc(15);* * sprintf(*ret, "%d", year);* * }* * return MAL_SUCCEED;* *}* For the bulk version i get an error in the log: gdk_atoms.c:1345: strPut: Assertion `(v[i] & 0x80) == 0' failed. *str* *UDFBATyearbracket(bat *ret, const bat *bid)* *{* * BAT *b, *bn;* * BUN i,n;* * str *y;* * const date *t;* * if ((b = BATdescriptor(*bid)) == NULL)* * throw(MAL, "UDF.BATyearbracket", "Cannot access descriptor");* * n = BATcount(b);* * bn = COLnew(b->hseqbase, TYPE_str, BATcount(b), TRANSIENT);* * if (bn == NULL) {* * BBPunfix(b->batCacheid);* * throw(MAL, "UDF.BATyearbracket", "memory allocation failure");* * }* * bn->tnonil = 1;* * bn->tnil = 0;* * t = (const date *) Tloc(b, 0);* * y = (str *) Tloc(bn, 0);* * for (i = 0; i < n; i++) {* * if (*t == date_nil) {* * *y = GDKstrdup(str_nil);* * } else* * UDFyearbracket(y, t);* * if (strcmp(*y, str_nil) == 0) {* * bn->tnonil = 0;* * bn->tnil = 1;* * }* * y++;* * t++;* * }* * BATsetcount(bn, (BUN) (y - (str *) Tloc(bn, 0)));* * bn->tsorted = BATcount(bn)<2;* * bn->trevsorted = BATcount(bn)<2;* * BBPkeepref(*ret = bn->batCacheid);* * BBPunfix(b->batCacheid);* * return MAL_SUCCEED;* *}* PS: I am not a c expert but i can find my way with basic operations and pointers. Any help or suggestions is appreciated. Thank you.

4 17