users-list September 2013

users-list@monetdb.org

25 participants
23 discussions

by Anthony Damico

this issue is new for SP-4 ..i have been using this syntax for a long time and never encountered this problem until the upgrade :( > dbGetQuery( db , "select ( sum( ( bene_sex_ident_cd = 1 ) ) ) / ( count( * ) ) as pct_male from x11" ) pct_male 1 0 > dbGetQuery( db , "select ( sum( ( bene_sex_ident_cd = 1 ) )*1.000 ) / ( count( * )*1.000 ) as pct_male from x11" ) Error in .local(conn, statement, ...) : Unable to execute statement 'select ( sum( ( bene_sex_ident_cd = 1 ) )*1.000 ) / ( count( * )*1.000 ) as pct_male from x11'. Server says 'too many digits (19 > 18)' [#22003]. > what diagnostics can i run for you to troubleshoot this? thanks!! :)

8 years, 9 months

new gdk bug (on windows) that might've been introduced by Feb2013-SP4

by Anthony Damico

if you run this R code (with MonetDB.R), you will automatically load a series of brazilian census bureau data sets onto your computer. after all tables have been downloaded and imported, the R script will run a very simple CREATE TABLE command that will cause mserver to shut down when it should not. library(downloader) setwd( "C:/My Directory/CENSO/" ) source_url( " https://raw.github.com/ajdamico/usgsd/master/Censo%20Demografico/download%2…" , prompt = FALSE , echo = TRUE ) after mserver.exe crashes, even if i re-load mserver.exe manually and then connect to it with mclient.exe, and run this block of code: create table c10_dom_pre_fpc as (SELECT * FROM ac_dom) UNION ALL (SELECT * FROM al_dom) UNION ALL (SELECT * FROM am_dom) UNION ALL (SELECT * FROM ap_dom) UNION ALL (SELECT * FROM ba_dom) UNION ALL (SELECT * FROM ce_dom) UNION ALL (SELECT * FROM df_dom) UNION ALL (SELECT * FROM es_dom) UNION ALL (SELECT * FROM go_dom) UNION ALL (SELECT * FROM ma_dom) UNION ALL (SELECT * FROM mg_dom) UNION ALL (SELECT * FROM ms_dom) UNION ALL (SELECT * FROM mt_dom) UNION ALL (SELECT * FROM pa_dom) UNION ALL (SELECT * FROM pb_dom) UNION ALL (SELECT * FROM pe_dom) UNION ALL (SELECT * FROM pi_dom) UNION ALL (SELECT * FROM pr_dom) UNION ALL (SELECT * FROM rj_dom) UNION ALL (SELECT * FROM rn_dom) UNION ALL (SELECT * FROM ro_dom) UNION ALL (SELECT * FROM rr_dom) UNION ALL (SELECT * FROM rs_dom) UNION ALL (SELECT * FROM sc_dom) UNION ALL (SELECT * FROM se_dom) UNION ALL (SELECT * FROM sp1_dom) UNION ALL (SELECT * FROM sp2_rm_dom) UNION ALL (SELECT * FROM to_dom ) WITH DATA; the exact same crash occurs, so this is a new mserver.exe bug not a MonetDB.R bug. should i file a bug report? thanks!!! in mserver---- # MonetDB 5 server v11.15.15 "Feb2013-SP4" # Serving database 'censo_demografico', using 8 threads # Compiled for x86_64-pc-winnt/64bit with 64bit OIDs dynamically linked # Found 7.860 GiB available main-memory. # 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 # Listening for connection requests on mapi:monetdb://127.0.0.1:50011/ # MonetDB/JAQL module loaded # MonetDB/SQL module loaded >= .\..\..\gdk\gdk_posix.c:800: MT_mremap(bat\02\65\26501.tail,468c50000,1889785 61024,755914244096): GDKextend() failed in mclient---- C:\Windows\System32>cd .. C:\Windows>cd .. C:\>cd "Program Files" C:\Program Files>cd MonetDB C:\Program Files\MonetDB>mclient censo_demografico -p 50011 'mclient' is not recognized as an internal or external command, operable program or batch file. C:\Program Files\MonetDB>mclient 'mclient' is not recognized as an internal or external command, operable program or batch file. C:\Program Files\MonetDB>ls 'ls' is not recognized as an internal or external command, operable program or batch file. C:\Program Files\MonetDB>dir Volume in drive C has no label. Volume Serial Number is 882E-FD8A Directory of C:\Program Files\MonetDB 09/06/2013 04:34 PM <DIR> . 09/06/2013 04:34 PM <DIR> .. 09/27/2013 12:27 PM <DIR> MonetDB5 0 File(s) 0 bytes 3 Dir(s) 451,568,218,112 bytes free C:\Program Files\MonetDB>cd MonetDB5 C:\Program Files\MonetDB\MonetDB5>mclient censo_demografico -p 50011 user(win32):monetdb password: Welcome to mclient, the MonetDB/SQL interactive terminal (Feb2013-SP4) Database: MonetDB v11.15.15 (Feb2013-SP4), 'censo_demografico' Type \q to quit, \? for a list of available commands auto commit mode: on sql>create table c10_dom_pre_fpc as (SELECT * FROM ac_dom) UNION ALL (SELECT * F ROM al_dom) UNION ALL (SELECT * FROM am_dom) UNION ALL (SELECT * FROM ap_dom) UN ION ALL (SELECT * FROM ba_dom) UNION ALL (SELECT * FROM ce_dom) UNION ALL (SELEC T * FROM df_dom) UNION ALL (SELECT * FROM es_dom) UNION ALL (SELECT * FROM go_do m) UNION ALL (SELECT * FROM ma_dom) UNION ALL (SELECT * FROM mg_dom) UNION ALL ( SELECT * FROM ms_dom) UNION ALL (SELECT * FROM mt_dom) UNION ALL (SELECT * FROM pa_dom) UNION ALL (SELECT * FROM pb_dom) UNION ALL (SELECT * FROM pe_dom) UNION ALL (SELECT * FROM pi_dom) UNION ALL (SELECT * FROM pr_dom) UNION ALL (SELECT * FROM rj_dom) UNION ALL (SELECT * FROM rn_dom) UNION ALL (SELECT * FROM ro_dom) U NION ALL (SELECT * FROM rr_dom) UNION ALL (SELECT * FROM rs_dom) UNION ALL (SELE CT * FROM sc_dom) UNION ALL (SELECT * FROM se_dom) UNION ALL (SELECT * FROM sp1_ dom) UNION ALL (SELECT * FROM sp2_rm_dom) UNION ALL (SELECT * FROM to_dom ) WITH DATA; GDK reported error. HEAPextend: failed to extend to 755914244096 for 02\65\26501.tail: MT_mremap() f ailed sql> sql>

8 years, 10 months

MonetDB compile error 11.15.15

by Brian Hood

Hi Guys, I just trying to update my Arch Linux package and when building came up with this compile error the package passed the sha1sum after download. Hope someone can help. In file included from /usr/include/string.h:25:0, from /var/abs/AUR/monetdb/src/MonetDB-11.15.15/monetdb5/extras/jaql/parser/jaql.y:23: /usr/include/features.h:327:4: warning: #warning _FORTIFY_SOURCE requires compiling with optimization (-O) [-Wcpp] # warning _FORTIFY_SOURCE requires compiling with optimization (-O) ^ y.tab.c: In function 'jaqlparse': y.tab.c:1665:32: error: 'scanner' undeclared (first use in this function) y.tab.c:1665:32: note: each undeclared identifier is reported only once for each function it appears in make[9]: *** [libjaqlp_la-jaql.tab.lo] Error 1 Regards, Brian Hood

8 years, 10 months

regular expression - substring

by Budulinku Dejmihrasku

Hi, i am looking for any idea/suggestion how to do something like Oracle has, i.e. REGEXP_SUBSTR(source, pattern[, position [, occurrence[, match_parameter]]]) which returns the desired substring. why i need this: i have vast amount of data to parse - i.e. i need to get all of the many possible [parameter=value] pairs from an url access log in order to feed the dimension tables w/parameter-value from the URL by parsing the string directly in MonetDB when i write a SQL function to do this (or any string parsing as a matter of fact), it is rather slow when parsing millions of records. i know that there is the PCRE.MAL and i already use with some success functions INDEX, MATCH or REPLACE but i was not able to find anything similar to the REGEXP_SUBSTR functionality to get the desired substring in one go would most likely help me to get better performance any suggestion? please? thanks milan

8 years, 10 months

Stopping queries, deleting tuples and other general use questions

by Ruben Silva

Hi, after a while using MonetDB some general use questions pops up. I've collected some which answers I think will be of great help for any MonetDB user. * * *#1:* In the monetdb manpage we can read: *“The kill command immediately sends a SIGKILL and should only be used as last resort for a database that doesn't respond any more. Killing a database may result in (partial) data loss.”* * * 1.1 If kill is the last resort, what are the other options? * * *#2:* System monitor (Oct 2013)<http://www.monetdb.org/Documentation/Cookbooks/SQLrecipes/monitor> 2.1 Will this be available in October or is there a new estimate? 2.2 Does the current development version include this feature yet so we can test it? I'm interested mainly in the methods sys.pause(qtag), sys.resume(qtag) and sys.stop(qtag), *specially sys.stop(qtag)*. 2.4 Is there some alternative method already available in the Feb2013 release to achieve this action? *#3:* In the monetdb manpage we can read about the *nclients* property: *“Sets the maximum amount of clients that can connect to this database at the same time. Setting this to a high value is discouraged.” * 3.1 What do you consider as a high value? Fabian Groffen stated in a similar question *"Concurrency is always a problem for MonetDB, since in the ideal case it means the processes are fighting for resources with each other." * 3.2 Does this mean (theoretically) that if I can run Query A in 10s in a system with resources R (Ram, Disk, Processor), with Rx2 I can run Query A and B (equivalent in effort to A) in the same 10s? *#4:* www.monetdb.org/Documentation/Manuals/SQLreference/Transactions: *"WARNING. The tuples being deleted are only marked as such. They do not reduce the table size. It calls for a vacuum cleaning algorithm."* * * I've found the /usr/lib/monetdb5/createdb/20_vacuum.sql script and after some googling I found the following post* * http://mail.monetdb.org/pipermail/users-list/2011-August/005051.html without an answer. 4.1 Does the following sequence frees the space of the deleted rows? sql>delete from sys.transfer_ip ; 651950 affected rows (117.620ms) sql>call vacuum('sys','transfer_ip'); Best regards, Ruben Silva

8 years, 10 months

Create Sorted View in monetdb

by Khaled Dehdouh

Please can you help me. I want to create a view sorted according to attributes like Oracle, my query is as follows: *Create view V1 as Select Name, First_Name, Age from **People O**redr by Name**;* but Monetdb shows me the following error "CREATE VIEW: ORDER BY not supported." How should I do to create a sorted view ?.

8 years, 10 months

Announcement: New Feb2013-SP4 Bugfix release of MonetDB suite

by Sjoerd Mullender

The MonetDB team at CWI/MonetDB BV is pleased to announce the Feb2013-SP4 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/>. Feb 2013-SP4 bugfix release Merovingian * monetdb now no longer compresses output when not connected to a terminal, bug #3292 Bug Fixes * 2781: SQL localtime() returns GMT time * 2962: client timezone information needs to be handled also for modifications to timestamp values * 3232: The function weekofyear returns results not according to the iso 8601 standard * 3255: M5 tests inlineFunction & ifthencst fail since changeset 207ed7d7e03a * 3292: monetdb status should print full line * 3307: distinct multicolumn performance and implementation suggestion for count distinct * 3310: Cast value to integer gives wrong result * 3311: strLength assertion on join and concat * 3313: Assertion in rel2bin_project * 3315: Incorrect behavior with subqueries in DELETE * 3317: correlated subselect does not work * 3318: UPDATE does not work with DECLEAREd TABLE in FUNCTION * 3319: UNIQUE column in DECLAREd TABLE in FUNCTION results in segfault * 3329: Function AVG fails during runtime in a procedure * 3330: Corrupt mitosis plan with group-by query * 3332: MEDIAN: no such operator 'median(decimal)' * 3336: DB files not removed if all rows are deleted, even after restart * 3337: Monet ODBC gives wrong schema information * 3339: Segvault in complex function call * 3340: crash on seeming incorrect SQL query * 3341: segfault caused by select rank() on a newly-loaded table * 3342: incorrect automatic cast from decimal to tinyint * 3345: copy binary into fails with the wrong error message * 3346: Deadlock in mal_dataflow's q_dequeue() (SciQL-2 branch) * 3348: SQL: LIKE incorrect with _ * 3349: When selecting everything from a joined result, column contents are incorrect in one of my use cases. * 3351: gzip'ed files cannot be bulkloaded into DB * 3352: Median function with decimal type doesn't work * 3353: Readonly tables call for UNSET * 3354: Introduce query time-out * 3358: Remote client connection stays in CLOSE_WAIT state * 3360: uninitialized memory causing bad output in query plans * 3362: ALTER TABLE operations silently remove previously set READ ONLY property * 3363: a join causes SIGSEGV after creation of Unique Key * 3367: Fully qualified order by column gives "relational query without result" * 3369: \0 Byte appears in MAPI

8 years, 10 months

Re: Contents of users-list Digest, Vol 13, Issue 20

by Edgar Mejia

1. I have MonetDB Database Server Toolkit v1.1 (Feb2013-SP3) (i install so: sudo apt-get install monetdb5-sql monetdb-client) shell> monetdbd create /path/to/mydbfarm shell> monetdbd start /path/to/mydbfarm shell> monetdb create voc shell> monetdb release voc 2. Ubuntu 12.04.3 LTS 3. Schema and data: Schema: CREATE TABLE timedimension ( id integer NOT NULL, yeart int DEFAULT NULL, quarter varchar(2) DEFAULT NULL, weekyear int DEFAULT NULL, dayt int DEFAULT NULL, namemonth varchar(10) DEFAULT NULL, nameday varchar(20) DEFAULT NULL, montht int DEFAULT NULL, day_of_week varchar(15) DEFAULT NULL, date date DEFAULT NULL, month2 char(2) DEFAULT NULL ) CREATE TABLE bi_con_cdr_diario ( ano INT , facmesfac VARCHAR(6) , comcodigo INT , succodigo VARCHAR(3) , procodigo INT , pronombre VARCHAR(20) , prycodigo VARCHAR(20) , fecllamada timestamp , clicodigo INT , clinombre VARCHAR(80) , vencodigo INT , vennombre VARCHAR(50) , sercodigo VARCHAR(3) , pryservicio VARCHAR(4) , sertipo VARCHAR(3) , serclase VARCHAR(4) , trdtipotelefono CHAR(10) , cfacodigo INT , cfanombre VARCHAR(30) , trddurllamada INT , minutosreales DOUBLE , minutosenteros INT , minutosfacturados DOUBLE , costo DOUBLE , costohit DOUBLE , valorbruto DOUBLE , valorneto DOUBLE , trdvalorlocalneto DOUBLE , trdvalorlocalimpu DOUBLE , valorlocal DOUBLE , codpaisori INT , nompaisori VARCHAR(80) , codpaisdes INT , nompaisdes VARCHAR(80) , comnombre2 VARCHAR(15) , linprnombre VARCHAR(40) , trdcodciuori BIGINT , trdnomciuori VARCHAR(80) , sernombre VARCHAR(50) , idtimedimension integer , linprcodigo INT , trdcodciudes INT , trdnomciudes VARCHAR(80) ); Timedimension data:20080101,2008,"1",1,1,"Ene","",1,"Martes","2008-01-01","01" bi_con_cdr_diario data:2013,"092013",4,"CTA",4,"EMBRATEL","HITBRAEMB","2013-09-04 00:00:00",3608,"Air Liquide do Brasil Ltda - Canoas",420,"Flavio Eugenio/Caroline Carneiro","E-N","E-N","ddd","PSTN","Fijo",89,"Consumo",459,7.65,10,10,0.66924,0.66924,1.43199992,1.1456000328063967,3.437,0.471,3.9080000000000004,55,"Brasil",55,"Brazil","HitBrasil","HIT VOICE",0,"0","HIT CALLSERVICE EMB NAL",20130904,1,0,"0",\N But the error is when i do group by as (select sum(minutosfacturados),succodigo,montht,comnombre2 from bi_con_cdr_diario as cdr inner join timedimension as td on cdr.idtimedimension=td.id where comcodigo=4 group by succodigo,comnombre2,montht;) and have 10.000.000 of rows. I appreciate very much your help. I believe this is one great product. Best Regards, Edgar Mejia

8 years, 10 months

Re: Contents of users-list Digest, Vol 13, Issue 20

by Edgar Mejia

Thank you Jennie Zhang. 1. I have MonetDB Database Server Toolkit v1.1 (Feb2013-SP3) (i install so: sudo apt-get install monetdb5-sql monetdb-client) shell> monetdbd create /path/to/mydbfarm shell> monetdbd start /path/to/mydbfarm shell> monetdb create voc shell> monetdb release voc 2. Ubuntu 12.04.3 LTS 3. Schema view schemadatamonetdb.sql file 4. Data: timedimension view datatimediM.csv bi_con_cdr_diario: cdr.csv But the error is when i do group by as (select sum(minutosfacturados),succodigo,montht,comnombre2 from bi_con_cdr_diario as cdr inner join timedimension as td on cdr.idtimedimension=td.id where comcodigo=4 group by succodigo,comnombre2,montht;) and have 10.000.000 of rows. I appreciate very much your help. I believe this is one great product. Best Regards, Edgar Mejia

8 years, 10 months

Monetdb crashed as group by

by Edgar Mejia

select sum(minutosfacturados),succodigo,montht,comnombre2 from bi_con_cdr_diario as cdr inner join timedimension as td on cdr.idtimedimension=td.id where comcodigo=4 group by succodigo,comnombre2,montht; but for example as this query work fine: select sum(minutosfacturados),succodigo,montht from bi_con_cdr_diario as cdr inner join timedimension as td on cdr.idtimedimension=td.id where comcodigo=4 group by succodigo,montht; What i need to do for to fix this? Thanks Edgar M

8 years, 10 months

2022

2021

2020

2019

2018

2017

2016

2015

2014

2013

2012

2011

2010

2009

2008

2007

2006

2005

2004

users-list September 2013