- users-list - monetdb.org

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