- users-list - monetdb.org

MonetDB-.Net
by Mitchell Weggemans 11 Feb '21

11 Feb '21

Hey, MonetDB.Net is an entirely community driven project. Therefore, I cannot 100% guarantee it is production ready. I made some effort into testing it however, and it worked fine. If you encounter any problems, please feel free to post them here or on StackOverflow, or even to propose changes over on the GitHub repo. Regards, Mitchell.

4 4

MonetDB-.Net
by imad hajj chahine 09 Feb '21

09 Feb '21

Hi, I am currently using Windows Monetdb ODBC driver to connect from .Net to a running monetdb server. Recently I found the github repo https://github.com/MonetDB/MonetDB-.Net and do some initial testing and it's far better in error reporting and support connection pooling. How mature is this .Net connector and is it recommended for usage in production environments and replace the ODBC driver? Is it the official connector for .Net and backed by the Monetdb team? Many thanks.

1 0

ODBC driver Error on Windows
by imad hajj chahine 08 Feb '21

08 Feb '21

Hi, After upgrading to the Oct 2020-SP2 (11.39.11) version the ODBC driver started to throw the following exception: ERROR [IM003] Specified driver could not be loaded due to system error 126: The specified module could not be found. (MonetDB ODBC Driver, C:\Program Files\MonetDB\MonetDB ODBC Driver\lib\MonetODBC.dll) Rolling back the driver to the version june 2020-SP1(11.37.11.01) the driver is working fine. Previously some driver releases missed the 2 libraries "libeay32.dll" and "ssleay32.dll", and adding them to the ODBC driver installation folder solved the problem, but not this time. Is there any missing dlls in the installation or its a more complicated issue. Thanks. Regards

2 3

forward=redirect file descriptor leak
by Karl Seguin 28 Jan '21

28 Jan '21

It appears that when configured with forward=redirect, there's a leak for every connection. I've reproduced using the two latest releases: ./monetdbd create ./farm ./monetdbd set forward=redirect ./farm ./monetdbd set listenaddr=127.0.0.1 ./farm ./monetdbd start ./farm ./monetdb create test ./monetdb release test lsof -p $(pidof monetdbd) | wc -l 40 ./mclient -d test -s "select 1" lsof -p $(pidof monetdbd) | wc -l 41 ./mclient -d test -s "select 1" lsof -p $(pidof monetdbd) | wc -l 42 ./mclient -d test -s "select 1" lsof -p $(pidof monetdbd) | wc -l 43 Karl

2 1

Announcement: New Oct2020-SP2 Bugfix release of MonetDB suite
by Sjoerd Mullender 25 Jan '21

25 Jan '21

The MonetDB team at MonetDB BV is pleased to announce the Oct2020-SP2 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://www.monetdb.org/downloads/>. Oct2020-SP2 Bugfix Release (11.39.11) SQL Frontend * CREATE [OR REPLACE] TRIGGER schema_name.trigger_name is now disallowed, because the trigger will be stored on the same schema as the table it refers to. Use a schema-qualified on the table reference (ie ON clause) when necessary. Bug Fixes * 6862: mserver5: crashes under update_table() when calling lib_sql.so ( max_clients = 2048) * 7002: monetdb stop fails * 7012: mclient enters an infinite loop when a file on the command line does not exist * 7013: Select * on grouped view: wrong error "cannot use non GROUP BY column 'a1' in query results without an aggregate function" * 7017: mal seems to leak in functions * 7020: release an older savepoint causes "BATproject2: does not match always" * 7021: savepoints crash mserver5 * 7022: transaction with an unreleased savepoint not properly persisted * 7023: CREATE VIEW: SELECT: cannot use non GROUP BY column '%1' in query results without an aggregate function * 7024: DELETE FROM or TRUNCATE on freshly created table leads to loosing all further inserts in same transaction * 7030: DROP TABLE with AUTO_INCREMENT doesn't drop sequence causing left-over dependency * 7034: User with sysadmin role cannot create another user * 7035: UPDATE and SELECT column privileges * 3772: Any user can grant a role.

1 0

Re: MonetDB-PHP Error on parameterized query with timestamp
by Kevork Vartanian 09 Dec '20

09 Dec '20

Thank you Ying and everyone involved. Regards, Kevork Vartanian ________________________________ From: users-list <users-list-bounces+kev=innotta.com.au(a)monetdb.org> on behalf of users-list-request(a)monetdb.org <users-list-request(a)monetdb.org> Sent: Thursday, 10 December 2020 1:56 AM To: users-list(a)monetdb.org <users-list(a)monetdb.org> Subject: users-list Digest, Vol 100, Issue 5 Send users-list mailing list submissions to users-list(a)monetdb.org To subscribe or unsubscribe via the World Wide Web, visit https://www.monetdb.org/mailman/listinfo/users-list or, via email, send a message with subject or body 'help' to users-list-request(a)monetdb.org You can reach the person managing the list at users-list-owner(a)monetdb.org When replying, please edit your Subject line so it is more specific than "Re: Contents of users-list digest..." Today's Topics: 1. Re: MonetDB-PHP Error on parameterized query with timestamp (Ying Zhang) ---------------------------------------------------------------------- Message: 1 Date: Wed, 9 Dec 2020 15:56:28 +0100 From: Ying Zhang <Y.Zhang(a)cwi.nl> To: Communication channel for MonetDB users <users-list(a)monetdb.org> Subject: Re: MonetDB-PHP Error on parameterized query with timestamp Message-ID: <6F309253-9D88-4E52-B079-2F2CBDCF1B81(a)cwi.nl> Content-Type: text/plain; charset="us-ascii" FYI, this error has really been resolved. Deployed a fix under version 1.1.3., for more info see Stack Overflow: - https://stackoverflow.com/questions/65077831/monetdb-php-error-on-parameter… <https://stackoverflow.com/questions/65077831/monetdb-php-error-on-parameter…> Updated the user doc: - https://github.com/MonetDB/MonetDB-PHP#example-3-parameterized-query-with-p… <https://github.com/MonetDB/MonetDB-PHP#example-3-parameterized-query-with-p…> And the protocol doc: - https://github.com/MonetDB/MonetDB-PHP/blob/master/protocol_doc/README.md#7… <https://github.com/MonetDB/MonetDB-PHP/blob/master/protocol_doc/README.md#7…> > On 8 Dec 2020, at 10:45, Ying Zhang <Y.Zhang(a)cwi.nl> wrote: > > Hai Kevork, > > Our MonetDB/PHP expert has answered this question in Stack Overflow: > > https://stackoverflow.com/questions/65077831/monetdb-php-error-on-parameter… <https://stackoverflow.com/questions/65077831/monetdb-php-error-on-parameter…> > > For users on this list, this is the Stack Overflow answer: > > the problem is that the "birth_date" field is not of a "date-time" type, but it's "date" only. So just remove "12:37:16" and it will work. The "char" type is converted to "date" by MonetDB itself, "date" doesn't have a specific literal in the SQL language. This works, I tested: > > $result = $connection->Query(<<<EOF > select > * > from > "cats" > where > "name" = ? > and "weight_kg" > ? > and "birth_date" < ? > limit > 10 > EOF > , [ "D'artagnan", 5.3, "2020-02-28" ]); > > Regards, > Jennie > >> On 7 Dec 2020, at 04:23, Kevork Vartanian <kev(a)innotta.com.au <mailto:kev@innotta.com.au>> wrote: >> >> Hi Sjoerd, >> >> Thank you for getting back to me. >> >> My colleague tested what you suggested and got the same error so it is still failing. He is certain he is passing the correct value as he is able to execute the same query in raw format, so he believed it has to do with the way params are implemented. I'm no expert but he said it looks like an error from C language. >> >> Please advise. >> >> Thank you. >> >> Regards, >> >> Kevork Vartanian >> >> From: users-list <users-list-bounces+kev=innotta.com.au(a)monetdb.org <mailto:users-list-bounces+kev=innotta.com.au@monetdb.org>> on behalf of users-list-request(a)monetdb.org <mailto:users-list-request@monetdb.org> <users-list-request(a)monetdb.org <mailto:users-list-request@monetdb.org>> >> Sent: Friday, 4 December 2020 10:00 PM >> To: users-list(a)monetdb.org <mailto:users-list@monetdb.org> <users-list(a)monetdb.org <mailto:users-list@monetdb.org>> >> Subject: users-list Digest, Vol 100, Issue 2 >> >> Send users-list mailing list submissions to >> users-list(a)monetdb.org <mailto:users-list@monetdb.org> >> >> To subscribe or unsubscribe via the World Wide Web, visit >> https://www.monetdb.org/mailman/listinfo/users-list <https://www.monetdb.org/mailman/listinfo/users-list> >> or, via email, send a message with subject or body 'help' to >> users-list-request(a)monetdb.org <mailto:users-list-request@monetdb.org> >> >> You can reach the person managing the list at >> users-list-owner(a)monetdb.org <mailto:users-list-owner@monetdb.org> >> >> When replying, please edit your Subject line so it is more specific >> than "Re: Contents of users-list digest..." >> >> >> Today's Topics: >> >> 1. MonetDB-PHP Error on parameterized query with timestamp >> (Kevork Vartanian) >> 2. Re: MonetDB-PHP Error on parameterized query with timestamp >> (Sjoerd Mullender) >> >> >> ---------------------------------------------------------------------- >> >> Message: 1 >> Date: Fri, 4 Dec 2020 00:11:59 +0000 >> From: "Kevork Vartanian" <kev(a)innotta.com.au <mailto:kev@innotta.com.au>> >> To: "users-list(a)monetdb.org <mailto:users-list@monetdb.org>" <users-list(a)monetdb.org <mailto:users-list@monetdb.org>> >> Subject: MonetDB-PHP Error on parameterized query with timestamp >> Message-ID: >> <SYBP282MB0346DC1924EC1C610E29428B81F10(a)SYBP282MB0346.AUSP282.PROD.OUTLOOK.COM <mailto:SYBP282MB0346DC1924EC1C610E29428B81F10@SYBP282MB0346.AUSP282.PROD.OUTLOOK.COM>> >> >> Content-Type: text/plain; charset="iso-8859-1" >> >> Hi, >> >> The below is from my colleague who is not a member on this list, so I'm sending on his behalf. >> >> I'm using MonetDB with MonetDB-PHP library. >> >> >> I have followed the documentation from: https://github.com/MonetDB/MonetDB-PHP <https://github.com/MonetDB/MonetDB-PHP> >> >> >> Everything is working fine, but if I set parameter for timestamp type column I get the following error: >> >> Error from MonetDB: 42000!Wrong type for argument 1 of function call: char, expected timestamp >> >> For example, this is working: >> >> $result = $connection->Query(' >> select >> * >> from >> "cats" >> where >> "name" = ? >> and "weight_kg" > ? >> limit >> 10', [ "D'artagnan", 5.3 ]); >> >> but this will not work: >> >> $result = $connection->Query(' >> select >> * >> from >> "cats" >> where >> "name" = ? >> and "weight_kg" > ? >> and "birthdate" < ? // timestamp data type >> limit >> 10 >> ', [ "D'artagnan", 5.3, "2020-02-28 12:37:16" ]); >> >> >> The error occurs for any column that is of timestamp type and only when passing value as parameter. >> >> >> What am I doing wrong? >> >> Thank you. >> >> >> Regards, >> >> Kevork Vartanian >> >> This email correspondence may contain privileged information and it is intended to be transmitted to the addressee abovementioned. If you are not the intended recipient of this email correspondence, you must not distribute, copy, forward or rely upon its contents. We ask that you notify the sender of this error and immediately delete this email correspondence. >>

1 0

Re: MonetDB-PHP Error on parameterized query with timestamp
by Kevork Vartanian 09 Dec '20

09 Dec '20

Hi Sjoerd, Thank you for getting back to me. My colleague tested what you suggested and got the same error so it is still failing. He is certain he is passing the correct value as he is able to execute the same query in raw format, so he believed it has to do with the way params are implemented. I'm no expert but he said it looks like an error from C language. Please advise. Thank you. Regards, Kevork Vartanian ________________________________ From: users-list <users-list-bounces+kev=innotta.com.au(a)monetdb.org> on behalf of users-list-request(a)monetdb.org <users-list-request(a)monetdb.org> Sent: Friday, 4 December 2020 10:00 PM To: users-list(a)monetdb.org <users-list(a)monetdb.org> Subject: users-list Digest, Vol 100, Issue 2 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-PHP Error on parameterized query with timestamp (Kevork Vartanian) 2. Re: MonetDB-PHP Error on parameterized query with timestamp (Sjoerd Mullender) ---------------------------------------------------------------------- Message: 1 Date: Fri, 4 Dec 2020 00:11:59 +0000 From: "Kevork Vartanian" <kev(a)innotta.com.au> To: "users-list(a)monetdb.org" <users-list(a)monetdb.org> Subject: MonetDB-PHP Error on parameterized query with timestamp Message-ID: <SYBP282MB0346DC1924EC1C610E29428B81F10(a)SYBP282MB0346.AUSP282.PROD.OUTLOOK.COM> Content-Type: text/plain; charset="iso-8859-1" Hi, The below is from my colleague who is not a member on this list, so I'm sending on his behalf. I'm using MonetDB with MonetDB-PHP library. I have followed the documentation from: https://github.com/MonetDB/MonetDB-PHP Everything is working fine, but if I set parameter for timestamp type column I get the following error: Error from MonetDB: 42000!Wrong type for argument 1 of function call: char, expected timestamp For example, this is working: $result = $connection->Query(' select * from "cats" where "name" = ? and "weight_kg" > ? limit 10', [ "D'artagnan", 5.3 ]); but this will not work: $result = $connection->Query(' select * from "cats" where "name" = ? and "weight_kg" > ? and "birthdate" < ? // timestamp data type limit 10 ', [ "D'artagnan", 5.3, "2020-02-28 12:37:16" ]); The error occurs for any column that is of timestamp type and only when passing value as parameter. What am I doing wrong? Thank you. Regards, Kevork Vartanian This email correspondence may contain privileged information and it is intended to be transmitted to the addressee abovementioned. If you are not the intended recipient of this email correspondence, you must not distribute, copy, forward or rely upon its contents. We ask that you notify the sender of this error and immediately delete this email correspondence.

2 2

MonetDB-PHP Error on parameterized query with timestamp
by Kevork Vartanian 04 Dec '20

04 Dec '20

Hi, The below is from my colleague who is not a member on this list, so I'm sending on his behalf. I'm using MonetDB with MonetDB-PHP library. I have followed the documentation from: https://github.com/MonetDB/MonetDB-PHP Everything is working fine, but if I set parameter for timestamp type column I get the following error: Error from MonetDB: 42000!Wrong type for argument 1 of function call: char, expected timestamp For example, this is working: $result = $connection->Query(' select * from "cats" where "name" = ? and "weight_kg" > ? limit 10', [ "D'artagnan", 5.3 ]); but this will not work: $result = $connection->Query(' select * from "cats" where "name" = ? and "weight_kg" > ? and "birthdate" < ? // timestamp data type limit 10 ', [ "D'artagnan", 5.3, "2020-02-28 12:37:16" ]); The error occurs for any column that is of timestamp type and only when passing value as parameter. What am I doing wrong? Thank you. Regards, Kevork Vartanian This email correspondence may contain privileged information and it is intended to be transmitted to the addressee abovementioned. If you are not the intended recipient of this email correspondence, you must not distribute, copy, forward or rely upon its contents. We ask that you notify the sender of this error and immediately delete this email correspondence.

2 1

MonetDB bug tracker has moved to github
by Sjoerd Mullender 01 Dec '20

01 Dec '20

The MonetDB bug tracker has been moved from our self-hosted bugzilla instance to github issues. All bug IDs have remained the same in the transition, making referencing bugs easier. The new bug tracker can be found at https://github.com/MonetDB/MonetDB/issues Don't forget to follow this tracker (if you're interested in doing that) or follow individual bugs. Due to the way we had to do the move, the old stars that you may have given to the MonetDB github repo have been lost, so please, star it again (or for the first time) if you feel so inclined. The old bugzilla tracker will remain on-line for the time being but entry of new bugs has been turned off. Shortcuts to bugs are forwarded to github. (Shortcuts are URLs in the form of https://www.monetdb.org/bugzilla/<bugid> and https://bugs.monetdb.org/<bugid>.) -- Sjoerd Mullender

1 0

MonetDB bug tracker is going to move
by Sjoerd Mullender 29 Nov '20

29 Nov '20

LS, The MonetDB team wishes to announce the end of our reliance on Bugzilla for bug tracking and enhancement requests. Instead, we switch over to the MonetDB Github issues repo (https://github.com/monetdb/monetdb) which is an hourly based mirror of the Mercurial repository managed at the development group. The move will take place on Friday, November 27, 2020. The actual transfer of the Bugzilla archive into the Github Issues may take a number of hours. A redirect will be in place to send people from Bugzilla towards the MonetDB GitHub repository instead. -- Sjoerd Mullender

1 1

Announcement: New Oct2020-SP1 Bugfix release of MonetDB suite
by Sjoerd Mullender 24 Nov '20

24 Nov '20

The MonetDB team at MonetDB BV is pleased to announce the Oct2020-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://www.monetdb.org/downloads/>. Oct 2020-SP1 bugfix release (11.39.7) Bug Fixes * 6890: Add support of xz/lzma (de)compression on MS Windows * 6891: Add support of lz4 (de)compression on MS Windows * 6971: Parsing table returning function on remote server fails * 6981: Oct2020: PREPARE DDL statement silently fails * 6983: monetdb allows to use non-existing optimizer pipe * 6998: MAL profiler buffer limitations * 7001: crossproduct generated for a simple (semi-)join * 7003: Segfault on large chain of constant decimal multiplication * 7005: Dropping a STREAM TABLE does not remove the associated column info from sys._columns * 7010: deallocate <id> results in all prepared statements being deallocated (not error-related) * 7011: uuid() called only once when used in projection list

1 0

Martin Kersten appointed Knight in the Royal Order of the Netherlands Lion and thanks for your support
by Ying Zhang 20 Nov '20

20 Nov '20

Dear list, Please allow me to proudly share with you the news that Martin Kersten, the founding father of MonetDB, has just been appointed Knight in the Royal Order of the Netherlands Lion [1] as a recognition for his 45+ years contribution to the research, innovation, education and development of database management systems, the results of which have been reflected in MonetDB. However, Martin and the MonetDB team have always been keenly aware that all achievements of Martin&MonetDB wouldn't have been possible without group efforts. So, taking this opportunity, the MonetDB team would like to thank all MonetDB users and contributors through the years for your support! Now, more than ever, the MonetDB team is motivated and determined to make MonetDB even better with every check-in 😀 Best regards, Jennie (a 15+ years MonetDB member and counting :)) [1] https://www.royal-house.nl/topics/decorations-and-honours/personal-decorati…

1 0

Need help using SQuirrel or another Windows SQL client
by marc＠scifinsys.com 19 Nov '20

19 Nov '20

Hi all -- Hopefully someone in this group can help me. :-) I am new to MonetDB and am having a problem setting up the connection for an SQL client. Part of me is wondering if the problem is related to using the latest version of MonetDB (Oct 2020) with this MonetDB JDBC driver (monetdb-jdbc-2.29.jre7.jar). I randomly chose SQuirrel on Windows and am open to alternatives. The connection I am trying to make is to MonetDB on Linux. I followed the instructions carefully at: https://www.monetdb.org/Documentation/ClientInterfaces/SQuirreL 1. From Linux, here is my connection. I have no problems logging in on the host using mclient. marc@HPZ620A: ip address inet 10.102.96.66/24 brd 10.102.96.255 scope global dynamic noprefixroute eno1 marc@HPZ620A:~/tmp/mydbfarm$ mclient -u monetdb -d voc password: Welcome to mclient, the MonetDB/SQL interactive terminal (Oct2020) Database: MonetDB v11.39.5 (Oct2020), 'mapi:monetdb://HPZ620A:50001/voc' sql> 2. Still on Linix, here is the Server Running. marc@HPZ620A: ps -auxw | grep 50001 marc 21727 0.2 0.2 2640864 92616 ? Ssl Nov13 2:50 /usr/bin/mserver5 --dbpath=/home/marc/tmp/mydbfarm/voc --set merovingian_uri mapi:monetdb://HPZ620A:50001/voc --set mapi_listenaddr none --set mapi_usock /home/marc/tmp/mydbfarm/voc/.mapi.sock --set monet_vault_key /home/marc/tmp/mydbfarm/voc/.vaultkey --set gdk_nr_threads 32 --set max_clients 64 --set sql_optimizer default_pipe 3. From Windows, I can ping that server. C:\Users\Admin>ping 10.102.96.66 Pinging 10.102.96.66 with 32 bytes of data: Reply from 10.102.96.66: bytes=32 time=59ms TTL=63 Reply from 10.102.96.66: bytes=32 time=55ms TTL=63 Reply from 10.102.96.66: bytes=32 time=58ms TTL=63 Reply from 10.102.96.66: bytes=32 time=58ms TTL=63 Ping statistics for 10.102.96.66: Packets: Sent = 4, Received = 4, Lost = 0 (0% loss), Approximate round trip times in milli-seconds: Minimum = 55ms, Maximum = 59ms, Average = 57ms 4. The Squirrel Setup. Note that I did download the jar file from MonetDB, per the instructions. 5. And finally.. The error message when I try to connect! Any suggestions on how I could solve this issue with SQuirrel or another Windows SQL client would be greatly appreciated. Thanks!! --- Marc

4 8

sub-second epoch precision
by Karl Seguin 17 Nov '20

17 Nov '20

I couldn't find a nice way to turn a timestamp into a sub-second epoch value. What I came up with was: sys.epoch(t) * 1000000 + cast((("second"(t) - floor("second"(t)))) * 1000000 as int) Most functions seem to treat seconds as the lowest unit, e.g. extract(seconds...) and time_to_str Is there a better option that I'm missing? Thanks, Karl

1 0

changes with temporal types
by Roberto Cornacchia 03 Nov '20

03 Nov '20

Hi team, I like the Oct2020 release very much. It has some discontinuities with past releases, and that's ok. One that I find a bit too painful though is the stricter time and interval typing. Not that I am against it, it is now more SQL compliant. But perhaps the transition could have been a bit softer. It boils down to date arithmetics between temporal and numeric types. Something like this was allowed before: 1 + (date1 - date2) (integer + interval) As the release notes say, a new function epoch_ms() has been added to help the transition. This function can turn dates and intervals into the number (int) of milliseconds since 1970-01-01 00:00:00 UTC. So if one really needs such arithmetics, could do something like 1 + epoch_ms(date1 - date2) The problem with this is that this function was not available in earlier releases. This makes the migration of existing applications very hard, because there is no combination that works in all cases: Deployed application Old MonetDB New MonetDB int + interval works fails int + cast(interval as int) works fails int + epoch_ms(interval) fails works As you see, there is no overlap between the solutions that worked with MonetDB < Oct2020 and those that work with MonetDB Oct2020. I wonder, would it have been such a bad idea to make the transition as it was done, but leave the ability to cast an interval to a number? At least for a couple of releases? We are now facing the issue that if we want to roll forward to MonetDB Oct2020, at the same time we need to make sure to redeploy our code to be compatible with it. There is so much that could go wrong with this! It's scary. I've nothing against breaking backward compatibility, but honestly I don't see the urgency in removing that cast, it would have helped a lot. Roberto Cornacchia +31 (0) 6 27 232 270 www.spinque.com

3 3

Github mirror
by Roberto Cornacchia 26 Oct '20

26 Oct '20

Hello, I was wondering, is the syncing of the github repository mirror not scheduled? I see that the latest commit on master is from today, but the latest commit on Oct2020 branch is from 14 days ago.

2 2

Announcement: New Oct2020 Feature release of MonetDB suite
by Sjoerd Mullender 19 Oct '20

19 Oct '20

The MonetDB team at MonetDB BV is pleased to announce the Oct2020 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/>. Oct 2020 feature release (11.39.5) MonetDB5 Server * The settings for specifying how mserver5 should listen to "The Internet" have been overhauled. See the manual for details. In brief, mapi_autosense, mapi_ipv6 and mapi_open are gone. If mapi_listenaddr equals "localhost" or "all", we listen to both IPv4 and IPv6 (if available), if "127.0.0.1" or "0.0.0.0", we listen to IPv4 only, if "::1" or "::" we listen to IPv6 only. The first of each pair is loopback interface only, the second is all interfaces. If mapi_listenaddr is "none", then no IP port is opened, you need to use a UNIX domain socket. If mapi_port is 0, we let the operating system choose a free port (like mapi_autosense). Default behavior has not changed. Whole System * Finished a first version of the new monitoring function user_statistics(), which is only intended for the DBAs. For each database user who has logged in during the current mserver5 session, it returns "username": login name of the database user, "querycount": the number of queries this user has executed since his/her first login, "totalticks": the total execution time (in microsecond) of the queries ran by this user, "maxquery": the query with the longest execution time (if two queries have the same execution time, the newer overwrites the older), "maxticks": the execution time of the 'maxquery' (in microsecond), "started": the start timestamp of the 'maxquery', "finished": the finish timestamp of the 'maxquery'. * Removed support for LiDAR data, that is the SQL procedures sys.lidarattach, sys.lidarload, and sys.lidarexport. * The sys.queue() has been turned into a circular buffer to allow for inspection of both active, paused and recently executed queries. Client Package * mclient and msqldump now also look in $XDG_CONFIG_HOME for the monetdb configuration file. SQL Frontend * Extended the system monitor with a table-returning function user_statistics() which keeps some statistics for each SQL user, e.g. the user's query count, total time spent, and maximal query seen. * Removed '@' syntax used to refer into a variable in a query. It was a non-standard method, which was replaced by a schema addition to variables. Variables in the global scope now have schema. All default global variables are set under schema "sys". However variables inside PSM don't have a schema, because there are no transaction semantics inside PSM at the moment. * Removed declared variables and tables from the global scope. They were transaction agnostic and incompatible with the SQL standard, i.e. they are valid exclusively under PSM (e.g. functions, procedures and triggers). * Scoping semantics were added for both variables and tables. Variables with the same name at a query are now resolved under the following precedence rules: 1. Tables, Views and CTEs at the FROM clause. 2. Variable declared in the body of function/procedure, i.e. local variable. 3. Function/procedure parameter. 4. Variable from the global scope. Tables with the same name now have the following precedence rules at a SQL query: 1. Table declared in the body of function/procedure, ie local table. 2. Temporary table. 3. Table from the current session schema. This means the query: SELECT * FROM "keys"; will list keys from temporary tables instead of persisted ones, because "keys" table is available for both "sys" and "tmp" schemas. * The table returning function "var" was extended with more details about globally declared variables, namely their schema, type and current value. * Removed obsolete sys.getContent(url) function. * Removed obsolete json.output(json) function. * Removed compatibility between interval types and other numeric types in favor for a more strict SQL standard compliance. This means operations between temporal types and other numeric types such as INT and DECIMAL are no longer possible, instead use interval types. e.g. SELECT date '2020-01-01' + 1; now gives the error. Instead do: SELECT date '2020-01-01' + interval '1' day; if 1 was meant to be a day interval. Setting an interval variable such as the session's current timezone with a number e.g. SET current_timezone = 1; is no longer possible. Instead do SET current_timezone = interval '1' hour; Casting between interval and other numeric types is no longer possible as well, because they are not compatible. * Because of incompatibilities this change may create, if a user intends to convert a numeric value to an interval, the multiplication function can be used in the form: <numeric value> * interval '1' <interval length> e.g. 10 * interval '1' second = interval '10' second. As for the other way around, the 'EPOCH' option was added to the extract syntax. This option returns the number of milliseconds since the UNIX epoch 1970-01-01 00:00:00 UTC for date, timestamp and time values (it can be negative). Meanwhile, for day and second intervals, it returns the total number of milliseconds in the interval. As a side note, the 'EPOCH' option is not available for month intervals, because this conversion is not transparent for this type. * Made general logarithm function log(x,base) compliant with the SQL standard, by swapping the input parameters. Instead of log(x,base), now is log(base,x). Bug Fixes * 3553: All schema access to ubiquitous functions * 3815: Incorrect results when expression contains implicit float/integer conversions * 6415: Date arithmetic types are inconsistent * 6814: provide native implementations for scalar functions sys.degrees(rad) and sys.radians(deg) * 6843: function sys.getcontent(url) always returns "Feature not supported" * 6857: remove not implemented aggregate function json.output(js json) * 6870: Missing bulk operators * 6878: SQL Connection Error when running SELECT queries containing AND command * 6910: SQLancer query: 'bat.append' undefined * 6930: SQLancer crash on join with coalesce * 6931: Allow EDITOR to be used for the current command in mclient * 6935: Wrong result when dividing interval by literal float * 6937: Lost the microsecond precisions * 6938: Segmentation fault in MalOptimizer * 6939: Error in optimizer multiplex when selecting profiler.getlimit() or wlc.clock() or wlc.tick() or wlr.clock() or wlr.tick() * 6941: SELECT queries on remote table fail when using LIKE in WHERE conditions * 6943: JSON parser is too permissive * 6948: msqldump with Empty BLOBs cannot be imported * 6949: Loosing timing precision * 6950: redundant/replicated code line in gdk/gdk_hash.c * 6951: Use a different naming scheme for MAL blocks * 6954: FILTER functions no longer find their implementation * 6955: ROUND(DECIMAL, PRECISION) gives incorrect result with non-scalar precision parameter * 6960: implementation of log(arg1,arg2) function is not compliant with the SQL standard, arguments are switched * 6962: "SELECT * FROM ids LIMIT 1" produces: exp_bin: !ERROR: Could not find %173.id * 6964: Table returning function: Cannot access column descriptor * 6965: Crash when using distinct on the result of a table returning function * 6974: Oct2020-branch cannot attach and load FITS files * 6976: Oct2020: default dbfarm cannot be started * 6978: Oct2020: \d shows empty result in schema created by include sql script * 6979: timestamp add integer * 6980: Oct2020: wrong mel definition for str.epilogue

1 0

Thread safety in python UDFs
by Ioannis Foufoulas 01 Oct '20

01 Oct '20

Hi, One question on python udfs. What about thread safety? In case of concurrent execution of numpy UDFs should the UDF developer use thread-safe structures or Monetdb handles this in some way? For example, pandas is not thread safe. This means that it should not be used in a UDF? Best, Yannis

2 1

Building MonetDBe from source
by Avik Sengupta 24 Sep '20

24 Sep '20

Hi, I've been trying to build MoneDBe from source, using Github master. I do not want to build the full executables, so I have used cmake --build . --target monetdbe With this, I am able to successfully build libmonetdbe.so. So that is great. However there does not seem to be an install-monetdbe or similar target. Running the `install` target tries to compile the whole application, which is not what I want. How do I copy the compiled artifacts (libmonetdbe.so and dependencies) to my install prefix? I'm trying not to have to manually copy the libraries. Regards - Avik

1 0

.mal and .sql files in Oct2020 lib folder
by Roberto Cornacchia 24 Sep '20

24 Sep '20

Hi there, I'm trying to compile from the Oct2020 branch and I have a question about the /lib64/monetdb5 folder. This folder used to contain .mal files. It also contained an autoload/ subfolder and a createdb/ subfolder (with .sql files). I don't see the same in the Oct2020 installation. I see that the content of /sql/scripts/CMakeLists.txt is entirely commented out. Is the installation of all these files temporarily disabled during development, or is this handled differently now? Roberto

4 4

Re: Internals of intermediate results in MonetDB
by Guodong Jin 24 Sep '20

24 Sep '20

Thanks! The point to MAL plan really helps me out! Best, Guodong On Sep 21, 2020, 06:00 -0400, 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. Internals of intermediate results in MonetDB (Guodong Jin) > 2. Re: Internals of intermediate results in MonetDB (Martin Kersten) > > > ---------------------------------------------------------------------- > > Message: 1 > Date: Sun, 20 Sep 2020 23:31:50 -0400 > From: Guodong Jin <guod.jin(a)gmail.com> > To: users-list(a)monetdb.org > Subject: Internals of intermediate results in MonetDB > Message-ID: <932d01d5-a742-4f57-8e19-ba7599667e42@Spark> > Content-Type: text/plain; charset="utf-8" > > Hi all, > > Thanks for your hard works on this great open source project. > I?m a student newly working on MonetDB to implement some research ideas, and I want to understand internals of how MonetDB chooses to materialize and how it represents intermediate results. > As I understand, table data and intermediate results in MonetDB are all stored as BATs, but I?m not sure if there are some special optimizations on BATs, rather than just array(s) of atoms. Here are two simple examples to elaborate my questions a little bit: > > ?? ?1.Given two tables,?Items(id, order, price, tax), and?Orders(id, discount). > ?? ? ? ? ?Items > ?? ? ? ? ? ? ? ? ?[I1, I2, I3, I4, I5, I6, I7, I8, I9, I10, I11...] > ?? ? ? ? ? ? ? ? ?[O3, O3, O3, O3, O3, O3, O3, O3, O3, O3, O4...] > ?? ? ? ? ? ? ? ? ?[10, 11, 20, 15, 110, 80, 90, 12, 13, 88, 30...] > ?? ? ? ? ? ? ? ? ?[0.1, 0.15, 0, 0.1, 0.1, 0.15, 0.11, 0.18, 0.10, 0.15, 0.20, ...] > ?? ? ? ? ?Orders > ?? ? ? ? ? ? ? ? ?[O1, O2, O3] > ?? ? ? ? ? ? ? ? ?[0.8, 0.9, 0.95] > > ?? ? ? ? ?When we perform a join between these two tables on the field `order` (one-to-many relationship represented by the primary-foreign key, O3 matches with [I1, I2, ?, I10]),?in the output intermediate BATs of the join, will MonetDB duplicate `O3` 10 times? Are there any optimizations currently in MonetDB to remove/reduce the duplication? > > ?? ?2. Given a table?item(id, price, tax), when MonetDB performs the filter `price<10` on the table,?will MonetDB actually copy all matched tuples into intermediate BATs? Or just keep a list of OIDs of matched tuples as references? > > Thanks! > > Best, > Guodong >

1 0

Internals of intermediate results in MonetDB
by Guodong Jin 21 Sep '20

21 Sep '20

Hi all, Thanks for your hard works on this great open source project. I’m a student newly working on MonetDB to implement some research ideas, and I want to understand internals of how MonetDB chooses to materialize and how it represents intermediate results. As I understand, table data and intermediate results in MonetDB are all stored as BATs, but I’m not sure if there are some special optimizations on BATs, rather than just array(s) of atoms. Here are two simple examples to elaborate my questions a little bit: 1.Given two tables, Items(id, order, price, tax), and Orders(id, discount). Items [I1, I2, I3, I4, I5, I6, I7, I8, I9, I10, I11...] [O3, O3, O3, O3, O3, O3, O3, O3, O3, O3, O4...] [10, 11, 20, 15, 110, 80, 90, 12, 13, 88, 30...] [0.1, 0.15, 0, 0.1, 0.1, 0.15, 0.11, 0.18, 0.10, 0.15, 0.20, ...] Orders [O1, O2, O3] [0.8, 0.9, 0.95] When we perform a join between these two tables on the field `order` (one-to-many relationship represented by the primary-foreign key, O3 matches with [I1, I2, …, I10]), in the output intermediate BATs of the join, will MonetDB duplicate `O3` 10 times? Are there any optimizations currently in MonetDB to remove/reduce the duplication? 2. Given a table item(id, price, tax), when MonetDB performs the filter `price<10` on the table, will MonetDB actually copy all matched tuples into intermediate BATs? Or just keep a list of OIDs of matched tuples as references? Thanks! Best, Guodong

2 1

Merge table error with one table
by Ioannis Foufoulas 17 Sep '20

17 Sep '20

Hi, we encounter an error when only one table is added to a merge table definition During a query like "select * from merge table” we get: “Table: missing ')'" Probably merge tables cannot contain just one table but the returned error looks like a syntax error. Some more info to reproduce: the table that has been added to the merge table is a remote table. Best, Yannis

3 4

Recommended compiler version and flags for Linux x86_64
by Daniel Glöckner 15 Sep '20

15 Sep '20

Hi, I'm currently building and testing MonetDB on CentOS7, x86_64. I was wondering about the recommended compiler version and compiler flags. So far I'm using gcc 9 and specifically enabled SSE3 extensions (though I did not notice any performance gain). Here's my cmake command cmake3 -DCMAKE_C_FLAGS="pthread -msse3 -O -Wall" -DCMAKE_INSTALL_PREFIX=../install ../MonetDB Is there a list of recommended compiler flags? Kind regards, Daniel

4 4

Nested tables?
by louie＠gmx.cn 14 Sep '20

14 Sep '20

Hi, Does MonetDB support nested tables? Oracle diagram for reference: https://docs.oracle.com/cd/B10501_01/appdev.920/a96594/adobjdes.htm#441615 Thanks, Louie

2 1

Default username and password: documented?
by Stefan Manegold 14 Sep '20

14 Sep '20

Hi, I just realize that, while the default (system-/admin-)username and password for a newly generated database in MonetDB are well known in the experienced MonetDB community, where do novice users find this information? I cannot find any explicit (or at least implicit?) documentation of this information, but maybe it's just that I fail to search properly? Any help is highly appreciated! Thanks! Best, Stefan -- | Stefan.Manegold(a)CWI.nl | DB Architectures (DA) | | www.CWI.nl/~manegold/ | Science Park 123 (L321) | | +31 (0)20 592-4212 | 1098 XG Amsterdam (NL) |

3 3

Why MonetDB uses Materialization
by Amit Pandey 11 Sep '20

11 Sep '20

Hey All, Thanks for the great open source project. So while going through some materials in a CMU DB course I saw it mentioned that MonetDB uses full materialization, and this is a bit weird since in c-store potentially scanning a billion columns a full materialization may take a lot of time and the classic volcano model might be better. Can you guys please explain the rationale ? These are the slides :- https://15445.courses.cs.cmu.edu/fall2019/slides/12-queryexecution1.pdfMone… is mentioned in Slide 11. Thanks and Regards

3 7

Bad query performance after lots of updates
by Daniel Glöckner 01 Sep '20

01 Sep '20

Hi, select count(*) from table where field = 'some value'; --> a few ms update table set field = 'value'; --> update 2 million rows out of 1 billion rows select count(*) from table where field = 'some value'; --> almost a minute :( Analyze did not help and sys.vacuum could not be used as the table has indexes (foreign key). Re-starting the DB helped ;) What's the recommended way to make MonetDB perform well after lots of updates? Kind regards, Daniel

2 1

compilers
by Roberto Cornacchia 01 Sep '20

01 Sep '20

Hi, I was wondering whether anyone has run any benchmarks with MonetDB for testing the effect of compilation options. For example, any combination of * gcc vs clang * -O1 vs -O2 vs -O3 * -march and -mtune I am actually planning to do this myself, but knowing about any previous evidence (even anecdotal) would be helpful. Cheers, Roberto

1 0

custom joins in Jun2020
by Roberto Cornacchia 27 Aug '20

27 Aug '20

This is a question about join order. In general, about how it changed from Nov2019 to Jun2020 releases. In particular, with respect to custom joins (filter functions). With a schema: CREATE TABLE t1(s string); CREATE TABLE t2(s string); Consider the following 2 queries, which only differ for having swapped conditions: Q1: SELECT t1.s, t2.s FROM t1, t2 WHERE t1.s <> t2.s AND [t1.s] maxlevenshtein [t2.s, 1]; Q2: SELECT t1.s, t2.s FROM t1, t2 WHERE [t1.s] maxlevenshtein [t2.s, 1] AND t1.s <> t2.s; [t1.s] maxlevenshtein [t2.s, 1] is equivalent to levenshtein(t1.s, t2.s) <=1 (i.e. the two strings have a levenshtein distance at most 1) This is a relatively expensive and selective function. In Nov2019, both Q1 and Q2 are translated to: - "maxlevenshtein" custom join - "!=" selection on the result In Jun2020, the two queries happen to be evaluated in the same order as they are written. Which means that Q2 is evaluated as: - "!=" join - "maxlevenshtein" selection This last evaluation plan is unfortunately not viable at all. The first join is not very selective, and the "maxlevenshtein" selection is run on way too many pairs, without the optimizations that can be exploited in a join (in a join, it is possible to skip the actual levenshtein computation for most combinations). Q2 in Jun2020 is 2 orders of magnitude slower than Q1, which quickly leads to unreasonably long query times. Of course, this is just one specific case. A very unfortunate one, due to the combination of a couple of factors: - The "!=" join is not selective enough - The custom function is an expensive one I guess my real questions are: - Is it by chance (or, as a by-product of more generic join ordering rules) that Nov2019 executes custom joins first in both Q1 and Q2, or was it an intentional choice to first execute custom joins? - What would a reasonable approach be? Is it reasonable to assume that if one writes a custom join, this is expected to use an expensive comparison function and that the join implementation can be much more efficient than the selection implementation (by skipping unnecessary comparisons)? If no assumptions can be made, can there be a way to annotate custom implementations with information on selectivity and cost? Thanks for you input, Roberto

1 1

Loading timestamps via Python loader functions
by Daniel Glöckner 26 Aug '20

26 Aug '20

Hi, there seems to be a limitation for Python-based loader functions wrt timestamps. Looking at the implementation in sql/backends/monet5/UDF/pyapi3/convert_loops.h suggests that timestamps are indeed not covered. Both of the the 2 following examples fail with "Failed conversion: MALException:pyapi3.eval:PY000!Unrecognized type. Could not convert to NPY_UNICODE.". CREATE LOADER array_loader() LANGUAGE PYTHON { from datetime import datetime _emit.emit( { 'a': [1,2,3], 'b': [datetime.utcnow(), datetime.utcnow(), datetime.utcnow()], 'c': ['1', '2', '3']}) }; CREATE LOADER array_loader() LANGUAGE PYTHON { import numpy as np from datetime import datetime dt = datetime.utcnow() _emit.emit( { 'a': [1,2,3], 'b': np.array([dt, dt, dt], dtype=np.datetime64), 'c': ['1', '2', '3']}) }; Is anyone aware of a workaround? An obvious workaround is to insert the timestamps as strings and convert them via SQL but that's much less efficient. Kind regards, Daniel

1 0

result properties in pcre.c
by Roberto Cornacchia 25 Aug '20

25 Aug '20

Hi, I was looking at pcre.c for some inspiration and found something suspicious. At the end of both pcre_likeselect() and re_likeselect(): if (bn && !msg) { BATsetcount(bn, BATcount(bn)); /* set some properties */ bn->tsorted = true; bn->trevsorted = bn->batCount <= 1; bn->tkey = true; bn->tseqbase = bn->batCount == 0 ? 0 : bn->batCount == 1 ? * (oid *) Tloc(bn, 0) : oid_nil; } Which I read as: if everything went well, then the result is sorted and key. But I miss why it should be sorted and key. Roberto

2 2

Re: Re: MonetDB on IBM Power architecture
by Daniel Glöckner 20 Aug '20

20 Aug '20

Hi, This looks very promising so far. Building on Power 8 was easy, just following the official guide from the wiki. https://www.monetdb.org/wiki/MonetDB:Building_from_sources So far I’ve only loaded larger amounts of data and the timings look good. Will perform more tests over the next days but overall this looks like absolutely usable for production. Kind regards, Daniel

1 0

JDBC Driver
by Walter Weinmann 19 Aug '20

19 Aug '20

Including the JDBC driver with gradle implementation 'monetdb:monetdb-java-lite:2.38' gives me: FAILURE: Build failed with an exception. * Where: Build file 'D:\SoftDevelopment\Projects\Konnexions\db_seeder\build.gradle' line: 66 * What went wrong: Could not determine the dependencies of task ':fatJar'. > Could not resolve all files for configuration ':runtimeClasspath'. > Could not find monetdb:monetdb-jdbc-new:2.37-SNAPSHOT. Searched in the following locations: - https://jcenter.bintray.com/monetdb/monetdb-jdbc-new/2.37-SNAPSHOT/maven-me… - https://jcenter.bintray.com/monetdb/monetdb-jdbc-new/2.37-SNAPSHOT/monetdb-… - https://repo.maven.apache.org/maven2/monetdb/monetdb-jdbc-new/2.37-SNAPSHOT… - https://repo.maven.apache.org/maven2/monetdb/monetdb-jdbc-new/2.37-SNAPSHOT… - https://jitpack.io/monetdb/monetdb-jdbc-new/2.37-SNAPSHOT/maven-metadata.xml - https://jitpack.io/monetdb/monetdb-jdbc-new/2.37-SNAPSHOT/monetdb-jdbc-new-… - http://maven.cubrid.org/monetdb/monetdb-jdbc-new/2.37-SNAPSHOT/maven-metada… - http://maven.cubrid.org/monetdb/monetdb-jdbc-new/2.37-SNAPSHOT/monetdb-jdbc… Required by: project : > monetdb:monetdb-java-lite:2.38 * Try: Run with --stacktrace option to get the stack trace. Run with --info or --debug option to get more log output. Run with --scan to get full insights.

3 2

Re: Re: MonetDB on IBM Power architecture
by Daniel Glöckner 19 Aug '20

19 Aug '20

Hi, Thanks a lot for confirming this. We’ll perform some basic tests on our Power 8 system to see if we’re able to build and run it. Kind regards, Daniel

1 0

MonetDB on IBM Power architecture
by Daniel Glöckner 19 Aug '20

19 Aug '20

Hi folks, I was wondering if one could run MonetDB on IBM Power architecture (Power 8 and later). I guess there's no official build for this? Did anyone try this out? Kind regards, Daniel

2 1

java.sql.BatchUpdateException: Error(s) occurred while executing the batch, see next SQLExceptions for details
by Walter Weinmann 18 Aug '20

18 Aug '20

*Without addBatch / executeBatch my program runs without problems:* [main] Start [main] tickerSymbolExtern='monetdb' [main] Start MonetDB [main] database table COUNTRY - 200 rows in total [main] database table TIMEZONE - 11 rows in total [main] database table COUNTRY_STATE - 500 rows so far [main] database table COUNTRY_STATE - 600 rows in total [main] database table CITY - 500 rows so far [main] database table CITY - 1000 rows so far [main] database table CITY - 1500 rows so far [main] database table CITY - 1800 rows in total [main] database table COMPANY - 500 rows so far [main] database table COMPANY - 1000 rows so far [main] database table COMPANY - 1500 rows so far [main] database table COMPANY - 2000 rows so far [main] database table COMPANY - 2500 rows so far [main] database table COMPANY - 3000 rows so far [main] database table COMPANY - 3500 rows so far [main] database table COMPANY - 4000 rows so far [main] database table COMPANY - 4500 rows so far [main] database table COMPANY - 5000 rows so far [main] database table COMPANY - 5400 rows in total [main] duration in seconds: 52 [main] End MonetDB [main] End *With addBatch / executeBatch and a batch size of 1000 I get the following error:* [main] Start [main] tickerSymbolExtern='monetdb' [main] Start MonetDB [main] database table COUNTRY - 200 rows in total [main] database table TIMEZONE - 11 rows in total [main] database table COUNTRY_STATE - 500 rows so far [main] database table COUNTRY_STATE - 600 rows in total [main] database table CITY - 500 rows so far [main] database table CITY - 1000 rows so far [main] database table CITY - 1500 rows so far java.sql.BatchUpdateException: Error(s) occurred while executing the batch, see next SQLExceptions for details at nl.cwi.monetdb.jdbc.MonetStatement.executeBatch(Unknown Source) at ch.konnexions.db_seeder.jdbc.AbstractJdbcSeeder.createDataInsert(AbstractJdbcSeeder.java:501) at ch.konnexions.db_seeder.jdbc.AbstractJdbcSeeder.createData(AbstractJdbcSeeder.java:404) at ch.konnexions.db_seeder.jdbc.AbstractJdbcSeeder.createData(AbstractJdbcSeeder.java:362) at ch.konnexions.db_seeder.DatabaseSeeder.main(DatabaseSeeder.java:141) Processing of the script was aborted, error code=1 *Both times the connection runs with autoCommit.*

3 2

java.sql.SQLException: DROP USER: 'kxn_user' owns a schema
by Walter Weinmann 18 Aug '20

18 Aug '20

*The following SQL statements are executed successfully:* SELECT count(*) FROM sys.users WHERE name = 'kxn_user' DROP SCHEMA IF EXISTS kxn_schema CASCADE CREATE USER kxn_user WITH UNENCRYPTED PASSWORD 'monetdb' NAME 'Dbseeder User' SCHEMA sys CREATE SCHEMA kxn_schema AUTHORIZATION kxn_user ALTER USER kxn_user SET SCHEMA kxn_schema CREATE TABLE COUNTRY ( PK_COUNTRY_ID BIGINT NOT NULL PRIMARY KEY, COUNTRY_MAP BLOB, CREATED TIMESTAMP NOT NULL, ISO3166 VARCHAR(50), MODIFIED TIMESTAMP, NAME VARCHAR(100) NOT NULL UNIQUE ) *The following SQL statement produces an error message:* DROP USER kxn_user java.sql.SQLException: DROP USER: 'kxn_user' owns a schema at nl.cwi.monetdb.jdbc.MonetConnection$ResponseList.executeQuery(Unknown Source) 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 ch.konnexions.db_seeder.jdbc.AbstractJdbcSeeder.dropUser(AbstractJdbcSeeder.java:814) at ch.konnexions.db_seeder.jdbc.monetdb.MonetdbSeeder.setupDatabase(MonetdbSeeder.java:103) at ch.konnexions.db_seeder.jdbc.AbstractJdbcSeeder.createData(AbstractJdbcSeeder.java:359) at ch.konnexions.db_seeder.DatabaseSeeder.main(DatabaseSeeder.java:141) Processing of the script was aborted, error code=1 *How can I delete the user in this constellation without errors ?*

2 1

New Elixir driver + some questions and feedback
by Karl Seguin 02 Aug '20

02 Aug '20

I've been working on a driver for Elixir (https://github.com/karlseguin/monet) It's immature, but in a decent enough state for our prototyping. Overall, I've been very pleased and excited about MonetDB. I wanted to share some observations from the perspective of both a newcomer and having developed a driver. First though, a couple questions: 1. I understand what a Q_BLOCK is, but I don't understand under what circumstances it happens. 2. Aside from the initial challenge response, it seems like all messages from the server are _always_ in response to a query. This is different than, say, PostgreSQL which can push data to signal some change, outside of the typical request->response flow. Correct? As for my feedback: 1. The first and, by far, single biggest issue is with the text-base protocol. I'm sure this has been discussed before, and I doubt that I can add any new insight. But it was tedious to implement and error prone. We were initially considering storing large blobs in MonetDB (for a secondary purpose), but I'm now rethinking that idea because of the performance for serializing and deserializing the data. The rest of these issues are in no particular order. 2. I'm pretty sure if you send an invalid x command, the server goes haywire. The simplest way I found to reproduce this is to change the 's' to 'x' in the official Node client (https://github.com/MonetDB/monetdb-nodejs/blob/19f24ec16db70a26d0b11e51d77b…) and connect. The CPU load spikes and the logs are filled with: "createExceptionInternal: !ERROR: SQLException:SQLparser:42000!Unrecognized X command: SET TIME ZONE INTERVAL '-480' MINUTE;"" (I'm running: 17ab9b680ec55b342c08d034f2c5659a073cc74a) 3. Maybe this is naive, but I don't understand why this works: insert into x (time_col) values ('10:30:50') but this doesn't: exec 7('10:30:50') I was hoping I could ignore the response from a "prepare" (except for getting the id from the first line) and just encode based on the argument types, but no. I need the type AND precision so that I can do "time(3) '...'" or "blob '....'" etc. 4. Point 3 is made marginally worse because: prepare select 1 - ? returns 2 rows. I look for the suffix "NULL,\tNULL,\tNULL\t]" to tell which are "real" placeholders. I'm not sure if that's correct. 5. I was surprised to see dates with 1, 2 and 3 digit years. Making it 4 digits would make it ISO8601 compliant. 6. I was caught off guard by the fact that ab error can show up as part of what seems like a healthy response. Initially, I was just looking for a response that stars with !. But while testing, a concurrent create table failed and I got "&3 X Y\n!CODE!ERR\n". Is there a rule that can help me parse these efficiently? (like they'll always be on the first or 2nd line), or can they really show up anywhere? 7. Some of the drivers appear to be in bad shape. The node driver gets confused by my point 4 above and fails to handle it. It also doesn't support time or blob values, and tries to JSON the built-in Date object. I couldn't get the go driver to work at all (nil referencse trying to read rows). The ruby driver has no support for prepared statements and I'm pretty sure it doesn't return the correct value for: db.query("select '\\\\'") 8. While I'm probably wrong, I can't help but think that merging the functionalities of the monetdbd and monetdb binaries would streamline the experience for newcomers. Please don't interpret these as unkind criticisms of (or unhappiness with) MonetDB. Again, overall, very happy.

2 1

Creation of a remote table overhead
by Ioannis Foufoulas 28 Jul '20

28 Jul '20

Hi, I am trying to develop a Python client which sends queries to different Monetdb servers and transfers the results between them using the remote tables functionalities. I’ve seen than my main overhead is execution of CREATE REMOTE TABLE statement. It requires 70-90msec so in cases with many transfers and fast and small queries this makes a difference. After looking at some older versions I’ve seen that the creation of a remote table was very fast before August 2018 (this is when authentication added). Is there any hint on this? Could the. authentication strategy change in someway so that it runs faster or is it possible to select among different authentication methods for the remote tables? Best, Yannis

4 7

MonetDB on GitHub
by Roberto Cornacchia 27 Jul '20

27 Jul '20

Hi, I was wondering whether the MonetDB repositories on GitHub (or any git-based platform) are, in your intentions, there to stay. That's because I'm planning to move our own fork from Mercurial to git. This would come very easy by using the GitHub repositories as upstreams. My second question though is: the git repositories are at this moment about 10 days behind. Is there a reason? Will that be improved? Thanks! Roberto

2 2

Announcement: New Jun2020-SP1 Bugfix release of MonetDB suite
by Sjoerd Mullender 27 Jul '20

27 Jul '20

The MonetDB team at MonetDB BV is pleased to announce the Jun2020-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://www.monetdb.org/downloads/>. Jun 2020-SP1 bugfix release (11.37.11) MonetDB Common * Hash buckets come in variable widths. But if a BAT grows long enough so that the BAT indexes that are stored in the buckets don't fit anymore, the buckets need to be widened. This is now fixed. Bug Fixes * 6844: sys.getUser('https://me:pw@www.monetdb.org/Doc') does not return the user: me * 6845: the url sys.get...(url) functions do not allow null as a parameter * 6858: json.keyarray(json '{ "":0 }') fails with error: Could not allocate space * 6859: only first character of the separator string in json.text(js json, sep string) is used * 6873: sys.hot_snapshot() creates incomplete snapshots if the write-ahead log is very large * 6876: tar files created by sys.hot_snapshot() produce warnings on some implementations of tar * 6877: MonetDB produces malformed LZ4 files * 6880: Left fuzzy queries are much slower than other fuzzy queries. * 6882: cgroups limits no longer respected? * 6883: SQLancer crash on delete query * 6884: SQLancer generates query with unclear error message * 6885: SQLancer causes assertion error on UTF8_strlen * 6886: SQLancer alter table add unique gives strange error message * 6887: SQLancer crash on complex query * 6888: SQLancer crash on cross join on view * 6889: SQLancer crash on long query * 6892: SQLancer crash on query with HAVING * 6893: SQLancer inner join reporting GDK error * 6894: SQLancer crash on rtrim function * 6895: SQLancer causing 'algebra.select' undefined error * 6896: SQLancer algebra.select' undefined 2 * 6897: SQLancer distinct aggregate with error on group by constant * 6898: SQLancer crash on join query * 6899: SQLancer TLP query with wrong results * 6900: SQLancer generated SIGFPE * 6901: SQLancer TLP query with wrong results 2 * 6902: SQLancer query: batcalc.between undefined * 6903: SQLancer calc.abs undefined * 6904: SQLancer aggr.subavg undefined * 6905: SQLancer TLP query with wrong results 3 * 6906: SQLancer crash on complex join * 6907: SQLancer algebra.select undefined * 6908: SQLancer inputs not the same size * 6909: SQLancer query with wrong results * 6911: SQLancer query: 'calc.bit' undefined * 6917: Decimal parsing fails * 6918: SQLancer query compilation error * 6919: SQLancer insert function doesn't handle utf-8 strings * 6920: SQLancer project_bte: does not match always * 6922: Timestamp columns not migrated to new format * 6923: Imprints data files for timestamp BAT not migrated to the new format * 6924: SQLancer query copy on unique pair of columns fails and complex query with GDK error * 6925: Count string rows in union of string tables leaks (RSS) memory * 6926: SQLancer query with wrong results * 6927: SQLancer inputs not the same size * 6928: SQLancer crash on coalesce * 6929: SQLancer calc.date undefined * 6932: Syntax error while parsing JSON numbers with exponent * 6934: sys.isauuid() returns wrong answer for some invalid uuid strings

1 0

enhancements question
by ALEX OSSIPOV 25 Jul '20

25 Jul '20

Hi MonetDB Team I am very interested in pushing bug 6702 which is in fact an enhancement (was enhancement request) but seems to be more of a miss these days since all other competing products have it already implemented. What is decision process on prioritizing enhancements and how can a specific request be expedited/elevated? Love this product but can't survive without recursive support in modern analytics world. Regards Alex

2 1

MonetDb crashes on a join using date between from and until date
by Groot, Frank (Sociale Verzekeringsbank) 22 Jul '20

22 Jul '20

Hi all, We are using the June 2020 release, trying to query a table with 24 mio rows and join each row from this 'left' table with one row from a right table according to a specific join clause. This clause does not result in a carthesian product. The query structure is as follows: select left.* from myschema.mytable left inner join otherschema.yourtable right on left.somedatefield >= right.fromdate and left.somedatefield <= right.untildate where left.otherdatefield is null ; This query results in following error in the merovingian.log: 2020-07-17 11:58:00 MSG merovingian[401281]: database 'datalake' (-1) has crashed with signal SIGSEGV (dumped core) However, if I change the query like below, making the left table a derived table and sorting the date field used in the join, the error doesn't occur: select left.* from (select * from myschema.mytable order by somedatefield) left inner join otherschema.yourtable right on left.somedatefield >= right.fromdate and left.somedatefield <= right.untildate where left.otherdatefield is null ; When I tested the original query with a subset from the left table there was a threshold of a certain amount of rows (in this case 26 rows) below which the query ran fine. Also, repeating the test on a different 'left' table with ~200k rows didn't result in a crash. Could it be that there's been a change in the query pipeline or join handling? Kind regards, Frank

2 1

Partition pruning for merge tables
by Daniel Glöckner 22 Jul '20

22 Jul '20

Hi, looking at merge tables and the related query plans I was expecting the planner to skip tables depending on partition information and query predicates. I'm using the official example for merge tables: https://www.monetdb.org/blog/updatable-merge-tables CREATE MERGE TABLE splitted (stamp TIMESTAMP, val INT) PARTITION BY RANGE ON (stamp); CREATE TABLE first_decade (stamp TIMESTAMP, val INT); CREATE TABLE second_decade (stamp TIMESTAMP, val INT); CREATE TABLE third_decade (stamp TIMESTAMP, val INT); ALTER TABLE splitted ADD TABLE first_decade AS PARTITION FROM TIMESTAMP '2000-01-01 00:00:00' TO TIMESTAMP '2010-01-01 00:00:00'; ALTER TABLE splitted ADD TABLE second_decade AS PARTITION FROM TIMESTAMP '2010-01-01 00:00:00' TO TIMESTAMP '2020-01-01 00:00:00'; ALTER TABLE splitted ADD TABLE third_decade AS PARTITION FROM TIMESTAMP '2020-01-01 00:00:00' TO RANGE MAXVALUE WITH NULL VALUES; INSERT INTO splitted VALUES (TIMESTAMP '2000-01-01 00:00:00', 1), (TIMESTAMP '2002-12-03 20:00:00', 2), (TIMESTAMP '2012-05-12 21:01:00', 3), (TIMESTAMP '2019-12-12 23:59:59', 4); INSERT INTO splitted VALUES (TIMESTAMP '2020-01-01 00:00:00', 5), (NULL, 6); When I trigger the following query I was expecting that only table second_decade would be used as Monet would be smart enough to check the partition information for each table. Kind regards, Daniel sql>plan select * from splitted where stamp = TIMESTAMP '2020-01-01 00:00:00'; +--------------------------------------------------------------------------------------------------------------------------------------+ | rel | +======================================================================================================================================+ | union ( | | | union ( | | | | project ( | | | | | select ( | | | | | | table(sys.first_decade) [ "first_decade"."stamp" as "splitted"."stamp", "first_decade"."val" as "splitted"."val" ] COUNT | | | | | ) [ "splitted"."stamp" = timestamp(7) "2020-01-01 00:00:00.000000" ] | | | | ) [ "splitted"."stamp", "splitted"."val" ], | | | | project ( | | | | | select ( | | | | | | table(sys.second_decade) [ "second_decade"."stamp" as "splitted"."stamp", "second_decade"."val" as "splitted"."val" ] COUNT | | | | | ) [ "splitted"."stamp" = timestamp(7) "2020-01-01 00:00:00.000000" ] | | | | ) [ "splitted"."stamp", "splitted"."val" ] | | | ) [ "splitted"."stamp", "splitted"."val" ], | | | project ( | | | | select ( | | | | | table(sys.third_decade) [ "third_decade"."stamp" as "splitted"."stamp", "third_decade"."val" as "splitted"."val" ] COUNT | | | | ) [ "splitted"."stamp" = timestamp(7) "2020-01-01 00:00:00.000000" ] | | | ) [ "splitted"."stamp", "splitted"."val" ] | | ) [ "splitted"."stamp", "splitted"."val" ] | +--------------------------------------------------------------------------------------------------------------------------------------+ 19 tuples clk: 2.428 ms

2 4

Re: Re: Implementing bulk export to Parquet
by Daniel Glöckner 20 Jul '20

20 Jul '20

Hi, thanks for the implementation hint with Arrow C++, Anton. I’ve tried a similar approach using Pyarrow () and initial tests look promising. NumPy arrays can be written to Parquet quite easily: https://www.mail-archive.com/dev@parquet.apache.org/msg04506.html <https://www.mail-archive.com/dev@parquet.apache.org/msg04506.html> Kind regards, Daniel

1 0

BUG on last version?
by Ariel Abadi 20 Jul '20

20 Jul '20

Hi! Maybe somebody can help me to understand if there is a bug on the latest version or if it is a better way to solve this issue. Linux Centos 7 Monetdb version 11.37.7 (Jun2020) I have created a new fresh database to avoid any issue on the upgrade, and imported all the data. The DB seems to be working fine. We are using creating some dynamic queries, so the final query we need to run is the following: select sum(nrm_realmin) as nrm_realmin from sb_traf.nrm_20200716 as t0 where (case when (nrm_tim_date || '@' || nrm_vep_id || '@' || nrm_rby_brn_id) in ( select distinct cast(curdate() - cast(value as interval day) as varchar(10)) || '@' || pbd_ven_id || '@' || pbd_deb_id as xx from sb_agre.agre_agr_agreement as agr join sb_agre.agre_prb_product_buy as prb on agr_id = prb_agr_id join sb_agre.agre_pbd_product_buydetail as psd on prb_id = pbd_prb_id left join sys.generate_series(0, 2) on (curdate() - cast(value as interval day)) between coalesce(prb_dstart, agr_dstart) and coalesce(prb_dend, agr_dend) and (curdate() - cast(value as interval day)) <= curdate() ) then 'BILAT' else 'AVAILABLE' end) not ilike '%BILAT%' I am using this exact query on some other versions and they are all working, but on this version the query keeps running with no answer, even worse, I tried to stop the query via CALL sys.stop(tag) ... and it never stops, I had to restart the database to stop that query. Does anybody have a clue how I can solve this issue? Thanks for the help in advance! Rgds Ariel <http://www.avg.com/email-signature?utm_medium=email&utm_source=link&utm_cam…> Virus-free. www.avg.com <http://www.avg.com/email-signature?utm_medium=email&utm_source=link&utm_cam…> <#DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>

2 1

Issues with None values handling in Python UDFs returning TABLE
by Daniel Glöckner 20 Jul '20

20 Jul '20

Hi, when testing TABLE-returning UDFs, I noticed that there are some issues with handling of None values from Python. Is there any magic approach on how to return NULLS / None values from such a UDF? Please see my examples below which are all not working ;) Kind regards, Daniel # 1 Does not work - Henk is just repeated in the second row drop function if exists python_table; CREATE FUNCTION python_table() RETURNS TABLE(name STRING, country STRING, age INTEGER) LANGUAGE PYTHON { result = dict() result['name'] = ['Henk', None, 'Elizabeth'] result['country'] = ['NL', 'USA', 'UK'] result['age'] = [25, 30, 33] return result }; SELECT * FROM python_table() ; # 2 Also does not work - Henk is just repeated in the second row drop function if exists python_table; CREATE FUNCTION python_table() RETURNS TABLE(name STRING, country STRING, age INTEGER) LANGUAGE PYTHON { result = dict() names = numpy.empty(3, dtype=object) names[0] = 'Henk' names[2] = 'Elizabeth' result['name'] = names result['country'] = ['NL', 'USA', 'UK'] result['age'] = [25, 30, 33] return result }; SELECT * FROM python_table() ; # 3 Also does not work - Henk is just repeated in the second row drop function if exists python_table; CREATE FUNCTION python_table() RETURNS TABLE(name STRING, country STRING, age INTEGER) LANGUAGE PYTHON { result = dict() names = numpy.full_like(numpy.arange(3, dtype=object), None) #names[0] = 'Henk' names[2] = 'Elizabeth' result['name'] = names result['country'] = ['NL', 'USA', 'UK'] result['age'] = [25, 30, 33] return result }; SELECT * FROM python_table() ; # 4 breaks if all values for a column are None # "Error converting dict return value "name": An array of size 3 was returned, yet we expect a list of 1 columns. The result is invalid.. " drop function if exists python_table; CREATE FUNCTION python_table() RETURNS TABLE(name STRING, country STRING, age INTEGER) LANGUAGE PYTHON { result = dict() names = numpy.full_like(numpy.arange(3, dtype=object), None) result['name'] = names result['country'] = ['NL', 'USA', 'UK'] result['age'] = [25, 30, 33] return result }; SELECT * FROM python_table() ;

1 0

Implementing bulk export to Parquet
by Daniel Glöckner 19 Jul '20

19 Jul '20

Hi, the project which I'm involved in would require us to integrate MonetDB with "big data technologies" so exporting data from MonetDB to Parquet for further processing in the big data tools would be an obvious choice. Can anyone guide me what it would mean to extend MonetDB with a writer for Parquet? It would be awesome if COPY INTO would be able to produce Parquet efficiently, e.g. using Arrow library: https://arrow.apache.org/docs/cpp/parquet.html Kind regards, Daniel

3 2

MonetDB/e and R
by Mark Clements 17 Jul '20

17 Jul '20

It is exciting to see the release of MonetDB/e - congratulations. I have two questions to offer. First, has anyone begun work on an R package? As a follow-up question if not, which of MonetDB.R, MonetDBLite or DuckDB packages for R would be a better starting point? Second, could someone kindly explain the potential advantages and disadvantages of using either MonetDB/e or DuckDB, please? They both seem to come from CWI, so this is a little confusing. Sincerely, Mark. När du skickar e-post till Karolinska Institutet (KI) innebär detta att KI kommer att behandla dina personuppgifter. Här finns information om hur KI behandlar personuppgifter<https://ki.se/medarbetare/integritetsskyddspolicy>. Sending email to Karolinska Institutet (KI) will result in KI processing your personal data. You can read more about KI’s processing of personal data here<https://ki.se/en/staff/data-protection-policy>.

3 2

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

New client library for PHP
by Tamas Bolner 10 Apr '20

10 Apr '20

Hi, I spent the past nights creating a client library for PHP, because I found too many problems with the official one. Just for the heads up: https://github.com/bolner/MonetDB-PHP-Deux Happy Easter! Tamas

1 0

RE: System Catalog - Objects creation date and time.
by Suresh Devarakonda 08 Apr '20

08 Apr '20

Hi, I just wanted to see when the objects such as tables created in MonetDB. I have looked at below note, but couldn't find the catalog tables that provides the table creation time so wondering if MonetDB has that capability. Can someone let me know at the earliest. Your early response is very much appreciated. https://www.monetdb.org/Documentation/SystemCatalog [cid:image002.jpg@01D60C19.28554180] 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

Running two databases with forward set to redirect (monetdbd)
by stephenb 08 Apr '20

08 Apr '20

We have an environment that does not support Unix domain sockets well. We thus configured monetdbd with forward=redirect, as we note from the monetdbd man page that "for local databases, the proxy method uses a UNIX domain socket feature" We can create two database, but trying to connect (on the default 50000 port) to them only the first-started database is available. If we try to connect to the second we get the error "request for database 'dev_sb2', but this is database 'dev_sb', did you mean to connect to monetdbd instead?". With some experimentation we have found that starting the databases directly using mserver5 and specifying different ports (--set mapi_port=50001 etc) we can connect to both databases, but this does complicate things and we would prefer to use the monetdb command for starting, stopping, creating etc. How can we configure monetdbd (or perhaps individual databases?) so that we can connect to two local databases through monetdbd and manage them using monetdb? Thanks Steve

2 2

Fwd: [Help] create a new db user but need to add prefix 'sys.' in order to execute timestamp_to_str() function
by Kevin Chen 07 Apr '20

07 Apr '20

Hi, I have a new db user created by following scripts: CREATE USER "imdb" WITH PASSWORD 'imdb' NAME 'IMDB Explorer' SCHEMA "sys"; CREATE SCHEMA "imdb" AUTHORIZATION "imdb"; ALTER USER "imdb" SET SCHEMA "imdb"; When I tried to execute the function "timestamp_to_str", I have to add "sys." prefix to make it effective. "date_trunc" also needed to do so. The default user "monetdb" can run the function directly without the prefix. What should I do to perform the same way as user "monetdb" ? sql>select timestamp_to_str(localtimestamp, '%Y-%m-%d %H:%M:%S %z'); SELECT: no such binary operator 'timestamp_to_str(timestamp,char)' sql>select sys.timestamp_to_str(localtimestamp, '%Y-%m-%d %H:%M:%S %z'); +---------------------------+ | L2 | +===========================+ | 2020-04-06 20:37:39 +0800 | +---------------------------+ 1 tuple sql>select date_trunc('minute', timestamp '2119-02-17 02:08:12.345678'); SELECT: no such binary operator 'date_trunc(char,timestamp)' sql>select sys.date_trunc('minute', timestamp '2119-02-17 02:08:12.345678'); +----------------------------+ | L2 | +============================+ | 2119-02-17 02:08:00.000000 | +----------------------------+ 1 tuple

2 1

Fwd: BULK UPLOAD very poor performance
by Ariel Abadi 01 Apr '20

01 Apr '20

Hi! After a big battle against the DB, I found 2 issues. 1) Running the COPY RECORDS FROM *STDIN *and from *FILE*. Is totally different in terms of performance. While the first one last 24 minutes, the second one last only *90 secs.* 2) As I had generated all those files thru msqldump (which it states the "CREATE TABLE" instruction and then the COPY RECORDS)... I try ro run the COPY *XXXX OFFSET *RECORDS (in order to avoid the create table statement)... and the query, because of the word "STDIN" in the file. It was not OFFSETTING all the rows I was asking for. I hope I've been clear... if not please let me know! Ariel On Tue, Mar 24, 2020 at 11:33 PM Martin Kersten <martin.kersten(a)cwi.nl> wrote: > Hi > > I am not sure what issue you are now dealing with, but since you are a > company > that seems to use (or considering) MonetDB in your commercial offerings, > I gladly refer you to MonetDBSolutions(*), where we can give you the > commercial > support needed to diagnose your issues. > > regards Martin > > (*) https://www.monetdbsolutions.com/ > > On 24/03/2020 23:55, Ariel Abadi wrote: > > Thanks Martin for your prompt response > > But I did and msqldump from other database, and now the upload is being > done thru the BULK LOAD. > > > > /COPY 14173375 RECORDS INTO "sb_traf"."nrm_20180818" FROM stdin USING > DELIMITERS '\t','\n','"';/ > > > > > > Any other idea ? :'( > > > > > > > > > > On Tue, Mar 24, 2020 at 7:37 PM Martin Kersten <martin.kersten(a)cwi.nl > <mailto:martin.kersten@cwi.nl>> wrote: > > > > Hi, > > > > given the numbers I suspect you are loading them as SQL insert > statements under autocommit mode, > > Please read the documentation on Bulk loading. > > > > regards,Martin > > Sent from my iPad > > > >> On 24 Mar 2020, at 23:12, Ariel Abadi <aabadi(a)starbi.com <mailto: > aabadi(a)starbi.com>> wrote: > >> > >> > >> Hi! > >> Maybe somebody can help me, please! > >> > >> I'm trying to upload into Monet a file with 14MM records. Is taking > more than *24 minutes.* > >> It is a fresh new database. > >> > >> Enclosed you will find the server configuration. > >> > >> Monetdb Version Nov2019-SP3 > >> > >> > >> *[root@mn tmp]$ lscpu* > >> Architecture: x86_64 > >> CPU op-mode(s): 32-bit, 64-bit > >> Byte Order: Little Endian > >> CPU(s): 40 > >> On-line CPU(s) list: 0-39 > >> Thread(s) per core: 2 > >> Core(s) per socket: 10 > >> Socket(s): 2 > >> NUMA node(s): 2 > >> Vendor ID: GenuineIntel > >> CPU family: 6 > >> Model: 85 > >> Model name: Intel(R) Xeon(R) Silver 4114 CPU @ 2.20GHz > >> Stepping: 4 > >> CPU MHz: 800.479 > >> BogoMIPS: 4400.00 > >> Virtualization: VT-x > >> L1d cache: 32K > >> L1i cache: 32K > >> L2 cache: 1024K > >> L3 cache: 14080K > >> NUMA node0 CPU(s): > 0,2,4,6,8,10,12,14,16,18,20,22,24,26,28,30,32,34,36,38 > >> NUMA node1 CPU(s): > 1,3,5,7,9,11,13,15,17,19,21,23,25,27,29,31,33,35,37,39 > >> Flags: fpu vme de pse tsc msr pae mce cx8 apic sep > mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm pbe > syscall nx pdpe1gb rdtscp lm constant_tsc art arch_perfmon pebs bts > rep_good nopl xtopology nonstop_tsc > >> cpuid aperfmperf pni pclmulqdq dtes64 monitor ds_cpl vmx smx est > tm2 ssse3 sdbg fma cx16 xtpr pdcm pcid dca sse4_1 sse4_2 x2apic movbe > popcnt tsc_deadline_timer aes xsave avx f16c rdrand lahf_lm abm > 3dnowprefetch cpuid_fault epb cat_l3 cdp_l3 > >> invpcid_single pti intel_ppin ssbd mba ibrs ibpb stibp tpr_shadow > vnmi flexpriority ept vpid ept_ad fsgsbase tsc_adjust bmi1 hle avx2 smep > bmi2 erms invpcid rtm cqm mpx rdt_a avx512f avx512dq rdseed adx smap > clflushopt clwb intel_pt avx512cd avx512bw > >> avx512vl xsaveopt xsavec xgetbv1 xsaves cqm_llc cqm_occup_llc > cqm_mbm_total cqm_mbm_local dtherm ida arat pln pts pku ospke md_clear > flush_l1d > >> > >> > >> *[root@mn tmp]$ cat /proc/meminfo* > >> > >> MemTotal: 83886080 kB > >> MemFree: 10535812 kB > >> MemAvailable: 80159744 kB > >> Buffers: 0 kB > >> Cached: 69623932 kB > >> SwapCached: 0 kB > >> Active: 6625436 kB > >> Inactive: 63737872 kB > >> Active(anon): 324456 kB > >> Inactive(anon): 417184 kB > >> Active(file): 6300980 kB > >> Inactive(file): 63320688 kB > >> Unevictable: 0 kB > >> Mlocked: 5320 kB > >> SwapTotal: 8388604 kB > >> SwapFree: 8381988 kB > >> Dirty: 220 kB > >> Writeback: 0 kB > >> AnonPages: 13626052 kB > >> Mapped: 186368 kB > >> Shmem: 1800 kB > >> KReclaimable: 4134960 kB > >> Slab: 0 kB > >> SReclaimable: 0 kB > >> SUnreclaim: 0 kB > >> KernelStack: 16720 kB > >> PageTables: 56500 kB > >> NFS_Unstable: 0 kB > >> Bounce: 0 kB > >> WritebackTmp: 0 kB > >> CommitLimit: 74123472 kB > >> Committed_AS: 18337812 kB > >> VmallocTotal: 34359738367 kB > >> VmallocUsed: 415836 kB > >> VmallocChunk: 0 kB > >> Percpu: 39552 kB > >> HardwareCorrupted: 0 kB > >> AnonHugePages: 0 kB > >> ShmemHugePages: 0 kB > >> ShmemPmdMapped: 0 kB > >> CmaTotal: 0 kB > >> CmaFree: 0 kB > >> HugePages_Total: 0 > >> HugePages_Free: 0 > >> HugePages_Rsvd: 0 > >> HugePages_Surp: 0 > >> Hugepagesize: 2048 kB > >> Hugetlb: 0 kB > >> DirectMap4k: 816396 kB > >> DirectMap2M: 82513920 kB > >> DirectMap1G: 52428800 kB > >> > >> > >> > >> > >> _______________________________________________ > >> 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 > > > > > > _______________________________________________ > > users-list mailing list > > users-list(a)monetdb.org > > https://www.monetdb.org/mailman/listinfo/users-list > > >

5 11

BULK UPLOAD very poor performance
by Ariel Abadi 24 Mar '20

24 Mar '20

Hi! Maybe somebody can help me, please! I'm trying to upload into Monet a file with 14MM records. Is taking more than *24 minutes.* It is a fresh new database. Enclosed you will find the server configuration. Monetdb Version Nov2019-SP3 *[root@mn tmp]$ lscpu* Architecture: x86_64 CPU op-mode(s): 32-bit, 64-bit Byte Order: Little Endian CPU(s): 40 On-line CPU(s) list: 0-39 Thread(s) per core: 2 Core(s) per socket: 10 Socket(s): 2 NUMA node(s): 2 Vendor ID: GenuineIntel CPU family: 6 Model: 85 Model name: Intel(R) Xeon(R) Silver 4114 CPU @ 2.20GHz Stepping: 4 CPU MHz: 800.479 BogoMIPS: 4400.00 Virtualization: VT-x L1d cache: 32K L1i cache: 32K L2 cache: 1024K L3 cache: 14080K NUMA node0 CPU(s): 0,2,4,6,8,10,12,14,16,18,20,22,24,26,28,30,32,34,36,38 NUMA node1 CPU(s): 1,3,5,7,9,11,13,15,17,19,21,23,25,27,29,31,33,35,37,39 Flags: fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm pbe syscall nx pdpe1gb rdtscp lm constant_tsc art arch_perfmon pebs bts rep_good nopl xtopology nonstop_tsc cpuid aperfmperf pni pclmulqdq dtes64 monitor ds_cpl vmx smx est tm2 ssse3 sdbg fma cx16 xtpr pdcm pcid dca sse4_1 sse4_2 x2apic movbe popcnt tsc_deadline_timer aes xsave avx f16c rdrand lahf_lm abm 3dnowprefetch cpuid_fault epb cat_l3 cdp_l3 invpcid_single pti intel_ppin ssbd mba ibrs ibpb stibp tpr_shadow vnmi flexpriority ept vpid ept_ad fsgsbase tsc_adjust bmi1 hle avx2 smep bmi2 erms invpcid rtm cqm mpx rdt_a avx512f avx512dq rdseed adx smap clflushopt clwb intel_pt avx512cd avx512bw avx512vl xsaveopt xsavec xgetbv1 xsaves cqm_llc cqm_occup_llc cqm_mbm_total cqm_mbm_local dtherm ida arat pln pts pku ospke md_clear flush_l1d *[root@mn tmp]$ cat /proc/meminfo* MemTotal: 83886080 kB MemFree: 10535812 kB MemAvailable: 80159744 kB Buffers: 0 kB Cached: 69623932 kB SwapCached: 0 kB Active: 6625436 kB Inactive: 63737872 kB Active(anon): 324456 kB Inactive(anon): 417184 kB Active(file): 6300980 kB Inactive(file): 63320688 kB Unevictable: 0 kB Mlocked: 5320 kB SwapTotal: 8388604 kB SwapFree: 8381988 kB Dirty: 220 kB Writeback: 0 kB AnonPages: 13626052 kB Mapped: 186368 kB Shmem: 1800 kB KReclaimable: 4134960 kB Slab: 0 kB SReclaimable: 0 kB SUnreclaim: 0 kB KernelStack: 16720 kB PageTables: 56500 kB NFS_Unstable: 0 kB Bounce: 0 kB WritebackTmp: 0 kB CommitLimit: 74123472 kB Committed_AS: 18337812 kB VmallocTotal: 34359738367 kB VmallocUsed: 415836 kB VmallocChunk: 0 kB Percpu: 39552 kB HardwareCorrupted: 0 kB AnonHugePages: 0 kB ShmemHugePages: 0 kB ShmemPmdMapped: 0 kB CmaTotal: 0 kB CmaFree: 0 kB HugePages_Total: 0 HugePages_Free: 0 HugePages_Rsvd: 0 HugePages_Surp: 0 Hugepagesize: 2048 kB Hugetlb: 0 kB DirectMap4k: 816396 kB DirectMap2M: 82513920 kB DirectMap1G: 52428800 kB

2 2

Slowdown behaviour after loading large amounts of data
by stephenb 23 Mar '20

23 Mar '20

We believe we might be observing the behaviour described here: https://www.monetdb.org/blog/monetdb-sql-transaction-management-scheme Specifically “At certain intervals, if the database is idle, the store manager will merge the updates from the WAL into the primary storage” We perform some heavy updates to the database (we have experimented with putting these in a single transaction or having separate transactions for each, but the behaviour is the same). The updates are basically dropping tables, recreating them and then a COPY INTO. Once the final load has finished, and has been committed, for around 30 seconds we can query the database, but then for a period of several minutes it basically locks up. I wonder if there is any way to control this behaviour? Regards Steve

1 0

stethoscope tool
by Suresh Devarakonda 12 Mar '20

12 Mar '20

Thank you Sjoerd Mullender. I am able to install it. ====== Doing sudo yum install /usr/bin/stethoscope should do it. That will install the package MonetDB-client-tools. On 09/03/2020 16.11, Suresh Devarakonda wrote: > Hi, > > > > I would like to use the stethoscope tool to monitor the performance of > queries. But, I do not find the tool in the MonetDB software that I > installed on my server. Can you help me to get the stethoscope tool. > > > > [root at xxxxxx<https://www.monetdb.org/mailman/listinfo/users-list> ~]# yum list installed |grep -i monet > > MonetDB.x86_64 11.35.9-20191218.el7 @monetdb > > MonetDB-SQL-server5.x86_64 11.35.9-20191218.el7 @monetdb > > MonetDB-client.x86_64 11.35.9-20191218.el7 @monetdb > > MonetDB-client-odbc.x86_64 11.35.9-20191218.el7 @monetdb > > MonetDB-release-epel.noarch 1.3-1.monetdb installed > > MonetDB-stream.x86_64 11.35.9-20191218.el7 @monetdb > > MonetDB5-server.x86_64 11.35.9-20191218.el7 @monetdb > > > > > > > > *Suresh Devarakonda* > > *84.51* <http://www.8451.com/>*° * > > *TDO - Database Support Team* > > *D:*513.632.3692 > > *M*: 859.512.1839 > > *A: *100 W. 5^th 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. > > > > > > > _______________________________________________ > users-list mailing list > users-list at monetdb.org<https://www.monetdb.org/mailman/listinfo/users-list> > https://www.monetdb.org/mailman/listinfo/users-list > ________________________________ * Previous message (by thread): stethoscope tool<https://www.monetdb.org/pipermail/users-list/2020-March/010593.html> * Messages sorted by: [ date ]<https://www.monetdb.org/pipermail/users-list/2020-March/date.html#10594> [ thread ]<https://www.monetdb.org/pipermail/users-list/2020-March/thread.html#10594> [ subject ]<https://www.monetdb.org/pipermail/users-list/2020-March/subject.html#10594> [ author ]<https://www.monetdb.org/pipermail/users-list/2020-March/author.html#10594>

1 0

stethoscope tool
by Suresh Devarakonda 09 Mar '20

09 Mar '20

Hi, I would like to use the stethoscope tool to monitor the performance of queries. But, I do not find the tool in the MonetDB software that I installed on my server. Can you help me to get the stethoscope tool. [root@xxxxxx ~]# yum list installed |grep -i monet MonetDB.x86_64 11.35.9-20191218.el7 @monetdb MonetDB-SQL-server5.x86_64 11.35.9-20191218.el7 @monetdb MonetDB-client.x86_64 11.35.9-20191218.el7 @monetdb MonetDB-client-odbc.x86_64 11.35.9-20191218.el7 @monetdb MonetDB-release-epel.noarch 1.3-1.monetdb installed MonetDB-stream.x86_64 11.35.9-20191218.el7 @monetdb MonetDB5-server.x86_64 11.35.9-20191218.el7 @monetdb [cid:image001.jpg@01D5F602.57C58E70] 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

SP2 R integration
by ALEX OSSIPOV 22 Feb '20

22 Feb '20

I am not able to get R part working in latest SP2 and getting this error: LoaderException:loadLibrary:Loading error failed to open lib rary rapi (from within file '/usr/lib64/monetdb5/lib_rapi.so'): libR.so: cannot open shared object file: No such file or directory I use Centos 7 package MonetDB-R.x86_64_11.35.15-20200217.el7 and wonder if it was build with --enable-R-shlib option. SP1 did not have this problem Thanks Alex

2 2

Announcement: New Nov2019-SP2 Bugfix release of MonetDB suite
by Sjoerd Mullender 21 Feb '20

21 Feb '20

The MonetDB team at MonetDB BV is pleased to announce the Nov2019-SP2 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://www.monetdb.org/downloads/>. Nov 2019-SP2 bugfix release (11.35.15) Bug Fixes * 6805: Using the cascade operator in a drop table statement ends in an exit from the Monetdb shell. * 6807: Median_avg and quantile_avg ignore NULL values * 6815: query with ifthenelse() crashes mserver5 * 6816: Monetdb Crashes on INSERT statement after ALTER statement in another connection * 6817: running analyze on a schema which contains a stream table stops with an error * 6819: functions do not persist

1 0

dropped functions
by ALEX OSSIPOV 18 Feb '20

18 Feb '20

I noticed every time Monetdb service is bounced all created functions disappear from database. Is it a bug or a way functions are handled in MonetDB? Thanks Alex.

2 1

search links are broken
by ALEX OSSIPOV 17 Feb '20

17 Feb '20

All links in Search results point to localhost:8080 instead of site. Can it be fixed? Thanks Alex

2 1

Include additional libraries
by Oxana Dolmatova 13 Feb '20

13 Feb '20

Hello, Could you tell what would be the easiest way to include additional libraries into the compilation process of MonetDB? More specifically I'm interested in calling MKL operations inside BAT Algebra operations. For example, mkl standalone operation of QR decomposition (in file mkl_qr.c all the additional headers are included) is compiled as follows: gcc -c mkl_qr.c gcc -o mkl_qr mkl_qr.o -L${MKLROOT}/lib/intel64 -Wl,--no-as-needed -lmkl_intel_ilp64 -lmkl_gnu_thread -lmkl_core -lgomp -lpthread -lm -ldl What would be the proper Makefile to edit and extend with additional libraries if I want to use MKL in batcalc.c, for example? Thank you and best regards, Oksana

1 0

How can i change plan choice according new constraint
by DEMBELE Simon Pierre 10 Feb '20

10 Feb '20

hello, I want to know if it is possible to change how monetdb choose a plan by adding new contraints. -- Simon

2 2

A bug occurs when query logging is enabled
by Hanfeng Chen 09 Feb '20

09 Feb '20

Hello, The bug can be reproduced on a standard TPC-H database with scale factor 1. A query returns NULL after query logging is enabled. The computer we use has Intel i7-8700K (12 cores) and 32GB RAM. Please see the details below. >> mclient -d tpch1 -t performance Welcome to mclient, the MonetDB/SQL interactive terminal (Nov2019-SP1) Database: MonetDB v11.35.9 (Nov2019-SP1), 'mapi:monetdb://tigger:50000/tpch1' FOLLOW US on https://twitter.com/MonetDB or https://github.com/MonetDB/MonetDB Type \q to quit, \? for a list of available commands auto commit mode: on sql>select sum(l_extendedprice) from lineitem; +------------------------------------------+ | L2 | +==========================================+ | 229577310901.20 | +------------------------------------------+ 1 tuple sql:0.083 opt:1.056 run:5.898 clk:7.763 ms sql>call sys.querylog_enable(); sql>select sum(l_extendedprice) from lineitem; +------------------------------------------+ | L2 | +==========================================+ | null | +------------------------------------------+ 1 tuple sql:0.095 opt:1.312 run:15.920 clk:25.033 ms Hanfeng Chen

2 1

Re: pymonetdb issue
by alexo15＠comcast.net 04 Feb '20

04 Feb '20

That would be great Thank you Pedro Sent from Xfinity Connect App ------ Original Message ------ From: ferreira To: ALEX OSSIPOV, Communication channel for MonetDB users Sent: February 4, 2020 at 1:42 AM Subject: Re: pymonetdb issue Hello Alex, The pymonetdb developer merged a patch with IPv6 support, which will be available at pymonetdb's next release. As for the R client, it is maintained by a volunteer that has not been active in the MonetDB community lately. I can ask him for it. Beset regards, Pedro On 2/1/20 1:28 AM, ALEX OSSIPOV wrote: Hi Pedro I also found R code has the same problem. I hope it will be addressed in upcoming release as well. Thanks Alex On January 7, 2020 at 9:22 AM ferreira <pedro.ferreira(a)monetdbsolutions.com> wrote: Hello Alex, I just looked at pymonetdb source code and indeed it does not support IPv6 yet. I'm going to create an issue to request for it. The reason is because in the past, the MAPI protocol (which MonetDB uses for socket connections) didn't support IPv6, so the clients (Python, Ruby, JDBC, etc.) never had to implemented them. However they can now add this option. Best regards, Pedro On 12/22/19 5:30 AM, ALEX OSSIPOV wrote: Hi All I installed pymonetdb v 1.2.1 and trying to connect to remote server MonetDB v11.35.3 (Nov2019) listening on IPV6 only. I have got next error in python client: >>> mapi_connection.connect(hostname="<server_name>", port=xxxxx, username="monetdb", password="monetdb", database="demo", language="sql", unix_socket=None, connect_timeout=-1) Traceback (most recent call last): File "<stdin>", line 1, in <module> File "/usr/local/lib/python3.7/site-packages/pymonetdb/mapi.py", line 143, in connect self.socket.connect((hostname, port)) socket.gaierror: [Errno -2] Name or service not known >>> My pc client is also using ipv6 to connect to MonetDB server. I also have jdbc connection that works fine. Question: Does it mean pymonetdb does not support ipv6 only environment? What would be the reason if it has nothing to do with protocol? Thanks Alex _______________________________________________ users-list mailing list users-list(a)monetdb.org https://www.monetdb.org/mailman/listinfo/users-list

1 0

pymonetdb issue
by ALEX OSSIPOV 04 Feb '20

04 Feb '20

Hi All I installed pymonetdb v 1.2.1 and trying to connect to remote server MonetDB v11.35.3 (Nov2019) listening on IPV6 only. I have got next error in python client: >>> mapi_connection.connect(hostname="<server_name>", port=xxxxx, username="monetdb", password="monetdb", database="demo", language="sql", unix_socket=None, connect_timeout=-1) Traceback (most recent call last): File "<stdin>", line 1, in <module> File "/usr/local/lib/python3.7/site-packages/pymonetdb/mapi.py", line 143, in connect self.socket.connect((hostname, port)) socket.gaierror: [Errno -2] Name or service not known >>> My pc client is also using ipv6 to connect to MonetDB server. I also have jdbc connection that works fine. Question: Does it mean pymonetdb does not support ipv6 only environment? What would be the reason if it has nothing to do with protocol? Thanks Alex

2 3

Python3 packages for epel
by ALEX OSSIPOV 31 Jan '20

31 Jan '20

I cannot seem find MonetDB-python packages in MonetDB EPEL 7/8 directories. Does it mean it is still work in progress ? Thanks Alex

2 2

Logs on Windows
by Илья Хайбуллин 27 Jan '20

27 Jan '20

Sometimes the MonetDB server just shuts down. I want to find at least some information about what happens before the shutdown. Please tell me how you can turn on logs on Windows or in some other way see what happens before shutting down. MonetDB 5 server 11.35.9 (Nov2019-SP1) (64-bit) Copyright (c) 1993 - July 2008 CWI Copyright (c) August 2008 - 2019 MonetDB B.V., all rights reserved Visit https://www.monetdb.org/ for further information Found 32.0GiB available memory, 8 available cpu cores Libraries: libpcre: 8.41 2017-07-05 openssl: OpenSSL 1.1.1d 10 Sep 2019 libxml2: 2.9.9 Compiled by: monet@WIN2K12R2 (x86_64-pc-winnt) Compilation: cl -GF -W3 -WX -MD -nologo -Ox -Zi -Oi Linking : cl -GF -W3 -WX -MD -nologo -Ox -Zi -Oi

1 0

MonetDB crashing due to out of memory - monetdbd[2247] 1.7 (Aug2018-SP2)
by Suresh Devarakonda 22 Jan '20

22 Jan '20

Thank you Ying and Jenin. I am going to upgrade the MonetDB as you suggested and then go from there. Can you please let me know where can I find the upgrade procedure. Thank you for your help in advance. Suresh Devarakonda 84.51° 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 LinkedIn Instagram Twitter Please consider the environment before printing this email. -----Original Message----- From: users-list <users-list-bounces+suresh.devarakonda=8451.com(a)monetdb.org> On Behalf Of users-list-request(a)monetdb.org Sent: Friday, January 17, 2020 6:00 AM To: users-list(a)monetdb.org Subject: users-list Digest, Vol 89, Issue 6 Send users-list mailing list submissions to users-list(a)monetdb.org To subscribe or unsubscribe via the World Wide Web, visit https://nam12.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.monet… or, via email, send a message with subject or body 'help' to users-list-request(a)monetdb.org You can reach the person managing the list at users-list-owner(a)monetdb.org When replying, please edit your Subject line so it is more specific than "Re: Contents of users-list digest..." Today's Topics: 1. Re: MonetDB crashing due to out of memory - monetdbd[2247] 1.7 (Aug2018-SP2) (Ying Zhang) ---------------------------------------------------------------------- Message: 1 Date: Thu, 16 Jan 2020 12:01:27 +0100 From: Ying Zhang <Y.Zhang(a)cwi.nl> To: Communication channel for MonetDB users <users-list(a)monetdb.org> Subject: Re: MonetDB crashing due to out of memory - monetdbd[2247] 1.7 (Aug2018-SP2) Message-ID: <E6A3FCCA-8023-4EBD-AF7A-CF89F615B2D8(a)cwi.nl> Content-Type: text/plain; charset=utf-8 Hai Suresh, First of all, I?d recommend you to upgrade your MonetDB installation to the most recent one, i.e. Nov2019-SP1. There are many improvements related to your problem since Aug2018-SP2. Also, the version you?re using is no longer supported by the open-source team. Please let us know if upgrade helps. But below, a bit more info. inlined. > On 15 Jan 2020, at 23:44, Suresh Devarakonda <Suresh.Devarakonda(a)8451.com> wrote: > > Hi, > > The Moentdb is crashing every couple of weeks due to out of memory in our environment. Did it really crash, or did MonetDB give error messages and stop executing the query? Crash always indicates a problem in MonetDB, but if it?s the second case, it?s expected. > What I have noticed that MonetDB is eating memory and not releasing it at all once we brought up the MonetDB server. I am not sure if this is how MonetDB works or is it a bug. MonetDB is supposed to free up the memory when it no longer use it, which often happens towards the end of a query execution. For further investigation, we need more information after you have upgraded to Nov2019-SP1. In general, we?ll need information with which we can reproduce the problem, e.g. data + queries. > This is running on monetdbd[2247] 1.7 (Aug2018-SP2) version. Also wondering is there way to limit the memory usage of MonetDB. Please beware that depending on your data sizes and (concurrent) queries, MonetDB might run faster into out-of-memory problem, if you limit the amount of memory it may use, How about putting MonetDB in a VM? There are also some undocumented kernel configurations for this, which we don?t recommend to our users lightly? > > Can you guys help me to resolve this issue. If you need professional support, please contact the supporting company MonetDB Solutions. Regards, Jennie > > [root@xxx ~]# monetdb status > name state health remarks > INVPRDB R 6h 67% 3d mapi:monetdb://xxxxx:50000/INVPRDB > FSPPRDB R 6h 50% 6w mapi:monetdb://xxxxx:50000/FSPPRDB > [root@xxxxx ~]# free -g --- Current utilization > total used free shared buff/cache available > Mem: 31 0 22 0 7 30 > Swap: 3 0 3 > > > <image003.png> > > > > > > <image002.jpg> > Suresh Devarakonda > 84.51? > 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 LinkedIn Instagram Twitter > Please consider the environment before printing this email. > > > _______________________________________________ > users-list mailing list > users-list(a)monetdb.org > https://nam12.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww. > monetdb.org%2Fmailman%2Flistinfo%2Fusers-list&data=02%7C01%7Csures > h.devarakonda%408451.com%7Cae946815e409491cb83d08d79b3c9c2c%7C5f9dc6bd > f38a454a864cc803691193c5%7C0%7C0%7C637148556952386018&sdata=3JmWyH > MNaeMNrBp8vX%2FAYES%2By%2FDqNHqGmE%2FKX%2F%2F4m%2Bs%3D&reserved=0 ------------------------------ Subject: Digest Footer _______________________________________________ users-list mailing list users-list(a)monetdb.org https://nam12.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.monet… ------------------------------ End of users-list Digest, Vol 89, Issue 6 *****************************************

2 2

Re: embedded python in latest release
by alexo15＠comcast.net 21 Jan '20

21 Jan '20

That was it. I was missing python packages Thank you Panos ------ Original Message ------ From: Panagiotis Koutsourakis To: users-list(a)monetdb.org Sent: January 21, 2020 at 7:54 AM Subject: Re: embedded python in latest release Hello Alex, make sure that you have the MonetDB-python or MonetDB-python3 packages installed: dnf install MonetDB-python or dnf install MonetDB-python3 Also you will need to notify the MonetDB server that you want to start with embedded python support. If you are starting the server using the MonetDB daemon, you need to set the relevant property for your database: monetdb set embedpy=true <database> or monetdb set embedpy3=true <database> If you are starting the server by hand, then mserver5 --dbpath=/path/to/database --set embedded_py=2 or mserver5 --dbpath=/path/to/database --set embedded_py=3 Best regards, Panos. On 1/21/20 5:11 AM, ALEX OSSIPOV wrote: Roberto I have all mentioned packages installed. I do not have any issues with R though. Centos based instance also works fine. Thanks Alex On January 20, 2020 at 3:49 AM Roberto Cornacchia <roberto.cornacchia(a)gmail.com> wrote: Alex, Do you have packages python3-devel python3-numpy (python2-devel python2-numpy for python 2) installed in your Fedora system? Yes, you can import what you like in the body of your function, as long as the package is installed in your system. Roberto On Sun, 19 Jan 2020 at 00:12, ALEX OSSIPOV < alexo15(a)comcast.net mailto:alexo15@comcast.net > wrote: I am having problem enabling embedded python in latest realease, MonetDB 5 server v11.35.9 (Nov2019-SP1) My 64bit Fedora environment has multiple versions of both python 2 and 3 wih 32 and 64 bit libs. MonetDB was installed following https://www.monetdb.org/downloads/Fedora/ with additional SELinux. I tried random_integers function from documentation with both embedpy=true and embedpy3=true separately but getting the same type of error: SQL Error [22000]: TypeException:user.s10_0[9]:'pyapi3.eval' undefined in: X_10:bat[:int] := pyapi3.eval(0x559cb0440270:ptr, "{ \n return numpy.random.randint(low, high, size=(amount,))\n}\n;":str, X_7:int, X_8:int, X_9:int); Both py2 and py3 versions work fine on my computer. How do I properly point to a specific python version to be used by MonetDB? Are there any specific env variables that should be set prior to installation/db creation? I tried to define PYTHONPATH but it did not help. Aside question: Can additional modules be imported in function body for PYTHON type functions or does it only work with numpy? Thanks Alex _______________________________________________ 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 https://www.monetdb.org/mailman/listinfo/users-list _______________________________________________ users-list mailing list users-list(a)monetdb.org https://www.monetdb.org/mailman/listinfo/users-list

1 0

embedded python in latest release
by ALEX OSSIPOV 21 Jan '20

21 Jan '20

I am having problem enabling embedded python in latest realease, MonetDB 5 server v11.35.9 (Nov2019-SP1) My 64bit Fedora environment has multiple versions of both python 2 and 3 wih 32 and 64 bit libs. MonetDB was installed following https://www.monetdb.org/downloads/Fedora/ with additional SELinux. I tried random_integers function from documentation with both embedpy=true and embedpy3=true separately but getting the same type of error: SQL Error [22000]: TypeException:user.s10_0[9]:'pyapi3.eval' undefined in: X_10:bat[:int] := pyapi3.eval(0x559cb0440270:ptr, "{ \n return numpy.random.randint(low, high, size=(amount,))\n}\n;":str, X_7:int, X_8:int, X_9:int); Both py2 and py3 versions work fine on my computer. How do I properly point to a specific python version to be used by MonetDB? Are there any specific env variables that should be set prior to installation/db creation? I tried to define PYTHONPATH but it did not help. Aside question: Can additional modules be imported in function body for PYTHON type functions or does it only work with numpy? Thanks Alex

3 3

MonetDB crashing due to out of memory - monetdbd[2247] 1.7 (Aug2018-SP2)
by Suresh Devarakonda 16 Jan '20

16 Jan '20

Hi, The Moentdb is crashing every couple of weeks due to out of memory in our environment. What I have noticed that MonetDB is eating memory and not releasing it at all once we brought up the MonetDB server. I am not sure if this is how MonetDB works or is it a bug. This is running on monetdbd[2247] 1.7 (Aug2018-SP2) version. Also wondering is there way to limit the memory usage of MonetDB. Can you guys help me to resolve this issue. [root@xxx ~]# monetdb status name state health remarks INVPRDB R 6h 67% 3d mapi:monetdb://xxxxx:50000/INVPRDB FSPPRDB R 6h 50% 6w mapi:monetdb://xxxxx:50000/FSPPRDB [root@xxxxx ~]# free -g --- Current utilization total used free shared buff/cache available Mem: 31 0 22 0 7 30 Swap: 3 0 3 [cid:image003.png@01D5CBCA.6268DF10] [cid:image002.jpg@01D3E7B3.57185110] 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

Monetdb crashed issue
by Kalaiarasi Gowtham 14 Jan '20

14 Jan '20

Hi, Currently, we are trying to start the database created in monetdb using the following command monetdb start <dbname> starting database 'dbname'... FAILED start: starting '<dbname>' failed: database '<dbname>' has crashed after starting, manual intervention needed, check monetdbd's logfile (merovingian.log) for details Getting logs as follows [root@localhost mydbfarm]# tail -f merovingian.log 2020-01-14 14:56:20 MSG merovingian[19527]: database '<dbname>' (19757) has exited with exit status 1 2020-01-14 14:56:20 ERR control[19527]: !monetdbd: an internal error has occurred 'database '<dbname>' has crashed after starting, manual intervention needed, check monetdbd's logfile (merovingian.log) for details' 2020-01-14 14:56:25 ERR merovingian[19527]: client error: database '<dbname>' has crashed after starting, manual intervention needed, check monetdbd's logfile (merovingian.log) for details Please,help us to solve this issue. Thanks Kalai

2 1