- users-list - monetdb.org

Plan for feature, external REMOTE table as a plugin (hadoop, jdbc, odbc, etc).
by Aris Setyawan 17 Oct '15

17 Oct '15

Hi, The key value of monetdb is it's parallel query. That many other opensource DBMS doesn't support it. But monetdb have it in the core engine. It is mean a mature feature. Having ability load (on the fly) any other DBMS data using external REMOTE table, and then query them using monetdb parallel query, will attract big community and contribution (as a plugin). The dbms, for example: jdbc, odbc, mysql, postgresql, hadoop, hive, or any commercial dbms. Any dbms which doesn't have parallel query feature, will benefit from this. Any plan for this feature? Regards, Aris

1 0

SQL join involving remote table.
by Aris Setyawan 17 Oct '15

17 Oct '15

Hi, According to https://www.monetdb.org/Documentation/Cookbooks/SQLrecipes/DistributedQuery… It is said that, "Queries involving remote tables are automatically split into subqueries by the master database and executed on remote databases.". I think this scenario: I have 3 server, one master server and two worker servers. I have 2 tables, A (big one, a fact table) and B (small one, a dimension table). A is a merge table of 2 remote tables. The remote table exist in two worker servers. B is a dimension table, that I'm not sure, on which server, I should place it on. And I will do queries, that join A and B. On which server, I should place table B to achieve distributed query processing? Is table B should be replicated on all remote table? Thanks, -Aris

2 2

JDBC Connection to a schema
by Vijay Krishna 16 Oct '15

16 Oct '15

Hi, I am trying to connect a schema within a database directly through JDBC with the following Java code. Connection con = DriverManager.getConnection( > "jdbc:monetdb://localhost/testUser", "monetdb", "monetdb"); > con.setSchema("schema1"); But this throws the following exception. > Exception in thread "main" java.sql.SQLException: syntax error, > unexpected '=' in: "set schema =" > at nl.cwi.monetdb.jdbc.MonetConnection$ResponseList.executeQuery( > MonetConnection.java:2525) > at nl.cwi.monetdb.jdbc.MonetConnection$ResponseList.processQuery( > MonetConnection.java:2273) > at nl.cwi.monetdb.jdbc.MonetStatement.internalExecute( > MonetStatement.java:497) > at nl.cwi.monetdb.jdbc.MonetStatement.execute(MonetStatement.java:338) > at nl.cwi.monetdb.jdbc.MonetStatement.executeUpdate( > MonetStatement.java:533) > at nl.cwi.monetdb.jdbc.MonetConnection.setSchema(MonetConnection.java:1267 > ) > at batcave.Monet.main(Monet.java:11) Is it really possible to connect to a specific schema? Or the only option is to query as "schemaName.tableName" ??? Thanks & Regards, Vijayakrishna.P. Mobile : (+91) 9500402305.

3 3

Baseline Performance Expectation?
by Paul Ramsey 15 Oct '15

15 Oct '15

Hi all, I’ve loaded a relatively small table (I think) of 30M records, 7 columns, and I’m doing some test queries summarizing and filtering the table, like this: select vendor_id, count(*) from taxi where geohash like '90efbc6a6fc%’ group by vendor_id; On my 4-core iMac, the CPU doesn’t get very hot (20% on top, so it’s not even lighting up one of the four CPUs) , but it’s hard to tell since the return time is only 2.5 second. Is that as fast as things will get? I doubled the table size and query times more-or-less doubled. I was just hoping to get query times in the < 1s range. Thanks, P -- Paul Ramsey Solutions Engineer CartoDB—Map Your World's Data 143 Roebling St 2A, New York, 11211, USA T: +1.917.463.3232 Skype: pwramsey3 W: www.cartodb.com Live sessions to learn CartoDB

2 1

Finding the current schema
by Enzo 14 Oct '15

14 Oct '15

Is there a query that would let me know what is the current schema in my db? E.g. I would expect as a response either sys (if I haven't set any schema), or foo (if I've set the schema foo). Kindest regards Enzo enzo(a)smartinsightsfromdata.com

1 0

Support for UUIDs in R and Python
by Doug Service 09 Oct '15

09 Oct '15

Hello everyone, I discovered this morning that the UUID type is not supported in MonetDB.R package on CRAN Error in monetdbRtype(info$types[i]) : Unknown DB type UUID and UUIDs are not support in the Python API which I installed from source File "/usr/local/lib/python3.4/dist-packages/monetdb/sql/pythonize.py", line 132, in convert raise ProgrammingError("type %s is not supported" % type_code) monetdb.exceptions.ProgrammingError: type uuid is not supported Will UUIDs be supported in R and Python in a future release? We use R extensively for statistical analysis on our data and would also like to use Python in certain cases. Doug

1 0

Re UUID and new crash problem
by Doug Service 09 Oct '15

09 Oct '15

Hello Stefan, I modified the uuid.mal file in the lib directory and that all works great for a small sample test from mclient. Thanks! Now I have run into a problem where the server crashes when I try to insert into one of the tables, though inserts in other tables work fine. I do not think this problem has anything with UUIDS as the table I am inserting into does not use UUIDS and I can do inserts into tables that have uuid columns. This insert that causes the crash worked fine on the last version of our schema that did not use UUIDs. I have a small database from msqldump and an insert statement that causes the server to crash consistently. However, I do not see any way to add attachments to a bug report on the tracker. Is there a way to do this? Is there any other information I can collect that would be useful in a bug report? Here is the monetdb log which shows a sigsegv. Doug 2015-10-06 16:02:19 MSG quantbox[3433]: # MonetDB 5 server v11.21.5 "Jul2015" 2015-10-06 16:02:19 MSG quantbox[3433]: # Serving database 'quantbox', using 4 threads 2015-10-06 16:02:19 MSG quantbox[3433]: # Compiled for x86_64-pc-linux-gnu/64bit with 64bit OIDs and 128bit integers dynamically linked 2015-10-06 16:02:19 MSG quantbox[3433]: # Found 1.907 GiB available main-memory. 2015-10-06 16:02:19 MSG quantbox[3433]: # Copyright (c) 1993-July 2008 CWI. 2015-10-06 16:02:19 MSG quantbox[3433]: # Copyright (c) August 2008-2015 MonetDB B.V., all rights reserved 2015-10-06 16:02:19 MSG quantbox[3433]: # Visit http://www.monetdb.org/ for further information 2015-10-06 16:02:19 MSG quantbox[3433]: # Listening for UNIX domain connection requests on mapi:monetdb:///var/lib/monetdb/quantbox/.mapi.sock 2015-10-06 16:02:19 MSG quantbox[3433]: # Start processing logs sql/sql_logs version 52200 2015-10-06 16:02:19 MSG quantbox[3433]: # Start reading the write-ahead log 'sql_logs/sql/log.4' 2015-10-06 16:02:19 MSG quantbox[3433]: # Finished reading the write-ahead log 'sql_logs/sql/log.4' 2015-10-06 16:02:19 MSG quantbox[3433]: # Finished processing logs sql/sql_logs 2015-10-06 16:02:19 MSG quantbox[3433]: # MonetDB/SQL module loaded 2015-10-06 16:02:19 MSG merovingian[809]: proxying client 10.1.10.101:40633 for database 'quantbox' to mapi:monetdb:///var/lib/monetdb/quantbox/.mapi.sock?database=quantbox 2015-10-06 16:02:19 MSG merovingian[809]: target connection is on local UNIX domain socket, passing on filedescriptor instead of proxying 2015-10-06 16:05:19 MSG merovingian[809]: proxying client (local) for database 'quantbox' to mapi:monetdb:///var/lib/monetdb/quantbox/.mapi.sock?database=quantbox 2015-10-06 16:05:19 MSG merovingian[809]: target connection is on local UNIX domain socket, passing on filedescriptor instead of proxying 2015-10-06 16:05:27 MSG merovingian[809]: proxying client (local) for database 'quantbox' to mapi:monetdb:///var/lib/monetdb/quantbox/.mapi.sock?database=quantbox 2015-10-06 16:05:27 MSG merovingian[809]: target connection is on local UNIX domain socket, passing on filedescriptor instead of proxying 2015-10-06 16:05:48 MSG merovingian[809]: database 'quantbox' (3433) was killed by signal SIGSEGV 2015-10-06 16:05:59 MSG merovingian[809]: starting database 'quantbox', up min/avg/max: 0s/0s/0s, crash average: 1.00 0.30 0.10 (3-0=3) 2015-10-06 16:06:00 MSG quantbox[3532]: arguments: /usr/bin/mserver5 --dbpath=/var/lib/monetdb/quantbox --set merovingian_uri=mapi:monetdb://qbdb:42000/quantbox --set mapi_open=false --set mapi_port=0 --set mapi_usock=/var/lib/monetdb/quantbox/.mapi.sock --set monet_vault_key=/var/lib/monetdb/quantbox/.vaultkey --set gdk_nr_threads=4 --set max_clients=64 --set sql_optimizer=default_pipe --set monet_daemon=yes

3 4

Problems implementing java jdbc
by Federico G Agripa 09 Oct '15

09 Oct '15

Hi, i'm having some issues implementing the java jdbc with a grails-app. Grails is a framework that runs on the jvm, allowing you to write all your code in java if you want. I'm using exactly the code you provide on your web page but it isn't recognizing a proper driver to connect to monet at runtime. I also tried to search some repo that provides a plugin or extension that allows me to use but i get the same error (the only one i found was www.clojars.com/monetdb) is there any example of a driver implementation you can provide or any forum to forward this? Thanks in advice

3 3

Grails integration
by Federico G Agripa 09 Oct '15

09 Oct '15

Hello, i'm working on a grails plugin to easily integrate with monetDB. So I would like to here what functionalities will be useful to you guys because I want it to be useful to all of you to. I'm implementing it from the monet jar 2.17 and I hope to have it up in grails plugins as soon as possible. Btw any collaboration will be really welcome Regards

1 0

Regex support
by imad hajj chahine 08 Oct '15

08 Oct '15

Hi, Monetdb is using pcre library for regular expression support, and using this function we can allow SQL to use the regex library: CREATE FUNCTION "pcre_replace" (origin string, pat string, repl string,flags string) RETURNS string EXTERNAL NAME pcre."replace"; I want to use regex for date conversion to the format supported by monetdb (yyyy-MM-dd), I have the following format dd/MM/yyyy, and i am trying to run the following with no success: select pcre_replace('15/10/2015', '^(3[01]|[12][0-9]|0?[1-9])/(1[012]|0?[1-9])/([0-9]{4})$', '\\3-\\2-\\1', 'i') select pcre_replace('15/10/2015', '^(3[01]|[12][0-9]|0?[1-9])/(1[012]|0?[1-9])/([0-9]{4})$', '{3}-{2}-{1}', 'i') select pcre_replace('15/10/2015', '^(?<day>3[01]|[12][0-9]|0?[1-9])/(?<month>1[012]|0?[1-9])/(?<year>[0-9]{4})$', '\\k<year>-\\k<month>-\\k<day>', 'i') My question is, is backreference supported in the replacement string; if yes, whats is the right syntax to use to reference the detected groups, if no, is there a clever way then using substring? Thanks

3 6

windows function
by imad hajj chahine 07 Oct '15

07 Oct '15

Hi, Since monetdb support window RANK functions, is it possible to extend the sql library to support also window aggregate function like SUM, AVG ... useful for running total and moving average. Any plan to support this in the near future or the implementation is easy to be done with user defined functions. Thanks.

2 4

UUIDs in MonetDB
by Doug Service 01 Oct '15

01 Oct '15

The documentation at https://www.monetdb.org/Documentation/Manuals/SQLreference/UUItype indicates that a UUID type is supported in MonetDB and gives the example select uuid(); +--------------------------------------+ | uuid | +======================================+ | 65950c76-a2f6-4543-660a-b849cf5f2453 | +--------------------------------------+ which appears to output from mclient. When I try the same with mclient I receive auto commit mode: on sql>select uuid(); SELECT: no such operator 'uuid' mapi.h only shows support for the following types #define MAPI_AUTO 0 /* automatic type detection */#define MAPI_TINY 1#define MAPI_UTINY 2#define MAPI_SHORT 3#define MAPI_USHORT 4#define MAPI_INT 5#define MAPI_UINT 6#define MAPI_LONG 7#define MAPI_ULONG 8#define MAPI_LONGLONG 9#define MAPI_ULONGLONG 10#define MAPI_CHAR 11#define MAPI_VARCHAR 12#define MAPI_FLOAT 13#define MAPI_DOUBLE 14#define MAPI_DATE 15#define MAPI_TIME 16#define MAPI_DATETIME 17#define MAPI_NUMERIC 18 Is it possible to get MonetDB to generate UUIDs and how is the type supported in mapi. Thanks Doug Service

4 8

R aggregation fails when input is exactly 200K tuples
by Roberto Cornacchia 01 Oct '15

01 Oct '15

Hi, I have a suspect failure at exactly 200K tuples in input. I declared a simple aggregate on two columns (here it doesn't aggregate, for simplicity, it just returns 42). START TRANSACTION; CREATE table test (customer int, d string, n int); INSERT INTO test VALUES(1,'2015-01-01', 100); INSERT INTO test VALUES(1, '2015-01-02', 100); INSERT INTO test VALUES(2, '2015-01-03', 100); INSERT INTO test VALUES(2, '2015-01-01', 100); INSERT INTO test VALUES(2, '2015-01-02', 100); CREATE AGGREGATE sow(d string, n int) RETURNS DOUBLE LANGUAGE R { # aggregation function is constant, not important here sow_aggr <- function(df) { 42.0 } df <- cbind(d,n) as.vector(by(df, aggr_group, sow_aggr)) }; select customer, sow(d,n) from test group by customer; ROLLBACK; +----------+--------------------------+ | customer | L1 | +==========+==========================+ | 1 | 42 | | 2 | 42 | +----------+--------------------------+ The result is what I had expected. That is true until table test is long 199999 tuples. When it's exactly 200000 tuples, I get: Error running R expression. Error message: Error in tapply(seq_len(200000L), list(INDICES = c(0, 1, 2, 3, 4, 5, 6, : arguments must have same length Calls: as.data.frame ... by.data.frame -> structure -> eval -> eval -> tapply I checked the vector aggr_group, and indeed it is not 200000 long, as it should be. Instead, it is just one longer than then number of distinct values for customer (the grouping column). Any thought? Roberto

2 3

COPY INTO string attribute containing single quote, succeed in Jan2014-SP2, fail in Jul2015
by Constantine Feng 30 Sep '15

30 Sep '15

Dear all, I am trying to bulk load the TPC-DS database benchmark into MonetDB. One of the data file "customer.csv" contains data in this format: 27|AAAAAAAALBAAAAAA|111621|835|16096|2450611|2450581|Dr.|Shawn|Prather|N|7|3|1972|PUERTO RICO||Shawn.Prather(a)8BusRYegn6.org|2452353 28|AAAAAAAAMBAAAAAA|889961|897|183480|2452615|2452585|Miss|Edith|Hernandez|Y|17|8|1959|CÔTE D'IVOIRE||Edith.Hernandez(a)BNHL0k.com|2452631 Note in the second row, there is a string attribute containing a single quote. And I use the below command to load it: COPY INTO customer from 'path/to/customer.csv' USING DELIMITERS '|','\n' NULL AS ''; I have two machines with two installations: one installed last year via Ubuntu apt-get, "monetdb version" shows "MonetDB Database Server Toolkit v1.1 (Jan2014-SP2)" the other recently follows the instructions here: https://www.monetdb.org/downloads/deb/ (Ubuntu), "MonetDB Database Server Toolkit v1.1 (Jul2015)" Somehow the loading succeeded in the former but failed in the latter. Is it due to some version difference? How can I fix it? Error: field 15 expecting varchar(15) 'CÔTE D'IVOIRE' Thanks in advance. - Neo

2 1

Strange characters with ODBC driver
by Dennis Pallett 28 Sep '15

28 Sep '15

Hi all, We're trying to use MonetDB together with SAP Business Objects using the ODBC driver but we're getting some strange results when using the table browser. The names of the schema's seem to be all mangled up (see attached screenshot). Any ideas what might be wrong? It works fine with the JDBC driver btw. Any advantages/disadvantages with both drivers? Best regards, Dennis Pallett

2 2

Version vs release naming schemes
by Eyal Rozenberg 28 Sep '15

28 Sep '15

What's the relation between the Month+Year release naming scheme (e.g. Jul2015, Oct2014-SP2) and the numeric versions (e.g. 11.19.9)? The website has a release history page: https://www.monetdb.org/Documentation/UserGuide/ReleaseNotes But I haven't found a page discussing the numeric version numbers. I'm not even sure the X.Y.Z format means version.subversion.subsubversion ... Eyal

2 1

Jul2015 not availbale for debian Wheezy
by imad hajj chahine 28 Sep '15

28 Sep '15

Hi, I am trying to upgrade my installation on debian from 11.19 to 11.21, i cant find the Jul2015 in the https://www.monetdb.org/downloads/deb/dists/wheezy/monetdb/binary-amd64/ and the apt-get is pointing to the 11.19.15 version, any idea how to get the latest release. Thank you

2 1

Issue importing certain character combinations
by andy.barlow＠datum360.com 25 Sep '15

25 Sep '15

I filed a bug earlier, the gist of which is below but perhaps there is some setting or flag I am missing when importing? If your import file contains a number of character combinations, such as \560, \561, \700 and probably lots of others, the copy into fails with a message like: Failed to import table line 5 field 1 'varchar(20)' expected in 'FIVE\700' Reproducible: Always Steps to Reproduce: 1. create table test (test varchar(20)); 2. create a load file test.txt with the following: ONE TWO THREE FOUR FIVE\700 3. copy into test from 'test.txt' using delimiters '\t','\n'; 4. fails with message: Failed to import table line 5 field 1 'varchar(20)' expected in 'FIVE\700' Actual Results: Failed to import table line 5 field 1 'varchar(20)' expected in 'FIVE\700' Expected Results: should import OK, the character combinations are valid Andy Barlow Datum360

1 1

Re: Disk space running out
by scArcer 24 Sep '15

24 Sep '15

"create table" and "insert data" are separate. And the disk is consumed by queries. because when I finished data loading, it was, only 3~4G. Thank you for the link. I will try restart server to see if monetdb can unlink these files too. I execute queries in auto commit mode, should I control transactions myself? ------------------ Original ------------------ From: "Guillaume Savary";<guillaume.savary(a)securactive.net>; Date: Thu, Sep 24, 2015 02:30 PM To: "Communication channel for MonetDB users"<users-list(a)monetdb.org>; Cc: "scArcer"<53308768(a)qq.com>; Subject: Re: Disk space running out Hi, Are you repeating this "create table + insert data" multiple times on this disk? Because there is a bug on the BAT files which are never freed : https://www.monetdb.org/bugzilla/show_bug.cgi?id=3802 Work around : try to do the "create table" in an other transaction than the "insert data". Le 24/09/2015 08:17, scArcer a écrit : Here is my situation. I create a table A, load about 3~4G data into it. Then a lot of queries are executed, making it using disk up to 1T. [hadoop@falco001-bi-30-141 data3]$ du -sh monetdb/ 1.1T monetdb/ and when I try to execute new query, I got this error: sql>select * from ods.attr_user_c_profile limit 10; could not allocate space for GDKsave: error -1 on: name=06/62/66261, ext=theap, mode=0 !OS: No space left on device HEAPextend: failed to extend to 983040 for 06/62/66261.theap: h->storage == STORE_MEM && can_map && fd >= 0 && HEAPsave_intern() < 0 ------------------ Original ------------------ From: "Martin Kersten";<martin.kersten(a)cwi.nl>; Date: Thu, Sep 24, 2015 02:03 PM To: "users-list"<users-list(a)monetdb.org>; Subject: Re: Disk space running out On 24/09/15 06:04, scArcer wrote: > Hi, I'm facing the problem of disk space running out. > Since monetdb will store the query intermediates into the disk when the memory is not enough to hold them all, I'm wondering that does monetdb will automatically free the disk space? > And if it doesn't, how can I do it manually? > > Hi It will release disk space as soon as possible. > _______________________________________________ > 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 _______________________________________________ users-list mailing list users-list(a)monetdb.org https://www.monetdb.org/mailman/listinfo/users-list -- Guillaume Savary Securactive R&D

1 0

Disk space running out
by scArcer 24 Sep '15

24 Sep '15

Hi, I'm facing the problem of disk space running out.Since monetdb will store the query intermediates into the disk when the memory is not enough to hold them all, I'm wondering that does monetdb will automatically free the disk space? And if it doesn't, how can I do it manually?

3 4

monetdb cluster on EC2
by imad hajj chahine 23 Sep '15

23 Sep '15

Hi, As per this link https://www.monetdb.org/pipermail/users-list/2013-August/006744.html, to be able to have monetdb cluster to work in ec2, a VPN must be used to connect the nodes. Following this link: https://www.buckhill.co.uk/blog/how-to-enable-broadcast-and-multicast-on-am…, i configured the VPN between the cluster nodes. Now for each node I have the eth0 and edge0 network interface, when i start monetdbd it directly bind to the eth0 interface, thats why i suppose the discover is not working. Any idea how to start monetdbd and bind to the interface used for VPN to be able to use TCP broadcast hence discover the other nodes. Any help will be appreciated. Thank You.

1 0

if i run these commands in R, mserver dies.. any ideas how to fix? thanks
by Anthony Damico 22 Sep '15

22 Sep '15

# MonetDB 5 server v11.21.3 "Jul2015" # Serving database 'marketscan', using 4 threads # Compiled for x86_64-pc-winnt/64bit with 64bit OIDs dynamically linked # Found 64.000 GiB available main-memory. # Copyright (c) 1993-July 2008 CWI. # Copyright (c) August 2008-2015 MonetDB B.V., all rights reserved # Visit http://www.monetdb.org/ for further information # Listening for connection requests on mapi:monetdb://127.0.0.1:49700/ # Start processing logs sql/sql_logs version 52200 # Start reading the write-ahead log 'sql_logs\sql\log.211' # Finished reading the write-ahead log 'sql_logs\sql\log.211' !ERROR: log_sequence_: write failed # Finished processing logs sql/sql_logs # MonetDB/SQL module loaded > ========================= > pid <- monetdb.server.start( batfile ) > dbname <- "marketscan" > dbport <- 49700 > monet.url <- paste0( "monetdb://localhost:" , dbport , "/" , dbname ) > db <- dbConnect( MonetDB.R() , monet.url , wait = TRUE ) Server not ready(cannot open the connection), retrying (ESC or CTRL+C to abort) > dbListTables(db) Error in .local(conn, statement, ...) : Unable to execute statement 'select schemas.name as sn, tables.name as tn from sys.tables join sys.schemas on tables.schema_id=sc...'. Server says 'ERROR: BBPdir_subcommit: Syncing BBP.dir file failed' [#GDKerror:]. > dbListTables(db) Error in .local(conn, statement, ...) : Unable to execute statement 'select schemas.name as sn, tables.name as tn from sys.tables join sys.schemas on tables.schema_id=sc...'. Server says 'ERROR: GDKsave: error on: name=06\651, ext=timprints, mode=0' [#GDKerror:]. > dbListTables(db) [1] "ccaea" "ccaed" "ccaef" "ccaei" "ccaeo" [6] "ccaep" "ccaes" "ccaet" "redbook" "state" [11] "wgt" "ccaea_st_wt" "ccaet_st_wt" "ccaes_monthly" "ccaes_annual" [16] "ccaeo_monthly" "ccaeo_annual" "ccaed_monthly" "ccaed_annual" "ccaes_admmon_xwalk" [21] "ccaes_monthly_wxw" "ccaed_svcmon_xwalk" "ccaed_monthly_wxw" "ccaeo_svcmon_xwalk" "ccaeo_monthly_wxw" [26] "acons_pre" "mcons_pre" "ccs" "acons_diag" "mcons_diag" [31] "readm" "acons_readm" "mcons_readm" "mcons" "acons" > > > # create a temporary person-year table > dbSendQuery( + db , + 'CREATE TABLE temp AS + SELECT + datatyp , + yr , + enrolid , + SUM( wgt ) AS sum_wgt , + SUM( CAST( ( rx_pay ) > 250 AS INTEGER ) ) AS rx_pay_above_250 , + SUM( CAST( ( rx_copay + rx_coins + rx_deduct ) > 250 AS INTEGER ) ) AS rx_copay_coins_deduct_above_250 + FROM + mcons + WHERE + datatyp = 1 + GROUP BY + datatyp , yr , enrolid + ORDER BY + datatyp , yr , enrolid + WITH DATA' + ) Error in readBin(con, "integer", n = 1, size = 2, signed = FALSE, endian = "little") : error reading from the connection > # # # # mserver is dead

2 2

Monetdb clusters VS Distributed Query Processing
by imad hajj chahine 22 Sep '15

22 Sep '15

Hi, I am trying to understand the main difference between clusters and the remote table introduced in JUL2015 . Correct me if I am wrong, in clusters the complete table is copied over all nodes, and monetdb will do the query parallel execution on the nodes and result concat. In the remote table I have more control on executing the query on a part of a table on a selected node depending on resources availables. If this is true, are the following point valid: 1. its better to have identical nodes for clusters 2. the execution time is inversely proportional to the number of nodes in the clusters 3. the executing time is dictated by the slowest node in cluster 4. fail-over is by default supported in cluster configuration One last question about the supported queries in these 2 modes(groupby, order, limit). if any, a reference to a documentation about the clustering is appreciated. Thank you.

2 1

Two questions about R integration
by Roberto Cornacchia 22 Sep '15

22 Sep '15

Hi, I have 2 questions about R integration. 1) I see that missing packages are automatically installed when invoked by library() calls. That is really cool. The question: if I need a library in a function, will this actually be loaded at every function call? 2) How is a SQL type date translated to R? Apparently not "Date", because I tried to use it as such in the declared procedure and I got a complaint. I tried to figure the type myself with this: sql> create function dt(d date) returns string language R { class(d) }; sql> select dt( cast('2015-09-21' as date) ); but unfortunately this triggered a server assertion :/ mserver5: .../src/monetdb5/mal/mal_interpreter.c:648: runMALsequence: Assertion `t == stk->stk[a].vtype' failed. Thanks, Roberto

2 2

sys.analyze not working
by Robert Koch 19 Sep '15

19 Sep '15

Following the manual https://www.monetdb.org/Documentation/Cookbooks/SQLrecipes/statistics I am trying to reproduce this, but it fails with a type conversion error: sql>call sys.analyze('sys', 'types'); conversion of string 'sys' to type int failed. sql>SELECT * FROM sys.statistics; +-----------+------+-------+-------+--------+-------+--------+------+--------+--------+ | column_id | type | width | stamp | sample | count | unique | nils | minval | maxval |> +===========+======+=======+=======+========+=======+========+======+========+========+ +-----------+------+-------+-------+--------+-------+--------+------+--------+--------+ 0 tuples (3.013ms) !1 column dropped! Tried it with different schema/table/column names. Both with system and user-generated tables. Always the same error. Running 11.21.5 here. Thanks.

3 2

couple of unanswered qs
by Anthony Damico 18 Sep '15

18 Sep '15

https://stackoverflow.com/questions/tagged/monetdb

1 0

Why doesn't MonetDB exploit SIMD feature?
by Xu,Wenjian 18 Sep '15

18 Sep '15

Hi, We know that MonetDB excels in application where the database hot-set can be largely held in main-memory, since the physical operators (e.g., hashjoin, sort) are highly optimized for main-memory. But it is strange to me that these operators do not utilize SIMD which a performance-critical feature of modern CPUs. For example, MonetDB use *timsort* as its underlying stable sort algorithm (do_ssort() in gdk/gdk_ssort_impl.h), but I cannot find any SIMD instructions there. Why doesn't MonetDB exploit SIMD feature? or did I miss something? In paper 'Vectorwise: Beyond Column Stores', the authors claim that X100/Vectorwise use SIMD instructions. So my follow-up question is that whether any of X100/Vectorwise's technologies/features has been integrated into MonetDB's code base. Thank you. Best regards, Xu, Wenjian

2 1

Encrypted Client Sessions
by James Candalino 17 Sep '15

17 Sep '15

Hi all, Is it possible to establish encrypted client sessions? If so, how? I didn't see anything in the documentation. With postgres connecting using jdbc is just jdbc:postgresql://dbhost/dbname?ssl=true. Any insight would be greatly appreciated. --James -- *NOTICE TO RECIPIENTS*: This communication is confidential and intended for the use of the addressee only. If you are not an intended recipient of this communication, please delete it immediately and notify the sender by return email. Unauthorized reading, dissemination, distribution or copying of this communication is prohibited. This communication does not constitute an offer to sell or a solicitation of an indication of interest to purchase any loan, security or any other financial product or instrument, nor is it an offer to sell or a solicitation of an indication of interest to purchase any products or services to any persons who are prohibited from receiving such information under applicable law. The contents of this communication may not be accurate or complete and are subject to change without notice. As such, Orchard App, Inc. (including its subsidiaries and affiliates, "Orchard") makes no representation regarding the accuracy or completeness of the information contained herein. The intended recipient is advised to consult its own professional advisors, including those specializing in legal, tax and accounting matters. Orchard does not provide legal, tax or accounting advice.

1 0

Accessing admin GUI
by Peter Smith 17 Sep '15

17 Sep '15

Hi, https://www.monetdb.org/XQuery/Tutorials/adminGUI It looks as if the admin GUI should automatically start up on 50001/TCP when I start MonetDB, but this doesn't seem to be the case: I have monetdb listening on 50000, but nothing listening on 50001. I'm running MonetDB 11.19 on Ubuntu. I'm using an init.d script to start MonetDB, and here's the running process: /usr/bin/mserver5 --dbpath=/home/administrator/monetdb/xxx/xxx --set merovingian_uri mapi:monetdb://127.0.0.1:50000/xxx --set mapi_open false --set mapi_port 0 --set mapi_usock /home/administrator/monetdb/xxx/xxx/.mapi.sock --set monet_vault_key /home/administrator/monetdb/xxx/xxx/.vaultkey --set gdk_nr_threads 16 --set max_clients 64 --set sql_optimizer default_pipe --set monet_daemon yes Do I need to pass extra start-up options to enable the GUI? (I don't see anything in the mserver5 man page) Thanks, Peter Smith

2 1

What is the difference ?
by Brian Hood 16 Sep '15

16 Sep '15

Hi All, So the new CREATE REPLICA TABLE definition / CREATE TABLE definition i'm not sure what the difference would be ? CREATE REPLICA TABLE test2 AS (SELECT * FROM test) WITH DATA; CREATE TABLE test2 AS (select * from test) WITH DATA; Regards, Brian Hood

2 1

Inserting in 'raw' mode??
by Vijay Krishna 15 Sep '15

15 Sep '15

Hi, I was analysing the Insert performance of MonetDB... I am trying to optimize a process where in a single tuple has to be inserted into a table of 1200 columns, which takes about 7.5 seconds presently... The query has values for only 5 columns out of 1200, yet the speed is still the same.. Is it possible to reduce this time even?? While digging on this, I came across this thread.. https://www.monetdb.org/pipermail/users-list/2005-August/000139.html *What is the insert speed relatively to other DBs? > * > Using SQL, with the current version we won't compete very well. We get > much more acceptable speeds in 'raw' mode, where lots of the parsing > overhead is removed. > > Can anyone please help me understand what is the 'raw' mode that is explained in this thread?? I am using Oct-2014 version on a CentOS machine with 128 GB RAM... Thanks & Regards, Vijayakrishna.P. Mobile : (+91) 9500402305.

3 2

DbVisualizer support for MonetDB
by Robert Koch 14 Sep '15

14 Sep '15

Hi Folks, DbVisualizer is my preferred GUI to MonetDB. DbVis offers capability to write own database profiles for specific DB's. Today I committed a MonetDB database profile for DbVisualizer. https://github.com/dbvisualizer/dbvis-database-profiles/tree/master/monetdb Anyone familiar to DbVis can import and/or improve this profile. There's a free DbVis version, but the Pro offers much more features, which make life easier. BTW, I'm not working for them. :-) Please feel free to write me your desired features by mail or directly into the MD-file inside the monetdb-directory. If possible, I will implement it inte the profile. Best regards Mario

1 0

`/usr/bin/mserver5': double free or corruption
by Brian Hood 10 Sep '15

10 Sep '15

Hi Guys, I created a brand new directory for my dbfarm yesterday and as you can see everything was fine for 24 hours then my merovingian.log shows an error. My Compile options are: ./configure --libdir=/usr/lib64 --prefix=/usr --sysconfdir=/etc --localstatedir=/var --disable-assert \ --disable-testing --enable-optimize --enable-rintegration --enable-jsonstore http://pastebin.com/qAebcMhp I'm currently only inserting to one table. DROP TABLE "threatmonitor".http_traffic_json; CREATE TABLE "threatmonitor".http_traffic_json ( id INT GENERATED ALWAYS AS IDENTITY ( START WITH 0 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 2 CYCLE ) primary key, guid char(36), recv_date date, recv_time time, json_data JSON ); Typical insert INSERT INTO http_traffic_json (recv_time,recv_date,guid,json_data) VALUES (NOW(), NOW(), '1f248d6e-ed15-2a3e-a08f-96536ed925d0', '{"http":{"host":"ad.sxp .smartclip.net","connection":"keep-alive","cachecontrol":"max-age=0","accept":"image/webp,*/*;q=0.8","useragent":"Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/4 2.0.2311.90 Safari/537.36","referer":" http://cdn.turn.com/server/ddc.htm?uid=8915259122209287941&rnd=246995741890…","acceptencoding":"gzip, defla te, sdch","acceptlanguage":"en-GB,en-US;q=0.8,en;q=0.6","cookie":"ommited"}}'); I will look more at this tomorrow and try and reproduce. Regards, Brian Hood

1 0

Issue upgrading from 11.17.9 to 11.21.5
by Brian Hood 09 Sep '15

09 Sep '15

Hi All, I am unsure what to do in this scenario. Can anyone help ? http://pastebin.com/LwLCLeAB 1. 2015-09-08 13:40:07 MSG threatmonitor[31747]: # Listening for UNIX domain connection requests on mapi:monetdb:///data2/threatmonitor/threatmonitor/.mapi.sock 2. 2015-09-08 13:40:07 MSG threatmonitor[31747]: # Start processing logs sql/sql_logs version 52200 3. 2015-09-08 13:40:07 ERR threatmonitor[31747]: !FATAL: Incompatible database version 052001, this server supports version 052200. 4. 2015-09-08 13:40:07 ERR threatmonitor[31747]: Maybe you need to upgrade to an intermediate release first. 5. 2015-09-08 13:40:07 ERR threatmonitor[31747]: 6. 2015-09-08 13:40:07 MSG merovingian[31739]: database 'threatmonitor' (31747) has exited with exit status 1 7. 2015-09-08 13:40:07 ERR control[31739]: (local): failed to fork mserver: database 'threatmonitor' appears to shut itself down after starting, check monetdbd's logfile for possible hints Regards, Brian Hood

2 5

Announcement: New Jul2015 Feature release of MonetDB suite
by Sjoerd Mullender 09 Sep '15

09 Sep '15

The MonetDB team at CWI/MonetDB BV is pleased to announce the Jul2015 feature release of the MonetDB suite of programs. More information about MonetDB can be found on our website at <http://www.monetdb.org/>. For details on this release, please see the release notes at <http://www.monetdb.org/Downloads/ReleaseNotes>. As usual, the download location is <http://dev.monetdb.org/downloads/>. Jul 2015 feature release MonetDB5 Server * Implemented batcalc.min and batcalc.max. Made calc.min and calc.max generic so that no other implementations are needed. * Removed function batcalc.ifthen. Build Environment * Upgraded the license to the Mozilla Public License Version 2.0. Client Package * Added a new output format to mclient: --format=expanded (or -fx). In this format, column values are shown in full and below each other. MonetDB Common * BUNtvar and BUNhvar macros no longer work for TYPE_void columns. * Changed interfaces of a lot of GDK-level functions. When they modify a BAT, don't return the same BAT or NULL, but instead return GDK_SUCCEED or GDK_FAIL. * Changed a bunch of hash-related functions to work on the tail column. The functions that have been changed to work on the tail column are: BAThash, BATprepareHash, HASHgonebad, HASHins, and HASHremove. Java Module * Improved JDBC driver to not throw NullPointerException anymore when calling isNullable() or getPrecision() or getScale() or getColumnDisplaySize() or getSchemaName() or getTableName() or getColumnClassName() on a ResultSetMetaData object. * We now compile the Java classes using the latest Java 1.8 version, and we tell it to compile for Java 1.7. SQL * Differences between time, timestamp, and date values now return properly typed interval types (second or month intervals) instead of integers. * Added support for 128-bit integers (called HUGEINT) on platforms that support this. * Removed support for the mseed library. * Removed support for RDF. * Removed DataCell. It was experimental code that was never enabled. Bug Fixes * 2618: Implement master slave scheme * 3361: constants as MAL function parameters prevent intermediate reuse * 3364: Cannot set role back to a user's default role * 3365: Unable to grant object privileges while having a non-default current_role * 3440: Sequence type errors * 3449: mserver crash on start - Freebsd 10 amd64 * 3476: Cannot revoke object access * 3496: autocompletion table names does not work correctly * 3556: when 2 multiplexed functions in MAL plan, only one is mapped correctly to bat<mod>.function primitive * 3564: Request: add support for postgresql specific scalar function: split_part(string text, delimiter text, field int) * 3625: SIGSEGV because mat array can overrun in opt_mergetable.c * 3627: SQRT in CASE does not work as of Oct2014 * 3654: configure --enable-fits requires extra commands after creating a database instance * 3673: mclient 'expanded' row formatter * 3674: Obfuscate event tracing * 3679: No error is given when incorrect timezone value is specified for a timetz column * 3686: Wrong associativity of multiply/divide * 3702: Filter function not found if created in a user schema * 3708: wrong scoping for cross-schema view references * 3716: alter table my_merge_table drop table t1; crashes mserver5 with Segmentation fault * 3724: Wrong size calculation in BATsubjoin * 3730: SAMPLE function not sampling randomly * 3732: memory leak (of InstrRecord) in opt_mergetable * 3733: "(TRUE OR <Exp>) AND <Exp>" is evaluated incorrectly * 3735: python connection with unix_socket * 3736: crash if mclient disconnects abruptly during a query * 3738: Database inconsistency when using savepoint * 3739: CASE statements do not handle NULLs in the IN () operator properly * 3740: select epoch(now()); types timestamptz(7,0) and bigint(64,0) are not equal * 3742: Division By Zero * 3744: cast to int gives different results for decimal than double * 3747: joins fail in the presence of nulls * 3748: Missing META-INF/services/java.sql.Driver in JDBC package * 3753: Hang on json field parsing * 3754: select from a REMOTE TABLE referring local table crashes mserver5 * 3756: column type conversion sticks to subsequent queries * 3758: "COPY INTO ..." doesn't work, if executing from 2 processes concurrently. * 3759: select data from "sys"."rejects" returns unexpected error and when next select data from "sys"."sessions" causes an assertion failure in mal_interpreter.c:646. * 3760: SQL parser has problem with (position of) a scalar subquery in a SELECT-list * 3761: SQL executor has problem with (position of) a subquery in a SELECT-list. Inconsistent behavior. * 3763: JDBC PreparedStatement for a table with 14 Foreign Keys crashing the Database * 3764: DROPping multiple users causes a crash * 3765: Re-granting a revoked privilege does not work * 3766: VIEW not visible if created under a different schema * 3767: CREATE TEMP TABLE using "LIKE" incorrectly handled * 3769: SIGSEGV when combining a cast/column alias with a UNION ALL view * 3770: combined conditions on declared table in User Defined Function definition crashes monetdb * 3771: Owner of the schema loses rights if assumes the monetdb role. * 3772: Any user can grant a role. * 3773: quantile(col, 0) and quantile(col, 1) fail * 3774: mclient is unaware of merge tables and remote tables * 3775: COPY INTO: Backslash preceding field separator kills import * 3778: Crash on remote table schema mismatch * 3779: server crashes on MAX() on SELECT DISTINCT something combo * 3783: Behavioural change in Jul2015 for 'timestamp minus timestamp' * 3784: Assertion failed: (bn->batCapacity >= cnt), function BAT_scanselect, file gdk_select.c, line 1008. * 3785: sum(interval) causes overflow in conversion to bte * 3786: ResultSet.close() never sends Xclose to free resources * 3787: "b and g must be aligned" from complex group/union query * 3791: HEAPextend: failed to extend to 2420077101056

3 3

Jul2015: access denied to sys.tables from custom user/schema
by Roberto Cornacchia 02 Sep '15

02 Sep '15

Hi there, Is it a Jul2015 feature that access to sys.tables is denied to custom a user/schema? However, sys._tables is accessible. That sounds strange. Roberto

3 4

I love Jul2015!
by Roberto Cornacchia 01 Sep '15

01 Sep '15

Not about issues this time. I just wanted to thank the team for this new upcoming release, I'm using it for a while already and it seems to be brilliant. I particularly like that hash tables are kept around, making a hot DB solve in *microseconds* joins that would take almost half a *second* otherwise. Ka-Boom!

3 2

Release Jul2015
by Luciano Sasso 31 Aug '15

31 Aug '15

Jul2015 is stable version ? This master/slave(master pull data for salave automatic) features this release Jul2015? -- Luciano Sasso Vieira Data Scientist & Solutions Architect luciano(a)gsgroup.com.br <http://www.gsgroup.com.br> | tel: 17 3353-0833 | cel: 17 99706-9335 www.gsgroup.com.br <http://www.gsgroup.com.br> --- Este email foi escaneado pelo Avast antivírus. https://www.avast.com/antivirus

2 1

Information for Datastorage, Indizes and Query optimization
by Dennis Wiest 31 Aug '15

31 Aug '15

Hello there, there is detailed information about MonetDB with respect to the data storage, indexing and query optimization. I think, unfortunately, on the website is not very much. Special for the Internal index and query optimization. Can you help me there? I need that for my work. greetings Wiest Dennis ________________________________ Avitech GmbH Principal Office: Bahnhofplatz 1 | 88045 Friedrichshafen | Germany Court Registration: Amtsgericht Ulm | HRB 728293 Gesch?ftsf?hrer/Managing Director: Antonio Maria Gonzalez Gorostiza VAT No.: DE223719716 http://avitech.aero

3 4

dplyr & monetdb - appropriate syntax for querying schema.table?
by Enzo 31 Aug '15

31 Aug '15

I’ve just posted the following on stackoverflow. Any help is welcome! In monetdb I have set up a schema main and my tables are created into this schema. For example, the department table is main.department. With dplyr I try to query the table: mdb <- src_monetdb(dbname="model", user="monetdb", password="monetdb") tbl(mdb, "department") But I get Error in .local(conn, statement, ...) : Unable to execute statement 'PREPARE SELECT * FROM "department"'. Server says 'SELECT: no such table 'department'' [#42S02]. I tried to use "main.department" and other similar combinations with no luck. What is the appropriate syntax? Enzo enzo(a)smartinsightsfromdata.com

2 1

password issue with Jul2015 Feature release of MonetDB suite
by Enzo 29 Aug '15

29 Aug '15

I do not seem to be able to log in R: the following was working before, but now it doesn’t. aconn <- dbConnect(MonetDB.R(), "monetdb://localhost:50000/ClevelandBaseModel", user= "monetdb", password="monetdb”) Error in .mapiAuthenticate(socket, dbname, user, password, language = language) : Authentication error: !InvalidCredentialsException:checkCredentials:invalid credentials for user ‘monetdb’ Has the password been changed?? Enzo enzo(a)smartinsightsfromdata.com

1 1

issue granting administrator rights
by Enzo 28 Aug '15

28 Aug '15

I have successfully managed to configure multi-user access to my user defined schema. All the users are granted monetdb “rights with: GRANT monetdb TO user1 Once logging the new user has ability to SELECT / DROP / INSERT etc. in the user schema. Sadly at the time of executing the MonetDB.R command: tStatus <- dbWriteTable(conn, tPFile, dataDt,overwrite=TRUE, csvdump=TRUE) I’ve got: Unable to execute statement 'COPY OFFSET 2 INTO demand FROM '/Users/xxx/demand.csv' USIN...'. Server says '!COPY INTO: insufficient privileges: COPY INTO from file(s) requires database administrator rights, use 'COPY INTO "demand" FROM STDIN' instead' So clearly GRANT monetdb TO user1 is not enough to empower user1. I tried: GRANT monetdb to “user1" WITH ADMIN OPTION; But at this point after: SET SCHEMA cleveland; SET ROLE monetdb; I get error "Role (monetdb) missing” It does not seem correct to me: surely it should be possible to grant rights to a user at the same level of “monetdb”, including administrator rights? Enzo enzo(a)smartinsightsfromdata.com

1 0

Re: any idea what's going on here? it was created under the previous version but now isn't loading after upgrade
by Niels Nes 28 Aug '15

28 Aug '15

On Fri, Aug 28, 2015 at 06:29:38AM -0400, Anthony Damico wrote: > hi, thanks, here's the file from > > R:\American Community Survey\MonetDB\acs\bat\BACKUP\BBP.dir So the db upgrade did create the sql_dsnapshots bat, but its not part of the sql catalog. Fixed a bug in the code for this now. Niels > > On Fri, Aug 28, 2015 at 5:56 AM, Niels Nes <Niels.Nes(a)cwi.nl> wrote: > > On Thu, Aug 27, 2015 at 02:09:22PM -0400, Anthony Damico wrote: > > # MonetDB 5 server v11.21.3 "Jul2015" > > # Serving database 'acs', using 4 threads > > # Compiled for x86_64-pc-winnt/64bit with 64bit OIDs dynamically > linked > > # Found 64.000 GiB available main-memory. > > # Copyright (c) 1993-July 2008 CWI. > > # Copyright (c) August 2008-2015 MonetDB B.V., all rights > reserved > > # Visit http://www.monetdb.org/ for further information > > # Listening for connection requests on mapi:monetdb:// > 127.0.0.1:50001/ > > # Start processing logs sql/sql_logs version 52200 > > !FATAL: Logger_new: BBPrename to sql_dsnapshots failed > > Press any key to continue . . . > > > _______________________________________________ > > users-list mailing list > > users-list(a)monetdb.org > > https://www.monetdb.org/mailman/listinfo/users-list > > Anthony > > As the server doesn't successfully start we need to see why the > rename > fails. Could you check the BBP.dir file (usualy in the bat > directory of > your dbpath, sometimes in the BACKUP directory there), and see > if a bat with this name exists (sql_dsnapshots). > > Niels > > > -- > Niels Nes, Manager ITF, Centrum Wiskunde & Informatica (CWI) > Science Park 123, 1098 XG Amsterdam, The Netherlands > room L3.14, phone ++31 20 592-4098 sip:4098@sip.cwi.nl > url: https://www.cwi.nl/people/niels e-mail: Niels.Nes(a)cwi.nl > _______________________________________________ > users-list mailing list > users-list(a)monetdb.org > https://www.monetdb.org/mailman/listinfo/users-list > > -- Niels Nes, Manager ITF, Centrum Wiskunde & Informatica (CWI) Science Park 123, 1098 XG Amsterdam, The Netherlands room L3.14, phone ++31 20 592-4098 sip:4098@sip.cwi.nl url: https://www.cwi.nl/people/niels e-mail: Niels.Nes(a)cwi.nl

3 2

any idea what's going on here? it was created under the previous version but now isn't loading after upgrade
by Anthony Damico 28 Aug '15

28 Aug '15

# MonetDB 5 server v11.21.3 "Jul2015" # Serving database 'acs', using 4 threads # Compiled for x86_64-pc-winnt/64bit with 64bit OIDs dynamically linked # Found 64.000 GiB available main-memory. # Copyright (c) 1993-July 2008 CWI. # Copyright (c) August 2008-2015 MonetDB B.V., all rights reserved # Visit http://www.monetdb.org/ for further information # Listening for connection requests on mapi:monetdb://127.0.0.1:50001/ # Start processing logs sql/sql_logs version 52200 !FATAL: Logger_new: BBPrename to sql_dsnapshots failed Press any key to continue . . .

2 1

Multiple MonetDB nodes behind HAProxy loadbalancer
by Dennis Pallett 20 Aug '15

20 Aug '15

Hi all, We're thinking about running multiple MonetDB servers with the same databases behind a single HAProxy loadbalancer (see attached diagram). Would this be a good idea? bad idea? Any experiences with this kind of setup? We are thinking about this route primarily so that we can bring a new dataset online during business hours without disrupting end-users who are running queries from our application. An additional benefit would be the ability to handle more load and enable horizontal scalability. Any insights would be much appreciated! Best regards, Dennis

3 3

Jul2015 Release Candidate
by Sjoerd Mullender 19 Aug '15

19 Aug '15

-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA256 I have created a release candidate for the Jul2015 release. The candidate is available at http://dev.monetdb.org/downloads/testing/. If all goes well, this candidate will morph into a release sometime soon(ish). In the mean time, you can use this release candidate and test to see whether it works. And if it doesn't work, I would really like to hear about it. - -- Sjoerd Mullender -----BEGIN PGP SIGNATURE----- Version: GnuPG v2 iQEcBAEBCAAGBQJVyKZgAAoJEISMxT6LrWYgE28H/2rudFXhcIfNgnX4yYJlpRT5 eI2Dzp9kShEmRibvNkWvCEF1j/qyWgQhx/jlHvucuEF/0k+U+7NYuK0vhzA0llyJ hspMkQ73UDXeVO0/SivtNYb40UpMyUQoXC7nSaSneglVDe4JcX4S3Y5GvHPByiqz 3UNMGDARfWlqPcbTtW57XfLejK/sd8nNZ3mGQuerVqEvMcKZaZIBSXjg/16WQVsN DMbPacPpu3yFeWQQtP2er6SL3UdfwgOP5+VNifKyjW9i6ZtB2lHWLEBWQY8G/5Tb 5lwgHvD99SJeLRMzcW5TFWhwv0ROx8chKP3btxMofZxjHhqseswGRLNDQa4XCU0= =ktpg -----END PGP SIGNATURE-----

6 13

RE: Concurrent queries problem (Gustavo Brian)
by Gustavo Brian 18 Aug '15

18 Aug '15

Hi, After checking CPU (48 cores) none of them get over 15%, there's free memory (119Gb) and 0% reads but high WRITE activity, any idea why? Thanks, Gustavo Brian Developer -----Original Message----- From: users-list [mailto:users-list-bounces+gustavo.brian=sunhotels.net@monetdb.org] On Behalf Of users-list-request(a)monetdb.org Sent: lunes, 17 de agosto de 2015 12:00 To: users-list(a)monetdb.org Subject: users-list Digest, Vol 36, Issue 19 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. Concurrent queries problem (Gustavo Brian) 2. Re: Concurrent queries problem (Martin Kersten) 3. Inserting in 'raw' mode?? (Vijay Krishna) ---------------------------------------------------------------------- Message: 1 Date: Sun, 16 Aug 2015 17:07:41 +0000 From: Gustavo Brian <Gustavo.Brian(a)sunhotels.net> To: "users-list(a)monetdb.org" <users-list(a)monetdb.org> Subject: Concurrent queries problem Message-ID: <4A8379DA0ABDFC42B172EDB698BD76E8D46EF829@mailbox02> Content-Type: text/plain; charset="iso-8859-1" Hi, I'm using one big table a my main storage for hotel prices and I'm running queries to get the best prices for specific dates and a couple of filters. On one test I have a query scanning 30 millions rows in ~400ms but when I try to run several queries at a time monetdb hangs. I've tried creating a funnel over the database but same result. What's the best approach if I need to run 150 queries per second ? Thanks in advance, Gustavo Brian Developer SunHotels E-mail Skype Tel Fax Web Operational Office gustavo.brian(a)sunhotels.net<mailto:gustavo.brian@sunhotels.net> Gustavo.brian.sunhotels +34 871 955 649 +34 971 281 460 www.sunhotels.org SunHotels Calle Cal?at 6 Pol?gono Son Valent? 07011 Palma de Mallorca Spain >> Winner of Grand Travel Award - Best Provider in Scandinavia << Confidential and proprietary information notice: This message (including any attachments) contains confidential information intended for a specific individual and purpose, and is protected by law. If you are not the intended recipient, you should delete this message and are hereby notified that any disclosure, copying, or distribution of this message, or the taking of any action based on it, is strictly prohibited. Please preserve message history when replying to this e-mail

1 0

Re: MonetDB performance disappointing
by Martin Schwitalla 17 Aug '15

17 Aug '15

Hi, sorry that it took so long but I could test the patch and it didn`t seem to change anything. The trace looks the same and the performance didn`t improve. Regards, Martin

1 1

Concurrent queries problem
by Gustavo Brian 16 Aug '15

16 Aug '15

Hi, I'm using one big table a my main storage for hotel prices and I'm running queries to get the best prices for specific dates and a couple of filters. On one test I have a query scanning 30 millions rows in ~400ms but when I try to run several queries at a time monetdb hangs. I've tried creating a funnel over the database but same result. What's the best approach if I need to run 150 queries per second ? Thanks in advance, Gustavo Brian Developer SunHotels E-mail Skype Tel Fax Web Operational Office gustavo.brian(a)sunhotels.net<mailto:gustavo.brian@sunhotels.net> Gustavo.brian.sunhotels +34 871 955 649 +34 971 281 460 www.sunhotels.org SunHotels Calle Calçat 6 Polígono Son Valentí 07011 Palma de Mallorca Spain >> Winner of Grand Travel Award - Best Provider in Scandinavia << Confidential and proprietary information notice: This message (including any attachments) contains confidential information intended for a specific individual and purpose, and is protected by law. If you are not the intended recipient, you should delete this message and are hereby notified that any disclosure, copying, or distribution of this message, or the taking of any action based on it, is strictly prohibited. Please preserve message history when replying to this e-mail

2 1

Maximum number of tables
by Masood Mortazavi 15 Aug '15

15 Aug '15

Hi -- Is there a configured maximum number of tables supported by a MonetDB instance? Maximum number of columns in a table? What's the rough size of metadata per column at run-time? Thanks, - Masood

4 4

NULL support in MonetDB?
by Stephen P. Morgan 14 Aug '15

14 Aug '15

Hi, I wonder if anyone can tell me whether MonetDB supports NULL values? Thanks, Steve

2 1

THEAP corruption after DB restart
by Dennis Lorson 14 Aug '15

14 Aug '15

Hi, My DB contains around 1000 tables consisting of 100K-15M records each. They all have the same schema, but I’m not using a single table because locality is required for the individual sets. After restarting MonetDB, I’m experiencing data corruption in some of the tables (around 10-20): - when selecting a single row from those tables (LIMIT 1), the mserver process crashes - when calling the storage() function, the mserver process crashes - the storage() function can only be used again after dropping the tables in question (by trial and error) More information: - The restart was performed as part of an EC2 instance stop/start, This should send a SIGTERM signal to monetdbd. - The DB farm is stored on EBS. Snapshots were made well before the restart, but the same tables are affected. - The corrupted tables were used before successfully and the content has been unchanged since. - No client connections were active at restart time. mserver info: > 2015-08-13 15:57:42 MSG DB[10633]: arguments: /usr/bin/mserver5 --dbpath=/opt/db-farm/DB --set merovingian_uri=mapi:monetdb://ip-HOST:50000/DB --set mapi_open=false --set mapi_port=0 --set mapi_usock=/opt/db-farm/DB/.mapi.sock --set monet_vault_key=/opt/db-farm/DB/.vaultkey --set gdk_nr_threads=4 --set max_clients=64 --set sql_optimizer=default_pipe --set monet_daemon=yes > 2015-08-13 15:57:42 MSG DB[10633]: # MonetDB 5 server v11.19.15 "Oct2014-SP4" > 2015-08-13 15:57:42 MSG DB[10633]: # Serving database ‘DB', using 4 threads > 2015-08-13 15:57:42 MSG DB[10633]: # Compiled for x86_64-pc-linux-gnu/64bit with 64bit OIDs dynamically linked > 2015-08-13 15:57:42 MSG DB[10633]: # Found 15.672 GiB available main-memory. This error occurs a couple of times in the server logs, but only when first starting monetdbd: > 2015-08-13 14:33:15 MSG: !ERROR: GDKload: cannot read: name=15/31/153162, ext=theap, 57144 bytes missing. > 2015-08-13 14:33:15 MSG: !OS: No such file or directory > 2015-08-13 14:33:15 MSG: !ERROR: GDKload: cannot read: name=15/25/152564, ext=theap, 58536 bytes missing. > 2015-08-13 14:33:15 MSG: !OS: No such file or directory And then each time an affected table is accessed: > 2015-08-13 16:02:19 MSG merovingian[1041]: database ‘DB' (10640) was killed by signal SIGSEGV I’m trying to reproduce the issue, but it would be helpful if anyone has any pointers. My hunch would be that the table catalog and the theap files are not persisted in the same way, so when snapshotting or restarting, one of the two is out of sync. Could it be that some dynamic column index is built, but then only persisted in mapped memory? The comments at the top of the gdk/gdk_storage.c file seem to imply that this is not the case, but I’m not sure. If so, how would one ensure that all changes are properly flushed to disk? Would fsfreeze help? What if an unexpected loss of power occurs? Thanks, - Dennis

1 1

Remote table joining and where conditions
by Wu XIANG 13 Aug '15

13 Aug '15

Hi guys, I've two questions on July2015 release of monetdb. 1) If I have two VMs (vm1, vm2) with monetdb installed: on vm1, there is a table named t1, and it's also accessible on vm2 via remote table alias rt1, and there's another table on vm2 named d2. If I do a table join on rt1 and d2, what happens here ? a. Is it true that data in t1 are fully loaded into vm2, and table joining happens on vm2 ? b. If I add a where condition on d2, what happens ? Is it true that data of t1 are pre-filtered by this newly added condition before sent to vm2 ? c. If there's another table on vm1 with the same name of 'd2', with where condition on d2 added, what happens then ? p.s. all queries are requested on CLI of vm2. 2) is there anything like partition key of prestodb in a partition table, such that some partitions can be fully skipped when querying with a where condition on these partition key provided ? thanks wu

1 0

Request Buffer pools
by Vijay Krishna 13 Aug '15

13 Aug '15

Hi, Does MonetDB have any built in buffer pools to handle parallel requests? I see the default number of parallel connections accepted and processed at a time by MonetDB is 64.. While I see that there are ways to increase it, is there anyway built in to pool incoming requests until the live 64 requests are processed? For example, our application would receive 100 requests at a time, while we wish to limit the number of queries handled at a time by MonetDB to 50. Is there any way to automatically handle the remaining 50 (something like innodb buffer pool) ?? Thanks & Regards, Vijayakrishna.P. Mobile : (+91) 9500402305.

4 8

Slow DB performance after data insertion
by Zimmermann, Alexander 10 Aug '15

10 Aug '15

Hi folks, in the following scenario I see very slow DB performance after data insertion. W/ slow performance I mean that „mclient -u XXX -d XXX" blocks for roughly 10min bevore it let me access the DB. Scenario: I parse a pcap file (40MB, 695 connection) in python w/ dpkt. For every new connection found in the pcap file I create a new entry in my connection table and two new empty packet tables (for each direction one table). The actual packet data is not directly inserted into the packet tables, but written into files to push the data later on w/ COPY BINARY INTO into the packet tables. Autocommit is off. I commit after the I read the pcap completely and before I start the COPY BINARY INTO. After the COPY BINARY INTO I’ve another commit. That’s it. The number of transactions is 3*695 (2*695 table creations for the packet tables and 695 entries into the connection table). The script itself terminates in roughly 30secs, but the DB is blocked +- 10mins. Why? What is going on internally in the DB? Can I speedup the performance? Cheers Alex

3 3

possible monetdb catalogue corruption and how to recover from it?
by Ashish Kumar Singh 10 Aug '15

10 Aug '15

Hi List, We are seeing a situation with monetdb v11.17.17 "Jan2014-SP2” release where at the time of dropping table , data base got crashed. After that we are not able to drop this table. Any command to drop this table is returning with success but we can not create same table again as it complains about table exists/ in use. But when we try to select * from this table or do a count on this it does complains saying No Such Table exist. My hunch is somehow database catalogue got corrupted, what is recommended way to bring back database to good health after any such condition occurs? We have not tried restarting monetdb instance yet. Regards, Ashish Kumar Singh Mobile: +91-9972045095 Skype: toashishk(a)gmail.com

5 8

Help with lag()
by Eggert, Lars 06 Aug '15

06 Aug '15

Hi, there doesn't seem to be much documentation about how to use lag() in monetdb, and I'm running into a cryptic error. Here is a snippet of a table: sql>select time, tcp_seq from packet_1_s order by time limit 10; +--------------------------+------------+ | time | tcp_seq | +==========================+============+ | 1436723208.3099999 | 2056936800 | | 1436723208.3099999 | 2056936800 | | 1436723208.3099999 | 2056936800 | | 1436723208.3099999 | 2056936800 | | 1436723208.3099999 | 2056936848 | | 1436723208.3099999 | 2056936848 | | 1436723208.3099999 | 2056936848 | | 1436723208.3099999 | 2056936848 | | 1436723208.3099999 | 2056936896 | | 1436723208.3099999 | 2056936896 | +--------------------------+------------+ 10 tuples (9.701ms) sql>select time, tcp_seq, lag(tcp_seq, 1) over (order by time) from packet_1_s order by time limit 10; TypeException:user.s22_25[502]:'calc.lag_grp' undefined in: _1678:any := calc.lag_grp(_1672:oid, _1674:oid, _1676:dbl) program contains errors Any ideas what causes this error? Thanks, Lars

3 5

Trouble with perl dbd modules for Monetdb
by Bryan Ellerbrock 03 Aug '15

03 Aug '15

Hi, I'm new to database work, and completely new to monetdb. I am attempting to load data into a new monetdb setup for testing, to see if it may make sense to migrate some genotyping datasets from postgres. I'm working in a Virtualbox Debian 64bit VM with MonetDB v11.19.15 (Oct2014-SP4). I've set up everything according to the MonetDB tutorial, and have had no trouble creating test tables and inserting data with simple sql statements. Beyond that though I need to use perl, and I'm having trouble with DBD::monetdb and it's dependencies. The download from cpan either times out or fails when working on dependencies MonetDB::CLI and MonetDB::CLI::MapiPP. If I use the verbose flag it reports: Test Summary Report ------------------- t/02cxn.t (Wstat: 28416 Tests: 15 Failed: 14) Failed tests: 2-15 Non-zero exit status: 111 Parse errors: Bad plan. You planned 18 tests but ran 15. t/75mil.t (Wstat: 28416 Tests: 15 Failed: 14) Failed tests: 2-15 Non-zero exit status: 111 Parse errors: Bad plan. You planned 18 tests but ran 15. When I skip these tests with a force install, and try to use the DBD in a loading script, I encounter a similar problem. The perl debugger shows the script getting stuck at my line of code attempting to connect to the database. my $dbh = DBI->connect("dbi:monetdb:database=genotypes;host=localhost",$username,$password, { PrintError => 0, RaiseError => 1 } # turn on exception handling ); If I step into the subroutine calls I can't pinpoint a failure but it is at some point after a call to MonetDB::CLI::MapiPP Am I missing something obvious? Is there something I can do to further address the problem? Any help would be greatly appreciated! Best regards, Bryan Ellerbrock

1 0

Problem using tomograph
by Martin Schwitalla 03 Aug '15

03 Aug '15

Hi, I`m trying to use tomograph for a query analysis. But unlike stethoscope it doesn`t work. I start the tomogroph with tomograph -u monetdb -d exomate, just like in the tutorial. After that I start a mclient with mclient -u monetdb -d exomate and execute the query. But tomograph triggers no output until I quit it with CTRL+C and when I try to gnuplot the file file, I only get errors because there is no valuable data. Could you maybe tell me what I am doing wrong? I`m using MonetDB 11.19.15. Regards, Martin

3 4

What JDBC jar
by Vic 26 Jul '15

26 Jul '15

Hi, There are 3: http://dev.monetdb.org/downloads/Java/Oct2014-SP4 Which one should I use? Vic

3 3

MonetDB: bulk insert failed due to weird characters
by Tri Vuong 24 Jul '15

24 Jul '15

Hi, I'm using "COPY INTO" to insert some data into MonetDB. The csv that I'm copying from has some weird characters like \367\251\240 in some string fields and it broke the insertion. The error that I got is: !value from line x field y not inserted, expecting type clob Does anyone know how to get around this problem? The version I'm using is MonetDB Database Server Toolkit v1.1 (Oct2014-SP4). Thanks. -- Tri Vuong

4 6

Re: Monetdb-R integration
by Hannes Mühleisen 23 Jul '15

23 Jul '15

Hi Imad, I just noticed this, grouped aggregates using R need to consider the aggr_group implicit parameter. This vector denotes which group the entries in the input belong to. In your case, something like this should work: CREATE AGGREGATE "ttest2samples"(arg1 double, arg2 varchar(250)) RETURNS double LANGUAGE R { unlist(lapply(split(data.frame(arg1, arg2, stringsAsFactors=F), aggr_group), function(x) {t.test(arg1 ~ arg2, data=x)$p.value})) }; This first splits the input according to the grouping variables, then calculates the p-value for every group separately, and then returns a single result value for each group. select "2-Age","ttest2samplesStatistic"("9-Score", "4-Gender") as "stat", "ttest2samples"("9-Score", "4-Gender") as "pvalue" from "Marketing_Loyalty_4700298d-9862-40b3-9028-b0f15dab9dea" group by "2-Age”; then results in +---------+--------------------------+--------------------------+ | 2-Age | stat | pvalue | +=========+==========================+==========================+ | [21-30] | -1.3224483820692825 | 0.18902257300165592 | | [10-20] | 0.15797419230807905 | 0.8781391200296775 | | > 30 | -2.636786922876647 | 0.011182421832548708 | +---------+--------------------------+--------------------------+ Sorry for the delay, Hannes On 09 Jul 2015, at 09:52, imad hajj chahine <imad.hajj.chahine(a)gmail.com> wrote: > > > Hi Hannes, > > Any update on the issue? > > Thank you. > > On Mon, Jun 22, 2015 at 4:18 PM, imad hajj chahine <imad.hajj.chahine(a)gmail.com> wrote: > ok thanks, if you want you can also test with this function (a one sample test that takes the second argument as simple double value): > > CREATE AGGREGATE "ttest1sample"(arg1 double, arg2 double) RETURNS double LANGUAGE R { t.test(arg1, arg2)$p.value }; > > select "2-Age", "ttest1sample"("9-Score",6.08963) from "Marketing_Loyalty_4700298d-9862-40b3-9028-b0f15dab9dea" where "2-Age" is not null group by "2-Age" > > On Mon, Jun 22, 2015 at 4:12 PM, Hannes Mühleisen <Hannes.Muehleisen(a)cwi.nl> wrote: > ok, reproduced issue. let me see why this only returns one row. > > > ----- Original Message ----- > From: "imad hajj chahine" <imad.hajj.chahine(a)gmail.com> > To: "Hannes Mühleisen" <Hannes.Muehleisen(a)cwi.nl> > Sent: Monday, June 22, 2015 3:10:55 PM > Subject: Re: Monetdb-R integration > > Sorry Hannes, you need to do a trim on "4-Gender" column as its the one > used in the test. > Sorry again its my fault. > > On Mon, Jun 22, 2015 at 3:42 PM, Hannes Mühleisen <Hannes.Muehleisen(a)cwi.nl> > wrote: > > > Did a trim() on the column but same error. There are three distinct values > > in that column. > > I get > > > > select distinct "2-Age" from > > "Marketing_Loyalty_4700298d-9862-40b3-9028-b0f15dab9dea”; > > +---------+ > > | 2-Age | > > +=========+ > > | [21-30] | > > | [10-20] | > > | > 30 | > > +————+ > > > > > > ----- Original Message ----- > > From: "imad hajj chahine" <imad.hajj.chahine(a)gmail.com> > > To: "Hannes Mühleisen" <Hannes.Muehleisen(a)cwi.nl> > > Sent: Monday, June 22, 2015 2:36:43 PM > > Subject: Re: Monetdb-R integration > > > > Hi Hannes, > > > > Please let me know if you need more information from my side. > > > > Regards > > > > On Fri, Jun 19, 2015 at 11:38 AM, imad hajj chahine < > > imad.hajj.chahine(a)gmail.com> wrote: > > > > > Hi Hannes, > > > > > > This error is due that the "2-age" column has more than 2 distinct > > values, > > > there is a space after some values. can you run a trim on the "2-Age" and > > > then try it again. > > > > > > As for the vector thing, I didnt get the point, shouldnt the db engine > > > split the data into vectors and feed the aggregate function which return > > > one data point? > > > > > > Thanks > > > > > > On Fri, Jun 19, 2015 at 11:12 AM, Hannes Mühleisen < > > > Hannes.Muehleisen(a)cwi.nl> wrote: > > > > > >> I am running attached script, which I pieced together from your emails. > > >> This does not run, it produces the following error: > > >> > > >> Error running R expression. Error message: Error in t.test.formula(arg1 > > ~ > > >> arg2) : > > >> grouping factor must have exactly 2 levels > > >> Calls: as.data.frame -> <Anonymous> -> t.test -> t.test.formula > > >> > > >> > > >> > > >> > > >> In general, for aggregations, the R code needs to return a vector with a > > >> single entry for each group. > > >> > > >> > > >> Best, > > >> > > >> Hannes > > >> > > >> > > >> > > >> > > >> > On 19 Jun 2015, at 09:44, imad hajj chahine < > > >> imad.hajj.chahine(a)gmail.com> wrote: > > >> > > > >> > Thanks, if you need more information from my side let me know. > > >> > > > >> > Also can you answer me on the following when i use 2 functions one to > > >> retrieve Pvalue and one for the stat value: > > >> > Am I doing the correct way to retrieve the stat and p-value, is the > > >> ttest get executed twice in this case? is there a more optimal way > > where i > > >> can retrieve both information. > > >> > > > >> > On Fri, Jun 19, 2015 at 10:39 AM, Hannes Mühleisen < > > >> Hannes.Muehleisen(a)cwi.nl> wrote: > > >> > Looking into this at the moment. > > >> > > > >> > > On 16 Jun 2015, at 09:56, Dimitar Nedev < > > >> dimitar.nedev(a)monetdbsolutions.com> wrote: > > >> > > > > >> > > Hannes, do you know if the STD_DEV is the issue there or if the > > >> queries should actually yield different results? > > >> > > > > >> > > Dimitar > > >> > > > > >> > >> On 2015-Jun-15, at 13:28 , imad hajj chahine < > > >> imad.hajj.chahine(a)gmail.com> wrote: > > >> > >> > > >> > >> Hi, > > >> > >> > > >> > >> Shouldn't we have the same result from running the first query and > > >> the second query? > > >> > >> after all its an aggregate function and should return the test for > > >> each group. > > >> > >> > > >> > >> I checked that monetdb had problem with running group by with > > >> statistics functions like STD_DEV, are these 2 issues related? > > >> > >> > > >> > >> Thank you. > > >> > >> > > >> > >> > > >> > >> > > >> > >> > > >> > >> On Mon, Jun 15, 2015 at 1:52 PM, Dimitar Nedev < > > >> dimitar.nedev(a)monetdbsolutions.com> wrote: > > >> > >> Hi Imad, > > >> > >> > > >> > >> Here is the output I got from running the queries. Note, this might > > >> not be entirely representative since I did clean-up some data before > > >> loading it. > > >> > >> > > >> > >> sql>select "2-Age", "tone"."ttest2"("9-Score",6.08963) from > > >> "tone"."Marketing_Loyalty_4700298d-9862-40b3-9028-b0f15dab9dea" where > > >> "2-Age" is not null group by "2-Age"; > > >> > >> +---------+--------------------------+ > > >> > >> | 2-Age | L1 | > > >> > >> +=========+==========================+ > > >> > >> | [10-20] | 0.89311103869734754 | > > >> > >> +---------+--------------------------+ > > >> > >> 3 tuples (3.218ms) > > >> > >> > > >> > >> sql>select '[10-20]' as "2-Age","tone"."ttest2"("9-Score",6.08963) > > >> from "tone"."Marketing_Loyalty_4700298d-9862-40b3-9028-b0f15dab9dea" > > where > > >> "2-Age" = '[10-20]' > > >> > >> more>union all > > >> > >> more>select '[21-30]' as "2-Age","tone"."ttest2"("9-Score",6.08963) > > >> from "tone"."Marketing_Loyalty_4700298d-9862-40b3-9028-b0f15dab9dea" > > where > > >> "2-Age" = '[21-30]' > > >> > >> more>union all > > >> > >> more>select '[> 30]' as "2-Age","tone"."ttest2"("9-Score",6.08963) > > >> from "tone"."Marketing_Loyalty_4700298d-9862-40b3-9028-b0f15dab9dea" > > where > > >> "2-Age" = '> 30'; > > >> > >> +---------+--------------------------+ > > >> > >> | 2-Age | L1 | > > >> > >> +=========+==========================+ > > >> > >> | [10-20] | 0.14150742355349472 | > > >> > >> | [21-30] | 0.32872830865003566 | > > >> > >> | [> 30] | 0.37127075363219841 | > > >> > >> +---------+--------------------------+ > > >> > >> 3 tuples (8.825ms) > > >> > >> > > >> > >> > > >> > >> sql>select "tone"."ttest2samplesStatistic"("9-Score", "4-Gender") > > as > > >> "stat", "tone"."ttest2samples"("9-Score", "4-Gender") as "pvalue" from > > >> "tone"."Marketing_Loyalty_4700298d-9862-40b3-9028-b0f15dab9dea" group > > by > > >> "2-Age"; > > >> > >> +--------------------------+--------------------------+ > > >> > >> | stat | pvalue | > > >> > >> +==========================+==========================+ > > >> > >> | -2.1588506639013736 | 0.032555033970368859 | > > >> > >> +--------------------------+--------------------------+ > > >> > >> 1 tuple (13.287ms) > > >> > >> > > >> > >> > > >> > >> I dropped the aggregate with: > > >> > >> sql>DROP AGGREGATE "tone"."ttest2samplesStatistic"; > > >> > >> operation successful (2.049ms) > > >> > >> > > >> > >> > > >> > >> Best regards, > > >> > >> Dimitar > > >> > >> > > >> > >>> On 2015-Jun-12, at 00:46 , imad hajj chahine < > > >> imad.hajj.chahine(a)gmail.com> wrote: > > >> > >>> > > >> > >>> Thank you Dimitar, > > >> > >>> > > >> > >>> I was using Squirrel to connect from my host machine to the VM. > > >> > >>> > > >> > >>> Now when I connect using SSH-client and run the short group by > > >> query it works but the result is not similar to the union queries (PS: i > > >> also run it in the VM direclty and had the same result) > > >> > >>> > > >> > >>> > > >> > >>> +---------+--------------------------+ > > >> > >>> | 2-Age | pval | > > >> > >>> +=========+==========================+ > > >> > >>> | [21-30] | 0.99996611809271041 | > > >> > >>> +---------+--------------------------+ > > >> > >>> 3 tuples (2.638ms) > > >> > >>> > > >> > >>> > > >> > >>> What i am trying to achieve is to run a one sample ttest for all > > >> the age categories, and it seems that the group by is ignored. > > >> > >>> > > >> > >>> I have also created 2 functions for the two sample ttest: > > >> > >>> > > >> > >>> CREATE AGGREGATE "tone"."ttest2samples"(arg1 double, arg2 > > >> varchar(250)) RETURNS double LANGUAGE R { t.test(arg1 ~ arg2)$p.value }; > > >> > >>> CREATE AGGREGATE "tone"."ttest2samplesStatistic"(arg1 double, arg2 > > >> varchar(250)) RETURNS double LANGUAGE R { t.test(arg1 ~ arg2)$statistic > > }; > > >> > >>> > > >> > >>> select "tone"."ttest2samplesStatistic"("9-Score", "4-Gender") as > > >> "stat", "tone"."ttest2samples"("9-Score", "4-Gender") as "pvalue" from > > >> "tone"."Marketing_Loyalty_4700298d-9862-40b3-9028-b0f15dab9dea" group > > by > > >> "2-Age" > > >> > >>> > > >> > >>> Am I doing the correct way to retrieve the stat and p-value, is > > the > > >> ttest get executed twice in this case? is there a more optimal way > > where i > > >> can retrieve both information. Please note that I also have the same > > >> grouping issue as before. > > >> > >>> > > >> > >>> Do you have the same behavior, or I am missing something in the > > >> creation of these functions. > > >> > >>> > > >> > >>> A final question, how do u drop a R function? i am trying to > > >> execute the following: > > >> > >>> drop all function "tone"."ttest2samplesStat" > > >> > >>> > > >> > >>> > > >> > >>> Thank you. > > >> > >>> > > >> > >>> > > >> > >>> > > >> > >>> On Thu, Jun 11, 2015 at 11:28 PM, Dimitar Nedev < > > >> dimitar.nedev(a)monetdbsolutions.com> wrote: > > >> > >>> Hi Imad, > > >> > >>> > > >> > >>> Hannes asked me to look at your issue with the MonetDB-R VM. > > >> > >>> > > >> > >>> I loaded the data and ran the queries you mentioned, but I did not > > >> encounter any issues. This holds for both the short and the union > > queries. > > >> I am also not familiar with the issue you report, nor its cause. At this > > >> time I think it is local to your instance. If you can export your db, or > > >> re-load the data, I would recommend you to create a new instance with a > > >> fresh image and try again. If you import the image as a new VM instance, > > >> you can keep your old one around as well. > > >> > >>> > > >> > >>> Please note that during the test: > > >> > >>> - I ran as the admin (monetdb) user, as the one created default on > > >> the VM image cannot create new schemas. > > >> > >>> - I have to clean the data a bit up to load it, removing dozen > > >> lines or so. > > >> > >>> - Renamed ttest1 to ttest2 to match the queries. > > >> > >>> > > >> > >>> Best regards, > > >> > >>> Dimitar > > >> > >>> > > >> > >>>>>>>>> Begin forwarded message: > > >> > >>>>>>>>> > > >> > >>>>>>>>> From: imad hajj chahine <imad.hajj.chahine(a)gmail.com> > > >> > >>>>>>>>> Subject: Re: Monetdb-R integration > > >> > >>>>>>>>> Date: 10 Jun 2015 10:11:48 CEST > > >> > >>>>>>>>> To: Hannes Mühleisen <Hannes.Muehleisen(a)cwi.nl> > > >> > >>>>>>>>> > > >> > >>>>>>>>> Hi, > > >> > >>>>>>>>> > > >> > >>>>>>>>> I am using the virtual box provided from > > >> https://www.monetdbsolutions.com/solutions/cloud/vm: > > >> > >>>>>>>>> OS:linux version 3.10.0-123.9.3.el7.x86_64 ( > > >> builder(a)kbuilder.dev.centos.org) > > >> > >>>>>>>>> Monetdb: V1.7: (Oct2014-SP1) > > >> > >>>>>>>>> R: 3.1.2 (2014-10-31) > > >> > >>>>>>>>> > > >> > >>>>>>>>> > > >> > >>>>>>>>> > > >> > >>>>>>>>> create table > > >> "tone"."Marketing_Loyalty_4700298d-9862-40b3-9028-b0f15dab9dea" > > ("Consumer > > >> Number" INTEGER,"1-Disloyalty" varchar(250),"2-Age" > > varchar(250),"3-Amount > > >> transferred" INTEGER,"4-Gender" varchar(250),"5-Citiz enship" > > >> varchar(250),"6- Profession" varchar(250),"7-City" varchar(250),"8-1 NC > > >> Billboard" varchar(250),"8-2 NC Friends" varchar(250),"8-3 NC Magazine" > > >> varchar(250),"8-4 NC Others" varchar(250),"9-Score" decimal(18,3),"10- > > Type > > >> of operation" varchar(250),"11-1 Welcoming" INTEGER,"11-2 Waiting time" > > >> INTEGER,"11-3 Time to finish request" INTEGER,"11-4 Fees" INTEGER,"11-5 > > >> Courtesy of employees" INTEGER,"11-6 Branch layout" INTEGER,"11-7 > > Attention > > >> to special requests" INTEGER,"12- Total time for service" > > >> decimal(18,3),"13- Distance from POS" decimal(18,3),"14- Weight" > > INTEGER) > > >> > >>>>>>>>> > > >> > >>>>>>>>> COPY offset 2 INTO > > >> "tone"."Marketing_Loyalty_4700298d-9862-40b3-9028-b0f15dab9dea" FROM > > >> > > '/home/App_Data/TestingDataSimple_2003_Marketing_Loyalty_4700298d-9862-40b3-9028-b0f15dab9dea.csv' > > >> USING DELIMITERS ',','\n','"'; > > >> > >>>>>>>>> > > >> > >>>>>>>>> Thanks > > >> > >>>>>>>>> > > >> > >>>>>>>>> On Wed, Jun 10, 2015 at 10:50 AM, Hannes Mühleisen < > > >> Hannes.Muehleisen(a)cwi.nl> wrote: > > >> > >>>>>>>>> Hello Imad, > > >> > >>>>>>>>> > > >> > >>>>>>>>> which version of MonetDB and R are you using? Which > > operating > > >> system? > > >> > >>>>>>>>> > > >> > >>>>>>>>> Can you please also include the commands you used to load > > >> your CSV into MonetDB/ create the table? > > >> > >>>>>>>>> > > >> > >>>>>>>>> Thanks, > > >> > >>>>>>>>> > > >> > >>>>>>>>> Hannes > > >> > >>>>>>>>> > > >> > >>>>>>>>>> On 10 Jun 2015, at 00:25, imad hajj chahine < > > >> imad.hajj.chahine(a)gmail.com> wrote: > > >> > >>>>>>>>>> > > >> > >>>>>>>>>> Hi Hannes, > > >> > >>>>>>>>>> > > >> > >>>>>>>>>> I am testing Monetdb-R integration to do some statistical > > >> test, i followed the tutorial you posted on youtube and i have created > > the > > >> following function: > > >> > >>>>>>>>>> > > >> > >>>>>>>>>> CREATE AGGREGATE "tone"."ttest1"(arg1 double, arg2 double) > > >> RETURNS double LANGUAGE R { t.test(arg1, arg2)$p.value }; > > >> > >>>>>>>>>> > > >> > >>>>>>>>>> this function will basically do a one sample t-test. > > >> > >>>>>>>>>> > > >> > >>>>>>>>>> I have loaded the sample attached file into a demo database > > >> and trying to run the following query: > > >> > >>>>>>>>>> > > >> > >>>>>>>>>> select "2-Age", "tone"."ttest2"("9-Score",6.08963) from > > >> "tone"."Marketing_Loyalty_4700298d-9862-40b3-9028-b0f15dab9dea" where > > >> "2-Age" is not null group by "2-Age" > > >> > >>>>>>>>>> > > >> > >>>>>>>>>> The following error happened > > >> > >>>>>>>>>> Error: protocol violation: unexpected line in data block: . > > >> > >>>>>>>>>> SQLState: M0M10 > > >> > >>>>>>>>>> ErrorCode: 0 > > >> > >>>>>>>>>> > > >> > >>>>>>>>>> alternatively i managed to have the intended result by > > >> running this query: > > >> > >>>>>>>>>> > > >> > >>>>>>>>>> select '[10-20]' as > > >> "2-Age","tone"."ttest2"("9-Score",6.08963) from > > >> "tone"."Marketing_Loyalty_4700298d-9862-40b3-9028-b0f15dab9dea" where > > >> "2-Age" = '[10-20]' > > >> > >>>>>>>>>> union all > > >> > >>>>>>>>>> select '[21-30]' as > > >> "2-Age","tone"."ttest2"("9-Score",6.08963) from > > >> "tone"."Marketing_Loyalty_4700298d-9862-40b3-9028-b0f15dab9dea" where > > >> "2-Age" = '[21-30]' > > >> > >>>>>>>>>> union all > > >> > >>>>>>>>>> select '[> 30]' as > > >> "2-Age","tone"."ttest2"("9-Score",6.08963) from > > >> "tone"."Marketing_Loyalty_4700298d-9862-40b3-9028-b0f15dab9dea" where > > >> "2-Age" = '> 30' > > >> > >>>>>>>>>> > > >> > >>>>>>>>>> > > >> > >>>>>>>>>> any help or guidance is appreciated. > > >> > >>>>>>>>>> Thank you. > > >> > >>>>>>>>>> > > >> > >>>>>>>>>> > > >> > > <TestingDataSimple_2003_Marketing_Loyalty_4700298d-9862-40b3-9028-b0f15dab9dea.csv> > > >> > >>>>>>>>> > > >> > >>>>>>>>> > > >> > >>>>>>>> > > >> > >>>>>>> > > >> > >>>>>> > > >> > >>>>> > > >> > >>>> > > >> > >>> > > >> > >> > > >> > >> > > >> > > > > >> > > > >> > > > >> > > >> > > >> > > > > > > >

1 0

Problem: Relational query without result
by Martin Schwitalla 22 Jul '15

22 Jul '15

Hi, I have a problem. I have tested on MonetDB 11.19.9 a query and wanted to test the same query on a better Workstation and installed MonetDB 11.19.15. But now I get every time the message „realtional query without result“ when I try to run the query. Can anybody help? Kind regards, Martin

4 8

How to work with MAL-statements?
by Martin Schwitalla 22 Jul '15

22 Jul '15

Hello, to improve execution time I wanted to work with MAL-statements, so that I could directly control the query plan. But it seems that the generated MAL-Plans don`t „just work“ when I type them. I tried to get used to MAL with the simple query „select * from table;“. This got me the following plan: function user.s1_1{autoCommit=true}():void; barrier X_59 := language.dataflow(); X_2 := sql.mvc(); X_3:bat[:oid,:oid] := sql.tid(X_2,"sys","diseases"); X_6:bat[:oid,:int] := sql.bind(X_2,"sys","diseases","id",0); (X_9,r1_9) := sql.bind(X_2,"sys","diseases","id",2); X_12:bat[:oid,:int] := sql.bind(X_2,"sys","diseases","id",1); X_14 := sql.delta(X_6,X_9,r1_9,X_12); X_15 := algebra.leftfetchjoin(X_3,X_14); X_23:bat[:oid,:str] := sql.bind(X_2,"sys","diseases","name",0); (X_25,r1_25) := sql.bind(X_2,"sys","diseases","name",2); X_27:bat[:oid,:str] := sql.bind(X_2,"sys","diseases","name",1); X_28 := sql.delta(X_23,X_25,r1_25,X_27); X_29 := algebra.leftfetchjoin(X_3,X_28); X_16:bat[:oid,:str] := sql.bind(X_2,"sys","diseases","short_name",0); (X_18,r1_18) := sql.bind(X_2,"sys","diseases","short_name",2); X_20:bat[:oid,:str] := sql.bind(X_2,"sys","diseases","short_name",1); X_21 := sql.delta(X_16,X_18,r1_18,X_20); X_22 := algebra.leftfetchjoin(X_3,X_21); language.pass(X_3); exit X_59; X_30 := sql.resultSet(3,1,X_15); sql.rsColumn(X_30,"sys.diseases","id","int",32,0,X_15); sql.rsColumn(X_30,"sys.diseases","short_name","varchar",255,0,X_22); sql.rsColumn(X_30,"sys.diseases","name","varchar",255,0,X_29); X_40 := io.stdout(); sql.exportResult(X_40,X_30); end s1_1; But when I try to run this statements I get the following error: MAPI = (monetdb) /tmp/.s.monetdb.5250 QUERY = end s1_1; ERROR = !TypeException:user.s1_1[7]:'sql.delta' undefined in: X_14:any := sql.delta(X_6:bat[:oid,:int], X_9:bat[:oid,:oid], r1_9:bat[:oid,:any], X_12:bat[:oid,:int]) !ERROR: MSresetVariablescould not allocate space for What am I`m missing that it doesn`t work? Kind regards, Martin

3 3

QUANTILE( colname , 0 ) and QUANTILE( colname , 1 ) problem
by Anthony Damico 21 Jul '15

21 Jul '15

hi monetdb team, i am getting errors below on data i can't share. before i spend a bunch of time trying to create a reproducible example for this: i've confirmed that MIN and MAX both do work on this column, and also that QUANTILE works with 0.01 thru 0.99 on this column. is there a reason QUANTILEs of 0 and 1 don't simply call MIN and MAX? thanks! Error in .local(conn, statement, ...) : Unable to execute statement 'SELECT QUANTILE( tot_pay , 0 ) FROM acons WHERE yr = 2013 AND ( dattyp1 = 1 OR dattyp2 = 1 OR dattyp...'. Server says 'overflow in conversion of 43553.818965373482 to bte.' [#22003]. > > unwtd_pct <- dbGetQuery( db , paste( "SELECT QUANTILE( tot_pay , 1 ) FROM acons" , where_clause ) ) Error in .local(conn, statement, ...) : Unable to execute statement 'SELECT QUANTILE( tot_pay , 1 ) FROM acons WHERE yr = 2013 AND ( dattyp1 = 1 OR dattyp2 = 1 OR dattyp...'. Server says 'overflow in conversion of 43553.818965373482 to bte.' [#22003]. >

3 3

CAST and CONVERT string to int
by Dennis Wiest 20 Jul '15

20 Jul '15

Hello, I have a question for the Operation Cast and CONVERT. I have the following table and wants to run this query. select * from value v2 WHERE v2.xpath_id = 378 AND v2.value BETWEEN 1000 AND 2000 LIMIT 10; This provides me with the wrong result because v2.value is a textfield. Now I wanted to with the following query to fix this problem, but I get an error. Is it at all possible to the cast or convert? select * from value v2 WHERE v2.xpath_id = 378 AND CAST(CAST(v2.value AS float)AS integer) > 1000 LIMIT 10; conversion of string 'RNAV' to type dbl failed. But this one Works select CAST(CAST(v2.value AS float)AS integer) from value v2 WHERE v2.xpath_id = 378 LIMIT 10; The problem is, "value" must be a text field, "value" can take all possible values. CREATE TABLE value ( id int NOT NULL AUTO_INCREMENT, element_id int NOT NULL, xpath_id int NOT NULL, value text, PRIMARY KEY (id) ); Thanks 4 Help Greetings Dennis ________________________________ Avitech GmbH Principal Office: Bahnhofplatz 1 | 88045 Friedrichshafen | Germany Court Registration: Amtsgericht Ulm | HRB 728293 Gesch?ftsf?hrer/Managing Director: Antonio Maria Gonzalez Gorostiza VAT No.: DE223719716 http://avitech.aero

3 5

combined conditions in user defined function made monetdb exited.
by integrity 19 Jul '15

19 Jul '15

Dear experts, I tried to create an function in monetdb, but the first one successful, the second one successful, but the combo failed. create function neighbor19() returns table (ra_avg double) begin declare table uzone (id bigint, ra_avg double); insert into uzone select id, ra_avg from uniquecatalog11; return table( select ra_avg from uzone as u0 where id between 10 and 20 ); end; successful. create function neighbor20() returns table (ra_avg double) begin declare table uzone (id bigint, ra_avg double); insert into uzone select id, ra_avg from uniquecatalog11; return table( select ra_avg from uzone as u0 where ra_avg between 10 and 20 ); end; successful. create function neighbor30() returns table (ra_avg double) begin declare table uzone (id bigint, ra_avg double); insert into uzone select id, ra_avg from uniquecatalog11; return table( select ra_avg from uzone as u0 where id between 10 and 20 and ra_avg between 10 and 20 ); end; failed with monetdb exited. in merovingian.log: 2015-07-19 13:34:36 MSG merovingian[25753]: database 'gwacdb' (633) was killed by signal SIGSEGV I tried on default and Jul2015 branch, all the same with each other. Any help or hints are appreciated! Best, Meng

3 5

Re: MonetDB performance disappointing
by Niels Nes 18 Jul '15

18 Jul '15

Martin Then trace should be somewhat different as the generated plan changed. Could you send the output of plan . Niels So put plan infront of the query. -------- Original message -------- From: Martin Schwitalla <MSchwitalla1(a)gmx.de> Date: 18/07/2015 12:52 (GMT+01:00) To: Communication channel for MonetDB users <users-list(a)monetdb.org> Cc: Niels Nes <Niels.Nes(a)cwi.nl> Subject: Re: MonetDB performance disappointing Hi, sorry that it took so long but I could test the patch and it didn`t seem to change anything. The trace looks the same and the performance didn`t improve. Regards, Martin _______________________________________________ users-list mailing list users-list(a)monetdb.org https://www.monetdb.org/mailman/listinfo/users-list

1 0

nearest value question on stackoverflow
by Anthony Damico 17 Jul '15

17 Jul '15

https://stackoverflow.com/questions/30641876/monetdb-sql-method-to-locate-o… i'm thinking i can do this with some costly self-join, but i'd appreciate any other eyes on the problem

4 12

No subject
by Bogdan Dimitrov 17 Jul '15

17 Jul '15

This is by no means minimal, but here I've listed some of the issues I had after manually building MonetDB version 11.19.9 with R integration: https://www.monetdb.org/pipermail/users-list/2015-April/008034.html Bogdan ------------------------------ Message: 4 Date: Thu, 25 Jun 2015 23:48:41 +0200 From: musa <pehlewan(a)gmail.com> To: users-list(a)monetdb.org Message-ID: <CAH1mZ_GQScbSTuDhDoy3W65FETH2e0Ntx8a7aScYwcVTjRh0WA(a)mail.gmail.com> Content-Type: text/plain; charset="utf-8" Can someone please provide a minimal example (how to start the server) for running the embedded R feature in windows? Regards, mpa ________________________________

2 1

Should MonetDB really be used in production?
by Frédéric Jolliton 17 Jul '15

17 Jul '15

Greetings, We filled a bug few days ago about simple SAVEPOINT usage corrupting the database (both in Oct2014-SP4 and tip of hg). This is worrying us a lot, because that's yet another bug easy to trigger. We're studying MonetDB extensively for our future needs. We've written optimizers, custom aggregate functions in C, and we've studied many internal aspects. But yet, from time to time, we are hit by bugs when performing simple, trivial queries. You can see the history of bugs we filled on the bugtracker (regarding UNION, SELECT, ..). This become time consuming. This make us nervous because we might have hard time trusting our complexes queries run by MonetDB. The performance are stunning, but the quality seems not high enough for production. Projects like PostgreSQL shows that even Open Source projects can attains incredible stability and robustness. (Admittedly there are many more contributors to PostgreSQL.) Note that we understand that it is a complex software, and that it is hard to cover all possible corner cases in tests. But we're wondering how stable MonetDB is expected to be. Is this really used in production? Regarding the MonetDB website, the primary source of information for the project, we've not found any reference documentation regarding all the supported SQL features. Sure, there is a page listing what is *not* supported, there are pages on various topics, but it is unclear if SAVEPOINT is really supported. (If I can say, I think that the website probably needs some refresh and better navigation.) Here is the latest bug I was talking about: $ bin/mclient -p 50005 test Welcome to mclient, the MonetDB/SQL interactive terminal (Oct2014-SP4) Database: MonetDB v11.19.13 (Oct2014-SP4), 'mapi:monetdb://fjolliton:50005/test' Type \q to quit, \? for a list of available commands auto commit mode: on sql>start transaction; auto commit mode: off sql>create table foo (a int); operation successful (0.528ms) sql>savepoint sp1; auto commit mode: off sql>release savepoint sp1; auto commit mode: off sql>commit; auto commit mode: on sql>select * from foo; +---+ | a | +===+ +---+ 0 tuples (0.9s) So far, so good. Now try to connect again: $ bin/mclient -p 50005 test Welcome to mclient, the MonetDB/SQL interactive terminal (Oct2014-SP4) Database: MonetDB v11.19.13 (Oct2014-SP4), 'mapi:monetdb://fjolliton:50005/test' Type \q to quit, \? for a list of available commands auto commit mode: on sql>select * from foo; SELECT: no such table 'foo' Whoops ! Regards, -- Frédéric Jolliton Sécuractive

9 10

Re: Re: Provide --password option for msqldump tool
by GMail 16 Jul '15

16 Jul '15

1 0

Fwd: Re: Provide --password option for msqldump tool
by Sherzod Mutalov 16 Jul '15

16 Jul '15

I added "password" option to mclient.c and msqldump.c, you can get changed sources from: https://gist.github.com/shmutalov/d3695ad67ad5195eeee6 I know, it is dangerous, but we need this option in our application. If you want I can provide mclient binaryies for Win7 x64.

1 0

Provide --password option for msqldump tool
by Aravind S Reddy 16 Jul '15

16 Jul '15

Hello Team, We were trying to dump multiple tables through 'msqldump' tool, and restoring it in different monetdb instance in a programatic way. But the difficulty here is, I do not see an option to pass the --password to 'msqldump' tool, where as it prompts for the password and accepts input from STDIN. Due to this, we are not able achieve our requirement programatically. And it would be good if -t(--table) option accepts multiple table names, through which we can dump the selected tables in a single shot. Regards, Aravind.S

4 5

Cluster advantages MonetDB
by Luciano Sasso 16 Jul '15

16 Jul '15

Hi, What the cluster Advantages MonetDB, If a query in shard tables in a cluster can not be ordered, what the advantage of using a MonetDB cluster? besides the replication in case of failure. Att, -- Luciano Sasso Vieira Data Scientist & Solutions Architect luciano(a)gsgroup.com.br <http://www.gsgroup.com.br> | tel: 17 3353-0833 | cel: 17 99706-9335 www.gsgroup.com.br <http://www.gsgroup.com.br> --- Este email foi escaneado pelo Avast antivírus. http://www.avast.com

5 5

Job openings at MonetDB Solutions
by Ying Zhang 14 Jul '15

14 Jul '15

Dear all, MonetDB Solutions, the supporting company of MonetDB, is looking for core database developers. If you’re interested, please see the job description here: https://www.monetdbsolutions.com/company/careers Regards, Jennie

1 0

Re: User for multiple schemas
by Ying Zhang 14 Jul '15

14 Jul '15

Hai Brian, Glad to know it helps. We’ve found the cause of the problem I mentioned. It’s not random. It has something to do with changing the ROLE of a USER, and then the privileges of that ROLE seem not properly handled. We’re working on improving the documentation, extending the test suite, and fixing bugs concerning privileges. Regards, Jennie > On Jul 14, 2015, at 12:10, Brian Hood <brianh6854(a)googlemail.com> wrote: > > Hi Jennie, > > That was actually what i was looking for thanks for that i didn't realize that you could have more than one create schema schema_name AUTHORIZATION per_user > > I'll also see if i encounter the bug and if i can find anything reproducible. > > Regards, > > Brian Hood > > > On Tue, Jul 14, 2015 at 10:03 AM, Ying Zhang <Y.Zhang(a)cwi.nl> wrote: > Hello Brian, > > What authorisation are you looking for? MonetDB does support authorise one user as the owner of multiple schema’s, e.g.: > > create user brian with password ’brian123' name ’brian' schema sys; > create schema schema1 AUTHORIZATION brian; > create schema schema2 AUTHORIZATION brian; > > Not sure if this is what you’re looking for. > > However, we have noticed a problem (in the current default branch, probably also exists in Jul2015) in handling the privileges. After a user has been authorised as the owner of a schema, the user can CREATE, INSERT, UPDATE, … tables, however, *sometimes* the user can’t SELECT from his/her own tables. This seems to happen at random, which makes it extremely difficult to debug. However, restarting the MonetDB server seems to temporarily resolve the problem. > > We’re working on improving handling privileges in MonetDB. If you notice any errors and missing features, please let us know. > > Regards, > > Jennie > > > On Jun 23, 2015, at 08:05, Stefan Manegold <Stefan.Manegold(a)cwi.nl> wrote: > > > > Hi Brian, > > > > If i'm not mistaken and recall correctly, access rights / authorization are per table, not per schema - at least in MonetDB; hence, you'd have to grant access for each table in other than the user's schema separately. > > > > Best, > > Stefan > > > > > > On June 22, 2015 8:45:08 PM CEST, Brian Hood <brianh6854(a)googlemail.com> wrote: > > Hi All, > > > > There is something I have pondered on is there a way to have a username that can be authorized for multiple schemas under your dbfarm. > > > > If not what is the reason ? > > > > I haven't seen a way to do this so far. > > > > Regards, > > > > Brian Hood > > > > > > > > users-list mailing list > > users-list(a)monetdb.org > > https://www.monetdb.org/mailman/listinfo/users-list > > > > -- > > | Stefan.Manegold(a)CWI.nl | DB Architectures (DA) | > > | www.CWI.nl/~manegold/ | Science Park 123 (L321) | > > | +31 (0)20 592-4212 | 1098 XG Amsterdam (NL) | > > _______________________________________________ > > users-list mailing list > > users-list(a)monetdb.org > > https://www.monetdb.org/mailman/listinfo/users-list > >

1 0

User for multiple schemas
by Brian Hood 14 Jul '15

14 Jul '15

Hi All, There is something I have pondered on is there a way to have a username that can be authorized for multiple schemas under your dbfarm. If not what is the reason ? I haven't seen a way to do this so far. Regards, Brian Hood

3 2

How do you generate documentation from MonetDB source files?
by Jon Mulieri 12 Jul '15

12 Jul '15

I have long appreciated the wealth of information in the comments of source files throughout the code base. It is apparently formatted for some documentation extraction. Can someone provide instructions for generating the documentation from the source? Thanks! Jon

3 2

Reproducing EmbeddedR results from MonetDB.org site
by George, Glover E ERDC-RDE-ITL-MS 10 Jul '15

10 Jul '15

Hi all, I’ve recently been profiling several techniques for a workflow that we’ve been trying to improve here at USACE. Originally we used python scripts with sqlite, but we ran into scalability problems on large data sets. This led us to MonetDB, with the promise of columnar-based analysis and the hope of both parallel query’s “under-the-hood” and possibly a distributed workflow across an HPC system. Looking at my profiling results, this has led me to a number of questions that hopefully you all can help us with. I have a single table from the TPC-H benchmark – lineitem – populated with 360million entries: "l_orderkey" INTEGER NOT NULL, "l_partkey" INTEGER NOT NULL, "l_suppkey" INTEGER NOT NULL, "l_linenumber" INTEGER NOT NULL, "l_quantity" DECIMAL(15,2) NOT NULL, "l_extendedprice" DECIMAL(15,2) NOT NULL, "l_discount" DECIMAL(15,2) NOT NULL, "l_tax" DECIMAL(15,2) NOT NULL, "l_returnflag" CHAR(1) NOT NULL, "l_linestatus" CHAR(1) NOT NULL, "l_shipdate" DATE NOT NULL, "l_commitdate" DATE NOT NULL, "l_receiptdate" DATE NOT NULL, "l_shipinstruct" CHAR(25) NOT NULL, "l_shipmode" CHAR(10) NOT NULL, "l_comment" VARCHAR(44) NOT NULL, System Setup: Intel(R) Xeon(R) CPU E5-2660 0 @ 2.20GHz - 16 cores (HT off) / 256G RAM MonetDB version 11.19.15 R version 3.2.0 1. I have followed the instructions here: https://www.monetdb.org/content/embedded-r-monetdb and tried to reproduce these results. I only attempt to reproduce the results from R (reading the data from CSV since – as noted in a previous email to this list – R fails to read in the 360 million rows using MonetDB.R but works fine reading the same amount from CSV), MonetDB, as well as MonetDB with Embedded R. - I compile both R and MonetDB from source. When building R, I include the option to build the R shared library (libR.so) and when compiling MonetDB I include the option for embedded R. - When creating the MonetDB database, I set the option for embedr=true. - The R function from the above URL is used, as well as the same SQL query – and it works…. However, the performance is much worse than using R alone. I know I must be overlooking something. Please see the image at https://goo.gl/wUvB2J (png on Google Drive). The X-axis is the number of rows, and the Y axis is the time in seconds. As you can see, embedded R is unexpectedly much worse than the other two. 2. While running the quantile function, only one core is active – whether embeddedR or just MonetDB’s. 3. When running the following query (after altering the table to include a new column z, of course): sql>update lineitem set z=l_extendedprice / l_quantity; 360011594 affected rows (3m 49s) … Multiple cores are active for the first 10 seconds, then it resorts to single core. A. What am I not understanding about MonetDB’s ability to use multiple cores? The only time I really see it use multiple cores seems to be when doing “copy into”. B. Can someone provide assistance in trying to replicate the Embedded R results? Should it be running in parallel? Or might something in my setup be configured incorrectly. Cheers!

3 3

(no subject)
by musa 10 Jul '15

10 Jul '15

Can someone please provide a minimal example (how to start the server) for running the embedded R feature in windows? Regards, mpa

3 2

RE: Concurrency test causes crash of MonetDB 5 server on Windows
by michael.witt＠uniserv.com 08 Jul '15

08 Jul '15

Hello, Guillaume: Thanks for the tip avoiding concurrency conflicts. We already considered that and changed to code to avoid such conflicts. In the meanwhile I tested both on Windows and Linux machines and now it is clear that it is a problem specific to the Windows builds of MonetDB. The mserver5 on Windows starts with 2 threads serving requests as stated in the console after starting... # Serving database 'uniserv', using 2 threads Then I found a statement about the thread implementation on Windows by Stefan Mangegold: https://www.monetdb.org/pipermail/developers-list/2010-July/003492.html "However, we do not know how well the multi-threading works on Windows. We rely on pthreads, and their implementation on Windows might not be as efficient and/or stable as on Unix-like systems ..." This is absolutely true! My tests show that mserver5 started with 2 threads always crashes after some time firing concurrent read requests. By the way, the windows machine has 2 physical and 2 logical cores. But if I do the same on a Linux machine (also with 2 threads) Mserver5 runs stable. What is the solution to stabilize Mserver5 on Windows? Set the number of threads to 1 by setting the start option: --set gdk_nr_threads=1 In the M5server.bat script. After setting that, Mserver5 run stable on Windows, too. Does anyone know better solutions? Kind regards, Michael -----Original Message----- From: users-list [mailto:users-list-bounces+michael.witt=uniserv.com@monetdb.org] On Behalf Of Guillaume Bassot Sent: Dienstag, 30. Juni 2015 10:26 To: Communication channel for MonetDB users Subject: Re: Concurrency test causes crash of MonetDB 5 server Hello Michael, From my experience, all "write" request executed concurrently always end with concurrency conflict. I'm talking of heavey request during several minutes, so it's easy to get concurrent access. Since i only use monetdb with a single process, stability was greatly improved. Regards Guillaume BASSOT Web Developer +33 (0)1 55 48 02 82 gbassot(a)inbox.fr <mailto:gbassot@inbox.fr> 30, rue Vincent Moris 92240 Malakoff - France Fax : +33 (0)1 55 48 02 79 2015-06-30 10:09 GMT+02:00 <michael.witt(a)uniserv.com <mailto:michael.witt@uniserv.com> >: Hi all, It’s a pity that no one answered the question I asked a few weeks ago about crashes of MonetDB 5 server under windows 64 Bit. So I think I offer more information about a scenario where the databases crashes. Hopefully someone could give me a hint how to improve stability of MonetDB under Windows 64 Bit with concurrent client connections. We are using Monet DB for a couple of months and facing some graved issues with concurrent queries. The problem is, when we query the database with two or more clients in a parallel, the database crashes! These are the facts: MonetDB server version: v11.19.9 ================================ # MonetDB 5 server v11.19.9 "Oct2014-SP2" # Serving database 'uniserv', using 2 threads # Compiled for x86_64-pc-winnt/64bit with 64bit OIDs dynamically linked # Found 8.000 GiB available main-memory. # Copyright (c) 1993-July 2008 CWI. # Copyright (c) August 2008-2015 MonetDB B.V., all rights reserved # Visit http://www.monetdb.org/ <http://www.monetdb.org/> for further information # Listening for connection requests on mapi:monetdb://QADQSHWIN:50000/ # MonetDB/SQL module loaded Server crashes also occur on versions v11.19.8 and v11.19.7. Operation system: ================= Microsoft Windows Server 2008 R2 Datacenter Version 6.1.7601 Service Pack 1 Build 7601 hosted on VMWare Virtual Platform x64-based PC 2x Processor Intel(R) Xeon(R) CPU E5-2680 v2 @ 2.80GHz, 2800 Mhz, 1 Core(s), 1 Logical Processor(s) Installed Physical Memory (RAM) 8,00 GB Available Physical Memory 5,25 GB Total Virtual Memory 16,0 GB Server installation folder: =========================== E:\Program Files\MonetDB\MonetDB5 Server startup script M5server.bat: =================================== @REM Copyright August 2008-2014 MonetDB B.V. @REM All Rights Reserved. @echo off setlocal rem figure out the folder name set MONETDB=%~dp0 rem remove the final backslash from the path set MONETDB=%MONETDB:~0,-1% rem extend the search path with our EXE and DLL folders set PATH=%MONETDB%\bin;%MONETDB%\lib;%MONETDB%\lib\MonetDB5;%PATH% rem prepare the arguments to mserver5 to tell it where to put the dbfarm REM Uniserv: Don't use APPDATA, so set APPDATA to empty String. set APPDATA= if "%APPDATA%" == "" goto usevar rem if the APPDATA variable does exist, put the database there set MONETDBDIR=%APPDATA%\MonetDB5 set MONETDBFARM="--dbpath=%MONETDBDIR%\dbfarm\demo" goto skipusevar :usevar rem if the APPDATA variable does not exist, put the database in the rem installation folder (i.e. default location, so no command line argument) REM Uniserv: Specify the DB installation folder rem set MONETDBDIR=%MONETDB%\var\MonetDB5 set MONETDBDIR=%MONETDB%\db set MONETDBFARM="--dbpath=%MONETDBDIR%\dbfarm\uniserv" :skipusevar rem the SQL log directory used to be in %MONETDBDIR%, but we now rem prefer it inside the dbfarm, so move it there if not exist "%MONETDBDIR%\sql_logs" goto skipmove for /d %%i in ("%MONETDBDIR%"\sql_logs\*) do move "%%i" "%MONETDBDIR%\dbfarm"\%%~ni\sql_logs rmdir "%MONETDBDIR%\sql_logs" :skipmove rem start the real server "%MONETDB%\bin\mserver5.exe" --set "prefix=%MONETDB%" --set mapi_open=true --set mapi_port=50000 --set max_clients=1000 --set "exec_prefix=%MONETDB%" %MONETDBFARM% %* if ERRORLEVEL 1 pause endlocal MonetDB JDBC Client: ================ We query the database via the latest MonetDB JDBC driver loaded from the central Maven repository: <groupId>monetdb</groupId> <artifactId>monetdb-jdbc</artifactId> <version>2.14</version> Database tables: ================ We create tables called "rule_result_<n>" CREATE TABLE "dq_scorecard"."rule_results_1" ("id" BIGINT NOT NULL PRIMARY KEY AUTO_INCREMENT, "record_id" VARCHAR(1024), "rule_1" INT, "rule_2" INT, ...); Each table has about 50+ INT columns. Query scenarios: ================ We have concurrent read access via JDBC driver and Spring Data Access Objects, the query look like that: *Client 1: 50 aggregate queries on 50 different columns on one database table like that: SELECT AVG("rule_n") FROM "dq_scorecard"."rule_results_1" WHERE "rule_n" >= 0 *Client 2: 50 aggregate queries on 50 different columns on another database table like that: SELECT AVG("rule_n") FROM "dq_scorecard"."rule_results_2" WHERE "rule_n" >= 0 *Client 3: 50 aggregate queries on 50 different columns on another database table like that: SELECT AVG("rule_n") FROM "dq_scorecard"."rule_results_3" WHERE "rule_n" >= 0 All clients run concurrently, at the moment we don't use a connection pool and create a connection for every query. During concurrent access the M5server crashes. The server crashes when each table has 100 records! Our production tables can have up to 50. 000. 000 of records! Kind regards, Michael From: users-list [mailto:users-list-bounces+michael.witt <mailto:users-list-bounces%2Bmichael.witt> =uniserv.com(a)monetdb.org <mailto:uniserv.com@monetdb.org> ] On Behalf Of michael.witt(a)uniserv.com <mailto:michael.witt@uniserv.com> Sent: Donnerstag, 28. Mai 2015 14:32 To: users-list(a)monetdb.org <mailto:users-list@monetdb.org> Subject: Concurrency test causes crash of MonetDB 5 server Hi, we experience a crash of MonetDB 5 server during tests of a multi-threaded client access via JDBC-Driver and MapiSocket: The application is already optimized to avoid “concurrency conflicts” because of the “Optimistic Concurrency Control” concept. The test simulates client access via two threads. All update operations (INSERT, UPDATE, DELETE, DROP TABLE, CREATE TABLE) are done in serial. All read operations are done in parallel to other read operations or to update operations. The test causes a crash of mserver5.exe after a few minutes. If the test uses just one thread, all read and update operations are done in serial and the server does not crash. What is the rule of thumb to avoid crashes when using several client threads? Crash details: Problem signature: Problem Event Name: APPCRASH Application Name: mserver5.exe Application Version: 0.0.0.0 Application Timestamp: 55389f58 Fault Module Name: StackHash_8ec6 Fault Module Version: 6.1.7601.18247 Fault Module Timestamp: 521eaf24 Exception Code: c0000374 Exception Offset: 00000000000c4102 OS Version: 6.1.7601.2.1.0.144.8 Locale ID: 1031 Additional Information 1: 8ec6 Additional Information 2: 8ec62499039d1d8234b025a62c725eb7 Additional Information 3: f8f3 Additional Information 4: f8f3e520847c0684b15d4080171779ef # MonetDB 5 server v11.19.9 "Oct2014-SP2" # Serving database 'uniserv', using 4 threads # Compiled for x86_64-pc-winnt/64bit with 64bit OIDs dynamically linked # Found 10.000 GiB available main-memory. .. # MonetDB/SQL module loaded Michael Witt _______________________________________________ users-list mailing list users-list(a)monetdb.org <mailto:users-list@monetdb.org> https://www.monetdb.org/mailman/listinfo/users-list

2 1

Field conversion sticks to subsequent queries
by Dennis Lorson 08 Jul '15

08 Jul '15

Hi all, I have a table with an INT column. The table is sorted by that column, and monetdb has recognized the sort order. While performing this query: -------------- SELECT * FROM table WHERE column = 12345678900; -------------- monetdb converts <table.column> to BIGINT/LNG as evidenced by the trace: -------------- 3612684 | X_17=<tmp_2352>[89617785] := batcalc.lng(X_16=<tmp_7142>[89617785]); -------------- This seems logical behavior since the condition value would overflow INT. (Of course the result set is empty by definition, but it’s a valid query) However, the same happens when I then perform subsequent queries with values that *can* be represented by INT: -------------- SELECT * FROM table WHERE column = 123; -------------- It is only when an explicit INT value is entered, that the type conversion is stopped for that query and subsequent ones: -------------- SELECT * FROM table WHERE column = CAST(123 AS INT); -------------- In other words, once the type conversion is performed once, it seems to “stick” to subsequent queries. Type conversion on that column takes ~4s on a 90MM row table here, while the regular query time is usually sub-millisecond. Entering the “wrong" value once will influence the DB performance heavily until restarted, which is far from a desirable scenario. Of course I could always clamp my value to INT range at the application level, but this is brittle, and the DB could do the right thing here. Is there a design consideration that I’m missing, or should I file this as a bug/performance issue? Thanks, Dennis

4 4

INNER JOIN with NULL values returning incorrect result
by Dennis Pallett 02 Jul '15

02 Jul '15

Hi all, I'm trying to do a simple INNER JOIN with two tables whereby the 1st table can contain NULL values in the join column. The query is as such: ---------- SELECT "B"."id", "A"."other_id" FROM "A" INNER JOIN "B" ON "B"."id" = "A"."other_id" WHERE "A"."afnemer" = '789' AND "A"."orgid" = 123; ---------- However this returns an incorrect result, such as: ---------- +------+----------+ | id | other_id | +======+==========+ | 143 | 143 | | 166 | 166 | | 147 | 147 | | 264 | 264 | | 323 | null | | 323 | 271 | | 271 | 275 | | 275 | 269 | | 269 | 150 | ---------- Clearly the JOIN has somehow mapped incorrect rows together from tables A and B. If I add a NOT NULL condition to the query on table A, I get correct results. This is the following query: ---------- SELECT "A"."other_id", "B"."id" , "A"."orgid" FROM "A" INNER JOIN "B" ON "B"."id" = "A"."other_id" WHERE "A"."afnemer" = '789' AND "A"."orgid" = 123 AND "A".other_id IS NOT NULL; ---------- Is this a known bug in MonetDB? Should I always add a NOT NULL condition to my JOIN queries? Best regards, Dennis Pallett

2 3

Can't start monetdb 11.19.15
by Brian Hood 01 Jul '15

01 Jul '15

Hi All, I seems when i look at the output i of the error log it seems there are missing methods, i have seen this as a bug report from a while ago online but i can't see the see the solution. Any ideas ? [root@dev-vnc-01 /]# monetdbd start /var/monetdb5/dbfarm [root@dev-vnc-01 /]# [root@dev-vnc-01 /]# monetdb status name state health remarks threatmonitor S 5d 100% 7s mapi:monetdb://dev-vnc-01:50000/threatmonitor [root@dev-vnc-01 /]# monetdb start threatmonitor starting database 'threatmonitor'... FAILED start: starting 'threatmonitor' failed: database 'threatmonitor' started up, but failed to open up a communication channel [root@dev-vnc-01 /]# pacman -Q monetdb monetdb 11.19.15-1 [root@dev-vnc-01 /]# monetdb -v MonetDB Database Server Toolkit v1.1 (Oct2014-SP4) [root@dev-vnc-01 /]# tail -70 /var/monetdb5/dbfarm/merovingian.log 2015-06-25 23:31:58 MSG merovingian[293]: monitoring dbfarm /var/monetdb5/dbfarm 2015-06-25 23:32:18 MSG merovingian[293]: accepting connections on TCP socket 0.0.0.0:50000 2015-06-25 23:32:18 MSG merovingian[293]: accepting connections on UNIX domain socket /tmp/.s.monetdb.50000 2015-06-25 23:32:38 MSG discovery[293]: listening for UDP messages on 0.0.0.0:50000 2015-06-25 23:32:38 MSG control[293]: accepting connections on UNIX domain socket /tmp/.s.merovingian.50000 2015-06-25 23:32:38 MSG discovery[293]: new neighbour dev-vnc-01 (dev-vnc-01) 2015-06-25 23:32:53 MSG control[293]: (local): created database 'threatmonitor' 2015-06-25 23:32:59 MSG control[293]: (local): served status list 2015-06-25 23:32:59 MSG control[293]: (local): released database 'threatmonitor' 2015-06-25 23:32:59 MSG discovery[293]: new database mapi:monetdb://dev-vnc-01:50000/threatmonitor (ttl=660s) 2015-06-25 23:33:08 MSG merovingian[293]: starting database 'threatmonitor', up min/avg/max: 0s/0s/0s, crash average: 0.00 0.00 0.00 (0-0=0) 2015-06-25 23:33:08 MSG merovingian[293]: sending process 304 (database 'threatmonitor') the TERM signal 2015-06-25 23:33:08 MSG threatmonitor[304]: arguments: /usr/bin/mserver5 --dbpath=/var/monetdb5/dbfarm/threatmonitor --set merovingian_uri=mapi:monetdb://dev-vnc-01:50000/threatmonitor --set mapi_open=false --set mapi_port=0 --set mapi_usock=/var/monetdb5/dbfarm/threatmonitor/.mapi.sock --set monet_vault_key=/var/monetdb5/dbfarm/threatmonitor/.vaultkey --set gdk_nr_threads=2 --set max_clients=64 --set sql_optimizer=default_pipe --set monet_daemon=yes 2015-06-25 23:33:08 MSG threatmonitor[304]: # MonetDB 5 server v11.19.15 "Oct2014-SP4" 2015-06-25 23:33:08 MSG threatmonitor[304]: # Serving database 'threatmonitor', using 2 threads 2015-06-25 23:33:08 MSG threatmonitor[304]: # Compiled for x86_64-unknown-linux-gnu/64bit with 64bit OIDs dynamically linked 2015-06-25 23:33:08 MSG threatmonitor[304]: # Found 7.411 GiB available main-memory. 2015-06-25 23:33:08 MSG threatmonitor[304]: # Copyright (c) 1993-July 2008 CWI. 2015-06-25 23:33:08 MSG threatmonitor[304]: # Copyright (c) August 2008-2015 MonetDB B.V., all rights reserved 2015-06-25 23:33:08 MSG threatmonitor[304]: # Visit http://www.monetdb.org/ for further information 2015-06-25 23:33:08 MSG threatmonitor[304]: !TypeException:user.main[1412]:object code for command rapi.prelude missing 2015-06-25 23:33:08 MSG threatmonitor[304]: !TypeException:user.main[1412]:'rapi.prelude' undefined in: _1412:void := rapi.prelude() 2015-06-25 23:33:08 MSG merovingian[293]: database 'threatmonitor' (304) has exited with exit status 0 2015-06-25 23:33:08 MSG merovingian[293]: database 'threatmonitor' has shut down 2015-06-25 23:33:08 ERR merovingian[293]: client error: database 'threatmonitor' started up, but failed to open up a communication channel 2015-06-25 23:33:08 MSG merovingian[293]: starting database 'threatmonitor', up min/avg/max: 0s/0s/0s, crash average: 0.00 0.00 0.00 (1-1=0) 2015-06-25 23:33:09 MSG merovingian[293]: sending process 307 (database 'threatmonitor') the TERM signal 2015-06-25 23:33:09 MSG threatmonitor[307]: arguments: /usr/bin/mserver5 --dbpath=/var/monetdb5/dbfarm/threatmonitor --set merovingian_uri=mapi:monetdb://dev-vnc-01:50000/threatmonitor --set mapi_open=false --set mapi_port=0 --set mapi_usock=/var/monetdb5/dbfarm/threatmonitor/.mapi.sock --set monet_vault_key=/var/monetdb5/dbfarm/threatmonitor/.vaultkey --set gdk_nr_threads=2 --set max_clients=64 --set sql_optimizer=default_pipe --set monet_daemon=yes 2015-06-25 23:33:09 MSG threatmonitor[307]: # MonetDB 5 server v11.19.15 "Oct2014-SP4" 2015-06-25 23:33:09 MSG threatmonitor[307]: # Serving database 'threatmonitor', using 2 threads 2015-06-25 23:33:09 MSG threatmonitor[307]: # Compiled for x86_64-unknown-linux-gnu/64bit with 64bit OIDs dynamically linked 2015-06-25 23:33:09 MSG threatmonitor[307]: # Found 7.411 GiB available main-memory. 2015-06-25 23:33:09 MSG threatmonitor[307]: # Copyright (c) 1993-July 2008 CWI. 2015-06-25 23:33:09 MSG threatmonitor[307]: # Copyright (c) August 2008-2015 MonetDB B.V., all rights reserved 2015-06-25 23:33:09 MSG threatmonitor[307]: # Visit http://www.monetdb.org/ for further information 2015-06-25 23:33:09 MSG threatmonitor[307]: !TypeException:user.main[1412]:object code for command rapi.prelude missing 2015-06-25 23:33:09 MSG threatmonitor[307]: !TypeException:user.main[1412]:'rapi.prelude' undefined in: _1412:void := rapi.prelude() 2015-06-25 23:33:09 MSG merovingian[293]: database 'threatmonitor' (307) has exited with exit status 0 2015-06-25 23:33:09 MSG merovingian[293]: database 'threatmonitor' has shut down 2015-06-25 23:33:13 ERR merovingian[293]: client error: database 'threatmonitor' started up, but failed to open up a communication channel 2015-06-26 00:08:20 MSG control[293]: (local): served status list 2015-06-26 00:08:20 ERR control[293]: (local): received stop signal for non running database: threatmonitor 2015-06-26 00:08:29 MSG merovingian[293]: caught SIGTERM, starting shutdown sequence 2015-06-26 00:08:30 MSG control[293]: control channel closed 2015-06-26 00:08:31 MSG merovingian[293]: Merovingian 1.7 stopped 2015-07-01 12:57:10 MSG merovingian[9]: Merovingian 1.7 (Oct2014-SP4) starting 2015-07-01 12:57:10 MSG merovingian[9]: monitoring dbfarm /var/monetdb5/dbfarm 2015-07-01 12:57:30 MSG merovingian[9]: accepting connections on TCP socket 0.0.0.0:50000 2015-07-01 12:57:30 MSG merovingian[9]: accepting connections on UNIX domain socket /tmp/.s.monetdb.50000 2015-07-01 12:57:50 MSG discovery[9]: listening for UDP messages on 0.0.0.0:50000 2015-07-01 12:57:50 MSG control[9]: accepting connections on UNIX domain socket /tmp/.s.merovingian.50000 2015-07-01 12:57:50 MSG discovery[9]: new neighbour dev-vnc-01 (dev-vnc-01) 2015-07-01 12:57:50 MSG discovery[9]: new database mapi:monetdb://dev-vnc-01:50000/threatmonitor (ttl=660s) 2015-07-01 12:57:54 MSG control[9]: (local): served status list 2015-07-01 12:58:03 MSG control[9]: (local): served status list 2015-07-01 12:58:03 MSG merovingian[9]: starting database 'threatmonitor', up min/avg/max: 0s/7s/27s, crash average: 0.00 0.00 0.00 (10-10=0) 2015-07-01 12:58:03 MSG merovingian[9]: sending process 15 (database 'threatmonitor') the TERM signal 2015-07-01 12:58:03 MSG threatmonitor[15]: arguments: /usr/bin/mserver5 --dbpath=/var/monetdb5/dbfarm/threatmonitor --set merovingian_uri=mapi:monetdb://dev-vnc-01:50000/threatmonitor --set mapi_open=false --set mapi_port=0 --set mapi_usock=/var/monetdb5/dbfarm/threatmonitor/.mapi.sock --set monet_vault_key=/var/monetdb5/dbfarm/threatmonitor/.vaultkey --set gdk_nr_threads=2 --set max_clients=64 --set sql_optimizer=default_pipe --set monet_daemon=yes 2015-07-01 12:58:03 MSG threatmonitor[15]: # MonetDB 5 server v11.19.15 "Oct2014-SP4" 2015-07-01 12:58:03 MSG threatmonitor[15]: # Serving database 'threatmonitor', using 2 threads 2015-07-01 12:58:03 MSG threatmonitor[15]: # Compiled for x86_64-unknown-linux-gnu/64bit with 64bit OIDs dynamically linked 2015-07-01 12:58:03 MSG threatmonitor[15]: # Found 7.411 GiB available main-memory. 2015-07-01 12:58:03 MSG threatmonitor[15]: # Copyright (c) 1993-July 2008 CWI. 2015-07-01 12:58:03 MSG threatmonitor[15]: # Copyright (c) August 2008-2015 MonetDB B.V., all rights reserved 2015-07-01 12:58:03 MSG threatmonitor[15]: # Visit http://www.monetdb.org/ for further information 2015-07-01 12:58:03 MSG threatmonitor[15]: !TypeException:user.main[1412]:object code for command rapi.prelude missing 2015-07-01 12:58:03 MSG threatmonitor[15]: !TypeException:user.main[1412]:'rapi.prelude' undefined in: _1412:void := rapi.prelude() 2015-07-01 12:58:03 MSG merovingian[9]: database 'threatmonitor' (15) has exited with exit status 0 2015-07-01 12:58:03 MSG merovingian[9]: database 'threatmonitor' has shut down 2015-07-01 12:58:03 ERR control[9]: (local): failed to fork mserver: database 'threatmonitor' started up, but failed to open up a communication channel Regards, Brian Hood

2 3

MonetDB performance disappointing
by Martin Schwitalla 30 Jun '15

30 Jun '15

Hello, I`m doing some experiments with a database, which originally runs on PostgreSQL. But some queries take very long on this system. Especially one query type, which takes about 14s to finish. The idea was to port it to MonetDB, because the database is not that big (~8GB) and it`s an analytical task. But suprisingly MonetDB takes even longer to finish. With the default pipeline it takes about 22-23s. I tried various pipelines and the minimal_pipe was the best. It finished the task in about 16,5s, better, but still not great. I`m asking myself what I could do to further improve the performance. I added some primary and foreign key constraints and set the database to readonly, but it didn`t do much. The trace tells me that some joins take the most time and some subselects also take much time. But nothing besides that. The query has 10 joins. Do you have any advices for me? Best regards, Martin

5 11

Concurrency test causes crash of MonetDB 5 server
by michael.witt＠uniserv.com 30 Jun '15

30 Jun '15

Hi, we experience a crash of MonetDB 5 server during tests of a multi-threaded client access via JDBC-Driver and MapiSocket: The application is already optimized to avoid "concurrency conflicts" because of the "Optimistic Concurrency Control" concept. The test simulates client access via two threads. All update operations (INSERT, UPDATE, DELETE, DROP TABLE, CREATE TABLE) are done in serial. All read operations are done in parallel to other read operations or to update operations. The test causes a crash of mserver5.exe after a few minutes. If the test uses just one thread, all read and update operations are done in serial and the server does not crash. What is the rule of thumb to avoid crashes when using several client threads? Crash details: Problem signature: Problem Event Name: APPCRASH Application Name: mserver5.exe Application Version: 0.0.0.0 Application Timestamp: 55389f58 Fault Module Name: StackHash_8ec6 Fault Module Version: 6.1.7601.18247 Fault Module Timestamp: 521eaf24 Exception Code: c0000374 Exception Offset: 00000000000c4102 OS Version: 6.1.7601.2.1.0.144.8 Locale ID: 1031 Additional Information 1: 8ec6 Additional Information 2: 8ec62499039d1d8234b025a62c725eb7 Additional Information 3: f8f3 Additional Information 4: f8f3e520847c0684b15d4080171779ef # MonetDB 5 server v11.19.9 "Oct2014-SP2" # Serving database 'uniserv', using 4 threads # Compiled for x86_64-pc-winnt/64bit with 64bit OIDs dynamically linked # Found 10.000 GiB available main-memory. .. # MonetDB/SQL module loaded Michael Witt

2 2

JDBC PreparedStatement crashing the Database!!
by Vijay Krishna 29 Jun '15

29 Jun '15

Hi, I recently faced an issue with MonetDB JDBC PreparedStatement. I have a table with 15 columns out of which 14 are foreign keys to columns in another 14 tables. When I try to insert a valid record (that satisfies all the FK relations) using PreparedStatement, the database gets crashed while executing the line, preparedStatement = connection.prepareStatement(sqlString); On trial and error testing, I found that this works very fine until a table has 13 FK relations. If the tables has 14 FKs, then the database crashes. On a brighter side, this operation works very fine in terminal (mclient) with 14 FKs even. Is there any limit on the FKs with prepared statement? Or what am I doing wrong here? Any help much appreciated. Thanks in advance. Regards, Vijayakrishna.P. Mobile : (+91) 9500402305.

2 2

Is minimal_pipe enforced on long MAL plans?
by Roberto Cornacchia 22 Jun '15

22 Jun '15

Hi there, Is minimal_pipe enforced under any condition? For example when the the MAL plan generated by rel2bin is longer than a given threshold? Context: I have a pretty long query which results in a MAL plan of 33K lines. Though the query is a bit long, I still would expect a 2-3K lines MAL plan. The plan indeed contains the same pieces of code re-evaluated over and over. It first, I simply blamed commonTerms not doing its job. The thing is, the MAL plans generated with minimal_pipe and default_pipe are absolutely identical for this query. Hence my question above. Best, Roberto

3 3

MonetDb Udf
by Fatma Nur Özdemir 19 Jun '15

19 Jun '15

Hello, We are using MonetDB5 server v11.19.15 “Oct2014-SP4” windows version and we want to use a function that we wrote in C programming language. However, we cannot use this user defined function within MonetDB. Maybe, this problem is about windows version because we are not sure that windows version of MonetDb supports this feature. If anybody knows something about this, please inform us. Thank you in advance. Fatma Nur [http://www.bilyoner.com/fm/mail/mailalti_Bmw-applewatch.jpg]<http://www.bilyoner.com/kampanyalar/2015haziranagustosmp.jspx> Bu elektronik posta ve onunla iletilen butun dosyalar sadece gondericisi tarafindan almasi amaclanan yetkili gercek ya da tuzel kisinin kullanimi icindir. Eger soz konusu yetkili alici degilseniz bu elektronik postanin icerigini aciklamaniz, kopyalamaniz, yonlendirmeniz ve kullanmaniz hukuka aykiri ve yasaktir, eger bu mesaji yanlislikla aldiysaniz lutfen gondereni ile temasa geciniz ve bu elektronik postayi siliniz. B�LYONER �nteraktif Hizmetleri A.S. bu mesajin icerdigi bilgilerin dogrulugu veya eksiksiz oldugu konusunda herhangi bir garanti vermemektedir. Bu nedenle bu bilgilerin ne sekilde olursa olsun iceriginden, iletilmesinden, alinmasindan ve saklanmasindan sorumlu degildir. Bu mesajdaki gorusler yalnizca gonderen kisiye aittir ve Bilyoner �nteraktif Hizmetleri A.S. nin goruslerini yansitmayabilir. Bu e-posta bilinen butun bilgisayar viruslerine karsi taranmistir.

1 0

Re: Monetdb-R integration
by imad hajj chahine 19 Jun '15

19 Jun '15

Hi Hannes, This error is due that the "2-age" column has more than 2 distinct values, there is a space after some values. can you run a trim on the "2-Age" and then try it again. As for the vector thing, I didnt get the point, shouldnt the db engine split the data into vectors and feed the aggregate function which return one data point? Thanks On Fri, Jun 19, 2015 at 11:12 AM, Hannes Mühleisen <Hannes.Muehleisen(a)cwi.nl > wrote: > I am running attached script, which I pieced together from your emails. > This does not run, it produces the following error: > > Error running R expression. Error message: Error in t.test.formula(arg1 ~ > arg2) : > grouping factor must have exactly 2 levels > Calls: as.data.frame -> <Anonymous> -> t.test -> t.test.formula > > > > > In general, for aggregations, the R code needs to return a vector with a > single entry for each group. > > > Best, > > Hannes > > > > > On 19 Jun 2015, at 09:44, imad hajj chahine <imad.hajj.chahine(a)gmail.com> > wrote: > > > > Thanks, if you need more information from my side let me know. > > > > Also can you answer me on the following when i use 2 functions one to > retrieve Pvalue and one for the stat value: > > Am I doing the correct way to retrieve the stat and p-value, is the > ttest get executed twice in this case? is there a more optimal way where i > can retrieve both information. > > > > On Fri, Jun 19, 2015 at 10:39 AM, Hannes Mühleisen < > Hannes.Muehleisen(a)cwi.nl> wrote: > > Looking into this at the moment. > > > > > On 16 Jun 2015, at 09:56, Dimitar Nedev < > dimitar.nedev(a)monetdbsolutions.com> wrote: > > > > > > Hannes, do you know if the STD_DEV is the issue there or if the > queries should actually yield different results? > > > > > > Dimitar > > > > > >> On 2015-Jun-15, at 13:28 , imad hajj chahine < > imad.hajj.chahine(a)gmail.com> wrote: > > >> > > >> Hi, > > >> > > >> Shouldn't we have the same result from running the first query and > the second query? > > >> after all its an aggregate function and should return the test for > each group. > > >> > > >> I checked that monetdb had problem with running group by with > statistics functions like STD_DEV, are these 2 issues related? > > >> > > >> Thank you. > > >> > > >> > > >> > > >> > > >> On Mon, Jun 15, 2015 at 1:52 PM, Dimitar Nedev < > dimitar.nedev(a)monetdbsolutions.com> wrote: > > >> Hi Imad, > > >> > > >> Here is the output I got from running the queries. Note, this might > not be entirely representative since I did clean-up some data before > loading it. > > >> > > >> sql>select "2-Age", "tone"."ttest2"("9-Score",6.08963) from > "tone"."Marketing_Loyalty_4700298d-9862-40b3-9028-b0f15dab9dea" where > "2-Age" is not null group by "2-Age"; > > >> +---------+--------------------------+ > > >> | 2-Age | L1 | > > >> +=========+==========================+ > > >> | [10-20] | 0.89311103869734754 | > > >> +---------+--------------------------+ > > >> 3 tuples (3.218ms) > > >> > > >> sql>select '[10-20]' as "2-Age","tone"."ttest2"("9-Score",6.08963) > from "tone"."Marketing_Loyalty_4700298d-9862-40b3-9028-b0f15dab9dea" where > "2-Age" = '[10-20]' > > >> more>union all > > >> more>select '[21-30]' as "2-Age","tone"."ttest2"("9-Score",6.08963) > from "tone"."Marketing_Loyalty_4700298d-9862-40b3-9028-b0f15dab9dea" where > "2-Age" = '[21-30]' > > >> more>union all > > >> more>select '[> 30]' as "2-Age","tone"."ttest2"("9-Score",6.08963) > from "tone"."Marketing_Loyalty_4700298d-9862-40b3-9028-b0f15dab9dea" where > "2-Age" = '> 30'; > > >> +---------+--------------------------+ > > >> | 2-Age | L1 | > > >> +=========+==========================+ > > >> | [10-20] | 0.14150742355349472 | > > >> | [21-30] | 0.32872830865003566 | > > >> | [> 30] | 0.37127075363219841 | > > >> +---------+--------------------------+ > > >> 3 tuples (8.825ms) > > >> > > >> > > >> sql>select "tone"."ttest2samplesStatistic"("9-Score", "4-Gender") as > "stat", "tone"."ttest2samples"("9-Score", "4-Gender") as "pvalue" from > "tone"."Marketing_Loyalty_4700298d-9862-40b3-9028-b0f15dab9dea" group by > "2-Age"; > > >> +--------------------------+--------------------------+ > > >> | stat | pvalue | > > >> +==========================+==========================+ > > >> | -2.1588506639013736 | 0.032555033970368859 | > > >> +--------------------------+--------------------------+ > > >> 1 tuple (13.287ms) > > >> > > >> > > >> I dropped the aggregate with: > > >> sql>DROP AGGREGATE "tone"."ttest2samplesStatistic"; > > >> operation successful (2.049ms) > > >> > > >> > > >> Best regards, > > >> Dimitar > > >> > > >>> On 2015-Jun-12, at 00:46 , imad hajj chahine < > imad.hajj.chahine(a)gmail.com> wrote: > > >>> > > >>> Thank you Dimitar, > > >>> > > >>> I was using Squirrel to connect from my host machine to the VM. > > >>> > > >>> Now when I connect using SSH-client and run the short group by query > it works but the result is not similar to the union queries (PS: i also run > it in the VM direclty and had the same result) > > >>> > > >>> > > >>> +---------+--------------------------+ > > >>> | 2-Age | pval | > > >>> +=========+==========================+ > > >>> | [21-30] | 0.99996611809271041 | > > >>> +---------+--------------------------+ > > >>> 3 tuples (2.638ms) > > >>> > > >>> > > >>> What i am trying to achieve is to run a one sample ttest for all the > age categories, and it seems that the group by is ignored. > > >>> > > >>> I have also created 2 functions for the two sample ttest: > > >>> > > >>> CREATE AGGREGATE "tone"."ttest2samples"(arg1 double, arg2 > varchar(250)) RETURNS double LANGUAGE R { t.test(arg1 ~ arg2)$p.value }; > > >>> CREATE AGGREGATE "tone"."ttest2samplesStatistic"(arg1 double, arg2 > varchar(250)) RETURNS double LANGUAGE R { t.test(arg1 ~ arg2)$statistic }; > > >>> > > >>> select "tone"."ttest2samplesStatistic"("9-Score", "4-Gender") as > "stat", "tone"."ttest2samples"("9-Score", "4-Gender") as "pvalue" from > "tone"."Marketing_Loyalty_4700298d-9862-40b3-9028-b0f15dab9dea" group by > "2-Age" > > >>> > > >>> Am I doing the correct way to retrieve the stat and p-value, is the > ttest get executed twice in this case? is there a more optimal way where i > can retrieve both information. Please note that I also have the same > grouping issue as before. > > >>> > > >>> Do you have the same behavior, or I am missing something in the > creation of these functions. > > >>> > > >>> A final question, how do u drop a R function? i am trying to execute > the following: > > >>> drop all function "tone"."ttest2samplesStat" > > >>> > > >>> > > >>> Thank you. > > >>> > > >>> > > >>> > > >>> On Thu, Jun 11, 2015 at 11:28 PM, Dimitar Nedev < > dimitar.nedev(a)monetdbsolutions.com> wrote: > > >>> Hi Imad, > > >>> > > >>> Hannes asked me to look at your issue with the MonetDB-R VM. > > >>> > > >>> I loaded the data and ran the queries you mentioned, but I did not > encounter any issues. This holds for both the short and the union queries. > I am also not familiar with the issue you report, nor its cause. At this > time I think it is local to your instance. If you can export your db, or > re-load the data, I would recommend you to create a new instance with a > fresh image and try again. If you import the image as a new VM instance, > you can keep your old one around as well. > > >>> > > >>> Please note that during the test: > > >>> - I ran as the admin (monetdb) user, as the one created default on > the VM image cannot create new schemas. > > >>> - I have to clean the data a bit up to load it, removing dozen lines > or so. > > >>> - Renamed ttest1 to ttest2 to match the queries. > > >>> > > >>> Best regards, > > >>> Dimitar > > >>> > > >>>>>>>>> Begin forwarded message: > > >>>>>>>>> > > >>>>>>>>> From: imad hajj chahine <imad.hajj.chahine(a)gmail.com> > > >>>>>>>>> Subject: Re: Monetdb-R integration > > >>>>>>>>> Date: 10 Jun 2015 10:11:48 CEST > > >>>>>>>>> To: Hannes Mühleisen <Hannes.Muehleisen(a)cwi.nl> > > >>>>>>>>> > > >>>>>>>>> Hi, > > >>>>>>>>> > > >>>>>>>>> I am using the virtual box provided from > https://www.monetdbsolutions.com/solutions/cloud/vm: > > >>>>>>>>> OS:linux version 3.10.0-123.9.3.el7.x86_64 ( > builder(a)kbuilder.dev.centos.org) > > >>>>>>>>> Monetdb: V1.7: (Oct2014-SP1) > > >>>>>>>>> R: 3.1.2 (2014-10-31) > > >>>>>>>>> > > >>>>>>>>> > > >>>>>>>>> > > >>>>>>>>> create table > "tone"."Marketing_Loyalty_4700298d-9862-40b3-9028-b0f15dab9dea" ("Consumer > Number" INTEGER,"1-Disloyalty" varchar(250),"2-Age" varchar(250),"3-Amount > transferred" INTEGER,"4-Gender" varchar(250),"5-Citiz enship" > varchar(250),"6- Profession" varchar(250),"7-City" varchar(250),"8-1 NC > Billboard" varchar(250),"8-2 NC Friends" varchar(250),"8-3 NC Magazine" > varchar(250),"8-4 NC Others" varchar(250),"9-Score" decimal(18,3),"10- Type > of operation" varchar(250),"11-1 Welcoming" INTEGER,"11-2 Waiting time" > INTEGER,"11-3 Time to finish request" INTEGER,"11-4 Fees" INTEGER,"11-5 > Courtesy of employees" INTEGER,"11-6 Branch layout" INTEGER,"11-7 Attention > to special requests" INTEGER,"12- Total time for service" > decimal(18,3),"13- Distance from POS" decimal(18,3),"14- Weight" INTEGER) > > >>>>>>>>> > > >>>>>>>>> COPY offset 2 INTO > "tone"."Marketing_Loyalty_4700298d-9862-40b3-9028-b0f15dab9dea" FROM > '/home/App_Data/TestingDataSimple_2003_Marketing_Loyalty_4700298d-9862-40b3-9028-b0f15dab9dea.csv' > USING DELIMITERS ',','\n','"'; > > >>>>>>>>> > > >>>>>>>>> Thanks > > >>>>>>>>> > > >>>>>>>>> On Wed, Jun 10, 2015 at 10:50 AM, Hannes Mühleisen < > Hannes.Muehleisen(a)cwi.nl> wrote: > > >>>>>>>>> Hello Imad, > > >>>>>>>>> > > >>>>>>>>> which version of MonetDB and R are you using? Which operating > system? > > >>>>>>>>> > > >>>>>>>>> Can you please also include the commands you used to load your > CSV into MonetDB/ create the table? > > >>>>>>>>> > > >>>>>>>>> Thanks, > > >>>>>>>>> > > >>>>>>>>> Hannes > > >>>>>>>>> > > >>>>>>>>>> On 10 Jun 2015, at 00:25, imad hajj chahine < > imad.hajj.chahine(a)gmail.com> wrote: > > >>>>>>>>>> > > >>>>>>>>>> Hi Hannes, > > >>>>>>>>>> > > >>>>>>>>>> I am testing Monetdb-R integration to do some statistical > test, i followed the tutorial you posted on youtube and i have created the > following function: > > >>>>>>>>>> > > >>>>>>>>>> CREATE AGGREGATE "tone"."ttest1"(arg1 double, arg2 double) > RETURNS double LANGUAGE R { t.test(arg1, arg2)$p.value }; > > >>>>>>>>>> > > >>>>>>>>>> this function will basically do a one sample t-test. > > >>>>>>>>>> > > >>>>>>>>>> I have loaded the sample attached file into a demo database > and trying to run the following query: > > >>>>>>>>>> > > >>>>>>>>>> select "2-Age", "tone"."ttest2"("9-Score",6.08963) from > "tone"."Marketing_Loyalty_4700298d-9862-40b3-9028-b0f15dab9dea" where > "2-Age" is not null group by "2-Age" > > >>>>>>>>>> > > >>>>>>>>>> The following error happened > > >>>>>>>>>> Error: protocol violation: unexpected line in data block: . > > >>>>>>>>>> SQLState: M0M10 > > >>>>>>>>>> ErrorCode: 0 > > >>>>>>>>>> > > >>>>>>>>>> alternatively i managed to have the intended result by > running this query: > > >>>>>>>>>> > > >>>>>>>>>> select '[10-20]' as > "2-Age","tone"."ttest2"("9-Score",6.08963) from > "tone"."Marketing_Loyalty_4700298d-9862-40b3-9028-b0f15dab9dea" where > "2-Age" = '[10-20]' > > >>>>>>>>>> union all > > >>>>>>>>>> select '[21-30]' as > "2-Age","tone"."ttest2"("9-Score",6.08963) from > "tone"."Marketing_Loyalty_4700298d-9862-40b3-9028-b0f15dab9dea" where > "2-Age" = '[21-30]' > > >>>>>>>>>> union all > > >>>>>>>>>> select '[> 30]' as "2-Age","tone"."ttest2"("9-Score",6.08963) > from "tone"."Marketing_Loyalty_4700298d-9862-40b3-9028-b0f15dab9dea" where > "2-Age" = '> 30' > > >>>>>>>>>> > > >>>>>>>>>> > > >>>>>>>>>> any help or guidance is appreciated. > > >>>>>>>>>> Thank you. > > >>>>>>>>>> > > >>>>>>>>>> > <TestingDataSimple_2003_Marketing_Loyalty_4700298d-9862-40b3-9028-b0f15dab9dea.csv> > > >>>>>>>>> > > >>>>>>>>> > > >>>>>>>> > > >>>>>>> > > >>>>>> > > >>>>> > > >>>> > > >>> > > >> > > >> > > > > > > > > > >

1 0

Fwd: Monetdb-R integration
by Hannes Mühleisen 19 Jun '15

19 Jun '15

(Forwarding to users list, free support is public) > Begin forwarded message: > > From: Hannes Mühleisen <hannes.muehleisen(a)cwi.nl> > Subject: Re: Monetdb-R integration > Date: 19 Jun 2015 10:12:48 CEST > To: imad hajj chahine <imad.hajj.chahine(a)gmail.com> > Cc: Dimitar Nedev <dimitar.nedev(a)monetdbsolutions.com> > > I am running attached script, which I pieced together from your emails. This does not run, it produces the following error: > > Error running R expression. Error message: Error in t.test.formula(arg1 ~ arg2) : > grouping factor must have exactly 2 levels > Calls: as.data.frame -> <Anonymous> -> t.test -> t.test.formula > > > > In general, for aggregations, the R code needs to return a vector with a single entry for each group. > > > Best, > > Hannes > > > >> On 19 Jun 2015, at 09:44, imad hajj chahine <imad.hajj.chahine(a)gmail.com> wrote: >> >> Thanks, if you need more information from my side let me know. >> >> Also can you answer me on the following when i use 2 functions one to retrieve Pvalue and one for the stat value: >> Am I doing the correct way to retrieve the stat and p-value, is the ttest get executed twice in this case? is there a more optimal way where i can retrieve both information. >> >> On Fri, Jun 19, 2015 at 10:39 AM, Hannes Mühleisen <Hannes.Muehleisen(a)cwi.nl> wrote: >> Looking into this at the moment. >> >>> On 16 Jun 2015, at 09:56, Dimitar Nedev <dimitar.nedev(a)monetdbsolutions.com> wrote: >>> >>> Hannes, do you know if the STD_DEV is the issue there or if the queries should actually yield different results? >>> >>> Dimitar >>> >>>> On 2015-Jun-15, at 13:28 , imad hajj chahine <imad.hajj.chahine(a)gmail.com> wrote: >>>> >>>> Hi, >>>> >>>> Shouldn't we have the same result from running the first query and the second query? >>>> after all its an aggregate function and should return the test for each group. >>>> >>>> I checked that monetdb had problem with running group by with statistics functions like STD_DEV, are these 2 issues related? >>>> >>>> Thank you. >>>> >>>> >>>> >>>> >>>> On Mon, Jun 15, 2015 at 1:52 PM, Dimitar Nedev <dimitar.nedev(a)monetdbsolutions.com> wrote: >>>> Hi Imad, >>>> >>>> Here is the output I got from running the queries. Note, this might not be entirely representative since I did clean-up some data before loading it. >>>> >>>> sql>select "2-Age", "tone"."ttest2"("9-Score",6.08963) from "tone"."Marketing_Loyalty_4700298d-9862-40b3-9028-b0f15dab9dea" where "2-Age" is not null group by "2-Age"; >>>> +---------+--------------------------+ >>>> | 2-Age | L1 | >>>> +=========+==========================+ >>>> | [10-20] | 0.89311103869734754 | >>>> +---------+--------------------------+ >>>> 3 tuples (3.218ms) >>>> >>>> sql>select '[10-20]' as "2-Age","tone"."ttest2"("9-Score",6.08963) from "tone"."Marketing_Loyalty_4700298d-9862-40b3-9028-b0f15dab9dea" where "2-Age" = '[10-20]' >>>> more>union all >>>> more>select '[21-30]' as "2-Age","tone"."ttest2"("9-Score",6.08963) from "tone"."Marketing_Loyalty_4700298d-9862-40b3-9028-b0f15dab9dea" where "2-Age" = '[21-30]' >>>> more>union all >>>> more>select '[> 30]' as "2-Age","tone"."ttest2"("9-Score",6.08963) from "tone"."Marketing_Loyalty_4700298d-9862-40b3-9028-b0f15dab9dea" where "2-Age" = '> 30'; >>>> +---------+--------------------------+ >>>> | 2-Age | L1 | >>>> +=========+==========================+ >>>> | [10-20] | 0.14150742355349472 | >>>> | [21-30] | 0.32872830865003566 | >>>> | [> 30] | 0.37127075363219841 | >>>> +---------+--------------------------+ >>>> 3 tuples (8.825ms) >>>> >>>> >>>> sql>select "tone"."ttest2samplesStatistic"("9-Score", "4-Gender") as "stat", "tone"."ttest2samples"("9-Score", "4-Gender") as "pvalue" from "tone"."Marketing_Loyalty_4700298d-9862-40b3-9028-b0f15dab9dea" group by "2-Age"; >>>> +--------------------------+--------------------------+ >>>> | stat | pvalue | >>>> +==========================+==========================+ >>>> | -2.1588506639013736 | 0.032555033970368859 | >>>> +--------------------------+--------------------------+ >>>> 1 tuple (13.287ms) >>>> >>>> >>>> I dropped the aggregate with: >>>> sql>DROP AGGREGATE "tone"."ttest2samplesStatistic"; >>>> operation successful (2.049ms) >>>> >>>> >>>> Best regards, >>>> Dimitar >>>> >>>>> On 2015-Jun-12, at 00:46 , imad hajj chahine <imad.hajj.chahine(a)gmail.com> wrote: >>>>> >>>>> Thank you Dimitar, >>>>> >>>>> I was using Squirrel to connect from my host machine to the VM. >>>>> >>>>> Now when I connect using SSH-client and run the short group by query it works but the result is not similar to the union queries (PS: i also run it in the VM direclty and had the same result) >>>>> >>>>> >>>>> +---------+--------------------------+ >>>>> | 2-Age | pval | >>>>> +=========+==========================+ >>>>> | [21-30] | 0.99996611809271041 | >>>>> +---------+--------------------------+ >>>>> 3 tuples (2.638ms) >>>>> >>>>> >>>>> What i am trying to achieve is to run a one sample ttest for all the age categories, and it seems that the group by is ignored. >>>>> >>>>> I have also created 2 functions for the two sample ttest: >>>>> >>>>> CREATE AGGREGATE "tone"."ttest2samples"(arg1 double, arg2 varchar(250)) RETURNS double LANGUAGE R { t.test(arg1 ~ arg2)$p.value }; >>>>> CREATE AGGREGATE "tone"."ttest2samplesStatistic"(arg1 double, arg2 varchar(250)) RETURNS double LANGUAGE R { t.test(arg1 ~ arg2)$statistic }; >>>>> >>>>> select "tone"."ttest2samplesStatistic"("9-Score", "4-Gender") as "stat", "tone"."ttest2samples"("9-Score", "4-Gender") as "pvalue" from "tone"."Marketing_Loyalty_4700298d-9862-40b3-9028-b0f15dab9dea" group by "2-Age" >>>>> >>>>> Am I doing the correct way to retrieve the stat and p-value, is the ttest get executed twice in this case? is there a more optimal way where i can retrieve both information. Please note that I also have the same grouping issue as before. >>>>> >>>>> Do you have the same behavior, or I am missing something in the creation of these functions. >>>>> >>>>> A final question, how do u drop a R function? i am trying to execute the following: >>>>> drop all function "tone"."ttest2samplesStat" >>>>> >>>>> >>>>> Thank you. >>>>> >>>>> >>>>> >>>>> On Thu, Jun 11, 2015 at 11:28 PM, Dimitar Nedev <dimitar.nedev(a)monetdbsolutions.com> wrote: >>>>> Hi Imad, >>>>> >>>>> Hannes asked me to look at your issue with the MonetDB-R VM. >>>>> >>>>> I loaded the data and ran the queries you mentioned, but I did not encounter any issues. This holds for both the short and the union queries. I am also not familiar with the issue you report, nor its cause. At this time I think it is local to your instance. If you can export your db, or re-load the data, I would recommend you to create a new instance with a fresh image and try again. If you import the image as a new VM instance, you can keep your old one around as well. >>>>> >>>>> Please note that during the test: >>>>> - I ran as the admin (monetdb) user, as the one created default on the VM image cannot create new schemas. >>>>> - I have to clean the data a bit up to load it, removing dozen lines or so. >>>>> - Renamed ttest1 to ttest2 to match the queries. >>>>> >>>>> Best regards, >>>>> Dimitar >>>>> >>>>>>>>>>> Begin forwarded message: >>>>>>>>>>> >>>>>>>>>>> From: imad hajj chahine <imad.hajj.chahine(a)gmail.com> >>>>>>>>>>> Subject: Re: Monetdb-R integration >>>>>>>>>>> Date: 10 Jun 2015 10:11:48 CEST >>>>>>>>>>> To: Hannes Mühleisen <Hannes.Muehleisen(a)cwi.nl> >>>>>>>>>>> >>>>>>>>>>> Hi, >>>>>>>>>>> >>>>>>>>>>> I am using the virtual box provided from https://www.monetdbsolutions.com/solutions/cloud/vm: >>>>>>>>>>> OS:linux version 3.10.0-123.9.3.el7.x86_64 (builder(a)kbuilder.dev.centos.org) >>>>>>>>>>> Monetdb: V1.7: (Oct2014-SP1) >>>>>>>>>>> R: 3.1.2 (2014-10-31) >>>>>>>>>>> >>>>>>>>>>> >>>>>>>>>>> >>>>>>>>>>> create table "tone"."Marketing_Loyalty_4700298d-9862-40b3-9028-b0f15dab9dea" ("Consumer Number" INTEGER,"1-Disloyalty" varchar(250),"2-Age" varchar(250),"3-Amount transferred" INTEGER,"4-Gender" varchar(250),"5-Citiz enship" varchar(250),"6- Profession" varchar(250),"7-City" varchar(250),"8-1 NC Billboard" varchar(250),"8-2 NC Friends" varchar(250),"8-3 NC Magazine" varchar(250),"8-4 NC Others" varchar(250),"9-Score" decimal(18,3),"10- Type of operation" varchar(250),"11-1 Welcoming" INTEGER,"11-2 Waiting time" INTEGER,"11-3 Time to finish request" INTEGER,"11-4 Fees" INTEGER,"11-5 Courtesy of employees" INTEGER,"11-6 Branch layout" INTEGER,"11-7 Attention to special requests" INTEGER,"12- Total time for service" decimal(18,3),"13- Distance from POS" decimal(18,3),"14- Weight" INTEGER) >>>>>>>>>>> >>>>>>>>>>> COPY offset 2 INTO "tone"."Marketing_Loyalty_4700298d-9862-40b3-9028-b0f15dab9dea" FROM '/home/App_Data/TestingDataSimple_2003_Marketing_Loyalty_4700298d-9862-40b3-9028-b0f15dab9dea.csv' USING DELIMITERS ',','\n','"'; >>>>>>>>>>> >>>>>>>>>>> Thanks >>>>>>>>>>> >>>>>>>>>>> On Wed, Jun 10, 2015 at 10:50 AM, Hannes Mühleisen <Hannes.Muehleisen(a)cwi.nl> wrote: >>>>>>>>>>> Hello Imad, >>>>>>>>>>> >>>>>>>>>>> which version of MonetDB and R are you using? Which operating system? >>>>>>>>>>> >>>>>>>>>>> Can you please also include the commands you used to load your CSV into MonetDB/ create the table? >>>>>>>>>>> >>>>>>>>>>> Thanks, >>>>>>>>>>> >>>>>>>>>>> Hannes >>>>>>>>>>> >>>>>>>>>>>> On 10 Jun 2015, at 00:25, imad hajj chahine <imad.hajj.chahine(a)gmail.com> wrote: >>>>>>>>>>>> >>>>>>>>>>>> Hi Hannes, >>>>>>>>>>>> >>>>>>>>>>>> I am testing Monetdb-R integration to do some statistical test, i followed the tutorial you posted on youtube and i have created the following function: >>>>>>>>>>>> >>>>>>>>>>>> CREATE AGGREGATE "tone"."ttest1"(arg1 double, arg2 double) RETURNS double LANGUAGE R { t.test(arg1, arg2)$p.value }; >>>>>>>>>>>> >>>>>>>>>>>> this function will basically do a one sample t-test. >>>>>>>>>>>> >>>>>>>>>>>> I have loaded the sample attached file into a demo database and trying to run the following query: >>>>>>>>>>>> >>>>>>>>>>>> select "2-Age", "tone"."ttest2"("9-Score",6.08963) from "tone"."Marketing_Loyalty_4700298d-9862-40b3-9028-b0f15dab9dea" where "2-Age" is not null group by "2-Age" >>>>>>>>>>>> >>>>>>>>>>>> The following error happened >>>>>>>>>>>> Error: protocol violation: unexpected line in data block: . >>>>>>>>>>>> SQLState: M0M10 >>>>>>>>>>>> ErrorCode: 0 >>>>>>>>>>>> >>>>>>>>>>>> alternatively i managed to have the intended result by running this query: >>>>>>>>>>>> >>>>>>>>>>>> select '[10-20]' as "2-Age","tone"."ttest2"("9-Score",6.08963) from "tone"."Marketing_Loyalty_4700298d-9862-40b3-9028-b0f15dab9dea" where "2-Age" = '[10-20]' >>>>>>>>>>>> union all >>>>>>>>>>>> select '[21-30]' as "2-Age","tone"."ttest2"("9-Score",6.08963) from "tone"."Marketing_Loyalty_4700298d-9862-40b3-9028-b0f15dab9dea" where "2-Age" = '[21-30]' >>>>>>>>>>>> union all >>>>>>>>>>>> select '[> 30]' as "2-Age","tone"."ttest2"("9-Score",6.08963) from "tone"."Marketing_Loyalty_4700298d-9862-40b3-9028-b0f15dab9dea" where "2-Age" = '> 30' >>>>>>>>>>>> >>>>>>>>>>>> >>>>>>>>>>>> any help or guidance is appreciated. >>>>>>>>>>>> Thank you. >>>>>>>>>>>> >>>>>>>>>>>> <TestingDataSimple_2003_Marketing_Loyalty_4700298d-9862-40b3-9028-b0f15dab9dea.csv> >>>>>>>>>>> >>>>>>>>>>> >>>>>>>>>> >>>>>>>>> >>>>>>>> >>>>>>> >>>>>> >>>>> >>>> >>>> >>> >> >> >

1 0

Enabling Spatial support?
by Thorsten Wesskallnies 17 Jun '15

17 Jun '15

Hi, I'm having some trouble with the instruction posted on the website here: https://www.monetdb.org/Documentation/Extensions/GIS I am running an OEL Server on version 6.6 and installed MonetDB using yum: Installed Packages Name : MonetDB Arch : x86_64 Version : 11.19.11 Release : 20150423.el6 ... Name : MonetDB-geom-MonetDB5 Arch : x86_64 Version : 11.19.11 Release : 20150423.el6 I also configured and installed the current version of the GEOS Library # geos-config --version >3.4.2 According to the above mentioned website I should now rebuild my DB with the '--enable-geom configure argument'. Unfortunately I'm unable to find the correct way to do it since yum installed a binary on my system and not the sourcecode. The 'MonetDB-geom' package doesn't seem to to the trick since the result of the sample Code on the Website returns an empty result. Hope somebody can point me in the right direction. MfG Thorsten Wesskallnies Avitech GmbH Principal Office: Bahnhofplatz 1 | 88045 Friedrichshafen | Germany Court Registration: Amtsgericht Ulm | HRB 728293 Geschäftsführer/Managing Director: Antonio Maria Gonzalez Gorostiza VAT No.: DE223719716 http://avitech.aero

2 1

trace disabled?
by Roberto Cornacchia 16 Jun '15

16 Jun '15

Hi there, Just to be sure: is it expected that trace is disabled / changed in Jan2015? I can see the trace by inspecting sys.tracelog() after the query, but no longer in one go as before. Roberto

1 1

Performing a quantile in R from a MonetDB.R db with 300million rows (UNCLASSIFIED)
by George, Glover E ERD-MS 15 Jun '15

15 Jun '15

Classification: UNCLASSIFIED Caveats: NONE Hi all, I'm currently trying to compare the performance of R's quantile function to that of MonetDB's. I have a table loaded with the TPC-H benchmark's data (scale factor of 50). I'm trying to return ~300 million rows of data (select l_extendedprice from lineitem), and although I have 256G of RAM, I'm getting the following: Error in paste(0)("",resp, collapse="") : Result would exceed 2^31-1 bytes The R statement is : x = dbGetQuery(conn, "select l_extendedprice from lineitem") Forgive me if this is a simplistic questions, as I am fairly new to R and don't know which avenue to take next. I took a look at bigmemory, but don't know quite how to return a result from monetdb and have it use a big.matrix. Note: the l_extendedprice is a floating (double) value. Any suggestions? Cheers. Glover George Classification: UNCLASSIFIED Caveats: NONE

2 2

1-to-N (bat) UDF
by Roberto Cornacchia 15 Jun '15

15 Jun '15

Hi there, I need a string tokenizer in MonetDB. The problem I have is not with the function itself, but with the fact that this is a 1 to N rows function. Implementing this for a single string value is easy enough, using a table function that takes a string a returns a table: create function tokenize(s string) returns table (token string) external name tokenize; select * from tokenize("one two three"); That's fine. The issue I'm having is with extending this to a column of strings. Ideally, given a string column one two three four five six seven eight I'd like to get an output along these lines (simplistic representation here): one two three | one one two three | two one two three | three four five six | four four five six | five four five six | six seven eight | seven seven eight | eight I can sure code the c function and the mal wrapper to implement this, but I can't see how to map it to SQL, given that table functions don't accept identifiers as parameters. Any idea? Any possible workaround? Thanks, Roberto

4 11

Geommodul and Linestring Query
by Dennis Wiest 15 Jun '15

15 Jun '15

Hello, I have a question for Geomodul. Is it possible to execute the following query with a LineString. This is my query with a point and the sample data set from the documentation. SELECT forests.name,buildings.name FROM forests,buildings WHERE "Intersect"(buildings.outline, Point(53.9,29.9)) = true; Now I want to replace the Point with a LineString like this in mysql. SELECT forests.name,buildings.name FROM forests,buildings WHERE "Intersect"(buildings.outline, LineString(Point(53.9,29.9), Point(53.0, 29.0))) = true; TestDatabase CREATE TABLE forests(id INT,name TEXT,shape MULTIPOLYGON); CREATE TABLE buildings(id INT,name TEXT,location POINT,outline POLYGON); INSERT INTO forests VALUES(109, 'Green Forest', 'MULTIPOLYGON( ((28 26,28 0,84 0,84 42,28 26), (52 18,66 23,73 9,48 6,52 18)), ((59 18,67 18,67 13,59 13,59 18)))'); INSERT INTO buildings VALUES(113, '123 Main Street', 'POINT( 52 30 )', 'POLYGON( ( 50 31, 54 31, 54 29, 50 29, 50 31) )'); INSERT INTO buildings VALUES(114, '215 Main Street', 'POINT( 64 33 )', 'POLYGON( ( 66 34, 62 34, 62 32, 66 32, 66 34) )'); SELECT forests.name,buildings.name FROM forests,buildings WHERE forests.name = 'Green Forest' and Overlaps(forests.shape, buildings.outline) = true; many thanks Dennis ________________________________ Avitech GmbH Principal Office: Bahnhofplatz 1 | 88045 Friedrichshafen | Germany Court Registration: Amtsgericht Ulm | HRB 728293 Gesch?ftsf?hrer/Managing Director: Antonio Maria Gonzalez Gorostiza VAT No.: DE223719716 http://avitech.aero

2 2

how to take output from DELETE and feed it to INSERT?
by integrity 12 Jun '15

12 Jun '15

Dear experts, Does anyone know how to take output from DELETE and feed it to INSERT INTO another table? Since now my pipeline ran into a certain point that 37 seconds of the total time 48 seconds spent on the following queries: INSERT INTO old_to_old SELECT uniqueid, targetid, distance_arcsec FROM associatedsource WHERE uniqueid IN (SELECT old_uniqueid FROM one_to_many) ORDER BY uniqueid, targetid; 42650 affected rows (20.0s) DELETE FROM associatedsource WHERE uniqueid IN (SELECT old_uniqueid FROM one_to_many); 42650 affected rows (16.6s) where associatedsource table is a large table with 1219234829 rows which is the size till the end of second day, while one_to_many table is quite small only 30 rows. associatedsource will accumulate processed data day by day, till the end of first day it has 603553616 rows. As associatedsource table grows, this two queries has severely degraded my pipeline, on first day the time for them were 9.1s and 7.8s. We can see monetdb server engine (randomly?) access the same data twice, for a large table and limited time allocated to a run of pipeline this is very expensive. if we can combine the queries into a single then the logic about which rows to move will be neater and improve performance twice. Thank you and best wishes, Meng

2 2

Dependency Types
by Robert Koch 11 Jun '15

11 Jun '15

Hi all, could someone please add an entry in the online documentation about the meaning of column "depend_type" in sys.dependencies? Recommended place is: https://www.monetdb.org/Documentation/SQLcatalog/TriggersDependencies Thank you. Robert

2 1

MonetDB crashed by some Query
by Dennis Wiest 10 Jun '15

10 Jun '15

Hello, I'm new here. I have the following problem. I have a database that looks like this (see end of email). Now I want to issue the following query. Select b. * FROM open_auctions o, b bidder WHERE (select b3.INCREASE from bidder b3 where b3.id = (select min (b3a.id) from bidder b3a where b3a.open_auction_id = o.open_auction_id)) * 2 <= (Select b2.INCREASE from bidder b2 where b2.id = (SELECT MAX (b2a.id) from bidder b2a where b2a.open_auction_id = o.open_auction_id)) AND o.open_auction_id = b.open_auction_id; The database crashes. If I divide the query individually and settled it works flawlessly. Other complex query also work. Only in this query crashes the database. Can anyone help me further? I installed MonetDB 11:19:11 64 bits on Redhat. 6 Thanks and Regards Dennis CREATE TABLE closed_auctions ( id int NOT NULL AUTO_INCREMENT, seller varchar(255) NOT NULL, buyer varchar(255) NOT NULL, itemref varchar(255) NOT NULL, price double NOT NULL, date varchar(255) NOT NULL, quantitiy int NOT NULL, type varchar(255) NOT NULL, PRIMARY KEY (id) ); INSERT INTO `open_auctions` (`id`, `open_auction_id`, `initial`, `reserve`, `aktuell`, `privacy`, `itemref`, `seller`, `quantity`, `type`, `start`, `ende`) VALUES (1, 'open_auction0', 210.62, 1540.75, 263.12, 'No', 'item0', 'person11', 1, 'Regular', '02/27/1998', '03/09/1999'), (2, 'open_auction1', 69.64, 398.65, 168.64, '', 'item2', 'person10', 1, 'Featured', '06/14/1998', '02/27/1999'), (3, 'open_auction2', 13.9, 0, 16.9, 'No', 'item3', 'person11', 1, 'Featured', '07/16/2000', '10/22/2000'), (4, 'open_auction3', 17.12, 0, 179.12, '', 'item5', 'person13', 1, 'Featured', '02/01/2001', '07/22/1999'), (5, 'open_auction4', 38.21, 116.91, 219.71, 'No', 'item7', 'person11', 4, 'Regular, Dutch', '08/05/2000', '01/09/1999'), (6, 'open_auction5', 75.95, 174.84, 116.45, 'No', 'item9', 'person7', 1, 'Featured', '01/27/2001', '10/08/1999'), (7, 'open_auction6', 67.27, 0, 77.77, 'Yes', 'item10', 'person14', 1, 'Featured', '12/15/2001', '07/13/1999'), (8, 'open_auction7', 35.53, 133.36, 73.03, 'Yes', 'item12', 'person16', 1, 'Regular', '04/06/2001', '04/11/2000'), (9, 'open_auction8', 92.54, 0, 101.54, '', 'item13', 'person12', 1, 'Regular', '10/20/1998', '02/04/2001'), (10, 'open_auction9', 9.88, 0, 137.38, '', 'item14', 'person10', 1, 'Featured', '06/16/1998', '12/05/2000'), (11, 'open_auction10', 86.28, 0, 239.28, 'Yes', 'item16', 'person8', 1, 'Featured', '06/26/2000', '02/04/1998'), (12, 'open_auction11', 4.12, 24.53, 38.62, '', 'item17', 'person12', 1, 'Regular', '11/03/1999', '03/02/2000'); CREATE TABLE bidder ( id int NOT NULL AUTO_INCREMENT, open_auction_id varchar(255) NOT NULL, date varchar(255) NOT NULL, time varchar(255) NOT NULL, personref varchar(255) NOT NULL, increase double NOT NULL, PRIMARY KEY (id) ); -- -- Daten für Tabelle `bidder` -- INSERT INTO `bidder` (`id`, `open_auction_id`, `date`, `time`, `personref`, `increase`) VALUES (1, 'open_auction0', '06/13/2001', '13:16:15', 'person0', 18), (2, 'open_auction0', '09/18/2000', '11:29:44', 'person23', 12), (3, 'open_auction0', '01/07/1998', '10:23:59', 'person14', 18), (4, 'open_auction0', '07/10/2001', '14:00:39', 'person16', 4.5), (5, 'open_auction1', '11/12/1998', '11:23:38', 'person20', 4.5), (6, 'open_auction1', '10/02/2000', '22:48:00', 'person4', 15), (7, 'open_auction1', '12/04/1998', '22:29:38', 'person23', 1.5), (8, 'open_auction1', '06/22/1999', '12:43:47', 'person19', 15), (9, 'open_auction1', '12/02/2001', '13:38:51', 'person15', 45), (10, 'open_auction1', '11/12/2001', '04:50:27', 'person9', 6), (11, 'open_auction1', '05/21/2001', '08:02:16', 'person5', 12), (12, 'open_auction2', '12/04/2000', '14:03:16', 'person5', 3), (13, 'open_auction3', '09/03/2000', '22:45:30', 'person8', 10.5), (14, 'open_auction3', '08/18/1998', '17:13:40', 'person22', 25.5), (15, 'open_auction3', '06/05/1998', '11:57:56', 'person19', 16.5), (16, 'open_auction3', '09/20/1999', '00:39:18', 'person5', 46.5), (17, 'open_auction3', '10/03/2001', '01:08:59', 'person23', 58.5), (18, 'open_auction3', '02/17/2001', '07:13:32', 'person20', 4.5), (19, 'open_auction4', '10/19/1998', '09:00:31', 'person7', 33), (20, 'open_auction4', '03/11/2001', '01:59:02', 'person8', 9), (21, 'open_auction4', '04/16/2001', '23:37:09', 'person8', 6), (22, 'open_auction4', '03/06/1999', '12:19:57', 'person5', 4.5), (23, 'open_auction4', '02/01/2000', '09:37:51', 'person21', 18), (24, 'open_auction4', '01/09/1999', '19:31:44', 'person1', 31.5), (25, 'open_auction4', '11/11/2001', '05:24:08', 'person16', 27), (26, 'open_auction4', '11/19/1998', '16:16:17', 'person10', 7.5), (27, 'open_auction4', '04/05/1999', '00:37:15', 'person17', 7.5), (28, 'open_auction4', '09/14/1999', '12:00:43', 'person12', 37.5), (29, 'open_auction5', '07/07/2000', '08:53:00', 'person15', 6), (30, 'open_auction5', '08/06/2001', '10:16:15', 'person13', 4.5), (31, 'open_auction5', '08/23/1999', '08:26:06', 'person17', 30), (32, 'open_auction6', '01/23/2000', '17:14:42', 'person1', 10.5), (33, 'open_auction7', '10/14/1999', '14:39:18', 'person16', 27), (34, 'open_auction7', '05/19/1999', '23:51:16', 'person14', 9), (35, 'open_auction7', '03/27/1999', '19:14:39', 'person23', 1.5), (36, 'open_auction8', '04/26/2001', '00:41:04', 'person12', 9), (37, 'open_auction9', '04/03/1999', '19:09:46', 'person22', 18), (38, 'open_auction9', '01/21/1999', '08:14:44', 'person19', 19.5), (39, 'open_auction9', '11/24/1999', '02:12:12', 'person11', 12), (40, 'open_auction9', '01/07/2001', '05:33:55', 'person7', 9), (41, 'open_auction9', '07/28/2000', '00:57:52', 'person16', 16.5), (42, 'open_auction9', '01/28/2000', '20:24:02', 'person23', 1.5), (43, 'open_auction9', '05/13/2001', '02:45:46', 'person13', 1.5), (44, 'open_auction9', '12/06/2000', '15:18:07', 'person21', 24), (45, 'open_auction9', '08/16/1998', '09:27:27', 'person14', 3), (46, 'open_auction9', '06/18/2001', '19:57:53', 'person7', 3), (47, 'open_auction9', '12/13/2001', '09:01:23', 'person2', 12), (48, 'open_auction9', '03/18/2001', '08:59:02', 'person13', 7.5), (49, 'open_auction10', '08/14/1998', '16:33:46', 'person23', 33), (50, 'open_auction10', '03/24/1998', '11:45:48', 'person3', 1.5), (51, 'open_auction10', '10/08/1998', '06:20:35', 'person1', 45), (52, 'open_auction10', '01/12/1998', '21:43:02', 'person3', 15), (53, 'open_auction10', '03/02/2001', '20:20:33', 'person0', 9), (54, 'open_auction10', '03/01/2000', '10:08:07', 'person23', 10.5), (55, 'open_auction10', '07/19/2001', '00:50:29', 'person6', 9), (56, 'open_auction10', '10/04/2001', '00:58:25', 'person4', 3), (57, 'open_auction10', '11/27/1998', '00:15:23', 'person21', 27), (58, 'open_auction11', '05/16/1998', '15:08:01', 'person1', 15), (59, 'open_auction11', '05/08/2000', '06:44:20', 'person3', 4.5), (60, 'open_auction11', '10/22/2001', '15:34:49', 'person4', 15); ________________________________ Avitech GmbH Principal Office: Bahnhofplatz 1 | 88045 Friedrichshafen | Germany Court Registration: Amtsgericht Ulm | HRB 728293 Geschäftsführer/Managing Director: Antonio Maria Gonzalez Gorostiza VAT No.: DE223719716 http://avitech.aero

3 3