- 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

Plan for feature, external REMOTE table as a plugin (hadoop, jdbc, odbc, etc).
by Aris Setyawan 17 Oct '15

17 Oct '15

Hi, The key value of monetdb is it's parallel query. That many other opensource DBMS doesn't support it. But monetdb have it in the core engine. It is mean a mature feature. Having ability load (on the fly) any other DBMS data using external REMOTE table, and then query them using monetdb parallel query, will attract big community and contribution (as a plugin). The dbms, for example: jdbc, odbc, mysql, postgresql, hadoop, hive, or any commercial dbms. Any dbms which doesn't have parallel query feature, will benefit from this. Any plan for this feature? Regards, Aris

1 0

SQL join involving remote table.
by Aris Setyawan 17 Oct '15

17 Oct '15

Hi, According to https://www.monetdb.org/Documentation/Cookbooks/SQLrecipes/DistributedQuery… It is said that, "Queries involving remote tables are automatically split into subqueries by the master database and executed on remote databases.". I think this scenario: I have 3 server, one master server and two worker servers. I have 2 tables, A (big one, a fact table) and B (small one, a dimension table). A is a merge table of 2 remote tables. The remote table exist in two worker servers. B is a dimension table, that I'm not sure, on which server, I should place it on. And I will do queries, that join A and B. On which server, I should place table B to achieve distributed query processing? Is table B should be replicated on all remote table? Thanks, -Aris

2 2

JDBC Connection to a schema
by Vijay Krishna 16 Oct '15

16 Oct '15

Hi, I am trying to connect a schema within a database directly through JDBC with the following Java code. Connection con = DriverManager.getConnection( > "jdbc:monetdb://localhost/testUser", "monetdb", "monetdb"); > con.setSchema("schema1"); But this throws the following exception. > Exception in thread "main" java.sql.SQLException: syntax error, > unexpected '=' in: "set schema =" > at nl.cwi.monetdb.jdbc.MonetConnection$ResponseList.executeQuery( > MonetConnection.java:2525) > at nl.cwi.monetdb.jdbc.MonetConnection$ResponseList.processQuery( > MonetConnection.java:2273) > at nl.cwi.monetdb.jdbc.MonetStatement.internalExecute( > MonetStatement.java:497) > at nl.cwi.monetdb.jdbc.MonetStatement.execute(MonetStatement.java:338) > at nl.cwi.monetdb.jdbc.MonetStatement.executeUpdate( > MonetStatement.java:533) > at nl.cwi.monetdb.jdbc.MonetConnection.setSchema(MonetConnection.java:1267 > ) > at batcave.Monet.main(Monet.java:11) Is it really possible to connect to a specific schema? Or the only option is to query as "schemaName.tableName" ??? Thanks & Regards, Vijayakrishna.P. Mobile : (+91) 9500402305.

3 3

Baseline Performance Expectation?
by Paul Ramsey 15 Oct '15

15 Oct '15

Hi all, I’ve loaded a relatively small table (I think) of 30M records, 7 columns, and I’m doing some test queries summarizing and filtering the table, like this: select vendor_id, count(*) from taxi where geohash like '90efbc6a6fc%’ group by vendor_id; On my 4-core iMac, the CPU doesn’t get very hot (20% on top, so it’s not even lighting up one of the four CPUs) , but it’s hard to tell since the return time is only 2.5 second. Is that as fast as things will get? I doubled the table size and query times more-or-less doubled. I was just hoping to get query times in the < 1s range. Thanks, P -- Paul Ramsey Solutions Engineer CartoDB—Map Your World's Data 143 Roebling St 2A, New York, 11211, USA T: +1.917.463.3232 Skype: pwramsey3 W: www.cartodb.com Live sessions to learn CartoDB

2 1

Finding the current schema
by Enzo 14 Oct '15

14 Oct '15

Is there a query that would let me know what is the current schema in my db? E.g. I would expect as a response either sys (if I haven't set any schema), or foo (if I've set the schema foo). Kindest regards Enzo enzo(a)smartinsightsfromdata.com

1 0

Support for UUIDs in R and Python
by Doug Service 09 Oct '15

09 Oct '15

Hello everyone, I discovered this morning that the UUID type is not supported in MonetDB.R package on CRAN Error in monetdbRtype(info$types[i]) : Unknown DB type UUID and UUIDs are not support in the Python API which I installed from source File "/usr/local/lib/python3.4/dist-packages/monetdb/sql/pythonize.py", line 132, in convert raise ProgrammingError("type %s is not supported" % type_code) monetdb.exceptions.ProgrammingError: type uuid is not supported Will UUIDs be supported in R and Python in a future release? We use R extensively for statistical analysis on our data and would also like to use Python in certain cases. Doug

1 0

Re UUID and new crash problem
by Doug Service 09 Oct '15

09 Oct '15

Hello Stefan, I modified the uuid.mal file in the lib directory and that all works great for a small sample test from mclient. Thanks! Now I have run into a problem where the server crashes when I try to insert into one of the tables, though inserts in other tables work fine. I do not think this problem has anything with UUIDS as the table I am inserting into does not use UUIDS and I can do inserts into tables that have uuid columns. This insert that causes the crash worked fine on the last version of our schema that did not use UUIDs. I have a small database from msqldump and an insert statement that causes the server to crash consistently. However, I do not see any way to add attachments to a bug report on the tracker. Is there a way to do this? Is there any other information I can collect that would be useful in a bug report? Here is the monetdb log which shows a sigsegv. Doug 2015-10-06 16:02:19 MSG quantbox[3433]: # MonetDB 5 server v11.21.5 "Jul2015" 2015-10-06 16:02:19 MSG quantbox[3433]: # Serving database 'quantbox', using 4 threads 2015-10-06 16:02:19 MSG quantbox[3433]: # Compiled for x86_64-pc-linux-gnu/64bit with 64bit OIDs and 128bit integers dynamically linked 2015-10-06 16:02:19 MSG quantbox[3433]: # Found 1.907 GiB available main-memory. 2015-10-06 16:02:19 MSG quantbox[3433]: # Copyright (c) 1993-July 2008 CWI. 2015-10-06 16:02:19 MSG quantbox[3433]: # Copyright (c) August 2008-2015 MonetDB B.V., all rights reserved 2015-10-06 16:02:19 MSG quantbox[3433]: # Visit http://www.monetdb.org/ for further information 2015-10-06 16:02:19 MSG quantbox[3433]: # Listening for UNIX domain connection requests on mapi:monetdb:///var/lib/monetdb/quantbox/.mapi.sock 2015-10-06 16:02:19 MSG quantbox[3433]: # Start processing logs sql/sql_logs version 52200 2015-10-06 16:02:19 MSG quantbox[3433]: # Start reading the write-ahead log 'sql_logs/sql/log.4' 2015-10-06 16:02:19 MSG quantbox[3433]: # Finished reading the write-ahead log 'sql_logs/sql/log.4' 2015-10-06 16:02:19 MSG quantbox[3433]: # Finished processing logs sql/sql_logs 2015-10-06 16:02:19 MSG quantbox[3433]: # MonetDB/SQL module loaded 2015-10-06 16:02:19 MSG merovingian[809]: proxying client 10.1.10.101:40633 for database 'quantbox' to mapi:monetdb:///var/lib/monetdb/quantbox/.mapi.sock?database=quantbox 2015-10-06 16:02:19 MSG merovingian[809]: target connection is on local UNIX domain socket, passing on filedescriptor instead of proxying 2015-10-06 16:05:19 MSG merovingian[809]: proxying client (local) for database 'quantbox' to mapi:monetdb:///var/lib/monetdb/quantbox/.mapi.sock?database=quantbox 2015-10-06 16:05:19 MSG merovingian[809]: target connection is on local UNIX domain socket, passing on filedescriptor instead of proxying 2015-10-06 16:05:27 MSG merovingian[809]: proxying client (local) for database 'quantbox' to mapi:monetdb:///var/lib/monetdb/quantbox/.mapi.sock?database=quantbox 2015-10-06 16:05:27 MSG merovingian[809]: target connection is on local UNIX domain socket, passing on filedescriptor instead of proxying 2015-10-06 16:05:48 MSG merovingian[809]: database 'quantbox' (3433) was killed by signal SIGSEGV 2015-10-06 16:05:59 MSG merovingian[809]: starting database 'quantbox', up min/avg/max: 0s/0s/0s, crash average: 1.00 0.30 0.10 (3-0=3) 2015-10-06 16:06:00 MSG quantbox[3532]: arguments: /usr/bin/mserver5 --dbpath=/var/lib/monetdb/quantbox --set merovingian_uri=mapi:monetdb://qbdb:42000/quantbox --set mapi_open=false --set mapi_port=0 --set mapi_usock=/var/lib/monetdb/quantbox/.mapi.sock --set monet_vault_key=/var/lib/monetdb/quantbox/.vaultkey --set gdk_nr_threads=4 --set max_clients=64 --set sql_optimizer=default_pipe --set monet_daemon=yes

3 4

Problems implementing java jdbc
by Federico G Agripa 09 Oct '15

09 Oct '15

Hi, i'm having some issues implementing the java jdbc with a grails-app. Grails is a framework that runs on the jvm, allowing you to write all your code in java if you want. I'm using exactly the code you provide on your web page but it isn't recognizing a proper driver to connect to monet at runtime. I also tried to search some repo that provides a plugin or extension that allows me to use but i get the same error (the only one i found was www.clojars.com/monetdb) is there any example of a driver implementation you can provide or any forum to forward this? Thanks in advice

3 3

Grails integration
by Federico G Agripa 09 Oct '15

09 Oct '15

Hello, i'm working on a grails plugin to easily integrate with monetDB. So I would like to here what functionalities will be useful to you guys because I want it to be useful to all of you to. I'm implementing it from the monet jar 2.17 and I hope to have it up in grails plugins as soon as possible. Btw any collaboration will be really welcome Regards

1 0

Regex support
by imad hajj chahine 08 Oct '15

08 Oct '15

Hi, Monetdb is using pcre library for regular expression support, and using this function we can allow SQL to use the regex library: CREATE FUNCTION "pcre_replace" (origin string, pat string, repl string,flags string) RETURNS string EXTERNAL NAME pcre."replace"; I want to use regex for date conversion to the format supported by monetdb (yyyy-MM-dd), I have the following format dd/MM/yyyy, and i am trying to run the following with no success: select pcre_replace('15/10/2015', '^(3[01]|[12][0-9]|0?[1-9])/(1[012]|0?[1-9])/([0-9]{4})$', '\\3-\\2-\\1', 'i') select pcre_replace('15/10/2015', '^(3[01]|[12][0-9]|0?[1-9])/(1[012]|0?[1-9])/([0-9]{4})$', '{3}-{2}-{1}', 'i') select pcre_replace('15/10/2015', '^(?<day>3[01]|[12][0-9]|0?[1-9])/(?<month>1[012]|0?[1-9])/(?<year>[0-9]{4})$', '\\k<year>-\\k<month>-\\k<day>', 'i') My question is, is backreference supported in the replacement string; if yes, whats is the right syntax to use to reference the detected groups, if no, is there a clever way then using substring? Thanks

3 6

windows function
by imad hajj chahine 07 Oct '15

07 Oct '15

Hi, Since monetdb support window RANK functions, is it possible to extend the sql library to support also window aggregate function like SUM, AVG ... useful for running total and moving average. Any plan to support this in the near future or the implementation is easy to be done with user defined functions. Thanks.

2 4

UUIDs in MonetDB
by Doug Service 01 Oct '15

01 Oct '15

The documentation at https://www.monetdb.org/Documentation/Manuals/SQLreference/UUItype indicates that a UUID type is supported in MonetDB and gives the example select uuid(); +--------------------------------------+ | uuid | +======================================+ | 65950c76-a2f6-4543-660a-b849cf5f2453 | +--------------------------------------+ which appears to output from mclient. When I try the same with mclient I receive auto commit mode: on sql>select uuid(); SELECT: no such operator 'uuid' mapi.h only shows support for the following types #define MAPI_AUTO 0 /* automatic type detection */#define MAPI_TINY 1#define MAPI_UTINY 2#define MAPI_SHORT 3#define MAPI_USHORT 4#define MAPI_INT 5#define MAPI_UINT 6#define MAPI_LONG 7#define MAPI_ULONG 8#define MAPI_LONGLONG 9#define MAPI_ULONGLONG 10#define MAPI_CHAR 11#define MAPI_VARCHAR 12#define MAPI_FLOAT 13#define MAPI_DOUBLE 14#define MAPI_DATE 15#define MAPI_TIME 16#define MAPI_DATETIME 17#define MAPI_NUMERIC 18 Is it possible to get MonetDB to generate UUIDs and how is the type supported in mapi. Thanks Doug Service

4 8

R aggregation fails when input is exactly 200K tuples
by Roberto Cornacchia 01 Oct '15

01 Oct '15

Hi, I have a suspect failure at exactly 200K tuples in input. I declared a simple aggregate on two columns (here it doesn't aggregate, for simplicity, it just returns 42). START TRANSACTION; CREATE table test (customer int, d string, n int); INSERT INTO test VALUES(1,'2015-01-01', 100); INSERT INTO test VALUES(1, '2015-01-02', 100); INSERT INTO test VALUES(2, '2015-01-03', 100); INSERT INTO test VALUES(2, '2015-01-01', 100); INSERT INTO test VALUES(2, '2015-01-02', 100); CREATE AGGREGATE sow(d string, n int) RETURNS DOUBLE LANGUAGE R { # aggregation function is constant, not important here sow_aggr <- function(df) { 42.0 } df <- cbind(d,n) as.vector(by(df, aggr_group, sow_aggr)) }; select customer, sow(d,n) from test group by customer; ROLLBACK; +----------+--------------------------+ | customer | L1 | +==========+==========================+ | 1 | 42 | | 2 | 42 | +----------+--------------------------+ The result is what I had expected. That is true until table test is long 199999 tuples. When it's exactly 200000 tuples, I get: Error running R expression. Error message: Error in tapply(seq_len(200000L), list(INDICES = c(0, 1, 2, 3, 4, 5, 6, : arguments must have same length Calls: as.data.frame ... by.data.frame -> structure -> eval -> eval -> tapply I checked the vector aggr_group, and indeed it is not 200000 long, as it should be. Instead, it is just one longer than then number of distinct values for customer (the grouping column). Any thought? Roberto

2 3

COPY INTO string attribute containing single quote, succeed in Jan2014-SP2, fail in Jul2015
by Constantine Feng 30 Sep '15

30 Sep '15

Dear all, I am trying to bulk load the TPC-DS database benchmark into MonetDB. One of the data file "customer.csv" contains data in this format: 27|AAAAAAAALBAAAAAA|111621|835|16096|2450611|2450581|Dr.|Shawn|Prather|N|7|3|1972|PUERTO RICO||Shawn.Prather(a)8BusRYegn6.org|2452353 28|AAAAAAAAMBAAAAAA|889961|897|183480|2452615|2452585|Miss|Edith|Hernandez|Y|17|8|1959|CÔTE D'IVOIRE||Edith.Hernandez(a)BNHL0k.com|2452631 Note in the second row, there is a string attribute containing a single quote. And I use the below command to load it: COPY INTO customer from 'path/to/customer.csv' USING DELIMITERS '|','\n' NULL AS ''; I have two machines with two installations: one installed last year via Ubuntu apt-get, "monetdb version" shows "MonetDB Database Server Toolkit v1.1 (Jan2014-SP2)" the other recently follows the instructions here: https://www.monetdb.org/downloads/deb/ (Ubuntu), "MonetDB Database Server Toolkit v1.1 (Jul2015)" Somehow the loading succeeded in the former but failed in the latter. Is it due to some version difference? How can I fix it? Error: field 15 expecting varchar(15) 'CÔTE D'IVOIRE' Thanks in advance. - Neo

2 1

Strange characters with ODBC driver
by Dennis Pallett 28 Sep '15

28 Sep '15

Hi all, We're trying to use MonetDB together with SAP Business Objects using the ODBC driver but we're getting some strange results when using the table browser. The names of the schema's seem to be all mangled up (see attached screenshot). Any ideas what might be wrong? It works fine with the JDBC driver btw. Any advantages/disadvantages with both drivers? Best regards, Dennis Pallett

2 2

Version vs release naming schemes
by Eyal Rozenberg 28 Sep '15

28 Sep '15

What's the relation between the Month+Year release naming scheme (e.g. Jul2015, Oct2014-SP2) and the numeric versions (e.g. 11.19.9)? The website has a release history page: https://www.monetdb.org/Documentation/UserGuide/ReleaseNotes But I haven't found a page discussing the numeric version numbers. I'm not even sure the X.Y.Z format means version.subversion.subsubversion ... Eyal

2 1

Jul2015 not availbale for debian Wheezy
by imad hajj chahine 28 Sep '15

28 Sep '15

Hi, I am trying to upgrade my installation on debian from 11.19 to 11.21, i cant find the Jul2015 in the https://www.monetdb.org/downloads/deb/dists/wheezy/monetdb/binary-amd64/ and the apt-get is pointing to the 11.19.15 version, any idea how to get the latest release. Thank you

2 1

Issue importing certain character combinations
by andy.barlow＠datum360.com 25 Sep '15

25 Sep '15

I filed a bug earlier, the gist of which is below but perhaps there is some setting or flag I am missing when importing? If your import file contains a number of character combinations, such as \560, \561, \700 and probably lots of others, the copy into fails with a message like: Failed to import table line 5 field 1 'varchar(20)' expected in 'FIVE\700' Reproducible: Always Steps to Reproduce: 1. create table test (test varchar(20)); 2. create a load file test.txt with the following: ONE TWO THREE FOUR FIVE\700 3. copy into test from 'test.txt' using delimiters '\t','\n'; 4. fails with message: Failed to import table line 5 field 1 'varchar(20)' expected in 'FIVE\700' Actual Results: Failed to import table line 5 field 1 'varchar(20)' expected in 'FIVE\700' Expected Results: should import OK, the character combinations are valid Andy Barlow Datum360

1 1

Re: Disk space running out
by scArcer 24 Sep '15

24 Sep '15

"create table" and "insert data" are separate. And the disk is consumed by queries. because when I finished data loading, it was, only 3~4G. Thank you for the link. I will try restart server to see if monetdb can unlink these files too. I execute queries in auto commit mode, should I control transactions myself? ------------------ Original ------------------ From: "Guillaume Savary";<guillaume.savary(a)securactive.net>; Date: Thu, Sep 24, 2015 02:30 PM To: "Communication channel for MonetDB users"<users-list(a)monetdb.org>; Cc: "scArcer"<53308768(a)qq.com>; Subject: Re: Disk space running out Hi, Are you repeating this "create table + insert data" multiple times on this disk? Because there is a bug on the BAT files which are never freed : https://www.monetdb.org/bugzilla/show_bug.cgi?id=3802 Work around : try to do the "create table" in an other transaction than the "insert data". Le 24/09/2015 08:17, scArcer a écrit : Here is my situation. I create a table A, load about 3~4G data into it. Then a lot of queries are executed, making it using disk up to 1T. [hadoop@falco001-bi-30-141 data3]$ du -sh monetdb/ 1.1T monetdb/ and when I try to execute new query, I got this error: sql>select * from ods.attr_user_c_profile limit 10; could not allocate space for GDKsave: error -1 on: name=06/62/66261, ext=theap, mode=0 !OS: No space left on device HEAPextend: failed to extend to 983040 for 06/62/66261.theap: h->storage == STORE_MEM && can_map && fd >= 0 && HEAPsave_intern() < 0 ------------------ Original ------------------ From: "Martin Kersten";<martin.kersten(a)cwi.nl>; Date: Thu, Sep 24, 2015 02:03 PM To: "users-list"<users-list(a)monetdb.org>; Subject: Re: Disk space running out On 24/09/15 06:04, scArcer wrote: > Hi, I'm facing the problem of disk space running out. > Since monetdb will store the query intermediates into the disk when the memory is not enough to hold them all, I'm wondering that does monetdb will automatically free the disk space? > And if it doesn't, how can I do it manually? > > Hi It will release disk space as soon as possible. > _______________________________________________ > 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 _______________________________________________ users-list mailing list users-list(a)monetdb.org https://www.monetdb.org/mailman/listinfo/users-list -- Guillaume Savary Securactive R&D

1 0

Disk space running out
by scArcer 24 Sep '15

24 Sep '15

Hi, I'm facing the problem of disk space running out.Since monetdb will store the query intermediates into the disk when the memory is not enough to hold them all, I'm wondering that does monetdb will automatically free the disk space? And if it doesn't, how can I do it manually?

3 4

monetdb cluster on EC2
by imad hajj chahine 23 Sep '15

23 Sep '15

Hi, As per this link https://www.monetdb.org/pipermail/users-list/2013-August/006744.html, to be able to have monetdb cluster to work in ec2, a VPN must be used to connect the nodes. Following this link: https://www.buckhill.co.uk/blog/how-to-enable-broadcast-and-multicast-on-am…, i configured the VPN between the cluster nodes. Now for each node I have the eth0 and edge0 network interface, when i start monetdbd it directly bind to the eth0 interface, thats why i suppose the discover is not working. Any idea how to start monetdbd and bind to the interface used for VPN to be able to use TCP broadcast hence discover the other nodes. Any help will be appreciated. Thank You.

1 0

if i run these commands in R, mserver dies.. any ideas how to fix? thanks
by Anthony Damico 22 Sep '15

22 Sep '15

# MonetDB 5 server v11.21.3 "Jul2015" # Serving database 'marketscan', using 4 threads # Compiled for x86_64-pc-winnt/64bit with 64bit OIDs dynamically linked # Found 64.000 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 connection requests on mapi:monetdb://127.0.0.1:49700/ # Start processing logs sql/sql_logs version 52200 # Start reading the write-ahead log 'sql_logs\sql\log.211' # Finished reading the write-ahead log 'sql_logs\sql\log.211' !ERROR: log_sequence_: write failed # Finished processing logs sql/sql_logs # MonetDB/SQL module loaded > ========================= > pid <- monetdb.server.start( batfile ) > dbname <- "marketscan" > dbport <- 49700 > monet.url <- paste0( "monetdb://localhost:" , dbport , "/" , dbname ) > db <- dbConnect( MonetDB.R() , monet.url , wait = TRUE ) Server not ready(cannot open the connection), retrying (ESC or CTRL+C to abort) > dbListTables(db) Error in .local(conn, statement, ...) : Unable to execute statement 'select schemas.name as sn, tables.name as tn from sys.tables join sys.schemas on tables.schema_id=sc...'. Server says 'ERROR: BBPdir_subcommit: Syncing BBP.dir file failed' [#GDKerror:]. > dbListTables(db) Error in .local(conn, statement, ...) : Unable to execute statement 'select schemas.name as sn, tables.name as tn from sys.tables join sys.schemas on tables.schema_id=sc...'. Server says 'ERROR: GDKsave: error on: name=06\651, ext=timprints, mode=0' [#GDKerror:]. > dbListTables(db) [1] "ccaea" "ccaed" "ccaef" "ccaei" "ccaeo" [6] "ccaep" "ccaes" "ccaet" "redbook" "state" [11] "wgt" "ccaea_st_wt" "ccaet_st_wt" "ccaes_monthly" "ccaes_annual" [16] "ccaeo_monthly" "ccaeo_annual" "ccaed_monthly" "ccaed_annual" "ccaes_admmon_xwalk" [21] "ccaes_monthly_wxw" "ccaed_svcmon_xwalk" "ccaed_monthly_wxw" "ccaeo_svcmon_xwalk" "ccaeo_monthly_wxw" [26] "acons_pre" "mcons_pre" "ccs" "acons_diag" "mcons_diag" [31] "readm" "acons_readm" "mcons_readm" "mcons" "acons" > > > # create a temporary person-year table > dbSendQuery( + db , + 'CREATE TABLE temp AS + SELECT + datatyp , + yr , + enrolid , + SUM( wgt ) AS sum_wgt , + SUM( CAST( ( rx_pay ) > 250 AS INTEGER ) ) AS rx_pay_above_250 , + SUM( CAST( ( rx_copay + rx_coins + rx_deduct ) > 250 AS INTEGER ) ) AS rx_copay_coins_deduct_above_250 + FROM + mcons + WHERE + datatyp = 1 + GROUP BY + datatyp , yr , enrolid + ORDER BY + datatyp , yr , enrolid + WITH DATA' + ) Error in readBin(con, "integer", n = 1, size = 2, signed = FALSE, endian = "little") : error reading from the connection > # # # # mserver is dead

2 2

Monetdb clusters VS Distributed Query Processing
by imad hajj chahine 22 Sep '15

22 Sep '15

Hi, I am trying to understand the main difference between clusters and the remote table introduced in JUL2015 . Correct me if I am wrong, in clusters the complete table is copied over all nodes, and monetdb will do the query parallel execution on the nodes and result concat. In the remote table I have more control on executing the query on a part of a table on a selected node depending on resources availables. If this is true, are the following point valid: 1. its better to have identical nodes for clusters 2. the execution time is inversely proportional to the number of nodes in the clusters 3. the executing time is dictated by the slowest node in cluster 4. fail-over is by default supported in cluster configuration One last question about the supported queries in these 2 modes(groupby, order, limit). if any, a reference to a documentation about the clustering is appreciated. Thank you.

2 1

Two questions about R integration
by Roberto Cornacchia 22 Sep '15

22 Sep '15

Hi, I have 2 questions about R integration. 1) I see that missing packages are automatically installed when invoked by library() calls. That is really cool. The question: if I need a library in a function, will this actually be loaded at every function call? 2) How is a SQL type date translated to R? Apparently not "Date", because I tried to use it as such in the declared procedure and I got a complaint. I tried to figure the type myself with this: sql> create function dt(d date) returns string language R { class(d) }; sql> select dt( cast('2015-09-21' as date) ); but unfortunately this triggered a server assertion :/ mserver5: .../src/monetdb5/mal/mal_interpreter.c:648: runMALsequence: Assertion `t == stk->stk[a].vtype' failed. Thanks, Roberto

2 2

sys.analyze not working
by Robert Koch 19 Sep '15

19 Sep '15

Following the manual https://www.monetdb.org/Documentation/Cookbooks/SQLrecipes/statistics I am trying to reproduce this, but it fails with a type conversion error: sql>call sys.analyze('sys', 'types'); conversion of string 'sys' to type int failed. sql>SELECT * FROM sys.statistics; +-----------+------+-------+-------+--------+-------+--------+------+--------+--------+ | column_id | type | width | stamp | sample | count | unique | nils | minval | maxval |> +===========+======+=======+=======+========+=======+========+======+========+========+ +-----------+------+-------+-------+--------+-------+--------+------+--------+--------+ 0 tuples (3.013ms) !1 column dropped! Tried it with different schema/table/column names. Both with system and user-generated tables. Always the same error. Running 11.21.5 here. Thanks.

3 2

couple of unanswered qs
by Anthony Damico 18 Sep '15

18 Sep '15

https://stackoverflow.com/questions/tagged/monetdb

1 0

Why doesn't MonetDB exploit SIMD feature?
by Xu,Wenjian 18 Sep '15

18 Sep '15

Hi, We know that MonetDB excels in application where the database hot-set can be largely held in main-memory, since the physical operators (e.g., hashjoin, sort) are highly optimized for main-memory. But it is strange to me that these operators do not utilize SIMD which a performance-critical feature of modern CPUs. For example, MonetDB use *timsort* as its underlying stable sort algorithm (do_ssort() in gdk/gdk_ssort_impl.h), but I cannot find any SIMD instructions there. Why doesn't MonetDB exploit SIMD feature? or did I miss something? In paper 'Vectorwise: Beyond Column Stores', the authors claim that X100/Vectorwise use SIMD instructions. So my follow-up question is that whether any of X100/Vectorwise's technologies/features has been integrated into MonetDB's code base. Thank you. Best regards, Xu, Wenjian

2 1

Encrypted Client Sessions
by James Candalino 17 Sep '15

17 Sep '15

Hi all, Is it possible to establish encrypted client sessions? If so, how? I didn't see anything in the documentation. With postgres connecting using jdbc is just jdbc:postgresql://dbhost/dbname?ssl=true. Any insight would be greatly appreciated. --James -- *NOTICE TO RECIPIENTS*: This communication is confidential and intended for the use of the addressee only. If you are not an intended recipient of this communication, please delete it immediately and notify the sender by return email. Unauthorized reading, dissemination, distribution or copying of this communication is prohibited. This communication does not constitute an offer to sell or a solicitation of an indication of interest to purchase any loan, security or any other financial product or instrument, nor is it an offer to sell or a solicitation of an indication of interest to purchase any products or services to any persons who are prohibited from receiving such information under applicable law. The contents of this communication may not be accurate or complete and are subject to change without notice. As such, Orchard App, Inc. (including its subsidiaries and affiliates, "Orchard") makes no representation regarding the accuracy or completeness of the information contained herein. The intended recipient is advised to consult its own professional advisors, including those specializing in legal, tax and accounting matters. Orchard does not provide legal, tax or accounting advice.

1 0

Accessing admin GUI
by Peter Smith 17 Sep '15

17 Sep '15

Hi, https://www.monetdb.org/XQuery/Tutorials/adminGUI It looks as if the admin GUI should automatically start up on 50001/TCP when I start MonetDB, but this doesn't seem to be the case: I have monetdb listening on 50000, but nothing listening on 50001. I'm running MonetDB 11.19 on Ubuntu. I'm using an init.d script to start MonetDB, and here's the running process: /usr/bin/mserver5 --dbpath=/home/administrator/monetdb/xxx/xxx --set merovingian_uri mapi:monetdb://127.0.0.1:50000/xxx --set mapi_open false --set mapi_port 0 --set mapi_usock /home/administrator/monetdb/xxx/xxx/.mapi.sock --set monet_vault_key /home/administrator/monetdb/xxx/xxx/.vaultkey --set gdk_nr_threads 16 --set max_clients 64 --set sql_optimizer default_pipe --set monet_daemon yes Do I need to pass extra start-up options to enable the GUI? (I don't see anything in the mserver5 man page) Thanks, Peter Smith

2 1

What is the difference ?
by Brian Hood 16 Sep '15

16 Sep '15

Hi All, So the new CREATE REPLICA TABLE definition / CREATE TABLE definition i'm not sure what the difference would be ? CREATE REPLICA TABLE test2 AS (SELECT * FROM test) WITH DATA; CREATE TABLE test2 AS (select * from test) WITH DATA; Regards, Brian Hood

2 1

Inserting in 'raw' mode??
by Vijay Krishna 15 Sep '15

15 Sep '15

Hi, I was analysing the Insert performance of MonetDB... I am trying to optimize a process where in a single tuple has to be inserted into a table of 1200 columns, which takes about 7.5 seconds presently... The query has values for only 5 columns out of 1200, yet the speed is still the same.. Is it possible to reduce this time even?? While digging on this, I came across this thread.. https://www.monetdb.org/pipermail/users-list/2005-August/000139.html *What is the insert speed relatively to other DBs? > * > Using SQL, with the current version we won't compete very well. We get > much more acceptable speeds in 'raw' mode, where lots of the parsing > overhead is removed. > > Can anyone please help me understand what is the 'raw' mode that is explained in this thread?? I am using Oct-2014 version on a CentOS machine with 128 GB RAM... Thanks & Regards, Vijayakrishna.P. Mobile : (+91) 9500402305.

3 2

DbVisualizer support for MonetDB
by Robert Koch 14 Sep '15

14 Sep '15

Hi Folks, DbVisualizer is my preferred GUI to MonetDB. DbVis offers capability to write own database profiles for specific DB's. Today I committed a MonetDB database profile for DbVisualizer. https://github.com/dbvisualizer/dbvis-database-profiles/tree/master/monetdb Anyone familiar to DbVis can import and/or improve this profile. There's a free DbVis version, but the Pro offers much more features, which make life easier. BTW, I'm not working for them. :-) Please feel free to write me your desired features by mail or directly into the MD-file inside the monetdb-directory. If possible, I will implement it inte the profile. Best regards Mario

1 0

`/usr/bin/mserver5': double free or corruption
by Brian Hood 10 Sep '15

10 Sep '15

Hi Guys, I created a brand new directory for my dbfarm yesterday and as you can see everything was fine for 24 hours then my merovingian.log shows an error. My Compile options are: ./configure --libdir=/usr/lib64 --prefix=/usr --sysconfdir=/etc --localstatedir=/var --disable-assert \ --disable-testing --enable-optimize --enable-rintegration --enable-jsonstore http://pastebin.com/qAebcMhp I'm currently only inserting to one table. DROP TABLE "threatmonitor".http_traffic_json; CREATE TABLE "threatmonitor".http_traffic_json ( id INT GENERATED ALWAYS AS IDENTITY ( START WITH 0 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 2 CYCLE ) primary key, guid char(36), recv_date date, recv_time time, json_data JSON ); Typical insert INSERT INTO http_traffic_json (recv_time,recv_date,guid,json_data) VALUES (NOW(), NOW(), '1f248d6e-ed15-2a3e-a08f-96536ed925d0', '{"http":{"host":"ad.sxp .smartclip.net","connection":"keep-alive","cachecontrol":"max-age=0","accept":"image/webp,*/*;q=0.8","useragent":"Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/4 2.0.2311.90 Safari/537.36","referer":" http://cdn.turn.com/server/ddc.htm?uid=8915259122209287941&rnd=246995741890…","acceptencoding":"gzip, defla te, sdch","acceptlanguage":"en-GB,en-US;q=0.8,en;q=0.6","cookie":"ommited"}}'); I will look more at this tomorrow and try and reproduce. Regards, Brian Hood

1 0

Issue upgrading from 11.17.9 to 11.21.5
by Brian Hood 09 Sep '15

09 Sep '15

Hi All, I am unsure what to do in this scenario. Can anyone help ? http://pastebin.com/LwLCLeAB 1. 2015-09-08 13:40:07 MSG threatmonitor[31747]: # Listening for UNIX domain connection requests on mapi:monetdb:///data2/threatmonitor/threatmonitor/.mapi.sock 2. 2015-09-08 13:40:07 MSG threatmonitor[31747]: # Start processing logs sql/sql_logs version 52200 3. 2015-09-08 13:40:07 ERR threatmonitor[31747]: !FATAL: Incompatible database version 052001, this server supports version 052200. 4. 2015-09-08 13:40:07 ERR threatmonitor[31747]: Maybe you need to upgrade to an intermediate release first. 5. 2015-09-08 13:40:07 ERR threatmonitor[31747]: 6. 2015-09-08 13:40:07 MSG merovingian[31739]: database 'threatmonitor' (31747) has exited with exit status 1 7. 2015-09-08 13:40:07 ERR control[31739]: (local): failed to fork mserver: database 'threatmonitor' appears to shut itself down after starting, check monetdbd's logfile for possible hints Regards, Brian Hood

2 5

Announcement: New Jul2015 Feature release of MonetDB suite
by Sjoerd Mullender 09 Sep '15

09 Sep '15

The MonetDB team at CWI/MonetDB BV is pleased to announce the Jul2015 feature 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 feature release MonetDB5 Server * Implemented batcalc.min and batcalc.max. Made calc.min and calc.max generic so that no other implementations are needed. * Removed function batcalc.ifthen. Build Environment * Upgraded the license to the Mozilla Public License Version 2.0. Client Package * Added a new output format to mclient: --format=expanded (or -fx). In this format, column values are shown in full and below each other. MonetDB Common * BUNtvar and BUNhvar macros no longer work for TYPE_void columns. * Changed interfaces of a lot of GDK-level functions. When they modify a BAT, don't return the same BAT or NULL, but instead return GDK_SUCCEED or GDK_FAIL. * Changed a bunch of hash-related functions to work on the tail column. The functions that have been changed to work on the tail column are: BAThash, BATprepareHash, HASHgonebad, HASHins, and HASHremove. Java Module * Improved JDBC driver to not throw NullPointerException anymore when calling isNullable() or getPrecision() or getScale() or getColumnDisplaySize() or getSchemaName() or getTableName() or getColumnClassName() on a ResultSetMetaData object. * We now compile the Java classes using the latest Java 1.8 version, and we tell it to compile for Java 1.7. SQL * Differences between time, timestamp, and date values now return properly typed interval types (second or month intervals) instead of integers. * Added support for 128-bit integers (called HUGEINT) on platforms that support this. * Removed support for the mseed library. * Removed support for RDF. * Removed DataCell. It was experimental code that was never enabled. Bug Fixes * 2618: Implement master slave scheme * 3361: constants as MAL function parameters prevent intermediate reuse * 3364: Cannot set role back to a user's default role * 3365: Unable to grant object privileges while having a non-default current_role * 3440: Sequence type errors * 3449: mserver crash on start - Freebsd 10 amd64 * 3476: Cannot revoke object access * 3496: autocompletion table names does not work correctly * 3556: when 2 multiplexed functions in MAL plan, only one is mapped correctly to bat<mod>.function primitive * 3564: Request: add support for postgresql specific scalar function: split_part(string text, delimiter text, field int) * 3625: SIGSEGV because mat array can overrun in opt_mergetable.c * 3627: SQRT in CASE does not work as of Oct2014 * 3654: configure --enable-fits requires extra commands after creating a database instance * 3673: mclient 'expanded' row formatter * 3674: Obfuscate event tracing * 3679: No error is given when incorrect timezone value is specified for a timetz column * 3686: Wrong associativity of multiply/divide * 3702: Filter function not found if created in a user schema * 3708: wrong scoping for cross-schema view references * 3716: alter table my_merge_table drop table t1; crashes mserver5 with Segmentation fault * 3724: Wrong size calculation in BATsubjoin * 3730: SAMPLE function not sampling randomly * 3732: memory leak (of InstrRecord) in opt_mergetable * 3733: "(TRUE OR <Exp>) AND <Exp>" is evaluated incorrectly * 3735: python connection with unix_socket * 3736: crash if mclient disconnects abruptly during a query * 3738: Database inconsistency when using savepoint * 3739: CASE statements do not handle NULLs in the IN () operator properly * 3740: select epoch(now()); types timestamptz(7,0) and bigint(64,0) are not equal * 3742: Division By Zero * 3744: cast to int gives different results for decimal than double * 3747: joins fail in the presence of nulls * 3748: Missing META-INF/services/java.sql.Driver in JDBC package * 3753: Hang on json field parsing * 3754: select from a REMOTE TABLE referring local table crashes mserver5 * 3756: column type conversion sticks to subsequent queries * 3758: "COPY INTO ..." doesn't work, if executing from 2 processes concurrently. * 3759: select data from "sys"."rejects" returns unexpected error and when next select data from "sys"."sessions" causes an assertion failure in mal_interpreter.c:646. * 3760: SQL parser has problem with (position of) a scalar subquery in a SELECT-list * 3761: SQL executor has problem with (position of) a subquery in a SELECT-list. Inconsistent behavior. * 3763: JDBC PreparedStatement for a table with 14 Foreign Keys crashing the Database * 3764: DROPping multiple users causes a crash * 3765: Re-granting a revoked privilege does not work * 3766: VIEW not visible if created under a different schema * 3767: CREATE TEMP TABLE using "LIKE" incorrectly handled * 3769: SIGSEGV when combining a cast/column alias with a UNION ALL view * 3770: combined conditions on declared table in User Defined Function definition crashes monetdb * 3771: Owner of the schema loses rights if assumes the monetdb role. * 3772: Any user can grant a role. * 3773: quantile(col, 0) and quantile(col, 1) fail * 3774: mclient is unaware of merge tables and remote tables * 3775: COPY INTO: Backslash preceding field separator kills import * 3778: Crash on remote table schema mismatch * 3779: server crashes on MAX() on SELECT DISTINCT something combo * 3783: Behavioural change in Jul2015 for 'timestamp minus timestamp' * 3784: Assertion failed: (bn->batCapacity >= cnt), function BAT_scanselect, file gdk_select.c, line 1008. * 3785: sum(interval) causes overflow in conversion to bte * 3786: ResultSet.close() never sends Xclose to free resources * 3787: "b and g must be aligned" from complex group/union query * 3791: HEAPextend: failed to extend to 2420077101056

3 3

Jul2015: access denied to sys.tables from custom user/schema
by Roberto Cornacchia 02 Sep '15

02 Sep '15

Hi there, Is it a Jul2015 feature that access to sys.tables is denied to custom a user/schema? However, sys._tables is accessible. That sounds strange. Roberto

3 4

I love Jul2015!
by Roberto Cornacchia 01 Sep '15

01 Sep '15

Not about issues this time. I just wanted to thank the team for this new upcoming release, I'm using it for a while already and it seems to be brilliant. I particularly like that hash tables are kept around, making a hot DB solve in *microseconds* joins that would take almost half a *second* otherwise. Ka-Boom!

3 2

Release Jul2015
by Luciano Sasso 31 Aug '15

31 Aug '15

Jul2015 is stable version ? This master/slave(master pull data for salave automatic) features this release Jul2015? -- 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

Information for Datastorage, Indizes and Query optimization
by Dennis Wiest 31 Aug '15

31 Aug '15

Hello there, there is detailed information about MonetDB with respect to the data storage, indexing and query optimization. I think, unfortunately, on the website is not very much. Special for the Internal index and query optimization. Can you help me there? I need that for my work. greetings Wiest Dennis ________________________________ Avitech GmbH Principal Office: Bahnhofplatz 1 | 88045 Friedrichshafen | Germany Court Registration: Amtsgericht Ulm | HRB 728293 Gesch?ftsf?hrer/Managing Director: Antonio Maria Gonzalez Gorostiza VAT No.: DE223719716 http://avitech.aero

3 4

dplyr & monetdb - appropriate syntax for querying schema.table?
by Enzo 31 Aug '15

31 Aug '15

I’ve just posted the following on stackoverflow. Any help is welcome! In monetdb I have set up a schema main and my tables are created into this schema. For example, the department table is main.department. With dplyr I try to query the table: mdb <- src_monetdb(dbname="model", user="monetdb", password="monetdb") tbl(mdb, "department") But I get Error in .local(conn, statement, ...) : Unable to execute statement 'PREPARE SELECT * FROM "department"'. Server says 'SELECT: no such table 'department'' [#42S02]. I tried to use "main.department" and other similar combinations with no luck. What is the appropriate syntax? Enzo enzo(a)smartinsightsfromdata.com

2 1

password issue with Jul2015 Feature release of MonetDB suite
by Enzo 29 Aug '15

29 Aug '15

I do not seem to be able to log in R: the following was working before, but now it doesn’t. aconn <- dbConnect(MonetDB.R(), "monetdb://localhost:50000/ClevelandBaseModel", user= "monetdb", password="monetdb”) Error in .mapiAuthenticate(socket, dbname, user, password, language = language) : Authentication error: !InvalidCredentialsException:checkCredentials:invalid credentials for user ‘monetdb’ Has the password been changed?? Enzo enzo(a)smartinsightsfromdata.com

1 1

issue granting administrator rights
by Enzo 28 Aug '15

28 Aug '15

I have successfully managed to configure multi-user access to my user defined schema. All the users are granted monetdb “rights with: GRANT monetdb TO user1 Once logging the new user has ability to SELECT / DROP / INSERT etc. in the user schema. Sadly at the time of executing the MonetDB.R command: tStatus <- dbWriteTable(conn, tPFile, dataDt,overwrite=TRUE, csvdump=TRUE) I’ve got: Unable to execute statement 'COPY OFFSET 2 INTO demand FROM '/Users/xxx/demand.csv' USIN...'. Server says '!COPY INTO: insufficient privileges: COPY INTO from file(s) requires database administrator rights, use 'COPY INTO "demand" FROM STDIN' instead' So clearly GRANT monetdb TO user1 is not enough to empower user1. I tried: GRANT monetdb to “user1" WITH ADMIN OPTION; But at this point after: SET SCHEMA cleveland; SET ROLE monetdb; I get error "Role (monetdb) missing” It does not seem correct to me: surely it should be possible to grant rights to a user at the same level of “monetdb”, including administrator rights? Enzo enzo(a)smartinsightsfromdata.com

1 0

Re: any idea what's going on here? it was created under the previous version but now isn't loading after upgrade
by Niels Nes 28 Aug '15

28 Aug '15

On Fri, Aug 28, 2015 at 06:29:38AM -0400, Anthony Damico wrote: > hi, thanks, here's the file from > > R:\American Community Survey\MonetDB\acs\bat\BACKUP\BBP.dir So the db upgrade did create the sql_dsnapshots bat, but its not part of the sql catalog. Fixed a bug in the code for this now. Niels > > On Fri, Aug 28, 2015 at 5:56 AM, Niels Nes <Niels.Nes(a)cwi.nl> wrote: > > On Thu, Aug 27, 2015 at 02:09:22PM -0400, Anthony Damico wrote: > > # MonetDB 5 server v11.21.3 "Jul2015" > > # Serving database 'acs', using 4 threads > > # Compiled for x86_64-pc-winnt/64bit with 64bit OIDs dynamically > linked > > # Found 64.000 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 connection requests on mapi:monetdb:// > 127.0.0.1:50001/ > > # Start processing logs sql/sql_logs version 52200 > > !FATAL: Logger_new: BBPrename to sql_dsnapshots failed > > Press any key to continue . . . > > > _______________________________________________ > > users-list mailing list > > users-list(a)monetdb.org > > https://www.monetdb.org/mailman/listinfo/users-list > > Anthony > > As the server doesn't successfully start we need to see why the > rename > fails. Could you check the BBP.dir file (usualy in the bat > directory of > your dbpath, sometimes in the BACKUP directory there), and see > if a bat with this name exists (sql_dsnapshots). > > Niels > > > -- > Niels Nes, Manager ITF, Centrum Wiskunde & Informatica (CWI) > Science Park 123, 1098 XG Amsterdam, The Netherlands > room L3.14, phone ++31 20 592-4098 sip:4098@sip.cwi.nl > url: https://www.cwi.nl/people/niels e-mail: Niels.Nes(a)cwi.nl > _______________________________________________ > users-list mailing list > users-list(a)monetdb.org > https://www.monetdb.org/mailman/listinfo/users-list > > -- Niels Nes, Manager ITF, Centrum Wiskunde & Informatica (CWI) Science Park 123, 1098 XG Amsterdam, The Netherlands room L3.14, phone ++31 20 592-4098 sip:4098@sip.cwi.nl url: https://www.cwi.nl/people/niels e-mail: Niels.Nes(a)cwi.nl

3 2

any idea what's going on here? it was created under the previous version but now isn't loading after upgrade
by Anthony Damico 28 Aug '15

28 Aug '15

# MonetDB 5 server v11.21.3 "Jul2015" # Serving database 'acs', using 4 threads # Compiled for x86_64-pc-winnt/64bit with 64bit OIDs dynamically linked # Found 64.000 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 connection requests on mapi:monetdb://127.0.0.1:50001/ # Start processing logs sql/sql_logs version 52200 !FATAL: Logger_new: BBPrename to sql_dsnapshots failed Press any key to continue . . .

2 1

Multiple MonetDB nodes behind HAProxy loadbalancer
by Dennis Pallett 20 Aug '15

20 Aug '15

Hi all, We're thinking about running multiple MonetDB servers with the same databases behind a single HAProxy loadbalancer (see attached diagram). Would this be a good idea? bad idea? Any experiences with this kind of setup? We are thinking about this route primarily so that we can bring a new dataset online during business hours without disrupting end-users who are running queries from our application. An additional benefit would be the ability to handle more load and enable horizontal scalability. Any insights would be much appreciated! Best regards, Dennis

3 3

Jul2015 Release Candidate
by Sjoerd Mullender 19 Aug '15

19 Aug '15

-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA256 I have created a release candidate for the Jul2015 release. The candidate is available at http://dev.monetdb.org/downloads/testing/. If all goes well, this candidate will morph into a release sometime soon(ish). In the mean time, you can use this release candidate and test to see whether it works. And if it doesn't work, I would really like to hear about it. - -- Sjoerd Mullender -----BEGIN PGP SIGNATURE----- Version: GnuPG v2 iQEcBAEBCAAGBQJVyKZgAAoJEISMxT6LrWYgE28H/2rudFXhcIfNgnX4yYJlpRT5 eI2Dzp9kShEmRibvNkWvCEF1j/qyWgQhx/jlHvucuEF/0k+U+7NYuK0vhzA0llyJ hspMkQ73UDXeVO0/SivtNYb40UpMyUQoXC7nSaSneglVDe4JcX4S3Y5GvHPByiqz 3UNMGDARfWlqPcbTtW57XfLejK/sd8nNZ3mGQuerVqEvMcKZaZIBSXjg/16WQVsN DMbPacPpu3yFeWQQtP2er6SL3UdfwgOP5+VNifKyjW9i6ZtB2lHWLEBWQY8G/5Tb 5lwgHvD99SJeLRMzcW5TFWhwv0ROx8chKP3btxMofZxjHhqseswGRLNDQa4XCU0= =ktpg -----END PGP SIGNATURE-----

6 13

RE: Concurrent queries problem (Gustavo Brian)
by Gustavo Brian 18 Aug '15

18 Aug '15

Hi, After checking CPU (48 cores) none of them get over 15%, there's free memory (119Gb) and 0% reads but high WRITE activity, any idea why? Thanks, Gustavo Brian Developer -----Original Message----- From: users-list [mailto:users-list-bounces+gustavo.brian=sunhotels.net@monetdb.org] On Behalf Of users-list-request(a)monetdb.org Sent: lunes, 17 de agosto de 2015 12:00 To: users-list(a)monetdb.org Subject: users-list Digest, Vol 36, Issue 19 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. Concurrent queries problem (Gustavo Brian) 2. Re: Concurrent queries problem (Martin Kersten) 3. Inserting in 'raw' mode?? (Vijay Krishna) ---------------------------------------------------------------------- Message: 1 Date: Sun, 16 Aug 2015 17:07:41 +0000 From: Gustavo Brian <Gustavo.Brian(a)sunhotels.net> To: "users-list(a)monetdb.org" <users-list(a)monetdb.org> Subject: Concurrent queries problem Message-ID: <4A8379DA0ABDFC42B172EDB698BD76E8D46EF829@mailbox02> Content-Type: text/plain; charset="iso-8859-1" Hi, I'm using one big table a my main storage for hotel prices and I'm running queries to get the best prices for specific dates and a couple of filters. On one test I have a query scanning 30 millions rows in ~400ms but when I try to run several queries at a time monetdb hangs. I've tried creating a funnel over the database but same result. What's the best approach if I need to run 150 queries per second ? Thanks in advance, Gustavo Brian Developer SunHotels E-mail Skype Tel Fax Web Operational Office gustavo.brian(a)sunhotels.net<mailto:gustavo.brian@sunhotels.net> Gustavo.brian.sunhotels +34 871 955 649 +34 971 281 460 www.sunhotels.org SunHotels Calle Cal?at 6 Pol?gono Son Valent? 07011 Palma de Mallorca Spain >> Winner of Grand Travel Award - Best Provider in Scandinavia << Confidential and proprietary information notice: This message (including any attachments) contains confidential information intended for a specific individual and purpose, and is protected by law. If you are not the intended recipient, you should delete this message and are hereby notified that any disclosure, copying, or distribution of this message, or the taking of any action based on it, is strictly prohibited. Please preserve message history when replying to this e-mail

1 0

Re: MonetDB performance disappointing
by Martin Schwitalla 17 Aug '15

17 Aug '15

Hi, sorry that it took so long but I could test the patch and it didn`t seem to change anything. The trace looks the same and the performance didn`t improve. Regards, Martin

1 1

Concurrent queries problem
by Gustavo Brian 16 Aug '15

16 Aug '15

Hi, I'm using one big table a my main storage for hotel prices and I'm running queries to get the best prices for specific dates and a couple of filters. On one test I have a query scanning 30 millions rows in ~400ms but when I try to run several queries at a time monetdb hangs. I've tried creating a funnel over the database but same result. What's the best approach if I need to run 150 queries per second ? Thanks in advance, Gustavo Brian Developer SunHotels E-mail Skype Tel Fax Web Operational Office gustavo.brian(a)sunhotels.net<mailto:gustavo.brian@sunhotels.net> Gustavo.brian.sunhotels +34 871 955 649 +34 971 281 460 www.sunhotels.org SunHotels Calle Calçat 6 Polígono Son Valentí 07011 Palma de Mallorca Spain >> Winner of Grand Travel Award - Best Provider in Scandinavia << Confidential and proprietary information notice: This message (including any attachments) contains confidential information intended for a specific individual and purpose, and is protected by law. If you are not the intended recipient, you should delete this message and are hereby notified that any disclosure, copying, or distribution of this message, or the taking of any action based on it, is strictly prohibited. Please preserve message history when replying to this e-mail

2 1

Maximum number of tables
by Masood Mortazavi 15 Aug '15

15 Aug '15

Hi -- Is there a configured maximum number of tables supported by a MonetDB instance? Maximum number of columns in a table? What's the rough size of metadata per column at run-time? Thanks, - Masood

4 4

NULL support in MonetDB?
by Stephen P. Morgan 14 Aug '15

14 Aug '15

Hi, I wonder if anyone can tell me whether MonetDB supports NULL values? Thanks, Steve

2 1

THEAP corruption after DB restart
by Dennis Lorson 14 Aug '15

14 Aug '15

Hi, My DB contains around 1000 tables consisting of 100K-15M records each. They all have the same schema, but I’m not using a single table because locality is required for the individual sets. After restarting MonetDB, I’m experiencing data corruption in some of the tables (around 10-20): - when selecting a single row from those tables (LIMIT 1), the mserver process crashes - when calling the storage() function, the mserver process crashes - the storage() function can only be used again after dropping the tables in question (by trial and error) More information: - The restart was performed as part of an EC2 instance stop/start, This should send a SIGTERM signal to monetdbd. - The DB farm is stored on EBS. Snapshots were made well before the restart, but the same tables are affected. - The corrupted tables were used before successfully and the content has been unchanged since. - No client connections were active at restart time. mserver info: > 2015-08-13 15:57:42 MSG DB[10633]: arguments: /usr/bin/mserver5 --dbpath=/opt/db-farm/DB --set merovingian_uri=mapi:monetdb://ip-HOST:50000/DB --set mapi_open=false --set mapi_port=0 --set mapi_usock=/opt/db-farm/DB/.mapi.sock --set monet_vault_key=/opt/db-farm/DB/.vaultkey --set gdk_nr_threads=4 --set max_clients=64 --set sql_optimizer=default_pipe --set monet_daemon=yes > 2015-08-13 15:57:42 MSG DB[10633]: # MonetDB 5 server v11.19.15 "Oct2014-SP4" > 2015-08-13 15:57:42 MSG DB[10633]: # Serving database ‘DB', using 4 threads > 2015-08-13 15:57:42 MSG DB[10633]: # Compiled for x86_64-pc-linux-gnu/64bit with 64bit OIDs dynamically linked > 2015-08-13 15:57:42 MSG DB[10633]: # Found 15.672 GiB available main-memory. This error occurs a couple of times in the server logs, but only when first starting monetdbd: > 2015-08-13 14:33:15 MSG: !ERROR: GDKload: cannot read: name=15/31/153162, ext=theap, 57144 bytes missing. > 2015-08-13 14:33:15 MSG: !OS: No such file or directory > 2015-08-13 14:33:15 MSG: !ERROR: GDKload: cannot read: name=15/25/152564, ext=theap, 58536 bytes missing. > 2015-08-13 14:33:15 MSG: !OS: No such file or directory And then each time an affected table is accessed: > 2015-08-13 16:02:19 MSG merovingian[1041]: database ‘DB' (10640) was killed by signal SIGSEGV I’m trying to reproduce the issue, but it would be helpful if anyone has any pointers. My hunch would be that the table catalog and the theap files are not persisted in the same way, so when snapshotting or restarting, one of the two is out of sync. Could it be that some dynamic column index is built, but then only persisted in mapped memory? The comments at the top of the gdk/gdk_storage.c file seem to imply that this is not the case, but I’m not sure. If so, how would one ensure that all changes are properly flushed to disk? Would fsfreeze help? What if an unexpected loss of power occurs? Thanks, - Dennis

1 1

Remote table joining and where conditions
by Wu XIANG 13 Aug '15

13 Aug '15

Hi guys, I've two questions on July2015 release of monetdb. 1) If I have two VMs (vm1, vm2) with monetdb installed: on vm1, there is a table named t1, and it's also accessible on vm2 via remote table alias rt1, and there's another table on vm2 named d2. If I do a table join on rt1 and d2, what happens here ? a. Is it true that data in t1 are fully loaded into vm2, and table joining happens on vm2 ? b. If I add a where condition on d2, what happens ? Is it true that data of t1 are pre-filtered by this newly added condition before sent to vm2 ? c. If there's another table on vm1 with the same name of 'd2', with where condition on d2 added, what happens then ? p.s. all queries are requested on CLI of vm2. 2) is there anything like partition key of prestodb in a partition table, such that some partitions can be fully skipped when querying with a where condition on these partition key provided ? thanks wu

1 0

Request Buffer pools
by Vijay Krishna 13 Aug '15

13 Aug '15

Hi, Does MonetDB have any built in buffer pools to handle parallel requests? I see the default number of parallel connections accepted and processed at a time by MonetDB is 64.. While I see that there are ways to increase it, is there anyway built in to pool incoming requests until the live 64 requests are processed? For example, our application would receive 100 requests at a time, while we wish to limit the number of queries handled at a time by MonetDB to 50. Is there any way to automatically handle the remaining 50 (something like innodb buffer pool) ?? Thanks & Regards, Vijayakrishna.P. Mobile : (+91) 9500402305.

4 8

Slow DB performance after data insertion
by Zimmermann, Alexander 10 Aug '15

10 Aug '15

Hi folks, in the following scenario I see very slow DB performance after data insertion. W/ slow performance I mean that „mclient -u XXX -d XXX" blocks for roughly 10min bevore it let me access the DB. Scenario: I parse a pcap file (40MB, 695 connection) in python w/ dpkt. For every new connection found in the pcap file I create a new entry in my connection table and two new empty packet tables (for each direction one table). The actual packet data is not directly inserted into the packet tables, but written into files to push the data later on w/ COPY BINARY INTO into the packet tables. Autocommit is off. I commit after the I read the pcap completely and before I start the COPY BINARY INTO. After the COPY BINARY INTO I’ve another commit. That’s it. The number of transactions is 3*695 (2*695 table creations for the packet tables and 695 entries into the connection table). The script itself terminates in roughly 30secs, but the DB is blocked +- 10mins. Why? What is going on internally in the DB? Can I speedup the performance? Cheers Alex

3 3

possible monetdb catalogue corruption and how to recover from it?
by Ashish Kumar Singh 10 Aug '15

10 Aug '15

Hi List, We are seeing a situation with monetdb v11.17.17 "Jan2014-SP2” release where at the time of dropping table , data base got crashed. After that we are not able to drop this table. Any command to drop this table is returning with success but we can not create same table again as it complains about table exists/ in use. But when we try to select * from this table or do a count on this it does complains saying No Such Table exist. My hunch is somehow database catalogue got corrupted, what is recommended way to bring back database to good health after any such condition occurs? We have not tried restarting monetdb instance yet. Regards, Ashish Kumar Singh Mobile: +91-9972045095 Skype: toashishk(a)gmail.com

5 8

Help with lag()
by Eggert, Lars 06 Aug '15

06 Aug '15

Hi, there doesn't seem to be much documentation about how to use lag() in monetdb, and I'm running into a cryptic error. Here is a snippet of a table: sql>select time, tcp_seq from packet_1_s order by time limit 10; +--------------------------+------------+ | time | tcp_seq | +==========================+============+ | 1436723208.3099999 | 2056936800 | | 1436723208.3099999 | 2056936800 | | 1436723208.3099999 | 2056936800 | | 1436723208.3099999 | 2056936800 | | 1436723208.3099999 | 2056936848 | | 1436723208.3099999 | 2056936848 | | 1436723208.3099999 | 2056936848 | | 1436723208.3099999 | 2056936848 | | 1436723208.3099999 | 2056936896 | | 1436723208.3099999 | 2056936896 | +--------------------------+------------+ 10 tuples (9.701ms) sql>select time, tcp_seq, lag(tcp_seq, 1) over (order by time) from packet_1_s order by time limit 10; TypeException:user.s22_25[502]:'calc.lag_grp' undefined in: _1678:any := calc.lag_grp(_1672:oid, _1674:oid, _1676:dbl) program contains errors Any ideas what causes this error? Thanks, Lars

3 5

Trouble with perl dbd modules for Monetdb
by Bryan Ellerbrock 03 Aug '15

03 Aug '15

Hi, I'm new to database work, and completely new to monetdb. I am attempting to load data into a new monetdb setup for testing, to see if it may make sense to migrate some genotyping datasets from postgres. I'm working in a Virtualbox Debian 64bit VM with MonetDB v11.19.15 (Oct2014-SP4). I've set up everything according to the MonetDB tutorial, and have had no trouble creating test tables and inserting data with simple sql statements. Beyond that though I need to use perl, and I'm having trouble with DBD::monetdb and it's dependencies. The download from cpan either times out or fails when working on dependencies MonetDB::CLI and MonetDB::CLI::MapiPP. If I use the verbose flag it reports: Test Summary Report ------------------- t/02cxn.t (Wstat: 28416 Tests: 15 Failed: 14) Failed tests: 2-15 Non-zero exit status: 111 Parse errors: Bad plan. You planned 18 tests but ran 15. t/75mil.t (Wstat: 28416 Tests: 15 Failed: 14) Failed tests: 2-15 Non-zero exit status: 111 Parse errors: Bad plan. You planned 18 tests but ran 15. When I skip these tests with a force install, and try to use the DBD in a loading script, I encounter a similar problem. The perl debugger shows the script getting stuck at my line of code attempting to connect to the database. my $dbh = DBI->connect("dbi:monetdb:database=genotypes;host=localhost",$username,$password, { PrintError => 0, RaiseError => 1 } # turn on exception handling ); If I step into the subroutine calls I can't pinpoint a failure but it is at some point after a call to MonetDB::CLI::MapiPP Am I missing something obvious? Is there something I can do to further address the problem? Any help would be greatly appreciated! Best regards, Bryan Ellerbrock

1 0

Problem using tomograph
by Martin Schwitalla 03 Aug '15

03 Aug '15

Hi, I`m trying to use tomograph for a query analysis. But unlike stethoscope it doesn`t work. I start the tomogroph with tomograph -u monetdb -d exomate, just like in the tutorial. After that I start a mclient with mclient -u monetdb -d exomate and execute the query. But tomograph triggers no output until I quit it with CTRL+C and when I try to gnuplot the file file, I only get errors because there is no valuable data. Could you maybe tell me what I am doing wrong? I`m using MonetDB 11.19.15. Regards, Martin

3 4

What JDBC jar
by Vic 26 Jul '15

26 Jul '15

Hi, There are 3: http://dev.monetdb.org/downloads/Java/Oct2014-SP4 Which one should I use? Vic

3 3

MonetDB: bulk insert failed due to weird characters
by Tri Vuong 24 Jul '15

24 Jul '15

Hi, I'm using "COPY INTO" to insert some data into MonetDB. The csv that I'm copying from has some weird characters like \367\251\240 in some string fields and it broke the insertion. The error that I got is: !value from line x field y not inserted, expecting type clob Does anyone know how to get around this problem? The version I'm using is MonetDB Database Server Toolkit v1.1 (Oct2014-SP4). Thanks. -- Tri Vuong

4 6

Re: Monetdb-R integration
by Hannes Mühleisen 23 Jul '15

23 Jul '15

Hi Imad, I just noticed this, grouped aggregates using R need to consider the aggr_group implicit parameter. This vector denotes which group the entries in the input belong to. In your case, something like this should work: CREATE AGGREGATE "ttest2samples"(arg1 double, arg2 varchar(250)) RETURNS double LANGUAGE R { unlist(lapply(split(data.frame(arg1, arg2, stringsAsFactors=F), aggr_group), function(x) {t.test(arg1 ~ arg2, data=x)$p.value})) }; This first splits the input according to the grouping variables, then calculates the p-value for every group separately, and then returns a single result value for each group. select "2-Age","ttest2samplesStatistic"("9-Score", "4-Gender") as "stat", "ttest2samples"("9-Score", "4-Gender") as "pvalue" from "Marketing_Loyalty_4700298d-9862-40b3-9028-b0f15dab9dea" group by "2-Age”; then results in +---------+--------------------------+--------------------------+ | 2-Age | stat | pvalue | +=========+==========================+==========================+ | [21-30] | -1.3224483820692825 | 0.18902257300165592 | | [10-20] | 0.15797419230807905 | 0.8781391200296775 | | > 30 | -2.636786922876647 | 0.011182421832548708 | +---------+--------------------------+--------------------------+ Sorry for the delay, Hannes On 09 Jul 2015, at 09:52, imad hajj chahine <imad.hajj.chahine(a)gmail.com> wrote: > > > Hi Hannes, > > Any update on the issue? > > Thank you. > > On Mon, Jun 22, 2015 at 4:18 PM, imad hajj chahine <imad.hajj.chahine(a)gmail.com> wrote: > ok thanks, if you want you can also test with this function (a one sample test that takes the second argument as simple double value): > > CREATE AGGREGATE "ttest1sample"(arg1 double, arg2 double) RETURNS double LANGUAGE R { t.test(arg1, arg2)$p.value }; > > select "2-Age", "ttest1sample"("9-Score",6.08963) from "Marketing_Loyalty_4700298d-9862-40b3-9028-b0f15dab9dea" where "2-Age" is not null group by "2-Age" > > On Mon, Jun 22, 2015 at 4:12 PM, Hannes Mühleisen <Hannes.Muehleisen(a)cwi.nl> wrote: > ok, reproduced issue. let me see why this only returns one row. > > > ----- Original Message ----- > From: "imad hajj chahine" <imad.hajj.chahine(a)gmail.com> > To: "Hannes Mühleisen" <Hannes.Muehleisen(a)cwi.nl> > Sent: Monday, June 22, 2015 3:10:55 PM > Subject: Re: Monetdb-R integration > > Sorry Hannes, you need to do a trim on "4-Gender" column as its the one > used in the test. > Sorry again its my fault. > > On Mon, Jun 22, 2015 at 3:42 PM, Hannes Mühleisen <Hannes.Muehleisen(a)cwi.nl> > wrote: > > > Did a trim() on the column but same error. There are three distinct values > > in that column. > > I get > > > > select distinct "2-Age" from > > "Marketing_Loyalty_4700298d-9862-40b3-9028-b0f15dab9dea”; > > +---------+ > > | 2-Age | > > +=========+ > > | [21-30] | > > | [10-20] | > > | > 30 | > > +————+ > > > > > > ----- Original Message ----- > > From: "imad hajj chahine" <imad.hajj.chahine(a)gmail.com> > > To: "Hannes Mühleisen" <Hannes.Muehleisen(a)cwi.nl> > > Sent: Monday, June 22, 2015 2:36:43 PM > > Subject: Re: Monetdb-R integration > > > > Hi Hannes, > > > > Please let me know if you need more information from my side. > > > > Regards > > > > On Fri, Jun 19, 2015 at 11:38 AM, imad hajj chahine < > > imad.hajj.chahine(a)gmail.com> wrote: > > > > > Hi Hannes, > > > > > > This error is due that the "2-age" column has more than 2 distinct > > values, > > > there is a space after some values. can you run a trim on the "2-Age" and > > > then try it again. > > > > > > As for the vector thing, I didnt get the point, shouldnt the db engine > > > split the data into vectors and feed the aggregate function which return > > > one data point? > > > > > > Thanks > > > > > > On Fri, Jun 19, 2015 at 11:12 AM, Hannes Mühleisen < > > > Hannes.Muehleisen(a)cwi.nl> wrote: > > > > > >> I am running attached script, which I pieced together from your emails. > > >> This does not run, it produces the following error: > > >> > > >> Error running R expression. Error message: Error in t.test.formula(arg1 > > ~ > > >> arg2) : > > >> grouping factor must have exactly 2 levels > > >> Calls: as.data.frame -> <Anonymous> -> t.test -> t.test.formula > > >> > > >> > > >> > > >> > > >> In general, for aggregations, the R code needs to return a vector with a > > >> single entry for each group. > > >> > > >> > > >> Best, > > >> > > >> Hannes > > >> > > >> > > >> > > >> > > >> > On 19 Jun 2015, at 09:44, imad hajj chahine < > > >> imad.hajj.chahine(a)gmail.com> wrote: > > >> > > > >> > Thanks, if you need more information from my side let me know. > > >> > > > >> > Also can you answer me on the following when i use 2 functions one to > > >> retrieve Pvalue and one for the stat value: > > >> > Am I doing the correct way to retrieve the stat and p-value, is the > > >> ttest get executed twice in this case? is there a more optimal way > > where i > > >> can retrieve both information. > > >> > > > >> > On Fri, Jun 19, 2015 at 10:39 AM, Hannes Mühleisen < > > >> Hannes.Muehleisen(a)cwi.nl> wrote: > > >> > Looking into this at the moment. > > >> > > > >> > > On 16 Jun 2015, at 09:56, Dimitar Nedev < > > >> dimitar.nedev(a)monetdbsolutions.com> wrote: > > >> > > > > >> > > Hannes, do you know if the STD_DEV is the issue there or if the > > >> queries should actually yield different results? > > >> > > > > >> > > Dimitar > > >> > > > > >> > >> On 2015-Jun-15, at 13:28 , imad hajj chahine < > > >> imad.hajj.chahine(a)gmail.com> wrote: > > >> > >> > > >> > >> Hi, > > >> > >> > > >> > >> Shouldn't we have the same result from running the first query and > > >> the second query? > > >> > >> after all its an aggregate function and should return the test for > > >> each group. > > >> > >> > > >> > >> I checked that monetdb had problem with running group by with > > >> statistics functions like STD_DEV, are these 2 issues related? > > >> > >> > > >> > >> Thank you. > > >> > >> > > >> > >> > > >> > >> > > >> > >> > > >> > >> On Mon, Jun 15, 2015 at 1:52 PM, Dimitar Nedev < > > >> dimitar.nedev(a)monetdbsolutions.com> wrote: > > >> > >> Hi Imad, > > >> > >> > > >> > >> Here is the output I got from running the queries. Note, this might > > >> not be entirely representative since I did clean-up some data before > > >> loading it. > > >> > >> > > >> > >> sql>select "2-Age", "tone"."ttest2"("9-Score",6.08963) from > > >> "tone"."Marketing_Loyalty_4700298d-9862-40b3-9028-b0f15dab9dea" where > > >> "2-Age" is not null group by "2-Age"; > > >> > >> +---------+--------------------------+ > > >> > >> | 2-Age | L1 | > > >> > >> +=========+==========================+ > > >> > >> | [10-20] | 0.89311103869734754 | > > >> > >> +---------+--------------------------+ > > >> > >> 3 tuples (3.218ms) > > >> > >> > > >> > >> sql>select '[10-20]' as "2-Age","tone"."ttest2"("9-Score",6.08963) > > >> from "tone"."Marketing_Loyalty_4700298d-9862-40b3-9028-b0f15dab9dea" > > where > > >> "2-Age" = '[10-20]' > > >> > >> more>union all > > >> > >> more>select '[21-30]' as "2-Age","tone"."ttest2"("9-Score",6.08963) > > >> from "tone"."Marketing_Loyalty_4700298d-9862-40b3-9028-b0f15dab9dea" > > where > > >> "2-Age" = '[21-30]' > > >> > >> more>union all > > >> > >> more>select '[> 30]' as "2-Age","tone"."ttest2"("9-Score",6.08963) > > >> from "tone"."Marketing_Loyalty_4700298d-9862-40b3-9028-b0f15dab9dea" > > where > > >> "2-Age" = '> 30'; > > >> > >> +---------+--------------------------+ > > >> > >> | 2-Age | L1 | > > >> > >> +=========+==========================+ > > >> > >> | [10-20] | 0.14150742355349472 | > > >> > >> | [21-30] | 0.32872830865003566 | > > >> > >> | [> 30] | 0.37127075363219841 | > > >> > >> +---------+--------------------------+ > > >> > >> 3 tuples (8.825ms) > > >> > >> > > >> > >> > > >> > >> sql>select "tone"."ttest2samplesStatistic"("9-Score", "4-Gender") > > as > > >> "stat", "tone"."ttest2samples"("9-Score", "4-Gender") as "pvalue" from > > >> "tone"."Marketing_Loyalty_4700298d-9862-40b3-9028-b0f15dab9dea" group > > by > > >> "2-Age"; > > >> > >> +--------------------------+--------------------------+ > > >> > >> | stat | pvalue | > > >> > >> +==========================+==========================+ > > >> > >> | -2.1588506639013736 | 0.032555033970368859 | > > >> > >> +--------------------------+--------------------------+ > > >> > >> 1 tuple (13.287ms) > > >> > >> > > >> > >> > > >> > >> I dropped the aggregate with: > > >> > >> sql>DROP AGGREGATE "tone"."ttest2samplesStatistic"; > > >> > >> operation successful (2.049ms) > > >> > >> > > >> > >> > > >> > >> Best regards, > > >> > >> Dimitar > > >> > >> > > >> > >>> On 2015-Jun-12, at 00:46 , imad hajj chahine < > > >> imad.hajj.chahine(a)gmail.com> wrote: > > >> > >>> > > >> > >>> Thank you Dimitar, > > >> > >>> > > >> > >>> I was using Squirrel to connect from my host machine to the VM. > > >> > >>> > > >> > >>> Now when I connect using SSH-client and run the short group by > > >> query it works but the result is not similar to the union queries (PS: i > > >> also run it in the VM direclty and had the same result) > > >> > >>> > > >> > >>> > > >> > >>> +---------+--------------------------+ > > >> > >>> | 2-Age | pval | > > >> > >>> +=========+==========================+ > > >> > >>> | [21-30] | 0.99996611809271041 | > > >> > >>> +---------+--------------------------+ > > >> > >>> 3 tuples (2.638ms) > > >> > >>> > > >> > >>> > > >> > >>> What i am trying to achieve is to run a one sample ttest for all > > >> the age categories, and it seems that the group by is ignored. > > >> > >>> > > >> > >>> I have also created 2 functions for the two sample ttest: > > >> > >>> > > >> > >>> CREATE AGGREGATE "tone"."ttest2samples"(arg1 double, arg2 > > >> varchar(250)) RETURNS double LANGUAGE R { t.test(arg1 ~ arg2)$p.value }; > > >> > >>> CREATE AGGREGATE "tone"."ttest2samplesStatistic"(arg1 double, arg2 > > >> varchar(250)) RETURNS double LANGUAGE R { t.test(arg1 ~ arg2)$statistic > > }; > > >> > >>> > > >> > >>> select "tone"."ttest2samplesStatistic"("9-Score", "4-Gender") as > > >> "stat", "tone"."ttest2samples"("9-Score", "4-Gender") as "pvalue" from > > >> "tone"."Marketing_Loyalty_4700298d-9862-40b3-9028-b0f15dab9dea" group > > by > > >> "2-Age" > > >> > >>> > > >> > >>> Am I doing the correct way to retrieve the stat and p-value, is > > the > > >> ttest get executed twice in this case? is there a more optimal way > > where i > > >> can retrieve both information. Please note that I also have the same > > >> grouping issue as before. > > >> > >>> > > >> > >>> Do you have the same behavior, or I am missing something in the > > >> creation of these functions. > > >> > >>> > > >> > >>> A final question, how do u drop a R function? i am trying to > > >> execute the following: > > >> > >>> drop all function "tone"."ttest2samplesStat" > > >> > >>> > > >> > >>> > > >> > >>> Thank you. > > >> > >>> > > >> > >>> > > >> > >>> > > >> > >>> On Thu, Jun 11, 2015 at 11:28 PM, Dimitar Nedev < > > >> dimitar.nedev(a)monetdbsolutions.com> wrote: > > >> > >>> Hi Imad, > > >> > >>> > > >> > >>> Hannes asked me to look at your issue with the MonetDB-R VM. > > >> > >>> > > >> > >>> I loaded the data and ran the queries you mentioned, but I did not > > >> encounter any issues. This holds for both the short and the union > > queries. > > >> I am also not familiar with the issue you report, nor its cause. At this > > >> time I think it is local to your instance. If you can export your db, or > > >> re-load the data, I would recommend you to create a new instance with a > > >> fresh image and try again. If you import the image as a new VM instance, > > >> you can keep your old one around as well. > > >> > >>> > > >> > >>> Please note that during the test: > > >> > >>> - I ran as the admin (monetdb) user, as the one created default on > > >> the VM image cannot create new schemas. > > >> > >>> - I have to clean the data a bit up to load it, removing dozen > > >> lines or so. > > >> > >>> - Renamed ttest1 to ttest2 to match the queries. > > >> > >>> > > >> > >>> Best regards, > > >> > >>> Dimitar > > >> > >>> > > >> > >>>>>>>>> Begin forwarded message: > > >> > >>>>>>>>> > > >> > >>>>>>>>> From: imad hajj chahine <imad.hajj.chahine(a)gmail.com> > > >> > >>>>>>>>> Subject: Re: Monetdb-R integration > > >> > >>>>>>>>> Date: 10 Jun 2015 10:11:48 CEST > > >> > >>>>>>>>> To: Hannes Mühleisen <Hannes.Muehleisen(a)cwi.nl> > > >> > >>>>>>>>> > > >> > >>>>>>>>> Hi, > > >> > >>>>>>>>> > > >> > >>>>>>>>> I am using the virtual box provided from > > >> https://www.monetdbsolutions.com/solutions/cloud/vm: > > >> > >>>>>>>>> OS:linux version 3.10.0-123.9.3.el7.x86_64 ( > > >> builder(a)kbuilder.dev.centos.org) > > >> > >>>>>>>>> Monetdb: V1.7: (Oct2014-SP1) > > >> > >>>>>>>>> R: 3.1.2 (2014-10-31) > > >> > >>>>>>>>> > > >> > >>>>>>>>> > > >> > >>>>>>>>> > > >> > >>>>>>>>> create table > > >> "tone"."Marketing_Loyalty_4700298d-9862-40b3-9028-b0f15dab9dea" > > ("Consumer > > >> Number" INTEGER,"1-Disloyalty" varchar(250),"2-Age" > > varchar(250),"3-Amount > > >> transferred" INTEGER,"4-Gender" varchar(250),"5-Citiz enship" > > >> varchar(250),"6- Profession" varchar(250),"7-City" varchar(250),"8-1 NC > > >> Billboard" varchar(250),"8-2 NC Friends" varchar(250),"8-3 NC Magazine" > > >> varchar(250),"8-4 NC Others" varchar(250),"9-Score" decimal(18,3),"10- > > Type > > >> of operation" varchar(250),"11-1 Welcoming" INTEGER,"11-2 Waiting time" > > >> INTEGER,"11-3 Time to finish request" INTEGER,"11-4 Fees" INTEGER,"11-5 > > >> Courtesy of employees" INTEGER,"11-6 Branch layout" INTEGER,"11-7 > > Attention > > >> to special requests" INTEGER,"12- Total time for service" > > >> decimal(18,3),"13- Distance from POS" decimal(18,3),"14- Weight" > > INTEGER) > > >> > >>>>>>>>> > > >> > >>>>>>>>> COPY offset 2 INTO > > >> "tone"."Marketing_Loyalty_4700298d-9862-40b3-9028-b0f15dab9dea" FROM > > >> > > '/home/App_Data/TestingDataSimple_2003_Marketing_Loyalty_4700298d-9862-40b3-9028-b0f15dab9dea.csv' > > >> USING DELIMITERS ',','\n','"'; > > >> > >>>>>>>>> > > >> > >>>>>>>>> Thanks > > >> > >>>>>>>>> > > >> > >>>>>>>>> On Wed, Jun 10, 2015 at 10:50 AM, Hannes Mühleisen < > > >> Hannes.Muehleisen(a)cwi.nl> wrote: > > >> > >>>>>>>>> Hello Imad, > > >> > >>>>>>>>> > > >> > >>>>>>>>> which version of MonetDB and R are you using? Which > > operating > > >> system? > > >> > >>>>>>>>> > > >> > >>>>>>>>> Can you please also include the commands you used to load > > >> your CSV into MonetDB/ create the table? > > >> > >>>>>>>>> > > >> > >>>>>>>>> Thanks, > > >> > >>>>>>>>> > > >> > >>>>>>>>> Hannes > > >> > >>>>>>>>> > > >> > >>>>>>>>>> On 10 Jun 2015, at 00:25, imad hajj chahine < > > >> imad.hajj.chahine(a)gmail.com> wrote: > > >> > >>>>>>>>>> > > >> > >>>>>>>>>> Hi Hannes, > > >> > >>>>>>>>>> > > >> > >>>>>>>>>> I am testing Monetdb-R integration to do some statistical > > >> test, i followed the tutorial you posted on youtube and i have created > > the > > >> following function: > > >> > >>>>>>>>>> > > >> > >>>>>>>>>> CREATE AGGREGATE "tone"."ttest1"(arg1 double, arg2 double) > > >> RETURNS double LANGUAGE R { t.test(arg1, arg2)$p.value }; > > >> > >>>>>>>>>> > > >> > >>>>>>>>>> this function will basically do a one sample t-test. > > >> > >>>>>>>>>> > > >> > >>>>>>>>>> I have loaded the sample attached file into a demo database > > >> and trying to run the following query: > > >> > >>>>>>>>>> > > >> > >>>>>>>>>> select "2-Age", "tone"."ttest2"("9-Score",6.08963) from > > >> "tone"."Marketing_Loyalty_4700298d-9862-40b3-9028-b0f15dab9dea" where > > >> "2-Age" is not null group by "2-Age" > > >> > >>>>>>>>>> > > >> > >>>>>>>>>> The following error happened > > >> > >>>>>>>>>> Error: protocol violation: unexpected line in data block: . > > >> > >>>>>>>>>> SQLState: M0M10 > > >> > >>>>>>>>>> ErrorCode: 0 > > >> > >>>>>>>>>> > > >> > >>>>>>>>>> alternatively i managed to have the intended result by > > >> running this query: > > >> > >>>>>>>>>> > > >> > >>>>>>>>>> select '[10-20]' as > > >> "2-Age","tone"."ttest2"("9-Score",6.08963) from > > >> "tone"."Marketing_Loyalty_4700298d-9862-40b3-9028-b0f15dab9dea" where > > >> "2-Age" = '[10-20]' > > >> > >>>>>>>>>> union all > > >> > >>>>>>>>>> select '[21-30]' as > > >> "2-Age","tone"."ttest2"("9-Score",6.08963) from > > >> "tone"."Marketing_Loyalty_4700298d-9862-40b3-9028-b0f15dab9dea" where > > >> "2-Age" = '[21-30]' > > >> > >>>>>>>>>> union all > > >> > >>>>>>>>>> select '[> 30]' as > > >> "2-Age","tone"."ttest2"("9-Score",6.08963) from > > >> "tone"."Marketing_Loyalty_4700298d-9862-40b3-9028-b0f15dab9dea" where > > >> "2-Age" = '> 30' > > >> > >>>>>>>>>> > > >> > >>>>>>>>>> > > >> > >>>>>>>>>> any help or guidance is appreciated. > > >> > >>>>>>>>>> Thank you. > > >> > >>>>>>>>>> > > >> > >>>>>>>>>> > > >> > > <TestingDataSimple_2003_Marketing_Loyalty_4700298d-9862-40b3-9028-b0f15dab9dea.csv> > > >> > >>>>>>>>> > > >> > >>>>>>>>> > > >> > >>>>>>>> > > >> > >>>>>>> > > >> > >>>>>> > > >> > >>>>> > > >> > >>>> > > >> > >>> > > >> > >> > > >> > >> > > >> > > > > >> > > > >> > > > >> > > >> > > >> > > > > > > >

1 0

Problem: Relational query without result
by Martin Schwitalla 22 Jul '15

22 Jul '15

Hi, I have a problem. I have tested on MonetDB 11.19.9 a query and wanted to test the same query on a better Workstation and installed MonetDB 11.19.15. But now I get every time the message „realtional query without result“ when I try to run the query. Can anybody help? Kind regards, Martin

4 8

How to work with MAL-statements?
by Martin Schwitalla 22 Jul '15

22 Jul '15

Hello, to improve execution time I wanted to work with MAL-statements, so that I could directly control the query plan. But it seems that the generated MAL-Plans don`t „just work“ when I type them. I tried to get used to MAL with the simple query „select * from table;“. This got me the following plan: function user.s1_1{autoCommit=true}():void; barrier X_59 := language.dataflow(); X_2 := sql.mvc(); X_3:bat[:oid,:oid] := sql.tid(X_2,"sys","diseases"); X_6:bat[:oid,:int] := sql.bind(X_2,"sys","diseases","id",0); (X_9,r1_9) := sql.bind(X_2,"sys","diseases","id",2); X_12:bat[:oid,:int] := sql.bind(X_2,"sys","diseases","id",1); X_14 := sql.delta(X_6,X_9,r1_9,X_12); X_15 := algebra.leftfetchjoin(X_3,X_14); X_23:bat[:oid,:str] := sql.bind(X_2,"sys","diseases","name",0); (X_25,r1_25) := sql.bind(X_2,"sys","diseases","name",2); X_27:bat[:oid,:str] := sql.bind(X_2,"sys","diseases","name",1); X_28 := sql.delta(X_23,X_25,r1_25,X_27); X_29 := algebra.leftfetchjoin(X_3,X_28); X_16:bat[:oid,:str] := sql.bind(X_2,"sys","diseases","short_name",0); (X_18,r1_18) := sql.bind(X_2,"sys","diseases","short_name",2); X_20:bat[:oid,:str] := sql.bind(X_2,"sys","diseases","short_name",1); X_21 := sql.delta(X_16,X_18,r1_18,X_20); X_22 := algebra.leftfetchjoin(X_3,X_21); language.pass(X_3); exit X_59; X_30 := sql.resultSet(3,1,X_15); sql.rsColumn(X_30,"sys.diseases","id","int",32,0,X_15); sql.rsColumn(X_30,"sys.diseases","short_name","varchar",255,0,X_22); sql.rsColumn(X_30,"sys.diseases","name","varchar",255,0,X_29); X_40 := io.stdout(); sql.exportResult(X_40,X_30); end s1_1; But when I try to run this statements I get the following error: MAPI = (monetdb) /tmp/.s.monetdb.5250 QUERY = end s1_1; ERROR = !TypeException:user.s1_1[7]:'sql.delta' undefined in: X_14:any := sql.delta(X_6:bat[:oid,:int], X_9:bat[:oid,:oid], r1_9:bat[:oid,:any], X_12:bat[:oid,:int]) !ERROR: MSresetVariablescould not allocate space for What am I`m missing that it doesn`t work? Kind regards, Martin

3 3

QUANTILE( colname , 0 ) and QUANTILE( colname , 1 ) problem
by Anthony Damico 21 Jul '15

21 Jul '15

hi monetdb team, i am getting errors below on data i can't share. before i spend a bunch of time trying to create a reproducible example for this: i've confirmed that MIN and MAX both do work on this column, and also that QUANTILE works with 0.01 thru 0.99 on this column. is there a reason QUANTILEs of 0 and 1 don't simply call MIN and MAX? thanks! Error in .local(conn, statement, ...) : Unable to execute statement 'SELECT QUANTILE( tot_pay , 0 ) FROM acons WHERE yr = 2013 AND ( dattyp1 = 1 OR dattyp2 = 1 OR dattyp...'. Server says 'overflow in conversion of 43553.818965373482 to bte.' [#22003]. > > unwtd_pct <- dbGetQuery( db , paste( "SELECT QUANTILE( tot_pay , 1 ) FROM acons" , where_clause ) ) Error in .local(conn, statement, ...) : Unable to execute statement 'SELECT QUANTILE( tot_pay , 1 ) FROM acons WHERE yr = 2013 AND ( dattyp1 = 1 OR dattyp2 = 1 OR dattyp...'. Server says 'overflow in conversion of 43553.818965373482 to bte.' [#22003]. >

3 3

CAST and CONVERT string to int
by Dennis Wiest 20 Jul '15

20 Jul '15

Hello, I have a question for the Operation Cast and CONVERT. I have the following table and wants to run this query. select * from value v2 WHERE v2.xpath_id = 378 AND v2.value BETWEEN 1000 AND 2000 LIMIT 10; This provides me with the wrong result because v2.value is a textfield. Now I wanted to with the following query to fix this problem, but I get an error. Is it at all possible to the cast or convert? select * from value v2 WHERE v2.xpath_id = 378 AND CAST(CAST(v2.value AS float)AS integer) > 1000 LIMIT 10; conversion of string 'RNAV' to type dbl failed. But this one Works select CAST(CAST(v2.value AS float)AS integer) from value v2 WHERE v2.xpath_id = 378 LIMIT 10; The problem is, "value" must be a text field, "value" can take all possible values. CREATE TABLE value ( id int NOT NULL AUTO_INCREMENT, element_id int NOT NULL, xpath_id int NOT NULL, value text, PRIMARY KEY (id) ); Thanks 4 Help Greetings Dennis ________________________________ Avitech GmbH Principal Office: Bahnhofplatz 1 | 88045 Friedrichshafen | Germany Court Registration: Amtsgericht Ulm | HRB 728293 Gesch?ftsf?hrer/Managing Director: Antonio Maria Gonzalez Gorostiza VAT No.: DE223719716 http://avitech.aero

3 5

combined conditions in user defined function made monetdb exited.
by integrity 19 Jul '15

19 Jul '15

Dear experts, I tried to create an function in monetdb, but the first one successful, the second one successful, but the combo failed. create function neighbor19() returns table (ra_avg double) begin declare table uzone (id bigint, ra_avg double); insert into uzone select id, ra_avg from uniquecatalog11; return table( select ra_avg from uzone as u0 where id between 10 and 20 ); end; successful. create function neighbor20() returns table (ra_avg double) begin declare table uzone (id bigint, ra_avg double); insert into uzone select id, ra_avg from uniquecatalog11; return table( select ra_avg from uzone as u0 where ra_avg between 10 and 20 ); end; successful. create function neighbor30() returns table (ra_avg double) begin declare table uzone (id bigint, ra_avg double); insert into uzone select id, ra_avg from uniquecatalog11; return table( select ra_avg from uzone as u0 where id between 10 and 20 and ra_avg between 10 and 20 ); end; failed with monetdb exited. in merovingian.log: 2015-07-19 13:34:36 MSG merovingian[25753]: database 'gwacdb' (633) was killed by signal SIGSEGV I tried on default and Jul2015 branch, all the same with each other. Any help or hints are appreciated! Best, Meng

3 5

Re: MonetDB performance disappointing
by Niels Nes 18 Jul '15

18 Jul '15

Martin Then trace should be somewhat different as the generated plan changed. Could you send the output of plan . Niels So put plan infront of the query. -------- Original message -------- From: Martin Schwitalla <MSchwitalla1(a)gmx.de> Date: 18/07/2015 12:52 (GMT+01:00) To: Communication channel for MonetDB users <users-list(a)monetdb.org> Cc: Niels Nes <Niels.Nes(a)cwi.nl> Subject: Re: MonetDB performance disappointing Hi, sorry that it took so long but I could test the patch and it didn`t seem to change anything. The trace looks the same and the performance didn`t improve. Regards, Martin _______________________________________________ users-list mailing list users-list(a)monetdb.org https://www.monetdb.org/mailman/listinfo/users-list

1 0

nearest value question on stackoverflow
by Anthony Damico 17 Jul '15

17 Jul '15

https://stackoverflow.com/questions/30641876/monetdb-sql-method-to-locate-o… i'm thinking i can do this with some costly self-join, but i'd appreciate any other eyes on the problem

4 12

No subject
by Bogdan Dimitrov 17 Jul '15

17 Jul '15

This is by no means minimal, but here I've listed some of the issues I had after manually building MonetDB version 11.19.9 with R integration: https://www.monetdb.org/pipermail/users-list/2015-April/008034.html Bogdan ------------------------------ Message: 4 Date: Thu, 25 Jun 2015 23:48:41 +0200 From: musa <pehlewan(a)gmail.com> To: users-list(a)monetdb.org Message-ID: <CAH1mZ_GQScbSTuDhDoy3W65FETH2e0Ntx8a7aScYwcVTjRh0WA(a)mail.gmail.com> Content-Type: text/plain; charset="utf-8" Can someone please provide a minimal example (how to start the server) for running the embedded R feature in windows? Regards, mpa ________________________________

2 1

Should MonetDB really be used in production?
by Frédéric Jolliton 17 Jul '15

17 Jul '15

Greetings, We filled a bug few days ago about simple SAVEPOINT usage corrupting the database (both in Oct2014-SP4 and tip of hg). This is worrying us a lot, because that's yet another bug easy to trigger. We're studying MonetDB extensively for our future needs. We've written optimizers, custom aggregate functions in C, and we've studied many internal aspects. But yet, from time to time, we are hit by bugs when performing simple, trivial queries. You can see the history of bugs we filled on the bugtracker (regarding UNION, SELECT, ..). This become time consuming. This make us nervous because we might have hard time trusting our complexes queries run by MonetDB. The performance are stunning, but the quality seems not high enough for production. Projects like PostgreSQL shows that even Open Source projects can attains incredible stability and robustness. (Admittedly there are many more contributors to PostgreSQL.) Note that we understand that it is a complex software, and that it is hard to cover all possible corner cases in tests. But we're wondering how stable MonetDB is expected to be. Is this really used in production? Regarding the MonetDB website, the primary source of information for the project, we've not found any reference documentation regarding all the supported SQL features. Sure, there is a page listing what is *not* supported, there are pages on various topics, but it is unclear if SAVEPOINT is really supported. (If I can say, I think that the website probably needs some refresh and better navigation.) Here is the latest bug I was talking about: $ bin/mclient -p 50005 test Welcome to mclient, the MonetDB/SQL interactive terminal (Oct2014-SP4) Database: MonetDB v11.19.13 (Oct2014-SP4), 'mapi:monetdb://fjolliton:50005/test' Type \q to quit, \? for a list of available commands auto commit mode: on sql>start transaction; auto commit mode: off sql>create table foo (a int); operation successful (0.528ms) sql>savepoint sp1; auto commit mode: off sql>release savepoint sp1; auto commit mode: off sql>commit; auto commit mode: on sql>select * from foo; +---+ | a | +===+ +---+ 0 tuples (0.9s) So far, so good. Now try to connect again: $ bin/mclient -p 50005 test Welcome to mclient, the MonetDB/SQL interactive terminal (Oct2014-SP4) Database: MonetDB v11.19.13 (Oct2014-SP4), 'mapi:monetdb://fjolliton:50005/test' Type \q to quit, \? for a list of available commands auto commit mode: on sql>select * from foo; SELECT: no such table 'foo' Whoops ! Regards, -- Frédéric Jolliton Sécuractive

9 10

Re: Re: Provide --password option for msqldump tool
by GMail 16 Jul '15

16 Jul '15

1 0

Fwd: Re: Provide --password option for msqldump tool
by Sherzod Mutalov 16 Jul '15

16 Jul '15

I added "password" option to mclient.c and msqldump.c, you can get changed sources from: https://gist.github.com/shmutalov/d3695ad67ad5195eeee6 I know, it is dangerous, but we need this option in our application. If you want I can provide mclient binaryies for Win7 x64.

1 0

Provide --password option for msqldump tool
by Aravind S Reddy 16 Jul '15

16 Jul '15

Hello Team, We were trying to dump multiple tables through 'msqldump' tool, and restoring it in different monetdb instance in a programatic way. But the difficulty here is, I do not see an option to pass the --password to 'msqldump' tool, where as it prompts for the password and accepts input from STDIN. Due to this, we are not able achieve our requirement programatically. And it would be good if -t(--table) option accepts multiple table names, through which we can dump the selected tables in a single shot. Regards, Aravind.S

4 5

Cluster advantages MonetDB
by Luciano Sasso 16 Jul '15

16 Jul '15

Hi, What the cluster Advantages MonetDB, If a query in shard tables in a cluster can not be ordered, what the advantage of using a MonetDB cluster? besides the replication in case of failure. 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. http://www.avast.com

5 5

Job openings at MonetDB Solutions
by Ying Zhang 14 Jul '15

14 Jul '15

Dear all, MonetDB Solutions, the supporting company of MonetDB, is looking for core database developers. If you’re interested, please see the job description here: https://www.monetdbsolutions.com/company/careers Regards, Jennie

1 0

Re: User for multiple schemas
by Ying Zhang 14 Jul '15

14 Jul '15

Hai Brian, Glad to know it helps. We’ve found the cause of the problem I mentioned. It’s not random. It has something to do with changing the ROLE of a USER, and then the privileges of that ROLE seem not properly handled. We’re working on improving the documentation, extending the test suite, and fixing bugs concerning privileges. Regards, Jennie > On Jul 14, 2015, at 12:10, Brian Hood <brianh6854(a)googlemail.com> wrote: > > Hi Jennie, > > That was actually what i was looking for thanks for that i didn't realize that you could have more than one create schema schema_name AUTHORIZATION per_user > > I'll also see if i encounter the bug and if i can find anything reproducible. > > Regards, > > Brian Hood > > > On Tue, Jul 14, 2015 at 10:03 AM, Ying Zhang <Y.Zhang(a)cwi.nl> wrote: > Hello Brian, > > What authorisation are you looking for? MonetDB does support authorise one user as the owner of multiple schema’s, e.g.: > > create user brian with password ’brian123' name ’brian' schema sys; > create schema schema1 AUTHORIZATION brian; > create schema schema2 AUTHORIZATION brian; > > Not sure if this is what you’re looking for. > > However, we have noticed a problem (in the current default branch, probably also exists in Jul2015) in handling the privileges. After a user has been authorised as the owner of a schema, the user can CREATE, INSERT, UPDATE, … tables, however, *sometimes* the user can’t SELECT from his/her own tables. This seems to happen at random, which makes it extremely difficult to debug. However, restarting the MonetDB server seems to temporarily resolve the problem. > > We’re working on improving handling privileges in MonetDB. If you notice any errors and missing features, please let us know. > > Regards, > > Jennie > > > On Jun 23, 2015, at 08:05, Stefan Manegold <Stefan.Manegold(a)cwi.nl> wrote: > > > > Hi Brian, > > > > If i'm not mistaken and recall correctly, access rights / authorization are per table, not per schema - at least in MonetDB; hence, you'd have to grant access for each table in other than the user's schema separately. > > > > Best, > > Stefan > > > > > > On June 22, 2015 8:45:08 PM CEST, Brian Hood <brianh6854(a)googlemail.com> wrote: > > Hi All, > > > > There is something I have pondered on is there a way to have a username that can be authorized for multiple schemas under your dbfarm. > > > > If not what is the reason ? > > > > I haven't seen a way to do this so far. > > > > Regards, > > > > Brian Hood > > > > > > > > users-list mailing list > > users-list(a)monetdb.org > > https://www.monetdb.org/mailman/listinfo/users-list > > > > -- > > | Stefan.Manegold(a)CWI.nl | DB Architectures (DA) | > > | www.CWI.nl/~manegold/ | Science Park 123 (L321) | > > | +31 (0)20 592-4212 | 1098 XG Amsterdam (NL) | > > _______________________________________________ > > users-list mailing list > > users-list(a)monetdb.org > > https://www.monetdb.org/mailman/listinfo/users-list > >

1 0

User for multiple schemas
by Brian Hood 14 Jul '15

14 Jul '15

Hi All, There is something I have pondered on is there a way to have a username that can be authorized for multiple schemas under your dbfarm. If not what is the reason ? I haven't seen a way to do this so far. Regards, Brian Hood

3 2

How do you generate documentation from MonetDB source files?
by Jon Mulieri 12 Jul '15

12 Jul '15

I have long appreciated the wealth of information in the comments of source files throughout the code base. It is apparently formatted for some documentation extraction. Can someone provide instructions for generating the documentation from the source? Thanks! Jon

3 2

Reproducing EmbeddedR results from MonetDB.org site
by George, Glover E ERDC-RDE-ITL-MS 10 Jul '15

10 Jul '15

Hi all, I’ve recently been profiling several techniques for a workflow that we’ve been trying to improve here at USACE. Originally we used python scripts with sqlite, but we ran into scalability problems on large data sets. This led us to MonetDB, with the promise of columnar-based analysis and the hope of both parallel query’s “under-the-hood” and possibly a distributed workflow across an HPC system. Looking at my profiling results, this has led me to a number of questions that hopefully you all can help us with. I have a single table from the TPC-H benchmark – lineitem – populated with 360million entries: "l_orderkey" INTEGER NOT NULL, "l_partkey" INTEGER NOT NULL, "l_suppkey" INTEGER NOT NULL, "l_linenumber" INTEGER NOT NULL, "l_quantity" DECIMAL(15,2) NOT NULL, "l_extendedprice" DECIMAL(15,2) NOT NULL, "l_discount" DECIMAL(15,2) NOT NULL, "l_tax" DECIMAL(15,2) NOT NULL, "l_returnflag" CHAR(1) NOT NULL, "l_linestatus" CHAR(1) NOT NULL, "l_shipdate" DATE NOT NULL, "l_commitdate" DATE NOT NULL, "l_receiptdate" DATE NOT NULL, "l_shipinstruct" CHAR(25) NOT NULL, "l_shipmode" CHAR(10) NOT NULL, "l_comment" VARCHAR(44) NOT NULL, System Setup: Intel(R) Xeon(R) CPU E5-2660 0 @ 2.20GHz - 16 cores (HT off) / 256G RAM MonetDB version 11.19.15 R version 3.2.0 1. I have followed the instructions here: https://www.monetdb.org/content/embedded-r-monetdb and tried to reproduce these results. I only attempt to reproduce the results from R (reading the data from CSV since – as noted in a previous email to this list – R fails to read in the 360 million rows using MonetDB.R but works fine reading the same amount from CSV), MonetDB, as well as MonetDB with Embedded R. - I compile both R and MonetDB from source. When building R, I include the option to build the R shared library (libR.so) and when compiling MonetDB I include the option for embedded R. - When creating the MonetDB database, I set the option for embedr=true. - The R function from the above URL is used, as well as the same SQL query – and it works…. However, the performance is much worse than using R alone. I know I must be overlooking something. Please see the image at https://goo.gl/wUvB2J (png on Google Drive). The X-axis is the number of rows, and the Y axis is the time in seconds. As you can see, embedded R is unexpectedly much worse than the other two. 2. While running the quantile function, only one core is active – whether embeddedR or just MonetDB’s. 3. When running the following query (after altering the table to include a new column z, of course): sql>update lineitem set z=l_extendedprice / l_quantity; 360011594 affected rows (3m 49s) … Multiple cores are active for the first 10 seconds, then it resorts to single core. A. What am I not understanding about MonetDB’s ability to use multiple cores? The only time I really see it use multiple cores seems to be when doing “copy into”. B. Can someone provide assistance in trying to replicate the Embedded R results? Should it be running in parallel? Or might something in my setup be configured incorrectly. Cheers!

3 3

(no subject)
by musa 10 Jul '15

10 Jul '15

Can someone please provide a minimal example (how to start the server) for running the embedded R feature in windows? Regards, mpa

3 2

RE: Concurrency test causes crash of MonetDB 5 server on Windows
by michael.witt＠uniserv.com 08 Jul '15

08 Jul '15

Hello, Guillaume: Thanks for the tip avoiding concurrency conflicts. We already considered that and changed to code to avoid such conflicts. In the meanwhile I tested both on Windows and Linux machines and now it is clear that it is a problem specific to the Windows builds of MonetDB. The mserver5 on Windows starts with 2 threads serving requests as stated in the console after starting... # Serving database 'uniserv', using 2 threads Then I found a statement about the thread implementation on Windows by Stefan Mangegold: https://www.monetdb.org/pipermail/developers-list/2010-July/003492.html "However, we do not know how well the multi-threading works on Windows. We rely on pthreads, and their implementation on Windows might not be as efficient and/or stable as on Unix-like systems ..." This is absolutely true! My tests show that mserver5 started with 2 threads always crashes after some time firing concurrent read requests. By the way, the windows machine has 2 physical and 2 logical cores. But if I do the same on a Linux machine (also with 2 threads) Mserver5 runs stable. What is the solution to stabilize Mserver5 on Windows? Set the number of threads to 1 by setting the start option: --set gdk_nr_threads=1 In the M5server.bat script. After setting that, Mserver5 run stable on Windows, too. Does anyone know better solutions? Kind regards, Michael -----Original Message----- From: users-list [mailto:users-list-bounces+michael.witt=uniserv.com@monetdb.org] On Behalf Of Guillaume Bassot Sent: Dienstag, 30. Juni 2015 10:26 To: Communication channel for MonetDB users Subject: Re: Concurrency test causes crash of MonetDB 5 server Hello Michael, From my experience, all "write" request executed concurrently always end with concurrency conflict. I'm talking of heavey request during several minutes, so it's easy to get concurrent access. Since i only use monetdb with a single process, stability was greatly improved. Regards Guillaume BASSOT Web Developer +33 (0)1 55 48 02 82 gbassot(a)inbox.fr <mailto:gbassot@inbox.fr> 30, rue Vincent Moris 92240 Malakoff - France Fax : +33 (0)1 55 48 02 79 2015-06-30 10:09 GMT+02:00 <michael.witt(a)uniserv.com <mailto:michael.witt@uniserv.com> >: Hi all, It’s a pity that no one answered the question I asked a few weeks ago about crashes of MonetDB 5 server under windows 64 Bit. So I think I offer more information about a scenario where the databases crashes. Hopefully someone could give me a hint how to improve stability of MonetDB under Windows 64 Bit with concurrent client connections. We are using Monet DB for a couple of months and facing some graved issues with concurrent queries. The problem is, when we query the database with two or more clients in a parallel, the database crashes! These are the facts: MonetDB server version: v11.19.9 ================================ # MonetDB 5 server v11.19.9 "Oct2014-SP2" # Serving database 'uniserv', using 2 threads # Compiled for x86_64-pc-winnt/64bit with 64bit OIDs dynamically linked # Found 8.000 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/ <http://www.monetdb.org/> for further information # Listening for connection requests on mapi:monetdb://QADQSHWIN:50000/ # MonetDB/SQL module loaded Server crashes also occur on versions v11.19.8 and v11.19.7. Operation system: ================= Microsoft Windows Server 2008 R2 Datacenter Version 6.1.7601 Service Pack 1 Build 7601 hosted on VMWare Virtual Platform x64-based PC 2x Processor Intel(R) Xeon(R) CPU E5-2680 v2 @ 2.80GHz, 2800 Mhz, 1 Core(s), 1 Logical Processor(s) Installed Physical Memory (RAM) 8,00 GB Available Physical Memory 5,25 GB Total Virtual Memory 16,0 GB Server installation folder: =========================== E:\Program Files\MonetDB\MonetDB5 Server startup script M5server.bat: =================================== @REM Copyright August 2008-2014 MonetDB B.V. @REM All Rights Reserved. @echo off setlocal rem figure out the folder name set MONETDB=%~dp0 rem remove the final backslash from the path set MONETDB=%MONETDB:~0,-1% rem extend the search path with our EXE and DLL folders set PATH=%MONETDB%\bin;%MONETDB%\lib;%MONETDB%\lib\MonetDB5;%PATH% rem prepare the arguments to mserver5 to tell it where to put the dbfarm REM Uniserv: Don't use APPDATA, so set APPDATA to empty String. set APPDATA= if "%APPDATA%" == "" goto usevar rem if the APPDATA variable does exist, put the database there set MONETDBDIR=%APPDATA%\MonetDB5 set MONETDBFARM="--dbpath=%MONETDBDIR%\dbfarm\demo" goto skipusevar :usevar rem if the APPDATA variable does not exist, put the database in the rem installation folder (i.e. default location, so no command line argument) REM Uniserv: Specify the DB installation folder rem set MONETDBDIR=%MONETDB%\var\MonetDB5 set MONETDBDIR=%MONETDB%\db set MONETDBFARM="--dbpath=%MONETDBDIR%\dbfarm\uniserv" :skipusevar rem the SQL log directory used to be in %MONETDBDIR%, but we now rem prefer it inside the dbfarm, so move it there if not exist "%MONETDBDIR%\sql_logs" goto skipmove for /d %%i in ("%MONETDBDIR%"\sql_logs\*) do move "%%i" "%MONETDBDIR%\dbfarm"\%%~ni\sql_logs rmdir "%MONETDBDIR%\sql_logs" :skipmove rem start the real server "%MONETDB%\bin\mserver5.exe" --set "prefix=%MONETDB%" --set mapi_open=true --set mapi_port=50000 --set max_clients=1000 --set "exec_prefix=%MONETDB%" %MONETDBFARM% %* if ERRORLEVEL 1 pause endlocal MonetDB JDBC Client: ================ We query the database via the latest MonetDB JDBC driver loaded from the central Maven repository: <groupId>monetdb</groupId> <artifactId>monetdb-jdbc</artifactId> <version>2.14</version> Database tables: ================ We create tables called "rule_result_<n>" CREATE TABLE "dq_scorecard"."rule_results_1" ("id" BIGINT NOT NULL PRIMARY KEY AUTO_INCREMENT, "record_id" VARCHAR(1024), "rule_1" INT, "rule_2" INT, ...); Each table has about 50+ INT columns. Query scenarios: ================ We have concurrent read access via JDBC driver and Spring Data Access Objects, the query look like that: *Client 1: 50 aggregate queries on 50 different columns on one database table like that: SELECT AVG("rule_n") FROM "dq_scorecard"."rule_results_1" WHERE "rule_n" >= 0 *Client 2: 50 aggregate queries on 50 different columns on another database table like that: SELECT AVG("rule_n") FROM "dq_scorecard"."rule_results_2" WHERE "rule_n" >= 0 *Client 3: 50 aggregate queries on 50 different columns on another database table like that: SELECT AVG("rule_n") FROM "dq_scorecard"."rule_results_3" WHERE "rule_n" >= 0 All clients run concurrently, at the moment we don't use a connection pool and create a connection for every query. During concurrent access the M5server crashes. The server crashes when each table has 100 records! Our production tables can have up to 50. 000. 000 of records! Kind regards, Michael From: users-list [mailto:users-list-bounces+michael.witt <mailto:users-list-bounces%2Bmichael.witt> =uniserv.com(a)monetdb.org <mailto:uniserv.com@monetdb.org> ] On Behalf Of michael.witt(a)uniserv.com <mailto:michael.witt@uniserv.com> Sent: Donnerstag, 28. Mai 2015 14:32 To: users-list(a)monetdb.org <mailto:users-list@monetdb.org> Subject: Concurrency test causes crash of MonetDB 5 server Hi, we experience a crash of MonetDB 5 server during tests of a multi-threaded client access via JDBC-Driver and MapiSocket: The application is already optimized to avoid “concurrency conflicts” because of the “Optimistic Concurrency Control” concept. The test simulates client access via two threads. All update operations (INSERT, UPDATE, DELETE, DROP TABLE, CREATE TABLE) are done in serial. All read operations are done in parallel to other read operations or to update operations. The test causes a crash of mserver5.exe after a few minutes. If the test uses just one thread, all read and update operations are done in serial and the server does not crash. What is the rule of thumb to avoid crashes when using several client threads? Crash details: Problem signature: Problem Event Name: APPCRASH Application Name: mserver5.exe Application Version: 0.0.0.0 Application Timestamp: 55389f58 Fault Module Name: StackHash_8ec6 Fault Module Version: 6.1.7601.18247 Fault Module Timestamp: 521eaf24 Exception Code: c0000374 Exception Offset: 00000000000c4102 OS Version: 6.1.7601.2.1.0.144.8 Locale ID: 1031 Additional Information 1: 8ec6 Additional Information 2: 8ec62499039d1d8234b025a62c725eb7 Additional Information 3: f8f3 Additional Information 4: f8f3e520847c0684b15d4080171779ef # MonetDB 5 server v11.19.9 "Oct2014-SP2" # Serving database 'uniserv', using 4 threads # Compiled for x86_64-pc-winnt/64bit with 64bit OIDs dynamically linked # Found 10.000 GiB available main-memory. .. # MonetDB/SQL module loaded Michael Witt _______________________________________________ users-list mailing list users-list(a)monetdb.org <mailto:users-list@monetdb.org> https://www.monetdb.org/mailman/listinfo/users-list

2 1

Field conversion sticks to subsequent queries
by Dennis Lorson 08 Jul '15

08 Jul '15

Hi all, I have a table with an INT column. The table is sorted by that column, and monetdb has recognized the sort order. While performing this query: -------------- SELECT * FROM table WHERE column = 12345678900; -------------- monetdb converts <table.column> to BIGINT/LNG as evidenced by the trace: -------------- 3612684 | X_17=<tmp_2352>[89617785] := batcalc.lng(X_16=<tmp_7142>[89617785]); -------------- This seems logical behavior since the condition value would overflow INT. (Of course the result set is empty by definition, but it’s a valid query) However, the same happens when I then perform subsequent queries with values that *can* be represented by INT: -------------- SELECT * FROM table WHERE column = 123; -------------- It is only when an explicit INT value is entered, that the type conversion is stopped for that query and subsequent ones: -------------- SELECT * FROM table WHERE column = CAST(123 AS INT); -------------- In other words, once the type conversion is performed once, it seems to “stick” to subsequent queries. Type conversion on that column takes ~4s on a 90MM row table here, while the regular query time is usually sub-millisecond. Entering the “wrong" value once will influence the DB performance heavily until restarted, which is far from a desirable scenario. Of course I could always clamp my value to INT range at the application level, but this is brittle, and the DB could do the right thing here. Is there a design consideration that I’m missing, or should I file this as a bug/performance issue? Thanks, Dennis

4 4

INNER JOIN with NULL values returning incorrect result
by Dennis Pallett 02 Jul '15

02 Jul '15

Hi all, I'm trying to do a simple INNER JOIN with two tables whereby the 1st table can contain NULL values in the join column. The query is as such: ---------- SELECT "B"."id", "A"."other_id" FROM "A" INNER JOIN "B" ON "B"."id" = "A"."other_id" WHERE "A"."afnemer" = '789' AND "A"."orgid" = 123; ---------- However this returns an incorrect result, such as: ---------- +------+----------+ | id | other_id | +======+==========+ | 143 | 143 | | 166 | 166 | | 147 | 147 | | 264 | 264 | | 323 | null | | 323 | 271 | | 271 | 275 | | 275 | 269 | | 269 | 150 | ---------- Clearly the JOIN has somehow mapped incorrect rows together from tables A and B. If I add a NOT NULL condition to the query on table A, I get correct results. This is the following query: ---------- SELECT "A"."other_id", "B"."id" , "A"."orgid" FROM "A" INNER JOIN "B" ON "B"."id" = "A"."other_id" WHERE "A"."afnemer" = '789' AND "A"."orgid" = 123 AND "A".other_id IS NOT NULL; ---------- Is this a known bug in MonetDB? Should I always add a NOT NULL condition to my JOIN queries? Best regards, Dennis Pallett

2 3

Can't start monetdb 11.19.15
by Brian Hood 01 Jul '15

01 Jul '15

Hi All, I seems when i look at the output i of the error log it seems there are missing methods, i have seen this as a bug report from a while ago online but i can't see the see the solution. Any ideas ? [root@dev-vnc-01 /]# monetdbd start /var/monetdb5/dbfarm [root@dev-vnc-01 /]# [root@dev-vnc-01 /]# monetdb status name state health remarks threatmonitor S 5d 100% 7s mapi:monetdb://dev-vnc-01:50000/threatmonitor [root@dev-vnc-01 /]# monetdb start threatmonitor starting database 'threatmonitor'... FAILED start: starting 'threatmonitor' failed: database 'threatmonitor' started up, but failed to open up a communication channel [root@dev-vnc-01 /]# pacman -Q monetdb monetdb 11.19.15-1 [root@dev-vnc-01 /]# monetdb -v MonetDB Database Server Toolkit v1.1 (Oct2014-SP4) [root@dev-vnc-01 /]# tail -70 /var/monetdb5/dbfarm/merovingian.log 2015-06-25 23:31:58 MSG merovingian[293]: monitoring dbfarm /var/monetdb5/dbfarm 2015-06-25 23:32:18 MSG merovingian[293]: accepting connections on TCP socket 0.0.0.0:50000 2015-06-25 23:32:18 MSG merovingian[293]: accepting connections on UNIX domain socket /tmp/.s.monetdb.50000 2015-06-25 23:32:38 MSG discovery[293]: listening for UDP messages on 0.0.0.0:50000 2015-06-25 23:32:38 MSG control[293]: accepting connections on UNIX domain socket /tmp/.s.merovingian.50000 2015-06-25 23:32:38 MSG discovery[293]: new neighbour dev-vnc-01 (dev-vnc-01) 2015-06-25 23:32:53 MSG control[293]: (local): created database 'threatmonitor' 2015-06-25 23:32:59 MSG control[293]: (local): served status list 2015-06-25 23:32:59 MSG control[293]: (local): released database 'threatmonitor' 2015-06-25 23:32:59 MSG discovery[293]: new database mapi:monetdb://dev-vnc-01:50000/threatmonitor (ttl=660s) 2015-06-25 23:33:08 MSG merovingian[293]: starting database 'threatmonitor', up min/avg/max: 0s/0s/0s, crash average: 0.00 0.00 0.00 (0-0=0) 2015-06-25 23:33:08 MSG merovingian[293]: sending process 304 (database 'threatmonitor') the TERM signal 2015-06-25 23:33:08 MSG threatmonitor[304]: arguments: /usr/bin/mserver5 --dbpath=/var/monetdb5/dbfarm/threatmonitor --set merovingian_uri=mapi:monetdb://dev-vnc-01:50000/threatmonitor --set mapi_open=false --set mapi_port=0 --set mapi_usock=/var/monetdb5/dbfarm/threatmonitor/.mapi.sock --set monet_vault_key=/var/monetdb5/dbfarm/threatmonitor/.vaultkey --set gdk_nr_threads=2 --set max_clients=64 --set sql_optimizer=default_pipe --set monet_daemon=yes 2015-06-25 23:33:08 MSG threatmonitor[304]: # MonetDB 5 server v11.19.15 "Oct2014-SP4" 2015-06-25 23:33:08 MSG threatmonitor[304]: # Serving database 'threatmonitor', using 2 threads 2015-06-25 23:33:08 MSG threatmonitor[304]: # Compiled for x86_64-unknown-linux-gnu/64bit with 64bit OIDs dynamically linked 2015-06-25 23:33:08 MSG threatmonitor[304]: # Found 7.411 GiB available main-memory. 2015-06-25 23:33:08 MSG threatmonitor[304]: # Copyright (c) 1993-July 2008 CWI. 2015-06-25 23:33:08 MSG threatmonitor[304]: # Copyright (c) August 2008-2015 MonetDB B.V., all rights reserved 2015-06-25 23:33:08 MSG threatmonitor[304]: # Visit http://www.monetdb.org/ for further information 2015-06-25 23:33:08 MSG threatmonitor[304]: !TypeException:user.main[1412]:object code for command rapi.prelude missing 2015-06-25 23:33:08 MSG threatmonitor[304]: !TypeException:user.main[1412]:'rapi.prelude' undefined in: _1412:void := rapi.prelude() 2015-06-25 23:33:08 MSG merovingian[293]: database 'threatmonitor' (304) has exited with exit status 0 2015-06-25 23:33:08 MSG merovingian[293]: database 'threatmonitor' has shut down 2015-06-25 23:33:08 ERR merovingian[293]: client error: database 'threatmonitor' started up, but failed to open up a communication channel 2015-06-25 23:33:08 MSG merovingian[293]: starting database 'threatmonitor', up min/avg/max: 0s/0s/0s, crash average: 0.00 0.00 0.00 (1-1=0) 2015-06-25 23:33:09 MSG merovingian[293]: sending process 307 (database 'threatmonitor') the TERM signal 2015-06-25 23:33:09 MSG threatmonitor[307]: arguments: /usr/bin/mserver5 --dbpath=/var/monetdb5/dbfarm/threatmonitor --set merovingian_uri=mapi:monetdb://dev-vnc-01:50000/threatmonitor --set mapi_open=false --set mapi_port=0 --set mapi_usock=/var/monetdb5/dbfarm/threatmonitor/.mapi.sock --set monet_vault_key=/var/monetdb5/dbfarm/threatmonitor/.vaultkey --set gdk_nr_threads=2 --set max_clients=64 --set sql_optimizer=default_pipe --set monet_daemon=yes 2015-06-25 23:33:09 MSG threatmonitor[307]: # MonetDB 5 server v11.19.15 "Oct2014-SP4" 2015-06-25 23:33:09 MSG threatmonitor[307]: # Serving database 'threatmonitor', using 2 threads 2015-06-25 23:33:09 MSG threatmonitor[307]: # Compiled for x86_64-unknown-linux-gnu/64bit with 64bit OIDs dynamically linked 2015-06-25 23:33:09 MSG threatmonitor[307]: # Found 7.411 GiB available main-memory. 2015-06-25 23:33:09 MSG threatmonitor[307]: # Copyright (c) 1993-July 2008 CWI. 2015-06-25 23:33:09 MSG threatmonitor[307]: # Copyright (c) August 2008-2015 MonetDB B.V., all rights reserved 2015-06-25 23:33:09 MSG threatmonitor[307]: # Visit http://www.monetdb.org/ for further information 2015-06-25 23:33:09 MSG threatmonitor[307]: !TypeException:user.main[1412]:object code for command rapi.prelude missing 2015-06-25 23:33:09 MSG threatmonitor[307]: !TypeException:user.main[1412]:'rapi.prelude' undefined in: _1412:void := rapi.prelude() 2015-06-25 23:33:09 MSG merovingian[293]: database 'threatmonitor' (307) has exited with exit status 0 2015-06-25 23:33:09 MSG merovingian[293]: database 'threatmonitor' has shut down 2015-06-25 23:33:13 ERR merovingian[293]: client error: database 'threatmonitor' started up, but failed to open up a communication channel 2015-06-26 00:08:20 MSG control[293]: (local): served status list 2015-06-26 00:08:20 ERR control[293]: (local): received stop signal for non running database: threatmonitor 2015-06-26 00:08:29 MSG merovingian[293]: caught SIGTERM, starting shutdown sequence 2015-06-26 00:08:30 MSG control[293]: control channel closed 2015-06-26 00:08:31 MSG merovingian[293]: Merovingian 1.7 stopped 2015-07-01 12:57:10 MSG merovingian[9]: Merovingian 1.7 (Oct2014-SP4) starting 2015-07-01 12:57:10 MSG merovingian[9]: monitoring dbfarm /var/monetdb5/dbfarm 2015-07-01 12:57:30 MSG merovingian[9]: accepting connections on TCP socket 0.0.0.0:50000 2015-07-01 12:57:30 MSG merovingian[9]: accepting connections on UNIX domain socket /tmp/.s.monetdb.50000 2015-07-01 12:57:50 MSG discovery[9]: listening for UDP messages on 0.0.0.0:50000 2015-07-01 12:57:50 MSG control[9]: accepting connections on UNIX domain socket /tmp/.s.merovingian.50000 2015-07-01 12:57:50 MSG discovery[9]: new neighbour dev-vnc-01 (dev-vnc-01) 2015-07-01 12:57:50 MSG discovery[9]: new database mapi:monetdb://dev-vnc-01:50000/threatmonitor (ttl=660s) 2015-07-01 12:57:54 MSG control[9]: (local): served status list 2015-07-01 12:58:03 MSG control[9]: (local): served status list 2015-07-01 12:58:03 MSG merovingian[9]: starting database 'threatmonitor', up min/avg/max: 0s/7s/27s, crash average: 0.00 0.00 0.00 (10-10=0) 2015-07-01 12:58:03 MSG merovingian[9]: sending process 15 (database 'threatmonitor') the TERM signal 2015-07-01 12:58:03 MSG threatmonitor[15]: arguments: /usr/bin/mserver5 --dbpath=/var/monetdb5/dbfarm/threatmonitor --set merovingian_uri=mapi:monetdb://dev-vnc-01:50000/threatmonitor --set mapi_open=false --set mapi_port=0 --set mapi_usock=/var/monetdb5/dbfarm/threatmonitor/.mapi.sock --set monet_vault_key=/var/monetdb5/dbfarm/threatmonitor/.vaultkey --set gdk_nr_threads=2 --set max_clients=64 --set sql_optimizer=default_pipe --set monet_daemon=yes 2015-07-01 12:58:03 MSG threatmonitor[15]: # MonetDB 5 server v11.19.15 "Oct2014-SP4" 2015-07-01 12:58:03 MSG threatmonitor[15]: # Serving database 'threatmonitor', using 2 threads 2015-07-01 12:58:03 MSG threatmonitor[15]: # Compiled for x86_64-unknown-linux-gnu/64bit with 64bit OIDs dynamically linked 2015-07-01 12:58:03 MSG threatmonitor[15]: # Found 7.411 GiB available main-memory. 2015-07-01 12:58:03 MSG threatmonitor[15]: # Copyright (c) 1993-July 2008 CWI. 2015-07-01 12:58:03 MSG threatmonitor[15]: # Copyright (c) August 2008-2015 MonetDB B.V., all rights reserved 2015-07-01 12:58:03 MSG threatmonitor[15]: # Visit http://www.monetdb.org/ for further information 2015-07-01 12:58:03 MSG threatmonitor[15]: !TypeException:user.main[1412]:object code for command rapi.prelude missing 2015-07-01 12:58:03 MSG threatmonitor[15]: !TypeException:user.main[1412]:'rapi.prelude' undefined in: _1412:void := rapi.prelude() 2015-07-01 12:58:03 MSG merovingian[9]: database 'threatmonitor' (15) has exited with exit status 0 2015-07-01 12:58:03 MSG merovingian[9]: database 'threatmonitor' has shut down 2015-07-01 12:58:03 ERR control[9]: (local): failed to fork mserver: database 'threatmonitor' started up, but failed to open up a communication channel Regards, Brian Hood

2 3

MonetDB performance disappointing
by Martin Schwitalla 30 Jun '15

30 Jun '15

Hello, I`m doing some experiments with a database, which originally runs on PostgreSQL. But some queries take very long on this system. Especially one query type, which takes about 14s to finish. The idea was to port it to MonetDB, because the database is not that big (~8GB) and it`s an analytical task. But suprisingly MonetDB takes even longer to finish. With the default pipeline it takes about 22-23s. I tried various pipelines and the minimal_pipe was the best. It finished the task in about 16,5s, better, but still not great. I`m asking myself what I could do to further improve the performance. I added some primary and foreign key constraints and set the database to readonly, but it didn`t do much. The trace tells me that some joins take the most time and some subselects also take much time. But nothing besides that. The query has 10 joins. Do you have any advices for me? Best regards, Martin

5 11

Concurrency test causes crash of MonetDB 5 server
by michael.witt＠uniserv.com 30 Jun '15

30 Jun '15

Hi, we experience a crash of MonetDB 5 server during tests of a multi-threaded client access via JDBC-Driver and MapiSocket: The application is already optimized to avoid "concurrency conflicts" because of the "Optimistic Concurrency Control" concept. The test simulates client access via two threads. All update operations (INSERT, UPDATE, DELETE, DROP TABLE, CREATE TABLE) are done in serial. All read operations are done in parallel to other read operations or to update operations. The test causes a crash of mserver5.exe after a few minutes. If the test uses just one thread, all read and update operations are done in serial and the server does not crash. What is the rule of thumb to avoid crashes when using several client threads? Crash details: Problem signature: Problem Event Name: APPCRASH Application Name: mserver5.exe Application Version: 0.0.0.0 Application Timestamp: 55389f58 Fault Module Name: StackHash_8ec6 Fault Module Version: 6.1.7601.18247 Fault Module Timestamp: 521eaf24 Exception Code: c0000374 Exception Offset: 00000000000c4102 OS Version: 6.1.7601.2.1.0.144.8 Locale ID: 1031 Additional Information 1: 8ec6 Additional Information 2: 8ec62499039d1d8234b025a62c725eb7 Additional Information 3: f8f3 Additional Information 4: f8f3e520847c0684b15d4080171779ef # MonetDB 5 server v11.19.9 "Oct2014-SP2" # Serving database 'uniserv', using 4 threads # Compiled for x86_64-pc-winnt/64bit with 64bit OIDs dynamically linked # Found 10.000 GiB available main-memory. .. # MonetDB/SQL module loaded Michael Witt

2 2

JDBC PreparedStatement crashing the Database!!
by Vijay Krishna 29 Jun '15

29 Jun '15

Hi, I recently faced an issue with MonetDB JDBC PreparedStatement. I have a table with 15 columns out of which 14 are foreign keys to columns in another 14 tables. When I try to insert a valid record (that satisfies all the FK relations) using PreparedStatement, the database gets crashed while executing the line, preparedStatement = connection.prepareStatement(sqlString); On trial and error testing, I found that this works very fine until a table has 13 FK relations. If the tables has 14 FKs, then the database crashes. On a brighter side, this operation works very fine in terminal (mclient) with 14 FKs even. Is there any limit on the FKs with prepared statement? Or what am I doing wrong here? Any help much appreciated. Thanks in advance. Regards, Vijayakrishna.P. Mobile : (+91) 9500402305.

2 2

Is minimal_pipe enforced on long MAL plans?
by Roberto Cornacchia 22 Jun '15

22 Jun '15

Hi there, Is minimal_pipe enforced under any condition? For example when the the MAL plan generated by rel2bin is longer than a given threshold? Context: I have a pretty long query which results in a MAL plan of 33K lines. Though the query is a bit long, I still would expect a 2-3K lines MAL plan. The plan indeed contains the same pieces of code re-evaluated over and over. It first, I simply blamed commonTerms not doing its job. The thing is, the MAL plans generated with minimal_pipe and default_pipe are absolutely identical for this query. Hence my question above. Best, Roberto

3 3

MonetDb Udf
by Fatma Nur Özdemir 19 Jun '15

19 Jun '15

Hello, We are using MonetDB5 server v11.19.15 “Oct2014-SP4” windows version and we want to use a function that we wrote in C programming language. However, we cannot use this user defined function within MonetDB. Maybe, this problem is about windows version because we are not sure that windows version of MonetDb supports this feature. If anybody knows something about this, please inform us. Thank you in advance. Fatma Nur [http://www.bilyoner.com/fm/mail/mailalti_Bmw-applewatch.jpg]<http://www.bilyoner.com/kampanyalar/2015haziranagustosmp.jspx> Bu elektronik posta ve onunla iletilen butun dosyalar sadece gondericisi tarafindan almasi amaclanan yetkili gercek ya da tuzel kisinin kullanimi icindir. Eger soz konusu yetkili alici degilseniz bu elektronik postanin icerigini aciklamaniz, kopyalamaniz, yonlendirmeniz ve kullanmaniz hukuka aykiri ve yasaktir, eger bu mesaji yanlislikla aldiysaniz lutfen gondereni ile temasa geciniz ve bu elektronik postayi siliniz. B�LYONER �nteraktif Hizmetleri A.S. bu mesajin icerdigi bilgilerin dogrulugu veya eksiksiz oldugu konusunda herhangi bir garanti vermemektedir. Bu nedenle bu bilgilerin ne sekilde olursa olsun iceriginden, iletilmesinden, alinmasindan ve saklanmasindan sorumlu degildir. Bu mesajdaki gorusler yalnizca gonderen kisiye aittir ve Bilyoner �nteraktif Hizmetleri A.S. nin goruslerini yansitmayabilir. Bu e-posta bilinen butun bilgisayar viruslerine karsi taranmistir.

1 0

Re: Monetdb-R integration
by imad hajj chahine 19 Jun '15

19 Jun '15

Hi Hannes, This error is due that the "2-age" column has more than 2 distinct values, there is a space after some values. can you run a trim on the "2-Age" and then try it again. As for the vector thing, I didnt get the point, shouldnt the db engine split the data into vectors and feed the aggregate function which return one data point? Thanks On Fri, Jun 19, 2015 at 11:12 AM, Hannes Mühleisen <Hannes.Muehleisen(a)cwi.nl > wrote: > I am running attached script, which I pieced together from your emails. > This does not run, it produces the following error: > > Error running R expression. Error message: Error in t.test.formula(arg1 ~ > arg2) : > grouping factor must have exactly 2 levels > Calls: as.data.frame -> <Anonymous> -> t.test -> t.test.formula > > > > > In general, for aggregations, the R code needs to return a vector with a > single entry for each group. > > > Best, > > Hannes > > > > > On 19 Jun 2015, at 09:44, imad hajj chahine <imad.hajj.chahine(a)gmail.com> > wrote: > > > > Thanks, if you need more information from my side let me know. > > > > Also can you answer me on the following when i use 2 functions one to > retrieve Pvalue and one for the stat value: > > Am I doing the correct way to retrieve the stat and p-value, is the > ttest get executed twice in this case? is there a more optimal way where i > can retrieve both information. > > > > On Fri, Jun 19, 2015 at 10:39 AM, Hannes Mühleisen < > Hannes.Muehleisen(a)cwi.nl> wrote: > > Looking into this at the moment. > > > > > On 16 Jun 2015, at 09:56, Dimitar Nedev < > dimitar.nedev(a)monetdbsolutions.com> wrote: > > > > > > Hannes, do you know if the STD_DEV is the issue there or if the > queries should actually yield different results? > > > > > > Dimitar > > > > > >> On 2015-Jun-15, at 13:28 , imad hajj chahine < > imad.hajj.chahine(a)gmail.com> wrote: > > >> > > >> Hi, > > >> > > >> Shouldn't we have the same result from running the first query and > the second query? > > >> after all its an aggregate function and should return the test for > each group. > > >> > > >> I checked that monetdb had problem with running group by with > statistics functions like STD_DEV, are these 2 issues related? > > >> > > >> Thank you. > > >> > > >> > > >> > > >> > > >> On Mon, Jun 15, 2015 at 1:52 PM, Dimitar Nedev < > dimitar.nedev(a)monetdbsolutions.com> wrote: > > >> Hi Imad, > > >> > > >> Here is the output I got from running the queries. Note, this might > not be entirely representative since I did clean-up some data before > loading it. > > >> > > >> sql>select "2-Age", "tone"."ttest2"("9-Score",6.08963) from > "tone"."Marketing_Loyalty_4700298d-9862-40b3-9028-b0f15dab9dea" where > "2-Age" is not null group by "2-Age"; > > >> +---------+--------------------------+ > > >> | 2-Age | L1 | > > >> +=========+==========================+ > > >> | [10-20] | 0.89311103869734754 | > > >> +---------+--------------------------+ > > >> 3 tuples (3.218ms) > > >> > > >> sql>select '[10-20]' as "2-Age","tone"."ttest2"("9-Score",6.08963) > from "tone"."Marketing_Loyalty_4700298d-9862-40b3-9028-b0f15dab9dea" where > "2-Age" = '[10-20]' > > >> more>union all > > >> more>select '[21-30]' as "2-Age","tone"."ttest2"("9-Score",6.08963) > from "tone"."Marketing_Loyalty_4700298d-9862-40b3-9028-b0f15dab9dea" where > "2-Age" = '[21-30]' > > >> more>union all > > >> more>select '[> 30]' as "2-Age","tone"."ttest2"("9-Score",6.08963) > from "tone"."Marketing_Loyalty_4700298d-9862-40b3-9028-b0f15dab9dea" where > "2-Age" = '> 30'; > > >> +---------+--------------------------+ > > >> | 2-Age | L1 | > > >> +=========+==========================+ > > >> | [10-20] | 0.14150742355349472 | > > >> | [21-30] | 0.32872830865003566 | > > >> | [> 30] | 0.37127075363219841 | > > >> +---------+--------------------------+ > > >> 3 tuples (8.825ms) > > >> > > >> > > >> sql>select "tone"."ttest2samplesStatistic"("9-Score", "4-Gender") as > "stat", "tone"."ttest2samples"("9-Score", "4-Gender") as "pvalue" from > "tone"."Marketing_Loyalty_4700298d-9862-40b3-9028-b0f15dab9dea" group by > "2-Age"; > > >> +--------------------------+--------------------------+ > > >> | stat | pvalue | > > >> +==========================+==========================+ > > >> | -2.1588506639013736 | 0.032555033970368859 | > > >> +--------------------------+--------------------------+ > > >> 1 tuple (13.287ms) > > >> > > >> > > >> I dropped the aggregate with: > > >> sql>DROP AGGREGATE "tone"."ttest2samplesStatistic"; > > >> operation successful (2.049ms) > > >> > > >> > > >> Best regards, > > >> Dimitar > > >> > > >>> On 2015-Jun-12, at 00:46 , imad hajj chahine < > imad.hajj.chahine(a)gmail.com> wrote: > > >>> > > >>> Thank you Dimitar, > > >>> > > >>> I was using Squirrel to connect from my host machine to the VM. > > >>> > > >>> Now when I connect using SSH-client and run the short group by query > it works but the result is not similar to the union queries (PS: i also run > it in the VM direclty and had the same result) > > >>> > > >>> > > >>> +---------+--------------------------+ > > >>> | 2-Age | pval | > > >>> +=========+==========================+ > > >>> | [21-30] | 0.99996611809271041 | > > >>> +---------+--------------------------+ > > >>> 3 tuples (2.638ms) > > >>> > > >>> > > >>> What i am trying to achieve is to run a one sample ttest for all the > age categories, and it seems that the group by is ignored. > > >>> > > >>> I have also created 2 functions for the two sample ttest: > > >>> > > >>> CREATE AGGREGATE "tone"."ttest2samples"(arg1 double, arg2 > varchar(250)) RETURNS double LANGUAGE R { t.test(arg1 ~ arg2)$p.value }; > > >>> CREATE AGGREGATE "tone"."ttest2samplesStatistic"(arg1 double, arg2 > varchar(250)) RETURNS double LANGUAGE R { t.test(arg1 ~ arg2)$statistic }; > > >>> > > >>> select "tone"."ttest2samplesStatistic"("9-Score", "4-Gender") as > "stat", "tone"."ttest2samples"("9-Score", "4-Gender") as "pvalue" from > "tone"."Marketing_Loyalty_4700298d-9862-40b3-9028-b0f15dab9dea" group by > "2-Age" > > >>> > > >>> Am I doing the correct way to retrieve the stat and p-value, is the > ttest get executed twice in this case? is there a more optimal way where i > can retrieve both information. Please note that I also have the same > grouping issue as before. > > >>> > > >>> Do you have the same behavior, or I am missing something in the > creation of these functions. > > >>> > > >>> A final question, how do u drop a R function? i am trying to execute > the following: > > >>> drop all function "tone"."ttest2samplesStat" > > >>> > > >>> > > >>> Thank you. > > >>> > > >>> > > >>> > > >>> On Thu, Jun 11, 2015 at 11:28 PM, Dimitar Nedev < > dimitar.nedev(a)monetdbsolutions.com> wrote: > > >>> Hi Imad, > > >>> > > >>> Hannes asked me to look at your issue with the MonetDB-R VM. > > >>> > > >>> I loaded the data and ran the queries you mentioned, but I did not > encounter any issues. This holds for both the short and the union queries. > I am also not familiar with the issue you report, nor its cause. At this > time I think it is local to your instance. If you can export your db, or > re-load the data, I would recommend you to create a new instance with a > fresh image and try again. If you import the image as a new VM instance, > you can keep your old one around as well. > > >>> > > >>> Please note that during the test: > > >>> - I ran as the admin (monetdb) user, as the one created default on > the VM image cannot create new schemas. > > >>> - I have to clean the data a bit up to load it, removing dozen lines > or so. > > >>> - Renamed ttest1 to ttest2 to match the queries. > > >>> > > >>> Best regards, > > >>> Dimitar > > >>> > > >>>>>>>>> Begin forwarded message: > > >>>>>>>>> > > >>>>>>>>> From: imad hajj chahine <imad.hajj.chahine(a)gmail.com> > > >>>>>>>>> Subject: Re: Monetdb-R integration > > >>>>>>>>> Date: 10 Jun 2015 10:11:48 CEST > > >>>>>>>>> To: Hannes Mühleisen <Hannes.Muehleisen(a)cwi.nl> > > >>>>>>>>> > > >>>>>>>>> Hi, > > >>>>>>>>> > > >>>>>>>>> I am using the virtual box provided from > https://www.monetdbsolutions.com/solutions/cloud/vm: > > >>>>>>>>> OS:linux version 3.10.0-123.9.3.el7.x86_64 ( > builder(a)kbuilder.dev.centos.org) > > >>>>>>>>> Monetdb: V1.7: (Oct2014-SP1) > > >>>>>>>>> R: 3.1.2 (2014-10-31) > > >>>>>>>>> > > >>>>>>>>> > > >>>>>>>>> > > >>>>>>>>> create table > "tone"."Marketing_Loyalty_4700298d-9862-40b3-9028-b0f15dab9dea" ("Consumer > Number" INTEGER,"1-Disloyalty" varchar(250),"2-Age" varchar(250),"3-Amount > transferred" INTEGER,"4-Gender" varchar(250),"5-Citiz enship" > varchar(250),"6- Profession" varchar(250),"7-City" varchar(250),"8-1 NC > Billboard" varchar(250),"8-2 NC Friends" varchar(250),"8-3 NC Magazine" > varchar(250),"8-4 NC Others" varchar(250),"9-Score" decimal(18,3),"10- Type > of operation" varchar(250),"11-1 Welcoming" INTEGER,"11-2 Waiting time" > INTEGER,"11-3 Time to finish request" INTEGER,"11-4 Fees" INTEGER,"11-5 > Courtesy of employees" INTEGER,"11-6 Branch layout" INTEGER,"11-7 Attention > to special requests" INTEGER,"12- Total time for service" > decimal(18,3),"13- Distance from POS" decimal(18,3),"14- Weight" INTEGER) > > >>>>>>>>> > > >>>>>>>>> COPY offset 2 INTO > "tone"."Marketing_Loyalty_4700298d-9862-40b3-9028-b0f15dab9dea" FROM > '/home/App_Data/TestingDataSimple_2003_Marketing_Loyalty_4700298d-9862-40b3-9028-b0f15dab9dea.csv' > USING DELIMITERS ',','\n','"'; > > >>>>>>>>> > > >>>>>>>>> Thanks > > >>>>>>>>> > > >>>>>>>>> On Wed, Jun 10, 2015 at 10:50 AM, Hannes Mühleisen < > Hannes.Muehleisen(a)cwi.nl> wrote: > > >>>>>>>>> Hello Imad, > > >>>>>>>>> > > >>>>>>>>> which version of MonetDB and R are you using? Which operating > system? > > >>>>>>>>> > > >>>>>>>>> Can you please also include the commands you used to load your > CSV into MonetDB/ create the table? > > >>>>>>>>> > > >>>>>>>>> Thanks, > > >>>>>>>>> > > >>>>>>>>> Hannes > > >>>>>>>>> > > >>>>>>>>>> On 10 Jun 2015, at 00:25, imad hajj chahine < > imad.hajj.chahine(a)gmail.com> wrote: > > >>>>>>>>>> > > >>>>>>>>>> Hi Hannes, > > >>>>>>>>>> > > >>>>>>>>>> I am testing Monetdb-R integration to do some statistical > test, i followed the tutorial you posted on youtube and i have created the > following function: > > >>>>>>>>>> > > >>>>>>>>>> CREATE AGGREGATE "tone"."ttest1"(arg1 double, arg2 double) > RETURNS double LANGUAGE R { t.test(arg1, arg2)$p.value }; > > >>>>>>>>>> > > >>>>>>>>>> this function will basically do a one sample t-test. > > >>>>>>>>>> > > >>>>>>>>>> I have loaded the sample attached file into a demo database > and trying to run the following query: > > >>>>>>>>>> > > >>>>>>>>>> select "2-Age", "tone"."ttest2"("9-Score",6.08963) from > "tone"."Marketing_Loyalty_4700298d-9862-40b3-9028-b0f15dab9dea" where > "2-Age" is not null group by "2-Age" > > >>>>>>>>>> > > >>>>>>>>>> The following error happened > > >>>>>>>>>> Error: protocol violation: unexpected line in data block: . > > >>>>>>>>>> SQLState: M0M10 > > >>>>>>>>>> ErrorCode: 0 > > >>>>>>>>>> > > >>>>>>>>>> alternatively i managed to have the intended result by > running this query: > > >>>>>>>>>> > > >>>>>>>>>> select '[10-20]' as > "2-Age","tone"."ttest2"("9-Score",6.08963) from > "tone"."Marketing_Loyalty_4700298d-9862-40b3-9028-b0f15dab9dea" where > "2-Age" = '[10-20]' > > >>>>>>>>>> union all > > >>>>>>>>>> select '[21-30]' as > "2-Age","tone"."ttest2"("9-Score",6.08963) from > "tone"."Marketing_Loyalty_4700298d-9862-40b3-9028-b0f15dab9dea" where > "2-Age" = '[21-30]' > > >>>>>>>>>> union all > > >>>>>>>>>> select '[> 30]' as "2-Age","tone"."ttest2"("9-Score",6.08963) > from "tone"."Marketing_Loyalty_4700298d-9862-40b3-9028-b0f15dab9dea" where > "2-Age" = '> 30' > > >>>>>>>>>> > > >>>>>>>>>> > > >>>>>>>>>> any help or guidance is appreciated. > > >>>>>>>>>> Thank you. > > >>>>>>>>>> > > >>>>>>>>>> > <TestingDataSimple_2003_Marketing_Loyalty_4700298d-9862-40b3-9028-b0f15dab9dea.csv> > > >>>>>>>>> > > >>>>>>>>> > > >>>>>>>> > > >>>>>>> > > >>>>>> > > >>>>> > > >>>> > > >>> > > >> > > >> > > > > > > > > > >

1 0

Fwd: Monetdb-R integration
by Hannes Mühleisen 19 Jun '15

19 Jun '15

(Forwarding to users list, free support is public) > Begin forwarded message: > > From: Hannes Mühleisen <hannes.muehleisen(a)cwi.nl> > Subject: Re: Monetdb-R integration > Date: 19 Jun 2015 10:12:48 CEST > To: imad hajj chahine <imad.hajj.chahine(a)gmail.com> > Cc: Dimitar Nedev <dimitar.nedev(a)monetdbsolutions.com> > > I am running attached script, which I pieced together from your emails. This does not run, it produces the following error: > > Error running R expression. Error message: Error in t.test.formula(arg1 ~ arg2) : > grouping factor must have exactly 2 levels > Calls: as.data.frame -> <Anonymous> -> t.test -> t.test.formula > > > > In general, for aggregations, the R code needs to return a vector with a single entry for each group. > > > Best, > > Hannes > > > >> On 19 Jun 2015, at 09:44, imad hajj chahine <imad.hajj.chahine(a)gmail.com> wrote: >> >> Thanks, if you need more information from my side let me know. >> >> Also can you answer me on the following when i use 2 functions one to retrieve Pvalue and one for the stat value: >> Am I doing the correct way to retrieve the stat and p-value, is the ttest get executed twice in this case? is there a more optimal way where i can retrieve both information. >> >> On Fri, Jun 19, 2015 at 10:39 AM, Hannes Mühleisen <Hannes.Muehleisen(a)cwi.nl> wrote: >> Looking into this at the moment. >> >>> On 16 Jun 2015, at 09:56, Dimitar Nedev <dimitar.nedev(a)monetdbsolutions.com> wrote: >>> >>> Hannes, do you know if the STD_DEV is the issue there or if the queries should actually yield different results? >>> >>> Dimitar >>> >>>> On 2015-Jun-15, at 13:28 , imad hajj chahine <imad.hajj.chahine(a)gmail.com> wrote: >>>> >>>> Hi, >>>> >>>> Shouldn't we have the same result from running the first query and the second query? >>>> after all its an aggregate function and should return the test for each group. >>>> >>>> I checked that monetdb had problem with running group by with statistics functions like STD_DEV, are these 2 issues related? >>>> >>>> Thank you. >>>> >>>> >>>> >>>> >>>> On Mon, Jun 15, 2015 at 1:52 PM, Dimitar Nedev <dimitar.nedev(a)monetdbsolutions.com> wrote: >>>> Hi Imad, >>>> >>>> Here is the output I got from running the queries. Note, this might not be entirely representative since I did clean-up some data before loading it. >>>> >>>> sql>select "2-Age", "tone"."ttest2"("9-Score",6.08963) from "tone"."Marketing_Loyalty_4700298d-9862-40b3-9028-b0f15dab9dea" where "2-Age" is not null group by "2-Age"; >>>> +---------+--------------------------+ >>>> | 2-Age | L1 | >>>> +=========+==========================+ >>>> | [10-20] | 0.89311103869734754 | >>>> +---------+--------------------------+ >>>> 3 tuples (3.218ms) >>>> >>>> sql>select '[10-20]' as "2-Age","tone"."ttest2"("9-Score",6.08963) from "tone"."Marketing_Loyalty_4700298d-9862-40b3-9028-b0f15dab9dea" where "2-Age" = '[10-20]' >>>> more>union all >>>> more>select '[21-30]' as "2-Age","tone"."ttest2"("9-Score",6.08963) from "tone"."Marketing_Loyalty_4700298d-9862-40b3-9028-b0f15dab9dea" where "2-Age" = '[21-30]' >>>> more>union all >>>> more>select '[> 30]' as "2-Age","tone"."ttest2"("9-Score",6.08963) from "tone"."Marketing_Loyalty_4700298d-9862-40b3-9028-b0f15dab9dea" where "2-Age" = '> 30'; >>>> +---------+--------------------------+ >>>> | 2-Age | L1 | >>>> +=========+==========================+ >>>> | [10-20] | 0.14150742355349472 | >>>> | [21-30] | 0.32872830865003566 | >>>> | [> 30] | 0.37127075363219841 | >>>> +---------+--------------------------+ >>>> 3 tuples (8.825ms) >>>> >>>> >>>> sql>select "tone"."ttest2samplesStatistic"("9-Score", "4-Gender") as "stat", "tone"."ttest2samples"("9-Score", "4-Gender") as "pvalue" from "tone"."Marketing_Loyalty_4700298d-9862-40b3-9028-b0f15dab9dea" group by "2-Age"; >>>> +--------------------------+--------------------------+ >>>> | stat | pvalue | >>>> +==========================+==========================+ >>>> | -2.1588506639013736 | 0.032555033970368859 | >>>> +--------------------------+--------------------------+ >>>> 1 tuple (13.287ms) >>>> >>>> >>>> I dropped the aggregate with: >>>> sql>DROP AGGREGATE "tone"."ttest2samplesStatistic"; >>>> operation successful (2.049ms) >>>> >>>> >>>> Best regards, >>>> Dimitar >>>> >>>>> On 2015-Jun-12, at 00:46 , imad hajj chahine <imad.hajj.chahine(a)gmail.com> wrote: >>>>> >>>>> Thank you Dimitar, >>>>> >>>>> I was using Squirrel to connect from my host machine to the VM. >>>>> >>>>> Now when I connect using SSH-client and run the short group by query it works but the result is not similar to the union queries (PS: i also run it in the VM direclty and had the same result) >>>>> >>>>> >>>>> +---------+--------------------------+ >>>>> | 2-Age | pval | >>>>> +=========+==========================+ >>>>> | [21-30] | 0.99996611809271041 | >>>>> +---------+--------------------------+ >>>>> 3 tuples (2.638ms) >>>>> >>>>> >>>>> What i am trying to achieve is to run a one sample ttest for all the age categories, and it seems that the group by is ignored. >>>>> >>>>> I have also created 2 functions for the two sample ttest: >>>>> >>>>> CREATE AGGREGATE "tone"."ttest2samples"(arg1 double, arg2 varchar(250)) RETURNS double LANGUAGE R { t.test(arg1 ~ arg2)$p.value }; >>>>> CREATE AGGREGATE "tone"."ttest2samplesStatistic"(arg1 double, arg2 varchar(250)) RETURNS double LANGUAGE R { t.test(arg1 ~ arg2)$statistic }; >>>>> >>>>> select "tone"."ttest2samplesStatistic"("9-Score", "4-Gender") as "stat", "tone"."ttest2samples"("9-Score", "4-Gender") as "pvalue" from "tone"."Marketing_Loyalty_4700298d-9862-40b3-9028-b0f15dab9dea" group by "2-Age" >>>>> >>>>> Am I doing the correct way to retrieve the stat and p-value, is the ttest get executed twice in this case? is there a more optimal way where i can retrieve both information. Please note that I also have the same grouping issue as before. >>>>> >>>>> Do you have the same behavior, or I am missing something in the creation of these functions. >>>>> >>>>> A final question, how do u drop a R function? i am trying to execute the following: >>>>> drop all function "tone"."ttest2samplesStat" >>>>> >>>>> >>>>> Thank you. >>>>> >>>>> >>>>> >>>>> On Thu, Jun 11, 2015 at 11:28 PM, Dimitar Nedev <dimitar.nedev(a)monetdbsolutions.com> wrote: >>>>> Hi Imad, >>>>> >>>>> Hannes asked me to look at your issue with the MonetDB-R VM. >>>>> >>>>> I loaded the data and ran the queries you mentioned, but I did not encounter any issues. This holds for both the short and the union queries. I am also not familiar with the issue you report, nor its cause. At this time I think it is local to your instance. If you can export your db, or re-load the data, I would recommend you to create a new instance with a fresh image and try again. If you import the image as a new VM instance, you can keep your old one around as well. >>>>> >>>>> Please note that during the test: >>>>> - I ran as the admin (monetdb) user, as the one created default on the VM image cannot create new schemas. >>>>> - I have to clean the data a bit up to load it, removing dozen lines or so. >>>>> - Renamed ttest1 to ttest2 to match the queries. >>>>> >>>>> Best regards, >>>>> Dimitar >>>>> >>>>>>>>>>> Begin forwarded message: >>>>>>>>>>> >>>>>>>>>>> From: imad hajj chahine <imad.hajj.chahine(a)gmail.com> >>>>>>>>>>> Subject: Re: Monetdb-R integration >>>>>>>>>>> Date: 10 Jun 2015 10:11:48 CEST >>>>>>>>>>> To: Hannes Mühleisen <Hannes.Muehleisen(a)cwi.nl> >>>>>>>>>>> >>>>>>>>>>> Hi, >>>>>>>>>>> >>>>>>>>>>> I am using the virtual box provided from https://www.monetdbsolutions.com/solutions/cloud/vm: >>>>>>>>>>> OS:linux version 3.10.0-123.9.3.el7.x86_64 (builder(a)kbuilder.dev.centos.org) >>>>>>>>>>> Monetdb: V1.7: (Oct2014-SP1) >>>>>>>>>>> R: 3.1.2 (2014-10-31) >>>>>>>>>>> >>>>>>>>>>> >>>>>>>>>>> >>>>>>>>>>> create table "tone"."Marketing_Loyalty_4700298d-9862-40b3-9028-b0f15dab9dea" ("Consumer Number" INTEGER,"1-Disloyalty" varchar(250),"2-Age" varchar(250),"3-Amount transferred" INTEGER,"4-Gender" varchar(250),"5-Citiz enship" varchar(250),"6- Profession" varchar(250),"7-City" varchar(250),"8-1 NC Billboard" varchar(250),"8-2 NC Friends" varchar(250),"8-3 NC Magazine" varchar(250),"8-4 NC Others" varchar(250),"9-Score" decimal(18,3),"10- Type of operation" varchar(250),"11-1 Welcoming" INTEGER,"11-2 Waiting time" INTEGER,"11-3 Time to finish request" INTEGER,"11-4 Fees" INTEGER,"11-5 Courtesy of employees" INTEGER,"11-6 Branch layout" INTEGER,"11-7 Attention to special requests" INTEGER,"12- Total time for service" decimal(18,3),"13- Distance from POS" decimal(18,3),"14- Weight" INTEGER) >>>>>>>>>>> >>>>>>>>>>> COPY offset 2 INTO "tone"."Marketing_Loyalty_4700298d-9862-40b3-9028-b0f15dab9dea" FROM '/home/App_Data/TestingDataSimple_2003_Marketing_Loyalty_4700298d-9862-40b3-9028-b0f15dab9dea.csv' USING DELIMITERS ',','\n','"'; >>>>>>>>>>> >>>>>>>>>>> Thanks >>>>>>>>>>> >>>>>>>>>>> On Wed, Jun 10, 2015 at 10:50 AM, Hannes Mühleisen <Hannes.Muehleisen(a)cwi.nl> wrote: >>>>>>>>>>> Hello Imad, >>>>>>>>>>> >>>>>>>>>>> which version of MonetDB and R are you using? Which operating system? >>>>>>>>>>> >>>>>>>>>>> Can you please also include the commands you used to load your CSV into MonetDB/ create the table? >>>>>>>>>>> >>>>>>>>>>> Thanks, >>>>>>>>>>> >>>>>>>>>>> Hannes >>>>>>>>>>> >>>>>>>>>>>> On 10 Jun 2015, at 00:25, imad hajj chahine <imad.hajj.chahine(a)gmail.com> wrote: >>>>>>>>>>>> >>>>>>>>>>>> Hi Hannes, >>>>>>>>>>>> >>>>>>>>>>>> I am testing Monetdb-R integration to do some statistical test, i followed the tutorial you posted on youtube and i have created the following function: >>>>>>>>>>>> >>>>>>>>>>>> CREATE AGGREGATE "tone"."ttest1"(arg1 double, arg2 double) RETURNS double LANGUAGE R { t.test(arg1, arg2)$p.value }; >>>>>>>>>>>> >>>>>>>>>>>> this function will basically do a one sample t-test. >>>>>>>>>>>> >>>>>>>>>>>> I have loaded the sample attached file into a demo database and trying to run the following query: >>>>>>>>>>>> >>>>>>>>>>>> select "2-Age", "tone"."ttest2"("9-Score",6.08963) from "tone"."Marketing_Loyalty_4700298d-9862-40b3-9028-b0f15dab9dea" where "2-Age" is not null group by "2-Age" >>>>>>>>>>>> >>>>>>>>>>>> The following error happened >>>>>>>>>>>> Error: protocol violation: unexpected line in data block: . >>>>>>>>>>>> SQLState: M0M10 >>>>>>>>>>>> ErrorCode: 0 >>>>>>>>>>>> >>>>>>>>>>>> alternatively i managed to have the intended result by running this query: >>>>>>>>>>>> >>>>>>>>>>>> select '[10-20]' as "2-Age","tone"."ttest2"("9-Score",6.08963) from "tone"."Marketing_Loyalty_4700298d-9862-40b3-9028-b0f15dab9dea" where "2-Age" = '[10-20]' >>>>>>>>>>>> union all >>>>>>>>>>>> select '[21-30]' as "2-Age","tone"."ttest2"("9-Score",6.08963) from "tone"."Marketing_Loyalty_4700298d-9862-40b3-9028-b0f15dab9dea" where "2-Age" = '[21-30]' >>>>>>>>>>>> union all >>>>>>>>>>>> select '[> 30]' as "2-Age","tone"."ttest2"("9-Score",6.08963) from "tone"."Marketing_Loyalty_4700298d-9862-40b3-9028-b0f15dab9dea" where "2-Age" = '> 30' >>>>>>>>>>>> >>>>>>>>>>>> >>>>>>>>>>>> any help or guidance is appreciated. >>>>>>>>>>>> Thank you. >>>>>>>>>>>> >>>>>>>>>>>> <TestingDataSimple_2003_Marketing_Loyalty_4700298d-9862-40b3-9028-b0f15dab9dea.csv> >>>>>>>>>>> >>>>>>>>>>> >>>>>>>>>> >>>>>>>>> >>>>>>>> >>>>>>> >>>>>> >>>>> >>>> >>>> >>> >> >> >

1 0

Enabling Spatial support?
by Thorsten Wesskallnies 17 Jun '15

17 Jun '15

Hi, I'm having some trouble with the instruction posted on the website here: https://www.monetdb.org/Documentation/Extensions/GIS I am running an OEL Server on version 6.6 and installed MonetDB using yum: Installed Packages Name : MonetDB Arch : x86_64 Version : 11.19.11 Release : 20150423.el6 ... Name : MonetDB-geom-MonetDB5 Arch : x86_64 Version : 11.19.11 Release : 20150423.el6 I also configured and installed the current version of the GEOS Library # geos-config --version >3.4.2 According to the above mentioned website I should now rebuild my DB with the '--enable-geom configure argument'. Unfortunately I'm unable to find the correct way to do it since yum installed a binary on my system and not the sourcecode. The 'MonetDB-geom' package doesn't seem to to the trick since the result of the sample Code on the Website returns an empty result. Hope somebody can point me in the right direction. MfG Thorsten Wesskallnies Avitech GmbH Principal Office: Bahnhofplatz 1 | 88045 Friedrichshafen | Germany Court Registration: Amtsgericht Ulm | HRB 728293 Geschäftsführer/Managing Director: Antonio Maria Gonzalez Gorostiza VAT No.: DE223719716 http://avitech.aero

2 1

trace disabled?
by Roberto Cornacchia 16 Jun '15

16 Jun '15

Hi there, Just to be sure: is it expected that trace is disabled / changed in Jan2015? I can see the trace by inspecting sys.tracelog() after the query, but no longer in one go as before. Roberto

1 1

Performing a quantile in R from a MonetDB.R db with 300million rows (UNCLASSIFIED)
by George, Glover E ERD-MS 15 Jun '15

15 Jun '15

Classification: UNCLASSIFIED Caveats: NONE Hi all, I'm currently trying to compare the performance of R's quantile function to that of MonetDB's. I have a table loaded with the TPC-H benchmark's data (scale factor of 50). I'm trying to return ~300 million rows of data (select l_extendedprice from lineitem), and although I have 256G of RAM, I'm getting the following: Error in paste(0)("",resp, collapse="") : Result would exceed 2^31-1 bytes The R statement is : x = dbGetQuery(conn, "select l_extendedprice from lineitem") Forgive me if this is a simplistic questions, as I am fairly new to R and don't know which avenue to take next. I took a look at bigmemory, but don't know quite how to return a result from monetdb and have it use a big.matrix. Note: the l_extendedprice is a floating (double) value. Any suggestions? Cheers. Glover George Classification: UNCLASSIFIED Caveats: NONE

2 2

1-to-N (bat) UDF
by Roberto Cornacchia 15 Jun '15

15 Jun '15

Hi there, I need a string tokenizer in MonetDB. The problem I have is not with the function itself, but with the fact that this is a 1 to N rows function. Implementing this for a single string value is easy enough, using a table function that takes a string a returns a table: create function tokenize(s string) returns table (token string) external name tokenize; select * from tokenize("one two three"); That's fine. The issue I'm having is with extending this to a column of strings. Ideally, given a string column one two three four five six seven eight I'd like to get an output along these lines (simplistic representation here): one two three | one one two three | two one two three | three four five six | four four five six | five four five six | six seven eight | seven seven eight | eight I can sure code the c function and the mal wrapper to implement this, but I can't see how to map it to SQL, given that table functions don't accept identifiers as parameters. Any idea? Any possible workaround? Thanks, Roberto

4 11

Geommodul and Linestring Query
by Dennis Wiest 15 Jun '15

15 Jun '15

Hello, I have a question for Geomodul. Is it possible to execute the following query with a LineString. This is my query with a point and the sample data set from the documentation. SELECT forests.name,buildings.name FROM forests,buildings WHERE "Intersect"(buildings.outline, Point(53.9,29.9)) = true; Now I want to replace the Point with a LineString like this in mysql. SELECT forests.name,buildings.name FROM forests,buildings WHERE "Intersect"(buildings.outline, LineString(Point(53.9,29.9), Point(53.0, 29.0))) = true; TestDatabase CREATE TABLE forests(id INT,name TEXT,shape MULTIPOLYGON); CREATE TABLE buildings(id INT,name TEXT,location POINT,outline POLYGON); INSERT INTO forests VALUES(109, 'Green Forest', 'MULTIPOLYGON( ((28 26,28 0,84 0,84 42,28 26), (52 18,66 23,73 9,48 6,52 18)), ((59 18,67 18,67 13,59 13,59 18)))'); INSERT INTO buildings VALUES(113, '123 Main Street', 'POINT( 52 30 )', 'POLYGON( ( 50 31, 54 31, 54 29, 50 29, 50 31) )'); INSERT INTO buildings VALUES(114, '215 Main Street', 'POINT( 64 33 )', 'POLYGON( ( 66 34, 62 34, 62 32, 66 32, 66 34) )'); SELECT forests.name,buildings.name FROM forests,buildings WHERE forests.name = 'Green Forest' and Overlaps(forests.shape, buildings.outline) = true; many thanks Dennis ________________________________ Avitech GmbH Principal Office: Bahnhofplatz 1 | 88045 Friedrichshafen | Germany Court Registration: Amtsgericht Ulm | HRB 728293 Gesch?ftsf?hrer/Managing Director: Antonio Maria Gonzalez Gorostiza VAT No.: DE223719716 http://avitech.aero

2 2

how to take output from DELETE and feed it to INSERT?
by integrity 12 Jun '15

12 Jun '15

Dear experts, Does anyone know how to take output from DELETE and feed it to INSERT INTO another table? Since now my pipeline ran into a certain point that 37 seconds of the total time 48 seconds spent on the following queries: INSERT INTO old_to_old SELECT uniqueid, targetid, distance_arcsec FROM associatedsource WHERE uniqueid IN (SELECT old_uniqueid FROM one_to_many) ORDER BY uniqueid, targetid; 42650 affected rows (20.0s) DELETE FROM associatedsource WHERE uniqueid IN (SELECT old_uniqueid FROM one_to_many); 42650 affected rows (16.6s) where associatedsource table is a large table with 1219234829 rows which is the size till the end of second day, while one_to_many table is quite small only 30 rows. associatedsource will accumulate processed data day by day, till the end of first day it has 603553616 rows. As associatedsource table grows, this two queries has severely degraded my pipeline, on first day the time for them were 9.1s and 7.8s. We can see monetdb server engine (randomly?) access the same data twice, for a large table and limited time allocated to a run of pipeline this is very expensive. if we can combine the queries into a single then the logic about which rows to move will be neater and improve performance twice. Thank you and best wishes, Meng

2 2

Dependency Types
by Robert Koch 11 Jun '15

11 Jun '15

Hi all, could someone please add an entry in the online documentation about the meaning of column "depend_type" in sys.dependencies? Recommended place is: https://www.monetdb.org/Documentation/SQLcatalog/TriggersDependencies Thank you. Robert

2 1

MonetDB crashed by some Query
by Dennis Wiest 10 Jun '15

10 Jun '15

Hello, I'm new here. I have the following problem. I have a database that looks like this (see end of email). Now I want to issue the following query. Select b. * FROM open_auctions o, b bidder WHERE (select b3.INCREASE from bidder b3 where b3.id = (select min (b3a.id) from bidder b3a where b3a.open_auction_id = o.open_auction_id)) * 2 <= (Select b2.INCREASE from bidder b2 where b2.id = (SELECT MAX (b2a.id) from bidder b2a where b2a.open_auction_id = o.open_auction_id)) AND o.open_auction_id = b.open_auction_id; The database crashes. If I divide the query individually and settled it works flawlessly. Other complex query also work. Only in this query crashes the database. Can anyone help me further? I installed MonetDB 11:19:11 64 bits on Redhat. 6 Thanks and Regards Dennis CREATE TABLE closed_auctions ( id int NOT NULL AUTO_INCREMENT, seller varchar(255) NOT NULL, buyer varchar(255) NOT NULL, itemref varchar(255) NOT NULL, price double NOT NULL, date varchar(255) NOT NULL, quantitiy int NOT NULL, type varchar(255) NOT NULL, PRIMARY KEY (id) ); INSERT INTO `open_auctions` (`id`, `open_auction_id`, `initial`, `reserve`, `aktuell`, `privacy`, `itemref`, `seller`, `quantity`, `type`, `start`, `ende`) VALUES (1, 'open_auction0', 210.62, 1540.75, 263.12, 'No', 'item0', 'person11', 1, 'Regular', '02/27/1998', '03/09/1999'), (2, 'open_auction1', 69.64, 398.65, 168.64, '', 'item2', 'person10', 1, 'Featured', '06/14/1998', '02/27/1999'), (3, 'open_auction2', 13.9, 0, 16.9, 'No', 'item3', 'person11', 1, 'Featured', '07/16/2000', '10/22/2000'), (4, 'open_auction3', 17.12, 0, 179.12, '', 'item5', 'person13', 1, 'Featured', '02/01/2001', '07/22/1999'), (5, 'open_auction4', 38.21, 116.91, 219.71, 'No', 'item7', 'person11', 4, 'Regular, Dutch', '08/05/2000', '01/09/1999'), (6, 'open_auction5', 75.95, 174.84, 116.45, 'No', 'item9', 'person7', 1, 'Featured', '01/27/2001', '10/08/1999'), (7, 'open_auction6', 67.27, 0, 77.77, 'Yes', 'item10', 'person14', 1, 'Featured', '12/15/2001', '07/13/1999'), (8, 'open_auction7', 35.53, 133.36, 73.03, 'Yes', 'item12', 'person16', 1, 'Regular', '04/06/2001', '04/11/2000'), (9, 'open_auction8', 92.54, 0, 101.54, '', 'item13', 'person12', 1, 'Regular', '10/20/1998', '02/04/2001'), (10, 'open_auction9', 9.88, 0, 137.38, '', 'item14', 'person10', 1, 'Featured', '06/16/1998', '12/05/2000'), (11, 'open_auction10', 86.28, 0, 239.28, 'Yes', 'item16', 'person8', 1, 'Featured', '06/26/2000', '02/04/1998'), (12, 'open_auction11', 4.12, 24.53, 38.62, '', 'item17', 'person12', 1, 'Regular', '11/03/1999', '03/02/2000'); CREATE TABLE bidder ( id int NOT NULL AUTO_INCREMENT, open_auction_id varchar(255) NOT NULL, date varchar(255) NOT NULL, time varchar(255) NOT NULL, personref varchar(255) NOT NULL, increase double NOT NULL, PRIMARY KEY (id) ); -- -- Daten für Tabelle `bidder` -- INSERT INTO `bidder` (`id`, `open_auction_id`, `date`, `time`, `personref`, `increase`) VALUES (1, 'open_auction0', '06/13/2001', '13:16:15', 'person0', 18), (2, 'open_auction0', '09/18/2000', '11:29:44', 'person23', 12), (3, 'open_auction0', '01/07/1998', '10:23:59', 'person14', 18), (4, 'open_auction0', '07/10/2001', '14:00:39', 'person16', 4.5), (5, 'open_auction1', '11/12/1998', '11:23:38', 'person20', 4.5), (6, 'open_auction1', '10/02/2000', '22:48:00', 'person4', 15), (7, 'open_auction1', '12/04/1998', '22:29:38', 'person23', 1.5), (8, 'open_auction1', '06/22/1999', '12:43:47', 'person19', 15), (9, 'open_auction1', '12/02/2001', '13:38:51', 'person15', 45), (10, 'open_auction1', '11/12/2001', '04:50:27', 'person9', 6), (11, 'open_auction1', '05/21/2001', '08:02:16', 'person5', 12), (12, 'open_auction2', '12/04/2000', '14:03:16', 'person5', 3), (13, 'open_auction3', '09/03/2000', '22:45:30', 'person8', 10.5), (14, 'open_auction3', '08/18/1998', '17:13:40', 'person22', 25.5), (15, 'open_auction3', '06/05/1998', '11:57:56', 'person19', 16.5), (16, 'open_auction3', '09/20/1999', '00:39:18', 'person5', 46.5), (17, 'open_auction3', '10/03/2001', '01:08:59', 'person23', 58.5), (18, 'open_auction3', '02/17/2001', '07:13:32', 'person20', 4.5), (19, 'open_auction4', '10/19/1998', '09:00:31', 'person7', 33), (20, 'open_auction4', '03/11/2001', '01:59:02', 'person8', 9), (21, 'open_auction4', '04/16/2001', '23:37:09', 'person8', 6), (22, 'open_auction4', '03/06/1999', '12:19:57', 'person5', 4.5), (23, 'open_auction4', '02/01/2000', '09:37:51', 'person21', 18), (24, 'open_auction4', '01/09/1999', '19:31:44', 'person1', 31.5), (25, 'open_auction4', '11/11/2001', '05:24:08', 'person16', 27), (26, 'open_auction4', '11/19/1998', '16:16:17', 'person10', 7.5), (27, 'open_auction4', '04/05/1999', '00:37:15', 'person17', 7.5), (28, 'open_auction4', '09/14/1999', '12:00:43', 'person12', 37.5), (29, 'open_auction5', '07/07/2000', '08:53:00', 'person15', 6), (30, 'open_auction5', '08/06/2001', '10:16:15', 'person13', 4.5), (31, 'open_auction5', '08/23/1999', '08:26:06', 'person17', 30), (32, 'open_auction6', '01/23/2000', '17:14:42', 'person1', 10.5), (33, 'open_auction7', '10/14/1999', '14:39:18', 'person16', 27), (34, 'open_auction7', '05/19/1999', '23:51:16', 'person14', 9), (35, 'open_auction7', '03/27/1999', '19:14:39', 'person23', 1.5), (36, 'open_auction8', '04/26/2001', '00:41:04', 'person12', 9), (37, 'open_auction9', '04/03/1999', '19:09:46', 'person22', 18), (38, 'open_auction9', '01/21/1999', '08:14:44', 'person19', 19.5), (39, 'open_auction9', '11/24/1999', '02:12:12', 'person11', 12), (40, 'open_auction9', '01/07/2001', '05:33:55', 'person7', 9), (41, 'open_auction9', '07/28/2000', '00:57:52', 'person16', 16.5), (42, 'open_auction9', '01/28/2000', '20:24:02', 'person23', 1.5), (43, 'open_auction9', '05/13/2001', '02:45:46', 'person13', 1.5), (44, 'open_auction9', '12/06/2000', '15:18:07', 'person21', 24), (45, 'open_auction9', '08/16/1998', '09:27:27', 'person14', 3), (46, 'open_auction9', '06/18/2001', '19:57:53', 'person7', 3), (47, 'open_auction9', '12/13/2001', '09:01:23', 'person2', 12), (48, 'open_auction9', '03/18/2001', '08:59:02', 'person13', 7.5), (49, 'open_auction10', '08/14/1998', '16:33:46', 'person23', 33), (50, 'open_auction10', '03/24/1998', '11:45:48', 'person3', 1.5), (51, 'open_auction10', '10/08/1998', '06:20:35', 'person1', 45), (52, 'open_auction10', '01/12/1998', '21:43:02', 'person3', 15), (53, 'open_auction10', '03/02/2001', '20:20:33', 'person0', 9), (54, 'open_auction10', '03/01/2000', '10:08:07', 'person23', 10.5), (55, 'open_auction10', '07/19/2001', '00:50:29', 'person6', 9), (56, 'open_auction10', '10/04/2001', '00:58:25', 'person4', 3), (57, 'open_auction10', '11/27/1998', '00:15:23', 'person21', 27), (58, 'open_auction11', '05/16/1998', '15:08:01', 'person1', 15), (59, 'open_auction11', '05/08/2000', '06:44:20', 'person3', 4.5), (60, 'open_auction11', '10/22/2001', '15:34:49', 'person4', 15); ________________________________ Avitech GmbH Principal Office: Bahnhofplatz 1 | 88045 Friedrichshafen | Germany Court Registration: Amtsgericht Ulm | HRB 728293 Geschäftsführer/Managing Director: Antonio Maria Gonzalez Gorostiza VAT No.: DE223719716 http://avitech.aero

3 3