- users-list - monetdb.org

Announcement: New Aug2018 Feature release of MonetDB suite
by Sjoerd Mullender 31 Aug '18

31 Aug '18

The MonetDB team at CWI/MonetDB BV is pleased to announce the Aug2018 feature release of the MonetDB suite of programs. More information about MonetDB can be found on our website at <https://www.monetdb.org/>. For details on this release, please see the release notes at <https://www.monetdb.org/Downloads/ReleaseNotes>. As usual, the download location is <https://dev.monetdb.org/downloads/>. Aug 2018 feature release (11.31.7) MonetDB5 Server * The lsst module was moved to a separate repository (https://dev.monetdb.org/hg/MonetDB-lsst/) Build Environment * Build the MonetDB-cfitsio RPM and libmonetdb5-server-cfitsio Debian/Ubuntu package. * On Windows, the separate MonetDB5-Geom installer has been incorporated into the main MonetDB5-SQL installer and is therefore no longer available as a separate download. Merovingian * Added a "logrotate" configuration file. See /etc/logrotate.d/monetdbd. * Changed the monetdb profilerstart command to be more robust. If the server or stethoscope crashed before, the pid file is still there, so the next time we try to start stethoscope, it will fail. Now the profilerstart command will check if a stethoscope process with the recorded pid is running. If not, we start stethoscope, assuming that something went wrong before. * Changed the monetdb stop command to try to stop stethoscope before stoping the server. The error conditions that can arrise from attempting to stop stethoscope are: + The database is not running. + The profilerlogpath is not set. + The profiler.pid file does not exist or is inaccessible. + The contents of the profiler.pid are not valid. + Shutdown of stethoscope did not succeed. + Removing the profiler.pid file failed. In all the cases, the attempt to stop the server can continue normally, so we actually ignore any errors that rise from the attempt to stop stethoscope. Client Package * ODBC: Implemented SQL_ATTR_QUERY_TIMEOUT parameter in SQLSetStmtAttr. * ODBC SQLGetInfo now returns a positive numeric value for InfoTypes: SQL_MAX_COLUMN_NAME_LEN, SQL_MAX_DRIVER_CONNECTIONS, SQL_MAX_IDENTIFIER_LEN, SQL_MAX_PROCEDURE_NAME_LEN, SQL_MAX_SCHEMA_NAME_LEN, SQL_MAX_TABLE_NAME_LEN and SQL_MAX_USER_NAME_LEN. * Added a '-f rowcount' option in mclient to repress printing the actual data of a resultset, but only print the number of returned tuples Stream Library * Added support for lz4 compressed files in the stream library MonetDB Common * Hash indexes are now persistent across server restarts. * The macros bunfastapp and tfastins and variants no longer set the dirty flag of the heap they write to. This now needs to be done separately (and preferably outside of the inner loop). * Removed batDirty flag from BAT record. Its function is completely superseded by batDirtydesc and the dirty flags on the various heaps. * Removed "masksize" argument of function BAThash. * A whole bunch of functions that took an int argument that was used as a Boolean (true/false) value now take a value of type bool. The functions BATkeyed, BATordered and BATordered_rev now return a bool instead of an int. * Removed the tdense property: it's function is completely replaced by whether or not tseqbase is equal to oid_nil. Testing Environment * Removed helper programs Mtimeout and MkillUsers: they have long been superseded by timeout handling by Mtest.py itself. SQL Frontend * Removed deprecated table producing system functions: sys.dependencies_columns_on_functions() sys.dependencies_columns_on_indexes() sys.dependencies_columns_on_keys() sys.dependencies_columns_on_triggers() sys.dependencies_columns_on_views() sys.dependencies_functions_on_functions() sys.dependencies_functions_on_triggers() sys.dependencies_keys_on_foreignkeys() sys.dependencies_owners_on_schemas() sys.dependencies_schemas_on_users() sys.dependencies_tables_on_foreignkeys() sys.dependencies_tables_on_functions() sys.dependencies_tables_on_indexes() sys.dependencies_tables_on_triggers() sys.dependencies_tables_on_views() sys.dependencies_views_on_functions() sys.dependencies_views_on_triggers() They are replaced by new system dependency_* views: sys.dependency_args_on_types sys.dependency_columns_on_functions sys.dependency_columns_on_indexes sys.dependency_columns_on_keys sys.dependency_columns_on_procedures sys.dependency_columns_on_triggers sys.dependency_columns_on_types sys.dependency_columns_on_views sys.dependency_functions_on_functions sys.dependency_functions_on_procedures sys.dependency_functions_on_triggers sys.dependency_functions_on_types sys.dependency_functions_on_views sys.dependency_keys_on_foreignkeys sys.dependency_owners_on_schemas sys.dependency_schemas_on_users sys.dependency_tables_on_foreignkeys sys.dependency_tables_on_functions sys.dependency_tables_on_indexes sys.dependency_tables_on_procedures sys.dependency_tables_on_triggers sys.dependency_tables_on_views sys.dependency_views_on_functions sys.dependency_views_on_procedures sys.dependency_views_on_views * Implemented group_concat(X,Y) aggregate function which also concatenates a column of strings X, but using a supplied string Y as the separator. This function is also a SQL extension. * Implemented group_concat(X) aggregate function which concatenates a column of strings using a comma as a separator. This function is not featured in the SQL standard. Bug Fixes * 4020: Importing timestamp with zone from copy into * 6506: Improper performance counters * 6556: Sqlitelogictest division by zero on COALESCE call * 6564: Changes to the Remote Table definition * 6575: Sqlitelogictest crash on groupby query with coalesce call * 6579: Sqlitelogic test infinite loop while compiling SQL query * 6586: Sqlitelogictest crash on complex aggregation query * 6593: Poor performance with like operator and escape clause * 6596: Multicolumn aggregation very slow after ANALYZE when persistent hashes are enabled * 6605: Sqlitelogictest set queries with wrong results * 6606: Misleading parameter name in generate_series function * 6610: Sqlitelogictest algebra.rangejoin undefined * 6611: Cannot compile with GCC 8.1 and --enable-debug=no * 6612: Implement BLOB handling in python UDFs * 6618: dependency column on sequence violated by DROP SEQUENCE * 6621: SELECT FROM REMOTE TABLE WHERE <> returns wrong results * 6624: "Cannot use non GROUP BY column in query results without an aggregate function" when using aggregate function in both HAVING and ORDER BY clauses. * 6625: OR in subselect causes the server to crash with segmentation fault * 6627: stddev_pop inconsistent behaviour * 6628: User cannot insert into own local temporary table * 6629: CREATE TABLE IF NOT EXISTS returns 42000! * 6630: Sqlitelogictest cast NULL to integer failing * 6632: Dataflow causes crash when THRnew fails * 6633: ILIKE clauses don't work on certain characters * 6635: monetdbd exits due to "Too many open files" error * 6637: Within a transaction, \d after an error causes mclient to exit * 6638: (sequences of) mkey.bulk_rotate_xor_hash() can generate NIL from non-NIL making multi-col joins return wrong results * 6639: COMMENT ON TABLE abc IS NULL invalidly sets the remark column to null where remark column is defined as NOT NULLable

1 0

Re: #!ERROR: THRnew: too many threads
by Stefan Manegold 14 Aug '18

14 Aug '18

Hi, The usual questions:Which version of MonetDBAndWhat kind of workload triggers this? Best,Stefan -------- Original message --------From: "Murthy, Gautham" <Gautham.Murthy(a)harman.com> Date: 13/08/2018 18:06 (GMT+01:00) To: users-list(a)monetdb.org Subject: #!ERROR: THRnew: too many threads Hello, We had a DB crash reported with the below messages, Do we need alter any system level parameters or changes to avoid the below issue – Server has 128 CPU and 1TB memory. 2018-08-13 08:46:31 MSG TSV_PROD_DB_B[57408]: #!ERROR: THRnew: too many threads 2018-08-13 08:46:31 MSG TSV_PROD_DB_B[57408]: #!ERROR: THRnew: too many threads 2018-08-13 08:46:31 MSG TSV_PROD_DB_B[57408]: #!ERROR: THRnew: too many threads /etc/security/limits.conf monetadmin soft nproc 102400 monetadmin hard nproc 102400 monetadmin soft nofile 500000 monetadmin hard nofile 500000 monetadmin soft stack 10240 monetadmin soft memlock unlimited monetadmin hard memlock unlimited [monetadmin@lnx1535 DBFARM_TSV_P_B]$ /monet_binaries/MonetDB-11.27.13_PY/bin/monetdb -p 50010 get all TSV_PROD_DB_B name prop source value TSV_PROD_DB_B name - TSV_PROD_DB_B TSV_PROD_DB_B type default database TSV_PROD_DB_B shared default yes TSV_PROD_DB_B nthreads default 128 TSV_PROD_DB_B optpipe local sequential_pipe TSV_PROD_DB_B readonly local yes TSV_PROD_DB_B embedr default no TSV_PROD_DB_B embedpy local yes TSV_PROD_DB_B embedpy3 default no TSV_PROD_DB_B nclients local 2048 TSV_PROD_DB_B dbextra default <unknown> [monetadmin@lnx1535 DBFARM_TSV_P_B]$ 2018-08-13 08:46:30 MSG merovingian[36790]: target connection is on local UNIX domain socket, passing on filedescriptor instead of proxying 2018-08-13 08:46:30 MSG merovingian[36790]: proxying client 10.106.5.250:29929 for database 'TSV_PROD_DB_B' to mapi:monetdb:///monet_data02/DBFARM_TSV_P_B/TSV_PROD_DB_B/.mapi.sock?database=TSV_PROD_DB_B 2018-08-13 08:46:30 MSG merovingian[36790]: target connection is on local UNIX domain socket, passing on filedescriptor instead of proxying 2018-08-13 08:46:31 MSG TSV_PROD_DB_B[57408]: #!ERROR: THRnew: too many threads 2018-08-13 08:46:31 MSG TSV_PROD_DB_B[57408]: #!ERROR: THRnew: too many threads 2018-08-13 08:46:31 MSG TSV_PROD_DB_B[57408]: #!ERROR: THRnew: too many threads 2018-08-13 08:46:31 MSG TSV_PROD_DB_B[57408]: #!ERROR: THRnew: too many threads 2018-08-13 08:46:31 MSG TSV_PROD_DB_B[57408]: #!ERROR: THRnew: too many threads 2018-08-13 08:46:31 MSG TSV_PROD_DB_B[57408]: #!ERROR: THRnew: too many threads 2018-08-13 08:46:31 MSG TSV_PROD_DB_B[57408]: #!ERROR: THRnew: too many threads 2018-08-13 08:46:31 MSG TSV_PROD_DB_B[57408]: #!ERROR: THRnew: too many threads 2018-08-13 08:46:31 MSG TSV_PROD_DB_B[57408]: #!ERROR: THRnew: too many threads 2018-08-13 08:46:31 MSG TSV_PROD_DB_B[57408]: #!ERROR: THRnew: too many threads 2018-08-13 08:46:31 MSG TSV_PROD_DB_B[57408]: #!ERROR: THRnew: too many threads 2018-08-13 08:46:31 MSG TSV_PROD_DB_B[57408]: #!ERROR: THRnew: too many threads 2018-08-13 08:46:33 MSG merovingian[36790]: proxying client 10.106.5.250:29957 for database 'TSV_PROD_DB_B' to mapi:monetdb:///monet_data02/DBFARM_TSV_P_B/TSV_PROD_DB_B/.mapi.sock?database=TSV_PROD_DB_B 2018-08-13 08:46:33 MSG merovingian[36790]: target connection is on local UNIX domain socket, passing on filedescriptor instead of proxying 2018-08-13 08:46:36 MSG TSV_PROD_DB_B[57408]: #!ERROR: THRnew: too many threads 2018-08-13 08:46:40 MSG merovingian[36790]: proxying client 10.106.5.250:64830 for database 'TSV_PROD_DB_B' to mapi:monetdb:///monet_data02/DBFARM_TSV_P_B/TSV_PROD_DB_B/.mapi.sock?database=TSV_PROD_DB_B 2018-08-13 08:46:40 MSG merovingian[36790]: target connection is on local UNIX domain socket, passing on filedescriptor instead of proxying 2018-08-13 08:46:42 MSG TSV_PROD_DB_B[57408]: #!ERROR: THRnew: too many threads 2018-08-13 08:47:47 MSG discovery[36790]: new neighbour lnx1536.ch3.prod.i.com (lnx1536.ch3.prod.i.com) 2018-08-13 08:47:48 MSG discovery[36790]: new database mapi:monetdb://lnx1536.ch3.prod.i.com:50010/TSV_PROD_DB_B (ttl=660s) 2018-08-13 08:48:55 MSG merovingian[36790]: database 'TSV_PROD_DB_B' (57408) has crashed (dumped core) 2018-08-13 08:49:12 MSG merovingian[36790]: database 'TSV_PROD_DB_B' has crashed after start on 2018-08-13 05:35:17, attempting restart, up min/avg/max: 2h/5d/3w, crash average: 1.00 0.70 0.43 (23-10=13) 2018-08-13 08:49:12 MSG TSV_PROD_DB_B[72455]: arguments: /monet_binaries/MonetDB-11.27.13_PY/bin/mserver5 --dbpath=/monet_data02/DBFARM_TSV_P_B/TSV_PROD_DB_B --set merovingian_uri=mapi:monetdb://lnx1535.ch3.prod.i.com:50010/TSV_PROD_DB_B --set mapi_open=false --set mapi_port=0 --set 2018-08-13 08:49:12 MSG TSV_PROD_DB_B[72455]: mapi_usock=/monet_data02/DBFARM_TSV_P_B/TSV_PROD_DB_B/.mapi.sock --set monet_vault_key=/monet_data02/DBFARM_TSV_P_B/TSV_PROD_DB_B/.vaultkey --set gdk_nr_threads=128 --set max_clients=2048 --set sql_optimizer=sequential_pipe --set embedded_py=true --readonly --set monet_daemon=yes 2018-08-13 08:49:12 MSG TSV_PROD_DB_B[72455]: 2018-08-13 08:49:12 MSG merovingian[36790]: proxying client 10.106.5.250:16467 for database 'TSV_PROD_DB_B' to mapi:monetdb:///monet_data02/DBFARM_TSV_P_B/TSV_PROD_DB_B/.mapi.sock?database=TSV_PROD_DB_B 2018-08-13 08:49:12 MSG merovingian[36790]: proxying client 10.106.5.250:45439 for database 'TSV_PROD_DB_B' to mapi:monetdb:///monet_data02/DBFARM_TSV_P_B/TSV_PROD_DB_B/.mapi.sock?database=TSV_PROD_DB_B 2018-08-13 08:49:12 MSG merovingian[36790]: starting a proxy failed: cannot connect: Connection refused 2018-08-13 08:49:12 ERR control[36790]: !monetdbd: an internal error has occurred 'cannot connect: Connection refused' 2018-08-13 08:49:12 ERR merovingian[36790]: client error: cannot connect: Connection refused 2018-08-13 08:49:12 MSG merovingian[36790]: proxying client 10.106.5.250:45447 for database 'TSV_PROD_DB_B' to mapi:monetdb:///monet_data02/DBFARM_TSV_P_B/TSV_PROD_DB_B/.mapi.sock?database=TSV_PROD_DB_B 2018-08-13 08:49:12 ERR control[36790]: !monetdbd: an internal error has occurred 'cannot connect: Connection refused' 2018-08-13 08:49:12 MSG merovingian[36790]: starting a proxy failed: cannot connect: Connection refused 2018-08-13 08:49:13 ERR merovingian[36790]: client error: cannot connect: Connection refused 2018-08-13 08:49:13 MSG merovingian[36790]: proxying client 10.106.5.250:36605 for database 'TSV_PROD_DB_B' to mapi:monetdb:///monet_data02/DBFARM_TSV_P_B/TSV_PROD_DB_B/.mapi.sock?database=TSV_PROD_DB_B /etc/security/limits.conf monetadmin soft nproc 102400 monetadmin hard nproc 102400 monetadmin soft nofile 500000 monetadmin hard nofile 500000 monetadmin soft stack 10240 monetadmin soft memlock unlimited monetadmin hard memlock unlimited [monetadmin@lnx1535 DBFARM_TSV_P_B]$ /monet_binaries/MonetDB-11.27.13_PY/bin/monetdb -p 50010 get all TSV_PROD_DB_B name prop source value TSV_PROD_DB_B name - TSV_PROD_DB_B TSV_PROD_DB_B type default database TSV_PROD_DB_B shared default yes TSV_PROD_DB_B nthreads default 128 TSV_PROD_DB_B optpipe local sequential_pipe TSV_PROD_DB_B readonly local yes TSV_PROD_DB_B embedr default no TSV_PROD_DB_B embedpy local yes TSV_PROD_DB_B embedpy3 default no TSV_PROD_DB_B nclients local 2048 TSV_PROD_DB_B dbextra default <unknown> [monetadmin@lnx1535 DBFARM_TSV_P_B]$

2 2

#!ERROR: THRnew: too many threads
by Murthy, Gautham 13 Aug '18

13 Aug '18

Hello, We had a DB crash reported with the below messages, Do we need alter any system level parameters or changes to avoid the below issue - Server has 128 CPU and 1TB memory. 2018-08-13 08:46:31 MSG TSV_PROD_DB_B[57408]: #!ERROR: THRnew: too many threads 2018-08-13 08:46:31 MSG TSV_PROD_DB_B[57408]: #!ERROR: THRnew: too many threads 2018-08-13 08:46:31 MSG TSV_PROD_DB_B[57408]: #!ERROR: THRnew: too many threads /etc/security/limits.conf monetadmin soft nproc 102400 monetadmin hard nproc 102400 monetadmin soft nofile 500000 monetadmin hard nofile 500000 monetadmin soft stack 10240 monetadmin soft memlock unlimited monetadmin hard memlock unlimited [monetadmin@lnx1535 DBFARM_TSV_P_B]$ /monet_binaries/MonetDB-11.27.13_PY/bin/monetdb -p 50010 get all TSV_PROD_DB_B name prop source value TSV_PROD_DB_B name - TSV_PROD_DB_B TSV_PROD_DB_B type default database TSV_PROD_DB_B shared default yes TSV_PROD_DB_B nthreads default 128 TSV_PROD_DB_B optpipe local sequential_pipe TSV_PROD_DB_B readonly local yes TSV_PROD_DB_B embedr default no TSV_PROD_DB_B embedpy local yes TSV_PROD_DB_B embedpy3 default no TSV_PROD_DB_B nclients local 2048 TSV_PROD_DB_B dbextra default <unknown> [monetadmin@lnx1535 DBFARM_TSV_P_B]$ 2018-08-13 08:46:30 MSG merovingian[36790]: target connection is on local UNIX domain socket, passing on filedescriptor instead of proxying 2018-08-13 08:46:30 MSG merovingian[36790]: proxying client 10.106.5.250:29929 for database 'TSV_PROD_DB_B' to mapi:monetdb:///monet_data02/DBFARM_TSV_P_B/TSV_PROD_DB_B/.mapi.sock?database=TSV_PROD_DB_B 2018-08-13 08:46:30 MSG merovingian[36790]: target connection is on local UNIX domain socket, passing on filedescriptor instead of proxying 2018-08-13 08:46:31 MSG TSV_PROD_DB_B[57408]: #!ERROR: THRnew: too many threads 2018-08-13 08:46:31 MSG TSV_PROD_DB_B[57408]: #!ERROR: THRnew: too many threads 2018-08-13 08:46:31 MSG TSV_PROD_DB_B[57408]: #!ERROR: THRnew: too many threads 2018-08-13 08:46:31 MSG TSV_PROD_DB_B[57408]: #!ERROR: THRnew: too many threads 2018-08-13 08:46:31 MSG TSV_PROD_DB_B[57408]: #!ERROR: THRnew: too many threads 2018-08-13 08:46:31 MSG TSV_PROD_DB_B[57408]: #!ERROR: THRnew: too many threads 2018-08-13 08:46:31 MSG TSV_PROD_DB_B[57408]: #!ERROR: THRnew: too many threads 2018-08-13 08:46:31 MSG TSV_PROD_DB_B[57408]: #!ERROR: THRnew: too many threads 2018-08-13 08:46:31 MSG TSV_PROD_DB_B[57408]: #!ERROR: THRnew: too many threads 2018-08-13 08:46:31 MSG TSV_PROD_DB_B[57408]: #!ERROR: THRnew: too many threads 2018-08-13 08:46:31 MSG TSV_PROD_DB_B[57408]: #!ERROR: THRnew: too many threads 2018-08-13 08:46:31 MSG TSV_PROD_DB_B[57408]: #!ERROR: THRnew: too many threads 2018-08-13 08:46:33 MSG merovingian[36790]: proxying client 10.106.5.250:29957 for database 'TSV_PROD_DB_B' to mapi:monetdb:///monet_data02/DBFARM_TSV_P_B/TSV_PROD_DB_B/.mapi.sock?database=TSV_PROD_DB_B 2018-08-13 08:46:33 MSG merovingian[36790]: target connection is on local UNIX domain socket, passing on filedescriptor instead of proxying 2018-08-13 08:46:36 MSG TSV_PROD_DB_B[57408]: #!ERROR: THRnew: too many threads 2018-08-13 08:46:40 MSG merovingian[36790]: proxying client 10.106.5.250:64830 for database 'TSV_PROD_DB_B' to mapi:monetdb:///monet_data02/DBFARM_TSV_P_B/TSV_PROD_DB_B/.mapi.sock?database=TSV_PROD_DB_B 2018-08-13 08:46:40 MSG merovingian[36790]: target connection is on local UNIX domain socket, passing on filedescriptor instead of proxying 2018-08-13 08:46:42 MSG TSV_PROD_DB_B[57408]: #!ERROR: THRnew: too many threads 2018-08-13 08:47:47 MSG discovery[36790]: new neighbour lnx1536.ch3.prod.i.com (lnx1536.ch3.prod.i.com) 2018-08-13 08:47:48 MSG discovery[36790]: new database mapi:monetdb://lnx1536.ch3.prod.i.com:50010/TSV_PROD_DB_B (ttl=660s) 2018-08-13 08:48:55 MSG merovingian[36790]: database 'TSV_PROD_DB_B' (57408) has crashed (dumped core) 2018-08-13 08:49:12 MSG merovingian[36790]: database 'TSV_PROD_DB_B' has crashed after start on 2018-08-13 05:35:17, attempting restart, up min/avg/max: 2h/5d/3w, crash average: 1.00 0.70 0.43 (23-10=13) 2018-08-13 08:49:12 MSG TSV_PROD_DB_B[72455]: arguments: /monet_binaries/MonetDB-11.27.13_PY/bin/mserver5 --dbpath=/monet_data02/DBFARM_TSV_P_B/TSV_PROD_DB_B --set merovingian_uri=mapi:monetdb://lnx1535.ch3.prod.i.com:50010/TSV_PROD_DB_B --set mapi_open=false --set mapi_port=0 --set 2018-08-13 08:49:12 MSG TSV_PROD_DB_B[72455]: mapi_usock=/monet_data02/DBFARM_TSV_P_B/TSV_PROD_DB_B/.mapi.sock --set monet_vault_key=/monet_data02/DBFARM_TSV_P_B/TSV_PROD_DB_B/.vaultkey --set gdk_nr_threads=128 --set max_clients=2048 --set sql_optimizer=sequential_pipe --set embedded_py=true --readonly --set monet_daemon=yes 2018-08-13 08:49:12 MSG TSV_PROD_DB_B[72455]: 2018-08-13 08:49:12 MSG merovingian[36790]: proxying client 10.106.5.250:16467 for database 'TSV_PROD_DB_B' to mapi:monetdb:///monet_data02/DBFARM_TSV_P_B/TSV_PROD_DB_B/.mapi.sock?database=TSV_PROD_DB_B 2018-08-13 08:49:12 MSG merovingian[36790]: proxying client 10.106.5.250:45439 for database 'TSV_PROD_DB_B' to mapi:monetdb:///monet_data02/DBFARM_TSV_P_B/TSV_PROD_DB_B/.mapi.sock?database=TSV_PROD_DB_B 2018-08-13 08:49:12 MSG merovingian[36790]: starting a proxy failed: cannot connect: Connection refused 2018-08-13 08:49:12 ERR control[36790]: !monetdbd: an internal error has occurred 'cannot connect: Connection refused' 2018-08-13 08:49:12 ERR merovingian[36790]: client error: cannot connect: Connection refused 2018-08-13 08:49:12 MSG merovingian[36790]: proxying client 10.106.5.250:45447 for database 'TSV_PROD_DB_B' to mapi:monetdb:///monet_data02/DBFARM_TSV_P_B/TSV_PROD_DB_B/.mapi.sock?database=TSV_PROD_DB_B 2018-08-13 08:49:12 ERR control[36790]: !monetdbd: an internal error has occurred 'cannot connect: Connection refused' 2018-08-13 08:49:12 MSG merovingian[36790]: starting a proxy failed: cannot connect: Connection refused 2018-08-13 08:49:13 ERR merovingian[36790]: client error: cannot connect: Connection refused 2018-08-13 08:49:13 MSG merovingian[36790]: proxying client 10.106.5.250:36605 for database 'TSV_PROD_DB_B' to mapi:monetdb:///monet_data02/DBFARM_TSV_P_B/TSV_PROD_DB_B/.mapi.sock?database=TSV_PROD_DB_B /etc/security/limits.conf monetadmin soft nproc 102400 monetadmin hard nproc 102400 monetadmin soft nofile 500000 monetadmin hard nofile 500000 monetadmin soft stack 10240 monetadmin soft memlock unlimited monetadmin hard memlock unlimited [monetadmin@lnx1535 DBFARM_TSV_P_B]$ /monet_binaries/MonetDB-11.27.13_PY/bin/monetdb -p 50010 get all TSV_PROD_DB_B name prop source value TSV_PROD_DB_B name - TSV_PROD_DB_B TSV_PROD_DB_B type default database TSV_PROD_DB_B shared default yes TSV_PROD_DB_B nthreads default 128 TSV_PROD_DB_B optpipe local sequential_pipe TSV_PROD_DB_B readonly local yes TSV_PROD_DB_B embedr default no TSV_PROD_DB_B embedpy local yes TSV_PROD_DB_B embedpy3 default no TSV_PROD_DB_B nclients local 2048 TSV_PROD_DB_B dbextra default <unknown> [monetadmin@lnx1535 DBFARM_TSV_P_B]$

1 0

SQL Statements are skipped when run via scripts concurrently on different tables
by Murthy, Gautham 06 Aug '18

06 Aug '18

Hello, Application Teams are experiencing a behavior where few statements are skipped when executed via scripts, They are running multiple scripts concurrently on different tables. Please let us know if there are any know issues or a fix for the reported behavior. Database: MonetDB v11.27.13 (Jul2017-SP4) ü DROP, CREATE, INSERT - New SQL sequence, however looks like SQL statement are not executing as in order in database as per system catalog table log time entries. We need to check on this. ü First DROP, INSERT started and then after couple of statements, CREATE SQL started executing. DROP TABLE IF EXISTS "KEYFOOD_IT_S_22_43321_C";^M CREATE TABLE "KEYFOOD_IT_S_22_43321_C"(ATTR_NAME VARCHAR(500) NOT NULL, COL_NAME VARCHAR(30) NOT NULL, AVP_KEY INTEGER NOT NULL, ATTR_VALUE VARCHAR(3000), ATTR_LONG_VALUE VARCHAR(3000), ATTR_SHORT_VALUE VARCHAR(100), ATTR_MEDIUM_VALUE VARCHAR(100), PARENT_AVP_KEY INTEGER, SORT_ORDER INTEGER); INSERT INTO "KEYFOOD_IT_S_22_43321_C" (ATTR_NAME, COL_NAME, AVP_KEY, ATTR_VALUE, ATTR_LONG_VALUE, ATTR_SHORT_VALUE, ATTR_MEDIUM_VALUE, PARENT_AVP_KEY, SORT_ORDER) SELECT ATTR_NAME, COL_NAME, AVP_KEY, ATTR_VALUE, ATTR_LONG_VALUE, ATTR_SHORT_VALUE, ATTR_MEDIUM_VALUE, PARENT_AVP_KEY, SORT_ORDER FROM "IT_DSJ_KEYFOOD_43321" WHERE COL_NAME='S_22_KEY' AND SORT_TYPE='CATG'; We were able to statements being skipped in sys.querylog_catalog and sys.querylog_calls. Thank You, Gautham

2 1

incorrect data being fetched with WHERE clause
by Murthy, Gautham 06 Aug '18

06 Aug '18

Hello, Data is retrieved which are not available in "where" clause. Application teams have seen some cases where this works fine after some hours. After data load we are seeing incorrect data being fetched. Are there any know issues with the below versions ? Database: MonetDB v11.27.13 (Jul2017-SP4) sql>select * FROM "IT_DSJ_NCKLDN_45121" WHERE COL_NAME='M_59647_KEY' AND SORT_TYPE='ORTH'; +-------------------+--------------+------------+-------------------+-------------------+--------------------+--------------------+------+-------+------+ | attr_name | col_name | avp_key | attr_value | attr_long_value | attr_short_value | attr_medium_value | pare | sort_ | sort | : : : : : : : : nt_a : order : _typ : : : : : : : : : vp_k : : e : : : : : : : : : ey : : : +===================+==============+============+===================+===================+====================+====================+======+=======+======+ | ABRASIVE INFO | S_22_KEY | 30000 | N/A | N/A | N/A | N/A | null | 30000 | CATG | | ABRASIVE INFO | S_22_KEY | 30000 | N/A | N/A | N/A | N/A | null | 30000 | ORTH | | ABRASIVE INFO | S_22_KEY | 30000 | N/A | N/A | N/A | N/A | null | 30000 | ORTH | | ABSORBENCY LEVEL | S_23_KEY | 30000 | N/A | N/A | N/A | N/A | null | 30000 | CATG | | ABSORBENCY LEVEL | S_23_KEY | 30000 | N/A | N/A | N/A | N/A | null | 30000 | ORTH | | ABSORBENCY LEVEL | S_23_KEY | 30000 | N/A | N/A | N/A | N/A | null | 30000 | ORTH | | ACTIVE INGREDIENT | S_25_KEY | 30000 | N/A | N/A | N/A | N/A | null | 30000 | CATG | | ACTIVE INGREDIENT | S_25_KEY | 30000 | N/A | N/A | N/A | N/A | null | 30000 | ORTH | Thank You, Gautham

2 1

Do "upserts" supports in MonetDB?
by Sharma, Sreejith 06 Aug '18

06 Aug '18

Hello, Is there any roadmap for implementing "upserts" or "merge" alike in oracle for MonetDB? Or is it already available? Regards, Sreejith

1 0

Django + MonetDB
by ALEX OSSIPOV 05 Aug '18

05 Aug '18

Hi All I was looking to use MonetDB with Django framework but couldn't find MonetSQLdb package and all references point to 10 years old git code. Is it possible to use current versions of MonetDB with Django and where can I find required packages with configuration instructions? Thanks Alex

1 0

outstanding updates
by Roberto Cornacchia 03 Aug '18

03 Aug '18

Hi, We are having a problem, for a while, that is difficult to isolate. Our ETL includes the following steps: 1) Open JDBC connection, add data to a table, commit, close connection 2) Open JDBC connection, some ALTER TALBLE statements, commit, close connection Occasionally, we get the following during step 2) : java.sql.SQLException: ALTER TABLE: set READ or INSERT ONLY not possible with outstanding updates (wait until updates are flushed) at nl.cwi.monetdb.jdbc.MonetConnection$ResponseList.executeQuery(MonetConnection.java:2732) What puzzles me is that step one is completed when this happens, the commit is done and the connection is closed (we triple-checked that is actually is closed). Notice that inserting a sleep between the to steps makes it work correctly. As I said, this is hard to isolate and reproduce. Still, can anyone guess what is exactly happening? Can it be that a background process is still flushing updates from step 1) ? Even if the JDBC connection is closed ? Can we force a blocking flush, so that it doesn't return until it's safe? If not, how can we know when updates are flushed? Thanks, Roberto

2 5

Mercurial source compilation fails on Mac OS 10.13.5
by Ying Zhang 19 Jul '18

19 Jul '18

Dear all, Not sure how long this has been going on, but I was just trying to refresh my MonetDB installation (compiled from Mercurial source code), but it failed with the following errors: In file included from /Users/jennie/monet/Mar2018/sql/backends/monet5/UDF/pyapi3/pyapi3.c:13: /Users/jennie/monet/Mar2018/sql/backends/monet5/UDF/pyapi3/../pyapi/pyapi.c:968:18: error: assigning to 'char *' from 'const char *' discards qualifiers [-Werror,-Wincompatible-pointer-types-discards-qualifiers] retnames[i] = PyUnicode_AsUTF8(colname); ^ ~~~~~~~~~~~~~~~~~~~~~~~~~ /Users/jennie/monet/Mar2018/sql/backends/monet5/UDF/pyapi3/../pyapi/pyapi.c:1382:19: error: assigning to 'char *' from 'const char *' discards qualifiers [-Werror,-Wincompatible-pointer-types-discards-qualifiers] py_error_string = PyString_AS_STRING(error); ^ ~~~~~~~~~~~~~~~~~~~~~~~~~ Mac OS 10.13.5 Darwin Kernel Version 17.6.0 Happens with both Mar2018 and default branch Does anyone know how to fix these errors and get MonetDB compiled? Thanks in advance! Regards, Jennie

2 2

Mar2018 production compilation fails on Fedora 28
by Roberto Cornacchia 19 Jul '18

19 Jul '18

Compiling from Mar2018 branch, today's hg tip, with production settings: --disable-debug --disable-developer --disable-assert --enable-optimize Fails with this on Fedora 28: In file included from /usr/include/string.h:494, from /home/roberto/MonetDBServer/MonetDB.Spinque_Mar2018/src/gdk/gdk.h:316, from /home/roberto/MonetDBServer/MonetDB.Spinque_Mar2018/src/gdk/gdk_calc.c:10: In function ‘memcpy’, inlined from ‘addstr_loop’ at /home/roberto/MonetDBServer/MonetDB.Spinque_Mar2018/src/gdk/gdk_calc.c:3415:3: /usr/include/bits/string_fortified.h:34:10: error: ‘__builtin_memcpy’ forming offset [3, 65535] is out of the bounds [0, 2] of object ‘str_nil’ with type ‘const char[2]’ [-Werror=array-bounds] return __builtin___memcpy_chk (__dest, __src, __len, __bos0 (__dest)); ^~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ (same error in several files) Compiling with debug settings succeeds: --enable-debug --enable-developer --enable-assert --disable-optimize Is this reproducible or has it something to do with my machine?

3 2

MonetDB Mar2018 Release - Connection terminates abruptly with no error codes/messages
by Murthy, Gautham 18 Jul '18

18 Jul '18

Hello, MonetDB release - MonetDB Mar2018 We are facing issues when trying to run queries concurrently on the newer version of MonetDB. When 2 or more sessions (create/drop tables) are executed concurrently, sessions get terminated without any error messages. Do we need to explicitly enable error feedback? Does hitting concurrency abruptly terminates the session/connection ? [monetadmin@lnx1490 ~]$ /monet_binaries/MonetDB-11.27.13_PY/bin/mclient mapi:monetdb://lnx1490.ch3.prod.i.com:50105/TSV_PROD_DB_A_C -u DM_POS_TSV_MRT_P6A password: Welcome to mclient, the MonetDB/SQL interactive terminal (Jul2017-SP4) Database: MonetDB v11.29.7 (Mar2018-SP1), 'mapi:monetdb://lnx1490.ch3.prod.i.com:50105/TSV_PROD_DB_A_C' Type \q to quit, \? for a list of available commands auto commit mode: on sql>\</factorydata/prddata02/prd/crosscut_output/create_desc_it_dsj_campbell_33423.sql [monetadmin@lnx1490 ~]$ Thank You, Gautham

1 0

MonetDB Crashed - Version Jul2017-SP4 - GDKmmap(327680) fails, try to free up space memory in use=48376596752, virtual memory in use=102304729360
by Murthy, Gautham 12 Jul '18

12 Jul '18

Hello All, We had a crash reported on our MonetDB database, Below are the error messages. Can you share any insights on the below reported issue. There were no memory/cpu crunch during the crash, System had ample resource available. MonetDB Version - Jul2017-SP4 2018-07-03 06:33:40 ERR TSV_PROD_DB_A315293<https://monetdbsolutions.atlassian.net/wiki/display/SUPPORT/315293>: #GDKmmap(327680) fails, try to free up space memory in use=48376596752,virtual memory in use=102304729360<https://monetdbsolutions.atlassian.net/wiki/display/SUPPORT/memory+in+use%3…> 2018-07-03 06:33:40 ERR TSV_PROD_DB_A315293<https://monetdbsolutions.atlassian.net/wiki/display/SUPPORT/315293>: #GDKmmap(327680) fails, try to free up space memory in use=48368938424,virtual memory in use=102295694776<https://monetdbsolutions.atlassian.net/wiki/display/SUPPORT/memory+in+use%3…> 2018-07-03 06:33:40 ERR TSV_PROD_DB_A315293<https://monetdbsolutions.atlassian.net/wiki/display/SUPPORT/315293>: #GDKmmap(327680) fails, try to free up space memory in use=48367612864,virtual memory in use=102294369216<https://monetdbsolutions.atlassian.net/wiki/display/SUPPORT/memory+in+use%3…> 2018-07-03 06:33:40 ERR TSV_PROD_DB_A315293<https://monetdbsolutions.atlassian.net/wiki/display/SUPPORT/315293>: #GDKmmap(327680) fails, try to free up space memory in use=48374211792,virtual memory in use=102300968144<https://monetdbsolutions.atlassian.net/wiki/display/SUPPORT/memory+in+use%3…> 2018-07-03 06:33:47 ERR TSV_PROD_DB_A315293<https://monetdbsolutions.atlassian.net/wiki/display/SUPPORT/315293>: #GDKmmap(524288) fails, try to free up space memory in use=48312652864,virtual memory in use=103295521856<https://monetdbsolutions.atlassian.net/wiki/display/SUPPORT/memory+in+use%3…> 2018-07-03 06:34:33 MSG merovingian315279<https://monetdbsolutions.atlassian.net/wiki/display/SUPPORT/315279>: database 'TSV_PROD_DB_A' has crashed after start on 2018-07-02 04:52:12, attempting restart, up min/avg/max: 2h/6d/4w, crash average: 1.00 0.60 0.20 (14-8=6) 2018-07-03 06:34:38 ERR control315279<https://monetdbsolutions.atlassian.net/wiki/display/SUPPORT/315279>: !monetdbd: an internal error has occurred 'unknown or impossible state: 4' 2018-07-03 06:34:40 ERR merovingian315279<https://monetdbsolutions.atlassian.net/wiki/display/SUPPORT/315279>: client error: unknown or impossible state: 4 2018-07-03 06:34:40 ERR control315279<https://monetdbsolutions.atlassian.net/wiki/display/SUPPORT/315279>: !monetdbd: an internal error has occurred 'unknown or impossible state: 4' 2018-07-03 06:34:41 ERR merovingian315279<https://monetdbsolutions.atlassian.net/wiki/display/SUPPORT/315279>: client error: unknown or impossible state: 4 2018-07-03 06:34:41 ERR control315279<https://monetdbsolutions.atlassian.net/wiki/display/SUPPORT/315279>: !monetdbd: an internal error has occurred 'unknown or impossible state: 4' What was running during the crash ? : Users were dropping, recreating tables and loading data. monetadmin@lnx1535 ~<https://monetdbsolutions.atlassian.net/wiki/display/SUPPORT/monetadmin%40ln…>$ cat /etc/redhat-release CentOS Linux release 7.4.1708 (Core) monetadmin@lnx1535 ~<https://monetdbsolutions.atlassian.net/wiki/display/SUPPORT/monetadmin%40ln…>$ free -g total used free shared buff/cache available Mem: 1007 199 419 1 388 804 Swap: 31 0 31 monetadmin@lnx1535 ~<https://monetdbsolutions.atlassian.net/wiki/display/SUPPORT/monetadmin%40ln…>$ cat /proc/cpuinfo|grep -i processor|wc -l 128 Thank You, Gautham

1 0

Memory management, 8 years later
by Roberto Cornacchia 28 Jun '18

28 Jun '18

Hi all, 8 yeas after this interesting thread about using ulimit to limit the data segment size ( https://www.monetdb.org/pipermail/users-list/2010-March/004007.html) a few things have changed and I find myself a bit lost again about memory management. At some point (can't remember when) mserver5 stopped respecting ulimit. No matter how I change the limits, it always sees all available physical ram. Does any other mechanism exist? The issue I'm facing all the time, and for which I find very little documentation, is: - multiple dbfarms, possibly tens of mserver5 instances, running on the same (virtual) machine, plus other applications - Not necessarily, but most times each dmfarm is served by a Docker container - CPU and memory resources need to be share fairly (ideally, controlling priorities) - No mserver5 should ever be killed because of memory issues Now, especially when using Docker, there are means to control the maximum memory size allowed to each process. However, the resolution in case of overusage is to kill the process. That is of course not a solution. What I'm looking for is: can I set a cap to resources *at process level*, in a way that mserver5 will just *see* those, make the best out of them, and never try to use more? (yes, one way would be a VM, but far from ideal for me) Does anyone have experience with / tips about multi-dbfarm environments? Thanks! Roberto

2 2

Troublesome Query
by Doherty, Adam 23 Jun '18

23 Jun '18

Hi all: I'm trying to figure how to calculate the time delta between two events. My events have a timestamp, event source, event type, and an event state and event id. So an event looks like - event_date_time event_source event_type event_state event_id 2018-06-22 09:05:07.000000 MODULE123 TYPEABC ACK 24567890 2018-06-22 11:32:14.000000 MODULE123 TYPEABC UNACK 24812266 This makes up a unique "event", start to finish. However I have hundreds or thousands of entries between and I want be able to group them together in their unique pairings as above. I'm not sure how to wrap my head around this one. Adam Doherty

2 1

Performance issues when streaming records from table.
by Manuel Pagliai 20 Jun '18

20 Jun '18

We are interested in adding support to import records from Monetdb tables in the ETL we are developing. We have performance issues when executing queries like: select col0, col1, ... coln from <table> on tables with a large number of records. What I am concerned about is the time spent upfront, before the first record is retrieved: once records become available we can start processing them. For example, on my machine, executing a executing a select * from <table> query on a 68 million records table takes around 24 seconds for the query to execute, before it is actually possible to start retrieving records from the server response. Queries are submitted via the JDBC driver. Is there a more efficient way of streaming records from a table ? Thanks, Best regards, *Manuel Pagliai* Visokio Ltd www.visokio.com +44 20 7351 4646

1 0

Announcement: New Mar2018-SP1 Bugfix release of MonetDB suite
by Panagiotis Koutsourakis 20 Jun '18

20 Jun '18

The MonetDB team at CWI/MonetDB BV is pleased to announce the Mar2018-SP1 bugfix release of the MonetDB suite of programs. More information about MonetDB can be found on our website at <https://www.monetdb.org/>. For details on this release, please see the release notes at <https://www.monetdb.org/Downloads/ReleaseNotes>. As usual, the download location is <https://dev.monetdb.org/downloads/>. Mar 2018-SP1 bugfix release (11.29.7) Build Environment * On Linux and Unix, try not to link to libraries multiple times: that only causes the code (and worse, the variables in those libraries) to be included multiple times. On Windows, we do need to link to libraries multiple times (once for each DLL we create if the DLL references anything from the library) but that doesn't cause the linked library to be loaded multiple times. SQL Frontend * Corrected the definition of view: sys.ids. Bug Fixes * 6562: Sqlitelogictest crash on group by query with not in operator * 6565: Sqlitelogictest crash on complex select query with coalesce call * 6566: Sqlitelogictest unavailable calc.- MAL operations * 6568: Sqlitelogictest crash on complex case query * 6569: Sqlitelogictest select query with not between cause with wrong results * 6570: Sqlitelogictest select coalesce undefined calc * 6572: ordered index Error in optimizer garbageCollector * 6573: Sqlitelogictest crash on complex select query with case statement * 6574: server crashed could not find ordered index while creating a table * 6576: Sqlitelogictest aritmetic expressions with negative numbers handling * 6577: creating temp table kills performance of the original query * 6578: One two-tuple insert gives different results than two single inserts * 6581: Join condition errors. * 6583: Fixed size string concatenation with integer results in fixed size string of size 0 * 6584: SELECT FROM REMOTE TABLE WHERE IS NOT NULL produces wrong result * 6585: Nested Merge tables cause an infinite loop in rel_optimizer * 6587: Sqlitelogictest crash on complex case statement * 6589: Sqlitelogictest crash on complex on complex expression * 6594: Sqlitelogictest crash on complex case statement * 6595: Remote decimal division triggers assertion / returns wrong answer * 6598: Python 3.4 not supported (due to usage of Py_DecodeLocale) * 6600: Sqlitelogictest queries fail to execute * 6601: "where is null" clause on remote table causes problem with next query * 6602: Sqlitelogictest wrong results in IN query * 6603: Sqlitelogictest: Aggregation query with distinct clause produces duplicated rows

1 0

pymonetdb or Python UDF
by Doherty, Adam 17 Jun '18

17 Jun '18

Hello all: This is more of curiosity but I have data in my events table currently trapped in a nvarchar field. I am wondering if I can build a table and do execute a select into with a python UDF pulling data from the existing fields plus the values of my nvarchar field. My string data has the following format - "key1=value, key2=val, key3=value"... I want to break out the data first by splitting on the , and then the = Keys would be the columns in the table. I can do this with a python script and pymonetdb but a UDF would be faster. Adam Doherty Process Control Systems Administrator Kearl Technical, Imperial 505 Quarry Park Blvd SE W3B.224 Calgary, AB T2C 5N1 adam.doherty(a)esso.ca<mailto:adam.doherty@esso.ca> P 587.476.4640<tel:+15874764640> M 403.869.1636<tel:+14038691636> imperialoil.ca<http://www.imperialoil.ca/Canada-English/default.aspx> | Twitter<http://www.twitter.com/imperialoil> | YouTube<http://www.youtube.com/imperialoil>

3 3

joinable generate_series with arguments from a table
by Stefan de Konink 14 Jun '18

14 Jun '18

Goodmorning, At this moment there is no functional compatibility between the PostgreSQL and MonetDB with respect to the generate_series function. The range in PostgreSQL is inclusive, in MonetDB exclusive. More importantly generate_series can't be used with argument that is joined from a different table, MonetDB just seems to support a subquery, hence joining that back without a primary is impossible. PostgreSQL; select generate_series(a, b), a from (select 1 as a, 2 as b) as a; generate_series | a -----------------+--- 1 | 1 2 | 1 (2 rows) MonetDB; select * from generate_series((select 1 as a, 2 as b)); +-------+ | value | +=======+ | 1 | +-------+ If I would like to functionally achieve the following; I have list that states the speeds over partial segments, identified by linear referencing. From 10 meter to 12 meter, the distance was 2 meter and speed was 2s. 10 2 2 I would like to expand these values to segments of one meter, hence end up with a table that would state: 10 2 2 11 2 2 12 2 2 Now I am aware of the Python bindings and I could achieve what I wanted by the function below, but I would find generate_series still very practical. DROP FUNCTION expand_per_meter; CREATE FUNCTION expand_per_meter(trip_hash BIGINT, userstopcode STRING, distancesincelastuserstop INTEGER, s INTEGER, t INTEGER) RETURNS TABLE(trip_hash BIGINT, userstopcode STRING, distancesincelastuserstop INTEGER, s INTEGER, t INTEGER) LANGUAGE PYTHON { result = dict() result['trip_hash'] = [] result['userstopcode'] = [] result['distancesincelastuserstop'] = [] result['s'] = [] result['t'] = [] for i in range(0, len(trip_hash)): expanded = range(distancesincelastuserstop[i], distancesincelastuserstop[i] + s[i] + 1) expand = len(expanded) result['trip_hash'] += [trip_hash[i]] * expand result['userstopcode'] += [userstopcode[i]] * expand result['distancesincelastuserstop'] += expanded result['s'] += [s[i]] * expand result['t'] += [t[i]] * expand return result }; -- Stefan

4 4

Re: Combine a table-returning function with the source table
by Mark Clements 13 Jun '18

13 Jun '18

Stefan, This is a good question. For the specific example, you are quite correct. However, the motivation for the question came from using the MonetDB-rmath extension and embedded R: often it is more efficient to calculate a number of values (e.g. the confidence intervals and p-values) together and then return all of the values as a table. This probably also reflects that I am a statistician and an old R user. The recent article by Raasveldt suggested that analysis objects from Python could be stored natively rather than being serialised BLOBs. This could be further extended to storing R objects natively. Members of the objects could then be extracted as they are needed. Admittedly, this would lose many relational advantages. My suggestion is halfway, where R and Python can be used for analytics, with simple relational storage of the results. Actually, is there any way to optimise a lateral call using BATs? -- Mark On 13 Jun 2018 17:43, Stefan Manegold <Stefan.Manegold(a)cwi.nl> wrote: Hi Mark, just out of curiosity, (in the given case/example) what's wrong with a simple select *, mean-1.96*sd as lci, mean+1.96*sd as uci from test; or create or replace function ci(se double) returns double begin return 1.96*se; end; select *, mean-ci(sd) as lci, mean+ci(sd) as uci from test; in case you really want/need to avoid the "redundant" occurrence or "1.96" ... Thanks! Best, Stefan ----- On Jun 13, 2018, at 5:30 PM, Mark Clements mark.clements(a)ki.se wrote: > Jennie, > > Thank you for replying to this inquiry - it's appreciated. > > Your solution gives the right result - but it requires changing the > function call and will not generalise easily to other column types or > other number of covariates. If we had another table such as: > > drop table test; > create table test as select 1 as id, 2 as sex, cast(3 as double) as > mean, cast(4.0 as double) as sd; > -- and repeating the function > create or replace function cis(mu double, se double) returns table(lci > double, uci double) > begin return select mu-1.96*se, mu+1.96*se; > end; > > we can get all of the test table combined with the confidence interval by: > > -- row_number() > select * from > (select *, row_number() over() as rid from test) as t1 > natural join > (select *, row_number() over() as rid from cis((select mean,sd from > test))) as t2; > -- or using lateral > select * from test, lateral cis(test.mean,test.sd); > > The row_number solution is fast, and the lateral solution is simple. I > was hoping for: > > select *, cis(mean,test).* from test; > > Again, thank you for your help. > > -- Mark > > On 06/08/2018 01:53 PM, Ying Zhang wrote: >> Hai Mark, >> >> I’m just wonder if this gives you wat you want: >> >> create function cis(mu double, se double) >> returns table(mu_out double, lci double, uci double) >> begin >> return select mu, mu-1.96*se, mu+1.96*se; >> end; >> >> Regards, >> Jennie > > _______________________________________________ > 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/<http://www.CWI.nl/~manegold/> | Science Park 123 (L321) | | +31 (0)20 592-4212 | 1098 XG Amsterdam (NL) | _______________________________________________ users-list mailing list users-list(a)monetdb.org https://www.monetdb.org/mailman/listinfo/users-list

1 0

Combine a table-returning function with the source table
by Mark Clements 13 Jun '18

13 Jun '18

For using the MonetDB-rmath extension, I was interested in using table-returning functions to return multiple values. Is there a fast and elegant way to combine the results with the source table in SQL? To make this concrete, consider some test data and a function to calculate a 95% confidence interval from a mean and standard error: drop table test; create table test as select cast(value as double) as value from generate_series(0,5); drop function cis(double,double); create function cis(mu double, se double) returns table(lci double, uci double) begin return select mu-1.96*se, mu+1.96*se; end; We can then call the table-returning function: select * from cis((select value, 1.0 from test)); If we want to re-join the results with the source table, we could use row_number() or use lateral: -- Using row_number: select * from (select *, row_number() over() as rid from test) as t1 natural join (select *, row_number() over() as rid from cis((select value,1.0 from test))) as t2; -- Using lateral: select * from test, lateral cis(test.value, cast(1.0 as double)) as t2; I believe that the latter will lead to a slow loop, even when BAT functions are available. The table-returning function can be used in the select statement only for a scalar expression: select cis(0,1).*; -- ok select cis(0,1).* from test; -- Error: no such operator 'cis' select cis(value,1).* from test; -- Error: identifier 'value' unknown Again, is there a fast and elegant way to combine the source table with a table-returning function in SQL? Kindly, Mark.

3 4

Temporal Function Issues
by Doherty, Adam 13 Jun '18

13 Jun '18

Hello all: I am trying to find the most efficient way to filter results for those matching today only. My events table has 550 million rows and I want a view as follows- CREATE VIEW events_today AS SELECT * FROM events WHERE str_to_date(timestamp_to_str(event_timestamp, '%Y-%m-%d'), '%Y-%m-%d') = CURRENT_DATE which should give me all events with today's date. Running the SELECT by itself also never returns. However, the query just hangs without ever returning. I'm running 11.29.3 (Mar2018) on CentOS 7 with 128GB and 16 Cores. Adam

2 2

High Memory Usage - Sever Not Releasing Resources?
by Doherty, Adam 08 Jun '18

08 Jun '18

Hi all: I ran into an issue yesterday when I ran a simple group count query - The query took 7 minutes, 13 seconds to run. Query was as follows - SELECT zone, count(*) FROM chronicle.events WHERE event_type = 'mb' GROUP BY zone This table, which a merge table currently has 546 million events. The merge table is made up of currently two merge table each representing a year (events_2018, and events_2017). Those merge tables are each made up of four tables, events_year_zone_(1-4) I have a scheduled task which inserts events into the relevant tables every 5 minutes from 5 Microsoft SQL Servers. Roughly 12,000 events broken across 5 inserts (one for each server). The import process is handled with a C# Windows service that utilizes ODBC to talk to MonetDB. The server is currently MonetDB v11.27.11 (July2017-SP3) running on VMware 6.5 in a CentOS 7 VM with 64GB of RAM, and 16 CPU cores. When I ran into this issue yesterday, the mserver5 process was utilizing 90% of available RAM. This was making my insertion task take longer and longer each time, and simple queries like the one above took minutes. Logged into the server via SSH, the input lag as a result of the almost total resource usage caused me to reboot the server. I restarted the VM, and the same query took 20 seconds. I'd like to sort where this issue might be coming from, but I'm not sure where to look. This server is currently part of a proof-of-concept project that will soon be going to production. Any guidance is greatly appreciated. Adam

2 2

ODBC Error on Insert
by Doherty, Adam 06 Jun '18

06 Jun '18

Hi All: I'm having some issues using ODBC with C#. I am trying to insert the contents of a DataTable into Monet using a foreach loop with MonetDB ODBC client under Windows. This is the error I receive... System.Data.Odbc.OdbcException (0x80131937): ERROR [42000] [MonetDB][ODBC Driver 11.27.11]syntax error, unexpected ',' in: "execute 5 (timestamp '2018-06-04 21:22:25',8651,'EVENT','','SYSTEM','1810_INFRA_" This is my loop which is wrapped in a transaction - foreach (DataRow row in events.Rows) { cmd.CommandText = String.Format("INSERT INTO chronicle.events_{0}_zone_{1} VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)", year, zone); cmd.Parameters.AddWithValue("@date_time", row["Date_Time"]); cmd.Parameters.AddWithValue("@fracsec", row["FracSec"]); cmd.Parameters.AddWithValue("@event_type", row["Event_Type"]); Truncated for brevity. I'm not sure where the error in my insert statement is because the error message itself appear to be truncated. Very frustrating as it hangs on the first row. Adam

2 1

new MonetDB SQL overview documentation
by martin van dinther 31 May '18

31 May '18

1 0

JIT implementation of the rmath extension
by Mark Clements 30 May '18

30 May '18

The JIT functions by Mark Raasveldt provide a powerful alternative approach to bring C and C++ code into MonetDB. I have provided an alternative implementation of the MonetDB-rmath extension using JIT functions. The JIT implementation is considerably shorter and simpler, and slightly more general - where the code can deal with function arguments that are any combination of scalars and BATs. However, there are indications that the functions are considerably slower. As a minor point, I needed to rename the functions (e.g. using r_ as a prefix) -- otherwise the function names would collide with the function signatures in Rmath.h. To use the JIT-based rmath extension: git clone https://github.com/mclements/MonetDB-rmath.git git checkout jit make sudo make install monetdb create test monetdb release test monetdb set embedc=yes test mclient -d test -s "select r_pnorm(1.96,0,1);" As a challenge for JIT-based extensions, new C functions (e.g. new functions that call Rmath.h, and for which we want to call the new functions in MonetDB) will need a header file that is available to MonetDB. A fragile (non-portable?) solution to find the MonetDB include folder is to use: MONETDBINCLUDE = $(patsubst -I%,%,$(firstword $(shell pkg-config --cflags-only-I monetdb5))) in the Makefile. This assumes that the MonetDB include folder is the first include folder in CFLAGS. The extension-specific header for any new functions can then be copied to that folder: cp rmath_ext.h $(MONETDBINCLUDE) I hope that this is useful. Sincerely, Mark.

1 0

Error: Failed to import - Leftover Data
by Doherty, Adam 23 May '18

23 May '18

Hi all: I am receiving an error "Failed to import table ... Leftover data ..." I am using a simple BASH script and the mclient tool to import my data from hundreds of CSV files. The fields are pipe | separated, text values are surrounded with double-quotes, and rows end with LF (\n). This is the command I am using to process the files - for csvfile in `ls | grep -i 2018.*\.csv$`; do mclient -d mydatabase -s "COPY INTO table FROM '/path/to/csv/files/$csvfile' USING DELIMITERS '|', '\n', '\"'"; done I have a .monetdb file setup in my home directory so as not to need the username and password. Thanks kindly, Adam Doherty

3 5

MonetDB Memory usage and release cached memory
by Murthy, Gautham 17 May '18

17 May '18

Hello, How do we make MonetDB release memory after usage. We are seeing high cached memory on the DB Servers and We also see queries failing with the below error - Memory Allocation Failure : Total Memory - 1123348 Physical Memory - 1123348 Virtual Memory - 2667052 How do we overcome this issue? Thank You, Gautham

1 0

Problem/bug with remote tables
by Bernát Marcell 10 May '18

10 May '18

Hello everyone, I believe I've encountered a bug with the remote table functionality. Here are the steps to replicate it. Tested with MonetDB 11.29.3, both on Ubuntu 14.04.5 LTS and Ubuntu 16.04.4 LTS. The problem persists even after restarting the DB farms and the MonetDB daemons. On node 1 Create a normal table, insert some data and check its contents: create table testtable(id int not null, name text, valid boolean); insert into testtable (id, name) values (1, '1'); insert into testtable (id, name) values (2, '2'); insert into testtable (id) values (3); select * from testtable; +------+------+-------+ | id | name | valid | +======+======+=======+ | 1 | 1 | null | | 2 | 2 | null | | 3 | null | null | +------+------+-------+ 3 tuples (6.446ms) On node 2 Create a remote table and check its contents to make sure it works: create remote table testtable(id int not null, name text, valid boolean) on 'mapi:monetdb://node1:50000/dbfarm'; select * from testtable; +------+------+-------+ | id | name | valid | +======+======+=======+ | 1 | 1 | null | | 2 | 2 | null | | 3 | null | null | +------+------+-------+ 3 tuples (6.446ms) Then try to select anything with a WHERE clause: select * from testtable where name is null; +------+------+-------+ | id | name | valid | +======+======+=======+ | 3 | null | null | +------+------+-------+ 1 tuple (5.186ms) It seemingly works fine, but now when you try to select anything else, you get the same result: select * from testtable; +------+------+-------+ | id | name | valid | +======+======+=======+ | 3 | null | null | +------+------+-------+ 1 tuple (4.466ms) If you log out from mclient and then back in, select * works fine again, so there's no data loss. However, even if you try the opposite of the previous query (is not null instead of is null), you get the same result: select * from testtable where name is not null; +------+------+-------+ | id | name | valid | +======+======+=======+ | 3 | null | null | +------+------+-------+ 1 tuple (5.358ms) Can someone else please try this to confirm it's a bug and not something on my end? Thanks in advance. Best regards, Marcell

3 4

Announcement: MonetDB-rmath
by Mark Clements 09 May '18

09 May '18

Dear all, A first version for MonetDB-rmath is now available. It includes approximately 90% of the functions in Rmath.h, including [rpdq](norm|unif|gamma|beta|lnorm|chisq|nchisq|f|t|binom|cauchy|exp|geom|hyper| nbinom|pois|weibull|logis|wilcox|signrank) and another 40 functions. The current implementation uses m4 for code generation and requires the r-mathlib package under Debian. One can clone the GitHub repository and install using: git clone https://github.com/mclements/MonetDB-rmath.git cd MonetDB-rmath make sudo make install You will then need to use a fresh database. An example script is: -- calculate the quantile and cumulative distribution function for a unit normal select qnorm(0.025,0,1), pnorm(-1.96,0,1); -- calculate E(Z^2) for Z~Normal(0,1) select sum(R_pow(value,2)*0.01*dnorm(value,0,1)) from sys.generate_series(-5.0,5.0,0.01); -- random numbers select set_seed(1,2); select rpois(value*0+100) from sys.generate_series(1,10); -- ok select rpois(100) from sys.generate_series(1,10); -- repeats the values 10 times:-< All of the functions are currently scalars. Next steps include adding BAT functions, including some of the constants at the beginning of Rmath.h, and adding some statistical tests. As an example, I have included poissonci(y,boundary) for an exact Poisson confidence interval for count y for a specific boundary (1=left, 2=right) with default confidence level of 95%: select poissonci(10,1), poissonci(10,2); The equivalent code in R would be poisson.test(10). For confidence intervals, it would be nice to have a type that is a pair of doubles. Sincerely, Mark.

2 2

Buggy attempt at MonetDB-rmath
by Mark Clements 03 May '18

03 May '18

I am trying to adapt MonetDB-gsl to use the rmath library. My code is at: https://github.com/mclements/MonetDB-rmath Under Ubuntu 16.04 LTS, I used: sudo apt install r-mathlib git clone https://github.com/mclements/MonetDB-rmath cd MonetDB-rmath make sudo make install // start up MonetDB server with a test database mclient -d test -s "select sys.pchisq(20.0,5.0);" This compiles but when I try "select sys.pchisq(20.0,5.0);" I get the error: SELECT: no such binary operator 'pchisq(decimal,decimal)' Any guidance would be appreciated. (You may ask why one would want to do this when one could use embedded R - I wanted to see how fast and easy it was to use the rmath library.) Kindly, Mark.

2 3

Copy a set database files and register on another database
by Murthy, Gautham 24 Apr '18

24 Apr '18

Hello, On MonetDB, Can we list the files for a schema and copy the files and register to another database ? We would want to know if we can - List the files for a specific table or a schema(all tables under a schema)? Copy the files to target database and register them on the other database Thank You,

1 0

MonetDB Read-Only without restarting the database
by Murthy, Gautham 24 Apr '18

24 Apr '18

Hello, This is regarding read-only mode in MonetDB. Can we mark the database read-only without restarting the database ? If Yes, How do we do it ? What are the options available to copy a database without restarting to another node or DB Farm? Msqldump the export and import is serialized, How do I increase the performance of exp/imp? Thank You, Gautham

1 0

mclient backward compatibility
by Roberto Cornacchia 18 Apr '18

18 Apr '18

mclient Mar2018 is not really compatible with mserver5 Jul2017. Not a real issue for me, but I'm just curious: is mclient supposed to be compatible with an earlier release of mserver5 (at least the previous release)? $ mclient x -u monetdb password: Welcome to mclient, the MonetDB/SQL interactive terminal (Mar2018) Database: MonetDB v11.27.14 (unreleased), 'mapi:monetdb:// photon.hq.spinque.com:55500/x' Type \q to quit, \? for a list of available commands auto commit mode: on sql>-- \d silently fails (nothing dumped) sql>\d sql> sql>-- \d on a table doesn't find table comments (introduced in Mar2018) sql>\d tables SELECT: no such table 'comments' Shouldn't queries that rely on the system catalog be defined server-side?

2 1

double precision IS NULL does not work ?
by Nicolas Paris 16 Apr '18

16 Apr '18

Hello I have those DOUBLE PRECISION fields, sometimes containing null values: sql>select count(1), range_low from omop.measurement group by range_low; +-----------+--------------------------+ | L2 | range_low | +===========+==========================+ | 304875924 | null | | 9448250 | 50 | | 16519843 | 0 | | 13198932 | 10 | | 14364002 | 20 | | 3964808 | 30 | | 2998528 | 3 | | 855829 | 2 | +-----------+--------------------------+ 8 tuples However, I am not able to filter based on is null: sql>select count(1) from omop.measurement where range_low is null; +------+ | L3 | +======+ | 0 | +------+ 1 tuple I am not able to understand if this behavior is normal regarding the documentation: "The types REAL, FLOAT and DOUBLE map to the underlying implementation system. No special attention is given to the value NaN." Thanks

2 1

mserver5 process consuming resources while not busy
by Nicolas Paris 15 Apr '18

15 Apr '18

Hello Sorry, I gave up digging in the mailing list because didn't found search functionality there ( https://www.monetdb.org/pipermail/users-list/ ) Then I wonder why I get that result when running "top" command on my server: > PID UTIL. PR NI VIRT RES SHR S %CPU %MEM TEMPS+ COM. > 3297 monetdb 20 0 53,007g 9,028g 8,396g S 100,5 28,8 117:20.95 mserver5 There is no query running AFAIK (select * from sys.queue is empty). I loaded my database several hours before from scratch, and then ran some sql. How can I figure out what appends in the background ? Is it indexing based on queries I made ? Apparently the disk is stable, by mean there is no creation of new indexes, or files under the hood. Any insight welcome, Thanks,

1 0

MonetDB Settings and Open Files
by Murthy, Gautham 14 Apr '18

14 Apr '18

Hello, We have MonetDB open files usage reaching 65k and beyond. How do we minimize the openfiles, We see most of them are mapped to memory. What is the storage(); function? What does the storage() function do when we execute it ? The usage of open files drastically reduced when the select *from storage() was executed on the database. What are the effects of running the select *from storage(); when users are accessing the database? What is the maximum size of database MonetDB can support ? Does running the storage() function when there is a large set of open files crash the database ? Do we need to set any system level parameters ? shmmax, file-max etc., Do we have any standard settings as prerequisites that needs to be considered if we are hosting databases ranging 1TB to 2TB? What should be the open files setting if we have 6-10 databases with size ranging from 100GB to 2TB ? Do we have options to export specific schemas rather that complete database or tables? Request you to please review and share the above information. Open files Test Case - DB size 630Gb Max Open files set to - 65536 [oracle@lnx1386 scripts]$ sh 10min_ofc.sh lsof -u oracle|wc -l Timestamp: 04-14-18 01:56:34 Open Files: 6559 lsof -u oracle|grep -i tcp|wc -l Timestamp: 04-14-18 01:56:34 Open Files Of TCP: 21 lsof -u oracle | grep -i /data/MYDBFARM_DIR/SV11DMP_DBFARM/TSV_PROD_DB | wc -l Timestamp: 04-14-18 01:56:35 Open Files - DB Files : 5864 lsof -u oracle | grep -i protocol: TCP | wc -l Timestamp: 04-14-18 01:56:35 Open Files Of - protocol: TCP : 2 lsof -u oracle | grep -i CLOSE_WAIT | wc -l Timestamp: 04-14-18 01:56:35 Open Files of TCP - CLOSE_WAIT: 0 lsof -u oracle | grep -i ESTABLISHED | wc -l Timestamp: 04-14-18 01:56:36 Open Files Of TCP - ESTABLISHED: 15 netstat -an |grep tcp|wc -l Timestamp: 04-14-18 01:56:36 netstat -an |grep tcp : 62 [oracle@lnx1386 scripts]$ sh /home/oracle/scripts/purge_op_fl.sh 50005 TSV_PROD_DB 1>/home/oracle/scripts/logs/purge_op_fl.stout 2>/home/oracle/scripts/logs/purge_op_fl.sterr [oracle@lnx1386 scripts]$ cat /home/oracle/scripts/purge_op_fl.sh /usr/bin/mclient -p ${1} -d ${2} < /home/oracle/scripts/purge_op_fl.sql [oracle@lnx1386 scripts]$ cat /home/oracle/scripts/purge_op_fl.sql select * from storage(); [oracle@lnx1386 scripts]$ [oracle@lnx1386 scripts]$ sh 10min_ofc.sh lsof -u oracle|wc -l Timestamp: 04-14-18 02:09:49 Open Files: 862 lsof -u oracle|grep -i tcp|wc -l Timestamp: 04-14-18 02:09:49 Open Files Of TCP: 25 lsof -u oracle | grep -i /data/MYDBFARM_DIR/SV11DMP_DBFARM/TSV_PROD_DB | wc -l Timestamp: 04-14-18 02:09:50 Open Files - DB Files : 163 lsof -u oracle | grep -i protocol: TCP | wc -l Timestamp: 04-14-18 02:09:50 Open Files Of - protocol: TCP : 2 lsof -u oracle | grep -i CLOSE_WAIT | wc -l Timestamp: 04-14-18 02:09:50 Open Files of TCP - CLOSE_WAIT: 0 lsof -u oracle | grep -i ESTABLISHED | wc -l Timestamp: 04-14-18 02:09:50 Open Files Of TCP - ESTABLISHED: 19 netstat -an |grep tcp|wc -l Timestamp: 04-14-18 02:09:50 netstat -an |grep tcp : 66 [oracle@lnx1386 scripts]$ Thank You, Gautham

2 1

Window Functions
by Doherty, Adam 13 Apr '18

13 Apr '18

Hello: I am trying to build an event tree from a series timestamps, however for each event I want to be able to return the preceding and succeeding events +/- 2 seconds to capture a complete series of events. I believe this is called lead/lag in other databases. I'm not sure how to proceed in building a CTE, short of pulling all the events out an running a filter function on them in Python. Thanks kindly, Adam Doherty

1 0

statistical MODE function
by Sharma, Sreejith 13 Apr '18

13 Apr '18

All, Is there any statistical MODE function available in MonetDB? If not, has anyone tried it for custom function? Need help here. Regards, Sreejith

2 1

INSERT using multiple cores (inspired by COPY from CSV in LOCKED mode)
by Anton Kravchenko 12 Apr '18

12 Apr '18

Hi there, 1) It looks like INSERTing into tables is bounded by a single CPU. And it kills performance of a query that normally uses multiple CPUs... CREATE TEMP TABLE t1 AS (some 'select query' that uses multiple cores when run standalone) ON COMMIT PRESERVE ROWS; or INSERT into t1 (some 'select query' that uses multiple cores when run standalone); 2) Do you think you could implement INSERT with LOCKED option? (like it was done for COPY from CSV in LOCKED mode) Please let me know whether you think it's doable. Thank you, Anton

1 1

Server Parameter Settings
by Murthy, Gautham 11 Apr '18

11 Apr '18

Hello, Need inputs on the initial prerequisites om server parameter settings - Database Size ranges from 300Gb to 1TB. Optimal sysctl parameters to be set for the server with below configuration - [oracle@lnx1386 ~]$ lscpu Architecture: x86_64 CPU op-mode(s): 32-bit, 64-bit Byte Order: Little Endian CPU(s): 128 On-line CPU(s) list: 0-127 Thread(s) per core: 2 Core(s) per socket: 16 Socket(s): 4 NUMA node(s): 4 Vendor ID: GenuineIntel CPU family: 6 Model: 79 Model name: Intel(R) Xeon(R) CPU E7-4850 v4 @ 2.10GHz Stepping: 1 CPU MHz: 1199.953 BogoMIPS: 4197.77 Virtualization: VT-x L1d cache: 32K L1i cache: 32K L2 cache: 256K L3 cache: 40960K NUMA node0 CPU(s): 0,4,8,12,16,20,24,28,32,36,40,44,48,52,56,60,64,68,72,76,80,84,88,92,96,100,104,108,112,116,120,124 NUMA node1 CPU(s): 1,5,9,13,17,21,25,29,33,37,41,45,49,53,57,61,65,69,73,77,81,85,89,93,97,101,105,109,113,117,121,125 NUMA node2 CPU(s): 2,6,10,14,18,22,26,30,34,38,42,46,50,54,58,62,66,70,74,78,82,86,90,94,98,102,106,110,114,118,122,126 NUMA node3 CPU(s): 3,7,11,15,19,23,27,31,35,39,43,47,51,55,59,63,67,71,75,79,83,87,91,95,99,103,107,111,115,119,123,127 [oracle@lnx1386 ~]$ [oracle@lnx1386 ~]$ free -g total used free shared buff/cache available Mem: 2015 17 1945 0 52 1996 Swap: 63 0 63 [oracle@lnx1386 ~]$ I/O - Storage : 10TB SSD Disks Thank You,

1 0

MonetDB - Issue with "too many open files"
by Sharma, Sreejith 11 Apr '18

11 Apr '18

Hi All, We encountered an issue with "too many open files", however not able to trace the root cause . Any information or suggestions here will be very helpful. We have a DB which has around 10K tables with a volume of 300GB approx sizing. The default "File Descriptor Limit" of 16K was set initially and when we encountered "too many open files" issue with parallel loads. Later, it's increased to 65K and upsized the ulimit assuming this should go through. However, at some point the issue reoccurred now with concurrent select or too many concurrent connections. Is there any optimal "file open" size MonetDB recommends? Or is there an option to clean up these? We noticed too many socket connection also opened which is not released. [cid:image001.png@01D3D1F0.58291540] Regards, Sreejith

1 0

Streaming queries
by Jeff Burnett 10 Apr '18

10 Apr '18

Hello, I am searching a CEP to do streaming queries that is embedded and written in C/C++. I also want it to work on linux. Except for the language requirement, Nesper/Esper would be a perfect fit for me. I have wondered if an extension of MonetDbLite would do the job. For me all the data arrives as a time series, like the price of a stock. This can either happen in real time or the data might be replayed from stored history to test other parts of the system. The goal is to build arbitrary queries that fire events when their conditions are met. For example, a listener may be attached to a query that fires if the value of the input stream has changed more than x% over the last n observations. Other events might fire every day at 4pm, indicating the end of the day. As I said, Nesper/Esper do this out of the box, but Nesper is in .net and esper is in Java. My code base is in c++ and JNI is unappealing. Does MonetdbLite handle these things already? Where might I run into difficulty in trying to adapt monet for this project? Any help is appreciated. Please let me know if I didn’t explain the task very well. Jeff

1 0

MonetDB - Transaction aborted issue
by Sharma, Sreejith 10 Apr '18

10 Apr '18

All, We are getting below error while creating a table. In the current session no other bulk load has happened. CREATE TABLE IF NOT EXISTS "SCHEMA_NAME"."TABLE_NAME" AS SELECT * FROM " SCHEMA_NAME "."TABLE_NAME" WHERE 1=0; INSERT INTO "SCHEMA_NAME"."TABLE_NAME" SELECT * FROM "SCHEMA_NAME"."TABLE_NAME2"; ERROR = !COMMIT: transaction is aborted because of concurrency conflicts, will ROLLBACK instead ~ Also, is there a way to update multiple tables at the same time without above error? Is this due to concurrent insert? Any help here is much appreciated! Regards, Sreejith

3 3

ordered index Error in optimizer garbageCollector
by Anton Kravchenko 06 Apr '18

06 Apr '18

Here is a reproducible example. create table t1(v0 int, v1 char(1)); insert into t1 values (1,'a'),(2,'b'),(3,'c'); create ordered index index_t1_v1 on t1(v1); create table t2(v1 char(1)); insert into t2 values ('a'); 1) gives "Error in optimizer garbageCollector" create temp table t3 as (select t1.v0 from t1,t2 where trim(t2.v1)=t1.v1) on commit preserve rows; 2) works as expected create temp table t3 as (select t1.v0 from t1,t2 where t2.v1=t1.v1) on commit preserve rows; 3) works as expected select t1.v0 from t1,t2 where trim(t2.v1)=t1.v1; 4) works as expected drop index index_t1_v1; create temp table t3 as (select t1.v0 from t1,t2 where trim(t2.v1)=t1.v1) on commit preserve rows; I am using the latest MonetDB version: MonetDB 5 server v11.29.3 "Mar2018" (64-bit, 128-bit integers) Copyright (c) 1993 - July 2008 CWI Copyright (c) August 2008 - 2018 MonetDB B.V., all rights reserved Visit https://www.monetdb.org/ for further information Found 31.3GiB available memory, 4 available cpu cores Libraries: libpcre: 8.32 2012-11-30 (compiled with 8.32) openssl: OpenSSL 1.0.2k 26 Jan 2017 (compiled with ) libxml2: 2.9.1 (compiled with 2.9.1) Compiled by: mockbuild@ (x86_64-redhat-linux-gnu) Compilation: gcc -std=gnu99 -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector-strong --param=ssp-buffer-size=4 -grecord-gcc-switches -m64 -mtune=generic Linking : /usr/bin/ld -m elf_x86_64 -Wl,-z,relro -Wl,-Bsymbolic-functions Thanks, Anton

2 1

Announcement: New Mar2018 Feature release of MonetDB suite
by Sjoerd Mullender 29 Mar '18

29 Mar '18

The MonetDB team at CWI/MonetDB BV is pleased to announce the Mar2018 feature release of the MonetDB suite of programs. More information about MonetDB can be found on our website at <https://www.monetdb.org/>. For details on this release, please see the release notes at <https://www.monetdb.org/Downloads/ReleaseNotes>. As usual, the download location is <https://dev.monetdb.org/downloads/>. Some notable changes: - Code base requires a C99 compiler. - The internal representation of REAL and DOUBLE NULL values has changed. Before they were represented as the smallest C float/double finite value (-FLT_MAX and -DBL_MAX). Now they are represented by a NaN (not-a-number) value. This requires a database upgrade which is done automatically at startup. Be advised that it is recommended to make a backup before upgrading. - Various extensions to SQL have been implemented. For more details, see below. Mar 2018 feature release (11.29.3) MonetDB5 Server * Implemented function pcre.replace_first which is like pcre.replace, except it only replaces the first match. * The EXPLAIN command now shows all the MAL type resolutions, because in general users may not be aware of their signatures. It also simplifies programs to analyze such plans. * Implemented versions of group.(sub)group(done) that don't return a histogram. * Removed MAL functions streams.socketRead, streams.socketReadBytes, streams.socketWrite, and streams.socketWriteBytes. * Removed MAL functions streams.openRead(s:streams):streams and streams.openWrite(s:streams):streams. * Lots of changes to streamline the internal error handling. In principle, all errors should now include a SQLSTATE error code (see the SQL standard). Build Environment * Added the .pdb files needed for debug symbols to the Windows installer for MonetDB/SQL. . * mclient's execution time profiling options and output format have been changed. Instead of implicitly via the "--interactive"/"-i" option (or when using an interactive mclient console), execution time profiling is now controlled via an explicit "--timer=timermode"/"-t timermode" command-line option, or a "\t timermode" command in the interactive mclient console. The default (also in the interactive mclient console) is now timermode "none", i.e., no timing information is given. Timermode "clock" activates client-side wall-clock timing ("clk") in "human-friendly" format much like the interactive mode did before. Timermode "performance" also provides detailed server-side timings: "sql" is the time to parse the SQL query, optimize the logical relational plan and create the initial physical (MAL) plan; "opt" is the time to optimize the physical (MAL) plan; "run" is the time to execute the physical (MAL) plan. With timermode "performance" all server-side timings and the client-side wall-clock time are given in milliseconds (ms). Note that the client-measured wall-clock time "clk" is reported per query only when options "--interactive" or "--echo" are used, because only then does mclient send individual lines (statements) of the SQL script to the server. Otherwise, mclient sends the SQL script in large(r) batch(es) to the server, and, thus, only the total wall-clock time per batch is measured and reported. The server-measured detailed performance timings "sql", "opt", "run" are always measured and reported per query. Also, all timing information is now given on a separate line and sent to stderr rather than stdout. * Some types and constants were moved from configure (and hence monetdb_config.h) to gdk.h. In particular, the types "lng" and "ulng" have been moved and can therefore no longer be used by code that doesn't (ultimately) include gdk.h. Just use int64_t instead. A bunch of format defines have been removed: SZFMT, SSZFMT, PTRFMT, PDFMT. Just use the C standard codes for those (%zu, %zd, %p, %td). The define for printing a lng (LLFMT) was also moved. Use PRId64 for printing int64_t in code not using gdk.h. Removed all references to __int64 and long long (use int64_t instead). Merovingian * Add daemon commands for starting/stopping collection of profiler (stethoscope) logs. Client Package * ODBC: The driver function SQLProcedureColumns was implemented. * ODBC: Changed table types as used by SQLTables from "LOCAL TEMPORARY" and "GLOBAL TEMPORARY" to "LOCAL TEMPORARY TABLE" and "GLOBAL TEMPORARY TABLE" respectively. * ODBC: Initial support for the HUGEINT type in SQL was added. Any value with type HUGEINT can be retrieved in a C variable with type SQL_C_CHAR or SQL_C_WCHAR. A value of type HUGEINT can be retrieved in other C types as long as they fit, the largest C type supported being a 64 bit integer (equivalent to BIGINT). * The functions in the mapi library that require 64 bit integers now use the standard type int64_t instead of the non-standard mapi_int64. This requires a compilation environment that has the stdint.h include file (standardized in C99). Compilation of the library also requires the inttypes.h include file (also standardized in C99). * Add a new pretty printing option to stethoscope Running stethoscope with the flag -j will produce not pretty printed output (one json object per line). Running with the -y flag will produce pretty printed output. Running with neither will produce the legacy, line oriented format mapilib * The three mapi_explain* functions and mapi_trace don't return any useful information, so they now return void. * The functions mapi_error_str and mapi_result_error now return const char * instead of plain char * to indicate that the returned data belongs to the library and should not be changed or freed by the application. * New function const char *mapi_result_errorcode(MapiHdl) which returns the SQLSTATE code if available when an error has occurred. stream * The interface of mnstr_fgetpos and mnstr_fsetpos was changed to look more like the standard C functions fsetpos and fgetpos: they both have a second argument "pointer to fpos_t". * Removed function wbstream. * Removed functions udp_rastream and udp_wastream. * Removed functions socket_rstream and socket_wstream. * Removed functions append_wstream and append_wastream. * Removed functions mnstr_rstream and mnstr_wstream. utils * The function mcrypt_getHashAlgorithms now returns a static, constant string, so the result should not be modified or freed. MonetDB Common * Changed return type of function void_replace_bat from BUN to gdk_return: it now only returns whether the operation succeeded or not. * Changed the return type of BATroles from void to gdk_return: it can fail due to malloc failure. * Removed functions ALIGNsetH, ALIGNsetT, and CREATEview_ (mind the underscore). The first can easily be replace by using BAThseqbase (that's all it did), the second was only used once, and the third can be replace by VIEWcreate. * Removed unused functions BATmemsize and BATvmsize. * Removed the tnodense property: it was maintained but never actually used, not even stored. * The NIL representation of the internal flt and dbl types was changed from the smallest representable finite value to NaN (not-a-number). * Changed the interface of ATOMformat and VALformat: they now return a pointer to the allocated string. * The length "method" for atoms now returns a size_t, the "len" field of a ValRecord is now a size_t, the "size" field of the atomDesc structure is now unsigned short. * Removed the "align" field from the ATOM descriptor (atomDesc) structure. * The atomtostr and atomfromstr "methods" for atoms now return ssize_t and require a pointer to size_t for the size of the buffer. * The atom tostr and fromstr "methods" now always return -1 on error. A return value greater than 0 is normal, a return value of 0 is not normal, but technically not an error. Testing Environment * Added a --data_path option to Mtest.py that defines an external data repository. See the commit message of c484932c7fd8 for more info. SQL * Extended support to use CREATE ORDERED INDEX on columns of type: char, varchar, clob, blob, url, json, inet and uuid. * Added new system view: sys.ids which contains all database objects ids which can be used in sys.dependencies table. * Added new system view: sys.dependencies_vw which shows all data of sys.dependencies including names on objects, object types and dependency types. * Added 25 new system views for finding out dependencies between database objects. These new dependency views improve, extend and replace the 17 sys.dependencies_X_on_Y() functions as previously defined in 21_dependency_functions.sql. Those sys.dependencies_X_on_Y() functions are now marked as deprecated. * Added new system view: sys.roles which contains all defined roles. * Added new system view: sys.var_values which shows the values for system variables. * Added support for COMMENT ON statements using SQL syntax: COMMENT ON { SCHEMA | TABLE | VIEW | COLUMN | INDEX | SEQUENCE | FUNCTION | PROCEDURE | AGGREGATE | FILTER FUNCTION | LOADER } qname IS { 'my description text' | NULL | '' } ; For COLUMN the qname can be "table_name"."column_name" or fully qualified as in: "schema_name"."table_name"."column_name". For FUNCTION, PROCEDURE, AGGREGATE, FILTER FUNCTION and LOADER the qname may need to include the signature (argument types) to be able to differentiate between multiple overloaded functions which have the same name and schema. By specifying IS NULL or IS '' you remove the comment for the database object. If a database object is dropped, the associated comment is also removed. Note: it is not allowed or possible to add comments for temporary tables or objects in schema "tmp". The sql catalog has been extended with system table: sys.comments. The keyword 'COMMENT' has now become a reserved keyword. * Removed system function sys.environment(). It was a duplicate of system function sys.env(). * Implemented behavior for DROP SCHEMA my_schema RESTRICT command. Previously the RESTRICT keyword was accepted but not obeyed. It would always do a CASCADE operation. Also the default behavior of DROP SCHEMA my_schema command is now changed into RESTRICT behavior (was CASCADE). * The internal NULL representation of the REAL (FLOAT) and DOUBLE types was changed from the smallest representable finite value to NaN (not-a-number). * A column default value can be used in a UPDATE statement: UPDATE tname SET cname = DEFAULT, and INSERT statements: INSERT INTO tname VALUES (..., DEFAULT, ...) * Added support for TRUNCATE statements conforming to the SQL:2008 standard: TRUNCATE [ TABLE ] qname [ CONTINUE IDENTITY | RESTART IDENTITY ] [ RESTRICT | CASCADE ] In a TRUNCATE statement a 'CONTINUE IDENTITY' or 'RESTART IDENTITY' clause can be passed to restart or not, being the former the default one. The 'CASCADE' option instructs to truncate referencing table(s) also if the referencing table(s) have foreign key references to this table. The default behavior is 'RESTRICT'. Note: it is possible to use TRUNCATE statements in a transaction and thus to rollback the effects of a truncate. The keywords 'TRUNCATE' and 'CONTINUE' have now become reserved keywords. * Added possibility to GRANT or REVOKE a TRUNCATE privilege to a user or role. * Added possibility to define a TRIGGER on a TRUNCATE event. * Added possibility to specify 'OR REPLACE' in following CREATE commands: CREATE [ OR REPLACE ] VIEW qname ... CREATE [ OR REPLACE ] TRIGGER qname ... * Added possibility to specify 'IF EXIST' in following DROP commands: DROP AGGREGATE [ IF EXISTS ] qname ... DROP FUNCTION [ IF EXISTS ] qname ... DROP FILTER FUNCTION [ IF EXISTS ] qname ... DROP LOADER [ IF EXISTS ] qname ... DROP PROCEDURE [ IF EXISTS ] qname ... DROP TRIGGER [ IF EXISTS ] qname ... * Lots of changes to streamline the internal error handling. In principle, all errors should now include a SQLSTATE error code (see the SQL standard). * Added support for extracting the quarter (number between 1 and 4) of a date or a timestamp or a timestamp with timezone in SQL: EXTRACT ( QUARTER FROM my_date_expr ). Added support for extracting the week (number between 1 and 53) of a date or a timestamp or a timestamp with timezone in SQL: EXTRACT ( WEEK FROM my_date_expr ). Added support for scalar functions: quarter(date_expr), quarter(timestamp_expr) and quarter(timestamptz_expr). Bug Fixes * 3574: Add support for: create OR REPLACE view ... * 3824: Created table not visible from ODBC * 3831: Extend date part extraction and date formating functions to support more formats like "quarter", "iso year/week" * 6244: Add support for: TRUNCATE TABLE * 6249: DEFAULT in row-values missing (sqlsmith) * 6346: BATsort returns GDK_SUCCEED when **sorted bat is null * 6438: Implement functionality to enforce the restrict option in: DROP SCHEMA xyz RESTRICT; * 6507: Column Header coming with trailing spaces in compiled version of MonetDB 11.27.9/11 * 6513: Sqlitelogictest: Wrong MAL plan generation for column product * 6526: Crash using aggregate function inside a case statement in having clause * 6529: Sqlitelogictest crash in select query with IN operator and cast * 6530: Sqlitelogictest: select query with NOT IN giving wrong results * 6532: copy into ignore null as directive if first column doesn't come from file * 6534: [Mar2018]: mclient -f tab / --format=tab complains about "unsupported formatter" * 6535: [Mar2018]: mclient -t / --timer does not work as documented * 6536: [Mar2018]: timing output of mclient -t / --timer= should go to stderr rather than stdout * 6537: [Mar2018]: mclient's default timing mode should (again) be "none" rather than "clock" * 6541: [Mar2018]: mclient reports incorrect wall-clock time * 6542: assertion failure when querying: select count(*) from sys.commented_function_signatures; * 6543: Mar2018: truncate on SQL system tables should NOT be allowed * 6545: Sqlitelogictest crash in IN query * 6546: Sqlitelogictest crash in IN query with division * 6547: OS-dependent behaviour for ilike * 6548: Select from remote table leaves session open * 6549: Add log messages when listen fails * 6550: Sqlitelogictest crash on complex CASE statement * 6551: Sqlitelogictest wrong NULL value cast * 6552: Sqlitelogictest crash on complex case statement * 6553: Sqlitelogictest crash on aggregation with having statement * 6554: Sqlitelogictest crash on complex case statement * 6555: Sqlitelogictest wrong result set generated from complex case statement * 6556: Sqlitelogictest division by zero on COALESCE call * 6557: Sqlitelogictest crash on aggregation query with not in * 6559: rows in sys.statistics are not removed when a temporary table is dropped. * 6560: Sqlitelogictest crash on group by query with having in * 6561: Sqlitelogictest crash on group by query with having not in clause

1 0

RE: Adding sum() to a query appears to disable multithreading/parallel execution
by Anderson, David B 28 Mar '18

28 Mar '18

But couldn't the exact summation operate in parallel on each of the sub-groups coming from the group by statement? I guess I was thinking that part of the computation would be split up as well. Would it be difficult to add a psum() function that would drop bits (so understood to be an approximation), but still operate in parallel? Thanks, Dave -----Original Message----- From: users-list [mailto:users-list-bounces+david.b.anderson=citi.com@monetdb.org] On Behalf Of Sjoerd Mullender Sent: Tuesday, March 27, 2018 10:36 AM To: users-list(a)monetdb.org Subject: Re: Adding sum() to a query appears to disable multithreading/parallel execution The reason for the lack of parallelization in case of sum() is that the column is of type float. When summing floating point numbers we try to give the correct result, even if there are large differences between individual values (e.g. a column that has 1e37, 1e-37, and -1e37, the result should be 1e-37, not 0). For this reason, we use an algorithm that remembers all bits during summing, but we then cannot parallelize the sum since the bits that didn't fit in a float (or double) for the partial sums get lost. On 27/03/18 15:42, Anderson, David B wrote: > All, > > Not entirely sure how to submit a bug report for this, but it appears that adding a sum() term to a query completely disables parallel execution of the query. > > When I run this query on my server (11.27.13 built from source), linux RH6, 24 core, 190GB memory: > > select s.product, d.fctrdt, d.delinq, count(*), count(d.realized) from > dyn d, stt s where s.loanseqnum = d.loanseqnum group by s.product, > d.fctrdt, d.delinq order by s.product, d.fctrdt, d.delinq; > > It executes in ~20 seconds (the table stt contains ~20 million > records, and the table dyn contains ~550 million records), and when I > run top I can see the process using all 24 cores, > > but when I change the term count(d.realized) to sum(d.realized), the query now takes 5-6 minutes to run, and when I run top, the mserver process appears to use a single core for most of the query. > > select s.product, d.fctrdt, d.delinq, count(*), sum(d.realized) from > dyn d, stt s where s.loanseqnum = d.loanseqnum group by s.product, > d.fctrdt, d.delinq order by s.product, d.fctrdt, d.delinq; > > Is this expected behavior? Am I doing something wrong? Queries are both run on a hot server. > > Note: the explain from the query containing sum(d.realized) only contains 103 tuples, but the initial query with count(d.realized) contains 1052 tuples. > > Thanks, > Dave > > > Here are the definitions of the stt/dyn tables: > > create TABLE stt ( > product char(12) NULL, > prefix char(2) NULL, > coupon float NULL, > productionyear smallint NULL, > cusip char(9) NULL, > loanseqnum char(12) NULL, > loanpurpose char(1) NULL, > tpoflag char(1) NULL, > proptype char(2) NULL, > occstatus char(1) NULL, > numunits smallint NULL, > state char(2) NULL, > cs smallint NULL, > origterm smallint NULL, > origltv float NULL, > orignoterate float NULL, > origloanamt float NULL, > origwala smallint NULL, > origserv char(8) NULL, > govtagy char(1) NULL); > > create TABLE dyn ( > fctrdt DATE NULL, > loanseqnum char(12) NULL, > scheduled float NULL, > realized float NULL, > smm float NULL, > delinq int NULL, > paidahead int NULL); > > And here is the Merovingian log: > > $ more merovingian.log > 2018-03-26 09:17:16 MSG merovingian[26418]: Merovingian 1.7 > (Jul2017-SP4) starting > 2018-03-26 09:17:16 MSG merovingian[26418]: monitoring dbfarm > /opt/flash1/test/DB > 2018-03-26 09:17:16 MSG merovingian[26418]: Merovingian 1.7 stopped > 2018-03-26 09:17:16 ERR merovingian[26418]: binding to stream socket > port 50000 failed: Address already in use > 2018-03-26 09:17:16 ERR merovingian[26418]: fatal startup condition > encountered, aborting startup > 2018-03-26 09:18:15 MSG merovingian[28537]: Merovingian 1.7 > (Jul2017-SP4) starting > 2018-03-26 09:18:15 MSG merovingian[28537]: monitoring dbfarm > /opt/flash1/test/DB > 2018-03-26 09:18:15 MSG merovingian[28537]: accepting connections on > TCP socket localhost:50000 > 2018-03-26 09:18:15 MSG merovingian[28537]: accepting connections on > UNIX domain socket /tmp/.s.monetdb.50000 > 2018-03-26 09:18:15 MSG discovery[28537]: listening for UDP messages > on localhost:50000 > 2018-03-26 09:18:15 MSG control[28537]: accepting connections on UNIX > domain socket /tmp/.s.merovingian.50000 > 2018-03-26 09:18:15 MSG control[28537]: (local): created database 'loan' > 2018-03-26 09:18:15 MSG control[28537]: (local): served status list > 2018-03-26 09:18:15 MSG control[28537]: (local): released database 'loan' > 2018-03-26 09:18:15 MSG merovingian[28537]: starting database 'loan', > up min/avg/max: 0s/0s/0s, crash average: 0.00 0.00 0.00 (0-0=0) > 2018-03-26 09:18:15 MSG merovingian[28537]: proxying client (local) > for database 'loan' to > mapi:monetdb:///opt/flash1/test/DB/loan/.mapi.sock?database=loan > 2018-03-26 09:18:15 MSG merovingian[28537]: target connection is on > local UNIX domain socket, passing on filedescriptor instead of > proxying > 2018-03-26 09:18:15 MSG loan[28550]: arguments: > /opt/tools/root/MonetDB-11.27.13/bin/mserver5 > --dbpath=/opt/flash1/test/DB/loan --set > merovingian_uri=mapi:monetdb://malxcs1p:50000/loan --set > mapi_open=false --set mapi_port=0 - -set > mapi_usock=/opt/flash1/test/DB/loan/.mapi.sock --set > monet_vault_key=/opt/flash1/test/DB/loan/.vaultkey --set > gdk_nr_threads=24 --set max_clients=64 --set > sql_optimizer=default_pipe --set monet_daemon=yes > 2018-03-26 09:18:15 MSG loan[28550]: # MonetDB 5 server v11.27.13 "Jul2017-SP4" > 2018-03-26 09:18:15 MSG loan[28550]: # Serving database 'loan', using > 24 threads > 2018-03-26 09:18:15 MSG loan[28550]: # Compiled for > x86_64-pc-linux-gnu/64bit with 128bit integers > 2018-03-26 09:18:15 MSG loan[28550]: # Found 189.145 GiB available main-memory. > 2018-03-26 09:18:15 MSG loan[28550]: # Copyright (c) 1993 - July 2008 CWI. > 2018-03-26 09:18:15 MSG loan[28550]: # Copyright (c) August 2008 - > 2018 MonetDB B.V., all rights reserved > 2018-03-26 09:18:15 MSG loan[28550]: # Visit https://www.monetdb.org/ > for further information > 2018-03-26 09:18:15 MSG loan[28550]: # Listening for UNIX domain > connection requests on > mapi:monetdb:///opt/flash1/test/DB/loan/.mapi.sock > 2018-03-26 09:18:15 MSG loan[28550]: # MonetDB/SQL module loaded > 2018-03-26 09:18:16 MSG loan[28550]: # SQL catalog created, loading > sql scripts once > 2018-03-26 09:18:16 MSG loan[28550]: # loading sql script: 09_like.sql > 2018-03-26 09:18:16 MSG loan[28550]: # loading sql script: 10_math.sql > 2018-03-26 09:18:16 MSG loan[28550]: # loading sql script: > 11_times.sql > 2018-03-26 09:18:16 MSG loan[28550]: # loading sql script: 12_url.sql > 2018-03-26 09:18:16 MSG loan[28550]: # loading sql script: 13_date.sql > 2018-03-26 09:18:16 MSG loan[28550]: # loading sql script: 14_inet.sql > 2018-03-26 09:18:16 MSG loan[28550]: # loading sql script: > 15_querylog.sql > 2018-03-26 09:18:16 MSG loan[28550]: # loading sql script: > 16_tracelog.sql > 2018-03-26 09:18:16 MSG loan[28550]: # loading sql script: > 17_temporal.sql > 2018-03-26 09:18:16 MSG loan[28550]: # loading sql script: > 18_index.sql > 2018-03-26 09:18:16 MSG loan[28550]: # loading sql script: > 20_vacuum.sql > 2018-03-26 09:18:16 MSG loan[28550]: # loading sql script: > 21_dependency_functions.sql > 2018-03-26 09:18:16 MSG loan[28550]: # loading sql script: > 22_clients.sql > 2018-03-26 09:18:16 MSG loan[28550]: # loading sql script: > 23_skyserver.sql > 2018-03-26 09:18:16 MSG loan[28550]: # loading sql script: > 25_debug.sql > 2018-03-26 09:18:16 MSG loan[28550]: # loading sql script: > 26_sysmon.sql > 2018-03-26 09:18:16 MSG loan[28550]: # loading sql script: > 27_rejects.sql > 2018-03-26 09:18:16 MSG loan[28550]: # loading sql script: > 39_analytics.sql > 2018-03-26 09:18:16 MSG loan[28550]: # loading sql script: > 39_analytics_hge.sql > 2018-03-26 09:18:16 MSG loan[28550]: # loading sql script: 40_json.sql > 2018-03-26 09:18:16 MSG loan[28550]: # loading sql script: > 40_json_hge.sql > 2018-03-26 09:18:16 MSG loan[28550]: # loading sql script: > 41_md5sum.sql > 2018-03-26 09:18:16 MSG loan[28550]: # loading sql script: 45_uuid.sql > 2018-03-26 09:18:16 MSG loan[28550]: # loading sql script: > 46_profiler.sql > 2018-03-26 09:18:16 MSG loan[28550]: # loading sql script: > 51_sys_schema_extension.sql > 2018-03-26 09:18:16 MSG loan[28550]: # loading sql script: > 75_storagemodel.sql > > > > > > > > _______________________________________________ > users-list mailing list > users-list(a)monetdb.org > https://www.monetdb.org/mailman/listinfo/users-list > -- Sjoerd Mullender

3 3

DB Crashing Post Data Load
by Sharma, Sreejith 28 Mar '18

28 Mar '18

All, We have a scenario where we load the data via the shell scripts from .dat file using ControlM process. We notice the DB crashes intermittently post the load. Is there any limitation or settings on multiple session loads? Or what happens if load is parallel? 2018-03-27 00:21:54 MSG merovingian[45726]: database 'TEST_MDP' has crashed after start on 2018-03-13 05:04:41, attempting restart, up min/avg/max: 2m/4d/2w, crash average: 1.00 1.00 0.80 (35-9=26 MonetDB : 11.27.13 OS : CentOS 6 Regards, Sreejith

2 2

Adding sum() to a query appears to disable multithreading/parallel execution
by Anderson, David B 27 Mar '18

27 Mar '18

All, Not entirely sure how to submit a bug report for this, but it appears that adding a sum() term to a query completely disables parallel execution of the query. When I run this query on my server (11.27.13 built from source), linux RH6, 24 core, 190GB memory: select s.product, d.fctrdt, d.delinq, count(*), count(d.realized) from dyn d, stt s where s.loanseqnum = d.loanseqnum group by s.product, d.fctrdt, d.delinq order by s.product, d.fctrdt, d.delinq; It executes in ~20 seconds (the table stt contains ~20 million records, and the table dyn contains ~550 million records), and when I run top I can see the process using all 24 cores, but when I change the term count(d.realized) to sum(d.realized), the query now takes 5-6 minutes to run, and when I run top, the mserver process appears to use a single core for most of the query. select s.product, d.fctrdt, d.delinq, count(*), sum(d.realized) from dyn d, stt s where s.loanseqnum = d.loanseqnum group by s.product, d.fctrdt, d.delinq order by s.product, d.fctrdt, d.delinq; Is this expected behavior? Am I doing something wrong? Queries are both run on a hot server. Note: the explain from the query containing sum(d.realized) only contains 103 tuples, but the initial query with count(d.realized) contains 1052 tuples. Thanks, Dave Here are the definitions of the stt/dyn tables: create TABLE stt ( product char(12) NULL, prefix char(2) NULL, coupon float NULL, productionyear smallint NULL, cusip char(9) NULL, loanseqnum char(12) NULL, loanpurpose char(1) NULL, tpoflag char(1) NULL, proptype char(2) NULL, occstatus char(1) NULL, numunits smallint NULL, state char(2) NULL, cs smallint NULL, origterm smallint NULL, origltv float NULL, orignoterate float NULL, origloanamt float NULL, origwala smallint NULL, origserv char(8) NULL, govtagy char(1) NULL); create TABLE dyn ( fctrdt DATE NULL, loanseqnum char(12) NULL, scheduled float NULL, realized float NULL, smm float NULL, delinq int NULL, paidahead int NULL); And here is the Merovingian log: $ more merovingian.log 2018-03-26 09:17:16 MSG merovingian[26418]: Merovingian 1.7 (Jul2017-SP4) starting 2018-03-26 09:17:16 MSG merovingian[26418]: monitoring dbfarm /opt/flash1/test/DB 2018-03-26 09:17:16 MSG merovingian[26418]: Merovingian 1.7 stopped 2018-03-26 09:17:16 ERR merovingian[26418]: binding to stream socket port 50000 failed: Address already in use 2018-03-26 09:17:16 ERR merovingian[26418]: fatal startup condition encountered, aborting startup 2018-03-26 09:18:15 MSG merovingian[28537]: Merovingian 1.7 (Jul2017-SP4) starting 2018-03-26 09:18:15 MSG merovingian[28537]: monitoring dbfarm /opt/flash1/test/DB 2018-03-26 09:18:15 MSG merovingian[28537]: accepting connections on TCP socket localhost:50000 2018-03-26 09:18:15 MSG merovingian[28537]: accepting connections on UNIX domain socket /tmp/.s.monetdb.50000 2018-03-26 09:18:15 MSG discovery[28537]: listening for UDP messages on localhost:50000 2018-03-26 09:18:15 MSG control[28537]: accepting connections on UNIX domain socket /tmp/.s.merovingian.50000 2018-03-26 09:18:15 MSG control[28537]: (local): created database 'loan' 2018-03-26 09:18:15 MSG control[28537]: (local): served status list 2018-03-26 09:18:15 MSG control[28537]: (local): released database 'loan' 2018-03-26 09:18:15 MSG merovingian[28537]: starting database 'loan', up min/avg/max: 0s/0s/0s, crash average: 0.00 0.00 0.00 (0-0=0) 2018-03-26 09:18:15 MSG merovingian[28537]: proxying client (local) for database 'loan' to mapi:monetdb:///opt/flash1/test/DB/loan/.mapi.sock?database=loan 2018-03-26 09:18:15 MSG merovingian[28537]: target connection is on local UNIX domain socket, passing on filedescriptor instead of proxying 2018-03-26 09:18:15 MSG loan[28550]: arguments: /opt/tools/root/MonetDB-11.27.13/bin/mserver5 --dbpath=/opt/flash1/test/DB/loan --set merovingian_uri=mapi:monetdb://malxcs1p:50000/loan --set mapi_open=false --set mapi_port=0 - -set mapi_usock=/opt/flash1/test/DB/loan/.mapi.sock --set monet_vault_key=/opt/flash1/test/DB/loan/.vaultkey --set gdk_nr_threads=24 --set max_clients=64 --set sql_optimizer=default_pipe --set monet_daemon=yes 2018-03-26 09:18:15 MSG loan[28550]: # MonetDB 5 server v11.27.13 "Jul2017-SP4" 2018-03-26 09:18:15 MSG loan[28550]: # Serving database 'loan', using 24 threads 2018-03-26 09:18:15 MSG loan[28550]: # Compiled for x86_64-pc-linux-gnu/64bit with 128bit integers 2018-03-26 09:18:15 MSG loan[28550]: # Found 189.145 GiB available main-memory. 2018-03-26 09:18:15 MSG loan[28550]: # Copyright (c) 1993 - July 2008 CWI. 2018-03-26 09:18:15 MSG loan[28550]: # Copyright (c) August 2008 - 2018 MonetDB B.V., all rights reserved 2018-03-26 09:18:15 MSG loan[28550]: # Visit https://www.monetdb.org/ for further information 2018-03-26 09:18:15 MSG loan[28550]: # Listening for UNIX domain connection requests on mapi:monetdb:///opt/flash1/test/DB/loan/.mapi.sock 2018-03-26 09:18:15 MSG loan[28550]: # MonetDB/SQL module loaded 2018-03-26 09:18:16 MSG loan[28550]: # SQL catalog created, loading sql scripts once 2018-03-26 09:18:16 MSG loan[28550]: # loading sql script: 09_like.sql 2018-03-26 09:18:16 MSG loan[28550]: # loading sql script: 10_math.sql 2018-03-26 09:18:16 MSG loan[28550]: # loading sql script: 11_times.sql 2018-03-26 09:18:16 MSG loan[28550]: # loading sql script: 12_url.sql 2018-03-26 09:18:16 MSG loan[28550]: # loading sql script: 13_date.sql 2018-03-26 09:18:16 MSG loan[28550]: # loading sql script: 14_inet.sql 2018-03-26 09:18:16 MSG loan[28550]: # loading sql script: 15_querylog.sql 2018-03-26 09:18:16 MSG loan[28550]: # loading sql script: 16_tracelog.sql 2018-03-26 09:18:16 MSG loan[28550]: # loading sql script: 17_temporal.sql 2018-03-26 09:18:16 MSG loan[28550]: # loading sql script: 18_index.sql 2018-03-26 09:18:16 MSG loan[28550]: # loading sql script: 20_vacuum.sql 2018-03-26 09:18:16 MSG loan[28550]: # loading sql script: 21_dependency_functions.sql 2018-03-26 09:18:16 MSG loan[28550]: # loading sql script: 22_clients.sql 2018-03-26 09:18:16 MSG loan[28550]: # loading sql script: 23_skyserver.sql 2018-03-26 09:18:16 MSG loan[28550]: # loading sql script: 25_debug.sql 2018-03-26 09:18:16 MSG loan[28550]: # loading sql script: 26_sysmon.sql 2018-03-26 09:18:16 MSG loan[28550]: # loading sql script: 27_rejects.sql 2018-03-26 09:18:16 MSG loan[28550]: # loading sql script: 39_analytics.sql 2018-03-26 09:18:16 MSG loan[28550]: # loading sql script: 39_analytics_hge.sql 2018-03-26 09:18:16 MSG loan[28550]: # loading sql script: 40_json.sql 2018-03-26 09:18:16 MSG loan[28550]: # loading sql script: 40_json_hge.sql 2018-03-26 09:18:16 MSG loan[28550]: # loading sql script: 41_md5sum.sql 2018-03-26 09:18:16 MSG loan[28550]: # loading sql script: 45_uuid.sql 2018-03-26 09:18:16 MSG loan[28550]: # loading sql script: 46_profiler.sql 2018-03-26 09:18:16 MSG loan[28550]: # loading sql script: 51_sys_schema_extension.sql 2018-03-26 09:18:16 MSG loan[28550]: # loading sql script: 75_storagemodel.sql

2 1

GDK reported error. MT_mremap: mremap failed. !OS: Cannot allocate memory
by RanchoYuan 26 Mar '18

26 Mar '18

total used free shared buff/cache available Mem: 17825792 59904 17765888 0 0 17765888 Swap: 33554432 0 33554432

1 0

GDK reported error. MT_mremap: mremap failed. !OS: Cannot allocate memory
by RanchoYuan 26 Mar '18

26 Mar '18

I'm not sure that you can get the accessory(script). Here I put the text. expe.sh: #!/bin/bash BASEDIR=$(cd `dirname $0`;pwd -P) DAT_DIR=$BASEDIR/tpc-ds_test_data10G #drop tables if they are existed for i in $(ls $DAT_DIR/*.dat) do filename=$(echo $i | awk -F '/' '{print $NF}' | awk -F '.' '{print $1}') echo "start dropping table $filename" #./drop_tables.sh $filename mclient -s "drop table $filename;" echo "end dropping table $filename" done #create tables mclient /home/monetdb/v2.6.0/tools/tpcds.sql #load data for i in $(ls $DAT_DIR/*.dat) do filename=$(echo $i | awk -F '/' '{print $NF}' | awk -F '.' '{print $1}') #./autoinsert.sh $filename echo "start loading data to table $filename" mclient -s "copy into $filename from '$DAT_DIR/$filename.dat' using delimiters '|' null as '';" echo "end loading data to table $filename" done the query is showed in previous email.

1 0

Less verbose logs?
by Roberto Cornacchia 22 Mar '18

22 Mar '18

merovingian.log files on our production servers are dominated by these two lines: 2018-03-21 16:55:38 MSG merovingian[23237]: proxying client localhost.localdomain:51502 for database 'test' to mapi:monetdb://path/test/.mapi.sock?database=test 2018-03-21 16:55:38 MSG merovingian[23237]: target connection is on local UNIX domain socket, passing on filedescriptor instead of proxying They account for 90% of the log. Is there a way to reduce verbosity?

1 1

GDK reported error. MT_mremap: mremap failed. !OS: Cannot allocate memory
by RanchoYuan 22 Mar '18

22 Mar '18

The free disk space is 800G. And the query also fail when I run query right after a restart of the MonetDB server. And I'm not very sure what the mserver5 size means. When I run the query, the log file provide some information below, is that your mentioned? #GDKmremap(2465464320) fails, try to free up space [memory in use=149750256,virtual memory in use=4497801712] Also, I'm sorry that the information(query statement and result) I put in is incomplete by my negligence. I copy the query statement and result and error message again as followed: result and error message: GDK reported error. MT_mremap: mremap(0x7ffc80000000,2054553600,2465464320) failed !OS: Cannot allocate memory HEAPextend: failed to extend to 2465464320 for 55/5577.tail: GDKmremap() failed +-------------+--------------+-------+ | c_last_name | c_first_name | sales | +=============+==============+=======+ +-------------+--------------+-------+ 0 tuples query statement: with frequent_ss_items as ( select substr(i_item_desc,1,30) itemdesc ,i_item_sk item_sk ,d_date solddate ,count(*) cnt from store_sales ,date_dim ,item where ss_sold_date_sk = d_date_sk and ss_item_sk = i_item_sk and d_year in (1999,1999+1,1999+2,1999+3) group by itemdesc ,i_item_sk ,d_date having count(*) >4 ), max_store_sales as ( select max(csales) tpcds_cmax from ( select c_customer_sk ,sum(ss_quantity*ss_sales_price) csales from store_sales ,customer ,date_dim where ss_customer_sk = c_customer_sk and ss_sold_date_sk = d_date_sk and d_year in (1999,1999+1,1999+2,1999+3) group by c_customer_sk ) x ), best_ss_customer as ( select c_customer_sk ,sum(ss_quantity*ss_sales_price) ssales from store_sales ,customer where ss_customer_sk = c_customer_sk group by c_customer_sk having sum(ss_quantity*ss_sales_price) > (95/100.0) * ( select * from max_store_sales ) ) select sum(sales) from ( select cs_quantity*cs_list_price sales from catalog_sales ,date_dim where d_year = 1999 and d_moy = 1 and cs_sold_date_sk = d_date_sk and cs_item_sk in (select item_sk from frequent_ss_items) and cs_bill_customer_sk in (select c_customer_sk from best_ss_customer) union all select ws_quantity*ws_list_price sales from web_sales ,date_dim where d_year = 1999 and d_moy = 1 and ws_sold_date_sk = d_date_sk and ws_item_sk in (select item_sk from frequent_ss_items) and ws_bill_customer_sk in (select c_customer_sk from best_ss_customer)) y limit 100; with frequent_ss_items as (select substr(i_item_desc,1,30) itemdesc,i_item_sk item_sk,d_date solddate,count(*) cnt from store_sales ,date_dim ,item where ss_sold_date_sk = d_date_sk and ss_item_sk = i_item_sk and d_year in (1999,1999 + 1,1999 + 2,1999 + 3) group by itemdesc,i_item_sk,d_date having count(*) >4), max_store_sales as (select max(csales) tpcds_cmax from (select c_customer_sk,sum(ss_quantity*ss_sales_price) csales from store_sales ,customer ,date_dim where ss_customer_sk = c_customer_sk and ss_sold_date_sk = d_date_sk and d_year in (1999,1999+1,1999+2,1999+3) group by c_customer_sk) x), best_ss_customer as (select c_customer_sk,sum(ss_quantity*ss_sales_price) ssales from store_sales ,customer where ss_customer_sk = c_customer_sk group by c_customer_sk having sum(ss_quantity*ss_sales_price) > (95/100.0) * (select * from max_store_sales)) select c_last_name,c_first_name,sales from (select c_last_name,c_first_name,sum(cs_quantity*cs_list_price) sales from catalog_sales ,customer ,date_dim where d_year = 1999 and d_moy = 1 and cs_sold_date_sk = d_date_sk and cs_item_sk in (select item_sk from frequent_ss_items) and cs_bill_customer_sk in (select c_customer_sk from best_ss_customer) and cs_bill_customer_sk = c_customer_sk group by c_last_name,c_first_name union all select c_last_name,c_first_name,sum(ws_quantity*ws_list_price) sales from web_sales ,customer ,date_dim where d_year = 1999 and d_moy = 1 and ws_sold_date_sk = d_date_sk and ws_item_sk in (select item_sk from frequent_ss_items) and ws_bill_customer_sk in (select c_customer_sk from best_ss_customer) and ws_bill_customer_sk = c_customer_sk group by c_last_name,c_first_name) x order by c_last_name,c_first_name,sales limit 100; Thanks! Regards, Rancho

2 1

M5server.bat error
by Amit Ambekar 22 Mar '18

22 Mar '18

Hi, i had installed monetdb on Microsoft server 12, but now when i want to start M5server , error occured. And i had tried this on various machine of Windows Server 2012, still same issue occurred. Is monetdb is not compatible with Windows 2012 server?

2 1

How to use MonetDB cluster?
by Li Japin 20 Mar '18

20 Mar '18

Hi, Recently, I read the MonetDB cluster management document, but I'm not sure which situation will use the MonetDB cluster and how to use it. I guess the cluster will combine with REMOTE table, is that right? But how can I monitor the remote table is online? using `monetdb discover` command? Best regards, Japin

1 0

merge tables: how to reveal member tabels?
by Stefan Manegold 16 Mar '18

16 Mar '18

Hi, given a merge table, e.g., sql>create table t1(a int, b real); operation successful (9.315ms) sql>create table t2(a int, b real); operation successful (8.497ms) sql>create merge table mt(a int, b real); operation successful (8.218ms) sql>alter table mt add table t1; operation successful (7.174ms) sql>alter table mt add table t2; operation successful (6.178ms) How can I see which tables are members of a merge tables, i.e., in this case, how can I see that t1 & t2 are members of mt? a plain select (obviously) only shows the content of mt, i.e., the combined content of t1 & t2 ... sql>select * from mt; +---+---+ | a | b | +===+===+ +---+---+ 0 tuples (4.690ms) Is there a query to get this directly from mt, or where can I otherwise find that info in the SQL catalog? 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) |

2 2

Can I avoid "n affected rows" messages?
by Eyal Rozenberg 15 Mar '18

15 Mar '18

When you perform an insert or update using mclient, you get a line printed saying "n rows affected". Now, I can avoid this line if I use -f trash on the command line - but I don't want to give up on actual output. I can also pass the output through a grep -Ev "^[0-9]+ rows affected$" as a filter, but I was wondering if I could just turn that message off somehow. I couldn't find a command-line switch which would have this effect. Eyal

2 1

GDK reported error. MT_mremap: mremap failed. !OS: Cannot allocate memory
by RanchoYuan 15 Mar '18

15 Mar '18

Hello all, I did a tpc-ds testing on MonetDB, and when it ran some queries, the error message was appeared: GDK reported error. MT_mremap: mremap(0x7ffdc0000000,2054553600,2465464320) failed !OS: Cannot allocate memory HEAPextend: failed to extend to 2465464320 for 44/4415.tail: GDKmremap() failed It seems that the memory has been ran out. But I have no idea what is the cause of this error. The version of Monetdb is 11.27.13, and OS is Ubunbtu-16.04 with memory is 8G, and with 20G testing data.(It is worth mentioning, a table named store_sales is 7.5G in the query.) The query statement is: with frequent_ss_items as ( select substr(i_item_desc,1,30) itemdesc ,i_item_sk item_sk ,d_date solddate ,count(*) cnt from store_sales ,date_dim ,item where ss_sold_date_sk = d_date_sk and ss_item_sk = i_item_sk and d_year in (1999,1999+1,1999+2,1999+3) group by itemdesc ,i_item_sk ,d_date having count(*) >4 ), max_store_sales as ( select max(csales) tpcds_cmax from ( select c_customer_sk ,sum(ss_quantity*ss_sales_price) csales from store_sales ,customer ,date_dim where ss_customer_sk = c_customer_sk and ss_sold_date_sk = d_date_sk and d_year in (1999,1999+1,1999+2,1999+3) group by c_customer_sk ) x ), best_ss_customer as ( select c_customer_sk ,sum(ss_quantity*ss_sales_price) ssales from store_sales ,customer where ss_customer_sk = c_customer_sk group by c_customer_sk having sum(ss_quantity*ss_sales_price) > (95/100.0) * ( select * from max_store_sales ) ) select sum(sales) from ( select cs_quantity*cs_list_price sales from catalog_sales ,date_dim where d_year = 1999 and d_moy = 1 and ws_sold_date_sk = d_date_sk and ws_item_sk in (select item_sk from frequent_ss_items) and ws_bill_customer_sk in (select c_customer_sk from best_ss_customer)) y limit 100; Please give me some advice if you have any idea, either of the direct question or my expression. Thanks a lot! Regards, Rancho

2 2

MonetDB - table without column- NOT able to delete it or use it
by Sharma, Sreejith 12 Mar '18

12 Mar '18

All, We are finding a strange issue where below table has been created via a shell script. Strangely it doesn't show up any columns and when we try to drop it's saying table doesn't exists. Is there any way below can happen? Is this because it's corrupted? How can we remove this? CREATE TABLE "DM_MON_TSV_STG_T1"."AMA_US_IRI_8135_2001" ( ); Regards, Sreejith

4 3

Backup and restore in Monetdb
by Amit Ambekar 10 Mar '18

10 Mar '18

Hi, How i can take back of monetdb database , and how to restore it?

2 1

current_time() precision
by Roberto Cornacchia 28 Feb '18

28 Feb '18

sql>select current_time(); +-----------------------+ | L2 | +=======================+ | 17:05:10.000000+01:00 | +-----------------------+ 1 tuple (0.796ms) Is it expected that the result type's precision of current_time() is limited to seconds? Any way to get milliseconds?

1 0

Monetdb hanging on query
by Manuel Pagliai 27 Feb '18

27 Feb '18

Hello, I am using Monetdb as the query engine of an application currently in development. I am connecting to monetdb via a jdbc connection. I can easily reproduce a scenario where monetdb gets stuck on a query, and never complete execution. Although reproducible, the issue is non deterministic, and it can occur while executing different queries, or not occur at all. When it happens I can see the thread stuck at I extracted the queries submitted to monetdb, and execute them via a standalone client (both the native client and a jdbc-based one) rather than our application, but I have been unable to reproduce the issue that way. I guess timing is important. The problem still occur even when queries are submitted sequentially and setting gdk_nr_threads=1 and sql_optimizer=sequential_pipe. The issue happens in Jul 2017-SP3 bugfix release (11.27.11), running on a Windows 7 machine. I have not tried 11.27.13, but by looking at the bug fixes I can't see anything relevant to this issue. Is there something that I can try, or a way to gather more information about what is happening ? I can share both the data, and the complete list of queries used during this scenario. Thanks, Best regards, *Manuel Pagliai* Visokio Ltd www.visokio.com +44 20 7351 4646 P.S. This is the stack-trace of the thread submitting the query when this happens: at java.net.SocketInputStream.socketRead0(Native Method) at java.net.SocketInputStream.socketRead(SocketInputStream.java:116) at java.net.SocketInputStream.read(SocketInputStream.java:171) at java.net.SocketInputStream.read(SocketInputStream.java:141) at java.io.BufferedInputStream.fill(BufferedInputStream.java:246) at java.io.BufferedInputStream.read1(BufferedInputStream.java:286) at java.io.BufferedInputStream.read(BufferedInputStream.java:345) - locked <0x00000000cae0b730> (a java.io.BufferedInputStream) at nl.cwi.monetdb.mcl.net.MapiSocket$BlockInputStream._read(Unknown Source) at nl.cwi.monetdb.mcl.net.MapiSocket$BlockInputStream.readBlock(Unknown Source) at nl.cwi.monetdb.mcl.net.MapiSocket$BlockInputStream.read(Unknown Source) at sun.nio.cs.StreamDecoder.readBytes(StreamDecoder.java:284) at sun.nio.cs.StreamDecoder.implRead(StreamDecoder.java:326) at sun.nio.cs.StreamDecoder.read(StreamDecoder.java:178) - locked <0x00000000cae13810> (a java.io.InputStreamReader) at java.io.InputStreamReader.read(InputStreamReader.java:184) at java.io.BufferedReader.fill(BufferedReader.java:161) at java.io.BufferedReader.readLine(BufferedReader.java:324) - locked <0x00000000cae13810> (a java.io.InputStreamReader) at java.io.BufferedReader.readLine(BufferedReader.java:389) at nl.cwi.monetdb.mcl.io.BufferedMCLReader.readLine(Unknown Source) at nl.cwi.monetdb.mcl.io.BufferedMCLReader.waitForPrompt(Unknown Source) - locked <0x00000000cae137d8> (a nl.cwi.monetdb.mcl.io.BufferedMCLReader) at nl.cwi.monetdb.jdbc.MonetConnection$ResponseList.executeQuery(Unknown Source) - locked <0x00000000cae0b430> (a nl.cwi.monetdb.mcl.net.MapiSocket) at nl.cwi.monetdb.jdbc.MonetConnection$ResponseList.processQuery(Unknown Source) at nl.cwi.monetdb.jdbc.MonetStatement.internalExecute(Unknown Source) at nl.cwi.monetdb.jdbc.MonetStatement.execute(Unknown Source) at nl.cwi.monetdb.jdbc.MonetStatement.executeQuery(Unknown Source)

2 1

MonetDB :: Alter statement to change datatype
by Sharma, Sreejith 22 Feb '18

22 Feb '18

All, Is it possible to change the datatype of a column in an existing table? With or without data? I saw the only way to change is to create a temporary column and swap if data is present. Is it true? Regards, Sreejith

2 1

Re: Order indexes: used or not?
by Stefan Manegold 20 Feb '18

20 Feb '18

Hi Torsten, To start mserver5 manually on a database created by monetdb/monetdb, you need to use the same command line (-options) the monedtd use to start mserver5, in particular the valley option. You find the details in the merovingian.log in your dbfarm Best,Stefan -------- Original message --------From: Torsten Grust <torsten.grust(a)gmail.com> Date: 2/20/18 17:08 (GMT+01:00) To: Lefteris <lsidir(a)gmail.com> Cc: Communication channel for MonetDB users <users-list(a)monetdb.org> Subject: Re: Order indexes: used or not? Hi Lefteris, that's very interesting. Thanks! I was about to experiment with this here, but I stumbled. When you start a database via mserver5 directly (instead of using monetdbd which, as I understand, is the recommend method), does the method of authentication change? I fail to login to my scratch database: $  mserver5 --dbpath=<path>/scratch # MonetDB 5 server v11.27.13 "Jul2017-SP4" # Serving database 'scratch', using 4 threads # [...] # MonetDB/SQL module loaded > [switch to other terminal] $ mclient -d scratch -u monetdb password: <supply password "monetdb"> InvalidCredentialsException:checkCredentials:invalid credentials for user 'monetdb' Whereas the login works just fine below: $ monetdbd start <path> $ mclient -d scratch -u monetdb password: <supply password "monetdb"> Welcome to mclient, the MonetDB/SQL interactive terminal (Jul2017-SP4) Database: MonetDB v11.27.13 (Jul2017-SP4), 'mapi:monetdb://closure:50000/scratch' [...] sql> I must be doing something stupid. Cheers, —Torsten On Tue, Feb 20, 2018 at 3:40 PM Lefteris <lsidir(a)gmail.com> wrote: Hi Torsten, the reason why you don't see anything in the EXPLAIN statement is that the use of an index is decided at runtime by the respective operator. The algebra.sort code will have an if statement that determines at query evaluation time if there is a usable ordered index and use it. The best way (for now) to see if the index is used or not is to start the mserver5 with the --algorithms flag. This flag will produce alot of output on the runtime decissions. For your example, I get the following output: #BATproject(l=tmp_523#3-sorted-key,r=tmp_433#3[int]-revsorted-key) #BATproject(l=tmp_523,r=tmp_433)=tmp_323#3-revsorted-key #BATcheckorderidx: reusing persisted orderidx 283 #BATproject(l=tmp_322#3-key,r=tmp_323#3[int]-revsorted-key) #BATproject(l=tmp_322,r=tmp_323)=tmp_417#3-key 11us #BATgroup(b=tmp_417#3[int],s=NULL#0,g=NULL#0,e=NULL#0,h=NULL#0,subsorted=1): trivial case: 1 element per group #BATprojectchain with 3 (2) BATs, size 3, type int #BATproject(l=tmp_322#3-key,r=tmp_323#3[int]-revsorted-key) #BATproject(l=tmp_322,r=tmp_323)=tmp_523#3-key 9us And the third line says "BATcheckorderidx: reusing persisted orderidx 283" which is an indication that MonetDB has found the index and is using it. As a side note, I think in this case it has also figured out that x is revsorted-key (aka unique reverse sorted) but I dont know which one wins over the other:) So in short, if you start mserver5 with --algorithms, redirect the console output in a file and search for BATcheckorderidx you will see if the ordered index is used. Please let us know if there are cases that ordered index should be used but it is not. lefteris On Fri, Feb 16, 2018 at 10:28 AM, Martin Kersten <martin.kersten(a)cwi.nl> wrote: > Hi > Same holds for ordered indices > > On 16/02/2018 10:27, Torsten Grust wrote: >> >> Hi Martin, >> >> thanks for the super-quick reply. You are talking of /imprints/ here but >> I assume that the same is true for /order indexes/ (these are separate >> concepts, right?). >> >> Best wishes, >> —Torsten >> >> >> On Fri, Feb 16, 2018 at 9:55 AM Martin Kersten <martin.kersten(a)cwi.nl >> <mailto:martin.kersten@cwi.nl>> wrote: >> >> Dear Torsten >> >> The use of an imprint is only part of the algebra.select >> implementation >> and depends on actual several properties. >> >> In this particular query, the SQL compiler when encountering an order >> by >> issues a sort statement, even if this is strictly not needed. That >> will be detected >> by the sort implementation. >> >> regards, Martin >> On 15/02/2018 23:31, Torsten Grust wrote: >> > Dear all, >> > >> > given a table definition and (explained) SQL query as follows: >> > >> > |CREATE TABLE foo (x INT,y INT); INSERT INTO foo VALUES (3,1), >> (2,2), (1,3); ALTER TABLE foo SET READ ONLY; CREATE ORDERED INDEX foo_x ON >> foo(x); EXPLAIN SELECT * FROM foo ORDER BY x; | >> > >> > In the |EXPLAIN| output, am I supposed to see that the order index >> is indeed used? I currently do see an |algebra.sort| operation, of course, >> but do not see any reference to the |foo_x| index. I had expected to find an >> |bat.getorderidx| operation in the >> > MAL program. >> > >> > Can anyone shed light on this? I'd be grateful. >> > >> > Cheers, >> > >> > —Torsten >> > >> > -- >> > | Prof. Dr. Torsten Grust >> > | Database Systems — Universität Tübingen (Germany) >> > | ✉︎ torsten.grust(a)uni-tuebingen.de >> <mailto:torsten.grust@uni-tuebingen.de> >> <mailto:torsten.grust@uni-tuebingen.de >> <mailto:torsten.grust@uni-tuebingen.de>> >> > | db.inf.uni-tuebingen.de <http://db.inf.uni-tuebingen.de> >> <http://db.inf.uni-tuebingen.de> >> > >> > >> > _______________________________________________ >> > users-list mailing list >> > users-list(a)monetdb.org <mailto:users-list@monetdb.org> >> > https://www.monetdb.org/mailman/listinfo/users-list >> > >> _______________________________________________ >> users-list mailing list >> users-list(a)monetdb.org <mailto:users-list@monetdb.org> >> https://www.monetdb.org/mailman/listinfo/users-list >> >> -- >> | Prof. Dr. Torsten Grust >> | Database Systems — Universität Tübingen (Germany) >> | ✉︎ torsten.grust(a)uni-tuebingen.de >> <mailto:torsten.grust@uni-tuebingen.de> >> | db.inf.uni-tuebingen.de <http://db.inf.uni-tuebingen.de> >> >> >> _______________________________________________ >> 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 -- | Prof. Dr. Torsten Grust | Database Systems — Universität Tübingen (Germany)| ✉︎ torsten.grust(a)uni-tuebingen.de| db.inf.uni-tuebingen.de

2 1

Order indexes: used or not?
by Torsten Grust 20 Feb '18

20 Feb '18

Dear all, given a table definition and (explained) SQL query as follows: CREATE TABLE foo (x INT,y INT); INSERT INTO foo VALUES (3,1), (2,2), (1,3); ALTER TABLE foo SET READ ONLY; CREATE ORDERED INDEX foo_x ON foo(x); EXPLAIN SELECT * FROM foo ORDER BY x; In the EXPLAIN output, am I supposed to see that the order index is indeed used? I currently do see an algebra.sort operation, of course, but do not see any reference to the foo_x index. I had expected to find an bat.getorderidx operation in the MAL program. Can anyone shed light on this? I'd be grateful. Cheers, —Torsten -- | Prof. Dr. Torsten Grust | Database Systems — Universität Tübingen (Germany) | ✉︎ torsten.grust(a)uni-tuebingen.de | db.inf.uni-tuebingen.de

3 5

matrix manipulation
by Jiří Fejfar 20 Feb '18

20 Feb '18

Dear MonetDB users! I would like to do some simple calculations (matrix multiplication and inversion) with large matrices (cca 40k by 40k or more). We are using PostgreSQL & R but we are facing problems when matrix doesn't fit memory. My initial idea was to use SciQL, but probably it is not merged into MonetDB yet [1]? I am sending also short presentation [2] illustrating nature of our data and calculation (slide 6). Thank you for your suggestions! Jiří. [1] https://www.monetdb.org/pipermail/users-list/2014-May/007319.html [2] https://drive.google.com/open?id=1P4Nuo_YuqhNyxoBqQHeIw6WiXe28I8kE

1 0

Select from remote table leaves session open
by Николай Чернов 19 Feb '18

19 Feb '18

I have two databases (v11.27.13 both) on two Debian8 machines. One with corrupted table (in my case it got corrupted due to bug in Jul2017-SP1) and another with remote table to the corrupted table. When I run the SELECT query from the remote table I get an error. And the session, opened by this query to the first DB did not close. I see it by querying 'sys.sessions' table. And every time I run this SELECT I get additional record in 'sys.sessions'. This happens only if en error occurs, on successful SELECT the session immediately disappears. How can I force this sessions to close or is it a bug?

1 0

Converting a timestamp to a UNIX timestamp
by Bernát Marcell 15 Feb '18

15 Feb '18

Dear MonetDB users, I'm looking for a way to convert a timestamp (or timestamp string) like "2018-02-14 23:59:59" to a UNIX timestamp using MonetDB's functions. In MySQL this can be done using the function UNIX_TIMESTAMP(), so I'm looking for the MonetDB equivalent of that, if it exists. I found sys.epoch(), but that does the opposite of what I need. Thanks in advance. Best regards, Marcell

1 1

Announcement: New Jul2017-SP4 Bugfix release of MonetDB suite
by Sjoerd Mullender 12 Feb '18

12 Feb '18

The MonetDB team at CWI/MonetDB BV is pleased to announce the Jul2017-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/>. Jul 2017-SP4 bugfix release (11.27.13) Build Environment * Added the .lib and .h files needed for building extensions to the Windows installer. Bug Fixes * 3470: Support setClob without length restrictions * 6468: JDBC 2.27 fails with year < 1000 * 6482: Query failures on order by on union * 6483: Monetdb crashes, on query * 6487: UNION of NULLs with several tables fails * 6488: Semijoin returns duplicate values from a column with unique values * 6489: Sqlitelogictest - Wrong result set of complex conditional query * 6490: Sqlitelogictest - Select query with an IN clause parse error * 6491: SELECT IN returns NULL instead of false when not found * 6492: Persistent hashes stored and then ignored. Storage info not in sync with actual indices. * 6493: Sqlitelogictest - Aggregation query on empty table with DISTINCT clause * 6494: Sqlitelogictest - Algebra operators priority in select query * 6495: Sqlitelogictest - Omitting AS in a result set column alias name * 6496: Sqlitelogictest - Select interval comparisons between floating-points and NULL * 6497: Sqlitelogictest - Select not between query producing wrong results * 6498: large virtual memory spike on BLOB column COUNT * 6499: Crash when trying to replace a function defined in sys from a different schema * 6502: Query with multiple limit clauses does not return anything * 6508: Segmentation fault in mserver5 on Python2 UDF with TIMESTAMP column input that has NULL values (conversion.c:438, PyNullMask_FromBAT) * 6510: Sqlitelogictest: Wrong output in aggregation query * 6512: Monetdb crashes on query with limit after sort with case * 6514: Sqlitelogictest: Range query between NULL values not possible * 6515: Insert null second interval value results in 0 * 6516: Sqlitelogictest unknown bat append operation * 6517: Sqlitelogictest overflow in conversion during MAL plan execution * 6518: Sqlitelogictest: count aggregation with not in operator * 6519: Sqlitelogictest: algebra join between lng and int BATs undefined * 6520: UPDATE with correlated subquery causes assertion (or segfault) * 6522: Sqlitelogictest: IN operator return a single column * 6523: Sqlitelogictest: Case statement subquery missing * 6524: Sqlitelogictest: Crash in aggregation query with IN operator * 6527: Crash using order by alias in subquery

1 0

MonetDB Connection drops issue on concurrency
by Sharma, Sreejith 12 Feb '18

12 Feb '18

Hi All, We have a situation where we are trying to emulate a scenario where we have multiple clients hitting into MonetDB instance in parallel, I mean with concurrency of for say 12 users. Queries are going through fine, however we notice connection drops in some cases and queries get cancelled. We assume, this happens when port is idle and when trying to reconnect there is a SQLConnect failure issues. If it's queries are firing uninterruptedly no issues spotted, if there is any idle situation the issue occurs. Can the "forward" - "redirect" option help here? If not, what other options? Regards, Sreejith

1 0

Diacritic removal
by Roberto Cornacchia 09 Feb '18

09 Feb '18

Just wondering, before I do it myself, whether "normalizing" strings by removing diacritics is a feature that you guys would consider interesting to implement. (I am aware that results of this "normalization" can be unsatisfactory in some cases - I'm ok with most obvious cases covered) Basically, something like what iconv does here: $ echo "Ã é ç" | iconv -f UTF-8 -t ASCII//TRANSLIT A e c I can imagine that these above would be pretty easy to solve using the same hash lookup approach used for case conversion. These, however, are example that produce more than one symbol in output. So that would be a little more complicated. $ echo "æ ß" | iconv -f UTF-8 -t ASCII//TRANSLIT ae ss So I have two questions: 1) Is this something you guys (MonetDB developers) would be interested in implementing? 2) If not - in that case I'll give it a go myself, would you advise an approach similar to the case conversion or to use the iconv lib? Thanks for your input. Rboerto

2 2

Install on Linux with no access to Internet
by ALEX OSSIPOV 08 Feb '18

08 Feb '18

I am trying to install MonetDB with client on the host without access to Internet. Are there any instructions to follow? Thanks Alex

3 5

MonetDB Evaluation - Excessive slowness in a particular query
by Alfio Bettini 06 Feb '18

06 Feb '18

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>

2 3

SQL Connect issues - ODBC Driver failure when idle situation
by Sharma, Sreejith 06 Feb '18

06 Feb '18

Hi All, Have a quick question - I have a situation where we are trying to emulate a scenario where we have multiple clients hitting into MonetDB instance in parallel, I mean with concurrency of for say 12 users. Queries are going through fine, however we notice some cases when a port/machine is idle and trying to reconnect we are finding SQLConnect failure issues. If it's queries are firing continuously it's fine, here if when some idle situation it may occur. Can the "forward" - redirect option help here? Regards, Sreejith

1 0

SQL Connect issues - ODBC Driver failure when idle situation
by Sharma, Sreejith 01 Feb '18

01 Feb '18

Hi All, Have a quick question - I have a situation where we are trying to emulate a scenario where we have multiple clients hitting into MonetDB instance in parallel, I mean with concurrency of for say 12 users. Queries are going through fine, however we notice some cases when a port/machine is idle and trying to reconnect we are finding SQLConnect failure issues. If it's queries are firing continuously it's fine, here if when some idle situation it may occur. Can the "forward" - redirect option help here? Regards, Sreejith

1 0

Crash using aggregate function inside a case statement in having clause
by Trevor Andreas 30 Jan '18

30 Jan '18

Hello, Database: MonetDB v11.27.11 (Jul2017-SP3) I am experiencing a crash when attempting to use an aggregate function inside a case statement in a having clause. Below is an example. One use case for this is that I am computing a formula on aggregated data, and want to ensure no divide by zero occurs. Are case statements in having clauses supported? Thanks! create table "t1" ( "id" int, "c1" varchar(100), "d1" int, "d2" int ); insert into "t1" values (1, 'A', 50, 80), (2, 'A', 200, 350), (3, 'A', 89, 125), (4, 'B', 4845, 13), (5, 'B', 194, 597), (6, 'C', 5636, 5802), (7, 'C', 375, 3405), (7, 'D', 365, 0), (7, 'D', 87, 0); -- Works select "c1", sum("d1") as "d1", sum("d2") as "d2" from "t1" group by "c1" having sum("d1") < case when 5 > 10 then 500 else 400 end; -- Works select "c1", sum("d1")as "d1", sum("d2") as "d2", 1.0 * sum("d1") / (1.0 * case when sum("d2") > 0 then sum("d2") else null end) as "formula" from "t1" group by "c1"; -- Crashes select "c1", sum("d1")as "d1", sum("d2") as "d2", 1.0 * sum("d1") / (1.0 * case when sum("d2") > 0 then sum("d2") else null end) as "formula" from "t1" group by "c1" having (1.0 * sum("d1") / (1.0 * case when sum("d2") > 0 then sum("d2") else null end)) > 1; Looking in merovingian.log: 2018-01-29 14:47:15 ERR test[2879]: *** Error in `/usr/bin/mserver5': double free or corruption (out): 0x00007f57200ea4b0 *** 2018-01-29 14:47:18 MSG merovingian[504]: database 'test' (2879) has crashed (dumped core) 2018-01-29 14:49:35 MSG merovingian[504]: database 'test' has crashed after start on 2018-01-29 14:42:36, attempting restart, up min/avg/max: 0s/0s/0s, crash average: 1.00 0.20 0.07 (2-0=2) 2

2 1

Help with performance issue
by Roberto Cornacchia 26 Jan '18

26 Jan '18

Hi there, Would someone like to pick up this wonderful opportunity to tell me that I'm doing something silly? Please do. In this loop, the cleanup take 4/5 of the total time. Is there a better way of doing this? loop(...) { BAT *gn, *en, *hn; BAT *idxn, *tn, *pn; // tokenize, group, transform, append // this takes 1/5 of the total loop time if (BATutf8_tokenize(&tokens, s, delims, min_tok_len) != GDK_SUCCEED) goto fail; if (BATgroup(&gn, &en, &hn, tokens, NULL, NULL, NULL, NULL) != GDK_SUCCEED) goto fail; idxn = BATconstant(0, TYPE_int, &idx, BATcount(en), TRANSIENT); tn = BATproject(en,tokens); pn = BATconvert(hn, NULL, TYPE_dbl, TRUE); if (BATappend(br1,idxn,NULL,FALSE) != GDK_SUCCEED) goto fail; if (BATappend(br2,tn,NULL,FALSE) != GDK_SUCCEED) goto fail; if (BATappend(br3,pn,NULL,FALSE) != GDK_SUCCEED) goto fail; // this takes 4/5 of the total loop time BBPreclaim(idxn); BBPreclaim(tn); BBPreclaim(pn); BBPreclaim(hn); BBPreclaim(gn); BBPreclaim(en); BBPreclaim(tokens); }

3 6

"brew install monetdb" failed due to "libxml2 library not found"
by Ying Zhang 25 Jan '18

25 Jan '18

Dear all, I try to install MonetDB with homebrew, but it failed with the error: configure: error: libxml2 library not found but required for MonetDB5 But as far as I can tell, libxml2 is already installed, e.g. $ brew list libxml2 /usr/local/Cellar/libxml2/2.9.7/bin/xml2-config /usr/local/Cellar/libxml2/2.9.7/bin/xmlcatalog /usr/local/Cellar/libxml2/2.9.7/bin/xmllint /usr/local/Cellar/libxml2/2.9.7/include/libxml2/ (47 files) /usr/local/Cellar/libxml2/2.9.7/lib/libxml2.2.dylib /usr/local/Cellar/libxml2/2.9.7/lib/cmake/libxml2/libxml2-config.cmake /usr/local/Cellar/libxml2/2.9.7/lib/pkgconfig/libxml-2.0.pc /usr/local/Cellar/libxml2/2.9.7/lib/python2.7/ (6 files) /usr/local/Cellar/libxml2/2.9.7/lib/ (3 other files) /usr/local/Cellar/libxml2/2.9.7/share/aclocal/libxml.m4 /usr/local/Cellar/libxml2/2.9.7/share/doc/ (153 files) /usr/local/Cellar/libxml2/2.9.7/share/gtk-doc/ (55 files) /usr/local/Cellar/libxml2/2.9.7/share/man/ (4 files) $ brew install libxml2 Warning: libxml2-2.9.7 already installed Is there anything I can do to fix this? Thanks a lot in advance! Jennie =============== Complete output of “brew install monetdb” =============== $ brew install monetdb Warning: You are using macOS 10.13. We do not provide support for this pre-release version. You may encounter build failures or other breakages. Please create pull-requests instead of filing issues. ==> Using the sandbox ==> Downloading https://www.monetdb.org/downloads/sources/Jul2017-SP3/MonetDB-11.27.11.tar.… Already downloaded: /Users/jennie/Library/Caches/Homebrew/monetdb-11.27.11.tar.xz ==> ./configure --prefix=/usr/local/Cellar/monetdb/11.27.11 --enable-debug=no --enable-assert=no --enable-optimi Last 15 lines from /Users/jennie/Library/Logs/Homebrew/monetdb/01.configure: checking how to link with libiconv... -liconv checking for iconv declaration... extern size_t iconv (iconv_t cd, char * *inbuf, size_t *inbytesleft, char * *outbuf, size_t *outbytesleft); checking for library containing uuid_generate... none required checking for CC_MD5_Update... yes checking for CC_RIPEMD160_Update... no checking for CC_SHA1_Update... yes checking for CC_SHA224_Update... yes checking for CC_SHA256_Update... yes checking for CC_SHA384_Update... yes checking for CC_SHA512_Update... yes checking for pcre... yes checking whether pcre comes with UTF-8 support... yes checking for libxml2... no configure: error: libxml2 library not found but required for MonetDB5 READ THIS: https://git.io/brew-troubleshooting If reporting this issue please do so at (not Homebrew/brew): https://github.com/Homebrew/homebrew-core/issues Warning: You are using macOS 10.13. We do not provide support for this pre-release version. You may encounter build failures or other breakages. Please create pull-requests instead of filing issues.

2 2

Re: MonetDB - question on rename table
by David Jankoski 25 Jan '18

25 Jan '18

hey sreejith, maybe this SO answer will be helfpul to you: https://stackoverflow.com/questions/19661538/how-to-rename-a-table-in-monet… hth. david On 25 January 2018 at 08:56, <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. MonetDB-extend clang error: ld: unknown option: -soname > (Ying Zhang) > 2. Re: MonetDB-extend clang error: ld: unknown option: -soname > (Sjoerd Mullender) > 3. "brew install monetdb" failed due to "libxml2 library not > found" (Ying Zhang) > 4. MonetDB - question on rename table (Sharma, Sreejith) > 5. Re: MonetDB - question on rename table (Stefan Manegold) > 6. RE: MonetDB - question on rename table (Sharma, Sreejith) > > > ---------------------------------------------------------------------- > > Message: 1 > Date: Wed, 24 Jan 2018 15:56:19 +0100 > From: Ying Zhang <Y.Zhang(a)cwi.nl> > To: Communication channel for MonetDB users <users-list(a)monetdb.org> > Subject: MonetDB-extend clang error: ld: unknown option: -soname > Message-ID: <1DD5C9B8-D559-4CD2-89E0-35CA729C9B67(a)cwi.nl> > Content-Type: text/plain; charset=utf-8 > > Dear all, > > I was trying to compile the regexp example in the MonetDB-extend repo on a Mac OS 10.13, but got an ?unknown option? error: > > $ make > cc -fPIC -DPIC -o lib_regexp.so -shared regexp.o -L/usr/local/opt/openssl/lib -L/Users/jennie/scratch/monet-install/Jul2017/debug/lib -L/usr/local/Cellar/pcre/8.41/lib -lmonetdb5 -lbat -lpcre -Wl,-soname -Wl,lib_regexp.so > ld: unknown option: -soname > clang: error: linker command failed with exit code 1 (use -v to see invocation) > make: *** [lib_regexp.so] Error 1 > > I looked around a bit. There are many discussions about clang error ld unknown option, but nothing specific for the ?-soname? option. Simply remove this option is not an option. > > Does anyone know how to fix/workaround this? > > Thanks a lot! > > Jennie > > ------------------------------ > > Message: 2 > Date: Wed, 24 Jan 2018 16:34:38 +0100 > From: Sjoerd Mullender <sjoerd(a)acm.org> > To: users-list(a)monetdb.org > Subject: Re: MonetDB-extend clang error: ld: unknown option: -soname > Message-ID: <78539081-3ab0-3b89-d22d-dbd12af4a6fa(a)acm.org> > Content-Type: text/plain; charset="utf-8" > > On 24/01/18 15:56, Ying Zhang wrote: >> Dear all, >> >> I was trying to compile the regexp example in the MonetDB-extend repo on a Mac OS 10.13, but got an ?unknown option? error: >> >> $ make >> cc -fPIC -DPIC -o lib_regexp.so -shared regexp.o -L/usr/local/opt/openssl/lib -L/Users/jennie/scratch/monet-install/Jul2017/debug/lib -L/usr/local/Cellar/pcre/8.41/lib -lmonetdb5 -lbat -lpcre -Wl,-soname -Wl,lib_regexp.so >> ld: unknown option: -soname >> clang: error: linker command failed with exit code 1 (use -v to see invocation) >> make: *** [lib_regexp.so] Error 1 >> >> I looked around a bit. There are many discussions about clang error ld unknown option, but nothing specific for the ?-soname? option. Simply remove this option is not an option. >> >> Does anyone know how to fix/workaround this? >> >> Thanks a lot! >> >> Jennie > > The question really is, how does one create a dynamically loaded module > on MacOS? The file will be loaded dynamically by mserver, not linked > into a program. > > > -- > Sjoerd Mullender > >

1 0

Re: MonetDB - question on rename table
by Stefan Manegold 25 Jan '18

25 Jan '18

Yep: that feature is not implemented. -------- Original message --------From: "Sharma, Sreejith" <Sreejith.Sharma(a)harman.com> Date: 1/25/18 07:45 (GMT+01:00) To: Communication channel for MonetDB users <users-list(a)monetdb.org> Subject: MonetDB - question on rename table All, Any reason why we can’t do RENAME of table in MonetDB? Regards, Sreejith

2 1

MonetDB - question on rename table
by Sharma, Sreejith 25 Jan '18

25 Jan '18

All, Any reason why we can't do RENAME of table in MonetDB? Regards, Sreejith

1 0

MonetDB-extend clang error: ld: unknown option: -soname
by Ying Zhang 24 Jan '18

24 Jan '18

Dear all, I was trying to compile the regexp example in the MonetDB-extend repo on a Mac OS 10.13, but got an “unknown option” error: $ make cc -fPIC -DPIC -o lib_regexp.so -shared regexp.o -L/usr/local/opt/openssl/lib -L/Users/jennie/scratch/monet-install/Jul2017/debug/lib -L/usr/local/Cellar/pcre/8.41/lib -lmonetdb5 -lbat -lpcre -Wl,-soname -Wl,lib_regexp.so ld: unknown option: -soname clang: error: linker command failed with exit code 1 (use -v to see invocation) make: *** [lib_regexp.so] Error 1 I looked around a bit. There are many discussions about clang error ld unknown option, but nothing specific for the “-soname” option. Simply remove this option is not an option. Does anyone know how to fix/workaround this? Thanks a lot! Jennie

2 1

Question on MonetDB Source Code Compilation
by Sharma, Sreejith 24 Jan '18

24 Jan '18

All, Here is a quick question. I have compiled "MonetDB v11.27.9 (Jul2017-SP2)" version of source code and while running below sample SQL am finding an issue. Could anyone help me to trace issue? Is this due to the compiler version? Issue Details: While using SUM() or any function without an alias the header datatype returned is showing as HUGEINT for base INTEGER column, where as it should have been BIGINT. This is causing a failure in my BI application as it's having issues in identifying HUGEINT. Below are the details. Source Code Version : MonetDB v11.27.9 (Jul2017-SP2) Linux OS Version : CentOS release 6.3 (Final) C Compile Version : gcc version 4.4.7 20120313 (Red Hat 4.4.7-16) (GCC) Also, I see below note in MonetDB documentation. Which version of GCC should be appropriate for compilation with __128 bit integer. Note: HUGEINT is only available since the Jul2015 release and on platforms with a C-compiler that supports the __int128 or __int128_t data type (e.g., recent gcc, clang, & icc on Linux or MacOS X) Sample Table DDL : CREATE TABLE INT_VALID (COL_INT INTEGER, COL_BIGINT BIGINT, COL_HUGEINT HUGEINT, COL_DOUBLE DOUBLE, COL_NUMERIC NUMERIC(10)); Compiled Version sql>SELECT SUM(col_int) FROM INT_VALID; mapi_query_part:36:SELECT SUM(col_int) FROM INT_VALID; fetch next block: start at:10093 got next block: length:105 text:&1 4 1 1 1 2452 % DM_POS_TSV_MRT_P6A.L4 # table_name % L3 # name % hugeint # type % 5 # length [ 11110 ] Binary Version sql>SELECT SUM(col_int) FROM INT_VALID; mapi_query_part:36:SELECT SUM(col_int) FROM INT_VALID; fetch next block: start at:1168 got next block: length:105 text:&1 4 1 1 1 13998 % DM_POS_TSV_MRT_P6A.L4 # table_name % L3 # name % bigint # type % 5 # length [ 11110 ] +---------+------------+--------------------------+-------------+ | col_int | col_bigint | col_double | col_numeric | +=========+============+==========================+=============+ | 1111 | 1234567 | 1234.44 | 1111 | | 2222 | 2345678 | 34355.66 | 2222 | | 3333 | 3456789 | 66423.66 | 3333 | | 4444 | 45678912 | 8765.66 | 4444 | +---------+------------+--------------------------+-------------+ Regards, Sreejith

2 3

Memory leak in MonetDB
by Sergii Kliuchnyk 23 Jan '18

23 Jan '18

Hello, my name Sergii. I hope this is right place for this message. In our company we using MonetDB near 1 year and we have only one problem with it - memory leak. We found only one dependence - if we making queries then memory starts growing. I read "Memory footprint" article that MonetDB excessively uses main memory for processing, but I thought it should stop allocate memory after sometime but it's not. And looks like this allocated memory is not gives some performance. What we do now is restarting monet server every month. I found on mserver5 man-page parameter "gdk_vmtrim" but I'm not 100% sure is it what we need and what correct way to apply this parameter. Maybe MonetDB SQL has some function to unload no needed memory? Maybe it's php/nodejs clients (from github.com/MonetDB repo) somehow cause this memory leaks? I will appreciate any help

1 1

BUG: on CREATE TABLE (like)
by Ariel Abadi 18 Jan '18

18 Jan '18

Hi! It seems to be, that there is a BUG on the statement CREATE TABLE.. It is not being consider the *DEFAULT property.* Take a look on the following example: sql>CREATE TABLE test (updated TIMESTAMP(0) DEFAULT current_timestamp()); operation successful (1.433ms) sql>\d test CREATE TABLE "sb_traf"."test" ( "updated" TIMESTAMP(0) DEFAULT current_timestamp() ); sql>CREATE TABLE test2 (LIKE test); operation successful (1.102ms) sql>\d test2 CREATE TABLE "sb_traf"."test2" ( "updated" TIMESTAMP(0) ); May be Im missing something, if thats the case, please let me know! Rgds Ariel

2 2

Embedded Python/NumPy
by Ariel Abadi 18 Jan '18

18 Jan '18

Hi! Someone can help please!?!?!!! I have created a function in python (see attached the definition), every time I try to use it against a "big table" monetdb is hanging out. What I found is that instead of sending the value of each nrm_json (to the function) , monet is sending the full column!!!.. This means that if the table has 10MM rows, then python is receiving ALL the records instead of the nrm_json STRING: SELECT sb_glob.getjsonfield_python(nrm_json, 'conference_id') FROM nrm_20180107 LIMIT 3; CREATE FUNCTION sb_glob.getjsonfield_python(nrm_json STRING, nrm_field STRING) RETURNS STRING LANGUAGE PYTHON { import ast a=ast.literal_eval(str(nrm_json.encode('utf-8'))) if not a.has_key(nrm_field): a[nrm_field] = '' return str(a[nrm_field]) }; I hope you understand, and guide me in solving this problem Thks in advance Ariel

1 0

program contains errors occured
by Amit Ambekar 18 Jan '18

18 Jan '18

Hi, I have big table around 15 mln records. getting error as "program contains errors" when i tried to fetch data using select query

2 1

Python Functions
by Ariel Abadi 11 Jan '18

11 Jan '18

Hi! Maybe someone can help me. Im trying to connect to a MySQL table to get some data and use it within MonetDB. Eveything works fine, TILL... the query Im running on MySQL is bringing NO DATA. As an example I put it as LIMIT 0 so I dont receive any row. Eventhough I have considered that situation and have defined the array with null values, Im still getting an error. *Could not create a Numpy array from the return type.* Does anybody has an idea how to solve this issue? Thks in advance! Ariel ----------------------------------------------------- DROP FUNCTION connect_to_mysql() ; CREATE FUNCTION connect_to_mysql() RETURNS TABLE(agr_id integer, agr_name string ) LANGUAGE PYTHON { import datetime import mysql.connector cnx = mysql.connector.connect(user='XXXX', password='XXXX',host='192.168.15.5',database='XXXXX',use_pure=False) cursor = cnx.cursor() query = "SELECT agr_id, agr_name FROM AGR_Agreement LIMIT 0 " cursor.execute(query) allrows = cursor.fetchall() ncols = len(cursor.description) colnames = [cursor.description[i][0] for i in range(ncols)] result = dict() for col in range(ncols): if len(allrows) == 0: result[colnames[col]] = [None] for row in range(len(allrows)): if allrows[row][col] == None: valor = None else: valor = str(allrows[row][col]) try: result[colnames[col]].append(valor) except KeyError: result[colnames[col]] = [valor] cursor.close() cnx.close() return result }; sql>SELECT * FROM connect_to_mysql() ; Could not create a Numpy array from the return type.

1 0

DEC2016-SP2 and BINARY bulk load
by Lynn Carol Johnson 11 Jan '18

11 Jan '18

Hi all – I have always used the COPY BINARY INTO … commands to load my 2.0 Billion row genetic data into a monetdb table. With 135 columns, it has been blindingly fast. Last week I moved from the June2016-SP2 release to dec2016-SP2. My binary loads are taking WAY longer. I killed one after 3 hours (via “call sys.stop(pid)” so it could clean up properly). I then started the load again, thinking perhaps the problem was related to the new columns I was adding. I have since dropped the table and remade it using the same data and scripts that worked in just over 3 minutes in February on the jun2016-SP2 load. It is really chugging along – I’m up to 30 minutes and counting. I don’t have access to the sql log files, but the Merovingian.log shows nothing. I do notice that previously the binary files, once loaded, were removed from the loading directly. This does not happen now. Were these files previously “moved” and now they are copied? Has anyone see this performance issue with Dec2016-SP2 COPY BINARY INTO …. Commands? Thanks - Lynn

5 17

RE: MonetDB - Column Header Question
by Sharma, Sreejith 09 Jan '18

09 Jan '18

I see below issue is happening only if base column datatype is integer. All other types working fine. From: Sharma, Sreejith Sent: Saturday, January 06, 2018 8:24 PM To: users-list(a)monetdb.org Subject: MonetDB - Column Header Question All, Quick question - For a specific scenario I would like to understand below - If we don't specify column header in SQL or alias, how is MonetDB picks up the column header? And what is the datatype for it? I saw it's hge (scalar type) is it so? Also, I would like to know where in source code it's happening. Example: SELECT SUM(COLUMN_NAME) FROM TABLE_NAME; ------------------+ | L3 | ------------------+ |123456678 | ------------------+ Regards, Sreejith

2 3

Bulk data upload through JDBC
by suvendu chakra 09 Jan '18

09 Jan '18

Hello, I am following SQLCopInto.java to upload bulk data into MonetDB but seems to be not working. I am even using mclient command to upload data but the command expects the input file to be accessible by the DB server, is there any alternative to this. Please suggest a best way to do through JDBC Thanks

3 3

MonetDB - Column Header Question
by Sharma, Sreejith 06 Jan '18

06 Jan '18

All, Quick question - For a specific scenario I would like to understand below - If we don't specify column header in SQL or alias, how is MonetDB picks up the column header? And what is the datatype for it? I saw it's hge (scalar type) is it so? Also, I would like to know where in source code it's happening. Example: SELECT SUM(COLUMN_NAME) FROM TABLE_NAME; ------------------+ | L3 | ------------------+ |123456678 | ------------------+ Regards, Sreejith

1 0

SyntaxException:parseError:Xclose
by Dennis Fassl 06 Jan '18

06 Jan '18

Hi all, I am generating MAL queries in Java and I am using the JDBC interface to execute them. This works fine for small tables. But if the resultSet has more than 100 entries MonetDB is printing the first 100 rows and then gives me the following error: ... [ 19, "undergraduateDegreeFrom", 1509 ] [ 19, "memberOf", 613 ] [ 19, "takesCourse", 556 ] [ 19, "advisor", 600 ] [ 20, "undergraduateDegreeFrom", 1493 ] [ 20, "memberOf", 613 ] [ 20, "takesCourse", 529 ] [ 20, "takesCourse", 578 ] [ 20, "takesCourse", 557 ] [ 20, "advisor", 1315 ] MAPI = (monetdb) /tmp/.s.monetdb.50000 QUERY = sql.resultSet(table_names, table_attrs, table_type, table_lens, table_scales, start_id, type, end_id); ERROR = !SyntaxException:parseError:Xclose 0 !SyntaxException:parseError: ^';' expected Could you help me to avoid this error? This is the full query: sql.init(); tbd := sql.mvc(); start_id:bat[:int] := sql.bind(tbd,"sys","relations","start_id",0:int); type:bat[:str] := sql.bind(tbd,"sys","relations","type",0:int); end_id:bat[:int] := sql.bind(tbd,"sys","relations","end_id",0:int); table_names := bat.new(nil:str); table_names := bat.append(table_names, "start_id"); table_names := bat.append(table_names, "type"); table_names := bat.append(table_names, "end_id"); table_attrs := bat.new(nil:str); table_attrs := bat.append(table_attrs, "str"); table_attrs := bat.append(table_attrs, "str"); table_attrs := bat.append(table_attrs, "str"); table_type := bat.new(:str); table_type := bat.append(table_type, "int"); table_type := bat.append(table_type, "varchar"); table_type := bat.append(table_type, "int"); table_lens := bat.new(nil:int); table_lens := bat.append(table_lens, 1); table_lens := bat.append(table_lens, 1); table_lens := bat.append(table_lens, 1); table_scales := bat.new(nil:int); table_scales := bat.append(table_scales, 0); table_scales := bat.append(table_scales, 0); table_scales := bat.append(table_scales, 0); sql.resultSet(table_names, table_attrs, table_type, table_lens, table_scales, start_id, type, end_id); I am using "MonetDB Database Server v1.7 (Dec2016-SP5)" on Ubuntu 14.04. What am I trying to do: The goal of the project is to use Cypher as an input language for MonetDB. I have a Cypher optimizer written in Java. The output of this optimizer should be used as input for MonetDB. In the end we want to compare the the runtime of a query in MonetDB and in Neo4J. Thanks and regards, Dennis

1 1

Re: MonetDB header Datatype Question
by Stefan Manegold 04 Jan '18

04 Jan '18

What's the (SQL-)type of your column COLUMN_NAME?Which version of MonetDB are you using?Which version of the MonetDB ODBC driver are you using?Which compiler did you use to compile MonetDB on which OS?What did configure say about what the largest supported integer type is? -------- Original message --------From: "Sharma, Sreejith" <Sreejith.Sharma(a)harman.com> Date: 12/31/17 18:33 (GMT+01:00) To: users-list(a)monetdb.org Subject: RE: MonetDB header Datatype Question All, Here is the specifics of error – typeODBC: SQL_UNKNOWN_TYPE mDataType: HG_NULL_VALUE(hgColumnType) precision: 0 scale: 0 nullable: true Column name: L6. Regards, Sreejith From: Sharma, Sreejith Sent: Sunday, December 31, 2017 11:21 AM To: users-list(a)monetdb.org Subject: MonetDB header Datatype Question All, I would like to know what is the MonetDB header datatype if no alias or column name provided? In my BI application, am getting an error while using SELECT SUM(DISTINCT COLUMN_NAME) FROM TABLE_NAME. I see it works in 11.27.9 build when installed in CentOS. However, when i download the source tarball and recompile the code, this fox seems to be lost. Is there anywhere this can be identified? Or fixed? Regards, Sreejith

3 5

Re: MonetDB header Datatype Question
by Stefan Manegold 04 Jan '18

04 Jan '18

Looks fine to me, but of course you're free to try a different compiler of your choice. Stefan -------- Original message --------From: "Sharma, Sreejith" <Sreejith.Sharma(a)harman.com> Date: 1/4/18 10:31 (GMT+01:00) To: Communication channel for MonetDB users <users-list(a)monetdb.org> Subject: RE: MonetDB header Datatype Question Thanks Stefan. However am suspecting if it's to do with the compiler. Did you got a chance to look at the details I sent across for the questions. configure:5039: gcc --version >&5 gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-16) Regards, Sreejith -----Original Message----- From: users-list [mailto:users-list-bounces+sreejith.sharma=harman.com@monetdb.org] On Behalf Of Stefan Manegold Sent: Wednesday, January 03, 2018 2:11 PM To: Communication channel for MonetDB users <users-list(a)monetdb.org> Subject: [EXTERNAL] Re: MonetDB header Datatype Question I was mislead by the "HG" prefix in the error message. I now realize that the error message does not seem to be a MonetDB error message. Hence, I'm afraid we cannot help much. You said is works/worked with a binary installation of Jul2017-SP2 (11.27.9), but does not with a self-compiled installation of the very same release. You'd need to double-check you compilation and installation of the self-compiled release. You can also try with the latest Jul2017-SP3 (11.27.11) release --- binary and/or self-compiled --- but as said, since the error message is not from MonetDB, you'd need to search for the problem (also) in the client software / application you're using. Best, Stefan ----- On Jan 1, 2018, at 5:55 PM, Sharma, Sreejith Sreejith.Sharma(a)harman.com wrote: > Hi Stefan, > > > > Here are the details you had asked for. Any help here soon is appreciated. > > > > What's the (SQL-)type of your column COLUMN_NAME? – > > INTEGER > > > > Which version of MonetDB are you using? – > > MonetDB 5 server v11.27.9 "Jul2017-SP2" (64-bit, 128-bit integers) > > > > Which version of the MonetDB ODBC driver are you using? > > ODBC Driver - /usr/lib64/libMonetODBC.so (11.27.9 and tried 11.27.5 > updated) > > > > Which compiler did you use to compile MonetDB on which OS? > > configure:5030: checking for C compiler version > > configure:5039: gcc --version >&5 > > gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-16) > > > > #define PACKAGE_NAME "MonetDB" > >| #define PACKAGE_TARNAME "MonetDB" > >| #define PACKAGE_VERSION "11.27.9" > >| #define PACKAGE_STRING "MonetDB 11.27.9" > >| #define PACKAGE_BUGREPORT "https://clicktime.symantec.com/a/1/js6skxjfSiwdIJPQwPy1ginHSudH8-MwrUFzw00H…" > >| #define PACKAGE_URL "https://clicktime.symantec.com/a/1/ZNQS7LXKm8wqT3QEExvIAnZ3Vk2CRq8bspHohtAV…" > >| #define PACKAGE "MonetDB" > >| #define VERSION "11.27.9" > >| #define HOST "x86_64-pc-linux-gnu" > >| #define MONETDB_RELEASE "Jul2017-SP2" > > > > What did configure say about what the largest supported integer type is? > > hge_bits='128' > > largest integer type: ${hge_bits} bits" > > > > > > Regards, > > Sreejith > > > > > From: users-list > [mailto:users-list-bounces+sreejith.sharma=harman.com@monetdb.org] On > Behalf Of Stefan Manegold > Sent: Monday, January 01, 2018 7:05 AM > To: Communication channel for MonetDB users <users-list(a)monetdb.org> > Subject: [EXTERNAL] Re: MonetDB header Datatype Question > > > > > > > > > What's the (SQL-)type of your column COLUMN_NAME? > > > Which version of MonetDB are you using? > > > Which version of the MonetDB ODBC driver are you using? > > > Which compiler did you use to compile MonetDB on which OS? > > > What did configure say about what the largest supported integer type is? > > > > > > > > > -------- Original message -------- > > > From: "Sharma, Sreejith" < Sreejith.Sharma(a)harman.com > > > > Date: 12/31/17 18:33 (GMT+01:00) > > > To: users-list(a)monetdb.org > > > Subject: RE: MonetDB header Datatype Question > > > > > > All, > > > > Here is the specifics of error – > > > > typeODBC: SQL_UNKNOWN_TYPE mDataType: HG_NULL_VALUE(hgColumnType) > precision: 0 > scale: 0 nullable: true Column name: L6. > > > > Regards, > > Sreejith > > > > > From: Sharma, Sreejith > Sent: Sunday, December 31, 2017 11:21 AM > To: users-list(a)monetdb.org > Subject: MonetDB header Datatype Question > > > > > All, > > > > > > I would like to know what is the MonetDB header datatype if no alias > or column name provided? In my BI application, am getting an error > while using SELECT SUM(DISTINCT COLUMN_NAME) FROM TABLE_NAME. I see it > works in 11.27.9 build when installed in CentOS. However, when i > download the source tarball and recompile the code, this fox seems to be lost. Is there anywhere this can be identified? > Or fixed? > > > Regards, > > > Sreejith > > _______________________________________________ > users-list mailing list > users-list(a)monetdb.org > https://clicktime.symantec.com/a/1/EiR5ebgK8iUa4CLKJS5IhZJEZAKRFGZRDFU > FUCIYntY=?d=l99sUyvGli3efslX3N2ZiOXRT2oUDQGPF4w16I0hM9zUMoOkJQMri6AAcO > e4XlOqB4NFs5YhzH2NIIET0LyAMDvzRQkCYnLv2cWSJsLXuGRkmOTx5BxDA1nwzncmE0Ex > YksGCv1OTBY5h8YekFgUFxCvagkJjjUM6qSdc8J_3jJdMcNfFBT-6B5sCtZZWTBqGFRvVr > mq4XFYO_7DYsI9LdIJf7p8tqhBL-xSVWB3pCxqFx46Iyjz8nidH-BXBjg-pRI2Gk56GQ4p > kiPFSFlud_PVfn48XxozGVFit5p6x7F-AVEgKrooyJUyAchXTTYPKW5qN60JUu-5toDlGZ > U0tcE6B8a_-BAZmcVxJiZTehQx5mL1hAm0sBK2BdVeLD5BkXQ_JG8gnBigTg0N4E9I6b5C > geHSGmxUBIKMr_4WO21MsBtAqElF9oRpXg%3D%3D&u=https%3A%2F%2Fwww.monetdb.o > rg%2Fmailman%2Flistinfo%2Fusers-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://clicktime.symantec.com/a/1/EiR5ebgK8iUa4CLKJS5IhZJEZAKRFGZRDFUFUCIY… _______________________________________________ users-list mailing list users-list(a)monetdb.org https://www.monetdb.org/mailman/listinfo/users-list

1 0

MonetDB header Datatype Question
by Sharma, Sreejith 31 Dec '17

31 Dec '17

All, I would like to know what is the MonetDB header datatype if no alias or column name provided? In my BI application, am getting an error while using SELECT SUM(DISTINCT COLUMN_NAME) FROM TABLE_NAME. I see it works in 11.27.9 build when installed in CentOS. However, when i download the source tarball and recompile the code, this fox seems to be lost. Is there anywhere this can be identified? Or fixed? Regards, Sreejith

1 1

Monetdb date column issue
by Amit Ambekar 27 Dec '17

27 Dec '17

Hi, i am using MonetDB v11.23.7 (Jun2016-SP1) version. in that when is reboot my ubuntu machine, my date columns in all tables becoming null . is there any issue related to version? Warm Regards, *Amit Suryakant 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

1 0

SELECT: too many nested operators
by Sharma, Sreejith 19 Dec '17

19 Dec '17

All, I have a valid scenario where our application generates a SQL which is around 4.4MB and contains 75K of combination of AND and OR operators. This SQL fails with "SELECT: too many nested operators" error while executing this SQL. While I went through the code, I noticed any thread size above THREAD_STACK_SIZE - 16 * 1024 is getting aborted. Is there any possibility that we can change this? Any reason why it's set to 16MB? As server our server has enough memory and storage and we need to understand if this can be handled in code or via parameter as this is valid scenario. Also, we can't split the SQL too. Regards, Sreejith

4 4

too many nested operators - Query size 4MB
by Sharma, Sreejith 16 Dec '17

16 Dec '17

All, Have a quick question- I have been trying to perform a test on bigger SQL and I see SQL's are failing which are above 4 MB with error "too many nested operators". There are many AND and OR operators. Not sure if there is any limitations. Regards, Sreejith

1 0

Announcement: New Jul2017-SP3 Bugfix release of MonetDB suite
by Sjoerd Mullender 14 Dec '17

14 Dec '17

The MonetDB team at CWI/MonetDB BV is pleased to announce the Jul2017-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/>. Jul 2017-SP3 bugfix release (11.27.11) MonetDB Common * Reimplemented summing of a column of floating point (flt and dbl) values. The old code could give wildly inaccurate results when adding up lots and lots of values due to lack of precision. Try SELECT sum(c) FROM t; where t is 100,000,000 rows, c is of type REAL and all values are equal to 1.1. (The old code returned 33554432 instead of 1.1e8.) Bug Fixes * 3898: Deadlock on insertion * 6429: ROUND produces wrong data type * 6436: Query sequence with 2x ifthenelse() and next nullif() causes mserver5 Segmentation fault * 6437: System schemas "profiler" and "json" shouldn't be allowed to be dropped. * 6439: Invalid references to sys.columns.id from sys.statistics.column_id * 6442: SEGFAULT with COPY INTO BEST EFFORT and skipping input columns * 6443: complex(?) query forgets(?) column name * 6444: Using 'with' keyword with table returning function crashes monetdb * 6445: Sqlitelogictest crash in MySQL query * 6446: sql_parser.y bug? * 6448: 'insert into' with multiple rows containing subqueries crashes * 6449: Assertion error in rel_dce_refs (sqlsmith) * 6450: Assertion error in exp_bin (sqlsmith) * 6451: Assertion error in sql_ref_dec (sqlsmith) * 6453: Assertion error in rel_rename_exps (sqlsmith) * 6454: SQL lexical error * 6455: Assertion error in rel_apply_rewrite (sqlsmith) * 6456: NULL becomes 0 in outer join * 6459: Assertion error in exp_bin (sqlsmith) * 6462: large virtual memory spike on BLOB column select * 6465: appending to variables sized atom bats other than str bats with force flag may result in corrupted heap * 6467: date_to_str formatter is wrong * 6470: mitosis gets in the way of simple select * 6471: calls to sys.generate_series should auto-convert arguments * 6472: Assertion failure in rel_rename (Sqlsmith) * 6477: assertion eror rel_push_project_up (sqlsmith) * 6478: Crash with nested order by/ limit offset * 6479: Mserver receives an assertion error on a procedure call * 6480: Segfault in mvc_find_subexp (sqlsmith)

1 0

Excessive slowness in 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. 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