- 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