July 2017 - users-list

Monetdb Installation
by Amit Ambekar 24 Jul '17

24 Jul '17

Hi, July 2017 release is not working good . I am getting error while login using terminal in Ubuntu OS. When new release will be done ??

2 1

Bug on timestamp ?
by Ariel Abadi 21 Jul '17

21 Jul '17

Hi! May be someone can help me, Im facing the following problem. Im trying to pull some information from the field nrm_connect_time, I tried almost all the date/timestamp functions, but all of them failed. Finally, I decided to CAST as STRING, and see what happends... If I do a SELECT DISTINCT .. then I have the correct information, if I dont include the DISTINCT what I see has nonsense... sql>SELECT nrm_connect_time, LEFT(CAST(nrm_connect_time AS STRING),10) FROM NRM_20170609 LIMIT 5; +----------------------+-----------------------+ | nrm_connect_time | left_nrm_connect_time | +======================+=======================+ | 2017-06-09 00:05:00 | -1-01-01 0 | | 2017-06-09 00:05:00 | -1-01-01 0 | | 2017-06-09 00:05:01 | -1-01-01 0 | | 2017-06-09 00:05:03 | -1-01-01 0 | | 2017-06-09 00:05:03 | -1-01-01 0 | +----------------------+-----------------------+ 5 tuples (29.802ms) sql>*SELECT DISTINCT* nrm_connect_time, LEFT(CAST(nrm_connect_time AS STRING),10) FROM NRM_20170609 LIMIT 5; +----------------------+------------+ | nrm_connect_time | L2 | +======================+============+ | 2017-06-09 00:05:00 | 2017-06-09 | | 2017-06-09 00:05:01 | 2017-06-09 | | 2017-06-09 00:05:03 | 2017-06-09 | | 2017-06-09 00:05:04 | 2017-06-09 | | 2017-06-09 00:05:05 | 2017-06-09 | +----------------------+------------+ 5 tuples (424.306ms) *The "nrm_connect_time" is defined as TIMESTAMP(0).* Let me show you a differente example: sql>*SELECT DISTINCT *nrm_connect_time, LEFT(CAST(nrm_connect_time AS STRING),10), timestamp_to_str(nrm_connect_time, '%Y') FROM NRM_20170609 LIMIT 5; +----------------------+------------+------+ | nrm_connect_time | L2 | L3 | +======================+============+======+ | 2017-06-09 00:05:00 | 2017-06-09 | 2017 | | 2017-06-09 00:05:01 | 2017-06-09 | 2017 | | 2017-06-09 00:05:03 | 2017-06-09 | 2017 | | 2017-06-09 00:05:04 | 2017-06-09 | 2017 | | 2017-06-09 00:05:05 | 2017-06-09 | 2017 | +----------------------+------------+------+ 5 tuples (707.217ms) sql>*SELECT *nrm_connect_time, LEFT(CAST(nrm_connect_time AS STRING),10), timestamp_to_str(nrm_connect_time, '%Y') FROM NRM_20170609 LIMIT 5; TypeException:user.s16_55[123]:'mtime.timestamp_to_str' undefined in: mtime.timestamp_to_str(X_196:oid,A1:str); program contains errors Regards Ariel

3 3

Re: Re: Add entry in a table only if that entry does not already exist
by Stefano Piani 21 Jul '17

21 Jul '17

Hello, sorry for my late replay, I have been really busy in the last few days. > Hello Stefano, > > Which MonetDB version do you use? On which OS? Sorry, I forgot to mention this. I am using ubuntu 16.04 and I am running MonetDB inside a Docker container (which also runs ubuntu). >> On 4 Jul 2017, at 16:32, Stefano Piani <stefano.piani(a)exact-lab.it> wrote: >> >> Dear monetdb users, >> >> I started to use MonetDB a few weeks ago and, until now, I have been >> able to use it proficiently. Unfortunately, I have faced a small issue >> that I have not been able to figure out how to fix. >> >> I collect data generated by different users. A unique id is associated >> to each user. I have a specific table (called "user") where I save the >> name and some other information about the user. In this table, the ID >> is a primary key. >> When I collect the data, I want to check if the specific user who >> generated that data is already saved in the user table. If this is not >> the case, >> I want to add a row to the user table for this user. >> Unfortunately, I have not been able to accomplish this operation in >> one SQL query and, therefore, I have been forced to query for the user >> id and, >> if the answer is negative, add the user using another query. The >> following is the python script that perform the operations. >> >> def save_user_in_database(user, cursor): >> db_line = 'SELECT 1 FROM users WHERE user_id = {}'.format(user.id) >> LOGGER.info('Executing on DB: %s', db_line) >> cursor.execute(db_line) >> >> answer = cursor.fetchall() >> LOGGER.info('Get this answer %s', answer) >> if len(answer) == 0: >> LOGGER.info('Inserting new user') >> >> db_line = sql_cmd_from_template( >> USER_TABLE_INSERT_TEMPLATE, >> user.id, >> user.name, >> user.description, >> user.location >> ) >> LOGGER.info('Executing on DB: %s', db_line) >> cursor.execute(db_line) > >Do you execute the queries in a different schema than the default ?sys? schema (which is recommended for database users)? >Because ?users? is a special VIEW in the ?sys? schema. > Yes, I should be in a different schema. In any case, I changed the name of the table just to be on the safe way. >> >> This function is executed inside a single threaded application. >> Moreover, there are no "DELETE" or similar operations inside this >> script (it only adds rows to the database). >> No user (human or software) but this script connects to my MonetDB instance. >> >> Unfortunately, after I while that the script is running (let's say one >> or two days), something unexpected happens. For example, this is the >> log generated for user 42847029 >> (there are a lot of other lines in between that I removed). As you can >> see, after a while, my check fails and I don't understand why. >> >> 2017/06/13 09:30:01 - INFO - save_user_in_database: Executing on DB: >> SELECT 1 FROM users WHERE user_id = 42847029 >> 2017/06/13 09:30:01 - INFO - save_user_in_database: Get this answer [(1,)] >> >> 2017/06/13 10:00:04 - INFO - save_user_in_database: Executing on DB: >> SELECT 1 FROM users WHERE user_id = 42847029 >> 2017/06/13 10:00:04 - INFO - save_user_in_database: Get this answer [(1,)] >> >> 2017/06/13 10:02:33 - INFO - save_user_in_database: Executing on DB: >> SELECT 1 FROM users WHERE user_id = 42847029 >> 2017/06/13 10:02:33 - INFO - save_user_in_database: Get this answer [(1,)] >> >> 2017/06/13 10:30:36 - INFO - save_user_in_database: Executing on DB: >> SELECT 1 FROM users WHERE user_id = 42847029 >> 2017/06/13 10:30:36 - INFO - save_user_in_database: Get this answer [] >> 2017/06/13 10:30:36 - INFO - save_user_in_database: Inserting new user > >This shouldn?t happen, as is shown by the error below that a user_id of 42847029 still exists; also, as you mentioned, there hasn?t been any delete queries. > >This might be a bug in MonetDB. > >Is this problem repeatable? If yes, can you give us a sequence of queries to reproduce this problem. We?d like to look into it. > I will try to reproduce the problem with some scripts as soon as possible. Unfortunately, I am quite busy in this period and therefore this will require some time. In any case, this happens only when the system is busy and the table is big enough. I think that the key to reproduce the problem is to stress the database with several operations per second. In this situation, I have noticed that the db starts to use a lot of RAM (about 15 GB for a database that occupies 3 GB on the disk). At that point, the db becomes extremely slow (for example, for a query that usually takes less than 10 ms I have to wait for more than 2 seconds) and the unique key problem start to appear. > >> 2017/06/13 10:30:36 - INFO - save_user_in_database: Executing on DB: >> INSERT INTO users(user_id, user_name, user_description, user_location) >> VALUES (42847029, 'XXXX', 'XXXX', 'XXXX') >> 2017/06/13 10:30:36 - WARNING - on_data: Error elaborating data >> Traceback (most recent call last): >> File "data_collector.py", line 267, in on_data >> return self.elaborate_data(data) >> File "data_collector.py", line 354, in elaborate_data >> save_user_in_database(user, cursor) >> File "data_collector.py", line 234, in save_user_in_database >> cursor.execute(db_line) >> File "/opt/Python-3.6.0/lib/python3.6/site-packages/pymonetdb-1. 0.4-py3.6.egg/pymonetdb/sql/cursors.py", >> line 164, in execute >> block = self.connection.execute(query) >> File "/opt/Python-3.6.0/lib/python3.6/site-packages/pymonetdb-1. 0.4-py3.6.egg/pymonetdb/sql/connections.py", >> line 133, in execute >> return self.command('s' + query + ';') >> File "/opt/Python-3.6.0/lib/python3.6/site-packages/pymonetdb-1. 0.4-py3.6.egg/pymonetdb/sql/connections.py", >> line 138, in command >> return self.mapi.cmd(command) >> File "/opt/Python-3.6.0/lib/python3.6/site-packages/pymonetdb-1. 0.4-py3.6.egg/pymonetdb/mapi.py", >> line 235, in cmd >> raise exception(string) >> pymonetdb.exceptions.IntegrityError: INSERT INTO: PRIMARY KEY >> constraint 'users.users_user_id_pkey' violated >> >> Have you any advice? Is there a way to perform the same check in just >> one SQL line? Is this way atomic (if a would ever move to a >> multithreading script)? > >It is not atomic, but you can make it atomic by wrapping both queries into one transaction. > >> Am I doing something >> wrong with my current code? Please, take into account that I do not >> have a deep experience with other SQL tools so it is perfectly >> possible that I miss something >> that for you is extremely obvious. > >No, you?re not doing anything wrong. But there are better alternatives: > >1. If this is a standalone action (i.e. not part of a transaction), the check for existing user_id is not necessary. The simplest and most efficient way is to always do the insert and ignore the ?primary key constraint ? violated? >error (e.g. use a try-catch block). > >2. If this insert is going to be part of a (big, long) transaction, then you won?t want the insert to fail. In that case, you can use a stored procedure: > >create procedure insert_new (id int, name varchar(10), description varchar(10), location varchar(10)) >begin > declare cnt int; > set cnt = (select count(*) from myusers where user_id=id); > > if (cnt = 0 ) > then > insert into myusers values (id, name, description, location); > end if; >end; > >Then call the procedure: call insert_new (42847029, 'XXXX', 'XXXX', 'XXXX?) > >I hope this helps. > II tried the solution number 2, but unfortunately the problem is still there. I get an Integrity error even calling the procedure that you described before. What is really strange is that, after a while, execute the procedure that you have showed me requires more than 3 seconds on my system. If I restart the monetdb instance, it takes a few milliseconds again. I think that maybe there is something broken in my setup. It could be that I have misconfigured something or I have a problem with my machine. Unfortunately, taking also into account that I am using Docker (which adds a layer of complexity), there are plenty of things that could not work as expected. Thank you for your help! >Regards, >Jennie > >> >> Thank you in advance! >> >> Best regards, >> >> Stefano Piani

2 1

MonetDB :: Memory Blots Up On High Concurrency
by Sharma, Sreejith 21 Jul '17

21 Jul '17

Hi, Does anyone encountered memory blot issue on high concurrency? Also, I don't see it coming down when idle. When its blots up, it's slows down the response time. Regards, Sreejith

3 6

Monetdb Installation
by amit＠g-square.in 21 Jul '17

21 Jul '17

Hi, how to install monetdb's old version June 2017 for ubuntu ??

2 1

Error trying to login
by amit＠g-square.in 20 Jul '17

20 Jul '17

Hi, I am using Ubuntu OS. I have created dbpharm and database. Now i am trying to login mclient using terminal but it gives following error. Please reply asap. root@mp-VirtualBox:/var/lib/monetdb# mclient -u monetdb -d narrator password: TypeException:user.sql.init[5]:'sqlcatalog.create_function' undefined in: sqlcatalog.create_function(X_1:str, X_3:str, X_5:ptr); MALException:optimizer.MALoptimizer:Start with inconsistent MAL plan

2 1

Re: how to know imprints take effect?
by integrity 20 Jul '17

20 Jul '17

Hi Stefan, Nice talking to you again! Sorry my query in the image didn't show up, so I paste it in text now. The query is a simple select with equals. ======================================== sql>select * from id14 where id=1461; +------+-------+------+ | id | label | cid | +======+=======+======+ | 1461 | false | 0 | +------+-------+------+ 1 tuple (104.549ms) sql> sql> sql>select row_number() over (),* from storage() where "table"='id14'; +------+--------+-------+--------+---------+----------+----------+---------+-----------+------------+----------+----------+-------+----------+--------+-----------+--------+----------+------+ | L5 | schema | table | column | type | mode | location | count | typewidth | columnsize | heapsize | hashes | phash | imprints | sorted | revsorted | unique | orderidx | L5 | +======+========+=======+========+=========+==========+==========+=========+===========+============+==========+==========+=======+==========+========+===========+========+==========+======+ | 1 | sys | id14 | id | bigint | writable | 12/1244 | 2811606 | 8 | 22492848 | 0 | 28049448 | false | 0 | false | false | null | 0 | 1 | | 2 | sys | id14 | label | boolean | writable | 02/254 | 2811606 | 1 | 2811606 | 0 | 0 | false | 0 | null | null | null | 0 | 2 | | 3 | sys | id14 | cid | bigint | writable | 12/1231 | 2811606 | 8 | 22492848 | 0 | 0 | false | 0 | false | false | null | 0 | 3 | +------+--------+-------+--------+---------+----------+----------+---------+-----------+------------+----------+----------+-------+----------+--------+-----------+--------+----------+------+ 3 tuples (10.090ms) ===================================================== Best, Meng ------------------ Original ------------------ From: "Stefan Manegold";<Stefan.Manegold(a)cwi.nl>; Date: Jul 19, 2017 To: "Communication channel for MonetDB users"<users-list(a)monetdb.org>; Subject: Re: how to know imprints take effect? Hi Meng, most important question: From what kind of queries do you expect millisecond responses over a table of 15,000,000 rows? Thanks! Best, Stefan ----- On Jul 19, 2017, at 12:37 PM, integrity 357416268(a)qq.com wrote: > Hi all, > > I am back, and I am using MonetDB Dec2016-SP5 with "enable-optimize" to do graph > computing. I remember that imprints are suitable for all numeric types, > however, after querying on the bigint column for many times, I found there is > no imprints. > > > > What I want to achieve is to "make" MonetDB respond in milliseconds, although I > know that in MonetDB it is not necessary to create index like imprints. > So, on a table of 15,000,000 rows, can I expect to get response in milliseconds? > and whether imprints will help with this? > > Any comments is appreciated! > > Best regards, > Meng > > _______________________________________________ > users-list mailing list > users-list(a)monetdb.org > https://www.monetdb.org/mailman/listinfo/users-list -- | Stefan.Manegold(a)CWI.nl | DB Architectures (DA) | | www.CWI.nl/~manegold/ | Science Park 123 (L321) | | +31 (0)20 592-4212 | 1098 XG Amsterdam (NL) | _______________________________________________ users-list mailing list users-list(a)monetdb.org https://www.monetdb.org/mailman/listinfo/users-list

2 1

how to know imprints take effect?
by integrity 19 Jul '17

19 Jul '17

Hi all, I am back, and I am using MonetDB Dec2016-SP5 with "enable-optimize" to do graph computing. I remember that imprints are suitable for all numeric types, however, after querying on the bigint column for many times, I found there is no imprints. What I want to achieve is to "make" MonetDB respond in milliseconds, although I know that in MonetDB it is not necessary to create index like imprints. So, on a table of 15,000,000 rows, can I expect to get response in milliseconds? and whether imprints will help with this? Any comments is appreciated! Best regards, Meng

2 1

Announcement: New Jul2017 Feature release of MonetDB suite
by Sjoerd Mullender 18 Jul '17

18 Jul '17

The MonetDB team at CWI/MonetDB BV is pleased to announce the Jul2017 feature 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 feature release MonetDB5 Server * The "sub" prefix of many functions, both at the MAL and the C level, has been removed. * Changed the interfaces of the AUTH* functions: pass values, not pointers to values. * Removed calc.setoid(). * group.subgroup is now called group.group if it is not refining a group. Both group.group and group.subgroup now also have variants with a candidate list. * The allocation schemes for MAL blocks and Variables has been turned into block-based. This reduces the number of malloc()/free() calls. . * Added a new server-side protocol implementation. The new protocol is backwards compatible with the old protocol. Clients can choose whether they want to use the old or the new protocol during the initial handshake with the server. The new protocol is a binary column-based protocol that is significantly faster than the old protocol when transferring large result sets. In addition, the new protocol supports compression using Snappy or LZ4. * Moved the sphinx extension module to its own repository. See https://dev.monetdb.org/hg/MonetDB-sphinx/. * Removed GSL module: it's now a separate (extension) package. See https://dev.monetdb.org/hg/MonetDB-gsl/. * The PCRE library is now optional for systems that support POSIX regular expressions. * Added 5 new sys schema tables: function_languages, function_types, key_types, index_types and privilege_codes. They are pre-loaded with static content and contain descriptive names for the various integer type and code values. See also sql/scripts/51_sys_schema_extension.sql Merovingian * Added handling of a dbextra property per database at the daemon level. The user can set the dbextra property for a database using the command: $ monetdb set dbextra=<path> <database> and the daemon will make sure to start the new server using the correct --dbextra parameter. Client Package * The mclient and msqldump programs lost compatibility with old mserver5 versions (pre 2014) which didn't have a "system" column in the sys.schemas table. * The mclient and msqldump programs lost compatibility with ancient mserver5 versions (pre 2011) which didn't have the sys.systemfunctions table. * Removed the "array" and "quick" functions from the mapi library. To be precise, the removed functions are: mapi_execute_array, mapi_fetch_field_array, mapi_prepare_array, mapi_query_array, mapi_quick_query, mapi_quick_query_array, and mapi_quick_response. * Added a more elaborate \help command for SQL expressions. MonetDB Common * Improved error checking in the logger code (dealing with the write-ahead log); changed return types a several functions from int to gdk_return (i.e., they now return GDK_SUCCEED or GDK_FAIL). The logger no longer calls GDKfatal on error. Instead the caller is responsible for dealing with errors. * BATsort may now create an order index as a by product. * Quantile calculations now use the order index if available (and use BATsort otherwise, producing an order index). * Quantiles calculate a position in the sorted column. If this position is not an integer, we now choose the nearest position, favoring the lower if the distance to the two adjacent positions is equal (round down to nearest integer). * Removed function BATprintf. Use BATprint or BATprintcolumns instead. * Removed BATsave from the list of exported functions. * Replaced BBPincref/BBPdecref with BBPfix/BBPunfix for physical reference count and BBPretain/BBPrelease for logical reference count maintenance. * Removed automatic conversion of 32-bit OIDs to 64 bits on 64-bit architectures. * Removed functions OIDbase() and OIDnew(). * Removed talign field from BAT descriptor. * BATappend now takes an optional (NULL if not used) candidate list for the to-be-appended BAT. * New function BATkeyed(BAT *b) that determines (possibly using a hash table) whether all values in b are distinct. SQL * Made the operator precedence of % equal to those of * and /. All three are evaluated from left to right. * Removed table sys.connections. It was a remnant of an experimental change that had already been removed in 2012. * Protect against runaway profiler events If you hit a barrier block during profiling, the JSON event log may quickly become unwieldy. Event production is protected using a high water mark, which ensures that never within the single execution of MAL block the instruction causes excessive event records. Bug Fixes * 3465: Request: add support for CREATE VIEW with ORDER BY clause * 3545: monetdb commands don't work with -h -P -p options (locally and remotely) * 3996: select * from sys.connections always returns 0 rows. Expected to see at least one row for the active connection. * 6187: Nested WITH queries not supported * 6225: Order of evaluation of the modulo operator * 6289: Crashes and hangs with remote tables * 6292: Runaway SQL optimizer in too many nested operators * 6310: Name resolution error (sqlsmith) * 6312: Object not found in LIMIT clause (sqlsmith) * 6313: Null type resolution in disjunction fails (sqlsmith) * 6319: Server crash on LATERAL (sqlsmith) * 6322: Crash on disjunction with LIMIT (sqlsmith) * 6323: Deadlock calling sys.bbp() * 6324: Sqlitelogictest crash in a IN query (8th) * 6327: The daemon does not respect the actual name of the mserver5 executable * 6330: Sqlitelogictest crash on a complex SELECT query * 6331: sys.statistics column "nils" always contains 0. Expected a positive value for columns that have one or more nils/NULLs * 6332: Sqlitelogictest crash related to an undefined MAL function

3 3

possible to change the decimal character from "." to ", " for the COPY INTO command?
by Anthony Damico 18 Jul '17

18 Jul '17

hi, i'm curious if this is possible to implement? not seeing it in the docs.. for example, in R you can use read.csv() which uses , as the delimiter and . as the decimal or you can use read.csv2 which uses ; as the delimiter and , as the decimal thanks all

1 0