October 2015 - users-list

Server crash / lib_sql.so
by Mathieu Raillard 26 Oct '15

26 Oct '15

Hi all, We are using MoentDB since 6 months with quite good results.(thanks to the dev team for their good work) Unfortunately yesterday we came across an issue for which we can't find a solution. Our database crashed for an unknown reason (RAM was fine, Disk space was fine) and now each time we re-use the same table with a query we used during the crash, the database crash with a seg fault Query : select * from storage(); OS Version : CentOS release 6.7 (Final) MonetDB Version : MonetDB Database Server Toolkit v1.1 (Jul2015) tail /var/log/messages : kernel: mserver5[9945]: segfault at 18 ip 00007f9e86fd534b sp 00007f9e862912e0 error 4 in lib_sql.so[7f9e86eb1000+182000] merovingian.log : database 'BI-DEV' (9972) was killed by signal SIGSEGV Log from gbd : Program received signal SIGSEGV, Segmentation fault. [Switching to Thread 0x7ffa24edc700 (LWP 4683)] 0x00007ffa25c1f34b in delta_bind_bat () from /usr/lib64/monetdb5/lib_sql.so (gdb) bt #0 0x00007ffa25c1f34b in delta_bind_bat () from /usr/lib64/monetdb5/lib_sql.so #1 0x00007ffa25b20fee in sql_storage () from /usr/lib64/monetdb5/lib_sql.so #2 0x00007ffa321aea45 in runMALsequence () from /usr/lib64/libmonetdb5.so.19 #3 0x00007ffa321afe29 in callMAL () from /usr/lib64/libmonetdb5.so.19 #4 0x00007ffa25b369f7 in SQLexecutePrepared () from /usr/lib64/monetdb5/lib_sql.so #5 0x00007ffa25b36efc in SQLengineIntern () from /usr/lib64/monetdb5/lib_sql.so #6 0x00007ffa321c87dd in runScenarioBody () from /usr/lib64/libmonetdb5.so.19 #7 0x00007ffa321c886f in runScenario () from /usr/lib64/libmonetdb5.so.19 #8 0x00007ffa321c9738 in MSserveClient () from /usr/lib64/libmonetdb5.so.19 #9 0x00007ffa321ca816 in MSscheduleClient () from /usr/lib64/libmonetdb5.so.19 #10 0x00007ffa32266e0f in doChallenge () from /usr/lib64/libmonetdb5.so.19 #11 0x00007ffa31d2adcf in thread_starter () from /usr/lib64/libbat.so.12 #12 0x00007ffa2f97da51 in start_thread (arg=0x7ffa24edc700) at pthread_create.c:301 #13 0x00007ffa2f6ca93d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:115 Any help to understand and correct what is going on would be nice. Regards Mathieu

3 7

overloaded ram on windows
by Anthony Damico 23 Oct '15

23 Oct '15

hi, this is just a general request for the awesome folks at cwi: could you give some serious thought to optimizing ram usage on windows? this happens to me a lot, i end up just leaving these big joins to run overnight-- http://s30.postimg.org/saeo9ca7l/overloaded.png happy to write up some r-driven reproducible examples if there's willingness to combat these bottlenecks. thanks as always from your biggest fan.

1 1

Offset timestamp field with random interval
by Robin Cijvat 21 Oct '15

21 Oct '15

Hello everyone! I am facing some (probably simple) problems. A snippet of a timestamp column in my table: +----------------------------+ | ts | +============================+ | 1595-04-02 00:00:00.000000 | | 1595-04-02 00:00:00.000000 | | 1595-04-02 00:00:00.000000 | | 1595-04-02 00:00:00.000000 | | 1598-03-25 00:00:00.000000 | +----------------------------+ I want to fill all the times in these fields with a random value (uniformly distributed is fine). This faces me with the following problems: 1) Adding an interval to a timestamp does not seem to be suited for dynamic intervals: This works fine: UPDATE "table" SET ts = (ts + INTERVAL '5000' SECOND); This doesn't: UPDATE "table" SET ts = (ts + INTERVAL (CAST(5000 AS STRING)) SECOND); Does someone maybe know of a way to accomplish this? 2) Does someone know how to normalize the output of the RAND operator available in SQL? It seems to calculate a random BIGINT, and in order for me to normalize it, I would need the maximum value of a BIGINT, which of course is platform dependent. Is there a way to get the max value of BIGINT on the SQL level? Or can I go about this another way? All help is much appreciated! Robin

4 11

Presto Connector for MonetDB
by Stephen P. Morgan 20 Oct '15

20 Oct '15

Does anyone know of a Presto connector for MonetDB? There are connectors for MySQL and PostgreSQL among others, but I don't see one for MonetDB. Thanks, Steve

2 1

Transactions
by Doug Service 19 Oct '15

19 Oct '15

We use MonetDB extensively in an internal system we are developing. We initially had issues getting transactions working so we put it off to make progress in other areas. However we are at the point now were we need to have transactions working. The mapi documentation ( https://www.monetdb.org/Documentation/Manuals/SQLreference/Programming/MAPI) says the following about auto commit. MapiMsg mapi_setAutocommit(Mapi mid, int autocommit) Set the autocommit flag (default is on). This only has an effect when the language is SQL. In that case, the server commits after each statement sent to the server. I infered from this statement that using transactions requires setting auto commit to false. However, when I did that I received the following error when I tried to use transactions. MSERVER(25001!START TRANSACTION: cannot start a transaction within a transaction) If I did not disable auto commit, I did not receive any errors when using transactions, but I did not know if the transaction were actually working. After using hg to enlist in the MonetDb code and searching for the error I found this. str SQLtransaction2(Client cntxt, MalBlkPtr mb, MalStkPtr stk, InstrPtr pci) { mvc *sql = NULL; str msg; (void) stk; (void) pci; if ((msg = getSQLContext(cntxt, mb, &sql, NULL)) != NULL) return msg; if ((msg = checkSQLContext(cntxt)) != NULL) return msg; if (sql->session->auto_commit == 0) throw(SQL, "sql.trans", "25001!START TRANSACTION: cannot start a transaction within a transaction"); if (sql->session->active) { RECYCLEdrop(cntxt); mvc_rollback(sql, 0, NULL); } sql->session->auto_commit = 0; sql->session->ac_on_commit = 1; sql->session->level = 0; (void) mvc_trans(sql); return msg; } which leads me to believe that one should not disable auto commit to use transaction as it appears to be auto disabled by the transaction code. My questions are: 1) Is the correct method of using transactions in MonetDb to leave the auto commit flag alone and just issue START TRANSACTION;, COMMIT;, and ROLLBACK; as needed? 2) If the answer to (1) is yes, is disabling auto commit, issuing updates, and then re enabling auto commit like a transaction? Doug Service

3 3

MonetDB not cleaning intermediate results which leads to filling up disk space
by abraun_75＠yahoo.de 19 Oct '15

19 Oct '15

Hi, Right now I am evaluating MonetDB and try to analyze a dataset that consists of 10 million rows. The data set has 2 tables, table a stores 10 million rows, table b stores 1 million rows. Both tables are being joined and some statistical values are being calculated (QUARTILE, AVG, SUM and GROUP BY). MonetDB is promising fast here. When starting up the database and executing the query the result is being returned within 4 seconds. I tried to execute the same SQL query 1000 times sequentially and to my surprise after the 60th iteration monetDB crashed because of insuffcient disk space. As far as I read intermediate results are being materialized within the bat folder. The .tail files are not being removed after a query was executed successfully and the files are not even removed after the database conenction is being closed. When I restart the server (dbfarm) the files are cleaned up perfectly. I am just wondering if this default behaviour can be changed? Are there any ways to enforce cleaning up the files? How to avoid a server crash because of insufficient memory? For the system I am planning to use monetDB for I am not able to restart the server before I execute the SQL queries. Thank you very much, Alex

5 8

current schema
by Enzo 19 Oct '15

19 Oct '15

Thanks to Jennie I understand that I can get the current schema with the query: select current_schema from sys.var(); While I get the “right” response, I get it as a series of duplicated lines (10 at the last count): For example, after setting a schema as set schema viewer; I get the following 10 lines from the query: Is it the right behaviour? And if yes, do these lines have a specific significance? Thanks Enzo enzo(a)smartinsightsfromdata.com

2 1

Re: users-list Digest, Vol 38, Issue 9
by Luciano Sasso 18 Oct '15

18 Oct '15

Jul 2015 is avaible for DEbian 7 ? At --- Este email foi escaneado pelo Avast antivírus. https://www.avast.com/antivirus

3 2

How to install tachograph?
by Xu,Wenjian 18 Oct '15

18 Oct '15

Hi all, I have installed the latest Jul2015 release, and I would like to use the latest tools tachograph to determine the progress of query execution. But I do not know how to install it. My system is ubuntu 14.04. I have tried to use *sudo apt-get install monetdb-tools* but failed to locate the package *monetdb-tools*. Any idea would be helpful. Thanks. Best, XU Wenjian

3 2

DataCell Stream
by Luciano Sasso 18 Oct '15

18 Oct '15

DataCel stream discontinead in new versions MonetDB ? A Idea, substitute for Proxy Node.JS ? --- Este email foi escaneado pelo Avast antivírus. https://www.avast.com/antivirus

3 3