- users-list - monetdb.org

New MonetDB JDBC driver v3.2 and JdbcClient released
by Martin van Dinther 03 Feb '22

03 Feb '22

The MonetDB team at MonetDB BV is pleased to announce a feature release of the MonetDB JDBC driver (monetdb-jdbc-3.2.jre8.jar) and JdbcClient CLI program (jdbcclient.jre8.jar). The new release adds the capability to use the SQL: COPY ... INTO ... ON CLIENT statements via Java from the client side. This allows users and programmers to use the much faster COPY statements to load large data sets into MonetDB from a local client side CSV file or stream. The JDBC MonetConnection API has been extended with methods to register and use Upload and Download Handlers to stream the data from the Java client application to the server and/or from the server to the Java client. See <https://www.monetdb.org/downloads/Java/onclient.txt> for details. The new functionality has also been added to the JdbcClient CLI program, so you can now also use COPY ... INTO ... ON CLIENT statements from this CLI program, provided you have started it with a --csvdir argument. The CSV data files may also be gzip compressed (file name ends with: .gz). Besides this new functionality we also improved both the JDBC driver and JdbcClient to work optimal with the new MonetDB Jan2022 server release. For a list of all changes please read: <https://www.monetdb.org/downloads/Java/ChangeLog>. We recommend all users of MonetDB JDBC driver and/or JdbcClient program to upgrade to this new release. Especially if you use the new MonetDB Jan2022 server release, you should use the new jar files. The download location is as usual: <https://www.monetdb.org/downloads/Java/>. For information and tips on using the MonetDB JDBC driver, read: <https://www.monetdb.org/downloads/Java/release.txt>. Reminder: as of release monetdb-jdbc-3.0.jre8.jar the MonetDB JDBC Driver class name has been changed to: org.monetdb.jdbc.MonetDriver It used to be nl.cwi.monetdb.jdbc.MonetDriver which is marked as deprecated. Please use the new driver class name asap in your configuration files/scripts and/or Java code. The old driver class name is currently still included in the jdbc jar file but only to ease the transition for existing deployments. The old driver class will be removed in a future release of the MonetDB JDBC driver.

1 0

Announcement: New Jan2022 Feature release of MonetDB suite
by Sjoerd Mullender 24 Jan '22

24 Jan '22

The MonetDB team at MonetDB BV is pleased to announce the Jan2022 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/>. ## Jan2022 Feature Release (11.43.5) ### - A couple of concurrency issues have been fixed. ### Windows Packaging - We now build Windows binaries using Visual Studio 2022. ### Client Package - A new output formatting mode was added to mclient. Use -fcsv-noquote to produce a CSV (comma-separated values) output where the quote characters have not been escapes. This can be useful when producing a single column string output that should be saved as is, e.g. when using the sys.dump_database() function. ### MonetDB Common - Implement string imprints (strimps for short) a pre-filter structure for strings in order to accelerate LIKE queries. If a strimp exists for a specific string column the strings are pre-filtered, rejecting strings that cannot possibly match, before the more expensive and accurate matching algorithms run. - On Windows, files and directories we create now get the attribute FILE_ATTIBUTE_NOT_CONTENT_INDEXED, meaning that they should not be indexed by indexing or search services. - Many (most) low level functions that could take a long time (such as BATjoin) can now be aborted with a timeout. When the function takes too long, the function will fail, and hence the whole SQL query will fail. - At some point in the past, string heaps were created where the hash value of the string was stored in the heap before the string. This hasn't been used in a long time. Now the code that could still read those old heaps has been removed. Bats that used the old format are converted automatically. - Some small interface changes to the atom functions: the atomPut function now returns (var_t) -1 on error instead of 0; the atomHeap function now returns success or failure as a gdk_return value. ### General - Reliance on the OpenSSL library has been removed. OpenSSL was used for the hash algorithms it contained (e.g. SHA512 and RIPEMD160) and for producing random numbers. The hash functions have been replaced by the original published functions, and random numbers are generated using system-specific random sources (i.e. not simply pseudo-random number generators). ### MonetDB5 Server - The storage cleanup in the 11.41.5 (Jul2021) release made the OLTP optimizer pipeline obsolete, thus it was removed. ### SQL Frontend - Add string imprints to the existing imprints index creation syntax. On string column "col" of a table "tbl" marked read only ("ALTER TABLE tbl SET READ ONLY") the user can create a string imprint using the syntax: "CREATE IMPRINTS INDEX index_name ON tbl(col);". - With the storage cleanup in the 11.41.5 (Jul2021) release, the ANALYZE statement was updated to accomodate those changes. The SAMPLE parameter is now ignored because ANALYZE generated statistics used by relational operators, are required to be precise. - In order to mitigate the I/O required to update the 'statistics' table, this table is no longer persisted. Alternately, it was changed into a computed view every time when queried. The 'stamp' and 'sample' fields were removed for the aforementioned reasons. The 'schema', 'table' and 'column' fields were added for convenience. - In previous versions there was no check that the INCREMENT BY value of a SEQUENCE was not zero. During the automatic upgrade of a database, INCREMENT BY values that are zero are set to one. - The method to compute the 'side_effect' effect property was changed for SQL functions defined in the backend engine (eg. ``CREATE FUNCTION ... EXTERNAL NAME "module"."function"''). It was changed from being computed by the SQL layer to the backend engine itself. As a consequence, the computed 'side_effect' value may be different, thus bringing incompatibilities. After an upgrade, if a 'side_effect' incompatibility arises, either the 'side_effect' value in the backend should be changed or the function should be re-created in SQL. - Removed deprecated system view sys.systemfunctions. It was marked as deprecated from release Apr2019 (11.33.3). Use query: select id as function_id from sys.functions where system; to get the same data as the old view. - Extended SQL system catalog with lookup table sys.fkey_actions and view sys.fkeys to provide user friendly querying of existing foreign keys and their ON UPDATE and ON DELETE referential action specifications. - Many improvements were done for REMOTE table plans. As a consequence, master or slave servers from this feature release are not compatible with older releases. - The view sys.ids has been changed to give more information about the objects in the system. In particular, there is an extra column added at the end that indicates whether the object is a system object. - The example modules opt_sql_append and udf are no longer loaded by default and no longer part of the binary release. If installed, they can be loaded using the --loadmodule option. - The built-in SQL functions to produce a dump that were added as a proof-of-concept in the previous release have been improved and are now usable. Use the query ``SELECT stmt FROM sys.dump_database(FALSE) ORDER BY o'' to produce a dump. The dump code built into mclient and msqldump is probably still more efficient, though. - The sys.epoch function has always been confusing. There are two versions, one with an INTEGER argument, and one with a BIGINT argument. The former accepted values as seconds, whereas the latter expected milliseconds. Also, the construct EXTRACT(EPOCH FROM value) returns a BIGINT with millisecond precision. This has now been overhauled. There is no longer a function sys.epoch with BIGINT argument, but instead there is a new function sys.epoch with DECIMAL(18,3) argument. The argument is seconds, but with 3 decimals, it provides millisecond accuracy. Also the EXTRACT(EPOCH FROM value) now returns a DECIMAL(18,3), again seconds with 3 decimals giving millisecond accuracy. Note that the internal, binary representation of DECIMAL(18,3) interpreted as seconds with 3 decimals and BIGINT with millisecond precision is exactly the same. ### Merovingian - Disabled logging into merovingian.log of next info message types: - proxying client <host>:<port> for database '<dbname>' to <url> - target connection is on local UNIX domain socket, passing on filedescriptor instead of proxying These messages were written to the log file at each connection. In most cases this information is not used. The disabling reduces the log file size. - Removed the deprecated monetdb commands `profilerstart` and `profilerstop`. ### Bug Fixes - [7168](https://github.com/MonetDB/MonetDB/issues/7168): Loosing the documentation - [7180](https://github.com/MonetDB/MonetDB/issues/7180): GROUP BY-subquery crashes MonetDb - [7182](https://github.com/MonetDB/MonetDB/issues/7182): Queries against sys.querylog_catalog, sys.querylog_calls or sys.querylog_history fail after restore of a db created using call sys.hot_snapshot(R'\path\file.tar'); - [7201](https://github.com/MonetDB/MonetDB/issues/7201): Selection of a subquery with a LEFT JOIN returns the wrong result set - [7202](https://github.com/MonetDB/MonetDB/issues/7202): DISTINCT does not work when sorting by additional columns - [7215](https://github.com/MonetDB/MonetDB/issues/7215): ODBC Driver SQLStatistics returns duplicate rows/rows for other tables

1 0

BATsetaccess() returns NULL
by Roberto Cornacchia 30 Dec '21

30 Dec '21

Hi there, I'm moving some code to Jul2021, and I got a bit stuck on this. The issue can be simplified to this (make a copy and, append another bat, and make the result read-only): bn = COLcopy(b, b->ttype, true, TRANSIENT); ret = BATappend(bn,u, s, force ? *force : false); bn = BATsetaccess(bn, BAT_READ); The only superficial difference from Oct2020 is that now BATaccess returns the modified bat instead of a gdk_return. The issue I see is that BATsetaccess() returns NULL, due to m1 == STORE_INVALID. Am I using this wrong? Any help is appreciated. Cheers, Roberto

1 1

SELinux problems
by Arjen P. de Vries 29 Dec '21

29 Dec '21

Hi all, Not really a bug report because I did not manage to figure out the cause. However, after upgrading from FC31 to FC32 I could not login any more, due to SELinux problems. Auto-relabeling did not work, nothing really... ... until I did dnf uninstall MonetDB-selinux. I came to this point because trying to give systemd services the correct labels with restorecon failed with an error referencing a monetdb specific file. I do not have the details unfortunately, but if you get problems, beware that MonetDB SELinux package and systemd may interfere in some way beyond my knowledge of these services. Best regards, Arjen PS: Some output from logs: sudo ausearch -c monetdb -m AVC,SELINUX_ERR [..] ---- time->Sat May 2 20:57:01 2020 type=AVC msg=audit(1588445821.693:203): avc: denied { open } for pid=1232 comm="monetdbd" path="/etc/resolv.conf" dev="dm-0" ino=3409775 scontext=system_u:system_r:init_t:s0 tcontext=system_u:object_r:default_t:s0 tclass=file permissive=1 ---- time->Sat May 2 21:12:56 2020 type=AVC msg=audit(1588446776.043:1194): avc: denied { execute } for pid=2861 comm="(monetdbd)" name="monetdbd" dev="dm-0" ino=2147256 scontext=system_u:system_r:init_t:s0 tcontext=system_u:object_r:unlabeled_t:s0 tclass=file permissive=1 trawcon="unconfined_u:object_r:monetdbd_exec_t:s0" ---- time->Sat May 2 21:12:56 2020 type=AVC msg=audit(1588446776.043:1195): avc: denied { execute_no_trans } for pid=2861 comm="(monetdbd)" path="/usr/bin/monetdbd" dev="dm-0" ino=2147256 scontext=system_u:system_r:init_t:s0 tcontext=system_u:object_r:unlabeled_t:s0 tclass=file permissive=1 trawcon="unconfined_u:object_r:monetdbd_exec_t:s0" ---- time->Sat May 2 21:12:56 2020 type=AVC msg=audit(1588446776.044:1196): avc: denied { map } for pid=2861 comm="monetdbd" path="/usr/bin/monetdbd" dev="dm-0" ino=2147256 scontext=system_u:system_r:init_t:s0 tcontext=system_u:object_r:unlabeled_t:s0 tclass=file permissive=1 trawcon="unconfined_u:object_r:monetdbd_exec_t:s0" ---- time->Sat May 2 21:12:56 2020 type=AVC msg=audit(1588446776.714:1197): avc: denied { remove_name } for pid=1232 comm="monetdbd" name="merovingian.pid" dev="tmpfs" ino=34369 scontext=system_u:system_r:init_t:s0 tcontext=system_u:object_r:unlabeled_t:s0 tclass=dir permissive=1 trawcon="system_u:object_r:monetdbd_var_run_t:s0" ---- time->Sat May 2 21:12:56 2020 type=AVC msg=audit(1588446776.714:1198): avc: denied { unlink } for pid=1232 comm="monetdbd" name="merovingian.pid" dev="tmpfs" ino=34369 scontext=system_u:system_r:init_t:s0 tcontext=system_u:object_r:unlabeled_t:s0 tclass=file permissive=1 ---- time->Sat May 2 21:12:56 2020 type=AVC msg=audit(1588446776.714:1199): avc: denied { write } for pid=1232 comm="monetdbd" name=".merovingian_lock" dev="dm-0" ino=5899443 scontext=system_u:system_r:init_t:s0 tcontext=system_u:object_r:unlabeled_t:s0 tclass=file permissive=1 trawcon="system_u:object_r:monetdbd_lock_t:s0" ---- time->Sat May 2 21:13:15 2020 type=AVC msg=audit(1588446795.214:1209): avc: denied { read } for pid=2925 comm="(monetdbd)" name="passwd" dev="dm-0" ino=524514 scontext=system_u:system_r:init_t:s0 tcontext=system_u:object_r:var_t:s0 tclass=file permissive=1 ---- time->Sat May 2 21:13:15 2020 type=AVC msg=audit(1588446795.214:1210): avc: denied { open } for pid=2925 comm="(monetdbd)" path="/var/lib/sss/mc/passwd" dev="dm-0" ino=524514 scontext=system_u:system_r:init_t:s0 tcontext=system_u:object_r:var_t:s0 tclass=file permissive=1 ---- time->Sat May 2 21:13:15 2020 type=AVC msg=audit(1588446795.214:1211): avc: denied { map } for pid=2925 comm="(monetdbd)" path="/var/lib/sss/mc/passwd" dev="dm-0" ino=524514 scontext=system_u:system_r:init_t:s0 tcontext=system_u:object_r:var_t:s0 tclass=file permissive=1 ---- time->Sat May 2 21:14:24 2020 type=AVC msg=audit(1588446864.487:1281): avc: denied { read } for pid=3072 comm="(monetdbd)" name="passwd" dev="dm-0" ino=524514 scontext=system_u:system_r:init_t:s0 tcontext=system_u:object_r:var_t:s0 tclass=file permissive=1 ---- time->Sat May 2 21:14:24 2020 type=AVC msg=audit(1588446864.487:1282): avc: denied { open } for pid=3072 comm="(monetdbd)" path="/var/lib/sss/mc/passwd" dev="dm-0" ino=524514 scontext=system_u:system_r:init_t:s0 tcontext=system_u:object_r:var_t:s0 tclass=file permissive=1 ---- time->Sat May 2 21:14:24 2020 type=AVC msg=audit(1588446864.487:1283): avc: denied { map } for pid=3072 comm="(monetdbd)" path="/var/lib/sss/mc/passwd" dev="dm-0" ino=524514 scontext=system_u:system_r:init_t:s0 tcontext=system_u:object_r:var_t:s0 tclass=file permissive=1 -- ==================================================================== ICIS, office M1.00.05 Radboud University Mercator 1 Faculty of Science Toernooiveld 212 arjen(a)cs.ru.nl NL-6525 EC Nijmegen, The Netherlands +31-(0)24-365 2354 ===================== http://www.informagus.nl/ ==================== -- ==================================================================== ICIS, office M1.00.05 Radboud University Mercator 1 Faculty of Science Toernooiveld 212 arjen(a)cs.ru.nl NL-6525 EC Nijmegen, The Netherlands +31-(0)24-365 2354 ===================== http://www.informagus.nl/ ====================

2 6

Announcement: New Jul2021-SP2 Bugfix release of MonetDB suite
by Sjoerd Mullender 21 Dec '21

21 Dec '21

The MonetDB team at MonetDB BV is pleased to announce the Jul2021-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/>. Jul2021-SP2 Bugfix Release (11.41.13) Client Package * Dumping the database now also dumps the read-only and insert-only states of tables. MonetDB Common * Sometimes when the server was restarted, it wouldn't start anymore due to an error from BBPcheckbats. We finally found and fixed a (hopefully "the") cause of this problem. SQL Frontend * Number parsing for SQL was fixed. If a number was immediately followed by letters (i.e. without a space), the number was accepted and the alphanumeric string starting with the letter was interpreted as an alias (if aliases were allowed in that position). Bug Fixes * 7163: Multiple sql.mvc() invocations in the same query * 7167: sys.shutdown() problems * 7184: Insert into query blocks all other queries * 7185: GROUPING SETS on groups with aliases provided in the SELECT returns empty result * 7186: data files created with COPY SELECT .. INTO 'file.csv' fail to be loaded using COPY INTO .. FROM 'file.csv' when double quoted string data contains the field values delimiter character * 7191: [MonetDBe] monetdbe_cleanup_statement() with bound NULLs on variable-sized types bug * 7196: BATproject2: does not match always * 7198: Suboptimal query plan for query containing JSON access filter and two negative string comparisons * 7200: PRIMARY KEY unique constraint is violated with concurrent inserts * 7206: Python UDF fails when returning an empty table as a dictionary

1 0

Archiving of MonetDBLite repositories
by Panagiotis Koutsourakis 15 Dec '21

15 Dec '21

Hello all, We would like to announce that MonetDBLite has been replaced by MonetDB/e. On January we will archive the following repositories: https://github.com/MonetDB/MonetDBLite-C https://github.com/MonetDB/MonetDBLite-R https://github.com/MonetDB/MonetDBLite-Python https://github.com/MonetDB/MonetDBLite-Java We've moved the best bits of MonetDBLite to MonetDB/e (i.e. MonetDB embedded), which we continue to develop and maintain. MonetDB/e is developed in the same codebase as the MonetDB server. This means that all new features and bug fixes land automatically in MonetDB/e. You can find bindings for Python (https://github.com/MonetDBSolutions/MonetDBe-Python) and Java (https://github.com/MonetDBSolutions/MonetDBe-Java) and we are working on bindings for R. You can also find a repository with code examples (https://github.com/MonetDBSolutions/monetdbe-examples)

1 0

Monetdbe and functions
by Ioannis Foufoulas 03 Dec '21

03 Dec '21

Hi, this is a question for monetdbe, I am trying to use create function with monetdbe, I ‘m getting a 'pyapi3.eval' library error in: Embedded Python 3 has not been enabled. Start server with --set embedded_py=3, however I am running the embedded version and I don’t know how to enable pyapi. Is there any configuration file, or any other workaround to enable these extensions? Thanks

1 0

Loader functions with temporary tables
by Ioannis Foufoulas 02 Nov '21

02 Nov '21

Hello, It seems that python loader functions do not work with temporary tables. Is it something that will be supported? Otherwise, is there any workaround to create a temporary table using a UDF that does not require a static definition of returned schema? Yannis

1 0

Issue with JIT C udfs
by Ioannis Foufoulas 05 Oct '21

05 Oct '21

Hi, I have an issue with C udfs. While most of the examples at https://www.monetdb.org/blog/JIT_C_C%252B%252B_UDFs_in_MonetDB run without problem, the example with SNAPPY does not run. It seems that linking, and includes work but if I call a function from the external library it fails with “write error on stream” The same happens also with other external libraries. My version is v11.37.11. Is there any idea for this issue? Yannis

2 1

Announcement: New Jul2021-SP1 Bugfix release of MonetDB suite
by Sjoerd Mullender 05 Oct '21

05 Oct '21

The MonetDB team at MonetDB BV is pleased to announce the Jul2021-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/>. Jul2021-SP1 Bugfix Release (11.41.11) MonetDB Common * Some deadlock and race condition issues were fixed. * Handling of the list of free bats has been improved, leading to less thread contention. * A problem was fixed where the server wouldn't start with a message from BBPcheckbats about files being too small. The issue was not that the file was too small, but that BBPcheckbats was looking at the wrong file. * An issue was fixed where a "short read" error was produced when memory was getting tight. * When appending to a string bat, we made an optimization where the string heap was sometimes copied completely to avoid having to insert strings individually. This copying was still done too eagerly, so now the string heap is copied less frequently. In particular, when appending to an empty bat, the string heap is now not always copied whole. SQL Frontend * If the server has been idle for a while with no active clients, the write-ahead log is now rotated. * A problem was fixed where files belonging to bats that had been deleted internally were not cleaned up, leading to a growing database (dbfarm) directory. * A leak was fixed where extra bats were created but never cleaned up, each taking up several kilobytes of memory. * [This feature was already released in Jul2021 (11.41.5), but the ChangeLog was missing] Grant indirect privileges. With "GRANT SELECT ON <my_view> TO <another_user>" and "GRANT EXECUTE ON FUNCTION <my_func> TO <another_user>", one can grant access to "my_view" and "my_func" to another user who does not have access to the underlying database objects (e.g. tables, views) used in "my_view" and "my_func". The grantee will only be able to access data revealed by "my_view" or conduct operations provided by "my_func". * Improved error reporting in COPY INTO by giving the line number (starting with one) for the row in which an error was found. In particular, the sys.rejects() table now lists the line number of the CSV file on which the record started in which an error was found. Bug Fixes * 7140: SQL Query Plan Non Optimal with View * 7162: Extend sys.var_values table * 7165: `JOINIDX: missing '.'` when running distributed join query on merged remote tables * 7172: Unexpected query result with merge tables * 7173: If truncate is in transaction then after restart of MonetDB the table is empty * 7178: Remote Table Throws Error - createExceptionInternal: !ERROR: SQLException:RAstatement2:42000!The number of projections don't match between the generated plan and the expected one: 1 != 1200

1 0