- users-list - monetdb.org

should DROP TABLE work even after a table has been SET READ ONLY?
by Anthony Damico 07 Mar '16

07 Mar '16

hi, this bugzilla ticket is marked as resolved, but this behavior seems unintuitive to me.. is it ok? thanks team CREATE TABLE my_table AS SELECT 1 AS my_column WITH DATA; ALTER TABLE my_table SET READ ONLY; SELECT COUNT(*) FROM my_table; DROP TABLE my_table; from https://www.monetdb.org/bugzilla/show_bug.cgi?id=3895

3 3

extreme performance breakdown after update
by Andreas F. Hoffmann 06 Mar '16

06 Mar '16

Hello List, I'm running monetdb on a Debian server and the software is managed via Debian packages. A few days ago I installed an update for the monetdb5-server package. After that I noticed a problem with queries with the function lower() in the WHERE clause: >>WHERE LOWER(“fieldname”) LIKE '%lower_string%'<< without any change to the data, the requests became much, much slower then before (from some hundred microseconds to some minutes). Not only in my application but also in the monetdb client... I could solve the problem via a switch from using LIKE to ILIKE and no lower()-function, but I'm curious what could be the cause for this dramatic performance breakdown? I searched the release history but I found nothing... Has anybody experienced the same effect? Anny ideas on this phenomenon? Greetings from Germany, Andreas (theafh)

4 6

how to turn off double quotes
by Anton Kravchenko 03 Mar '16

03 Mar '16

Hi there, I wonder if there is a way to "copy select * from some_table into '\path\to\some.csv' delimiters ','; ", so that in output csv there is no double quotes for char vars? (for example, some_string instead of "some_string") P.S. I am using MonetDB v11.21.11 (Jul2015-SP1) on Windows. Thank you, Anton

1 1

(no subject)
by Anthony Damico 03 Mar '16

03 Mar '16

http://stackoverflow.com/questions/35730919/monetdb-select-query-slow-when-…

1 0

how to change the user "monetdb" password?
by Lynn Carol Johnson 02 Mar '16

02 Mar '16

Hi all - I've been googling to try and find a method of changing the default password for user monetdb. The links I've found are all 8-9 years old (e.g. This<https://www.monetdb.org/pipermail/users-list/2007-December/001345.html> one) My commands: 1. monetdbd stop <mydbfarm> 2. cd /Usr/local/monetdb/bin 3. ./mserver5 Then execute a change password command and get these results, which look similar to the email exchange noted in the 8 year old link above: rs-btlcj34lt:bin lcj34$ ./mserver5 # MonetDB 5 server v11.21.11 "Jul2015-SP1" # Serving database 'demo', using 8 threads # Compiled for x86_64-apple-darwin14/64bit with 64bit OIDs and 128bit integers dynamically linked # Found 16.000 GiB available main-memory. # Copyright (c) 1993-July 2008 CWI. # Copyright (c) August 2008-2015 MonetDB B.V., all rights reserved # Visit http://www.monetdb.org/ for further information # Listening for connection requests on mapi:monetdb://127.0.0.1:50000/ # MonetDB/GIS module loaded # Start processing logs sql/sql_logs version 52200 # Finished processing logs sql/sql_logs # MonetDB/SQL module loaded >changePassword("monetdb","bucklerLab"); !TypeException:user.main[1]:'user.changePassword' undefined in: user.changePassword("monetdb":str,"bucklerLab":str); > We are opening up our database to some outside folks. I've successfully created a read-only user/pwd for them, but want to protect the db by changing the default monetdb user password. I'm practicing on my own local copy before messing with our "real" db, but can't get this to work. Is there some other way I should be going about this? One additional question: What is the "passphrase" parameter I see when I run "monetdbd get all <mydbfarm>"? I tried playing with that but have not found what it effects. Thanks - Lynn

2 2

MySQL unix_timestamp() equivalent?
by Vijay Krishna 02 Mar '16

02 Mar '16

Hi, I am looking for an Epoch (bigint) to date/timestamp converter in MonetDB - something similar to unix_timestamp() in MySQL. I referred this page - https://www.monetdb.org/Documentation/SQLreference/Temporal and tried out a few functions, but it seems to work only with the existing Timestamp columns. Am I missing something here? I am using MonetDB Jan2014 release on a Mac OS-X. Any help much appreciated. Thanks & Regards, Vijayakrishna.P. Mobile : (+91) 9500402305.

4 7

MAPI COPY INTO from stream
by Gustavo Brian 02 Mar '16

02 Mar '16

Hi, I'm trying to bulk load data into monetdb through COPY INTO statement on the fly. I've created an aggregator script that will create a session and send chunk of data using MAPI interface. Like this: "sCOPY INTO foo FROM STDIN USING DELIMITERS ',','\\n';" //+ "\n" + "100,10,0,'10'\n" + "\n" I've been following this example: http://dev.monetdb.org/hg/MonetDB/file/tip/java/example/SQLcopyinto.java (haven't tested) I planned to add as part of the monetdb nodejs module: https://github.com/MonetDB/monetdb-nodejs/issues/1 but seems to be out of the scope of that repository. Sadly I'm not getting any error just doesn't work. What's the correct way ? Thanks.

2 1

Typecasting in prepared statements
by Arno Mittelbach 24 Feb '16

24 Feb '16

Hi all, I am having a strange problem regarding prepared queries with Umlaute (e.g., ä). It seems that the rule for type casting strings correspond to the length of the string. The problem is that an Umlaut seems to count for two "normal" characters. Here is an example. Consider the following simple table: create table A (a varchar(4)); Now if we create a prepared statement (ultimately I want to do this from Java) such as prepare select * from A where a = ?; we can execute the statement and supply 4 characters as the argument: exec 3('1234'); This works fine. However, if I am using an Umlaut within the parameter, then things don't work out. For example exec 4('ä123'); fails with the following error message EXEC: wrong type for argument 1 of prepared statement: char, expected varchar However, if I now shorten the parameter by "1" character, it works again. That is, exec 5('ä12'); is ok. Can anybody explain this behavior? Is this a bug? And is there a workaround? The only thing I came up with so far is to double the size of every column. Best regards, Arno

3 3

Slow query - round 2
by Luciano Sasso 24 Feb '16

24 Feb '16

Hi, Do you think that many ( very many ) inserts / updates on this table , inserts that were aborted by network error could corrupt data and cause it? Remember , table have 100M rows. I have table with 30M rows, and run well work. Att, -- Luciano Sasso Vieira Data Scientist & Solutions Architect luciano(a)gsgroup.com.br <http://www.gsgroup.com.br> | tel: 17 3353-0833 | cel: 17 99706-9335 www.gsgroup.com.br <http://www.gsgroup.com.br> --- Este email foi escaneado pelo Avast antivírus. https://www.avast.com/antivirus

2 1

File system level backup of running database
by Dennis Pallett 23 Feb '16

23 Feb '16

Hi all, Is it possible to create a file system level backup of a running MonetDB database? What are the downsides of doing this? For what it's worth, our databases are generally never updated outside of pre-fixed windows so only SELECT queries will be executed during any file system level backup. Best regards, Dennis Pallett

4 3

do these two commands create an error in the dev version of monetdb?
by Anthony Damico 23 Feb '16

23 Feb '16

they do work in the latest release but they crash monetdblite, making me wonder about the root culprit.. thanks all create table my_table (my_id clob, my_double double); SELECT COUNT( DISTINCT my_id ) AS unique_enrollees , QUANTILE( my_double , 0.25 ) AS some_quantile FROM my_table;

2 2

is there a downloadable nightly build exe installer for windows?
by Anthony Damico 23 Feb '16

23 Feb '16

or do i need to do something with these? http://dev.monetdb.org/hg/MonetDB/file/tip/NT/installer64 never done this before..curious how cumbersome it is i want to check whether this is a bug introduced within mserver or a bug specific to monetdblite https://www.monetdb.org/bugzilla/show_bug.cgi?id=3927 thanks!

2 2

Slow query
by Luciano Sasso 21 Feb '16

21 Feb '16

Hi Stefan About "prepare statements calls/traces ", in paralell select count(*), this service that insert in 10 and 10 minutes. But even without such active service , the time is the same, 90 minutes. My mistake, my server have 6 cores. Hi, (as a side note, I'm afraid I'm too "old fashioned" and do no longer understand "modern" MonetDB traces; at least, I have no idea, why there are so many prepare statements calls/traces in your trace of a simple select count(*) query ... ) Having said that, the trace suggests that your machine either has 6 cores, or MonetDB has decided to split it in 6 slices for processing, although you machine has only 4 cores? Also, the trace reveals that the vast majority of the time is spent in 6 (one per slice) sql.tid() calls: ....... -- Luciano Sasso Vieira Data Scientist & Solutions Architect luciano(a)gsgroup.com.br <http://www.gsgroup.com.br> | tel: 17 3353-0833 | cel: 17 99706-9335 www.gsgroup.com.br <http://www.gsgroup.com.br> --- Este email foi escaneado pelo Avast antivírus. https://www.avast.com/antivirus

1 0

Slow query
by Luciano Sasso 21 Feb '16

21 Feb '16

Hi, Martin Yes, before performance analysis, load is zero, the server is Linix console only. My RAM memory is low for this database size. what do you think ? Att, Hi, about slow query Yes, there is, but still left over resources. While the query (which takes 90 minutes) are running, to perform another in another table (9M rows), this latter returns usually in seconds. Hi Its all about concurrent processes while working with MonetDB. All those processes compete for RAM and disk bandwidth. Even running a browser already takes off a few GB from your RAM. At some point, you end up swapping intermediates to disk, which is a slow process (depending on your HDD). For performance analysis, you better shut down all the processes you don't need. Ideally, the CPU load = 0 before doing a performance analysis. > " > > A simple "select count(*) from table", run on 90 minutes. > > that is not normal. most likely there are a lot of other > processes competing for resources. > > > " -- Luciano Sasso Vieira Data Scientist & Solutions Architect luciano(a)gsgroup.com.br <http://www.gsgroup.com.br> | tel: 17 3353-0833 | cel: 17 99706-9335 www.gsgroup.com.br <http://www.gsgroup.com.br> --- Este email foi escaneado pelo Avast antivírus. https://www.avast.com/antivirus

1 0

Slow query
by Luciano Sasso 21 Feb '16

21 Feb '16

Hi, send now trace for query in link: https://drive.google.com/folderview?id=0B0L6U0kZpUaFNUU3ak9PNUFVZW8&usp=sha… Att, -- Luciano Sasso Vieira Data Scientist & Solutions Architect luciano(a)gsgroup.com.br <http://www.gsgroup.com.br> | tel: 17 3353-0833 | cel: 17 99706-9335 www.gsgroup.com.br <http://www.gsgroup.com.br> --- Este email foi escaneado pelo Avast antivírus. https://www.avast.com/antivirus

1 0

Slow Query
by Luciano Sasso 21 Feb '16

21 Feb '16

Hi, send attached trace for query. Att, --- Este email foi escaneado pelo Avast antivírus. https://www.avast.com/antivirus

3 2

Re: users-list Digest, Vol 42, Issue 17
by Luciano Sasso 21 Feb '16

21 Feb '16

Hi, about slow query Yes, there is, but still left over resources. While the query (which takes 90 minutes) are running, to perform another in another table (9M rows), this latter returns usually in seconds. " A simple "select count(*) from table", run on 90 minutes. that is not normal. most likely there are a lot of other processes competing for resources. " Em 21/02/2016 08:00, users-list-request(a)monetdb.org escreveu: > 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: Slow Query (Martin Kersten) > 2. Re: Slow Query (Stefan Manegold) > 3. might be of interest (Anthony Damico) > > > ---------------------------------------------------------------------- > > Message: 1 > Date: Sat, 20 Feb 2016 21:34:06 +0100 > From: Martin Kersten <martin(a)monetdb.org> > To: users-list(a)monetdb.org > Subject: Re: Slow Query > Message-ID: <56C8CDBE.3060006(a)monetdb.org> > Content-Type: text/plain; charset=windows-1252; format=flowed > > On 20/02/16 19:39, Luciano Sasso wrote: >> Hi, >> >> I have MonetDB JUL2015-SP2, with table of 100M rows and 20 coluns, >> 4 of number type, others string type. >> >> A simple "select count(*) from table", run on 90 minutes. > that is not normal. most likely there are a lot of other > processes competing for resources. > >> Hardware and SO is: Debian 8, 4 cores, 11G RAM and 20G free on disc. >> >> While monet run the query, command top, show 42G of virtual memory >> >> Its normal ?, any idea ? >> >> thanks. >> >> >> -- >> >> Luciano Sasso Vieira >> Data Scientist & Solutions Architect >> >> <http://www.gsgroup.com.br>luciano(a)gsgroup.com.br | tel: 17 3353-0833 | cel: 17 99706-9335 >> <http://www.gsgroup.com.br>www.gsgroup.com.br >> >> >> Este e-mail foi enviado por um computador sem v?rus e protegido pelo Avast. >> www.avast.com <https://www.avast.com/sig-email> >> >> >> >> _______________________________________________ >> users-list mailing list >> users-list(a)monetdb.org >> https://www.monetdb.org/mailman/listinfo/users-list >> > > > ------------------------------ > > Message: 2 > Date: Sat, 20 Feb 2016 22:16:01 +0100 (CET) > From: Stefan Manegold <Stefan.Manegold(a)cwi.nl> > To: Communication channel for MonetDB users <users-list(a)monetdb.org> > Subject: Re: Slow Query > Message-ID: <1956506938.11256849.1456002961125.JavaMail.zimbra(a)cwi.nl> > Content-Type: text/plain; charset="utf-8" > > Hi, > > this is indeed unexpected. > > To possibly better understand what's happening, > could you please run your query prefixed with keyword TRACE > and share the trace (profiling) result? > > Best, > Stefan > > ----- On Feb 20, 2016, at 7:39 PM, Luciano Sasso luciano(a)gsgroup.com.br wrote: > >> Hi, >> >> I have MonetDB JUL2015-SP2, with table of 100M rows and 20 coluns, >> 4 of number type, others string type. >> >> A simple "select count(*) from table", run on 90 minutes. >> >> Hardware and SO is: Debian 8, 4 cores, 11G RAM and 20G free on disc. >> >> While monet run the query, command top, show 42G of virtual memory. >> >> Its normal ?, any idea ? >> >> thanks. >> >> >> -- >> >> >> Luciano Sasso Vieira >> Data Scientist & Solutions Architect >> >> luciano(a)gsgroup.com.br | tel: 17 3353-0833 | cel: 17 99706-9335 >> www.gsgroup.com.br >> >> >> Este e-mail foi enviado por um computador sem v?rus e protegido pelo Avast. >> www.avast.com >> >> _______________________________________________ >> users-list mailing list >> users-list(a)monetdb.org >> https://www.monetdb.org/mailman/listinfo/users-list -- Luciano Sasso Vieira Data Scientist & Solutions Architect luciano(a)gsgroup.com.br <http://www.gsgroup.com.br> | tel: 17 3353-0833 | cel: 17 99706-9335 www.gsgroup.com.br <http://www.gsgroup.com.br> --- Este email foi escaneado pelo Avast antivírus. https://www.avast.com/antivirus

2 1

might be of interest
by Anthony Damico 21 Feb '16

21 Feb '16

http://stackoverflow.com/questions/32380663/improve-speed-of-queries-using-…

1 0

Slow Query
by Luciano Sasso 20 Feb '16

20 Feb '16

Hi, I have MonetDB JUL2015-SP2, with table of 100M rows and 20 coluns, 4 of number type, others string type. A simple "select count(*) from table", run on 90 minutes. Hardware and SO is: Debian 8, 4 cores, 11G RAM and 20G free on disc. While monet run the query, command top, show 42G of virtual memory. Its normal ?, any idea ? thanks. -- Luciano Sasso Vieira Data Scientist & Solutions Architect luciano(a)gsgroup.com.br <http://www.gsgroup.com.br> | tel: 17 3353-0833 | cel: 17 99706-9335 www.gsgroup.com.br <http://www.gsgroup.com.br> --- Este email foi escaneado pelo Avast antivírus. https://www.avast.com/antivirus

3 2

Error in connecting monetdb server to localhost
by Debangana Sarkar 17 Feb '16

17 Feb '16

when I am connecting to MonetDB server it is showing an error : FATAL logger error there is no logger catalog but a log file is found. Are you sure you are using the same combination of database --dbpath and log directory Press any key to continue. Client error is: couldnot connect to localhost:50000 unknow error. Previously I installed the monetDB server and client and it was working.But for some reason it was uninstalled.Now after installing this error is coming. Please help

1 0

is it possible for a user to turn assertions on/off in mserver easily?
by Anthony Damico 16 Feb '16

16 Feb '16

might help with future testing..thanks all

3 3

Re: Re: Some time monetdbd can't start existing dbfarm
by Haiqing Ying 12 Feb '16

12 Feb '16

Thanks, Lefteris. You might be right and the log did suggest some file (.vaultkey) can't be found before it stopped working. I'll move the dbfarm out of /tmp and give it a try. Regards, HQ

1 0

Re: Re: Some time monetdbd can't start existing dbfarm
by Haiqing Ying 11 Feb '16

11 Feb '16

Hi Brian, Thanks for looking into this issue. Here is some more details in meroveringian.log - The DB was up for about a week. There're a lot of lines of 'proxying client ...', until the line 'client error: cannot start database 'EXM': no .vaultkey found...' 2016-02-07 08:08:13 MSG merovingian[12262]: proxying client localhost:33861 for database 'EXM' to mapi:monetdb:///tmp/exm/EXM/.mapi.sock?database=EXM 2016-02-07 08:08:13 MSG merovingian[12262]: target connection is on local UNIX domain socket, passing on filedescriptor instead of proxying 2016-02-07 08:08:13 MSG merovingian[12262]: proxying client localhost:33862 for database 'EXM' to mapi:monetdb:///tmp/exm/EXM/.mapi.sock?database=EXM 2016-02-07 08:08:13 MSG merovingian[12262]: target connection is on local UNIX domain socket, passing on filedescriptor instead of proxying 2016-02-07 08:08:13 MSG merovingian[12262]: proxying client localhost:33863 for database 'EXM' to mapi:monetdb:///tmp/exm/EXM/.mapi.sock?database=EXM 2016-02-07 08:08:13 MSG merovingian[12262]: target connection is on local UNIX domain socket, passing on filedescriptor instead of proxying 2016-02-07 08:36:20 MSG merovingian[12262]: starting database 'EXM', up min/avg/max: 0s/0s/0s, crash average: 1.00 0.10 0.03 (1-0=1) 2016-02-07 08:36:20 ERR merovingian[12262]: client error: cannot start database 'EXM': no .vaultkey found (did you create the database with `monetdb create EXM`?) 2016-02-07 08:36:20 MSG merovingian[12262]: starting database 'EXM', up min/avg/max: 0s/0s/0s, crash average: 1.00 0.10 0.03 (1-0=1) 2016-02-07 08:36:20 ERR merovingian[12262]: client error: cannot start database 'EXM': no .vaultkey found (did you create the database with `monetdb create EXM`?) 2016-02-07 08:36:20 MSG merovingian[12262]: starting database 'EXM', up min/avg/max: 0s/0s/0s, crash average: 1.00 0.10 0.03 (1-0=1) 2016-02-07 08:36:21 ERR merovingian[12262]: client error: cannot start database 'EXM': no .vaultkey found (did you create the database with `monetdb create EXM`?) And then, a lot of lines like below: 2016-02-07 14:44:18 MSG merovingian[12262]: starting database 'EXM', up min/avg/max: 0s/0s/0s, crash average: 1.00 0.10 0.03 (1-0=1) 2016-02-07 14:44:23 ERR merovingian[12262]: client error: cannot start database 'EXM': no .vaultkey found (did you create the database with `monetdb create EXM`?) Eventually, there're lot of errors like this: 2016-02-08 01:13:58 MSG merovingian[12262]: starting database 'EXM', up min/avg/max: 0s/0s/0s, crash average: 1.00 0.10 0.03 (1-0=1) 2016-02-08 01:14:03 ERR merovingian[12262]: client error: cannot start database 'EXM': no .vaultkey found (did you create the database with `monetdb create EXM`?) 2016-02-08 01:14:11 ERR merovingian[12262]: client error: could not retrieve uplog information: could not open file /tmp/exm/EXM/.uplog: Too many open file s 2016-02-08 01:14:18 ERR merovingian[12262]: client error: could not retrieve uplog information: could not open file /tmp/exm/EXM/.uplog: Too many open file s 2016-02-08 01:14:26 ERR merovingian[12262]: client error: could not retrieve uplog information: could not open file /tmp/exm/EXM/.uplog: Too many open file S Then neither mclient nor monetdbd stop cmd works any more. This is what I have in /tmp/exm folder after reboot: [centos@ip-10-72-0-98 exm]$ pwd /tmp/exm [centos@ip-10-72-0-98 exm]$ ls -al total 8088 drwxr-xr-x. 3 centos centos 62 Feb 9 17:33 . drwxrwxrwt. 11 root root 4096 Feb 11 13:36 .. drwx------. 4 centos centos 77 Feb 9 17:33 EXM -rw-------. 1 centos centos 0 Feb 9 17:33 .merovingian_lock -rw-------. 1 centos centos 8272151 Feb 9 17:33 merovingian.log [centos@ip-10-72-0-98 exm]$ ls -al EXM total 8 drwx------. 4 centos centos 77 Feb 9 17:33 . drwxr-xr-x. 3 centos centos 62 Feb 9 17:33 .. drwx------. 66 centos centos 4096 Feb 2 19:48 bat -rw-------. 1 centos centos 0 Feb 7 08:36 .gdk_lock srwx------. 1 centos centos 0 Jan 27 14:50 .mapi.sock drwx------. 3 centos centos 16 Jan 27 14:50 sql_logs -rw-------. 1 centos centos 22 Feb 9 17:33 .uplog If needed, I can send you the whole merovingian.log file (which is 8GB in size). Thanks, HQ

1 0

copy into stdout and redirect to a file
by Anton Kravchenko 11 Feb '16

11 Feb '16

Hi there, I wonder if one can copy a query result into stdout and redirect it to a file? I've tried: mclient.bat -u monetdb -d somedb -s "copy select * from sometable into stdout" - > .\somefile.csv But, only the table header is saved in somefile.csv... P.S. 'select * from sometable' shows all data rows as expected. I am using MonetDB v11.21.11 (Jul2015-SP1) on Windows. Thank you, Anton

2 2

Some time monetdbd can't start existing dbfarm
by Haiqing Ying 11 Feb '16

11 Feb '16

Hi, I created a dbfarm (/tmp/exm ), then created database (EXM), load data, do queries, etc, everything works fine for a week. Then I found mclient stopped working (can't connect to monetdb). The log file shows 'too many files opened' issue. I guess it might be some connection leak issue. I tried 'monetdbd stop /tmp/exm', but it's not responding. It looks I can't stop the dbfarm now. I then reboot the system and after reboot tried 'monetdbd start /tmp/exm' but I got an error saying ' cannot find file in /tmp/exm'. I verified the /tmp/exm folder did exist and there're still sub folder ( ./EXM) and log file still under /tmp/exm. The only option left seems to be creating a new dbfarm and recreating the whole database again. We're moving to use MonetDB in production and if this is a real bug then it would be too risky for production. The question is, is this a real issue? Or it's just some user error that I made? How do I make sure we won't run into this situation again in production mode. Many thanks, HQ

3 2