- users-list - monetdb.org

Excessive slowness in a query
by Alfio Bettini 14 Dec '17

14 Dec '17

Hi all, we're testing MonetDB performances in order to understand if it could be possible to use it as data mart in our BI software. We're considering a set of queries executed in our test environment (CentOS 7, 32 GB RAM, 4 cpu). All the executed queries have quite good performances (between 10 and 45 seconds) except the following one that is taking several minutes in order to be executed: SELECT id, count(distinct value) FROM table GROUP BY id; The table contains 100 millions of data. It has about 130 columns (varchar, int, bigint, smallint, tinyint, timestamp, datetime). We have no BLOB/CLOB/Text types. The fields used in the query have these data types: id: int value: bigint Can you suggest us any optimizations in order to have this query run faster? Do you know some possible reasons why it's so slow? Kind Regards, Alfio Bettini The information contained in this message, together with any attachments, may be privileged and confidential and is intended for the use of the addressee(s) only. Any review, copying, use or distribution of this email by others (including any of its attachments) is strictly prohibited. If you are not the intended recipient, please destroy this email and its attachments and contact the sender immediately. This email was sent by EidosMedia SpA<http://www.eidosmedia.com>

1 0

Re: algebra.join heapextend crash
by Stefan Manegold 13 Dec '17

13 Dec '17

Allocating "only" about 2.8 GB does not ring alarms that the plan night be degenerated - assuming your data is not tiny. Could you share:OS, full query, data sizes, free disk space at time of failure, mserver5 process size at time of failure? Thanks,Stefan -------- Original message --------From: Anthony Damico <ajdamico(a)gmail.com> Date: 11/22/17 12:13 (GMT+01:00) To: Communication channel for MonetDB users <users-list(a)monetdb.org> Subject: algebra.join heapextend crash hi, i'm using monetdblite-r 0.5.0 on a confidential dataset. my syntax worked on monetdblite 0.3.1 on the same machine ftruncate: Invalid argument Error in .local(conn, statement, ...) : Unable to execute statement 'CREATE TABLE ccaed123_b_svcmon_xwalk AS ( SELECT a.enrolid , a.yr , a.dataty...'. Server says 'MALException:algebra.join:GDK reported error. !ERROR: GDKextendf: c !ERROR: HEAPextend: failed to extend to 2890530816 for 02\57\25766.tail: GDKmre map() failed '. it's a local drive, the hard disk isn't close to full and the machine has 700gb of ram, but the table it's trying to create might be bigger than that. dunno if related to https://www.monetdb.org/bugzilla/show_bug.cgi?id=3791 thanks

2 4

MonetDB Blob Inserts
by Brian Hood 07 Dec '17

07 Dec '17

Hi Guys, I don't seem to be able to INSERT into a blob field is there any information how to achieve this. Regards, Brian Hood

3 6

What is better JSON or TEXT field in terms of performance ???
by Ariel Abadi 07 Dec '17

07 Dec '17

Hi All! Does anybody has worked with json fields? We are planning to use json fields with very large text on it (the hierarchy of our json would be plain (only one level), we are doing some tests over 7MM records, it took like 1m30s to retrieve the information. In this case to get the information we used the function json.filter(name_of_field, '$..xxxx') We have also tested using a TEXT field (and using some string functions) we parsed the information inside the field and we found that sometimes it was quicker it took from 1m30s to 35s. I dont know exactly why the difference on that delta. We need to implement the processes, and need to decide how to move forward.... Does anybody can help me on the decision ??? We know and is a fact, that Monet can beat any DB in terms of performance, and that is why we decided to use it, but now Im worried about this type of queries (against json/text fields) and how long this queries would take if we increase the amount of records.... What would you suggest ?? Thanks in advance! Rgds Ariel

3 3

mserver5 crash on startup in Ubuntu Xenial
by Manuel Pagliai 06 Dec '17

06 Dec '17

Hello, I get this error when I try to start monetdb on Ubuntu Xenial 16.04.03 mserver5 visokio # MonetDB 5 server v11.27.9 "Jul2017-SP2" # Serving database 'demo', using 12 threads # Compiled for x86_64-pc-linux-gnu/64bit with 128bit integers # Found 7.702 GiB available main-memory. # Copyright (c) 1993-July 2008 CWI. # Copyright (c) August 2008-2017 MonetDB B.V., all rights reserved # Visit https://www.monetdb.org/ for further information !SyntaxException:parseError:include sqlcatalog; !SyntaxException:parseError:^LoaderException:loadLibrary:Loading error failed to open library sql (from within file '/usr/lib/monetdb5/lib_sql.so'): /usr/lib/monetdb5/lib_sql.so: undefined symbol: mnstr_writeFlt !SyntaxException:parseError:include sqlcatalog; !SyntaxException:parseError: ^';' expected Segmentation fault (core dumped) any ideas ? Thanks, Best regards, *Manuel Pagliai* Visokio Ltd www.visokio.com +44 20 7351 4646

1 0

Issue with SUM(DISTINCT) in 11.27.9
by Sharma, Sreejith 05 Dec '17

05 Dec '17

All, We have noticed when SQL pattern has SUM(DISTINCT... ) there is a degradation of SQL timings when compared to 11.27.5 build. Anyone is aware of this? Regards, Sreejith

1 0

Re: MAL query "sql.resultSet"
by dennis.fassl 01 Dec '17

01 Dec '17

Hi all, can you help me with the below query? Is there an other way to return query results using the jdbc interface (without using sql.resultSet)? Thanks in advance! Best regards,Dennis -------- Ursprüngliche Nachricht --------Von: Dennis Fassl <dennis.fassl(a)uni-konstanz.de> Datum: 25.11.17 16:55 (GMT+01:00) An: users-list(a)monetdb.org Betreff: MAL query "sql.resultSet" Hi all, I am trying to execute the MAL query below. But the command "sql.resultSet" is terminating mclient. When using the JDBC interface I get "SQLException: Connection to server lost! (mserver still alive?)". Do you have an idea why this is happening? Thanks and regards, Dennis sql.init(); table_names := bat.new(nil:str); bat.append(table_names, "t0"); bat.append(table_names, "t1"); table_attrs := bat.new(nil:str); bat.append(table_attrs, "int_col"); bat.append(table_attrs, "str_col"); table_types := bat.new(nil:str); bat.append(table_types, "int"); bat.append(table_types, "str"); table_lens := bat.new(nil:int); bat.append(table_lens, 1); bat.append(table_lens, 1); table_scales := bat.new(nil:int); bat.append(table_scales, 0); bat.append(table_scales, 0); ints := bat.new(nil:int); bat.append(ints, 42); bat.append(ints, 23); strs := bat.new(nil:str); bat.append(strs, "foo"); bat.append(strs, "bar"); sql.resultSet(table_names, table_attrs, table_types, table_lens, table_scales, ints, strs); _______________________________________________ users-list mailing list users-list(a)monetdb.org https://www.monetdb.org/mailman/listinfo/users-list

4 6

MAL query "sql.resultSet"
by Dennis Fassl 25 Nov '17

25 Nov '17

Hi all, I am trying to execute the MAL query below. But the command "sql.resultSet" is terminating mclient. When using the JDBC interface I get "SQLException: Connection to server lost! (mserver still alive?)". Do you have an idea why this is happening? Thanks and regards, Dennis sql.init(); table_names := bat.new(nil:str); bat.append(table_names, "t0"); bat.append(table_names, "t1"); table_attrs := bat.new(nil:str); bat.append(table_attrs, "int_col"); bat.append(table_attrs, "str_col"); table_types := bat.new(nil:str); bat.append(table_types, "int"); bat.append(table_types, "str"); table_lens := bat.new(nil:int); bat.append(table_lens, 1); bat.append(table_lens, 1); table_scales := bat.new(nil:int); bat.append(table_scales, 0); bat.append(table_scales, 0); ints := bat.new(nil:int); bat.append(ints, 42); bat.append(ints, 23); strs := bat.new(nil:str); bat.append(strs, "foo"); bat.append(strs, "bar"); sql.resultSet(table_names, table_attrs, table_types, table_lens, table_scales, ints, strs);

1 0

Number of instance
by Sharma, Sreejith 23 Nov '17

23 Nov '17

All, May be a dump question, but curious to know. What is the maximum number of MonetDB instance anyone has setup in a single server? And what level of concurrency has been tested? Regards, Sreejith

1 0

MonetDB - Data Compression, is it possible?
by Sharma, Sreejith 23 Nov '17

23 Nov '17

All, Is there any data compression methods available in MonetDB? What I understand from I read is, by default MonetDB doesn't have a compression methods to avoid the overhead of uncompression while loading the files into memory mapped files. Is that true? If there is a compression mechanism (alike QUERY HIGH Compression in Oracle) what is it in here? Regards, Sreejith

2 2

algebra.join heapextend crash
by Anthony Damico 22 Nov '17

22 Nov '17

hi, i'm using monetdblite-r 0.5.0 on a confidential dataset. my syntax worked on monetdblite 0.3.1 on the same machine ftruncate: Invalid argument Error in .local(conn, statement, ...) : Unable to execute statement 'CREATE TABLE ccaed123_b_svcmon_xwalk AS ( SELECT a.enrolid , a.yr , a.dataty...'. Server says 'MALException:algebra.join:GDK reported error. !ERROR: GDKextendf: c !ERROR: HEAPextend: failed to extend to 2890530816 for 02\57\25766.tail: GDKmre map() failed '. it's a local drive, the hard disk isn't close to full and the machine has 700gb of ram, but the table it's trying to create might be bigger than that. dunno if related to https://www.monetdb.org/bugzilla/show_bug.cgi?id=3791 thanks

1 0

Patch for backreferences in PCRE replace
by Roberto Cornacchia 16 Nov '17

16 Nov '17

Hi, Following up on this thread ( https://www.monetdb.org/pipermail/users-list/2016-April/009107.html) from last year, the issue was that the pcre-based regex replacement did not implement back references in the replacement string. In need for this feature, I so far used the pcre implementation via R function. It works well, but using R just for this is a bit odd, especially because R-core has a pretty dumb dependency on the whole TeX distribution, and building MonetDB docker images that need to include R and TeX just because of regex isn't a neat option. I have also tried to use both re and regex packages via python, but this is very slow compared to pcre. So I finally decided to put a few hours on the MonetDB implementation and added support for back references. They can be indicated with both \1 and $1 syntax. Back-reference 0 is the whole match. Out-of-bound back-references are empty strings. While at it: - pcre.replace replaces all matches, so I made a variant pcre.replacefirst to replace only the first match. - the bat implementation was ignored because of a missing "module batpcre" in pcre.mal, it always resorted to manifold on the string version. Fixed that. Also, I'm not sure why there don't seem to exist SQL functions declared for this. I use the following: CREATE FUNCTION pcre_replace(s string, pattern string, repl string, flags string) RETURNS string EXTERNAL NAME pcre."replace"; CREATE FUNCTION pcre_replacefirst(s string, pattern string, repl string, flags string) RETURNS string EXTERNAL NAME pcre."replace_first"; Perhaps they could come by default? Please feel free to use and modify the patch (Jul2017) in attachment if you think it's useful. I've tested it quite a bit, no issues found. Here are some examples: sql>select name, pcre_replace(name, '([a-z]+)_([a-z]+)', '[\\1]-[\\2]', '') as replaced from sys.functions limit 2; +-------------+---------------------------------------------+ | name | replaced | +=============+=============================================+ | mbr_overlap | [mbr]-[overlap] | | mbr_overlap | [mbr]-[overlap] | +-------------+---------------------------------------------+ 2 tuples (4.650ms) sql>select name, pcre_replacefirst(name, '([a-z])', '[\\1]', '') as replaced from sys.functions limit 2; +-------------+---------------------------------------+ | name | replaced | +=============+=======================================+ | mbr_overlap | [m]br_overlap | | mbr_overlap | [m]br_overlap | +-------------+---------------------------------------+ 2 tuples (4.728ms) sql>select name, pcre_replace(name, '([a-z])', '[\\1]', '') as replaced from sys.functions limit 2; +-------------+-----------------------------------------------------------------------------------------------------+ | name | replaced | +=============+=====================================================================================================+ | mbr_overlap | [m][b][r]_[o][v][e][r][l][a][p] | | mbr_overlap | [m][b][r]_[o][v][e][r][l][a][p] | +-------------+-----------------------------------------------------------------------------------------------------+ 2 tuples (10.360ms)

1 1

UDF function with >3 parameters cannot benefit from using PYTHON_MAP
by Hanfeng Chen 15 Nov '17

15 Nov '17

Hi, I created a table with 9 columns and passed 6 columns from the table to a UDF. This UDF is fully vectorizable. However, we found only single thread was used during the execution. Then, we came to think about what the problem behind and we created the two simple functions python_min_map_3 and python_min_map_4 which takes 3 and 4 parameters separately. The MonetDB Linux version we used was July 2017. If PYTHON_MAP is enabled, it would return the results of each segment processed by parallel code. The number of results depends on how many threads are used. Otherwise, a single value should be returned (e.g. because of numpy.min). The result is - python_min_map_3 returns a couple of numbers - python_min_map_4 returns a single number Even when we increased the number of the arguments, a single number was always returned. Is it a restriction in using PYTHON_MAP when the number of arguments should be no more than 3? Here is our example code below. CREATE FUNCTION python_min_map_3(x0 FLOAT, x1 FLOAT, x2 FLOAT) RETURNS FLOAT LANGUAGE PYTHON_MAP { return numpy.min(x0) }; select python_min_map_3(x0, x1, x2) from table_0; CREATE FUNCTION python_min_map_4(x0 FLOAT, x1 FLOAT, x2 FLOAT, x3 FLOAT) RETURNS FLOAT LANGUAGE PYTHON_MAP { return numpy.min(x0) }; select python_min_map_4(x0, x1, x2,x3) from table_0; NumPy in MonetDB reference: https://www.monetdb.org/blog/embedded-pythonnumpy-monetdb Best regards, Hanfeng Chen

1 0

read BLOB column on tail heap and loop over rows
by Anton Kravchenko 13 Nov '17

13 Nov '17

I wonder how one would read BLOB column and loop over its rows? BAT *b = table_funcs.full_column(m->session->tr, blob_col_name); //blob *readblobrows = (blob*) b->T.heap.base; //unsigned char *readblobrows = (unsigned char*) b->T.heap.base; p.s. For INT column it would be go like this: int * readintrows = (int *) b->T.heap.base; for (irow=0; irow < nrow; irow++) { intvalue = *readintrows++; } Thanks, Anton

1 1

Querying JSON arrays
by Brian Ploetz 09 Nov '17

09 Nov '17

Hi, Suppose I have a table with a "tags" column that is of the JSON datatype ( https://www.monetdb.org/Documentation/Manuals/SQLreference/Types/JSON) The column will contain a JSON array of Strings. For example: ["foo","bar","baz"] Is it possible to query for rows that contain a specific value in the tags array? For example, if I wanted to find all of the rows where tags contains "foo", my query would look something like: select count(*) from my_table where tags ???? 'foo'; But it's not clear to me what ???? should be (or if it's even possible) based on the JSON documentation above. Thanks in advance. BP

2 3

large virtual memory spike on BLOB column select
by Anton Kravchenko 09 Nov '17

09 Nov '17

I am using MonetDB v11.27.9 on cent7. 1) 'select v_blob from blob_table' produces a virtual memory spike ~800Gb [using a 'limit' reproduces virtual memory spike too] >From 'select * from storage()' it looks a blob column size is ~25Gb. 2) And 'create table new_blob_table as (select v_blob from blob_table) with data;' produces only ~50Gb of virtual memory. 3) Also for a table with non-blob column both 'select * from nonblob_table' and 'create new_nonblobtable as (select * from nonblob_table) with data;' produce the ~same virtual memory consumption. p.s. Here are steps to reproduce this issue # to generate date by using Python hex_blob='47'*2000 hex_blob_chunk=(hex_blob+'\n')*1000 f=open('blob_data.txt','w') for irow in range(10000): f.write(hex_blob_chunk) f.close() # to load data into Monet blob_table create table blob_table(v_blob blob); COPY 10000000 RECORDS INTO blob_table FROM '/home/blob_data.txt' using delimiters ',','\n' NULL AS '' LOCKED; # to produce virtual memory spike of ~800GB select * from blob_table limit 50000; # to produce virtual memory of ~50Gb create table new_blob_table as (select * from blob_table) with data; Thanks, Anton

2 1

Problems creating json files with more than 10^3 table entries
by Giacomo Bergami 09 Nov '17

09 Nov '17

I am using MonetDB-11.27.9 on ubuntu 17.04. The client was compiled from the source code. I am trying to run the following query on your database select distinct T.src as author, T1.src as coauthor, json.tojsonarray(T1.dst) as papers from edges1000 as T, edges1000 as T1 where T.dst = T1.dst AND T.src <> T1.src group by T.src, T1.src ; When I run this query over a table with at most 10^4 entries, the mclient client quits. If I try to execute the same qury via JDBC, I receive an error that tells me "maybe mserver closed unexpectedly". On the other hand, the server is still alive and responsive if I afterwards run the query either oover mclient or JDBC over a table having less data (edges10). Is that a problem of memory allocation ? Is there a way to solve this issue? I'm using the default configuration, and compiled the source code using "autoreconf -if" first. Thanks in advance for your support, Bergami G

1 0

latest version question
by Lynn Carol Johnson 02 Nov '17

02 Nov '17

Hello monetdb team – We upgraded this afternoon to the latest version of monetdb (Jul2017-SP2). When testing access for my lab, I noticed the general login could no longer perform the command: \d <table_name> The errors were related to missing group access to sys.objects, sys.keys and sys.idxs. When, as monetdb admin, I granted “select” on these tables, the “\d <table_name>” command then worked. Is this something new, or does it indicate a problem with the db ? It was not necessary to grant general users permission to these system tables previously. Thanks - Lynn

2 4

Problem in installation
by Amit Ambekar 27 Oct '17

27 Oct '17

Hi, Sorry sending this email again because of limit in message body is 40 kb. I had installed monetdb and now wanted to start mclient. So first i started Mserver5.bat and then when i click on mclient.bat that time "api-ms-win-crt-runtime-l1-1-0.dll is missing issue" this error is coming in alert box. Is monetdb is only usefull for windows server 2003,vista and Windows 7 or it can be used with Windows 2012 server also? Installed Visual C++ Redistributable for Visual Studio 2015 , then too error coming . Please check attached screenshot. this error coming when tried to start mclient

3 4

Setting Up Tools
by Sandy 26 Oct '17

26 Oct '17

Just getting back into trying MonetDB after many years. Got the environment set up pretty well with the test DB `VOC` loaded up and able to do all expected in the command line tools. So I figured I would give a couple of the mentioned GUI tools a shot and see how they work against the server I just set up on a virtual box. I picked up the JDBC drivers and installed on my Window's 10 install of SQuirrel SQL and DBeaver. I got both of the drivers works sort of... On DBeaver I can execute and see queries in the tool but can't do much more than that. Overall looks like an issue with accessing the system table `sys.environment`, not sure if anything related to set up or rights. Using the `voc` user as created with the example db in the getting started. Any help or thought on where to start? Thanks! Sandy -------- Errors * DBeaver 4.0.8 On inspection of some of the logs I see things like - `Error loading object metadata from database` `SQL Error [22000]: SELECT: access denied for voc to table 'sys.environment' java.sql.SQLException: SELECT: access denied for voc to table 'sys.environment'` * SQuirreL SQL Client 3.7.1 java.sql.SQLException: SELECT: access denied for voc to table 'sys.environment' -------- Driver and DB versions I'm using this JAR for the drivers - C:\Users\Squidward\Downloads\monetdb-jdbc-2.25.jar, 2.25 (Liberica 20170330 based on MCL v1.15) MonetDB version - MonetDB v11.25.23 (Dec2016-SP5), 'mapi:monetdb://sandy-VirtualBox-18:50000/voc'

3 6

Announcement: New Jul2017-SP2 Bugfix release of MonetDB suite
by Sjoerd Mullender 26 Oct '17

26 Oct '17

The MonetDB team at CWI/MonetDB BV is pleased to announce the Jul2017-SP2 bugfix release of the MonetDB suite of programs. More information about MonetDB can be found on our website at <http://www.monetdb.org/>. For details on this release, please see the release notes at <http://www.monetdb.org/Downloads/ReleaseNotes>. As usual, the download location is <http://dev.monetdb.org/downloads/>. Jul 2017-SP2 bugfix release (11.27.9) MonetDB Common * A serious bug, possibly resulting in database corruption, having to do with appending data to a string BAT was fixed. Bug Fixes * 4017: server crashes when executing particular loopback query in embedded python * 6207: identifier ambiguous when grouping and selecting the same column twice * 6239: Incorrect profiling * 6261: New handling of delta tables hurts badly reusage of bats * 6287: should the CORR function return some numeric type that allows fractions? * 6321: Two-column aggregation on join result extremely slow. * 6335: Sqlitelogictest crash in complex SQL query * 6343: MERGE TABLE issue: unable to find * 6348: Interference of procedure/table name * 6350: Carriage return and form feed in TEXT fields are returned as 'r' and 'f' in jdbcclient and ResultSets * 6352: Scope resolution problem (sqlsmith) * 6353: implicit NULL value not propagated in distributed/remote query * 6374: Wrong answer from merge table after content changes * 6379: Table UDF: SEGV raised when invoking a non existing function * 6380: unable to create new databases from clean installation * 6381: Parser misses error messages in conditional * 6382: Can't set JSON fields via PreparedStatement * 6384: crash when setting a wrong listenaddr * 6385: AGGREGATE UDFs with more than 2 parameters incorrectly processed * 6386: Unexpected error from server for query with long floats * 6387: Performance degradation on multi column sort * 6388: JDBC Connection via user voc produces errors when fetching certain meta data information * 6392: SELECT EXISTS (empty table) returns 'true' * 6395: BAT leak of scalar result sets * 6397: Isolation of generating functions not correct * 6398: Null Matches in outer join are not supported * 6399: UDF crashes when subquery and scalar values are passed as pameters * 6400: getCharacterStream() currently not supported * 6404: COPY INTO crashes if table has primary key or foreign key constraint * 6405: Compilation error if DISABLE_PARENT_HASH not defined * 6409: sqllogictest crash on aggregation query with NOT IN clause in HAVING clause * 6410: Sqlitelogictest crash on aggregation query with IN clause * 6411: Sqlitelogictest crash in aggregation query * 6412: Overflow in sys.epoch * 6413: Wrong answer for TPC-H Q17 * 6414: Using RPAD returns: could not allocate space * 6416: Sqlitelogictest crash in aggregation query with a NOT IN clause * 6417: Segfault encountered (sqlsmith) * 6418: Segfault in renaming (sqlsmith) * 6419: segfault in rel_optimizer (sqlsmith) * 6420: Assertion error in mergetable task (sqlsmith) * 6422: Another assertion error in rel_or (sqlsmith) * 6423: Dereference null pointer (sqlsmith) * 6424: Assertion error in rel_rename_expr (sqlsmith) * 6425: Assertion error in exp_bin (sqlsmith) * 6426: Assertion error in rel_find_exp_ (sqlsmith) * 6427: Assertion error in eq_typeswitchloop (sqlsmith) * 6428: Sqlitelogictest crash in aggregation query * 6430: Assertion raised in another eq_typeswitch error (sqlsmith) * 6431: Sqlitelogictest crash in aggregation query with a long having clause * 6432: Assertion error in exp_bin (sqlsmith) * 6433: Sqlitelogictest crash in complex SELECT query with IN operator * 6435: Sqlitelogictest crash in simple select query * 6437: System schemas "profiler" and "json" shouldn't be allowed to be dropped. * 6438: Implement functionality to enforce the restrict option in: DROP SCHEMA xyz RESTRICT; * 6440: Faulty plan generated. Query returns more rows than expected or existing in the view sys.tables.

1 0

Proper syntax for a windowing function
by Omri Schwarz 25 Oct '17

25 Oct '17

Hi, all. I'm trying to figure out the proper syntax for queries such as a moving average over a window in MonetDB. THis works: sql>select avg(energy) from TABLENAME; +--------------------------+ | L3 | +==========================+ | 658.5049567388747 | +--------------------------+ This fails: select avg(energy) OVER () from TABLENAME; SELECT: function 'avg' not found (Welcome to mclient, the MonetDB/SQL interactive terminal (Jul2017-SP1) Database: MonetDB v11.27.5 (Jul2017-SP1), 'mapi:monetdb://SERVER2:50000/scada' ) How do I write such a query? Many thanks.

4 5

Transaction Replication Question
by Sharma, Sreejith 25 Oct '17

25 Oct '17

All, I wanted to have "Transaction Replication" setup for getting an load balanced environment. It's mentioned in the documentation that below property needs to be setup for replication or shipping of the transaction log. How and where should I setup this? Is there anything else I should keep in mind for transaction replication? gdk_keep_persisted_log_files gdk_shared_logdir gdk_shared_drift_threshold Also, I wanted to know if we can have similar approach as below. Can we have different tables that are part of the same schema from different disks/mounts in MonetDB? Table 1 from Mount X Table 2 from Mount Y Regards, Sreejith

2 1

Yacc bug in sql_parser.y ?
by Omri Schwarz 24 Oct '17

24 Oct '17

Hi, all. Still trying to get rolling averages out of my MonetDB database, and I ran into this: Line 3954 of sql_parser.y, window_frame_end: UNBOUNDED FOLLOWING { $$ = _symbol_create_int(SQL_FRAME, -1); } | window_frame_following { $$ = $1; } | CURRENT ROW { $$ = _symbol_create_int(SQL_FRAME, 0); } ; window_frame_following: value_exp PRECEDING { $$ = $1; } ; Should that not be "value_exp FOLLOWING "?

1 0

RE: Transaction Replication Question - how to setup gdk_keep_persisted_log_files?
by Sharma, Sreejith 24 Oct '17

24 Oct '17

Also, below parameters. gdk_shared_logdir gdk_shared_drift_threshold From: Sharma, Sreejith Sent: Tuesday, October 24, 2017 4:01 PM To: Communication channel for MonetDB users Subject: Transaction Replication Question - how to setup gdk_keep_persisted_log_files? All, I wanted to have "Transaction Replication" setup for getting an load balanced environment. It's mentioned in the documentation that below property needs to be setup for replication or shipping of the transaction log. How and where should I setup this? Is there anything else I should keep in mind for transaction replication? gdk_keep_persisted_log_files Regards, Sreejith

1 0

Transaction Replication Question - how to setup gdk_keep_persisted_log_files?
by Sharma, Sreejith 24 Oct '17

24 Oct '17

All, I wanted to have "Transaction Replication" setup for getting an load balanced environment. It's mentioned in the documentation that below property needs to be setup for replication or shipping of the transaction log. How and where should I setup this? Is there anything else I should keep in mind for transaction replication? gdk_keep_persisted_log_files Regards, Sreejith

1 0

IMPORTANT: serious possibility for database corruption
by Sjoerd Mullender 23 Oct '17

23 Oct '17

We just found (and fixed) a serious problem in the Jul2017 releases that can result in database corruption. So far we have only seen the corruption occur after a database (mserver5) restart. We have not yet seen a problem with a server that continues running (but unfortunately no guarantees). We will create a new release candidate with this bug fixed as soon as possible (that will be version 11.27.9, i.e. anything in the 11.27.X series older than this is affected). Currently known work around is to *not* restart the server, but keep it running, and creating a dump of the database using msqldump. This dump will then have to be restored into a fresh database in a server that is not affected by the bug (Dec2016 release or the upcoming Jul2017-SP2). -- Sjoerd Mullender

1 0

Problem in installation
by Amit Ambekar 23 Oct '17

23 Oct '17

Hi Stefan, I had installed monetdb and now wanted to start mclient. So first i started Mserver5.bat and then when i click on mclient.bat that time "api-ms-win-crt-runtime-l1-1-0.dll is missing issue" this error is coming in alert box. Is monetdb is only usefull for windows server 2003,vista and Windows 7 or it can be used with Windows 2012 server also? On Monday 23 October 2017 03:30 PM, users-list-request(a)monetdb.org wrote: > Send users-list mailing list submissions to > users-list(a)monetdb.org > > To subscribe or unsubscribe via the World Wide Web, visit > https://www.monetdb.org/mailman/listinfo/users-list > or, via email, send a message with subject or body 'help' to > users-list-request(a)monetdb.org > > You can reach the person managing the list at > users-list-owner(a)monetdb.org > > When replying, please edit your Subject line so it is more specific > than "Re: Contents of users-list digest..." > > > Today's Topics: > > 1. Why does corr(int,int) return int? (Stefan Manegold) > 2. Re: Why does corr(int,int) return int? (Anthony Damico) > 3. Problem in installation (Amit Ambekar) > 4. Re: Why does corr(int,int) return int? (Stefan Manegold) > 5. Re: Problem in installation (Stefan Manegold) > > > ---------------------------------------------------------------------- > > Message: 1 > Date: Sun, 22 Oct 2017 19:48:27 +0200 (CEST) > From: Stefan Manegold <Stefan.Manegold(a)cwi.nl> > To: Communication channel for MonetDB users <users-list(a)monetdb.org> > Subject: Why does corr(int,int) return int? > Message-ID: <1997044866.6459538.1508694507470.JavaMail.zimbra(a)cwi.nl> > Content-Type: text/plain; charset=utf-8 > > > Hi, > > could some one possible explain to me > why the MonetDB corr() aggregate function, > that IMHO is supposed to calculate the correlation between two attributes, > does return the same type as (the larger of) its input type(s)? > > IMHO the correlation between two attributes (or (samples of) random variables > is a factional value between -1 and 1. > > Consequently, MonetDB corr() on two integer columns returns an integer results, > i.e., one of {-1,0,1} ... > > Shouldn't corr() --- much like avg(), var_pop(), var_samp(), stddev_samp(), stddev_pop() > always return type double ? > > Thanks! > > Best, > Stefan > -- Amit Ambekar Technology Associate Office: Ist floor, Poddar Chambers, Mathruradas Mill Compound, Lower Parel, Mumbai 400013 | Landline: +91 22 43470408 Cell: +91-7741823310 | Email: amit(a)g-square.in| Website: www.g-square.in cid:image003.png@01D2A947.80D76E60

2 1

Problem in windows OS
by Amit Ambekar 23 Oct '17

23 Oct '17

Hi, I am using monetdb since long time with linux OS, i just tried it with Windows 7 OS. But in windows after shut down machine, database is crashing. I am not able to use any query in mclient now for that databases. What is problem ?

2 2

Why does corr(int,int) return int?
by Stefan Manegold 23 Oct '17

23 Oct '17

Hi, could some one possible explain to me why the MonetDB corr() aggregate function, that IMHO is supposed to calculate the correlation between two attributes, does return the same type as (the larger of) its input type(s)? IMHO the correlation between two attributes (or (samples of) random variables is a factional value between -1 and 1. Consequently, MonetDB corr() on two integer columns returns an integer results, i.e., one of {-1,0,1} ... Shouldn't corr() --- much like avg(), var_pop(), var_samp(), stddev_samp(), stddev_pop() always return type double ? Thanks! Best, Stefan -- | 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) |

2 2

Is there kind of tablespace concepts?
by Sharma, Sreejith 18 Oct '17

18 Oct '17

Hi All, Is there any possibility of retrieving the same table from 2 different sources (as similar to 2 tablespaces)? Regards, Sreejith

2 5

MonetDB build failure on Alpine Linux
by Artem Klevtsov 14 Oct '17

14 Oct '17

Hi, I tried to build MonetDB from source on the Alpine Linux. But build process was stopped with the following message: merovingian.c: In function 'main': merovingian.c:835:51: error: 'S_IRWXO' undeclared (first use in this function) (e = openConnectionUNIX(&unsock, control_usock, S_IRWXO, _mero_ctlout)) == NO_ERR) { ^~~~~~~ merovingian.c:835:51: note: each undeclared identifier is reported only once for each function it appears in merovingian.c:862:9: error: 'S_IRWXG' undeclared (first use in this function) umask(S_IRWXG | S_IRWXO); ^~~~~~~ Steps to reproduce (inside the alpine:latest docker container): apk update apk add musl-dev gcc bison make python libressl-dev libxml2-dev xz-dev bzip2-dev lz4-dev readline-dev snappy-dev curl-dev pcre-dev unixodbc-dev libatomic_ops-dev wget wget https://www.monetdb.org/downloads/sources/Jul2017-SP1/MonetDB-11.27.5.tar.xz tar xvf MonetDB-11.27.5.tar.xz cd MonetDB-11.27.5/ ./configure make I found that S_IRWXO and S_IRWXG macro defined in /usr/include/sys/stat.h: egrep '(S_IRWXO|S_IRWXG)' /usr/include/sys/stat.h #define S_IRWXG 0070 #define S_IRWXO 0007 I found also that sys/stat.h included in merovingian.c: grep sys/stat.h /MonetDB-11.27.5/tools/merovingian/daemon/merovingian.c #include <sys/stat.h> /* stat */ -- Regards Artem Klevtsov

1 0

RE: users-list Digest, Vol 62, Issue 5
by Warren Master 13 Oct '17

13 Oct '17

Restarting the server resolved the issue. I did not look at the plans.. There still remains the isse that tables can be added multiple times to the same merge table causing duplicate rows to be returned In some cases we were unable to remove a table from a merge table without first dropping the merge table Additionally we also have issues with bulk copy into tables of merge tables GDKerror:!ERROR: Incompatible operands. - this occurs now and then.. the only fix is to drop the merge table , drop the affecting table that fails to bulk copy to , recreate it and recreate the merge table. When I tried to drop the table after removing from the merge table , the server would core dump., I was only able to drop the bad table after dropping the merge table, the alter table Merge Table drop table Sub Table did not work Warren S. Master Chief Technology Officer, TRG Management LP 280 Park Avenue - 30th floor/West - New York, NY 10017 Phone 212.984.3504 Email warren.master(a)rohatyngroup.com Web www.rohatyngroup.com Linkedin www.linkedin.com/in/warrenmaster -----Original Message----- From: users-list [mailto:users-list-bounces+warren.master=rohatyngroup.com@monetdb.org] On Behalf Of users-list-request(a)monetdb.org Sent: Friday, October 13, 2017 6:00 AM To: users-list(a)monetdb.org Subject: users-list Digest, Vol 62, Issue 5 Send users-list mailing list submissions to users-list(a)monetdb.org To subscribe or unsubscribe via the World Wide Web, visit https://www.monetdb.org/mailman/listinfo/users-list or, via email, send a message with subject or body 'help' to users-list-request(a)monetdb.org You can reach the person managing the list at users-list-owner(a)monetdb.org When replying, please edit your Subject line so it is more specific than "Re: Contents of users-list digest..." Today's Topics: 1. Re: Merge Table Issues (Niels Nes) ---------------------------------------------------------------------- Message: 1 Date: Thu, 12 Oct 2017 20:39:51 +0200 From: Niels Nes <Niels.Nes(a)cwi.nl> To: Communication channel for MonetDB users <users-list(a)monetdb.org> Subject: Re: Merge Table Issues Message-ID: <20171012183951.GA8701(a)niels.cwi.nl> Content-Type: text/plain; charset=utf-8 On Thu, Oct 05, 2017 at 07:29:08PM +0000, Warren Master wrote: > all_vw is a merge table with three tables all with identical structure Warren Do the plan's look correct, ie include the proper member tables? Niels > > > > intraday > > history > > currentyear > > ---------------------------------------------------------------------- > ----------------------- > > > > My merge tables do not return consistent results > > > > I have tried to refresh statistics > > > > ANALYZE sys. intraday (asofdate) MINMAX > > ANALYZE sys. history (asofdate) MINMAX > > ANALYZE sys. currentyear (asofdate) MINMAX > > > > still same issue > > > > the issue exists for a few dates that I checked, there are many days > that do work on the merge table , all dates work on the view and the > individual tables. > > I thought it might be an issue with statistics then the conversion > function made it work on the merge table , so now at a loss. > > > > > > > > the following query on the merge table return no rows > > ---------------------------------------------------------------------- > ----------------------- > > > > select fundid,sum(curdlttot) as fxt > > from vw where asofdate='2017-10-02' > > group by fundid > > > > The following does return data from the merge table albeit, much > slower ? after converting date value > > ---------------------------------------------------------------------- > ----------------------- > > select fundid,sum(curdlttot) as fxt > > from vw where asofdate='2017-10-02D' > > group by fundid > > > > select fundid,sum(curdlttot) as fxt > > from vw where asofdate= date_to_str(asofdate, '%Y-%m-%d') = > '2017-10-02' > > group by fundid > > > > > > the followings query on the subtable of the merge table returns data > with no issues or performance > > ---------------------------------------------------------------------- > ----------------------- > > select fundid,sum(curdlttot) as fxt > > from currentyear where asofdate='2017-10-02' > > group by fundid > > > > creating a view on the union of all three tables returns data as > expected > > > > create view test_all as > > select * from intraday > > union all > > select * from history > > union all > > select * from currentyear > > > > > > select fundid,sum(curdlttot) as fxt > > from test_all where asofdate='2017-10-02' > > group by fundid > > > > > > > > > > > > Warren S. Master > > Chief Technology Officer, CISO > > TRG Management LP > > 280 Park Avenue - 30th floor/West - New York, NY 10017 > > Phone 212.984.3504 > > Email warren.master(a)rohatyngroup.com > > Web www.rohatyngroup.com > > Linkedin www.linkedin.com/in/warrenmaster > > > > This e-mail communication and any attachments may contain confidential > and privileged information and are for use by the intended > addressee(s) only. If you are not the intended addressee, you have > received this communication in error. Any dissemination or > reproduction of this email or its contents is strictly prohibited and > may be unlawful. If you suspect you have received this communication > in error, please notify us immediately by replying to this message and > delete it from your computer.Thank you. > _______________________________________________ > 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/716e-mail: Niels.Nes(a)cwi.nl ------------------------------ Subject: Digest Footer _______________________________________________ users-list mailing list users-list(a)monetdb.org https://www.monetdb.org/mailman/listinfo/users-list ------------------------------ End of users-list Digest, Vol 62, Issue 5 ***************************************** This e-mail communication and any attachments may contain confidential and privileged information and are for use by the intended addressee(s) only. If you are not the intended addressee, you have received this communication in error. Any dissemination or reproduction of this email or its contents is strictly prohibited and may be unlawful. If you suspect you have received this communication in error, please notify us immediately by replying to this message and delete it from your computer.Thank you.

1 0

Merge Table Issues
by Warren Master 12 Oct '17

12 Oct '17

all_vw is a merge table with three tables all with identical structure intraday history currentyear --------------------------------------------------------------------------------------------- My merge tables do not return consistent results I have tried to refresh statistics ANALYZE sys. intraday (asofdate) MINMAX ANALYZE sys. history (asofdate) MINMAX ANALYZE sys. currentyear (asofdate) MINMAX still same issue the issue exists for a few dates that I checked, there are many days that do work on the merge table , all dates work on the view and the individual tables. I thought it might be an issue with statistics then the conversion function made it work on the merge table , so now at a loss. the following query on the merge table return no rows --------------------------------------------------------------------------------------------- select fundid,sum(curdlttot) as fxt from vw where asofdate='2017-10-02' group by fundid The following does return data from the merge table albeit, much slower - after converting date value --------------------------------------------------------------------------------------------- select fundid,sum(curdlttot) as fxt from vw where asofdate='2017-10-02D' group by fundid select fundid,sum(curdlttot) as fxt from vw where asofdate= date_to_str(asofdate, '%Y-%m-%d') = '2017-10-02' group by fundid the followings query on the subtable of the merge table returns data with no issues or performance --------------------------------------------------------------------------------------------- select fundid,sum(curdlttot) as fxt from currentyear where asofdate='2017-10-02' group by fundid creating a view on the union of all three tables returns data as expected create view test_all as select * from intraday union all select * from history union all select * from currentyear select fundid,sum(curdlttot) as fxt from test_all where asofdate='2017-10-02' group by fundid Warren S. Master Chief Technology Officer, CISO TRG Management LP 280 Park Avenue - 30th floor/West - New York, NY 10017 Phone 212.984.3504 Email warren.master(a)rohatyngroup.com<mailto:warren.master@rohatyngroup.com> Web www.rohatyngroup.com<http://www.rohatyngroup.com/> Linkedin www.linkedin.com/in/warrenmaster<http://www.linkedin.com/in/warrenmaster> This e-mail communication and any attachments may contain confidential and privileged information and are for use by the intended addressee(s) only. If you are not the intended addressee, you have received this communication in error. Any dissemination or reproduction of this email or its contents is strictly prohibited and may be unlawful. If you suspect you have received this communication in error, please notify us immediately by replying to this message and delete it from your computer.Thank you.

2 1

mal function returns clob datatype
by Gatis Ozoliņš 10 Oct '17

10 Oct '17

I have string aggregation mal function with this definition: function group_concat(b:bat[:str],s:bat[:str]):str; It was working fine on Dec 2016-SP3, however we upgraded to Jul 2017-SP1 and now function is returning clob datatype. I looked at Changelog and code changes and couldn't find anything that would explain this. Is it feature or bug? Gatis

1 0

make[9]: *** No rule to make target `../../../../sql/backends/monet5/lib_sql.la', needed by `lib_myudfname.la'. Stop.
by Anton Kravchenko 09 Oct '17

09 Oct '17

Hi there, I will try to map my issue to something that you are familiar with. For example "MonetDB-11.23.3/sql/backends/monet5/vaults" module. 1) It has Makefile.ag which has (among other lines): lib__vault = { MODULE DIR = libdir/monetdb5 SOURCES = vault.c LIBS = ../../../../monetdb5/tools/libmonetdb5 \ ../../../../gdk/libbat $(curl_LIBS) } And that means that "libmonetdb.la" has to be created before "vaults" is built. 2) Let's say I have added "../../../../sql/backends/monet5/lib_sql" dependency lib__vault = { MODULE DIR = libdir/monetdb5 SOURCES = vault.c LIBS = ../../../../monetdb5/tools/libmonetdb5 \ * ../../../../sql/backends/monet5/lib_sql \* ../../../../gdk/libbat $(curl_LIBS) } As you can see "vaults" is leaving inside "MonetDB-11.23.3/sql/backends/monet5" directory. 3) Now my question is: How can one propagate into MonetDB-11.23.3/sql/backends/monet5/Makefile.am that "lib_sql.la" has to be created before "vaults" is built? p.s. On Windows, there is MonetDB-11.23.3/sql/backends/monet5/Makefile.msc used (instead Makefile.am). And I would just change order in "all-recursive all-msc" to "all-msc all-recursive": # build-all: $(BUILT_SOURCES) all-recursive all-msc build-all: $(BUILT_SOURCES) all-msc all-recursive Thank you, Anton

2 4

Error starting monetdb
by Corbit, Dann 06 Oct '17

06 Oct '17

When M5server.bat runs, it outputs this message: !FATAL: BBPinit: first != 0 (ID = 72). And exits. I do not have any data I need to save in my database. Is there a way to drop the old database and create a new one, so that I can again run tests?

1 0

How to improve query performance on a single node in MonetDB?
by Tomas Repik | Instarea 04 Oct '17

04 Oct '17

Hello list, recently I came across MonetDB as a columnar database and I have some issues with getting it perform well. I posted my question on StackOverflow <https://stackoverflow.com/questions/46558842/how-to-improve-query-performan…>, idealy post your answers there but, mailing it to the list is fine as well. Thank you Tomas > TL;DR > I've installed the latest (MonetDB 5 server v11v.27.5 "Jul2017-SP1") on > Windows 2012 Server and I'm trying to query large table 1,4 billion rows in > a reasonable time 2-3s. > Is this even possible with MonetDB? What could I do to improve the > performance? > Detailed description of what I've done so far: > Created table: > CREATE TABLE t939ba ( id INT, xa INT, xb INT, ya INT, yb INT, a1 TINYINT, > a2 TINYINT, a3 TINYINT, a4 TINYINT, a5 TINYINT, a6 TINYINT, a7 TINYINT, a8 > TINYINT, a9 TINYINT); > Loaded the data: > COPY 1450000000 OFFSET 2 RECORDS INTO tbl FROM 'D:\\es_export\\file.csv' > USING DELIMITERS ',' NULL AS '' LOCKED; > Run the query: > SELECT COUNT(DISTINCT id) FROM tbl WHERE a1=22 > AND xb>=143455 AND yb>=90911 AND xa<=143615 AND ya<=91007 > AND a2 IN (2, 3, 4) AND a3 IN (0, 1, 2, 3, 4) AND a4 IN (0, 1, 2) > AND a5 IN (-1, 1, 2, 3, 4, 5, 6, 7) AND a6 IN (-1, 11, 12, 13, 14); > It took *14m 52s* to get the result.

3 2

build error MonetDBv11.27.5 on Linux
by Anton Kravchenko 19 Sep '17

19 Sep '17

Hi there, I am able to build and install MonetDB v11.21.19 "Jul2015-SP4" by following and example from https://www.monetdb.org/wiki/MonetDB:Building_from_sources But when I try build MonetDB v11.27.5 on same machine I get this error (for a full log info see https://drive.google.com/drive/folders/0B7dMycp0Ku-NTlRsZW95R0xCNmM?usp=sha… ): /bin/sh ../../../../../libtool --tag=CC --mode=link gcc -DLIBPYAPI -g -O2 -D_REENTRANT -module -avoid-version -o lib_pyapi.la -rpath /home/SECDEV.LOCAL/akravchenko/MonetDB-Jul2017-SP1/lib/monetdb5 lib_pyapi_la-pyapi.lo lib_pyapi_la-unicode.lo lib_pyapi_la-pytypes.lo lib_pyapi_la-type_conversion.lo lib_pyapi_la-formatinput.lo lib_pyapi_la-connection.lo lib_pyapi_la-pyloader.lo lib_pyapi_la-emit.lo lib_pyapi_la-conversion.lo ../../../../../monetdb5/tools/libmonetdb5.la ../../../../../gdk/libbat.la -lpython2.7 -lpthread -ldl -lutil -lm -Xlinker -export-dynamic libtool: link: gcc -shared -fPIC -DPIC .libs/lib_pyapi_la-pyapi.o .libs/lib_pyapi_la-unicode.o .libs/lib_pyapi_la-pytypes.o .libs/lib_pyapi_la-type_conversion.o .libs/lib_pyapi_la-formatinput.o .libs/lib_pyapi_la-connection.o .libs/lib_pyapi_la-pyloader.o .libs/lib_pyapi_la-emit.o .libs/lib_pyapi_la-conversion.o -Wl,-rpath -Wl,/home/SECDEV.LOCAL/akravchenko/MonetDB-Jul2017-SP1/BUILD/monetdb5/tools/.libs -Wl,-rpath -Wl,/home/SECDEV.LOCAL/akravchenko/MonetDB-Jul2017-SP1/BUILD/gdk/.libs -Wl,-rpath -Wl,/home/SECDEV.LOCAL/akravchenko/MonetDB-Jul2017-SP1/BUILD/clients/mapilib/.libs -Wl,-rpath -Wl,/home/SECDEV.LOCAL/akravchenko/MonetDB-Jul2017-SP1/BUILD/common/stream/.libs -Wl,-rpath -Wl,/home/SECDEV.LOCAL/akravchenko/MonetDB-Jul2017-SP1/lib -L/home/SECDEV.LOCAL/akravchenko/MonetDB-Jul2017-SP1/BUILD/gdk/.libs -L/home/SECDEV.LOCAL/akravchenko/MonetDB-Jul2017-SP1/BUILD/clients/mapilib/.libs -L/home/SECDEV.LOCAL/akravchenko/MonetDB-Jul2017-SP1/BUILD/common/stream/.libs ../../../../../monetdb5/tools/.libs/libmonetdb5.so /home/SECDEV.LOCAL/akravchenko/MonetDB-Jul2017-SP1/BUILD/gdk/.libs/libbat.so /home/SECDEV.LOCAL/akravchenko/MonetDB-Jul2017-SP1/BUILD/ clients/mapilib/.libs/libmapi.so -lpcre -lxml2 ../../../../../gdk/.libs/libbat.so /home/SECDEV.LOCAL/akravchenko /MonetDB-Jul2017-SP1/BUILD/common/stream/.libs/libstream.so -lsnappy -lssl -lcrypto -lz -lpython2.7 -lpthread -ldl -lutil -lm -g -O2 -Wl,-export-dynamic -pthread -Wl,-soname -Wl,lib_pyapi.so -o .libs/lib_pyapi.so /usr/bin/ld: cannot find -lpython2.7 collect2: ld returned 1 exit status make[11]: *** [lib_pyapi.la] Error 1 make[11]: Leaving directory `/home/SECDEV.LOCAL/akravchenk o/MonetDB-Jul2017-SP1/BUILD/sql/backends/monet5/UDF/pyapi' make[10]: *** [all] Error 2 make[10]: Leaving directory `/home/SECDEV.LOCAL/akravchenk o/MonetDB-Jul2017-SP1/BUILD/sql/backends/monet5/UDF/pyapi' make[9]: *** [all-recursive] Error 1 make[9]: Leaving directory `/home/SECDEV.LOCAL/akravchenk o/MonetDB-Jul2017-SP1/BUILD/sql/backends/monet5/UDF' make[8]: *** [all] Error 2 make[8]: Leaving directory `/home/SECDEV.LOCAL/akravchenk o/MonetDB-Jul2017-SP1/BUILD/sql/backends/monet5/UDF' make[7]: *** [all-recursive] Error 1 make[7]: Leaving directory `/home/SECDEV.LOCAL/akravchenk o/MonetDB-Jul2017-SP1/BUILD/sql/backends/monet5' make[6]: *** [all] Error 2 make[6]: Leaving directory `/home/SECDEV.LOCAL/akravchenk o/MonetDB-Jul2017-SP1/BUILD/sql/backends/monet5' make[5]: *** [all-recursive] Error 1 make[5]: Leaving directory `/home/SECDEV.LOCAL/akravchenk o/MonetDB-Jul2017-SP1/BUILD/sql/backends' make[4]: *** [all] Error 2 make[4]: Leaving directory `/home/SECDEV.LOCAL/akravchenk o/MonetDB-Jul2017-SP1/BUILD/sql/backends' make[3]: *** [all-recursive] Error 1 make[3]: Leaving directory `/home/SECDEV.LOCAL/akravchenk o/MonetDB-Jul2017-SP1/BUILD/sql' make[2]: *** [all] Error 2 make[2]: Leaving directory `/home/SECDEV.LOCAL/akravchenk o/MonetDB-Jul2017-SP1/BUILD/sql' make[1]: *** [all-recursive] Error 1 make[1]: Leaving directory `/home/SECDEV.LOCAL/akravchenk o/MonetDB-Jul2017-SP1/BUILD' make: *** [all] Error 2 Thank you, Anton

2 4

Embedded Python is enabled but an error was thrown during initialization.
by Anton Kravchenko 15 Sep '17

15 Sep '17

Hi there, sql>create or replace function f1() returns table(v1 INT) more>LANGUAGE PYTHON { more> return [1,2,3] more>}; operation successful (3.658ms) sql>select * from f1(); Embedded Python is enabled but an error was thrown during initialization. Does one have to setup another python env variables (like it's done on MonetDB for Windows: PYTHONHOME, PYTHONPATH) on Linux? p.s. Here are is how I setup environment 1) installed MonetDB v11.27.5 (Jul2017-SP1) binaries on centos-release-7-4.1708.el7.centos.x86_64: sudo yum install https://dev.monetdb.org/downloads/epel/MonetDB-release-epel.noarch.rpm sudo yum install https://www.monetdb.org/downloads/epel/7/x86_64/MonetDB-stream-11.27.5-2017… sudo yum install https://www.monetdb.org/downloads/epel/7/x86_64/MonetDB-client-11.27.5-2017… sudo yum install https://www.monetdb.org/downloads/epel/7/x86_64/MonetDB5-server-11.27.5-201… sudo yum install https://www.monetdb.org/downloads/epel/7/x86_64/MonetDB-SQL-server5-11.27.5… sudo yum install https://www.monetdb.org/downloads/epel/7/x86_64/MonetDB-python2-11.27.5-201… 2) installed Anaconda2 Python 2.7.13 |Anaconda 4.4.0 (64-bit)| (default, Dec 20 2016, 23:09:15) [GCC 4.4.7 20120313 (Red Hat 4.4.7-1)] on linux2 export PATH="/home/akravchenko/anaconda2/bin:$PATH" 3) created and started Monet database: monetdbd create hnode1_dbfarm monetdbd set port=50001 hnode1_dbfarm monetdbd set control=yes hnode1_dbfarm monetdbd set passphrase=monetdb hnode1_dbfarm monetdbd start hnode1_dbfarm monetdb create hnode1_dbfarm_db1 *monetdb set embedpy=true hnode1_dbfarm_db1* monetdb start hnode1_dbfarm_db1 monetdb release hnode1_dbfarm_db1 4)monetdbd get all hnode1_dbfarm property value hostname somehostname dbfarm hnode1_dbfarm status monetdbd[31089] 1.7 (Jul2017-SP1) is serving this dbfarm mserver /usr/bin/mserver5 logfile hnode1_dbfarm/merovingian.log pidfile hnode1_dbfarm/merovingian.pid sockdir /tmp listenaddr localhost port 50001 exittimeout 60 forward proxy discovery yes discoveryttl 600 control yes passphrase {SHA512}a73f1d86383446438ac64f56e15ada38b41fbb18f029d2181723aeb2acac6a831f60e5fdbd64ac2c8c70e035dd44cbbe3b45565ef2d58feb2821a2078c7fad35 mapisock /tmp/.s.monetdb.50001 controlsock /tmp/.s.merovingian.50001 5) monetdb get all hnode1_dbfarm_db1 name prop source value hnode1_dbfarm_db1 name - hnode1_dbfarm_db1 hnode1_dbfarm_db1 type default database hnode1_dbfarm_db1 shared default yes hnode1_dbfarm_db1 nthreads default 32 hnode1_dbfarm_db1 optpipe default default_pipe hnode1_dbfarm_db1 readonly default no hnode1_dbfarm_db1 embedr default no hnode1_dbfarm_db1 embedpy local yes hnode1_dbfarm_db1 embedpy3 default no hnode1_dbfarm_db1 nclients default 64 hnode1_dbfarm_db1 dbextra default <unknown> Thank you, Anton

1 2

SQL_CALC_FOUND_ROWS equivalent?
by Alberto Ferrari 15 Sep '17

15 Sep '17

Hi, is there any function or trick in Monet equivalent to mysql "SELECT SQL_CALC_FOUND_ROWS ..." to get de total rows when limit is present ? ((pagination) So I could get de TOTAL rows/pages number without exec another query everytime I need paginate results. Thanks!

2 2

Re: Column rename issue
by Stefan Manegold 15 Sep '17

15 Sep '17

Hi, Add new column, copy/cast data from old to new column, drop old column. Stefan -------- Original message --------From: Amit Ambekar <amit(a)g-square.in> Date: 9/15/17 12:14 (GMT+01:00) To: users-list(a)monetdb.org Subject: Column rename issue Hi, how to update already created column name and type in monetdb ? _______________________________________________ users-list mailing list users-list(a)monetdb.org https://www.monetdb.org/mailman/listinfo/users-list

1 0

Column rename issue
by Amit Ambekar 15 Sep '17

15 Sep '17

Hi, how to update already created column name and type in monetdb ?

1 0

large scale hardware configuration for MonetDB
by Anton Kravchenko 06 Sep '17

06 Sep '17

Hi there, >From https://www.monetdbsolutions.com/news/monetdb-numascale : "The system is fine-tuned to exploit the large main memories of modern computer systems effectively and has proven to be highly efficient in data warehouse query processing. NumaQ can easily scale to thousands of cores and terabytes of memory for data-intensive." I wonder if there is a public documentation on how to configure large scale hardware for optimal MonetDB performance. So one could try setup NumaQ-like machine by using what you already learned. Also from https://stackoverflow.com/questions/7435235/is-it-worth-trying-monetdb : "MonetDB is developed/tuned under two main assumptions: 1. Your workload is analytical, i.e., you have lots of (grouped) aggregations and the like. 2. Even more important: your hot dataset (the data that you actually work with) fits into the main memory of your system. MonetDB does not have it's own Buffer Manager but relies on the OS to handle disk I/O. Since the OS (especially windows but Linux too) is sometimes very dumb about disk swapping that may become a problem (especially for joins that run out of memory)." Are there any official benchmarks for Windows vs Linux MonetDB performance (especially for queries that require disk swapping) ? Thank you, Anton

2 1

default_pipe optimizer slowness during concurrency
by Sharma, Sreejith 06 Sep '17

06 Sep '17

Hi, I have a scenario where if I run the a SQL with 10 user concurrency in a recursive mode, the execution time is significantly slower. The SQL which completes in .8sec will end up in completing 5-10hrs during concurrency. This behavior is seen when the optimizer is set to "default_pipe" , however when set to "sequential_pipe" the timing has been drastically reduced and it end ups in seconds. How is "sequential_pipe" optimizer works here, is it to do anything with concurrency. NOTE - this happens when there is DISTINCT pattern in SQL. Regards, Sreejith

2 1

Autoload scripts from MonetDB/sql/scripts
by Lefteris 05 Sep '17

05 Sep '17

After playing around with some udfs and such, it seems to me that the autoload feature of the scripts in directory MonetDB/sql/scripts only run if I start monetdb with the following command mserver5 --dbpath=... and then when I connect with mclient all these messages about loading the scripts appear at the mserver5 console. However, if I create a dbfarm with monetdbd create path/ monetdbd start path/ and then a database monetdb create dbname monetdb release dbname and connect with mclient -lsql -d dbname all the goodies from the script dir do not appear to be there. Is there another trigger for this case that runs autoload? thanx

3 5

How to get Memory Dump. Is there any way to eliminate Memory leaks.
by Sharma, Sreejith 05 Sep '17

05 Sep '17

All, Is there any way to get the memory dump from MonetDB? The memory consumed by merver5 post concurrent query execution are not coming down. Need to investigate why memory leak is happening here. Regards, Sreejith

2 2

Monetdb on windows with embedded python 3.6
by imad hajj chahine 05 Sep '17

05 Sep '17

Hi, I have been successfully using monetdb (11.25.3) on windows with embedded python 2.7, recently I needed a library that is only supported on python 3.6, is it possible somehow to point monetdb to the python 3.6 folder? I know this is possible on linux, but my dev environment is on windows. Thank you.

3 6

Defining a table with a sorted column
by Cota, Luis 05 Sep '17

05 Sep '17

Is it possible to define a table such that the server maintains the table sorted with each new row? I'm dealing with observations on market data, and while market data for a specific stock symbol will be sorted, I will be adding multiple symbols to the same table in chunks, so that each symbol is contained within its own load file, and is sorted within that file. How can I enforce this constraint? - Luis ________________________________ IMPORTANT: The information contained in this email and/or its attachments is confidential. If you are not the intended recipient, please notify the sender immediately by reply and immediately delete this message and all its attachments. Any review, use, reproduction, disclosure or dissemination of this message or any attachment by an unintended recipient is strictly prohibited. Neither this message nor any attachment is intended as or should be construed as an offer, solicitation or recommendation to buy or sell any security or other financial instrument. Neither the sender, his or her employer nor any of their respective affiliates makes any warranties as to the completeness or accuracy of any of the information contained herein or that this message or any of its attachments is free of viruses.

2 1

Function with table as argument that return a table
by imad hajj chahine 05 Sep '17

05 Sep '17

Hi, I want to create a Python function that accept a query as argument, do some processing and return another table, In addition to the table columns I want to have some additional scalar parameters and i am getting the following error: function requires a single sub query. CREATE function f1(col1 string, col2 int, col3 double, scalar1 int) RETURNS TABLE(col1 string, col2 double, col3 double, col4 double) LANGUAGE PYTHON Is this the normal behavior of Table function, as in aggregate function I can combine columns with scalar parameters, any workaround for this issue. Thank you.

2 3

COPY INTO from LZ4 file: bug or missing feature?
by Ying Zhang 04 Sep '17

04 Sep '17

Dear all, Is COPY INTO from an LZ4 file supported by MonetDB? When I try to load data from an LZ4 file, I always get the error: Failed to import table 'pt', record too long (EOS found) Is this an error or a missing feature? Additional info: - MonetDB version Jul2017 (Mercurial source) - Mac OS (Sierra) - I checked the config.log that my lz4 lib was found - The LZ4 file is valid (I decompressed it, then I could load the data). Thanks in advance! Jennie

1 1

Execute MAL queries
by Dennis Fassl 04 Sep '17

04 Sep '17

Dear all, I am trying to write and execute MAL queries. The overall goal is to translate Cypher to MAL and to use Cypher as an input language for MonetDB. I am using the "explain" statement to get some example MAL queries. This is an output of explain (without the optimizer stuff at the end): function user.s4_1():void; X_0:void := querylog.define("explain select source, target from graph where source = \\'A\\';","default_pipe",14:int); X_30 := bat.new(nil:str); X_36 := bat.new(nil:int); X_34 := bat.new(nil:int); X_33 := bat.new(nil:str); X_32 := bat.new(nil:str); X_3 := sql.mvc(); X_7:bat[:str] := sql.bind(X_3,"sys","graph","source",0:int); C_4:bat[:oid] := sql.tid(X_3,"sys","graph"); C_18 := algebra.thetasubselect(X_7,C_4,"A","=="); X_21:bat[:str] := sql.bind(X_3,"sys","graph","target",0:int); X_28 := algebra.projection(C_18,X_21); X_20 := algebra.projection(C_18,X_7); X_37 := bat.append(X_30,"sys.graph"); X_39 := bat.append(X_32,"source"); X_40 := bat.append(X_33,"varchar"); X_42 := bat.append(X_34,31:int); X_44 := bat.append(X_36,0:int); X_46 := bat.append(X_37,"sys.graph"); X_47 := bat.append(X_39,"target"); X_48 := bat.append(X_40,"varchar"); X_49 := bat.append(X_42,31:int); X_50 := bat.append(X_44,0:int); sql.resultSet(X_46,X_47,X_48,X_49,X_50,X_20,X_28); end user.s4_1; But when I try to execute this query I get errors like that: ERROR = !TypeException:user.main[8]:'sql.tid' undefined in: C_4:bat[:oid] := sql.tid("sys":str,"sys":str,"graph":str); I don't know why the "X_3" is replaced by "sys". I see changes like that in the queries regularly. Does someone know what is wrong or missing in this query? Thanks a lot in advance! Best regards, Dennis

4 5

Possible bug with table generating functions
by Mark Clements 29 Aug '17

29 Aug '17

Is there a restriction on the use of the same table generating function in the same select statement? This relates to the following possible bug: -- reimplement generate_series for MonetDBLite CREATE FUNCTION my_generate_series(start INT, finish INT) RETURNS TABLE (value INT) BEGIN DECLARE TABLE tmp_generate_series(value INT); DECLARE i INT; SET i = start; WHILE (i <= finish) DO INSERT INTO tmp_generate_series VALUES(i); SET i = i + 1; END WHILE; RETURN tmp_generate_series; END; --- This seems to work quite nicely: select * from my_generate_series(1,10); -- and it's fast: select count(*) from my_generate_series(1,100000); -- however, I get odd and non-deterministic counts for the following: select count(*) from my_generate_series(1,100) as t1, my_generate_series(1,100) as t2; This is using MonetDB v11.23.3 (Jun2016) that was compiled with embedded R, but not run with --set embedded_r=true. I got similar errors with MonetDBLite. Kindly, Mark.

2 2

Looking for Admin UI / Tools / Resources
by Cota, Luis 28 Aug '17

28 Aug '17

Can anyone recommend any tools (commercial or open source) which assist in administering a MonetDB instance? Things I'd like to do are: Create tables from existing data files (CSV, TXT, HDF5) Import or query Oracle databases and either create tables, or populate tables, with the results of those queries Query existing monetdb instances Thanks, Luis ________________________________ IMPORTANT: The information contained in this email and/or its attachments is confidential. If you are not the intended recipient, please notify the sender immediately by reply and immediately delete this message and all its attachments. Any review, use, reproduction, disclosure or dissemination of this message or any attachment by an unintended recipient is strictly prohibited. Neither this message nor any attachment is intended as or should be construed as an offer, solicitation or recommendation to buy or sell any security or other financial instrument. Neither the sender, his or her employer nor any of their respective affiliates makes any warranties as to the completeness or accuracy of any of the information contained herein or that this message or any of its attachments is free of viruses.

1 0

Error after updating monetdb
by Mahmoud Mohsen 19 Aug '17

19 Aug '17

Hello, I have updated monetdb from Dec2016-SP5 to version v11.27.5 "Jul2017-SP1, but after this when trying to accessing the database via mclient -u monetdb -d tpch2 I am getting this error: internal error while starting mserver 'database 'tpch2' started up, but failed to open up a communication channel', please refer to the logs looking at the logs, 2017-08-18 22:42:02 ERR merovingian[10645]: client error: database 'voc' started up, but failed to open up a communication channel 2017-08-18 22:42:02 MSG merovingian[10645]: database 'voc' (10660) has exited with exit status 0 2017-08-18 22:42:02 MSG merovingian[10645]: starting database 'voc', up min/avg/max: 0s/0s/0s, crash average: 0.00 0.00 0.00 (1-1=0) 2017-08-18 22:42:02 MSG merovingian[10645]: sending process 10663 (database 'voc') the TERM signal 2017-08-18 22:42:02 MSG voc[10663]: arguments: /usr/bin/mserver5 --dbpath=/home/mohsen/mfarm/voc --set merovingian_uri=mapi:monetdb://u145-16:50000/voc --set mapi_open=false --set mapi_port=0 --set mapi_usock=/home/mohsen/mfarm/voc/.mapi.sock --set monet_vault_key=/home/mohsen/mfarm/voc/.vaultkey --set gdk_nr_threads=8 --set max_clients=64 --set sql_optimizer=default_pipe --set monet_daemon=yes 2017-08-18 22:42:02 MSG voc[10663]: # MonetDB 5 server v11.27.5 "Jul2017-SP1" 2017-08-18 22:42:02 MSG voc[10663]: # Serving database 'voc', using 8 threads 2017-08-18 22:42:02 MSG voc[10663]: # Compiled for x86_64-pc-linux-gnu/64bit with 128bit integers 2017-08-18 22:42:02 MSG voc[10663]: # Found 251.889 GiB available main-memory. 2017-08-18 22:42:02 MSG voc[10663]: # Copyright (c) 1993-July 2008 CWI. 2017-08-18 22:42:02 MSG voc[10663]: # Copyright (c) August 2008-2017 MonetDB B.V., all rights reserved 2017-08-18 22:42:02 MSG voc[10663]: # Visit https://www.monetdb.org/ for further information 2017-08-18 22:42:02 MSG voc[10663]: !SyntaxException:parseError:include sqlcatalog; 2017-08-18 22:42:02 MSG voc[10663]: !SyntaxException:parseError:^LoaderException:loadLibrary:Loading error failed to open library sql (from within file '/usr/lib/monetdb5/lib_sql.so'): /usr/lib/monetdb5/lib_sql.so: undefined symbol: mnstr_writeFlt 2017-08-18 22:42:02 MSG voc[10663]: !SyntaxException:parseError:include sqlcatalog; 2017-08-18 22:42:02 MSG voc[10663]: !SyntaxException:parseError: ^';' expected 2017-08-18 22:42:03 MSG merovingian[10645]: database 'voc' has shut down 2017-08-18 22:42:03 ERR control[10645]: !monetdbd: an internal error has occurred 'database 'voc' started up, but failed to open up a communication channel' 2017-08-18 22:42:07 ERR merovingian[10645]: client error: database 'voc' started up, but failed to open up a communication channel 2017-08-18 22:42:07 MSG merovingian[10645]: database 'voc' (10663) has exited with exit status 0 Thank you a lot in advance, Best regards, Mahmoud Mohsen

2 3

Re: Re: failed to import record too long: Loading 1.5 Terabyte table
by Stefan Manegold 18 Aug '17

18 Aug '17

You need to use the correct record delimiter i.e. newline instead of space. -------- Original message --------From: Mahmoud Mohsen <mmohsen35(a)gmail.com> Date: 8/18/17 19:40 (GMT+01:00) To: users-list(a)monetdb.org Subject: Re: Re: failed to import record too long: Loading 1.5 Terabyte table Dear Stefan, Thank you very much for your quick reply, so this is the first line of the file "lineitem.tbl", so no need to use an offset I think 1|310378689|15378705|1|17|29786.89|0.04|0.02|N|O|1996-03-13|1996-02-12|1996-03-22|DELIVER IN PERSON|TRUCK|egular courts above the| as for the version, I am using: MonetDB Database Server Toolkit v1.1 (Dec2016-SP5). So can this may be the cause of the error... an old version? I am looking forward hearing from you, Best regards, Mahmoud Mohsen

2 2

failed to import record too long: Loading 1.5 Terabyte table
by Mahmoud Mohsen 18 Aug '17

18 Aug '17

Hello, I'm trying to load a table with 1.5 Terabytes of data, the command I'm using COPY INTO lineitem FROM '/vol1/tpch-dbgen/2000/lineitem.tbl' USING DELIMITERS '|', ' ' LOCKED; The physical memory I'm using is 256G, the swap is 2.2 terabytes, the dbfarm is located within area has 4 Terabytes of free storage. The message I'm getting: "Failed to import table 'lineitem', record too long" So what could be a possible solution to this problem, Thank you a lot in advance, Mahmoud Mohsen

3 3

(lastest version) monetdb source compilation and installation problem
by 张馨匀 17 Aug '17

17 Aug '17

Hi All, Could you please help me to solve the following error? When I try to install monetdb I have the following error: * fatal error: *geos_c.h: No such file or directory #include <geos_c.h> *My steps used to compile monetdb src and to install monetdb are:* a. Environment setup: $ export MonetDB_rel=July2017 $ export MonetDB_ver=11.27.5 $ export MonetDB_base_dir=$HOME/MonetDB-$MonetDB_rel $ export MonetDB_source_dir=$MonetDB_base_dir/MonetDB-$MonetDB_ver $ export MonetDB_build_dir=$MonetDB_base_dir/BUILD $ export MonetDB_prefix_dir=$MonetDB_base_dir $ mkdir $MonetDB_base_dir $ cd $MonetDB_base_dir b. Download the latest version: $ wget https://dev.monetdb.org/downloads/sources/Jul2017-SP1/MonetDB-11.27.5.tar.xz $ tar xf MonetDB-$MonetDB_ver.tar.xz c. Build: $ mkdir $MonetDB_build_dir $ cd $MonetDB_build_dir $ MonetDB_source_dir/configure --prefix=$MonetDB_prefix_dir d. Install: $ make -j 20 (error mentioned above occurs here) $ make -j 20 install Thanks,

2 1

Memory bloat on concurrency - Any fix to release memory post query run
by Sharma, Sreejith 11 Aug '17

11 Aug '17

Hi, I have been using monetdb (11.25.3) in CentOS for one of application. I have been working on a PSR testing for this application and have noticed on high concurrency and on same pattern of query running again and again causing memory to bloat up and doesn’t release once the query run completes. I understand that the data is lifted into memory and the result set is also cached, however I assume this should be release once the system is idle which I don’t see it as of now. Is there any way or parameter to release the unused memory blocks? I have asked this before, but couldn't get to the root to fix this. Regards, Sreejith

3 4

Troubles with Python API
by 倪令斌 09 Aug '17

09 Aug '17

Hi, I want to set up an API to MonetDBwith Python. I have followed the instructions（https://www.monetdb.org/Documentation/Manuals/SQLreference/Pro… plan to make connection to my DB with this Python statement: import pymonetdb; connection=pymonetdb.connect(username="monetdb",password="monetdb",hostname="127.0.0.1",database="testdb") But I got the following ERROR: Traceback (most recent call last): File "<stdin>", line 1, in <module> File "/home/lingbin/maizegenome/maizedbfarm/testpy/local/lib/python2.7/site-packages/pymonetdb/__init__.py", line 48, in connect return Connection(*args, **kwargs) File "/home/lingbin/maizegenome/maizedbfarm/testpy/local/lib/python2.7/site-packages/pymonetdb/sql/connections.py", line 55, in __init__ unix_socket=unix_socket, connect_timeout=connect_timeout) File "/home/lingbin/maizegenome/maizedbfarm/testpy/local/lib/python2.7/site-packages/pymonetdb/mapi.py", line 139, in connect self.socket.connect((hostname, port)) File "/usr/lib/python2.7/socket.py", line 228, in meth return getattr(self._sock,name)(*args) socket.error: [Errno 111] Connection refused Do you have a hint for me how to solve this error? Many thanks, Lingbin

2 3

Troubles with Python API
by 倪令斌 08 Aug '17

08 Aug '17

Hi, I want to set up an API to MonetDBwith Python. I have followed the instructions（https://www.monetdb.org/Documentation/Manuals/SQLreference/Pro… plan to make connection to my DB with this Python statement: import pymonetdb; connection=pymonetdb.connect(username="monetdb",password="monetdb",hostname="127.0.0.1",database="testdb") But I got the following ERROR: Traceback (most recent call last): File "<stdin>", line 1, in <module> File "/home/lingbin/maizegenome/maizedbfarm/testpy/local/lib/python2.7/site-packages/pymonetdb/__init__.py", line 48, in connect return Connection(*args, **kwargs) File "/home/lingbin/maizegenome/maizedbfarm/testpy/local/lib/python2.7/site-packages/pymonetdb/sql/connections.py", line 55, in __init__ unix_socket=unix_socket, connect_timeout=connect_timeout) File "/home/lingbin/maizegenome/maizedbfarm/testpy/local/lib/python2.7/site-packages/pymonetdb/mapi.py", line 139, in connect self.socket.connect((hostname, port)) File "/usr/lib/python2.7/socket.py", line 228, in meth return getattr(self._sock,name)(*args) socket.error: [Errno 111] Connection refused Do you have a hint for me how to solve this error? Many thanks, Lingbin

1 0

Mdiff outputs unexpected result
by Zhouyang Jia 05 Aug '17

05 Aug '17

Hi, I'm new to MonetDB, and I find that the component Mdiff would generate unexpected results if inputted wrong files, e.g., ./Mdiff wrong_file_1 wrong_file1. Mdiff will output that these two files are equal, whereas the expected result shoud be "files not exist". Attached please find the patch against version MonetDB-11.27.5. Hopefully, it can solve this problem. Best, Zhouyang

1 0

Announcement: New Jul2017-SP1 Bugfix release of MonetDB suite
by Sjoerd Mullender 04 Aug '17

04 Aug '17

The MonetDB team at CWI/MonetDB BV is pleased to announce the Jul2017-SP1 bugfix release of the MonetDB suite of programs. More information about MonetDB can be found on our website at <http://www.monetdb.org/>. For details on this release, please see the release notes at <http://www.monetdb.org/Downloads/ReleaseNotes>. As usual, the download location is <http://dev.monetdb.org/downloads/>. Jul 2017-SP1 bugfix release Build Environment * The Debian and Ubuntu installers have been fixed: there was a file missing in the Jul2017 release. * Added a new RPM called MonetDB-selinux which provides the SELinux policy required to run MonetDB under systemd, especially on Fedora 26. * The Windows installers (*.msi files) are now created using the WiX Toolset. * The Windows binaries are now built using Visual Studio 2015. Because of this, you may need to install the Visual C++ Redistributable for Visual Studio 2015 before being able to run MonetDB. Merovingian * monetdbd was leaking open file descriptors to the mserver5 process it started. This has been fixed. MonetDB Common * Many functions in GDK are now annotated with the GCC attribute __warn_unused_result__ meaning that the compiler will issue a warning if the result of the function (usually an indication of an error) is not used. Bug Fixes * 6325: Merge table unusable in other connections * 6328: Transactional/multi-connection issues with merge tables * 6336: VALUES multiple inserts error * 6339: Mserver5 crashes on nested SELECT * 6340: sample operator takes effect after the execution of the query, expected before * 6341: MERGE TABLE issue: Cannot register * 6342: MERGE TABLE issue: hang * 6344: Spurious errors and assertions (SQLsmith) * 6375: MAL profiler truncates JSON objects larger than 8192 characters

3 2

Error during installation (make)
by Dennis Fassl 31 Jul '17

31 Jul '17

Hi, I want to install MonetDB from the source files. (Version 11.27.1). I am following the guide "HowToStart.rst" in the source directory. But I get an error when executing make. Please find the output below. Do you have a hint for me how to solve this error? Many thanks and kind regards, Dennis make all-recursive make[1]: Entering directory `/home/dennis/MonetDB-11.27.1/buildDir' Making all in buildtools make[2]: Entering directory `/home/dennis/MonetDB-11.27.1/buildDir/buildtools' make all-recursive make[3]: Entering directory `/home/dennis/MonetDB-11.27.1/buildDir/buildtools' Making all in conf make[4]: Entering directory `/home/dennis/MonetDB-11.27.1/buildDir/buildtools/conf' make all-am make[5]: Entering directory `/home/dennis/MonetDB-11.27.1/buildDir/buildtools/conf' make[5]: Nothing to be done for `all-am'. make[5]: Leaving directory `/home/dennis/MonetDB-11.27.1/buildDir/buildtools/conf' make[4]: Leaving directory `/home/dennis/MonetDB-11.27.1/buildDir/buildtools/conf' make[4]: Entering directory `/home/dennis/MonetDB-11.27.1/buildDir/buildtools' make[4]: Nothing to be done for `all-am'. make[4]: Leaving directory `/home/dennis/MonetDB-11.27.1/buildDir/buildtools' make[3]: Leaving directory `/home/dennis/MonetDB-11.27.1/buildDir/buildtools' make[2]: Leaving directory `/home/dennis/MonetDB-11.27.1/buildDir/buildtools' Making all in common make[2]: Entering directory `/home/dennis/MonetDB-11.27.1/buildDir/common' make all-recursive make[3]: Entering directory `/home/dennis/MonetDB-11.27.1/buildDir/common' Making all in stream make[4]: Entering directory `/home/dennis/MonetDB-11.27.1/buildDir/common/stream' make all-am make[5]: Entering directory `/home/dennis/MonetDB-11.27.1/buildDir/common/stream' make[5]: Nothing to be done for `all-am'. make[5]: Leaving directory `/home/dennis/MonetDB-11.27.1/buildDir/common/stream' make[4]: Leaving directory `/home/dennis/MonetDB-11.27.1/buildDir/common/stream' Making all in options make[4]: Entering directory `/home/dennis/MonetDB-11.27.1/buildDir/common/options' make all-am make[5]: Entering directory `/home/dennis/MonetDB-11.27.1/buildDir/common/options' make[5]: Nothing to be done for `all-am'. make[5]: Leaving directory `/home/dennis/MonetDB-11.27.1/buildDir/common/options' make[4]: Leaving directory `/home/dennis/MonetDB-11.27.1/buildDir/common/options' Making all in utils make[4]: Entering directory `/home/dennis/MonetDB-11.27.1/buildDir/common/utils' make all-am make[5]: Entering directory `/home/dennis/MonetDB-11.27.1/buildDir/common/utils' make[5]: Nothing to be done for `all-am'. make[5]: Leaving directory `/home/dennis/MonetDB-11.27.1/buildDir/common/utils' make[4]: Leaving directory `/home/dennis/MonetDB-11.27.1/buildDir/common/utils' make[4]: Entering directory `/home/dennis/MonetDB-11.27.1/buildDir/common' make[4]: Nothing to be done for `all-am'. make[4]: Leaving directory `/home/dennis/MonetDB-11.27.1/buildDir/common' make[3]: Leaving directory `/home/dennis/MonetDB-11.27.1/buildDir/common' make[2]: Leaving directory `/home/dennis/MonetDB-11.27.1/buildDir/common' Making all in clients make[2]: Entering directory `/home/dennis/MonetDB-11.27.1/buildDir/clients' make all-recursive make[3]: Entering directory `/home/dennis/MonetDB-11.27.1/buildDir/clients' Making all in mapilib make[4]: Entering directory `/home/dennis/MonetDB-11.27.1/buildDir/clients/mapilib' make all-am make[5]: Entering directory `/home/dennis/MonetDB-11.27.1/buildDir/clients/mapilib' make[5]: Nothing to be done for `all-am'. make[5]: Leaving directory `/home/dennis/MonetDB-11.27.1/buildDir/clients/mapilib' make[4]: Leaving directory `/home/dennis/MonetDB-11.27.1/buildDir/clients/mapilib' Making all in mapiclient make[4]: Entering directory `/home/dennis/MonetDB-11.27.1/buildDir/clients/mapiclient' make all-am make[5]: Entering directory `/home/dennis/MonetDB-11.27.1/buildDir/clients/mapiclient' make[5]: Nothing to be done for `all-am'. make[5]: Leaving directory `/home/dennis/MonetDB-11.27.1/buildDir/clients/mapiclient' make[4]: Leaving directory `/home/dennis/MonetDB-11.27.1/buildDir/clients/mapiclient' Making all in examples make[4]: Entering directory `/home/dennis/MonetDB-11.27.1/buildDir/clients/examples' make all-recursive make[5]: Entering directory `/home/dennis/MonetDB-11.27.1/buildDir/clients/examples' Making all in C make[6]: Entering directory `/home/dennis/MonetDB-11.27.1/buildDir/clients/examples/C' make all-am make[7]: Entering directory `/home/dennis/MonetDB-11.27.1/buildDir/clients/examples/C' make[7]: Nothing to be done for `all-am'. make[7]: Leaving directory `/home/dennis/MonetDB-11.27.1/buildDir/clients/examples/C' make[6]: Leaving directory `/home/dennis/MonetDB-11.27.1/buildDir/clients/examples/C' Making all in php make[6]: Entering directory `/home/dennis/MonetDB-11.27.1/buildDir/clients/examples/php' make all-am make[7]: Entering directory `/home/dennis/MonetDB-11.27.1/buildDir/clients/examples/php' make[7]: Nothing to be done for `all-am'. make[7]: Leaving directory `/home/dennis/MonetDB-11.27.1/buildDir/clients/examples/php' make[6]: Leaving directory `/home/dennis/MonetDB-11.27.1/buildDir/clients/examples/php' Making all in perl make[6]: Entering directory `/home/dennis/MonetDB-11.27.1/buildDir/clients/examples/perl' make all-am make[7]: Entering directory `/home/dennis/MonetDB-11.27.1/buildDir/clients/examples/perl' make[7]: Nothing to be done for `all-am'. make[7]: Leaving directory `/home/dennis/MonetDB-11.27.1/buildDir/clients/examples/perl' make[6]: Leaving directory `/home/dennis/MonetDB-11.27.1/buildDir/clients/examples/perl' make[6]: Entering directory `/home/dennis/MonetDB-11.27.1/buildDir/clients/examples' make[6]: Nothing to be done for `all-am'. make[6]: Leaving directory `/home/dennis/MonetDB-11.27.1/buildDir/clients/examples' make[5]: Leaving directory `/home/dennis/MonetDB-11.27.1/buildDir/clients/examples' make[4]: Leaving directory `/home/dennis/MonetDB-11.27.1/buildDir/clients/examples' make[4]: Entering directory `/home/dennis/MonetDB-11.27.1/buildDir/clients' make[4]: Nothing to be done for `all-am'. make[4]: Leaving directory `/home/dennis/MonetDB-11.27.1/buildDir/clients' make[3]: Leaving directory `/home/dennis/MonetDB-11.27.1/buildDir/clients' make[2]: Leaving directory `/home/dennis/MonetDB-11.27.1/buildDir/clients' Making all in gdk make[2]: Entering directory `/home/dennis/MonetDB-11.27.1/buildDir/gdk' make all-am make[3]: Entering directory `/home/dennis/MonetDB-11.27.1/buildDir/gdk' make[3]: Nothing to be done for `all-am'. make[3]: Leaving directory `/home/dennis/MonetDB-11.27.1/buildDir/gdk' make[2]: Leaving directory `/home/dennis/MonetDB-11.27.1/buildDir/gdk' Making all in monetdb5 make[2]: Entering directory `/home/dennis/MonetDB-11.27.1/buildDir/monetdb5' make all-recursive make[3]: Entering directory `/home/dennis/MonetDB-11.27.1/buildDir/monetdb5' Making all in mal make[4]: Entering directory `/home/dennis/MonetDB-11.27.1/buildDir/monetdb5/mal' make all-am make[5]: Entering directory `/home/dennis/MonetDB-11.27.1/buildDir/monetdb5/mal' make[5]: Nothing to be done for `all-am'. make[5]: Leaving directory `/home/dennis/MonetDB-11.27.1/buildDir/monetdb5/mal' make[4]: Leaving directory `/home/dennis/MonetDB-11.27.1/buildDir/monetdb5/mal' Making all in modules make[4]: Entering directory `/home/dennis/MonetDB-11.27.1/buildDir/monetdb5/modules' make all-recursive make[5]: Entering directory `/home/dennis/MonetDB-11.27.1/buildDir/monetdb5/modules' Making all in atoms make[6]: Entering directory `/home/dennis/MonetDB-11.27.1/buildDir/monetdb5/modules/atoms' make all-am make[7]: Entering directory `/home/dennis/MonetDB-11.27.1/buildDir/monetdb5/modules/atoms' make[7]: Nothing to be done for `all-am'. make[7]: Leaving directory `/home/dennis/MonetDB-11.27.1/buildDir/monetdb5/modules/atoms' make[6]: Leaving directory `/home/dennis/MonetDB-11.27.1/buildDir/monetdb5/modules/atoms' Making all in kernel make[6]: Entering directory `/home/dennis/MonetDB-11.27.1/buildDir/monetdb5/modules/kernel' make all-am make[7]: Entering directory `/home/dennis/MonetDB-11.27.1/buildDir/monetdb5/modules/kernel' make[7]: Nothing to be done for `all-am'. make[7]: Leaving directory `/home/dennis/MonetDB-11.27.1/buildDir/monetdb5/modules/kernel' make[6]: Leaving directory `/home/dennis/MonetDB-11.27.1/buildDir/monetdb5/modules/kernel' Making all in mal make[6]: Entering directory `/home/dennis/MonetDB-11.27.1/buildDir/monetdb5/modules/mal' make all-am make[7]: Entering directory `/home/dennis/MonetDB-11.27.1/buildDir/monetdb5/modules/mal' make[7]: Nothing to be done for `all-am'. make[7]: Leaving directory `/home/dennis/MonetDB-11.27.1/buildDir/monetdb5/modules/mal' make[6]: Leaving directory `/home/dennis/MonetDB-11.27.1/buildDir/monetdb5/modules/mal' make[6]: Entering directory `/home/dennis/MonetDB-11.27.1/buildDir/monetdb5/modules' make[6]: Nothing to be done for `all-am'. make[6]: Leaving directory `/home/dennis/MonetDB-11.27.1/buildDir/monetdb5/modules' make[5]: Leaving directory `/home/dennis/MonetDB-11.27.1/buildDir/monetdb5/modules' make[4]: Leaving directory `/home/dennis/MonetDB-11.27.1/buildDir/monetdb5/modules' Making all in optimizer make[4]: Entering directory `/home/dennis/MonetDB-11.27.1/buildDir/monetdb5/optimizer' make all-am make[5]: Entering directory `/home/dennis/MonetDB-11.27.1/buildDir/monetdb5/optimizer' make[5]: Nothing to be done for `all-am'. make[5]: Leaving directory `/home/dennis/MonetDB-11.27.1/buildDir/monetdb5/optimizer' make[4]: Leaving directory `/home/dennis/MonetDB-11.27.1/buildDir/monetdb5/optimizer' Making all in scheduler make[4]: Entering directory `/home/dennis/MonetDB-11.27.1/buildDir/monetdb5/scheduler' make all-am make[5]: Entering directory `/home/dennis/MonetDB-11.27.1/buildDir/monetdb5/scheduler' make[5]: Nothing to be done for `all-am'. make[5]: Leaving directory `/home/dennis/MonetDB-11.27.1/buildDir/monetdb5/scheduler' make[4]: Leaving directory `/home/dennis/MonetDB-11.27.1/buildDir/monetdb5/scheduler' Making all in tools make[4]: Entering directory `/home/dennis/MonetDB-11.27.1/buildDir/monetdb5/tools' make all-am make[5]: Entering directory `/home/dennis/MonetDB-11.27.1/buildDir/monetdb5/tools' make[5]: Nothing to be done for `all-am'. make[5]: Leaving directory `/home/dennis/MonetDB-11.27.1/buildDir/monetdb5/tools' make[4]: Leaving directory `/home/dennis/MonetDB-11.27.1/buildDir/monetdb5/tools' Making all in extras make[4]: Entering directory `/home/dennis/MonetDB-11.27.1/buildDir/monetdb5/extras' make all-recursive make[5]: Entering directory `/home/dennis/MonetDB-11.27.1/buildDir/monetdb5/extras' Making all in mal_optimizer_template make[6]: Entering directory `/home/dennis/MonetDB-11.27.1/buildDir/monetdb5/extras/mal_optimizer_template' make all-am make[7]: Entering directory `/home/dennis/MonetDB-11.27.1/buildDir/monetdb5/extras/mal_optimizer_template' make[7]: Nothing to be done for `all-am'. make[7]: Leaving directory `/home/dennis/MonetDB-11.27.1/buildDir/monetdb5/extras/mal_optimizer_template' make[6]: Leaving directory `/home/dennis/MonetDB-11.27.1/buildDir/monetdb5/extras/mal_optimizer_template' Making all in rapi make[6]: Entering directory `/home/dennis/MonetDB-11.27.1/buildDir/monetdb5/extras/rapi' make all-am make[7]: Entering directory `/home/dennis/MonetDB-11.27.1/buildDir/monetdb5/extras/rapi' make[7]: Nothing to be done for `all-am'. make[7]: Leaving directory `/home/dennis/MonetDB-11.27.1/buildDir/monetdb5/extras/rapi' make[6]: Leaving directory `/home/dennis/MonetDB-11.27.1/buildDir/monetdb5/extras/rapi' make[6]: Entering directory `/home/dennis/MonetDB-11.27.1/buildDir/monetdb5/extras' make[6]: Nothing to be done for `all-am'. make[6]: Leaving directory `/home/dennis/MonetDB-11.27.1/buildDir/monetdb5/extras' make[5]: Leaving directory `/home/dennis/MonetDB-11.27.1/buildDir/monetdb5/extras' make[4]: Leaving directory `/home/dennis/MonetDB-11.27.1/buildDir/monetdb5/extras' make[4]: Entering directory `/home/dennis/MonetDB-11.27.1/buildDir/monetdb5' make[4]: Nothing to be done for `all-am'. make[4]: Leaving directory `/home/dennis/MonetDB-11.27.1/buildDir/monetdb5' make[3]: Leaving directory `/home/dennis/MonetDB-11.27.1/buildDir/monetdb5' make[2]: Leaving directory `/home/dennis/MonetDB-11.27.1/buildDir/monetdb5' Making all in sql make[2]: Entering directory `/home/dennis/MonetDB-11.27.1/buildDir/sql' make all-recursive make[3]: Entering directory `/home/dennis/MonetDB-11.27.1/buildDir/sql' Making all in include make[4]: Entering directory `/home/dennis/MonetDB-11.27.1/buildDir/sql/include' make all-am make[5]: Entering directory `/home/dennis/MonetDB-11.27.1/buildDir/sql/include' make[5]: Nothing to be done for `all-am'. make[5]: Leaving directory `/home/dennis/MonetDB-11.27.1/buildDir/sql/include' make[4]: Leaving directory `/home/dennis/MonetDB-11.27.1/buildDir/sql/include' Making all in common make[4]: Entering directory `/home/dennis/MonetDB-11.27.1/buildDir/sql/common' make all-am make[5]: Entering directory `/home/dennis/MonetDB-11.27.1/buildDir/sql/common' make[5]: Nothing to be done for `all-am'. make[5]: Leaving directory `/home/dennis/MonetDB-11.27.1/buildDir/sql/common' make[4]: Leaving directory `/home/dennis/MonetDB-11.27.1/buildDir/sql/common' Making all in storage make[4]: Entering directory `/home/dennis/MonetDB-11.27.1/buildDir/sql/storage' make all-recursive make[5]: Entering directory `/home/dennis/MonetDB-11.27.1/buildDir/sql/storage' Making all in bat make[6]: Entering directory `/home/dennis/MonetDB-11.27.1/buildDir/sql/storage/bat' make all-am make[7]: Entering directory `/home/dennis/MonetDB-11.27.1/buildDir/sql/storage/bat' make[7]: Nothing to be done for `all-am'. make[7]: Leaving directory `/home/dennis/MonetDB-11.27.1/buildDir/sql/storage/bat' make[6]: Leaving directory `/home/dennis/MonetDB-11.27.1/buildDir/sql/storage/bat' make[6]: Entering directory `/home/dennis/MonetDB-11.27.1/buildDir/sql/storage' make[6]: Nothing to be done for `all-am'. make[6]: Leaving directory `/home/dennis/MonetDB-11.27.1/buildDir/sql/storage' make[5]: Leaving directory `/home/dennis/MonetDB-11.27.1/buildDir/sql/storage' make[4]: Leaving directory `/home/dennis/MonetDB-11.27.1/buildDir/sql/storage' Making all in server make[4]: Entering directory `/home/dennis/MonetDB-11.27.1/buildDir/sql/server' make all-am make[5]: Entering directory `/home/dennis/MonetDB-11.27.1/buildDir/sql/server' make[5]: Nothing to be done for `all-am'. make[5]: Leaving directory `/home/dennis/MonetDB-11.27.1/buildDir/sql/server' make[4]: Leaving directory `/home/dennis/MonetDB-11.27.1/buildDir/sql/server' Making all in backends make[4]: Entering directory `/home/dennis/MonetDB-11.27.1/buildDir/sql/backends' make all-recursive make[5]: Entering directory `/home/dennis/MonetDB-11.27.1/buildDir/sql/backends' Making all in monet5 make[6]: Entering directory `/home/dennis/MonetDB-11.27.1/buildDir/sql/backends/monet5' make all-recursive make[7]: Entering directory `/home/dennis/MonetDB-11.27.1/buildDir/sql/backends/monet5' Making all in vaults make[8]: Entering directory `/home/dennis/MonetDB-11.27.1/buildDir/sql/backends/monet5/vaults' make all-recursive make[9]: Entering directory `/home/dennis/MonetDB-11.27.1/buildDir/sql/backends/monet5/vaults' make[10]: Entering directory `/home/dennis/MonetDB-11.27.1/buildDir/sql/backends/monet5/vaults' make[10]: Nothing to be done for `all-am'. make[10]: Leaving directory `/home/dennis/MonetDB-11.27.1/buildDir/sql/backends/monet5/vaults' make[9]: Leaving directory `/home/dennis/MonetDB-11.27.1/buildDir/sql/backends/monet5/vaults' make[8]: Leaving directory `/home/dennis/MonetDB-11.27.1/buildDir/sql/backends/monet5/vaults' Making all in UDF make[8]: Entering directory `/home/dennis/MonetDB-11.27.1/buildDir/sql/backends/monet5/UDF' make all-recursive make[9]: Entering directory `/home/dennis/MonetDB-11.27.1/buildDir/sql/backends/monet5/UDF' Making all in cudf make[10]: Entering directory `/home/dennis/MonetDB-11.27.1/buildDir/sql/backends/monet5/UDF/cudf' make all-am make[11]: Entering directory `/home/dennis/MonetDB-11.27.1/buildDir/sql/backends/monet5/UDF/cudf' make[11]: Nothing to be done for `all-am'. make[11]: Leaving directory `/home/dennis/MonetDB-11.27.1/buildDir/sql/backends/monet5/UDF/cudf' make[10]: Leaving directory `/home/dennis/MonetDB-11.27.1/buildDir/sql/backends/monet5/UDF/cudf' make[10]: Entering directory `/home/dennis/MonetDB-11.27.1/buildDir/sql/backends/monet5/UDF' make[10]: Nothing to be done for `all-am'. make[10]: Leaving directory `/home/dennis/MonetDB-11.27.1/buildDir/sql/backends/monet5/UDF' make[9]: Leaving directory `/home/dennis/MonetDB-11.27.1/buildDir/sql/backends/monet5/UDF' make[8]: Leaving directory `/home/dennis/MonetDB-11.27.1/buildDir/sql/backends/monet5/UDF' Making all in LSST make[8]: Entering directory `/home/dennis/MonetDB-11.27.1/buildDir/sql/backends/monet5/LSST' make all-am make[9]: Entering directory `/home/dennis/MonetDB-11.27.1/buildDir/sql/backends/monet5/LSST' make[9]: Nothing to be done for `all-am'. make[9]: Leaving directory `/home/dennis/MonetDB-11.27.1/buildDir/sql/backends/monet5/LSST' make[8]: Leaving directory `/home/dennis/MonetDB-11.27.1/buildDir/sql/backends/monet5/LSST' Making all in generator make[8]: Entering directory `/home/dennis/MonetDB-11.27.1/buildDir/sql/backends/monet5/generator' make all-am make[9]: Entering directory `/home/dennis/MonetDB-11.27.1/buildDir/sql/backends/monet5/generator' make[9]: Nothing to be done for `all-am'. make[9]: Leaving directory `/home/dennis/MonetDB-11.27.1/buildDir/sql/backends/monet5/generator' make[8]: Leaving directory `/home/dennis/MonetDB-11.27.1/buildDir/sql/backends/monet5/generator' make[8]: Entering directory `/home/dennis/MonetDB-11.27.1/buildDir/sql/backends/monet5' make[8]: Nothing to be done for `all-am'. make[8]: Leaving directory `/home/dennis/MonetDB-11.27.1/buildDir/sql/backends/monet5' make[7]: Leaving directory `/home/dennis/MonetDB-11.27.1/buildDir/sql/backends/monet5' make[6]: Leaving directory `/home/dennis/MonetDB-11.27.1/buildDir/sql/backends/monet5' make[6]: Entering directory `/home/dennis/MonetDB-11.27.1/buildDir/sql/backends' make[6]: Nothing to be done for `all-am'. make[6]: Leaving directory `/home/dennis/MonetDB-11.27.1/buildDir/sql/backends' make[5]: Leaving directory `/home/dennis/MonetDB-11.27.1/buildDir/sql/backends' make[4]: Leaving directory `/home/dennis/MonetDB-11.27.1/buildDir/sql/backends' Making all in scripts make[4]: Entering directory `/home/dennis/MonetDB-11.27.1/buildDir/sql/scripts' make all-am make[5]: Entering directory `/home/dennis/MonetDB-11.27.1/buildDir/sql/scripts' make[5]: Nothing to be done for `all-am'. make[5]: Leaving directory `/home/dennis/MonetDB-11.27.1/buildDir/sql/scripts' make[4]: Leaving directory `/home/dennis/MonetDB-11.27.1/buildDir/sql/scripts' make[4]: Entering directory `/home/dennis/MonetDB-11.27.1/buildDir/sql' make[4]: Nothing to be done for `all-am'. make[4]: Leaving directory `/home/dennis/MonetDB-11.27.1/buildDir/sql' make[3]: Leaving directory `/home/dennis/MonetDB-11.27.1/buildDir/sql' make[2]: Leaving directory `/home/dennis/MonetDB-11.27.1/buildDir/sql' Making all in tools make[2]: Entering directory `/home/dennis/MonetDB-11.27.1/buildDir/tools' make all-recursive make[3]: Entering directory `/home/dennis/MonetDB-11.27.1/buildDir/tools' Making all in mserver make[4]: Entering directory `/home/dennis/MonetDB-11.27.1/buildDir/tools/mserver' make all-am make[5]: Entering directory `/home/dennis/MonetDB-11.27.1/buildDir/tools/mserver' make[5]: Nothing to be done for `all-am'. make[5]: Leaving directory `/home/dennis/MonetDB-11.27.1/buildDir/tools/mserver' make[4]: Leaving directory `/home/dennis/MonetDB-11.27.1/buildDir/tools/mserver' Making all in merovingian make[4]: Entering directory `/home/dennis/MonetDB-11.27.1/buildDir/tools/merovingian' make all-recursive make[5]: Entering directory `/home/dennis/MonetDB-11.27.1/buildDir/tools/merovingian' Making all in utils make[6]: Entering directory `/home/dennis/MonetDB-11.27.1/buildDir/tools/merovingian/utils' make all-am make[7]: Entering directory `/home/dennis/MonetDB-11.27.1/buildDir/tools/merovingian/utils' make[7]: Nothing to be done for `all-am'. make[7]: Leaving directory `/home/dennis/MonetDB-11.27.1/buildDir/tools/merovingian/utils' make[6]: Leaving directory `/home/dennis/MonetDB-11.27.1/buildDir/tools/merovingian/utils' Making all in client make[6]: Entering directory `/home/dennis/MonetDB-11.27.1/buildDir/tools/merovingian/client' make all-am make[7]: Entering directory `/home/dennis/MonetDB-11.27.1/buildDir/tools/merovingian/client' make[7]: Nothing to be done for `all-am'. make[7]: Leaving directory `/home/dennis/MonetDB-11.27.1/buildDir/tools/merovingian/client' make[6]: Leaving directory `/home/dennis/MonetDB-11.27.1/buildDir/tools/merovingian/client' Making all in daemon make[6]: Entering directory `/home/dennis/MonetDB-11.27.1/buildDir/tools/merovingian/daemon' make all-am make[7]: Entering directory `/home/dennis/MonetDB-11.27.1/buildDir/tools/merovingian/daemon' /bin/bash ../../../libtool --tag=CC --mode=link gcc -g -O2 -export-dynamic -o monetdbd merovingian.o argvcmds.o client.o connections.o controlrunner.o discoveryrunner.o forkmserver.o handlers.o multiplex-funnel.o proxy.o ../utils/libmeroutil.la ../../../common/utils/libmutils.la ../../../monetdb5/tools/libmonetdb5.la -lssl -lcrypto -pthread libtool: link: gcc -g -O2 -o .libs/monetdbd merovingian.o argvcmds.o client.o connections.o controlrunner.o discoveryrunner.o forkmserver.o handlers.o multiplex-funnel.o proxy.o -pthread -Wl,--export-dynamic ../utils/.libs/libmeroutil.a ../../../common/utils/.libs/libmutils.a ../../../monetdb5/tools/.libs/libmonetdb5.so -lssl -lcrypto -pthread /usr/bin/ld: controlrunner.o: undefined reference to symbol 'GDKinit' //home/dennis/MonetDB-11.27.1/buildDir/gdk/.libs/libbat.so.15: error adding symbols: DSO missing from command line collect2: error: ld returned 1 exit status make[7]: *** [monetdbd] Error 1 make[7]: Leaving directory `/home/dennis/MonetDB-11.27.1/buildDir/tools/merovingian/daemon' make[6]: *** [all] Error 2 make[6]: Leaving directory `/home/dennis/MonetDB-11.27.1/buildDir/tools/merovingian/daemon' make[5]: *** [all-recursive] Error 1 make[5]: Leaving directory `/home/dennis/MonetDB-11.27.1/buildDir/tools/merovingian' make[4]: *** [all] Error 2 make[4]: Leaving directory `/home/dennis/MonetDB-11.27.1/buildDir/tools/merovingian' make[3]: *** [all-recursive] Error 1 make[3]: Leaving directory `/home/dennis/MonetDB-11.27.1/buildDir/tools' make[2]: *** [all] Error 2 make[2]: Leaving directory `/home/dennis/MonetDB-11.27.1/buildDir/tools' make[1]: *** [all-recursive] Error 1 make[1]: Leaving directory `/home/dennis/MonetDB-11.27.1/buildDir' make: *** [all] Error 2

2 1

C (or Java) API to insert blob via prep statement
by Anton Kravchenko 26 Jul '17

26 Jul '17

Is blob insertion (via prepared statement) supported by C (or Java) MonetDB API as of now?

1 0

COPY INTO doesn't recognise sorted varchar column
by Sridhar Eaturu 25 Jul '17

25 Jul '17

Hi, I imported some csv data using COPY INTO to a table. The data is sorted by the first column "sym", which is of VARCHAR type. Upon importing, the COPY INTO is not recognising the sortedness of the "sym" column. On the other hand, another test I ran with a sorted DATE column gets marked as sorted as expected. I'm using MonetDB v11.25.23 (Dec2016-SP5), and the steps demonstrating this are below. Is this expected? sql>CREATE TABLE tempquote (sym VARCHAR(3),time0 TIME,cond1 VARCHAR(1),bid INTEGER,bidqty INTEGER,ask INTEGER,askqty INTEGER,cond2 VARCHAR(1)); operation successful (2.430ms) sql>COPY INTO tempquote from 'tempquote.csv' USING DELIMITERS ',','\n' NULL AS ''; 5 affected rows (31.972ms) sql>select * from tempquote; +------+----------+-------+------+--------+------+--------+-------+ | sym | time0 | cond1 | bid | bidqty | ask | askqty | cond2 | +======+==========+=======+======+========+======+========+=======+ | ABY | 09:30:03 | F | 1489 | 50 | 1872 | 44 | I | | ACG | 09:30:00 | M | 1419 | 27 | 1542 | 43 | A | | AHK | 09:30:00 | G | 1094 | 74 | 1808 | 56 | F | | AXN | 09:30:01 | I | 1022 | 26 | 1541 | 88 | E | | BEV | 09:30:00 | L | 1377 | 38 | 1353 | 77 | M | +------+----------+-------+------+--------+------+--------+-------+ 5 tuples (1.131ms) sql>select table,column,sorted from storage() where "table"='tempquote'; +-----------+--------+--------+ | table | column | sorted | +===========+========+========+ | tempquote | sym | null | | tempquote | time0 | null | | tempquote | cond1 | null | | tempquote | bid | null | | tempquote | bidqty | null | | tempquote | ask | null | | tempquote | askqty | null | | tempquote | cond2 | null | +-----------+--------+--------+ 8 tuples (5.090ms) sql> Thanks, Sridhar

1 0

Is there any parameter to release memory? I see memory bloats on high concurrency and continuous runs
by Sharma, Sreejith 25 Jul '17

25 Jul '17

Hi, Is there any parameter or any options available for MonetDB to release the memory consumed? As one of the application I tested would run on high concurrency and may run SQL overnight. In such cases I see a memory bloat which may come up to 200gb and will never come down. Please advise? Regards, Sreejith

2 3

Troubles with the MonetDB
by 倪令斌 25 Jul '17

25 Jul '17

Dear Sir/Madam， I have encountered a problem that it has troubled me for several days and I can not continue the next job.I found it online for a long time, but I did not find a solution.Hope you can give me some inspiration: what's the matter with my PC? PS:The OS is Ubuntu16.04. Thanks a lot! Cheers, Lingbin

2 2

Copy into with DEFAULT values
by Ariel Abadi 24 Jul '17

24 Jul '17

Hi Im trying to upload a FILE into one table. The FILE has all the fields of the table, except for the last field which is the *updated *time. For that reason I have created the table with the field: "nrm_now" TIMESTAMP(0) DEFAULT current_timestamp() BUT when im trying to insert the FILE (COY INTO test_20170711 from FILE .... ), there is an error: Failed to import table 'test_20170711', Column value 143 missing Does anybody can help me? Am I doing something wrong? Which should be the best way to implement this? Thnks in advance Ariel

2 2

Monetdb Installation
by Amit Ambekar 24 Jul '17

24 Jul '17

Hi, July 2017 release is not working good . I am getting error while login using terminal in Ubuntu OS. When new release will be done ??

2 1

Bug on timestamp ?
by Ariel Abadi 21 Jul '17

21 Jul '17

Hi! May be someone can help me, Im facing the following problem. Im trying to pull some information from the field nrm_connect_time, I tried almost all the date/timestamp functions, but all of them failed. Finally, I decided to CAST as STRING, and see what happends... If I do a SELECT DISTINCT .. then I have the correct information, if I dont include the DISTINCT what I see has nonsense... sql>SELECT nrm_connect_time, LEFT(CAST(nrm_connect_time AS STRING),10) FROM NRM_20170609 LIMIT 5; +----------------------+-----------------------+ | nrm_connect_time | left_nrm_connect_time | +======================+=======================+ | 2017-06-09 00:05:00 | -1-01-01 0 | | 2017-06-09 00:05:00 | -1-01-01 0 | | 2017-06-09 00:05:01 | -1-01-01 0 | | 2017-06-09 00:05:03 | -1-01-01 0 | | 2017-06-09 00:05:03 | -1-01-01 0 | +----------------------+-----------------------+ 5 tuples (29.802ms) sql>*SELECT DISTINCT* nrm_connect_time, LEFT(CAST(nrm_connect_time AS STRING),10) FROM NRM_20170609 LIMIT 5; +----------------------+------------+ | nrm_connect_time | L2 | +======================+============+ | 2017-06-09 00:05:00 | 2017-06-09 | | 2017-06-09 00:05:01 | 2017-06-09 | | 2017-06-09 00:05:03 | 2017-06-09 | | 2017-06-09 00:05:04 | 2017-06-09 | | 2017-06-09 00:05:05 | 2017-06-09 | +----------------------+------------+ 5 tuples (424.306ms) *The "nrm_connect_time" is defined as TIMESTAMP(0).* Let me show you a differente example: sql>*SELECT DISTINCT *nrm_connect_time, LEFT(CAST(nrm_connect_time AS STRING),10), timestamp_to_str(nrm_connect_time, '%Y') FROM NRM_20170609 LIMIT 5; +----------------------+------------+------+ | nrm_connect_time | L2 | L3 | +======================+============+======+ | 2017-06-09 00:05:00 | 2017-06-09 | 2017 | | 2017-06-09 00:05:01 | 2017-06-09 | 2017 | | 2017-06-09 00:05:03 | 2017-06-09 | 2017 | | 2017-06-09 00:05:04 | 2017-06-09 | 2017 | | 2017-06-09 00:05:05 | 2017-06-09 | 2017 | +----------------------+------------+------+ 5 tuples (707.217ms) sql>*SELECT *nrm_connect_time, LEFT(CAST(nrm_connect_time AS STRING),10), timestamp_to_str(nrm_connect_time, '%Y') FROM NRM_20170609 LIMIT 5; TypeException:user.s16_55[123]:'mtime.timestamp_to_str' undefined in: mtime.timestamp_to_str(X_196:oid,A1:str); program contains errors Regards Ariel

3 3

Re: Re: Add entry in a table only if that entry does not already exist
by Stefano Piani 21 Jul '17

21 Jul '17

Hello, sorry for my late replay, I have been really busy in the last few days. > Hello Stefano, > > Which MonetDB version do you use? On which OS? Sorry, I forgot to mention this. I am using ubuntu 16.04 and I am running MonetDB inside a Docker container (which also runs ubuntu). >> On 4 Jul 2017, at 16:32, Stefano Piani <stefano.piani(a)exact-lab.it> wrote: >> >> Dear monetdb users, >> >> I started to use MonetDB a few weeks ago and, until now, I have been >> able to use it proficiently. Unfortunately, I have faced a small issue >> that I have not been able to figure out how to fix. >> >> I collect data generated by different users. A unique id is associated >> to each user. I have a specific table (called "user") where I save the >> name and some other information about the user. In this table, the ID >> is a primary key. >> When I collect the data, I want to check if the specific user who >> generated that data is already saved in the user table. If this is not >> the case, >> I want to add a row to the user table for this user. >> Unfortunately, I have not been able to accomplish this operation in >> one SQL query and, therefore, I have been forced to query for the user >> id and, >> if the answer is negative, add the user using another query. The >> following is the python script that perform the operations. >> >> def save_user_in_database(user, cursor): >> db_line = 'SELECT 1 FROM users WHERE user_id = {}'.format(user.id) >> LOGGER.info('Executing on DB: %s', db_line) >> cursor.execute(db_line) >> >> answer = cursor.fetchall() >> LOGGER.info('Get this answer %s', answer) >> if len(answer) == 0: >> LOGGER.info('Inserting new user') >> >> db_line = sql_cmd_from_template( >> USER_TABLE_INSERT_TEMPLATE, >> user.id, >> user.name, >> user.description, >> user.location >> ) >> LOGGER.info('Executing on DB: %s', db_line) >> cursor.execute(db_line) > >Do you execute the queries in a different schema than the default ?sys? schema (which is recommended for database users)? >Because ?users? is a special VIEW in the ?sys? schema. > Yes, I should be in a different schema. In any case, I changed the name of the table just to be on the safe way. >> >> This function is executed inside a single threaded application. >> Moreover, there are no "DELETE" or similar operations inside this >> script (it only adds rows to the database). >> No user (human or software) but this script connects to my MonetDB instance. >> >> Unfortunately, after I while that the script is running (let's say one >> or two days), something unexpected happens. For example, this is the >> log generated for user 42847029 >> (there are a lot of other lines in between that I removed). As you can >> see, after a while, my check fails and I don't understand why. >> >> 2017/06/13 09:30:01 - INFO - save_user_in_database: Executing on DB: >> SELECT 1 FROM users WHERE user_id = 42847029 >> 2017/06/13 09:30:01 - INFO - save_user_in_database: Get this answer [(1,)] >> >> 2017/06/13 10:00:04 - INFO - save_user_in_database: Executing on DB: >> SELECT 1 FROM users WHERE user_id = 42847029 >> 2017/06/13 10:00:04 - INFO - save_user_in_database: Get this answer [(1,)] >> >> 2017/06/13 10:02:33 - INFO - save_user_in_database: Executing on DB: >> SELECT 1 FROM users WHERE user_id = 42847029 >> 2017/06/13 10:02:33 - INFO - save_user_in_database: Get this answer [(1,)] >> >> 2017/06/13 10:30:36 - INFO - save_user_in_database: Executing on DB: >> SELECT 1 FROM users WHERE user_id = 42847029 >> 2017/06/13 10:30:36 - INFO - save_user_in_database: Get this answer [] >> 2017/06/13 10:30:36 - INFO - save_user_in_database: Inserting new user > >This shouldn?t happen, as is shown by the error below that a user_id of 42847029 still exists; also, as you mentioned, there hasn?t been any delete queries. > >This might be a bug in MonetDB. > >Is this problem repeatable? If yes, can you give us a sequence of queries to reproduce this problem. We?d like to look into it. > I will try to reproduce the problem with some scripts as soon as possible. Unfortunately, I am quite busy in this period and therefore this will require some time. In any case, this happens only when the system is busy and the table is big enough. I think that the key to reproduce the problem is to stress the database with several operations per second. In this situation, I have noticed that the db starts to use a lot of RAM (about 15 GB for a database that occupies 3 GB on the disk). At that point, the db becomes extremely slow (for example, for a query that usually takes less than 10 ms I have to wait for more than 2 seconds) and the unique key problem start to appear. > >> 2017/06/13 10:30:36 - INFO - save_user_in_database: Executing on DB: >> INSERT INTO users(user_id, user_name, user_description, user_location) >> VALUES (42847029, 'XXXX', 'XXXX', 'XXXX') >> 2017/06/13 10:30:36 - WARNING - on_data: Error elaborating data >> Traceback (most recent call last): >> File "data_collector.py", line 267, in on_data >> return self.elaborate_data(data) >> File "data_collector.py", line 354, in elaborate_data >> save_user_in_database(user, cursor) >> File "data_collector.py", line 234, in save_user_in_database >> cursor.execute(db_line) >> File "/opt/Python-3.6.0/lib/python3.6/site-packages/pymonetdb-1. 0.4-py3.6.egg/pymonetdb/sql/cursors.py", >> line 164, in execute >> block = self.connection.execute(query) >> File "/opt/Python-3.6.0/lib/python3.6/site-packages/pymonetdb-1. 0.4-py3.6.egg/pymonetdb/sql/connections.py", >> line 133, in execute >> return self.command('s' + query + ';') >> File "/opt/Python-3.6.0/lib/python3.6/site-packages/pymonetdb-1. 0.4-py3.6.egg/pymonetdb/sql/connections.py", >> line 138, in command >> return self.mapi.cmd(command) >> File "/opt/Python-3.6.0/lib/python3.6/site-packages/pymonetdb-1. 0.4-py3.6.egg/pymonetdb/mapi.py", >> line 235, in cmd >> raise exception(string) >> pymonetdb.exceptions.IntegrityError: INSERT INTO: PRIMARY KEY >> constraint 'users.users_user_id_pkey' violated >> >> Have you any advice? Is there a way to perform the same check in just >> one SQL line? Is this way atomic (if a would ever move to a >> multithreading script)? > >It is not atomic, but you can make it atomic by wrapping both queries into one transaction. > >> Am I doing something >> wrong with my current code? Please, take into account that I do not >> have a deep experience with other SQL tools so it is perfectly >> possible that I miss something >> that for you is extremely obvious. > >No, you?re not doing anything wrong. But there are better alternatives: > >1. If this is a standalone action (i.e. not part of a transaction), the check for existing user_id is not necessary. The simplest and most efficient way is to always do the insert and ignore the ?primary key constraint ? violated? >error (e.g. use a try-catch block). > >2. If this insert is going to be part of a (big, long) transaction, then you won?t want the insert to fail. In that case, you can use a stored procedure: > >create procedure insert_new (id int, name varchar(10), description varchar(10), location varchar(10)) >begin > declare cnt int; > set cnt = (select count(*) from myusers where user_id=id); > > if (cnt = 0 ) > then > insert into myusers values (id, name, description, location); > end if; >end; > >Then call the procedure: call insert_new (42847029, 'XXXX', 'XXXX', 'XXXX?) > >I hope this helps. > II tried the solution number 2, but unfortunately the problem is still there. I get an Integrity error even calling the procedure that you described before. What is really strange is that, after a while, execute the procedure that you have showed me requires more than 3 seconds on my system. If I restart the monetdb instance, it takes a few milliseconds again. I think that maybe there is something broken in my setup. It could be that I have misconfigured something or I have a problem with my machine. Unfortunately, taking also into account that I am using Docker (which adds a layer of complexity), there are plenty of things that could not work as expected. Thank you for your help! >Regards, >Jennie > >> >> Thank you in advance! >> >> Best regards, >> >> Stefano Piani

2 1

MonetDB :: Memory Blots Up On High Concurrency
by Sharma, Sreejith 21 Jul '17

21 Jul '17

Hi, Does anyone encountered memory blot issue on high concurrency? Also, I don't see it coming down when idle. When its blots up, it's slows down the response time. Regards, Sreejith

3 6

Monetdb Installation
by amit＠g-square.in 21 Jul '17

21 Jul '17

Hi, how to install monetdb's old version June 2017 for ubuntu ??

2 1

Error trying to login
by amit＠g-square.in 20 Jul '17

20 Jul '17

Hi, I am using Ubuntu OS. I have created dbpharm and database. Now i am trying to login mclient using terminal but it gives following error. Please reply asap. root@mp-VirtualBox:/var/lib/monetdb# mclient -u monetdb -d narrator password: TypeException:user.sql.init[5]:'sqlcatalog.create_function' undefined in: sqlcatalog.create_function(X_1:str, X_3:str, X_5:ptr); MALException:optimizer.MALoptimizer:Start with inconsistent MAL plan

2 1

Re: how to know imprints take effect?
by integrity 20 Jul '17

20 Jul '17

Hi Stefan, Nice talking to you again! Sorry my query in the image didn't show up, so I paste it in text now. The query is a simple select with equals. ======================================== sql>select * from id14 where id=1461; +------+-------+------+ | id | label | cid | +======+=======+======+ | 1461 | false | 0 | +------+-------+------+ 1 tuple (104.549ms) sql> sql> sql>select row_number() over (),* from storage() where "table"='id14'; +------+--------+-------+--------+---------+----------+----------+---------+-----------+------------+----------+----------+-------+----------+--------+-----------+--------+----------+------+ | L5 | schema | table | column | type | mode | location | count | typewidth | columnsize | heapsize | hashes | phash | imprints | sorted | revsorted | unique | orderidx | L5 | +======+========+=======+========+=========+==========+==========+=========+===========+============+==========+==========+=======+==========+========+===========+========+==========+======+ | 1 | sys | id14 | id | bigint | writable | 12/1244 | 2811606 | 8 | 22492848 | 0 | 28049448 | false | 0 | false | false | null | 0 | 1 | | 2 | sys | id14 | label | boolean | writable | 02/254 | 2811606 | 1 | 2811606 | 0 | 0 | false | 0 | null | null | null | 0 | 2 | | 3 | sys | id14 | cid | bigint | writable | 12/1231 | 2811606 | 8 | 22492848 | 0 | 0 | false | 0 | false | false | null | 0 | 3 | +------+--------+-------+--------+---------+----------+----------+---------+-----------+------------+----------+----------+-------+----------+--------+-----------+--------+----------+------+ 3 tuples (10.090ms) ===================================================== Best, Meng ------------------ Original ------------------ From: "Stefan Manegold";<Stefan.Manegold(a)cwi.nl>; Date: Jul 19, 2017 To: "Communication channel for MonetDB users"<users-list(a)monetdb.org>; Subject: Re: how to know imprints take effect? Hi Meng, most important question: From what kind of queries do you expect millisecond responses over a table of 15,000,000 rows? Thanks! Best, Stefan ----- On Jul 19, 2017, at 12:37 PM, integrity 357416268(a)qq.com wrote: > Hi all, > > I am back, and I am using MonetDB Dec2016-SP5 with "enable-optimize" to do graph > computing. I remember that imprints are suitable for all numeric types, > however, after querying on the bigint column for many times, I found there is > no imprints. > > > > What I want to achieve is to "make" MonetDB respond in milliseconds, although I > know that in MonetDB it is not necessary to create index like imprints. > So, on a table of 15,000,000 rows, can I expect to get response in milliseconds? > and whether imprints will help with this? > > Any comments is appreciated! > > Best regards, > Meng > > _______________________________________________ > 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

2 1

how to know imprints take effect?
by integrity 19 Jul '17

19 Jul '17

Hi all, I am back, and I am using MonetDB Dec2016-SP5 with "enable-optimize" to do graph computing. I remember that imprints are suitable for all numeric types, however, after querying on the bigint column for many times, I found there is no imprints. What I want to achieve is to "make" MonetDB respond in milliseconds, although I know that in MonetDB it is not necessary to create index like imprints. So, on a table of 15,000,000 rows, can I expect to get response in milliseconds? and whether imprints will help with this? Any comments is appreciated! Best regards, Meng

2 1

Announcement: New Jul2017 Feature release of MonetDB suite
by Sjoerd Mullender 18 Jul '17

18 Jul '17

The MonetDB team at CWI/MonetDB BV is pleased to announce the Jul2017 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 2017 feature release MonetDB5 Server * The "sub" prefix of many functions, both at the MAL and the C level, has been removed. * Changed the interfaces of the AUTH* functions: pass values, not pointers to values. * Removed calc.setoid(). * group.subgroup is now called group.group if it is not refining a group. Both group.group and group.subgroup now also have variants with a candidate list. * The allocation schemes for MAL blocks and Variables has been turned into block-based. This reduces the number of malloc()/free() calls. . * Added a new server-side protocol implementation. The new protocol is backwards compatible with the old protocol. Clients can choose whether they want to use the old or the new protocol during the initial handshake with the server. The new protocol is a binary column-based protocol that is significantly faster than the old protocol when transferring large result sets. In addition, the new protocol supports compression using Snappy or LZ4. * Moved the sphinx extension module to its own repository. See https://dev.monetdb.org/hg/MonetDB-sphinx/. * Removed GSL module: it's now a separate (extension) package. See https://dev.monetdb.org/hg/MonetDB-gsl/. * The PCRE library is now optional for systems that support POSIX regular expressions. * Added 5 new sys schema tables: function_languages, function_types, key_types, index_types and privilege_codes. They are pre-loaded with static content and contain descriptive names for the various integer type and code values. See also sql/scripts/51_sys_schema_extension.sql Merovingian * Added handling of a dbextra property per database at the daemon level. The user can set the dbextra property for a database using the command: $ monetdb set dbextra=<path> <database> and the daemon will make sure to start the new server using the correct --dbextra parameter. Client Package * The mclient and msqldump programs lost compatibility with old mserver5 versions (pre 2014) which didn't have a "system" column in the sys.schemas table. * The mclient and msqldump programs lost compatibility with ancient mserver5 versions (pre 2011) which didn't have the sys.systemfunctions table. * Removed the "array" and "quick" functions from the mapi library. To be precise, the removed functions are: mapi_execute_array, mapi_fetch_field_array, mapi_prepare_array, mapi_query_array, mapi_quick_query, mapi_quick_query_array, and mapi_quick_response. * Added a more elaborate \help command for SQL expressions. MonetDB Common * Improved error checking in the logger code (dealing with the write-ahead log); changed return types a several functions from int to gdk_return (i.e., they now return GDK_SUCCEED or GDK_FAIL). The logger no longer calls GDKfatal on error. Instead the caller is responsible for dealing with errors. * BATsort may now create an order index as a by product. * Quantile calculations now use the order index if available (and use BATsort otherwise, producing an order index). * Quantiles calculate a position in the sorted column. If this position is not an integer, we now choose the nearest position, favoring the lower if the distance to the two adjacent positions is equal (round down to nearest integer). * Removed function BATprintf. Use BATprint or BATprintcolumns instead. * Removed BATsave from the list of exported functions. * Replaced BBPincref/BBPdecref with BBPfix/BBPunfix for physical reference count and BBPretain/BBPrelease for logical reference count maintenance. * Removed automatic conversion of 32-bit OIDs to 64 bits on 64-bit architectures. * Removed functions OIDbase() and OIDnew(). * Removed talign field from BAT descriptor. * BATappend now takes an optional (NULL if not used) candidate list for the to-be-appended BAT. * New function BATkeyed(BAT *b) that determines (possibly using a hash table) whether all values in b are distinct. SQL * Made the operator precedence of % equal to those of * and /. All three are evaluated from left to right. * Removed table sys.connections. It was a remnant of an experimental change that had already been removed in 2012. * Protect against runaway profiler events If you hit a barrier block during profiling, the JSON event log may quickly become unwieldy. Event production is protected using a high water mark, which ensures that never within the single execution of MAL block the instruction causes excessive event records. Bug Fixes * 3465: Request: add support for CREATE VIEW with ORDER BY clause * 3545: monetdb commands don't work with -h -P -p options (locally and remotely) * 3996: select * from sys.connections always returns 0 rows. Expected to see at least one row for the active connection. * 6187: Nested WITH queries not supported * 6225: Order of evaluation of the modulo operator * 6289: Crashes and hangs with remote tables * 6292: Runaway SQL optimizer in too many nested operators * 6310: Name resolution error (sqlsmith) * 6312: Object not found in LIMIT clause (sqlsmith) * 6313: Null type resolution in disjunction fails (sqlsmith) * 6319: Server crash on LATERAL (sqlsmith) * 6322: Crash on disjunction with LIMIT (sqlsmith) * 6323: Deadlock calling sys.bbp() * 6324: Sqlitelogictest crash in a IN query (8th) * 6327: The daemon does not respect the actual name of the mserver5 executable * 6330: Sqlitelogictest crash on a complex SELECT query * 6331: sys.statistics column "nils" always contains 0. Expected a positive value for columns that have one or more nils/NULLs * 6332: Sqlitelogictest crash related to an undefined MAL function

3 3

possible to change the decimal character from "." to ", " for the COPY INTO command?
by Anthony Damico 18 Jul '17

18 Jul '17

hi, i'm curious if this is possible to implement? not seeing it in the docs.. for example, in R you can use read.csv() which uses , as the delimiter and . as the decimal or you can use read.csv2 which uses ; as the delimiter and , as the decimal thanks all

1 0

Add entry in a table only if that entry does not already exist
by Stefano Piani 14 Jul '17

14 Jul '17

Dear monetdb users, I started to use MonetDB a few weeks ago and, until now, I have been able to use it proficiently. Unfortunately, I have faced a small issue that I have not been able to figure out how to fix. I collect data generated by different users. A unique id is associated to each user. I have a specific table (called "user") where I save the name and some other information about the user. In this table, the ID is a primary key. When I collect the data, I want to check if the specific user who generated that data is already saved in the user table. If this is not the case, I want to add a row to the user table for this user. Unfortunately, I have not been able to accomplish this operation in one SQL query and, therefore, I have been forced to query for the user id and, if the answer is negative, add the user using another query. The following is the python script that perform the operations. def save_user_in_database(user, cursor): db_line = 'SELECT 1 FROM users WHERE user_id = {}'.format(user.id) LOGGER.info('Executing on DB: %s', db_line) cursor.execute(db_line) answer = cursor.fetchall() LOGGER.info('Get this answer %s', answer) if len(answer) == 0: LOGGER.info('Inserting new user') db_line = sql_cmd_from_template( USER_TABLE_INSERT_TEMPLATE, user.id, user.name, user.description, user.location ) LOGGER.info('Executing on DB: %s', db_line) cursor.execute(db_line) This function is executed inside a single threaded application. Moreover, there are no "DELETE" or similar operations inside this script (it only adds rows to the database). No user (human or software) but this script connects to my MonetDB instance. Unfortunately, after I while that the script is running (let's say one or two days), something unexpected happens. For example, this is the log generated for user 42847029 (there are a lot of other lines in between that I removed). As you can see, after a while, my check fails and I don't understand why. 2017/06/13 09:30:01 - INFO - save_user_in_database: Executing on DB: SELECT 1 FROM users WHERE user_id = 42847029 2017/06/13 09:30:01 - INFO - save_user_in_database: Get this answer [(1,)] 2017/06/13 10:00:04 - INFO - save_user_in_database: Executing on DB: SELECT 1 FROM users WHERE user_id = 42847029 2017/06/13 10:00:04 - INFO - save_user_in_database: Get this answer [(1,)] 2017/06/13 10:02:33 - INFO - save_user_in_database: Executing on DB: SELECT 1 FROM users WHERE user_id = 42847029 2017/06/13 10:02:33 - INFO - save_user_in_database: Get this answer [(1,)] 2017/06/13 10:30:36 - INFO - save_user_in_database: Executing on DB: SELECT 1 FROM users WHERE user_id = 42847029 2017/06/13 10:30:36 - INFO - save_user_in_database: Get this answer [] 2017/06/13 10:30:36 - INFO - save_user_in_database: Inserting new user 2017/06/13 10:30:36 - INFO - save_user_in_database: Executing on DB: INSERT INTO users(user_id, user_name, user_description, user_location) VALUES (42847029, 'XXXX', 'XXXX', 'XXXX') 2017/06/13 10:30:36 - WARNING - on_data: Error elaborating data Traceback (most recent call last): File "data_collector.py", line 267, in on_data return self.elaborate_data(data) File "data_collector.py", line 354, in elaborate_data save_user_in_database(user, cursor) File "data_collector.py", line 234, in save_user_in_database cursor.execute(db_line) File "/opt/Python-3.6.0/lib/python3.6/site-packages/pymonetdb-1.0.4-py3.6.egg/pymonetdb/sql/cursors.py", line 164, in execute block = self.connection.execute(query) File "/opt/Python-3.6.0/lib/python3.6/site-packages/pymonetdb-1.0.4-py3.6.egg/pymonetdb/sql/connections.py", line 133, in execute return self.command('s' + query + ';') File "/opt/Python-3.6.0/lib/python3.6/site-packages/pymonetdb-1.0.4-py3.6.egg/pymonetdb/sql/connections.py", line 138, in command return self.mapi.cmd(command) File "/opt/Python-3.6.0/lib/python3.6/site-packages/pymonetdb-1.0.4-py3.6.egg/pymonetdb/mapi.py", line 235, in cmd raise exception(string) pymonetdb.exceptions.IntegrityError: INSERT INTO: PRIMARY KEY constraint 'users.users_user_id_pkey' violated Have you any advice? Is there a way to perform the same check in just one SQL line? Is this way atomic (if a would ever move to a multithreading script)? Am I doing something wrong with my current code? Please, take into account that I do not have a deep experience with other SQL tools so it is perfectly possible that I miss something that for you is extremely obvious. Thank you in advance! Best regards, Stefano Piani

2 3

Drop a local temporary table within a function
by Luis Larios 14 Jul '17

14 Jul '17

Hi, all, Consider the following scenario. I have a bunch of user defined functions, f_i, that create, populate, and use a local temporary table with the same name "res": create function f_i() returns table( ... ) begin # Result stats table. create local temporary table res( param1 int, param2 int ); /* Populate res */ /* Use res in some queries */ return table( /* Query involving res */ ); end; My first question is: Does "create local temporary table x" create a table that is disposed/dropped as soon as the function where it was declared finishes execution? I assumed this was the case, but I'm now skeptical as we are running into all sorts of concurrency issues that point to the "res" table not being cleared out properly from memory. This takes me to the second question: How do I drop a temporary table if such table exists? The reason for this is to prevent "table create" exceptions (which we are currently running into). I'd like to achieve something along these lines: create function f_i() returns table( ... ) begin # Drop local temporary table if such table exists. drop local temporary table res; <-------- How do I do this in MonetDB? # Result stats table. create local temporary table res( param1 int, param2 int ); /* Populate res */ /* Use res in some queries */ return table( /* Query involving res */ ); end; Where, as you can see, I want to explicitly drop the local temporary table "res" if such exists. If I leave the "local temporary" markers in the drop statement, MonetDB complains that it found unexpected "LOCAL" or "TEMPORARY". I wouldn't like to remove these markers because a statement like: drop table res; will drop any table in the default function schema with that name. Any help and hints are well appreciated! Thanks for the help in advance ~ Luis Angel The information transmitted, including any attachments, is intended only for the individual or entity to which it is addressed, and may contain confidential and/or privileged information. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by individuals or entities other than the intended recipient is prohibited, and all liability arising therefrom is disclaimed. If you have received this communication in error, please delete the information from any computer and notify the sender.

3 2

Jul2015-SP4 vs. Dec2016-SP5
by Anderson, David B 13 Jul '17

13 Jul '17

All, I know I've skipped multiple releases so this is a difficult question to answer, but should I expect the Dec2016-SP5 version of MonetDB to be faster than/equal to the Jul2015-SP4 version? I have a parallel set of servers (on the same box, 24 cores 192GB ram, listening to different ports) operating on identical data sets, and the Dec2016 version is slower than the Jul2015 version (~15-20%). My queries are basic joins between two tables with grouping. I have top running on the same box, and I can see that the Jul2015 server uses all 24 cores, but the Dec2016 server barely cracks 2-3. The MAL plans obtained by explain are totally different (which partly explains the cores being utilized because I can see blocks of 24 commands) and the length of the Dec2016 one is a fraction of the Jul2015 version. I guess the older version was inefficiently using all 24 cores, but the new version efficiently uses a smaller set of cores?? Thanks, Dave

2 2

script-friendly prepare statement
by Roberto Cornacchia 13 Jul '17

13 Jul '17

Hi there, I'm trying to script the execution of (generated) prepared queries. It does not look possible, because the identifier to use for the EXEC statement is returned dynamically by the PREPARE statement: sql>prepare select 1; execute prepared statement using: EXEC 73(...) This can obviously not be scripted. If I tried to parse the output of the prepare statement to retrieve the identifier, this would split PREPARE and EXEC in two different mclient sessions, losing the prepared statement. As far as I know, there is at the moment no syntax available for providing the identifier, something like: sql>prepare myid as select 1; (that wouldn't be a bad idea, would it?) Besides syntax extensions, I was wondering if there is any trick I could use at runtime with the current syntax to retrieve that identifier. Like, is it stored in some system table? Is it available as any undocumented variable? I could not find anything like that, but I might have missed it. Cheers, Roberto

3 4

adding an "insert into" completely changes output from explain
by Anderson, David B 13 Jul '17

13 Jul '17

All, Is it expected behavior if adding "insert into xxxx" at the beginning of a sql query completely alters the output of explain? Without the "insert into", explain returns 1490 tuples, but when I add it, explain only returns 88. I was expecting that the number would go up, i.e. that the original plan would be wrapped in some additional commands which would implement the "insert into" part. Here is my sql: explain insert into aggr select 'CHANNEL', s.product, s.coupon, s.productionyear, s.tpoflag, sum(1), sum(d.scheduled)/1000000, sum(d.realized )/1000000, round(100*(1-power(sum(d.realized)/sum(d.scheduled),12)),3) as cpr from stt s, dyn d where s.loanseqnum = d.loanseqnum group by s.product, s.coupon, s.productionyear, s.tpoflag having sum(d.scheduled) > 0 ;

2 1

Extremely slow order by queries
by Manish gupta 13 Jul '17

13 Jul '17

Hi, I have a scenario where data size is ~3M rows. My query is select C1, C2, C3 from T1 group by C1,C2,C3 This returns result in 30 Sec ( number of groups around 1 M ) While, if I modify the query to select C1, C2, C3 from T1 group by C1,C2,C3 Order by C1, C2 on the same dataset, it takes ~ 1 hour to get result. The platforms I tried is both widows 64 bit and Linux 64 bit, with latest monet installed. Both the machines have 4 cores. And the behavior is same on both the machines. I am really surprised to see overhead of second query. One more observation is that this query occupies only single core, while monet is free to utilize 4 cores. Regards, Manish

2 1

Mserver returns with "memory exhausted in" error if query size exceeds 1.2MB
by Sharma, Sreejith 08 Jul '17

08 Jul '17

Hi, I have encountered scenario where SQL generated exceeds 3mb and it's failing in mserver with an error "memory exhausted in select .......". On further checks I have noticed that the SQL will go through only if SQL size is less than 1.2MB. Has anyone encountered this before? Or is anyone aware of any SQL size limitation in MonetDB? Please help. Regards, Sreejith

1 0

access MapiHdl by using qtag value from C API
by Anton Kravchenko 26 Jun '17

26 Jun '17

Is there a way to access MapiHdl of the query by using its qtag value?

1 0

Re: MonetDB taking lot of storage space
by Stefan Manegold 17 Jun '17

17 Jun '17

Typo in formula:does * columns * (average) width per columnmust readrows * columns * (average) width per column, -------- Original message --------From: Stefan Manegold <stefan.manegold(a)cwi.nl> Date: 6/17/17 12:33 (GMT+01:00) To: Communication channel for MonetDB users <users-list(a)monetdb.org> Subject: Re: MonetDB taking lot of storage space Hi Amir, May I assume "cr" stands for "crore" (c.f.., https://en.m.wikipedia.org/wiki/Crore) I.e. 1 cr = 10 million = 10^7 = 10,000,000 ? The plain expected size of the database assuming only fixed-width column types and ignoring automatically generated indexes, etc.Can be assumed as does * columns * (average) width per column, e.g.,Assuming all 4 byte numerical columns:(4+5) * 10^7 * 10 * 4 byte = 36 * 10^8 ~= 3.6 GBor twice as much for all 8 byte columns. Your 196 GB is far from that. Are you sure there is no other left-over data in your database?As Jennie suggested, did you do a lot of update (insert, delete) operations on these tables?Which (top-level) directories in your dbfarm are the largest? Best,Stefan -------- Original message --------From: Ying Zhang <Y.Zhang(a)cwi.nl> Date: 6/17/17 10:35 (GMT+01:00) To: Communication channel for MonetDB users <users-list(a)monetdb.org> Subject: Re: MonetDB taking lot of storage space Hai, MonetDB doesn’t provide any user definable compressions. What do you mean with “5cr rows”? Compressed Rows? 198GB is indeed pretty big, so it’s expected to contain a lot of table data. If you don’t have variable length columns, it’s easy to compute the expected storage space: #rows * #columns * (sum of the length of each column). Are your database fresh or a lot of insert and delete have been conducted on them? Then, the deletes might have leaved garbages. Did you happen to have put larges BLOBs or CLOBs in some columns? Which MonetDB version and OS do you use? Regards, Jennie > On 17 Jun 2017, at 09:38, Amit Ambekar <amit(a)g-square.in> wrote: > > Hi, > > i have 2 databases in monetdb. first one has 5cr rows and second has 4 cr rows with average 10 columns per table. It takes 196 GB storage space. is it normal ?? most of data consists of numeric values. And How i can compress database size in MonetDB ?? > > _______________________________________________ > users-list mailing list > users-list(a)monetdb.org > https://www.monetdb.org/mailman/listinfo/users-list _______________________________________________ users-list mailing list users-list(a)monetdb.org https://www.monetdb.org/mailman/listinfo/users-list

1 0

Re: MonetDB taking lot of storage space
by Stefan Manegold 17 Jun '17

17 Jun '17

Hi Amir, May I assume "cr" stands for "crore" (c.f.., https://en.m.wikipedia.org/wiki/Crore) I.e. 1 cr = 10 million = 10^7 = 10,000,000 ? The plain expected size of the database assuming only fixed-width column types and ignoring automatically generated indexes, etc.Can be assumed as does * columns * (average) width per column, e.g.,Assuming all 4 byte numerical columns:(4+5) * 10^7 * 10 * 4 byte = 36 * 10^8 ~= 3.6 GBor twice as much for all 8 byte columns. Your 196 GB is far from that. Are you sure there is no other left-over data in your database?As Jennie suggested, did you do a lot of update (insert, delete) operations on these tables?Which (top-level) directories in your dbfarm are the largest? Best,Stefan -------- Original message --------From: Ying Zhang <Y.Zhang(a)cwi.nl> Date: 6/17/17 10:35 (GMT+01:00) To: Communication channel for MonetDB users <users-list(a)monetdb.org> Subject: Re: MonetDB taking lot of storage space Hai, MonetDB doesn’t provide any user definable compressions. What do you mean with “5cr rows”? Compressed Rows? 198GB is indeed pretty big, so it’s expected to contain a lot of table data. If you don’t have variable length columns, it’s easy to compute the expected storage space: #rows * #columns * (sum of the length of each column). Are your database fresh or a lot of insert and delete have been conducted on them? Then, the deletes might have leaved garbages. Did you happen to have put larges BLOBs or CLOBs in some columns? Which MonetDB version and OS do you use? Regards, Jennie > On 17 Jun 2017, at 09:38, Amit Ambekar <amit(a)g-square.in> wrote: > > Hi, > > i have 2 databases in monetdb. first one has 5cr rows and second has 4 cr rows with average 10 columns per table. It takes 196 GB storage space. is it normal ?? most of data consists of numeric values. And How i can compress database size in MonetDB ?? > > _______________________________________________ > users-list mailing list > users-list(a)monetdb.org > https://www.monetdb.org/mailman/listinfo/users-list _______________________________________________ users-list mailing list users-list(a)monetdb.org https://www.monetdb.org/mailman/listinfo/users-list

1 0

MonetDB taking lot of storage space
by Amit Ambekar 17 Jun '17

17 Jun '17

Hi, i have 2 databases in monetdb. first one has 5cr rows and second has 4 cr rows with average 10 columns per table. It takes 196 GB storage space. is it normal ?? most of data consists of numeric values. And How i can compress database size in MonetDB ??

2 1

SQLsetTrace assumes in-place append
by Roberto Cornacchia 14 Jun '17

14 Jun '17

Background: I'm working on a side-effect-free branch of MonetDB. No in-place operations allowed. With bat.append() already changed this way, everything seemed to work so far, except TRACE. Even TRACE SELECT 1 would fail. I looked at it and the reason is that SQLsetTrace assumed in-place appends, thus first adding new empty bats to resultset, then filling in those bats. Everywhere else this is not assumed. The return bat of append is always created and used, even if it is the same of the first operand. The diff in attachment makes it work for both branches and is in my opinion more correct and consistent with the rest of the code. Best, Roberto

1 0

Announcement: New Dec2016-SP5 Bugfix release of MonetDB suite
by Sjoerd Mullender 12 Jun '17

12 Jun '17

The MonetDB team at CWI/MonetDB BV is pleased to announce the Dec2016-SP5 bugfix release of the MonetDB suite of programs. More information about MonetDB can be found on our website at <http://www.monetdb.org/>. For details on this release, please see the release notes at <http://www.monetdb.org/Downloads/ReleaseNotes>. As usual, the download location is <http://dev.monetdb.org/downloads/>. Dec 2016-SP5 bugfix release Bug Fixes * 6290: Crash (and assertion failure) with a correlated subquery with NOT IN in the select-list * 6291: crash if executes a function with sample operator * 6294: Sqlitelogictest crash * 6296: Another sqllitelogictest crash :( * 6297: 7th sqllitelogictest crash :( * 6300: Protect against missing BATs (sqlsmith) * 6314: Lateral crash report (sqlsmith) * 6315: Exist operator on type bigint missing (sqlsmith) * 6316: Coalesc and limit error (sqlsmith) * 6317: Two-column integer aggregation extremely slow * 6318: Daemon crashes if .merovignian_properties for a database contains more than 42 entries * 6320: The daemon ignores all but the last entry in the .merovignian_properties file when spawning mserver5

1 0

How to use prepared statements within a function
by Luis Larios 08 Jun '17

08 Jun '17

Hi, We’d like to know if there is a way to create and execute prepared statements within an SQL function. In particular, once that we use: “prepare select * from table” How can I retrieve the prepared statement ID to use in an exec()? Thanks, Luis Ángel. The information transmitted, including any attachments, is intended only for the individual or entity to which it is addressed, and may contain confidential and/or privileged information. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by individuals or entities other than the intended recipient is prohibited, and all liability arising therefrom is disclaimed. If you have received this communication in error, please delete the information from any computer and notify the sender.

2 1

Cannot execute embedded R in MonetDB
by Luis Larios 25 May '17

25 May '17

Hi, We are having issues when executing embedded R in a MonetDB SQL function. We have compiled and configured MonetDB accordingly, so as to be able to run embedded R; however, the simple example in the documentation fails to execute. This is the function we have: create function rapi00() returns table( d int ) language R { return seq(1,10); }; Having just "seq(1,10)" doesn't make any difference either. When this function gets created, we don't get any error; however, when we do the following: select d from rapi00() as r where d > 5; we get the this error: Query failed: TypeException:user.s6_3[3]:'rapi.eval' undefined in: X_4:bat[:int] := rapi.eval(7fdb9830e210:ptr,"{\n\treturn seq(1,10);\n};":str); We don't know how to tackle this issue. We have followed the process from https://www.monetdb.org/content/embedded-r-monetdb. Do you have any suggestion to make this work? Thanks! Luis Ángel. The information transmitted, including any attachments, is intended only for the individual or entity to which it is addressed, and may contain confidential and/or privileged information. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by individuals or entities other than the intended recipient is prohibited, and all liability arising therefrom is disclaimed. If you have received this communication in error, please delete the information from any computer and notify the sender.

2 1

binary bulk loading improvements?
by Lynn Carol Johnson 22 May '17

22 May '17

Hello Stefan and developers – Has any more thought been given to performance issues on binary bulk loading of large tables? I’m hoping to hear the “steal” option will be available in an upcoming release. Thanks - Lynn

1 0

Distributed Join and Replica Tables
by Kelly Burkhart 11 May '17

11 May '17

Can someone describe how exactly replica tables are used to push joins to worker nodes? Assume a master node and two worker nodes. I assume my reference table would go on my master node, and on my worker nodes I would have my large data table and replicas of the reference tables from the master node. Is that the general strategy? Thanks, -K

4 6

RE: Queries regarding embedded python
by Ganesh 08 May '17

08 May '17

Hi there, hope you are doing good. We were trying to implement Monetdb/python i.e. embedded python and tried the following code but its giving an error. (We have tried it on Linux) CREATE FUNCTION python_times_two(i INTEGER) RETURNS INTEGER LANGUAGE PYTHON { return i * 2 }; CREATE TABLE integers(i INTEGER); INSERT INTO integers VALUES (1), (2), (3), (4), (5); SELECT python_times_two(i) AS result FROM integers; Error: TypeException:user.s12_1[10]:'pyapi.eval' undefined in: X_18:int := pyapi.eval(0x7f051006a340:ptr,"{\n return i * 2\n};":str,X_17:int); Please help us on what should be done in order to code to work. Also need clarification on following points: 1. Does embedded R or embedded python work on windows? 2. Does it take data in memory when we use machine learning with Numpy and Scikitlearn? Awaiting for your response. Warm Regards, Ganesh Anbhule Data Scientist

2 1

Retrieving all schemata for a user
by Luis Larios 07 May '17

07 May '17

Hi, I wonder if anyone knows how I can retrieve all schemata on which a user has been granted permissions/authorization. I have checked most of the hinting tables I get from querying >> select id, name, schema_id, query from sys.tables where system = true; but I have had no luck with that. I only get the default schema for every user when I perform the following: >> select * from sys.users Any ideas or suggestions are welcome. Thanks! Luis Ángel. The information transmitted, including any attachments, is intended only for the individual or entity to which it is addressed, and may contain confidential and/or privileged information. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by individuals or entities other than the intended recipient is prohibited, and all liability arising therefrom is disclaimed. If you have received this communication in error, please delete the information from any computer and notify the sender.

2 1

Error while installing Monetdb
by Amit Ambekar 06 May '17

06 May '17

Hi, How much time it will take for new release ? On Wednesday 03 May 2017 03:30 PM, users-list-request(a)monetdb.org wrote: > Error while installing Monetdb

4 5

Problem with release
by Amit Ambekar 03 May '17

03 May '17

Hi, New release of monetdb is not installing on ubuntu 16.04 LTS. I think problem will be your new release which is happened on 1 May 2017. Change https://dev.monetdb.org/downloads/deb/dists/xenial/monetdb/binary-amd64/Rel…: Valid-Until: Tue, 30 Nov 2038 00:00:00 JST this is not working. JST to some other proper format

1 0

Node.js and MonetDB...
by Kevork Vartanian 03 May '17

03 May '17

Hi, For one of the projects I'm working on, I'm planning to use Node.js (and probably Angular 2) and I was wondering if any of you know of any good resources and documentation for using Node.js with MonetDB, or if anyone has hands on experience and can share that with the users list? I have seen some npm documentation but any advice from you is greatly appreciated. Thank you in advance. Regards, Kevork Vartanian Technical Solutions Consultant kev(a)innotta.com.au<mailto:kev@innotta.com.au> | +61 406 776 789 | www.innotta.com.au<http://www.innotta.com.au/> [innotta logo 673] This email correspondence may contain privileged information and it is intended to be transmitted to the addressee abovementioned. If you are not the intended recipient of this email correspondence, you must not distribute, copy, forward or rely upon its contents. We ask that you notify the sender of this error and immediately delete this email correspondence.

2 1

Error while installing Monetdb
by Amit Ambekar 03 May '17

03 May '17

Hi, I am trying to install monetdb but some error occurs on my ubuntu OS. Invalid 'Date' entry in Release file /var/lib/apt/lists/partial/dev.monetdb.org_downloads_deb_dists_xenial_Release E: Failed to fetch http://dev.monetdb.org/downloads/deb/dists/xenial/Release Invalid 'Valid-Until' entry in Release file /var/lib/apt/lists/partial/dev.monetdb.org_downloads_deb_dists_xenial_Release

1 0

Announcement: New Dec2016-SP4 Bugfix release of MonetDB suite
by Sjoerd Mullender 01 May '17

01 May '17

The MonetDB team at CWI/MonetDB BV is pleased to announce the Dec2016-SP4 bugfix release of the MonetDB suite of programs. More information about MonetDB can be found on our website at <http://www.monetdb.org/>. For details on this release, please see the release notes at <http://www.monetdb.org/Downloads/ReleaseNotes>. As usual, the download location is <http://dev.monetdb.org/downloads/>. Dec 2016-SP4 bugfix release MonetDB5 Server * Fixed a bug causing a crash during cleanup when mserver5 is stopped with monetdb stop database. * Some memory leaks were plugged. MonetDB Common * A potential deadlock was fixed in order index creation. * A bug that could happen during recovery of the write-ahead log (WAL) was fixed. See changeset 98ad79c555cc for details. SQL * Some memory leaks were plugged. * Upgrade code was added for an old change in the sys.settimeout function. * A bug was fixed with the automatic "vacuum" operation on system tables. Bug Fixes * 6110: cast of a SQL boolean value to a string or clob or (var)char is wrong * 6254: Crash (and assertion failure) after querying a view which uses a correlated subquery in the select-list * 6256: Assertion Trigger on FULL OUTER JOIN with more than two BETWEEN clauses * 6257: wrong count values (1 instead of 0) for correlated aggregation queries * 6258: Vulnerability in FITS and NETCDF data vaults * 6259: crash on select query from sqlitelogictests * 6260: Sqlitelogictest crash * 6288: Function cannot find column in merge table * 6295: msqldump writes unescaped timestamp values when using inserts

1 0

Excution Model of MonetDB (column-at-a-time...or?)
by Mahmoud Mohsen 15 Apr '17

15 Apr '17

Greetings Eyal, /Thank you/ a lot for your quick /response/, >Are you interested in that for purposes of academic research? Yes, If you could suggest me similar work, that is opensource, I would very much appreciate it. Best Regards, Mahmoud Mohsen >1. Column-at-a-time. >2. I'm almost certain the answer is "No", but even if it had been "Yes", >the changes in MonetDB since then would make it unrealistic to use it >with today's code without rewriting a lot of it if not all of it. And >the changes since those days in the closed-source Vectorwise/Actian >Vector have also been significant >Are you interested in that for purposes of academic research? >Eyal On 04/14/2017 07:39 PM, Mahmoud Mohsen wrote: >/Greetings, />//>/The current query engine of the MonetDB...what model of processing does />/it follow....(column-at-a-time, tuple-at-a-time, vector-at-a-time)? />//>/one last question.... />//>/Has the X100 query engine ever been published as opensource in the older />/releases of MonetDB? />//>/Thanks a lot in advance, />//>/Best Regards, />//>/Mahmoud Mohsen /

1 0

Excution Model of MonetDB (column-at-a-time...or?)
by Mahmoud Mohsen 14 Apr '17

14 Apr '17

Greetings, The current query engine of the MonetDB...what model of processing does it follow....(column-at-a-time, tuple-at-a-time, vector-at-a-time)? one last question.... Has the X100 query engine ever been published as opensource in the older releases of MonetDB? Thanks a lot in advance, Best Regards, Mahmoud Mohsen

2 1

remote tables and OCC
by Roberto Cornacchia 12 Apr '17

12 Apr '17

I'm curious to know whether optimistic concurrency control applies across remote tables. For example: db1 contains tableA db2 defines remote tableA-> db1 say that db1 modifies tableA, while db2 already had an open transaction on the same table. Will the transaction on db2 fail? Roberto

1 0

COLcopy
by Roberto Cornacchia 04 Apr '17

04 Apr '17

I'm seeing a strange behaviour with COLcopy(). (Dec2016, optimized, non-devel compilation) In short, it seems to take almost half second to copy a 1-tuple string (view) bat. Inspected with gdb, I see that the copy falls in "(3) we can copy the heaps (memcopy, or even VM page sharing)", with the following values: cnt = 1 bunstocopy = BUN_NONE isVIEW(b) = TRUE VIEWtparent(b) = 0 b->T.heap.size = 1024 b->T.vheap.size = 1094320128 The actual tail and heap copy then takes place: heapcopy(bn, "tail", &bthp, &b->theap) heapcopy(bn, "theap", &thp, b->tvheap) Does this mean that a heap of almost 1GB has been copied for a 1-tuple view? Roberto

2 3

Wrong n. of rows in MAL plan?
by Roberto Cornacchia 04 Apr '17

04 Apr '17

Could you give me a feedback on whether this is a possible bug or not? In the following excerpt: X_377=<tmp_2516>[9]:bat[:str] := mat.packIncrement(X_376=<tmp_2516>[9]:bat[:str],X_335=<tmp_20775>[0]:bat[:str]);"] ... ... X_100=<tmp_2516>[10]:bat[:str] := mat.packIncrement(X_377=<tmp_2516>[10]:bat[:str],X_336=<tmp_24175>[1]:bat[:str]);"] X_377 has 9 rows. But when it's used as a parameter for X_100, it is reported to have 10 rows. In between the two instructions above, X_377 is never used. Roberto

1 0

Announcement: New Dec2016-SP3 Bugfix release of MonetDB suite
by Sjoerd Mullender 31 Mar '17

31 Mar '17

The MonetDB team at CWI/MonetDB BV is pleased to announce the Dec2016-SP3 bugfix release of the MonetDB suite of programs. More information about MonetDB can be found on our website at <http://www.monetdb.org/>. For details on this release, please see the release notes at <http://www.monetdb.org/Downloads/ReleaseNotes>. As usual, the download location is <http://dev.monetdb.org/downloads/>. Dec 2016-SP3 bugfix release MonetDB Common * Fixed a bug when appending string bats that are fully duplicate eliminated. It could happend that the to-be-appended bat had an empty string at an offset and at that same offset in the to-be-appended-to bat there happened to be a (sequence of) NULL(s). Then this offset would be used, even though it might nog be the right offset for the empty string in the to-be-appended-to bat. This would result in multiple offsets for the empty string, breaking the promise of being duplicate eliminated. Bug Fixes * 6138: Weak duplicate elimination in string heaps > 64KB * 6183: ResultSet returns double quoted column name if name contains space characters * 6216: Assertion raised (sqlsmith) * 6219: Crash in rel_optimizer (sqlsmith) * 6227: Monetdb fails on remote tables * 6228: mclient crashes if real column is multiplied by it itself * 6229: ANALYZE, unexpected end of input * 6230: ANALYZE, syntax error * 6237: semijoin with empty right bat does not return immediately * 6242: Crash on rel_reduce_groupby_exps (sqlsmith) * 6243: Static optimization gives wrong result (1 + NULL = -127) * 6245: Nested query crashes all versions of MonetDB or gives wrong result starting from Dec2016-SP2 * 6246: update statements: references to a table do not bind to its alias * 6247: Type analysis issue (sqlsmith) * 6248: update statements: the semantic stage does not resolve the relation in the from clause * 6250: Assertion failure when querying a Blob column with order by DESC * 6251: Crash after adding an ordered index on sys.statistics column and querying sys.statistics * 6253: FITS Data Vaults does not work when using user/pw and other than sys schema name

1 0

Remote Table IS NULL
by Siklós Gábor 27 Mar '17

27 Mar '17

Hi there, We have an issue about the remote table on monetdb version of 2016 (sp1 and sp2) on Ubuntu 14.04 and on Windows Server2012 R2 The scenario for reproducing the issue with two nodes on node1 Create a table on remote node and fill it: CREATE TABLE test(id int not null, name text, valid boolean); INSERT INTO test (id, name) VALUES (1, '1'); INSERT INTO test (id, name) VALUES (2, '2'); INSERT INTO test (id) VALUES (3); on node2 CREATE REMOTE TABLE test(id int not null, name text, valid boolean) ON 'mapi:monetdb://node1:50000/dbfarm'; Then on node2: select * from test; +------+------+-------+ | id | name | valid | +======+======+=======+ | 1 | 1 | null | | 2 | 2 | null | | 3 | null | null | +------+------+-------+ It works fine, but: select * from test where name is null; +----+------+-------+ | id | name | valid | +====+======+=======+ +----+------+-------+ id 3 should appear here. Furthermore: select * from test where name is not null; (mapi:monetdb://monetdb@192.168.254.31/reports2) Cannot register project ( select ( table(sys.test) [ test.id NOT NULL, test.name, test.valid ] COUNT ) [ clob "NULL" ! <= test.name ! <= clob "NULL" ] ) [ test.id NOT NULL, test.name, test.valid ] REMOTE mapi:monetdb://.../... select * from test where valid is null; illegal input, not a JSON header (got '') and node1 is crashed (need : monetdb start farm). After downgrading on ubuntu 14.04 to version of 2015 (SP4) this scenraio works fine. Thanks, SG

2 1

Table size does not decrease despite records deletion
by Manish gupta 26 Mar '17

26 Mar '17

Dear All, Is there a way to truncate the table size when deleting some of the records? In my application, I delete and insert ~1 million records daily. But effectively, it should result in few hundreds additional records. But, I see the size of the table increases by a millions ( that can be seen in storage(), as well as hard disk size ). While the "select count(*) from table" reflects correct number. Is there a way, I can stop increase in table size in my scheme? Regards, Manish

3 10

Memory profiling of monet database
by Manish gupta 24 Mar '17

24 Mar '17

Dear all, I am looking at some memory profiling of monet database. Basically, the database size at disk is ~160 GB ( Although I am not very convinced with this big data size, there are 200M records with ~70 columns distributed among two big tables and several smaller ( relatively ) tables) . Right now, I have 64GB physical RAM dedicated machine for this database, but soon after firing queries on these tables ( with all sort of permutations on columns, but no joins between tables ), the memory is almost fully occupied, and resource crunch kills the performance. Is there any utility, which shows which all columns are in-memory and what is the size? And better some setting through which I can guide which columns should remain in memory and which should immediately trimmed after query returns? I am using windows 16 core machine on 64 bit architecture. Regards, Manish

2 4

Merge table performance on single machine with 4 cores
by Manish gupta 23 Mar '17

23 Mar '17

Dear all, I am checking the performance of merge table vs single table on single machine with 4 cores. The fact table contains ~10million records. I am using # MonetDB 5 server v11.25.9 "Dec2016-SP2" on windows machine ( core i7 16GB physical ram ). I have created a merge table which contains fact table and 3 blank copies of fact table. I am seeing surprisingly slow performance of merge table queries compared to base table queries. Base table query ================= sql>select sum(f_1_1_1), count(*), f_1_1_7,f_1_1_5 from t3760_279_1_1 group by f_1_1_7,f_1_1_5 order by sum(f_1_1_1) desc limit 10; +-----------+------+---------+---------+ | L2 | L5 | f_1_1_7 | f_1_1_5 | +===========+======+=========+=========+ | 125885351 | 14 | SALE | 8084869 | | 125629268 | 15 | SALE | 6852518 | | 121733416 | 16 | SALE | 5440605 | | 110328868 | 13 | SALE | 4842411 | | 100848236 | 11 | SALE | 8068509 | | 100719291 | 16 | SALE | 5822736 | | 93127875 | 11 | SALE | 6529612 | | 91034094 | 13 | SALE | 3401293 | | 90766181 | 10 | SALE | 8084900 | | 88848574 | 10 | SALE | 2660811 | +-----------+------+---------+---------+ 10 tuples (44.2s) Merge Table Query ================= sql>select sum(f_1_1_1), count(*), f_1_1_7,f_1_1_5 from mt279_1_1 group by f_1_1_7,f_1_1_5 order by sum(f_1_1_1) desc limit 10; +-----------+------+---------+---------+ | L2 | L5 | f_1_1_7 | f_1_1_5 | +===========+======+=========+=========+ | 125885351 | 14 | SALE | 8084869 | | 125629268 | 15 | SALE | 6852518 | | 121733416 | 16 | SALE | 5440605 | | 110328868 | 13 | SALE | 4842411 | | 100848236 | 11 | SALE | 8068509 | | 100719291 | 16 | SALE | 5822736 | | 93127875 | 11 | SALE | 6529612 | | 91034094 | 13 | SALE | 3401293 | | 90766181 | 10 | SALE | 8084900 | | 88848574 | 10 | SALE | 2660811 | +-----------+------+---------+---------+ 10 tuples (1m 51s) For the same results ( because merge table is containing the base table and 3 blank copies of base table ), merge table query is taking more than 2.5 times the base table runtime. Why is it so? Thanks, Mohit

5 11

How to execute a query string in MonetDB
by Luis Larios 20 Mar '17

20 Mar '17

Hi, I'm attempting to declare a query string to execute in MonetDB. The documentation is not clear if this is possible, but other engines allow this functionality (such as MySQL and MS SQL Sever). Trying the following execute 'select * from tables'; fails with the message Error: syntax error, unexpected STRING, expecting IDENT or sqlINT The reason behind this is that I need to declare a variable *schema name*, such that I can execute a query in the following way: declare s varchar(32); set s = 'the_schema'; execute 'select * from ' || the_schema || '.the_table'; Please let me know if this is even possible in MonetDB or if you have some tips about that. I tried with a *prepared statement*, as given in MonetDB's documentation -- but the following code cannot execute prepare 'select * from ' || the_schema || '.the_table'; because prepare expects an actual query, not a string. I'd like to achieve this with only SQL stored functions. The client I have has to execute the SQL functions directly, and there's no intermediate Java/PHP/etc scripts to build the SQL on the fly before they're being sent to MonetDB server. Thus, I should create a function like so: create function getData( dataSchema varchar(32) ) returns bigint begin declare query varchar(128); set query = 'select count(*) from ' || dataSchema || '.the_table'; return( execute query ); end; My function is more complicated than this, with other tables and even functions. The issue is that functions belong to one schema, and data tables belong to another schema, which is not known in 'compile' time. Thanks for the help! Luis Ángel The information transmitted, including any attachments, is intended only for the individual or entity to which it is addressed, and may contain confidential and/or privileged information. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by individuals or entities other than the intended recipient is prohibited, and all liability arising therefrom is disclaimed. If you have received this communication in error, please delete the information from any computer and notify the sender.

3 5

weird mserver startup failure on windows
by Anthony Damico 16 Mar '17

16 Mar '17

hi, i am trying to test something with this version of mserver on windows: http://monetdb.cwi.nl/testweb/web/63169:4438fea68169/MonetDB5-SQL-Installer… it installed fine, but when i load up an mserver session, it says: # MonetDB 5 server v11.26.0 "default-4438fea68169" # Serving database 'demo', using 8 threads # Compiled for x86_64-pc-winnt/64bit # Found 15.914 GiB available main-memory. # Copyright (c) 1993-July 2008 CWI. # Copyright (c) August 2008-2017 MonetDB B.V., all rights reserved # Visit http://www.monetdb.org/ for further information !SyntaxException:parseError: !LoaderException:loadLibrary:Loading error failed to open library pyapi (from within file 'C:\Program Files\MonetDB\MonetDB5\lib\monetdb5\lib_pyapi.dll'): The specified module could not be found. !SyntaxException:parseError: !<identifier> expected > and then if i click on the window, it disappears. if i try to connect to an mserver session through MonetDB.R, the mserver window repeats these two lines forever: # SQL catalog created, loading sql scripts once # loading sql script: 09_like.sql here's a screenshot: http://tinypic.com/r/s6rtjd/9 any ideas about what i should try? maybe installing python? but i've never had to do so before, so maybe i'm doing something else dumb.. thanks!

2 3

Checking for a table's existence from a Python connection. (mapi ? sql?)
by Omri Schwarz 13 Mar '17

13 Mar '17

Hi, all. I’m trying to add a MonetDB interface to VoltTron, a Python based server for building and energy management systems. To that end I have to be able to use some of the mclient commands from it, for example, to check if a table exists (\d ), from Python. IIUC, monetdb.mapi.Connection objects should enable me to do that, but I have no idea how. A second question is about performance. The existing databse schema for this product looks like this in MonetDB: CREATE TABLE "voc"."data_test" ( "ts" TIMESTAMP(3) NOT NULL, "topic_id" INTEGER NOT NULL, "value_string" CHARACTER LARGE OBJECT NOT NULL, CONSTRAINT "data_test_topic_id_ts_unique" UNIQUE ("topic_id", "ts") ); There’s a time stamp and topic ID, and SELECT queries are almost always for a narrow range in time, and a small set of topic IDs (usually a set of one). The “value_string” is there because values in the topics can range in type, but are almost always floats. Can I expect MonetDB’s performance to be good For this use case? Or should I force the value_string to a float?

2 1

monetdb to remote monetdbd makes fun of me
by Roberto Cornacchia 09 Mar '17

09 Mar '17

I'm running a dbfarm on a remote server serverX The dbfarm has been created this way (passphrase changed): monetdbd create /var/lib/monetdb/dbfarm monetdbd set control=yes /var/lib/monetdb/dbfarm monetdbd set port=50000 /var/lib/monetdb/dbfarm monetdbd set listenaddr=0.0.0.0 /var/lib/monetdb/dbfarm monetdbd set passphrase=roberto /var/lib/monetdb/dbfarm Port 50000 is open. >From another machine on the same subnet, I request a status: $ monetdb -hserverX -p50000 -Proberto status status: =OK It does connect (using a different port gives a connection refused). However, it just acknowledges the command, without performing it. Whatever command I try, it behaves the same: $ monetdb -hserverX -p50000 -Proberto create x create: =OK $ monetdb -hserverX -p50000 -Proberto create x create: =OK Notice that the second one should have failed. This confirms that the commands are not executed at all. I have had this issue for a long time, it's nothing new. I just never asked before, because I normally solve it with ssh tunneling a port and using it locally. In this case, I cannot use this. Am I missing something, or is it a bug? Roberto

2 2

on windows, is it possible to limit the amount of RAM available to mserver.exe?
by Anthony Damico 04 Mar '17

04 Mar '17

https://www.monetdb.org/Documentation/mserver5-man-page makes it look like i should be able to do this, but i have tried a few different things and none of them have worked. in the mserver window, i just want this line to be reduced to 50MB or something very small. # Found 15.913 GiB available main-memory. mserver window: # MonetDB 5 server v11.25.5 "Dec2016-SP1" # Serving database 'mydb', using 8 threads # Compiled for x86_64-pc-winnt/64bit # Found 15.913 GiB available main-memory. # Copyright (c) 1993-July 2008 CWI. # Copyright (c) August 2008-2017 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/ # MonetDB/SQL module loaded here's what my database.bat file looks like: @echo off setlocal set MONETDB=C:\Program Files\MonetDB\MonetDB5 set PATH=%MONETDB%\bin;%MONETDB%\lib;%MONETDB%\lib\MonetDB5;%PATH% "%MONETDB%\bin\mserver5.exe" --daemon=yes --set "prefix=%MONETDB%" --set "exec_prefix=%MONETDB%" "--dbpath=C:\Users\damico\AppData\Local\Temp\RtmpgBoQzw\mydb" %* --set mapi_port=50001 if ERRORLEVEL 1 pause endlocal thanks!

1 0

merovingian.log too verbose?
by Roberto Cornacchia 03 Mar '17

03 Mar '17

Is there a way to make monetdbd a bit quieter? In a setting with many connections, it quickly becomes several GB of this: 2016-03-01 10:25:03 MSG merovingian[94243]: proxying client localhost:52272 for database 'xxx' to mapi:monetdb:///var/lib/monetdb/dbfarms/yyy/xxx/.mapi.sock?database=xxx 2016-03-01 10:25:03 MSG merovingian[94243]: target connection is on local UNIX domain socket, passing on filedescriptor instead of proxying

2 4

Announcement: New Dec2016-SP2 Bugfix release of MonetDB suite
by Sjoerd Mullender 03 Mar '17

03 Mar '17

The MonetDB team at CWI/MonetDB BV is pleased to announce the Dec2016-SP2 bugfix release of the MonetDB suite of programs. More information about MonetDB can be found on our website at <http://www.monetdb.org/>. For details on this release, please see the release notes at <http://www.monetdb.org/Downloads/ReleaseNotes>. As usual, the download location is <http://dev.monetdb.org/downloads/>. Dec 2016-SP2 bugfix release Bug Fixes * 4034: argnames array in rapi.c has fixed length (that was too short) * 6080: mserver5: rel_bin.c:2391: rel2bin_project: Assertion `0' failed. * 6081: Segmentation fault (core dumped) * 6082: group.subgroup is undefined if group by is used on an expression involving only constants * 6111: Maximum number of digits for hge decimal is listed as 39 in sys.types. Should be 38 as DECIMAL(39) is not supported. * 6112: Crash upgrading Jul2015->Jun2016 * 6130: Query rewriter crashes on a NULL pointer when having a correlated subquery * 6133: A crash occurs when preparing an INSERT on joined tables during the query semantic phase * 6141: Getting an error message regarding a non-GROUP-BY column rather than an unknown identifier * 6177: Server crashes * 6186: Null casting causes no results (silent server crash?) * 6189: Removing a NOT NULL constraint from a PKey column should NOT be allowed * 6190: CASE query crashes database * 6191: MT_msync failed with "Cannot allocate memory" * 6192: Numeric column stores wrong values after adding large numbers * 6193: converting to a smaller precision (fewer or no decimals after decimal point) should round/truncate consistently * 6194: splitpart returns truncated last part if it contains non ascii caracters * 6195: Cast from huge decimal type to smaller returns wrong results * 6196: Database crashes after generate_series query * 6198: COALESCE could be more optimized * 6201: MonetDB completely giving up on certain queries - no error and no result * 6202: querying a table with an ordered index on string/varchar column crashes server and makes server unrestartable! * 6203: copy into: Failed to import table Leftover data 'False' * 6205: Integer addition overflow * 6206: casting strings with more than one trailing zero ('0') to decimal goes wrong * 6209: Aggregation over complex OR expressions produce wrong results * 6210: Upgrading a database from Jun2015 or older crashes the server * 6213: SQLsmith causes server to crash * 6217: Segfault in rel_optimizer (sqlsmith) * 6218: grouped quantiles with all null group causes following groups to return null * 6224: mal_parser: cannot refer to types containing an underscore

1 0

monetdb.org system maintenance
by Sjoerd Mullender 01 Mar '17

01 Mar '17

Tomorrow, March 2, we will be upgrading the hardware for the system on which all monetdb.org infrastructure runs. Expect some downtime. -- Sjoerd Mullender

1 0

monetdb cluster
by 张馨匀 01 Mar '17

01 Mar '17

Hi all, Do you have any idea about the query throughput regarding monetdb cluster, is it slow? I created the monetdb cluster using remote table, when I do query, it's really slow, about 3sec/query. Do you know how to improve this? Or MonetDB can actually do better than this? And How? Thank you

3 2

compilation src error with the latest version
by 张馨匀 28 Feb '17

28 Feb '17

Hi all, Do you know how to fix the src compilation error? Error is as following: *fatal error: *geos_c.h: No such file or directory #include <geos_c.h> * ^* compilation terminated. Thanks,

4 3

Running aggregates?
by Stefan Manegold 27 Feb '17

27 Feb '17

Hi, does MonetDB support any efficient/optimized functionality to compute running aggregates? A quick check/test suggest that aggregation functions with windows functions like below does not seem to be supported, is it? ======== SELECT somedate, somevalue, SUM(somevalue) OVER(ORDER BY somedate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RunningTotal FROM Table ======== Is their anything else that would provide running aggregates? Or am I (for now?) bound to calculating running aggregates using the obvious theta-self-join (expected to be "non-optimal" due to redundant work and large (huge) intermediate results), or "hijacking"/"mis-using" the bulk-version of a (to be implemented) scalar function? Thanks! Stefan -- | 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) |

3 3

MALException:opt.remap:Function 'calc.isnil' not defined
by Anthony Damico 27 Feb '17

27 Feb '17

hi, i upgraded monetdb and am hitting this error with code that previously worked. my (giant) query has lots of CAST and IS NULL commands in it.. trying to narrow it down now, but does anyone have ideas about what might've introduced this? it looks like something is calling IS NULL where it shouldn't be allowed to? thanks

4 13

single-user / locked mode
by Roberto Cornacchia 20 Feb '17

20 Feb '17

Hi, I'm confused about the single-user mode (working with latest Dec2016 from hg). *Question/suggestion 1* 'mserver5 --single-user' can also be set with 'monetdb set singleuser=yes'. However, 'monetdb get all' doesn't show 'singleuser' as one of the options, I had to guess it myself. It would be nice if monetdb could show an actual list of available options, instead a manually compiled one. *Question 2* is 'mserver5 --single-user' expected to put the database in a locked state, equivalent to 'monetdb lock' ? If the answer is yes, then it doesn't do it consistently: - it does allow to use COPY INTO.... LOCKED (from which you would think the database is locked) but: - it doesn't show the status of the database as locked - it indeed allows new client connections Could you clarify how 'single-user' and 'locked' relate? *Question 3 *(long-ish) I am pushing, via JDBC, the entire English DBpedia via COPY INTO (without 'LOCKED'), into a dbfarm on local SSD. It is about 200GB raw data and it takes ~9 hours. Data goes through a pipeline of light pre-processing (feeding thread) and then inserted into the stream of COPY INTO (output thread). What I observe is that, at the beginning, the feeding thread is mush active, and the output thread very little. After a couple of hours, the output thread is mostly active, and the feeding thread mostly idle. I told myself that MonetDB is taking more and more time to flush appends to disk. That's where I though using 'COPY INTO .. LOCKED' would help. Not only it didn't help, but it got the symptoms worse. The output thread is almost 100% active, and the feeding thread almost 100% idle. Indeed, it is already running for 12h and still going on. Was I wrong to expect that 'LOCKED' would help? Thanks, Roberto

2 2

Option to suppress status messages when running -f mode
by Anderson, David B 17 Feb '17

17 Feb '17

All, Is there a way to suppress the output of status messages like "3000 affected rows" when using mclient and "-f csv"? I have an sql script where I keep reducing a table and inserting the rows into another table. The final sql command extracts the final result. Would be nice if the status messages could be suppressed completely or sent to stderr (a --quiet option). I could also just run two scripts, or pipe the output through grep to remove the offending lines. Thanks, Dave

3 2

Best way to force table into hot set
by Anderson, David B 13 Feb '17

13 Feb '17

All, Is there a preferred method to move an entire table into the database hot-set? I guess I can have a query which computes across all of the columns in the table, but wondered if there was something better/cleaner. Thanks, Dave

1 0

prevent mitosis from partitioning (filter) function arguments
by Roberto Cornacchia 12 Feb '17

12 Feb '17

I have implemented a custom join as a filter function. By design, it cannot work correctly when the input tables are partitioned by mitosis. How can I prevent mitosis from partitioning its inputs? Roberto

2 6

Severe performance degradation
by Dennis Pallett 10 Feb '17

10 Feb '17

Hi all, We recently upgraded to the Dec2016 release but had to downgrade immediately after experiencing a severe degradation in performance. I have attached 4 graphs of our performance monitoring (of the last 3 days). It should be fairly obvious when we upgraded and when we downgraded again. We're still looking into the root cause but it appears that it has something to do with memory leaks when using aggregations on varchar colums with a high cardinality. Has anyone else experienced the same performance degradation? Is this a known (regression) bug? Best regards, Dennis Pallett

1 1

QueryTimeout Question
by Sebastián Acevedo 09 Feb '17

09 Feb '17

Hi, there is any way to chnage the default querytimeout for a user?... i need to use this to make a restriction for long querys, if i run call sys.settimeout(1) this change is only apllied to the current session and if i login again with the same user the timeout is not longer applied. +---------+-----------------+------+------------------+------+-------+ | user | login | sess | lastcommand | quer | activ | : : : iont : : ytim : e : : : : imeo : : eout : : : : : ut : : : : +=========+=================+======+==================+======+=======+ | monetdb | 2017-02-03 19:1 | 0 | 2017-02-03 19:13 | 1 | true | : : 2:37.000000 : : :28.000000 : : : | monetdb | 2017-02-03 18:0 | 0 | 2017-02-03 18:10 | 0 | false | : : 9:47.000000 : : :18.000000 : : : | monetdb | 2017-02-03 19:1 | 0 | 2017-02-03 19:13 | 0 | false | : : 3:10.000000 : : :12.000000 : : : +---------+-----------------+------+------------------+------+-------+ Thanks Sebastián Acevedo

3 4

Python Aggregate functions
by Anderson, David B 06 Feb '17

06 Feb '17

Mark, I think there is a bug in the implementation of the PYTHON_MAP aggregate functions. The return value of a PYTHON_MAP aggregate function can change if there are other functions in the query. The return value of my function weighted_percentile_0 (definition below) changes if I include the function median() in my query. The results are incorrect if median() isn't in the query, but are correct if median() is in the query. In this test case, the weights (v2) are all 1 and the values (v1) are uniformly sampled integers from [1,10]. Number of rows for each fctr is ~1000, and my function should reduce to min(v1) in this case. Is some information bleeding between threads? Should I submit a bug report? This is on the default branch. Thanks, Dave sql>select fctr,weighted_percentile_0(v1,v2),min(v1) from mini where fctr < 10 group by fctr order by fctr; +------+--------------------------+------+ | fctr | L1 | L2 | +======+==========================+======+ | 1 | 3 | 1 | | 2 | 2 | 1 | | 3 | 2 | 1 | | 4 | 1 | 1 | | 5 | 3 | 1 | | 6 | 3 | 1 | | 7 | 2 | 1 | | 8 | 1 | 1 | | 9 | 2 | 1 | +------+--------------------------+------+ 9 tuples (126.928ms) sql>select fctr,weighted_percentile_0(v1,v2),min(v1),median(v1) from mini where fctr < 10 group by fctr order by fctr; +------+--------------------------+------+------+ | fctr | L1 | L2 | L3 | +======+==========================+======+======+ | 1 | 1 | 1 | 5 | | 2 | 1 | 1 | 5 | | 3 | 1 | 1 | 5 | | 4 | 1 | 1 | 5 | | 5 | 1 | 1 | 5 | | 6 | 1 | 1 | 5 | | 7 | 1 | 1 | 5 | | 8 | 1 | 1 | 5 | | 9 | 1 | 1 | 5 | +------+--------------------------+------+------+ 9 tuples (519.195ms) sql> CREATE AGGREGATE weighted_percentile_0(a DOUBLE, w DOUBLE) RETURNS DOUBLE LANGUAGE PYTHON_MAP { import numpy as np # Standardize and sort based on values in a q = np.array([0]) / 100.0 idx = np.argsort(a) a_sort = a[idx] w_sort = w[idx] # Get the cumulative sum of weights ecdf = np.cumsum(w_sort) # Find the percentile index positions associated with the percentiles p = q * (w_sort.sum() - 1) # Find the bounding indices (both low and high) idx_low = np.searchsorted(ecdf, p, side='right') idx_high = np.searchsorted(ecdf, p + 1, side='right') idx_high[idx_high > ecdf.size - 1] = ecdf.size - 1 # Calculate the weights weights_high = p - np.floor(p) weights_low = 1.0 - weights_high # Extract the low/high indexes and multiply by the corresponding weights x1 = np.take(a_sort, idx_low) * weights_low x2 = np.take(a_sort, idx_high) * weights_high wp = np.add(x1,x2) return(wp[0]) };

3 4

Virtual Column
by imad hajj chahine 01 Feb '17

01 Feb '17

Hi, How far is the feature of supporting virtual column, with the possibility to persist it as a physical column, from the future release of monetdb? This feature is supported in mysql starting the 5.7 version. https://dev.mysql.com/doc/refman/5.7/en/create-table-generated-columns.html Thank you.

1 0

SQL grammar
by imad hajj chahine 01 Feb '17

01 Feb '17

Hi, I am interested to know how actually monetdb parse an SQL expression and generate the expression tree, are you using lex/yacc similar grammar tool. I am currently using antlr for some basic parsing tasks, but my objective is to be able to generate a complete expression tree from an SQL expression. Thank you.

1 0

Installing Monetdb in on Ubuntu 16.04.1 LTS via repo
by Yan Grange 01 Feb '17

01 Feb '17

Hi there, I'm trying to install monetdb via the instructions on the website inside a Docker container derived from the Ubuntu 16.04.1 LTS image. I get the following error message: root@ContainerHost:/local# sudo apt-get update Ign:1 http://dev.monetdb.org/downloads/deb xenial InRelease Get:2 http://dev.monetdb.org/downloads/deb xenial Release [3878 B] Get:3 http://dev.monetdb.org/downloads/deb xenial Release.gpg [163 B] Hit:4 http://archive.ubuntu.com/ubuntu xenial InRelease Hit:5 http://archive.ubuntu.com/ubuntu xenial-updates InRelease Hit:6 http://archive.ubuntu.com/ubuntu xenial-security InRelease Fetched 4041 B in 0s (6161 B/s) Reading package lists... Done W: http://dev.monetdb.org/downloads/deb/dists/xenial/Release.gpg: Signature by key 213E64DCD5DDC2C063CC39FD053C3ED40583366F uses weak digest algorithm (SHA1) W: Invalid 'Date' entry in Release file /var/lib/apt/lists/partial/dev.monetdb.org_downloads_deb_dists_xenial_Release E: Failed to fetch http://dev.monetdb.org/downloads/deb/dists/xenial/Release Invalid 'Valid-Until' entry in Release file /var/lib/apt/lists/partial/dev.monetdb.org_downloads_deb_dists_xenial_Release E: Some index files failed to download. They have been ignored, or old ones used instead. I did only find one reference to this in a previous discussion. The advice given was to create a bug report. Since for that I need to have an account and that I can't find an account creation link on the bugzilla page, I decided to use this mailing list. Regards and thanks a lot, Yan Grange

2 1

problem stopping/starting monetdbd
by Lynn Carol Johnson 30 Jan '17

30 Jan '17

Hi all - Yesterday our monetdb instance seemed to be running slowing, perhaps due to user queries that had been killed but maybe not cleaned up. We decided to stop the dbs, then start/stop monetdbd. I executed the following commands, but was unable to restart the daemon: sudo monetdbd stop /local/monetdb/dbfarmBL sudo monetdbd start /local/monetdb/dbfarmBL We had our admin reboot the machine, thinking this would clear up everything. This morning I tried again to start monetdbd. The command comes back with no messages, but I get a connection refused message when trying to access a database: could not connect to localhost:50000: Connection refused Get all shows this: [lcj34@cbsudc01 anp68_ije_Locus9Combined_Result]$ monetdbd get all /local/monetdb/dbfarmBL property value hostname cbsudc01 dbfarm /local/monetdb/dbfarmBL status no monetdbd is serving this dbfarm mserver unknown (monetdbd not running) logfile /local/monetdb/dbfarmBL/merovingian.log pidfile /local/monetdb/dbfarmBL/merovingian.pid sockdir /tmp listenaddr 128.84.3.206 port 50000 exittimeout 60 forward proxy discovery yes discoveryttl 600 control no passphrase <unknown> mapisock /tmp/.s.monetdb.50000 controlsock /tmp/.s.merovingian.50000 [lcj34@cbsudc01 anp68_ije_Locus9Combined_Result]$ The log shows the following message: 2017-01-27 07:53:26 MSG maizeFullGenomeDB[14286]: # Listening for UNIX domain connection requests on mapi:monetdb:///local/monetdb/dbfarmBL/maizeFullGenomeDB/.mapi.sock 2017-01-27 07:53:26 MSG maizeFullGenomeDB[14286]: # MonetDB/SQL module loaded 2017-01-27 07:53:29 ERR merovingian[13705]: client error: cannot connect: Connection refused 2017-01-27 07:55:41 MSG merovingian[13705]: caught SIGTERM, starting shutdown sequence 2017-01-27 07:55:50 MSG merovingian[15395]: Merovingian 1.7 (Jun2016-SP2) starting 2017-01-27 07:55:50 MSG merovingian[15395]: monitoring dbfarm /local/monetdb/dbfarmBL 2017-01-27 07:55:50 MSG merovingian[15395]: accepting connections on TCP socket cbsudc01:50000 2017-01-27 07:55:50 MSG merovingian[15395]: accepting connections on UNIX domain socket /tmp/.s.monetdb.50000 2017-01-27 07:55:50 ERR merovingian[15395]: binding to datagram socket port 50000 failed: no available address 2017-01-27 07:55:50 MSG merovingian[15395]: Merovingian 1.7 stopped 2017-01-27 07:55:50 ERR merovingian[15395]: fatal startup condition encountered, aborting startup Any ideas what to try? Thanks - Lynn

3 7

Re: running make command gives error on installation
by Pham Duc 29 Jan '17

29 Jan '17

Hi Miguel, I guess that you tried MonetDB 11.2.0 as it is mentioned in the "Heuristics-based Query Optimisation for SPARQL" paper (https://openproceedings.org/2012/conf/edbt/TsialiamanisSFCB12.pdf) Did you check the instruction provided by the ICS-FORTH researchers ( http://www.ics.forth.gr/isl/index_main.php?l=e&c=646 ) for installing MonetDB? If not, please follow that instruction. If it still does not work, you will need to contact ICS-FORTH researchers (please see the contact at the end of the instruction) as that RDF version of MonetDB 11.2.0 was specifically developed for their research with the support of our group members (e.g., Lefteris). Best regards, Minh Duc ----- Original Message ----- From: "Martin Kersten" <martin.kersten(a)cwi.nl> To: "Communication channel for MonetDB users" <users-list(a)monetdb.org>, "P Boncz" <P.Boncz(a)cwi.nl>, "Pham Minh Duc" <P.Minh.Duc(a)cwi.nl> Sent: Sunday, January 29, 2017 8:41:12 AM Subject: Re: running make command gives error on installation Hi Indeed, this is a branch used by Pham Duc during his PhD research. The code base has not been kept in sync with the main branches and you should contact him/his advisor for further support. regards, Martin On 28/01/2017 21:59, Miguel Mármol wrote: > Hi, > I am trying to install MonetDB5 11.2.0 (The version of MonetDB is not an official MonetDB release. This version includes an RDF frontend that is used to parse RDF datasets and load them in MonetDB.). > > I get the following error when I run the make command: > > > miguel@miguel-VirtualBox:~/Downloads/MonetDB$ sudo make > make all-recursive > make[1]: Entering directory '/home/miguel/Downloads/MonetDB' > Making all in buildtools > make[2]: Entering directory '/home/miguel/Downloads/MonetDB/buildtools' > make all-recursive > make[3]: Entering directory '/home/miguel/Downloads/MonetDB/buildtools' > Making all in Mx > make[4]: Entering directory '/home/miguel/Downloads/MonetDB/buildtools/Mx' > make all-am > make[5]: Entering directory '/home/miguel/Downloads/MonetDB/buildtools/Mx' > make[5]: Nothing to be done for 'all-am'. > make[5]: Leaving directory '/home/miguel/Downloads/MonetDB/buildtools/Mx' > make[4]: Leaving directory '/home/miguel/Downloads/MonetDB/buildtools/Mx' > Making all in conf > make[4]: Entering directory '/home/miguel/Downloads/MonetDB/buildtools/conf' > make all-am > make[5]: Entering directory '/home/miguel/Downloads/MonetDB/buildtools/conf' > make[5]: Nothing to be done for 'all-am'. > make[5]: Leaving directory '/home/miguel/Downloads/MonetDB/buildtools/conf' > make[4]: Leaving directory '/home/miguel/Downloads/MonetDB/buildtools/conf' > make[4]: Entering directory '/home/miguel/Downloads/MonetDB/buildtools' > make[4]: Nothing to be done for 'all-am'. > make[4]: Leaving directory '/home/miguel/Downloads/MonetDB/buildtools' > make[3]: Leaving directory '/home/miguel/Downloads/MonetDB/buildtools' > make[2]: Leaving directory '/home/miguel/Downloads/MonetDB/buildtools' > Making all in common > make[2]: Entering directory '/home/miguel/Downloads/MonetDB/common' > make all-recursive > make[3]: Entering directory '/home/miguel/Downloads/MonetDB/common' > Making all in stream > make[4]: Entering directory '/home/miguel/Downloads/MonetDB/common/stream' > make all-am > make[5]: Entering directory '/home/miguel/Downloads/MonetDB/common/stream' > make[5]: Nothing to be done for 'all-am'. > make[5]: Leaving directory '/home/miguel/Downloads/MonetDB/common/stream' > make[4]: Leaving directory '/home/miguel/Downloads/MonetDB/common/stream' > Making all in options > make[4]: Entering directory '/home/miguel/Downloads/MonetDB/common/options' > make all-am > make[5]: Entering directory '/home/miguel/Downloads/MonetDB/common/options' > make[5]: Nothing to be done for 'all-am'. > make[5]: Leaving directory '/home/miguel/Downloads/MonetDB/common/options' > make[4]: Leaving directory '/home/miguel/Downloads/MonetDB/common/options' > Making all in utils > make[4]: Entering directory '/home/miguel/Downloads/MonetDB/common/utils' > make all-am > make[5]: Entering directory '/home/miguel/Downloads/MonetDB/common/utils' > make[5]: Nothing to be done for 'all-am'. > make[5]: Leaving directory '/home/miguel/Downloads/MonetDB/common/utils' > make[4]: Leaving directory '/home/miguel/Downloads/MonetDB/common/utils' > make[4]: Entering directory '/home/miguel/Downloads/MonetDB/common' > make[4]: Nothing to be done for 'all-am'. > make[4]: Leaving directory '/home/miguel/Downloads/MonetDB/common' > make[3]: Leaving directory '/home/miguel/Downloads/MonetDB/common' > make[2]: Leaving directory '/home/miguel/Downloads/MonetDB/common' > Making all in clients > make[2]: Entering directory '/home/miguel/Downloads/MonetDB/clients' > make all-recursive > make[3]: Entering directory '/home/miguel/Downloads/MonetDB/clients' > Making all in mapilib > make[4]: Entering directory '/home/miguel/Downloads/MonetDB/clients/mapilib' > make all-am > make[5]: Entering directory '/home/miguel/Downloads/MonetDB/clients/mapilib' > make[5]: Nothing to be done for 'all-am'. > make[5]: Leaving directory '/home/miguel/Downloads/MonetDB/clients/mapilib' > make[4]: Leaving directory '/home/miguel/Downloads/MonetDB/clients/mapilib' > Making all in mapiclient > make[4]: Entering directory '/home/miguel/Downloads/MonetDB/clients/mapiclient' > make all-am > make[5]: Entering directory '/home/miguel/Downloads/MonetDB/clients/mapiclient' > /bin/bash ../../libtool --tag=CC --mode=link gcc -Wall -Wextra -g -pedantic -Wno-long-long -Wno-declaration-after-statement -D_REENTRANT -export-dynamic -o mclient mclient.o ReadlineTools.o libmcutil.la ../mapilib/libmapi.la > ../../common/stream/libstream.la -lcrypt -pthread > libtool: link: gcc -Wall -Wextra -g -pedantic -Wno-long-long -Wno-declaration-after-statement -D_REENTRANT -o .libs/mclient mclient.o ReadlineTools.o -pthread -Wl,--export-dynamic ./.libs/libmcutil.a ../mapilib/.libs/libmapi.so > ../../common/stream/.libs/libstream.so -lcrypt -pthread -Wl,-rpath -Wl,/usr/local/monet/lib > ../mapilib/.libs/libmapi.so: undefined reference to `SHA256' > ../mapilib/.libs/libmapi.so: undefined reference to `SHA384' > ../mapilib/.libs/libmapi.so: undefined reference to `RIPEMD160' > ../mapilib/.libs/libmapi.so: undefined reference to `MD5' > ../mapilib/.libs/libmapi.so: undefined reference to `SHA1' > ../mapilib/.libs/libmapi.so: undefined reference to `SHA512' > ../mapilib/.libs/libmapi.so: undefined reference to `SHA224' > collect2: error: ld returned 1 exit status > Makefile:666: recipe for target 'mclient' failed > make[5]: *** [mclient] Error 1 > make[5]: Leaving directory '/home/miguel/Downloads/MonetDB/clients/mapiclient' > Makefile:568: recipe for target 'all' failed > make[4]: *** [all] Error 2 > make[4]: Leaving directory '/home/miguel/Downloads/MonetDB/clients/mapiclient' > Makefile:764: recipe for target 'all-recursive' failed > make[3]: *** [all-recursive] Error 1 > make[3]: Leaving directory '/home/miguel/Downloads/MonetDB/clients' > Makefile:709: recipe for target 'all' failed > make[2]: *** [all] Error 2 > make[2]: Leaving directory '/home/miguel/Downloads/MonetDB/clients' > Makefile:600: recipe for target 'all-recursive' failed > make[1]: *** [all-recursive] Error 1 > make[1]: Leaving directory '/home/miguel/Downloads/MonetDB' > Makefile:530: recipe for target 'all' failed > make: *** [all] Error 2 > > Please any help will be really appreciated, thanks. > > Miguel > > > > _______________________________________________ > users-list mailing list > users-list(a)monetdb.org > https://www.monetdb.org/mailman/listinfo/users-list >

1 0

running make command gives error on installation
by Miguel Mármol 29 Jan '17

29 Jan '17

Hi, I am trying to install MonetDB5 11.2.0 (The version of MonetDB is not an official MonetDB release. This version includes an RDF frontend that is used to parse RDF datasets and load them in MonetDB.). I get the following error when I run the make command: miguel@miguel-VirtualBox:~/Downloads/MonetDB$ sudo make make all-recursive make[1]: Entering directory '/home/miguel/Downloads/MonetDB' Making all in buildtools make[2]: Entering directory '/home/miguel/Downloads/MonetDB/buildtools' make all-recursive make[3]: Entering directory '/home/miguel/Downloads/MonetDB/buildtools' Making all in Mx make[4]: Entering directory '/home/miguel/Downloads/MonetDB/buildtools/Mx' make all-am make[5]: Entering directory '/home/miguel/Downloads/MonetDB/buildtools/Mx' make[5]: Nothing to be done for 'all-am'. make[5]: Leaving directory '/home/miguel/Downloads/MonetDB/buildtools/Mx' make[4]: Leaving directory '/home/miguel/Downloads/MonetDB/buildtools/Mx' Making all in conf make[4]: Entering directory '/home/miguel/Downloads/MonetDB/buildtools/conf' make all-am make[5]: Entering directory '/home/miguel/Downloads/MonetDB/buildtools/conf' make[5]: Nothing to be done for 'all-am'. make[5]: Leaving directory '/home/miguel/Downloads/MonetDB/buildtools/conf' make[4]: Leaving directory '/home/miguel/Downloads/MonetDB/buildtools/conf' make[4]: Entering directory '/home/miguel/Downloads/MonetDB/buildtools' make[4]: Nothing to be done for 'all-am'. make[4]: Leaving directory '/home/miguel/Downloads/MonetDB/buildtools' make[3]: Leaving directory '/home/miguel/Downloads/MonetDB/buildtools' make[2]: Leaving directory '/home/miguel/Downloads/MonetDB/buildtools' Making all in common make[2]: Entering directory '/home/miguel/Downloads/MonetDB/common' make all-recursive make[3]: Entering directory '/home/miguel/Downloads/MonetDB/common' Making all in stream make[4]: Entering directory '/home/miguel/Downloads/MonetDB/common/stream' make all-am make[5]: Entering directory '/home/miguel/Downloads/MonetDB/common/stream' make[5]: Nothing to be done for 'all-am'. make[5]: Leaving directory '/home/miguel/Downloads/MonetDB/common/stream' make[4]: Leaving directory '/home/miguel/Downloads/MonetDB/common/stream' Making all in options make[4]: Entering directory '/home/miguel/Downloads/MonetDB/common/options' make all-am make[5]: Entering directory '/home/miguel/Downloads/MonetDB/common/options' make[5]: Nothing to be done for 'all-am'. make[5]: Leaving directory '/home/miguel/Downloads/MonetDB/common/options' make[4]: Leaving directory '/home/miguel/Downloads/MonetDB/common/options' Making all in utils make[4]: Entering directory '/home/miguel/Downloads/MonetDB/common/utils' make all-am make[5]: Entering directory '/home/miguel/Downloads/MonetDB/common/utils' make[5]: Nothing to be done for 'all-am'. make[5]: Leaving directory '/home/miguel/Downloads/MonetDB/common/utils' make[4]: Leaving directory '/home/miguel/Downloads/MonetDB/common/utils' make[4]: Entering directory '/home/miguel/Downloads/MonetDB/common' make[4]: Nothing to be done for 'all-am'. make[4]: Leaving directory '/home/miguel/Downloads/MonetDB/common' make[3]: Leaving directory '/home/miguel/Downloads/MonetDB/common' make[2]: Leaving directory '/home/miguel/Downloads/MonetDB/common' Making all in clients make[2]: Entering directory '/home/miguel/Downloads/MonetDB/clients' make all-recursive make[3]: Entering directory '/home/miguel/Downloads/MonetDB/clients' Making all in mapilib make[4]: Entering directory '/home/miguel/Downloads/MonetDB/clients/mapilib' make all-am make[5]: Entering directory '/home/miguel/Downloads/MonetDB/clients/mapilib' make[5]: Nothing to be done for 'all-am'. make[5]: Leaving directory '/home/miguel/Downloads/MonetDB/clients/mapilib' make[4]: Leaving directory '/home/miguel/Downloads/MonetDB/clients/mapilib' Making all in mapiclient make[4]: Entering directory '/home/miguel/Downloads/MonetDB/clients/mapiclient' make all-am make[5]: Entering directory '/home/miguel/Downloads/MonetDB/clients/mapiclient' /bin/bash ../../libtool --tag=CC --mode=link gcc -Wall -Wextra -g -pedantic -Wno-long-long -Wno-declaration-after-statement -D_REENTRANT -export-dynamic -o mclient mclient.o ReadlineTools.o libmcutil.la ../mapilib/libmapi.la ../../common/stream/libstream.la -lcrypt -pthread libtool: link: gcc -Wall -Wextra -g -pedantic -Wno-long-long -Wno-declaration-after-statement -D_REENTRANT -o .libs/mclient mclient.o ReadlineTools.o -pthread -Wl,--export-dynamic ./.libs/libmcutil.a ../mapilib/.libs/libmapi.so ../../common/stream/.libs/libstream.so -lcrypt -pthread -Wl,-rpath -Wl,/usr/local/monet/lib ../mapilib/.libs/libmapi.so: undefined reference to `SHA256' ../mapilib/.libs/libmapi.so: undefined reference to `SHA384' ../mapilib/.libs/libmapi.so: undefined reference to `RIPEMD160' ../mapilib/.libs/libmapi.so: undefined reference to `MD5' ../mapilib/.libs/libmapi.so: undefined reference to `SHA1' ../mapilib/.libs/libmapi.so: undefined reference to `SHA512' ../mapilib/.libs/libmapi.so: undefined reference to `SHA224' collect2: error: ld returned 1 exit status Makefile:666: recipe for target 'mclient' failed make[5]: *** [mclient] Error 1 make[5]: Leaving directory '/home/miguel/Downloads/MonetDB/clients/mapiclient' Makefile:568: recipe for target 'all' failed make[4]: *** [all] Error 2 make[4]: Leaving directory '/home/miguel/Downloads/MonetDB/clients/mapiclient' Makefile:764: recipe for target 'all-recursive' failed make[3]: *** [all-recursive] Error 1 make[3]: Leaving directory '/home/miguel/Downloads/MonetDB/clients' Makefile:709: recipe for target 'all' failed make[2]: *** [all] Error 2 make[2]: Leaving directory '/home/miguel/Downloads/MonetDB/clients' Makefile:600: recipe for target 'all-recursive' failed make[1]: *** [all-recursive] Error 1 make[1]: Leaving directory '/home/miguel/Downloads/MonetDB' Makefile:530: recipe for target 'all' failed make: *** [all] Error 2 Please any help will be really appreciated, thanks. Miguel

2 1

apt having trouble installing deb packages
by Clayton A Davis 27 Jan '17

27 Jan '17

Ubuntu seems to be having an issue with the deb packages. On 16.04, it complains about the "Valid-Until" entry in the Release file, and on 16.10 it complains about the "Date" entry and the security. Here are the error messages from 16.04: W: http://dev.monetdb.org/downloads/deb/dists/xenial/Release.gpg: Signature by key 213E64DCD5DDC2C063CC39FD053C3ED40583366F uses weak digest algorithm (SHA1) W: Invalid 'Date' entry in Release file /var/lib/apt/lists/dev.monetdb.org_downloads_deb_dists_xenial_Release W: http://www.openprinting.org/download/printdriver/debian/dists/lsb3.2/Releas…: Signature by key F8897B6F00075648E248B7EC24CBF5474CFD1E2F uses weak digest algorithm (SHA1) E: Failed to fetch http://dev.monetdb.org/downloads/deb/dists/xenial/Release Invalid 'Valid-Until' entry in Release file /var/lib/apt/lists/dev.monetdb.org_downloads_deb_dists_xenial_Release And from 16.10: W: http://dev.monetdb.org/downloads/deb/dists/yakkety/Release.gpg: Signature by key 213E64DCD5DDC2C063CC39FD053C3ED40583366F uses weak digest algorithm (SHA1) W: Invalid 'Date' entry in Release file /var/lib/apt/lists/partial/dev.monetdb.org_downloads_deb_dists_yakkety_Release E: The repository 'http://dev.monetdb.org/downloads/deb yakkety Release' provides only weak security information. N: Updating from such a repository can't be done securely, and is therefore disabled by default. N: See apt-secure(8) manpage for repository creation and user configuration details. I know the workarounds to get it working on my machine, I just wanted to put this issue out there in case it started with the recent release. -CAD

1 0

Announcement: New Dec2016-SP1 Bugfix release of MonetDB suite
by Panagiotis Koutsourakis 26 Jan '17

26 Jan '17

The MonetDB team at CWI/MonetDB BV is pleased to announce the Dec2016-SP1 bugfix release of the MonetDB suite of programs. More information about MonetDB can be found on our website at <http://www.monetdb.org/>. For details on this release, please see the release notes at <http://www.monetdb.org/Downloads/ReleaseNotes>. As usual, the download location is <http://dev.monetdb.org/downloads/>. Dec 2016-SP1 bugfix release Bug Fixes * 4039: Slow mserver5 start after drop of tables (> 1 hour) * 4048: Segfault on vacuum with parallel updates * 6079: pushselect optimizer bug on MAL snippet * 6140: INNER JOIN gives the results of a CROSS JOIN * 6150: Query giving wrong results, extra records are appearing * 6175: The program can't start because python27.dll is missing from your computer. * 6178: AVG + GROUP BY returns NULL for some records that should have results * 6179: mergetable optimizer messes up sample * 6182: sys.shutdown triggers assertion in clients.c * 6184: Incorrect result set - Extra records in result set

1 0

Backup Strategies
by Vincent Sheffer 25 Jan '17

25 Jan '17

I have been attempting to create a viable period backup strategy for MonetDB and have had little success to date. Our database is way too big to use msqldump, so our only option is the one outlined here: https://www.monetdb.org/Documentation/UserGuide/FastDumpRestore. I follow those steps and use rsync to do an incremental backup of the dbfarm to another device (from fast SSD to RAID5 array). That works fine, until it doesn’t, in which case the back up gets corrupted somehow and MonetDB won’t start from the backup. That is the state I am currently in. This is the error that I am getting: !FATAL: logger_load: BBPrename to sql_snapshots_bid failed Any help on 1. Fixing the existing backup, and/or 2. Helping me understand a better way to do period, incremental backups. Thanks, Vince The information transmitted, including any attachments, is intended only for the individual or entity to which it is addressed, and may contain confidential and/or privileged information. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by individuals or entities other than the intended recipient is prohibited, and all liability arising therefrom is disclaimed. If you have received this communication in error, please delete the information from any computer and notify the sender.

4 8

Database crash on executing a big insert
by Shrihari Shastry 25 Jan '17

25 Jan '17

Hi, I' trying to perform a big insert of about 4 mil rows of data: insert into clustertable1(keyword, p0,p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11,p12,p13,p14,p15,p16,p17,p18,p19,p20,p21,p22,p23,p24,p25,p26,p27,p28,p29,p30,p31,p32,p33,p34,p35,p36,p37,p38,p39,p40,p41,p42,p43,p44,p45,p46,p47,p48,p49,p50) select keyword,p0,p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11,p12,p13,p14,p15,p16,p17,p18,p19,p20,p21,p22,p23,p24,p25,p26,p27,p28,p29,p30,p31,p32,p33,p34,p35,p36,p37,p38,p39,p40,p41,p42,p43,p44,p45,p46,p47,p48,p49,p50 from cluster_submaster where cluster_no = '0'; But the moment I fire this query, the server crashes with the below printed in the logs: 2017-01-25 10:05:11 MSG kwresearch[3418]: # Visit http://www.monetdb.org/ for further information 2017-01-25 10:05:11 MSG kwresearch[3418]: # Listening for UNIX domain connection requests on mapi:monetdb:///monetDB/myfarm/kwresearch/.mapi.sock 2017-01-25 10:05:11 MSG kwresearch[3418]: # MonetDB/SQL module loaded 2017-01-25 10:07:06 MSG merovingian[3201]: proxying client (local) for database 'kwresearch' to mapi:monetdb:///monetDB/myfarm/kwresearch/.mapi.sock?database=kwresearch 2017-01-25 10:07:06 MSG merovingian[3201]: target connection is on local UNIX domain socket, passing on filedescriptor instead of proxying 2017-01-25 10:07:06 MSG merovingian[3201]: proxying client (local) for database 'kwresearch' to mapi:monetdb:///monetDB/myfarm/kwresearch/.mapi.sock?database=kwresearch 2017-01-25 10:07:06 MSG merovingian[3201]: target connection is on local UNIX domain socket, passing on filedescriptor instead of proxying 2017-01-25 10:07:12 MSG merovingian[3201]: database 'kwresearch' (3418) was killed by signal SIGSEGV Appreciate any help! Thanks, Shrihari ******************* iquanti.com E-Mail Domain LEGAL DISCLAIMER ******************* This E-Mail may contain confidential and/or legally privileged Information and is meant for the intended recipient(s) only. If you have received this e-mail in error and are not the intended recipient/s, kindly notify us at it(a)iquanti.com and then delete this e-mail immediately from your system. You are also hereby notified that any use, any form of reproduction, dissemination, copying, disclosure, modification, distribution and/or publication of this e-mail, its contents or its attachment/s other than by its intended recipient/s is strictly prohibited and may be unlawful.

2 1

bool from aggregate
by Stefano Fioravanzo 25 Jan '17

25 Jan '17

Hello, I would like to know if there is a way to replicate the behavior of the bool_and function of Postgresql. An example of its use is the following: SELECT bool_and(A) FROM mytable GROUP BY B; bool_and will return true if all values in the group are true, false otherwise. I would like to do the same thing in monetdb. Thanks Stefano

2 1

Case insensitive version of the IN operator?
by Kevork Vartanian 13 Jan '17

13 Jan '17

Hi, I was wondering if there is a case insensitive version of the IN operator, similar to ILIKE. Or is the IN itself case insensitive? It's mainly to avoid using nested OR's and ILIKE's if a case insensitive IN exists, to make the queries much simpler. Also, I don't want to use functions like lower as it's a big data set and the use of functions may make the queries even slower. Please advise. Thank you. Regards, Kevork Vartanian Technical Solutions Consultant kevork.vartanian(a)innotta.com.au | +61 406 776 789 | www.innotta.com.au

3 2

mclient COPY INFO Local files to a remote MonetDB Server Error
by ping gao 高平(0) 11 Jan '17

11 Jan '17

Hi ALL mclient COPY INFO Local files to a remote MonetDB Server Error. Mclient how to copy a local file to a remote MonetDB Server? Known to use: mclient COPY INFO - < file ; Will appear: syntax error, unexpected sqlINT in: Error Error Info :could not open file ‘xxxxx’ : No such file or directory

3 4

COPY INFO ERROR
by ping gao 高平(0) 10 Jan '17

10 Jan '17

Hi ALL I encountered a problem using a COPY INFO. Strange thing is my IP is 59, execute the following statement is not a problem on the machine.So freak! IP:192.168.110.59 (Database Server) [hotel-revenue-online@bjs-hotel-RevenueCompute-02 data_visual_monetdb]$ mclient -u monetdb -d hotel-revenue -s "COPY 2 RECORDS INTO hotel_revenue_octopus_booking_order FROM '/home/hotel-revenue-online/ping.gao/data_visual_monetdb/hotel_revenue_octopus_booking_order.sql' USING DELIMITERS ',','\\n'<smb://n'>" password: 2 affected rows IP:192.168.110.179 Error 1: [hotel-revenue-online@bjs-hotel-RevenueCompute-01 datavisual_service]$ mclient -h 192.168.110.59 -p 50001 -d hotel-revenue -s "COPY 2 RECORDS INTO hotel_revenue_octopus_hotel_info FROM /home/hotel-revenue-online/datavisual_service/data_tmp/hotel_revenue_octopus_hotel_info.data USING DELIMITERS '\\001','\\n'<smb://001','//n'>” syntax error, unexpected '/', expecting STRING or '(' or STDIN in: "copy 2 records into hotel_revenue_octopus_hotel_info from /“ Error 2: [hotel-revenue-online@bjs-hotel-RevenueCompute-01 datavisual_service]$ mclient -h 192.168.110.59 -p 50001 -d hotel-revenue -s "COPY 2 RECORDS INTO hotel_revenue_octopus_hotel_info FROM '/home/hotel-revenue-online/datavisual_service/data_tmp/hotel_revenue_octopus_hotel_info.data' USING DELIMITERS '\\001<smb://001>','\\n'<smb://n'>" could not open file '/home/hotel-revenue-online/datavisual_service/data_tmp/hotel_revenue_octopus_hotel_info.data': No such file or directory Error 3: [hotel-revenue-online@bjs-hotel-RevenueCompute-01 datavisual_service]$ mclient -h 192.168.110.59 -p 50001 -d hotel-revenue -s "COPY 2 RECORDS INTO hotel_revenue_octopus_hotel_info FROM STDIN DELIMITERS '\\001','\\n'<smb://001','//n'>" - < /home/hotel-revenue-online/datavisual_service/data_tmp/hotel_revenue_octopus_hotel_info.data 2 affected rows syntax error, unexpected sqlINT in: "90564318"

2 2

RE: COPY INFO ERROR
by Stefan Manegold 10 Jan '17

10 Jan '17

Not strange at all. Quotes are missing around file path (string). Sent from my Samsung Galaxy smartphone. -------- Original message --------From: "ping gao 高平(0)" <ping.gao(a)corp.elong.com> Date: 1/10/17 08:07 (GMT+01:00) To: Communication channel for MonetDB users <users-list(a)monetdb.org> Subject: COPY INFO ERROR Hi ALL I encountered a problem using a COPY INFO. Strange thing is my IP is 59, execute the following statement is not a problem on the machine.So freak! IP:192.168.110.59 (Database Server) [hotel-revenue-online@bjs-hotel-RevenueCompute-02 data_visual_monetdb]$ mclient -u monetdb -d hotel-revenue -s "COPY 2 RECORDS INTO hotel_revenue_octopus_booking_order FROM '/home/hotel-revenue-online/ping.gao/data_visual_monetdb/hotel_revenue_octopus_booking_order.sql' USING DELIMITERS ',','\\n'" password: 2 affected rows IP:192.168.110.179 Error 1: [hotel-revenue-online@bjs-hotel-RevenueCompute-01 datavisual_service]$ mclient -h 192.168.110.59 -p 50001 -d hotel-revenue -s "COPY 2 RECORDS INTO hotel_revenue_octopus_hotel_info FROM /home/hotel-revenue-online/datavisual_service/data_tmp/hotel_revenue_octopus_hotel_info.data USING DELIMITERS '\\001','\\n'” syntax error, unexpected '/', expecting STRING or '(' or STDIN in: "copy 2 records into hotel_revenue_octopus_hotel_info from /“ Error 2: [hotel-revenue-online@bjs-hotel-RevenueCompute-01 datavisual_service]$ mclient -h 192.168.110.59 -p 50001 -d hotel-revenue -s "COPY 2 RECORDS INTO hotel_revenue_octopus_hotel_info FROM '/home/hotel-revenue-online/datavisual_service/data_tmp/hotel_revenue_octopus_hotel_info.data' USING DELIMITERS '\\001','\\n'" could not open file '/home/hotel-revenue-online/datavisual_service/data_tmp/hotel_revenue_octopus_hotel_info.data': No such file or directory Error 3: [hotel-revenue-online@bjs-hotel-RevenueCompute-01 datavisual_service]$ mclient -h 192.168.110.59 -p 50001 -d hotel-revenue -s "COPY 2 RECORDS INTO hotel_revenue_octopus_hotel_info FROM STDIN DELIMITERS '\\001','\\n'" - < /home/hotel-revenue-online/datavisual_service/data_tmp/hotel_revenue_octopus_hotel_info.data 2 affected rows syntax error, unexpected sqlINT in: "90564318"

2 1

MAL function syntax
by Moritz Bruder 09 Jan '17

09 Jan '17

Hi, I'm having problems with the exact syntax of MAL functions. I used the syntax from the documentation, which is working for some functions, but my hand-written example doesn't work (neither when pasted in the REPL, nor as input file): "^<identifier> expected" at the end of every line. Any idea what could cause this? (I'm using a fairly recent development version.) Regards, Moritz

2 1

C UDF
by imad hajj chahine 06 Jan '17

06 Jan '17

Hi, After reviewing all the other alternatives like SQL and Python UDF, I was either stuck on performance with SQL UDF or on usability with Python UDF (unable to use with aggregation, and not such great performance with dates), so I decided to go the hard way with C functions, as a bonus it will give me the possibility to change the functionalities without worrying about dependencies, which was not the case in other languages. The purpose is to create a set of formatting functions for Year, Quarter, Month, Week and Day brackets, and of course i need to create the bulk version of each function for performance. Starting from the MTIMEdate_extract_year_bulk, now i have the simple function working, and successfully calling it from mclient: *str* *UDFyearbracket(str *ret, const date *v)* *{* * if (*v == date_nil) {* * *ret = GDKstrdup(str_nil);* * } else {* * int year;* * fromdate(*v, NULL, NULL, &year);* * *ret = (str) GDKmalloc(15);* * sprintf(*ret, "%d", year);* * }* * return MAL_SUCCEED;* *}* For the bulk version i get an error in the log: gdk_atoms.c:1345: strPut: Assertion `(v[i] & 0x80) == 0' failed. *str* *UDFBATyearbracket(bat *ret, const bat *bid)* *{* * BAT *b, *bn;* * BUN i,n;* * str *y;* * const date *t;* * if ((b = BATdescriptor(*bid)) == NULL)* * throw(MAL, "UDF.BATyearbracket", "Cannot access descriptor");* * n = BATcount(b);* * bn = COLnew(b->hseqbase, TYPE_str, BATcount(b), TRANSIENT);* * if (bn == NULL) {* * BBPunfix(b->batCacheid);* * throw(MAL, "UDF.BATyearbracket", "memory allocation failure");* * }* * bn->tnonil = 1;* * bn->tnil = 0;* * t = (const date *) Tloc(b, 0);* * y = (str *) Tloc(bn, 0);* * for (i = 0; i < n; i++) {* * if (*t == date_nil) {* * *y = GDKstrdup(str_nil);* * } else* * UDFyearbracket(y, t);* * if (strcmp(*y, str_nil) == 0) {* * bn->tnonil = 0;* * bn->tnil = 1;* * }* * y++;* * t++;* * }* * BATsetcount(bn, (BUN) (y - (str *) Tloc(bn, 0)));* * bn->tsorted = BATcount(bn)<2;* * bn->trevsorted = BATcount(bn)<2;* * BBPkeepref(*ret = bn->batCacheid);* * BBPunfix(b->batCacheid);* * return MAL_SUCCEED;* *}* PS: I am not a c expert but i can find my way with basic operations and pointers. Any help or suggestions is appreciated. Thank you.

4 17

Upgraded to lastest MonetDB, now get error
by Bryan Senseman 04 Jan '17

04 Jan '17

Ok, I upgraded my Windows version of MonetDB to Dec2016 from Jun2016-SP1 and I get this error. !FATAL: BBPinit: head column must be VOID (ID = 3). I was able to successfully downgrade (uninstall/reinstall) Jun2016-SP1; do I need to install SP2 first? -- ------------------------------ This transmission is confidential and intended solely for the use of the recipient named above. It may contain confidential, proprietary, or legally privileged information. If you are not the intended recipient, you are hereby notified that any unauthorized review, use, disclosure or distribution is strictly prohibited. If you have received this transmission in error, please contact the sender by reply e-mail and delete the original transmission and all copies from your system.

1 0

COPY INFO Exception
by ping gao 高平(0) 03 Jan '17

03 Jan '17

Hi ALL I encountered a problem using a COPY INFO The command to execute: [hotel@02 data_visual_monetdb]$ mclient -u monetdb -d hotel-revenue -s "COPY 1727300 RECORDS INTO hotel_revenue_octopus_hotel_info FROM '/home/ping.gao/data_visual_monetdbinfo.data' USING DELIMITERS ',','\\n'<smb://n'>,'\"'" password: Failed to import table Leftover data '"0”' Data Format(Line separator —> \n): “test”,”test”,”test”,”test”

2 2

File System Backups
by Vincent Sheffer 30 Dec '16

30 Dec '16

I’m trying to do automated incremental backups following these instructions: https://www.monetdb.org/Documentation/UserGuide/FastDumpRestore My question is whether why isn’t it enough to just stop the DB? Is the waiting for open sessions a courtesy or is it required that no statements are executing prior to shutdown? Is it similar for the shutdown command? It gives people in a session time to exit or something? Again, these are to be automated at a time of night that queries should not be being executed. Bottom line question: is it enough to stop and lock a database to ensure the binary backup will have no errors that prevent the backup from being restored or are the other steps required? Thanks, Vince The information transmitted, including any attachments, is intended only for the individual or entity to which it is addressed, and may contain confidential and/or privileged information. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by individuals or entities other than the intended recipient is prohibited, and all liability arising therefrom is disclaimed. If you have received this communication in error, please delete the information from any computer and notify the sender.

2 1

table renaming
by Stefano Fioravanzo 30 Dec '16

30 Dec '16

Hello, In this blog post https://www.monetdb.org/blog/monetdb-autumn-2016-feature-release-outlook <https://www.monetdb.org/blog/monetdb-autumn-2016-feature-release-outlook> it was mentioned the possibility of renaming tables. It is possible with the new Dec2016 release? Thank you Stefano

2 1

UDF performance issue
by imad hajj chahine 30 Dec '16

30 Dec '16

Hi, I have a weird behavior when i package some sql code in a function, the performance deteriorate. The same code running directly in select statement is blinking fast. ex: *select date_to_str("Expiry_Date",'%Y-%m-%d') from table*: exec time is 0.72s if i create a function: *CREATE FUNCTION DayBracket(pdate date) returns char(10)* *BEGIN* * return date_to_str(pdate,'%Y-%m-%d');* *end;* *grant execute on function DayBracket to public;* and execute *select DayBracket("Expiry_Date") from table*: exec time is 24s even when i set the limit to 1 its taking the same time, so i guess it should be something related to loading the function. The following function perform much better: *CREATE FUNCTION DayBracketOpt(pdate date) returns char(10)* *BEGIN* * return extract(year from pdate) || '-' || lpad(extract(month from pdate),2,'0') || '-' || lpad(dayofmonth(pdate),2,'0');* *end;* *grant execute on function DayBracketOpt to public;* I also tried to create a Python function: *CREATE FUNCTION pyDayBracket(pdate date) returns string* *LANGUAGE PYTHON {* * from time import strftime, strptime* * return [strftime("%Y-%m-%d", strptime(pdt,"%Y-%m-%d")) for pdt in pdate]* *};* it is taking too long and throwing an exception:buffer size mismatch PS: the pdate array type is detected in pythons as string not as date; Also how to delete a function with dependency, I need to replace some functions which are used in others functions and views. Any flags to turn off dependency check before dropping? Thank you.

4 11

Merry Christmas and Happy 2017
by Ying Zhang 23 Dec '16

23 Dec '16

Dear MonetDB users, The MonetDB team wishes you a very Merry Christmas and Happy New Year! Thank you very much for helping us improving our work! Hope to see you all back in 2017, in which we’re of course going to continue our work, and come up with new MonetDB releases with more features, better performance and improved stability. Best wishes, Ying Zhang (on behalf of the MonetDB team)

2 1

Bug #3878
by Brian Hood 22 Dec '16

22 Dec '16

Hi Guys, I remember this somehow got fixed in the next release or 2 however i am not sure how it got fixed and possibly via a different issue. So it should be closed. https://www.monetdb.org/bugzilla/show_bug.cgi?id=3878 Regards, Brian Hood

1 0

Announcement: New Dec2016 Feature release of MonetDB suite
by Panagiotis Koutsourakis 21 Dec '16

21 Dec '16

The MonetDB team at CWI/MonetDB BV is pleased to announce the Dec2016 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/>. Dec 2016 feature release MonetDB5 Server * Removed the zorder module with functions zorder.encode, zorder.decode_x and zorder.decode_y. * Removed command bbp.getHeat(). * Removed bat.setColumn with two arguments and bat.setRole. Use bat.setColumn with one argument instead. * Removed function BKCappend_reverse_val_wrap: it was unused. * The "wrd" type has been removed from GDK and MAL. The type was defined to be a 32 bit integer on 32 bit architectures and a 64 bit integer on 64 bit architectures. We now generally use "lng" (always 64 bits) where "wrd" was used. * Keep a collection of full traces. Each time the SQL user applies the TRACE option, the full json trace is retained within the <dbpath>/<dbname>/sql_traces Build Environment * New packages MonetDB-python2 (Fedora) and monetdb-python2 (Debian/Ubuntu) have been created for Python 2 integration into MonetDB. * With OID size equal to ABI/word size, mserver5 does not need to print the OID size, anymore. * Removed obsolete code associated with long gone static linking option. * Removed configure option --enable-oid32 to compile with 32 bit OIDs on a 64 bit architecture. . * The Perl, PHP, and Python clients, and the JDBC driver each now have their own repositories and release cycles. The Python client is maintained by Gijs Molenaar on Github (https://github.com/gijzelaerr/pymonetdb) the other clients are maintained by CWI/MonetDB on our own server (https://dev.monetdb.org/hg/monetdb-java, https://dev.monetdb.org/hg/monetdb-perl, https://dev.monetdb.org/hg/monetdb-php) MonetDB Common * The tnokey values must now be 0 if it is not known whether all values in a column are distinct. * The 2-bit tkey field in the bat descriptor has been split into two single bit fields: tkey and tunique. The old tkey&BOUND2BTRUE value is now stored in tunique. * Implemented conversion to str from any type (not just the internal types). * VALcopy and VALinit both return their first argument on success or (and that's new) NULL on (allocation) failure. * BATattach now can also create a str BAT from a file consisting of null-terminated strings. The input file must be encoded using UTF-8. * BATattach now copies the input file instead of "stealing" it. * Removed the lastused "timestamp" from the BBP. * Removed batStamp field from BAT descriptor, and removed the BBPcurstamp function. * Removed unused functions BBPhot and BBPcold. * Removed BATderiveTailProps and BATderiveProps. Just set the properties you know about, or use BATsettrivprop. * Removed the macro BUNfirst. It can be replaced by 0. * Changed BATroles by removing the argument to set the name of the head column. * The head column is now completely gone. MonetDB is completely "headless". * The format of the BBP.dir file was simplified. Since the head column is VOID, the only value that needs to be stored is the head seqbase. * BATs now only have a single (logical) name. * The function BATmirror is gone. The HEAD column is always VOID (with a non-nil seqbase) and the TAIL column carries the data. All functions that deal with data work on the TAIL column. * BATkey now works on the TAIL column instead of the HEAD column. * Replaced BATseqbase with BAThseqbase and BATtseqbase, the former for setting the seqbase on the HEAD, the latter for setting the seqbase on the TAIL. * Replaced function BATnew with COLnew with slightly different arguments: the first argument of COLnew is the SEQBASE of the head column (which is always VOID). * The "wrd" type has been removed from GDK and MAL. The type was defined to be a 32 bit integer on 32 bit architectures and a 64 bit integer on 64 bit architectures. We now generally use "lng" (always 64 bits) where "wrd" was used. SQL * Removed functions sys.zorder_encode, sys.zorder_decode_x, and sys.zorder_decode_y. * The experimental recycler code is moved to the attic. * The syntax of bat.new(:oid,:any) has been changed by dropping the superflous :oid. All BATs are now binary associations between a void column and a materialized value column. (except for the internal :bat[:void,:void] representation of simple oid ranged tails.) Bug Fixes * 3357: Implement setQueryTimeout() * 3445: Add support for database name to dotmonetdb file * 3597: SQL to MAL listing looses types * 3973: JDBC hangs * 3976: Performance enhancement to LIKE without wildcards * 4005: Correlated update causes incorrect null constraint violation * 4016: merge table only optimises for point query * 4040: sys.storage call can take a long time * 4047: Segfault when updating a dropped table * 4050: Database corruption when running low on inode * 4057: missing bulk operations between constant and bat * 4061: SIGSEGV in candscan_lng * 4066: Deadlocked monetdbd * 6068: Error message about incompatible BBP version should be clearer * 6069: query with union all silently crashes * 6070: setting negative session query timeout should not be possible/allowed * 6071: where clause with cast and floor fails to sigsegv * 6072: Bind to UPD delta column does not get/show type information in EXPLAIN * 6073: Missing type information for constants in MAL explain * 6074: SET ROLE command does not work * 6075: gdk_calc.c:13113: BATcalcifthenelse_intern: Assertion `col2 != NULL' failed. * 6076: rel_optimizer.c:5426: rel_push_project_up: Assertion `e' failed. * 6077: mserver5: rel_optimizer.c:5444: rel_push_project_up: Assertion `e' failed. * 6078: rel_bin.c:2402: rel2bin_project: Assertion `0' failed. * 6084: Merge table point to wrong columns if columns in partition tables are deleted * 6108: monetdb5-sql sysv init script does not wait for shutdown * 6114: segfault raised in the query rewriter due to a null pointer * 6115: Assertion hit in the codegen * 6116: Codegen does not support certain kind of selects on scalar subqueries * 6117: Assertion hit in the query rewriting stage during the push down phase * 6118: SIGSEGV in strPut due to shared heap * 6119: Assertion hit in the MAL optimiser on a complex query * 6120: QUANTILE() treats NULL as if it is zero * 6121: SELECT a.col IN ( b.col FROM b ) FROM a statements with no error but no result * 6123: Assertion hit in the codegen #2 * 6124: CASE <column> WHEN NULL THEN 0 ELSE 1 END returns wrong result * 6125: Stack overflow in the query rewriter with a query having an OR condition and a nested SELECT subexpression * 6126: batcalc.== can't handle void BATs * 6139: Debian libmonetdb13 conflicts with libmonetdb5-server-geom

1 0

Returning empty results from an embedded python table function.
by Joseph D'silva 20 Dec '16

20 Dec '16

Hi All, I am trying to return an "empty" result set from an embedded python function (as in some situations I will not have valid rows to produce in the output). However the very many attempts of trying to return an "empty" result from the function have failed (as is documented in the example below). How do you get it done ? Thanks. DROP FUNCTION p_results_exp01; CREATE FUNCTION p_results_exp01() RETURNS TABLE(val1 STRING, val2 INTEGER) LANGUAGE PYTHON { #Unsupported result object. Expected either a list, dictionary, a numpy array, a numpy masked array or a pandas data frame, but received an object of type "<class 'NoneType'>" #return None; #Errors #Expected a return value with name "val1", but this key was not present in the dictionary. #return {}; #Errors #Error converting dict return value "val1": MALException:pyapi.eval:Unsupported result object. Expected either a list, dictionary, a numpy array, a numpy masked array or a pandas data frame, but received an object of type "<class 'NoneType'>". #return {'val1':None, 'val2':None}; #Errors #Error converting dict return value "val1": MALException:pyapi.eval:An array of size 0 was returned, yet we expect a list of 1 columns. The result is invalid.. #return {'val1':[], 'val2':[]}; #return {'val1':['value_1'], 'val2':[1]}; #Works, as it has value. }; SELECT * FROM p_results_exp01(); Joseph

2 3

Python integration
by Imad Hajj Chahine 17 Dec '16

17 Dec '16

Hi, I need to calculate weighted quartile from a list of values and their counts, and all solutions that i found revolves around calculating the cumulative count. Since monetdb lacks the sum over window function, I want to create an aggregate function in python to return the weighted quartile after computing the cumulative count. As I understand there is 3 branches: release, candidate and dev. Currently the release version is june2016, candidate is dec 2016, dev is the default. Is the python integration only supported on default branch? Any plan to move it to stable branch? Thank you.

3 3

SUM() OVER (ORDER BY..)
by Anthony Damico 13 Dec '16

13 Dec '16

hi, monetdb does not support SUM() OVER commands.. does anyone have a smart alternative to implement this? thanks SELECT batch_id, job_count, SUM(job_count) OVER (ORDER BY duration) as cumjobs, SUM(job_count) over () as totjobs, duration FROM test_data ;

5 13

min() vs median() for string columns
by Eyal Rozenberg 13 Dec '16

13 Dec '16

When I try to get the min() or the max() of a string column (whose type is CHAR(2) ) - that works; but when I try to get the median() - I get an error about not being able to convert a value (say, 'AA') to type bte. Is this intended behavior? A bug? Perhaps something I might be doing wrong? PS - In case it matters: * I'm using v11.23.7 * I'm using the USDT on-time flight data, in a DB you can setup using the script here: https://github.com/eyalroz/usdt-ontime-tools

1 0

MonetDB unpredictable performance
by Kevork Vartanian 08 Dec '16

08 Dec '16

Hi, I have a few questions and hopefully someone will be able to answer me and put me in the right direction. - When we're having performance issues, the only way we can resolve them, is by dumping the data again, sorted by datetime, and after that the queries are fast. When we do this, the table enables sorting on the datetime column and that's why the queries are fast as there won't be a full table scan. As soon as we insert new data (always in the same sorted order), firstly the table is losing the sorting and secondly the queries become extremely slow - We find it very unpredictable, and somehow not very deterministic, especially when it comes to indexing, sorting and log file consumption. We can't control those, it seems - The read ahead log is growing uncontrollably and we don't really know when it's committing - When running the queries (after the table loses the sorting), we can see it's writing too much data (storage usage grows significantly when query is still running) and that's most probably because it is doing a full table scan - Even if we restart the engine, it takes a while for it to be back, as it recommits the read ahead log which takes time, and it doesn't make a difference with the sorting and therefore the queries are still slow even after a restart Is there a way we can further optimise so we don't have these issues? We have more than 600 million entries and when the datetime field is sorted, the queries are quick. The only way we can force the sorting is when we dump the data again, but we can't really do that all the time and we need to insert new data at least once every night. It's an Ubuntu 14.04.4 LTS instance on AWS with 2 CPU's and 16 GB of RAM (and SSD drives). Thank you. Regards, Kevork Vartanian Technical Solutions Consultant kevork.vartanian(a)innotta.com.au | +61 406 776 789 | www.innotta.com.au

2 1

null value from python function
by Stefano Fioravanzo 05 Dec '16

05 Dec '16

Hello, Some months ago I reported a problem when returning null values from an embedded py function (may they be inside arrays or data frames). Indeed, the null values cannot be queried using sql like: SELECT * FROM t WHERE v IS NULL the above query does not return any result, but selecting the row mclient presents a ‘null’ values in the cell. I am using a development build of a couple of months ago, I would like to know if this issue has been resolved or not. Thank you Stefano

2 1

Database dropping connections frequently with proxying.
by Joseph D'silva 01 Dec '16

01 Dec '16

Running on Ubuntu 16.04 LTS, database and clients run on the same system. I am using the Dec2016 branch, and noticed that the database connections get dropped randomly, but in high frequency. This error occurs irrespective for whether I use mclient, python package (pymonetdb) or the JDBC driver from the site. >From what I could figure if I use the forward=redirect option instead of the default proxy, the frequency reduces drastically, but is still present (if I leave the mclient connection idle for like 5 minutes it will drop - is there an idle time out somewhere ? Also is there a way to force a network connection locally instead of unix socket ? ). Sample of some messages For the default proxy option =========================== >From merovingian.log 2016-12-01 10:59:33 MSG merovingian[29445]: target connection is on local UNIX domain socket, passing on filedescriptor instead of proxying Python error due to dropped connection. >>> rows=cursor.execute("SELECT 'HELLO';"); Traceback (most recent call last): File "<stdin>", line 1, in <module> File "/usr/local/lib/python3.5/dist-packages/pymonetdb/sql/cursors.py", line 164, in execute block = self.connection.execute(query) File "/usr/local/lib/python3.5/dist-packages/pymonetdb/sql/connections.py", line 133, in execute return self.command('s' + query + ';') File "/usr/local/lib/python3.5/dist-packages/pymonetdb/sql/connections.py", line 138, in command return self.mapi.cmd(command) File "/usr/local/lib/python3.5/dist-packages/pymonetdb/mapi.py", line 208, in cmd self._putblock(operation) File "/usr/local/lib/python3.5/dist-packages/pymonetdb/mapi.py", line 310, in _putblock self._putblock_inet(block) File "/usr/local/lib/python3.5/dist-packages/pymonetdb/mapi.py", line 321, in _putblock_inet self.socket.send(flag) BrokenPipeError: [Errno 32] Broken pipe JDBC Error (I am using PyCharm to execute some SQLs) [08000] Connection to server lost! (mserver still alive?) On the other hand, mclient does not show any error message, tried -X option, but no message when it dies. It just dies while executing a command (probably because the connection is dropped) A peek at netstat shows that the connections disappear from it before mclient dies. $ netstat -anp | grep 50000 tcp 0 0 127.0.0.1:50000 0.0.0.0:* LISTEN 29445/monetdbd udp 0 0 127.0.0.1:50000 0.0.0.0:* 29445/monetdbd unix 2 [ ACC ] STREAM LISTENING 3869978213 <(386)%20997-8213> 29445/monetdbd /tmp/.s.monetdb.50000 unix 2 [ ACC ] STREAM LISTENING 3869978215 <(386)%20997-8215> 29445/monetdbd /tmp/.s.merovingian.50000 unix 3 [ ] STREAM CONNECTED 3875263615 29486/mserver5 /tmp/.s.monetdb.50000 unix 2 [ ] STREAM CONNECTED 3871676942 29486/mserver5 /tmp/.s.monetdb.50000 $ netstat -anp | grep 50000 tcp 0 0 127.0.0.1:50000 0.0.0.0:* LISTEN 29445/monetdbd udp 0 0 127.0.0.1:50000 0.0.0.0:* 29445/monetdbd unix 2 [ ACC ] STREAM LISTENING 3869978213 <(386)%20997-8213> 29445/monetdbd /tmp/.s.monetdb.50000 unix 2 [ ACC ] STREAM LISTENING 3869978215 <(386)%20997-8215> 29445/monetdbd /tmp/.s.merovingian.50000 unix 2 [ ] STREAM CONNECTED 3871676942 29486/mserver5 /tmp/.s.monetdb.50000 After switching to the forward option (where this issue is less frequent to be bothersome) =========================== >From merovingian.log 2016-12-01 11:22:59 MSG merovingian[29824]: redirecting client (local) for database 'voc' to mapi:monetdb://cerberus:50001/voc Although for most part the connections are stable, if and when mclient dies, I do notice that the netstat entries are NOT being removed. (see the entries piling up at the bottom though there are no active client processes) $ netstat -anp | grep 50000 (Not all processes could be identified, non-owned process info will not be shown, you would have to be root to see it all.) tcp 0 0 127.0.0.1:50000 0.0.0.0:* LISTEN 29824/monetdbd tcp 1 0 127.0.0.1:50000 127.0.0.1:35870 CLOSE_WAIT 29824/monetdbd udp 0 0 127.0.0.1:50000 0.0.0.0:* 29824/monetdbd unix 2 [ ACC ] STREAM LISTENING 3876967185 29824/monetdbd /tmp/.s.monetdb.50000 unix 2 [ ACC ] STREAM LISTENING 3876967187 29824/monetdbd /tmp/.s.merovingian.50000 unix 2 [ ] STREAM CONNECTED 3877116313 29824/monetdbd /tmp/.s.monetdb.50000 unix 2 [ ] STREAM CONNECTED 3880819958 29824/monetdbd /tmp/.s.monetdb.50000 unix 2 [ ] STREAM CONNECTED 3878012589 29824/monetdbd /tmp/.s.monetdb.50000 unix 2 [ ] STREAM CONNECTED 3877885158 29824/monetdbd /tmp/.s.monetdb.50000 Thanks, Joseph

1 0

Building monetdb with Python 3
by Joseph D'silva 01 Dec '16

01 Dec '16

Hi all, Has anyone had any luck building the sources with Python 3 ? I tried building the Dec2016 branch from git, passing --with-python3=/usr/bin/python3 --with-pyversion=/usr/bin/python3 --with-pyconfig=/usr/bin/python3-config to configure, but received many errors during make under the pyapi directory, such as. /usr/include/python3.5m/object.h:86:5: error: missing braces around initializer [-Werror=missing-braces] { _PyObject_EXTRA_INIT \ ^ connection.c:159:5: note: in expansion of macro ‘PyObject_HEAD_INIT’ PyObject_HEAD_INIT(NULL) ^ /usr/include/python3.5m/object.h:86:5: note: (near initialization for ‘Py_ConnectionType.ob_base’) { _PyObject_EXTRA_INIT \ ^ connection.c:159:5: note: in expansion of macro ‘PyObject_HEAD_INIT’ PyObject_HEAD_INIT(NULL) ^ connection.c:159:5: error: missing initializer for field ‘ob_size’ of ‘PyVarObject {aka struct <anonymous>}’ [-Werror=missing-field-initializers] ................. connection.c:246:5: error: ‘return’ with a value, in function returning void [-Werror] import_array(); ^ It looks like some of the macro definitions to make python3 header files work with the pyapi source might be missing. I tried fixing some of the sources like unicode.c, formatinput.c etc. But it looks like majority of the source files have issues. Thanks, Joseph

2 2

MonetDB and IN search
by blacky_2k2＠yahoo.com 29 Nov '16

29 Nov '16

Hello, We’ve been battling for some time with what is fundamentally a simple query. We have a databases with about 15 columns which store int and float values. The query searches in each column with an IN condition , where the possible test values in the IN condition may vary from a 1 to 200 - where these 200 conditions are all the possible values of that column. So normally a query would look something like: SELECT id FROM table WHERE a IN (1,2,3,4) AND b IN (4,5,6,) AND c IN (2,5,6) ...... AND i = 5 etc. The database has about 2 mil rows but will grow to potentially 10 mil rows. A query similar to the one above takes about 80-100 ms. With about 50 test values for each IN. So far, the best results we got with MariaDB and proper indexing using Engine Memory where the above query takes about 2ms. We suspect MonetDB is not optimised for such queries, but since we don’t know much about its workings can you please confirm this? Otherwise, is there a way to improve the above query? Say using multiple JOINs instead of IN ? By the way, we are experimenting with MonetDB out of curiosity, we heard a lot of good things about it. Thank you very much, Silviu

3 4

Improved concurrency version of MonetDB
by Puthick Hok 24 Nov '16

24 Nov '16

Hi, I'm using MonetDB v11.23.13 (Jun2016-SP2). We decided to use MonetDB to store DNA marker data for our plant breeding platform (www.kddart.org) 2 years ago based on MonetDB performance on data retrieval and data update is also good. The use of MonetDB is not purely OLAP. There are lot of aspects of OLTP because people need to load marker data from their genotyping services. We finish integrating MonetDB into the platform and developing more tools for the system. As the platform is getting more mature, we started to do concurrency testing against the system including the part that is relying on MonetDB. Naively, I ran into a concurrency problem described in a master thesis by Rene Tassy (Improving Transactional Scalability in MonetDB). The work done in the thesis is very promising and I would like to thank you all for that. My question in relation to how I would deal with my problem is that what is the time frame for the improved MVCC Optimistic Concurrency Strategy in the MonetDB release? Regards, Puthick

2 1

Functions in MOnetdb
by Ariel Abadi 22 Nov '16

22 Nov '16

Hi! We are trying to create a couple of functions in Monet. Every function we create has a terrible bad performance. Can someone help me to find out what Im doing wrong ? Here is the function CREATE FUNCTION sb_glob.camelcase(input VARCHAR(500)) RETURNS VARCHAR(500) begin DECLARE len INT; DECLARE i INT; SET len = CHAR_LENGTH(input); SET input = LOWER(input); SET i = 0; WHILE (i < len) DO SET input = CASE WHEN ((SUBSTR(input,i,1) = ' ' OR SUBSTR(input,i,1) = '-' OR i = 0) AND (i < len)) THEN (LEFT(input,i) || UPPER(SUBSTR(input,i + 1,1)) || RIGHT(input,len - i - 1)) ELSE input END; SET i = i + 1; END WHILE; RETURN input; END Thks in advance!

2 1

filter based on json value of key/value pair
by Lynn Carol Johnson 21 Nov '16

21 Nov '16

Hello all - I'm having trouble pulling rows based on the value of a json key/value pair. I have created a table with a json object that looks like this: sql>\d testjson CREATE TABLE "sys"."testjson" ( "chr" INTEGER, "pos" INTEGER, "value" JSON ); sql> I have entries in the table that look as below: sql>select * from testjson; +------+-----------+----------------------------------------------------------------+ | chr | pos | value | +======+===========+================================================================+ | 1 | 209890809 | {"df":1,"pval":4.556021744872574E-6,"r2":0.0995066009518681} | | 1 | 789383847 | {"df":1,"pval":2.50962115178055E-6,"r2":0.10462833261259474} | | 2 | 127893782 | {"df":1,"pval":4.2825829011938765E-6,"r2":0.10003907080878045} | | 2 | 234533212 | {"df":2,"pval":4.556021744872574E-6,"r2":0.0995066009518681} | +------+-----------+----------------------------------------------------------------+ 4 tuples (1.027ms) sql> I would like to filter on json values. Not the json key, the json key's value. I can pull json values based on a non-json field, e.g. Grab the pval where chr>1 as below: sql>select json.filter(value,'r2') as r2Value from testjson where chr=1; +-----------------------+ | r2value | +=======================+ | [0.0995066009518681] | | [0.10462833261259474] | +-----------------------+ 2 tuples (1.179ms) sql> But I want to pull chr/pos where pval < some amount, or df > some number. For example, how would I write the query to select all rows where df > 1? This query should return the last row of the table above. I've tried the following: sql>select * from testjson where (json.filter(value,'df') > 1); types json(0,0) and tinyint(8,0) are not equal sql> Thanks for your help - Lynn

2 2

how to structure queries like SELECT a.col IN ( b.col FROM b ) FROM a ?
by Anthony Damico 21 Nov '16

21 Nov '16

hi monetdb team, i've pasted a minimal reproducible example of a few SQL queries that i had expected to work -- only about half of them do work, and the other half just error with 'ParseException:SQLparser:' my goal is to get the very last line working, so if anybody could suggest a hack or a workaround that doesn't involve a JOIN, i would appreciate it. thanks all!! CREATE TABLE table_one (this_column INTEGER) ; INSERT INTO table_one VALUES (1) ; INSERT INTO table_one VALUES (2) ; INSERT INTO table_one VALUES (3) ; INSERT INTO table_one VALUES (4) ; INSERT INTO table_one VALUES (5) ; CREATE TABLE table_two (this_column INTEGER) ; INSERT INTO table_two VALUES (1) ; INSERT INTO table_two VALUES (2) ; # works as expected SELECT SUM( ( table_one.this_column IN ( SELECT this_column FROM table_two ) ) ) FROM table_one ; SELECT MAX( ( table_one.this_column IN ( SELECT this_column FROM table_two ) ) ) FROM table_one ; # fails SELECT ( table_one.this_column IN ( SELECT this_column FROM table_two ) ) AS new_column FROM table_one ; # Server says 'ParseException:SQLparser:'. # works SELECT AVG( CASE WHEN table_one.this_column IN ( SELECT this_column FROM table_two ) THEN 1 ELSE 0 END ) AS new_column FROM table_one ; # fails SELECT ( CASE WHEN ( table_one.this_column IN ( SELECT this_column FROM table_two ) ) THEN 1 ELSE 0 END ) AS new_column FROM table_one ; # Server says 'ParseException:SQLparser:'. # fails [[this query is the one i would like to implement]] SELECT COUNT(*) , ( CASE WHEN ( table_one.this_column IN ( SELECT this_column FROM table_two ) ) THEN 1 ELSE 0 END ) AS new_column FROM table_one GROUP BY new_column ; # Server says 'ParseException:SQLparser:'.

3 8

Unable to install//access external R Packages inside of MonetDB
by Cas Saternos 10 Nov '16

10 Nov '16

Hello All, Just wanted to report a bug, or at least unexpected behavior. I am new to monetdb, and could not submit this to your bugzilla tracker. *Background* I successfully have used MonetDB with Docker ( https://www.monetdb.org/blog/docker-container-monetdb-r) and was able to create FUNCTIONs and AGGREGATEs with R (as described here: https://www.monetdb.org/content/embedded-r-monetdb) This blog post also states: "As a final note on usage, you can use any R package from CRAN within your function without explicitly installing it. We overloaded the library function in R to automatically install missing packages." *Description* However, I am not able to use external R packages available from CRAN. The session below describes both Docker commands and SQL used. This session was run on OSX using Docker Machine. Thank you, -Cas *bash-3.2$* docker ps CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES d6b944445237 monetdb/monetdb-r-docker:latest "/usr/bin/supervisord" 13 minutes ago Up 13 minutes 0.0.0.0:32768->50000/tcp monetdb-r-docker *bash-3.2$* docker exec -it monetdb-r-docker mclient db user:monetdb password: Welcome to mclient, the MonetDB/SQL interactive terminal (Jul2015-SP4) Database: MonetDB v11.21.19 (Jul2015-SP4), 'mapi:monetdb://d6b944445237: 50000/db' Type \q to quit, \? for a list of available commands auto commit mode: on # Create and use a function to show that MonetDB with basic R expressions works: *sql>*CREATE FUNCTION test_add() RETURNS DOUBLE LANGUAGE R { 1 + 1 }; operation successful (1.254ms) *sql>*SELECT test_add(); +--------------------------+ | test_add | +==========================+ | 2 | +--------------------------+ # Add only the library function call to the function just shown (and give the function a new name). The function is created successfully, but an error occurs when the function is invoked in a query: *sql>*CREATE FUNCTION test_library() RETURNS DOUBLE LANGUAGE R { library("ggplot2"); 1 + 1 }; operation successful (1.378ms) *sql>*SELECT test_library(); Error running R expression. Error message: Error in .library.original(package, help, pos, lib.loc, character.only = T, : there is no package called 'ggplot2' Calls: as.data.frame -> <Anonymous> -> library -> .library.original *sql>*

1 0

NULL in COPY INTO FROM bulk
by Mathieu Raillard 10 Nov '16

10 Nov '16

Hi all, We are working with monetdb since 1 year and half, the performance are great. We are using in production the July 2015 release and we want to upgrade to the last release. We installed the new version and ran some tests to ensure the compatibility, unfortunatly we discovered a change in the way monetdb is handling NULL in bulk import. We are exporting datas from Mysql and by default we get a \N for every NULL value plus every field are enclosed by "" ( even number and timestamp). Exemple : 1,test,2016-01-01 00:00:00,null in database become "1","test","2016-01-01 00:00:00',"\N" in bulk. This bulk is working fine with the July 2015 realease but we cant import "\N" in number and timestamp with the june 2016 realase Have you an idea how can we make it works ? Here is some results from my tests On the July 2015 release : create schema test; create table test.test ( field1 int, field2 timestamp, field3 varchar(20) ); Bulk1 : "1","2016-01-01 00:00:00","test" COPY 10 RECORDS INTO test.test FROM '/tmp/bulk1' USING DELIMITERS ',','\n','"' NULL AS '\\N' ; 1 affected row (52.812ms) Bulk2 : "1","2016-01-01 00:00:00","\N" COPY 10 RECORDS INTO test.test FROM '/tmp/bulk2' USING DELIMITERS ',','\n','"' NULL AS '\\N' ; 1 affected row (47.852ms) Bulk3 : "1","\N","test" COPY 10 RECORDS INTO test.test FROM '/tmp/bulk3' USING DELIMITERS ',','\n','"' NULL AS '\\N' ; 1 affected row (58.811ms) Bulk4 : "\N","2016-01-01 00:00:00","test" COPY 10 RECORDS INTO test.test FROM '/tmp/bulk4' USING DELIMITERS ',','\n','"' NULL AS '\\N' ; 1 affected row (32.812ms) Bulk5 : "\N","\N","\N" COPY 10 RECORDS INTO test.test FROM '/tmp/bulk5' USING DELIMITERS ',','\n','"' NULL AS '\\N' ; 1 affected row (63.785ms) select * from test.test field1 field2 field3 1 2016-01-01 00:00:00.0 test 1 2016-01-01 00:00:00.0 <null> 1 <null> test <null> 2016-01-01 00:00:00.0 test <null> <null> <null> Tested on the Jun 2016 release : create schema test; create table test.test ( field1 int, field2 timestamp, field3 varchar(20) ); Bulk1 : "1","2016-01-01 00:00:00","test" COPY 10 RECORDS INTO test.test FROM '/tmp/bulk1' USING DELIMITERS ',','\n','"' NULL AS '\\N' ; 1 affected row (61.812ms) Bulk2 : "1","2016-01-01 00:00:00","\N" COPY 10 RECORDS INTO test.test FROM '/tmp/bulk2' USING DELIMITERS ',','\n','"' NULL AS '\\N' ; 1 affected row (47.314ms) Bulk3 : "1","\N","test" COPY 10 RECORDS INTO test.test FROM '/tmp/bulk3' USING DELIMITERS ',','\n','"' NULL AS '\\N' ; Failed to import table line 1 field 2 'timestamp(7)' expected in '\N' Bulk4 : "\N","2016-01-01 00:00:00","test" COPY 10 RECORDS INTO test.test FROM '/tmp/bulk4' USING DELIMITERS ',','\n','"' NULL AS '\\N' ; Failed to import table line 1 field 1 'int' expected in '\N' Bulk5 : "\N","\N","\N" COPY 10 RECORDS INTO test.test FROM '/tmp/bulk5' USING DELIMITERS ',','\n','"' NULL AS '\\N' ; Failed to import table line 1 field 1 'int' expected in '\N' select * from test.test +--------+----------------------------+--------+ | field1 | field2 | field3 | +========+============================+========+ | 1 | 2016-01-01 00:00:00.000000 | test | | 1 | 2016-01-01 00:00:00.000000 | N | +--------+----------------------------+--------+ Regards Mathieu

1 0

COPY subquery INTO file_name - INCLUDE field name
by Ariel Abadi 10 Nov '16

10 Nov '16

Hi! We are using the COPY *subquery* INTO *file_name *[ [USING] DELIMITERS *field_separator* [',' *record_separator* [ ',' *string_quot*e ]]] [ NULL AS *null_string* ] BUT we need to have in the file the name of the fields on the first row. Im looking on the documentation, but I cannot find anything related to this, can someone help me with this? Thks in advance!

3 3

Fwd: Performance tuning
by Rajendra Bhat 09 Nov '16

09 Nov '16

Hi team, I am running monetdb on 16 core processor and 30gb ram. I have table around 54 fields. gzip compressed csv size is around 6.5 GB. query will take around 6 min. please suggest tuning options. query: Select count(distinct userid),sum(mrp)/sum(noOfItems),sum(mrp)/sum( orderCount),sum(revenue),sum(orderCount),sumnoOfItems),sum( returnCount),sum(rtoCount),sum(0),sum(0),sum(tDiscountPer),sum(cDiscountPer) from historical where gender='men' and brand='puma' Thanks and Regards Rajendra Bhat -- Thanks and Regards Rajendra Bhat

2 3

How do I force start a new monetdbd instance?
by Anderson, David B 09 Nov '16

09 Nov '16

Or what files do I need to delete from the file system? When I try to start a new monetdbd instance, I am getting the error: another monetdbd is already running but there are no monetdb processes running. . .

2 1

TimeZone conversion functions
by Ariel Abadi 03 Nov '16

03 Nov '16

Hi! Can anyone help me understand how or where I can check the included functions on MonetDB to convert timestamp from one timezone to another? Thanks in advance! Rgds Ariel

2 2

MAL optimizer result inspection
by Moritz Bruder 02 Nov '16

02 Nov '16

Hi, is it possible to look at the output of a specific optimizer? I tried using *optimizer.showFlowGraph*, which just hangs, and *optimizer.showPlan*, which does not show some variables. Is there maybe something I missed? Regards, Moritz

2 1

Getting "NOT NULL constraint violated" for a column with a DEFAULT value
by Eyal Rozenberg 24 Oct '16

24 Oct '16

I have a column defined as follows: "div5tailnum" VARCHAR(10) NOT NULL DEFAULT '' (that's the output of \d mytable), or if you like: Div5TailNum VARCHAR(10) NOT NULL DEFAULT '' (that's from the CREATE TABLE command). I'm trying to load data from a CSV file using the following SQL: COPY OFFSET 2 INTO mytable FROM file.csv USING DELIMITERS ',','\n','"' NULL AS ''; but I get: INSERT INTO: NOT NULL constraint violated for column ontime.div5tailnum shouldn't that be impossible, seeing how I've provided a default?

3 4

Cannot install MonetDB on Ubuntu 16.04.1 LTS (Xenial Xerus)
by Peter Lee 24 Oct '16

24 Oct '16

Hi- I couldn't install MonetDB on Ubuntu 16.04.1 LTS (Xenial Xerus) hosted on EC2 yesterday. I kept getting the error below. It looks like the repository does not have a "source" folder. However, I noticed that the "Trusty" version had a "source" folder, and so I tried installing the Trusty version on my Xenial box, and it installed successfully. So far, I haven't encountered any problems, but was there anything I did wrong in the installation process? Will I encounter problems down the road with the Trusty version installed on a Xenial box? $ sudo apt-get update Hit:1 http://us-east-1.ec2.archive.ubuntu.com/ubuntu xenial InRelease Hit:2 http://us-east-1.ec2.archive.ubuntu.com/ubuntu xenial-updates InRelease Hit:3 http://us-east-1.ec2.archive.ubuntu.com/ubuntu xenial-backports InRelease Get:4 http://security.ubuntu.com/ubuntu xenial-security InRelease [94.5 kB] Ign:5 http://dev.monetdb.org/downloads/deb xenial InRelease Get:6 http://dev.monetdb.org/downloads/deb xenial Release [2,715 B] Get:7 http://dev.monetdb.org/downloads/deb xenial Release.gpg [163 B] Fetched 97.3 kB in 0s (185 kB/s) Reading package lists... Done W: http://dev.monetdb.org/downloads/deb/dists/xenial/Release.gpg: Signature by key 213E64DCD5DDC2C063CC39FD053C3ED40583366F uses weak digest algorithm (SHA1) E: Failed to fetch http://dev.monetdb.org/downloads/deb/dists/xenial/Release Unable to find expected entry 'monetdb/source/Sources' in Release file (Wrong sources.list entry or malformed file) E: Some index files failed to download. They have been ignored, or old ones used instead.

3 2

Fwd: query with group.subgroupdone' undefined in
by José Cavieres 22 Oct '16

22 Oct '16

Hello, Can anyone explain why adding "2*" in the following query results in an error: Original Query: sql>select (CAST(now()-'1970-01-01 00:00:00' as BIGINT)) as minuto,avg(CAST(N6_end - trStart as BIGINT)) as promedio from testquery where trStart>'2016-10-06 00:00:00.000000' GROUP BY minuto; +---------------+--------------------------+ | minuto | promedio | +===============+==========================+ | 1475770734000 | null | +---------------+--------------------------+ Query with "2*": sql>select 2*CAST(now()-'1970-01-01 00:00:00' as BIGINT) as minuto,avg(CAST(N6_end - trStart as BIGINT)) as promedio from testquery where trStart>'2016-10-06 00:00:00.000000' GROUP BY minuto; TypeException:user.s20_1[10]:'group.subgroupdone' undefined in: (X_15:any,r1_15:any,r2_15:any) := group.subgroupdone(X_14:hge); program contains errors Explain: ------------------------------------------------------------ ------------------------------------------------------------ ---------------------+ | mal | +=========================================================== ============================================================ ======================+ | function user.s22_1(A0:bte,A1:str,A2:str):void; | sql.mvc(); | | X_5:timestamp := mtime.current_timestamp(); | | A1; | | calc.timestamp(X_6,7,1); | | X_10:lng := mtime.diff(X_5,X_7); | | calc.lng(X_10); | | calc.hge(X_11); | | A0; | | X_14:hge := calc.*(X_12,X_13); | | (X_15,r1_15,r2_15) := group.subgroupdone(X_14); | | algebra.projection(r1_15,X_14); | | bat.setKey(X_18,true); | | X_21:bat[:oid] := sql.tid(X_4,"sys","testquery") ; | | X_24:bat[:timestamp] := sql.bind(X_4,"sys","testquery" ,"trstart",0); | | (X_27,r1_27) := sql.bind(X_4,"sys","testquery" ,"trstart",2); | | X_30:bat[:timestamp] := sql.bind(X_4,"sys","testquery" ,"trstart",1); | | sql.delta(X_24,X_27,r1_27,X_30); | | algebra.projection(X_21,X_31); | | X_33:bat[:timestamp] := mal.multiplex("calc","timestam p",X_32,7); | | A2; | | calc.timestamp(X_36,7); | | algebra.thetasubselect(X_33,X_37,">"); | | X_40:bat[:timestamp] := sql.bind(X_4,"sys","testquery" ,"n6_end",0); | | (X_42,r1_42) := sql.bind(X_4,"sys","testquery" ,"n6_end",2); | | X_44:bat[:timestamp] := sql.bind(X_4,"sys","testquery" ,"n6_end",1); | | sql.delta(X_40,X_42,r1_42,X_44); | | algebra.projection(X_21,X_45); | | algebra.projection(X_38,X_46); | | algebra.projection(X_38,X_32); | | X_49:bat[:lng] := mal.multiplex("mtime","diff",X _47,X_48); | | batcalc.lng(X_49); | | batcalc.dbl(X_52); | | X_54:bat[:dbl] := aggr.subavg(X_53,X_15,r1_15,tr ue,true); | | bat.new(nil:oid,nil:str); | | bat.new(nil:oid,nil:str); | | bat.new(nil:oid,nil:str); | | bat.new(nil:oid,nil:int); | | bat.new(nil:oid,nil:int); | | bat.append(X_58,".L1"); | | bat.append(X_61,"minuto"); | | bat.append(X_62,"hugeint"); | | bat.append(X_63,72); | | bat.append(X_65,0); | | bat.append(X_66,"sys.L3"); | | bat.append(X_68,"promedio"); | | bat.append(X_70,"double"); | | bat.append(X_72,53); | | bat.append(X_74,0); | | sql.resultSet(X_76,X_78,X_80,X_82,X_84,X_19,X_54); | | end user.s22_1; | | # querylog.define("explain select 2*cast(now()-\\'1970-01-01 00:00:00\\' as bigint) as minuto,avg(cast(n6_end - trstart as bigint)) as prom | : edio from testquery where trstart>\\'2016-10-06 00:00:00.000000\\' group by minuto;","default_pipe") : Environment: - MonetDB v11.23.7 (Jun2016-SP1) from release package - Centos and Ubuntu - Create table attached - The type of the columns in the query is timestamp - A previous versions of this email was rejected because the "create table" statement is too big for the 40Kbytes limit of this list. Thanks Jose Cavieres

4 7

system init script
by Heyns Emiliano 21 Oct '16

21 Oct '16

Does anyone have a working init script for MonetDB? -- Groet, Emiliano Heyns

1 0

how well does MonetDB scale to large servers (e.g. 4 CPU/64+ cores/2+TB RAM)?
by Igor Mendelev 21 Oct '16

21 Oct '16

[Sorry if this question was already asked and answered before - I'm new to this mailing list.] Recently servers with up to 6TB RAM became relatively affordable to be used with MonetDB (considering total cost of server hw + DB sw license) - but will MonetDB scale well to use all that hardware? I know it can be tried on the cloud using (for example) x1.32xlarge EC2 spot instances but before doing that I'd like to ask people with more knowledge/experience with MonetDB if it at least looks feasible, if there are any important tuning parameters for such hw etc. If you've tired similar hardware and/or know some relevant implementaton details maybe you can share your experience? Thanks. Igor

2 1

Data lost
by Luciano Sasso 17 Oct '16

17 Oct '16

Hi, I am delete rows from table, with "delete from mytable where id=x", and rows deleted no return in select "select * from mytable where id=x", its OK ! Version "Jul2015-SP1" But, when restart database, deleted rows return in select "select * from mytable where id=x". Its bug ?, or i have execute vacuum command, how to execute vacuum command. ? -- Luciano Sasso Vieira Data Scientist & Solutions Architect luciano(a)gsgroup.com.br <http://www.gsgroup.com.br> | tel: 17 3353-0833 | 17 3202-4040 | 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

new monetdb version, old farm
by Lynn Carol Johnson 17 Oct '16

17 Oct '16

Hello - My understanding is you should not run a new monetdb version with a dbfarm created from an older version. We have just updated our system from a Jul2015-SP3 version to the latest Jun2016-SP2 version. I have created a new db farm and plan to recreate the tables in the new farm. My question; How do you remove the old dbfarm? Our data is quite large (1.8 T) and I'd like to retrieve this space for other use. While I can start the db against the new farm, I can find no method of removing the old dbfarm to reclaim this space. Thanks - Lynn

5 7

Zero copy optimization in embedded python and loop back queries
by Joseph D'silva 16 Oct '16

16 Oct '16

Hi All, I was wondering if zero copy optimization in embedded Python is applicable even for results returned inside functions that use _conn.execute("select ...") ? Also is there something to look for in explain plan that will indicate if zero copy optimization is used or not ? Thanks -J

1 0

weird behavior when using views with calculated columns and double type
by imad hajj chahine 14 Oct '16

14 Oct '16

Hi, When paginating over a view that contains a calculated double columns. i got a TypeException error and sometimes the server crash. To replicate the error, create a table with 2 double columns col1 and col2, fill it with some test data; after create a view as select col1, col2, col1+col2 as col3 from table. when running select col1, col2, col3 from view limit 100 offset 100 i got the error. Also in related context, when i use 2 quantiles function on the calculated view column i get the same error. select quantile(col3, 0.25), quantile(col3, 0.75) from view. I am using jun2016-sp1 on windows. Thank you.

2 1

Announcement: New Jun2016-SP2 Bugfix release of MonetDB suite
by Sjoerd Mullender 13 Oct '16

13 Oct '16

The MonetDB team at CWI/MonetDB BV is pleased to announce the Jun2016-SP2 bugfix release of the MonetDB suite of programs. More information about MonetDB can be found on our website at <http://www.monetdb.org/>. For details on this release, please see the release notes at <http://www.monetdb.org/Downloads/ReleaseNotes>. As usual, the download location is <http://dev.monetdb.org/downloads/>. Jun 2016-SP2 bugfix release Build Environment * We now use the CommonCrypto library instead of the OpenSSL library on Darwin. Bug Fixes * 3939: Assert failure on concurrent queries when querying sys.queue * 4019: Casting a timestamp from a string results in NULL * 4025: expressions in the WHERE clause that evaluates incorrectly * 4038: After upgrade from 11.21.19, jdbc couldn't list tables for non sys users * 4044: Server crash when trying to delete a table has been added to a merge table with "cascade" at the end * 4049: Wrong results for queries with "OR" and "LEFT JOIN" * 4052: Infinite loop in rel_select * 4054: copy into file wrongly exports functions * 4058: Server crashes with a particular conditional query * 4059: Geom functions only visible by user monetdb * 4060: BAT leak in some aggregate queries * 4062: Error: SELECT: no such binary operator 1800 790 9186'like(varchar,varchar)' when used in query running in other schema than sys * 4064: Assertion: column not found * 4067: Relevant column name not printed when a CSV parsing error occurs * 4070: Extra condition in join predicate of explicit join produces wrong MAL code * 4074: Cannot use prepared statements when caching disabled * 6065: CTE with row number and union fails within MAL

1 0

EXPLAIN output *almost* re-parsable
by Roberto Cornacchia 10 Oct '16

10 Oct '16

Almost for fun, I did this little experiment: take the output of an EXPLAIN from sql, and execute it in MAL (mclient -l mal). I found only *1* detail which doesn't parse, here it is in a minimal example: * Got the explain of a trivial query: sql>\f raw sql>explain select name from sys.functions; % .explain # table_name % mal # name % clob # type % 94 # length function user.s4_1():void; X_32:void := querylog.define("explain select name from sys.functions;","default_pipe",21); X_16 := bat.new(nil:oid,nil:str); X_24 := bat.append(X_16,"sys.functions"); X_19 := bat.new(nil:oid,nil:str); X_26 := bat.append(X_19,"name"); X_20 := bat.new(nil:oid,nil:str); X_27 := bat.append(X_20,"varchar"); X_21 := bat.new(nil:oid,nil:int); X_29 := bat.append(X_21,256); X_23 := bat.new(nil:oid,nil:int); X_31 := bat.append(X_23,0); X_1 := sql.mvc(); C_2:bat[:oid] := sql.tid(X_1,"sys","functions"); X_5:bat[:str] := sql.bind(X_1,"sys","functions","name",0); (C_8,r1_8) := sql.bind(X_1,"sys","functions","name",2); X_11:bat[:str] := sql.bind(X_1,"sys","functions","name",1); X_13 := sql.delta(X_5,C_8,r1_8,X_11); X_14 := algebra.projection(C_2,X_13); sql.resultSet(X_24,X_26,X_27,X_29,X_31,X_14); end user.s4_1; # optimizer.mitosis() # optimizer.dataflow() * Opened a MAL session, initialized sql, and pasted the function above: mal>sql.init(); mal>function user.s4_1():void; mal> X_32:void := querylog.define("explain select name from sys.functions;","default_pipe",21); mal> X_16 := bat.new(nil:oid,nil:str); mal> X_24 := bat.append(X_16,"sys.functions"); mal> X_19 := bat.new(nil:oid,nil:str); mal> X_26 := bat.append(X_19,"name"); mal> X_20 := bat.new(nil:oid,nil:str); mal> X_27 := bat.append(X_20,"varchar"); mal> X_21 := bat.new(nil:oid,nil:int); mal> X_29 := bat.append(X_21,256); mal> X_23 := bat.new(nil:oid,nil:int); mal> X_31 := bat.append(X_23,0); mal> X_1 := sql.mvc(); mal> C_2:bat[:oid] := sql.tid(X_1,"sys","functions"); mal> X_5:bat[:str] := sql.bind(X_1,"sys","functions","name",0); mal> (C_8,*r1_8*) := sql.bind(X_1,"sys","functions","name",2); mal> X_11:bat[:str] := sql.bind(X_1,"sys","functions","name",1); mal> X_13 := sql.delta(X_5,C_8,r1_8,X_11); mal> X_14 := algebra.projection(C_2,X_13); mal> sql.resultSet(X_24,X_26,X_27,X_29,X_31,X_14); mal>end user.s4_1; MAPI = (monetdb) /tmp/.s.monetdb.54500 QUERY = end user.s4_1; ERROR = !TypeException:user.s4_1[17]:'sql.delta' undefined in: X_13:any := sql.delta(X_5:bat[:str],C_8:bat[:oid],*r1_8:bat[:any]*,X_11:bat[:str]); mal> The error is easy to find: sql.delta() expects r1_8 to be of type :bat[:str], but it gets :bat[:any] instead. * If I fix it manually: mal>function user.s4_1():void; mal> X_32:void := querylog.define("explain select name from sys.functions;","default_pipe",21); mal> X_16 := bat.new(nil:oid,nil:str); mal> X_24 := bat.append(X_16,"sys.functions"); mal> X_19 := bat.new(nil:oid,nil:str); mal> X_26 := bat.append(X_19,"name"); mal> X_20 := bat.new(nil:oid,nil:str); mal> X_27 := bat.append(X_20,"varchar"); mal> X_21 := bat.new(nil:oid,nil:int); mal> X_29 := bat.append(X_21,256); mal> X_23 := bat.new(nil:oid,nil:int); mal> X_31 := bat.append(X_23,0); mal> X_1 := sql.mvc(); mal> C_2:bat[:oid] := sql.tid(X_1,"sys","functions"); mal> X_5:bat[:str] := sql.bind(X_1,"sys","functions","name",0); mal> (C_8,*r1_8:bat[:str]*) := sql.bind(X_1,"sys","functions","name",2); mal> X_11:bat[:str] := sql.bind(X_1,"sys","functions","name",1); mal> X_13 := sql.delta(X_5,C_8,r1_8,X_11); mal> X_14 := algebra.projection(C_2,X_13); mal> sql.resultSet(X_24,X_26,X_27,X_29,X_31,X_14); mal>end user.s4_1; mal> Yay, it works. ** The reason I write this email:* - I suspect that the output of EXPLAIN is not meant to necessarily be re-parsable, but just to be informative - Nevertheless, it really is almost re-parsable. I tried a much larger query, (2000+ lines of MAL), and this was the only detail. It looks like fixing that small detail could be worth it. - Also, I wonder if this is, in fact, a bug. Binds to access types 0 and 1 do get the right type information. Why not access type 2?

2 2

More info about the MAL plan cache
by Roberto Cornacchia 07 Oct '16

07 Oct '16

Hi there, I have a couple of questions about the plan cache. 1) I know it caches MAL a user-functions for a given "SQL query". What does "SQL query" really means? The unparsed SQL script? The parse tree? The relational algebra tree? The normalized/optimized algebra tree? 2) What are the conditions for the cache to be used? Does it kick in only for some types of queries? I noticed some rather large queries (which take 300-400ms just to be translated from SQL to MAL) are not being cached (or so it seems - how can I check?) 3) Hypothetically speaking, would it be possible to interleave SQL and MAL queries? I am well aware that using MAL directly is not recommended - I only would like to know about feasibility. My typical use case is: START TRANSACTION; a few INSERT INTO "parameters"; SELECT * FROM my_big_view; -- my_big_view uses table "parameters" ROLLBACK; -- values inserted into "parameters" are flushed View "my_big_view" can be a rather large query. It takes an awful lot of time just to get a MAL plan for it. Suppose I have already a MAL plan for it, at client side. I could start a MAL session and run it. I guess though, the main problem is that it wouldn't be in the same transaction as the preceding (sql) INSERT INTO, so it wouldn't get the values inserted into "parameters". Would the only possibility be to move also the INSERT INTO into MAL? So skip SQL altogether? Thanks for you help, Roberto

1 1

slowdown after delete
by Stefano Fioravanzo 03 Oct '16

03 Oct '16

Hello, In my db I have a table with ~300million rows on which I have to perform ~1k aggregation queries periodically. I have noticed that if I delete even a small amount of rows from the table I have a greatly reduced performance compared to when no delete has been made. Specifically, with no delete the 1k queries complete in about 2 hours, if I delete a few records the task completes in about 10-12 hours. Inserting new rows in the table does not present the issue. Moreover if I dump and restore the table after the delete, everything’s back to normal and the queries execute fast. So the issue seems to be related to the fact that monet does not actually delete the records from the bat file, right? Are you aware of the issue? Is there some kind of vacuum operation available? If not, what is the fastest way to dump or copy and restore the table; or is there any other trick that may solve the issue? Any suggestion would be much appreciated, Thank you Stefano

2 1

Select with 5K+ columns kills server
by Pascal Heus 03 Oct '16

03 Oct '16

Hi: I have a MonetDB 5 server v11.23.7 "Jun2016-SP1 instance hosting a table with over 5600 columns (for the US General Social Survey) If we run a simple query like SELECT * FROM gss7214_v4 limit 2 we get the first two records as expected. However, the same query with all the column names explicitly stated like select "year","id","wrkstat","hrs1","hrs2","evwork","occ","prestige","wrkslf",<...thousands of additional columns - see attached...> from "gss7214_v4" WHERE "year"=1972 LIMIT 2 OFFSET 0; seem to kill the server. The process keeps running but become non-responsive. Now this is on a AWS EC2 t2.small instance with just ~2Gb or memory. Maybe more memory may solve this issue (?). But in any case concerned that the server hangs without warning or error message in the log. Any potential workaround? Suggestion most appreciated best *P

3 2

slowdown after delete
by Stefano Fioravanzo 30 Sep '16

30 Sep '16

Hello, In my db I have a table with ~300million rows on which I have to perform ~1k aggregation queries periodically. I have noticed that if I delete even a small amount of rows from the table I have a greatly reduced performance compared to when no delete has been made. Specifically, with no delete the 1k queries complete in about 2 hours, if I delete a few records the task completes in about 10-12 hours. Inserting new rows in the table does not present the issue. Moreover if I dump and restore the table after the delete, everything’s back to normal and the queries execute fast. So the issue seems to be related to the fact that monet does not actually delete the records from the bat file, right? Are you aware of the issue? Is there some kind of vacuum operation available? If not, what is the fastest way to dump or copy and restore the table; or is there any other trick that may solve the issue? Any suggestion would be much appreciated, Thank you Stefano

3 6

Running different MonetDB versions in parallel
by Moritz Bruder 30 Sep '16

30 Sep '16

Hi, so I read several times on the mailing-list, that it is indeed possible to run several MonetDB instances on the same server. I tried this myself with the following configuration: System: Linux ... 3.13.0-79-generic #123-Ubuntu SMP Fri Feb 19 14:27:58 UTC 2016 x86_64 x86_64 x86_64 GNU/Linux MonetDB version: mercurial revision 324165f1d055 from branch default (also tested with the topmost version) Configure script parameters: --disable-geom --disable-fits --disable-netcdf --disable-lidar --disable-shp --disable-gsl --disable-rintegration --disable-pyintegration --disable-jdbc --prefix=/home/bruder/monetdb-root --disable-assert --disable-testing --enable-optimize --program-suffix='-dsh' 1. Another version is running with a different user on the default port 50000 2. With my user, I set up a local db-farm, using port 49999. (A prefix is used on binaries, to prevent any conflicts with the existing version.) Creating a new database with *monetdbd* works, but once I try to start it with *monetdb*, it gives me the following error message: starting database 'test'... FAILED start: starting 'test' failed: database 'test' appears to shut itself down after starting, check monetdbd's logfile for possible hints Another thing I tried was starting mserver5 by hand: mserver5-dsh --dbpath=/home/bruder/monetdb-dbfarm/test --set merovingian_uri=mapi:monetdb://dbiron2:49999/test --set mapi_open=false --set mapi_port=0 --set mapi_usock=/home/bruder/monetdb-dbfarm/test/.mapi.sock --set monet_vault_key=/home/bruder/monetdb-dbfarm/test/.vaultkey --set gdk_nr_threads=16 --set max_clients=64 --set sql_optimizer=default_pipe --set monet_daemon=yes This time it won't accept my password (I assume the default password is *monetdb*): mclient-dsh -p49999 -dtest -umonetdb InvalidCredentialsException:checkCredentials:invalid credentials for user 'monetdb' I added the log file and the properties of the db-farm. Regards, Moritz

3 5

suggestions
by Yves Dorfsman 30 Sep '16

30 Sep '16

Is there a place to make suggestions on improvement for monetdb? Three things that probably seem minor compared to the goals of monetdb, but which would be very helpful in my opinion: - better copy to csv, a la postgres, with keywords lile "csv" and "header" - ctrl-c to cancel a query but not kill the mclient sesion - history kept and re-used between mclient sessions -- http://yves.zioup.com gpg: 4096R/32B0F416

3 2

Binary bulk load null values
by Dennis Pallett 25 Sep '16

25 Sep '16

Hi all, I'm working on a utility to bulk load data into MonetDB using the binary bulk loading options. At the moment I'm running into the issue whereby I'm unable to insert real NULL values in varchar columns. Only a literal "null" is inserted, which obviously fails the 'WHERE col IS NULL' condition. Can anyone tell me what value is used to import a null value into a varchar column? Best regards, Dennis

3 3

subsort fails silently
by Moritz Bruder 20 Sep '16

20 Sep '16

Hi, I just noticed a bug in the current revision (accc14b26ef3) of the default branch. I added a short mal snippet to reproduce the issue. I guess it's somehow related to sorting a void BAT, but I couldn't reduce the bug to a simple BAT (without the TPCH table). Regards, Moritz

2 1

RAID 0
by Vincent Sheffer 20 Sep '16

20 Sep '16

Are there any MonetDB specific advice/instructions for using a RAID 0 SSD array with MonetDB beyond make sure you have backups? I’m not sure what I’m looking for, other than may be tips on how to configure a dbfarm (i.e. monetdbd/mserver5 properties). Thanks, Vince The information transmitted, including any attachments, is intended only for the individual or entity to which it is addressed, and may contain confidential and/or privileged information. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by individuals or entities other than the intended recipient is prohibited, and all liability arising therefrom is disclaimed. If you have received this communication in error, please delete the information from any computer and notify the sender.

2 2

MonetDB Crystal client code in my article
by Brian Hood 18 Sep '16

18 Sep '16

Hi Guys, I've included a very basic MonetDB client code in the Crystal Language that actually connects to mserver5 performs a query and exits just to demonstrate how easy that is in Crystal to use a C binding but also for creating some more interest in MonetDB. https://github.com/puppetpies/Snippets/blob/master/Crystal%20-%20Ruby%20dyn… I will be publishing it on Medium / Slideshare next week. Regards, Brian Hood

1 0

COPY FROM support compressed files?
by Yves Dorfsman 16 Sep '16

16 Sep '16

Can I COPY FROM directly from compressed files? If so, what format (tried with zstd files, didn't work, but that's very new so not surprised)? Thanks. -- http://yves.zioup.com gpg: 4096R/32B0F416

3 2

Does R run in a sandbox in MonetDB?
by Pablo S 08 Sep '16

08 Sep '16

I'm refering to the MonetDB feature that allows to create R functions that run embedded in the database. For example, you can do: CREATE FUNCTION rapi00() RETURNS TABLE (d INTEGER) LANGUAGE R { seq(1,10); }; Now, I need my users to create dynamically these user defined functions by letting them enter these on the UI; the application will create the function in the database and a process will run them. Problem is that R allows to run operating system level commands, such as list.files(path=".") or even file.remove("some_file") Will MonetDB allow these external interactions with the operating system, or it runs in a sandbox?

2 1