March 2016 - users-list

Re: Duplicate and reappearing tables.
by Nicolas Stalder 16 Mar '16

16 Mar '16

> > Message: 5 > Date: Tue, 15 Mar 2016 21:27:10 +0100 (CET) > From: Stefan Manegold <Stefan.Manegold(a)cwi.nl> > To: Communication channel for MonetDB users <users-list(a)monetdb.org> > Subject: Re: Duplicate and reappearing tables. > > Hi Nicolas, > > could you please share which version of MonetDB you're using on which OS? > > Also, did you initially create your database with the very same version of > MonetDB that you're using now, or did you create it with an earlier version > (which) and then upgrade it to the one you're using now (directly or via > intermetiate release steps)? > Stefan, It's Jul2015-SP2 running on Debian 8 (jessie). We loaded it from scratch, two daily files 100-250MB in size. Earlier we were running Jul2015-SP1 with the same issue. > Message: 7 > Date: Wed, 16 Mar 2016 08:02:47 +0000 > From: Marcel Blok <m.blok(a)chs.nl> > To: Communication channel for MonetDB users <users-list(a)monetdb.org> > Subject: RE: Duplicate and reappearing tables. > > Hi Nicolas, > > We have (and unfortunately sometimes still do) experience similar issues. > The issues appear most often at stopping and starting the database. In an > earlier thread (named "WAL read files") Pierre-Adrien found out that > underscores in table and/or column names could cause conflicts, and cause > similar issues. Maybe this is related to the issue you are experiencing? > > See also the bug report: > https://www.monetdb.org/bugzilla/show_bug.cgi?id=3912 I am aware of this report. However, I do not have any tables where <table_name>_<column_name> conflicts. The WAL files would be in db/sql_logs/sql, yes? I see a 12 byte file "log" and a 513K file "log.791". Can I inspect or understand what those files contain? What is in this numbered log file? I also tried loading a msqldump of the data into a new MonetDB instance, which eventually crashes with "Illegal input sequence". Are there any ways of making mserver5 more verbose? Clearly, something is happening at database startup. Cheers, Nicolas.

1 0

Shipping data / old voyages
by Brian Hood 16 Mar '16

16 Mar '16

Hi Guys, I have spent quite a bit of time with the voc database developing another project. I have where did you acquire this data as its very interesting. Regards Brian Hood

2 2

Duplicate and reappearing tables.
by Nicolas Stalder 16 Mar '16

16 Mar '16

Hi all I am running a currently roughly 400G-sized single Monet instance. We load daily CSV files into corresponding tables, and use merge tables to query the data (e.g., feed_20160301, feed_20160302, ... and feed_mt). We have a problem mainly with duplicate tables (same schema + table name, different table_id in sys.tables). My approach is to "DROP TABLE foo" for N - 1 times if there are N copies (is there even a way to SELECT or DROP by table_id?). This seemingly fixes the problem, however after restarting the database, the dropped tables reappear. Similarly, I have some old empty tables, which reappear after being dropped when the database is restarted. We start and stop the database to roll over connections during maintenance. Is this a known issue? Can it be fixed? Not sure how to make this reproducible. Thanks, Nicolas Stalder.

3 2

will monetdb work well if it needs to swap onto a SSD?
by Anthony Damico 15 Mar '16

15 Mar '16

pretty sure that monetdb hits a huge performance wall when you use up all your ram, but i'm curious if anyone has experience with swapping onto a SSD? is the speed-loss nearly equally bad, or is swapping onto a SSD a reasonable choice? thanks all

1 0

Backslash n interpreted as newline.
by Nicolas Stalder 15 Mar '16

15 Mar '16

Hi all I have noticed that when loading tables from CSV files via COPY INTO ... USING DELIMITERS '\t','\n', the ASCII characters '\' followed by 'n' are interpreted as a newline, leading to the loading being aborted due to an incorrect number of columns in this row. This seems to me to be a bug? Also, it would be most helpful if the error message contained a line number (as is the case if there is unicode in the CSV file). Thanks, Nicolas Stalder.

1 0

Debugging: !SELECT: no such operator 'normalize2'
by Willem Ligtenberg 15 Mar '16

15 Mar '16

I have created a function in R for MonetDB. My first function works fine: dbGetQuery(conn, "DROP FUNCTION normalize;") functionDef <- paste( "CREATE FUNCTION normalize(welltype STRING, data_column DOUBLE) RETURNS TABLE (i DOUBLE) LANGUAGE R {", "idx <- which(welltype == 'LC')", "100 * data_column / median(data_column[idx])", "};", sep = "\n") dbGetQuery(conn, functionDef) # example use stmt <- "SELECT barcode, normalize(welltype_code, data_column1) FROM hcs;" dbGetQuery(conn, stmt) # Works! Yay! So I wanted to create a more advanced version: dbGetQuery(conn, "DROP FUNCTION normalize2;") functionDef <- paste( "CREATE FUNCTION normalize2(barcode DOUBLE, welltype STRING, data_column DOUBLE) RETURNS TABLE (i DOUBLE) LANGUAGE R {", "idx <- which(welltype == 'LC')", "100 * data_column / median(data_column[idx])", "};", sep = "\n") dbGetQuery(conn, functionDef) As you see, the only thing that I have changed so far, is the addition of an extra argument to the function. However, this fails: # example use stmt <- "SELECT normalize2(barcode, welltype_code, data_column1) FROM hcs;" dbGetQuery(conn, stmt) I checked, and the types are all fine: barcode = DOUBLE welltype_code = CHARACTER LARGE OBJECT data_column1 = DOUBLE So what is going wrong? Kind regards, Willem

2 3

why "INSERT INTO ... SELECT" is concurrency conflicts with "CREATE TABLE"
by Pierre-Adrien Coustillas 15 Mar '16

15 Mar '16

Hi, Why is that the query INSERT INTO io__selection_top (id_navigateur) SELECT id_navigateur FROM bdm__index_client_cookies_id_navigateur; has a "COMMIT: transaction is aborted because of concurrency conflicts, will ROLLBACK instead" whith this query : DROP TABLE pierre; What is the table that a conflict ? SYS.TABLES ? Thanks Pierre -- 1G6 52 route de bischwiller 67300 Schiltigheim Société de Services et de Formations en Logiciels Libres http://1g6.biz Tél : 06 64 63 70 35 // 09 54 38 70 90

3 2

Monetdb crashes.
by Souri Ratnaparkhi 14 Mar '16

14 Mar '16

Hello, We have a main table with 126 columns and approx. 94000 rows. We are trying to load approx 2500 records from temporary table into main table using query “insert into select” query. The database crashes upon query execution. We get error “*monetdbd: internal error while starting mserver, please refer to the logs*” when to try to connect again. No specific error is created in log file. 1500 records successfully loads from temp table to main table. We also created another main table with same DDL, that table is empty & inserted all the records from temp successfully without any issue. What could be the reason? Is 126 cols an issue? Please help. We are stuck. (btw: We have machine about 200GB of hard disk & 16 GB RAM (14 GB free)) OS* - Ubuntu 14.04.2 LTS (GNU/Linux 3.13.0-55-generic x86_64)* Monetdb version* - MonetDB v11.21.13 (Jul2015-SP2)* Thank you [image: Description: Description: Description: Description: logo] * Souri Ratnaparkhi United Software Associates Pvt. Ltd. Pune, India Mob: 8975677255* * Phone (India Lines): (91) 020-25445550 **www.usain.com* <http://www.usain.com/>

4 10

User Defined Function and regular Function
by Shmagi Kavtaradze 13 Mar '16

13 Mar '16

I read documentation, but have some questions: 1) What is the real difference between UDF and just creating function in sql? I mean the speed of execution. 2) If I create a function, is it implicitly C? I am asking because there is python binding also. 3) If I have already installed Monetdb and want to create UDF, should I recompile whole monetdb? I am not good enough yet to do something like this. I am asking, because I want to take a text type column full of 0s and 1s ("110101010"), represent it as a "int" or "bit" and do bitwise AND against other columns. When I am saying represent as int/bit, I mean not to change ("110101010") into bit, just let C know that it should interpret that number already as int or bit.

2 2

Function
by Shmagi Kavtaradze 12 Mar '16

12 Mar '16

I want to compare two integer columns (filled with 1s and 0s) to each other with bitwise and, then count number of 1s and divide by length of column entries. I wrote a function, but not sure if it is right or how to create in in Monetdb from console. create function BAnd(int a, int b) returns float begin declare count int; int length = floor(log10(abs(a))) + 1; temp = a & b; while (temp != 0) { if (n % 10 == 1) count++; temp /= 10; } return count/length; end;

1 0