- users-list - monetdb.org

Monetdb with R-integrated virtual box connect from windows
by imad hajj chahine 09 Jun '15

09 Jun '15

Hi, I downloaded the virtual box of monetdb with R-integrated and I am using the windows7 as the host OS. I am trying to connect Squirrel running on windows to the monetdb service running in the virtual box with no luck. I tried the virtual box in Nat and bridged mode and I tried to run the mserver5 with mapi_open=true but the monetdb service is still invisible to the Squirrel client. Any help with the settings will be appreciated, or better if anyone has a version of monetdb with R-integrated compiled for windows as i also need to load text files from windows into the database. Thanks.

1 0

Announcement: New Oct2014-SP4 Bugfix release of MonetDB suite
by Sjoerd Mullender 09 Jun '15

09 Jun '15

The MonetDB team at CWI/MonetDB BV is pleased to announce the Oct2014-SP4 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/>. Oct 2014-SP4 bugfix release Bug Fixes * 3705: Assertion failure in rel_bin.c:2274: rel2bin_project: Assertion `0' failed. * 3707: var() possibly not working in debug builds * 3712: Concurrency issue on querying the SQL catalog * 3713: Long startup cost for simple session * 3715: Crash with two ALTER TABLE statements in a transaction * 3718: Adding and dropping a non existing tablename to/from a merge table is accepted without an error * 3719: Assertion failure in /MonetDB-11.19.11/gdk/gdk_bat.c:2841: BATassertHeadProps: Assertion `!b->H->key || cmp != 0' failed. * 3720: Incorrect results on joining with same table * 3723: Assertion failure in rel_bin.c:2548: rel2bin_groupby: Assertion `0' failed. * 3725: LEFT JOIN bug with CONST value * 3731: left shift for IP addresses not available to non-system users

1 0

user defined is_numeric ( check if string is numeric ) and is_date(if string is valid date ) sql function for monetdb
by Shivanandan Gupta 09 Jun '15

09 Jun '15

Hi Team, I was looking for is_numeric() and is_date() function in monetdb but couldn't find it. So I have written it ( there will be many ways to do it , I am sharing the way I implemented ) . This will help other users in the group. #################################################################### IS NUMERIC function #################################################################### create function is_numeric (value varchar(100)) returns int begin declare isnumeric int; set isnumeric = length(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(value,'','0'),',',''),'1',''),'2',''),'3',''),'4',''),'5',''),'6',''),'7',''),'8',''),'9',''),'0','') ); if (isnumeric>0) then return 0; else return 1; end if; end; #################################################################### IS DATE function #################################################################### create function is_date (value varchar(100)) returns int begin declare is_date int; set is_date = case when SUBSTRING(value,7,4) BETWEEN '1900' AND '2100' then case when SUBSTRING(value,1,2) BETWEEN '01' AND '12' then case when SUBSTRING(value,4,2) BETWEEN '01' AND (CASE WHEN SUBSTRING(value,1,2) in('01','03','05','07','08','10','12') AND SUBSTRING(value,4,6) BETWEEN '01' AND '31' THEN '31' ELSE CASE WHEN SUBSTRING(value,1,2) in ('04','06','09','11') AND SUBSTRING(value,4,6) BETWEEN '01' AND '30' THEN '30' ELSE CASE WHEN (SUBSTRING (value,1,2) in ('02') ) AND MOD(SUBSTRING(value,7,4),400)=0 OR MOD(SUBSTRING(value,7,4),4)=0 THEN '29' ELSE '28' END END END) then 1 ELSE 0 END else 0 END else 0 END ; if (is_date=0) then return 0; else return 1; end if; end; -- Cheers, Shivanandan Gupta

1 0

possible to add a search box?
by Anthony Damico 09 Jun '15

09 Jun '15

https://www.monetdb.org/mailman/listinfo/users-list https://www.monetdb.org/mailman/listinfo something like R has here http://tolstoy.newcastle.edu.au/R/

1 0

How to install the MonetDB as a Windows Service
by fadila.mumbasic＠uniserv.com 05 Jun '15

05 Jun '15

Hi, is there any way to install the MonetDB as a Windows Service? Thanks! Regards, Fadila Mumbasic

2 1

Setting up ODBC driver on Linux (Ubuntu 14.04)
by Dan Bartow 05 Jun '15

05 Jun '15

All - Thanks in advance for the help. Im trying to set up just the ODBC driver on a Linux client to connect to my MonetDB instance which is running on another host. I should probably just ask the simple question first, which is "is there any documentation somewhere, or can someone tell me some steps to do this easily?". I wasn't able to find anything instructing on how to do just that. Ive done the following manually, but if there is a documented way, or a better way, please point me in the right direction. If I try to test my connection with isql, this is what I see: isql -v MonetDB [08001][unixODBC][MonetDB][ODBC Driver 11.19.11]Client unable to establish connection [ISQL]ERROR: Could not SQLConnect All of the obvious stuff has been checked - the server is definitely up and running and I can connect to it via JDBC via SQLWorkbench, I can use mclient on both the db host as well as this Ubuntu box Im trying to connect from and mclient connects just fine. I can telnet to the host on port 50000... etc. Any ideas why I might be getting this error? To set up the ODBC driver, I did this: 1) I copied libMonetODBC.so and libMonetODBCs.so from the libmonet-client-odbc package into /usr/lib/x86_64-linux-gnu/odbc 2) I added this to /etc/odbcinst.ini [MonetDB] Description = MonetDB Driver Driver = libMonetODBC.so Setup = libMonetODBCs.so UsageCount = 2 3) I added this DSN to /etc/odbc.ini [MonetDB] Description = Connection for MonetDB Driver = MonetDB Database = voc Servername = 172.17.0.204 User = voc Password = voc Port = 50000 Thank you very much! - - Dan *Dan Bartow* [Product Design] --------------------------------------------------------- *SOASTA* www.linkedin.com/in/danbartow www.twitter.com/perfdan This email and any attachments thereto may contain private, confidential and privileged material for the sole use of the intended recipient. Any review, copying or distribution of this email (or any attachments thereto) by others is strictly prohibited. If you are not the intended recipient, please contact the sender immediately and permanently delete the original and any copies of this email and any attachments thereto.

1 0

should the SAMPLE command produce random results?
by Anthony Damico 01 Jun '15

01 Jun '15

# here's a reproducible example using R code to repeat the sampling 1000 times. in both SAMPLE examples below, the database pulls the 2 less than 200 times out of 1000. shouldn't it be close to 500 out of 1000? this seems not random (misleading to users?) sorry if i'm misunderstanding something.. thank you!! # start in an empty directory somewhere # setwd( "C:/My Directory/MonetDB" ) # # # # # # # # # START OF SETUP - no editing required library(MonetDB.R) batfile <- monetdb.server.setup( database.directory = paste0( getwd() , "/MonetDB" ) , monetdb.program.path = ifelse( .Platform$OS.type == "windows" , "C:/Program Files/MonetDB/MonetDB5" , "" ) , dbname = "test" , dbport = 50000 ) pid <- monetdb.server.start( batfile ) db <- dbConnect( MonetDB.R() , "monetdb://localhost:50000/test" , wait = TRUE ) # # # END OF SETUP dbGetQuery( db , "SELECT 1 AS col UNION ALL SELECT 2 AS col" ) out <- NULL for ( i in 1:1000 ){ out <- c( out , dbGetQuery( db , "SELECT * FROM ( SELECT 1 AS col UNION ALL SELECT 2 AS col ) AS temp SAMPLE 0.5" ) ) } # not random table( unlist( out ) ) # 1 2 # 880 120 out <- NULL for ( i in 1:1000 ){ out <- c( out , dbGetQuery( db , "SELECT * FROM ( SELECT 1 AS col UNION ALL SELECT 2 AS col ) AS temp SAMPLE 1" ) ) } # ALSO not random table( unlist( out ) ) # 1 2 # 856 144

2 5

a few performance improvement questions
by Anthony Damico 29 May '15

29 May '15

hi, i've been running some pretty big queries on a few tables (total of 900 million records) on a 64GB windows machine and i'm curious if anyone might provide advice about whether any of these strategies are computationally expensive? i am maxing out my available RAM in my queries a lot, and at the point it hits that ceiling, the computation starts dragging. here's my pseudo-sql that displays the kinds of commands i've been running. thanks! ============ (a) i am conducting a left join where not all records match. the non-matching records need to be zeroes in general, is this the least computationally demanding way to get an average amount per person, including the zeroes? SELECT AVG( sum_amount ) AS avg_amt_per_person FROM ( SELECT person_id , SUM( CASE WHEN amount IS NULL THEN 0 ELSE amount END ) AS sum_amount FROM ( ( SELECT person_id FROM person_table ) AS a LEFT JOIN ( SELECT person_id , amount FROM amount_table ) AS b ON a.person_id = b.person_id ) AS temp_table GROUP BY person_id ) ; ============ (b) i am on windows (so no strptime) and i have dates in either a mm/dd/yy format but some january thru september are m/dd/yy (i.e. the month can either be one or two digits). i am just calculating the month as part of my query on-the-fly. is it foolish of me not to add a new, permanent column to the data set? i don't have a good sense of whether this segment slows down my query by a lot. it is one of my join/merge variables. CAST( CASE WHEN SUBSTRING( some_date , 2 , 1 ) = '/' THEN SUBSTRING( some_date , 1 , 1 ) ELSE SUBSTRING( some_date , 1 , 2 ) END AS DOUBLE ) AS some_month ============ (c) i have a three tables that have the same structure that get LEFT JOINed onto my main table. so right now i am doing something like-- main_table LEFT JOIN second_table LEFT JOIN third_table LEFT JOIN fourth_table is it smarter fo me to do this instead? -- CREATE TABLE stacked_table FROM second_table UNION ALL third_table UNION ALL fourth_table main_table LEFT JOIN stacked_table

2 3

is bugzilla down?
by Anthony Damico 27 May '15

27 May '15

https://www.monetdb.org/bugzilla/ connection times out -- from computers in three countries.

2 1

Permission Issues
by Raffael Marty 27 May '15

27 May '15

I have a table called ip_range with an inet field (ip). I do the following query: select * from ip_range where ip << inet '10.0.0.0/8’ SELECT: no such binary operator 'left_shift(inet,inet)' Which results in an error. This is by using a non system user. If I create the same table as moentdb user and do the same query, it works as intended. Am I missing something with regards to permissions? Running 11.19.11-20150 on Ubuntu. Thanks Raffael To reproduce: create table test (a inet); insert into test (a) values ('10.0.0.1’); select * from test where a << inet '10.0.0.0/8’; — Visual Analytics Workshop at #BlackHat US in August: http://bit.ly/1FN1W5e Raffael Marty ceo @ pixlcloud http://pixlcloud.com @raffaelmarty http://raffy.ch

2 3

Picky SQL sytnax
by Raffael Marty 26 May '15

26 May '15

I am having some issues with the SQL syntax in MonetDB. Is it just really picky or am I doing something that is not SQL99 compatible? Couple examples: This query works: select source_address, count(distinct destination_port) as distcount from flows group by source_address having count(distinct destination_port)>1 limit 10; The following query doesn’t work. I am just trying to use the AS keyword to make things a bit more readable. select source_address, count(distinct destination_port) as distcount from flows group by source_address having distcount>1 limit 10; SELECT: identifier 'distcount’ unknown I had numerous other issues where little things like quotes and such made a difference. For example, creating a table would not work like this: CREATE TABLE foo (range text); syntax error, unexpected RANGE, expecting FOREIGN or PRIMARY or UNIQUE in: "create table foo (range" This works: CREATE TABLE foo (“range” text); Third example: (I could never get this working - tried removing the quotes, which made things worse) sql>INSERT INTO ranges ("range", "title", "external", "description", "routeadvertised", "X", "Y", "Z", "N", "M", "O", "P", "Q", "R", "S", "T", "U", "V", "A", "B", "C", "D", "E", "F", "G", "H", "I", "J" ) VALUES ('0.0.0.0/0', 'IPv4', , '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', 0, 0, '', '', '', '', '', 1, '1', '' ); syntax error, unexpected ',' in: "insert into ranges ("range", "title", "external", "description", "routeadvert" Is there a statement length restriction? Same happens whether I use the mclient or go through Python code. I am running version 11.19.11-20150 Thanks so much! Raffael — Visual Analytics Workshop at #BlackHat US in August: http://bit.ly/1FN1W5e Raffael Marty ceo @ pixlcloud http://pixlcloud.com @raffaelmarty http://raffy.ch

3 2

Announcement: New Oct2014-SP3 Bugfix release of MonetDB suite
by Sjoerd Mullender 26 May '15

26 May '15

The MonetDB team at CWI/MonetDB BV is pleased to announce the Oct2014-SP3 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/>. Oct 2014-SP3 bugfix release Build Environment * We now also create debug packages for Debian and Ubuntu. MonetDB Common * Replaced the rangejoin implementation with one that uses imprints if it can. Bug Fixes * 3466: UPDATE statements fails with "GDKerror: MT_mremap() failed" * 3602: Surprising overload resolution of generate_series * 3613: SQL data dictionary contains columns names which are also special keywords. This causes unexpected/unneeded SQL query errors * 3645: Network address operators such as << and <<= do not work * 3647: missing BAT for a column leads to crash in gtr_update_delta * 3648: memory corruption on unclean connection shutdown with local temporary tables * 3650: Naming of persistent BATs is fragile * 3653: PREPARE crashes mserver if unbound variable is function parameter * 3655: SQL WHERE -1 in (-1) issue? * 3656: error message after calling fitsload() * 3660: Incorrect Results for Comparison Operators on inet Datatype * 3661: Ship debug symbols for pre-built binaries * 3662: UPDATE row with row value constructor crashes monetdb server * 3663: Incorrect result ROW_NUMBER in subquery * 3664: SQLstatementIntern missing parameter when using jsonstore * 3665: inter-session starvation issue, particularly affects sys.queue * 3666: casting text column to inet truncating text column and resulting inet for first occurrence only * 3667: insert of negative value for oid column aborts mserver5 process with assertion failure * 3669: ALTER TABLE <tbl_nm> ADD CONSTRAINT <tbl_uc1> UNIQUE (col1, col2, col3) causes Assertion failure and abort * 3671: ODBC-Access on Windows 2012 does not work - E_FAIL * 3672: libbat_la-gdk_utils.o: relocation R_X86_64_PC32 against `MT_global_exit' can not be used when making a shared object * 3676: merovingian hangs trying to exit * 3677: Crash in BATgroup_internal (caused by 87379087770d?) * 3678: Ruby driver installation ignores prefix * 3680: Prepared statements fail on execution with message 'Symbol type not found' * 3684: Wrong query result set WHERE "IS NULL" or "NOT IN" clauses uses in combination with ORDER, LIMIT and OFFSET * 3687: 'bat.insert' undefined * 3688: Crash at exit (overrun THRerrorcount?) * 3689: No more connections accepted if a single client misbehaves * 3690: find_fk: Assertion `t && i' failed. * 3691: conversion of whitespaces string to double or float is accepted without an error during insert * 3693: algebra.join undefined (caused by non-existing variables in the plan) * 3696: Inconsistent behavior between dbl (SQL double) and flt (SQL real) data types and across platforms * 3697: mserver5[26946]: segfault at 0 ip 00007f3d0e1ab808 sp 00007f3cefbfcad0 error 4 in lib_sql.so[7f3d0e180000+16c000] * 3699: segfault again! (during last week I found 3 segfault bugs already) * 3703: INSERT INTO a MERGE TABLE crashes mserver5 * 3704: Unknown identifier from subquery * 3705: Assertion failure in rel_bin.c:2274: rel2bin_project: Assertion `0' failed. * 3706: Assertion failure in gdk_bat.c: BATassertHeadProps: Assertion `!b->H->sorted || cmp <= 0' failed. * 3709: "BATproject: does not match always" on abusive use of ALTER TABLE SET READ ONLY

2 6

Documenting date time functionality in MonetDB
by Robin Cijvat 22 May '15

22 May '15

I am currently working on documenting date/time functionality that exists in MonetDB and I have run into some problems/questions. I hope someone can help me out: 1) According to the Temporal Types page (https://www.monetdb.org/Documentation/SQLreference/Temporal) there should be a type 'daytime'. However, the following fails: create table t (b daytime);-> type (daytime) unknown in: "create table t (b daytime)" Does anyone know whether this feature has been removed or that this is a bug (or that I am perhaps missing something)? 2) Converting strings to dates works like a charm: *SELECT str_to_date('23-09-1987', '%d-%m-%Y');*-> 1987-09-23 But how about converting strings to times/timestamps: *SELECT str_to_date('11:40', '%H:%M');* -> null I can not seem to find any definitions of built in functions that take a string and produce anything other than a date. Thanks in advance! Robin PS: I am running the latest default branch on Ubuntu 14.04.

2 3

division by zero
by Anthony Damico 22 May '15

22 May '15

i'm just curious/thinking out loud if anything can be done to prevent division by zero from de-railing a huge query? unlike most error messages, division by zero destroys a query *after* the bulk of the processing occurs - so you sit and wait and the command still fails, despite all the time and cpu revving. i assume it's impossible to check for this event *before* a query gets executed, but would it be possible to have a database option that essentially replaces zeroes with NULLs in the circumstances that this does occur so the query still returns the rest of the table? or would doing that be sloppy database management? so maybe this fails select 1 / 0 as x; but you change some setting and it auto-coerces everything under a division operator to select 1 / ( CASE WHEN value = 0 THEN NULL ELSE value END ) as x; that way you lose x but maybe not other columns? sorry if this is stupid..and thank you

4 4

Re: users-list Digest, Vol 33, Issue 14
by Michael Helmke 19 May '15

19 May '15

Hi, Thank you for your reply. I am using mclient to do the inserts. For your question about the monetdb version, I get "MonetDB Database Server Toolkit v1.1 (Oct2014-SP3)" from the monetdb --version switch. If I insert "b\\\\c" that does work fine. And I tried doubling all of the backslashes which worked until I got unicode characters of the form: \\u03c1 for example. That case then doesn't work. Adding backslash escapes on the quotes in my second example didn't change anything. In the short term, I'm going to skip json and simply store strings. I'm also playing with the json parser in the code to do tests and see if I can find a simple fix or workaround there. Thank you, Michael Helmke > Date: Tue, 19 May 2015 08:46:29 +0200 (CEST) > From: Arjen de Rijke <Arjen.de.Rijke(a)cwi.nl> > To: Communication channel for MonetDB users <users-list(a)monetdb.org> > Subject: Re: Known issues with JSON field type? > Message-ID: <1281096997.870323.1432017989628.JavaMail.zimbra(a)cwi.nl> > Content-Type: text/plain; charset=utf-8 > > Hi Michael, > > Since \ is used for escaping characters and " is/can also be a string delimiter, using these within a json object that is inserted in a database, can be tricky. It might also depend on how you do the insertion, for example if you use mclient or if you use for example jdbc from code. > > How do you try to insert these records and what happens if you try something like this: > > insert into foo (jdata) values ('["a", "b\\\\c"]'); > > insert into foo (jdata) values ('{"abc": \"\"}'); > > And which version of monetdb are you using? > > Arjen de Rijke > > ----- Original Message ----- >> From: "Michael Helmke" <michael(a)visan.com> >> To: users-list(a)monetdb.org >> Sent: Tuesday, May 19, 2015 3:42:28 AM >> Subject: Known issues with JSON field type? > >> I am using MonetDB 11.19.9 and using a JSON field type in my database. I am new >> to MonetDB and experienced with SQL and databases. >> >> I find when I try to store some json strings that I think are valid json, the >> database complains on row insert. >> >> For example, the following don't work: >> >> insert into foo (jdata) values ('["a", "b\\c"]'); >> >> insert into foo (jdata) values ('{"abc": ""}'); >> >> The first problem is with the quoted backslash. The second problem is with the >> double quotes. If I remove the quoted backslash it works, likewise replace >> double quotes with null and it works. >> >> This is my table create: >> >> create table foo ( jdata json ); >> >> My read of the JSON spec is that this is legal JSON. Are there known issues >> with JSON support in MonetDB? >> >> Thank you, >> >> Michael Helmke >> _______________________________________________ >> users-list mailing list >> users-list(a)monetdb.org >> https://www.monetdb.org/mailman/listinfo/users-list >

1 0

easiest way to coerce "?m/?d/yyyy" strings to a date in monetdb?
by Anthony Damico 19 May '15

19 May '15

hi, i have a VARCHAR column with dates like "1/1/2014" and also "12/31/2014" since there are no leading zeroes, the months and days are in different positions so i cannot use something like this to CAST it into a date type. CAST( SUBSTRING( x , 7 , 10 ) || '-' || SUBSTRING( x , 1 , 2 ) || '-' || SUBSTRING( x , 4 , 5 ) AS DATE ) AS y could someone offer advice to convert this string field to date in monetdb? thanks!!

4 10

Known issues with JSON field type?
by Michael Helmke 19 May '15

19 May '15

I am using MonetDB 11.19.9 and using a JSON field type in my database. I am new to MonetDB and experienced with SQL and databases. I find when I try to store some json strings that I think are valid json, the database complains on row insert. For example, the following don't work: insert into foo (jdata) values ('["a", "b\\c"]'); insert into foo (jdata) values ('{"abc": ""}'); The first problem is with the quoted backslash. The second problem is with the double quotes. If I remove the quoted backslash it works, likewise replace double quotes with null and it works. This is my table create: create table foo ( jdata json ); My read of the JSON spec is that this is legal JSON. Are there known issues with JSON support in MonetDB? Thank you, Michael Helmke

2 1

Re: Facing issues in implementing Embedded R in MonetDB-11.19.11
by Dimitar Nedev 15 May '15

15 May '15

Hi Vishal, Please try the following: separate the sources, build and installation directories, to avoid any overwriting files. Delete the old sources, build and installation directories and unpack the sources again. Once the sources and unpacked, make a directory called 'BUILD' in the sources dir. Go in the BUILD directory and run the configure script from there, also setting an installation location using the '--prefix' option, e.g.: ../configure --prefix=$HOME/monetdb-install --enable-optimize --enable-rintegratioin=yes Then go to '$HOME/monetdb-install' and run monetdbd from there: bin/monetdbd create dbfarm bin/monetdbd start dbfarm ... Do the same for the rest of the monetdb setup. I am asking you to do this, since I just tired the exact same setup on a clean Ubuntu Server 14.04.2 on VBox without any issues. Best regards, Dimitar > On 2015-May-15, at 12:00 , Vishal Virmani <virmani.vishal(a)gmail.com> wrote: > > Hi Dimitar > > I enabled R integration explicitly using monetdb set embed_r=true testdb1. > > Please find attached merovingian.log file generated for your reference, Dimitar i ran the following commands to check if monetdbd, monetdb and mclient are installed properly. > > The console output is generated are mentioned for your reference > > sudo monetdbd –version > > > > MonetDB Database Server v1.7 (Oct2014-SP3) > > > > sudo monetdb –version > > > > MonetDB Database Server Toolkit v1.1 (Oct2014-SP3) > > > > sudo mclient –version > > > > mclient, the MonetDB interactive terminal (Oct2014-SP3) support for command-line editing compiled-in character encoding UTF-8 > > > I think these are properly installed as can be seen from console outputs i mentioned for each of them. > > Kindly guide me to do the needful > > Regards > Vishal Virmani > +91 99108 47231 > @poisoncreed > > On Fri, May 15, 2015 at 5:07 AM, Vishal Virmani <virmani.vishal(a)gmail.com> wrote: > Hi Dimitar > > I enabled R integration explicitly using monetdb set embed_r=true testdb1. > > I have attached the log file for your reference, Dimitar i ran the following commands to check if monetdbd, monetdb and mclient are installed properly. > > The console output is generated are mentioned for your reference > > sudo monetdbd –version > > > > MonetDB Database Server v1.7 (Oct2014-SP3) > > > > sudo monetdb –version > > > > MonetDB Database Server Toolkit v1.1 (Oct2014-SP3) > > > > sudo mclient –version > > > > mclient, the MonetDB interactive terminal (Oct2014-SP3) support for command-line editing compiled-in character encoding UTF-8 > > > I think these are properly installed as can be seen from console outputs i mentioned for each of them. > > Kindly guide me to do the needful > > > > Regards > Vishal Virmani > +91 99108 47231 > @poisoncreed > > On Thu, May 14, 2015 at 12:23 PM, Dimitar Nedev <dimitar.nedev(a)monetdbsolutions.com> wrote: > Hi Vishal, > > Can you please attach the 'merovingian.log' file from the dbfarm directory? > > After step 11, you must enable the R integration for the newly created database, before starting it: > monetdb set embedr=true testdb1 > This in unlikely to be the cause of the "failed to open up a communication channel" error, but is needed to actually enable the R integration. > > Also, it is not necessary to configure, make, install, create dbfarms and start databases with sudo. A regular user should also be able to do that, as long as he has sufficient write access permissions for the target directories. Please try that as well. > > A side note: If you want to get better performance from your database (and not planning to attach a debugger to your it), it's recommended to set the '--enable-optimize' option when configuring the build at step 6. > > Best regards, > Dimitar > > > On 2015-May-14, at 01:49 , Vishal Virmani <virmani.vishal(a)gmail.com> wrote: > > > > Hi Hannes > > > > As you suggested i checked, i am not having any multiple installation of MonetDB. > > > > Further i have done a fresh installation. I am including the messages in the merovingian file as well for your reference. > > > > Steps followed were > > > > Step 1 : I created a clean VM with Ubuntu 14.04 server. > > > > Step 2: Installed R-3.2.0 from source files. I checked the R environment it is working fine. > > > > Step 3: Downloaded MonetDB source files from http://dev.monetdb.org/downloads/sources/Oct2014-SP3/MonetDB-11.19.11.tar.xz <http://dev.monetdb.org/downloads/sources/Oct2014-SP3/MonetDB-11.19.11.tar.xz> > > > > Step 4: Created an empty directory sourcefiles in which i downloaded MonetDB-11.19.11-tar.xz > > > > Step 5: Unpacked the source files > > > > Step 6: Executed the command sudo ./configure --enable-rintegratioin=yes > > > > The result of executing the above command generated the following output: > > > > Output of running configure command > > > > Enabled/disabled build options: > > > > strict is disabled (by default) > > assert is disabled (by default) > > debug is disabled (by default) > > optimize is disabled (by default) > > developer is disabled (by default) > > instrument is disabled (by default) > > profile is disabled (by default) > > > > > > Enabled/disabled components: > > > > gdk is enabled > > monetdb5 is enabled > > sql is enabled > > geom is disabled (geos library required for geom library) > > gsim is disabled (gsl library not found) > > fits is disabled (cfitsio library not found) > > jsonstore is disabled (by default) > > microhttpd is disabled (by default) > > rdf is disabled (by default) > > rintegration is enabled > > datacell is disabled (by default) > > odbc is disbaled (unixODBC library required for ODBC driver) > > jdbc is disabled (ant is required to build MonetDB JDBC) > > control is disabled (ant is required to build merovingian control library) > > testing is enabled > > > > > > > > > > Step 7 : Executed sudo make and make install respectively. They are working fine. > > > > > > Step 8 : Checked sudo mserver5 --version , it gave the the desired output > > > > Step 9: Created a dbfarm using the command sudo monetdbd create /mydbfarm > > > > Step 10 Started the dbfarm by running sudo monetdbd start /mydbfarm, this also get executed successfully > > > > Step 11. Created a sample database testdb1 using command : sudo monetdb create testdb1. This also gets executed fine. > > > > Step 12 Upon execution of command sudo monetdb start testdb1, it fails showing me an error in merovingian file and on console i am generating following output > > > > starting database 'testdb1'...FAILED > > start: starting 'testdb1' failed: datbase 'testdb1' started up, but failed to open up a communication channel. > > > > > > Looking forward to your suggestion > > > > Regards > > Vishal Virmani > > +91 99108 47231 > > > > > > > > > > Regards > > Vishal Virmani > > +91 99108 47231 > > @poisoncreed > > > > On Tue, May 12, 2015 at 3:30 PM, <users-list-request(a)monetdb.org <mailto:users-list-request@monetdb.org>> wrote: > > Send users-list mailing list submissions to > > users-list(a)monetdb.org <mailto:users-list@monetdb.org> > > > > To subscribe or unsubscribe via the World Wide Web, visit > > https://www.monetdb.org/mailman/listinfo/users-list <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 <mailto:users-list-request@monetdb.org> > > > > You can reach the person managing the list at > > users-list-owner(a)monetdb.org <mailto:users-list-owner@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. Facing issues in implementing Embedded R in MonetDB-11.19.11 > > (Vishal Virmani) > > 2. Re: Facing issues in implementing Embedded R in > > MonetDB-11.19.11 (Hannes M?hleisen) > > > > > > ---------------------------------------------------------------------- > > > > Message: 1 > > Date: Tue, 12 May 2015 05:29:13 +0530 > > From: Vishal Virmani <virmani.vishal(a)gmail.com <mailto:virmani.vishal@gmail.com>> > > To: users-list(a)monetdb.org <mailto:users-list@monetdb.org> > > Subject: Facing issues in implementing Embedded R in MonetDB-11.19.11 > > Message-ID: > > <CA+dsbSFzWfqd=GEuAP17rqVPCfVWySShJa+rnyUPHkNbvrjbLw(a)mail.gmail.com <mailto:GEuAP17rqVPCfVWySShJa%2BrnyUPHkNbvrjbLw@mail.gmail.com>> > > Content-Type: text/plain; charset="utf-8" > > > > Hi > > > > I am implementing Embedded R in MonetDB version 11.19.11. My environment is > > VM with Ubuntu 14.04 Server. I have build MonetDB from source and R as well > > as from source. > > Running the command sudo mserver5 --version shows me the desired output. > > > > When i try to invoke a database using sudo monetdb start testdb1 command, > > where testdb1 is a database i successfully created, it repeatedly throws up > > an error...database 'testdb1' started up, but failed to open up a > > communication channel. > > > > Upon checking the associated merovingian log file i see further messages > > saying the object code to rapi.prelude missing. > > > > Any way to resolve this...? > > > > > > Regards > > Vishal Virmani > > @poisoncreed > >

1 0

Facing issues in implementing Embedded R in MonetDB-11.19.11
by Vishal Virmani 14 May '15

14 May '15

Hi Hannes As you suggested i checked, i am not having any multiple installation of MonetDB. Further i have done a fresh installation. I am including the messages in the merovingian file as well for your reference. Steps followed were Step 1 : I created a clean VM with Ubuntu 14.04 server. Step 2: Installed R-3.2.0 from source files. I checked the R environment it is working fine. Step 3: Downloaded MonetDB source files from http://dev.monetdb.org/downloads/sources/Oct2014-SP3/MonetDB-11.19.11.tar.xz Step 4: Created an empty directory sourcefiles in which i downloaded MonetDB-11.19.11-tar.xz Step 5: Unpacked the source files Step 6: Executed the command sudo ./configure --enable-rintegratioin=yes The result of executing the above command generated the following output: Output of running configure command Enabled/disabled build options: strict is disabled (by default) assert is disabled (by default) debug is disabled (by default) optimize is disabled (by default) developer is disabled (by default) instrument is disabled (by default) profile is disabled (by default) Enabled/disabled components: gdk is enabled monetdb5 is enabled sql is enabled geom is disabled (geos library required for geom library) gsim is disabled (gsl library not found) fits is disabled (cfitsio library not found) jsonstore is disabled (by default) microhttpd is disabled (by default) rdf is disabled (by default) rintegration is enabled datacell is disabled (by default) odbc is disbaled (unixODBC library required for ODBC driver) jdbc is disabled (ant is required to build MonetDB JDBC) control is disabled (ant is required to build merovingian control library) testing is enabled Step 7 : Executed sudo make and make install respectively. They are working fine. Step 8 : Checked sudo mserver5 --version , it gave the the desired output Step 9: Created a dbfarm using the command sudo monetdbd create /mydbfarm Step 10 Started the dbfarm by running sudo monetdbd start /mydbfarm, this also get executed successfully Step 11. Created a sample database testdb1 using command : sudo monetdb create testdb1. This also gets executed fine. Step 12 Upon execution of command sudo monetdb start testdb1, it fails showing me an error in merovingian file and on console i am generating following output starting database 'testdb1'...FAILED start: starting 'testdb1' failed: datbase 'testdb1' started up, but failed to open up a communication channel. Looking forward to your suggestion Regards Vishal Virmani +91 99108 47231 Regards Vishal Virmani +91 99108 47231 @poisoncreed On Tue, May 12, 2015 at 3:30 PM, <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. Facing issues in implementing Embedded R in MonetDB-11.19.11 > (Vishal Virmani) > 2. Re: Facing issues in implementing Embedded R in > MonetDB-11.19.11 (Hannes M?hleisen) > > > ---------------------------------------------------------------------- > > Message: 1 > Date: Tue, 12 May 2015 05:29:13 +0530 > From: Vishal Virmani <virmani.vishal(a)gmail.com> > To: users-list(a)monetdb.org > Subject: Facing issues in implementing Embedded R in MonetDB-11.19.11 > Message-ID: > <CA+dsbSFzWfqd= > GEuAP17rqVPCfVWySShJa+rnyUPHkNbvrjbLw(a)mail.gmail.com> > Content-Type: text/plain; charset="utf-8" > > Hi > > I am implementing Embedded R in MonetDB version 11.19.11. My environment is > VM with Ubuntu 14.04 Server. I have build MonetDB from source and R as well > as from source. > Running the command sudo mserver5 --version shows me the desired output. > > When i try to invoke a database using sudo monetdb start testdb1 command, > where testdb1 is a database i successfully created, it repeatedly throws up > an error...database 'testdb1' started up, but failed to open up a > communication channel. > > Upon checking the associated merovingian log file i see further messages > saying the object code to rapi.prelude missing. > > Any way to resolve this...? > > > Regards > Vishal Virmani > @poisoncreed >

2 1

Reclaiming Hard Disk Space occupied by Intermidiates
by Ashish Kumar Singh 12 May '15

12 May '15

Dear List, I understand from monetdb documentation that intermediates can be materialised in to hard disk in certain scenarios. In our scenario we were running out of disk space infrequently , Is there any way we can suggest monetdb to get rid of all intermediates? Any pointers on this will be really helpful. Regards, Ashish Kumar Singh Mobile: +91-9972045095 Skype: toashishk(a)gmail.com

2 1

Facing issues in implementing Embedded R in MonetDB-11.19.11
by Vishal Virmani 12 May '15

12 May '15

Hi I am implementing Embedded R in MonetDB version 11.19.11. My environment is VM with Ubuntu 14.04 Server. I have build MonetDB from source and R as well as from source. Running the command sudo mserver5 --version shows me the desired output. When i try to invoke a database using sudo monetdb start testdb1 command, where testdb1 is a database i successfully created, it repeatedly throws up an error...database 'testdb1' started up, but failed to open up a communication channel. Upon checking the associated merovingian log file i see further messages saying the object code to rapi.prelude missing. Any way to resolve this...? Regards Vishal Virmani @poisoncreed

2 1

Problem selecting entries newer than a certain date
by Maarten Sander 01 May '15

01 May '15

Hi everyone, I’m probably missing something, but it seems that I cannot select entries that are newer than a given timestamp. Below are my attempts and the schema of the table. Any help would be greatly appreciated! Regards, Maarten sql>SELECT completed FROM "reporting"."transactions" WHERE completed >= '2015-04-10 00:00:00.0000' ORDER BY completed DESC; +-----------+ | completed | +===========+ +-----------+ 0 tuples (0.595ms) sql>SELECT completed FROM "reporting"."transactions" WHERE completed >= CAST('2015-04-10 00:00:00.0000' AS TIMESTAMP) ORDER BY completed DESC; +-----------+ | completed | +===========+ +-----------+ 0 tuples (0.328ms) sql>SELECT completed FROM "reporting"."transactions" ORDER BY completed DESC LIMIT 10; +----------------------------+ | completed | +============================+ | 2015-04-10 11:50:22.051000 | | 2015-04-10 10:55:50.039000 | | 2015-04-09 09:41:13.023000 | | 2015-04-09 09:39:09.079000 | | 2015-04-09 09:35:56.085000 | | 2015-04-08 12:55:13.007000 | | 2015-04-08 12:37:07.002000 | | 2015-04-08 12:28:22.000000 | | 2015-04-08 12:25:25.052000 | | 2015-04-07 21:59:59.099000 | +----------------------------+ 10 tuples (0.683ms) sql>\d "reporting"."transactions" CREATE TABLE "reporting"."transactions" ( "id" CHAR(36) NOT NULL, "status" VARCHAR(25) NOT NULL, "created" TIMESTAMP NOT NULL, "completed" TIMESTAMP, "currency" CHAR(3) NOT NULL, "amount" BIGINT NOT NULL, "balance" VARCHAR(25) NOT NULL, "payment_method" VARCHAR(50) NOT NULL, "fee_currency" CHAR(3), "fee_amount" BIGINT, "order_id" CHAR(36) NOT NULL, "order_status" VARCHAR(25) NOT NULL, "project_id" CHAR(36) NOT NULL, "merchant_id" CHAR(36) NOT NULL, CONSTRAINT "transactions_id_pkey" PRIMARY KEY ("id") );

2 4

Efficient bulk load via JDBC?
by Roberto Cornacchia 29 Apr '15

29 Apr '15

Hello, Is there (or is there a plan for) efficient bulk load via JDBC? (I'm currently running Oct2014 and JDBC 2.13) I looked at: https://www.monetdb.org/book/export/html/340 As far as I understand, the most efficient way currently available in JDBC is to use batches of inserts via prepared statements (with autocommit off). Unfortunately this does not get even close to be fast enough. The speed of a COPY INTO is what I am looking for. But, this is not supported via JDBC. Is there a specific reason, or is it simply not implemented? I do know about the workaround of performing the COPY INTO via mapi protocol (like in http://dev.monetdb.org/hg/MonetDB/file/tip/java/example/SQLcopyinto.java) This provides good speed indeed. However, it is not transaction-safe. When I use this method, and some SQL transaction happens to read (only read!) from the same tables at the same time, then I often get data corruption: the COPY INTO seems to end well (and the data gets actually in place), but new SQL queries on those tables fail with a "BATproject: does not match always" GDK error (checking with gdb I see that the right side of a fetchjoin has count 0). Any idea?

3 4

How to CREATE TRIGGER with a schema-qualifies table?
by Robert Koch 29 Apr '15

29 Apr '15

I have a schema "test" with 2 tables. I want to create a trigger inside this schema bound to a table inside this schema. sql>create trigger test.trigger1 after update on test.profiletab insert into test.triggerlog; syntax error, unexpected '.', expecting WHILE in: "create trigger test.trigger1 after update on test." Thank you.

1 0

INSERT DEFAULT values only
by Robert Koch 28 Apr '15

28 Apr '15

How can I insert a row with default values only? CREATE TABLE test.tbl1 ( id bigint DEFAULT NEXT VALUE FOR "my_test_seq" NOT NULL, instime DATE DEFAULT now() NOT NULL ); Both insert into test.triggerlog (id) VALUES (10000); and insert into test.triggerlog (instime) VALUES (now()); work. Neither insert into test.triggerlog (id, instime) VALUES (DEFAULT, DEFAULT); nor insert into test.triggerlog (id, instime) VALUES (); work. What is the correct statement? Thank you.

1 1

Confusing if MonetDB RDF/SparQL is supported
by Aris Setyawan 27 Apr '15

27 Apr '15

Hi All, In the recent week, I monitoring the monet's development commit logs. >From that logs, I have found that "RDF support was removed". But in the next log, some features under RDF was added. I'm bit confused about this. What is the state of RDF support in MonetDB? Thanks, -Aris

1 0

Parallel Query
by Luciano Sasso 25 Apr '15

25 Apr '15

Hi, 1- On a single server, the query is executed in parallel?, Common relational database also has this ability (SQL Server, PostgreSQL etc.)? 2- And in a cluster, the same query is distributed for cluster nodes , in MonetDB? 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

2 1

How to create a user with administrator rights
by Puthick Hok 24 Apr '15

24 Apr '15

Hi, I wan to use "COPY INTO table from 'file'" SQL instruction with a user rather than monetdb. This user needs the administrator rights. So far I have done these using monetdb user as the login: CREATE USER "mdb_user" WITH PASSWORD '"password"' NAME 'Test db user' SCHEMA "sys"; CREATE ROLE "testdbadmin" WITH ADMIN CURRENT_USER; GRANT "testdbadmin" TO "mdb_user" WITH ADMIN OPTION; CREATE SCHEMA "testdb" AUTHORIZATION "mdb_user"; ALTER USER "mdb_user" SET SCHEMA "testdb"; I learnt this from MonetDB UserGuide tutorial and from MonetDB authorisation documentation. However, I still cannot use 'mdb_user' for COPY INTO from file command. What is missing? What does 'ADMIN OPTION' do? How to make 'mdb_user' have the same right as monetdb? At the moment, I'm using monetdb user directly. But, it would be a bit nicer to have a different username. I'm working on a web service connecting to 3 different databases: mysql, postgres and now monetdb. They have a common username for the database connection. If I keep using monetdb user, the commonality rule is broken. I have another to use 'mdb_user' via STDIN and piping, but exporting DOTMONETDBFILE as an environment variable to make mclient login with out asking for password is another complexity dimension. Making mdb_user to have administrator rights like monetdb is the best option for me if this is possible, and I hope this is possible. Regards, Puthick

2 2

MonetDB Cluster
by Luciano Sasso 23 Apr '15

23 Apr '15

Hi, Cluster MonetDB is only partitioning or is replication, or all? 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

1 0

Database Size
by Luciano Sasso 23 Apr '15

23 Apr '15

Hi, What is the largest (size in G) database that you have worked? on a single server or cluster. 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

1 0

Server crash with DDL in transaction
by Pete Hollobon 23 Apr '15

23 Apr '15

Running this on Oct2014-SP2 crashes the server. CREATE TABLE bugexample ( id INTEGER ); START TRANSACTION; ALTER TABLE bugexample ADD COLUMN newcolumn integer; ALTER TABLE bugexample ALTER COLUMN newcolumn SET NOT NULL; COMMIT; It is reproducible. I've logged a bug - number 3715. Thanks, Pete.

1 0

Table types in MonetDB
by Robert Koch 22 Apr '15

22 Apr '15

Hi all, in the documentation of system catalog (https://www.monetdb.org/Documentation/SQLcatalog/TablesColumns) I found 4 types of tables: 0=Table/1=View/2=Generated/3=Merge What is 2 and 3? Thanks.

2 3

Exactly 10 Seconds Delay during JDBC connect to MonetDB
by Robert Koch 22 Apr '15

22 Apr '15

Hi all, I'm using DBVisualizer to connect via JDBC to MonetDB. There is a delay of very exactly 10 seconds during the connection process. This only happens via JDBC. There are no DNS issues, since the connection is done via IP directly. Connection from the commandline of the server with mclient works perfectly. First step to get closer was to inspect the output of tcpdump. It seems to be on the side of MonetDB, since the last packet is the one sent to MonetDB (192.168.56.102) from my Windows Client (192.168.56.1). 09:33:24.663824 IP 192.168.56.1.57271 > 192.168.56.102.50000: Flags [.], ack 1, win 256, length 0 --- 10 soconds 09:33:34.676884 IP 192.168.56.102.50000 > 192.168.56.1.57271: Flags [P.], seq 1:3, ack 1, win 229, length 2 Additionally, DBVis ist connecting speedy with other databases (PostgreSQL) on the same server. Using following version: MonetDB 11.19.9 MonetDB Native Driver 2.14 (Liberica 20150123 based on MCL v1.11) I did not experience this problem in former versions of MonetDB. Any idea? Thank you.

2 3

Creating an AWS AMI
by Maarten Sander 22 Apr '15

22 Apr '15

Hi everyone, Is there an easy way to create an AWS AMI running MonetDB? Perhaps someone maintains a Packer template? Regards, Maarten

2 4

Strange syntax error when create trigger
by Caroptions Caroptions 21 Apr '15

21 Apr '15

Hi User-List, I am experiencing very strange behavior: CREATE TRIGGER marketdata.calc_sdate BEFORE INSERT ON marketdata.quotes FOR EACH ROW BEGIN ATOMIC -- select * from marketdata.quotes limit 1; END; ERROR: syntax error, unexpected '.', expecting WHILE in: "create trigger marketdata.calc_sdate before insert on marketdata." When I am creating trigger on table without schema it works fine. CREATE TRIGGER marketdata.calc_sdate BEFORE INSERT ON t1 ... Assuming t1 created without schema. All combination of " around schema and table, schema only, table only didn't help. Am I doing something wrong or this is the bug? Thanks,John

2 2

More information about data loss in Bugzilla
by Luciano Sasso 20 Apr '15

20 Apr '15

I found this bug related to loss of data, is exactly what my bug, it was not clear in bugzilla if it was resolved or not, can tell me? I'm very insecure with MonetDB. https://www.monetdb.org/bugzilla/show_bug.cgi?id=3271 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

1 0

More information about data loss
by Luciano Sasso 20 Apr '15

20 Apr '15

Hi, Some more details about data loss in MonetDB, I saw all that yours have commented, and also is aware that I am not with the latest version (update in ), but is a serious mistake in the previous version . Have a comment that said a data synchronization correction was made, has to do with the change or reorganization of data, even when it is only run queries? Will by defragmentation reasons (vacum PostgreSQL), or index creation, the data is changed / moved and in this case can be lost. The query for example is "select product from products where cod_product in (123,12434,33434)", only the data in this column (cod_product) were lost (went blank), and still only clinhas related codes on in. The only thing out of the ordinary has happened is that the ongoing connection was aborted, Pentaho was killed forced, aborting the connections, but the MonetDB service has stopped "stop MonetDB dw", which usually stopped, when start "moentdb start dw", rose correctly without errors, but took a long time to rise, very much, and then went to see the lost data. 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

1 0

MAL aggregate function difficulty
by Puthick Hok 20 Apr '15

20 Apr '15

Hi, I'm trying to define an aggregate function in MonetDB to concatenate rows in select group by (GROUP_CONCAT in MySQL). Postgres does not have this but there is an example to define this function. I'm starting to use monetdb from mysql thanks to MonetDB performance for large data sets. For some small tables, we have the feature to allow users to store small amount of custom data in rows and have a web service to convert rows into columns. We have this feature because we can do it in MySQL and Postgres using GROUP_CONCAT. I'm working on an open source breeding system which stores breeding data in MySQL, environment data in Postgres and now marker data in MonetDB in Australia. The system is called KDDart. Going back to my problem is that there is the same question with GROUP_CONCAT about 3 years ago. I have been googling and trying based on the information available for 2 days now. I have learnt how to define a MAL function and make it available in SQL mode by saving it in the autoload. However, when it comes to define and write a MAL aggregate function. I'm unable to find any step-by-step example. I could not start successfully with a simple aggregate solution. I google further that we a documentation on monetdb website about defining an aggregate function in R. I installed R and recompile my MonetDB source (MonetDB-11.19.9) on my openSuSe 12.2 with a pre-compiled R (R-base-2.15.1-1.1.4.x86_64). I followed the instruction to enable rintegration and compile successfully. R integration is loaded with mserver5 instruction. But when using monetdbd it says 'set: no such property: embedr'. I did bit of C debugging but I only got Segmentation fault. I don't know what you could do to help me out. I wish I could write the GROUP_CONCAT MAL function and being able to define it permanently in .mal file and being able to link it to the SQL. I hope you can help me out. Thanks in advance. Puthick

3 3

MonetDB Cluster
by Luciano Sasso 17 Apr '15

17 Apr '15

Hello, I read all posts about cluster in MonetDB, but dont understand, I will make some specific quaestions, I think it will be more objective: 1- MonetDB makes automatic replication between nodes? 2- The writing is performed in a just node and this replicate to the other? 3. The reading can be done on any node? 4- There is a router that distributes queries between the nodes? 5- If a query from a client is very cost, this query can be distributed between nodes? 6- MonetDB makes sharding transparently? 7- How to funnel and multiplex ? 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

3 2

MonetDB vs Vertica CE
by Luciano Sasso 17 Apr '15

17 Apr '15

Hi, The team of staff this questioning me about using Vertica, but it has limitations on the CE version. Someone already used the Vertica has some advantage over MonetDB? -- 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

7 10

MonetDB vs Vertica CE
by Luciano Sasso 17 Apr '15

17 Apr '15

Hi, thanks for awnsers. Precisely, I tried last year to deploy Vertical, but had trouble install and configure, since the MonetDB, was simply install and use, so do I use to this day with a database analytical about 30G, and 50 million records in the main table. I use Pentaho (Saiku) to explore the data, and also queries for creating dashboards. Now I pretty increase the volume of data, so that is posted another question about MonetDB clustered, it is the only thi still worries me, if I need to scalling. I dont think I still need something like HBase / Hadoop. Also have questioned me about using MongoDB, but from what I saw on MongoDB, needs a lot of programming to performance aggregations etc. with the MonetDB it is transparent. Today my data for analysis in MonetDB are fully denormalized not use model schema star. I see that many proposes the architecture with MongoDB for DW and Vertica or MonetDB, for analysis, but that think with volume that I have, I can use the MonetDB dw as well. I see there are many things about trying to force the use of Big data tools without really need it. Att, On 16/04/2015 10:38, 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: MonetDB vs Vertica CE (Ruben Silva) > > > ---------------------------------------------------------------------- > > Message: 1 > Date: Thu, 16 Apr 2015 14:37:47 +0100 > From: Ruben Silva <ruben.silva(a)cortex-intelligence.com> > To: Communication channel for MonetDB users <users-list(a)monetdb.org> > Subject: Re: MonetDB vs Vertica CE > Message-ID: > <CAER5Ptm+M8e1B5r0bgXKnMrADGhW+ch6Wcdy5co1jw8b=CehCA(a)mail.gmail.com> > Content-Type: text/plain; charset="utf-8" > > Hello Luciano, > > I don't have a full grasp of the new stuff introduced into MonetDB in the > last year, but as far as I know there are still major differences between > the two systems for me the most important is scalability. Vertica has no > single point of failure when working as a cluster. However the CE version > is limited to three nodes, so scalability is also limited in that case. > > Vertica relies heavily on projections as a way to improve performance. If > your data model is relatively stable it is something fairly easy to use, > but if your data model changes at a high rate you will have a considerable > amount of additional work managing the projections. Also if you use many > projections they will hurt your DML statements performance. > > MonetDB feeds on CPU and has no boundaries for its appetite, consuming as > much resources as possible in order to respond to a single statement. That > can be very good or very bad, it depends on your use case. Vertica has > resource pools that are used to regulate the stuff (IO, Ram, CPU) that each > query is allowed to use. > > I don't know your use case (and that is crucial for a good advice), but if > you are considering a scenario where you have a single machine for the > database system, then go with MonetDB without any doubt. It is just faster > and simpler. > > Cumprimentos (Regards), > > 2015-04-16 14:04 GMT+01:00 Luciano Sasso <luciano(a)gsgroup.com.br>: > >> Hi, >> >> The team of staff this questioning me about using Vertica, but it has >> limitations on >> the CE version. Someone already used the Vertica has some advantage over >> MonetDB? >> >> -- >> 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 >> >> >> ------------------------------ >> [image: Avast logo] <http://www.avast.com/> >> >> Este email foi escaneado pelo Avast antiv?rus. >> www.avast.com >> >> >> _______________________________________________ >> users-list mailing list >> users-list(a)monetdb.org >> https://www.monetdb.org/mailman/listinfo/users-list >> >> >

2 1

MonetDB clustered
by Luciano Sasso 16 Apr '15

16 Apr '15

Hi, I have a question about MonetDB clustered 1- It only replicates data between nodes? 2- He replicates data between nodes doing sharding? 3- When a query is sent to the cluster, it is processed by one node just or distributed by several nodes? 4- Cluster is related to multiplex-funnel? 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

2 1

Re: MonetDB vs Vertica CE
by Luciano Sasso 16 Apr '15

16 Apr '15

Hi, Right, but in the case when you take the data 30 minutes, you take all the data, and the bank have to see what should be changed or inserted? is not better you take only the new data in this way will always be inserted? I do it today Att, On 16/04/2015 10:47, 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: MonetDB vs Vertica CE (shamsul hassan) > > > ---------------------------------------------------------------------- > > Message: 1 > Date: Thu, 16 Apr 2015 14:47:46 +0100 > From: shamsul hassan <shamsulbuddy(a)gmail.com> > To: Communication channel for MonetDB users <users-list(a)monetdb.org> > Subject: Re: MonetDB vs Vertica CE > Message-ID: > <CAONo-du24VrQ_PR4nzNq7rT7Uw47fKQb5Pr376UWEDmTbCN7qQ(a)mail.gmail.com> > Content-Type: text/plain; charset="utf-8" > > there is only one thing which has stopped me for using MonetDB in > production is the lack of UPSERT operation. > In my case new data comes every 30 mins and I cannot truncate and load the > whole table again. Ideally an UPSERT would have helped here as it will only > touch those records which are new or changed without truncating the whole > table. > > I raised the question few days before in Community list but didn't get any > useful answer. > > If i get any workaround for these UPSERTS the i would be as happy as pig in > dirty water :) > > Thanks > > > On Thu, Apr 16, 2015 at 2:37 PM, Ruben Silva < > ruben.silva(a)cortex-intelligence.com> wrote: > >> Hello Luciano, >> >> I don't have a full grasp of the new stuff introduced into MonetDB in the >> last year, but as far as I know there are still major differences between >> the two systems for me the most important is scalability. Vertica has no >> single point of failure when working as a cluster. However the CE version >> is limited to three nodes, so scalability is also limited in that case. >> >> Vertica relies heavily on projections as a way to improve performance. If >> your data model is relatively stable it is something fairly easy to use, >> but if your data model changes at a high rate you will have a considerable >> amount of additional work managing the projections. Also if you use many >> projections they will hurt your DML statements performance. >> >> MonetDB feeds on CPU and has no boundaries for its appetite, consuming as >> much resources as possible in order to respond to a single statement. That >> can be very good or very bad, it depends on your use case. Vertica has >> resource pools that are used to regulate the stuff (IO, Ram, CPU) that each >> query is allowed to use. >> >> I don't know your use case (and that is crucial for a good advice), but if >> you are considering a scenario where you have a single machine for the >> database system, then go with MonetDB without any doubt. It is just faster >> and simpler. >> >> Cumprimentos (Regards), >> >> 2015-04-16 14:04 GMT+01:00 Luciano Sasso <luciano(a)gsgroup.com.br>: >> >>> Hi, >>> >>> The team of staff this questioning me about using Vertica, but it has >>> limitations on >>> the CE version. Someone already used the Vertica has some advantage over >>> MonetDB? >>> >>> -- >>> 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 >>> >>> >>> ------------------------------ >>> [image: Avast logo] <http://www.avast.com/> >>> >>> Este email foi escaneado pelo Avast antiv?rus. >>> www.avast.com >>> >>> >>> _______________________________________________ >>> 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

MonetDB with R Integration for Windows Issues
by Bogdan Dimitrov 15 Apr '15

15 Apr '15

Hello everyone, I have been trying to get Embedded R in MonetDB to run on Windows 7 and I ran into a bunch of problems. I was wondering if anybody else has been able to get it running or whether there are better workarounds for the issues that I found. I am using manually built MonetDB version 11.19.9 with R integration enabled. The problems I had to fix seem to stem from the fact that the MonetDB is built using the VS2010 compiler, while the R assemblies are built with the MinGW compiler, so sometimes the two do not play nicely together and the server crashes with access violation errors on several occasions. After some debugging of both MonetDB and R source I identified the following problems: 1. The call in rapi.c line 373: R_tryEvalSilent(VECTOR_ELT(R_ParseVector(mkString(rapiinclude), 1, &status, R_NilValue), 0), R_GlobalEnv, &evalErr); fails with an access violation error. The problem is that the path contained in the char array called rapiinclude uses backslashes (Windows style), while the R code expects a UNIX style path with forward slashes. I added a bit of code to rapi.c that replaces the backslashes with forward slashes in the path to work around this issue. 2. The call in rapi.c line 742: fprintf(stdout, "# MonetDB/R module loaded\n"); fails with an access violation (as well as the rest of fprintf calls in rapi.c). Passing file handles across dll boundaries can cause problems even when same compiler is used, here it is passed between dlls built with different compilers (VS2010 compiled lib_rapi.dll to MinGW compiled R.dll). I replaced all fprintf calls with printf to work around this issue. Fixing the above issues I managed to run MonetDB with embedded R on Windows 7 and all the R scripts I tried seemed to work fine until I tried to use an R library that had to be downloaded first automatically using the overloaded library() function as described in https://www.monetdb.org/content/embedded-r-monetdb . 3. The call in rapi.R line 32: install.packages(package,repos=c("http://cran.rstudio.com/"),lib=.rapi.libdir,quiet=T) causes access violation as well, but I have not been able to find a workaround for this yet (apart from manually downloading the R package and dropping it in rapi_packages folder in dbfarm, so no downloading needs to happen). These should be Windows specific issues as MonetDB and R can be compiled with the same compiler for *NIX systems, while the MonetDB build instructions for Windows explicitly state that there is no longer support for compilers other than Microsoft Visual Studio's. I would appreciate it if anybody can help me solve the problem with downloading R libraries or knows a way to get the R integration running on Windows without the custom code changes described above. Best regards, Bogdan Dimitrov ________________________________

2 1

segfault in function when return table from IF (or any code branch)
by Caroptions Caroptions 14 Apr '15

14 Apr '15

DROP FUNCTION test(text); CREATE FUNCTION test(a text) RETURNS table(SOMEFIELD date) BEGIN IF a is NULL THEN return table( select cast(sdate as date) from quotes limit 10 ); ELSE return table( select cast(sdate as date) from quotes limit 10 ); END IF; END;-- ****** OR ******CREATE FUNCTION test(a text) RETURNS table(SOMEFIELD double) BEGIN declare table t(f double); select 0.0 into t; return t; END; -- ****** OR ******CREATE FUNCTION test(a text) RETURNS table(SOMEFIELD double) BEGIN declare table t(f double); set t = table (select 0.0); return t; END; When submit (not even execute) -> segfaultBug: https://www.monetdb.org/bugzilla/show_bug.cgi?id=3699Thanks,Vasili

1 0

Time comparison is not correct
by Caroptions Caroptions 14 Apr '15

14 Apr '15

select cast(timestamptz '2001-01-01 19:00:00 -04:00' as timetz)< cast( timestamptz '2001-01-01 23:00:00 -04:00' as timetz); -> false. Bug: https://www.monetdb.org/bugzilla/show_bug.cgi?id=3700 As an idea (and it seems PostgreSQL uses this approach) when you convert to UTC, you should not "reset" count at 24 but allow hours more that 24:00. This way 23:00 -04:00 will become 27:00 UTC then the comparison will be correct regardless timezone. Thanks,John

1 0

NThreads Parameter and the Performance Related to the Number of Database Instance
by Joseph Mate 10 Apr '15

10 Apr '15

Hello MonetDB Users, In short: 1) What does the nthreads setting mean? 2) Why does performance increase as you increase the number of database instances of a single farm? Is there any way to avoid this? In long: What does the nthreads setting mean? From the manpage[1] it's the number of worker threads that perform main processing. Is this the total number of threads for that database instance? Or is it per query? I've compared the performance of different settings. I find it strange that nthreads=8 would perform the best because I have 12 cores. I confirmed the number of cores by checking nproc. Here are my plots showing that 8 threads performs best on my system: Throughput: https://cs.uwaterloo.ca/~jmate/nthreads-and-database-instances/throughput-v… Average Response Ttime: https://cs.uwaterloo.ca/~jmate/nthreads-and-database-instances/rsptm-avg-vs… 99th Percentile Response Time: https://cs.uwaterloo.ca/~jmate/nthreads-and-database-instances/rsptm-p99-vs… The second thing is: why does the performance increase if I distribute the conncurent clients over multiple database instances on the same data farm? I expected the opposite; performance should decrease. I thought I would increase the overhead by adding more database instances to the same farm. Is there any way to avoid this? Below are the plots I have. I used 40 concurrent TPC-H clients for all: Throughput: https://cs.uwaterloo.ca/~jmate/nthreads-and-database-instances/throughput-v… Average Response Time: https://cs.uwaterloo.ca/~jmate/nthreads-and-database-instances/rsptm-avg-vs… 99th Percentile Response Time: https://cs.uwaterloo.ca/~jmate/nthreads-and-database-instances/rsptm-p99-vs… Context: To give you guys some context on my project: I'm a Master's student doing my research on DBaaS tenant placement. I am evaluating some placement algorithms by using the TPC-H workload and MonetDB as the database. I am using separate database instances on a single farm for isolation. The workload I'm testing with runs read only queries from the TPC-H benchmark. Each TPC-H client is a thread each with its own persistent connection to the database running the following pseudocode: while true for queryNum 1 ... 22 # except query 15 which creates a tmp table run queury queryNum Each database instance has 100MB of data. Thank you for your time, Joseph References: [1] https://www.monetdb.org/Documentation/monetdb-man-page

3 5

first_value/last_value solution in MonetDB
by Caroptions Caroptions 10 Apr '15

10 Apr '15

Hi, Till I am trying to resolve my business problem I discovered the segfault bug which I have reported in MonetDB bug tracking system. I believe Monetdb is great product and team is moving in right direction utilizing latests advances in technology. Nevertheless my "business" problem is to build OHLC Time Series for longer time frame from shorter ohlc time series. I need partition by interval (5 minutes, 30 minutes, 1 hour, 1 day) and get first record of open, max of high, min of low and last of close from let's say 1 minute ohlc series. In postgresql there are 2 functions: first_value and last_value, which allows to do exactly this. Are there any function/approach/techniques which could be utilized to have the same results in MonetDB, I could do this aggregation on client side but this way I will not utilize DB potential and mainly the network traffic will be prohibitive for the solution. I am sure this question is already addressed somewhere, but I couldn't find, the solution should be trivial and pretty standard but I no luck on my side, if you push me in right directly I will really appreciate. Thank you in advance!John

2 5

database was killed by signal SIGSEGV
by Miguel Costa 09 Apr '15

09 Apr '15

Hi there, I'm running a monetdb server with the 2014-SP2 version. I have 61GB of RAM with CentOS 6.5. I have one table with almost 100M rows. I do cycles of 85000 inserts and everything is fine for the first ones, but after some cycles it just gives the error "database was killed by signal SIGSEGV" On my var/log/messages gives the error : kernel: mserver5[6773]: segfault at 18 ip error 4 in lib_sql.so. In the beginning everything was fine, I manage to insert the 100M and I didn't had any problems, but now it's constantly crashing. My database size is more or less 40GB. The memory on the server occupied doesn't reach 1GB. Any suggestions of what to look for?

2 1

Querying a monetdb cluster
by José Raúl Pérez Rodríguez 09 Apr '15

09 Apr '15

Hi, I am new in monetdb. How do I make a query to a cluster? I maked a test inserting data into each particular node of a two node cluster, but I don't see the way of making queries to the cluster, I known that multiplex funnel can return data from several nodes, joining data obtained from each node, but with multiplex funnel, I can't sort the data. There is not doc that explains this problem?

1 0

Join memory overhead, COPY INTO csv, MT_mremap failure.
by Valentin Reis 09 Apr '15

09 Apr '15

Hello all, To the purpose of evaluating MonetDB, I am trying to perform the following: Load two csv files into two tables, from disk A. Perform a JOIN operation on two fields, using disk B for the database. Pipe the result to a csv file, on disk A. This results in a very high disk B usage, and subsequent MT_mremap failure, as seen in merovingian.log: 2015-04-08 12:50:14 ERR google-trace[2737]: = gdk_posix.c:428: MT_mremap(./bat/06/675.tail,7e8738e90000,91992817664,110391328768): GDKextendf() failed Does somebody have ideas or explanations? Details follow: I am loading csv data from hard drive A. The files have size: -rw-r--r-- 1 fre fre 2.6G Apr 5 16:25 task_events_cut.csv -rw-r--r-- 1 fre fre 34G Apr 5 16:04 task_usage_cut.csv I create a database in a separate hard drive B (2TB): rm -rf /mnt/diskB/mdb mkdir /mnt/diskB/mdb/ monetdbd create /mnt/diskB/mdb monetdbd start /mnt/diskB/mdb monetdb create google-trace monetdb release google-trace I then load the data into the database: mclient ct_trace_events_reduced.sql -d google-trace pv /mnt/diskA/task_events_cut.csv |mclient -d google-trace -s "COPY INTO task_events_reduced FROM STDIN USING DELIMITERS ',','\\n'" - mclient ct_trace_usage_reduced.sql -d google-trace pv /mnt/diskA/task_usage_cut.csv |mclient -d google-trace -s "COPY INTO task_usage_reduced FROM STDIN USING DELIMITERS ',','\\n'" - using very standard scripts: $ cat ct_trace_events_reduced.sql ct_trace_usage_reduced.sql DROP TABLE task_events_reduced; CREATE TABLE task_events_reduced ( "job_id" BIGINT , "task_id" BIGINT , "class" SMALLINT , "priority" SMALLINT); DROP TABLE task_usage_reduced; CREATE TABLE task_usage_reduced ( "job_id" BIGINT , "task_id" BIGINT , "cpu_mean" FLOAT , "cpu_sample" FLOAT); These two operations take about 50 minutes, which is very reasonable. I then use mclient to do my join: mclient join.sql -d google-trace using the script: $ cat join.sql COPY (SELECT te.job_id, te.task_id, te.class, te.priority, tu.cpu_mean, tu.cpu_sample FROM (SELECT * FROM task_events_reduced )AS te RIGHT JOIN (SELECT * FROM task_usage_reduced )AS tu ON(te.job_id=tu.job_id AND te.task_id=tu.task_id) ) INTO '/diskA/join.csv' USING DELIMITERS ',','\n'; This results in more than three hours of data crunching on a google compute engine machine (16 processors and 100GB RAM), where disk B is being increasingly used, until it is full (2TB HDD). Then, the aforementionned error happens. I am not hoping that MonetDB would perform streaming I/O on the right file from the join. However the disk usage seems quite high. Is there a way to force MonetDB to do a hash join? Thanks a lot, Valentin Reis University of Warsaw

4 7

MonetDB Real time analytics
by Luciano Sasso 09 Apr '15

09 Apr '15

Hello, I can work in real time with the MonetDB, for example, a job extracting and inserting data in MonetDB as stream and other job for reading data, aggregation and creation of indicators for dashboads in real or near real time ? -- Luciano Sasso Vieira Gestor - Inteligência de Negócios luciano(a)gsgroup.com.br <http://www.gsgroup.com.br> | tel: 17 3353-0833 | cel: 17 9706-9335 www.gsgroup.com.br <http://www.gsgroup.com.br> --- Este email foi escaneado pelo Avast antivírus. http://www.avast.com

1 0

RE: Details about MonetDB, data loss in restart server (Ying Zhang)
by Luciano Sasso 08 Apr '15

08 Apr '15

Hi, - OS is Debian 7 64-bit, in a VM in VMware virtualization System. - Hard disk 100G with 26% usage. - 5 cores. - 6G RAM. - Version MonetDB: MonetDB 5 server v11.15.17 "Feb2013-SP5" - Table: create table (id int, dt date, product varchar (50) .... etc, and another 30 columns between int and varchar. - Query: select dt, count (id) from table where produtct in (....) group by dt - History: Long time MonetDB use with Pentaho (Saiku) for analysis of data cubes, never had problems, works great with millions of records. When the problem occurred, the steps taken were: 1- A couple of days before it occurred, I made a 32 million load records to the table. 2- I used this table for many analyzes via SQL client (without using cubes) and everything was fine. 3- I created a cube in Pentaho for this table and also used a lot and everything went well. 4- When I put the dt fields in the cube and the interface seems to have caught the Pentaho and mserver held all machine resources (memory and CPU), less disk. This lock may have been because the Pentaho, but specifically the mondrian, have had a problem with the date field 5- I tried to stop the Pentaho, but did not work, had to force a kill -9 pid. 6- In the case of MonetDB, the command MonetDB stop dw, worked properly and stopped the service. 7- When I run MonetDB start dw, it took a while longer than usual, but the service start usually without showing error on console. 8- With other tables there were no data loss problem, only that table lost data just the product column, which was used in the query, the remaining columns have not suffered problems. But but at no time update or delete command has been executed, only selects were executed 9- All though I forced Pentaho standing in the execution of the query, but MonetDB stopped normal, and a query should never change data, of course? Log 2015-03-31 15:58:38 MSG control[1114]: (local): served status list 2015-03-31 15:58:38 MSG merovingian[1114]: sending process 14173 (database 'dw-zoombox') the TERM signal 2015-03-31 15:58:39 MSG merovingian[1114]: database 'dw-zoombox' (14173) has exited with exit status 0 2015-03-31 15:58:39 MSG merovingian[1114]: database 'dw-zoombox' has shut down 2015-03-31 15:58:39 MSG control[1114]: (local): stopped database 'dw-zoombox' 2015-03-31 15:58:44 MSG control[1114]: (local): served status list 2015-03-31 15:58:44 MSG merovingian[1114]: starting database 'dw-zoombox', up min/avg/max: 2d/9w/18w, crash average: 0.00 0.10 0.03 (3-2=1) 2015-03-31 15:58:45 MSG dw-zoombox[19366]: arguments: /usr/bin/mserver5 --dbpath=/dados/dbfarm/dw-zoombox --set merovingian_uri=mapi:monetdb://SRVBIL01:50000/dw-zoombox --set mapi_open=false --set mapi_port=0 --set mapi_usock=/dados/dbfarm/dw-zoombox/.mapi.sock --set monet_vault_key=/dados/dbfarm/dw-zoombox/.vaultkey --set gdk_nr_threads=5 --set max_clients=64 --set sql_optimizer=default_pipe --set monet_daemon=yes 2015-03-31 15:59:34 MSG dw-zoombox[19366]: # MonetDB 5 server v11.15.17 "Feb2013-SP5" 2015-03-31 15:59:34 MSG dw-zoombox[19366]: # Serving database 'dw-zoombox', using 5 threads 2015-03-31 15:59:34 MSG dw-zoombox[19366]: # Compiled for x86_64-pc-linux-gnu/64bit with 64bit OIDs dynamically linked 2015-03-31 15:59:34 MSG dw-zoombox[19366]: # Found 11.765 GiB available main-memory. 2015-03-31 15:59:34 MSG dw-zoombox[19366]: # Copyright (c) 1993-July 2008 CWI. 2015-03-31 15:59:34 MSG dw-zoombox[19366]: # Copyright (c) August 2008-2013 MonetDB B.V., all rights reserved 2015-03-31 15:59:34 MSG dw-zoombox[19366]: # Visit http://www.monetdb.org/ for further information 2015-03-31 15:59:34 MSG dw-zoombox[19366]: # Listening for UNIX domain connection requests on mapi:monetdb:///dados/dbfarm/dw-zoombox/.mapi.sock 2015-03-31 15:59:34 MSG dw-zoombox[19366]: # MonetDB/SQL module loaded 2015-03-31 15:59:34 MSG control[1114]: (local): started 'dw-zoombox' 2015-03-31 15:59:34 MSG merovingian[1114]: proxying client 192.168.1.7:49105 for database 'dw-zoombox' to mapi:monetdb:///dados/dbfarm/dw-zoombox/.mapi.sock?database=dw-zoombox 2015-03-31 15:59:34 MSG merovingian[1114]: target connection is on local UNIX domain socket, passing on filedescriptor instead of proxying 2015-03-31 16:05:00 MSG merovingian[1114]: proxying client 192.168.1.7:49117 for database 'dw-zoombox' to mapi:monetdb:///dados/dbfarm/dw-zoombox/.mapi.sock?database=dw-zoombox 2015-03-31 16:05:00 MSG merovingian[1114]: target connection is on local UNIX domain socket, passing on filedescriptor instead of proxying 2015-03-31 16:05:20 MSG merovingian[1114]: proxying client 192.168.1.7:49119 for database 'dw-zoombox' to mapi:monetdb:///dados/dbfarm/dw-zoombox/.mapi.sock?database=dw-zoombox 2015-03-31 16:05:20 MSG merovingian[1114]: target connection is on local UNIX domain socket, passing on filedescriptor instead of proxying 2015-03-31 16:06:05 ERR discovery[1114]: cannot retrieve name info: Temporary failure in name resolution 2015-03-31 16:16:05 ERR discovery[1114]: cannot retrieve name info: Temporary failure in name resolution 2015-03-31 16:16:16 MSG merovingian[1114]: proxying client 192.168.1.8:50220 for database 'dw-zoombox' to mapi:monetdb:///dados/dbfarm/dw-zoombox/.mapi.sock?database=dw-zoombox 2015-03-31 16:16:16 MSG merovingian[1114]: target connection is on local UNIX domain socket, passing on filedescriptor instead of proxying 2015-03-31 16:17:04 MSG merovingian[1114]: proxying client 192.168.1.7:49159 for database 'dw-zoombox' to mapi:monetdb:///dados/dbfarm/dw-zoombox/.mapi.sock?database=dw-zoombox 2015-03-31 16:17:04 MSG merovingian[1114]: target connection is on local UNIX domain socket, passing on filedescriptor instead of proxying > ---------------------------------------------------------------------- > > Message: 1 > Date: Thu, 2 Apr 2015 23:16:39 +0200 > From: Ying Zhang <Y.Zhang(a)cwi.nl> > To: "Communication channel for developers of the MonetDB suite." > <developers-list(a)monetdb.org> > Cc: users-list(a)monetdb.org > Subject: Re: MonetDB, data loss in restart server > Message-ID: <6C2903C0-4399-462D-9D3F-822351DE1979(a)cwi.nl> > Content-Type: text/plain; charset=windows-1252 > > Hello Luciano, > > Thanks for using monetdb, and sorry for the inconvenience. > Would you please give us some background information about your setting: > > - which OS? > - hardware spec. of your system > - which monetdb version? > - what does your query do? > - when and how did the database stop? If the monetdb server stopped, did it give any error message? > - what's at the end of the merovingian log? > > With kind regards, > > Jennie > >> On Apr 01, 2015, at 06:46, Luciano Sasso <luciano(a)gsgroup.com.br> wrote: >> >> Hello, >> >> I have used the MonetDB in production for some time, and I have nothing to complain about, >> are tables with more than 30 columns and 30 million records, do analysis through cubes in Pentaho / Saiku. >> Have restarted the server several times, and never had problems with loss of data.But today, >> after performing a query that was consuming a lot of machine, stopped the database, and when I started, >> data from some columns were blank. How can it, will the data were still uncommitted? >> when performing a query the MonetDB changes the data or reorganized? which may have occurred, I am concerned. >> >> >> -- >> <Slice_01.png> <Slice_02.jpg> >> Luciano Sasso Vieira >> Gestor - Intelig?ncia de Neg?cios >> >> luciano(a)gsgroup.com.br | tel: 17 3353-0833 | cel: 17 9706-9335 >> <Slice_04.png> www.gsgroup.com.br >> <Slice_06.png> >> >> >> Este email foi escaneado pelo Avast antiv?rus. >> www.avast.com >> >> >> _______________________________________________ >> developers-list mailing list >> developers-list(a)monetdb.org >> https://www.monetdb.org/mailman/listinfo/developers-list > > > ------------------------------ > > Subject: Digest Footer > > _______________________________________________ > users-list mailing list > users-list(a)monetdb.org > https://www.monetdb.org/mailman/listinfo/users-list > > > ------------------------------ > > End of users-list Digest, Vol 32, Issue 6 > ***************************************** > > -- Luciano Sasso Vieira Gestor - Inteligência de Negócios luciano(a)gsgroup.com.br <http://www.gsgroup.com.br> | tel: 17 3353-0833 | cel: 17 9706-9335 www.gsgroup.com.br <http://www.gsgroup.com.br> --- Este email foi escaneado pelo Avast antivírus. http://www.avast.com

2 1

mserver5 segfault at 0 ip error 4 in lib_sql.so
by Caroptions Caroptions 08 Apr '15

08 Apr '15

Hi User-list! I am experiencing segfault monetdb crash, platform: CentOS Linux release 7.1.1503 (Core) 3.10.0-229.1.2.el7.x86_64 #1 SMP Fri Mar 27 03:04:26 UTC 2015 x86_64 x86_64 x86_64 GNU/Linux Table: CREATE TABLE "marketdata"."quotes" ( "id" INTEGER NOT NULL DEFAULT next value for "marketdata"."seq_6391", "qtime" TIMESTAMP WITH TIME ZONE NOT NULL, "sdate" TIMESTAMP WITH TIME ZONE, "sym" VARCHAR(10) NOT NULL, "cur" VARCHAR(10) NOT NULL, "open" DOUBLE NOT NULL, "high" DOUBLE NOT NULL, "low" DOUBLE NOT NULL, "close" DOUBLE NOT NULL, "volume" DOUBLE, CONSTRAINT "quotes_id_pkey" PRIMARY KEY ("id") ); SQL: WITH cte(qtime, open, close, sdate, id, rnk) as ( select qtime, sdate, id, row_number() over (partition by sdate order by qtime asc) as rnk from marketdata.quotes where sym='SPY') select * from cte where rnk=1; ~2M records loaded from csv. Always segfault crashes monetdb daemon and DB became not repairable. Reproducible on MonetDB-11.19.9 installed from yum repository, as well as compiled from sources, with-optimization and without. Also it crashed on latest development source branch MonetDB-11c0c6cf614f with optimization and without. >From mclient and JDBC client. Any help is very appreciated!! I am available to debug / provide additional information if needed. Thanks, John

2 2

RE: MonetDB, data loss in restart server
by Luciano Sasso 08 Apr '15

08 Apr '15

OK, to update, just use apt-get, I will have problems with structure of database? I can use the old dbfarm, will be compatible? On Wed, Apr 08, 2015 at 11:08:53AM -0300, Luciano Sasso wrote: >/ Anyone have any idea about ? />/ />/ />/ - OS is Debian 7 64-bit, in a VM in VMware virtualization System. />/ - Hard disk 100G with 26% usage. />/ - 5 cores. />/ - 6G RAM. />/ - Version MonetDB: MonetDB 5 server v11.15.17 "Feb2013-SP5" /Thats a very old version. I recall a fix which we did in the meantime related to possible data loss, ie we added extra f(data)sync calls. Which could very well be the problem on a virtual system. Niels >/ - Table: create table (id int, dt date, product varchar (50) .... etc, />/ and another 30 columns between int and varchar. />/ - Query: select dt, count (id) from table where produtct in (....) group />/ by dt />/ / -- Luciano Sasso Vieira Gestor - Inteligência de Negócios luciano(a)gsgroup.com.br <http://www.gsgroup.com.br> | tel: 17 3353-0833 | cel: 17 9706-9335 www.gsgroup.com.br <http://www.gsgroup.com.br> --- Este email foi escaneado pelo Avast antivírus. http://www.avast.com

2 1

Incorrect results on joining with same table
by Vijay Krishna 08 Apr '15

08 Apr '15

Hi, I am using MonetDB Jan2014 SP1, running on MacOS. I have a table like this. sql>select * from two; +------+---------------------------------------------------------------------------------------------- | id | name | istrue | joindate | jointime | count | +======+==================================================== | 1 | null | false | 2017-12-08 | 2014-06-30 14:05:31.000000 | 2 | | 2 | vijay | false | 2017-12-08 | 2014-06-30 14:05:59.000000 | 3 | | 3 | krish | true | 2017-12-08 | 2014-06-30 14:06:17.000000 | 5 | | 4 | bat | true | null | null | null | | 5 | gotham | false | null | null | null | | 6 | wayne | false | null | null | 3 | +------+------------------------------------------------------------------------------------------------- 6 tuples (3.504ms) I tried to do a join on this table with the same table with the following query and got this result. sql>select two.id, two.name, two.count, twoAlias.id as id2, twoAlias.name as name2 from two left join two twoAlias on two.count=twoAlias.id; +------+------------------------------------------- -------------------------------------------------------+ | id | name | count | id2 | name2 +=============================================================+ | 1 | null | 2 | 2 | vijay | | 2 | vijay | 3 | 3 | krish | | 3 | krish | 5 | 5 | gotham | | 4 | bat | null | 3 | krish | | 5 | gotham | null | null | null | | 6 | wayne | 3 | null | null | +------+-------------------------------- --------------------------------------------------------------------+ 6 tuples (5.605ms) As it could be seen clearly, *this join is clearly resulting in a wrong output* at the 4th row. *The join criterion two.count=twoAlias.id is satisfied in the first 3 rows, but are clearly violated in the 4th row*, where in the values in the join columns (count and id2 here) are clearly found to be different. Hence the join rows are not matching. I expected that the output would be +------+------------------------------------------- -------------------------------------------------------+ | id | name | count | id2 | name2 +=============================================================+ | 1 | null | 2 | 2 | vijay | | 2 | vijay | 3 | 3 | krish | | 3 | krish | 5 | 5 | gotham | | 4 | bat | null | null | null | | 5 | gotham | null | null | null | | 6 | wayne | 3 | null | null | +------+-------------------------------- --------------------------------------------------------------------+ 6 tuples (5.605ms) Is this really a bug?? Else, what would be the logic behind this? Thanks in advance. With Regards, Vijayakrishna.P. Mobile : (+91) 9500402305.

2 2

RE: MonetDB, data loss in restart server
by Luciano Sasso 08 Apr '15

08 Apr '15

Anyone have any idea about ? - OS is Debian 7 64-bit, in a VM in VMware virtualization System. - Hard disk 100G with 26% usage. - 5 cores. - 6G RAM. - Version MonetDB: MonetDB 5 server v11.15.17 "Feb2013-SP5" - Table: create table (id int, dt date, product varchar (50) .... etc, and another 30 columns between int and varchar. - Query: select dt, count (id) from table where produtct in (....) group by dt Message: 1 Date: Thu, 2 Apr 2015 23:16:39 +0200 From: Ying Zhang<Y.Zhang at cwi.nl <https://www.monetdb.org/mailman/listinfo/users-list>> To: "Communication channel for developers of the MonetDB suite." <developers-list at monetdb.org <https://www.monetdb.org/mailman/listinfo/users-list>> Cc:users-list at monetdb.org <https://www.monetdb.org/mailman/listinfo/users-list> Subject: Re: MonetDB, data loss in restart server Message-ID:<6C2903C0-4399-462D-9D3F-822351DE1979 at cwi.nl <https://www.monetdb.org/mailman/listinfo/users-list>> Content-Type: text/plain; charset=windows-1252 Hello Luciano, Thanks for using monetdb, and sorry for the inconvenience. Would you please give us some background information about your setting: - which OS? - hardware spec. of your system - which monetdb version? - what does your query do? - when and how did the database stop? If the monetdb server stopped, did it give any error message? - what's at the end of the merovingian log? With kind regards, Jennie -- Luciano Sasso Vieira Gestor - Inteligência de Negócios luciano(a)gsgroup.com.br <http://www.gsgroup.com.br> | tel: 17 3353-0833 | cel: 17 9706-9335 www.gsgroup.com.br <http://www.gsgroup.com.br> --- Este email foi escaneado pelo Avast antivírus. http://www.avast.com

2 1

MonetDB for DW
by Luciano Sasso 08 Apr '15

08 Apr '15

Which of you with experience MonetDB production with large volume of data? You use some NoSQL base for storage of DW, and makes loads for MonetDB, for analysis, or use the monetdDB with dw too? Att, -- Luciano Sasso Vieira Gestor - Inteligência de Negócios luciano(a)gsgroup.com.br <http://www.gsgroup.com.br> | tel: 17 3353-0833 | cel: 17 9706-9335 www.gsgroup.com.br <http://www.gsgroup.com.br> --- Este email foi escaneado pelo Avast antivírus. http://www.avast.com

1 0

Cluster, How work ?
by Luciano Sasso 08 Apr '15

08 Apr '15

On a cluster using MonetDB, the documentation is not clear how it works. Data is automatically replicated between cluster nodes? There is an automatic load balancing? There sharding? Att, -- Luciano Sasso Vieira Gestor - Inteligência de Negócios luciano(a)gsgroup.com.br <http://www.gsgroup.com.br> | tel: 17 3353-0833 | cel: 17 9706-9335 www.gsgroup.com.br <http://www.gsgroup.com.br> --- Este email foi escaneado pelo Avast antivírus. http://www.avast.com

1 0

Sorting cluster's level queries
by José Raúl Pérez Rodríguez 06 Apr '15

06 Apr '15

Hi, I am new in monetdb. I was quering a multiplex funnel database and obviously (after read the description of multiplex funnel), the return values don't show up sorted. My question is: if using multiplex funnel databases queries can't be sorted, then how are sorted the queries at cluster level, i.e queries that retrieve data from 2 or more nodes? and how to perform that kind of quieries (not using a multiplex funnel). Thanks,

1 0

About "Zero-copy integration between MonetDB and R"
by Aris Setyawan 06 Apr '15

06 Apr '15

Hi all, First, thanks for implementing multi-core, parallel execution engine, ini MonetDB. This make MonetDB, the only opensource dbms, which have this feature. I'm sure, since MonetDB use mmap file, it is already use Zero-copy memory technique when loading data into memory for further processing. But, why the "Zero-copy integration between MonetDB and R" is not implemented in MonetDB release? -Aris

2 1

Building MonetDB from RPM on ROSA Desktop R4
by Brian Hood 05 Apr '15

05 Apr '15

Hi Guys, I know this isn't a support distro at all i'm just trying to get MonetDB to work on my ROSA Desktop R4 machine which is Linux / KDE with a Twist distro. http://pastebin.com/wiH6RPzQ Its all about this at the bottom. 1. cd . && /bin/sh ./config.status rpm.mk 2. config.status: creating rpm.mk 3. cd . && /bin/sh /home/brian/Downloads/rpmbuild/BUILD/MonetDB-11.19.7/buildtools/conf/missing automake-1.13 --foreign 4. configure.ac:372: error: required file 'buildtools/conf/compile' not found 5. configure.ac:372: 'automake --add-missing' can install 'compile' 6. Makefile:521: recipe for target 'Makefile.in' failed 7. make: *** [Makefile.in] Error 1 8. error: Bad exit status from /var/tmp/rpm-tmp.25756 (%build) 9. Automake the wrong version i'm not sure it seems to be upto date. Anybody any ideas on the off chance somebody has seen this before ? Regards, Brian Hood

2 2

Re: MonetDB, data loss in restart server
by Luciano Sasso 05 Apr '15

05 Apr '15

- OS is Debian 7 64-bit, in a VM in VMware virtualization System. - Hard disk 100G with 26% usage. - 5 cores. - 6G RAM. - Version MonetDB: MonetDB 5 server v11.15.17 "Feb2013-SP5" - Table: create table (id int, dt date, product varchar (50) .... etc, and another 30 columns between int and varchar. - Query: select dt, count (id) from table where produtct in (....) group by dt Message: 1 Date: Thu, 2 Apr 2015 23:16:39 +0200 From: Ying Zhang<Y.Zhang(a)cwi.nl> To: "Communication channel for developers of the MonetDB suite." <developers-list(a)monetdb.org> Cc:users-list@monetdb.org Subject: Re: MonetDB, data loss in restart server Message-ID:<6C2903C0-4399-462D-9D3F-822351DE1979(a)cwi.nl> Content-Type: text/plain; charset=windows-1252 Hello Luciano, Thanks for using monetdb, and sorry for the inconvenience. Would you please give us some background information about your setting: - which OS? - hardware spec. of your system - which monetdb version? - what does your query do? - when and how did the database stop? If the monetdb server stopped, did it give any error message? - what's at the end of the merovingian log? With kind regards, Jennie -- Luciano Sasso Vieira Gestor - Inteligência de Negócios luciano(a)gsgroup.com.br <http://www.gsgroup.com.br> | tel: 17 3353-0833 | cel: 17 9706-9335 www.gsgroup.com.br <http://www.gsgroup.com.br> --- Este email foi escaneado pelo Avast antivírus. http://www.avast.com

1 0

Re: MonetDB, data loss in restart server
by Ying Zhang 02 Apr '15

02 Apr '15

Hello Luciano, Thanks for using monetdb, and sorry for the inconvenience. Would you please give us some background information about your setting: - which OS? - hardware spec. of your system - which monetdb version? - what does your query do? - when and how did the database stop? If the monetdb server stopped, did it give any error message? - what's at the end of the merovingian log? With kind regards, Jennie > On Apr 01, 2015, at 06:46, Luciano Sasso <luciano(a)gsgroup.com.br> wrote: > > Hello, > > I have used the MonetDB in production for some time, and I have nothing to complain about, > are tables with more than 30 columns and 30 million records, do analysis through cubes in Pentaho / Saiku. > Have restarted the server several times, and never had problems with loss of data.But today, > after performing a query that was consuming a lot of machine, stopped the database, and when I started, > data from some columns were blank. How can it, will the data were still uncommitted? > when performing a query the MonetDB changes the data or reorganized? which may have occurred, I am concerned. > > > -- > <Slice_01.png> <Slice_02.jpg> > Luciano Sasso Vieira > Gestor - Inteligência de Negócios > > luciano(a)gsgroup.com.br | tel: 17 3353-0833 | cel: 17 9706-9335 > <Slice_04.png> www.gsgroup.com.br > <Slice_06.png> > > > Este email foi escaneado pelo Avast antivírus. > www.avast.com > > > _______________________________________________ > developers-list mailing list > developers-list(a)monetdb.org > https://www.monetdb.org/mailman/listinfo/developers-list

1 0

MonetDB in Production
by Luciano Sasso 02 Apr '15

02 Apr '15

Hi, I use MonetDB production together with Pentaho BI Server and Saiku, works very well and never had problems. I am currently about 30 million entries in a table of about 40 columns, occupying 9G disk. I'll use it now on other projects that this volume will increase a lot. On what basis of size and number of records you have experience and have arrived? Use in production? I was worried because a few days ago I had a corrupt basis, that is, some columns were blank after restarte the bank, do not know if is related to the data base or was disk failure, to join if I do not have evidence of failure disk, but I can not say. Att, -- Luciano Sasso Vieira Gestor - Inteligência de Negócios luciano(a)gsgroup.com.br <http://www.gsgroup.com.br> | tel: 17 3353-0833 | cel: 17 9706-9335 www.gsgroup.com.br <http://www.gsgroup.com.br> --- Este email foi escaneado pelo Avast antivírus. http://www.avast.com

1 0

Putting field description in SQL CREATE table
by Puthick Hok 02 Apr '15

02 Apr '15

Hi, I am working on developing a database web service layer for an open source plant/animal breeding platform. So far I rely on having field comment in CREATE TABLE SQL statement like CREATE TABLE "extract" ( "ExtractId" int NOT NULL auto_increment COMMENT 'Id of the DNA extract') ; for the online help. So far, we use MySQL for general storage, postgres for GIS and environment data storage. Both MySQL and Postgres support field comment in slightly different means. We are going to switch to MonetDB as our improved marker data storage. I have tested that the previous CREATE TABLE SQL would not work in MonetDB. Will MonetDB support this feature in the future? Regards, Puthick

3 2

Multiple nodes
by Jiří Nádvorník 02 Apr '15

02 Apr '15

Hi, Does MonetDB support some kind of installation over multiple nodes (servers)? How can I control data partitioning over these nodes? Jiri Nadvornik Astronomical Institute AV CR Stellar department Czech Republic <mailto:nadvornik.ji@gmail.com> nadvornik.ji(a)gmail.com

1 0

C++ user defined function
by Jiří Nádvorník 01 Apr '15

01 Apr '15

Hi, I'm trying to write a function in C++ and I thought I would just provide a makefile for it in my custom folder in MonetDB/sql/backends/monet5/CUSTOM. But it seems like all of the Makefiles in UDF sample are generated by the bootstrap. How do I get over this? I need to use external C++ libraries in the makefile.. Thank you for help. Best regards, Jiri Nadvornik Astronomical Institute AV CR Stellar department Czech Republic <mailto:nadvornik.ji@gmail.com> nadvornik.ji(a)gmail.com

4 9

UDF insert
by Jiří Nádvorník 01 Apr '15

01 Apr '15

Hi, I'm trying to find this in already defined C functions, but with no luck.. What is the set of commands I need to use when I want to insert a row into a table? The row is just array of <dbl, dbl, int, int>. Also, how do I pass a row as a parameter to the function? Do I need to pass all of the columns separately, or can I pass the whole row as an array? Thank you for pointing me the right direction. Cheers, Jiri Nadvornik Astronomical Institute AV CR Stellar department Czech Republic <mailto:nadvornik.ji@gmail.com> nadvornik.ji(a)gmail.com

1 0

MonetDB default character set
by Puthick Hok 01 Apr '15

01 Apr '15

Hi, MonetDB is very good. I'm happy to have found this amazing open source product on the Internet. We want to use MonetDB to store high density marker (genotyping or snp) data. Is the default character set is UTF8 in MonetDB? Regards, Puthick

3 2

Mal description for a BAT function
by Jiří Nádvorník 31 Mar '15

31 Mar '15

Hi, I'm sorry for spamming so much, but I have to get more into the idea of BATs before I will be a little more self-sufficient. I need a function which takes two table columns as parameters. I would come up with this MAL declaration: command build_chunks(ra:bat[:oid,:dbl], dec:bat[:oid,:dbl], nside_low:int, nside_high:int):bat[:oid,:oid] address OSPSbuild_chunks comment "Build HEALPix groups for further processing."; It should take ra, dec parameters (from my DB table) and then some integer parameters. I guess it should return an oid, because all it does is that it should write some computed result (multiple rows) to another table. I have still no idea how I will do that, but I feel I'm getting closer J. The function produces a c++ vector of some integers, which I want to write down to another table. Is that MAL declaration looking correct? How should I call that function? I understand that SELECT reverse('hello'), but I don't know when the parameter is passed from a query. Thank you very much for pointing me in the right direction. Best regards, Jiri Nadvornik Astronomical Institute AV CR Stellar department Czech Republic <mailto:nadvornik.ji@gmail.com> nadvornik.ji(a)gmail.com

1 0

UDF function example
by Jiří Nádvorník 31 Mar '15

31 Mar '15

Hi, When I download the source code from mercurial and run: 1. bootstrap, configure, make, make install 2. monetdbd start myFarm, mclient demo 3. sql>select 'hello',reverse('hello'); The output is: SELECT: no such unary operator 'reverse(char)' What can be wrong here? I had the original installation from packages, then I apt-get removed them and installed via the make install. Any ideas appreciated. Best regards, Jiri Nadvornik Astronomical Institute AV CR Stellar department Czech Republic <mailto:nadvornik.ji@gmail.com> nadvornik.ji(a)gmail.com

3 3

Table partitioning over nodes
by Jiří Nádvorník 31 Mar '15

31 Mar '15

Hi All, I have one table with data that can be grouped easily. I need to iterate over these groups, count their members and distribute them uniformly over the database nodes (physically). If I have three groups A, B, C of size (1, 1, 2) and two physical nodes - I want A,B to be on node no. 1 and C on the node no. 2. The question is quite simple. Is it possible? Jiri Nadvornik Astronomical Institute AV CR Stellar department Czech Republic <mailto:nadvornik.ji@gmail.com> nadvornik.ji(a)gmail.com

1 0

COPY INTO FROM STDIN and auto increment column
by fadila.mumbasic＠uniserv.com 30 Mar '15

30 Mar '15

Hi, We are facing problems with COPY INTO .. FROM STDIN and an auto increment column, which is the primary key column in the table. The client is our own java client. If we don't set any value for this column (which is our expected constellation) there is neither error nor exception, but no record is inserted into the table. A try with a null value for this column produces the same result (no exception, no record in the table) The only way to insert records is to set the proper value for the column, but it is not the supposed behavior of an auto increment column, isn't it? Best regards, Fadila

2 1

SQL string from PreparedStatement ??
by Vijay Krishna 30 Mar '15

30 Mar '15

Hi, Is there any way to get the SQL query as a string from the PreparedStatement of MonetDB?? (Like in some DBs, toString will give the SQL string) Any help much appreciated. Thanks in advance. Thanks & Regards, Vijayakrishna.P. Mobile : (+91) 9500402305.

2 1

Count function with Over clause()
by Amey M 30 Mar '15

30 Mar '15

The Aggregate count function doesn't work with the over clause in MonetDB. I am a new user on MonetDB, and have trouble using queries running on other databases like Postgres on MonetDB. When using the Count function with Over clause I get the following error: sql>select count(id) over() from sample_table ;SELECT: function 'count' not found Regards, Ameya

1 0

MonetDB C functions
by Jiří Nádvorník 29 Mar '15

29 Mar '15

Hi all, I'm trying to understand the example of user defined functions in MonetDB (https://www.monetdb.org/Documentation/Cookbooks/SQLrecipes/UserDefinedFunct ion). I understand the concept for just reversing a string. But what about working with tabular data? I'd like to pass to this C function a whole table (list of rows) and then return another table(s)/write result data to other tables. What is the correct way of doing this? Can I pass some kind of arrays to the C function? Can I return these arrays from a function? Or is it possible to query the DB directly from the C script? Thank you very much for some answers. Best regards, Jiri Nadvornik Astronomical Institute AV CR Stellar department Czech Republic <mailto:nadvornik.ji@gmail.com> nadvornik.ji(a)gmail.com

2 2

Better understand those MAL functions
by Leon-Constantin Fopa 29 Mar '15

29 Mar '15

hello dear all, I'm trying to understand an execution plan generated in MAL by monetDB. I would like to how exactlly the following functions works : - sql.mvc - algebra.subselect - sql.bind - sql.subdelta - algebra.leftfetchjoin Best regards,

1 0

Arrays in MonetDB
by Jiří Nádvorník 27 Mar '15

27 Mar '15

Hi, Do I understand correctly that MonetDB does not support array types? What's the best possibility to use instead of them? I have a table of pixels and table of points. For each pixel.ID I have two different arrays of points to store in the DB. Thank you for clarification. Best regards, Jiri Nadvornik Astronomical Institute AV CR Stellar department Czech Republic <mailto:nadvornik.ji@gmail.com> nadvornik.ji(a)gmail.com

1 0

MonetDB user defined function grouping
by Jiří Nádvorník 26 Mar '15

26 Mar '15

Hi all, I am trying to do a user defined function written in C++ which takes the whole table as a parameter, loads it into a vector structure, and then divides the data in some groups (duplicities will occur). How can I return the results of this grouping from the C function? Probably I'd like to store the group IDs in a separate table and create and then create an M:N table between the group IDs and the original records. Any help appreciated. Best regards, Jiri Nadvornik Astronomical Institute AV CR Stellar department Czech Republic <mailto:nadvornik.ji@gmail.com> nadvornik.ji(a)gmail.com

1 0

HEALPix with monetDB
by Jiří Nádvorník 25 Mar '15

25 Mar '15

Hi all, I'm planning to index my spherical data in MonetDB byHEALPix. I will need it for: 1. Data partitioning - distribute the data uniformly on the DB nodes, but keep them sorted (keep points close on the sphere on the same node). 2. Neighbor lookup - can I actually index my spherical coordinates stored in MonetDB by some kind of a BTree of HEALPix pixels? I understand how computing a hash for the coordinates and indexing on it helps in relational DB, but how do the indices function in an array DB like MonetDB? I'd like to benefit from the array storage of the data, because I will often look for nearest neighbors and compute distances between these neighbors. I will also divide the data in the beginning for parallel tasks by some calculation of their pixels, but that will be done only once in the beginning, the main computation will be around clustering these objects, which means lots and lots of neighbor searches and distance computation. Has somebody already tried to use HEALPix, or some other spherical index in MonetDB? How can I control the distribution of data across the nodes (e.g. sort them by this index)? How can I use and benefit from this kind of indexing in MonetDB? Thank you very much for even some questions answered. Best regards, Jiri Nadvornik Astronomical Institute AV CR Stellar department Czech Republic nadvornik.ji(a)gmail.com

1 0

Case insensitive LIKE ??
by Vijay Krishna 23 Mar '15

23 Mar '15

Hi, By default the LIKE criteria of MonetDB returns results by matching the exact case of the search word (MonetDB Jan 2014 SP1 Mac). How to make the LIKE criteria to do a case insensitive search by default?? (Something like the BINARY keyword that does case sensitive search in MySQL??) I don't wish to add a workaround like "LOWER(column) like LOWER(searchword)" here.. Any help much appreciated. Thanks in advance. Thanks & Regards, Vijayakrishna.P. Mobile : (+91) 9500402305.

2 2

How to tell MonetDB that data are sorted in the table?
by Leon-Constantin Fopa 19 Mar '15

19 Mar '15

Hi Given a table `data` (as described below) and given the insurance that that values imported into this table are sorted. CREATE TABLE data (INT value) CREATE INDEX index_value ON data (value) Since MonetDB can neglect the `CREATE INDEX` statement (see details here : https://www.monetdb.org/Documentation/Manuals/SQLreference/Indices) How do I tell MonetDB that data are sorted in order to let him improves lookup operations ? Best regards, Fopa Léon Constantin

2 3

Datacell issue on Jan2014-SP3 build
by Tsai Li Ming 18 Mar '15

18 Mar '15

Hi, We are trying data cell on the Jan2014-SP3 build. There is a “Query datacell.pass failed:SQLException:mvc:SQL module not initialised” error. Please see below: Welcome to mclient, the MonetDB/SQL interactive terminal (Jan2014-SP3) Database: MonetDB v11.17.21 (Jan2014-SP3), 'mapi:monetdb://localhost.localdomain:50000/foo' Type \q to quit, \? for a list of available commands auto commit mode: on sql>set optimizer='datacell_pipe'; operation successful (1.465ms) sql> sql>create table datacell.bsktin( more> id integer, more> tag integer, more> payload integer more>); operation successful (325.852ms) sql>create table datacell.bsktout (like datacell.bsktin); operation successful (106.356ms) sql> sql>call datacell.receptor('datacell.bsktin','localhost',50500); sql> sql>call datacell.emitter('datacell.bsktout','localhost',50600); sql> sql>call datacell.query('datacell.pass', 'insert into datacell.bsktout select * from datacell.bsktin;'); sql> sql>select * from datacell.receptors(); select * from datacell.emitters(); select * from datacell.queries(); select * from datacell.baskets(); +-----------------+-----------+-------+----------+---------+--------+----------------------------+--------+----------+---------+ | nme | host | port | protocol | mode | status | lastseen | cycles | received | pending | +=================+===========+=======+==========+=========+========+============================+========+==========+=========+ | datacell.bsktin | localhost | 50500 | TCP | passive | paused | null | 0 | 0 | 0 | +-----------------+-----------+-------+----------+---------+--------+----------------------------+--------+----------+---------+ 1 tuple (10.546ms) +------------------+-----------+-------+----------+--------+--------+----------------------------+--------+------+---------+ | nme | host | port | protocol | mode | status | lastsent | cycles | sent | pending | +==================+===========+=======+==========+========+========+============================+========+======+=========+ | datacell.bsktout | localhost | 50600 | UDP | active | paused | null | 0 | 0 | 0 | +------------------+-----------+-------+----------+--------+--------+----------------------------+--------+------+---------+ 1 tuple (10.780ms) +---------------+--------+----------------------------+--------+--------+------+-------+-------------------------------------------------------------+ | nme | status | lastrun | cycles | events | time | error | def | +===============+========+============================+========+========+======+=======+=============================================================+ | datacell.pass | paused | null | 0 | 0 | 0 | | insert into datacell.bsktout select * from datacell.bsktin; | +---------------+--------+----------------------------+--------+--------+------+-------+-------------------------------------------------------------+ 1 tuple (15.348ms) +------------------+-----------+---------+-----------+-----------+------------+------+----------------------------+--------+ | nme | threshold | winsize | winstride | timeslice | timestride | beat | seen | events | +==================+===========+=========+===========+===========+============+======+============================+========+ | datacell.bsktin | 0 | 0 | 0 | 0 | 0 | 0 | null | 0 | | datacell.bsktout | 0 | 0 | 0 | 0 | 0 | 0 | null | 0 | +------------------+-----------+---------+-----------+-----------+------------+------+----------------------------+--------+ 2 tuples (15.704ms) sql> sql>call datacell.resume(); sql>call datacell.dump(); invalid/unknown response from server, ignoring output ? #baskets[ 1] datacell.bsktin columns 3 threshold 0 window=[0,0] time window=[0,0] beat 0 milliseconds events 0 ? #baskets[ 2] datacell.bsktout columns 3 threshold 0 window=[0,0] time window=[0,0] beat 0 milliseconds events 0 ? #receptor datacell.bsktin at localhost:50500 protocol=TCP mode=passive status=running delay=1000 ? #emitter datacell.bsktout at localhost:50600 protocol=UDP mode=active status=running delay=1000 ? #scheduler status running (remaining output omitted, use \fraw to examine in detail) We then ran nc and sensor program but get nothing on the nc output: sql>select * from datacell.receptors(); select * from datacell.emitters(); select * from datacell.queries(); select * from datacell.baskets(); +-----------------+-----------+-------+----------+---------+---------+----------------------------+--------+----------+---------+ | nme | host | port | protocol | mode | status | lastseen | cycles | received | pending | +=================+===========+=======+==========+=========+=========+============================+========+==========+=========+ | datacell.bsktin | localhost | 50500 | TCP | passive | running | 2015-03-12 09:58:36.000000 | 1 | 1000 | 1000 | +-----------------+-----------+-------+----------+---------+---------+----------------------------+--------+----------+---------+ 1 tuple (5.729ms) +------------------+-----------+-------+----------+--------+---------+----------------------------+--------+------+---------+ | nme | host | port | protocol | mode | status | lastsent | cycles | sent | pending | +==================+===========+=======+==========+========+=========+============================+========+======+=========+ | datacell.bsktout | localhost | 50600 | UDP | active | running | null | 0 | 0 | 0 | +------------------+-----------+-------+----------+--------+---------+----------------------------+--------+------+---------+ 1 tuple (6.183ms) +---------------+---------+----------------------------+--------+------+----------+------------------------------------------------+------------------------------------------------+ | nme | status | lastrun | cycles | even | time | error | def | : : : : : ts : : : : +===============+=========+============================+========+======+==========+================================================+================================================+ | datacell.pass | running | null | 105797 | 0 | 12205937 | Query datacell.pass failed:SQLException:mvc:SQ | insert into datacell.bsktout select * from dat | : : : : : : : L module not initialized : acell.bsktin; : +---------------+---------+----------------------------+--------+------+----------+------------------------------------------------+------------------------------------------------+ 1 tuple (6.299ms) +------------------+-----------+---------+-----------+-----------+------------+------+----------------------------+--------+ | nme | threshold | winsize | winstride | timeslice | timestride | beat | seen | events | +==================+===========+=========+===========+===========+============+======+============================+========+ | datacell.bsktin | 0 | 0 | 0 | 0 | 0 | 0 | 2015-03-12 10:04:22.000000 | 1000 | | datacell.bsktout | 0 | 0 | 0 | 0 | 0 | 0 | null | 0 | +------------------+-----------+---------+-----------+-----------+------------+------+----------------------------+--------+ 2 tuples (6.487ms) Can someone help? Our MonetDB build is faulty? Regards, Liming

4 5

Geometrical grid for spherical data
by Jiří Nádvorník 17 Mar '15

17 Mar '15

Hi All, I have spherical coordinates (right ascension, declination) and I'd like to iterate over areas in this 2-dimensional space. Easiest that comes to mind is just create a grid of squares (say 5 arcsecs wide) and iterate over them. Is it easier to do this in MonetDB with just spherical coordinates (some kind of spherical index), or do I have to convert them to x,y,z coordinates and create the grid in 3D? Thank you for your insight. Jiri Nadvornik Astronomical Institute AV CR Stellar department Czech Republic <mailto:nadvornik.ji@gmail.com> nadvornik.ji(a)gmail.com

3 4

Discussion on Concurrency Insert Issue "insert into A from B table and insert into C from D "
by Shivanandan Gupta 17 Mar '15

17 Mar '15

Hi All, I have written a java program when runs a two threads in parallel , copy command in parallel is working fine using batch files but the insert into using batch file is getting hanged , I have given the commands below which I have used in the batch file. Copy command Batch. mclient.bat demo -s "copy into A FROM 'D:\ETL_Source\abcd.csv' using delimiters ',';" Insert into Batch mclient.bat demo -s "insert into B select * from A;" One more problem I am facing is that while running concurrent insert into A select * from B and insert into C select * from D. I have read a lot about concurrent issue but still I am a bit confuse , my understanding is that for any insert into table monetdb do a db lock and rollback all other queries running in parallel , if this is true then in real time scenario how we can use this db for a DWH , as any time we will have multiple facts and dimensions and for facts we will be doing parallel loads, data volume will be very high. Please help me out if my understanding is wrong or if I can change some settings to accept parallel insert command. Please let me know if you need more information from my side for better understanding. -- Cheers, Shivanandan Gupta

1 0

Multi threaded (2 threads) Java program for copy command
by Shivanandan Gupta 13 Mar '15

13 Mar '15

Hi Jennie, I am getting below given exception while running in parallel. Exception in thread "main" java.sql.SQLException: COMMIT: transaction is aborted because of concurency conflicts, will ROLLBACK instead at nl.cwi.monetdb.jdbc.MonetConnection$ResponseList.executeQuery(MonetConnection.java:2535) at nl.cwi.monetdb.jdbc.MonetConnection$ResponseList.processQuery(MonetConnection.java:2284) at nl.cwi.monetdb.jdbc.MonetStatement.internalExecute(MonetStatement.java:508) at nl.cwi.monetdb.jdbc.MonetStatement.execute(MonetStatement.java:349) at connect_monetdb.runETL2(connect_monetdb.java:79) at connect_monetdb.main(connect_monetdb.java:14) Thank you, Shivanandan Gupta. On Fri, Mar 13, 2015 at 8:09 PM, Shivanandan Gupta < shivnandangupta(a)gmail.com> wrote: > Hi Jennie, > > Thanks for your reply. please find my replies below: > > Jennie Commented: Concurrent insertion into two different tables should > work. Do you have anymore information about why the COMMIT has failed? > Maybe the problem lies somewhere else than concurrent insertions? > > Shiva's Comment: yes when I run two batch file by clicking it with > difference of second ( as it takes few seconds to click the second batch > file) and it runs perfectly, even I have tried running copy command > directly on the monetsql client and on two clients in parallel it works > perfectly. Is there some configuration change required on monetdb server? > > Jennie Commented: - Have you tried the two COPY INTO separately? To know > if the problem is indeed caused by concurrent insertions. > > Shiva's Comment: When I am trying with two separate java program running > concurrently its throwing same exception. > > > Jennie Comment: - Does the problem occur one-time, or every time you > repeatedly execute your code? > Shiva's Comment: Yes the same error message is coming every time I run > concurrent java programs with copy command in it ( here copying data to > different tables ). > > Any help will be really appreciated. > > Thank you. > > Shivanandan Gupta. > > > Date: Fri, 13 Mar 2015 15:36:23 +0800 > From: Ying Zhang <Y.Zhang(a)cwi.nl> > To: Communication channel for MonetDB users <users-list(a)monetdb.org> > Cc: martin(a)monetdb.org > Subject: Re: Multi threaded (2 threads) Java program for copy command > Message-ID: <89A66109-B083-45A4-B59E-62B7AF0C9D86(a)cwi.nl> > Content-Type: text/plain; charset=us-ascii > > > > On Mar 12, 2015, at 19:27, Shivanandan Gupta <shivnandangupta(a)gmail.com> > wrote: > > > > Hi Martin, > > > > Thanks for your prompt reply , I am using two different tables here but > still I am getting "Exception in thread "main" java.sql.SQLException: > COMMIT: failed" exception. > > Hello Shivanandan, > > Concurrent insertion into two different tables should work. Do you have > anymore information about why the COMMIT has failed? Maybe the problem > lies somewhere else than concurrent insertions? > > - Have you tried the two COPY INTO separately? To know if the problem is > indeed caused by concurrent insertions. > - Does the problem occur one-time, or every time you repeatedly execute > your code? > > Regards, > > Jennie > > > > On Thu, Mar 12, 2015 at 4:57 PM, Shivanandan Gupta < > shivnandangupta(a)gmail.com> wrote: > >> Hi Martin, >>> >> >> Thanks for your prompt reply , I am using two different tables here but >> still I am getting "Exception in thread "main" java.sql.SQLException: >> COMMIT: failed" exception. >> >> >> >>> Message: 2 >>> Date: Thu, 12 Mar 2015 08:32:31 +0100 >>> From: Martin Kersten <martin(a)monetdb.org> >>> To: users-list(a)monetdb.org >>> Subject: Re: Multi threaded (2 threads) Java program for copy command >>> commit fails for one thread >>> Message-ID: <5501410F.1070508(a)monetdb.org> >>> Content-Type: text/plain; charset=windows-1252; format=flowed >>> >>> Hi, >>> >>> Yes. Insertion into the same table would be invalidated due to the >>> transaction semantics. >>> See >>> https://www.monetdb.org/Documentation/Manuals/SQLreference/Transactions >>> >>>

1 0

Re: Multi threaded (2 threads) Java program for copy command
by Shivanandan Gupta 13 Mar '15

13 Mar '15

> > Hi Martin, > Thanks for your prompt reply , I am using two different tables here but still I am getting "Exception in thread "main" java.sql.SQLException: COMMIT: failed" exception. > Message: 2 > Date: Thu, 12 Mar 2015 08:32:31 +0100 > From: Martin Kersten <martin(a)monetdb.org> > To: users-list(a)monetdb.org > Subject: Re: Multi threaded (2 threads) Java program for copy command > commit fails for one thread > Message-ID: <5501410F.1070508(a)monetdb.org> > Content-Type: text/plain; charset=windows-1252; format=flowed > > Hi, > > Yes. Insertion into the same table would be invalidated due to the > transaction semantics. > See > https://www.monetdb.org/Documentation/Manuals/SQLreference/Transactions > > On 12/03/15 08:18, Shivanandan Gupta wrote: > > Hi All, > > > > I am evaluating monetdb for our dwh and I am trying to load data from > two different csv file to 2 different tables using multi-threaded java > program in parallel, for one thread the load is successful and other one is > failing with exception given below. > > > > Exception in thread "main" java.sql.SQLException: COMMIT: failed > > at > nl.cwi.monetdb.jdbc.MonetConnection$ResponseList.executeQuery(MonetConnection.java:2535) > > at > nl.cwi.monetdb.jdbc.MonetConnection$ResponseList.processQuery(MonetConnection.java:2284) > > at nl.cwi.monetdb.jdbc.MonetConnection.commit(MonetConnection.java:374) > > at connect_monetdb.runETL(connect_monetdb.java:78) > > at connect_monetdb.main(connect_monetdb.java:14) > > > > > > command used is : > > > > String csql = "copy into s_asset_"+filename+" from '"+filepath+""+ > filename +".csv' using delimiters',';"; > > st.execute(csql); > > con.commit(); > > > > > > I am using monetdb jdbc driver and monetdb server is also on the same > machine. > > > > Any help will be really great. > > > > Thank you. > > > > Cheers, > > Shivanandan Gupta > > > > > > > > _______________________________________________ > > users-list mailing list > > users-list(a)monetdb.org > > https://www.monetdb.org/mailman/listinfo/users-list > > > > > >

2 2

Multi threaded (2 threads) Java program for copy command commit fails for one thread
by Shivanandan Gupta 12 Mar '15

12 Mar '15

Hi All, I am evaluating monetdb for our dwh and I am trying to load data from two different csv file to 2 different tables using multi-threaded java program in parallel, for one thread the load is successful and other one is failing with exception given below. Exception in thread "main" java.sql.SQLException: COMMIT: failed at nl.cwi.monetdb.jdbc.MonetConnection$ResponseList.executeQuery(MonetConnection.java:2535) at nl.cwi.monetdb.jdbc.MonetConnection$ResponseList.processQuery(MonetConnection.java:2284) at nl.cwi.monetdb.jdbc.MonetConnection.commit(MonetConnection.java:374) at connect_monetdb.runETL(connect_monetdb.java:78) at connect_monetdb.main(connect_monetdb.java:14) command used is : String csql = "copy into s_asset_"+filename+" from '"+filepath+""+ filename +".csv' using delimiters',';"; st.execute(csql); con.commit(); I am using monetdb jdbc driver and monetdb server is also on the same machine. Any help will be really great. Thank you. Cheers, Shivanandan Gupta

2 1

Re: Problem with Datacell
by Julien Marie 10 Mar '15

10 Mar '15

I managed to make it work a bit more by installing Jan-2014 version of MonetDB. I can send data into datacell, I can send data out of datacell if I insert directly into the bsktout. But the queries are not working : +----------------+---------+---------------------------+--------+------+----------+----------------------------+----------------------------+ | nme | status | lastrun | cycles | even | time | error | def | : : : : : ts : : : : +================+=========+===========================+========+======+==========+============================+============================+ | datacell.passd | running | null | 980245 | 0 | 17780876 | Query datacell.passd faile | insert into datacell.bout | : : : : : : : d:SQLException:mvc:SQL mod : select * from datacell.bbi : : : : : : : : ule not initialized : n; : +----------------+---------+---------------------------+--------+------+----------+----------------------------+----------------------------+ Is there a version or release that was working ? Julien Marie > Message: 1 > Date: Mon, 9 Mar 2015 09:55:06 +0800 > From: Julien Marie <jm(a)producture.com> > To: users-list(a)monetdb.org > Subject: Problem with Datacell > Message-ID: > < > CABmP2VKm0csQTzcUcgwiusw9o52qxmbmrZ7PM+auxoj7skdrCw(a)mail.gmail.com> > Content-Type: text/plain; charset="utf-8" > > I know the feature is deemed experimental, but I have troubles just to > start the example and I keep on having transaction error messages : > > sql>call datacell.receptor('datacell.bsktin', 'localhost', 50500); > sql>call datacell.emitter('datacell.bsktout', 'localhost', 50600); > START TRANSACTION: cannot start a transaction within a transaction > > What is going on ? > > Thank you very much > > -- > *Julien Marie* > > jm(a)producture.com > skype : thewiseup > +63 917 511 8258 > > >

1 0

Horizontal partitioning
by Bart Lenoir 09 Mar '15

09 Mar '15

Hi All, Does MonetDB besides load balancing (round robin) in its clustering also support horizontal partitioning? If so, how? Thanks. Cheers, John

1 0

Problem with Datacell
by Julien Marie 09 Mar '15

09 Mar '15

I know the feature is deemed experimental, but I have troubles just to start the example and I keep on having transaction error messages : sql>call datacell.receptor('datacell.bsktin', 'localhost', 50500); sql>call datacell.emitter('datacell.bsktout', 'localhost', 50600); START TRANSACTION: cannot start a transaction within a transaction What is going on ? Thank you very much -- *Julien Marie* jm(a)producture.com skype : thewiseup +63 917 511 8258 -----BEGIN PGP PUBLIC KEY BLOCK----- xsBNBFNmiRIBCAC77MQjKyaDaRo9bQhfGNXIDK3SldtI+A56EAY2xh8/WCrJ HYwhdwW/2aConIa5eL92WLf0W6FSmjk0rqSp1bkkDt8RW7JFiftCACZkSwHF Rg3zpUeMgFG3BBRpwCiOxSygDza61AmMAQ+8LuLZOETEFddT/DQNCBouZLYN KE9uCs+lYkrpc3YbPfx+kGUDj5rT2SYnHwt4r352OjT1U8ABQdxBigymmzgT rZh/4oyVXjMtBCRwAqwmVtzwoNoarAcgsm6qQgNwTdy5NtmKlgs2hqQtIkQK 3uL+e3zyWb1B8e6gUhpn5cooeJG1hvlJ65XUTHgHOCCz7Ue4q1rl/5kZABEB AAHNIEp1bGllbiBNYXJpZSA8am1AcHJvZHVjdHVyZS5jb20+wsByBBABCAAm BQJTZokZBgsJCAcDAgkQ7nj49Sxgl2gEFQgCCgMWAgECGwMCHgEAANulB/4u c+UeSeo8Wn8COAKSoMyWh2m5zBEdRdTlELVNcQIH3ssqu1M3TEpfBixVofp2 NPYKNF7FrQPl7z9+w0azlVJVIDF6DlGWjVlKm1nDTlVr4gW8J6mgRYivV3Tp HlBHIFmelOIcBpJAhUfc73H5a9BAsJ/a2Iekpc0TERsnarVuA3sqeTqPkwBY komURJWx0yxiYj91Be1PDRRi+DyGBKa8AeapXiCmCtpGAxksB43iwiiFnLev iZCWp3bKeBCLZy5IkONNU6mNzTcljrgHzRjs9P8OSiEIJ9T8tubHUkkA0LsR 9l7vmBk3aESNF8w1LmRTRN+gBkXsMSjzNilGj7zBzsBNBFNmiRkBCACOsST4 AuZffy8Ny7w1Qhvo1Okt9bBoh5zIJKE0tEo1L4YmDYALKnagVHNUwiAM26Bv ynK+pAvRopt62Wu+s1hQgHNuwnTMuuL0OW4Y8daOIN9pUAqnT/etskSwH24W niP445IEa09RyA1b2Mwhy8hEN/eCFHmnY36U5+qiiUAjN6Khsflzkq4I2Qy9 wwMVhNC8/J1QtmODcUekMgnGG0XcXNm22N1EU3llr5yG/oYD0dAKnIIv4rf8 3s6LU7ND/SOjmaLNtzwLuGawcCBPGfaBpB77ozJz0jJ3RaEwuY/Kqt4Zfp5T 0xLFgr+fXxSjHku0qU6PGT3iH+eHkWZXGG2pABEBAAHCwF8EGAEIABMFAlNm iRwJEO54+PUsYJdoAhsMAADWtwf9HJiym4Oi2zwWiDksmLc7699iVY1pvuic RlqwMw2bmFd+PUUFupU6CR8jOTcYPJm3NzZ9gwPM3yzjd/BReWmd4mfZJfvf EIYHzJoDRmnT5t8b9Wp6jllWtVAHbN9FTUW3uJODDKhffhYNbmAhxpotao+a XQJcSrcWE1Gl030ZQryOfkz2l/eUfYYSK38gsR7ksz22Gb1CKS4gmG8jgxQs vmDTSRagUYEXOdTaX5HKMdHrMw1ll1Z8bMb/y0N0kgDmIF0pAX2i+bk5UpnU 6mbeLCwo0OoIPYpC4GvGPBglull+BRJlwvf9Kpzlip5KHYSTnb19yyjbs1qC rpsqrnUA6g== =o6AX -----END PGP PUBLIC KEY BLOCK-----

1 0

Clustering
by Jiří Nádvorník 03 Mar '15

03 Mar '15

Hi All, I'm trying to solve a clustering problem on my astronomical data. It is simply repeated observations of stars on images with random errors, effectively creating clusters with Gaussian distributions. These clusters have from 1 to cca 1000 points and are approximately of the same size (~1 arcsec). So much for the introduction. I already tried to solve this problem with simple methods but with poor results. Our regions are very dense and the size of the clusters is often (cca 20 % of the observations) comparable to their distances. Overlaps are, of course, happening too. So I decided to use a more robust algorithm and compare the results. Due to the size of our dataset (cca 4e8 observations, cca 8e6 real objects), I am also counting on massive parallelization of the algorithm, focusing on saving real time. I have one simple question for you. What algorithm would you use for the clustering? I would like to use K-means or EM Gaussian mixtures, because the data is naturally Gaussian distributed. But then I would have to foretell the number of clusters - what algorithm would you use there? Or should I use some more complicated Bayesian algorithm which does not need the number of clusters? Thank you very much for your answers. Jiri Nadvornik Astronomical Institute AV CR Stellar department Czech Republic <mailto:nadvornik.ji@gmail.com> nadvornik.ji(a)gmail.com

2 1

Converting Bigint field (containing unixtime in millisecs) to TIMESTAMP
by Davide Magatti 02 Mar '15

02 Mar '15

Hi all, Database: MonetDB v11.19.9 (Oct2014-SP2) I am trying to convert a big string fiel in a table containing unixtimes in millisecs to timestamps but i cannot find the right way to do it. So my table descriptor: TABLE "table" ( "user" VARCHAR(512), "time" BIGINT, [...] ); i tried to do > select CAST( CAST(time/1000 AS INTERVAL SECOND) AS TIMESTAMP) from table ; >>types sec_interval(13,0) and timestamp(7,0) are not equal And did not work, also using time in millisec the thing does not change. I know i am doing something wrong but i cannot get it. Could you please advise me? :) thx! Davide

2 1

SQL scenario initialization error
by Doug Service 02 Mar '15

02 Mar '15

A monet database we have created will not start with the following error: sudo monetdb start dbprod starting database 'dbprod'... FAILED start: starting 'dbprod' failed: database 'dbprod' did not initialise the sql scenario Is it possible to recover from an error like this? Here is the relevant log file portion. 2015-03-01 16:11:36 MSG dbprod[5743]: arguments: /usr/bin/mserver5 --dbpath=/var/lib/monetdb/dbprod --set merovingian_uri=mapi:monetdb://qbdata:42000/dbprod --set mapi_open=false --set mapi_port=0 --set mapi_usock=/var/lib/monetdb/dbprod/.mapi.sock --set monet_vault_key=/var/lib/monetdb/dbprod/.vaultkey --set gdk_nr_threads=2 --set max_clients=64 --set sql_optimizer=default_pipe --set monet_daemon=yes 2015-03-01 16:11:36 ERR dbprod[5743]: !mvc_init: unable to create system tables 2015-03-01 16:11:36 MSG dbprod[5743]: # MonetDB 5 server v11.19.7 "Oct2014-SP1" 2015-03-01 16:11:36 MSG dbprod[5743]: # Serving database 'dbprod', using 2 threads 2015-03-01 16:11:36 MSG dbprod[5743]: # Compiled for x86_64-pc-linux-gnu/64bit with 64bit OIDs dynamically linked 2015-03-01 16:11:36 MSG dbprod[5743]: # Found 1.955 GiB available main-memory. 2015-03-01 16:11:36 MSG dbprod[5743]: # Copyright (c) 1993-July 2008 CWI. 2015-03-01 16:11:36 MSG dbprod[5743]: # Copyright (c) August 2008-2014 MonetDB B.V., all rights reserved 2015-03-01 16:11:36 MSG dbprod[5743]: # Visit http://www.monetdb.org/ for further information 2015-03-01 16:11:36 MSG dbprod[5743]: # Listening for UNIX domain connection requests on mapi:monetdb:///var/lib/monetdb/dbprod/.mapi.sock 2015-03-01 16:11:36 MSG dbprod[5743]: !SQLException:SQLinit:Catalogue initialization failed 2015-03-01 16:11:36 MSG dbprod[5743]: !ERROR: Could not read the version number from the file 'sql_logs/sql//log'. 2015-03-01 16:11:36 MSG merovingian[1568]: database 'dbprod' (5743) has exited with exit status 0 2015-03-01 16:11:36 MSG merovingian[1568]: database 'dbprod' has shut down 2015-03-01 16:11:36 ERR control[1568]: (local): failed to fork mserver: database 'dbprod' did not initialise the sql scenario 2015-03-01 16:12:45 MSG control[1568]: (local): served status list 2015-03-01 16:12:45 MSG control[1568]: (local): locked database 'dbprod' 2015-03-01 16:13:09 MSG control[1568]: (local): served status list 2015-03-01 16:13:09 MSG control[1568]: (local): released database 'dbprod' 2015-03-01 16:13:19 MSG control[1568]: (local): served status list 2015-03-01 16:13:19 MSG merovingian[1568]: starting database 'dbprod', up min/avg/max: 0s/2d/1w, crash average: 0.00 0.50 0.17 (15-10=5) 2015-03-01 16:13:20 MSG merovingian[1568]: sending process 5769 (database 'dbprod') the TERM signal

3 3

Import from PostgreSQL
by Jiří Nádvorník 27 Feb '15

27 Feb '15

Hi All, I'm trying to migrate my data from postgresql to monetDB. I only need one table without privileges and such, so I decided to export it to csv and create a table in monetdb. Then when I run the copy command, the following error occurs. $ mclient -u caucau -d demo -s " copy into bextract.observation from stdin" - < bextract_observation.csv value 'obsname_id' from line 1 field 1 not inserted, expecting type int failed to import table I am quite sure the number is an integer :). The DD file and data file are attached. Thank you very much for answers, Jiri Nadvornik

2 2

MonetDB installation
by Jiří Nádvorník 27 Feb '15

27 Feb '15

Hi All, I have followed the instructions as simple as they are in the docs, but when I tried to start the monetdb create <path to my dbfarm folder>, it cries that it cannot find conrol port (and I have to suply it with -p along with -h and -P parameters). I am only trying to get things running on localhost - do I need to somehow setup the mserver5 daemon? No mention of that in the tutorial. Thank you very much for help. Best regards, Jiri Nadvornik

6 9

Migration from MySQL to MonetDB
by Vijay Krishna 24 Feb '15

24 Feb '15

Hi, I have been trying to migrate my databases as a whole from MySQL to MonetDB. I tried out dumping from MySQL and restoring to MonetDB. But I received a lot of syntax errors due to the mismatch between MonetDB dump file recognition and MySQL dump file. Is this migration as a whole possible? Any ideas on how to do this?? Has anyone tried this out?? If not possible, what is the easiest way to copy a database as a whole from MySQL to MonetDB? Any help much appreciated. Thanks in advance. With Regards, Vijay.

6 6

RE: MonetDB + FITS
by Jiří Nádvorník 23 Feb '15

23 Feb '15

> -----Original Message----- > From: Dimitar Nedev [mailto:dimitar.nedev@monetdbsolutions.com] > Sent: 23. února 2015 16:28 > To: Jiří Nádvorník > Subject: Re: MonetDB + FITS > > > > On 2015-Feb-23, at 13:28 , Jiří Nádvorník <nadvornik.ji(a)gmail.com> wrote: > > > > Hi, > > > > I will be using this on Debian. > > > > Ok, the ingestion seems quite straight-forward, so to the next point. The > coordinates will be ra, dec (right ascension, declination). I have to decide > what to do with these "coordinate types". > > > > First option is to use some kind of indexing (if you support any) on these > coordinates. Q3C, PgSphere comes in mind for relational DBs. > MonetDB does indexing (very lightweight), but currently entirely managed > by the engine. In general you can just start querying and not worry about > these things, depending on your data size, or at least how it was designed. > The first time this data is accessed an index of some sort is created. Some of > this indices are persistent, so they will be preserved between server restarts. > This means that any following query over the same columns will most likely > be faster. If you really want you can also force an index creation, executing a > range query (e.g. "SELECT COUNT(*) FROM table WHERE column > value") > will create an index on that column. > MonetDB already has a module called GEOM, that supports OGS Simple > Features for GeoSpatial queries. I am not sure if it currently has the function > that need to query a spherical coordinates. The module is already > considerably extended and all the changes will be in the next major version > (expected in the next few month). This includes a lot more GIS functions and > will also include some optimizations for GeoSpatial queries. > > > > > Second option is to transform them into [x,y,z] coordinates on a cube and > use these numbers as coordinates for dimensions. > You can certainly do that, but if actual data transformation is required, it > might be very time consuming. Such queries can very likely be done without > data transformation, albeit with a more complex SQL query. Performance > might not actually suffer. > Now, MonetDB has a module for accessing array-like data called SciQL, but it > is still quite experimental, so I don't recommend using it yet. > > > > > What would you choose? The queries will be of two kinds. > > > > First one: Select me a chunk of the sky and pass it for further processing. > This will be directly connected with how I want to partition the data > (physically) on hard drives. > > > > Second one (not necessarily in the DB, C++ parallel application is maybe a > better option?) - Some more fancy stuff on the result of previous query > (chunk of the sky). > > It will be more or less some Bayesian euclidean clustering of the rows > (result will be clusters of points with coordinates close to each other). > Running this algorithm will contain searching for neighbors many and many > times over. > > That is the question whether I want to keep this in DB or should I load it to > the memory of a C++ application and search for the neighbors and clusters > there. > For both kind of queries I would use a database, not only because I work on > one, but because the work/research done on database management > systems in general have made then very efficient when it comes to accessing > data. > > For an advanced analysis you can still use a database, and if you stick with > MonetDB you can actually do the clustering right in the database. As of this > release, we have support for in-database processing using R [1]. You can use > an R function to perform Bayesian clustering directly on the data in the > system, without having to export it or pass it to another application. Aside > from performance benefits, due to reduced amounts of data shuffling, this > should also limit the amount of processes you'll need to implement yourself. > This approach has been used by researchers - going over large volume of > data, looking for events/records in the data that stood out. This is actually the biggest performance issue. I have read lots of papers where Bayesian approach was used for cca 500 000 objects. We have ATM 4e8 objects and expect to have 6e8 objects in the end. That leads me to the opinion that some kind of parallelization will be needed (the clustering algorithm will have to be running in parallel for chunks of the sky and then some algorithm above that will have to solve the overlapping regions). Can something like that be written in the R module? > > My suggestion is to extract a small dataset, prepare your data ingestion > procedures and try out the small queries (select me a chunk of the sky). > Since you would be working on small amount of data the actual data > processing should not take much time. This way you can verify and refine > your process. When it comes to the actual queries, I would recommend you > to try with simple queries and see if they can do the work, crafting more > complex ones if needed. > That must be my approach and I will have to write the whole thing before I can test the actual usefulness on big data, but that's the beauty of this job :) that everything we have done in the past months can be worthless, but surely not the things we learn from it. > I hope this answers some of your questions. > Yes it does, thank you very much for such outgoing communication. Kind regards, Jiri > Best regards, > Dimitar > > [1] https://www.monetdb.org/content/embedded-r-monetdb > > > > > Thank you for your suggestions. > > > > Jiri N. > > > >> -----Original Message----- > >> From: Dimitar Nedev [mailto:dimitar.nedev@monetdbsolutions.com] > >> Sent: 23. února 2015 12:52 > >> To: Jiří Nádvorník > >> Subject: Re: MonetDB + FITS > >> > >> Hi Jiří, > >> > >> Absolutely, if you have the data in anther database, exporting and > >> importing in MonetDB shouldn't be a problem. > >> > >> To dump PostgreSQL database to an .sql file you you can use the > "pg_dump" > >> utility. You can then load it in MonetDB from "mclient". Here's more > >> info how to load a database dump in MonetDB: > >> https://www.monetdb.org/Documentation/UserGuide/DumpRestore > >> These are of course some differences between the two databases' SQL > >> interfaces, which might require some changes to the .sql file, like > >> adapting data types, but PostgreSQL and MonetDB datatypes are similar. > >> > >> Dumping it to a CSV is also an option and it can eliminate potential > >> SQL incompatibility issues. Loading CSV files also happens through the > "mclient". > >> First you'll need to create the table for loading data into. Then > >> basically involves invoking the "COPY INTO" SQL command, specifying > >> the table to load the data into, the location of the file and the > >> delimiters. Here's more info on that: > >> > https://www.monetdb.org/Documentation/Cookbooks/SQLrecipes/Loading > >> BulkData > >> > >> The FIST support was developed in conjunction with astronomers > >> (mostly from ASTRON - the Dutch institute for radio astronomy), the > >> main idea being connivence of data ingestion from large repositories > >> of FITS files. As such, it might pay of using it if you need to load a large > volume of FITS files. > >> > >> By the way, what kind of operating system are you going to use > >> MonetDB on? We are interested what operating system our users have. > >> > >> Best regards, > >> Dimitar > >> > >>> On 2015-Feb-23, at 12:00 , Jiří Nádvorník <nadvornik.ji(a)gmail.com> > wrote: > >>> > >>> Hi Dimitar, > >>> > >>> This sounds a bit complicated. I have the data already ingested into > >>> a > >> postgresql database (in more or less 1 table). Wouldn't it be easier > >> to import it from there? Or from some other simple structure like csv? > >>> > >>> I don't really need to work with the files as such - it is just > >>> means of holding > >> the data for me. > >>> > >>> Kind regads, > >>> > >>> Jiri N. > >>> > >>>> -----Original Message----- > >>>> From: Dimitar Nedev [mailto:dimitar.nedev@monetdbsolutions.com] > >>>> Sent: 23. února 2015 11:09 > >>>> To: Jiří Nádvorník > >>>> Cc: MonetDB Users > >>>> Subject: Re: MonetDB + FITS > >>>> > >>>> Hi Jiří, > >>>> > >>>> This might be a bit long, but please bear with me, if you do it > >>>> once it's relatively simple. > >>>> To use FIST it currently takes 4 general steps: > >>>> 1) Build MonetDB with FITS support > >>>> 2) Create SQL functions for FIST data loading > >>>> 3) Attach a FIST file for loading > >>>> 4) Load the data from the file > >>>> > >>>> 1) For FITS to work, you must build MonetDB together with the 'cfitsio' > >>>> library. If you are using OS X, you can installMonetDB via > >>>> Homebrew, which you can also configure to build the binaries with FITS > support: > >>>> "brew install monetdb --with-cfitsio". If you are building MonetDB > >>>> on another UNIX/Linux systems, you need to have the cfitsio library > >>>> present on your path when configuring the build. If its presence is > >>>> detected, MonetDB will be built with FITS support. More on building > >> MonetDB you can find here: > >>>> https://www.monetdb.org/Developers/SourceCompile > >>>> > >>>> 2) In all releases, including the least (Oct2014-SP2) of MonetDB > >>>> the FITS SQL functions for data loading are not created > >>>> automatically, even if FIST support is enabled. This is, sort of, > >>>> only cosmetic, since the FITS support is there, just not > >>>> conveniently exposed at SQL. Still, if you are building MonetDB > >>>> from the latest stable sources, as Homebrew will by default, you > >>>> will must first define the > >> required SQL functions like this: > >>>> create procedure fitsattach(fname string) external name fits.attach; > >>>> create procedure fitsload(tname string) external name fits.load; > >>>> create procedure listdirpat(dirname string,pat string) external > >>>> name fits.listdirpattern; > >>>> > >>>> 3) Then you can run the 'attach' function to pre-process your file: > >>>> call fitsattach('/Users/dnedev/monetdb/r564532_cat.fits'); > >>>> > >>>> At this point you can already take a glimpse at your (meta-)data > >>>> Listing the attached files: > >>>> select * from fits_files; > >>>> Listing the catalogs in each file: > >>>> select * from fits_tables; > >>>> Listing the columns contained in the catalogs: > >>>> select * from fits_columns; > >>>> > >>>> 4) Finally load the data in the FITS, specify separately which > >>>> catalogs to be loaded, as they are listed in "fits_tables", e.g. : > >>>> call fitsload('r564532_cat_1'); > >>>> call fitsload('r564532_cat_2'); > >>>> call fitsload('r564532_cat_3'); > >>>> call fitsload('r564532_cat_4'); > >>>> > >>>> The data should name be loaded in a table with the same name as the > >>>> catalog , which you can access like this: > >>>> select * from "r564532_cat_1"; > >>>> > >>>> As of the next MonetDB release, the FITS functions will be created > >>>> automatically, eliminating the second step. This also means that if > >>>> you are building MonetDB from sources checked out from our > >>>> repository, and not using the stable release sources, the functions > >>>> will be > >> create for you. > >>>> We are also working on improving the documentation and usability of > >>>> these functions. > >>>> > >>>> I hope this helps. > >>>> > >>>> Best regards, > >>>> Dimitar > >>>> > >>>> > >>>>> On 2015-Feb-23, at 09:55 , Jiří Nádvorník <nadvornik.ji(a)gmail.com> > >> wrote: > >>>>> > >>>>> Hi All, > >>>>> > >>>>> I have a simple question – how to setup and ingest data into > >>>>> MonetDB > >>>> from FITS binary tables (FITS extensions)? Namely this will be > >>>> astronomical objects (1 row = coordinates, magnitude, …). > >>>>> > >>>>> Thank you very much for info, > >>>>> > >>>>> Jiri Nadvornik > >>>>> Astronomical Institute AV CR > >>>>> Stellar department > >>>>> Czech Republic > >>>>> nadvornik.ji(a)gmail.com > >>>>> > >>>>> > >>>>> > >>>>> _______________________________________________ > >>>>> users-list mailing list > >>>>> users-list(a)monetdb.org > >>>>> https://www.monetdb.org/mailman/listinfo/users-list > >>> > >>> > > > >

1 0

MonetDB + FITS
by Jiří Nádvorník 23 Feb '15

23 Feb '15

Hi All, I have a simple question - how to setup and ingest data into MonetDB from FITS binary tables (FITS extensions)? Namely this will be astronomical objects (1 row = coordinates, magnitude, .). Thank you very much for info, Jiri Nadvornik Astronomical Institute AV CR Stellar department Czech Republic <mailto:nadvornik.ji@gmail.com> nadvornik.ji(a)gmail.com

4 5

Fixed size table performance
by Gustavo Brian 23 Feb '15

23 Feb '15

Hi, I want to implement a calendar based big table with prices for hotel accommodation with MonetDB. Once I have all the data in the table the performance is amazing but now I'm worried about insert/delete/update performance. I've asking at stackoverflow: http://stackoverflow.com/questions/28122178/monetdb-refresh-data-in-backgro… and had a nice approach but now I wonder about having a fixed size table because I can approximate the needed records for having a year of data and update them each time I have to: enable/disable, change free units, update prices, ... Some of the columns will be used for search: "destination", "date", "category", ... others for refinement: "units", "blocked", ... and others as values for the aggregations: "price", "breakfast", ... Is a fixed size table a good approach ? Updating the columns used for refinement or calculation will affect performance ? Thanks, Gustavo Brian Developer

2 1

Table as function parameter
by Bart Lenoir 20 Feb '15

20 Feb '15

To be more precise: SQL table data 'converted' to R data.frame data inside MonetDB (functions).

1 0

Table as function parameter
by Bart Lenoir 20 Feb '15

20 Feb '15

Hi Martin, Thank you for your quick response. I was hoping to find a way to use 'data.frame' in MonetDB's SQL with embedded R functions. Column oriented RDBMS with integrated/embedded R are serious contenders in the (Big) Data Science arena. Kind regards, Bart

1 0

Table as function parameter
by Bart Lenoir 19 Feb '15

19 Feb '15

Do MonetDB functions accept tables as parameters?

2 1

Compilation Error MonetDB-11.19.9
by Bart Lenoir 15 Feb '15

15 Feb '15

Hi Stefan, I filed a bug report: 3672 Thanks. Cheers, Bart

1 0

Compilation Error MonetDB-11.19.9
by Bart Lenoir 15 Feb '15

15 Feb '15

Hi Stefan, Thank you for your quick response. The config output has 600+ lines and the config.log is over 11k lines. Can I send you these as attachment by email? Cheers, Bart

2 1

Compilation Error MonetDB-11.19.9
by Bart Lenoir 15 Feb '15

15 Feb '15

Compilation of MonetDB-11.19.9 generates error in 'make' on Oracle Linux (2.6.18-371.11.1.0.1.el5 - x86_64). /usr/bin/ld: .libs/libbat_la-gdk_utils.o: relocation R_X86_64_PC32 against `MT_global_exit' can not be used when making a shared object; recompile with -fPIC

2 1

Bulk loading with NULL values in a column
by Scott Mathieson 13 Feb '15

13 Feb '15

Hi, Is it possible to perform a binary load of column data that contains NULLs - the documentation seems to indicate that the data is just a binary file of a C array of the appropriate type, but that would imply you can't have NULL values in any of the columns, is that correct? Regards, Scott ________________________________

2 1

Bug : msqldump, drop, create
by Pierre-Adrien Coustillas 07 Feb '15

07 Feb '15

Hello, I finally get to reproduce a table conflict bug still present in the 2014 version of OCT SP1 I run these commands simultaneously in two terminals : ---------------------------------------------------------------------------- terminal 1 : $ msqldump --database=lemonde --table=test_integrite_tra > backup_test_integrite_tra.sql terminal 2 : $ mclient lemonde --statement="SELECT count(*) FROM test_integrite_tra" +----------+ | L1 | +==========+ | 19681703 | +----------+ $ mclient lemonde --statement="DROP TABLE test_integrite_tra" operation successful $ mclient lemonde --statement="CREATE TABLE test_integrite_tra AS SELECT id_unique,device,nbvisistes FROM sys.visiteurs_device_2 WITH NO DATA" operation successful ---------------------------------------------------------------------------- I count the number of table test_integrite_tra sql>SELECT count(*) FROM SYS.TABLES WHERE name='test_integrite_tra'; +------+ | L1 | +======+ | 1 | +------+ And I restart monetdb : # service monetdb5-sql stop ; sleep 30 ; service monetdb5-sql start Stopping MonetDB SQL server: monetdbd. Starting MonetDB SQL server: monetdbd. I count the number of table test_integrite_tra sql>SELECT count(*) FROM SYS.TABLES WHERE name='test_integrite_tra'; +------+ | L1 | +======+ | 2 | +------+ 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

2 1

strange bug different count(*) two terminals
by Pierre-Adrien Coustillas 07 Feb '15

07 Feb '15

Hi, I have the same request that I run on the same server, the same database in two different mclient . The results are not the same. There is no activity on the server , no other query running. pcoustillas@groupelemonde:~$ mclient lemonde Welcome to mclient, the MonetDB/SQL interactive terminal (Oct2014-SP1) Database: MonetDB v11.19.7 (Oct2014-SP1), 'mapi:monetdb://groupelemonde:50000/lemonde' Type \q to quit, \? for a list of available commands auto commit mode: on sql>TRACE SELECT count(*) FROM ref__cookies__lemonde__os; +------+ | L1 | +======+ | 97 | +------+ 1 tuple (14.885ms) +-------+---------------------------------------------------------------------------------------------------------------------+ | ticks | stmt | +=======+=====================================================================================================================+ | 2 | X_2 := sql.mvc(); | | 13 | X_3:bat[:oid,:oid] =<tmp_32275>[97] := sql.tid(X_2=0,"sys","ref__cookies__lemonde__os"); | | 32 | X_6:bat[:oid,:int] =<tmp_5355>[97] := sql.bind(X_2=0,"sys","ref__cookies__lemonde__os","id",0); | | 9 | X_9=<tmp_26020>[97] := algebra.leftfetchjoin(X_3=<tmp_32275>:bat[:oid,:oid][97],X_6=<tmp_5355>:bat[:oid,:int][97]); | | 1 | X_10 := aggr.count(X_9=<tmp_26020>[97]); | | 503 | barrier X_31 := language.dataflow(); | | 4 | sql.exportValue(1,"sys.L1","L1","wrd",64,0,6,X_10=97:wrd,""); | | 0 | end s2_4; | | 555 | function user.s2_4(); | | 627 | X_5:void := user.s2_4(); | +-------+---------------------------------------------------------------------------------------------------------------------+ 10 tuples (14.976ms) pcoustillas@groupelemonde:~$ mclient lemonde Welcome to mclient, the MonetDB/SQL interactive terminal (Oct2014-SP1) Database: MonetDB v11.19.7 (Oct2014-SP1), 'mapi:monetdb://groupelemonde:50000/lemonde' Type \q to quit, \? for a list of available commands auto commit mode: on sql>TRACE SELECT count(*) FROM ref__cookies__lemonde__os; +------+ | L1 | +======+ | 0 | +------+ 1 tuple (6.841ms) +-------+------------------------------------------------------------------------------------------------------------------+ | ticks | stmt | +=======+==================================================================================================================+ | 2 | X_2 := sql.mvc(); | | 13 | X_3:bat[:oid,:oid] =<tmp_47161>[0] := sql.tid(X_2=0,"sys","ref__cookies__lemonde__os"); | | 9 | X_6:bat[:oid,:int] =<tmp_4226>[0] := sql.bind(X_2=0,"sys","ref__cookies__lemonde__os","id",0); | | 9 | X_9=<tmp_17466>[0] := algebra.leftfetchjoin(X_3=<tmp_47161>:bat[:oid,:oid][0],X_6=<tmp_4226>:bat[:oid,:int][0]); | | 1 | X_10 := aggr.count(X_9=<tmp_17466>[0]); | | 531 | barrier X_29 := language.dataflow(); | | 21 | sql.exportValue(1,"sys.L1","L1","wrd",64,0,6,X_10=0:wrd,""); | | 0 | end s2_1; | | 727 | function user.s2_1(); | | 868 | X_5:void := user.s2_1(); | +-------+------------------------------------------------------------------------------------------------------------------+ 10 tuples (6.951ms) any ideas ? thanks 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

2 1

Database crash recovery??
by Vijay Krishna 06 Feb '15

06 Feb '15

Hi, While I was running a query today, I got this error today - *Allocation failedGDKmallocmax: failed for 18446744072964793488 bytes* I ignored it thinking it was some runtime error and repeatedly tried to run the same query 3 or 4 times. Finally, mclient got crashed. I tried to reconnect to mclient, but it was not successful. So I tried restarting the database, only to find that the database has already been shut down. When I tried starting the database, I am receiving this error - *starting database 'testDB'... FAILEDstart: starting 'testDB' failed: database 'testDB' appears to shut itself down after starting, check monetdbd's logfile for possible hints* I tried after restarting the demon multiple times, but no use. I checked merovingian.log file in the db farm folder, but it showed only this - *2015-02-04 05:59:50 ERR control[7829]: (local): failed to fork mserver: database 'testDB' appears to shut itself down after starting, check monetdbd's logfile for possible hints* I tried to check the monetdbd's log file, but I am unable to find the log folder in var. Has the database crashed irrecoverably? How to run MonetDB again with at least a new database?? Any help much appreciated. P.S.: I run MonetDB Oct2014-SP1 version in a CentOS machine with 128 GB RAM. Thanks & Regards, Vijay.

3 2

Disk I/O
by Tim Burress 02 Feb '15

02 Feb '15

Hello, I have a large database that is commonly used for relatively simple SELECT queries. Some of these can take quite a bit of time (hours... overnight) so I've been looking into it a bit. What seems clear is that mserver5 (Oct2014-SP1) is disk-bound, but one curious thing is that most of the time it is write-bound. Despite doing only SELECT queries (no updates or inserts), the amount of data being written is nearly two times as much as data read. Why is that? Is the server restructuring the database on the fly in order to get better performance in later queries? 3X the disk I/O seems excessive so I wonder if there is some way to tune this, or investigate further to see what is happening? Thanks! Tim

6 14

Upserts in MonetDB
by shamsul hassan 29 Jan '15

29 Jan '15

Hi All, I have been looking into UPSERTING records in MonetDB but didn't find anything. Only few years before somebody had asked the same question - https://www.monetdb.org/pipermail/users-list/2011-May/004916.html but no solution for that. Since its been few years from then , so I was wondering if MonetDB does provide the ability to do UPSERTS. Any pointers here will be really helpful. Thanks

2 1

Announcement: New Oct2014-SP2 Bugfix release of MonetDB suite
by Sjoerd Mullender 29 Jan '15

29 Jan '15

The MonetDB team at CWI/MonetDB BV is pleased to announce the Oct2014-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/>. Oct 2014-SP2 bugfix release Client Package * Changes to the Perl interface, thanks to Stefan O'Rear: 1. removes "use sigtrap", because this has global effects and should not be used by modules, only by the application. 2. allows Perl 5.8.1+ Unicode strings to be passed to quote() and included in statements (UTF-8 encoded, as expected by Monet's str module) 3. quote and unquote now use the same quoting rules as the MonetDB server, allowing for all characters except NUL to be round-tripped 4. several character loops have been reimplemented in regex for much greater performance 5. micro-optimizations to the result fetch loop 6. block boundaries are preserved in piggyback data so that Xclose is not appended or prepended to a SQL command 7. diagnostic messages #foo before a result header are ignored, this is necessary to use recycler_pipe 8. fail quickly and loudly if we receive a continuation prompt (or any other response that starts with a non-ASCII character) 9. header lines must start with %, not merely contain %, fixing a bug when querying a table where string values contain % 10. after closing a large resultset, account for the fact that a reply will come and do not lose sync 11. allow a MAPI_TRACE environment variable to dump wire protocol frames to standard output 12. fixes maximum MAPI block size to match the server limit of 16k. previously would crash on blocks larger than 16k SQL * Fixed a typo in a column name of the sys.tablestoragemodel view (auxillary changed to auxiliary). Bug Fixes * 3467: Field aliases with '#' character excise field names in result set. * 3605: relational query without result * 3619: Missing dll on MonetDB Start * 3622: Type resolution error * 3624: insert of incomplete or invalid ip address values in inet column is silently accepted but the values are not stored (they become/show nil) * 3626: casting a type without alias results in program contains errors * 3628: mclient and ODBC driver report 'type mismatch' when stddev_pop used in a select which returns 0 rows * 3629: IF THEN ELSEIF always evaluates the first test as true * 3630: segv on rel_order_by_column_exp * 3632: running make clean twice gives an error in clients/ruby/adapter * 3633: Wrong result for HAVING with floating-point constant * 3640: Missing implementation of scalar function: sql_sub(<date>, <month interval>) * 3641: SQL lexer fails to detect string end if it the last character is U+FEFF ZERO WIDTH NO-BREAK SPACE * 3642: Combined WHERE conditions less-than plus equals-to produce incorrect results * 3643: Missing implementations of scalar function: sql_sub(<timetz>, arg2) * 3644: COPY INTO fails to import "inet" data type when value has prefix length in CIDR notation * 3646: ORDER BY clause does not produce proper results on 'inet' datatype * 3649: recycler crashes with concurrent transactions

2 1

Multiple insert into error
by Art Apl 27 Jan '15

27 Jan '15

Hi! I am trying to convert Lucene index into MonetDB with my app ( https://github.com/hjortron/LuceneToMonetDBConverter) App iterates through all Lucene docs and creates "insert into" query for each. First time problem appears on 80k row(and then dramatically falls down to 250 row), app throws error: INSERT INTO sys."EventTracking"(datetime,Hash,UserIP,EventType,SearchResultState,ViewType,PageNumber,ResultsCount,ElapsedTime) VALUES('2014-12-10 13:22:41','hod','127.0.0.1','Search','>No',0,0,2368195,85) Unhandled Exception: System.Data.Odbc.OdbcException: ERROR [HY000] [MonetDB][ODBC Driver 11.19.7]General error In merovingian.log I have only: "2015-01-26 00:43:03 ERR discovery[846]: cannot retrieve name info: Temporary failure in name resolution", that repeats each 30 minutes. On another instance of MonetDB my app works fine. Please help.

2 2

Database sharding across cluster in MonetDB
by Alastair McKinley 27 Jan '15

27 Jan '15

Hi all, I am wondering if there are any plans to develop horizontal sharding support for MonetDB? There aren't really other open source databases out there that support horizontal sharding with anywhere close to the performance of MonetDB. I realise there are a lot of challenges and scalability issues with a lot of typical DB operators, but for certain use cases that I have seen it could work really well, likely significantly faster than HBase/SparkSQL and it's rivals. Does anyone else think this would be a desirable feature? Best regards, Alastair

1 0

Best background refresh strategy
by Gustavo Brian 27 Jan '15

27 Jan '15

Hi, No changes done to the basic setup. I'm running OLAP queries and not all 54 columns are used in any query, it depends on the query which columns are used. The query is kind of SELECT SUM(),AVG(), SUM() + WHERE + GROUP BY The WHERE columns have a great compression so it's faster to find by them. My last test runs in 340 ms and aggregates between 900 and 2000 rows per result. Care: "select * from table where key = 123456" (not an expert but this is pure OLTP) Same for me, "select * from bigcalendar limit 2" -> Sometimes I have to kill it, :) but it's something that I don't need at all. I'm using MonetDB as a kind of cache of my OLTP system. IMO MonetDB is perfect for OLAP (not only analytical, operational as well! Now I'm working on the refresh approach) if you are looking for a fast search engine by key, maybe a pure key/value-store is more appropriate. Hope this helps. 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: martes, 27 de enero de 2015 12:00 To: users-list(a)monetdb.org Subject: users-list Digest, Vol 29, Issue 20 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: Best background refresh strategy (shamsul hassan) 2. Join index structure (Joseph D'silva) ---------------------------------------------------------------------- Message: 1 Date: Mon, 26 Jan 2015 11:44:49 +0000 From: shamsul hassan <shamsulbuddy(a)gmail.com> To: Communication channel for MonetDB users <users-list(a)monetdb.org> Subject: Re: Best background refresh strategy Message-ID: <CAONo-dtZpv4LfhKrM+cCh7tZcsDM4daTU80rLigcQR-ruYEbag(a)mail.gmail.com> Content-Type: text/plain; charset="utf-8" *338 million rows / 54 columns table and ran queries in ms* -- WOW .. Do you mind sharing your server configuartion and the setting for mserver5. Also what kind of queries are you firing .. ?? I find it bit slow on line level queries , something like select * from table where key = 123456 Thanks On Sun, Jan 25, 2015 at 6:56 AM, Martin Kersten <martin(a)monetdb.org> wrote: > On 25/01/15 06:48, Gustavo Brian wrote: > >> Hi, >> > Hi, > > thanks for the interest and appraisal. > >> >> I'm trying monetdb as a bigtable for calendar based queries. >> I've created a 338 million rows / 54 columns table and ran queries in ms. >> That's perfect! >> I wonder to see if monetdb is suitable for a search service with high >> traffic (several hundred request per second) and which will the best >> data update strategy because during the "updates/insert/copy into" >> actions everything gets blocked. >> > Queries should not be a problem, but for updates you should read and > understand > https://www.monetdb.org/blog/monetdb-sql-transaction-management-scheme > https://www.monetdb.org/Documentation/Manuals/SQLreference/Transaction > s > > I would like to know how cluster mode will help as well. >> > Depends on the complexity of the query and your machine specs. > regards, Martin > > >> Thanks, >> Gustavo Brian >> _______________________________________________ >> 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

Join index structure
by Joseph D'silva 26 Jan '15

26 Jan '15

Hello, Does anyone know the Join index structure used in the database ? (implementation) and how it is being utilized by the optimizer ? I couldn't find any documentation / papers out there other than a single sentence mentioned at the end of this page, which is not much. https://www.monetdb.org/Documentation/Manuals/MonetDB/Optimizers/Landscape "*Foreign Key replacements Goal: to improve multi-attribute joins over foreign key constraints Rationale: the code produced by the SQL frontend involves foreign key constraints, which provides many opportunities for speedy code using a join index. Impact: large Status: Implemented in the SQL strategic optimizer.*" Thanks Joseph

1 0

Best background refresh strategy
by Gustavo Brian 26 Jan '15

26 Jan '15

Hi, I'm trying monetdb as a bigtable for calendar based queries. I've created a 338 million rows / 54 columns table and ran queries in ms. That's perfect! I wonder to see if monetdb is suitable for a search service with high traffic (several hundred request per second) and which will the best data update strategy because during the "updates/insert/copy into" actions everything gets blocked. I would like to know how cluster mode will help as well. Thanks, Gustavo Brian

3 2

Not utilising Mutithreads
by shamsul hassan 22 Jan '15

22 Jan '15

Hi All, I have recently install monetdb "OCT 2014-SP1" release on my Oracle Enterprise 6 Linux server. Server configuration - 8 CPU Cores 64 GB RAM I have loaded a data set with 70 columns having 15 Millions Rows. I have started mserver5 as below -- mserver5 --debug=10 --set *gdk_nr_threads=8* --dbpath=/data/monetdb/testdb --set mapi_open=true --set mapi_port=54321 --set merovingian_uri=mapi:monetdb://localhost:54321/testdb --set mapi_usock=/data/monetdb/testdb/.mapi.sock --set monet_vault_key=/data/monetdb/testdb/.vaultkey --set sql_optimizer=default_pipe --set max_clients=64 Now I have ran a query as below -- select product_type,COUNT(DISTINCT sales_document),sum(ord_qty) FROM bookings where ord_entry_date between '2014-04-01' and '2014-10-01' group by product_type; This query took around 90 Secs .. So I started tomograph and checked plotted graph ... which shows the parallelism usage as 39.9 % only and its utilizing only 1 thread with maximum time went in the step - group.subgroupdone almost 98%. My Question is that even I have started the server with gdk_nr_threads=8 , then why it is just using single thread. More info -- mserver5 --version MonetDB 5 server v11.19.7 "Oct2014-SP1" (64-bit, 64-bit oids) Copyright (c) 1993-July 2008 CWI Copyright (c) August 2008-2014 MonetDB B.V., all rights reserved Visit http://www.monetdb.org/ for further information Found 63.0GiB available memory, 8 available cpu cores Libraries: libpcre: 7.8 2008-09-05 (compiled with 7.8) openssl: OpenSSL 1.0.1e 11 Feb 2013 (compiled with OpenSSL 1.0.1e-fips 11 Feb 2013) libxml2: 2.7.6 (compiled with 2.7.6) Compiled by: root@localhost (x86_64-unknown-linux-gnu) Compilation: gcc -O3 -fomit-frame-pointer -pipe -D_FORTIFY_SOURCE=2 Linking : /usr/bin/ld -m elf_x86_64 Any pointers ?? Thanks

4 7

MonetDB Sharding
by gms_xy＠gmx.de 21 Jan '15

21 Jan '15

As mentioned in previous posts, I have some use cases for MonetDB, which act on tables with a billion rows. MonetDB works fast and stable on straight SELECT statements or aggregates on single tables Problems occured when doing big joins, which should produce a result set of 30 rows only, but running hours before failing. SELECT tbl1.*, tbl2.* FROM tbl1, tbl2 WHERE tbl1.xid = tbl2.xid AND tbl1.pk = 12345678 (same behavior, if written with JOIN explicitly) So I am wondering, if I can help MonetDB with partitioning or sharding. Asked already here: https://www.monetdb.org/pipermail/developers-list/2014-October/004325.html What is the state regarding manual partitioning? During last weeks I saw significant acceleration with sharding on other DBMS. Is there anybody, who can give some introduction or share experiences with sharding on MonetDB? 3rd party tools? What ist the maximum number of tables within a MonetDB database? What is the estimated overhead of a single table, multiplied a million times? Thank you Robert

3 2

GDK
by Brian Hood 20 Jan '15

20 Jan '15

Hi Guys, I have seen it around so much in Monetdb what does the acronym gdk actually stand for ? Regards, Brian Hood

2 1

Re: users-list Digest, Vol 29, Issue 14
by bhuvan Mitra 20 Jan '15

20 Jan '15

Thank you stefan. Is there any way through which I can get to know about those implementations or buying it from actian is the only way? Actually I want to contribute to c-store, open source column oriented database in this compression/decompression area. This answer from you will be very worthy to me. Thanks in advance. On Mon, Jan 19, 2015 at 9:29 PM, <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: users-list Digest, Vol 29, Issue 12 (Stefan Manegold) > 2. Next release? (g4(a)novadsp.com) > 3. Re: Not utilising Mutithreads (shamsul hassan) > 4. Re: Next release? (Sjoerd Mullender) > > > ---------------------------------------------------------------------- > > Message: 1 > Date: Mon, 19 Jan 2015 14:58:13 +0100 (CET) > From: Stefan Manegold <Stefan.Manegold(a)cwi.nl> > To: Communication channel for MonetDB users <users-list(a)monetdb.org> > Subject: Re: users-list Digest, Vol 29, Issue 12 > Message-ID: <895297092.253122.1421675893625.JavaMail.zimbra(a)cwi.nl> > Content-Type: text/plain; charset=utf-8 > > > Hi, > > MonetDB and Actian/VectorWise are two different systems that do not share > any code. > > MonetDB does not use any compression in the way Actian/VectorWise does. > > Best, > Stefan > > > ----- Original Message ----- > > Thank you Martin. > > So, did monetdb bought the codes from actian code base and included in > it's > > source code? > > Also kindly tell me if monetdb is following any other compression > techniques > > apart from pfor, pfor-delta and pdict. > > Thanks in advance. > > > > On Mon, Jan 19, 2015 at 4:30 PM, < 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: Not utilising Mutithreads (Martin Kersten) > > 2. regarding source code (bhuvan Mitra) > > 3. Re: regarding source code (Martin Kersten) > > 4. information about compression/decompression techniques of > > monetdb (bhuvan Mitra) > > > > > > ---------------------------------------------------------------------- > > > > Message: 1 > > Date: Sun, 18 Jan 2015 12:20:15 +0100 > > From: Martin Kersten < martin(a)monetdb.org > > > To: users-list(a)monetdb.org > > Subject: Re: Not utilising Mutithreads > > Message-ID: < 54BB96EF.3030906(a)monetdb.org > > > Content-Type: text/plain; charset=windows-1252; format=flowed > > > > Hi > > > > Each MAL instruction is executed by a single thread. > > Some operators require the complete input to be available, > > such as in your case the group.subgroup(). > > > > martin > > > > On 18/01/15 01:27, shamsul hassan wrote: > > > Hi All, > > > > > > I have recently install monetdb "OCT 2014-SP1" release on my Oracle > > > Enterprise 6 Linux server. > > > > > > Server configuration - > > > > > > 8 CPU Cores > > > 64 GB RAM > > > > > > I have loaded a data set with 70 columns having 15 Millions Rows. > > > > > > I have started mserver5 as below -- > > > > > > mserver5 --debug=10 --set *gdk_nr_threads=8* > --dbpath=/data/monetdb/testdb > > > --set mapi_open=true --set mapi_port=54321 --set > > > merovingian_uri=mapi:monetdb://localhost:54321/testdb --set > > > mapi_usock=/data/monetdb/testdb/.mapi.sock --set > > > monet_vault_key=/data/monetdb/testdb/.vaultkey --set > > > sql_optimizer=default_pipe --set max_clients=64 > > > > > > Now I have ran a query as below -- > > > > > > select product_type,COUNT(DISTINCT sales_document),sum(ord_qty) FROM > > > bookings where ord_entry_date between '2014-04-01' and '2014-10-01' > group > > > by product_type; > > > > > > This query took around 90 Secs .. > > > So I started tomograph and checked plotted graph ... which shows the > > > parallelism usage as 39.9 % only and its utilizing only 1 thread with > > > maximum time went in the step - group.subgroupdone almost 98%. > > > > > > My Question is that even I have started the server with > gdk_nr_threads=8 , > > > then why it is just using single thread. > > > > > > More info -- > > > > > > mserver5 --version > > > > > > MonetDB 5 server v11.19.7 "Oct2014-SP1" (64-bit, 64-bit oids) > > > > > > Copyright (c) 1993-July 2008 CWI > > > > > > Copyright (c) August 2008-2014 MonetDB B.V., all rights reserved > > > > > > Visit http://www.monetdb.org/ for further information > > > > > > Found 63.0GiB available memory, 8 available cpu cores > > > > > > Libraries: > > > > > > libpcre: 7.8 2008-09-05 (compiled with 7.8) > > > > > > openssl: OpenSSL 1.0.1e 11 Feb 2013 (compiled with OpenSSL 1.0.1e-fips > 11 > > > Feb 2013) > > > > > > libxml2: 2.7.6 (compiled with 2.7.6) > > > > > > Compiled by: root@localhost (x86_64-unknown-linux-gnu) > > > > > > Compilation: gcc -O3 -fomit-frame-pointer -pipe -D_FORTIFY_SOURCE=2 > > > > > > Linking : /usr/bin/ld -m elf_x86_64 > > > > > > Any pointers ?? > > > > > > Thanks > > > > > > > > > > > > _______________________________________________ > > > users-list mailing list > > > users-list(a)monetdb.org > > > https://www.monetdb.org/mailman/listinfo/users-list > > > > > > > > > > > ------------------------------ > > > > Message: 2 > > Date: Mon, 19 Jan 2015 12:28:05 +0530 > > From: bhuvan Mitra < bhuvanrjb(a)gmail.com > > > To: users-list(a)monetdb.org > > Subject: regarding source code > > Message-ID: > > < CAMmryP6eg5AAMY1x70KJZMdc3k7P8aqjnHuS5xN-fNSJ8mN_sQ(a)mail.gmail.com > > > Content-Type: text/plain; charset="utf-8" > > > > Hello, > > > > I am very interested to know and learn about implementations of > > compression/decompression techniques that monetdb is following. I have > seen > > the monetdb's light weight compression algorithms(pfor, pfor-delta, > pdict), > > cache involved decompression techniques and excited to know how actually > > are they implemented in form of codes. Kindly tell me in which > folder/file > > is the source code of this compression/decompression algorithms present. > I > > have downloaded the source tarball from the website and could not find > > these code snippets. Plese help me to identify the path. > > > > Regards, > > mg > >

2 1

Next release?
by g4＠novadsp.com 19 Jan '15

19 Jan '15

Hello - is there any timeframe for a next release? There are a couple of post Oct2014-SP1 bug-fixes which would be really good to have included. Many thanks.

2 1

Re: users-list Digest, Vol 29, Issue 12
by bhuvan Mitra 19 Jan '15

19 Jan '15

Thank you Martin. So, did monetdb bought the codes from actian code base and included in it's source code? Also kindly tell me if monetdb is following any other compression techniques apart from pfor, pfor-delta and pdict. Thanks in advance. On Mon, Jan 19, 2015 at 4:30 PM, <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: Not utilising Mutithreads (Martin Kersten) > 2. regarding source code (bhuvan Mitra) > 3. Re: regarding source code (Martin Kersten) > 4. information about compression/decompression techniques of > monetdb (bhuvan Mitra) > > > ---------------------------------------------------------------------- > > Message: 1 > Date: Sun, 18 Jan 2015 12:20:15 +0100 > From: Martin Kersten <martin(a)monetdb.org> > To: users-list(a)monetdb.org > Subject: Re: Not utilising Mutithreads > Message-ID: <54BB96EF.3030906(a)monetdb.org> > Content-Type: text/plain; charset=windows-1252; format=flowed > > Hi > > Each MAL instruction is executed by a single thread. > Some operators require the complete input to be available, > such as in your case the group.subgroup(). > > martin > > On 18/01/15 01:27, shamsul hassan wrote: > > Hi All, > > > > I have recently install monetdb "OCT 2014-SP1" release on my Oracle > Enterprise 6 Linux server. > > > > Server configuration - > > > > 8 CPU Cores > > 64 GB RAM > > > > I have loaded a data set with 70 columns having 15 Millions Rows. > > > > I have started mserver5 as below -- > > > > mserver5 --debug=10 --set *gdk_nr_threads=8* > --dbpath=/data/monetdb/testdb --set mapi_open=true --set mapi_port=54321 > --set merovingian_uri=mapi:monetdb://localhost:54321/testdb --set > mapi_usock=/data/monetdb/testdb/.mapi.sock --set > > monet_vault_key=/data/monetdb/testdb/.vaultkey --set > sql_optimizer=default_pipe --set max_clients=64 > > > > Now I have ran a query as below -- > > > > select product_type,COUNT(DISTINCT sales_document),sum(ord_qty) FROM > bookings where ord_entry_date between '2014-04-01' and '2014-10-01' group > by product_type; > > > > This query took around 90 Secs .. > > So I started tomograph and checked plotted graph ... which shows the > parallelism usage as 39.9 % only and its utilizing only 1 thread with > maximum time went in the step - group.subgroupdone almost 98%. > > > > My Question is that even I have started the server with gdk_nr_threads=8 > , then why it is just using single thread. > > > > More info -- > > > > mserver5 --version > > > > MonetDB 5 server v11.19.7 "Oct2014-SP1" (64-bit, 64-bit oids) > > > > Copyright (c) 1993-July 2008 CWI > > > > Copyright (c) August 2008-2014 MonetDB B.V., all rights reserved > > > > Visit http://www.monetdb.org/ for further information > > > > Found 63.0GiB available memory, 8 available cpu cores > > > > Libraries: > > > > libpcre: 7.8 2008-09-05 (compiled with 7.8) > > > > openssl: OpenSSL 1.0.1e 11 Feb 2013 (compiled with OpenSSL > 1.0.1e-fips 11 Feb 2013) > > > > libxml2: 2.7.6 (compiled with 2.7.6) > > > > Compiled by: root@localhost (x86_64-unknown-linux-gnu) > > > > Compilation: gcc -O3 -fomit-frame-pointer -pipe -D_FORTIFY_SOURCE=2 > > > > Linking : /usr/bin/ld -m elf_x86_64 > > > > Any pointers ?? > > > > Thanks > > > > > > > > _______________________________________________ > > users-list mailing list > > users-list(a)monetdb.org > > https://www.monetdb.org/mailman/listinfo/users-list > > > > > > ------------------------------ > > Message: 2 > Date: Mon, 19 Jan 2015 12:28:05 +0530 > From: bhuvan Mitra <bhuvanrjb(a)gmail.com> > To: users-list(a)monetdb.org > Subject: regarding source code > Message-ID: > < > CAMmryP6eg5AAMY1x70KJZMdc3k7P8aqjnHuS5xN-fNSJ8mN_sQ(a)mail.gmail.com> > Content-Type: text/plain; charset="utf-8" > > Hello, > > I am very interested to know and learn about implementations of > compression/decompression techniques that monetdb is following. I have seen > the monetdb's light weight compression algorithms(pfor, pfor-delta, pdict), > cache involved decompression techniques and excited to know how actually > are they implemented in form of codes. Kindly tell me in which folder/file > is the source code of this compression/decompression algorithms present. I > have downloaded the source tarball from the website and could not find > these code snippets. Plese help me to identify the path. > > Regards, > mg >

2 1

information about compression/decompression techniques of monetdb
by bhuvan Mitra 19 Jan '15

19 Jan '15

I have been studying about various column-oriented databases and found monetdb to be very interesting as it is efficient and open source. Please tell me in which file/folder does source code of compression/decompression techniques written in monetdb. I am interested to learn the implementations of light weight compression algorithms and cache-decompression. I have source code of monetdb downloaded, just let me know the path of requested codes.

1 0

regarding source code
by bhuvan Mitra 19 Jan '15

19 Jan '15

Hello, I am very interested to know and learn about implementations of compression/decompression techniques that monetdb is following. I have seen the monetdb's light weight compression algorithms(pfor, pfor-delta, pdict), cache involved decompression techniques and excited to know how actually are they implemented in form of codes. Kindly tell me in which folder/file is the source code of this compression/decompression algorithms present. I have downloaded the source tarball from the website and could not find these code snippets. Plese help me to identify the path. Regards, mg

2 1

analyse performance slow query
by Pierre-Adrien Coustillas 16 Jan '15

16 Jan '15

Hi, I try to analyze the slowness of a query with EXPLAIN and stethoscope The query is : SELECT count (*) FROM ( SELECT id_unique, siteniveau1, siteniveau2, '0000-00-00', 0, 0, MAX( debutvisite ), 0, 0 FROM tmp __ at __ lm __visites_ agregees _id_unique GROUP BY id_unique,siteniveau1,siteniveau2 ) tbl ; the beginning of explain is ( there is 272 tuples ) +============================================================================================================================================================================================+ | function user.s2_5{ autoCommit = true }(A0: str ,A1: bte ,A2: bte ,A3: bte ,A4: bte ): void ; | | barrier X_429 := language . dataflow (); | | X_7 := sql . mvc (); | | X_55:bat[: oid ,: oid ] := sql . tid (X_7," sys "," tmp __ at __ lm __visites_ agregees _id_unique",0,8); | | X_70:bat[: oid ,: int ] := sql . bind (X_7," sys "," tmp __ at __ lm __visites_ agregees _id_unique","id_unique",0,0,8); | | (X_78:bat[: oid ,: oid ] ,X_79:bat[: oid ,: int ] ) := sql . bind (X_7," sys "," tmp __ at __ lm __visites_ agregees _id_unique","id_unique",2,0,8); | | X_161 := sql .delta(X_70,X_78,X_79); | | X_169 := algebra . leftfetchjoin (X_55,X_161); | | X_96:bat[: oid ,: int ] := sql . bind (X_7," sys "," tmp __ at __ lm __visites_ agregees _id_unique","siteniveau1",0,0,8); | | (X_111:bat[: oid ,: oid ] ,X_112:bat[: oid ,: int ] ) := sql . bind (X_7," sys "," tmp __ at __ lm __visites_ agregees _id_unique","siteniveau1",2,0,8); | | X_184 := sql .delta(X_96,X_111,X_112); | | X_193 := algebra . leftfetchjoin (X_55,X_184); | | X_129:bat[: oid ,: int ] := sql . bind (X_7," sys "," tmp __ at __ lm __visites_ agregees _id_unique","siteniveau2",0,0,8); | | (X_144:bat[: oid ,: oid ] ,X_145:bat[: oid ,: int ] ) := sql . bind (X_7," sys "," tmp __ at __ lm __visites_ agregees _id_unique","siteniveau2",2,0,8); | | X_201 := sql .delta(X_129,X_144,X_145); | | X_209 := algebra . leftfetchjoin (X_55,X_201); | | (X_224,X_225,X_226) := group. subgroup (X_209); | | (X_248,X_249,X_250) := group. subgroup (X_193,X_224); | | (X_272,X_273,X_274) := group. subgroupdone (X_169,X_248); | | X_275 := algebra . leftfetchjoin (X_273,X_169); | | X_304 := algebra . leftfetchjoin (X_273,X_248); | | X_331 := algebra . leftfetchjoin (X_304,X_249); group. subgroupdone (X_169,X_248); set 10 minutes to run . Why ? how to understand this function ? 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

3 2

MSVCR80.dll Error Question
by Zola Javzmaa 16 Jan '15

16 Jan '15

Hello All, I am having trouble installing Monetdb on to my computer. Would anybody have any suggestions to fixing it? I'm on a Window 7, 64 bit operating system and I get the following error message when I try to load the server program: "Error message: This application has failed to start because MSVCR80.dll was not found. Re-installing the application might fix the problem." Thanks, Zola

3 2

rename database
by Pierre-Adrien Coustillas 15 Jan '15

15 Jan '15

Hi, Is it possible to rename a database ? I thought to do like this: monetdb stop database mv /var/lib/monetdb/database /var/lib/monetdb/new_database monetdb start new_database it's okay ? 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

4 3

MonetDB Usage for spherical clustering
by Jiří Nádvorník 14 Jan '15

14 Jan '15

Dear MonetDB support, My name is Jiri Nadvornik and I am working for Czech astronomical institute (http://www.asu.cas.cz/en/about/about-the-institute) For more than a year I’m trying to generate identifiers for astronomical objects used when creating light curves for these objects. In general, I’m working with spherical coordinates and based on Euclidean parameters (and possibly other parameters, like magnitude) I am clustering these objects to assign multiple observations to one real object. A bottleneck when doing this in a relational database (like PostgreSQL) is not the finding of the neighbors as such (~ ms in a loop), but the inefficiency of working with disk IO when doing so. So after a little analysis of what could be done, I am quite interested into storing the data (mainly spherical coordinates) in an array DB, physically partition regions of the sky to multiple disks and then use the clustering on these partitions in parallel. My question no. 1 for you is whether MonetDB has some kind of support for spherical indexing (PgSphere, Q3C, HealPix, …) to ingest and correctly distribute the astronomical data. I need to control the way of how the data will be distributed to physical partitions. If question no.1 can be somehow answered as true, then question no 2. is: What is the easiest way to install MonetDB, ingest my data and test them? P.S.: Our processed dataset contains now cca 3e8 observations (entries for clustering) which resolve to about 6e6 real objects. We expect that number to double when we process all of our data. Thank you for your answer. Kind regards, Jiri Nadvornik Astronomical Institute of the Academy of Sciences of the Czech Republic nadvornik.ji(a)gmail.com

3 2

Bug in Query Plan??
by Vijay Krishna 09 Jan '15

09 Jan '15

Hi, I have been studying on multiple join performances in MonetDB. I am running the latest version of MonetDB Oct2014 - SP1 (MonetDB-11.19.3) on a CentOS machine with 128 GB RAM. All my tables have 100 to 300 columns and around 35 lakh rows. I have the following query with 5 joins and a few criteria. SELECT * FROM table1 INNER JOIN table2 on table1.col1=table2.col1 LEFT JOIN table3 ON table1.col2=table3.col1 LEFT JOIN table3 ON table1.col3=table3.col1 LEFT JOIN table4 ON table2.col2=table4.col1 LEFT JOIN table5 ON table2.col3=table5.col1 WHERE (((table2.col10 like '%a%' OR table5.col11 like '%s%') and ((table2.col12 like '%w%' *and table4.col13 like '%k'*) OR table2.col14 like '%h%') and (table1.col21 = 1) AND (table1.col22 IN (1,2,3))) AND (((table1.col23 >= 15842000000000000) AND (table1.col23 <= 15842999999999999)) OR ((table1.col23 >= 0) AND (table1.col23 <= 999999999999)))) ORDER BY 1 DESC LIMIT 10 OFFSET 0; This query has only 2000 matching rows and it returns the result in *2.9 seconds*. In the same query, if I remove one criteria alone (the bolded part), the result would still match only 2000 rows, but the result would come in *300 milliseconds* even. I used the PLAN statement and found that the query plan that the executor takes for the first query is a much complex path and *it performs 3 joins for all the 35 lakh rows* (i.e. before executing the criteria even) and hence the time has spiked to 3 seconds. May be the hot data of 35 lakh tuples would have not fit into the memory?? Not sure. But if I remove the highlighted criteria, the executor evaluates the criteria first and *it performs the joins for only 7000 rows* and hence it is faster 1000 times. Is this a bug? Or is there any way to optimise my query to match the executor to choose the right plan? Any help much appreciated. I could even demo this. Thanks & Regards, Vijayakrishna.P. Mobile : (+91) 9500402305.

1 0

Is there a way to limit worker threads on Windows
by Bryan Senseman 06 Jan '15

06 Jan '15

All, I have tried various approaches, but nothing changes the ‘using 8 threads’ message displayed at server startup. I have a client (with a client) that is running MonetDB on a 2cpu windows box and getting various errors, I’m trying to duplicate them but cannot and am concerned it might be that my hardware (read laptop) is too “powerful” to create the error condition. I really do not want to light up a virtual machine just to reduce threads. 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.

5 8

no_mitosis_optimizer - is it advisable?
by Vijay Krishna 05 Jan '15

05 Jan '15

Hi, I have been working on join performances with MonetDB. I tried using various optimizer pipelines. Few costly queries which took 15 seconds with the default pipeline, returned results as fast as 5 seconds with the 'no_mitosis_optimizer' pipeline. I am looking to study more on mitosis optimizer. Is there any reference on what does it do? >From the mserver5 man page, I got this - "forcefully activate mitosis even on small tables, i.e., split small tables in as many (tiny) pieces as there are cores (threads) available;" So, does this mean with the mitosis optimizer, the tables are split and processed? If so, then why are queries slower with mitosis optimizer in the pipeline? Also, from the monetdb man page, I was alerted that "Changing this setting is discouraged at all times." What is the disadvantage of changing the optimizer pipeline to something other than the 'default_pipe'? Though the 'no_mitosis_optimizer' is stable, is it worth for production? Any help much appreciated. Thanks & Regards, Vijayakrishna.P. Mobile : (+91) 9500402305.

4 3

EXPLAIN/TRACE statements reference
by Vijay Krishna 28 Dec '14

28 Dec '14

Hi, I have been studying on the slow query behavior with certain joins in MonetDB. I use explain & trace statements to understand the flow. But, I couldn't actually understand the MAL statements that appear as the output of EXPLAIN/TRACE statements. I tried learning MAL with this reference - https://www.monetdb.org/Documentation/Manuals/MonetDB/MALreference But it had no explanations for system defined MAL statements that appear in trace. For example, from the trace statement, I could find that a lot of time was spent in the MAL expression "language.dataflow()", but without actually knowing what does this step actually do, I am unable to optimize the query. - *Is there any reference guide to understand the output of EXPLAIN/TRACE? Or should we refer to the source code to understand this? * - *Is it possible to know the query plan that Monet arrives to execute a query? * - *Is it possible to make the output of EXPLAIN/TRACE to be in SQL format, like in MySQL?* Any help much appreciated. Thanks & Regards, Vijayakrishna.P. Mobile : (+91) 9500402305.

2 2

Monetdb mserver5 performance flag
by Jose Noel Mendoza Bastidas 24 Dec '14

24 Dec '14

Hello, If it is not too much trouble, could someone explain in detail what happens when you work with mserver5 with flag −−performance (JOINPROPMASK | DEADBEEFMASK)? My intention is to execute several queries and measure their performance in time using the structures provided by MonetDB in order to optimize, at the moment I have documented myself about the Optimizer Pipelines and I would like to know if somebody knows about other structures offered by MonetDB to optimize queries. The purpose of my research is to show the advantages of MonetDB, taking advantage of every resource provided to optimize queries. Thanks in advance.

4 3

Monetdb user-defined optimizer pipeline
by Jose Noel Mendoza Bastidas 23 Dec '14

23 Dec '14

Hi, I was measuring the execution time of some queries using different optimizers that provides MonetDB (minimal, default, no_mitosis, sequential and recycler). Now I want to measure these times but using user-defined optimizer pipeline For this I am testing with the following command: MonetDB Septemberoptpipe='optimizer.inline();optimizer.remap();optimizer.deadcode();optimizer.multiplex();optimizer.garbageCollector();' tesisdb According to the documentation that would be equivalent to using minimal_pipe, the fact is that when I use the expressed command above and run the query throws me the following error: Function '@ 7 ?.' not defined I wonder if someone has happened something like this or have any idea how to create different pipes to those provided by the system Thanks in advance

2 2

Enabled and disabled cracking option on monetdb
by Jose Noel Mendoza Bastidas 17 Dec '14

17 Dec '14

Hi, I am a student at Simon Bolivar University in Venezuela , i' ve been performing an experimental study using MonetDB - v11.17.21 (Jan2014-SP3). I was reading two papers by MonetDb developers about Database Cracking where authors got better results with cracking enabled than without it. In these papers, authors stated that they were able to turn on and off the cracking facilities in monetdb. However, I haven' t been able to find information on how this can be done. In my experimental study, I also need to run my queries and study the performace differences obtained using thes two options. My actual question: do you know how I can turn on and off the cracking option on monetdb? Do you have documentation that explain this? Thanks in advance,

2 1

speed query with 2 columns group by
by Pierre-Adrien Coustillas 15 Dec '14

15 Dec '14

Hello, I have an query with the same source data on Jan2014 -SP3 version of monetddb and Oct2014 - SP1 : CREATE TABLE tmp __pierre5 AS ( SELECT id_unique, Device , COUNT (*) AS NBVISISTES FROM donnees_lmd_pierre GROUP BY id_unique, Device ) WITH DATA; On version Jan2014 -SP3 : Welcome to mclient , the MonetDB / SQL interactive terminal (Jan2014-SP3) Database : MonetDB v11.17.21 (Jan2014-SP3), 'mapi : monetdb :// lemondedev :50000/ lemonde' Type \q to quit , \? for a list of available commands auto commit mode: on sql > CREATE TABLE tmp __pierre5 AS ( SELECT id_unique, Device , COUNT (*) AS NBVISISTES FROM donnees _ lmd _pierre GROUP BY id_unique, Device ) WITH DATA; operation successful (6m 30s) On version Oct2014-SP1 : Welcome to mclient , the MonetDB / SQL interactive terminal (Oct2014-SP1) Database : MonetDB v11.19.7 (Oct2014-SP1), 'mapi : monetdb :// monetdb :50000/ lemonde' Type \q to quit , \? for a list of available commands auto commit mode: on sql > CREATE TABLE tmp __pierre5 AS ( SELECT id_unique, Device , COUNT (*) AS NBVISISTES FROM donnees _ lmd _pierre GROUP BY id_unique, Device ) WITH DATA; operation successful (383m 20s) explain query is different , you want to explain the results of the query ? 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

3 7

Support of string function in Monetdb
by Leon-Constantin Fopa 14 Dec '14

14 Dec '14

Hi every body, Does Monetdb supports string function like concat_ws in Postgresql (http://www.postgresql.org/docs/9.1/static/functions-string.html) ? Best regards, Fopa Léon Constantin,

2 1

RAM usage of MonetDB
by Vijay Krishna 13 Dec '14

13 Dec '14

Hi, I have been testing MonetDB for quite a while and I am a bit quizzed by its RAM utilisation. I tested MonetDB with some complex data and queries as per my requirement in 2 machines - *one with 16 GB RAM and the other with 128 GB RAM *with no memory restrictions for applications. There are no applications other than MySQL and MonetDB running in those machines. The puzzling part was *all the queries took the same time with both the machines*. I have read quite a few documentations wherein I found that the MonetDB performance heavily depends upon RAM memory available. What am I missing? Any help much appreciated. Thanks & Regards, Vijayakrishna.P. Mobile : (+91) 9500402305.

2 1

Inserting arrays with SciQL
by Dimitar Misev 11 Dec '14

11 Dec '14

Hi, I've been experimenting with loading arrays into SciQL, but hit a wall at approx. 200MB TIFF size. Not sure if I'm doing something wrong or if there is a better way to import arrays? sql>CALL rs.attach('grey2.tif'); sql>CALL rs.import(2); Connection terminated Here's some metadata of the file: $ gdalinfo grey2.tif Driver: GTiff/GeoTIFF Files: grey2.tif Size is 21600, 10800 Coordinate System is: GEOGCS["WGS 84", DATUM["WGS_1984", SPHEROID["WGS 84",6378137,298.257223563, AUTHORITY["EPSG","7030"]], AUTHORITY["EPSG","6326"]], PRIMEM["Greenwich",0], UNIT["degree",0.0174532925199433], AUTHORITY["EPSG","4326"]] GeoTransform = 291.065568377342, 3.938632460246877e-06, 8.68784597348722e-07 -22.27513940185346, 8.250100004406028e-07, -3.669324727326387e-06 Metadata: AREA_OR_POINT=Point Image Structure Metadata: INTERLEAVE=BAND Corner Coordinates: Upper Left ( 291.066, -22.275) (291d 3'56.05"E, 22d16'30.50"S) Lower Left ( 291.075, -22.315) (291d 4'29.82"E, 22d18'53.17"S) Upper Right ( 291.151, -22.257) (291d 9' 2.31"E, 22d15'26.35"S) Lower Right ( 291.160, -22.297) (291d 9'36.09"E, 22d17'49.01"S) Center ( 291.113, -22.286) (291d 6'46.07"E, 22d17' 9.76"S) Band 1 Block=21600x1 Type=Byte, ColorInterp=Gray I managed to import a similarly sized file previously, but copying it to another file and importing again fails with "Connection terminated" as well. $ gdalinfo grey.tif Driver: GTiff/GeoTIFF Files: grey.tif Size is 21600, 10800 Coordinate System is `' Origin = (-180.016666666666652,90.016666666666666) Pixel Size = (0.016666666666665,-0.016666666666665) Metadata: TIFFTAG_DATETIME=2008:06:22 09:01:55 TIFFTAG_RESOLUTIONUNIT=2 (pixels/inch) TIFFTAG_SOFTWARE=Adobe Photoshop CS3 Macintosh TIFFTAG_XRESOLUTION=72 TIFFTAG_YRESOLUTION=72 Image Structure Metadata: INTERLEAVE=BAND Corner Coordinates: Upper Left (-180.0166667, 90.0166667) Lower Left (-180.0166667, -89.9833333) Upper Right ( 179.9833333, 90.0166667) Lower Right ( 179.9833333, -89.9833333) Center ( -0.0166667, 0.0166667) Band 1 Block=21600x1 Type=Byte, ColorInterp=Gray Thanks, Dimitar

1 1

Re: inner join very slow
by Stefan Manegold 08 Dec '14

08 Dec '14

Of what type(s) are the join attributes? How big is the expected join result? If you don't know, select count(*), sum(cnt) from (select count(*) as cnt from tmp__pierre2 group by siteniveau1, chapitreniveau1) as x; select count(*), sum(cnt) from (select count(*) as cnt from ref__at_lm_chapitres group by site, idchapitre1) as x; might help to give some estimation / idea ... Best, Stefan On December 1, 2014 6:16:11 PM CET, Pierre-Adrien Coustillas <pcoustillas(a)1g6.biz> wrote: >Hello, > > >With this query : >SELECT count(*) FROM tmp__pierre2 SRC INNER JOIN ref__at_lm_chapitres >SUB ON SRC.siteniveau1=SUB.site AND >SRC.chapitreniveau1=SUB.idchapitre1; > > >SELECT count(*) FROM tmp__pierre2; >+------------+ >| L1 | >+============+ >| 1 061 964 254 | > >SELECT count(*) FROM ref__at_lm_chapitres; >+---------+ >| L1 | >+=========+ >| 1 331 867 | > > >after several hours of running the query is still not finished and I >stopped > > >how to find out where the problem? > > >thanks > > >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 > > > >------------------------------------------------------------------------ > >_______________________________________________ >users-list mailing list >users-list(a)monetdb.org >https://www.monetdb.org/mailman/listinfo/users-list

2 3

COMMIT: transaction is aborted because of concurency conflicts, will ROLLBACK instead
by Pierre-Adrien Coustillas 08 Dec '14

08 Dec '14

Hello, is this a bug or a feature I do not understand ? I have a script bash : ------------------------------------ pcoustillas@monetdb:~$ cat bug_commit.sh #!/bin/bash i=1 while [ $i -le 3 ] do create="CREATE TABLE tmp_schampy_odc__lemondeclient_$i AS ( SELECT datamart__lemonde.id_unique, ( departement IN ( '08','10','67','42','10','15' ) ) AS top_departement FROM datamart__lemonde ) WITH DATA " echo $create mclient lemonde --statement="$create" & i=$(($i+1)) done ------------------------------------ I monetdb restarts before : service monetdb5-sql stop ; sleep 10 ; service monetdb5-sql start And i run script : pcoustillas@monetdb:~$ ./bug_commit.sh CREATE TABLE tmp_schampy_odc__lemondeclient_1 AS ( SELECT datamart__lemonde.id_unique, ( departement IN ( '08','10','67','42','10','15' ) ) AS top_departement FROM datamart__lemonde ) WITH DATA CREATE TABLE tmp_schampy_odc__lemondeclient_2 AS ( SELECT datamart__lemonde.id_unique, ( departement IN ( '08','10','67','42','10','15' ) ) AS top_departement FROM datamart__lemonde ) WITH DATA CREATE TABLE tmp_schampy_odc__lemondeclient_3 AS ( SELECT datamart__lemonde.id_unique, ( departement IN ( '08','10','67','42','10','15' ) ) AS top_departement FROM datamart__lemonde ) WITH DATA pcoustillas@monetdb:~$ operation successful operation successful COMMIT: transaction is aborted because of concurency conflicts, will ROLLBACK instead operation successful why is there a conflict while they are different tables that are created? I use the last version Database: MonetDB v11.19.7 (Oct2014-SP1) I have the same bug on : MonetDB v11.17.21 (Jan2014-SP3) 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

3 4

Grant select on all tables
by Dennis Pallett 08 Dec '14

08 Dec '14

Hi all, I'm playing around with the authorisation/privileges functionality in MonetDB and I'm wondering if it's possible to grant a user the SELECT privilege on ALL tables (present and future tables) in a schema? At the moment I'm only able to grant this privilege on specific tables and I'd ideally like to create a user with full SELECT privileges and nothing more or less. Best regards, Dennis

1 1

SciQL-2 branch
by Dimitar Misev 04 Dec '14

04 Dec '14

Hi, I wanted to try SciQL so I got the SciQL-2 branch, but unfortunately it doesn't seem to be working. Following the tutorial at https://www.monetdb.org/Documentation/UserGuide/Tutorial I got stuck at $ mclient -u monetdb -d voc password: 3F000!CREATE PROCEDURE: no such schema 'rs' ParseException:SQLparser:3F000!CREATE PROCEDURE: no such schema 'rs' The default branch works ok. Any ideas on this problem, is there maybe a specific commit known to work? Thanks, Dimitar

4 9

Release Disk Space
by baraa Mohamad 03 Dec '14

03 Dec '14

Hello, I'm using MonetDB (on ubuntu) for doing some performance tests. I had added and deleted a lot of tables and right now I have no more free disk space since it seems that MonetDB does not clean up disk space after dropping tables. I would like to know the best way to clean up (as much as possible) disk space on MonetDB. Thank you for your help,Baraa

2 2

Error in mserver5 standalone
by Qing Guo 02 Dec '14

02 Dec '14

Hello, I was doing a performance profiling on MonetDB server so I run mserver5 in standalone. The command line I used is: mserver5 --dbpath=[path_to_your_db] --set mapi_open=false --set mapi_port=0 --set single-user=yes --set gdk_nr_threads=1 --set max_clients=1 --set sql_optimizer=default_pipe --set monet_daemon=no < [input] It worked for previous releases but not for the Oct2014. Instead, it gives the following message: "error initSQLreferences" The problem appears that "algebraRef" is not assigned a value before calling "initSQLreferences(void)". Any ideas to solve this problem? Much appreciate, thanks! Kevin

2 2

GROUP BY with JOIN produces unexpected results
by Jonas Goerlich 02 Dec '14

02 Dec '14

Hello, I am using MonetDB in a university project dealing with analysis of twitter data. "stuff" contains some pre-selected user ids. "tweets1207" contain tweet data for one day (userid, latitude, longitude, time, ....) What I want in the end is to find all (rounded) unique geo-coordinates of tweets by users in "stuff". I am using the following query: SELECT userid, ROUND(latitude,0) AS lat, ROUND(longitude,0) AS long FROM tweets1207 INNER JOIN stuff USING (userid) WHERE latitude <> 0 AND longitude <> 0 GROUP BY userid, lat, long ORDER BY userid, lat, long; The result set however still contains rows with duplicate values for "lat" and "long", e.g. userid lat long ----------------------------- 19703 41 -74 19703 41 -74 If I remove the INNER JOIN part from the query, the grouping works as expected. To check whether I am doing something fundamentally wrong (don't have much experience with SQL), I tried running the same query in a sqlite version of the data, where it produced the result I expected (although much, much slower...) Can anybody point me in the right direction? Am I doing something wrong here? Best regards and thanks in advance, Jonas

2 2

inner join very slow
by Pierre-Adrien Coustillas 01 Dec '14

01 Dec '14

Hello, With this query : SELECT count(*) FROM tmp__pierre2 SRC INNER JOIN ref__at_lm_chapitres SUB ON SRC.siteniveau1=SUB.site AND SRC.chapitreniveau1=SUB.idchapitre1; SELECT count(*) FROM tmp__pierre2; +------------+ | L1 | +============+ | 1 061 964 254 | SELECT count(*) FROM ref__at_lm_chapitres; +---------+ | L1 | +=========+ | 1 331 867 | after several hours of running the query is still not finished and I stopped how to find out where the problem? thanks 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

1 0

espace disk after crash
by Pierre-Adrien Coustillas 28 Nov '14

28 Nov '14

Hello, I try to understand the use of disk space after disk full. I look at the files in the folder base /bat/ root@groupelemonde:/var/lib/monetdb/lemonde# ls -l bat/06/676.* -rw------- 1 monetdb monetdb 1579876352 oct. 15 17:42 bat/06/676.tail -rw------- 1 monetdb monetdb 3606183936 nov. 28 15:03 bat/06/676.theap And i look sys.storage SELECT location,columnsize,heapsize FROM sys.storage WHERE location LIKE '06/676'; +----------+------------+------------+ | location | columnsize | heapsize | +==========+============+============+ | 06/676 | 1579850648 | 3606183936 | 676.tail is columnsize column 676.theap is heapsize column but I have 2 large file (> 100 Go) root@groupelemonde:/var/lib/monetdb/lemonde# ls -l bat/06/670.* -rw------- 1 monetdb monetdb 120641335296 nov. 28 14:28 bat/06/670.tail root@groupelemonde:/var/lib/monetdb/lemonde# ls -l bat/06/636.* -rw------- 1 monetdb monetdb 144702111744 nov. 28 12:32 bat/06/636.tail sql>select location,columnsize,heapsize from sys.storage WHERE location IN ('06/636','06/670'); 0 tuples (34.050ms) the modification time of two files is identical to the full disk on my server. Is what I can delete these 2 files ? 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

1 0

Big Slowdown (15-20X ) on import functions in Monetdb October 2014 Release
by Ashish Kumar Singh 28 Nov '14

28 Nov '14

All, With October,2014 release we are stucked in to very strange performance issue while importing data on virtualised environments. On Virtual machines in our local VMware servers and amazon machine we are seeing import performance has downgraded to close to 15 to 20 while importing ~ 2000 files as compared to 1 hour time being taken with old release. This we are seeing consistently with CentOS, SLES vm and configuration is 4 Core 33 GB machine. We tried on quite a few amazon vm just to make sure its not an issue with a particular instance setup. On a physical machine of 4 core 24 GB we are able to match speed of import with old monetdb. We are issuing copy into command through JDBC connection. Can you guys help me with pointers on this issue, I will be happy to share any debug info. Regards, Ashish Kumar Singh Mobile: +91-9972045095 Skype: toashishk(a)gmail.com

5 17

handling of (indirect) division by zero: nulls or an error?
by g4＠novadsp.com 27 Nov '14

27 Nov '14

I posted earlier about a problem with 'type mismatch' being reported. The root cause is quite simple: it is returned if the stddev_pop() function is used in a select that returns zero rows. Given that the count of the population (N) is used as the denominator in the calculation this makes sense. A question arising then is this. If one has assembled a more complex list of aggregate functions in a query, would it be better for null to be returned instead of an error? i.e. select count(weight), sum(weight), average(weight) where (no rows are selected) => 0,null,null Should this not also be the case if stddev_pop is included? select count(weight), sum(weight), average(weight), stddev_pop(weight) where (no rows are selected) => 0,null,null,null Instead of 'type mismatch' ? TIA. J.

1 0

Announcement: New Oct2014-SP1 Bugfix release of MonetDB suite
by Sjoerd Mullender 27 Nov '14

27 Nov '14

The MonetDB team at CWI/MonetDB BV is pleased to announce the Oct2014-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/>. Oct 2014-SP1 bugfix release MonetDB5 Server * Fixed adding of 0 intervals to dates. Build Environment * The libraries included in the Windows installers have been upgraded. We now use libxml2-2.9.2, openssl-1.0.1j, pcre-8.36, and zlib-1.2.8. MonetDB Common * Implemented a change to the way in which string bats are appended. We now try harder to limit the growth of the string heap. * Fixed some problems with BATsample. It was possible for BATsample to return a value that was just beyond the end of the sampled BAT. Also, on some systems the range of the rand() function is rather limited (0..32767) and trying to get a sample larger than this range would result in an infinite loop. SQL * Fixed sys.queue() implementation to report on other queries being executed. * A number of bugs were fixed in the code to upgrade a database from previous releases. This version should fix the upgrade of a database that had been upgraded to the Oct2014 release, but also properly upgrade directly from Jan2014 and Feb2013 releases. Bug Fixes * 3580: cosmetic change (append newline) * 3609: Incorrect use of generate_series * 3611: quantile() and median() commands crash when used 2x on the same variable on a null table * 3612: assertion failure when deleting rows from table to which a FK constraint is defined * 3620: ORDER BY broken when using UNION ALL * 3621: Hexadecimal literal vs decimal literal

1 0

'type mismatch' error
by g4＠novadsp.com 26 Nov '14

26 Nov '14

Using both mclient and C#/C++ test clients connected via 64bit ODBC I am encountering this type mismatch error for no obvious reason. This is something of a show-stopper for what had proved to be a very interesting experiment! # MonetDB 5 server v11.19.3 "Oct2014" # Serving database 'dbfarm', using 4 threads # Compiled for x86_64-pc-winnt/64bit with 64bit OIDs dynamically linked # Found 15.982 GiB available main-memory. An example query below, weight is a double, all other columns are integer. There are no NULL values, nor any oddities sql>SELECT count(weight) , sum(weight) , avg(weight) , stddev_pop(weight) FROM test_1 WHERE ((severity = 1 OR severity = 2) AND (iat = 2) AND (ilit = 3)); Type mismatch But a family of closely related queries do succeed: sql>SELECT count(weight) , sum(weight) , avg(weight) , stddev_pop(weight) FROM test_1 WHERE ((severity = 1 OR severity = 2) AND (iat = 2) AND (ilit = 11)); | L1 | L2 | L3 |> | 290517 | 290517 | 1 | 1 tuple (200.115ms) !1 column dropped! Enabling ODBC logging does not reveal a great deal more than this: dbc-con.vshost 13d8-2ac4 ENTER SQLExecDirectW HSTMT 0x000000000048F370 WCHAR * 0x000000000256458C [ -3] "SELECT count(weight) as cnt, sum(weight) as total, avg(weight) as average, stddev_pop(weight) as stddev FROM test_1 WHERE ((severity = 1 OR severity = 2) AND (iat = 2) AND (ilit = 3))\ 0" SDWORD -3 odbc-con.vshost 13d8-2ac4 EXIT SQLExecDirectW with return code -1 (SQL_ERROR) HSTMT 0x000000000048F370 WCHAR * 0x000000000256458C [ -3] "SELECT count(weight) as cnt, sum(weight) as total, avg(weight) as average, stddev_pop(weight) as stddev FROM test_1 WHERE ((severity = 1 OR severity = 2) AND (iat = 2) AND (ilit = 3))\ 0" SDWORD -3 DIAG [HY000] [MonetDB][ODBC Driver 11.19.3]Type mismatch (0) odbc-con.vshost 13d8-2ac4 ENTER SQLGetDiagRecW SQLSMALLINT 3 <SQL_HANDLE_STMT> SQLHANDLE 0x000000000048F370 SQLSMALLINT 1 SQLWCHAR * 0x000000001C39D940 SQLINTEGER * 0x000000001C39DB34 SQLWCHAR * 0x000000001B6246C0 SQLSMALLINT 1024 SQLSMALLINT * 0x000000001C39DB30 odbc-con.vshost 13d8-2ac4 EXIT SQLGetDiagRecW with return code 0 (SQL_SUCCESS) SQLSMALLINT 3 <SQL_HANDLE_STMT> SQLHANDLE 0x000000000048F370 SQLSMALLINT 1 SQLWCHAR * 0x000000001C39D940 [ 5] "HY000" SQLINTEGER * 0x000000001C39DB34 (0) SQLWCHAR * 0x000000001B6246C0 [ 43] "[MonetDB][ODBC Driver 11.19.3]Type mismatch" SQLSMALLINT 1024 SQLSMALLINT * 0x000000001C39DB30 (43)

1 0

Loading RDF data
by Rinke Hoekstra 26 Nov '14

26 Nov '14

Hi All, After some fiddling, I managed to compile and install the latest version of MonetDB with RDF support enabled (turns out it requires an old version of Raptor... 1.4). My next step would be to load some actual RDF data... but I cannot find any mention of how to do this on the MonetDB website, nor in the mailinglist archives. Pointers very welcome. Thanks, Rinke ---- Rinke Hoekstra VU University Amsterdam/University of Amsterdam

1 0

corrupt catalog Oct 2014
by Pierre-Adrien Coustillas 26 Nov '14

26 Nov '14

Hello, we have again duplicated table on version Oct2014. pcoustillas@ monetdb :~$ mclient lemonde Welcome to mclient , the MonetDB / SQL interactive terminal (Oct2014) Database : MonetDB v11.19.3 (Oct2014), 'mapi : monetdb :// monetdb :50000/ lemonde' Type \q to quit , \? for a list of available commands auto commit mode: on sql >\d src __ at __ lm __visites_ agregees table sys . src __ at __ lm __visites_ agregees is not unique, corrupt catalog ? sql > echo " SELECT name FROM SYS .TABLES WHERE system = false ;" | mclient lemonde | grep " " | grep -v " name " | grep -v " tuple " | sed 's /[| ]// g' | sort | uniq -c | grep -v " 1 " 2 src __ at __ lm __visites_ agregees 2 src __ at __ tra __visites_ agregees 2 tmp _ lm _ suppr _id 4 tmp _ lm _ suppr _id_unique 2 tmp _ tra _ suppr _id 4 tmp _ tra _ suppr _id_unique we do a lot of drop and create table . Is the trouble can come of it ? it must take a sleep after a drop ? 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

2 1

support for window aggregates and window frames ...
by Jay Prasad 24 Nov '14

24 Nov '14

Does MonetDB support window aggregates and window frames? >From the documentation it looks like it supports only window rank functions. Queries of the form: SELECT saleyear, salequarter, sum(saleamount), sum(saleamount) OVER ( PARTITION BY saleyear ORDER BY salequarter ASC) AS partitionedsaleamount FROM ds_1108_null_advent_1580276965 group by saleyear, salequarter; fail with this error: SELECT: function 'sum' not found However, the code seems to include branches that process window aggregates. See sql_parser.y (line 3530) and rel_select.c (line 4658). I am also seeing a similar issue with window frames: SELECT saleyear, salequarter, EXTRACT (MONTH FROM orderdate) AS monthofsale, SUM(saleamount) AS totalsales, RANK() OVER ( PARTITION BY monthofsale ORDER BY SUM(saleamount) ASC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS rank FROM ds_1108_null_advent_1580276965 GROUP BY saleyear,salequarter,monthofsale ORDER BY saleyear ASC LIMIT 20; TypeException:user.s16_1[60]:'algebra.subsort' undefined in: (_103:any, r1_103:any, r2_103:any) := algebra.subsort(_92:int, r1_100:bat[:oid,:oid], r2_100:bat[:o id,:oid], _83:bit, _99:bit) program contains errors NOTICE: This e-mail message and any included attachments are from Infogix, Inc. ("Infogix") and are intended solely for use by the individual(s) to whom the message was addressed. The information contained herein may include privileged or otherwise confidential information. Unauthorized review, forwarding, printing, copying, distributing, or using the information contained in this message is strictly prohibited. If you have received this message in error, or have reason to believe that you are not authorized to receive it, please promptly notify the sender by e-mail, delete the message from your computer, and do not copy or disclose the information to anyone else. If you properly received this e-mail as an addressee, please maintain its contents in confidence to protect confidentiality. Thank you.

1 0

How to use JSON in MonetDB
by Art Apl 24 Nov '14

24 Nov '14

Hello! MonetDB documentation says, that JSON is supported in MonetDB, but I can`t find any example. When I try to implement this query in MonetDB client: json.filter({ "store": { "bicycle": { "color": "red", "price": 19.95 }}},'$') I get this error: syntax error, unexpected IDENT in: "json" THX

2 4

JDBC driver: Statement#setMaxRows()
by Robert Koch 24 Nov '14

24 Nov '14

I have big tables with billions of rows. If I click accidentally on DBVisualizer's data tab, or, if the data tab of a small table is opened and I click on a big table, DBVisualizer tries to retrieve all rows. Then I have to kill DBVisualizer process and/or the DB server process. Answer of DBVisualizer's team: "Hi Robert, It seems like the JDBC driver does not implement the Statement#setMaxRows() method. Please contact your driver developer about this problem." Is the Statement#setMaxRows() method implemented?

2 2

Resource on the PHP API for monetdb
by Leon-Constantin Fopa 24 Nov '14

24 Nov '14

Hi every one, I'm interested in accessing monetdb from a PHP script. Can someone provide me a link on resource with introduce that. The research of the keyword "monetdb" from the the http://php.net website search engine give no related result. thanks in advance, Best regards, Fopa Léon Constantin

3 3

Whicht GUIs/Frontends are you using? / MonetDB support in DBVisualizer
by Robert Koch 24 Nov '14

24 Nov '14

Which tools are you using? As mentioned, I am using DBVisualizer for work with MonetDB. I asked for a better support of MonetDB in DBVisualizer: "MonetDB is the leading open-source, column-oriented, analytical in-memory database. MonetDB's community is growing and MonetDB does not come along with a frontend. It would be great to have a native support out of DBVis." This is the answer: "Hi Robert, We have an open ticket for addressing support for MonetDB in DbVisualizer. Currently the number of requests for it is low so I cannot say when or if it will happen. Most of the database specific support is expressed in a so called database profile in DbVisualizer. This is a single XML file which defines up to 80% of the functionality for a database. The database profile framework is documented in the users guide and if you are looking for improved MonetDB support I suggest you look into building a database profile for it. http://confluence.dbvis.com/display/UG91/Database+Profiles If you have any related questions please don't hesitate to contact us." I somebody out there, who is interested in MonetDB support in DBVisualizer, willing to build a database profile XML file?

1 0

JDBC driver: sorting columns? - DatabaseMetaData#getColumns()
by Robert Koch 24 Nov '14

24 Nov '14

I am using DBVisualizer via JDBC to work with MonetDB. When exporting tables with DBVisualizers @export function, columns are sorted alphabetically, which causes problems when LOADING the exported csv into another database, and the import tool is expecting the original order of columns. I wrote to the DBVisualizer team, they answered as follows: "Hi Robert, Export uses the column order reported by the JDBC driver, so please consult your driver developer to see if you can get the driver fixed. More specifically, DbVisualizer uses the column order as reported by the DatabaseMetaData#getColumns() method." If MonetDB JDBC driver is actually sorting columns, could you please return the given order of table columns?

2 1

To:11/19/2014 6:25:44 PM 1
by drrock1982 19 Nov '14

19 Nov '14

http://teamspl.com.sg/kq/vzxadbrpevtcdjxfltlghewxx.kyorlorbxfhoubwawxvbtvgf… drrock1982(a)rocketmail.com

1 0

Multithreaded prob
by Doug Service 18 Nov '14

18 Nov '14

I have installed MonetDB and and started using it through MAPI. The documentation indicates that opening multiple connections from separate threads in a single process is supported. I perused the MAPI source and it appears that all of the Monet data structures used are associated with the connection. In our application the threads will be independent and processing similar but unique data sets each with its own connection to the database. No threads will share a database connection nor attempt to use another threads connection. Does anyone have experience using multiple threads in a single process each accessing a separate database MonetDB MAPI connection they would like to share? Did you have any problems? Doug Service

5 6

CREATE TEMPORARY TABLE WITH DATA seems not working - Jan2014-SP3
by Satoshi Nagayasu 18 Nov '14

18 Nov '14

Hi all, As shown below, despite CREATE TABLE WITH DATA works fine, CREATE TEMPORARY TABLE WITH DATA is not able to create a temp table with data. --------------------------------------------------------- $ mclient mydb Welcome to mclient, the MonetDB/SQL interactive terminal (Jan2014-SP3) Database: MonetDB v11.17.21 (Jan2014-SP3), 'mapi:monetdb://localhost.localdomain:50000/mydb' Type \q to quit, \? for a list of available commands auto commit mode: on sql>CREATE TABLE t2 AS SELECT 1 WITH DATA; operation successful (1.143ms) sql>SELECT * FROM t2; +------+ | v | +======+ | 1 | +------+ 1 tuple (0.453ms) sql>CREATE TEMPORARY TABLE t AS SELECT 1 WITH DATA; operation successful (0.985ms) sql>SELECT * FROM t; +---+ | v | +===+ +---+ 0 tuples (0.439ms) sql> --------------------------------------------------------- Is this a bug? or something I misunderstand? Regards, -- NAGAYASU Satoshi <snaga(a)uptime.jp>

2 2

[ftin.india.altair.com] Validate Email
by ashishkumar.singh＠altair.com 17 Nov '14

17 Nov '14

Validate Email Address ====================== Email validation to download secure attachment at ftin.india.altair.com. Please click on the following link to view the message: https://ftin.india.altair.com:8443/validate/eJCZlisrascBO00ffEVsQu/LasnXi5p… Or alternatively, enter this validation codein the browser window where you entered your email address: LasnXi5p0Y6ExdEYOSxmmU This code and link is valid for 20 minutes from the request, until 17:23. Please click on the original link in the message email if the link has expired to request a new validation email.

1 0

Operations on Network Addresses
by Tim Burress 14 Nov '14

14 Nov '14

Hello, I'm a relatively new user and have run into a problem which may just be a misunderstanding. I'm using the Oct2014 version, installed on Fedora 20 from the MonetDB rpm repository. When I try to query a database using inet "containment" operators like << or <<= it seems that the operators are not recognized: sql> select src,dst from raw.ids where src << inet'xxx.xxx.xxx.xxx/27'; SELECT: no such binary operator 'left_shift(inet,inet)' sql> select src,dst from raw.ids where src <<= inet'xxx.xxx.xxx.xxx/27'; syntax error, unexpected '=' in: "select src,dst from raw.ids where src <<=" Other ordinary binary inet operators seem OK: sql> select src,dst from raw.ids where src = inet'xxx.xxx.xxx.xxx'; (prints out table) sql> select src,dst from raw.ids where src < dst; (prints out table) However the documented functions do not seem to be recognized: sql> select masklen(inet'192.168.0.0/24') SELECT: no such unary operator 'masklen(inet)' sql> select netmask(inet'192.168.0.0/24') SELECT: no such unary operator 'netmask(inet)' So it seems that support for network address types is only partially available in this release. Is that normal? Do I need to rebuild from source in order to get them? Thanks! Tim

3 7

Security Precautions Of Using Monet DB
by kannan rbk 13 Nov '14

13 Nov '14

Hi, I am started using of MonetDB in our development environment, we decided to move this to our production environment. Before deploying into production environment, I want to know about existing security vulnerabilities and precautions to be taken care of using MonetDB. I had searched in www.monetdb.org, but I haven't found required things. Could you please suggest me any security advisory documents or help guide? I can't find anything in nvd.nist.gov vulnerability database. -- Regards, Kannan RBK

2 2

Struggling with the MINUS operator
by Leon-Constantin Fopa 13 Nov '14

13 Nov '14

Hi ! Here is the query I ran : sql> SELECT s,o FROM "prop_XXX" MINUS SELECT s,o FROM "class_XXX" ; and here is the error I got : syntax error, unexpected SELECT, expecting SCOLON in: "select s,o from "prop_XXX" minus select" Did I missed something ? best regards,

1 1

how to obtain the number of row affected by an INSERT query ?
by Leon-Constantin Fopa 13 Nov '14

13 Nov '14

Hi ! When using the user-interface mclient it quite useful to see the numbers of rows affected by your INSERT query. sql>INSERT INTO ..... 125 affected rows (832.608ms) I wonder if there is a way to obtain this information within a server-side SQL program in MonetDB like Oracle does with SQL%ROWCOUNT in PG/SQL? thanks in advance !

1 0

Monet Bulk load does not work from Java after upgrade to Oct2014
by Radovan Bičiště 13 Nov '14

13 Nov '14

Hello, I hate to be a bugger again. :( We use Pentaho PDI (KETTLE) for loading data from our Oracle DWH to MonetDB. After upgrade from Oct2013-SP6 to Oct2014 bulk loaders stopped working. Any attempt to use ends with Server connection lost message. Here is log message from KETTLE: 2014/11/13 11:13:02 - MonetDB Bulk Loader.0 - COPY 1 RECORDS INTO drmaxtst.typy_pohybu_stage FROM STDIN USING DELIMITERS '|',' 2014/11/13 11:13:02 - MonetDB Bulk Loader.0 - ','"' NULL AS 'null'; 2014/11/13 11:13:02 - MonetDB Bulk Loader.0 - ERROR (version 5.0.1-stable, build 1 from 2013-11-15_16-08-58 by buildguy) : Error in step, asking everyone to stop because of: 2014/11/13 11:13:02 - MonetDB Bulk Loader.0 - ERROR (version 5.0.1-stable, build 1 from 2013-11-15_16-08-58 by buildguy) : org.pentaho.di.core.exception.KettleException: 2014/11/13 11:13:02 - MonetDB Bulk Loader.0 - An error occurred writing data to the MonetDB API (MAPI) process 2014/11/13 11:13:02 - MonetDB Bulk Loader.0 - Connection to server lost! 2014/11/13 11:13:02 - MonetDB Bulk Loader.0 - 2014/11/13 11:13:02 - MonetDB Bulk Loader.0 - at org.pentaho.di.trans.steps.monetdbbulkloader.MonetDBBulkLoader.writeBufferToMonetDB(MonetDBBulkLoader.java:506) 2014/11/13 11:13:02 - MonetDB Bulk Loader.0 - at org.pentaho.di.trans.steps.monetdbbulkloader.MonetDBBulkLoader.writeRowToMonetDB(MonetDBBulkLoader.java:230) 2014/11/13 11:13:02 - MonetDB Bulk Loader.0 - at org.pentaho.di.trans.steps.monetdbbulkloader.MonetDBBulkLoader.processRow(MonetDBBulkLoader.java:207) 2014/11/13 11:13:02 - MonetDB Bulk Loader.0 - at org.pentaho.di.trans.step.RunThread.run(RunThread.java:60) 2014/11/13 11:13:02 - MonetDB Bulk Loader.0 - at java.lang.Thread.run(Thread.java:744) 2014/11/13 11:13:02 - MonetDB Bulk Loader.0 - Caused by: java.io.IOException: Connection to server lost! 2014/11/13 11:13:02 - MonetDB Bulk Loader.0 - at nl.cwi.monetdb.mcl.io.BufferedMCLReader.waitForPrompt(BufferedMCLReader.java:197) 2014/11/13 11:13:02 - MonetDB Bulk Loader.0 - at org.pentaho.di.trans.steps.monetdbbulkloader.MonetDBBulkLoader.writeBufferToMonetDB(MonetDBBulkLoader.java:494) 2014/11/13 11:13:02 - MonetDB Bulk Loader.0 - ... 4 more To be honest the problem first showed up after upgrade from Jan2014SP1 to Jan2014SP2. I was hoping it might be temporary which seems it is not. Thank you for any help, Radovan

2 2

server-side scripting in MonetDB
by Leon-Constantin Fopa 13 Nov '14

13 Nov '14

Hi ! I read Here (http://db-engines.com/en/system/MonetDB%3BOracle%3BSQLite) that MonetDB support server-side script (like PG/SQL for Oracle). However I'm not able to found any related documentation on the MonetDB website Can some one have any pointers, documentation, tutorial about server-side scripting in monetdb ? thanks Best regards, Fopa Léon Constantin

2 2

New Oct2014 - JDBC unable to connect to the database
by Radovan Bičiště 11 Nov '14

11 Nov '14

Hello, Today I've started to test Oct2014 release. I upgraded the database with no issues and downloaded latest JDBC driver monet-jdbc-2.12.jar . When I try to connect to the upgrade database I get an error about tracelog. Here is the log file: 2014-11-11 08:54 INFO Creating new connection for [{Default group}/DRMAX - DEV] for driver=nl.cwi.monetdb.jdbc.MonetDriver and URL=[jdbc:monetdb://lux-dev1-ana.dev.ceosdata.com:50000/zkdev1] 2014-11-11 08:54 INFO Adding ClassLoader URL=file:/home/rbiciste/tools/lib/monetdb-jdbc-2.12.jar 2014-11-11 08:54 ERROR Error connecting to the database using URL=jdbc:monetdb://lux-dev1-ana.dev.ceosdata.com:50000/zkdev1, username=drmaxtst SELECT: no such operator 'tracelog' [SQL State=08001] java.sql.SQLException: SELECT: no such operator 'tracelog' at nl.cwi.monetdb.jdbc.MonetConnection.<init>(MonetConnection.java:254) at nl.cwi.monetdb.jdbc.MonetDriver.connect(MonetDriver.java:171) at workbench.db.DbDriver.connect(DbDriver.java:429) at workbench.db.ConnectionMgr.connect(ConnectionMgr.java:228) at workbench.db.ConnectionMgr.getConnection(ConnectionMgr.java:158) at workbench.gui.components.ConnectionSelector.doConnect(ConnectionSelector.java:233) at workbench.gui.components.ConnectionSelector$2.run(ConnectionSelector.java:133) Caused by: nl.cwi.monetdb.mcl.MCLException: SELECT: no such operator 'tracelog' at nl.cwi.monetdb.mcl.net.MapiSocket.connect(MapiSocket.java:311) at nl.cwi.monetdb.mcl.net.MapiSocket.connect(MapiSocket.java:398) at nl.cwi.monetdb.mcl.net.MapiSocket.connect(MapiSocket.java:252) at nl.cwi.monetdb.jdbc.MonetConnection.<init>(MonetConnection.java:232) ... 6 more Any help would be appreciated. Thank you, Radovan

3 4

Announcement: New Oct2014 Feature release of MonetDB suite
by Sjoerd Mullender 11 Nov '14

11 Nov '14

The MonetDB team at CWI/MonetDB BV is pleased to announce the Oct2014 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/>. Important consideration when upgrading to this release. In this release we have fixed bug 3474. This bug made it possible that there were duplicates despite there being a unique or primary key constraint present. This could only happen for multi-column constraints. MonetDB maintains an internal column containing a hash for multi-column constraints, and there was a bug in the maintenance code of this column. During an upgrade, this column needs to be recreated. This is done by dropping and adding back the constraints. If there are no duplicates, adding back the constrains should work without problem, but if there are duplicates, adding the constraints will fail. There is unfortunately no easy way to tell the user, so after having made the first connection to the server (when the upgrade happens), check the merovingian log (if you use merovingian -- also known as monetdbd) or the server output window (if you don't use merovingian). Jaql support is removed. The experimental code for Jaql support has been removed. If you have RPM or DEB packages for Jaql installed from an earlier version (MonetDB-jaql and monetdb5_jaql respectively), you will need to uninstall them before the upgrade. Known issue when upgrading old versions. We have noticed that upgrading directly from a version older than the Jan2014 release gave an error. Upgrading to Jan2014 first and then on to Oct2014 should work. Backup. As always, make a backup of your databases before upgrading. Oct 2014 feature release MonetDB5 Server * Removed algebra.materialize. * Removed algebra.kunique and algebra.tunique. They were subsumed by algebra.subunique. * Remove algebra.antiuselect and algebra.thetauselect. They were subsumed by algebra.subselect. * Removed algebra.topN and its imlementation BATtopN. The function was not used. * Removed aggr.histogram and its implementation BAThistogram. The function was not used, and did not produce output in the "headless" (i.e. dense-headed) format. Histograms can be created as a by-product of group.subgroup. Build Environment * Jacqueline, the MonetDB/JAQL frontend, has been removed. The frontend never grew beyond being experimental, and there is no interest anymore to maintain the code. Merovingian * monetdb create: add -p flag to set monetdb user password on creation, and therefore allow creating the database in unlocked state mapilib * Changed mapi_timeout argument from seconds to milliseconds. stream * Changed mnstr_settimeout function so that the specified timeout is now in milliseconds (used to be seconds), and that it also needs an extra argument specifying a callback function (no arguments, int result) that should return TRUE if the timeout should cause the function to abort or continue what it was doing. MonetDB Common * Added "multifarm" capability. It is now possible to separate persistent and transient BATs into different directories (presumably on different disks). This can be done by using the --dbextra option of mserver5 (see the man page). SQL * Added PostgreSQL compatible string TRIM, LTRIM, RTRIM, LPAD and RPAD functions * Stop support for upgrading directly from a database created with a server from the Oct2012 release or older. You can upgrade via the Feb2013 or Jan2014 release. Bug Fixes * 2945: evaluation of SQL "between SYMMETRIC" requires MAL iterator because there is no (bulk) MIN/MAX(a,b) * 3204: monetdb create: allow setting admin password during creation * 3390: Missing definition for pushSht in monetdb5/mal/mal_builder.h * 3402: We should have a C implementation of mal.multiplex. * 3422: Segmentation fault after large table insert * 3459: incomplete implementation of JDBC driver getNumericFunctions(), getStringFunctions(), getSystemFunctions(), getTimeDateFunctions(), getSQLKeywords() methods in MonetDatabaseM * 3471: JDBC driver: incorrect output result of SQL query: SELECT 1 ; * 3474: bulk and scalar versions of mkey.rotate_xor_hash differ * 3484: COPY INTO on a file works fine on Linux/OSX, but not on Windows * 3488: Slow SQL execution for correlated scalar subquery * 3489: SQL query with ORDER BY does not order its result as requested * 3490: SQL query kills the mserver5 (Segmentation fault) * 3491: SQL query kills the mserver5 (Segmentation fault) * 3493: Test monetdb5/modules/mal/Tests/pqueue.mal fails since recent checkins * 3494: Tests monetdb5/modules/mal/Tests/pqueue[23].mal lack correct/expected/intended output * 3495: Test sql/test/centipede/Tests/olap.sql lacks correct/expected/intended output * 3497: monetdb start reports crash in merovingian.log * 3498: SQL throws TypeException if aggregations and limit statements are both present * 3502: Database was killed by signal SIGBUS * 3504: COPY INTO does not allow OFFSET without specifying amount of records * 3505: expression with <boolean> = NOT <boolean> returns a syntax error but NOT <boolean> = <boolean> not * 3506: conversion to varchar terminates mserver * 3508: conversion of string '0 ' to type smallint or integer fails * 3510: timestamp + month interval generates bogus MAL? * 3511: When having multiple selections combined with aliases not all of them seem to be evalauted. * 3512: auto-conversion of string to `sht` type no longer works * 3513: COPY BINARY INTO fails on 6gb file; works fine on 3gb * 3516: inserting '0' into a column of datatype numeric fails * 3518: UNION with subqueries * 3521: large results of function exp() are not automatically returned as double * 3522: SQL catalog table sys.columns lists columns for table ids which do not exist in sys.tables * 3523: Window function over union gives no result * 3524: wrong error on missing aggregation column * 3527: select distinct - order by - limit 2 results in one single result * 3528: segfault at mal_session.c:521 * 3532: several geom tests crash after manifold changes * 3534: missing table name with invalid column in join using (and problems after resolving it) * 3536: program contains error with join using integer and smallint * 3542: gdk/gdk_bat.c:2904: BATassertHeadProps: Assertion `!b->H->revsorted || cmp >= 0' failed. * 3543: invalid behavior and incorrect data results for SQL data type: numeric(4,4) * 3544: sys.reuse() corrupts data * 3546: Division by zero in CASE statement that should avoid it * 3547: Empty query when selecting a field from a view made of UNION ALL * 3551: Wrong ticks in TRACE * 3552: incorrect data results for "WHERE int_col <> 0" * 3554: Issue with subselect and ORDER BY * 3555: Order of evaluation inside CASE WHEN * 3558: numeric values (as strings) are incorrectly parsed/converted and invalid strings are accepted without error * 3560: Error "BATproject: does not match always" with subselect/groupby/having * 3562: mserver5: gdk_bat.c:2855: BATassertHeadProps: Assertion `!b->H->revsorted || cmp >= 0' failed. * 3563: incorrect results for scalar function locate(in_str, search_str, occurrence) * 3565: Wrong/confusing error message when trying to add a FK to a TEMP TABLE * 3572: Table names with escaped double quotes are rejected * 3573: alter table alter_not_null_test alter test set NOT NULL; is accepted when test contains null. This used to be restricted but isn't anymore * 3575: segmentation fault in mserver5 process * 3576: Dropping default value definitions from a table does not work as expected * 3577: SIGSEGV in BATins_kdiff * 3579: segmentation fault in mserver5 process * 3581: mserver5: rel_bin.c:2504: rel2bin_groupby: Assertion `0' failed. * 3582: mserver5: sql_mem.c:48: sql_ref_dec: Assertion `r->refcnt > 0' failed. * 3583: Possible buffer overflow in max(varchar) * 3585: Incorrect query terminates connection * 3586: mserver5: sql/storage/store.c:3610: sys_drop_func: Assertion `rid_func != oid_nil' failed. * 3592: SIGSEGV in MANIFOLDjob * 3593: delta_append_val: Assertion `!c || ((c)->S->count) == bat->ibase' failed. * 3594: gdk/gdk_bat.c:2855: BATassertHeadProps: Assertion `!b->H->revsorted || cmp >= 0' failed. * 3595: Race/heap corruption on thread exit * 3596: gdk_bat.c:2861: BATassertHeadProps: Assertion `!b->H->nonil || cmp != 0' failed. * 3597: SQL to MAL listing looses types * 3598: SQL bulk load should ignore leading/trailing spaces also with type decimal (as with integers & real/double) * 3599: Double-free of imprints * 3601: Trivial typo in debian/monetdb5-sql.init.d * 3603: "monetdb create -p" hangs monetdbd * 3604: Sys.queue ignored upon errors

4 3

MonetDB joins
by baraa Mohamad 10 Nov '14

10 Nov '14

Hello, I'd like to know if MonetDB stores a copy of the whole table in order to reduce the joins between columns when doing select *. In fact, I'm doing a research for PhD and I appreciate the query execution time delivered by MonetDB over huge tables :) Thanks in advance,Baraa

3 3

Querying from Linux Terminal
by Vijay Krishna 09 Nov '14

09 Nov '14

Hi, Can I execute a large number of queries through shell script directly from terminal? In MySql I use, mysql -u root -ppassword -e "select * from table" in my shell script, which returns the result without logging into mysql client. Similarly, I want to execute queries in the terminal without logging into mclient. Is there any equivalent way of achieving the same? I have to do a lot of updates and instead of doing those one by one, I prefer writing a shell script to run hundreds of queries one by one automatically. Any help much appreciated. With Regards, Vijayakrishna.P. Mobile : (+91) 9500402305.

5 4

Arch Linux Packages for MonetDB + Language R
by Brian Hood 07 Nov '14

07 Nov '14

Hi All, I have updated my packages to the latest versions you'll need to install dependencies first there 2 for r-lang 3 for monetdb of which one is r-lang anyway, you also need base-devel installing first otherwise you won't be able to build anything. https://aur.archlinux.org/packages/r-lang - 3.1.2 https://aur.archlinux.org/packages/monetdb - 11.19.3 You should be able to build MonetDB on Arch Linux via AUR ( Arch Linux User Repository) without issues you need the abs package installed ( Arch Linux Build System ) first. Regards, PS: https://wiki.archlinux.org/index.php/Arch_Build_System Brian Hood

1 0

MonetDB - Integrated R Support
by Brian Hood 07 Nov '14

07 Nov '14

Hi All, I built Language R from source install it all went well. However when i try to compile MonetDB from source with enable r integration i get the following. make[7]: Entering directory '/data2/AUR/monetdb/src/MonetDB-11.19.3/monetdb5/extras/rapi' /bin/sh ../../../libtool --tag=CC --mode=compile gcc -DHAVE_CONFIG_H -I. -I../../.. -I. -I../../../common/options -I./../../../common/options -I../../../common/stream -I./../../../common/stream -I../../../gdk -I./../../../gdk -I../../mal -I./../../mal -I../../../sql/include -I./../../../sql/include -D_FORTIFY_SOURCE=2 -DLIBRAPI -O3 -fomit-frame-pointer -pipe -march=x86-64 -mtune=generic -pipe -fstack-protector-strong --param=ssp-buffer-size=4 -D_FORTIFY_SOURCE=2 -D_REENTRANT -c -o lib_rapi_la-rapi.lo `test -f 'rapi.c' || echo './'`rapi.c libtool: compile: gcc -DHAVE_CONFIG_H -I. -I../../.. -I. -I../../../common/options -I./../../../common/options -I../../../common/stream -I./../../../common/stream -I../../../gdk -I./../../../gdk -I../../mal -I./../../mal -I../../../sql/include -I./../../../sql/include -D_FORTIFY_SOURCE=2 -DLIBRAPI -O3 -fomit-frame-pointer -pipe -march=x86-64 -mtune=generic -pipe -fstack-protector-strong --param=ssp-buffer-size=4 -D_FORTIFY_SOURCE=2 -D_REENTRANT -c rapi.c -fPIC -DPIC -o .libs/lib_rapi_la-rapi.o rapi.c:42:23: fatal error: Rembedded.h: No such file or directory #include <Rembedded.h> ^ compilation terminated. Makefile:825: recipe for target 'lib_rapi_la-rapi.lo' failed make[7]: *** [lib_rapi_la-rapi.lo] Error 1 make[7]: Leaving directory '/data2/AUR/monetdb/src/MonetDB-11.19.3/monetdb5/extras/rapi' Makefile:499: recipe for target 'all' failed make[6]: *** [all] Error 2 make[6]: Leaving directory '/data2/AUR/monetdb/src/MonetDB-11.19.3/monetdb5/extras/rapi' Makefile:526: recipe for target 'all-recursive' failed make[5]: *** [all-recursive] Error 1 make[5]: Leaving directory '/data2/AUR/monetdb/src/MonetDB-11.19.3/monetdb5/extras' Makefile:479: recipe for target 'all' failed make[4]: *** [all] Error 2 make[4]: Leaving directory '/data2/AUR/monetdb/src/MonetDB-11.19.3/monetdb5/extras' Makefile:522: recipe for target 'all-recursive' failed make[3]: *** [all-recursive] Error 1 make[3]: Leaving directory '/data2/AUR/monetdb/src/MonetDB-11.19.3/monetdb5' Makefile:475: recipe for target 'all' failed make[2]: *** [all] Error 2 make[2]: Leaving directory '/data2/AUR/monetdb/src/MonetDB-11.19.3/monetdb5' Makefile:586: recipe for target 'all-recursive' failed make[1]: *** [all-recursive] Error 1 make[1]: Leaving directory '/data2/AUR/monetdb/src/MonetDB-11.19.3' Makefile:515: recipe for target 'all' failed make: *** [all] Error 2 My Rembedded.h exists here /usr/lib64/R/include/Rembedded.h Is there some environment variables i can set for the location of the my R header files ? Regards, Brian Hood

2 2

sys.queue()
by Guillaume Bassot 06 Nov '14

06 Nov '14

Hello, On release JAN2014, select * from sys.queue(); is returning all running process. On release OCT2014 the same command is returning SELECT: no such operator 'queue' Do i miss a new way to get process list ? Could you please tell me how to do ? Thanks Guillaume BASSOT Web Developer +33 (0)1 55 48 02 82 gbassot(a)inbox.fr 30, rue Vincent Moris 92240 Malakoff - France Fax : +33 (0)1 55 48 02 79

5 6

Improving performance of bulk loading data
by Dennis Pallett 06 Nov '14

06 Nov '14

Hi all, We're experiencing some problems during bulk loading of new data into our MonetDB database. We use COPY INTO to load several CSV files every day but this loading takes (too) long but more importantly the database is completely unresponsive with regards to any incoming queries therefore completely disabling our production environment. I'm currently looking into improving this proces and the hardware of our MonetDB server is obviously one of the starting points. Does anyone know which specs should be upgraded? Would adding more core's/CPU's have the most positive effect? Or perhaps more memory? Any advice or insight would be greatly appreciated! Best regards, Dennis Pallett

3 4

Can't use ORDER BY in subqueries
by Kotschenreuther, Leo 06 Nov '14

06 Nov '14

Hi all, I tried to use some more complex queries, they contained subqueries. I will shortly desrcibe the situation: I have two tables (e.g. test and foo) and want to join them. Furthermore I want to preselect a subset of one of the two tables by adding some conditions (e.g. order the records by attribute c and only choosing the biggest one). A query could look like the this: sql>select * from test join (select * from foo order by c limit 1); The result I receive is the following: syntax error, unexpected ORDER, expecting INTERSECT or EXCEPT or UNION or ')' in: "select * from test join (select * from foo order” On the other hand, I can run the subquery without the main query and I receive a valid result. Is there a possibility to use subqueries with an ORDER BY statement without separating them into two independent queries? Regards Leo

7 16

Several problems with spatial features and query execution time measuring
by Kotschenreuther, Leo 03 Nov '14

03 Nov '14

Hi all, I’m facing several problems with the spacial extension of Monetdb. Here are the basics about my environment: I am using MonetDB v11.17.21 (Jan2014-SP3) on a Suse Linux Enterprise Server 11 (x86_64, Patchlevel 2). I installed MonetDB by compiling from source as there is no Release for SLES available. 1) First of all, I had a glance at the documentation about the spatial functions [1]. I did need some time to realise that every function that is described with the prefix “ST_” in the documentation works without the prefix. Could somebody please change the documentation so that it is according to the sql functions? 2) I would like to use the “Intersects” function that says whether two geometries intersect or not intersect. Apparently my Monetdb-Server doesn’t know the function although he knows other ones like the Equals function. Let’s create two tables with Geometries, fill them with one Polygon each and then check whether the two Polygons intersect: sql>create table one (idone INT, shape Polygon); sql>create table two (idtwo INT, figure Polygon); sql>insert into one values (0, GeomFromText('Polygon((0 0, 1 0, 1 1, 0 1, 0 0))', 4326)); sql>insert into two values (0, GeomFromText('Polygon((0 0, 2 0, 2 2, 0 2, 0 0))', 4326)); sql>select Intersects(shape, figure) from one join two ON idone = idtwo; As result of the last query I receive the following: SELECT: no such binary operator 'intersects(polygon,polygon)’ I tried this on the sql console using mclient, I tried the same inside my python script using the mounted.sql API. Both times I received the same message. 3) I would like to measure the query execution time for all my queries. I found [2] where it is described how to measure the execution time. Following the steps and running the queries from the sql console, everything works fine. When I want to do the same inside my python script, I get an error on running the following statement: select id, query, avg(run) from sys.querylog_history group by id,query; This is the traceback, I skipped the parts of the trace that belong to my own code. The first line is the place where I try to access the sys.querylog_history table. Traceback (most recent call last): ... File "/Users/leokotschenreuther/EPIC/GIS benchmark/db-benchmark/db/monetdb_db.py", line 109, in runQueries self.cursor.execute("select id, query, avg(run) from sys.querylog_history group by id,query") File "/usr/local/lib/python2.7/site-packages/monetdb/sql/cursors.py", line 170, in execute self.__store_result(block) File "/usr/local/lib/python2.7/site-packages/monetdb/sql/cursors.py", line 397, in __store_result values = self.__parse_tuple(line) File "/usr/local/lib/python2.7/site-packages/monetdb/sql/cursors.py", line 440, in __parse_tuple self.description)]) File "/usr/local/lib/python2.7/site-packages/monetdb/sql/pythonize.py", line 139, in convert raise ProgrammingError("type %s is not supported" % type_code) monetdb.exceptions.ProgrammingError: type oid is not supported Does somebody know whether there is a chance to do the time measuring in python as well? I know that I could just use the time module in python but I want to measure the database server execution time. Thanks for reading and helping! Regards Leo [1]: https://www.monetdb.org/Documentation/Extensions/GIS/SpatialFunctions <https://www.monetdb.org/Documentation/Extensions/GIS/SpatialFunctions> [2]: https://www.monetdb.org/Documentation/Cookbooks/SQLrecipes/QueryHistoryNew <https://www.monetdb.org/Documentation/Cookbooks/SQLrecipes/QueryHistoryNew>

2 1

"select * from sys.queue" not working in Oct2014
by Robert Koch 03 Nov '14

03 Nov '14

Welcome to mclient, the MonetDB/SQL interactive terminal (Oct2014) Database: MonetDB v11.19.3 (Oct2014), 'mapi:monetdb://server:50000/anonym' Type \q to quit, \? for a list of available commands auto commit mode: on sql>select * from sys.queue; SELECT: no such operator 'queue' sql>select * from sys.queue(); SELECT: no such operator 'queue' sql>

1 0

Re: MonetDB error while restarting the database
by Ashish Kumar Singh 31 Oct '14

31 Oct '14

Hi All, I have filled an issue Bug 3610: exittimeout property not being honoured against monetdb. It looks like GDKExit error is coming too frequently due to all this and its not even waiting for default value of 60 seconds. Regards, Ashish -----Original Message----- From: Ashish Singh <ashishkumar.singh(a)altair.com> Date: Thursday, 30 October 2014 8:24 pm To: "developers-list(a)monetdb.org" <developers-list(a)monetdb.org> Subject: Re: MonetDB error while restarting the database >Thanks All! > >we made sure now that database connection pool has released all >connections before we shutdown monetdb. > >But still infrequently we are seeing this error popping up and we have >double checked that client has returned all the connections. > >Before stopping the database a data load process happened from the client >to monetdb, can it be contributed to that?? > > > >Regards, >Ashish > >-----Original Message----- >From: Sjoerd Mullender <sjoerd(a)monetdb.org> >Reply-To: "developers-list(a)monetdb.org" <developers-list(a)monetdb.org> >Date: Monday, 27 October 2014 11:07 pm >To: "developers-list(a)monetdb.org" <developers-list(a)monetdb.org> >Subject: Re: MonetDB error while restarting the database > >> >> >>On 27/10/14 17:42, Dimitar Nedev wrote: >>> Hi Ashish, >>> >>> What happened there is monetdbd send a TERM signal to the mserver >>>process (the actual MonetDB database process), which killed all running >>>threads. The messages "#GDKexit: killing thread" means the the MonetDB >>>kernel (GDK) killed a thread still working at the time the process was >>>terminated. This is most likely an indication that the database was >>>still processing something at that time. Depending on what it was >>>actually doing, data corruption might occur. This can explain why >>>monetdbd reports an internal error why truing to start the mserver >>>process again. >> >>Data corruption should *never* occur. If you have proof of (and a >>recipe for) data corruption, please file a bug report. >>Of course, if a thread was still working, the transaction it was working >>on will most likely not get committed. >> >>Otherwise the above is correct. >> >>> Check the merovingian.log file again for the events when monetdbd >>>reported the internal error. There should be more information logged on >>>why mserver cannot start up any more. >>> >>> Best regards, >>> Dimitar >>> >>>> On 2014-Oct-27, at 15:55 , Ashish Kumar Singh >>>><ashishkumar.singh(a)altair.com> wrote: >>>> >>>> Thanks Dimitar, >>>> >>>> Yes we are restarting the data base and as part of that we are seeing >>>>this >>>> error. >>>> >>>> I am more worried about the error below: >>>> >>>> >>>> 2014-10-27 09:01:26 ERR merovingian[23583]: unknown state: 42014-10-27 >>>> 09:01:26 ERR pbsworksdb[6351]: #GDKexit: killing thread >>>> >>>> >>>> On java client side it says >>>> >>>> >>>> monetdbd: internal error while starting mserver, please refer to the >>>>logs >>>> >>>> >>>> >>>> >>>> Regards, >>>> Ashish >>>> >>>> >>>> -----Original Message----- >>>> From: Dimitar Nedev <D.G.Nedev(a)cwi.nl> >>>> Reply-To: "users-list(a)monetdb.org" <users-list(a)monetdb.org> >>>> Date: Monday, 27 October 2014 8:15 pm >>>> To: "users-list(a)monetdb.org" <users-list(a)monetdb.org> >>>> Subject: Re: MonetDB error while restarting the database >>>> >>>>> Hi Ashish, >>>>> >>>>> The exit timeout only tells monetdbd how long should it wait when >>>>>trying >>>>> to gracefully shut down a mserver process. According to the log, the >>>>> mserver process was shut down within the limit, since there are no >>>>> messages like "timeout of <exittimeout> seconds expired, sending >>>>>process >>>>> <PID> the KILL signal". >>>>> >>>>> Now, the interesting entry in the log is the control process message: >>>>> 2014-10-27 09:01:26 MSG control[23583]: (local): stopped database >>>>> 'pbsworksdb' >>>>> >>>>> This one would indicate that someone (or something) gracefully >>>>>stopped >>>>> the database using the monetdbd client. Probably with the following >>>>> command line call like: 'monetdb stop pbsworksdb'. >>>>> Do not be confused by the order of logged events - monetdbd will >>>>>first >>>>> stop the process and later log that the call to stop the a database >>>>>has >>>>> been successful. >>>>> >>>>> Best regards, >>>>> Dimitar >>>>> >>>>> >>>>>> On 2014-Oct-27, at 14:49 , Ashish Kumar Singh >>>>>> <ashishkumar.singh(a)altair.com> wrote: >>>>>> >>>>>> Thanks! >>>>>> >>>>>> >>>>>> Appreciate your response so there are few more people getting the >>>>>>same >>>>>> error :). >>>>>> Any suggestions from dev team on this issue? Can monetdbd >>>>>>exittimeout >>>>>> be also in picture for this? >>>>>> >>>>>> >>>>>> Regards, >>>>>> Ashish >>>>>> >>>>>> >>>>>> >>>>>> From: Pierre-Adrien Coustillas <pcoustillas(a)1g6.biz> >>>>>> Date: Monday, 27 October 2014 6:51 pm >>>>>> To: "users-list(a)monetdb.org" <users-list(a)monetdb.org> >>>>>> Cc: "developers-list(a)monetdb.org" <developers-list(a)monetdb.org>, >>>>>>Ashish >>>>>> Singh <ashishkumar.singh(a)altair.com> >>>>>> Subject: Re: MonetDB error while restarting the database >>>>>> >>>>>>> Hello >>>>>>> >>>>>>> (google translate) >>>>>>> >>>>>>> I receive this error when there is no specific spacedisk. >>>>>>> Monetdb sometimes uses a lot of temporary diskspace >>>>>>>(500GBisnormalfor >>>>>>> 1TBof data), which arereleasedat the end oftreatment or after >>>>>>>acrash >>>>>>> >>>>>>> Error last week : >>>>>>> 2014-10-22 22:51:46 MSG merovingian[18865]: sending process 30608 >>>>>>> (database 'lemonde') the TERM signal >>>>>>> 2014-10-22 22:51:46 ERR merovingian[18865]: unknown state: >>>>>>>42014-10-22 >>>>>>> 22:51:46 ERR lemonde[30608]: #GDKexit: killing thread >>>>>>> >>>>>>> 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 >>>>>>> >>>>>>> De: "Ashish Kumar Singh" <ashishkumar.singh(a)altair.com> >>>>>>> À: "Ashish Kumar Singh" <ashishkumar.singh(a)altair.com>, >>>>>>> users-list(a)monetdb.org >>>>>>> Cc: developers-list(a)monetdb.org >>>>>>> Envoyé: Lundi 27 Octobre 2014 13:49:19 >>>>>>> Objet: Re: MonetDB error while restarting the database >>>>>>> >>>>>>> Guys, >>>>>>> >>>>>>> Any help with this will be really helpful for us? >>>>>>> >>>>>>> >>>>>>> >>>>>>> Regards, >>>>>>> Ashish >>>>>>> >>>>>>> From: Ashish Singh <ashishkumar.singh(a)altair.com> >>>>>>> Date: Monday, 27 October 2014 4:06 pm >>>>>>> To: "users-list(a)monetdb.org" <users-list(a)monetdb.org> >>>>>>> Subject: MonetDB error while restarting the database >>>>>>> >>>>>>>> >>>>>>>> All, >>>>>>>> >>>>>>>> >>>>>>>> >>>>>>>> We are facing a new issue with monetdb where monetdb connections >>>>>>>>and >>>>>>>> process is being killed very frequently. Is this a known issue or >>>>>>>>any >>>>>>>> suggestion in getting more details will be helpful. >>>>>>>> >>>>>>>> >>>>>>>> >>>>>>>> Regards, >>>>>>>> Ashish >>>>>>>> >>>>>>>> Mervogian.log: >>>>>>>> >>>>>>>> >>>>>>>> >>>>>>>> 2014-10-27 09:01:00 MSG pbsworksdb[6351]: arguments: >>>>>>>> >>>>>>>>/opt/pbsworks/12.4_rc3.1_live/portal/thirdparty/monetdb/bin/mserver >>>>>>>>5 >>>>>>>> >>>>>>>>--dbpath=/opt/pbsworks/12.4_rc3.1_live/portal/thirdparty/monetdb/pb >>>>>>>>s >>>>>>>>wor >>>>>>>> ksdata/pbsworksdb --set >>>>>>>> merovingian_uri=mapi:monetdb://blrec12-vm6:9200/pbsworksdb --set >>>>>>>> mapi_open=false --set mapi_port=0 --set >>>>>>>> >>>>>>>>mapi_usock=/opt/pbsworks/12.4_rc3.1_live/portal/thirdparty/monetdb/ >>>>>>>>p >>>>>>>>bsw >>>>>>>> orksdata/pbsworksdb/.mapi.sock --set >>>>>>>> >>>>>>>>monet_vault_key=/opt/pbsworks/12.4_rc3.1_live/portal/thirdparty/mon >>>>>>>>e >>>>>>>>tdb >>>>>>>> /pbsworksdata/pbsworksdb/.vaultkey --set gdk_nr_threads=4 --set >>>>>>>> max_clients=64 --set sql_optimizer=default_pipe --set >>>>>>>>monet_daemon=yes >>>>>>>> 2014-10-27 09:01:00 MSG pbsworksdb[6351]: # MonetDB 5 server >>>>>>>> v11.17.17 "Jan2014-SP2" >>>>>>>> 2014-10-27 09:01:00 MSG pbsworksdb[6351]: # Serving database >>>>>>>> 'pbsworksdb', using 4 threads >>>>>>>> 2014-10-27 09:01:00 MSG pbsworksdb[6351]: # Compiled for >>>>>>>> x86_64-unknown-linux-gnu/64bit with 64bit OIDs dynamically linked >>>>>>>> 2014-10-27 09:01:00 MSG pbsworksdb[6351]: # Found 15.675 GiB >>>>>>>> available main-memory. >>>>>>>> 2014-10-27 09:01:00 MSG pbsworksdb[6351]: # Copyright (c) >>>>>>>>1993-July >>>>>>>> 2008 CWI. >>>>>>>> 2014-10-27 09:01:00 MSG pbsworksdb[6351]: # Copyright (c) August >>>>>>>> 2008-2014 MonetDB B.V., all rights reserved >>>>>>>> 2014-10-27 09:01:00 MSG pbsworksdb[6351]: # Visit >>>>>>>> http://www.monetdb.org/ for further information >>>>>>>> 2014-10-27 09:01:00 MSG pbsworksdb[6351]: # Listening for UNIX >>>>>>>>domain >>>>>>>> connection requests on >>>>>>>> >>>>>>>>mapi:monetdb:///opt/pbsworks/12.4_rc3.1_live/portal/thirdparty/mone >>>>>>>>t >>>>>>>>db/ >>>>>>>> pbsworksdata/pbsworksdb/.mapi.sock >>>>>>>> 2014-10-27 09:01:00 MSG pbsworksdb[6351]: # MonetDB/SQL module >>>>>>>>loaded >>>>>>>> 2014-10-27 09:01:00 MSG control[23583]: (local): started >>>>>>>>'pbsworksdb' >>>>>>>> 2014-10-27 09:01:00 MSG merovingian[23583]: proxying client >>>>>>>> localhost.localdomain:53844 for database 'pbsworksdb' to >>>>>>>> >>>>>>>>mapi:monetdb:///opt/pbsworks/12.4_rc3.1_live/portal/thirdparty/mone >>>>>>>>t >>>>>>>>db/ >>>>>>>> pbsworksdata/pbsworksdb/.mapi.sock?database=pbsworksdb >>>>>>>> 2014-10-27 09:01:00 MSG merovingian[23583]: target connection is >>>>>>>>on >>>>>>>> local UNIX domain socket, passing on filedescriptor instead of >>>>>>>>proxying >>>>>>>> 2014-10-27 09:01:26 MSG control[23583]: (local): served status >>>>>>>>list >>>>>>>> 2014-10-27 09:01:26 MSG merovingian[23583]: sending process 6351 >>>>>>>> (database 'pbsworksdb') the TERM signal >>>>>>>> 2014-10-27 09:01:26 ERR merovingian[23583]: unknown state: >>>>>>>> 42014-10-27 09:01:26 ERR pbsworksdb[6351]: #GDKexit: killing >>>>>>>>thread >>>>>>>> 2014-10-27 09:01:26 MSG merovingian[23583]: database 'pbsworksdb' >>>>>>>> (6351) has exited with exit status 0 >>>>>>>> 2014-10-27 09:01:26 MSG merovingian[23583]: database 'pbsworksdb' >>>>>>>>has >>>>>>>> shut down >>>>>>>> 2014-10-27 09:01:26 MSG control[23583]: (local): stopped database >>>>>>>> 'pbsworksdb' >>>>>>>> 2014-10-27 09:01:26 MSG control[23583]: (local): served status >>>>>>>>list >>>>>>>> 2014-10-27 09:01:26 MSG merovingian[23583]: starting database >>>>>>>> 'pbsworksdb', up min/avg/max: 5s/14m/1h, crash average: 0.00 0.00 >>>>>>>>0.00 >>>>>>>> (648-648=0) >>>>>>>> 2014-10-27 09:01:27 MSG pbsworksdb[6575]: arguments: >>>>>>>> >>>>>>>>/opt/pbsworks/12.4_rc3.1_live/portal/thirdparty/monetdb/bin/mserver >>>>>>>>5 >>>>>>>> >>>>>>>>--dbpath=/opt/pbsworks/12.4_rc3.1_live/portal/thirdparty/monetdb/pb >>>>>>>>s >>>>>>>>wor >>>>>>>> ksdata/pbsworksdb --set >>>>>>>> merovingian_uri=mapi:monetdb://blrec12-vm6:9200/pbsworksdb --set >>>>>>>> mapi_open=false --set mapi_port=0 --set >>>>>>>> >>>>>>>>mapi_usock=/opt/pbsworks/12.4_rc3.1_live/portal/thirdparty/monetdb/ >>>>>>>>p >>>>>>>>bsw >>>>>>>> orksdata/pbsworksdb/.mapi.sock --set >>>>>>>> >>>>>>>>monet_vault_key=/opt/pbsworks/12.4_rc3.1_live/portal/thirdparty/mon >>>>>>>>e >>>>>>>>tdb >>>>>>>> /pbsworksdata/pbsworksdb/.vaultkey --set gdk_nr_threads=4 --set >>>>>>>> max_clients=64 --set sql_optimizer=default_pipe --set >>>>>>>>monet_daemon=yes >>>>>>>> 2014-10-27 09:01:32 MSG pbsworksdb[6575]: # MonetDB 5 server >>>>>>>> v11.17.17 "Jan2014-SP2" >>>>>>>> 2014-10-27 09:01:32 MSG pbsworksdb[6575]: # Serving database >>>>>>>> 'pbsworksdb', using 4 threads >>>>>>>> 2014-10-27 09:01:32 MSG pbsworksdb[6575]: # Compiled for >>>>>>>> x86_64-unknown-linux-gnu/64bit with 64bit OIDs dynamically linked >>>>>>>> 2014-10-27 09:01:32 MSG pbsworksdb[6575]: # Found 15.675 GiB >>>>>>>> available main-memory. >>>>>>>> 2014-10-27 09:01:32 MSG pbsworksdb[6575]: # Copyright (c) >>>>>>>>1993-July >>>>>>>> 2008 CWI. >>>>>>>> 2014-10-27 09:01:32 MSG pbsworksdb[6575]: # Copyright (c) August >>>>>>>> 2008-2014 MonetDB B.V., all rights reserved >>>>>>>> 2014-10-27 09:01:32 MSG pbsworksdb[6575]: # Visit >>>>>>>> http://www.monetdb.org/ for further information >>>>>>>> 2014-10-27 09:01:32 MSG pbsworksdb[6575]: # Listening for UNIX >>>>>>>>domain >>>>>>>> connection requests on >>>>>>>> >>>>>>>>mapi:monetdb:///opt/pbsworks/12.4_rc3.1_live/portal/thirdparty/mone >>>>>>>>t >>>>>>>>db/ >>>>>>>> pbsworksdata/pbsworksdb/.mapi.sock >>>>>>>> 2014-10-27 09:01:32 MSG pbsworksdb[6575]: # MonetDB/SQL module >>>>>>>>loaded >>>>>>>> 2014-10-27 09:01:32 MSG merovingian[23583]: proxying client >>>>>>>> localhost.localdomain:53853 for database 'pbsworksdb' to >>>>>>>> >>>>>>>>mapi:monetdb:///opt/pbsworks/12.4_rc3.1_live/portal/thirdparty/mone >>>>>>>>t >>>>>>>>db/ >>>>>>>> pbsworksdata/pbsworksdb/.mapi.sock?database=pbsworksdb >>>>>>>> 2014-10-27 09:01:32 MSG merovingian[23583]: target connection is >>>>>>>>on >>>>>>>> local UNIX domain socket, passing on filedescriptor instead of >>>>>>>>proxying >>>>>>>> 2014-10-27 09:01:32 MSG c >>>>>>>> >>>>>>>> >>>>>>>> >>>>>>>> >>>>>>>> >>>>>>> >>>>>>> _______________________________________________ >>>>>>> users-list mailing list >>>>>>> users-list(a)monetdb.org >>>>>>> https://www.monetdb.org/mailman/listinfo/users-list >>>>>>> >>>>>> _______________________________________________ >>>>>> developers-list mailing list >>>>>> developers-list(a)monetdb.org >>>>>> https://www.monetdb.org/mailman/listinfo/developers-list >>>>> >>>>> _______________________________________________ >>>>> users-list mailing list >>>>> users-list(a)monetdb.org >>>>> https://www.monetdb.org/mailman/listinfo/users-list >>>> >>>> _______________________________________________ >>>> developers-list mailing list >>>> developers-list(a)monetdb.org >>>> https://www.monetdb.org/mailman/listinfo/developers-list >>> >>> _______________________________________________ >>> developers-list mailing list >>> developers-list(a)monetdb.org >>> https://www.monetdb.org/mailman/listinfo/developers-list >>> >> >>-- >>Sjoerd Mullender >> >

1 0

SQL_NO_CACHE equivalent?
by Vijay Krishna 30 Oct '14

30 Oct '14

Hi, Is there any equivalent to SQL_NO_CACHE in monetdb?? I wish the queries I run to return results from disk in order to check performance. Any help much appreciated. Thanks in advance. With Regards, Vijayakrishna.P. Mobile : (+91) 9500402305.

2 1

detect fragmented tables
by Pierre-Adrien Coustillas 30 Oct '14

30 Oct '14

Hello Before working with optimizer, Stethoscope and Tomograph , first I have to defragment the fragmented tables. How to detect fragmented tables ? Write a script that reads heapsize column ? select * , ( heapsize / 1021/1024/1024) as giga from sys.storage heapsize ORDER BY DESC LIMIT 20; What do you think ? or a more specific sql query by reading the column "count ", " typewidth ", " ColumnSize " and " heapsize " ? 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

2 5

Transaction Failure: BATsubselect: invalid argument: b must have a dense head
by Tim Burress 30 Oct '14

30 Oct '14

Hello, I've been using MonetDB for a couple of weeks now with no problems, but today ran into this issue when doing a series of DELETE/INSERT commands to update a table. When I issue the commands individually (autocommit on), there doesn't seem to be any difficulty, but when I do them as part of a transaction, I get this error on the second insert: START TRANSACTION; DELETE... 1 affected rows (10.145ms) INSERT... 1 affected rows (11.464ms) DELETE... 1 affected rows (3.402ms) INSERT... GDK reported error. BATsubselect: invalid argument: b must have a dense head I'm not sure what this means or what to do about it. Similar transactions were done yesterday with no problem, so I thought perhaps the table had been corrupted, but if I drop the table and recreate it from the original data, the above error still occurs. Thanks for any help you can provide, and if there is anything I can do that would give you some hints about what's happening, just tell me what to do and I'll be happy to help. Tim

1 0

MonetDB error while restarting the database
by Ashish Kumar Singh 27 Oct '14

27 Oct '14

All, We are facing a new issue with monetdb where monetdb connections and process is being killed very frequently. Is this a known issue or any suggestion in getting more details will be helpful. Regards, Ashish Mervogian.log: 2014-10-27 09:01:00 MSG pbsworksdb[6351]: arguments: /opt/pbsworks/12.4_rc3.1_live/portal/thirdparty/monetdb/bin/mserver5 --dbpath=/opt/pbsworks/12.4_rc3.1_live/portal/thirdparty/monetdb/pbsworksdata/pbsworksdb --set merovingian_uri=mapi:monetdb://blrec12-vm6:9200/pbsworksdb --set mapi_open=false --set mapi_port=0 --set mapi_usock=/opt/pbsworks/12.4_rc3.1_live/portal/thirdparty/monetdb/pbsworksdata/pbsworksdb/.mapi.sock --set monet_vault_key=/opt/pbsworks/12.4_rc3.1_live/portal/thirdparty/monetdb/pbsworksdata/pbsworksdb/.vaultkey --set gdk_nr_threads=4 --set max_clients=64 --set sql_optimizer=default_pipe --set monet_daemon=yes 2014-10-27 09:01:00 MSG pbsworksdb[6351]: # MonetDB 5 server v11.17.17 "Jan2014-SP2" 2014-10-27 09:01:00 MSG pbsworksdb[6351]: # Serving database 'pbsworksdb', using 4 threads 2014-10-27 09:01:00 MSG pbsworksdb[6351]: # Compiled for x86_64-unknown-linux-gnu/64bit with 64bit OIDs dynamically linked 2014-10-27 09:01:00 MSG pbsworksdb[6351]: # Found 15.675 GiB available main-memory. 2014-10-27 09:01:00 MSG pbsworksdb[6351]: # Copyright (c) 1993-July 2008 CWI. 2014-10-27 09:01:00 MSG pbsworksdb[6351]: # Copyright (c) August 2008-2014 MonetDB B.V., all rights reserved 2014-10-27 09:01:00 MSG pbsworksdb[6351]: # Visit http://www.monetdb.org/ for further information 2014-10-27 09:01:00 MSG pbsworksdb[6351]: # Listening for UNIX domain connection requests on mapi:monetdb:///opt/pbsworks/12.4_rc3.1_live/portal/thirdparty/monetdb/pbsworksdata/pbsworksdb/.mapi.sock 2014-10-27 09:01:00 MSG pbsworksdb[6351]: # MonetDB/SQL module loaded 2014-10-27 09:01:00 MSG control[23583]: (local): started 'pbsworksdb' 2014-10-27 09:01:00 MSG merovingian[23583]: proxying client localhost.localdomain:53844 for database 'pbsworksdb' to mapi:monetdb:///opt/pbsworks/12.4_rc3.1_live/portal/thirdparty/monetdb/pbsworksdata/pbsworksdb/.mapi.sock?database=pbsworksdb 2014-10-27 09:01:00 MSG merovingian[23583]: target connection is on local UNIX domain socket, passing on filedescriptor instead of proxying 2014-10-27 09:01:26 MSG control[23583]: (local): served status list 2014-10-27 09:01:26 MSG merovingian[23583]: sending process 6351 (database 'pbsworksdb') the TERM signal 2014-10-27 09:01:26 ERR merovingian[23583]: unknown state: 42014-10-27 09:01:26 ERR pbsworksdb[6351]: #GDKexit: killing thread 2014-10-27 09:01:26 MSG merovingian[23583]: database 'pbsworksdb' (6351) has exited with exit status 0 2014-10-27 09:01:26 MSG merovingian[23583]: database 'pbsworksdb' has shut down 2014-10-27 09:01:26 MSG control[23583]: (local): stopped database 'pbsworksdb' 2014-10-27 09:01:26 MSG control[23583]: (local): served status list 2014-10-27 09:01:26 MSG merovingian[23583]: starting database 'pbsworksdb', up min/avg/max: 5s/14m/1h, crash average: 0.00 0.00 0.00 (648-648=0) 2014-10-27 09:01:27 MSG pbsworksdb[6575]: arguments: /opt/pbsworks/12.4_rc3.1_live/portal/thirdparty/monetdb/bin/mserver5 --dbpath=/opt/pbsworks/12.4_rc3.1_live/portal/thirdparty/monetdb/pbsworksdata/pbsworksdb --set merovingian_uri=mapi:monetdb://blrec12-vm6:9200/pbsworksdb --set mapi_open=false --set mapi_port=0 --set mapi_usock=/opt/pbsworks/12.4_rc3.1_live/portal/thirdparty/monetdb/pbsworksdata/pbsworksdb/.mapi.sock --set monet_vault_key=/opt/pbsworks/12.4_rc3.1_live/portal/thirdparty/monetdb/pbsworksdata/pbsworksdb/.vaultkey --set gdk_nr_threads=4 --set max_clients=64 --set sql_optimizer=default_pipe --set monet_daemon=yes 2014-10-27 09:01:32 MSG pbsworksdb[6575]: # MonetDB 5 server v11.17.17 "Jan2014-SP2" 2014-10-27 09:01:32 MSG pbsworksdb[6575]: # Serving database 'pbsworksdb', using 4 threads 2014-10-27 09:01:32 MSG pbsworksdb[6575]: # Compiled for x86_64-unknown-linux-gnu/64bit with 64bit OIDs dynamically linked 2014-10-27 09:01:32 MSG pbsworksdb[6575]: # Found 15.675 GiB available main-memory. 2014-10-27 09:01:32 MSG pbsworksdb[6575]: # Copyright (c) 1993-July 2008 CWI. 2014-10-27 09:01:32 MSG pbsworksdb[6575]: # Copyright (c) August 2008-2014 MonetDB B.V., all rights reserved 2014-10-27 09:01:32 MSG pbsworksdb[6575]: # Visit http://www.monetdb.org/ for further information 2014-10-27 09:01:32 MSG pbsworksdb[6575]: # Listening for UNIX domain connection requests on mapi:monetdb:///opt/pbsworks/12.4_rc3.1_live/portal/thirdparty/monetdb/pbsworksdata/pbsworksdb/.mapi.sock 2014-10-27 09:01:32 MSG pbsworksdb[6575]: # MonetDB/SQL module loaded 2014-10-27 09:01:32 MSG merovingian[23583]: proxying client localhost.localdomain:53853 for database 'pbsworksdb' to mapi:monetdb:///opt/pbsworks/12.4_rc3.1_live/portal/thirdparty/monetdb/pbsworksdata/pbsworksdb/.mapi.sock?database=pbsworksdb 2014-10-27 09:01:32 MSG merovingian[23583]: target connection is on local UNIX domain socket, passing on filedescriptor instead of proxying 2014-10-27 09:01:32 MSG c

3 6

set optimizer
by Pierre-Adrien Coustillas 23 Oct '14

23 Oct '14

Hello , (google translate) I am a little fun with the optimizer . sql>s elec t count (distinct idvisiteur ) from src_inbox__at__lm__visites ; + ---------- + | L1 | + + ========== | 75528080 | + ---------- + 1 tuple ( 3m 51s ) sql> set optimizer = ' minimal_pipe ; sql> select count (distinct idvisiteur ) from src_inbox__at__lm__visites ; + ---------- + | L1 | + + ========== | 75528080 | + ---------- + 1 tuple (1m 28s ) sql> set optimizer = ' default_pipe ; sql> select count (distinct idvisiteur ) from src_inbox__at__lm__visites ; + ---------- + | L1 | + + ========== | 75528080 | + ---------- + 1 tuple ( 3m 54s ) It's very interesting . Is there a risk to change optimizer ? What are the best practices ? thank you -- 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

2 1

heapsize, speed, and vacuum
by Pierre-Adrien Coustillas 23 Oct '14

23 Oct '14

Hello, (google translate) I have a query that takes a long time : select count(distinct idvisiteur) from src_inbox__at__lm__visites; After two hours of running, it is not over , I'll kill her . if I look at the heapsize column storage ( ) sql>select * from storage() WHERE "table"='src_inbox__at__lm__visites'; +--------+----------------------------+---------------------+---------+-------------+-----------+-----------+------------+-------------+---------+--------+ | schema | table | column | type | location | count | typewidth | columnsize | heapsize | indices | sorted | +========+============================+=====================+=========+=============+===========+===========+============+=============+=========+========+ | sys | src_inbox__at__lm__visites | idvisite | varchar | 01/00/10025 | 315819936 | 5 | 2526559488 | 27755937792 | 0 | false | | sys | src_inbox__at__lm__visites | idvisiteur | varchar | 76/7667 | 315819936 | 19 | 2526559488 | 80449110016 | 0 | false | | sys | src_inbox__at__lm__visites | idvisiteuridentifie | varchar | 62/6201 | 315819936 | 2 | 1263279744 | 2222391296 | 0 | false | | sys | src_inbox__at__lm__visites | siteniveau1 | varchar | 77/7704 | 315819936 | 5 | 315819936 | 65536 | 0 | false | | sys | src_inbox__at__lm__visites | debutvisite | varchar | 30/3064 | 315819936 | 19 | 2526559488 | 10741678080 | 0 | false | | sys | src_inbox__at__lm__visites | catvisiteur | varchar | 31/3171 | 315819936 | 0 | 631639872 | 65536 | 0 | false | | sys | src_inbox__at__lm__visites | region | varchar | 67/6706 | 315819936 | 12 | 1263279744 | 2077556736 | 0 | false | | sys | src_inbox__at__lm__visites | ville | varchar | 40/4071 | 315819936 | 10 | 2526559488 | 22635741184 | 0 | false | | sys | src_inbox__at__lm__visites | os | varchar | 01/26/12673 | 315819936 | 8 | 631639872 | 65536 | 0 | false | | sys | src_inbox__at__lm__visites | browser | varchar | 01/44/14410 | 315819936 | 17 | 631639872 | 65536 | 0 | false | | sys | src_inbox__at__lm__visites | sourcevisite | varchar | 42/4234 | 315819936 | 1 | 631639872 | 65536 | 0 | false | | sys | src_inbox__at__lm__visites | idcampagne | varchar | 01/41/14100 | 315819936 | 0 | 631639872 | 65536 | 0 | false | | sys | src_inbox__at__lm__visites | prefixe | varchar | 01/22/12277 | 315819936 | 0 | 315819936 | 65536 | 0 | false | | sys | src_inbox__at__lm__visites | libcampagne | varchar | 65/6573 | 315819936 | 0 | 631639872 | 65536 | 0 | false | | sys | src_inbox__at__lm__visites | emailing | varchar | 01/04/10434 | 315819936 | 0 | 631639872 | 65536 | 0 | false | | sys | src_inbox__at__lm__visites | dateenvois | varchar | 01/22/12264 | 315819936 | 0 | 631639872 | 65536 | 0 | false | | sys | src_inbox__at__lm__visites | nbpages | int | 56/5615 | 315819936 | 4 | 1263279744 | 0 | 0 | false | | sys | src_inbox__at__lm__visites | dureevisite | time | 01/02/10211 | 315819936 | 4 | 1263279744 | 0 | 0 | false | | sys | src_inbox__at__lm__visites | id_unique | int | 01/23/12370 | 315819936 | 4 | 1263279744 | 0 | 0 | false | +--------+----------------------------+---------------------+---------+-------------+-----------+-----------+------------+-------------+---------+--------+ Is the heapsize column represents the amount of data to be placed in RAM ? I have 64GB of RAM on the server , so the idvisiteur column can not be held in RAM. Is this true? ( 64 000 000 000 < 80 449 110 016 ) I execute a vacuum (experimental command ?) : https://www.monetdb.org/Documentation/UserGuide/AutoloadingScript (Table vacuum commands (experimental!)) call vacuum('sys','src_inbox__at__lm__visites'); sql>select * from storage() WHERE "table"='src_inbox__at__lm__visites'; +--------+----------------------------+---------------------+---------+-------------+-----------+-----------+------------+------------+---------+--------+ | schema | table | column | type | location | count | typewidth | columnsize | heapsize | indices | sorted | +========+============================+=====================+=========+=============+===========+===========+============+============+=========+========+ | sys | src_inbox__at__lm__visites | idvisite | varchar | 36 | 315819936 | 5 | 2526559488 | 4906811392 | 0 | false | | sys | src_inbox__at__lm__visites | idvisiteur | varchar | 64/6435 | 315819936 | 19 | 1263279744 | 3667394560 | 0 | false | | sys | src_inbox__at__lm__visites | idvisiteuridentifie | varchar | 66/6640 | 315819936 | 2 | 1263279744 | 40042496 | 0 | false | | sys | src_inbox__at__lm__visites | siteniveau1 | varchar | 67/6743 | 315819936 | 5 | 315819936 | 73728 | 0 | false | | sys | src_inbox__at__lm__visites | debutvisite | varchar | 70/7045 | 315819936 | 19 | 2526559488 | 7576551424 | 0 | false | | sys | src_inbox__at__lm__visites | catvisiteur | varchar | 71/7152 | 315819936 | 0 | 631639872 | 73728 | 0 | false | | sys | src_inbox__at__lm__visites | region | varchar | 72/7252 | 315819936 | 12 | 1263279744 | 106561536 | 0 | false | | sys | src_inbox__at__lm__visites | ville | varchar | 73/7353 | 315819936 | 10 | 1263279744 | 1362165760 | 0 | false | | sys | src_inbox__at__lm__visites | os | varchar | 74/7453 | 315819936 | 8 | 631639872 | 73728 | 0 | false | | sys | src_inbox__at__lm__visites | browser | varchar | 75/7554 | 315819936 | 17 | 631639872 | 73728 | 0 | false | | sys | src_inbox__at__lm__visites | sourcevisite | varchar | 76/7655 | 315819936 | 1 | 631639872 | 73728 | 0 | false | | sys | src_inbox__at__lm__visites | idcampagne | varchar | 77/7761 | 315819936 | 0 | 631639872 | 73728 | 0 | false | | sys | src_inbox__at__lm__visites | prefixe | varchar | 01/00/10064 | 315819936 | 0 | 315819936 | 73728 | 0 | false | | sys | src_inbox__at__lm__visites | libcampagne | varchar | 01/01/10165 | 315819936 | 0 | 631639872 | 73728 | 0 | false | | sys | src_inbox__at__lm__visites | emailing | varchar | 01/02/10270 | 315819936 | 0 | 631639872 | 73728 | 0 | false | | sys | src_inbox__at__lm__visites | dateenvois | varchar | 01/03/10372 | 315819936 | 0 | 631639872 | 73728 | 0 | false | | sys | src_inbox__at__lm__visites | nbpages | int | 01/04/10476 | 315819936 | 4 | 1263279744 | 0 | 0 | false | | sys | src_inbox__at__lm__visites | dureevisite | time | 01/06/10600 | 315819936 | 4 | 1263279744 | 0 | 0 | false | | sys | src_inbox__at__lm__visites | id_unique | int | 01/07/10703 | 315819936 | 4 | 1263279744 | 0 | 0 | false | +--------+----------------------------+---------------------+---------+-------------+-----------+-----------+------------+------------+---------+--------+ heapsize of idvisteur = 3 667 394 560 (3,6GB) instead of 80 449 110 016 (80 GB) my query is present MUCH faster sql>select count(distinct idvisiteur) from src_inbox__at__lm__visites; +----------+ | L1 | +==========+ | 75528081 | +----------+ 1 tuple (4m 22s) HOURA !!! but WHY WHY WHY ? I want to understand 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

3 3

table not unique
by Pierre-Adrien Coustillas 22 Oct '14

22 Oct '14

Hello, (google translate) We had a crash monetdb following a full disk. We had already found that the bug not uniq table ( corrupt catalog ) could be related to these crash . And indeed this command will return no rows before the crash , but 20 lines after the crash : echo "SELECT name FROM SYS.TABLES WHERE system=false;" | mclient base | grep " " | grep -v " name " | grep -v " tuple" | sed 's/[| ]//g' | sort | uniq -c | grep -v " 1 " 5 bdm__lm__agregats 4 bdm__lm__agregats_temp 5 bdm__tra__agregats 4 bdm__tra__agregats_temp 5 ci__data__agregats 6 ci__data__reporting .. ... ... Is the October 2014 release will fix this trouble ? We are Jan2014 - SP3 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

4 3

Drop table not existe
by Pierre-Adrien Coustillas 20 Oct '14

20 Oct '14

Hello, (sorry my english, google translate) When I drop tables that does not exist, the drop may remain in queue. sql>DROP TABLE ci__data__agregats ; DROP TABLE ci__data__reporting ;DROP TABLE ci__data__reporting_ci ; DROP TABLE ci__data__reporting_regle; DROP TABLE ci__data__resultats; DROP TABLE ci__data__resultats_datamart; DROP TABLE ci__data__traductions; DROP TABLE ci__ref__calcul_final; DROP TABLE ci__ref__traductions_agregats; DROP TABLE src__arv__achat; DROP TABLE src__bil__commandes; DROP TABLE src__eve__abo; DROP TABLE src__eve__qlf; DROP TABLE src__eve__vpc; DROP TABLE src__mia__inscrits; DROP TABLE src__sdv__inscrits; operation successful (28.442ms) DROP TABLE: no such table 'ci__data__reporting' DROP TABLE: no such table 'ci__data__reporting_ci' DROP TABLE: no such table 'ci__data__reporting_regle' operation successful (28.490ms) operation successful (28.497ms) operation successful (28.502ms) operation successful (28.507ms) operation successful (28.511ms) DROP TABLE: no such table 'src__arv__achat' DROP TABLE: no such table 'src__bil__commandes' DROP TABLE: no such table 'src__eve__abo' DROP TABLE: no such table 'src__eve__qlf' DROP TABLE: no such table 'src__eve__vpc' DROP TABLE: no such table 'src__mia__inscrits' DROP TABLE: no such table 'src__sdv__inscrits' sql>select * from sys.queue(); +------+---------+----------------------------+----------------------------+----------+---------+-------------------+---------------------------------+ | qtag | user | started | estimate | progress | status | tag | query | +======+=========+============================+============================+==========+=========+===================+=================================+ | 12 | monetdb | 2014-10-17 14:34:16.000000 | 2014-10-17 14:34:16.024000 | null | running | 51539607560@0 | drop table src__arv__achat; | | 13 | monetdb | 2014-10-17 14:34:16.000000 | 6453-11-03 00:20:26.928000 | null | running | 140082976780320@0 | drop table src__bil__commandes; | | 14 | monetdb | 2014-10-17 14:34:16.000000 | null | null | running | 4@0 | drop table src__eve__abo; | | 15 | monetdb | 2014-10-17 14:34:23.000000 | null | null | running | 11776651@0 | select * from sys.queue(); | +------+---------+----------------------------+----------------------------+----------+---------+-------------------+---------------------------------+ 4 tuples (1.504ms) We do a lot of drop followed by create table. We believe that this is related to a concern for duplication in the catalog table ( table xxx is not single , corrupt catalog ? ) 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

3 2

MAL script error
by Hassan, Ahmad 14 Oct '14

14 Oct '14

Hi, One executing the MAL script http://pastebin.com/VUPbhD8V , I get the following errors: !TypeException:user.s0_11[87]:'algebra.join' undefined in: (X_150:any, r1_280:any) := algebra.join(X_137:bat[:oid,:oid], X_149:bat[:oid,:any]) !TypeException:user.s0_11[88]:'algebra.leftfetchjoin' undefined in: X_152:any := algebra.leftfetchjoin(r1_280:any, X_138:bat[:oid,:oid]) !TypeException:user.s0_11[94]:'algebra.join' undefined in: (X_161:any, r1_304:any) := algebra.join(X_152:any, X_160:bat[:oid,:any]) !TypeException:user.s0_11[95]:'algebra.leftfetchjoin' undefined in: X_404:any := algebra.leftfetchjoin(X_150:any, X_135:bat[:oid,:oid]) !TypeException:user.s0_11[100]:'algebra.leftfetchjoinPath' undefined in: X_174:bat[:oid,:int] := algebra.leftfetchjoinPath(X_161:any, X_404:any, X_173:bat[:oid,:any]) !TypeException:user.s0_11[106]:'algebra.leftfetchjoin' undefined in: X_183:any := algebra.leftfetchjoin(r1_304:any, X_182:bat[:oid,:any]) !TypeException:user.s0_11[107]:'algebra.join' undefined in: (X_184:any, r1_343:any) := algebra.join(X_183:any, X_107:bat[:oid,:int]) !TypeException:user.s0_11[109]:'algebra.leftfetchjoin' undefined in: X_187:any := algebra.leftfetchjoin(X_186:bat[:oid,:oid], X_183:any) !TypeException:user.s0_11[110]:'group.subgroupdone' undefined in: (X_188:any, r1_349:any, r2_349:any) := group.subgroupdone(X_187:any) !TypeException:user.s0_11[111]:'algebra.leftfetchjoin' undefined in: X_191:any := algebra.leftfetchjoin(r1_349:any, X_187:any) !TypeException:user.s0_11[113]:'algebra.leftfetchjoinPath' undefined in: X_195:bat[:oid,:lng] := algebra.leftfetchjoinPath(X_186:bat[:oid,:oid], r1_304:any, X_194:bat[:oid,:any]) !TypeException:user.s0_11[114]:'aggr.submin' undefined in: X_196:bat[:oid,:lng] := aggr.submin(X_195:bat[:oid,:lng], X_188:any, r1_349:any, _201:bit) !TypeException:user.s0_11[119]:'algebra.leftfetchjoin' undefined in: X_202:any := algebra.leftfetchjoin(r1_389:bat[:oid,:oid], X_191:any) !TypeException:user.main[2]:'user.s0_11' undefined in: _4:any := user.s0_11() The equivalent SQL query works fine but I am interested in running MAL script via mserver5. How can I resolve the above exceptions please? Thanks.

3 8

Re: Left/Right joins performance - Need help!!
by Vijay Krishna 10 Oct '14

10 Oct '14

Hello Martin, Thanks for the useful tip. On cross checking the trace statements of both inner and left join queries, found out that most of the time taken by the left join query is spent at the following stage. This instance has happened for at least 20 times and each is around 40-60 seconds long while executing a single left/right join query. <<<-------- start quote - sample trace left join query --------->>> 45983604 | X_2138=<tmp_14607>[64851] := bat.append(X_2128=<tmp_14607>:bat[:oid,:str][64851],X_2137=<tmp_14544>:bat[:oid,:str][64851],true); 46443428 | X_3402=<tmp_6631>[64851] := bat.append(X_3390=<tmp_6631>:bat[:oid,:str][64851],X_3401=<tmp_20102>:bat[:oid,:str][64851],true); 53520133 | X_1820=<tmp_12102>[64851] := bat.append(X_1808=<tmp_12102>:bat[:oid,:str][64851],X_1819=<tmp_10437>:bat[:oid,:str][64851],true); 64750174 | X_1864=<tmp_12404>[64851] := bat.append(X_1854=<tmp_12404>:bat[:oid,:str][64851],X_1863=<tmp_21302>:bat[:oid,:str][64851],true); <<<-------- end quote - sample trace left join query --------->>> To my understanding, I think the discrepancy is because of the different approaches on deltas in executing both the queries. Not sure if I'm right. I would also like to know if this large left/inner join discrepancy is a default Monet behaviour or this is just because of some ignorant mistake/misconfigurations of mine (considering my heavy data load). Thanks & Regards, Vijayakrishna.P. Mobile : 9500402305.

1 1

Group by and select distinct
by Vijay Krishna 09 Oct '14

09 Oct '14

Hi, The output of "*select * from table group by col1*" and "*select distinct(col1) from table*" appear to be *same*. But in SQL, group by returns all columns and distinct retrieves only that column values over which the function is ran. Is there any way to retrieve non duplicate rows based on a single column (similar to group by in SQL) in MonetDB? Thanks in advance. With Regards, Vijayakrishna.P. Mobile : 9500402305.

2 1

Left/Right joins performance - Need help!!
by Vijay Krishna 09 Oct '14

09 Oct '14

Hi all, I have been comparing MySQL with MonetDB. Obviously, queries that took minutes in MySQL got executed in a matter of few seconds in Monet. However, I found a real blockade with joins. I have 2 tables - each with 150 columns. Among these (150+150) columns, around 60 are CHARACTER LARGE OBJECT type. Both the tables are populated with around 50,000 rows - with data in all the 150 columns. The average length of data in a CLOB type column is 9,000 (varying from 2 characters to 20,000 characters). The primary key of both the tables have same values and the join is always based on the primary key. The rows are by default inserted in ascending order on the primary key. When I ran *an inner join query on these two tables with about 5 criteria and with limit 1000, Monet processed this query in 5 seconds*, which is completely impressive compared to MySQL's (19 seconds). But when I ran *the same query with same criteria and limits using a left or right joins, Monet took around 5 minutes, which is clearly way behind MySQL's (just 22 seconds)*. Having read a lot about Monet's blinding speed compared to traditional relation row based DBs, I could feel that I am missing something, but couldn't figure out what. Can anyone please tell me why there is such a huge difference in such query execution time and how I can prevent it? Much grateful to have any help. Thanks a lot in advance. P.S.: I am running Monet on Macbook Pro - 2.3 GHz Core i7 - Quad core processor with 8 GB RAM. With Regards, Vijayakrishna.P. Mobile : +91-9500402305.

2 1

MonetDB and clout-backup
by Thomas Yokota 07 Oct '14

07 Oct '14

Hi, I am interested in using MonetDB in my workflow. Currently, I am using SQLite. One reason is that I can store the db one a separate file in my dropbox dir, which allows me to work from multiple computers rather than relying on a VMware-type solution. Can MonetDB give me the same flexibility? If so, is it simply saving the DB into a dir nested within Dropbox dir? Thank you, Thomas

1 0

Can not drop the view
by Radovan Bičiště 06 Oct '14

06 Oct '14

Hello, I'm wondering if there is some hard core way to drop a view. Somehow I have three views that I'm not able to drop. I can issue drop statement several times and it returns success. Here is log from mclient: ceos@lux-drmax-ana:/u01/ceosdata/database/monetdb/drmax/crm$ mclient -d zkdev1 -u drmaxtst password: Welcome to mclient, the MonetDB/SQL interactive terminal (Jan2014-SP2) Database: MonetDB v11.17.17 (Jan2014-SP2), 'mapi:monetdb://lux-drmax-ana:50000/zkdev1' Type \q to quit, \? for a list of available commands auto commit mode: on sql>\dv VIEW drmaxtst.all_constraints VIEW drmaxtst.all_tab_columns VIEW drmaxtst.mmtest_v VIEW drmaxtst.pohyby_prodeje_v VIEW drmaxtst.prijmy_2012_v_mr_tmp VIEW drmaxtst.prijmy_v VIEW drmaxtst.prodeje_2011_v VIEW drmaxtst.prodeje_2012_v VIEW drmaxtst.prodeje_2012_v_mr_tmp VIEW drmaxtst.prodeje_2013_v VIEW drmaxtst.prodeje_2014_v VIEW drmaxtst.prodeje_v VIEW drmaxtst.stav_skladu_historie sql>drop view drmaxtst.prodeje_v; operation successful (3.168ms) sql>drop view drmaxtst.prodeje_v; operation successful (3.004ms) sql>drop view drmaxtst.prodeje_v; operation successful (7.501ms) sql>drop view drmaxtst.prodeje_v; operation successful (4.154ms) sql>drop view drmaxtst.prodeje_v; operation successful (2.351ms) sql>drop view drmaxtst.prodeje_v; operation successful (2.410ms) sql>drop view drmaxtst.prodeje_v; operation successful (2.355ms) sql>drop view drmaxtst.prodeje_v; operation successful (2.685ms) sql>drop view drmaxtst.prodeje_v; operation successful (2.646ms) sql>drop view drmaxtst.prodeje_v; operation successful (2.695ms) sql>drop view drmaxtst.prodeje_v; operation successful (2.390ms) sql>drop view drmaxtst.prodeje_v; operation successful (2.399ms) sql>drop view drmaxtst.prodeje_v; DROP VIEW: unknown view 'prodeje_v' sql>drop view drmaxtst.prodeje_v; DROP VIEW: unknown view 'prodeje_v' sql>\dv VIEW drmaxtst.all_constraints VIEW drmaxtst.all_tab_columns VIEW drmaxtst.mmtest_v VIEW drmaxtst.pohyby_prodeje_v VIEW drmaxtst.prijmy_2012_v_mr_tmp VIEW drmaxtst.prijmy_v VIEW drmaxtst.prodeje_2011_v VIEW drmaxtst.prodeje_2012_v VIEW drmaxtst.prodeje_2012_v_mr_tmp VIEW drmaxtst.prodeje_2013_v VIEW drmaxtst.prodeje_2014_v VIEW drmaxtst.prodeje_v VIEW drmaxtst.stav_skladu_historie sql> I guess my only option is to dump a reload the database. :( Radovan

5 11

any tips to COPY INTO from a csv with whitespace around some DOUBLE fields?
by Anthony Damico 03 Oct '14

03 Oct '14

here's the problem-- Unable to execute statement 'COPY 2626675 OFFSET 2 RECORDS INTO OPPR_ALL_DTL_GNRL_09302014 FROM 'C:\Users\anthonyd\AppData\Local\...'. Server says '!value '1 ' from line 1 field 51 not inserted, expecting type double'. i could import it as VARCHAR and then CAST it back into a DOUBLE type, but i'm wondering if anyone has an easier/more clever way to import this? handling it manually would mean that i cannot use the monet.read.csv() function, so it's a bit more work. thanks!

2 2