April 2014 - users-list

UPDATE fails with BATproject: does not match always
by Radovan Bičiště 11 Apr '14

11 Apr '14

Hello, I have a problem with following update and suspect data corruption. UPDATE crmkarta SET rodne_cislo = (SELECT MAX(rodne_cislo) FROM verka_klient WHERE crmkarta.cislo_karty = verka_klient.cislo_karty AND rodne_cislo IS NOT NULL) WHERE cislo_karty IS NOT NULL --AND aktivita > 0 AND EXISTS (SELECT verka_klient.cislo_karty FROM verka_klient WHERE crmkarta.cislo_karty = verka_klient.cislo_karty AND rodne_cislo IS NOT NULL); Can not create object [SQL State=22000] Next: BATproject: does not match always [SQL State=22000] Next: BATproject: does not match always [SQL State=22000] Would dump and restore of tables help ? We are on version Jan2014-SP1. Thank you, Radovan

1 0

Update with subquery
by George Kollias 09 Apr '14

09 Apr '14

Hello, Let's assume the script below (version Jan-2014-SP1): 1> select * from checking where id = (select id from account where name='ipzwhsqk'); | id | value | +==+========+ | 1 | 7580.67 | 2> select * from saving where id = (select id from account where name='ipzwhsqk'); | id | value | +==+========+ | 1 | 2599.01 | 3> select case when (select ((select value from saving where saving.id=account.id) + (select value from checking where checking.id=account.id)) from account where name='ipzwhsqk') < 1000000 then checking.value - 1000000 - 1 else checking.value - 1000000 end from checking where checking.id = (select id from account where name='ipzwhsqk'); | ifthenelse_isnull_<_L7 | +==============+ | -992420.33 | -- Update + same select as step 3. It's executed successfully. 4> update checking set value = ( select case when (select ((select value from saving where saving.id=account.id) + (select value from checking where checking.id=account.id)) from account where name='ipzwhsqk') < 1000000 then checking.value - 1000000 - 1 else checking.value - 1000000 end) where checking.id = (select id from account where name='ipzwhsqk'); 5> select * from checking where id = (select id from account where name='ipzwhsqk'); | id | value | +==+========+ | 1 | 7580.67 | So, it seems that the step 4's update has no impact. Am i missing something? Thank you, George

2 2

MonetDB usage of openssl and recently reported Security Vulnerability Issue in Openssl ( refer http://arstechnica.com/security/2014/04/critical-crypto-bug-in-openssl-opens-two-thirds-of-the-web-to-eavesdropping/ )
by Ashish Kumar Singh 09 Apr '14

09 Apr '14

Hi All, Can any one give some guideline on how MonetDB uses openssl library which is a dependency expressed during compile time and what kind of measures should taken to avoid security vulnerability reported recently with openssl. Please refer to links below : http://arstechnica.com/security/2014/04/critical-crypto-bug-in-openssl-open… http://blog.cloudflare.com/staying-ahead-of-openssl-vulnerabilities Regards, Ashish

2 1

decreasing table size
by Radovan Bičiště 09 Apr '14

09 Apr '14

Hello, Thanks to the great new feature sys.storage() I'm able to see table size. As per documentation I would expect that "DELETE FROM table" without any condition will reclaim the disk space. According the sys.storage() it does not. I tried to delete and than select from sys.storage() and the table size was still the same. Am I doing something wrong? Thank you, Radovan

2 2

Properly handling of concurrency conflicts
by Alastair McKinley 08 Apr '14

08 Apr '14

Dear all, I am currently testing an application running on MonetDB that experiences intermittent errors when under high concurrency. I believe these are aborted transactions (the mapi error code is -4). The applications loads a small input table using the mapi interface, runs two queries internally (including writing to an intermediate table) and then reads the result set. I have tried to produce a similar minimal example using the Python API which does produce concurrency errors and I am wondering what the correct thing to do on the client side is. Should I simply resubmit the failed query? The Python script used is shown below: import monetdb.sql import sys from multiprocessing import Process import uuid def f(): dbh = monetdb.sql.Connection(port=int(sys.argv[1]),database=sys.argv[2],hostname=sys.argv[3],autocommit=True) for i in range (0,20): tablename = "input_"+str(uuid.uuid1()).replace("-","_") cursor = dbh.cursor() #cursor.execute('create local temporary table %s (term int, p float) on commit preserve rows;' % tablename) cursor.execute('declare table %s (term int, p float);' % tablename) cursor.execute('insert into %s values (1,0.1);' % tablename) cursor.execute('select * from %s;' % tablename) print(cursor.fetchall()) cursor.execute('drop table %s;' % tablename) dbh.close() procs=[] for t in range(0,int(sys.argv[4])): p = Process(target=f) procs.append(p) p.start() for proc in procs: proc.join()? Running this locally with "python2.7 concurrency.py 50000 test_db 127.0.0.1 10" results in a number of error messages like: ?ProgrammingError: 40000!COMMIT: transaction is aborted because of concurency conflicts, will ROLLBACK instead What is the right thing to do? It seems that anything that writes to the db even when they are separate tables causes these messages. Best regards and thanks, Alastair

2 3

Flushing the cache
by Masood Mortazavi 08 Apr '14

08 Apr '14

Is there a method (available from mclient) to cause MonetDB to evict all cached columns without having to restart? Thanks, - Masood

2 1

Hash ('#') in column aliases
by Clarkson, Edward C. 06 Apr '14

06 Apr '14

Hi, I'm using the 64 bit windows Jan'14 release (v11.17.9 "Jan2014"). I wanted to confirm the following behavior is a bug before I reported it (this is from the mclient terminal): sql>CREATE TABLE test (a int); operation successful (5.230ms) sql>SELECT a AS "#" FROM test; +--+ | | +==+ +--+ 0 tuples (3.320ms) In contrast: sql>SELECT a AS "@" FROM test; +---+ | @ | +===+ +---+ 0 tuples (1.672ms) When there's data in the table, all the *results* are fine, it's just the field names which are wonky. Loosely, it looks like the first alias (I have not tried making columns with '#' in the name, so maybe there too?) to use '#' basically blanks out its and all following column names in the result set--so even if I'm missing something w/r/t escaping "#", it doesn't' seem right that all following columns should also be blank... Thanks, Ed

1 0

Scope of local temporary tables
by Alastair McKinley 05 Apr '14

05 Apr '14

Hi all, I was wondering if I could clarify something about the scope of local temporary tables in MonetDB. I imagined that these would be local to a single client session but it seems that I am wrong about this. If I execute the following Python script multiple times in succession, it fails as the table name is already in use. import monetdb.sql import sys dbh = monetdb.sql.Connection(port=int(sys.argv[1]),database=sys.argv[2],hostname=sys.argv[3],autocommit=True) cursor = dbh.cursor() cursor.execute('create local temporary table input (term int, p float) on commit preserve rows;'); cursor.execute('insert into input values ( 1,0.1);'); cursor.execute('select * from input;'); print(cursor.fetchall()) dbh.close() How is the local temporary table different from a normal table in this case? Best regards, Alastair

2 3

Fastest ('standard") way to load data into MonetDB
by Masood Mortazavi 04 Apr '14

04 Apr '14

What is the fastest way to load data into MonetDB, using its "standard" user APIs? -m.

2 3

How to update one table from another table or from aggregation in subqueries?
by George Kollias 04 Apr '14

04 Apr '14

Hello, On a related note, would it be possible to have a "with" statement inside the subquery, .i.e. "update t1 set data = *(with tmp_table as (select ...) select ... from ... where ...)* where exists (select * from t2 where t1.id= t2.id);"? According to my tests it's not possible. What would be the best way to simulate it in MonetDB? Using a View/Temporary Table? Thank you, George > Thanks for your reply and clarification! > With Jan2014 release, the two functions can be expressed in the following > working forms: > 1) update t1 set data=(select t1.data+t2.data from t2 where t2.id=t1.id) > where exists (select * from t2 where t1.id=t2.id); > 2) update t1 set data = (select sum(t2.data) from t2 where t2.id=t1.idgroup > by t2.id) where exists (select * from t2 where t1.id=t2.id);

1 0