users-list May 2014

users-list@monetdb.org

16 participants
15 discussions

by László Sándor

Hi, I reinstalled MonetDB as well as the ODBC driver after last week's update to SP2. How could I troubleshoot my system if I get a Memory allocation error SQLSTATE=HY001 if Stata would query the database? Otherwise I do see the data source listed, both in Stata (with -odbc query-) and Microsoft ODBC Data Source Administrator. (The latter lists the database under System DSN with the right name, platform and driver.) Thanks, Laszlo

8 years, 1 month

Announcement: New Jan2014-SP2 Bugfix release of MonetDB suite

by Sjoerd Mullender

The MonetDB team at CWI/MonetDB BV is pleased to announce the Jan2014-SP2 bugfix 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/>. Jan 2014-SP2 bugfix release Build Environment * Lots of minor fixes were made for potential defects found by Coverity Scan. * On Windows we now build the geom module against version 3.4.2 of the geos library. Client Package * ODBC: Implemented {call procedure-name(...)} escape. The version {?=call ...} is not implemented. Bug Fixes * 3424: numeric values at the front of strings determines whether CAST works successfully * 3439: Python driver drops milliseconds from timestamps * 3446: SET READ ONLY forgets previous changes * 3455: String columns unusable from 64-bit .NET via ODBC * 3456: Insert fails * 3457: When kernel of remote client crashes, the connection remains established on server side * 3458: mserver5 crash on SQL: SELECT COUNT(*) FROM SYS.TABLES HAVING COUNT(*) > 0 * 3461: mserver5 crash on SQL: SELECT * FROM SYS.ARGS WHERE FUNC_ID NOT IN (SELECT ID FROM SYS.FUNCTIONS) OR FUNC_ID NOT IN (SELECT * FROM SYS.FUNCTIONS) * 3462: Invalid SQL (IN with subquery which returns multiple columns) is accepted * 3463: Crash on SELECT with SERIAL aggregation and GROUP BY column alias's * 3468: Local temporary table persists across sessions * 3469: Absolute network paths considered invalid for COPY INTO ... FROM statement. * 3473: Various memory leaks in SQL compilation * 3477: ODBC driver raises "unexpected end of input" for prepared string parameter from .NET application * 3481: Cannot run multiple COPY INTO statements in one 's'-command * 3482: Crossproduct error

8 years, 1 month

how to count the number of query results in monetdb

by Bing Zhang

Hi: Is there a fast way to get the number of results, instead of looping on the ResultSet.next()? Thanks.

8 years, 2 months

Processing web logs in monetdb

by Christopher Nelson

Hello, I've been looking at monetdb for a while, and I've been very impressed with the project. I would like to use monetdb to analyze web log data from a very active site. I have no doubt that it can handle the quantity of data, but I'm not sure how to perform some of the queries that I have used in other systems. For example, I would like in particular to be able to perform click-stream analysis of the data. On other systems I've used proprietary extensions which allowed me to create a window by pattern matching a certain column. Do you have any suggestions about where I should look to perform similar kinds of queries? Thanks! -={C}=-

8 years, 2 months

question: efficient use of partitioned tables

by László Sándor

Hi, This might be less of MonetDB-specific question about features than about RDBMS (or just SQL) in general, but I would still appreciate your advice in these matters. I have data with a few "cross-sectional" tables (e.g. the parents of an individual) and many longitudinal/panel tables in annual chunks (e.g. tax records for each year separately). I wonder if it is a good idea to merge/union the annual tables into long panels. The benefits seems to be a much cleaner structure for the consistent columns, correspondingly easier code to write, fewer loops, tables names, lines of code. Also, fast joins on individual IDs and years across the tables without intermediary steps. However, the downsides are that not all columns are consistent across years, and there are simply some that appear only in certain years. Storing missing values for other years for these variables is a waste of disk space and memory. The merged tables could be too big anyway — if it matters in MonetDB (think of tables ~70 GB in some cases, while my server has 128 GB RAM), while many use cases would focus on a subset of years anyway. Do you have any thoughts about this? Maybe more concrete questions would be: — How cumbersome would it be (for a novice!) to code joining data from the annual tables separately, for multiple series of tables? (E.g. union all tax returns for 1997-2008, then join all residential data for these 12 years year-by-year, then employment records year-by-year etc.) — How much slower would this *run* than simply selecting years from the longitudinal tables? — Shall I be worried about the longitudinal tables having the size on the same order of magnitude as my physical memory, or MonetDB handles it equally efficiently (it would need to access many of the small annual tables anyway, that also takes memory). Thanks, Laszlo

8 years, 2 months

Strange query performance

by N.H. Schuiling

Hi all, Can someone explain me why this simple query takes so long to execute on MonetDB 5 server v11.17.13 "Jan2014-SP1"? sql>select * from fp_tree limit 1; +-----------------------------+ | path | +=============================+ | ;8;60;379;519;1241;174;692; | +-----------------------------+ 1 tuple (1.2s) Table fp_tree has one column path with data type string. Some useful statistics of table fp_tree: sql>select count(*) from fp_tree; +--------+ | L1 | +========+ | 416499 | +--------+ 1 tuple (2.801ms) sql>select max(length(path)) from fp_tree; +------+ | L1 | +======+ | 1275 | +------+ 1 tuple (254.742ms) sql>select min(length(path)) from fp_tree; +------+ | L1 | +======+ | 3 | +------+ 1 tuple (272.186ms) sql>select avg(length(path)) from fp_tree; +--------------------------+ | L1 | +==========================+ | 202.73579288305606 | +--------------------------+ 1 tuple (331.306ms) Query trace: +---------+-------------------------------------------------------------------+ | ticks | stmt | +=========+===================================================================+ | 3 | X_2 := sql.mvc(); | | 29 | X_35:bat[:oid,:oid] =<tmp_440>[104124] := sql.tid(X_2=0,"sys","fp | : : _tree",0,4); : | 6 | X_8=<tmp_3501>[0] := sql.bind(X_2=0,"sys","fp_tree","path",1); | | 40 | (X_52:bat[:oid,:oid] =<tmp_2110>[0],X_53:bat[:oid,:str] =<tmp_350 | : : 1>[0]) := sql.bind(X_2=0,"sys","fp_tree","path",2,3,4); : | 8 | X_45:bat[:oid,:str] =<tmp_1357>[104127] := sql.bind(X_2=0,"sys"," | : : fp_tree","path",0,3,4); : | 3 | X_57=<tmp_1357>[104127] := sql.delta(X_45=<tmp_1357>:bat[:oid,:st | : : r][104127],X_52=<tmp_2110>:bat[:oid,:oid][0],X_53=<tmp_3501>:bat[ : : : :oid,:str][0],X_8=<tmp_3501>[0]); : | 4 | X_41:bat[:oid,:oid] =<tmp_1256>[104127] := sql.tid(X_2=0,"sys","f | : : p_tree",3,4); : | 8 | X_61=<tmp_302>[104127] := algebra.leftfetchjoin(X_41=<tmp_1256>:b | : : at[:oid,:oid][104127],X_57=<tmp_1357>[104127]); : | 10 | X_65=<tmpr_1256>[1] := algebra.subslice(X_61=<tmp_302>[104127],0: | : : wrd,0:wrd); : | 6 | X_71=<tmp_1357>[1] := algebra.leftfetchjoin(X_65=<tmpr_1256>[1],X | : : _61=<tmp_302>[104127]); : | 1 | language.pass(X_61=<tmp_302>[104127]); | | 24 | (X_50:bat[:oid,:oid] =<tmp_2110>[0],X_51:bat[:oid,:str] =<tmp_350 | : : 1>[0]) := sql.bind(X_2=0,"sys","fp_tree","path",2,2,4); : | 5 | X_44:bat[:oid,:str] =<tmp_1560>[104124] := sql.bind(X_2=0,"sys"," | : : fp_tree","path",0,2,4); : | 3 | X_56=<tmp_1560>[104124] := sql.delta(X_44=<tmp_1560>:bat[:oid,:st | : : r][104124],X_50=<tmp_2110>:bat[:oid,:oid][0],X_51=<tmp_3501>:bat[ : : : :oid,:str][0]); : | 3 | X_39:bat[:oid,:oid] =<tmp_1760>[104124] := sql.tid(X_2=0,"sys","f | : : p_tree",2,4); : | 5 | X_60=<tmp_302>[104124] := algebra.leftfetchjoin(X_39=<tmp_1760>:b | : : at[:oid,:oid][104124],X_56=<tmp_1560>[104124]); : | 8 | X_64=<tmpr_1760>[1] := algebra.subslice(X_60=<tmp_302>[104124],0: | : : wrd,0:wrd); : | 4 | X_70=<tmp_1560>[1] := algebra.leftfetchjoin(X_64=<tmpr_1760>[1],X | : : _60=<tmp_302>[104124]); : | 1 | language.pass(X_60=<tmp_302>[104124]); | | 21 | (X_48:bat[:oid,:oid] =<tmp_2110>[0],X_49:bat[:oid,:str] =<tmp_350 | : : 1>[0]) := sql.bind(X_2=0,"sys","fp_tree","path",2,1,4); : | 5 | X_43:bat[:oid,:str] =<tmp_1660>[104124] := sql.bind(X_2=0,"sys"," | : : fp_tree","path",0,1,4); : | 1 | X_55=<tmp_1660>[104124] := sql.delta(X_43=<tmp_1660>:bat[:oid,:st | : : r][104124],X_48=<tmp_2110>:bat[:oid,:oid][0],X_49=<tmp_3501>:bat[ : : : :oid,:str][0]); : | 3 | X_37:bat[:oid,:oid] =<tmp_1054>[104124] := sql.tid(X_2=0,"sys","f | : : p_tree",1,4); : | 5 | X_59=<tmp_302>[104124] := algebra.leftfetchjoin(X_37=<tmp_1054>:b | : : at[:oid,:oid][104124],X_55=<tmp_1660>[104124]); : | 8 | X_63=<tmpr_1054>[1] := algebra.subslice(X_59=<tmp_302>[104124],0: | : : wrd,0:wrd); : | 5 | X_69=<tmp_1660>[1] := algebra.leftfetchjoin(X_63=<tmpr_1054>[1],X | : : _59=<tmp_302>[104124]); : | 1 | language.pass(X_59=<tmp_302>[104124]); | | 23 | (X_46:bat[:oid,:oid] =<tmp_2110>[0],X_47:bat[:oid,:str] =<tmp_350 | : : 1>[0]) := sql.bind(X_2=0,"sys","fp_tree","path",2,0,4); : | 9 | X_42:bat[:oid,:str] =<tmp_462>[104124] := sql.bind(X_2=0,"sys","f | : : p_tree","path",0,0,4); : | 2 | X_54=<tmp_462>[104124] := sql.delta(X_42=<tmp_462>:bat[:oid,:str] | : : [104124],X_46=<tmp_2110>:bat[:oid,:oid][0],X_47=<tmp_3501>:bat[:o : : : id,:str][0]); : | 6 | X_58=<tmp_764>[104124] := algebra.leftfetchjoin(X_35=<tmp_440>:ba | : : t[:oid,:oid][104124],X_54=<tmp_462>[104124]); : | 8 | X_62=<tmpr_440>[1] := algebra.subslice(X_58=<tmp_764>[104124],0:w | : : rd,0:wrd); : | 10 | X_72=<tmp_462>[1] := mat.packIncrement(X_62=<tmpr_440>[1],4); | | 3 | X_74=<tmp_462>[2] := mat.packIncrement(X_72=<tmp_462>[2],X_63=<tm | : : pr_1054>[1]); : | 1 | language.pass(X_63=<tmpr_1054>[1]); | | 2 | X_75=<tmp_462>[3] := mat.packIncrement(X_74=<tmp_462>[3],X_64=<tm | : : pr_1760>[1]); : | 1 | language.pass(X_64=<tmpr_1760>[1]); | | 3 | X_66=<tmp_462>[4] := mat.packIncrement(X_75=<tmp_462>[4],X_65=<tm | : : pr_1256>[1]); : | 1 | language.pass(X_65=<tmpr_1256>[1]); | | 9 | X_11=<tmpr_1206>[1] := algebra.subslice(X_66=<tmp_462>[4],0:wrd,0 | : : :wrd); : | 5 | X_68=<tmp_1760>[1] := algebra.leftfetchjoin(X_62=<tmpr_440>[1],X_ | : : 58=<tmp_764>[104124]); : | 1 | language.pass(X_62=<tmpr_440>[1]); | | 6 | language.pass(X_58=<tmp_764>[104124]); | | 1019824 | X_77=<tmp_1256>[1] := mat.packIncrement(X_68=<tmp_1760>[1],4); | | 8 | X_78=<tmp_1256>[2] := mat.packIncrement(X_77=<tmp_1256>[2],X_69=< | : : tmp_1660>[1]); : | 7 | X_79=<tmp_1256>[3] := mat.packIncrement(X_78=<tmp_1256>[3],X_70=< | : : tmp_1560>[1]); : | 18 | X_67=<tmp_1256>[4] := mat.packIncrement(X_79=<tmp_1256>[4],X_71=< | : : tmp_1357>[1]); : | 18 | X_12=<tmp_1357>[1] := algebra.leftfetchjoin(X_11=<tmpr_1206>[1],X | : : _67=<tmp_1256>[4]); : | 1095479 | barrier X_89 := language.dataflow(); | | 13 | X_13 := sql.resultSet(1,1,X_12=<tmp_1357>[1]); | | 7 | sql.rsColumn(X_13=7,"sys.fp_tree","path","clob",0,0,X_12=<tmp_135 | : : 7>[1]); : | 2 | X_17 := io.stdout(); | | 33 | sql.exportResult(X_17=="104d2":streams,X_13=7); | | 0 | end s2_2; | | 1095725 | function user.s2_2(); | | 1095772 | X_5:void := user.s2_2(); | +---------+-------------------------------------------------------------------+ 56 tuples (1.1s) Why does mat.packIncrement takes so much time? Thanks in advance, Nik

8 years, 2 months

How to use SciQL on MonetDB

by Yingwei Yu

Hello, I am new to MonetDB, and I am very interested in the SciQL functionality. I downloaded and installed the windows build of MonetDB. When I tried some SciQL on the client, it seems that it does not work. I followed the commands in paper Kersten et. al (2011) as follows: sql>start transaction; auto commit mode: off sql>CREATE ARRAY A1 ( more> x INTEGER DIMENSION[4], more> v FLOAT DEFAULT 0.0); syntax error, unexpected IDENT, expecting INDEX in: "create array" Do I miss something important? Please advise. Thanks, yingwei

8 years, 2 months

ODBC driver cannot be removed (or repaired, upgraded), Jan2014-SP2 .msi files

by László Sándor

Hi, With today's Jan2014-SP2 update, I wanted to uninstall and reinstall using the .msi files in Windows Server 2012. (I also wanted to change the install locations, as I have no access to the default location on C to change the bat files and edit where my dbfarm should be.) Maybe because I ran MonetDB5-SQL-Installer-x86_64-20140514.msi before MonetDB-ODBC-Installer-x86_64-20140514.msi, but now the ODBC remove fails, repairing also. A desperate deletion of the entire C:/Program Files/MonetDB, did not make things better, maybe it made worse. Could you let me know how I could proceed with uninstalling the ODBC driver? I do want to have a clean, stable reinstall (under the new location) which I can trust. Thanks! Laszlo

8 years, 2 months

ascii encoding error in block input (COPY INTO)

by László Sándor

Hi, I found previous notes<https://www.monetdb.org/pipermail/users-list/2010-November/004529.html>here that the server would expect UTF-8 input from the client or COPY INTO, so I need to work on my my Western ISO Latin 1 files. However, the error message is confusing about, COPY INTO complains about "'ascii' codec can't decode byte 0xc4 in position 8: ordinal not in range(128)." Shall I convert the data to UTF-8 then, or is that futile? What has the ascii codec to do with UTF-8? Thanks, Laszlo

8 years, 2 months

COPY INTO on Windows

by László Sándor

Hi, I investigated further, and actually I cannot get any CSV file working with monetDB's COPY INTO on Windows. Hannes filed a bug report today: https://www.monetdb.org/bugzilla/show_bug.cgi?id=3484 However, I tried other things since his/our attempts, e.g. removing the final end-of-line (EOL, still CRLF on Windows) or switching EOL to LF only for the temporary file (and changing the COPY INTO call's delimiters too, of course). Neither helped. Still the same error: Incomplete record at end of file. (Which I am not sure whether it literally means the end of the source textfile or simply the last line before breaking.) Basically, the question is, is there a way to get this working on Windows in Jan14-SP1? Or could someone please verify it's worth downgrading to a previous because it definitely can do COPY INTO? Thanks! Laszlo

8 years, 2 months

2022

2021

2020

2019

2018

2017

2016

2015

2014

2013

2012

2011

2010

2009

2008

2007

2006

2005

2004

users-list May 2014