January 2018 - users-list

Memory leak in MonetDB
by Sergii Kliuchnyk 23 Jan '18

23 Jan '18

Hello, my name Sergii. I hope this is right place for this message. In our company we using MonetDB near 1 year and we have only one problem with it - memory leak. We found only one dependence - if we making queries then memory starts growing. I read "Memory footprint" article that MonetDB excessively uses main memory for processing, but I thought it should stop allocate memory after sometime but it's not. And looks like this allocated memory is not gives some performance. What we do now is restarting monet server every month. I found on mserver5 man-page parameter "gdk_vmtrim" but I'm not 100% sure is it what we need and what correct way to apply this parameter. Maybe MonetDB SQL has some function to unload no needed memory? Maybe it's php/nodejs clients (from github.com/MonetDB repo) somehow cause this memory leaks? I will appreciate any help

1 1

BUG: on CREATE TABLE (like)
by Ariel Abadi 18 Jan '18

18 Jan '18

Hi! It seems to be, that there is a BUG on the statement CREATE TABLE.. It is not being consider the *DEFAULT property.* Take a look on the following example: sql>CREATE TABLE test (updated TIMESTAMP(0) DEFAULT current_timestamp()); operation successful (1.433ms) sql>\d test CREATE TABLE "sb_traf"."test" ( "updated" TIMESTAMP(0) DEFAULT current_timestamp() ); sql>CREATE TABLE test2 (LIKE test); operation successful (1.102ms) sql>\d test2 CREATE TABLE "sb_traf"."test2" ( "updated" TIMESTAMP(0) ); May be Im missing something, if thats the case, please let me know! Rgds Ariel

2 2

Embedded Python/NumPy
by Ariel Abadi 18 Jan '18

18 Jan '18

Hi! Someone can help please!?!?!!! I have created a function in python (see attached the definition), every time I try to use it against a "big table" monetdb is hanging out. What I found is that instead of sending the value of each nrm_json (to the function) , monet is sending the full column!!!.. This means that if the table has 10MM rows, then python is receiving ALL the records instead of the nrm_json STRING: SELECT sb_glob.getjsonfield_python(nrm_json, 'conference_id') FROM nrm_20180107 LIMIT 3; CREATE FUNCTION sb_glob.getjsonfield_python(nrm_json STRING, nrm_field STRING) RETURNS STRING LANGUAGE PYTHON { import ast a=ast.literal_eval(str(nrm_json.encode('utf-8'))) if not a.has_key(nrm_field): a[nrm_field] = '' return str(a[nrm_field]) }; I hope you understand, and guide me in solving this problem Thks in advance Ariel

1 0

program contains errors occured
by Amit Ambekar 18 Jan '18

18 Jan '18

Hi, I have big table around 15 mln records. getting error as "program contains errors" when i tried to fetch data using select query

2 1

Python Functions
by Ariel Abadi 11 Jan '18

11 Jan '18

Hi! Maybe someone can help me. Im trying to connect to a MySQL table to get some data and use it within MonetDB. Eveything works fine, TILL... the query Im running on MySQL is bringing NO DATA. As an example I put it as LIMIT 0 so I dont receive any row. Eventhough I have considered that situation and have defined the array with null values, Im still getting an error. *Could not create a Numpy array from the return type.* Does anybody has an idea how to solve this issue? Thks in advance! Ariel ----------------------------------------------------- DROP FUNCTION connect_to_mysql() ; CREATE FUNCTION connect_to_mysql() RETURNS TABLE(agr_id integer, agr_name string ) LANGUAGE PYTHON { import datetime import mysql.connector cnx = mysql.connector.connect(user='XXXX', password='XXXX',host='192.168.15.5',database='XXXXX',use_pure=False) cursor = cnx.cursor() query = "SELECT agr_id, agr_name FROM AGR_Agreement LIMIT 0 " cursor.execute(query) allrows = cursor.fetchall() ncols = len(cursor.description) colnames = [cursor.description[i][0] for i in range(ncols)] result = dict() for col in range(ncols): if len(allrows) == 0: result[colnames[col]] = [None] for row in range(len(allrows)): if allrows[row][col] == None: valor = None else: valor = str(allrows[row][col]) try: result[colnames[col]].append(valor) except KeyError: result[colnames[col]] = [valor] cursor.close() cnx.close() return result }; sql>SELECT * FROM connect_to_mysql() ; Could not create a Numpy array from the return type.

1 0

DEC2016-SP2 and BINARY bulk load
by Lynn Carol Johnson 11 Jan '18

11 Jan '18

Hi all – I have always used the COPY BINARY INTO … commands to load my 2.0 Billion row genetic data into a monetdb table. With 135 columns, it has been blindingly fast. Last week I moved from the June2016-SP2 release to dec2016-SP2. My binary loads are taking WAY longer. I killed one after 3 hours (via “call sys.stop(pid)” so it could clean up properly). I then started the load again, thinking perhaps the problem was related to the new columns I was adding. I have since dropped the table and remade it using the same data and scripts that worked in just over 3 minutes in February on the jun2016-SP2 load. It is really chugging along – I’m up to 30 minutes and counting. I don’t have access to the sql log files, but the Merovingian.log shows nothing. I do notice that previously the binary files, once loaded, were removed from the loading directly. This does not happen now. Were these files previously “moved” and now they are copied? Has anyone see this performance issue with Dec2016-SP2 COPY BINARY INTO …. Commands? Thanks - Lynn

5 17

RE: MonetDB - Column Header Question
by Sharma, Sreejith 09 Jan '18

09 Jan '18

I see below issue is happening only if base column datatype is integer. All other types working fine. From: Sharma, Sreejith Sent: Saturday, January 06, 2018 8:24 PM To: users-list(a)monetdb.org Subject: MonetDB - Column Header Question All, Quick question - For a specific scenario I would like to understand below - If we don't specify column header in SQL or alias, how is MonetDB picks up the column header? And what is the datatype for it? I saw it's hge (scalar type) is it so? Also, I would like to know where in source code it's happening. Example: SELECT SUM(COLUMN_NAME) FROM TABLE_NAME; ------------------+ | L3 | ------------------+ |123456678 | ------------------+ Regards, Sreejith

2 3

Bulk data upload through JDBC
by suvendu chakra 09 Jan '18

09 Jan '18

Hello, I am following SQLCopInto.java to upload bulk data into MonetDB but seems to be not working. I am even using mclient command to upload data but the command expects the input file to be accessible by the DB server, is there any alternative to this. Please suggest a best way to do through JDBC Thanks

3 3

MonetDB - Column Header Question
by Sharma, Sreejith 06 Jan '18

06 Jan '18

All, Quick question - For a specific scenario I would like to understand below - If we don't specify column header in SQL or alias, how is MonetDB picks up the column header? And what is the datatype for it? I saw it's hge (scalar type) is it so? Also, I would like to know where in source code it's happening. Example: SELECT SUM(COLUMN_NAME) FROM TABLE_NAME; ------------------+ | L3 | ------------------+ |123456678 | ------------------+ Regards, Sreejith

1 0

SyntaxException:parseError:Xclose
by Dennis Fassl 06 Jan '18

06 Jan '18

Hi all, I am generating MAL queries in Java and I am using the JDBC interface to execute them. This works fine for small tables. But if the resultSet has more than 100 entries MonetDB is printing the first 100 rows and then gives me the following error: ... [ 19, "undergraduateDegreeFrom", 1509 ] [ 19, "memberOf", 613 ] [ 19, "takesCourse", 556 ] [ 19, "advisor", 600 ] [ 20, "undergraduateDegreeFrom", 1493 ] [ 20, "memberOf", 613 ] [ 20, "takesCourse", 529 ] [ 20, "takesCourse", 578 ] [ 20, "takesCourse", 557 ] [ 20, "advisor", 1315 ] MAPI = (monetdb) /tmp/.s.monetdb.50000 QUERY = sql.resultSet(table_names, table_attrs, table_type, table_lens, table_scales, start_id, type, end_id); ERROR = !SyntaxException:parseError:Xclose 0 !SyntaxException:parseError: ^';' expected Could you help me to avoid this error? This is the full query: sql.init(); tbd := sql.mvc(); start_id:bat[:int] := sql.bind(tbd,"sys","relations","start_id",0:int); type:bat[:str] := sql.bind(tbd,"sys","relations","type",0:int); end_id:bat[:int] := sql.bind(tbd,"sys","relations","end_id",0:int); table_names := bat.new(nil:str); table_names := bat.append(table_names, "start_id"); table_names := bat.append(table_names, "type"); table_names := bat.append(table_names, "end_id"); table_attrs := bat.new(nil:str); table_attrs := bat.append(table_attrs, "str"); table_attrs := bat.append(table_attrs, "str"); table_attrs := bat.append(table_attrs, "str"); table_type := bat.new(:str); table_type := bat.append(table_type, "int"); table_type := bat.append(table_type, "varchar"); table_type := bat.append(table_type, "int"); table_lens := bat.new(nil:int); table_lens := bat.append(table_lens, 1); table_lens := bat.append(table_lens, 1); table_lens := bat.append(table_lens, 1); table_scales := bat.new(nil:int); table_scales := bat.append(table_scales, 0); table_scales := bat.append(table_scales, 0); table_scales := bat.append(table_scales, 0); sql.resultSet(table_names, table_attrs, table_type, table_lens, table_scales, start_id, type, end_id); I am using "MonetDB Database Server v1.7 (Dec2016-SP5)" on Ubuntu 14.04. What am I trying to do: The goal of the project is to use Cypher as an input language for MonetDB. I have a Cypher optimizer written in Java. The output of this optimizer should be used as input for MonetDB. In the end we want to compare the the runtime of a query in MonetDB and in Neo4J. Thanks and regards, Dennis

1 1