- 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