- users-list - monetdb.org

sql query failure question
by Lynn Carol Johnson 09 Feb '16

09 Feb '16

Hi all - I’m going nuts trying to determine why a monetdb SQL query does not work. I am trying to pull data from 2 tables. These tables are in the DB as shown here: sql>\d TABLE maizefullgenome.fullgenomegff TABLE maizefullgenome.fullgenomestable TABLE maizefullgenome.gene_from_gff TABLE maizefullgenome.ranges_from_gff sql> If I join only on the “pos” field, the query executes. Here is that query: > delChrPosGene_fullGenome <-dbGetQuery(conn, "SELECT fg.chr,fg.pos,gt.gene_name FROM maizeFullGenome.fullgenomestable fg join maizeFullGenome.gene_from_gff gt on (fg.pos >= gt.start_pos AND fg.pos <= gt.end_pos) where gerp_score> 2 AND gerp_neutral_tree_length > 2 AND ((sift_score_a < 0.05 AND sift_score_a >= -1) OR (sift_score_c < 0.05 AND sift_score_c >=-1) OR (sift_score_g < 0.05 AND sift_score_g >=-1) OR (sift_score_t < 0.05 AND sift_score_t >=-1))") If I add the “chr” field, it fails with the message below. Can anyone tell me what is wrong with this second query? > delChrPosGene_fullGenome <-dbGetQuery(conn, "SELECT fg.chr,fg.pos,gt.gene_name FROM maizeFullGenome.fullgenomestable fg join maizeFullGenome.gene_from_gff gt on (fg.chr = gt.chr AND fg.pos >= gt.start_pos AND fg.pos <= gt.end_pos) where gerp_score> 2 AND gerp_neutral_tree_length > 2 AND ((sift_score_a < 0.05 AND sift_score_a >= -1) OR (sift_score_c < 0.05 AND sift_score_c >=-1) OR (sift_score_g < 0.05 AND sift_score_g >=-1) OR (sift_score_t < 0.05 AND sift_score_t >=-1))") Error in .local(conn, statement, ...) : Unable to execute statement 'SELECT fg.chr,fg.pos,gt.gene_name FROM maizeFullGenome.fullgenomestable fg join maizeFullGenome.gene...'. Server says '!Object not found'. Here are the fields in the gene_from_gff table: sql>\d maizefullgenome.gene_from_gff CREATE TABLE "maizefullgenome"."gene_from_gff" ( "chr" INTEGER, "start_pos" INTEGER, "end_pos" INTEGER, "gene_name" CHARACTER LARGE OBJECT ); sql> Here are the fields in the fullgenomestable table: sql>\d maizefullgenome.fullgenomestable CREATE TABLE "maizefullgenome"."fullgenomestable" ( "chr" INTEGER, "pos" INTEGER, "ref_allele" TINYINT, "two_bp_upstream" INTEGER, "two_bp_downstream" INTEGER, "three_bp_upstream" INTEGER, "three_bp_center" INTEGER, "three_bp_downstream" INTEGER, "four_bp_upstream" INTEGER, "four_bp_downstream" INTEGER, "five_bp_upstream" INTEGER, "five_bp_center" INTEGER, "five_bp_downstream" INTEGER, "gc_content" TINYINT, "gerp_neutral_tree_length" REAL, "gerp_score" REAL, "gerp_conserved" BOOLEAN, "cmethylation" REAL, "cpg_methylation_1bk_moving_average" REAL, "chg_methylation_1bk_moving_average" REAL, "chh_methylation_1bk_moving_average" REAL, "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, "allele_total_depth" INTEGER, "site_presence" INTEGER, "conservedgene_presence" BOOLEAN, "minor_allele" INTEGER, "maf" REAL, "combined_site_uniqueness_score" REAL, "site_uniqueness_score_sd" REAL, "expected_invariant_site" BOOLEAN, "codon_position" TINYINT, "within_cds" BOOLEAN, "within_gene" BOOLEAN, "within_transcript" BOOLEAN, "within_exon" BOOLEAN, "within_five_prime_utr" BOOLEAN, "within_three_prime_utr" BOOLEAN, "within_mirna" BOOLEAN, "line_family" INTEGER, "sine_family" INTEGER, "ltr_family" INTEGER, "helitron" BOOLEAN, "carot_common_support_interval" TINYINT, "toco_common_support_interval" TINYINT, "orig_amino_acid_a" TINYINT, "new_amino_acid_a" TINYINT, "amino_acid_substitute_pos_a" INTEGER, "sift_score_a" REAL, "sift_median_seq_info_a" REAL, "number_seqs_at_pos_a" INTEGER, "orig_amino_acid_c" TINYINT, "new_amino_acid_c" TINYINT, "amino_acid_substitute_pos_c" INTEGER, "sift_score_c" REAL, "sift_median_seq_info_c" REAL, "number_seqs_at_pos_c" INTEGER, "orig_amino_acid_g" TINYINT, "new_amino_acid_g" TINYINT, "amino_acid_substitute_pos_g" INTEGER, "sift_score_g" REAL, "sift_median_seq_info_g" REAL, "number_seqs_at_pos_g" INTEGER, "orig_amino_acid_t" TINYINT, "new_amino_acid_t" TINYINT, "amino_acid_substitute_pos_t" INTEGER, "sift_score_t" REAL, "sift_median_seq_info_t" REAL, "number_seqs_at_pos_t" INTEGER, "b73_uniqueness_score" REAL, "cml247_uniqueness_score" REAL, "ph207_uniqueness_score" REAL, "w22_uniqueness_score" REAL, "coelorachis_anc" TINYINT, "vossia_anc" TINYINT, "sorghum_anc" TINYINT, "oryza_anc" TINYINT, "setaria_anc" TINYINT, "brachypodium_anc" TINYINT, "hordeum_anc" TINYINT, "musa_anc" TINYINT, "populus_anc" TINYINT, "vitis_anc" TINYINT, "arabidopsis_anc" TINYINT, "panicum_anc" TINYINT ); sql> Any hints are greatly appreciated! Thanks - Lynn

3 6

WAL read files
by Pierre-Adrien Coustillas 08 Feb '16

08 Feb '16

Hi, is it possible to read WAL files ? sql/ log.<transaction id> why ? We still have a problem of duplicate table . All our monetsdb are updated with the latest version of the repository debian8 , and that several server. We have learned to remove duplicate tables and a stable production. But I want to understand this bug Duplication of tables are always created after a restart monetdb After reading this page https://www.monetdb.org/blog/monetdb-sql-transaction-management-scheme, and particularly this ""By default, persisted transactions are cleaned up from the WAL. During database startup, all transaction still left in the WAL are read and any that are not yet applied are written into the persistent BAT storage"" . I think that the drop or create commands are not cleaned up from the WAL We used the library php _ map.inci and we run a lot of command drop / create ( ... I know this is not recommended ... ) We used autocommit. If I can read and understand what is in the WAL file, can help me understand this bug Thank 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 6

Multi-core join performance
by Koliousis, Alexandros 01 Feb '16

01 Feb '16

Hi, Thank you for your reply. The system has 16 physical cores (2 sockets, 8 cores/sockets, 20MB L3 cache, 256KB L2) and 64GB of RAM. The MonetDB server uses 16 threads to serve my test database and it finds ~63GB of available memory. I used synthetic data to populate the two tables (with names A and B). The particular query I reported about in my previous e-mail generates a lot of output: approx. 500 million tuples. The join predicate is (A.a1 < B.b1) where columns A.a1 and B.b1 contain all unique integers from 1 to 32768. With a lower selectivity (say, 1%, by controlling the values inserted into A.a1 and B.b1), the query executes faster but its behaviour is the same: only 1 thread is utilised. Attached is a trace of the query. Hope this helps, Alexandros PS: Apologies for the separate thread; somehow I ended up subscribing in digest mode > Hi > > This is insufficient information to shed light on it. > Crucial information is system characteristics. > And also a TRACE of your query can shed light on the issue. > > Furthermore, is it a cold or hot execution of the query? > > regards, Martin > > On 20/01/16 13:24, Alexandros Koliousis wrote: > > Hi, > > > > I am joining two tables, each with 32K tuples. The query runs for > ~23-24 seconds on my (multi-core) machine. > > > > During execution, however, only 1 core is utilised, while the rest > of the CPU cores are idle. The cores are under-utilised although I > have configured the MonetDB server to use all the physical cores of my > machine (either by setting 'nthreads' or, in the > > case of mserver5, 'gdk_nr_threads'). > > > > I am running MonetDB server v11.21.13 "Jul2015-SP2", compiled from > source. > > > > Is this due to some configuration setting I missed? Or, due to the > fact that I am running a single-operator query? > > > > Alternatively, I was thinking of creating merge tables, thus > partitioning my tables manually, hoping that this will allow for > intra-operator parallelism. > > > > Thanks, > > > > Alexandros > > > > > > > > _______________________________________________ > > users-list mailing list > > users-list at monetdb.org > > https://www.monetdb.org/mailman/listinfo/users-list > >

3 2

Stored procedure
by poornima＠apakau.com 30 Jan '16

30 Jan '16

Hi, Can you help me with creating a stored procedure in monet ? I keep getting unexpected END even for a simple procedure as follows. create procedure foobar() begin end; I have even tried with the delimiter like in mysql. Nothing works. Regards, Poornima.

2 1

MonetDB test client application not linking
by Ivan Zuboff 30 Jan '16

30 Jan '16

I tried to compile test application ( https://www.monetdb.org/Documentation/Manuals/SQLreference/Programming/MAPI) for MonetDB under Ubuntu 14.04 LTS Trusty Tahr. I followed the download instructions from official site (https://www.monetdb.org/downloads/deb) installation was successful, then I installed a bunch of other packages in order to copmpile it. Now when I try to compile and link test application I get the following errors: libtool: compile: gcc -c -I/usr/include/monetdb test.c -fPIC -DPIC -o .libs/test.o libtool: compile: gcc -c -I/usr/include/monetdb test.c -o test.o >/dev/null 2>&1 libtool: link: gcc -o test test.o -lmapi -lstream -lssl -lcrypto -lcurl -lz test.o: In function `die': test.c:(.text+0x1c): undefined reference to `mapi_explain_query' test.c:(.text+0x27): undefined reference to `mapi_result_error' test.c:(.text+0x3f): undefined reference to `mapi_explain_result' test.c:(.text+0x4a): undefined reference to `mapi_next_result' test.c:(.text+0x5a): undefined reference to `mapi_close_handle' test.c:(.text+0x65): undefined reference to `mapi_destroy' test.c:(.text+0x81): undefined reference to `mapi_explain' test.c:(.text+0x8c): undefined reference to `mapi_destroy' test.o: In function `query': test.c:(.text+0xde): undefined reference to `mapi_query' test.c:(.text+0xf2): undefined reference to `mapi_error' test.o: In function `update': test.c:(.text+0x133): undefined reference to `mapi_close_handle' test.o: In function `main': test.c:(.text+0x190): undefined reference to `mapi_connect' test.c:(.text+0x1a0): undefined reference to `mapi_error' test.c:(.text+0x222): undefined reference to `mapi_fetch_field' test.c:(.text+0x23a): undefined reference to `mapi_fetch_field' test.c:(.text+0x266): undefined reference to `mapi_fetch_row' test.c:(.text+0x276): undefined reference to `mapi_close_handle' test.c:(.text+0x282): undefined reference to `mapi_destroy' collect2: error: ld returned 1 exit status ./t.sh: 11: ./t.sh: ./test: not found What I'm doing wrong? What should I additionally install or change in compilation options for successful linking of test example? Best regards and thanks in advance, Ivan

1 0

A test email (please ignore)
by Ying Zhang 29 Jan '16

29 Jan '16

Dear all, This is just a test e-mail to see if I can still send/receive e-mails to/from this mailing list, since the last e-mail I received here was on Jan. 22nd, 2016. Is it just because MonetDB has been issues free the past days, or is something wrong with the email server? Sorry for bothering you. Regards, Jennie

3 2

Issue with Perl monetdb DBD plugin for DBI on a VM
by Puthick Hok 22 Jan '16

22 Jan '16

Hi, I'm using MonetDB-11.19.7 compiled from source on openSUSE 12.1. I'm also using the Perl client code from MonetDB source to connect to MonetDB from Perl via Perl DBI module. It works fine for a physical server but a VM server with a vmdk drive. It has an issue that the database handler must be disconnected and reconnected in order to execute more SQL statements; otherwise there will be an error saying 'Statement handle not marked as Active'. So I have written 2 testing Perl scripts doing SQL select for 100 iterations. One script is to demonstrate that it will fail while the other will work for all the iterations. The one that works is the script that connect and disconnect to MonetDB in every iteration before executing the SQL. In order to help you replicate the problem, there is an attached tar.gz file containing the 2 perl scripts - one for success and the other one for failure. Due to the size limitation, I cannot include the database dump file for the demonstration. Regards, Puthick

2 1

Query timeout
by Luciano Sasso 21 Jan '16

21 Jan '16

Any idea, how to set query timeout ? Other question, the limit of 65 connection is for merogivian process or mserver ? Att, -- 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

1 0

How to subtract two columns from two different datasets?
by integrity 20 Jan '16

20 Jan '16

Dear experts, I have columns coming from two different datasets which also resides in two different tables in the same layout,now i need the difference of that columns. Example: sql>select (select mag from one_to_many o, targets16 t where o.new_targetid=t.id) - (select mag from one_to_many o,targets16 t where o.old_uniqueid=t.id) ; cardinality violation (22>1) p.s. these two tables have both 22 rows. But if I subtract on single values, it works: select (select avg(mag) from mag1) - (select avg(mag) from mag2); +--------------------------+ | sql_sub_L1 | +==========================+ | -0.015908087452235264 | +--------------------------+ 1 tuple (4.486ms) can any one help me out? Thanks In Advance !!!! Meng

2 1

Multi-core join performance
by Alexandros Koliousis 20 Jan '16

20 Jan '16

Hi, I am joining two tables, each with 32K tuples. The query runs for ~23-24 seconds on my (multi-core) machine. During execution, however, only 1 core is utilised, while the rest of the CPU cores are idle. The cores are under-utilised although I have configured the MonetDB server to use all the physical cores of my machine (either by setting 'nthreads' or, in the case of mserver5, 'gdk_nr_threads'). I am running MonetDB server v11.21.13 "Jul2015-SP2", compiled from source. Is this due to some configuration setting I missed? Or, due to the fact that I am running a single-operator query? Alternatively, I was thinking of creating merge tables, thus partitioning my tables manually, hoping that this will allow for intra-operator parallelism. Thanks, Alexandros

2 1

create table uppercase with " "
by Pierre-Adrien Coustillas 20 Jan '16

20 Jan '16

Hello, bug or feature ? * PETER in uppercase : sql>CREATE TABLE "PETER" ( nom varchar(20) ); operation successful (2.545ms) sql>DROP TABLE PETER; DROP TABLE: no such table 'peter' sql>DROP TABLE "PETER"; operation successful (1.947ms) * peter in lowercase : sql>CREATE TABLE "peter" ( nom varchar(20) ); operation successful (2.917ms) sql>DROP TABLE peter; operation successful (1.882ms) * peter in lowercase and uppercase : sql>CREATE TABLE "peter" ( nom varchar(20) ); operation successful (2.900ms) sql>CREATE TABLE PETER ( nom varchar(20) ); CREATE TABLE: name 'peter' already in use sql>CREATE TABLE "PETER" ( nom varchar(20) ); operation successful (9.339ms) Conclusion : PETER is convert in peter "PETER" is not convert Pierre Coustillas -- 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

2 1

best practice use schema
by Pierre-Adrien Coustillas 20 Jan '16

20 Jan '16

Hello, What is the best practice on the use of schema ? S hould we always create a schema , or is can I use the " sys" schema by default ? Pierre Coustillas -- 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

1 0

creating a binary file for "COPY binary ..."
by Lynn Carol Johnson 19 Jan '16

19 Jan '16

Hi all - I'm creating a large db table with several columns. To speed it up, I'm using monetdb's "COPY BINARY" command. I have a question on creating the binary files. What I've done is use java's DataOutputStream to create the binary file - using code that looks as below (sometimes an int, sometimes a real or byte): writerChr = new DataOutputStream(new BufferedOutputStream(new FileOutputStream(chrFile))); writerChr.writeInt(intChrom); When I read this file using a test program and java's DataInputStream, it correctly shows me the integer values I've written. In the "writerChr" case, all values in this particular column are "10". However, when I load this into monetdb the value is way off - it shows a large number. The command I am using to copy this file and one other into a table with 2 "int" columns is: COPY binary into justchrpos from ('/home/lcj34/chrFileC10.bin','/home/lcj34/posFileC10.bin'); It copies in fine, but when I cheek the values I see this: (not 10!) sql>\d justchrpos CREATE TABLE "testbig"."justchrpos" ( "chr" INTEGER, "pos" INTEGER ); sql>select * from justchrpos limit 10; +-----------+-----------+ | chr | pos | +===========+===========+ | 167772160 | 16777216 | | 167772160 | 33554432 | | 167772160 | 50331648 | | 167772160 | 67108864 | | 167772160 | 83886080 | | 167772160 | 100663296 | | 167772160 | 117440512 | | 167772160 | 134217728 | | 167772160 | 150994944 | | 167772160 | 167772160 | +-----------+-----------+ 10 tuples (3.601ms) sql> QUESTION: Is using java's DataOutputStream a valid way to create the binary files for monetdb? If not, what should I be using? As I mentioned above, when I read back the created file using java's DataInputStream, I see the values "10" as I expect. Thanks - Lynn

3 3

Monet way of handling string columns
by bhuvan Mitra 18 Jan '16

18 Jan '16

Hello, I have recently started looking into Monetdb and want to contribute to it. My complete focus is on string columns as numeric data can already be processed with maximum speed. I know it uses BATs to store every single column, but I read some where that it uses dictionary for string columns. How is it actually using dictionary? And are there any other special handling techniques for strings? When a query like 'contains' comes, how will monet handle that? I found there is no inverted index in monet to deal queries like this. (Please tell a rough model of how query execution is done incase of string columns) Hope this forum gives me some insight of what's happening during string column processing. Thanks in advance, -Mitra

1 0

MonetDB-11.21.13 (Jul2015-SP2) can't start in CentOS 6.3
by chenkaijiang＠bigknow.com.cn 18 Jan '16

18 Jan '16

I downloaded the source codes at https://www.monetdb.org/downloads/sources/Jul2015-SP2/MonetDB-11.21… ./configure --prefix=/usr/local/MonetDBmakemake install No error is reported. But can't start:monetdbd create ./ycmonetdbd: error while loading shared libraries: libstream.so.7: cannot open shared object file: No such file or directory It looks like the libstream.so is lib on debian linux.  How can I fix it on CentOS 6.3?

2 1

How to set query timeout in MonetDB Server
by Luciano Sasso 16 Jan '16

16 Jan '16

How to set query timeout in MonetDB Server? -- 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

1 0

Announcement: New Jul2015-SP2 Bugfix release of MonetDB suite
by Sjoerd Mullender 16 Jan '16

16 Jan '16

The MonetDB team at CWI/MonetDB BV is pleased to announce the Jul2015-SP2 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/>. Jul 2015-SP2 bugfix release Bug Fixes * 2014: 'null' from copy into gets wrong * 3817: opt_pushselect stuck with multi-table UDF * 3835: windows does not release ram after operations * 3836: rand() only gets evaluated once when used as an expression * 3838: Update column with or without parenthesis produce different results * 3840: savepoints may crash the database * 3841: mclient fails with response "Challenge string is not valid" * 3842: SQL execution fails to finish and reports bogus error messages * 3845: Too many VALUES in INSERT freeze mserver5 * 3847: Wrong SQL results for a certain combination of GROUP BY / ORDER BY / LIMIT * 3848: mserver segfault during bulk loading/updating * 3849: HUGEINT incorrect value * 3850: DEL character not escaped * 3851: expression that should evaluate to FALSE evaluates to TRUE in SELECT query * 3852: CASE statement produces GDK error on multithreaded database: BATproject does not match always * 3854: Complex expression with comparison evaluates incorrectly in WHERE clause * 3855: Foreign key referencing table in a different schema - not allowed. * 3857: Large LIMIT in SELECT may abort the query * 3861: Using window functions cause a crash * 3864: Error in bulk import for chinese character * 3871: NOT x LIKE triggers "too many nested operators" * 3872: mserver crashes under specific combination of JOIN and WHERE conditions * 3873: mserver5: gdk_bat.c:1015: setcolprops: Assertion `x != ((void *)0) || col->type == 0' failed. * 3879: Database crashes when querying with several UNION ALLs. * 3887: Querying "sys"."tracelog" causes assertion violation and crash of mserver5 process * 3889: read only does not protect empty tables * 3895: read only does not protect this table

2 1

error opening DB on External drive
by Lynn Carol Johnson 15 Jan '16

15 Jan '16

Hello - I am running monetdb with the July2015 service pack on MAC OSX. I have no problem running and connecting to a dbfarm that lives on my MAC. But I want to test a very large data set so have created a dbfarm on a Samsung T1 SSD external drive that is USB connected to my MAC. I have successfully started monetdbd server and have attached to my dbfarm. I created/released a new database. Yet when I attempt to run mClient it fails. Here is the "get all" command: rs-btlcj34lt:testMapiSock lcj34$ monetdbd get all /Volumes/Samsung_T1/alt-dbfarm property value hostname rs-btlcj34lt.maize.cornell.edu dbfarm /Volumes/Samsung_T1/alt-dbfarm status monetdbd[740] 1.7 (Jul2015-SP1) is serving this dbfarm mserver /Users/lcj34/development/usr/local/monetdb/bin/mserver5 logfile /Volumes/Samsung_T1/alt-dbfarm/merovingian.log pidfile /Volumes/Samsung_T1/alt-dbfarm/merovingian.pid sockdir /tmp port 50000 exittimeout 60 forward proxy discovery true discoveryttl 600 control no passphrase <unknown> mapisock /tmp/.s.monetdb.50000 controlsock /tmp/.s.merovingian.50000 rs-btlcj34lt:testMapiSock lcj34$ Here is the command line error: rs-btlcj34lt:alt-dbfarm lcj34$ mclient -u monetdb -d maizeFullGenomeDB password: monetdbd: internal error while starting mserver, please refer to the logs And here is the error from the log file. 2016-01-14 10:33:26 MSG maizeFullGenomeDB[749]: # Visit http://www.monetdb.org/ for further information 2016-01-14 10:33:26 MSG maizeFullGenomeDB[749]: !IOException:mal_mapi.listen:operation failed: binding to UNIX socket file /Volumes/Samsung_T1/alt-dbfarm/maizeFullGenomeDB/.mapi.sock failed: No such file or directory If I switch back to a dbfarm local to the MAC, it works fine. Any ideas why I can't connect to a dbfarm on the external drive? Is there something else that needs to be setup for the socket creation to work? Thanks - Lynn

3 3

Error on database startup
by Robin Cijvat 13 Jan '16

13 Jan '16

Hello everyone! Every now and then my laptop fails to wake up from standby mode and I have to do a hard reset, while monetdb is running in the background. I have had the following problem multiple times lately when trying to start the database that was stopped during the hard reset: monetdb start tpch starting database 'tpch'... FAILED start: starting 'tpch' failed: database 'tpch' did not initialise the sql scenario This is from the log: 2016-01-13 07:22:38 MSG tpch[7085]: # Start processing logs sql/sql_logs version 52200 2016-01-13 07:22:38 ERR tpch[7085]: !mvc_init: unable to create system tables 2016-01-13 07:22:38 MSG tpch[7085]: !SQLException:SQLinit:Catalogue initialization failed 2016-01-13 07:22:38 MSG tpch[7085]: !ERROR: Could not read the version number from the file 'sql_logs/sql//log'. 2016-01-13 07:22:38 MSG merovingian[7066]: database 'tpch' (7085) has exited with exit status 0 2016-01-13 07:22:38 MSG merovingian[7066]: database 'tpch' has shut down 2016-01-13 07:22:38 ERR control[7066]: (local): failed to fork mserver: database 'tpch' did not initialise the sql scenario Now I don't keep any important information in this database, so for me it is fine to reload everything. However, since this is a recurring problem, I wondered if anyone has any suggestions as to how to fix such a 'broken' database.

1 0

New syntax filter functions
by Roberto Cornacchia 08 Jan '16

08 Jan '16

Hi there, I'm a bit struggling with the syntax for filter function, after it changed from Oct2014 to Jul2015. I have looked at the release notes, but they don't seem to even mention this change of syntax. This used to work in Oct2014: create filter function maxlevenshtein(s1 string, s2 string, k int) external name str."distjoin_levenshtein"; select t.name, f.name from sys.tables t, sys.functions f where t.name maxlevenshtein(2) f.name; In Jul2015, if I look at Niels' comment in https://www.monetdb.org/bugzilla/show_bug.cgi?id=3702 , it should be something similar to select t.name, f.name from sys.tables t, sys.functions f where [t.name] maxlevenshtein [f.name, 2]; But this is still not right. How should it be? And should I change the SQL / MAL / C interface of the function as well? Thanks,

1 2

Monitoring MonetDB performance
by Alexander Gordt 07 Jan '16

07 Jan '16

Hello together, I’ve got a performance issue with my project, where the DB performance is going down for what ever reason. The activity monitor of OSX shows me the following numbers about the mserver5-process: The CPU usage is very low (15-25% of one core!), the IO traffic on my ssd is fair (about 10-40 MB/s), but the speed slows down. I’ve had a look in the merovingian.log, but there’s only the connection shown. For the first 5000 messages, the CPU usage of mserver5 was about 70-110% - why is it going down?… How could I analyze, why the database performance is going down? Is it possible, that there is an issue with the JDBC-driver, that the performance is decreasing? I attache a log file where you can see that the performance is quite good for some time, but then goes down for whatever reason. 18:40:46 [main] INFO ModelPersistenceTest - Finished BatchInsert of 200 message in 15.711 seconds - totally inserted 5200 messages 18:40:46 [main] INFO ModelPersistenceTest - Starting Batch-Insert for of 200 message 18:41:03 [main] INFO ModelPersistenceTest - Finished BatchInsert of 200 message in 17.189 seconds - totally inserted 5400 messages 18:41:03 [main] INFO ModelPersistenceTest - Starting Batch-Insert for of 200 message 18:41:27 [main] INFO ModelPersistenceTest - Finished BatchInsert of 200 message in 23.834 seconds - totally inserted 5600 messages 18:41:27 [main] INFO ModelPersistenceTest - Starting Batch-Insert for of 200 message 18:42:19 [main] INFO ModelPersistenceTest - Finished BatchInsert of 200 message in 52.13 seconds - totally inserted 5800 messages 18:42:19 [main] INFO ModelPersistenceTest - Starting Batch-Insert for of 200 message 18:43:09 [main] INFO ModelPersistenceTest - Finished BatchInsert of 200 message in 49.892 seconds - totally inserted 6000 messages 18:43:09 [main] INFO ModelPersistenceTest - Starting Batch-Insert for of 200 message 18:43:59 [main] INFO ModelPersistenceTest - Finished BatchInsert of 200 message in 49.786 seconds - totally inserted 6200 messages 18:43:59 [main] INFO ModelPersistenceTest - Starting Batch-Insert for of 200 message 18:45:31 [main] INFO ModelPersistenceTest - Finished BatchInsert of 200 message in 92.389 seconds - totally inserted 6400 messages 18:45:31 [main] INFO ModelPersistenceTest - Starting Batch-Insert for of 200 message 18:47:26 [main] INFO ModelPersistenceTest - Finished BatchInsert of 200 message in 114.412 seconds - totally inserted 6600 messages 18:47:26 [main] INFO ModelPersistenceTest - Starting Batch-Insert for of 200 message 18:49:24 [main] INFO ModelPersistenceTest - Finished BatchInsert of 200 message in 118.324 seconds - totally inserted 6800 messages One message results in 10-50 insert statements in different tables and the 200 messages are processed in one transaction. The absolute performance isn’t nice, but that is another topic – I’ve read about "copy into“ and that kind of stuff – but that gonna be part in a later optimization. I tried to find out, if there is a long running process, but „select * from sys.queue;“ didn’t show me anything of interest – the result shows only this select. Is there a way to gather some statistics about executed queries and their runtime? Or about blocking resources… Thanks in advance for bringing any light in the black box :) Best regards, Alex

1 1

About JDBC Driver
by Luciano Sasso 07 Jan '16

07 Jan '16

Steps for error: 1- In testing with java client and JDBC 2:19, an error occurs when saving decimal field, for example the value 11:23 is recorded as 13. 2- When I run the same test with the 2.8 driver, saves normally and correct. Not only is the 2.19 driver, all above 2.8 is that same mistake. My Java client is Pentaho Data Integration, My MonetDB is JUL2015-SPI I go zip package test, and send fo tests. Att, -- 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

About database crack
by chenkaijiang＠bigknow.com.cn 06 Jan '16

06 Jan '16

Hi, all, I find that in Oct2010 branch (http://dev.monetdb.org/hg/MonetDB/shortlog/Oct2010) it looks like that the database cracking technology has been implemented. But looks like that it wasn't merged into the latest release (Jul2015), right? Why not merge this into the latest release? Is there any plan to merge it in the future?

3 3

MonetDBLite error in R
by Iain Wallace 06 Jan '16

06 Jan '16

hi all, I was wondering if anybody could suggest how to debug this error: Server says 'GDKerror:!ERROR: BATsetaccess: tmp_3505 has -1 views I am trying to do a simple join using dplyr sample%>%left_join(interaction_pairs) The tables appear to be working correctly as I can collect them and use them in R properly interaction_pairs<-collect(interaction_pairs) Thanks Iain

2 1

invalid credentials using mserver5
by Xu,Wenjian 06 Jan '16

06 Jan '16

Hi all, Normally I am using *monetdbd*+"mclient" combination to use MonetDB. Now, since I want to see the debug info in each gdk operator, I have to use *mserver5*. I do as follows (JULY2015-SP1+ubuntu14.04): Step1: $ mserver5 --dbpath=${HOME}/[my_dbpath] --daemon=yes --algorithms Step2: In another console: $ mclient -d [dbname] -u monetdb password: monetdb It replies: InvalidCredentialsException:checkCredentials:invalid credentials for user 'monetdb' However, user:monetdb, pwd:monetdb is the default one and I can use it successfully in *monetdbd*+"mclient" combination. Any idea? Thanks, XU Wenjian

2 1

Jdbc Driver
by Luciano Sasso 05 Jan '16

05 Jan '16

Hi, Its error "https://www.monetdb.org/bugzilla/show_bug.cgi?id=3561", persist in version 2.19, exists most new version with bug fix ? -- 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

Dropping foreign key constraint
by Alexander Gordt 05 Jan '16

05 Jan '16

Hello together, I’m very new to MonetDB, so please be gracious :) I’m trying to drop a foreign key, what should be a very simple task – but I didn’t manage to do it. The following statement ran successfully – as the result says – but the foreign key is still there: alter table visit drop constraint visit_configuration_id_fkey; I also tried this – but this command is not supported?? alter table visit drop foreign key visit_configuration_id_fkey; An error occurred when executing the SQL command: alter table visit drop foreign key visit_configuration_id_fkey syntax error, unexpected FOREIGN in: "alter table visit drop foreign" [SQL State=42000] Execution time: 0.01s 1 statement failed. Best regards and thanks for any hints, Alex

2 4

could == be aliased to the = operator? or will that break things?
by Anthony Damico 04 Jan '16

04 Jan '16

hello, just an idea-- if monetdb gets a flood of new R users over the next year, they might get thrown off by things breaking when they use a double-equals sign (example below).. sqlite accepts the double-equals as a single equals. thanks > dbGetQuery(db,'select count(distinct mycolumn) from mytable where yr=2003') L1 1 334343 > dbGetQuery(db,'select count(distinct mycolumn) from mytable where yr==2003') Error in .local(conn, statement, ...) : Unable to execute statement 'select count(distinct mycolumn) from mytable where yr==2003'. Server says 'syntax error, unexpected '=' in: "select count(distinct mycolumn) from mytable where yr=="' [#42000]. >

1 0

Re: Update: Extract Table Statistics after Data import using Monet.Read.Csv
by Anthony Damico 31 Dec '15

31 Dec '15

https://www.monetdb.org/bugzilla/show_bug.cgi?id=3835 next version release should fix this.. or use monetdblite which already has the fix (the example scripts i sent use monetdblite) On Dec 30, 2015 11:53 PM, "Jigar Shah" <jigar.shah1984(a)hotmail.com> wrote: > Thanks a lot Dr. Hannes, Anthony. The trick worked perfectly. See the > stats below: > > > > > > However, there is one odd behavior I have noticed. Even after completion > of the query, the RAM usage is still the same as when running the query. > The RAM usage only reduces once I terminate the connection and stop the > server. > > Is this behavior normal for the MonetDB server ? Also, ‘Src_MonetDB’ > creates a separate connection than ‘dbConnect’ > > Can the two connection run at the same time ? I noticed that sometimes > when I use the Src_MonetDB connection, the dbConnect gets terminated > automatically. > > Thanks a lot for your help on these. > > Best Regards, > > Jigar > > Sent from Mail <http://go.microsoft.com/fwlink/?LinkId=550986> for > Windows 10 > > > > > *From: *Anthony Damico <ajdamico(a)gmail.com> > *Sent: *Tuesday, December 29, 2015 6:22 AM > *To: *Communication channel for MonetDB users <users-list(a)monetdb.org> > *Subject: *Re: Extract Table Statistics after Data import using > Monet.Read.Csv > > > > for an example of this, see > > > https://github.com/ajdamico/asdfree/tree/master/Basic%20Stand%20Alone%20Med… > > On Dec 29, 2015 2:50 AM, "Hannes Mühleisen" <hannes.muehleisen(a)cwi.nl> > wrote: > > Hi Jigar, > > > On 29.12.2015, at 01:21, Jigar Shah <jigar.shah1984(a)hotmail.com> wrote: > > > > 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. > > > You can pass a vector of _all_ your file names to monetdb.read.csv(), this > will load all of them into a new table and does not require the merge. The > different schemas come from the function guessing the column types from the > file. > > Hannes > > _______________________________________________ > users-list mailing list > users-list(a)monetdb.org > https://www.monetdb.org/mailman/listinfo/users-list > > >

1 0

Performing a direct access to MonetDB's BAT tables via MAL
by Giacomo Bergami 31 Dec '15

31 Dec '15

Good Morning/Evening everyone. My aim is to direcly access to the BAT column store representation of MonetDB through the standard libraries (MAPI/JDBC) - if possible. I know that each SQL query gets rewrited into a BAT algebra expression, but in my case I want to access to a single value per time (e.g. I want to access to the value stored in the i-th row and in the column labelled "j" of a given table) and maybe in this case the direct access is quicker than performing the whole SQL query. I've already tried to use the mclient with the MAL language and read the documentation, and I tried to use the client with something like this: mdb.start(); bbp.open(); bbp.prelude(); a := bbp.bind("fx", "size"); where "fx" should be the name of the table and "size" one of its attributes. But unfortunately I do not know how to search for a specific row-key and to get the correspondant value, and AFAIK i have to get the id of the column "size". Thanks in advance, Giacomo Bergami ____________________________ Giacomo Bergami, Ph.D Student University of Bologna https://www.unibo.it/sitoweb/giacomo.bergami2

3 5

Almost all test cases failed in "sql" package
by Xu,Wenjian 31 Dec '15

31 Dec '15

Hi, I have compiled and installed MonetDB-11.21.11 (JUL2015-SP1) from pre-packaged source distribution. My environment is Ubuntu 14.04 LTS. I tried to run the test cases by the following command: $ sudo Mtest.py -r [package-name] I found that almost all tests PASSED when [package-name]=monetdb5, which is expected. But when I switched to [package-name]=sql, almost all tests FAILED. What is the reason? Or I did something wrong? Any idea would be helpful. Thanks. Best, Wenjian

1 0

回复：Is there any MonetDB branch that supports B-tree index?
by chenkaijiang＠bigknow.com.cn 30 Dec '15

30 Dec '15

The lecture can also be found at http://homepages.cwi.nl/~manegold/teaching/adt/lectures/lecture5.pdf ----- 原始邮件 ----- 发件人：<chenkaijiang(a)bigknow.com.cn> 收件人：users-list<users-list(a)monetdb.org> 主题：Is there any MonetDB branch that supports B-tree index? 日期：2015-12-30 23:46:03 Hi, all, from page 2 of the attach lecture5.pdf, it looks like that MonetDB supports B-tree index in some branch? Is there any MonetDB branch that supports B-tree index? (I mean the persistent index that can be used in a query without creating it on the fly) Regards,Kaijiang

2 1

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

MonetDB Remote tables
by Brian Hood 26 Nov '15

26 Nov '15

Hi Guys, I have 1 master and 4 mserver nodes running. I have 4 ippacket and 4 tcppacket schemas identical per table schemas across all nodes. i have 4 remote references on the master to ippacket remote nodes and the same for tcppacket. However when i try and join 2 MERGE tables each containing 4 schemas i get the following. sql>select count(*) from ippacket a, tcppacket b where a.guid = b.guid; (mapi:monetdb://monetdb@172.17.0.5/threatmonitor) GDK reported error. ERROR: BATproject: does not match always Any pointers please ? Regards, Brian Hood

2 5

回复：Re: 回复：Range query in MonetDB
by chenkaijiang＠bigknow.com.cn 26 Nov '15

26 Nov '15

Hi, Martin, great to hear it, thank you very much! Do you have a schedule, when to release this feature? We're looking forward to running MonetDB in the production environment!  ----- 原始邮件 ----- 发件人：Martin Kersten <martin.kersten(a)cwi.nl> 收件人：Communication channel for MonetDB users <users-list(a)monetdb.org> 主题：Re: 回复：Range query in MonetDB 日期：2015-11-26 04:02:19 On 25/11/15 17:08, chenkaijiang(a)bigknow.com.cn wrote: > Sometimes the traditional DB is 10 to 100 times faster with the help of the index. Yes. It is on the roadmap. > > ----- 原始邮件 ----- > 发件人：<chenkaijiang(a)bigknow.com.cn> > 收件人：users-list<users-list(a)monetdb.org>,developers-list<developers-list(a)monetdb.org>, > 主题：Range query in MonetDB > 日期：2015-11-25 23:11:07 > > Since MonetDB actually ignore SQL statement CREATE INDEX, the performance sometimes is quite poor comparing to traditional DB. Such as: https://www.monetdb.org/pipermail/users-list/2008-April/001839.html, > http://stackoverflow.com/questions/22430284/monetdb-slow-query-with-index > > In reality, queries with ranges (such as date ranges) are very common and thus index is critical. Sometimes, it gives 100 times performance boost, especially when the queries are relatively simple. > > We debug into MonetDB source codes and found that CREATE INDEX only creates an entry in the system catalog. > > Is MonetDB going to support index in the coming future, or in some roadmap? > > > > > > > > > _______________________________________________ > users-list mailing list > users-list(a)monetdb.org > https://www.monetdb.org/mailman/listinfo/users-list > _______________________________________________ users-list mailing list users-list(a)monetdb.org https://www.monetdb.org/mailman/listinfo/users-list

1 0

回复：Range query in MonetDB
by chenkaijiang＠bigknow.com.cn 25 Nov '15

25 Nov '15

Sometimes the traditional DB is 10 to 100 times faster with the help of the index. ----- 原始邮件 ----- 发件人：<chenkaijiang(a)bigknow.com.cn> 收件人：users-list<users-list(a)monetdb.org>,developers-list<developers-list(a)monetdb.org>, 主题：Range query in MonetDB 日期：2015-11-25 23:11:07 Since MonetDB actually ignore SQL statement CREATE INDEX, the performance sometimes is quite poor comparing to traditional DB. Such as: https://www.monetdb.org/pipermail/users-list/2008-April/001839.html, http://stackoverflow.com/questions/22430284/monetdb-slow-query-with-index In reality, queries with ranges (such as date ranges) are very common and thus index is critical. Sometimes, it gives 100 times performance boost, especially when the queries are relatively simple. We debug into MonetDB source codes and found that CREATE INDEX only creates an entry in the system catalog. Is MonetDB going to support index in the coming future, or in some roadmap?

2 1

Driver JDBC 2.8 dont work in JUL2015SP1
by Luciano Sasso 25 Nov '15

25 Nov '15

Driver JDBC 2.8 dont work in JUL2015SP1? I use 2.8 for ETL (run work with decimal, 2.18 dont work), and 2.18 for dashboards access. Att, -- 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

1 0

Range query in MonetDB
by chenkaijiang＠bigknow.com.cn 25 Nov '15

25 Nov '15

Since MonetDB actually ignore SQL statement CREATE INDEX, the performance sometimes is quite poor comparing to traditional DB. Such as: https://www.monetdb.org/pipermail/users-list/2008-April/001839.htm… In reality, queries with ranges (such as date ranges) are very common and thus index is critical. Sometimes, it gives 100 times performance boost, especially when the queries are relatively simple. We debug into MonetDB source codes and found that CREATE INDEX only creates an entry in the system catalog.  Is MonetDB going to support index in the coming future, or in some roadmap? 

1 0

Serious error in data recording of type numeric
by Luciano Sasso 25 Nov '15

25 Nov '15

Serious error in data recording of type numeric. Using the jdbc driver 2.8, recording both base OUT2014SP4 when JUL2015SP1, records normally. But when using any jdbc driver above 2.8, the recording is wrong, for example, the value 845 is recorded as 850. Is it a bug? Att, -- 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

1 0

MonetDB with dynamic columns
by Niklas Bivald 25 Nov '15

25 Nov '15

Hi, I've had my eyes on MonetDB for quite some time and the Numpy implementation really pushed me over the edge (as in this is the right solution for us). A big shout out to Mark Raasveldt for both the function and support and bug-fixes along the way. We have a fairly small dataset which probably won't exceed 10 million rows, 500 columns. We do flight data analysis so each row can be though of as a flight, and all the columns are data-points connected to it. We then use Numpy to prepare the data for graphs and limit our selection using standard SQL. We generally SELECT between 1-5 columns and use the WHERE statement on *another* 1-5 columns to select our rows. However, as we develop more analyses we connect more and more data-points (columns) to each row. Now there is a few ways we could deal with this: 1. Store the data in a column-less format (such as JSON) and re-create the MonetDB database when we need to add or remove columns (since MonetDB is really fast to read data into it, and we have no problem automating this process) - This is what we've done so far in our proof of concept 2. Store everything in MonetDB and use Schema alterations as new columns are added (requires altering in a live database) 3. Split the data into multiple tables (one for each analysis) and use JOIN to select them. This makes it not so much one huge table, but several small. Still need schema alterations when doing anything besides adding analysis. (4. Use a lookup table syntax, where we store three columns: ID, NAME, VALUE and instead of having 10 million rows and 500 columns use 10*500 rows - one row for each data point) - Since MonetDB excels at being a columnar storage putting everything into one table with only three columns seems like a very bad fit. Nevertheless it's a though. Before I spend more time doing proof-of-concepts on scenario 2 and 3 I just wanted to ask: Am I missing something? Is there any obvious solution that I am not covering? Is there any way to have "open" columns (i.e dynamic columns instead of fixed schema where the columns are created upon insert) Again, thank you so much for the work everyone of you is doing on MonetDB. I hope to start contributing as I learn more (probably just adding documentation short-term, possibly committing fixes in the long-term) Regards, Niklas

2 1

about replica table
by chenkaijiang＠bigknow.com.cn 25 Nov '15

25 Nov '15

Hi, from https://www.monetdb.org/Documentation/Manuals/SQLreference/Tables, there 4 types of tables: normal, merge, remote, replica. For merge and remote table, I found explaining on https://www.monetdb.org/Documentation/Cookbooks/SQLrecipes/Distribu…. But what is replica table? Any documents? Could somebody help?

2 1

Driver JDBC, error rounding numeric value
by Luciano Sasso 25 Nov '15

25 Nov '15

Any idea, about error driver JDBC rounding error. Driver 2.18 run error, driver 2.1 run work ok. -- 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

1 0

Error driver JDBC
by Luciano Sasso 24 Nov '15

24 Nov '15

Hi, Iam use MonetDB JUL 2015-SPI, and driver JDBC 2.18, but error on load data, in rooud value, exampla, value 854 in source, load 850 in target. I am test with driver JDBC 2.1 and run word, value 845, load in target with 845. any idea !!! -- 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

1 0

Vacuum limitations
by Sébastien RAILLARD (PASSMAN) 24 Nov '15

24 Nov '15

Dear all, I have tried to call the vacuum function using this command: call sys.vacuum('my_schema','my_table'); I have a message telling me "vacuum not allowed on tables with indices". So, if I understand correctly, the table on which vacuum can be done must not have any index, even a primary key? Best regards, Sebastien -- 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/>

2 2

err incorrect checksum for freed object
by Lynn Carol Johnson 24 Nov '15

24 Nov '15

Hello - I had a working installation of monetdb on my MAC (OS x 10.9.5). Initially it was installed with the July 2015 release, I then updated to the july2015SP1. With the original version I was able to load a large (10G ) file without problems. For reasons relating to debugging a monetdb install on a red hat machine, I deleted the entries in the DB on my MAC and tried to re-add them. My commands were: > delete from annosites; > select count(*) from annosites; The db showed 0 items as expected. 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 ); But now, doing a "COPY INTO" continues to either hang with no error in the merovingian.log file , or to fail with the following error in the merovingian.log file. Most often it hangs. I have tried COPY into with and without the "records" option: sql> COPY INTO annosites from '/Users/lcj34/notes_files/machineLearningDB/annoDB_related/siteAnnoNoHdrsCol35Fixed_20151011.txt' USING DELIMITERS '\t','\n'; sql> sql>COPY 61000000 records INTO annosites from '/Users/lcj34/notes_files/machineLearningDB/annoDB_related/siteAnnoNoHdrsCol35Fixed_20151011.txt' USING DELIMITERS '\t','\n'; The merovingian.log file the one time it had an error: 015-11-23 15:02:55 MSG testJeff[752]: # loading sql script: 99_system.sql 2015-11-23 15:15:49 ERR testJeff[752]: mserver5(752,0x112908000) malloc: *** error for object 0x7fbe21a08208: incorrect checksum for freed object - object was probably modified after being freed. 2015-11-23 15:15:49 ERR testJeff[752]: *** set a breakpoint in malloc_error_break to debug 2015-11-23 15:15:50 MSG merovingian[747]: database 'testJeff' (752) was killed by signal SIGABRT Any idea what could be wrong? I tried stopping, then destroying the database and starting it over. This didn't help. I then created a new dbfarm, and new database, connect monetdbd to it, and tried again. While I can create this database of 37 columns, I am unable to load the file that previously was successfully loaded. Is this my a MAC issue? What else can I try on monetdb side? Is there any reason to re-install everything ? I'm still in the testing stage so would not be losing data. Thanks - Lynn

1 2

commit failures with dbfarm on iSCSI LUN
by Roberto Cornacchia 24 Nov '15

24 Nov '15

Hi there, Do you have any experience with running a dbfarm over iSCSI? We have tried to use the NAS in our 1Gbit LAN for our largish daily experiments with MonetDB. It's a very handy setup and seems more suited than NFS. It seems to achieve reasonable performance, but we get quite regularly (though not predictably for now) commit failures during a rather long ETL. We do not get such commit failures when the same db and ETL are run on a local disk. Excerpt from merovingian.log (Jul2015-SP1): 2015-11-24 11:52:37 ERR trec01[19110]: !ERROR: bm_subcommit: commit failed 2015-11-24 11:52:37 ERR trec01[19110]: !ERROR: log_tend: write failed 2015-11-24 11:52:37 ERR trec01[19110]: !FATAL: 40000!COMMIT: transation commit failed (perhaps your disk is full?) exiting (kernel error: !ERROR: GDKsave: error on: name=07/717, ext=theap, mode=1 2015-11-24 11:52:37 ERR trec01[19110]: !OS: Input/output error 2015-11-24 11:52:37 ERR trec01[19110]: ) The disk is most definitely not full, 1.5 TB available (the same works on a local disk with less space available). It looks like iSCSI is the problem (which works perfectly except these random failures). Can you think of any reason why iSCSI could could fail where a real local block device would not? iscsi client (where MonetDB runs): libiscsi 1.11.0 iscsi storage (where the dbfarm is stored): iscsid 2.0-871 The iSCSI LUN is created as regular file with thin provisioning (a file that dynamically grows on the NAS). We haven't tried yet with a fixed-size block-level LUN (trying this today anyway) Hoping someone can have an idea already. Roberto

2 2

String functions support in MonetDB
by Sébastien RAILLARD (PASSMAN) 24 Nov '15

24 Nov '15

Dear all, Is there a way to concatenate more than 2 strings at once? The CONCAT function only allow for concatenating 2 strings. Also, hhe JDBC driver for MonetDB reports that the following string functions are supported (see the list below). Are these functions documented somewhere? ascii char_length character_length code concat copyfrom difference editdistance editdistance2 get_value_for ilike index insert lcase left length levenshtein like locate lower lpad ltrim next_value_for not_ilike not_like octet_length patindex qgramnormalize repeat replace restart right rpad rtrim similarity soundex space splitpart strings substring trim truncate ucase upper Best regards, Sebastien -- 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/>

5 7

Making RPM packages for CentOS
by Sébastien RAILLARD (PASSMAN) 24 Nov '15

24 Nov '15

Dear all, Using a CentOS 6.7 x64 system, I was able to compile and make the RPM packages using the command "make rpm". The created packages were correctly installed and are working on the CentOS 6.7 x64 system. As I understand, the creation of the RPM packages process was made for Fedora systems: is-there any problem or limitation to be expected to create packages using "make rpm" for CentOS 6.x? Best regards, Sebastien -- 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/>

2 3

installing on redHat
by Lynn Carol Johnson 23 Nov '15

23 Nov '15

Hi all - I'm attempting to install on Redhat release 7.1 (Maipo) following the instructions here: https://www.monetdb.org/downloads/epel/ I have successfully executed : yum install http://dev.monetdb.org/downloads/epel/MonetDB-release-epel-1.1-1.monetdb.no… And rpm --import http://dev.monetdb.org/downloads/MonetDB-GPG-KEY But installing the server and client fail with the following message: [root@aztec Monetdb]# yum install MonetDB-SQL-server5 MonetDB-client Loaded plugins: langpacks, product-id, rhnplugin, subscription-manager This system is receiving updates from RHN Classic or Red Hat Satellite. http://dev.monetdb.org/downloads/epel/7Server/x86_64/repodata/repomd.xml: [Errno 14] HTTP Error 404 - Not Found Trying other mirror. One of the configured repositories failed (MonetDB 7Server - x86_64), and yum doesn't have enough cached data to continue. At this point the only safe thing yum can do is fail. There are a few ways to work "fix" this: 1. Contact the upstream for the repository and get them to fix the problem. 2. Reconfigure the baseurl/etc. for the repository, to point to a working upstream. This is most often useful if you are using a newer distribution release than is supported by the repository (and the packages for the previous distribution release still work). 3. Disable the repository, so yum won't use it by default. Yum will then just ignore the repository until you permanently enable it again or use --enablerepo for temporary usage: yum-config-manager --disable monetdb 4. Configure the failing repository to be skipped, if it is unavailable. Note that yum will try to contact the repo. when it runs most commands, so will have to try and fail each time (and thus. yum will be be much slower). If it is a very temporary problem though, this is often a nice compromise: yum-config-manager --save --setopt=monetdb.skip_if_unavailable=true failure: repodata/repomd.xml from monetdb: [Errno 256] No more mirrors to try. http://dev.monetdb.org/downloads/epel/7Server/x86_64/repodata/repomd.xml: [Errno 14] HTTP Error 404 - Not Found [root@aztec Monetdb]# When attempting to find this site from a browser I am unsuccessful. Should the repomd.xml file be grabbed from elsewhere? Any suggestions? Thanks - Lynn

3 3

MonetDBLite issue: Compute() function not working
by Iain Wallace 23 Nov '15

23 Nov '15

Hi, Firstly, thanks for the wonderful MonetDBLite package. It certainly speeds up my analysis! I came acrros an issue that I thought you might want to be aware of. The compute() function doesn't appear to be working with MonetDBLite while collect() does Building from the example https://www.monetdb.org/blog/monetdblite-r library(MonetDB.R) ms <- src_monetdb(embedded=dbdir) x<-table(ms,"mtcars") y<-x%>%filter(gear==4) dim(y) [1] 12 11 compute(y) Source: MonetDB () From: lmsomncnjj [0 x 11] Error: n not greater than 0L I wasn't sure where to post this, so have also posted the issue on dplyr's github page: https://github.com/hadley/dplyr/issues/1533 Thanks Iain

3 4

MonetDB Remote / Merge ( Distributed Query Processing ) Bug
by Brian Hood 19 Nov '15

19 Nov '15

Hi All, It seems when i run my SQL in sequence i can repeated break things with a MAL error. If this isn't a bug please can you point me in the right direction but looks like one with me. Master has 4 remote tables and a MERGE table ippacket with all the remote table added. All mserver5 instances are the same version / release. Welcome to mclient, the MonetDB/SQL interactive terminal (Jul2015-SP1) Database: MonetDB v11.21.11 (Jul2015-SP1), 'mapi:monetdb://mdb-master-01:50000/threatmonitor' Type \q to quit, \? for a list of available commands auto commit mode: on sql>\d MERGE TABLE threatmonitor.ippacket REMOTE TABLE threatmonitor.wifi_ippacket REMOTE TABLE threatmonitor.wifi_ippacket2 REMOTE TABLE threatmonitor.wifi_ippacket3 REMOTE TABLE threatmonitor.wifi_ippacket4 sql>select count(*) from ippacket limit 10; +------+ | L1 | +======+ | 951 | +------+ 1 tuple (44.499ms) sql>select guid, recv_date, ip_dst from ippacket limit 10 more>; (mapi:monetdb://monetdb@172.17.0.5/threatmonitor) 'user.l4' undefined in: (rmt3424_X_3_bat_oid_str:bat[:oid,:str],rmt3425_X_6_bat_oid_str:bat[:oid,:str],rmt3427_X_7_bat_oid_str:bat[:oid,:str]) := user.l4(); The second time i run the same query and its absolutely fine. sql>select guid, recv_date, ip_dst from ippacket limit 10 more>; +--------------------------------------+---------------------------+----------------+ | guid | recv_date | ip_dst | +======================================+===========================+================+ | 5f4c9bcb-e4b7-0af1-2919-fdef5623d20a | 2015-07-20 19:52:45 +0000 | 10.130.19.42 | | 2238c353-33cd-5174-31b6-ca57b4decade | 2015-07-20 19:52:45 +0000 | 216.58.209.227 | | 8ac3dde3-92ce-ff27-f6b1-f296b0d139f5 | 2015-07-20 19:52:45 +0000 | 10.130.19.42 | | 7a204771-8623-e498-5f45-ba1c58adb3da | 2015-07-20 19:52:45 +0000 | 85.25.200.151 | | 496ab33b-3ae9-56b5-9108-209413dcddeb | 2015-07-20 19:52:45 +0000 | 85.25.200.151 | | a1537072-8545-74eb-3b7e-9615a4c534ac | 2015-07-20 19:52:45 +0000 | 10.130.19.42 | | 91124c5a-0854-7f21-e60a-f175e45d68d1 | 2015-07-20 19:52:45 +0000 | 10.130.19.42 | | acefc01b-6ef8-6197-f5c0-27194e5749db | 2015-07-20 19:52:45 +0000 | 85.25.200.151 | | f0694ee8-2e7f-e019-a96e-450489ae8f37 | 2015-07-20 19:52:45 +0000 | 10.130.19.42 | | 78c246a9-eba2-79b5-fb87-59609f44a7d9 | 2015-07-20 19:52:45 +0000 | 85.25.200.151 | +--------------------------------------+---------------------------+----------------+ 10 tuples (63.443ms) sql> I've attached my example schema. Regards, Brian Hood

1 1

MonetDB writing to disk?!
by Martin Schwitalla 19 Nov '15

19 Nov '15

Hi, I´m running some analysis on MonetDB and have quite bad results with MonetDB. At first I thought it would be the fault of the Operator-at-a-Time Modell. I could replicate the behaviour that predicates on more columns resulted in a linear growing in execution times. This is a direct consequence of the Operator-at-a-Time Modell. But know i realized that the behaviour heavily depends on the used disk in the system. If I have the database on my primary disk storage I get quite good results. But because the primary disk storage is quite limited in our experimental PC I have worked on an NAS with 11TB. When I launch the same query on the NAS the query doesn`t take 0,4s, but 25s! After further testing I could see with iotop that the mserver is constantly writing something to the disks. But why is that so? I`m running Ubuntu 14.04 64bit, with 2 Intel Xeon E5-2690 CPUs and 62GB RAM. I have installed the TPC-H benchmark and running select count(*) from lineitem where l_orderkey%2=0; on my primary disk storage took about 400ms. On the external disk storage it took about 23s. The table contains 149996355 tuple and I created the TPC-H database with a scaling factor of 25. While running this query SELECT anon_1.sample_id AS anon_1_sample_id, anon_1.variant_id AS anon_1_variant_id, anon_1.qual AS anon_1_qual, anon_1.is_heterozygous AS anon_1_is_heterozygous, anon_1.read_depth AS anon_1_read_depth, anon_1.ref_depth AS anon_1_ref_depth, anon_1.alt_depth AS anon_1_alt_depth, anon_1.strand_bias AS anon_1_strand_bias, anon_1.qual_by_depth AS anon_1_qual_by_depth, anon_1.mapping_qual AS anon_1_mapping_qual, anon_1.haplotype_score AS anon_1_haplotype_score, anon_1.mapping_qual_bias AS anon_1_mapping_qual_bias, anon_1.read_pos_bias AS anon_1_read_pos_bias, annotations.variant_id AS annotations_variant_id, annotations.feature_id AS annotations_feature_id, annotations.ref_codon AS annotations_ref_codon, annotations.alt_codon AS annotations_alt_codon, annotations.ref_acid AS annotations_ref_acid, annotations.alt_acid AS annotations_alt_acid, annotations.type AS annotations_type, annotations.region AS annotations_region, annotations.splice_dist AS annotations_splice_dist FROM (SELECT calls.sample_id AS sample_id, calls.variant_id AS variant_id, calls.qual AS qual, calls.is_heterozygous AS is_heterozygous, calls.read_depth AS read_depth, calls.ref_depth AS ref_depth, calls.alt_depth AS alt_depth, calls.strand_bias AS strand_bias, calls.qual_by_depth AS qual_by_depth, calls.mapping_qual AS mapping_qual, calls.haplotype_score AS haplotype_score, calls.mapping_qual_bias AS mapping_qual_bias, calls.read_pos_bias AS read_pos_bias FROM calls JOIN samples ON samples.id = calls.sample_id JOIN patients ON patients.id = samples.patient_id JOIN diseases ON diseases.id = samples.disease_id LEFT OUTER JOIN (SELECT calls.sample_id AS sample_id, calls.variant_id AS variant_id, calls.qual AS qual, calls.is_heterozygous AS is_heterozygous, calls.read_depth AS read_depth, calls.ref_depth AS ref_depth, calls.alt_depth AS alt_depth, calls.strand_bias AS strand_bias, calls.qual_by_depth AS qual_by_depth, calls.mapping_qual AS mapping_qual, calls.haplotype_score AS haplotype_score, calls.mapping_qual_bias AS mapping_qual_bias, calls.read_pos_bias AS read_pos_bias FROM calls WHERE calls.qual > 0 AND calls.sample_id IN (227, 230, 233, 234, 237, 190, 195, 198, 199, 203, 270, 273, 276, 189, 343, 366, 367, 368)) AS anon_2 ON anon_2.variant_id = calls.variant_id LEFT OUTER JOIN known ON known.variant_id = calls.variant_id AND known.source_id IN (1, 2, 20, 19, 46) WHERE samples.accession IN ('17041R5', '20195R', '21984R', '23273R', '23390R', '13264R', '18337R', '18533R', '19811R', '20039R', '21776R', '21809R', '22927R', '17294R', '17071R', '21016R', 'Greif1R', '18337T', '18533T', '19811T', '20039T', '21809T', '22927T', '20195T', '21984T', '23273T', '23390T', '17041T', '17294T', '17071T', '21016T', '13264T', '21776T', 'Greif1T1S') AND calls.qual >= 50 AND anon_2.variant_id IS NULL AND (known.variant_id IS NULL OR known.clinical AND (NOT known.clinical_significance = 2) OR known.precious OR known.locus_specific_db)) AS anon_1 JOIN variants ON variants.id = anon_1.variant_id JOIN annotations ON variants.id = annotations.variant_id JOIN features ON features.id = annotations.feature_id JOIN transcripts ON transcripts.id = features.transcript_id JOIN genes ON genes.id = transcripts.gene_id WHERE (variants.is_transition IS NULL OR variants.is_transversion IS NULL OR (variants.is_transition OR variants.is_transversion) AND (anon_1.strand_bias IS NULL OR anon_1.strand_bias <= 60.0) AND (anon_1.qual_by_depth IS NULL OR anon_1.qual_by_depth >= 2.0) AND (anon_1.mapping_qual IS NULL OR anon_1.mapping_qual >= 40.0) AND (anon_1.haplotype_score IS NULL OR anon_1.haplotype_score <= 13.0) AND (anon_1.mapping_qual_bias IS NULL OR anon_1.mapping_qual_bias >= -12.5) AND (anon_1.read_pos_bias IS NULL OR anon_1.read_pos_bias >= -8.0) OR NOT (variants.is_transition OR variants.is_transversion) AND (anon_1.strand_bias IS NULL OR anon_1.strand_bias <= 200.0) AND (anon_1.qual_by_depth IS NULL OR anon_1.qual_by_depth >= 2.0) AND (anon_1.read_pos_bias IS NULL OR anon_1.read_pos_bias >= -20.0)) AND (abs(annotations.splice_dist) <= 10 OR annotations.region = 2 AND (NOT annotations.type = 2) OR annotations.region = 3 OR annotations.region = 1); I could see that the mserver is constantly writing to the disks. So why is MonetDB constantly writing to the disks, even when the query is quite simpel like the one TPC-H query above? The data should fit into the memory without problems. Any ideas or suggestions? Kind Regards, Martin

2 3

Using multiple COPY INTO inside a transaction from Java
by Gerardo Blanco 18 Nov '15

18 Nov '15

Hi, We are already using the COPY INTO bulk insert from Java via BufferedMCLWriter, as you suggest in your recipes book<https://www.monetdb.org/Documentation/Cookbooks/SQLrecipes/LoadingBulkData>, and based in the example<http://dev.monetdb.org/hg/MonetDB/file/tip/java/example/SQLcopyinto.java> you provided. I understood that each COPY INTO behaves as a single transaction. My question now is if there is a way to group two or more COPY INTO and make them behave as a single transaction. Thank you Gerardo

3 2

publishing this november 30th
by Anthony Damico 17 Nov '15

17 Nov '15

some good news to go with the bad https://github.com/ajdamico/asdfree/tree/master/IPUMS%20International asdfree.com has had over 1,000 unique visitors per week since august. hopefully this will push even more users your way. thanks

1 0

the majority of my monetdb programs broke between Jul2015 and Jul2015-SP1
by Anthony Damico 17 Nov '15

17 Nov '15

am i alone here? i'm very unhappy about this.. i'm sorry i'm being such a pain in the ass, but this has disrupted all of my work. all of my code worked on both unix and windows on prior versions. both the "american community survey" and "national incident based reporting system" bugs were introduced recently. [1] this bug [2] was also introduced after Jul2015, but luckily fixed before sp1's release. that had affected another two scripts. i am having a hell of a time reproducing a similar bug that's equally difficult to reproduce. it was a bit of a herculean effort for hannes to get 3828 isolated, the bug i am struggling with now is no different. [1] https://www.monetdb.org/bugzilla/buglist.cgi?bug_status=NEW&bug_status=ASSI… [2] https://www.monetdb.org/bugzilla/show_bug.cgi?id=3828

1 0

what could i explore to create a reproducible example for a bug report?
by Anthony Damico 17 Nov '15

17 Nov '15

yellow highlight is the only difference > # this command works fine create table zp13 as select v.* , arf.fips , arf.county , arf.countyst , arf.msacode from v13 as v left join arf on v.state_code = arf.state_cd AND v.bene_county_cd = arf.cnty_cd WITH DATA; > # this command crashes mserver create table zp13 as select v.* , arf.fips , arf.county , arf.countyst , arf.msacode , arf.msaname from v13 as v left join arf on v.state_code = arf.state_cd AND v.bene_county_cd = arf.cnty_cd WITH DATA; i've attached the sys.columns for the two tables, not sure if attachments are allowed on the mailing list but i can send separately if needed. thanks!

1 2

MonetDB concurrent commits exception
by Vijay Krishna 17 Nov '15

17 Nov '15

Hi, I am running a setup on MonetDB where in concurrent transactions take place. Everything works very fine but for a few transactions getting aborted due to SQLExceptions. I have set *auto commit to false* for all these transactions. I am getting frequent SQLExceptions with the message as *java.sql.SQLException: COMMIT: failed* But if I retry the same transaction, this works fine. Any idea on what would be the reason for frequent commit fails? When I set *auto commit to true* for the transactions, I get similarly frequent Exceptions but the message becomes *java.sql.SQLException: COMMIT: transaction is aborted because of concurrency conflicts* Is it like both the exception thrown be for the same cause? Any help much appreciated. Thanks in Advance. Vijayakrishna.P. Mobile : (+91) 9500402305.

4 5

Chinese string and varchar size
by Mathieu Raillard 16 Nov '15

16 Nov '15

Hi all, Last week we find a strange case with chinese character and VARCHAR size. We have in our database a field defined with VARCHAR(5) and a chinese string "不要让早把" to be inserted. If we insert the string with SQL insert everything works find, but if we try to insert via BULK monet throw an error. I made several test and i need to extend the varchar from 5 to 10 to get the bulk insert working. It looks like BULK somehow counts bits and not characters ? I give you some infos if you want to reproduce the case: create table test.varcharsize4 ( id int, varchar105 varchar(4), primary key (id) ); create table test.varcharsize5 ( id int, varchar106 varchar(5), primary key (id) ); create table test.varcharsize9 ( id int, varchar200 varchar(8), primary key (id) ); create table test.varcharsize10 ( id int, varchar200 varchar(10), primary key (id) ); *Insert SQL :* insert into test.varcharsize4 values (1,'不要让早把'); insert into test.varcharsize5 values (1,'不要让早把'); insert into test.varcharsize9 values (1,'不要让早把'); insert into test.varcharsize10 values (1,'不要让早把'); *BULK Line to insert :* "1","不要让早把" *BULK executions :* COPY 10 RECORDS INTO test.varcharsize4 FROM '/root/test_varchar_chinois' USING DELIMITERS ',','\n','\"' NULL AS '\\N' ; Result : Failed to import table line 1 field 2 'varchar(4)' expected in '不要让早把' ------ COPY 10 RECORDS INTO test.varcharsize5 FROM '/root/test_varchar_chinois' USING DELIMITERS ',','\n','\"' NULL AS '\\N' ; Result : Failed to import table line 1 field 2 'varchar(5)' expected in '不要让早把' ------- COPY 10 RECORDS INTO test.varcharsize89 FROM '/root/test_varchar_chinois' USING DELIMITERS ',','\n','\"' NULL AS '\\N' ; Result : Failed to import table line 1 field 2 'varchar(9)' expected in '不要让早把' ------- COPY 10 RECORDS INTO test.varcharsize10 FROM '/root/test_varchar_chinois' USING DELIMITERS ',','\n','\"' NULL AS '\\N' ; OK Regards Mathieu

2 1

Merge table; partition pruning?
by Bryan Senseman 13 Nov '15

13 Nov '15

Ok, I was all excited about MERGE TABLEs moving up into the SQL layer, unfortunately my enthusiasm might be misplaced as I don’t see any improvement in query performance. Let me explain: I have a fact table with ~300 M rows with 5 years of data. I created 5 yearly tables, ordering by my date_key (and also created a year column). Checked storage to verify MonetDB knew they were sorted. Created a Merge table over all 5 yearly tables. I then ran test queries with various year subsets (using direct fact column filter and joining from date dimension), from 1 to all 5 and well I didn’t see any improvement in query speed when a subset of the 5 years was queried. In fact I usually saw 10-20% degradation of performance. I am no MAL expert, but looking at the PLAN and the EXPLAIN/TRACE outputs it appears to me tables that shouldn’t have data for the query are still scanned. I ran this both on a 16GB 4 core Windows box and a 32GB 8 core EC2 Linux box, same behavior both places, using both July2015 and July2015SP1. Is this expected behavior? Am I missing some magic to have MonetDB do the Oracle concept of Partition Elimination? Or are Merge tables really just meant for data maintenance? Thanx, Bryan -- ------------------------------ This transmission is confidential and intended solely for the use of the recipient named above. It may contain confidential, proprietary, or legally privileged information. If you are not the intended recipient, you are hereby notified that any unauthorized review, use, disclosure or distribution is strictly prohibited. If you have received this transmission in error, please contact the sender by reply e-mail and delete the original transmission and all copies from your system.

4 3

Error starting a production database
by Juan Carlos Gastélum 13 Nov '15

13 Nov '15

Hi I'm getting the following error when a I try to start our production database: # builtin opt gdk_dbpath = /var/monetdb5/dbfarm/demo # builtin opt gdk_debug = 0 # builtin opt gdk_vmtrim = no # builtin opt monet_prompt = > # builtin opt monet_daemon = no # builtin opt mapi_port = 50000 # builtin opt mapi_open = false # builtin opt mapi_autosense = false # builtin opt sql_optimizer = default_pipe # builtin opt sql_debug = 0 # cmdline opt gdk_dbpath = /usr/local/monetdb/alm_dat_far # cmdline opt merovingian_uri = mapi:monetdb://farser07:50000/alm_dat_far # cmdline opt mapi_open = false # cmdline opt mapi_port = 0 # cmdline opt mapi_usock = /usr/local/monetdb/alm_dat_far/.mapi.sock # cmdline opt monet_vault_key = /usr/local/monetdb/alm_dat_far/.vaultkey # cmdline opt gdk_nr_threads = 6 # cmdline opt max_clients = 64 # cmdline opt sql_optimizer = default_pipe # cmdline opt monet_daemon = yes # cmdline opt gdk_debug = 10 # MonetDB 5 server v11.21.11 "Jul2015-SP1" # Serving database 'alm_dat_far', using 6 threads # Compiled for x86_64-pc-linux-gnu/64bit with 64bit OIDs and 128bit integers dynamically linked # Found 39.298 GiB available main-memory. # Copyright (c) 1993-July 2008 CWI. # Copyright (c) August 2008-2015 MonetDB B.V., all rights reserved # Visit http://www.monetdb.org/ for further information # Listening for UNIX domain connection requests on mapi:monetdb:///usr/local/monetdb/alm_dat_far/.mapi.sock # Start processing logs sql/sql_logs version 52200 !FATAL: Logger_new: cannot create seqs bats What can I do to solve it? I have been trying to recover a backup of the virtual machine where the instance of monetdb is but until now I have not been able to recover this database. Thanks in advance. Juan Carlos Gastelum

3 9

Merge and remote table errors
by Bart Lenoir 12 Nov '15

12 Nov '15

Hi, Just adding another finding: Using 1 MERGE TABLE (on Mac) with 2 REMOTE TABLEs (on Linux [physically remote, in another network]) COUNT, MIN, MAX are fine but AVG, MEDIAN, QUANTILE etc. make the server crash. Using 1 MERGE TABLE (on Linux) with 1 REMOTE TABLE (on Linux [physically together, in the same network]) and 1 local TABLE, all the above functions are fine except for the occasional error. sql>select sys.median(doubleDataType) from xxxxxxx; (mapi:monetdb://monetdb@192.168.1.212/xxxxxxx) 'user.l2' undefined in: (rmt3853_X_62_bat_oid_str:bat[:oid,:str],rmt3854_X_65_bat_oid_str:bat[:oid,:str],rmt3855_X_66_bat_oid_str:bat[:oid,:str ],rmt3856_X_67_bat_oid_str:bat[:oid,:str],rmt3857_X_68_bat_oid_dbl:bat[:oid,:dbl],rmt3858_X_70_bat_oid_dbl:bat[:oid,:d bl],rmt3859_X_71_bat_oid_dbl:bat[:oid,:dbl],rmt3860_X_72_bat_oid_dbl:bat[:oid,:dbl],rmt3861_X_73_bat_oid_dbl:bat[:oid ,:dbl],rmt3862_X_74_bat_oid_dbl:bat[:oid,:dbl],rmt3863_X_75_bat_oid_dbl:bat[:oid,:dbl],rmt3864_X_76_bat_oid_dbl:bat[: oid,:dbl],rmt3865_X_77_bat_oid_dbl:bat[:oid,:dbl],rmt3866_X_78_bat_oid_dbl:bat[:oid,:dbl],rmt3867_X_79_bat_oid_dbl:ba t[:oid,:dbl],rmt3868_X_80_bat_oid_dbl:bat[:oid,:dbl],rmt3869_X_81_bat_oid_dbl:bat[:oid,:dbl],rmt3870_X_82_bat_oid_dbl: bat[:oid,:dbl],rmt3871_X_83_bat_oid_dbl:bat[:oid,:dbl],rmt3872_X_84_bat_oid_dbl:bat[:oid,:dbl],rmt3873_X_85_bat_oid_d bl:bat[:oid,:dbl],rmt3874_X_86_bat_oid_dbl:bat[:oid,:dbl],rmt3875_X_87_bat_oid_dbl:bat[:oid,:dbl],rmt3876_X_88_bat_oid _dbl:bat[:oid,:dbl],rmt3877_X_89_bat_oid_dbl:bat[:oid,:dbl],rmt3878_X_90_bat_oid_dbl:bat[:oid,:dbl],rmt3879_X_91_bat_o id_dbl:bat[:oid,:dbl],rmt3880_X_92_bat_oid_dbl:bat[:oid,:dbl],rmt3881_X_93_bat_oid_dbl:bat[:oid,:dbl],rmt3882_X_94_ba t_oid_dbl:bat[:oid,:dbl],rmt3883_X_95_bat_oid_dbl:bat[:oid,:dbl],rmt3884_X_97_bat_oid_dbl:bat[:oid,:dbl],rmt3885_X_98_ bat_oid_dbl:bat[:oid,:dbl],rmt3886_X_99_bat_oid_dbl:bat[:oid,:dbl],rmt3887_X_100_bat_oid_dbl:bat[:oid,:dbl],rmt3888_X_ 101_bat_oid_dbl:bat[:oid,:dbl],rmt3889_X_103_bat_oid_dbl:bat[:oid,:dbl],rmt3890_X_104_bat_oid_dbl:bat[:oid,:dbl],rmt38 91_X_105_bat_oid_dbl:bat[:oid,:dbl],rmt3892_X_106_bat_oid_dbl:bat[:oid,:dbl],rmt3893_X_107_bat_oid_dbl:bat[:oid,:dbl],r mt3894_X_108_bat_oid_dbl:bat[:oid,:dbl],rmt3895_X_109_bat_oid_dbl:bat[:oid,:dbl],rmt3896_X_110_bat_oid_dbl:bat[:oid,: dbl],rmt3897_X_111_bat_oid_dbl:bat[:oid,:dbl],rmt3898_X_112_bat_oid_dbl:bat[:oid,:dbl],rmt3899_X_113_bat_oid_dbl:bat[: oid,:dbl],rmt3900_X_114_bat_oid_dbl:bat[:oid,:dbl],rmt3901_X_115_bat_oid_dbl:bat[:oid,:dbl],rmt3902_X_116_bat_oid_dbl: bat[:oid,:dbl],rmt3903_X_117_bat_oid_dbl:bat[:oid,:dbl],rmt3904_X_118_bat_oid_dbl:bat[:oid,:dbl],rmt3905_X_119_bat_oid _dbl:bat[:oid,:dbl],rmt3906_X_120_bat_oid_dbl:bat[:oid,:dbl],rmt3907_X_121_bat_oid_dbl:bat[:oid,:dbl],rmt3908_X_122_ba t_oid_dbl:bat[:oid,:dbl],rmt3909_X_123_bat_oid_dbl:bat[:oid,:dbl],rmt3910_X_124_bat_oid_dbl:bat[:oid,:dbl],rmt3911_X_12 5_bat_oid_dbl:bat[:oid,:dbl],rmt3912_X_126_bat_oid_bit:bat[:oid,:bit],rmt3913_X_128_bat_oid_bit:bat[:oid,:bit],rmt3914_X_ 130_bat_oid_oid:bat[:oid,:oid]) := user.l2(); Server version on Mac and Linux machines: MonetDB 5 server v11.21.5 "Jul2015" (64-bit, 64-bit oids, 128-bit integers) Thanks. Kind regards, Bart

1 0

Duplicate views problem
by Dennis Pallett 11 Nov '15

11 Nov '15

Hi all, We're having a problem whereby it's somehow possible in a MonetDB database to have multiple views with the same name. See attached screenshot of an example. Any ideas on how this is possible? Seems like a bug but unfortunately we can't reliably reproduce it. It just happens every now and then. Best regards, Dennis

2 2

Usage of pool
by poornima＠apakau.com 11 Nov '15

11 Nov '15

Hi, I am trying to use monetdb with Hikari Pool. I have set the connection timeout to 4500. I have 4 max connections. The query execution takes at the max 20 ms. I am testing with 250 threads and 100000 requests. But I get a connection time out as follows : 11 09:40:24 PST.DEBUG118*HikariPool~logPoolState@339: Timeout failure pool HikariPool-0 stats (total=4, active=3, idle=1, waiting=1) The waiting number increases to a large number as 139. The idle drops to 0 in just 4 such messages. My Hikari pool configuration is as follows _dataSource = new HikariDataSource(); _dataSource.setDriverClassName("nl.cwi.monetdb.jdbc.MonetDriver"); _dataSource.setJdbcUrl("jdbc:monetdb://" + _reg.getStrProp("tdbUrl")); _dataSource.setUsername(_reg.getStrProp("tdbUsr")); _dataSource.setPassword(_reg.getStrProp("tdbPswd")); _dataSource.setMaximumPoolSize(4); _dataSource.setConnectionTimeout(4500); _dataSource.addDataSourceProperty("cachePrepStmts", "true"); _dataSource.addDataSourceProperty("prepStmtCacheSize", "250"); _dataSource.addDataSourceProperty("prepStmtCacheSqlLimit", "2048"); Do I need to change the configuration ? Or am I supposed to change the pool ? Which is the pool that is used with monetdb JDBC driver ? Regards, Poornima.

1 0

thanks for BEST EFFORT
by Anthony Damico 11 Nov '15

11 Nov '15

1 0

Trying to understand select performance when returning many columns
by Ben Jeffery 09 Nov '15

09 Nov '15

I have a table of 93 columns and ~90M rows which is a 60GB "bat" directory on disk. Mostly I'm doing things with a few columns - Monet has been _amazing_ for those use cases so thanks for all your hard work. However sometimes I need to return all columns for a small subset of rows and I can't quite understand what Monet is doing or why it is taking a long time. Here's some timings for 'select * from table order by "unsorted_numerical_column" limit N;' caches all flushed before each query: N=1 907ms N=100 3105ms N=200 6782ms N=500 14656ms N=1000 30100ms N=10000 71093ms For the last two my 32GB ram is being completely filled with the cache. I'm clearly missing something in my understanding as I don't see why that should be needed. Surely you only need to read the whole of the sort column and then grab only N rows from all the other columns? Is there anything I can do to reduce the amount of data loaded? The output of "explain" is at https://gist.github.com/benjeffery/30e9f6173b625f5d0c31 <https://gist.github.com/benjeffery/30e9f6173b625f5d0c31if>. Thanks, Ben

1 0

MonetDB JDBC driver and IntelliJ IDEA 15
by Endre Palatinus 09 Nov '15

09 Nov '15

Dear MonetDB users, I would like to access my MonetDB (latest version: v1.7 (Jul2015-SP1)) database through JDBC (MonetDB JDBC driver version 2.18) using IntelliJ IDEA 15.0 under Mac OS X 10.9.5. The "Test connection" function succeeds, but the "Refresh" function crashes in the Database tool of the IDE with the Exception below. The same problem happened when I was trying to connect to PostgreSQL 9.4 with a PostgreSQL JDBC driver older than JDBC version 4.2. I guess that MonetDB's JDBC driver has JDBC version 4.0 only. Any ideas on how to fix this? Best regards, Endre Palatinus ===== Exception: ====== TABLE_TYPE unavailable java.lang.NullPointerException at nl.cwi.monetdb.jdbc.MonetResultSet.getObject(MonetResultSet.java:1945) at com.intellij.database.remote.jdbc.impl.RemoteResultSetImpl.getObject(RemoteResultSetImpl.java:77) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:497) at sun.rmi.server.UnicastServerRef.dispatch(UnicastServerRef.java:323) at sun.rmi.transport.Transport$1.run(Transport.java:200) at sun.rmi.transport.Transport$1.run(Transport.java:197) at java.security.AccessController.doPrivileged(Native Method) at sun.rmi.transport.Transport.serviceCall(Transport.java:196) at sun.rmi.transport.tcp.TCPTransport.handleMessages(TCPTransport.java:568) at sun.rmi.transport.tcp.TCPTransport$ConnectionHandler.run0(TCPTransport.java:826) at sun.rmi.transport.tcp.TCPTransport$ConnectionHandler.lambda$run$254(TCPTransport.java:683) at sun.rmi.transport.tcp.TCPTransport$ConnectionHandler$$Lambda$2/490517473.run(Unknown Source) at java.security.AccessController.doPrivileged(Native Method) at sun.rmi.transport.tcp.TCPTransport$ConnectionHandler.run(TCPTransport.java:682) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) at java.lang.Thread.run(Thread.java:745) at sun.rmi.transport.StreamRemoteCall.exceptionReceivedFromServer(StreamRemoteCall.java:276) at sun.rmi.transport.StreamRemoteCall.executeCall(StreamRemoteCall.java:253) at sun.rmi.server.UnicastRef.invoke(UnicastRef.java:162) at java.rmi.server.RemoteObjectInvocationHandler.invokeRemoteMethod(RemoteObjectInvocationHandler.java:194) at java.rmi.server.RemoteObjectInvocationHandler.invoke(RemoteObjectInvocationHandler.java:148) at com.sun.proxy.$Proxy130.getObject(Unknown Source) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:497) at com.intellij.execution.rmi.RemoteUtil.invokeRemote(RemoteUtil.java:124) at com.intellij.execution.rmi.RemoteUtil.access$100(RemoteUtil.java:36) at com.intellij.execution.rmi.RemoteUtil$RemoteInvocationHandler.invoke(RemoteUtil.java:229) at com.sun.proxy.$Proxy131.getObject(Unknown Source) at com.intellij.database.util.JdbcUtil.resultSetToListSafe(JdbcUtil.java:61) at com.intellij.database.dataSource.DatabaseModelLegacyLoader.getAllTableTypes(DatabaseModelLegacyLoader.java:966) at com.intellij.database.dataSource.DatabaseModelLegacyLoader.loadDataSourceStateInner(DatabaseModelLegacyLoader.java:127) at com.intellij.database.dataSource.DatabaseModelLegacyLoader.loadInner(DatabaseModelLegacyLoader.java:88) at com.intellij.database.dataSource.DatabaseModelLoader.performLegacyIntrospection(DatabaseModelLoader.java:197) at com.intellij.database.dataSource.DatabaseModelLoader.performIntrospection(DatabaseModelLoader.java:99) at com.intellij.database.dataSource.AbstractDataSource.refreshMetaData(AbstractDataSource.java:61) at com.intellij.database.dataSource.AbstractDataSource$1.perform(AbstractDataSource.java:36) at com.intellij.database.dataSource.AbstractDataSource$1.perform(AbstractDataSource.java:34) at com.intellij.database.dataSource.AbstractDataSource.performJdbcOperation(AbstractDataSource.java:116) at com.intellij.database.dataSource.AbstractDataSource.refreshMetaData(AbstractDataSource.java:34) at com.intellij.database.dataSource.DataSourceUiUtil$2.run(DataSourceUiUtil.java:188) at com.intellij.openapi.progress.impl.CoreProgressManager$TaskRunnable.run(CoreProgressManager.java:563) at com.intellij.openapi.progress.impl.CoreProgressManager$2.run(CoreProgressManager.java:142) at com.intellij.openapi.progress.impl.CoreProgressManager.a(CoreProgressManager.java:446) at com.intellij.openapi.progress.impl.CoreProgressManager.executeProcessUnderProgress(CoreProgressManager.java:392) at com.intellij.openapi.progress.impl.ProgressManagerImpl.executeProcessUnderProgress(ProgressManagerImpl.java:54) at com.intellij.openapi.progress.impl.CoreProgressManager.runProcess(CoreProgressManager.java:127) at com.intellij.openapi.progress.impl.ProgressManagerImpl$1.run(ProgressManagerImpl.java:126) at com.intellij.openapi.application.impl.ApplicationImpl$8.run(ApplicationImpl.java:365) at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511) at java.util.concurrent.FutureTask.run(FutureTask.java:266) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) at java.lang.Thread.run(Thread.java:745) at org.jetbrains.ide.PooledThreadExecutor$1$1.run(PooledThreadExecutor.java:55) and 8 duplicate reports

1 0

setting up db with storagemodel.sql
by Lynn Carol Johnson 08 Nov '15

08 Nov '15

Hi all - I am new to monetdb. I am running on a MAC (OS X 10.9.5) I have installed monetdb, I have setup a dbfarm, created a couple of databases, and loaded tables. I would like to run some analysis on the amount of storage used for some of my tables. I have seen the description of autoloading scripts here: https://www.monetdb.org/book/export/html/242 And examples of running the storage() command here: https://www.monetdb.org/Documentation/Cookbooks/SQLrecipes/storage-model But I get "no such operator 'storagemodlinit' when I try to execute "call storagemodelinit()" . What command must be run to include the .sql extensions with the database? Is something run when we create the dbfarm? When we create the individual databases? I can see the .sql extensions in my /usr/local/monetdb/lib/monetdb5/createdb folder on my MAC. I'd appreciate any pointers on how to set this up. Googling has not provided me with any answers. Thanks - Lynn

3 7

Merge and remote table errors
by Bart Lenoir 08 Nov '15

08 Nov '15

Hi, I'm running queries on a Mac MERGE table using two Linux REMOTE tables and got for each query occasionally (1/10 times) the following identical error: (mapi:monetdb://monetdb@localhost/whalesdb) 'user.l2' undefined in: (rmt1094_X_2_bat_oid_str:bat[:oid,:str],rmt1095_X_5_bat_oid_wrd:bat[:oid,:wrd]) := user.l2(); The same queries on the same data set but from Linux to Linux produces also occasionally (1/10 times) a similar but different identical error: (mapi:monetdb://monetdb@localhost/whalesdb) 'user.l1' undefined in: (rmt438_X_3_bat_oid_str:bat[:oid,:str],rmt439_X_6_bat_oid_str:bat[:oid,:str],rmt440_X_7_bat_oid_int:bat[:oid,:int]) := user.l1(); Per default the connections are via SSH tunnels though direct access via remote IPs, and also local IPs where possible, gives the same results. The tables are generated using R and are having currently still having CHARACTER LARGE OBJECT as data types (in case that matters). As pointed out already only 1 out of 10 times the queries are run the error messages are shown. Has someone else come across problems like these? Thanks. Kind regards, Bart

1 0

Handling of control characters NUL and DEL
by Sébastien RAILLARD (PASSMAN) 05 Nov '15

05 Nov '15

Dear all, During data validation, we have noticed some strange behavior when working with the control characters NUL (0x00) and DEL (0x7F): * The NUL character seems to be ignored. For example, if we try to insert in a VARCHAR column this text: 'AB' || CODE(0) || 'CD', the stored value will be 'ABCD'. * The DEL character isn't escaped when copying data from table to file, whereas all the other control characters (from 0x01 to 0x1F) are correctly escaped. This may produce some display artifacts where the string 'AB' || CODE(127) || 'CD' will be usually displayed as 'ACD'. Best regards, Sebastien -- 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/>

3 6

HUEGINT incorrect value
by Sébastien RAILLARD (PASSMAN) 05 Nov '15

05 Nov '15

Dear all, The value -9223372036854775808 isn't correctly inserted in HUGEINT column, the result of the insertion is a "nil" value. Steps to reproduce the issue: CREATE TABLE "seb"."test_hugeint" ( "ID" INTEGER NOT NULL AUTO_INCREMENT, "cHUGEINT" HUGEINT, CONSTRAINT "pkey2" PRIMARY KEY ("ID") ); INSERT INTO "seb"."test_hugeint" ("cHUGEINT") VALUES (-9223372036854775807); INSERT INTO "seb"."test_hugeint" ("cHUGEINT") VALUES (-9223372036854775808); INSERT INTO "seb"."test_hugeint" ("cHUGEINT") VALUES (-9223372036854775809); And the result of SELECT * FROM "seb"."test_hugeint"; is: +------+----------------------+ | ID | cHUGEINT | +======+======================+ | 1 | -9223372036854775807 | | 2 | nil | | 3 | -9223372036854775809 | +------+----------------------+ Sébastien -- 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/>

3 2

Dataype documentation error
by Sébastien RAILLARD (PASSMAN) 05 Nov '15

05 Nov '15

Dear all, There is an error in the builtin datatypes documentation page (https://www.monetdb.org/Documentation/Manuals/SQLreference/BuiltinTypes) The valid interval for the BIGINT type is from -9223372036854775807 to 9223372036854775807. Also, I don't know if it may be useful, but the limits for the HUEGEINT in numeral form are -170141183460469231731687303715884105727 to 170141183460469231731687303715884105727. Sebastien. -- 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/>

2 1

Re: Parallel batch insert failure in MonetDB through JDBC
by Siddharth Tyagi 04 Nov '15

04 Nov '15

Hi, I am trying to insert data in multiple tables in parallel through prepared statements batch execution. I am facing a problem , first thread that executes the batch makes other executions to crash because of the underlying exception - prepared statement is no longer available. Is there a limitation of updating schema in parallel jobs ? If not then what may be the cause of this problem ? Any light on how I may be able to implement this will be helpful. >> code that executes batches package monet.test; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.SQLException; import java.sql.Statement; import java.sql.Timestamp; import java.sql.Types; import java.util.ArrayList; import java.util.List; import java.util.Random; public class BatchTest { public static class TestRunner implements Runnable { private final static String problemHere = "jdbc:monetdb://localhost/test-db"; static { try { Class.forName("nl.cwi.monetdb.jdbc.MonetDriver"); } catch (ClassNotFoundException e) { // TODO Auto-generated catch block e.printStackTrace(); } } private static Connection createConnection() throws SQLException { // happens one time per thread return DriverManager.getConnection(problemHere, "monetdb", "monetdb"); } private static int fieldCount = 10; private static int[] colType = new int[fieldCount]; private static final int[] types = { Types.VARCHAR, Types.TIMESTAMP, Types.DECIMAL }; static { Random random = new Random(); // initialize column types for (int i = 0; i < fieldCount; i++) { colType[i] = types[random.nextInt(types.length)]; } } private final String name; private Connection con; private int batchCount; private int batchSize; private String create; private String drop; private String insert; public TestRunner(String string, int bs, int bc) throws SQLException { this.name = string; this.con = createConnection(); this.batchCount = bc; this.batchSize = bs; this.create = "create table " + name + " ("; for (int i = 0; i < fieldCount; i++) { create += (i == 0 ? "" : ",") + "col" + i + " " + getType(colType[i]); } this.create += ")"; this.insert = "insert into " + name + " values ("; for (int i = 0; i < fieldCount; i++) { insert += (i == 0 ? "" : ",") + "?"; } this.insert += ")"; this.drop = "drop table " + name; } private static String getType(int i) { switch (i) { case Types.DECIMAL: return "decimal(18,9)"; case Types.VARCHAR: return "varchar(30000)"; case Types.TIMESTAMP: return "timestamp"; } return null; } protected void finalize() throws Throwable { if (con != null) { con.close(); } }; public void run() { System.out.format("%s started.%ncreate: %s%ninsert: %s%ndrop: %s%n", name, create, insert, drop); try (Statement stmt = con.createStatement()) { // this will throw the exception of concurrency con synchronized (problemHere) { System.out.format("%d created %s%n", stmt.executeUpdate(create), name); if (!con.getAutoCommit()) { con.commit(); } } } catch (SQLException e1) { e1.printStackTrace(); return; } try (PreparedStatement stmt = con.prepareStatement(insert)) { while (batchCount-- > 0) { for (int i = batchSize; i > 0; i--) { setBatchInput(stmt); stmt.addBatch(); } System.out.format("%s - submitting batch ...%n", name); stmt.executeBatch(); if (!con.getAutoCommit()) { con.commit(); } } } catch (Exception e) { e.printStackTrace(); } finally { if (con != null) { try (Statement stmt = con.createStatement()) { synchronized (problemHere) { System.out.format("%d deleted - %s%n", stmt.executeUpdate(drop), name); if (!con.getAutoCommit()) { con.commit(); } } } catch (SQLException e1) { e1.printStackTrace(); } try { con.close(); con = null; } catch (SQLException e) { e.printStackTrace(); } } } System.out.format("%s finished.", name); } private static void setBatchInput(PreparedStatement stmt) throws SQLException { for (int i = 1; i <= fieldCount; i++) { stmt.setObject(i, getRandomFieldValue(colType[i - 1])); } } private static Object getRandomFieldValue(int type) { switch (type) { case Types.DECIMAL: return 0; case Types.VARCHAR: return "null"; case Types.TIMESTAMP: return new Timestamp(System.currentTimeMillis()); } return null; } } public static void main(String[] args) { int num = 2; List<Thread> ts = new ArrayList<>(); while (num-- > 0) { Thread t = null; try { (t = new Thread(new TestRunner("runner_" + num, 10000, 1))).start(); } catch (SQLException e) { e.printStackTrace(); } ts.add(t); } for (Thread t : ts) { try { t.join(); } catch (InterruptedException e) { e.printStackTrace(); } } } } On Wed, Nov 4, 2015 at 3:20 PM, Siddharth Tyagi <inbox.siddharth(a)gmail.com> wrote: > Hi, > I am trying to insert data in multiple tables in parallel through prepared > statements batch execution. > I am facing a problem , > > first thread that executes the batch makes other executions to crash > because of the underlying exception - prepared statement is no longer > available. > > Is there a limitation of updating schema in parallel jobs ? > If not then what may be the cause of this problem ? > > Any light on how I may be able to implement this will be helpful. > > >> code that executes batches > > package monet.test; > > import java.sql.Connection; > import java.sql.DriverManager; > import java.sql.PreparedStatement; > import java.sql.SQLException; > import java.sql.Statement; > import java.sql.Timestamp; > import java.sql.Types; > import java.util.ArrayList; > import java.util.List; > import java.util.Random; > > public class BatchTest { > public static class TestRunner implements Runnable { > private final static String problemHere = > "jdbc:monetdb://localhost/test-db"; > > static { > try { > Class.forName("nl.cwi.monetdb.jdbc.MonetDriver"); > } catch (ClassNotFoundException e) { > // TODO Auto-generated catch block > e.printStackTrace(); > } > } > > private static Connection createConnection() throws SQLException { > // happens one time per thread > return DriverManager.getConnection(problemHere, "monetdb", "monetdb"); > } > > private static int fieldCount = 10; > private static int[] colType = new int[fieldCount]; > private static final int[] types = { Types.VARCHAR, Types.TIMESTAMP, > Types.DECIMAL }; > > static { > Random random = new Random(); > // initialize column types > for (int i = 0; i < fieldCount; i++) { > colType[i] = types[random.nextInt(types.length)]; > } > } > > private final String name; > private Connection con; > private int batchCount; > private int batchSize; > private String create; > private String drop; > private String insert; > > public TestRunner(String string, int bs, int bc) throws SQLException { > this.name = string; > this.con = createConnection(); > this.batchCount = bc; > this.batchSize = bs; > this.create = "create table " + name + " ("; > > for (int i = 0; i < fieldCount; i++) { > create += (i == 0 ? "" : ",") + "col" + i + " " + getType(colType[i]); > } > this.create += ")"; > this.insert = "insert into " + name + " values ("; > for (int i = 0; i < fieldCount; i++) { > insert += (i == 0 ? "" : ",") + "?"; > } > this.insert += ")"; > this.drop = "drop table " + name; > } > > private static String getType(int i) { > switch (i) { > case Types.DECIMAL: > return "decimal(18,9)"; > case Types.VARCHAR: > return "varchar(30000)"; > case Types.TIMESTAMP: > return "timestamp"; > } > return null; > } > > protected void finalize() throws Throwable { > if (con != null) { > con.close(); > } > }; > > public void run() { > System.out.format("%s started.%ncreate: %s%ninsert: %s%ndrop: %s%n", > name, create, insert, drop); > try (Statement stmt = con.createStatement()) { > // this will throw the exception of concurrency con > synchronized (problemHere) { > System.out.format("%d created %s%n", stmt.executeUpdate(create), name); > if (!con.getAutoCommit()) { > con.commit(); > } > } > } catch (SQLException e1) { > e1.printStackTrace(); > return; > } > try (PreparedStatement stmt = con.prepareStatement(insert)) { > while (batchCount-- > 0) { > for (int i = batchSize; i > 0; i--) { > setBatchInput(stmt); > stmt.addBatch(); > } > System.out.format("%s - submitting batch ...%n", name); > stmt.executeBatch(); > if (!con.getAutoCommit()) { > con.commit(); > } > } > } catch (Exception e) { > e.printStackTrace(); > } finally { > if (con != null) { > try (Statement stmt = con.createStatement()) { > synchronized (problemHere) { > System.out.format("%d deleted - %s%n", stmt.executeUpdate(drop), name); > if (!con.getAutoCommit()) { > con.commit(); > } > } > } catch (SQLException e1) { > e1.printStackTrace(); > } > try { > con.close(); > con = null; > } catch (SQLException e) { > e.printStackTrace(); > } > } > } > System.out.format("%s finished.", name); > } > > private static void setBatchInput(PreparedStatement stmt) throws > SQLException { > for (int i = 1; i <= fieldCount; i++) { > stmt.setObject(i, getRandomFieldValue(colType[i - 1])); > } > } > > private static Object getRandomFieldValue(int type) { > switch (type) { > case Types.DECIMAL: > return 0; > case Types.VARCHAR: > return "null"; > case Types.TIMESTAMP: > return new Timestamp(System.currentTimeMillis()); > } > return null; > } > } > > public static void main(String[] args) { > int num = 2; > List<Thread> ts = new ArrayList<>(); > while (num-- > 0) { > Thread t = null; > try { > (t = new Thread(new TestRunner("runner_" + num, 10000, 1))).start(); > } catch (SQLException e) { > e.printStackTrace(); > } > ts.add(t); > } > for (Thread t : ts) { > try { > t.join(); > } catch (InterruptedException e) { > e.printStackTrace(); > } > } > } > > } > > -- > > > *Thanks and RegardsSiddharth Tyagi* > -- *Thanks and RegardsSiddharth Tyagi*

1 0

Announcement: New Jul2015-SP1 Bugfix release of MonetDB suite
by Sjoerd Mullender 03 Nov '15

03 Nov '15

The MonetDB team at CWI/MonetDB BV is pleased to announce the Jul2015-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/>. Jul 2015-SP1 bugfix release Client Package * In the SQL formatter of mclient (the default) we now properly align East Asian wide characters. Bug Fixes * 3789: Query on large string table fails on HEAPextend * 3794: table function sys.rejects() and view sys.rejects() are listed are metadata objects but give an (incorrect) error when they are queried * 3797: COPY INTO with incorrect number columns * 3798: SELECT query with INTERSECT causes assertion failure * 3800: LIKE is broken for many patterns * 3802: Disk space never freed: a logical ref is keeped on a deleted BATs * 3803: SQL query parser fails to parse valid SELECT query with a CASE .. END in it. It fails with parser error: identifier 'x' ambiguous * 3804: `monetdb status` command crashes under certain conditions * 3809: Inefficient plan is generated for queries with many (>= 24) joined tables which take a long time or an HEAPalloc error. I get Error: GDK reported error. HEAPalloc: Insufficie * 3810: Fix statistics gathering * 3811: NOT LIKE not working if the operand doesn't contains wildcards. * 3813: COPY INTO fails on perfectly clean CSV file * 3814: Server crash when using bitwise NOT operation in SQL query * 3816: Server crashes when trying to convert timestamp to str with incorrect format * 3818: Crash when performing UNION/GROUP BY over tables with different columns * 3819: order of tables in FROM-clause has negative impact on generated plan (using crossproducts instead of joins) * 3820: mclient accepts table with repeated constraint which causes crash on insert * 3821: Unexpected error when using a number instead of a boolean * 3822: Yet another LIKE operator issue * 3823: JDBC Connection to a schema - setSchema() error * 3825: MonetDB not cleaning intermediate results which leads to filling up disk space and ultimately server crash * 3827: Certains comparisons between UUID produce a MAL error * 3828: Schema corruption after several ALTER TABLE statements and server restart * 3829: Certains simple WHERE clause cause MonetDB to segfault without explanation * 3830: Coalesce typing inconsistencies * 3833: NULL literals refused at many places * 3834: Date comparison returns incorrect results * 3839: msqldump generates incorrect syntax ON UPDATE (null)

1 0

Join optimization
by imad hajj chahine 02 Nov '15

02 Nov '15

Hi, How can we control the algorithm used for the join operation? In the trace i am always getting algebra.leftfetchjoin, what could possibly be done to use merge join or hash join that will be reflected in the trace by algebra.join. Thanks.

4 5

timestamptz conversion
by Robin Cijvat 29 Oct '15

29 Oct '15

Hi all, I am currently extending the NodeJS connector for MonetDB, that communicates with the mserver process using Mapi. However, I face some unexpected behavior when I insert timestamptz values into a table and then retrieve it. This is the Mapi: TX: sCREATE TABLE bar (a timestamptz); RX: &3 TX: sINSERT INTO bar VALUES ('2015-10-29 11:31:35.000000+01:00'); RX: &2 1 -1 TX: sSELECT * FROM bar; RX: &1 0 1 1 1 % sys.bar # table_name % a # name % timestamptz # type % 32 # length [ 2015-10-29 10:31:35.000000+00:00 ] As you can see, inserting the timestamptz string '2015-10-29 11:31:35.000000+01:00', results in it being stored as its equivalent '2015-10-29 10:31:35.000000+00:00'. If I do the same with mclient, this does not happen, and I get the exact same string back. Does anyone know why this might be happening?

2 2

python module monetdblite
by Ben Jeffery 28 Oct '15

28 Oct '15

Hi, I'm aware the embedded "monetdblite" library isn't released yet - but it seems perfect for my use case so am very excited to try it. I've tried compiling the source from the pyapi branch but run into: "ImportError: /usr/local/lib/python2.7/dist-packages/monetdb5/lib/monetdb5/lib_pyapi.so: undefined symbol: batint_dec2_dbl" when I "import monetdblite" on Linux Mint 17 with python 2.7 or 3.4 Any chance of some build instructions for this module? Or do I just need to be patient for the release? :) Ta, Ben

2 4

Selecting database at runtime?
by Dennis Pallett 28 Oct '15

28 Oct '15

Hi all, I'm pretty sure I already know the answer, but is it possible to select the database at runtime instead of "hardcoded" into the connection string? For example, my (JDBC) connection string would only be the MonetDB host (i.e. jdbc:monetdb://192.168.0.1/) and then I'd be able to execute a query to select the right database (e.g. SET DATABASE dbname). Best regards, Dennis

2 1

Change default role of user
by Dennis Pallett 27 Oct '15

27 Oct '15

Hi, Is it possible to change the default role of a user? I want to avoid the step of having to execute the "SET ROLE role" statement before being able to run my queries. Best regards, Dennis Pallett

2 2

Server crash / lib_sql.so
by Mathieu Raillard 26 Oct '15

26 Oct '15

Hi all, We are using MoentDB since 6 months with quite good results.(thanks to the dev team for their good work) Unfortunately yesterday we came across an issue for which we can't find a solution. Our database crashed for an unknown reason (RAM was fine, Disk space was fine) and now each time we re-use the same table with a query we used during the crash, the database crash with a seg fault Query : select * from storage(); OS Version : CentOS release 6.7 (Final) MonetDB Version : MonetDB Database Server Toolkit v1.1 (Jul2015) tail /var/log/messages : kernel: mserver5[9945]: segfault at 18 ip 00007f9e86fd534b sp 00007f9e862912e0 error 4 in lib_sql.so[7f9e86eb1000+182000] merovingian.log : database 'BI-DEV' (9972) was killed by signal SIGSEGV Log from gbd : Program received signal SIGSEGV, Segmentation fault. [Switching to Thread 0x7ffa24edc700 (LWP 4683)] 0x00007ffa25c1f34b in delta_bind_bat () from /usr/lib64/monetdb5/lib_sql.so (gdb) bt #0 0x00007ffa25c1f34b in delta_bind_bat () from /usr/lib64/monetdb5/lib_sql.so #1 0x00007ffa25b20fee in sql_storage () from /usr/lib64/monetdb5/lib_sql.so #2 0x00007ffa321aea45 in runMALsequence () from /usr/lib64/libmonetdb5.so.19 #3 0x00007ffa321afe29 in callMAL () from /usr/lib64/libmonetdb5.so.19 #4 0x00007ffa25b369f7 in SQLexecutePrepared () from /usr/lib64/monetdb5/lib_sql.so #5 0x00007ffa25b36efc in SQLengineIntern () from /usr/lib64/monetdb5/lib_sql.so #6 0x00007ffa321c87dd in runScenarioBody () from /usr/lib64/libmonetdb5.so.19 #7 0x00007ffa321c886f in runScenario () from /usr/lib64/libmonetdb5.so.19 #8 0x00007ffa321c9738 in MSserveClient () from /usr/lib64/libmonetdb5.so.19 #9 0x00007ffa321ca816 in MSscheduleClient () from /usr/lib64/libmonetdb5.so.19 #10 0x00007ffa32266e0f in doChallenge () from /usr/lib64/libmonetdb5.so.19 #11 0x00007ffa31d2adcf in thread_starter () from /usr/lib64/libbat.so.12 #12 0x00007ffa2f97da51 in start_thread (arg=0x7ffa24edc700) at pthread_create.c:301 #13 0x00007ffa2f6ca93d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:115 Any help to understand and correct what is going on would be nice. Regards Mathieu

3 7

overloaded ram on windows
by Anthony Damico 23 Oct '15

23 Oct '15

hi, this is just a general request for the awesome folks at cwi: could you give some serious thought to optimizing ram usage on windows? this happens to me a lot, i end up just leaving these big joins to run overnight-- http://s30.postimg.org/saeo9ca7l/overloaded.png happy to write up some r-driven reproducible examples if there's willingness to combat these bottlenecks. thanks as always from your biggest fan.

1 1

Offset timestamp field with random interval
by Robin Cijvat 21 Oct '15

21 Oct '15

Hello everyone! I am facing some (probably simple) problems. A snippet of a timestamp column in my table: +----------------------------+ | ts | +============================+ | 1595-04-02 00:00:00.000000 | | 1595-04-02 00:00:00.000000 | | 1595-04-02 00:00:00.000000 | | 1595-04-02 00:00:00.000000 | | 1598-03-25 00:00:00.000000 | +----------------------------+ I want to fill all the times in these fields with a random value (uniformly distributed is fine). This faces me with the following problems: 1) Adding an interval to a timestamp does not seem to be suited for dynamic intervals: This works fine: UPDATE "table" SET ts = (ts + INTERVAL '5000' SECOND); This doesn't: UPDATE "table" SET ts = (ts + INTERVAL (CAST(5000 AS STRING)) SECOND); Does someone maybe know of a way to accomplish this? 2) Does someone know how to normalize the output of the RAND operator available in SQL? It seems to calculate a random BIGINT, and in order for me to normalize it, I would need the maximum value of a BIGINT, which of course is platform dependent. Is there a way to get the max value of BIGINT on the SQL level? Or can I go about this another way? All help is much appreciated! Robin

4 11

Presto Connector for MonetDB
by Stephen P. Morgan 20 Oct '15

20 Oct '15

Does anyone know of a Presto connector for MonetDB? There are connectors for MySQL and PostgreSQL among others, but I don't see one for MonetDB. Thanks, Steve

2 1

Transactions
by Doug Service 19 Oct '15

19 Oct '15

We use MonetDB extensively in an internal system we are developing. We initially had issues getting transactions working so we put it off to make progress in other areas. However we are at the point now were we need to have transactions working. The mapi documentation ( https://www.monetdb.org/Documentation/Manuals/SQLreference/Programming/MAPI) says the following about auto commit. MapiMsg mapi_setAutocommit(Mapi mid, int autocommit) Set the autocommit flag (default is on). This only has an effect when the language is SQL. In that case, the server commits after each statement sent to the server. I infered from this statement that using transactions requires setting auto commit to false. However, when I did that I received the following error when I tried to use transactions. MSERVER(25001!START TRANSACTION: cannot start a transaction within a transaction) If I did not disable auto commit, I did not receive any errors when using transactions, but I did not know if the transaction were actually working. After using hg to enlist in the MonetDb code and searching for the error I found this. str SQLtransaction2(Client cntxt, MalBlkPtr mb, MalStkPtr stk, InstrPtr pci) { mvc *sql = NULL; str msg; (void) stk; (void) pci; if ((msg = getSQLContext(cntxt, mb, &sql, NULL)) != NULL) return msg; if ((msg = checkSQLContext(cntxt)) != NULL) return msg; if (sql->session->auto_commit == 0) throw(SQL, "sql.trans", "25001!START TRANSACTION: cannot start a transaction within a transaction"); if (sql->session->active) { RECYCLEdrop(cntxt); mvc_rollback(sql, 0, NULL); } sql->session->auto_commit = 0; sql->session->ac_on_commit = 1; sql->session->level = 0; (void) mvc_trans(sql); return msg; } which leads me to believe that one should not disable auto commit to use transaction as it appears to be auto disabled by the transaction code. My questions are: 1) Is the correct method of using transactions in MonetDb to leave the auto commit flag alone and just issue START TRANSACTION;, COMMIT;, and ROLLBACK; as needed? 2) If the answer to (1) is yes, is disabling auto commit, issuing updates, and then re enabling auto commit like a transaction? Doug Service

3 3

MonetDB not cleaning intermediate results which leads to filling up disk space
by abraun_75＠yahoo.de 19 Oct '15

19 Oct '15

Hi, Right now I am evaluating MonetDB and try to analyze a dataset that consists of 10 million rows. The data set has 2 tables, table a stores 10 million rows, table b stores 1 million rows. Both tables are being joined and some statistical values are being calculated (QUARTILE, AVG, SUM and GROUP BY). MonetDB is promising fast here. When starting up the database and executing the query the result is being returned within 4 seconds. I tried to execute the same SQL query 1000 times sequentially and to my surprise after the 60th iteration monetDB crashed because of insuffcient disk space. As far as I read intermediate results are being materialized within the bat folder. The .tail files are not being removed after a query was executed successfully and the files are not even removed after the database conenction is being closed. When I restart the server (dbfarm) the files are cleaned up perfectly. I am just wondering if this default behaviour can be changed? Are there any ways to enforce cleaning up the files? How to avoid a server crash because of insufficient memory? For the system I am planning to use monetDB for I am not able to restart the server before I execute the SQL queries. Thank you very much, Alex

5 8

current schema
by Enzo 19 Oct '15

19 Oct '15

Thanks to Jennie I understand that I can get the current schema with the query: select current_schema from sys.var(); While I get the “right” response, I get it as a series of duplicated lines (10 at the last count): For example, after setting a schema as set schema viewer; I get the following 10 lines from the query: Is it the right behaviour? And if yes, do these lines have a specific significance? Thanks Enzo enzo(a)smartinsightsfromdata.com

2 1

Re: users-list Digest, Vol 38, Issue 9
by Luciano Sasso 18 Oct '15

18 Oct '15

Jul 2015 is avaible for DEbian 7 ? At --- Este email foi escaneado pelo Avast antivírus. https://www.avast.com/antivirus

3 2

How to install tachograph?
by Xu,Wenjian 18 Oct '15

18 Oct '15

Hi all, I have installed the latest Jul2015 release, and I would like to use the latest tools tachograph to determine the progress of query execution. But I do not know how to install it. My system is ubuntu 14.04. I have tried to use *sudo apt-get install monetdb-tools* but failed to locate the package *monetdb-tools*. Any idea would be helpful. Thanks. Best, XU Wenjian

3 2

DataCell Stream
by Luciano Sasso 18 Oct '15

18 Oct '15

DataCel stream discontinead in new versions MonetDB ? A Idea, substitute for Proxy Node.JS ? --- Este email foi escaneado pelo Avast antivírus. https://www.avast.com/antivirus

3 3

Re: users-list Digest, Vol 38, Issue 9
by Luciano Sasso 18 Oct '15

18 Oct '15

Im Jul 2015, remote tables can sort in result query ? At --- Este email foi escaneado pelo Avast antivírus. https://www.avast.com/antivirus

2 1

MonetDB scripting
by Doug Service 18 Oct '15

18 Oct '15

Hello Everyone, I need to become more conversant in monetdb scripting and have read the pages on functions and procedures on the website. However, more examples would be very helpful. Does anyone know of a good repository of extensive monetdb scripts that provide good models. Doug Service

2 1

Concurrent Transaction control in MonetDB
by Vijay Krishna 18 Oct '15

18 Oct '15

Hi, I read the following lines in a thesis on MonetDB titled - "Improving Transactional Stability in MonetDB" where in they had explained the concurrency control in MonetDB as "Concurrent updates (writers) are not anticipated and in case of N con􏰃icting transactions, only one of them (the one having the earliest time-stamp) is guaranteed to eventually commit its changes. Concurrent appends (on the same table) are detected as write-to-write conflicts and only the writer carrying the earliest time-stamp will end up committing successfully, the rest will be aborted." Why is that the transaction having the earliest timestamp is allowed to commit, in case of a conflict? Shouldn't it be the latest transaction that should commit on a data, since the latest transaction would be having the latest version of data? Or, what am I getting wrong here? Any help much appreciated. Thanks & Regards, Vijayakrishna.P. Mobile : (+91) 9500402305.

1 0