June 2011 - users-list

[MonetDB-users] Windowing functions ?
by Fabian Pijcke 15 Jun '11

15 Jun '11

Hello everyone, After visiting the MonetDB website, I didn't saw any information about the windowing functions of SQL (ranking, selecting max according to a defined order, etc.) A (very) short introduction can be found on wikipedia: http://en.wikipedia.org/wiki/Select_%28SQL%29#Window_function I know it is implemented into PostGreSQL, Oracle and Microsoft SQL Server. Is there any chance to see this function in MonetDB in the future ? Thanks in advance, sorry for my poor english ... :( Fabian Pijcke

2 2

[MonetDB-users] Create Database - Load Data
by Lowterm 14 Jun '11

14 Jun '11

Hi, how can I create a new Database under Windows and how can I load data in a table form a external file? Thanks -- View this message in context: http://old.nabble.com/Create-Database---Load-Data-tp31653813p31653813.html Sent from the monetdb-users mailing list archive at Nabble.com.

8 24

[MonetDB-users] PhD position at CWI Database Architecture group
by Stefan Manegold 13 Jun '11

13 Jun '11

CWI is an internationally renowned research institute in mathematics and computer science, located in Amsterdam, The Netherlands. The focus is on fundamental research problems, inspired by societal needs. Research is carried out in 18 research groups and a substantial fraction is carried out in the framework of (inter)national programs. VACANCY CWI has a vacancy for a talented PhD candidate in the DB Architectures group (http://www.cwi.nl/en/research-groups/Database-Architectures) DISTRIBUTED DATABASE ARCHITECTURES FOR SCIENCE APPLICATIONS The Database Architectures group is one of the world-wide leading groups in system-oriented database research, e.g., it received the 10-year best paper award at VLDB 2009 for their pioneering research on hardware-aware database technology and the column-store model. The group has a unique methodology that combines fundamental database research with active participation of all group members in database engineering, centered around the open-source analytical database system MonetDB (http://www.monetdb.org/) You will be expected to write both papers and contribute to this code base. The focus of the group is on hard core data management and for this position specifically on database architectures to support large scale scientific applications in the context of the SciLens project (http://www.scilens.org) The goal is to leverage the potential power of a 330 node dedicated database machine to improve science in, e.g., astronomy and seismology. (http://www.scilens.org/content/platform) CWI maintains excellent relations with industry and the academic world, both in the Netherlands as well as abroad. An internship at one of the partnering database research labs world-wide is considered part of the PhD track. After their research careers at CWI, many staff members find employment in these sectors or in spin-off companies based on research results from CWI. Of course, library and computing facilities are first-class. CWI's non-scientific services to its personnel include career planning, training and courses, assistance in finding housing, and tailor-made solutions to problems that may occasionally arise. REQUIREMENTS The PhD candidates must have a master degree in computer science, (specialization distributed systems, database, software engineering) mathematics, or related fields. Preferable qualifications for candidates include proven research talent, system programming skills (C/C++), practical experience with database systems, and software development in a team. One should bring good academic writing and presentation skills. Candidates are expected to have an excellent command of English. TERMS AND CONDITIONS The terms of employment are in accordance with the Dutch "CAO-onderzoeksinstellingen". Moreover, CWI offers attractive working conditions, including flexibility and help with housing for foreigners. The gross monthly salary (on a full time basis) will be â¬ 2,037 during the first year and will reach â¬ 2,610 during the fourth year. INFORMATION Additional information can be obtained from prof.dr. Martin Kersten, email martin.kersten(a)cwi.nl, telephone +31(0)20 592 4066, or dr. Stefan Manegold, email stefan.manegold(a)cwi.nl, telephone +31(0)20 592 4212. -- | Stefan.Manegold @ CWI.nl | DB Architectures (INS1) | | http://CWI.nl/~manegold/ | Science Park 123 (L321) | | Tel.: +31 (0)20 592-4212 | 1098 XG Amsterdam (NL) |

1 0

[MonetDB-users] Select Query crashes the server with "--single-user" option
by Ramak 13 Jun '11

13 Jun '11

Hi, I am trying to use MonetDB in single user mode. While I could load the data using "COPY INTO" command and run a few select queries successfully, the moment i run a query with group by on more than one column, the server crashes saying " ---- >Segmentation fault". Is there a way to get around it? or, am I using it the wrong way? I could recreate the problem doing this--- 1. Start server with "sudo mserver5 --dbfarm='/home/user/work/tmp/data/test' --single-user" 2. start mclient (and create a simple table and fire a select query with group by on multiple columns) >create table test ( market_size int not null, market int not null, state int not null); >select count(*) from test group by market_size, state; -- server crashes on the above query. I am using MonetDB 5 server v11.3.3 "Apr2011-SP1" (64-bit, 64-bit oids) Thanks, Ram -- View this message in context: http://old.nabble.com/Select-Query-crashes-the-server-with-%22--single-user… Sent from the monetdb-users mailing list archive at Nabble.com.

2 3

[MonetDB-users] Production Database Crash - Analysis / Recovery?
by Philippe Hanrigou 11 Jun '11

11 Jun '11

My database is crashing quite systematically in production during some data import (which is in the form as a serie of DELETE ... WHERE / COPY INTO). The relevant information from the merovigian.log seems to be: 2011-06-02 04:53:27 MSG prod_reporting[25449]: !SQLException:SQLinit:Catalogue initialization failed 2011-06-02 04:53:27 MSG prod_reporting[25449]: !ERROR: HEAPextend: failed to extend to 3316460814336 for 11/40/114026theap A) What does this error mean? ----------------------------------------- A lack of memory? I am quite confused as the entire database is barely 5G on disk and there is 7G of RAM on this machine, which is dedicated solely to MonetDB. Moreover I only DELETE/COPY INTO in a single table at a time and the biggest table is barely 1.6G. Cleary there is some memory dynamic that I am not understanding. B) How do I recover the crashed database? ----------------------------------------------------------- It would not start anymore: 2011-06-02 04:55:19 MSG prod_reporting[1665]: # Listening for UNIX domain connection requests on mapi:monetdb:///var/monetdb5/dbfarm/prod_reporting/.mapi.sock 2011-06-02 04:55:19 MSG prod_reporting[1665]: # MonetDB/SQL module loaded 2011-06-02 04:55:19 MSG merovingian[1644]: database 'prod_reporting' (1665) was killed by signal SIGSEGV 2011-06-02 04:55:29 ERR control[1644]: (local): failed to fork mserver: database 'prod_reporting' has crashed after starting, manual intervention needed, check merovingian's logfile for details Thanks in advance, - Philippe -- more complete extract from merovigian.log --- 2011-06-02 04:40:38 MSG merovingian[639]: proxying client ip-10-204-61-105.ec2.internal:52864 for database 'prod_reporting' to mapi:monetdb:///var/monetdb5/dbfarm/prod_reporting/.mapi.sock?database=prod_reporting 2011-06-02 04:40:38 MSG merovingian[639]: target connection is on local UNIX domain socket, passing on filedescriptor instead of proxying 2011-06-02 04:53:09 MSG control[639]: (local): served status list 2011-06-02 04:53:23 MSG merovingian[639]: caught SIGTERM, starting shutdown sequence 2011-06-02 04:53:24 MSG control[639]: control channel closed 2011-06-02 04:53:27 MSG merovingian[639]: sending process 25449 (database 'prod_reporting') the TERM signal 2011-06-02 04:53:27 MSG prod_reporting[25449]: !SQLException:SQLinit:Catalogue initialization failed 2011-06-02 04:53:27 MSG prod_reporting[25449]: !ERROR: HEAPextend: failed to extend to 3316460814336 for 11/40/114026theap 2011-06-02 04:53:27 MSG merovingian[639]: database 'prod_reporting' (25449) has exited with exit status 0 2011-06-02 04:53:27 MSG merovingian[639]: database 'prod_reporting' has shut down 2011-06-02 04:53:27 MSG merovingian[639]: Merovingian 1.4 stopped 2011-06-02 04:55:16 MSG merovingian[1644]: Merovingian 1.4 (Apr2011-SP1) starting 2011-06-02 04:55:16 MSG merovingian[1644]: monitoring dbfarm /var/monetdb5/dbfarm 2011-06-02 04:55:16 MSG merovingian[1644]: accepting connections on TCP socket 0.0.0.0:50000 2011-06-02 04:55:16 MSG merovingian[1644]: accepting connections on UNIX domain socket /tmp/.s.monetdb.50000 2011-06-02 04:55:16 MSG discovery[1644]: listening for UDP messages on 0.0.0.0:50000 2011-06-02 04:55:16 MSG control[1644]: accepting connections on UNIX domain socket /tmp/.s.merovingian.50001 2011-06-02 04:55:16 MSG control[1644]: accepting connections on TCP socket 0.0.0.0:50001 2011-06-02 04:55:16 MSG discovery[1644]: new neighbour ip-10-32-111-2 (ip-10-32-111-2.ec2.internal) 2011-06-02 04:55:16 MSG discovery[1644]: new database mapi:monetdb://ip-10-32-111-2:50000/prod_reporting (ttl=660s) 2011-06-02 04:55:16 MSG discovery[1644]: registered neighbour ip-10-32-111-2:50001 2011-06-02 04:55:19 MSG control[1644]: (local): served status list 2011-06-02 04:55:19 MSG merovingian[1644]: starting database 'prod_reporting', up min/avg/max: 1m/31m/1h, crash average: 0.00 0.10 0.03 (6-5=1) 2011-06-02 04:55:19 MSG prod_reporting[1665]: arguments: /usr/bin/mserver5 --set gdk_dbfarm=/var/monetdb5/dbfarm --dbname=prod_reporting --set merovingian_uri=mapi:monetdb://ip-10-32-111-2:50000/prod_reporting --set mapi_open=false --set mapi_port=0 --set mapi_usock=/var/monetdb5/dbfarm/prod_reporting/.mapi.sock --set monet_vault_key=/var/monetdb5/dbfarm/prod_reporting/.vaultkey --set monet_daemon=yes 2011-06-02 04:55:19 MSG prod_reporting[1665]: # MonetDB 5 server v11.3.3 "Apr2011-SP1" 2011-06-02 04:55:19 MSG prod_reporting[1665]: # Serving database 'prod_reporting', using 2 threads 2011-06-02 04:55:19 MSG prod_reporting[1665]: # Compiled for x86_64-pc-linux-gnu/64bit with 64bit OIDs dynamically linked 2011-06-02 04:55:19 MSG prod_reporting[1665]: # Found 7.294 GiB available main-memory. 2011-06-02 04:55:19 MSG prod_reporting[1665]: # Copyright (c) 1993-July 2008 CWI. 2011-06-02 04:55:19 MSG prod_reporting[1665]: # Copyright (c) August 2008-2011 MonetDB B.V., all rights reserved 2011-06-02 04:55:19 MSG prod_reporting[1665]: # Listening for UNIX domain connection requests on mapi:monetdb:///var/monetdb5/dbfarm/prod_reporting/.mapi.sock 2011-06-02 04:55:19 MSG prod_reporting[1665]: # MonetDB/SQL module loaded 2011-06-02 04:55:19 MSG merovingian[1644]: database 'prod_reporting' (1665) was killed by signal SIGSEGV 2011-06-02 04:55:29 ERR control[1644]: (local): failed to fork mserver: database 'prod_reporting' has crashed after starting, manual intervention needed, check merovingian's logfile for details 2011-06-02 05:03:18 MSG merovingian[1644]: database 'prod_reporting' has crashed after start on 2011-06-02 04:55:19, attempting restart, up min/avg/max: 1m/31m/1h, crash average: 1.00 0.20 0.07 (7-5=2)

4 8

[MonetDB-users] monetdb checkpoint
by Philippe Hanrigou 11 Jun '11

11 Jun '11

Hi, Online documentation at http://www.monetdb.org/Documentation/Cookbooks/ServerMgmt/ResourceLimits mentions: > The database checkpoint can be made using 'monetdb checkpoint' Yet checkpoint does not seem to be a valid monetdb command. Is it a typo, an historical vestige? Cheers, - Philippe

2 2

[MonetDB-users] Octopus usage in MonetDB
by Andrei Cojocaru 07 Jun '11

07 Jun '11

Hi all, I am trying to setup (and play around with) a MonetDB-Apr11 cluster since the dataset I am expecting to have is beyond what can fit in the memory one node (starting with at least a few hundred gigabytes). I have been following the instructions athttp://www.monetdb.org/Documentation/Cookbooks/Recipes/Cluster and have a few nodes setup. I ran all the commands given there on all the nodes except for the creation of the master database which I ran on only the master. Also, I set the pipe to octopus_pipe on the master database. In mclient, I ran the following commands (from the sample squid test for octopus in the MonetDB source code): create table squidA( Bid int, Cval int); create table squidD( Eid int, Fval int); -- let's fill the tables create procedure fill(lim int) begin declare i int; set i = 0; while i < lim do insert into squidA values(i,rand()); insert into squidD values(i,rand()); set i = i+1; end while; end; declare size int; set size=100; call fill(size); It all runs with no errors. However, when I run: select Bid from squidA, I get a screenful of errors. They are all a variation of: TypeException:octopus.tentacle_3_11[6]:'octopus.bind' undefined in: _18:bat[:oid,:int] := octopus.bind(_2:str, _8:str, _9:str, _13:str, _19:int, _16:int, _17:int, version:int) Would you be able to offer some guidance on how to get this running? I was considering setting up application level partitioning and run multiple MonetDB instances directly, but if octopus does a lot of that work I would prefer to save myself the development and debugging time. Thanks for any help you could provide. -Andrei

3 2

[MonetDB-users] Problem with Create Tables
by Lowterm 03 Jun '11

03 Jun '11

Hi, what's the meaning of this syntax error? Syntax error, unexpected CREATE, expecting SCOLON in: "create ..." I get this message with the "create table" if I load a Files with "sql>\< filename", which contains this statement. The create-statement works, if I insert it directly. The second error message ist: syntax error, unexpected ON, expecting ')' or ',' in: "create ..." in the following create statement: CREATE TABLE dim_warehouse ( is_current smallint NOT NULL, last_modified timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) but I can not see wath is wrong. Thanks -- View this message in context: http://old.nabble.com/Problem-with-Create-Tables-tp31757411p31757411.html Sent from the monetdb-users mailing list archive at Nabble.com.

3 6

[MonetDB-users] Aggregating by a computed field?
by Synaptic Axon 01 Jun '11

01 Jun '11

Hello, I recently install MonetDB and have been trying to get a feel for its speed and storage. I ran into an issue with SELECT queries trying to GROUP BY a computed field. For example, imagine a table like this: CREATE TABLE foo ( ts TIMESTAMP NOT NULL, category VARCHAR(32) NOT NULL ); I want to count the number of categories by year, so I want to craft a query like this: sql> SELECT EXTRACT( year from ts ) as year, category, COUNT(category) FROM foo GROUP BY 1, 2 ORDER BY 3 DESC; syntax error, unexpected sqlINT in: "SELECT EXTRACT( year from ts ) as year, category, COUNT(category) FROM foo GROUP BY 1, 2 ORDER BY 3 DESC" Unfortunately, MonetDB does not appear to support positional fields in the GROUP BY clause. So I tried rewriting it like this: sql> SELECT EXTRACT( year from ts ) as year, category, COUNT(category) FROM foo GROUP BY EXTRACT( year from ts ), category ORDER BY 3 DESC; syntax error, unexpected EXTRACT in: "SELECT EXTRACT( year from ts ) as year, category, COUNT(category) FROM foo GROUP BY EXTRACT( year from ts ), category ORDER BY 3 DESC" Ok, how about using "year" instead of the extract( .. ) bit? sql> SELECT EXTRACT( year from ts ) as year, category, COUNT(category) FROM foo GROUP BY year, category ORDER BY 3 DESC; syntax error, unexpected YEAR in: "SELECT EXTRACT( year from ts ) as year, category, COUNT(category) FROM foo GROUP BY year, category ORDER BY 3 DESC" What is the correct way to group by the computed field? Should I create a view? Is there a positional syntax for the GROUP BY clause I am overlooking? $ monetdb --version MonetDB Database Server Toolkit v0.8 (Apr2011) $ monetdbd --version MonetDB Database Server v1.4 (Apr2011) $ mclient --version mclient, the MonetDB interactive terminal (Apr2011) support for command-line editing compiled-in Thank you.

3 5