- users-list - monetdb.org

PHP insert performance
by - - 16 Jul '20

16 Jul '20

I have run the series of INSERT tests in PHP for monetdb and SAP SQLAnywhere. The tests show: performance of the monetdb on INSERT operations is almost twice slower against SQLA with the equal conditions (DDL, index, data, autocommit, 10K records), monetdb is in in-memory mode . What can be a reason? p.s. It is not benchmark, I just used the SQLA test server already running. -- - -

4 12

News on MonetDB
by Martin Kersten 14 Jul '20

14 Jul '20

Hi, In recent weeks we have issued a few blog posts on the MonetDB site and announced them on Twitter. The latest one is a demonstration of MonetDB as a drop in library in Python. see https://monetdb.org/blog/taking-MonetDBe-for-a-taxi-ride Feedback on the embedded version of MonetDB is always welcome. regards, Martin Kersten

1 0

Error with concurrent inserts via PymonetDB mapi
by Ioannis Foufoulas 13 Jul '20

13 Jul '20

Hi, I have a weird error when I am sending the following queries in parallel from 2 different python clients with MAPI: insert into table1 select numpy_count(c1) as c1 from view; insert into table2 select numpy_count(c1) as c1 from view2; where numpy_count is: CREATE or replace AGGREGATE numpy_count(val INTEGER) RETURNS INTEGER LANGUAGE PYTHON { import time time.sleep(3) return val.size }; The error is: pymonetdb.exceptions.OperationalError: GDK reported error: GDKreleasesem: semctl failed: Invalid argument However, if I run these 2 queries in parallel from 2 instances of mclient they work and AFTER doing this, they also work from python’s client with mapi. Is there any hint on this? Best, Yannis

2 2

mserver5 options
by - - 12 Jul '20

12 Jul '20

Hi, How to pass/set mserver5 options via monetdb(d) ? I’m trying to get started monetdb in in-memory mode. mserver5 has option --in-memory Run database in-memory only however all attempts to pass it were unsuccessful. -- - -

2 4

Many aggregated columns / using custom aggregation function for pivot
by Daniel Glöckner 10 Jul '20

10 Jul '20

Hi, today I checked out how to implement custom aggregation functions in Python. Works pretty neat and scales well when you have few columns which you want to aggregate, however I have many :) Imagine that the following aggregate functions has variants which perform a similar iteration but call a different function (not sum). The call to numpy.unique() and the iteration would have to be performed for each aggregated column which is bad in terms of performance. Background: I have records in a key / value like structure so there's a key column and a value column. I want to group by primary key and then create a column for each key. It's like pivot but without aggregation. Any ideas how to do this with SQL or custom functions in MonetDB? Kind regards, Daniel CREATE AGGREGATE python_aggregate(val INTEGER) RETURNS INTEGER LANGUAGE PYTHON { try: unique = numpy.unique(aggr_group) x = numpy.zeros(shape=(unique.size)) for i in range(0, unique.size): x[i] = numpy.sum(val[aggr_group==unique[i]]) except NameError: # aggr_group doesn't exist. no groups, aggregate on all data x = numpy.sum(val) return(x) };

1 0

Killing long running SQL queries
by Daniel Glöckner 09 Jul '20

09 Jul '20

Hi, sometimes things just go wrong ;) Is there a way to 1) list (long) running queries and 2) kill them? Kind regards, Daniel

2 1

Re: Re: Bit of documentation lacking (indexes, performance)
by gloeckner.daniel＠gmail.com 09 Jul '20

09 Jul '20

Hi, Thanks Martin Kersten, Alex Ossipov and Martin van Dinther for the valuable advice! It’s actually quite impressive to see how well MonetDB being a “knobless” system performs without any performance tuning. That’s what you do most of your day when using “big data” technologies from my experience 😉 but it’s of course not what you’re ultimately aiming to do… I think it could be considered as a unique selling point for MonetDB. Maybe the website should highlight this more and improve new user’s confidence level (indexes are not missing features but tuned automatically (how?)…., etc.). I’ll give PKs and FKs a shot to see how they affect performance. General advice, hire a working student and have her or him revamp the website 😃 There’s quite a bit of information but sometimes outdated and hard to navigate. My journey into MonetDB land continues, no blockers so far. Kind regards, Daniel

2 1

Bit of documentation lacking (indexes, performance)
by Daniel Glöckner 08 Jul '20

08 Jul '20

Hi, I'm currently in the process of evaluating MonetDB. Hats off for this great product! Very impressed by ease-of-use and performance so far. Looking for information what indexes are used by MonetDB I found a pretty empty page only: https://www.monetdb.org/Documentation/SQLReference/Indexes During our performance evaluation I was searching for common recipes to achieve good performance. Following page is unfortunately also under construction. https://www.monetdb.org/Documentation/SQLReference/PerformanceOptimization/… Is there any performance handbook available? I also did not find information regarding (performance) parameters for the DB server. Kind regards, Daniel

4 3

Huge merovingian.log after update
by Lynn Carol Johnson 07 Jul '20

07 Jul '20

HI all – A week or so ago we ran an upgrade to the lastest monetdb June-2020 patch. Our db was 2 patches old. While the log showed no specific problems, the upgrade effected our databases in that 2 of them would crash when they were accessed, and the third one (the largest one) I could access but could not see any tables. I ended up “destroying” all the dbs and recreating them fomr the saved binary files. Now, we are getting disk full messages (the db is only read – no one is adding to it). I look at the file sizes and there is a huge mervingian.log file: Is it a problem if I delete this file? What will it hurt? Should I “tail” it and save the end of it? Please advise. root@cbsudc01 dbfarmBL]# du -h --max-depth=1 1.3M ./testDB_lynn 52G ./maizeChrom10DB 955G ./maizeFullGenomeDB 1.3M ./testDB 6.9T . [lcj34@cbsudc01 dbfarmBL]$ l -ltr total 6320304952 -rw-rw---- 1 root buckler 339559112 Oct 9 2018 merovingian.log.old drwx------ 4 root root 121 Jun 26 13:54 testDB_lynn/ drwx------ 4 root root 121 Jul 1 17:23 maizeChrom10DB/ drwx------ 4 root root 121 Jul 1 17:23 testDB/ -rw-r--r-- 1 root root 6 Jul 1 17:23 merovingian.pid drwx------ 4 root root 4096 Jul 1 17:24 maizeFullGenomeDB/ -rw-r--r-- 1 root buckler 6471651893248 Jul 6 14:43 merovingian.log [lcj34@cbsudc01 dbfarmBL]$ Thanks - Lynn

3 5

inadvertantly last schema
by Lynn Carol Johnson 29 Jun '20

29 Jun '20

Hi all – I have a monetdb instance with a lot of tables and a lot of data. This data is organized into schemas. While logging on today as the admistrator, I inadvertently typed: sql>schema maizeFullGenome; syntax error, unexpected SCHEMA in: "schema" sql> Instead of “set schema maizeFullGenome;” This has apparently overwritten the data I had in my maizeFullGenome Schema. Is there anyway to get this back besides finding an old copy of the db? The following shows the schema as now gone: sql>set schema maizeFullGenome; operation successful sql>\d sql> Yes, a very stupid mistake. Lynn

2 5

Grant access to sql system catalog
by Suresh Devarakonda 29 Jun '20

29 Jun '20

Hi, I just wanted to check if there is a way to grant access to system tables and views such as sys.tables and sys.schemas to the users other than granting "sysadmin" role. I am not sure what kind of privileges the sysadmin role possess so would like to find if there are other roles or privileges available to access system tables and views. [cid:image001.jpg@01D64952.B2EE5CC0] Suresh Devarakonda 84.51<http://www.8451.com/>° TDO - Database Support Team D: 513.632.3692 M: 859.512.1839 A: 100 W. 5th Street, Cincinnati, Ohio 45202 Cincinnati | Chicago | New York | Portland Facebook<https://www.facebook.com/8451group/?ref=aymt_homepage_panel> LinkedIn<https://www.linkedin.com/company/84-51/?trk=biz-companies-cym> Instagram<https://www.instagram.com/84.51/> Twitter<https://twitter.com/8451group> Please consider the environment before printing this email.

2 1

Predicates in UDFs
by Oxana Dolmatova 25 Jun '20

25 Jun '20

Hello, Could you please explain why predicates with subqueries in UDFs do not work? For example, I have the following statements: create table itab (x int, y int); insert into itab values (1, 0); insert into itab values (2, 1); create function test1(n int) returns int begin while (n > 9) do set n = n -1; end while; return n; end; create function test2(n int) returns int begin while (n > (select 9 from itab) ) do set n = n -1; end while; return n; end; create function test3(n int) returns int begin while (exists (select x from itab where x < n)) do set n = n -1; end while; return n; end; The function test1 can be successfully created and executed, but functions test2 and test3 cannot be created and executed. Thank you and best regards, Oksana

2 3

Re: MonetDB Table Append
by Niels Nes 22 Jun '20

22 Jun '20

On Mon, Jun 22, 2020 at 08:11:26PM +0400, Miro Mannino wrote: > Dear Niels Nes, > > We would like to insert data column by column, for various reasons. > > The problem is that if we create a larger table with column T1 and > column T2, and insert 1000 rows on column T1, then the next 1000 insert > on T2 are going to be appended, resulting in 1000 rows of (val, NULL) > followed by another 1000 rows of (NULL, val). We could do that if there > is an option to INSERT INTO from the first row as well (ignoring > non-null values). > > In general, we are trying to use MonetDB taking advantage of a column > store DB, but we can't really insert a batch of values for one column > after another column due to the usual semantic of INSERT INTO that > works really in rows. > > Looking forward to hearing from you, Column by column inserts is indeed a problem with the sql language being row oriented. We do have some solution, ie binary copy into, but that maybe a bit too much for you. See https://www.monetdb.org/Documentation/Cookbooks/SQLrecipes/BinaryBulkLoad Niels > > Miro > > > > On Mon, Jun 22, 2020 at 8:02 PM Niels Nes <Niels.Nes(a)cwi.nl> wrote: > > Min > > Why don't you just create one larger table? > > Niels > > On 22 June 2020 14:59:44 CEST, Min Kyu Jung <mkj280(a)nyu.edu> wrote: > > To whom this may concern, > > I am Min, and I am currently utilizing MonetDB for a project > and I was wondering if there are any optimizations made in > MonetDB such that two tables with the same number of rows can > be joined together? > For example, consider the following two table: > CREATE TABLE t1(rowNumber, val); > WITH SERIES AS( SELECT * FROM generate_series(0, 10000)) > INSERT INTO t1 SELECT ROW_NUMBER() over (), rand() FROM series; > CREATE TABLE t2(rowNumber, val); > WITH SERIES AS( SELECT * FROM generate_series(0, 10000)) > INSERT INTO t2 SELECT ROW_NUMBER() over (), rand() FROM series; > > In this situation, I currently join the table as follows: > CREATE TABLE t3(row#, val1, val2); > INSERT INTO t3 SELECT ROW_NUMBER(), t1.val, t2.val FROM t1 > INNER JOIN t2 on t1.rowNumber = t2.rowNumber; > > Given that MonetDB is column-stored, I was wondering if there > is a simpler/faster/more optimized way of stitching the two > tables into one table. > > To be clearer, as of right now, I make sure all tables have the > rowNumber column. However, is there a way where I can join > multiple tables (all with the same number of rows) without > pre-generating rowNumber column for each table? > An example is this: > INSERT INTO t3 SELECT t1.val, t2.val FROM t1 INNER JOIN t2 on > row_number(t1) = row_number(t2); > > Sincerely, > Min > -- Niels Nes, CTO, MonetDB Solutions url: https://www.monetdbsolutions.com e-mail: Niels.Nes(a)monetdbsolutions.com

2 1

MonetDB Table Append
by Min Kyu Jung 22 Jun '20

22 Jun '20

To whom this may concern, I am Min, and I am currently utilizing MonetDB for a project and I was wondering if there are any optimizations made in MonetDB such that two tables with the same number of rows can be joined together? For example, consider the following two table: CREATE TABLE t1(rowNumber, val); WITH SERIES AS( SELECT * FROM generate_series(0, 10000)) INSERT INTO t1 SELECT ROW_NUMBER() over (), rand() FROM series; CREATE TABLE t2(rowNumber, val); WITH SERIES AS( SELECT * FROM generate_series(0, 10000)) INSERT INTO t2 SELECT ROW_NUMBER() over (), rand() FROM series; In this situation, I currently join the table as follows: CREATE TABLE t3(row#, val1, val2); INSERT INTO t3 SELECT ROW_NUMBER(), t1.val, t2.val FROM t1 INNER JOIN t2 on t1.rowNumber = t2.rowNumber; Given that MonetDB is column-stored, I was wondering if there is a simpler/faster/more optimized way of stitching the two tables into one table. To be clearer, as of right now, I make sure all tables have the rowNumber column. However, is there a way where I can join multiple tables (all with the same number of rows) without pre-generating rowNumber column for each table? An example is this: INSERT INTO t3 SELECT t1.val, t2.val FROM t1 INNER JOIN t2 on row_number(t1) = row_number(t2); Sincerely, Min

2 1

Announcement: New Jun2020 Feature release of MonetDB suite
by Sjoerd Mullender 03 Jun '20

03 Jun '20

The MonetDB team at MonetDB BV is pleased to announce the Jun2020 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://www.monetdb.org/downloads/>. Jun 2020 feature release (11.37.7) MonetDB5 Server * The mserver5 option --verbose (-v) was removed. A similar effect can be had by issuing the query CALL logging.setcomplevel('SQL_TRANS', 'INFO'); as the monetdb user. * The functions algebra.intersect, algebra.semijoin, and algebra.crossproduct have an extra argument, bool max_one, which indicates that there must be no more than one match in the join. * The example module opt_sql_append is not installed in the binary packages anymore. * Added mserver5 option (--dbtrace=<path>) in order to be able to specify the output file any produced traces. * Removed function bat.setKey(). * algebra.rangejoin now has two extra arguments: anti:bit and symmetric:bit. * Added session identifier, number of workers and memory claim to the sysmon queue. * The worker (number of threads), memory (in MB) and optimizer pipeline limits can now be set per user session basis. The query and session timeouts are now set in seconds. * With required privileges an user can set resource limits for a session. * There are now versions of group.(sub)group(done) that produce a single output containing just the groups. * algebra.join and algebra.leftjoin now have forms which return a single column. The column that is returned is the left column of the two column version. * The MAL profiler now assigns the SQL TRACE output to the client record thereby avoiding the interaction with other queries, but loosing insight of competing queries. The stethoscope should be used for that. SELinux Support * There was a problem with the MonetDB SELinux support on Fedora 32. That is fixed in this release. In order to do a proper upgrade of the package if you have already installed MonetDB-selinux on Fedora 32, you may need to uninstall (dnf remove) the old package and then install the new. Whole System * A new system to deal with debug output has been implemented. There is now an option --dbtrace to mserver5 that takes a file argument to which debug output is written. The default value is the file mdbtrace.log inside the database directory. This option can also be set through the monetdb program. * The home directory of the automatically created monetdb user was changed from /var/MonetDB to /var/lib/monetdb (RPM based systems only). This home directory is (currently) not used for anything, though. * Python 2 support has been removed. There is now only support for using Python 3. * Removed support for bam and sam files. * Added mserver5 option (--set raw_strings=true|false) and monetdb database property (raw_strings=yes|no) to control interpretation of strings. Merovingian * On Fedora and RHEL systems (not RHEL 6), if monetdbd runs under systemd, when the package is updated, monetdbd (and hence any mserver5 process it runs) is restarted. * Added dbtrace mserver5 option to the daemon in order to set mserver5's output directory for the produced traces. Client Package * The monetdb-client-tools (Debian/Ubuntu) and MonetDB-client-tools (Fedora/RH) containing the stethoscope, tachograph, and tomograph has been removed. A completely new version of stethoscope will be released to replace the old version. * Removed the possibility of using the MD5 checksum for authentication purposes. It was never actively used but was there as an option. Now the option has been removed. * Add port and host as fields in the .monetdb file. * Added 'sessionid' column to system function sys.queue(), so each query gets tagged with the current session identifier * Allow monetdb user to control session and query time out and selectively stopping a client sessions with a soft termination request. ODBC Driver * The NUMERIC and FLOAT types are now handled fully. Before only DECIMAL, FLOAT, and DOUBLE were handled fully. * Some bugs were fixed in the passing back and forth between application and server of values of type GUID (UUID). MonetDB Common * The functions BATintersect, BATsemijoin, and BATsubcross have an extra argument, bool max_one, which indicates that there must be no more than one match in the join. * The "unique" property on BATs was removed. The property indicated that all values in a BAT *had* to be distinct, but this was not actually used. * A new type of candidate list has been introduced. Candidate lists are used internally to specify which rows of a column participate in an operation. Before, candidate lists always contained a list of candidate row IDs. The new candidate list type specifies a list of row IDs that should NOT be considered (negative candidates). * The maximum number of BATs in the system has been increased for 64 bit architectures. * The hash tables used internally by the system now uses a technique based on Linear Hashing which allows them to grow gracefully. This means that hash tables aren't removed and recreated nearly as often anymore. This also meant that the hash table had to be split into two files, which means that after an upgrade the hash tables have to be recreated. * Implemented a version of BATproject, called BATproject2, with two "right" arguments which conceptually follow each other. * Removed MT_mmap and MT_munmap from the list of exported functions. Use GDKmmap and GDKmunmap with the same parameters instead. * Changed the interface of the atom "fix" and "unfix" functions. They now return a value of type gdk_return to indicate success/failure. * Removed the tunique property. The tunique property indicated that all values in the column had to be distinct. It was removed because it wasn't used. * BATrangeselect now has two extra arguments: anti and symmetric (both bool). SQL Frontend * Updating the value of a sequence now requires privilege on its own schema. * The sys.querylog_enable(threshold integer) now actually enables the querylog and uses a threshold in milliseconds. * Removed UNION JOIN statements. They were dropped by the SQL:2003 standard, plus MonetDB implementation was not fully compliant. * The OFFSET value in the COPY INTO query now counts uninterpreted newlines. Before it counted "unquoted record separators" which meant that if you had a single quote on a line that you want to skip, you could not use the feature. * Added support for FROM RANGE MINVALUE TO RANGE MAXVALUE and FROM RANGE MINVALUE TO RANGE MAXVALUE WITH NULL VALUES cases in partitioned tables by range (before they weren't). * Removed support for Python 2. Python 2 itself is no longer supported. Use Python 3 instead. Functions that were declared as LANGUAGE PYTHON2 or LANGUAGE PYTHON2_MAP are changed to LANGUAGE PYTHON and LANGUAGE PYTHON_MAP respectively (without changing the Python code). * Implemented 'covar_pop' and 'covar_samp' aggregate functions, as well as their window function counterparts. Implemented 'stddev_samp', 'stddev_pop', 'var_samp', 'var_pop', 'corr' and 'group_concat' window function correspondents. * Extended SQL catalog with CREATE WINDOW syntax for user-defined SQL:2003 window functions. At the moment, window functions must be defined on the backend engine, i.e. on this case MAL. In the current implementation, the backend code generation creates two additional columns of type lng with the start and end offsets for each row. * Added prepared_statements_args view, which details the arguments for the prepared statements created in the current session. * Added sys.prepared_statements view, which lists the available prepared statements in the current session. * Added deallocate statements with the syntax 'DEALLOCATE [PREPARE] { number | ALL }', to close an existing prepared statement or all, through the SQL layer. Previously this feature was available via MAPI exclusively with the "release" command. * Added support for raw strings using the syntax r'' or R''. This means that C-like escapes will remain uninterpreted within those strings. For instance SELECT r'\"' returns a string of length two. The user needs to escape single quotes by doubling them: SELECT r''''. * Implemented ROLLUP, CUBE and GROUPING SETS from SQL:1999. They define grouping subsets used with the GROUP BY clause in order to compute partial groupings. Also, the GROUPING aggregate was added. This aggregate is a bitmask identifying the grouping columns not present in the generated grouping row when used with the operators described above. * Updated user session procedures by adding the possibility to set properties based on a session identifier. Optimizer pipeline: sys.setoptimizer(int, string) Number of worker threads: sys.setworkerlimit(int, int) Memory limits (in MB): sys.setmemorylimit(int, int) Query timeout (in ms): sys.setquerytimeout(int, int) Session timeout (in ms): sys.setsessiontimeout(int, int) The first argument corresponds to the id of the session to modify, and these procedures are bound to the monetdb user exclusively. The versions of the mentioned procedures with just the second argument were added as well, where the changes are reflected in the current user session, and therefore every user can call them. * The procedures sys.settimeout(bigint), sys.settimeout(bigint,bigint) and sys.session(bigint) are now deprecated. Instead use sys.setquerytimeout and sys.setsessiontimeout mentioned above. * Added SQL procedure sys.hot_snapshot() which can be used to write a snapshot of the database to a tar file. For example, sys.hot_snapshot('/tmp/snapshot.tar'). If compression support is compiled in, snapshots can also be compressed ('/tmp/snapshot.tar.gz'). The tar file expands to a single directory with the same name as the database that was snapshotted. This directory can be passed directly as the --dbpath argument of mserver5 or it can be copied into an existing dbfarm and started from monetdbd. Bug Fixes * 6298: unexpectedly slow execution of SELECT length(fieldname) FROM tablename LIMIT 1 queries * 6401: Suspected memory leak in mserver5 when creating/dropping tables * 6687: Count distinct very slow and use too much the hard drive * 6723: columns aliases duplicates should not be allowed. automatic aliasing required. * 6731: Add system view to allow querying of available prepared statements and their parameters * 6732: Add SQL command to close a specific prepared statement * 6750: Executing a query on a non-existing column on a remote table crashes the remote server * 6785: function sys.isaURL(url) should have been declared as sys.isaURL(string) * 6808: reveal the alarm.sleep procedure in SQL * 6813: function not_uniques(bigint) returns error when called * 6818: usage of multiple column expressions in where-clause (f(a), f(b)) in (select a, b) causes assertion failure on mserver5 * 6821: Failed to start monetdb with embedded python * 6828: Server crashes when executing a window query with ordering by EXTRACT date * 6846: Global temporary table not accessible in other connections / sessions * 6847: A simple way of speeding up impscheck for dense canditers * 6850: Idle timestamp not set * 6851: json parser doesn't parse integers correctly * 6863: thash files not released upon drop table * 6864: (I)LIKE with multiple % doen't find matches

1 0

Crashed Database
by Ariel Abadi 20 May '20

20 May '20

Hi! I have deployed several MonetDB databases on different customers, the database is excellent haven't had that much problems, but I'm starting feeling that the database is becoming "fragile" Yesterday I have 2 crashes... Firstly, a database of 2.4TB of data... Monet crashed... and the only information I have received was: ERR cli_mx[28693]: #main thread:!ERROR: BBPcheckbats: file /opt/monetdb/cli_mx/cli_mx/bat/52/5236.tail too small (expected 137803304, actual 137166848) 2020-05-19 11:10:00 MSG cli_mx[28693]: !ERROR: BBPcheckbats: file /opt/monetdb/cli_mx/cli_mx/bat/52/5236.tail too small (expected 137803304, actual 137166848) 2020-05-19 11:08:11 ERR control[28629]: (local): failed to fork mserver: database 'cli_mx' has crashed after starting, manual intervention needed, check monetdbd's logfile (merovingian.log) for details Obviously that info Ive got it from merovingian.log.... so, which is the manual intervention needed... dont know! We run a backup all the nights (we rsync the database farm against backup server). Dont know the reason, but it seems that at moment of the backup, eventhought the database was working fine... The backup was done. So when I received the "crashed" notice.. I tried to recover from the backup.... but it seems the copy was already failing. So I had to start from zero... uploading all the information again. it was a very tought job, once I completed (at 1am in the morning), I stopped the db to do backup.... Again... the database has crashed!!!! never again started!!!! In this case the error was the following: 2020-05-19 23:57:52 MSG merovingian[2379]: sending process 2389 (database 'cli_mx') the TERM signal 2020-05-19 23:57:53 MSG merovingian[2379]: database 'cli_mx' has shut down 2020-05-19 23:57:53 MSG control[2379]: (local): stopped database 'cli_mx' 2020-05-19 23:57:54 MSG merovingian[2379]: database 'cli_mx' (2389) has exited with exit status 0 So the 3rd time, needed to upload the information again.. I have just finished!... Now, obviously I am scared to stop and start the db... and till this moment i dont have a backup of the farm directory. Can someone please help me to find the problem why the database has crashed? The only thing that I found on the /var/log/messages was the following (may be this may help to find the issue) May 19 23:03:49 mx-pve kernel: [4716718.867312] mserver5[19453]: segfault at 138 ip 00007fad7b66b1e9 sp 00007ffe648e5600 error 6 in lib_sql.so[7fad7b532000+1bc000] May 19 23:10:03 mx-monet kernel: [4717092.595561] mserver5[22348]: segfault at 138 ip 00007f07572d41e9 sp 00007ffd9f5aa950 error 6 in lib_sql.so[7f075719b000+1bc000] May 19 23:15:03 mx-monet kernel: [4717392.674941] mserver5[24044]: segfault at 138 ip 00007f4e7c0041e9 sp 00007ffeab6e8e90 error 6 in lib_sql.so[7f4e7becb000+1bc000] May 19 23:16:57 mx-monet kernel: [4717506.863046] mserver5[24675]: segfault at 138 ip 00007f8710d891e9 sp 00007ffc71a0e4a0 error 6 in lib_sql.so[7f8710c50000+1bc000] Do you have an idea what can I do ???! The version Im using is: v11.35.19 (Nov2019-SP3) on a Linux Centos 7. Ariel

1 0

ILIKE Operator. Bad function?
by Ariel Abadi 20 May '20

20 May '20

Hi! May be some can help me. We are using the ILIKE operator to filter data. When we use a couple of "%" at a certain point the db stops filtering correctly. We started with *ILIKE '%guate%' *it found 7 records, then we changed to *ILIKE '%guate%**com% *it found 1 record (which is ok)... finally we changed to *ILIKE '%guate%mo%**com%**' *and now it didn't find anything :'( Please see the attached examples... *A*) sql>SELECT DISTINCT nts_area_name FROM sb_rmsv.nts_nt_sum_report WHERE nts_area_name *ILIKE '%guate%'*; +---------------------------+ | nts_area_name | +===========================+ | Guatemala | | Guatemala-Mobile | | Guatemala-Mobile Comcel | | Guatemala-Mobile Movistar | | Guatemala-Mobile PCS | | Guatemala-Telgua | | Guatemala-Telefonica | +---------------------------+ *B*) sql>SELECT DISTINCT nts_area_name FROM sb_rmsv.nts_nt_sum_report WHERE nts_area_name *ILIKE '%guate%**com%;* +-------------------------+ | nts_area_name | +=========================+ | Guatemala-Mobile Comcel | +-------------------------+ *C*) sql>SELECT DISTINCT nts_area_name FROM sb_rmsv.nts_nt_sum_report WHERE nts_area_name *ILIKE '%guate%mo%**com%**'*; +---------------+ | nts_area_name | +===============+ +---------------+ 0 tuples

4 6

Auto-scaling MonetDB cluster on AWS?
by Pascal Heus 18 May '20

18 May '20

Has anyone configured an scalable MonetDB using Docker cluster on Amazon Web Services or other cloud environment? I'm looking into setting this up for one of our project around COBID-19 and essentially need to: - have my number of MonetDB instances to go up and down with user demand - use a load balancer to spread user queries across multiple instances I had in mind that all the instances would just point to a single dbfarm directory (all the user queries are read only), but is this an option or will cause problems (locks, caching, etc.)? Based on what I understand from Cluster Management (https://www.monetdb.org/Documentation/Cookbooks/SQLrecipes/ClusterManagement) or Lazy Replication (https://www.monetdb.org/Documentation/Cookbooks/SQLrecipes/WorkloadCaptureR…) I need to replicate the database for each instance, which is not really practical. This document seems to hint that using the "Replication Services" model would be an option, but then I end up with any copies of the same database, and this will require a significant amount of extra disk space (the transaction logs + one copy per instance). http://www.exanest.eu/pub/2016_RTPBD_Monetdb.pdf Any guidance, suggestion, example would be greatly appreciated. best *P

2 1

MonetDB Cluster size
by 逄振洲 11 May '20

11 May '20

i am very interested in using MonetDB as OLAP solution，But now I have a few questions。 1. MonetDB cluster the largest scale ? 2. any customer case study ?

1 0

Integer divided by integer never produces a real number
by Groot, Frank (Sociale Verzekeringsbank) 23 Apr '20

23 Apr '20

Hi, Can you tell me which of the following division outcomes is expected behaviour? --Division -- outcome select 10/3, -- 3 10/3.0, -- 3.333 10.0/3, -- 3.333 cast(10 as decimal(18,2))/3, -- 3.333 cast(10 as decimal(18,0))/3, -- 3 cast(10 as decimal(18,0))/3.0 -- 3.333 ; Personally, I would have expected all of the outcomes to be real numbers and not integers. Can you tell me if that is a realistic expectation? Kind regards, Frank Groot

3 4

conditional DDL statements
by Roberto Cornacchia 22 Apr '20

22 Apr '20

Hi, I am afraid I know the answer to this already ("not supported"), but I wanted to check with you guys. Perhaps someone has smart workaround? Essentially what I would like to do is something like: IF condition THEN CREATE FUNCTION ....; As far as I know, flow control statements are only allowed inside functions/procedures. However, CREATE statements are not allowed inside functions/procedure. So I don't see any way to do what I wrote above. Notice, I am aware of CREATE OR REPLACE. What I'm looking for is an arbitrary condition test. Any trick? And/or, any plan to implement something in this direction? Roberto

4 6

Integer divided by integer never produces a real number
by Groot, Frank (Sociale Verzekeringsbank) 22 Apr '20

22 Apr '20

Hi, Can you tell me which of the following division outcomes is expected behaviour? --Division -- outcome select 10/3, -- 3 10/3.0, -- 3.333 10.0/3, -- 3.333 cast(10 as decimal(18,2))/3, -- 3.333 cast(10 as decimal(18,0))/3, -- 3 cast(10 as decimal(18,0))/3.0 -- 3.333 ; Personally, I would have expected all of the outcomes to be real numbers and not integers. Can you tell me if that is a realistic expectation? Kind regards, Frank Groot

1 0

Compilation error macOS Catalina
by Ying Zhang 16 Apr '20

16 Apr '20

Hai all, Compiling MonetDB Mercurial source on macOS Catalina (10.15.4) results in the following error: /Users/jennie/monet/Nov2019/common/utils/mcrypt.c:476:3: error: 'CC_MD5_Final' is deprecated: first deprecated in macOS 10.15 - This function is cryptographically broken and should not be used in security contexts. Clients should migrate to SHA256 (or stronger). [-Werror,-Wdeprecated-declarations] MD5_Final(md, &c); ^ /Applications/Xcode.app/Contents/Developer/Platforms/MacOSX.platform/Developer/SDKs/MacOSX.sdk/usr/include/CommonCrypto/CommonDigest.h:282:37: note: expanded from macro 'MD5_Final' #define MD5_Final CC_MD5_Final ^ /Applications/Xcode.app/Contents/Developer/Platforms/MacOSX.platform/Developer/SDKs/MacOSX.sdk/usr/include/CommonCrypto/CommonDigest.h:139:12: note: 'CC_MD5_Final' has been explicitly marked deprecated here extern int CC_MD5_Final(unsigned char *md, CC_MD5_CTX *c) ^ 7 errors generated. How can I get rid of this error? Disable -Wdeprecated-declarations? Or disable MD5 but use SHA256 instead? Thanks! Jennie

2 1

sql.init() is gone?
by Torsten Grust 14 Apr '20

14 Apr '20

Dear MonetDB friends, I am about to start the next semester of my DB implementation course here at U Tübingen. The course peeks at the internals of a variety of DBMSs, PostgreSQL and MonetDB5 in particular. The discussion in that course relies on being able to use the functions in the MAL 'sql' module directly such that students can understand how SQL queries are compiled into sequences of MAL statements. MAL scripts that have worked perfectly for this in the past now already stop at the initial sql.init() command: $ mclient -d scratch -l mal Welcome to mclient, the MonetDB interactive terminal (Nov2019-SP3) [...] mal>sql.init(); MAPI = (monetdb) /tmp/.s.monetdb.50000 QUERY = sql.init(); ERROR = !TypeException:user.main[1]:'sql.init' undefined in: sql.init(); I am running version 11.35.19 (Nov2019-SP3). Have the magic incantations that talk to SQL from MAL changed? I'd appreciate any help here. Thanks, —Torsten -- | Prof. Dr. Torsten Grust | Database Systems — Universität Tübingen (Germany) | ✉︎ torsten.grust(a)uni-tuebingen.de | db.inf.uni-tuebingen.de

2 2

SQL script autoload
by Roberto Cornacchia 14 Apr '20

14 Apr '20

Hi, Is there something similar to the lib/autoload folder, but for SQL scripts? My use case is: I have a bunch of UDFs. A SQL script defines their SQL interfaces The script is in lib/createdb. Sometimes I make changes to those UDFs, and their interfaces need to be updated. But my the script is only run on DB creation, so I have to manually go thorough all existing databases and run the script. The script is in a way idempotent, because it uses CREATE OR REPLACE, so I don't mind running it every time the database is started. But I can't find a way to do that.

2 1