- users-list - monetdb.org

Setting connection time-out?
by Dennis Pallett 10 Sep '13

10 Sep '13

Hello, Is it possible to set a time-out in MonetDB for connections? On our test instance of MonetDB we are running into connection limits because our developers are starting/stopping our application all day long without properly closing the connections. On production this is not a problem because there the connections are closed properly. Best regards, Dennis Pallett

1 0

GeoSpatial
by Lee Hachadoorian 05 Sep '13

05 Sep '13

I found out about MonetDB a few months ago and am currently giving it a whirl. I am a geographer and am interested in the GeoSpatial extension. I have found limited documentation [1][2], which (I think) has changed since I first saw it a few months ago. I remember something about a shapefile import application that was based on shp2pgsql used by PostGIS, and also something about using OpenJump to visualize the data stored in the MonetDB database, but I cannot find this information any more. I have successfully created a spatial table by copying a CREATE TABLE statement from PostGIS, then using COPY ... TO in PostGIS and COPY ... FROM in MonetDB. But I would like to know if a shp2pgsql equivalent exists for MonetDB, or if there is any work on or plans for OGR functionality. I would also like to know how users visualize MonetDB spatial data in OpenJump or any other GIS, which incidentally also leads into the question of OGR functionality since so many FOSS GISes (e.g. QGIS) use OGR for data loading. Best, --Lee [1] http://www.monetdb.org/Documentation/Extensions/GIS [2] http://www.monetdb.org/Documentation/Extensions/GIS/SpatialTypes -- Lee Hachadoorian Assistant Professor in Geography, Dartmouth College http://freecity.commons.gc.cuny.edu

2 1

Database crash; won't start anymore
by Ralph Holz 30 Aug '13

30 Aug '13

Hi, I've tried to load a good chunk of the Internet census into our MonetDB - about 100M rows. Although the import scripts completed, it seems it shot down Monet: 2013-08-28 04:10:35 MSG merovingian[9857]: database 'census' (10773) was killed by signal SIGBUS 2013-08-28 10:47:59 MSG merovingian[9857]: database 'census' has crashed after start on 2013-08-28 01:14:07, attempting restart, up min/avg/max: 4m/4d/3w, crash average: 1.00 0.10 0.03 (8-7=1) This is then followed by 2013-08-28 10:48:32 MSG census[19161]: # still reading write-ahead log "/mnt/monetdb/census/sql_logs/sql/log.990" (0% done) [...] 2013-08-28 10:53:12 MSG census[19161]: # still reading write-ahead log "/mnt/monetdb/census/sql_logs/sql/log.990" (4% done) 2013-08-28 10:53:34 MSG merovingian[9857]: database 'census' (19161) has crashed (dumped core) Restarting it, yielded the same results for a few times. Once or twice it got to 35% in the WAL 2013-08-28 15:34:27 MSG census[14420]: # still reading write-ahead log "/mnt/monetdb/census/sql_logs/sql/log.990" (35% done) 2013-08-28 15:34:59 MSG merovingian[9857]: database 'census' (14420) has crashed (dumped core) 2013-08-28 15:34:59 ERR merovingian[9857]: client error: database 'census' has crashed after starting, manual intervention needed, check monetdbd's logfile for details 2013-08-28 15:34:59 ERR merovingian[9857]: client error: client (local) sent challenge in incomplete block: 2013-08-28 15:34:59 ERR merovingian[9857]: client error: client (local) sent challenge in incomplete block: 2013-08-28 15:34:59 MSG merovingian[9857]: database 'census' has crashed after start on 2013-08-28 14:43:34, attempting restart, up min/avg/max: 4m/4d/3w, crash average: 1.00 0.90 0.30 (16-7=9) Since then, it won't start (I don't know what caused the client error BTW). Data loss is not a concern, but we'd sure like to know if this can be fixed or if we should start looking at another DB... Thanks for any help, Ralph -- Ralph Holz I8 - Network Architectures and Services Technische Universität München http://www.net.in.tum.de/de/mitarbeiter/holz/ Phone +49.89.289.18043 PGP: A805 D19C E23E 6BBB E0C4 86DC 520E 0C83 69B0 03EF

3 7

Archlinux AUR Package
by Brian Hood 29 Aug '13

29 Aug '13

Hi All, I maintain a package build in the user repository which i update every new release. aur.archlinux.org - just search Regards, Brian Hood

1 0

Clustered Indexes with monetdb
by Ashish Kumar Singh 28 Aug '13

28 Aug '13

HI All, Can somebody point me to relevant information on how to do clustered indexes with monetdb? Regards, Ashish

3 7

Server - location of tmp database objects
by Radovan Bičiště 27 Aug '13

27 Aug '13

Hello, I was wondering if there is a way to specify for database where to put tmp files? For example when we use temporary tables etc. it is filling our data disk. I have another disk on the machine that I would like to use for temporary tables etc. Thank you for any hints. Best regards, Radovan -- __________________________ Radovan Bičiště ceos data s.r.o. třída SNP 402/48 500 03 Hradec Králové Czech Republic mobil CZ: +420 601 563 014 skype: rbiciste

4 3

Technical Problem connecting with database
by Hancel Gonzalez 27 Aug '13

27 Aug '13

Hi, i have a problem: i installed *monetdb 11.9.7 *from source code and created the database farm and the database with commands *monetdbd *and *monetdb, *respectively. The problem is when i try connect to the database (*mclient*) and dont work, see the log file but i dont how to solve. Please, anyone can help me to solve? This is the message in log file: *2013-08-26 21:48:41 MSG merovingian[7622]: starting database 'Berlin10MDB', up min/avg/max: 0s/0s/0s, crash average: 0.00 0.00 0.00 (0-0=0)* * * *2013-08-26 21:48:41 MSG Berlin10MDB[7668]: arguments: /usr/local/bin/mserver5 --set gdk_dbfarm=/home/gdesimone/monet_data --dbname=Berlin10MDB --set merovingian_uri=mapi:monetdb://Athena:50000/Berlin10MDB --set mapi_open=false --set mapi_port=0 --set mapi_usock=/home/gdesimone/monet_data/Berlin10MDB/.mapi.sock --set monet_vault_key=/home/gdesimone/monet_data/Berlin10MDB/.vaultkey --set gdk_nr_threads=4 --set max_clients=64 --set sql_optimizer=default_pipe --set monet_daemon=yes* * * *2013-08-26 21:48:41 MSG merovingian[7622]: database 'Berlin10MDB' (7668) has exited with exit status 127* * * *2013-08-26 21:48:41 ERR merovingian[7622]: client error: database 'Berlin10MDB' appears to shut itself down after starting, check monetdbd's logfile for possible hints* * * Many thanks! Regards, Hancel.

3 5

Database Problem
by Thomas Johann 23 Aug '13

23 Aug '13

Dear all, I’m new to monetdb database programming and have a problem setting up my first database which I can not explain by myself: I’m basically running the following Python code: tempdir = 'E:\\xetra\\tempdir\\monetdb' connection = monetdb.sql.connect(username="monetdb", password="monetdb", hostname="localhost", database="database") # expected table layout connection.execute("CREATE TABLE XXX(Name_1 TEXT, Name_2 TEXT, Name_3 TEXT, Name_4 TEXT, Num_1 INT, Num_2 REAL,Num_3 INT,Name_5 CHARACTER(1))") def convert_table(filename,path): print("Reading CSV") try: start = time.clock() csv_to_sql = path + "\\" + filename connection.execute("COPY 100000000 OFFSET 2 RECORDS INTO XXX FROM '" + csv_to_sql + "' USING DELIMITERS ';','\n' ;") elapsed = (time.clock() - start) connection.commit() print("Time to write to database: " + str(elapsed) + " seconds") except: #get wrong transaction out of DB with rollback connection.rollback() try: #DB conversion start = time.clock() infile = open(csv_to_sql, 'rb') csv_to_sql_converted=path+ '\\converted.csv' outfile = open(csv_to_sql_converted, 'wb') BLOCKSIZE = 100000000 # experiment with size while True: block = infile.read(BLOCKSIZE) if not block: break outfile.write(block.decode('cp1252','strict').encode('utf-8','strict')) infile.close() outfile.close() elapsed = (time.clock() - start) print("Time to convert file to UTF-8: " + str(elapsed) + " seconds") #write to DB start = time.clock() connection.execute("COPY 100000000 OFFSET 2 RECORDS INTO XXX FROM '" + csv_to_sql_converted + "' USING DELIMITERS ';','\n' ;") connection.commit() os.remove(csv_to_sql_converted) elapsed = (time.clock() - start) print("Time to write to database: " + str(elapsed) + " seconds") except Exception as inst: #get wrong transaction out of DB with rollback connection.rollback() print type(inst) # the exception instance print inst.args # arguments stored in .args print inst # __str__ allows args to printed directly print("Could not write file "+csv_to_sql+"to the database XXX") filename_list = [“XYZ.csv”, “ABC.csv”,…] path = “C:\\” for filename in filename_list: convert_table(filename, path) I had to alter the code as the actual csvs contain very sensitive data, which I am not allowed to publish. But the structure is the same. I do not get any error message and the program runs through properly. Usually it goes through the “try:” part of the function. When I look into the resulting database the number of lines is as expected (so the observations must be somewhere in the database) but there are some gaps in the output, meaning that some values from the .csvs are just not copied and I am unable to compare those gap-lines to the original data as the identifier is usually among the missing variables. Some variables from the original data are just missing. This happens (at least to what I can say) in an unsystematic way. I have the impression that MonetDB might just be overwhelmed by the amount of data. Is this possible? The .csvs have each a size of 1.3GB and I’m reading more than 1000 of them into the database which has a final size of about 2 TB. I’m running this under Windows Server 2008 R2, 32GB RAM, AMD Opteron 4234 6-Core 3.10 GHz, using Anaconda. When I manually read in one single file, that made problems beforehand, I will find it in the database without error. So, I’m pretty puzzled what is going on here. Might it happen that the connection.execute-command runs through without triggering an error message but still not reading the whole dataset? I would be very glad about any help from the community. Thanks, Thomas

2 2

Installation and configuration on Windows 8
by m- dev 22 Aug '13

22 Aug '13

I'm not familiar with database administration, but I guess I'm having similar problems to this Windows user: http://mail.monetdb.org/pipermail/users-list/2013-June/006078.html I installed DB5-SQL, Geom and ODBC 32 and 64bit, default options for all. After that it's not clear to me which steps are for Linux or Windows, or both. Or how to set up the configuration file. In Windows 8 I start 'MonetDB SQL Server'. I can also start 'MonetDB SQL Client'. i) I added PATH in Environment variable to the installation folder (that defaults to 32-bit Program files): ;C:\Program Files\MonetDB\MonetDB5 But I couldn't set up a configuration file, either from within the Server: > cat ~/.monetdb SyntaxException:parseError:cat ~/.monetdb SyntaxException:parseError: ^,';' expected Or the Client: sql>cat ~/.monetdb more>name=#### more>password=######### more>language=SQL more> Creating that as a text file renamed config.monetdb in the installation folder doesn't work either. ii) I've extracted the voc_dump.sql file, but the Guide doesn't specify the location of mclient.exe and when I found it in C:\Program Files\MonetDB\MonetDB5\bin but it won't open because: "libmapi.dll is missing...Try reinstalling the program to fix this problem" Clearly I haven't set it up MonetDB correctly, I can only guess, but what are the correct steps? Is there any other guide or tutorial on installation and configuration on Windows 8? Thanks, Mark

2 2

SELECT DISTINCT on datatype inet causes high load even after cancelling
by Ralph Holz 22 Aug '13

22 Aug '13

Good day, We're experiencing a phenomenon that I can't quite explain. We have a table of maybe 110M entries mapping IP addresses to some values. The data type of field ip_addr is net. No indices or constraints set. A SELECT COUNT(ip) is zippy - result in some ms. Any SELECT, in fact, that does not use WHERE is fast. E.g.: SELECT ip, result FROM iptable LIMIT 10000; +------------------ 10000 tuples (4.6s) However: A SELECT COUNT(DISTINCT ip) does not return even after waiting an hour or, just to try it out once, 24 hrs. Load remained at 27 during that time. I restarted the server after that, as I had done ctrl-c in mclient, and that seems to let the current query continue? What would be the right way to cancel a query? I then tried SELECT ip FROM iptable WHERE ip = inet '1.0.0.1' That IP is the second in the column. The query did not return even after 10 minutes. That seems a bit excessive. Does anyone have a clue why this is so? Should I avoid inet? Thanks, Ralph Disclaimer: I come from a psql background and these are my first steps with vertical-col data stores. Server: MonetDB 5 server v11.15.11 "Feb2013-SP3" (64-bit, 64-bit oids) Copyright (c) 1993-July 2008 CWI Copyright (c) August 2008-2013 MonetDB B.V., all rights reserved Visit http://www.monetdb.org/ for further information Found 142.0GiB available memory, 24 available cpu cores Libraries: libpcre: 8.30 2012-02-04 (compiled with 8.02) openssl: OpenSSL 0.9.8o 01 Jun 2010 (compiled with OpenSSL 0.9.8o 01 Jun 2010) libxml2: 2.7.8 (compiled with 2.7.8) Compiled by: root(a)dev.monetdb.org (x86_64-pc-linux-gnu) Compilation: gcc -O3 -fomit-frame-pointer -pipe -Wp,-D_FORTIFY_SOURCE=2 Linking : /usr/bin/ld -m elf_x86_64 -- Ralph Holz I8 - Network Architectures and Services Technische Universität München http://www.net.in.tum.de/de/mitarbeiter/holz/ Phone +49.89.289.18043 PGP: A805 D19C E23E 6BBB E0C4 86DC 520E 0C83 69B0 03EF

1 0