- users-list - monetdb.org

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

DBSeeder Version 3.0.3
by Walter Weinmann 12 Sep '21

12 Sep '21

We are pleased to release version 3.0.3 of DBSeeder ( https://github.com/KonnexionsGmbH/db_seeder). DBSeeder also supports the latest version of MonetDB available on Docker Hub (DBMS Jul2021). [image: Perf_Snap_3.0.3_win10.png]

1 0

Announcement: New Jul2021 Feature release of MonetDB suite
by Sjoerd Mullender 09 Aug '21

09 Aug '21

The MonetDB team at MonetDB BV is pleased to announce the Jul2021 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/>. Jul2021 Feature Release (11.41.5) Client Package * The MonetDB stethoscope has been removed. There is now a separate package available with PIP (monetdb_stethoscope) or as an RPM or DEB package (stethoscope) from the monetdb.org repository. Mapi Library * Add optional MAPI header field which can be used to immediately set reply size, autocommit, time zone and some other options, see mapi.h. This makes client connection setup faster. Support has been added to mapilib, pymonetdb and the jdbc driver. ODBC Driver * A typo that made the SQLSpecialColumns function unusable was fixed. MonetDB Common * A bug in the grouping code has been fixed. * Hash indexes are no longer maintained at all cost: if the number of distinct values is too small compared to the total number of values, the index is dropped instead of being maintained during updates. * A new type, called msk, was introduced. This is a bit mask type. In a bat with type msk, each row occupies a single bit, so 8 rows are stored in a single byte. There is no NULL value for this type. * The function of the BAT iterator (type BATiter, function bat_iterator) has been expanded. The iterator now contains more information about the BAT, and it contains a pointer to the heaps (theap and tvheap) that are stable, at least in the sense that they will remain available even when parallel threads update the BAT and cause those heaps to grow (and therefore possibly move in memory). A call to bat_iterator must now be accompanied by a call to bat_iterator_end. * Implemented function BUNreplacemultiincr to replace multiple values in a BAT in one go, starting at a given position. * Implemented new function BUNreplacemulti to replace multiple values in a BAT in one go, at the given positions. * Removed function BUNinplace, just use BUNreplace, and check whether the BAT argument is of type TYPE_void before calling if you don't want to materialize. * Implemented a function BUNappendmulti which appends an array of values to a BAT. It is a generalization of the function BUNappend. * Changed the interface of the atom read function. It now requires an extra pointer to a size_t value that gives the current size of the destination buffer, and when that buffer is too small, it receives the size of the reallocated buffer that is large enough. In any case, and as before, the return value is a pointer to the destination buffer. * Environment variables (sys.env()) must be UTF-8, but since they can contain file names which may not be UTF-8, there is now a mechanism to store the original values outside of sys.env() and store %-escaped (similar to URL escaping) values in the environment. The key must still be UTF-8. * We now save the location of the min and max values when known. MonetDB5 Server * When using the --in-memory option, mserver5 will run completely in memory, i.e. not create a database on disk. The server can still be connected to using the name of the in-memory database. This name is "in-memory". * By using the option "--dbextra=in-memory", mserver5 can be instructed to keep transient BATs completely in memory. SQL Frontend * The system view sys.ids has been updated to include some more system IDs. * The sys.storage() function now only returns meta data, i.e. data that can be calculated without access to the column contents. * Since STREAM tables support is removed, left over STREAM tables are dropped from the catalog. * Fix a warning emitted by some implementations of the tar(1) command when unpacking hot snapshot files. * support reading the concatenation of compressed files as a single compressed file. * COPY BINARY overhaul. Allow control over binary endianness using COPY [ (BIG | LITTLE | NATIVE) ENDIAN] BINARY syntax. Defaults to NATIVE. Strings are now \0 terminated rather than \n. Support for BOOL, TINYINT, SMALLINT, INT, LARGEINT, HUGEINT, with their respective "INTMIN" values as the NULL representation; 32 and 64 bit FLOAT/REAL, with NaN as the NULL representation; VARCHAR/TEXT, JSON and URL with \x80 as the NULL representation; UUID as fixed width 16 byte binary values, with (by default) all zeroes as the NULL representation; temporal type structs as defined in copybinary.h with any invalid value as the NULL representation. * In the Jul2021 release the storage and transaction layers have undergone major changes. The goal of these changes is robust performance under inserts/updates and deletes and lowering the transaction startup costs, allowing faster (small) queries. Where the old transaction layer duplicated a lot of data structures during startup, the new layer shares the same tree. Using object timestamps the isolation of object is guaranteed. On the storage side the timestamps indicate whether a row is visible (deleted or valid), to a transaction as well. The changes also give some slight changes on the perceived transactional behavior. The new implementation uses shared structures among all transactions, which do not allow multiple changes of the same object. And we then follow the principle of the first writer wins, i.e., if a transaction creates a table with name 'table_name', and concurrently one other transaction does the same the later of the two will fail with a concurrency conflict error message (even if the first writer never commits). We expect most users not to notice this change, as such schema changes aren't usually done concurrently. * There is now a function sys.current_sessionid() to return the session ID of the current session. This ID corresponds with the sessionid in the sys.queue() result. * Merge statements could not produce correct results on complex join conditions, so a renovation was made. As a consequence, subqueries now have to be disabled on merge join conditions. * preserve in-query comments * Use of CTEs inside UPDATE and DELETE statements are now more restrict. Previously they could be used without any extra specification in the query (eg. with "v1"("c1") as (...) delete from "t" where "t"."c1" = "v1"."c1"), however this was not conformant with the SQL standard. In order to use them, they must be specified in the FROM clause in UPDATE statements or inside a subquery. * Added 'schema path' property to user, specifying a list of schemas to be searched on to find SQL objects such as tables and functions. The scoping rules have been updated to support this feature and it now finds SQL objects in the following order: 1. On occasions with multiple tables (e.g. add foreign key constraint, add table to a merge table), the child will be searched on the parent's schema. 2. For tables only, declared tables on the stack. 3. 'tmp' schema if not listed on the 'schema path'. 4. Session's current schema. 5. Each schema from the 'schema path' in order. 6. 'sys' schema if not listed on the 'schema path'. Whenever the full path is specified, ie "schema"."object", no search will be made besides on the explicit schema. * To update the schema path ALTER USER x SCHEMA PATH y; statement was added. [SCHEMA PATH string] syntax was added to the CREATE USER statement. The schema path must be a single string where each schema must be between double quotes and separated with a single comma, e.g. '"sch1","sch2"' For every created user, if the schema path is not given, '"sys"' will be the default schema path. * Changes in the schema path won't be reflected on currently connected users, therefore they have to re-connect to see the change. Non existent schemas on the path will be ignored. * Leftover STREAM table definition from Datacell extension was removed from the parser. They had no effect anymore. Merovingian * Deprecate `profilerstart` and `profilerstop` commands. Since stethoscope is a separate project (https://github.com/MonetDBSolutions/monetdb-pystethoscope) the installation directory is not standard anymore. `profilerstart` and `profilerstop` commands assume that the stethoscope executable is in the same directory as `mserver5`. This is no longer necessarily true since stethoscope can now be installed in a python virtual environment. The commands still work if stethoscope is installed using the official MonetDB installers, or if a symbolic link is created in the directory where `mserver5` is located. * The exittimeout value can now be set to a negative value (e.g. -1) to indicate that when stopping the dbfarm (using monetdbd stop dbfarm), any mserver5 processes are to be sent a termination signal and then waited for until they terminate. In addition, if exittimeout is greater than zero, the mserver5 processes are sent a SIGKILL signal after the specified timeout and the managing monetdbd is sent a SIGKILL signal after another five seconds (if it didn't terminate already). The old situation was that the managing monetdbd process was sent a SIGKILL after 30 seconds, and the mserver5 processes that hadn't terminated yet would be allowed to continue their termination sequence. Bug Fixes * 2030: Temporary table is semi-persistent when transaction fails * 7031: I cannot start MoentDb, because the installation path has Chinese. * 7055: Table count returning function used inside other function gives wrong results. * 7075: Inconsistent Results using CTEs in Large Queries * 7079: WITH table AS... UPDATE ignores the WHERE conditions on table * 7081: Attempt to allocate too much space in UPDATE query * 7093: `current_schema` not in sys.keywords * 7096: DEBUG SQL statement broken * 7115: Jul2021: ParseException while upgrading Oct2020 database * 7116: Jul2021: Cannot create filter functions * 7125: MonetDB Round Function issues in the latest release * 7126: The "lower" and "upper" functions doesn't work for Cyrillic alphabet * 7127: Bug report: "write error on stream" that results in mclient crash * 7128: Bug report: strange error message "Subquery result missing" * 7129: Bug report: TypeException:user.main[19]:'batcalc.between' undefined * 7130: Bug report: TypeException:user.main[396]:'algebra.join' undefined * 7131: Bug report: TypeException:user.main[273]:'bat.append' undefined * 7133: WITH <alias> ( SELECT x ) DELETE FROM ... deletes wrong tuples * 7136: MERGE statement is deleting rows if the column is set as NOT NULL even though it should not * 7137: Segmentation fault while loading data * 7138: Monetdb Python UDF crashes because of null aggr_group_arr * 7141: COUNT(DISTINCT col) does not calculate correctly distinct values * 7142: Aggregates returning tables should not be allowed * 7144: Type up-casting (INT to BIGINT) doesn't always happen automatically * 7146: Query produces this error: !ERROR: Could not find %102.%102 * 7147: Internal error occurs and is not shown on the screen * 7148: Select distinct is not working correctly * 7151: Insertion is too slow * 7153: System UDFs lose their indentation - Python functions broken * 7158: Python aggregate UDF returns garbage when run on empty table * 7161: fix priority

1 0

DBSeeder Version 3.0.1
by Walter Weinmann 03 Aug '21

03 Aug '21

We are pleased to release version 3.0.1 of DBSeeder ( https://github.com/KonnexionsGmbH/db_seeder). The most important enhancement is the possibility to deactivate the constraints of the type FOREIGN, PRIMARY and UNIQUE KEY before the DML statements and to activate them again afterwards. The JDBC DatabaseMetaData class is used for this purpose. DBSeeder also supports the latest version of MonetDB available on Docker Hub (DBMS Oct2020-SP5) and Maven Repository (JDBC 3.1.jre8).

1 0

Unable to connect to Monetdb after Upgrade : Challenge string is not valid, it is empty
by AUBERT Simon 02 Jul '21

02 Jul '21

Hello all I had an old monetdb installation on my Windows laptop -Did the upgrade in Oct2020-SP5 Bugfix Release (11.39.17) It really looks like I can start the server C:\Program Files\MonetDB\MonetDB5\bin>mserver5 # MonetDB 5 server v11.39.17 (Oct2020-SP5) # Serving database 'demo', using 4 threads # Compiled for amd64-pc-windows-msvc/64bit # Found 15.862 GiB available main-memory of which we use 12.928 GiB # Copyright (c) 1993 - July 2008 CWI. # Copyright (c) August 2008 - 2021 MonetDB B.V., all rights reserved # Visit https://www.monetdb.org/ for further information # Listening for connection requests on mapi:monetdb://localhost:50000/ # MonetDB/GIS module loaded # MonetDB/SQL module loaded # MonetDB server is started. To stop server press Ctrl-C. But when I want to use the mclient, it asks the user and password and then... nothing and after a few minutes I have this message Challenge string is not valid, it is empty Same thing with an ODBC connection : after a long time, it fails. I have reinstalled Monetdb but nothing changed. I have no idea of what's wrong. Best regards, Simon Simon AUBERT [cid:image001.jpg@01D76D97.13BF6A80] Bourse Maritime - 1, place Lainé - 33 000 Bordeaux Mob : +33(0)6 63 83 76 29

2 2