users-list February 2018

users-list@monetdb.org

15 participants
15 discussions

by Roberto Cornacchia

sql>select current_time(); +-----------------------+ | L2 | +=======================+ | 17:05:10.000000+01:00 | +-----------------------+ 1 tuple (0.796ms) Is it expected that the result type's precision of current_time() is limited to seconds? Any way to get milliseconds?

4 years, 5 months

Monetdb hanging on query

by Manuel Pagliai

Hello, I am using Monetdb as the query engine of an application currently in development. I am connecting to monetdb via a jdbc connection. I can easily reproduce a scenario where monetdb gets stuck on a query, and never complete execution. Although reproducible, the issue is non deterministic, and it can occur while executing different queries, or not occur at all. When it happens I can see the thread stuck at I extracted the queries submitted to monetdb, and execute them via a standalone client (both the native client and a jdbc-based one) rather than our application, but I have been unable to reproduce the issue that way. I guess timing is important. The problem still occur even when queries are submitted sequentially and setting gdk_nr_threads=1 and sql_optimizer=sequential_pipe. The issue happens in Jul 2017-SP3 bugfix release (11.27.11), running on a Windows 7 machine. I have not tried 11.27.13, but by looking at the bug fixes I can't see anything relevant to this issue. Is there something that I can try, or a way to gather more information about what is happening ? I can share both the data, and the complete list of queries used during this scenario. Thanks, Best regards, *Manuel Pagliai* Visokio Ltd www.visokio.com +44 20 7351 4646 P.S. This is the stack-trace of the thread submitting the query when this happens: at java.net.SocketInputStream.socketRead0(Native Method) at java.net.SocketInputStream.socketRead(SocketInputStream.java:116) at java.net.SocketInputStream.read(SocketInputStream.java:171) at java.net.SocketInputStream.read(SocketInputStream.java:141) at java.io.BufferedInputStream.fill(BufferedInputStream.java:246) at java.io.BufferedInputStream.read1(BufferedInputStream.java:286) at java.io.BufferedInputStream.read(BufferedInputStream.java:345) - locked <0x00000000cae0b730> (a java.io.BufferedInputStream) at nl.cwi.monetdb.mcl.net.MapiSocket$BlockInputStream._read(Unknown Source) at nl.cwi.monetdb.mcl.net.MapiSocket$BlockInputStream.readBlock(Unknown Source) at nl.cwi.monetdb.mcl.net.MapiSocket$BlockInputStream.read(Unknown Source) at sun.nio.cs.StreamDecoder.readBytes(StreamDecoder.java:284) at sun.nio.cs.StreamDecoder.implRead(StreamDecoder.java:326) at sun.nio.cs.StreamDecoder.read(StreamDecoder.java:178) - locked <0x00000000cae13810> (a java.io.InputStreamReader) at java.io.InputStreamReader.read(InputStreamReader.java:184) at java.io.BufferedReader.fill(BufferedReader.java:161) at java.io.BufferedReader.readLine(BufferedReader.java:324) - locked <0x00000000cae13810> (a java.io.InputStreamReader) at java.io.BufferedReader.readLine(BufferedReader.java:389) at nl.cwi.monetdb.mcl.io.BufferedMCLReader.readLine(Unknown Source) at nl.cwi.monetdb.mcl.io.BufferedMCLReader.waitForPrompt(Unknown Source) - locked <0x00000000cae137d8> (a nl.cwi.monetdb.mcl.io.BufferedMCLReader) at nl.cwi.monetdb.jdbc.MonetConnection$ResponseList.executeQuery(Unknown Source) - locked <0x00000000cae0b430> (a nl.cwi.monetdb.mcl.net.MapiSocket) at nl.cwi.monetdb.jdbc.MonetConnection$ResponseList.processQuery(Unknown Source) at nl.cwi.monetdb.jdbc.MonetStatement.internalExecute(Unknown Source) at nl.cwi.monetdb.jdbc.MonetStatement.execute(Unknown Source) at nl.cwi.monetdb.jdbc.MonetStatement.executeQuery(Unknown Source)

4 years, 5 months

MonetDB :: Alter statement to change datatype

by Sharma, Sreejith

All, Is it possible to change the datatype of a column in an existing table? With or without data? I saw the only way to change is to create a temporary column and swap if data is present. Is it true? Regards, Sreejith

4 years, 5 months

Re: Order indexes: used or not?

by Stefan Manegold

Hi Torsten, To start mserver5 manually on a database created by monetdb/monetdb, you need to use the same command line (-options) the monedtd use to start mserver5, in particular the valley option. You find the details in the merovingian.log in your dbfarm Best,Stefan -------- Original message --------From: Torsten Grust <torsten.grust(a)gmail.com> Date: 2/20/18 17:08 (GMT+01:00) To: Lefteris <lsidir(a)gmail.com> Cc: Communication channel for MonetDB users <users-list(a)monetdb.org> Subject: Re: Order indexes: used or not? Hi Lefteris, that's very interesting. Thanks! I was about to experiment with this here, but I stumbled. When you start a database via mserver5 directly (instead of using monetdbd which, as I understand, is the recommend method), does the method of authentication change? I fail to login to my scratch database: $  mserver5 --dbpath=<path>/scratch # MonetDB 5 server v11.27.13 "Jul2017-SP4" # Serving database 'scratch', using 4 threads # [...] # MonetDB/SQL module loaded > [switch to other terminal] $ mclient -d scratch -u monetdb password: <supply password "monetdb"> InvalidCredentialsException:checkCredentials:invalid credentials for user 'monetdb' Whereas the login works just fine below: $ monetdbd start <path> $ mclient -d scratch -u monetdb password: <supply password "monetdb"> Welcome to mclient, the MonetDB/SQL interactive terminal (Jul2017-SP4) Database: MonetDB v11.27.13 (Jul2017-SP4), 'mapi:monetdb://closure:50000/scratch' [...] sql> I must be doing something stupid. Cheers, —Torsten On Tue, Feb 20, 2018 at 3:40 PM Lefteris <lsidir(a)gmail.com> wrote: Hi Torsten, the reason why you don't see anything in the EXPLAIN statement is that the use of an index is decided at runtime by the respective operator. The algebra.sort code will have an if statement that determines at query evaluation time if there is a usable ordered index and use it. The best way (for now) to see if the index is used or not is to start the mserver5 with the --algorithms flag. This flag will produce alot of output on the runtime decissions. For your example, I get the following output: #BATproject(l=tmp_523#3-sorted-key,r=tmp_433#3[int]-revsorted-key) #BATproject(l=tmp_523,r=tmp_433)=tmp_323#3-revsorted-key #BATcheckorderidx: reusing persisted orderidx 283 #BATproject(l=tmp_322#3-key,r=tmp_323#3[int]-revsorted-key) #BATproject(l=tmp_322,r=tmp_323)=tmp_417#3-key 11us #BATgroup(b=tmp_417#3[int],s=NULL#0,g=NULL#0,e=NULL#0,h=NULL#0,subsorted=1): trivial case: 1 element per group #BATprojectchain with 3 (2) BATs, size 3, type int #BATproject(l=tmp_322#3-key,r=tmp_323#3[int]-revsorted-key) #BATproject(l=tmp_322,r=tmp_323)=tmp_523#3-key 9us And the third line says "BATcheckorderidx: reusing persisted orderidx 283" which is an indication that MonetDB has found the index and is using it. As a side note, I think in this case it has also figured out that x is revsorted-key (aka unique reverse sorted) but I dont know which one wins over the other:) So in short, if you start mserver5 with --algorithms, redirect the console output in a file and search for BATcheckorderidx you will see if the ordered index is used. Please let us know if there are cases that ordered index should be used but it is not. lefteris On Fri, Feb 16, 2018 at 10:28 AM, Martin Kersten <martin.kersten(a)cwi.nl> wrote: > Hi > Same holds for ordered indices > > On 16/02/2018 10:27, Torsten Grust wrote: >> >> Hi Martin, >> >> thanks for the super-quick reply. You are talking of /imprints/ here but >> I assume that the same is true for /order indexes/ (these are separate >> concepts, right?). >> >> Best wishes, >> —Torsten >> >> >> On Fri, Feb 16, 2018 at 9:55 AM Martin Kersten <martin.kersten(a)cwi.nl >> <mailto:martin.kersten@cwi.nl>> wrote: >> >> Dear Torsten >> >> The use of an imprint is only part of the algebra.select >> implementation >> and depends on actual several properties. >> >> In this particular query, the SQL compiler when encountering an order >> by >> issues a sort statement, even if this is strictly not needed. That >> will be detected >> by the sort implementation. >> >> regards, Martin >> On 15/02/2018 23:31, Torsten Grust wrote: >> > Dear all, >> > >> > given a table definition and (explained) SQL query as follows: >> > >> > |CREATE TABLE foo (x INT,y INT); INSERT INTO foo VALUES (3,1), >> (2,2), (1,3); ALTER TABLE foo SET READ ONLY; CREATE ORDERED INDEX foo_x ON >> foo(x); EXPLAIN SELECT * FROM foo ORDER BY x; | >> > >> > In the |EXPLAIN| output, am I supposed to see that the order index >> is indeed used? I currently do see an |algebra.sort| operation, of course, >> but do not see any reference to the |foo_x| index. I had expected to find an >> |bat.getorderidx| operation in the >> > MAL program. >> > >> > Can anyone shed light on this? I'd be grateful. >> > >> > Cheers, >> > >> > —Torsten >> > >> > -- >> > | Prof. Dr. Torsten Grust >> > | Database Systems — Universität Tübingen (Germany) >> > | ✉︎ torsten.grust(a)uni-tuebingen.de >> <mailto:torsten.grust@uni-tuebingen.de> >> <mailto:torsten.grust@uni-tuebingen.de >> <mailto:torsten.grust@uni-tuebingen.de>> >> > | db.inf.uni-tuebingen.de <http://db.inf.uni-tuebingen.de> >> <http://db.inf.uni-tuebingen.de> >> > >> > >> > _______________________________________________ >> > users-list mailing list >> > users-list(a)monetdb.org <mailto:users-list@monetdb.org> >> > https://www.monetdb.org/mailman/listinfo/users-list >> > >> _______________________________________________ >> users-list mailing list >> users-list(a)monetdb.org <mailto:users-list@monetdb.org> >> https://www.monetdb.org/mailman/listinfo/users-list >> >> -- >> | Prof. Dr. Torsten Grust >> | Database Systems — Universität Tübingen (Germany) >> | ✉︎ torsten.grust(a)uni-tuebingen.de >> <mailto:torsten.grust@uni-tuebingen.de> >> | db.inf.uni-tuebingen.de <http://db.inf.uni-tuebingen.de> >> >> >> _______________________________________________ >> 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 -- | Prof. Dr. Torsten Grust | Database Systems — Universität Tübingen (Germany)| ✉︎ torsten.grust(a)uni-tuebingen.de| db.inf.uni-tuebingen.de

4 years, 5 months

Order indexes: used or not?

by Torsten Grust

Dear all, given a table definition and (explained) SQL query as follows: CREATE TABLE foo (x INT,y INT); INSERT INTO foo VALUES (3,1), (2,2), (1,3); ALTER TABLE foo SET READ ONLY; CREATE ORDERED INDEX foo_x ON foo(x); EXPLAIN SELECT * FROM foo ORDER BY x; In the EXPLAIN output, am I supposed to see that the order index is indeed used? I currently do see an algebra.sort operation, of course, but do not see any reference to the foo_x index. I had expected to find an bat.getorderidx operation in the MAL program. Can anyone shed light on this? I'd be grateful. Cheers, —Torsten -- | Prof. Dr. Torsten Grust | Database Systems — Universität Tübingen (Germany) | ✉︎ torsten.grust(a)uni-tuebingen.de | db.inf.uni-tuebingen.de

4 years, 5 months

matrix manipulation

by Jiří Fejfar

Dear MonetDB users! I would like to do some simple calculations (matrix multiplication and inversion) with large matrices (cca 40k by 40k or more). We are using PostgreSQL & R but we are facing problems when matrix doesn't fit memory. My initial idea was to use SciQL, but probably it is not merged into MonetDB yet [1]? I am sending also short presentation [2] illustrating nature of our data and calculation (slide 6). Thank you for your suggestions! Jiří. [1] https://www.monetdb.org/pipermail/users-list/2014-May/007319.html [2] https://drive.google.com/open?id=1P4Nuo_YuqhNyxoBqQHeIw6WiXe28I8kE

4 years, 5 months

Select from remote table leaves session open

by Николай Чернов

I have two databases (v11.27.13 both) on two Debian8 machines. One with corrupted table (in my case it got corrupted due to bug in Jul2017-SP1) and another with remote table to the corrupted table. When I run the SELECT query from the remote table I get an error. And the session, opened by this query to the first DB did not close. I see it by querying 'sys.sessions' table. And every time I run this SELECT I get additional record in 'sys.sessions'. This happens only if en error occurs, on successful SELECT the session immediately disappears. How can I force this sessions to close or is it a bug?

4 years, 5 months

Converting a timestamp to a UNIX timestamp

by Bernát Marcell

Dear MonetDB users, I'm looking for a way to convert a timestamp (or timestamp string) like "2018-02-14 23:59:59" to a UNIX timestamp using MonetDB's functions. In MySQL this can be done using the function UNIX_TIMESTAMP(), so I'm looking for the MonetDB equivalent of that, if it exists. I found sys.epoch(), but that does the opposite of what I need. Thanks in advance. Best regards, Marcell

4 years, 5 months

Announcement: New Jul2017-SP4 Bugfix release of MonetDB suite

by Sjoerd Mullender

The MonetDB team at CWI/MonetDB BV is pleased to announce the Jul2017-SP4 bugfix release of the MonetDB suite of programs. More information about MonetDB can be found on our website at <http://www.monetdb.org/>. For details on this release, please see the release notes at <http://www.monetdb.org/Downloads/ReleaseNotes>. As usual, the download location is <http://dev.monetdb.org/downloads/>. Jul 2017-SP4 bugfix release (11.27.13) Build Environment * Added the .lib and .h files needed for building extensions to the Windows installer. Bug Fixes * 3470: Support setClob without length restrictions * 6468: JDBC 2.27 fails with year < 1000 * 6482: Query failures on order by on union * 6483: Monetdb crashes, on query * 6487: UNION of NULLs with several tables fails * 6488: Semijoin returns duplicate values from a column with unique values * 6489: Sqlitelogictest - Wrong result set of complex conditional query * 6490: Sqlitelogictest - Select query with an IN clause parse error * 6491: SELECT IN returns NULL instead of false when not found * 6492: Persistent hashes stored and then ignored. Storage info not in sync with actual indices. * 6493: Sqlitelogictest - Aggregation query on empty table with DISTINCT clause * 6494: Sqlitelogictest - Algebra operators priority in select query * 6495: Sqlitelogictest - Omitting AS in a result set column alias name * 6496: Sqlitelogictest - Select interval comparisons between floating-points and NULL * 6497: Sqlitelogictest - Select not between query producing wrong results * 6498: large virtual memory spike on BLOB column COUNT * 6499: Crash when trying to replace a function defined in sys from a different schema * 6502: Query with multiple limit clauses does not return anything * 6508: Segmentation fault in mserver5 on Python2 UDF with TIMESTAMP column input that has NULL values (conversion.c:438, PyNullMask_FromBAT) * 6510: Sqlitelogictest: Wrong output in aggregation query * 6512: Monetdb crashes on query with limit after sort with case * 6514: Sqlitelogictest: Range query between NULL values not possible * 6515: Insert null second interval value results in 0 * 6516: Sqlitelogictest unknown bat append operation * 6517: Sqlitelogictest overflow in conversion during MAL plan execution * 6518: Sqlitelogictest: count aggregation with not in operator * 6519: Sqlitelogictest: algebra join between lng and int BATs undefined * 6520: UPDATE with correlated subquery causes assertion (or segfault) * 6522: Sqlitelogictest: IN operator return a single column * 6523: Sqlitelogictest: Case statement subquery missing * 6524: Sqlitelogictest: Crash in aggregation query with IN operator * 6527: Crash using order by alias in subquery

4 years, 5 months

MonetDB Connection drops issue on concurrency

by Sharma, Sreejith

Hi All, We have a situation where we are trying to emulate a scenario where we have multiple clients hitting into MonetDB instance in parallel, I mean with concurrency of for say 12 users. Queries are going through fine, however we notice connection drops in some cases and queries get cancelled. We assume, this happens when port is idle and when trying to reconnect there is a SQLConnect failure issues. If it's queries are firing uninterruptedly no issues spotted, if there is any idle situation the issue occurs. Can the "forward" - "redirect" option help here? If not, what other options? Regards, Sreejith

4 years, 5 months

2022

2021

2020

2019

2018

2017

2016

2015

2014

2013

2012

2011

2010

2009

2008

2007

2006

2005

2004

users-list February 2018