- 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