- users-list - monetdb.org

Extract Table Statistics after Data import using Monet.Read.Csv
by Jigar Shah 29 Dec '15

29 Dec '15

Hello, I am trying to import a very large dataset of approx.. 170 million records. The dataset is divided into have 12 tables of approx. 2 GB each. In order to recreate the entire dataset, I want to use the MERGE function to merge all the 12 tables into one dataset for further analysis. However, when I try to add the table to merge table, it gives an error of type mismatch in columns. Can you please help me how to determine the column type after a ‘csv’ file has been imported using monet.red.csv ? I would like to use the same R interface to MonetDB as I am still learning MonetDB and not conversant with the DB. Thanks & Regards, Jigar Shah Sent from Mail for Windows 10

3 2

am i doing something stupid, or should i file a bug report? this seems absurd :) thanks
by Anthony Damico 18 Dec '15

18 Dec '15

sql>create table my_table as select 1 with data; operation successful (57.729ms) sql>alter table my_table set read only; operation successful (5.488ms) sql>alter table my_table add column my_column integer; operation successful (10.563ms) sql>drop table my_table; operation successful (6.075ms) sql>

5 9

Examples for lag and lead functions in monetdb
by Ashish Kumar Singh 15 Dec '15

15 Dec '15

Did anyone got lead and lag functions working with monetdb? Any test case , examples will be appreciated. Sent from Nine<http://www.9folders.com/>

3 4

very low insertion speed
by poornima＠apakau.com 15 Dec '15

15 Dec '15

Hi, Is there something that I need to configure. I am inserting to a local database to a table with only one covered index. I am using jdbc to write. I am getting only 30-35 insertions/sec even on a new table. This seems very low. Am I missing something ? Regards, Poornima.

6 7

Problem with case when else statement and casting
by John Utke 09 Dec '15

09 Dec '15

Hello, I am having an issue with MonetDB Oct 2014-SP2., though I believe it's also an issue with Jul2015-SP1. The issue happens when using a select case, the items in the when statement are validated even when the statement will never execute. This will cause issues with casting, that otherwise should never have executed. For example, this sample code: select case when false = true then cast('a' as int) else 'b' end Results in the error: Error: conversion of string 'a' to type int failed. SQLState: 22018 ErrorCode: 0 Even though the when statement should never be true and it shouldn't matter if it can't cast 'a' to an int. While the similar query: select case when false = true then 'a' else 'b' end Will always result in 'b', as expected. It is almost as if the query is not short circuiting correctly and checks every statement of the when statement. I was previously on release Jan-2014 SP1 and I did not have this issue. Has anybody else seen something like this? Is there a setting somewhere to turn off this when check? Apologies if this is a common/simple question, I am relatively new to MonetDB. Thank you, John

2 2

Re: users-list Digest, Vol 40, Issue 1
by Luciano Sasso 01 Dec '15

01 Dec '15

Hi, abou driver jdbc 2.18, error in insert data type numeric, any idea ? Att, On 01/12/2015 09:00, users-list-request(a)monetdb.org wrote: > Send users-list mailing list submissions to > users-list(a)monetdb.org > > To subscribe or unsubscribe via the World Wide Web, visit > https://www.monetdb.org/mailman/listinfo/users-list > or, via email, send a message with subject or body 'help' to > users-list-request(a)monetdb.org > > You can reach the person managing the list at > users-list-owner(a)monetdb.org > > When replying, please edit your Subject line so it is more specific > than "Re: Contents of users-list digest..." > > > Today's Topics: > > 1. Re: Double insert (Stefan Manegold) > > > ---------------------------------------------------------------------- > > Message: 1 > Date: Mon, 30 Nov 2015 15:46:53 +0100 (CET) > From: Stefan Manegold <Stefan.Manegold(a)cwi.nl> > To: Communication channel for MonetDB users <users-list(a)monetdb.org> > Subject: Re: Double insert > Message-ID: <652294476.3054123.1448894813168.JavaMail.zimbra(a)cwi.nl> > Content-Type: text/plain; charset=utf-8 > > Dear S?bastien & Mathieu, > > in MonetDB, for numerical types, the NULL value is the smallest value of the type's domain; > see also > https://www.monetdb.org/Documentation/Manuals/SQLreference/BuiltinTypes > > Best, > Stefan > > ----- On Nov 30, 2015, at 3:13 PM, S?bastien RAILLARD (PASSMAN) sebastien.raillard(a)passman.fr wrote: > >> Dear Mark, >> >> We are doing some integrity checks over the data loaded in MonetDB, we would >> like to know how the values can be correctly loaded. >> We have tested many cases (like min/max) for the different types, that why we >> have found this behavior. >> In practice, we will not need to insert the -1.7976931348623157e308 value! >> But it should be great if the documentation can stat which values are reserved >> for NULL storage. >> >> Best regards, >> Sebastien >> >> >> From: users-list >> [mailto:users-list-bounces+sebastien.raillard=passman.fr@monetdb.org] On Behalf >> Of Mark Raasveldt >> Sent: vendredi 27 novembre 2015 17:02 >> To: Communication channel for MonetDB users >> Subject: Re: Double insert >> >> Hey Mathieu, >> This is not actually a bug, but a feature :) >> MonetDB encodes null values of numeric columns by a specific value. This is >> typically the lowest possible value in that column, or close to the lowest >> possible value for that column. For doubles, the null value is encoded as >> -1.7976931348623157e308, thus if you insert this value into the database it >> will be interpreted as null. >> >> Is there any specific reason you need this value in the database? >> Regards, >> Mark >> >> On Fri, Nov 27, 2015 at 4:37 PM, Mathieu Raillard <mraillard(a)data-mat.fr> wrote: >> Hi all, >> >> We found a strange case this week when testing some insert. >> >> We tried to insert -1.7976931348623157e308 in a double value and we get a null >> in the column. >> >> Data to reproduce the case : >> >> create table test.testdouble >> ( >> id int, >> testdouble double, >> primary key (id) >> ) >> >> insert into test.testdouble (id,testdouble) values (1, -1.7976931348623157e308) >> >> select * from test.testdouble >> >> Regards >> >> Mathieu >> >> _______________________________________________ >> users-list mailing list >> users-list(a)monetdb.org >> https://www.monetdb.org/mailman/listinfo/users-list >> >> >> >> -- >> >> >> Parc d'activit? Tolsto? >> 4 rue Edouard Aynard >> 69100 Villeurbanne >> T?l. +33 (0)4 78 95 05 80 >> Fax +33 (0)4 78 95 00 17 >> >> www.passman.fr >> www.passman-hotels.com >> www.passman-camping.com >> www.passman-sante.com >> <https://www.facebook.com/PASSMAN-187787814053/> >> _______________________________________________ >> users-list mailing list >> users-list(a)monetdb.org >> https://www.monetdb.org/mailman/listinfo/users-list -- Luciano Sasso Vieira Data Scientist & Solutions Architect luciano(a)gsgroup.com.br <http://www.gsgroup.com.br> | tel: 17 3353-0833 | cel: 17 99706-9335 www.gsgroup.com.br <http://www.gsgroup.com.br> --- Este email foi escaneado pelo Avast antivírus. https://www.avast.com/antivirus

2 1

Double insert
by Mathieu Raillard 30 Nov '15

30 Nov '15

Hi all, We found a strange case this week when testing some insert. We tried to insert -1.7976931348623157e308 in a double value and we get a null in the column. Data to reproduce the case : create table test.testdouble ( id int, testdouble double, primary key (id) ) insert into test.testdouble (id,testdouble) values (1, -1.7976931348623157e308) select * from test.testdouble Regards Mathieu

4 3

July2015-sp1 fails loading data
by Lynn Carol Johnson 30 Nov '15

30 Nov '15

Hi all - Sorry for the multiple posts. Earlier in the fall I downloaded monetdb Jul2015 version on my MAC and could successfully load a file with 60M rows, 37 columns of mixed int/real/text/boolean data. Later I updated to the Jul2015-SP1 pack and was unable to load this same file. After trying to debug, I removed monetdb, reinstalled Jul2015-SP1, still had the problem. Then un-installed, reinstalled the Jul2015 version and it worked again. So the problem appears to be with the Jul2015-SP1 version. Details: After creating a schema, I load a .sql file that creates this table: sql>\d annosites CREATE TABLE "testjeff"."annosites" ( "chr" INTEGER, "pos" INTEGER, "hapmap31_total_depth" INTEGER, "hapmap31_num_taxa" SMALLINT, "hapmap31_num_alleles" SMALLINT, "hapmap31_minor_allele_avg_depth" REAL, "hapmap31_minor_allele_avg_phred" REAL, "hapmap31_num_hets" SMALLINT, "hapmap31_ed_factor" REAL, "hapmap31_seg_test_p_value" REAL, "hapmap31_ibd_one_allele" BOOLEAN, "hapmap31_in_local_ld" BOOLEAN, "hapmap31_maf" REAL, "hapmap31_near_indel" BOOLEAN, "hapmap31_first_alt_allele_is_ins_or_del" BOOLEAN, "snpeff40e_effect_hapmap31" CHARACTER LARGE OBJECT, "snpeff40e_effectimpact_hapmap31" CHARACTER LARGE OBJECT, "snpeff40e_functionalclass_hapmap31" CHARACTER LARGE OBJECT, "gerp_neutral_tree_length" REAL, "gerp_score" REAL, "gerp_conserved" BOOLEAN, "mnase_low_minus_high_rpm_shoots" REAL, "mnase_bayes_factor_shoots" REAL, "mnase_hotspot_shoots" BOOLEAN, "mnase_low_minus_high_rpm_roots" REAL, "mnase_bayes_factor_roots" REAL, "mnase_hotspot_roots" BOOLEAN, "within_gene" BOOLEAN, "within_transcript" BOOLEAN, "within_exon" BOOLEAN, "within_cds" BOOLEAN, "within_cds_from_gff3" BOOLEAN, "within_five_prime_utr" BOOLEAN, "within_three_prime_utr" BOOLEAN, "codon_position" SMALLINT, "go_term_accession" CHARACTER LARGE OBJECT, "go_term_name" CHARACTER LARGE OBJECT ); sql> Then I use a COPY INTO command (sometimes specifying number of records, sometimes not) to load my data file containing 60M lines: (this is the command when it WORKS on the Jul2015 load): sql>COPY INTO annoSites FROM '/Users/lcj34/notes_files/machineLearningDB/annoDB_related/siteAnnoNoHdrsCol35Fixed_20151011.txt' USING DELIMITERS '\t','\n'; 60362853 affected rows (3m 5s) sql> When running with the Jul2015-SP1 installed on the MAC (OS 10.9.5) . the COPY INTO command dies (i.e., I get back an sql prompt with no message). The merovingian.log from the MAC shows this: 2015-11-23 13:08:21 MSG test1[1531]: # MonetDB/SQL module loaded 2015-11-23 13:08:21 MSG merovingian[1528]: proxying client 0.0.0.0:0 for database 'test1' to mapi:monetdb:///users/lcj34/development/mydbfarm/test1/.mapi.sock?database=test1 2015-11-23 13:08:21 MSG merovingian[1528]: target connection is on local UNIX domain socket, passing on filedescriptor instead of proxying 2015-11-23 13:08:21 MSG merovingian[1528]: proxying client 0.0.0.0:0 for database 'test1' to mapi:monetdb:///users/lcj34/development/mydbfarm/test1/.mapi.sock?database=test1 2015-11-23 13:08:21 MSG merovingian[1528]: target connection is on local UNIX domain socket, passing on filedescriptor instead of proxying 2015-11-23 13:13:01 ERR test1[1531]: mserver5(1531,0x38967f000) malloc: *** error for object 0x7fc17393b208: incorrect checksum for freed object - object was probably modified after being freed. 2015-11-23 13:13:01 ERR test1[1531]: *** set a breakpoint in malloc_error_break to debug 2015-11-23 13:13:03 MSG merovingian[1528]: database 'test1' (1531) was killed by signal SIGABRT My MAC is just a test bed, our real server is RedHat Release which I am trying to get setup with monetdb. On this machine I loaded the Jul2015-sp1 for REdHat/CentOS following these instructions: http://rogerhosto.com/installing-monetdb-on-centosredhat/ The errors are a bit different. On Redhat, the command also aborts, sometimes with no message, sometimes with the message below. I show running both with and without specifying the number of records as the error message is slightly different. (Note on the MAC I did not receive a command line message): sql>COPY 61000000 records INTO annosites FROM '/home/lcj34/monetdbFiles/sites10000Jeff.txt' USING DELIMITERS '\t','\n'; Failed to import table Leftover data 'component of nuclear inner membrane;molecular_function;biological_process;endoplasmic reticulum' sql> sql>COPY INTO annosites FROM '/home/lcj34/monetdbFiles/sites10000Jeff.txt' USING DELIMITERS '\t','\n'; Failed to import table Leftover data 'binding' sql> There are multiple days/attempts (yesterday and today). The last attempt to COPY the file has these messages: 2015-11-24 12:39:45 MSG merovingian[15627]: proxying client (local) for database 'jeffTest' to mapi:monetdb:///opt/dbfarm/jeffTest/.mapi.sock?database=jeffTest 2015-11-24 12:39:45 MSG merovingian[15627]: target connection is on local UNIX domain socket, passing on filedescriptor instead of proxying 2015-11-24 12:43:38 ERR jeffTest[15693]: *** Error in `/usr/bin/mserver5': free(): invalid next size (normal): 0x00007f5b44004ac0 *** 2015-11-24 12:43:38 ERR jeffTest[15693]: ======= Backtrace: ========= 2015-11-24 12:43:38 ERR jeffTest[15693]: /lib64/libc.so.6(+0x7d1fd)[0x7f5b628101fd] 2015-11-24 12:43:38 ERR jeffTest[15693]: /lib64/libbat.so.12(GDKfree+0x13)[0x7f5b64f6f093]2015-11-24 12:43:38 ERR jeffTest[15693]: /lib64/libmonetdb5.so.19(+0x135e11)[0x7f5b655e1e11] 2015-11-24 12:43:38 ERR jeffTest[15693]: /lib64/libmonetdb5.so.19(+0x136534)[0x7f5b655e2534]2015-11-24 12:43:38 ERR jeffTest[15693]: /lib64/libpthread.so.0(+0x7df5)[0x7f5b62b5bdf5] 2015-11-24 12:43:38 ERR jeffTest[15693]: /lib64/libc.so.6(clone+0x6d)[0x7f5b628891ad]2015-11-24 12:43:38 ERR jeffTest[15693]: ======= Memory map: ======== 2015-11-24 12:43:38 ERR jeffTest[15693]: 00400000-00405000 r-xp 00000000 fd:01 1076941767 /usr/bin/mserver5 2015-11-24 12:43:38 ERR jeffTest[15693]: 00604000-00605000 r--p 00004000 fd:01 1076941767 /usr/bin/mserver5 2015-11-24 12:43:38 ERR jeffTest[15693]: 00605000-00606000 rw-p 00005000 fd:01 1076941767 /usr/bin/mserver5 2015-11-24 12:43:38 ERR jeffTest[15693]: 00606000-00608000 rw-p 00000000 00:00 02015-11-24 12:43:38 ERR jeffTest[15693]: 012af000-02860000 rw-p 00000000 00:00 0 [heap] I can forward a log file if requested. I'm thinking perhaps there is some problem in the file with a floating point/real number? Though importing this file works with the Jul2015 release. Were there changes related to this in the service pack? Final question: We need to be testing on the RedHat machine. Is the Jul2015 EPEL for Redhat/CentOS still available? Thanks - Lynn

2 2

syntax error create table union all
by Charles DiMaggio 28 Nov '15

28 Nov '15

Hello. I am trying to create a new table as the union of two existing tables via an R DBI connection, but am receiving an error message that I cannot seem to fix. Any advice to help correct the syntax would be greatly appreciated. > dbGetQuery(mdb, + "CREATE TABLE nis_0411 AS + Select * FROM nis_0407 + UNION ALL + Select * FROM is_0811") Error in .local(conn, statement, ...) : Unable to execute statement 'CREATE TABLE nis_0411 AS Select * FROM nis_0407 UNION ALL Select * FROM nis_0811'. Server says 'syntax error, unexpected SCOLON, expecting WITH in: "create table nis_0411 as select * from nis_0407 union all select * from nis_" ' [#42000]. Cheers Charles Charles DiMaggio, PhD, MPH Director of Injury Research Department of Surgery New York University School of Medicine 462 First Avenue, NBV 15 New York, NY 10016-9196 Charles.Dimaggio(a)nyumc.org Direct Line: 516.308.6426 (preferred) Office: 212.263.3202

2 2

MonetDB July2015 SP1 issues
by Brian Hood 27 Nov '15

27 Nov '15

Hi Guys, I have been noticing quite a few issues on the mailing list regarding existing features of monetdb. I think myself testing the new Remote / Merge technology of which bugs are to be expected to be happening at the moment. This seems unusual with existing features and reminds me of data corruption along while in 11.15 I think it was. Is there anything we can do as the user base / community to help document as well as compile with debug and run such cases under gdb ? Thoughts everyone who believes in the amazing MonetDB. Regards, Brian Hood

2 1