users-list May 2015

users-list@monetdb.org

19 participants
19 discussions

by Luciano Sasso

Hi, What the cluster Advantages MonetDB, If a query in shard tables in a cluster can not be ordered, what the advantage of using a MonetDB cluster? besides the replication in case of failure. Att, -- Luciano Sasso Vieira Data Scientist & Solutions Architect luciano(a)gsgroup.com.br <http://www.gsgroup.com.br> | tel: 17 3353-0833 | cel: 17 99706-9335 www.gsgroup.com.br <http://www.gsgroup.com.br> --- Este email foi escaneado pelo Avast antivírus. http://www.avast.com

7 years

Concurrency test causes crash of MonetDB 5 server

by michael.witt＠uniserv.com

Hi, we experience a crash of MonetDB 5 server during tests of a multi-threaded client access via JDBC-Driver and MapiSocket: The application is already optimized to avoid "concurrency conflicts" because of the "Optimistic Concurrency Control" concept. The test simulates client access via two threads. All update operations (INSERT, UPDATE, DELETE, DROP TABLE, CREATE TABLE) are done in serial. All read operations are done in parallel to other read operations or to update operations. The test causes a crash of mserver5.exe after a few minutes. If the test uses just one thread, all read and update operations are done in serial and the server does not crash. What is the rule of thumb to avoid crashes when using several client threads? Crash details: Problem signature: Problem Event Name: APPCRASH Application Name: mserver5.exe Application Version: 0.0.0.0 Application Timestamp: 55389f58 Fault Module Name: StackHash_8ec6 Fault Module Version: 6.1.7601.18247 Fault Module Timestamp: 521eaf24 Exception Code: c0000374 Exception Offset: 00000000000c4102 OS Version: 6.1.7601.2.1.0.144.8 Locale ID: 1031 Additional Information 1: 8ec6 Additional Information 2: 8ec62499039d1d8234b025a62c725eb7 Additional Information 3: f8f3 Additional Information 4: f8f3e520847c0684b15d4080171779ef # MonetDB 5 server v11.19.9 "Oct2014-SP2" # Serving database 'uniserv', using 4 threads # Compiled for x86_64-pc-winnt/64bit with 64bit OIDs dynamically linked # Found 10.000 GiB available main-memory. .. # MonetDB/SQL module loaded Michael Witt

7 years

1-to-N (bat) UDF

by Roberto Cornacchia

Hi there, I need a string tokenizer in MonetDB. The problem I have is not with the function itself, but with the fact that this is a 1 to N rows function. Implementing this for a single string value is easy enough, using a table function that takes a string a returns a table: create function tokenize(s string) returns table (token string) external name tokenize; select * from tokenize("one two three"); That's fine. The issue I'm having is with extending this to a column of strings. Ideally, given a string column one two three four five six seven eight I'd like to get an output along these lines (simplistic representation here): one two three | one one two three | two one two three | three four five six | four four five six | five four five six | six seven eight | seven seven eight | eight I can sure code the c function and the mal wrapper to implement this, but I can't see how to map it to SQL, given that table functions don't accept identifiers as parameters. Any idea? Any possible workaround? Thanks, Roberto

7 years, 1 month

should the SAMPLE command produce random results?

by Anthony Damico

# here's a reproducible example using R code to repeat the sampling 1000 times. in both SAMPLE examples below, the database pulls the 2 less than 200 times out of 1000. shouldn't it be close to 500 out of 1000? this seems not random (misleading to users?) sorry if i'm misunderstanding something.. thank you!! # start in an empty directory somewhere # setwd( "C:/My Directory/MonetDB" ) # # # # # # # # # START OF SETUP - no editing required library(MonetDB.R) batfile <- monetdb.server.setup( database.directory = paste0( getwd() , "/MonetDB" ) , monetdb.program.path = ifelse( .Platform$OS.type == "windows" , "C:/Program Files/MonetDB/MonetDB5" , "" ) , dbname = "test" , dbport = 50000 ) pid <- monetdb.server.start( batfile ) db <- dbConnect( MonetDB.R() , "monetdb://localhost:50000/test" , wait = TRUE ) # # # END OF SETUP dbGetQuery( db , "SELECT 1 AS col UNION ALL SELECT 2 AS col" ) out <- NULL for ( i in 1:1000 ){ out <- c( out , dbGetQuery( db , "SELECT * FROM ( SELECT 1 AS col UNION ALL SELECT 2 AS col ) AS temp SAMPLE 0.5" ) ) } # not random table( unlist( out ) ) # 1 2 # 880 120 out <- NULL for ( i in 1:1000 ){ out <- c( out , dbGetQuery( db , "SELECT * FROM ( SELECT 1 AS col UNION ALL SELECT 2 AS col ) AS temp SAMPLE 1" ) ) } # ALSO not random table( unlist( out ) ) # 1 2 # 856 144

7 years, 1 month

a few performance improvement questions

by Anthony Damico

hi, i've been running some pretty big queries on a few tables (total of 900 million records) on a 64GB windows machine and i'm curious if anyone might provide advice about whether any of these strategies are computationally expensive? i am maxing out my available RAM in my queries a lot, and at the point it hits that ceiling, the computation starts dragging. here's my pseudo-sql that displays the kinds of commands i've been running. thanks! ============ (a) i am conducting a left join where not all records match. the non-matching records need to be zeroes in general, is this the least computationally demanding way to get an average amount per person, including the zeroes? SELECT AVG( sum_amount ) AS avg_amt_per_person FROM ( SELECT person_id , SUM( CASE WHEN amount IS NULL THEN 0 ELSE amount END ) AS sum_amount FROM ( ( SELECT person_id FROM person_table ) AS a LEFT JOIN ( SELECT person_id , amount FROM amount_table ) AS b ON a.person_id = b.person_id ) AS temp_table GROUP BY person_id ) ; ============ (b) i am on windows (so no strptime) and i have dates in either a mm/dd/yy format but some january thru september are m/dd/yy (i.e. the month can either be one or two digits). i am just calculating the month as part of my query on-the-fly. is it foolish of me not to add a new, permanent column to the data set? i don't have a good sense of whether this segment slows down my query by a lot. it is one of my join/merge variables. CAST( CASE WHEN SUBSTRING( some_date , 2 , 1 ) = '/' THEN SUBSTRING( some_date , 1 , 1 ) ELSE SUBSTRING( some_date , 1 , 2 ) END AS DOUBLE ) AS some_month ============ (c) i have a three tables that have the same structure that get LEFT JOINed onto my main table. so right now i am doing something like-- main_table LEFT JOIN second_table LEFT JOIN third_table LEFT JOIN fourth_table is it smarter fo me to do this instead? -- CREATE TABLE stacked_table FROM second_table UNION ALL third_table UNION ALL fourth_table main_table LEFT JOIN stacked_table

7 years, 2 months

is bugzilla down?

by Anthony Damico

https://www.monetdb.org/bugzilla/ connection times out -- from computers in three countries.

7 years, 2 months

Permission Issues

by Raffael Marty

I have a table called ip_range with an inet field (ip). I do the following query: select * from ip_range where ip << inet '10.0.0.0/8’ SELECT: no such binary operator 'left_shift(inet,inet)' Which results in an error. This is by using a non system user. If I create the same table as moentdb user and do the same query, it works as intended. Am I missing something with regards to permissions? Running 11.19.11-20150 on Ubuntu. Thanks Raffael To reproduce: create table test (a inet); insert into test (a) values ('10.0.0.1’); select * from test where a << inet '10.0.0.0/8’; — Visual Analytics Workshop at #BlackHat US in August: http://bit.ly/1FN1W5e Raffael Marty ceo @ pixlcloud http://pixlcloud.com @raffaelmarty http://raffy.ch

7 years, 2 months

Picky SQL sytnax

by Raffael Marty

I am having some issues with the SQL syntax in MonetDB. Is it just really picky or am I doing something that is not SQL99 compatible? Couple examples: This query works: select source_address, count(distinct destination_port) as distcount from flows group by source_address having count(distinct destination_port)>1 limit 10; The following query doesn’t work. I am just trying to use the AS keyword to make things a bit more readable. select source_address, count(distinct destination_port) as distcount from flows group by source_address having distcount>1 limit 10; SELECT: identifier 'distcount’ unknown I had numerous other issues where little things like quotes and such made a difference. For example, creating a table would not work like this: CREATE TABLE foo (range text); syntax error, unexpected RANGE, expecting FOREIGN or PRIMARY or UNIQUE in: "create table foo (range" This works: CREATE TABLE foo (“range” text); Third example: (I could never get this working - tried removing the quotes, which made things worse) sql>INSERT INTO ranges ("range", "title", "external", "description", "routeadvertised", "X", "Y", "Z", "N", "M", "O", "P", "Q", "R", "S", "T", "U", "V", "A", "B", "C", "D", "E", "F", "G", "H", "I", "J" ) VALUES ('0.0.0.0/0', 'IPv4', , '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', 0, 0, '', '', '', '', '', 1, '1', '' ); syntax error, unexpected ',' in: "insert into ranges ("range", "title", "external", "description", "routeadvert" Is there a statement length restriction? Same happens whether I use the mclient or go through Python code. I am running version 11.19.11-20150 Thanks so much! Raffael — Visual Analytics Workshop at #BlackHat US in August: http://bit.ly/1FN1W5e Raffael Marty ceo @ pixlcloud http://pixlcloud.com @raffaelmarty http://raffy.ch

7 years, 2 months

Announcement: New Oct2014-SP3 Bugfix release of MonetDB suite

by Sjoerd Mullender

The MonetDB team at CWI/MonetDB BV is pleased to announce the Oct2014-SP3 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/>. Oct 2014-SP3 bugfix release Build Environment * We now also create debug packages for Debian and Ubuntu. MonetDB Common * Replaced the rangejoin implementation with one that uses imprints if it can. Bug Fixes * 3466: UPDATE statements fails with "GDKerror: MT_mremap() failed" * 3602: Surprising overload resolution of generate_series * 3613: SQL data dictionary contains columns names which are also special keywords. This causes unexpected/unneeded SQL query errors * 3645: Network address operators such as << and <<= do not work * 3647: missing BAT for a column leads to crash in gtr_update_delta * 3648: memory corruption on unclean connection shutdown with local temporary tables * 3650: Naming of persistent BATs is fragile * 3653: PREPARE crashes mserver if unbound variable is function parameter * 3655: SQL WHERE -1 in (-1) issue? * 3656: error message after calling fitsload() * 3660: Incorrect Results for Comparison Operators on inet Datatype * 3661: Ship debug symbols for pre-built binaries * 3662: UPDATE row with row value constructor crashes monetdb server * 3663: Incorrect result ROW_NUMBER in subquery * 3664: SQLstatementIntern missing parameter when using jsonstore * 3665: inter-session starvation issue, particularly affects sys.queue * 3666: casting text column to inet truncating text column and resulting inet for first occurrence only * 3667: insert of negative value for oid column aborts mserver5 process with assertion failure * 3669: ALTER TABLE <tbl_nm> ADD CONSTRAINT <tbl_uc1> UNIQUE (col1, col2, col3) causes Assertion failure and abort * 3671: ODBC-Access on Windows 2012 does not work - E_FAIL * 3672: libbat_la-gdk_utils.o: relocation R_X86_64_PC32 against `MT_global_exit' can not be used when making a shared object * 3676: merovingian hangs trying to exit * 3677: Crash in BATgroup_internal (caused by 87379087770d?) * 3678: Ruby driver installation ignores prefix * 3680: Prepared statements fail on execution with message 'Symbol type not found' * 3684: Wrong query result set WHERE "IS NULL" or "NOT IN" clauses uses in combination with ORDER, LIMIT and OFFSET * 3687: 'bat.insert' undefined * 3688: Crash at exit (overrun THRerrorcount?) * 3689: No more connections accepted if a single client misbehaves * 3690: find_fk: Assertion `t && i' failed. * 3691: conversion of whitespaces string to double or float is accepted without an error during insert * 3693: algebra.join undefined (caused by non-existing variables in the plan) * 3696: Inconsistent behavior between dbl (SQL double) and flt (SQL real) data types and across platforms * 3697: mserver5[26946]: segfault at 0 ip 00007f3d0e1ab808 sp 00007f3cefbfcad0 error 4 in lib_sql.so[7f3d0e180000+16c000] * 3699: segfault again! (during last week I found 3 segfault bugs already) * 3703: INSERT INTO a MERGE TABLE crashes mserver5 * 3704: Unknown identifier from subquery * 3705: Assertion failure in rel_bin.c:2274: rel2bin_project: Assertion `0' failed. * 3706: Assertion failure in gdk_bat.c: BATassertHeadProps: Assertion `!b->H->sorted || cmp <= 0' failed. * 3709: "BATproject: does not match always" on abusive use of ALTER TABLE SET READ ONLY

7 years, 2 months

Documenting date time functionality in MonetDB

by Robin Cijvat

I am currently working on documenting date/time functionality that exists in MonetDB and I have run into some problems/questions. I hope someone can help me out: 1) According to the Temporal Types page (https://www.monetdb.org/Documentation/SQLreference/Temporal), there should be a type 'daytime'. However, the following fails: create table t (b daytime);-> type (daytime) unknown in: "create table t (b daytime)" Does anyone know whether this feature has been removed or that this is a bug (or that I am perhaps missing something)? 2) Converting strings to dates works like a charm: *SELECT str_to_date('23-09-1987', '%d-%m-%Y');*-> 1987-09-23 But how about converting strings to times/timestamps: *SELECT str_to_date('11:40', '%H:%M');* -> null I can not seem to find any definitions of built in functions that take a string and produce anything other than a date. Thanks in advance! Robin PS: I am running the latest default branch on Ubuntu 14.04.

7 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 2015