- users-list - monetdb.org

Announcement: New Aug2018-SP2 Bugfix release of MonetDB suite
by Sjoerd Mullender 14 Jan '19

14 Jan '19

The MonetDB team at CWI/MonetDB BV is pleased to announce the Aug2018-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://dev.monetdb.org/downloads/>. Aug 2018-SP2 bugfix release (11.31.13) SQL Frontend * Disabled function sys.getcontent(url). Bug Fixes * 6643: schema name qualifier in create global temporary table json.table6643 ... is not honered * 6645: optimizer treats all the functions with no or constant parameters as constant * 6650: PREPARE SQL statement fails to compile user defined functions with parameter/s * 6651: Multi-column IN clause for subquery produces wrong results * 6653: CREATE TABLE accepts empty table/column name * 6654: Incorrect handling of 'TRUE' in compound select * 6657: Restart sequence with a non atomic sub-query cardinality gives wrong error message * 6660: GRANTing a ROLE is not idempotent * 6662: Concurrency Conflicts Exception string "!transaction is aborted because of concurrency conflicts, will ROLLBACK instead" not shown on prompt. * 6664: mserver5 crash: infinite recursive happens at rel_bin.c:489 * 6665: Creation of serial types does not accept negative numbers * 6666: COPY INTO from .. LOCKED doubles input data * 6668: The SAMPLE key word doesn't work in a subquery. * 6669: COPY [xxx RECORDS] INTO foo FROM STDIN ... doesn't work without specifying nr of to be copied records * 6672: SQLGetData with SQL_C_WCHAR string truncation and invalid StrLen_or_Ind value

1 0

Re: Performance degradation
by Jeremy Norris 12 Jan '19

12 Jan '19

Here's some additional observations on this puzzling issue: The slow table is "cmdb", the normal table is "cmdb2". Both have the same number of rows according to "SELECT count(*)" (73553310). However, sys.statistics shows something a bit different: sql>SELECT x.name, count(*) AS columns_with, z.count FROM sys.tables AS x, sys.columns AS y, sys.statistics AS z WHERE x.id = y.table_id AND y.id = z.column_id GROUP BY x.name, z.count; +-------+--------------+----------+ | name | columns_with | count | +=======+==============+==========+ | cmdb | 8 | 73553310 | | cmdb | 145 | 73553311 | | cmdb2 | 153 | 73553310 | +-------+--------------+----------+ 3 tuples sql> For the slow table, "cmdb", there are 145/153 columns that have an extra "row". This row count mismatch causes it to skip parallelization optimizations in "monetdb5/optimizer/opt_mitosis.c", line 212. A few related questions: 1) In the columnar model, is is valid for conceptual tuples to exist which don't have values for each column like this? (This seems strange as now there's a difference between an explicit NULL and "not existing"). How might this situation have happened? (Only transactional full-row inserts were applied to this DB). Can this be repaired? 2) Is the conditional in "monetdb5/optimizer/opt_mitosis.c", line 212 sufficiently robust? I may be misunderstanding the full context of this line, but it seems like we could still benefit from splitting/parallelization when r < rowcnt, no? Thanks, -Jeremy Norris

1 0

Performance degradation
by Herman Knief 10 Jan '19

10 Jan '19

Greetings, Running MonetDB v11.31.11 (Aug2018-SP1), we ran into an issue where queries that have been running quite well, suddenly slowed by about 5x. Looking at the explain, it seems we hit a condition that is preventing mitosis? Copying the tables related to the query seems to restore performance to the previous level, but would be very curious to understand what happened here. What metrics or stats should we be looking at? Changes we see in the plan are as follows: Bad example: sql.tid(X_4:int, "waltest":str, "cmdb":str); Good example: sql.tid(X_4:int, "waltest":str, "cmdb2":str, 0:int, 32:int); Looking at the storage info, the sizes (heap and column), type width and counts look the same between the two tables. Any thoughts or suggestions would be appreciated. - Herman -- Please excuse typos... I can't type.

1 0

Embedded Python module is not available in recent builds of Windows version
by Kevin Chen 21 Dec '18

21 Dec '18

Hi, I upgraded the MonetDB windows version to 2018Aug and found that the embedded Python module is no longer working. Even the startup script with “embedded_py=true”, the module was not loaded. There was no message showing “MonetDB/Python2 module loaded”. All my Python functions are failed. Please help. Thanks. Kevin

2 1

!FATAL: SQL type wrd missing
by Ariel Abadi 18 Dec '18

18 Dec '18

Hi! Please I need an urgent solution (if there is one) I have upgraded a database to the latest version, and when Im trying to start the DB, there is an error: 2018-12-18 10:58:06 ERR DB[29818]: !FATAL: SQL type wrd missing Can someone help me with any clue!???? Thks in advance Ariel

2 3

Re: grafana-monetdb-datasource installation problem
by Svetlin 03 Dec '18

03 Dec '18

Hi Ariel, The current version of the grafana-monetdb-datasource plugin cannot be used with the latest grafana release. There is a missing breaking dependency that has being introduced(see #10053 <https://github.com/grafana/grafana/issues/10053>) and we didn’t have time to patch that in. Secondly, you would need a http server as a mediator to forward the queries to monetdb. The grafana monetdb datasource plugin merely creates MonetDB compatible queries and forwards responses back to grafana core for visualization. I hope this helps. Cheers, Svetlin > On Dec 3, 2018, at 11:03 AM, Ying (Jennie) Zhang <ying.zhang(a)monetdbsolutions.com> wrote: > >> aabadi(a)starconnecting.com <mailto:aabadi@starconnecting.com>

2 1

grafana-monetdb-datasource installation problem
by Ariel Abadi 01 Dec '18

01 Dec '18

Does anybody has installed the monetdb datasource plugin for Grafana? Im running Grafana v5.3.4. The plugin was downloaded from https://github.com/MonetDBSolutions/grafana-monetdb-datasource And is not working... :'( Does anybody knows how to connect MonetDB within Grafana? Any ideas will be very much appreciated!!! Thks Ariel

2 1

Re: Issues trying to create a table...
by Stefan Manegold 28 Nov '18

28 Nov '18

Hi, Internally (as in in the MonetDB code base),the impact of the problem could imho be significantly reduced (though not fully avoided)by "simply" using different separator characters (or even strings?) when creating qnames,e.g. schema:table;column insteadof schema_table_column ... A unique bijective reversible mapping from schema,table,column to qname should avoid the problem entirely , but might require to use separator chracter(s) that are not valid in SQL identifiers, if such exist ... Best,Stefan -------- Original message --------From: Ying Zhang <Y.Zhang(a)cwi.nl> Date: 28/11/2018 07:33 (GMT+01:00) To: Communication channel for MonetDB users <users-list(a)monetdb.org> Subject: Re: Issues trying to create a table... > On 28 Nov 2018, at 04:18, Herman Knief <hknief(a)gmail.com> wrote: > > Hi Folks, > I'm new to MonetDB and trying to setup a schema from a MariaDB export. > I have a particular table that reports a conflict with the create statement. I've stripped this down to just a particular field that seems to be causing the issue, but unsure where to go from here. > > sql>CREATE TABLE "sc_cat_item_delivery_plan" ("script" string); > CREATE TABLE: zboot_sc_cat_item_delivery_plan_script conflicts Hai Herman, This is because something has already been defined before, whose complete qname is “zboot_sc_cat_item_delivery_plan_script”. This often happens with (but probably not limited to) schema_name+table_name+column_name. In MonetDB test suite, we have two examples to trigger this (type of) error: sql>create table foo (id integer, bar_id integer); operation successful sql>create table foo_bar (id integer); CREATE TABLE: sys_foo_bar_id conflicts and sql>create table foo_bar (baz int); operation successful sql>create table foo (bar_baz int); CREATE TABLE: sys_foo_bar_baz conflicts This is not a bug. You need to change your table and/or column definitions, like your alternatives below. Then CREATE TABLE will succeed. Regards, Jennie > > What's interesting is that either changing the name of the table or the name of the field and these statements work. There is something about this particular combo that is problematic. > sql>CREATE TABLE "sc_cat_item_delivery_plan1" ("script" string); > operation successful > sql>CREATE TABLE "sc_cat_item_delivery_plan" ("script1" string); > operation successful > > I have a total of 2251 tables... all were created except for this one. I've tried to destroy and recreate the schema with no luck. Logs don't seem to indicate any issue and trying to use the debug on the client didn't get me anywhere. Thoughts? Suggestions? > > -- > Please excuse typos... I can't type. > _______________________________________________ > 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

2 1

Issues trying to create a table...
by Herman Knief 28 Nov '18

28 Nov '18

Hi Folks, I'm new to MonetDB and trying to setup a schema from a MariaDB export. I have a particular table that reports a conflict with the create statement. I've stripped this down to just a particular field that seems to be causing the issue, but unsure where to go from here. sql>CREATE TABLE "sc_cat_item_delivery_plan" ("script" string); CREATE TABLE: zboot_sc_cat_item_delivery_plan_script conflicts What's interesting is that either changing the name of the table or the name of the field and these statements work. There is something about this particular combo that is problematic. sql>CREATE TABLE "sc_cat_item_delivery_plan1" ("script" string); operation successful sql>CREATE TABLE "sc_cat_item_delivery_plan" ("script1" string); operation successful I have a total of 2251 tables... all were created except for this one. I've tried to destroy and recreate the schema with no luck. Logs don't seem to indicate any issue and trying to use the debug on the client didn't get me anywhere. Thoughts? Suggestions? -- Please excuse typos... I can't type.

3 2

Issue while connecting to MonetDB via Perl cpan driver
by Sharma, Sreejith 15 Nov '18

15 Nov '18

Hi, We have a Perl code via which we are trying to connect to one of the MonetDB instance. Here we are using DBD::monetdb Perl cpan driver for connectivity. when we run the snippet, it just tries to connect to DB and seems hung, looks like it's awaiting for user input for password. Also, on the server side logs we see an error (challenge error) can connection will not be established. Here is a sample snippet which isn't working. Could anyone help with an idea or code snippet which works with Perl DBI for MonetDB connectivity? [cid:image001.png@01D47D35.C23AD040] #!/usr/bin/perl # use strict; use warnings; use DBI(); print "Attempting to connect...\n"; my $dbh = DBI->connect('dbi:monetdb:host=XXXX;port=55555', 'USERNAME', 'Password', { RaiseError => 1 } ); print "Connected. Trying SQL...\n"; my $sth = $dbh->prepare('SELECT * FROM env'); $sth->execute; $sth->dump_results; print "Done\n"; exit 0; Regards, Sreejith

1 0