- users-list - monetdb.org

Announcement: New Sep2022 Feature release of MonetDB suite
by Sjoerd Mullender 26 Sep '22

26 Sep '22

The MonetDB team at MonetDB Solutions BV is pleased to announce the Sep2022 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/documentation/admin-guide/release-notes/sep2022/>. As usual, the download location is <https://www.monetdb.org/downloads/>. The Sep2022 documentation can be found [here](https://www.monetdb.org/documentation-Sep2022/). ## Sep2022 Feature Release (11.45.7) ### Client Package - Dumping of function GRANTs was improved by adding the types of the function (and procedure) arguments. ### ODBC Driver - Improved SQLSetPos(). It now allows RowNumber to be 0 when Operation is SQL_POSITION. - Improved ODBC Error/Warning messages. They now include the name of the Data Source as required by the ODBC specification: [MonetDB][ODBC driver VERSION][data-source-name] data-source-supplied-text - Improved MonetDB ODBC Data Source Configuration dialog on MS Windows by hiding the typed in password text. It now shows dots for the characters. This fixes request https://github.com/MonetDB/MonetDB/issues/7314 - Changed output of TABLE_CAT or PROCEDURE_CAT result columns as returned by ODBC functions: SQLTables(), SQLColumns(), SQLPrimaryKeys(), SQLForeignKeys(), SQLStatistics(), SQLTablePrivileges(), SQLColumnPrivileges(), SQLProcedures() and SQLProcedureColumns(). They used to return the static database name but now they will return NULL as MonetDB does not support CATALOG objects or qualifiers. - Removed the possibility to retrieve or set the CURRENT_CATALOG via SQLGetConnectAttr(hdbc, SQL_ATTR_CURRENT_CATALOG, ...) and SQLSetConnectAttr(hdbc, SQL_ATTR_CURRENT_CATALOG, ...) as MonetDB does not support CATALOG objects (no SQL support for: CREATE CATALOG abc or SET CATALOG abc) and therefore there is no CURRENT_CATALOG. - Corrected ODBC functions SQLTablePrivileges() and SQLColumnPrivileges() for local temporary tables located in schema tmp. They did not return any rows when the temporary table had privileges set. Now they do return rows as expected. - Improved SQLProcedures() and SQLProcedureColumns(). They now list information also for all built-in system procedures and functions, not only those created via SQL. Also corrected the value of ORDINAL_POSITION for scalar function arguments. It would start at 2 instead of 1. - Extended output of SQLProcedures() and SQLProcedureColumns() resultsets with an extra column SPECIFIC_NAME. This column contains the name which uniquely identifies this procedure or function within its schema. As MonetDB supports overloaded procedures and functions, the combination of PROCEDURE_SCHEM and PROCEDURE_NAME is not enough to uniquely identify a procedure or function. This extra column allows you to correctly match the corresponding rows returned by SQLProcedureColumns() with the specific rows of SQLProcedures(). This extra column SPECIFIC_NAME is implemented similar to the JDBC DatabaseMetaData methods getProcedures() and getProcedureColumns(). - For SQLForeignKeys() corrected the output of columns UPDATE_RULE and DELETE_RULE. These columns used to always return 3 (= SQL_NO_ACTION) but now they will report the action codes as specified in the FOREIGN KEY CONSTRAINT construction. - Fixed issue in function SQLProcedureColumns(). When the argument ColumnName was not empty it generated an invalid SQL query which failed with error: SELECT: no such column 'c.name'. This has been resolved. - Corrected implementation of SQLGetInfo(hdbc, SQL_MAX_DRIVER_CONNECTIONS, ...). It used to always return 64. Now it returns the value from the MonetDB server. - Changed the column names case of the result sets as returned by SQLTables(), SQLColumns(), SQLSpecialColumns(), SQLPrimaryKeys(), SQLForeignKeys(), SQLStatistics(), SQLTablePrivileges(), SQLColumnPrivileges(), SQLProcedures() and SQLProcedureColumns(). The column names where all in lowercase but the ODBC specification defines them in uppercase, so changed them to uppercase. - Corrected SQLSpecialColumns(..., SQL_BEST_ROWID, ...). Previously it only returned rows when the table had a primary key. Now it also returns rows when a table has a unique constraint. - Corrected SQLStatistics(..., SQL_INDEX_ALL, ...). Previously it only returned rows when the table had a primary or unique key. Now it also returns rows for indices which are not based on a key. - Corrected SQLStatistics(..., SQL_ENSURE). It now returns CARDINALITY information for columns based on a primary/unique key. Previously it always returned NULL for the CARDINALITY result column. - Corrected ODBC functions SQLPrimaryKeys(), SQLSpecialColumns() and SQLStatistics() for local temporary tables located in schema tmp. They did not return any rows when the temp table had a primary or unique key or index. Now they do return rows as expected. ### MonetDB Common - Fixed a bug in ORDER BY with both NULLS LAST and LIMIT when the ordering was on an interger or integer-like column and NULL values are present. - The median_avg and quantile_avg returned bogus results in the non-grouped case (i.e. something like SELECT sys.median_avg(i) FROM t). - The abort_on_error parameter of all GDK-level functions has been removed. Errors (e.g. overflow) now also results in an error. - Implemented BC/AD (and BCE/CE) suffixes when parsing dates. - The interface for using strimps has not changed (create an imprint index on a column of a read only table), but now construction happens at the beginning of the first query that uses the strimp and is performed in a multithreaded manner. - Implemented the use of strimps for NOT LIKE queries. The idea is to run the corresponding LIKE query using strimps and take the complement of the result. We keep around NULL values both during strimp filtering and during the pcre part of the LIKE query so that they get discarded automatically when we take the complement. - The function BBPkeepref now gets a BAT pointer as argument instead of a bat id. - Get rid of macro Tsize, use ->twidth instead. - Get rid of macro BUNlast, just use BATcount instead. - The BLOB type has been moved into the GDK layer. - When adding or subtracting months from a date or timestamp value, clamp the result to the calculated month instead of wrapping to the beginning of the next month. See bug [7227](https://https://github.com/MonetDB/MonetDB/issues/7227). - The median_avg and quantile_avg returned bogus results in the non-grouped case (i.e. something like SELECT sys.median_avg(i) FROM t). ### MonetDB5 Server - Disabled volcano pipeline due to known issues. ### SQL Frontend - The function sys.tracelog is now executable by anyone. The function (and view of the same name) returns the tracing information of a query that was prepended with the TRACE keyword. - Fixed a bug in COPY BINARY INTO where the input wasn't checked thoroughly enough. - Extended the built-in profiler to emit non-MAL events related to query compilation, optimization, transactions and client connections. To minimize, simplify and optimize the process of generating and processing profiler output, only the events marking the end of an operation are emitted in most cases and the emitted json messages themselves are trimmed down to their essential fields. Furthermore, the MAL instruction profiler.openstream now requires an integer as a single argument, "0" for default behaviour or "4" to turn on the profiler in minimal mode, which causes it to only emit general events and excludes individual MAL instruction execution events from the profiler streams. The MAL instruction profiler.openstream with zero arguments is deprecated. - Extended system view sys.users with columns: schema_path, max_memory, max_workers, optimizer and default_role. Extended system table sys.db_user_info with columns: schema_path, max_memory, max_workers, optimizer, default_role and password. The password is encrypted. This table can only be queried when the user has been granted the select privilege from monetdb. - CREATE USER statement has been extended to take more optional arguments like MAX_MEMORY, MAX_WORKERS, OPTIMIZER, DEFAULT ROLE. ALTER USER statement has also been extended with DEFAULT ROLE. - A new function sys.url_extract_host(string, bool) which returns the host name from the given URL has been implemented. The bool argument, if true, causes the www. prefix to be removed. - The user authentication tables are now administered by the SQL layer instead of the MAL layer. This means that changing (adding, dropping, altering) user and role information is now transaction-safe. - Added the UNLOGGED TABLE feature. - Removed functions sys.index and sys.strings. ### Merovingian - Added "loglevel" property for monetdbd logging (to merovingian.log). The loglevel can be set to: error or warning or information or debug. The loglevel property can be changed dynamically via command: monetdbd set loglevel=warning /path/to/dbfarm Default the loglevel is set to: information When loglevel is error, only errors are logged. When loglevel is warning, errors and warnings are logged. When loglevel is information, errors and warnings and information messages are logged. When loglevel is debug, all messages are logged. - There is now a loadmodules property that can be used to add --loadmodule arguments to the mserver5 command line. See the monetdb and mserver5 manual pages. - In certain cases (when an mserver5 process exits right after producing a message) the log message was logged over and over again, causing monetdbd to use 100% CPU. This has been fixed. ### Bug Fixes - [6982](https://github.com/MonetDB/MonetDB/issues/6982): Wrong constraint name in error message of constraint violation - [7209](https://github.com/MonetDB/MonetDB/issues/7209): Configuration option for merovingian.log - [7225](https://github.com/MonetDB/MonetDB/issues/7225): Invalid memory access when extending a BAT during appends - [7227](https://github.com/MonetDB/MonetDB/issues/7227): Date calculations, bug or feature - [7273](https://github.com/MonetDB/MonetDB/issues/7273): Concurrent reads and writes causes "BATproject2: does not match always" error - [7282](https://github.com/MonetDB/MonetDB/issues/7282): call sys.dump_table_data(); fails - [7285](https://github.com/MonetDB/MonetDB/issues/7285): C-UDFs: aggr_group.count has wrong value (number of input rows instead of number of groups). - [7296](https://github.com/MonetDB/MonetDB/issues/7296): Implictly cast a timestamp string to DATE when appropriate - [7297](https://github.com/MonetDB/MonetDB/issues/7297): Parsing partial dates behaves unpredictable - [7306](https://github.com/MonetDB/MonetDB/issues/7306): ODBC Driver Assertion `stmt->Dbc->FirstStmt' Failed - [7314](https://github.com/MonetDB/MonetDB/issues/7314): ODBC Driver : please mask/hide password - [7318](https://github.com/MonetDB/MonetDB/issues/7318): distinct in a subquery not working properly

1 0

insert pandas df to monetdb
by heikal79＠live.com 06 Sep '22

06 Sep '22

Hi is it possible to insert pandas df to montedb?. I have used both sqlalchemy and df.to_sql( name='invoiceline', con=engine, schema='tst', if_exists='append',index=False, index_label=None) and it returns text() got an unexpected keyword argument 'bindparams'. Also used pymonetdb and cursor.executemany('INSERT INTO tst.invoiceline VALUES (%s)', [[x] for x, row in df.iterrows()]) and returned 21S01!INSERT INTO: number of values doesn't match number of columns of table 'invoiceline'. My table contins 5 columns. BR

3 2

Change server configuration on Windows
by heikal79＠live.com 01 Sep '22

01 Sep '22

Hi Is it possible to change the server configuration on Windows including datafarm location, port,... Regards

2 2

Recovering database after BBPcheckbats error
by imad hajj chahine 24 Aug '22

24 Aug '22

Hi, I am using version Jan2022-SP2, and after a server restart I had the BBPcheckbats error. I upgraded to the latest version Jan2022-SP4 as it has the fix for this reported bug, and I am still unable to start the database. After manually editing the BBP.dir file and removing the entries on which the database failed to load with messages similar to (BBPcheckbats file /bat/01/67/16774.tail1 too small (expected 143640, actual 0), I managed to make the database start and connect to it and query the tables successfully. Is this the right way to recover from this? what I am risking losing by deleting these entries, should I have corrupted tables that are affected by these entries? What is exactly the role of BBP.dir? any documentation describing the use of this file. Appreciate your help. Thank you.

2 3

Copy into from stdin hanging after upgrade to 11.43.21
by imad hajj chahine 23 Aug '22

23 Aug '22

Hi, I have a bunch of dump sql files generated using msqldump command, that I was loading without any error to the version 11.43.15. After upgrading to version 11.43.21 to avoid the BBPCheckbats bug, loading the dump started to hang the server. Downgrading back to 11.43.15 fixed the issue. the command I am using to load the dump: mclient -d dbname -f sql < "pathtofile/dump.sql" sample dump file: COPY 1 RECORDS INTO tablename FROM stdin USING DELIMITERS '\t','\n','"'; Thank you.

1 0

Announcement: New Jan2022-SP4 Bugfix release of MonetDB suite
by Sjoerd Mullender 22 Aug '22

22 Aug '22

The MonetDB team at MonetDB Solutions BV is pleased to announce the Jan2022-SP4 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/documentation/admin-guide/release-notes/jan2022/>. As usual, the download location is <https://www.monetdb.org/downloads/>. ### Client Package - Implemented dump of global grants, that is to say, grants for COPY INTO and COPY FROM which grant permission to users to write to or read from files on the server (COPY INTO queries without the ON CLIENT option). - Fixed a bug where when the semicolon at the end of a COPY INTO query that reads from STDIN is at exactly a 10240 byte boundary in a file, the data isn't read as input for the COPY INTO but instead as a new SQL query. ### MonetDB Common - A bug was fixed when upgrading a database from the Oct2020 releases (11.39.X) or older when the write-ahead log (WAL) was not empty and contained instructions to create new tables. - When destroying a bat, make sure there are no files left over in the BACKUP directory since they can cause problems when the bat id gets reused. - Fixed an off-by-one error in the logger which caused older log files to stick around longer in the write-ahead log than necessary. - When an empty BAT is committed, skip writing (and synchronizing to disk) the heap (tail and theap) files and write 0 for their sizes to the BBP.dir file. When reading the BBP.dir file, if an empty BAT is encountered, set the sizes of those files to 0. This fixes potential issues during startup of the server (BBPcheckbats reporting errors). - Make sure heap files of transient bats get deleted when the bat is destroyed. If the bat was a partial view (sharing the vheap but not the tail), the tail file wasn't deleted. - Various changes were made to satisfy newer compilers. - The batDirtydesc and batDirtyflushed Boolean values have been deprecated and are no longer used. They were both holdovers from long ago. - Various race conditions (data races) have been fixed. - All accesses to the BACKUP directory need to be protected by the same lock. The lock already existed (GDKtmLock), but wasn't used consistently. This is now fixed. Hopefully this makes the hot snapshot code more reliable. ### MonetDB5 Server - Various race conditions (data races) have been fixed. ### Merovingian - When multiple identical messages are written to the log, write the first one, and combine subsequent ones in a single message. - Fixed a leak where the log file wasn't closed when it was reopened after a log rotation (SIGHUP signal). - Try to deal more gracefully with "inherited" mserver5 processes. This includes not complaining about an "impossible state", and allowing such processes to be stopped by the monetdbd process. - When a transient failure occurs during processing of a new connection to the monetdbd server, sleep for half a second so that if the transient failure occurs again, the log file doesn't get swamped with error messages. ### Bug Fixes - [7040](https://github.com/MonetDB/MonetDB/issues/7040): Memory leak detected for MAPI interface - [7298](https://github.com/MonetDB/MonetDB/issues/7298): Irresponsive database server after reading incomplete SQL script. - [7308](https://github.com/MonetDB/MonetDB/issues/7308): Race condition in MVCC transaction management

1 0

Re: Define a cast function from day_interval to int
by yunus 08 Aug '22

08 Aug '22

Hello Imad, Without going into too much detail, you might want to start having a look at the MonetDB-extend <https://www.monetdb.org/hg/MonetDB-extend> repository. Also keep in mind, that the use of MAL scripts for declaring C functions in MAL is deprecated in favor of the command and pattern preprocessor macro's. Regards, Yunus

1 0

Aggregation on double column in different monetdb version
by imad hajj chahine 04 Aug '22

04 Aug '22

Hi, I am currently migrating monetdb from an old version to the current release, and I noticed that sum on double precision columns is returning a slightly different result between both versions. The only difference between the system is HugeInt enabled on the newer version. ex: To reproduce the mismatch (subset of values extracted from the table) SELECT sum(cast(c1 as double)) --, sum(c1) FROM (VALUES (218.5792349726776),(792.3497267759564),(846.9945355191256),(819.6721311475409),(846.9945355191256),(819.6721311475409),(846.9945355191256),(846.9945355191256),(819.6721311475409),(846.9945355191256),(819.6721311475409),(846.9945355191256),(657.5342465753424),(4109.58904109589),(4234.972677595628),(3961.748633879781),(4234.972677595628),(4098.360655737704),(4234.972677595628),(4098.360655737704),(4234.972677595628),(4234.972677595628),(4098.360655737704),(4234.972677595628),(4098.360655737704),(273.224043715847),(219.1780821917808),(821.917808219178),(849.3150684931506),(846.9945355191256),(792.3497267759564),(846.9945355191256),(819.6721311475409),(846.9945355191256),(819.6721311475409),(846.9945355191256),(846.9945355191256),(819.6721311475409),(655.7377049180328),(249.3150684931507),(1104.109589041096),(1101.092896174863),(1030.054644808743),(1101.092896174863),(1065.573770491803),(1101.092896174863),(1065.573770491803),(1101.092896174863),(1101.092896174863),(1065.573770491803),(1101.092896174863),(852.4590163934427),(219.1780821917808),(1698.630136986301),(1643.835616438356),(1698.630136986301),(1693.989071038251),(1584.699453551912),(1693.989071038251),(1639.344262295081),(1693.989071038251),(1639.344262295081),(1693.989071038251),(1693.989071038251),(1475.409836065573),(60.27397260273972),(1868.493150684931),(1808.219178082191),(1868.493150684931),(1863.387978142076),(1743.169398907103),(1863.387978142076),(1803.27868852459),(1863.387978142076),(1803.27868852459),(1863.387978142076),(1863.387978142076),(1803.27868852459),(258.9041095890410),(381.1475409836065),(356.5573770491803),(381.1475409836065),(368.8524590163934),(381.1475409836065),(368.8524590163934),(381.1475409836065),(381.1475409836065),(368.8524590163934),(381.1475409836065),(368.8524590163934),(135.2459016393442),(542.4657534246575),(904.1095890410959),(934.2465753424657),(931.6939890710382),(871.5846994535519),(931.6939890710382),(901.639344262295),(931.6939890710382),(901.639344262295) ) t1 (c1) old-V11.27.11: 137756.87551463424 new-V11.43.15: 137756.8755146343 ExactValue: 137756.87551463430452 The aggregation over double columns should return the same result regardless of the engine versions, as the double precision type did not change, unless there was a bug reported and it was fixed. Any idea what could affect the result difference. Regards.

2 2

Define a cast function from day_interval to int
by imad hajj chahine 03 Aug '22

03 Aug '22

Hi, I am currently migrating monetdb from an old version to the current release, and I noticed that the difference of 2 dates return a day_interval and cannot be cast to an int and I have to use the "day" function for this purpose. This is fine for newly written scripts, but for old scripts I am hoping to avoid a rewrite. ex: cast(date1-date2 as int) return an error: types day_interval(4,0) and int(32,0) are not equal How to define a cast function from day_interval to int to avoid any changes to the old scripts?

2 2

Procedure vanishing
by hyemi.jeong＠obzen.com 21 Jul '22

21 Jul '22

Hello, I have just started to use monetDB and something strange has happened. I've made 5 procedures and 2 of them are just gone... I don't know what happened, they've just disappeared. Does this normally happen? Is there any possible explanation for this? I cannot possibly imagine what causes for this vanishing. Hope to hearing from you. Thanks.

1 0