April 2014 - users-list

Blob and Clob not implement in JDBC v 2.10?
by Phat Loc 11 Apr '14

11 Apr '14

Hello, I am not able to get Blob and Clob to work in JDBC v 2.10? I get a SQLFeatureNotSupportedException? Is this the correct behavior? Thanks, Phat

2 5

fastest way of having a number of rows of a table in monetdb ?
by Leon-Constantin Fopa 11 Apr '14

11 Apr '14

Helo everybody, To have the exact number of rows of a table t I actually run the following query SELECT COUNT(*) AS n FROM t; Is there a faster way to do this ? Best regards, Fopa

1 0

How to have the list of all tables names of a monetdb database ?
by Leon-Constantin Fopa 11 Apr '14

11 Apr '14

Hi everyone In postgresql one can have all tables names by running the following query SELECT table_name FROM information_schema.tables WHERE table_schema='public'; Is there something similar in monetdb to have the list of tables ? Best regards, Fopa

3 3

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