December 2012 - users-list

Unable to execute prepared statements with a large number of parameters (say over 140 or so)
by Percy Wegmann 14 Dec '12

14 Dec '12

I couldn't find any existing bugs that cover this, so I logged a new one: http://bugs.monetdb.org/show_bug.cgi?id=3208 This is kind of a show-stopper for me right now, so I'm hoping that someone might have some idea where to start looking for a solution. I'm debugging sql_scenario.c right now, but it'll take me some time to get familiar with the code. Cheers, -- Percy Wegmann +1 512 637 8500 ext 148 _______________________________________________ users-list mailing list users-list(a)monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list

1 0

Still searching to find the Octopus :-)
by Masood Mortazavi 13 Dec '12

13 Dec '12

I'm still searching for user documentation that would explain MonetDB/octopus usage/feature. Does any exist? Thanks, Masood P.S. My earlier e-mails regarding the octopus documentation was somewhat rushed, it seems. The documentation in the man pages are actually for remtoe access and funnel-multiplex features, which seem entirely different. _______________________________________________ users-list mailing list users-list(a)monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list

3 4

Does anybody know the endian ness (little, big) of BAT file?
by swapnil joshi 10 Dec '12

10 Dec '12

Hi, Does anybody know the endian ness (little, big) of BAT file? -- Regards, Swapnil K. Joshi _______________________________________________ users-list mailing list users-list(a)monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list

2 1

Adding group_concat support
by Tapomay Dey 07 Dec '12

07 Dec '12

Just confirming. Can we yet define functions without having to recompile. Planning to try adding group_concat support. Regards, Tapomay. _______________________________________________ users-list mailing list users-list(a)monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list

2 2

Re: Failed to load 128GB CSV file using Oct2012
by Stefan Manegold 05 Dec '12

05 Dec '12

Jennie, just to be sure: are you sure there is sufficient free disk space on your machine, say, twice the expected database size before starting the load? How large is your database (and sql_logs) just after the failure while the server is still running? How much disk space is still free at that time? Stefan Ying Zhang <Y.Zhang(a)cwi.nl> wrote: Hai all, I try to load a CVS file of ~128GB in the ssdb branch (based on Oct2012), but after quite sometime, it failed with: #GDKrealloc(19598973840) fails, try to free up space [memory in use=22695528216,virtual memory in use=138875848472] #GDKrealloc(19598973840) result [mem=22695528216,vm=138875848472] In the last Sep., I was able to execute the same query with the same data. But now it takes much longer and ends in a crash. For INS1-ers, the command to start the server: /export/scratch2/zhang/monet-install/ssdb/debug/bin/mserver5 --set gdk_dbfarm=/export/scratch2/zhang/dbfarm --dbname=ssdb --set mapi_usock=/export/scratch2/zhang/dbfarm/ssdb/.mapi.sock --set monet_vault_key=/export/scratch2/zhang/dbfarm/ssdb/.vaultkey --set gdk_nr_threads=8 --set max_clients=64 --set sql_optimizer=default_pipe --set mapi_port=60000 The command to start the query: cd /ufs/zhang/papers/ssdb/monetdb ./run.sh loadimg small output Does anyone have an idea? If anymore information is needed, please let me know. I'm re-running the query to get a GDB stack trace. Thanks! Jennie _______________________________________________ users-list mailing list users-list(a)monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list _______________________________________________ users-list mailing list users-list(a)monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list

3 2

Query takes too much time
by Ivan Videla 05 Dec '12

05 Dec '12

Hi All, I have a table with 268 millions of records from supermarket transactions tran_id, prod_id, quantity, price, cost and i want to make a calculation like sum(quantity*(price-cost)). But this query takes too much time. I want to know if i can improve the results using an index. Or monetdb itself use an index on-the-fly. If not i would like to know how can i make an index to improve the execution time. Regards, Ivan _______________________________________________ users-list mailing list users-list(a)monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list

3 12

Failed to load 128GB CSV file using Oct2012
by Ying Zhang 05 Dec '12

05 Dec '12

Hai all, I try to load a CVS file of ~128GB in the ssdb branch (based on Oct2012), but after quite sometime, it failed with: #GDKrealloc(19598973840) fails, try to free up space [memory in use=22695528216,virtual memory in use=138875848472] #GDKrealloc(19598973840) result [mem=22695528216,vm=138875848472] In the last Sep., I was able to execute the same query with the same data. But now it takes much longer and ends in a crash. For INS1-ers, the command to start the server: /export/scratch2/zhang/monet-install/ssdb/debug/bin/mserver5 --set gdk_dbfarm=/export/scratch2/zhang/dbfarm --dbname=ssdb --set mapi_usock=/export/scratch2/zhang/dbfarm/ssdb/.mapi.sock --set monet_vault_key=/export/scratch2/zhang/dbfarm/ssdb/.vaultkey --set gdk_nr_threads=8 --set max_clients=64 --set sql_optimizer=default_pipe --set mapi_port=60000 The command to start the query: cd /ufs/zhang/papers/ssdb/monetdb ./run.sh loadimg small output Does anyone have an idea? If anymore information is needed, please let me know. I'm re-running the query to get a GDB stack trace. Thanks! Jennie _______________________________________________ users-list mailing list users-list(a)monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list

1 0

INET query/type conversion issues
by Percy Wegmann 04 Dec '12

04 Dec '12

Hi MonetDB Team, I encountered a strange problem related to querying against an INET column. To reproduce: percy@percy-sandbox:~$ uname -a Linux percy-sandbox 3.2.0-33-generic #52-*Ubuntu* SMP Thu Oct 18 16:29:15 UTC 2012 x86_64 x86_64 x86_64 GNU/Linux percy@percy-sandbox:~$ mclient -u monetdb -d click password: Welcome to mclient, the MonetDB/SQL interactive terminal (Oct2012) Database: *MonetDB v11.13.3 (Oct2012)*, 'mapi:monetdb://percy-sandbox:50000/click' Type \q to quit, \? for a list of available commands auto commit mode: on sql>create table test_table(col inet); operation successful (27.394ms) sql>insert into test_table values(*'127.0.0.1'*); 1 affected row (20.370ms) sql>insert into test_table values(*inet '127.0.0.1'*); 1 affected row (19.654ms) sql>insert into test_table values(*cast('127.0.0.1' as inet)*); 1 affected row (25.038ms) sql>select count(*) from test_table where col = '127.0.0.1'; +------+ | L1 | +======+ | *3* | +------+ 1 tuple (1.547ms) sql>select count(*) from test_table where col = inet '127.0.0.1'; +------+ | L1 | +======+ | *0* | +------+ 1 tuple (0.946ms) sql>select count(*) from test_table where col = cast('127.0.0.1' as inet); +------+ | L1 | +======+ | *2* | +------+ 1 tuple (0.982ms) sql>select count(*) from test_table where col = inet '127.0.0.1'; +------+ | L1 | +======+ | *2* | +------+ 1 tuple (0.937ms) sql>select count(*) from test_table where col = inet '127.0.0.1'; +------+ | L1 | +======+ | *0* | +------+ 1 tuple (0.801ms) Expected Result: All queries return a count of 3 Actual Result: Queries return different counts depending on how INET value was bound to query. Particularly interesting is that the output when using the inet '127.0.0.1' syntax is non-deterministic. Does this ring any bells? Should I log it? Cheers, -- Percy Wegmann +1 512 637 8500 ext 148 _______________________________________________ users-list mailing list users-list(a)monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list

1 1

Manually Loading all SQL Script
by swapnil joshi 04 Dec '12

04 Dec '12

Hi , My SQL Script is loaded only one's, When i am creating new database using following command I have copy and paste my terminal command swapnil@swapnil-lp:~$ sudo monetdb create test5 [sudo] password for swapnil: Sorry, try again. [sudo] password for swapnil: created database in maintenance mode: test5 swapnil@swapnil-lp:~$ sudo monetdb release test5 taken database out of maintenance mode: test5 swapnil@swapnil-lp:~$ sudo monetdb start test5 starting database 'test5'... done swapnil@swapnil-lp:~$ mclient test5 Welcome to mclient, the MonetDB/SQL interactive terminal (Jul2012-SP1) Database: MonetDB v11.11.7 (Jul2012-SP1), 'mapi:monetdb://swapnil-lp:50000/test5' Type \q to quit, \? for a list of available commands auto commit mode: on sql> After that if i am checking merovingian.log (Monetdb log file) 2012-12-04 17:57:20 MSG test2[11694]: # SQL catalog created, loading sql scripts once 2012-12-04 17:57:20 MSG test2[11694]: # loading sql script: 09_like.sql 2012-12-04 17:57:20 MSG test2[11694]: # loading sql script: 10_math.sql 2012-12-04 17:57:20 MSG test2[11694]: # loading sql script: 11_times.sql 2012-12-04 17:57:20 MSG test2[11694]: # loading sql script: 12_url.sql 2012-12-04 17:57:20 MSG test2[11694]: # loading sql script: 13_date.sql 2012-12-04 17:57:20 MSG test2[11694]: # loading sql script: 14_inet.sql 2012-12-04 17:57:20 MSG test2[11694]: # loading sql script: 15_history.sql 2012-12-04 17:57:20 MSG test2[11694]: # loading sql script: 16_tracelog.sql 2012-12-04 17:57:20 MSG test2[11694]: # loading sql script: 17_compress.sql 2012-12-04 17:57:20 MSG test2[11694]: # loading sql script: 18_dictionary.sql 2012-12-04 17:57:20 MSG test2[11694]: # loading sql script: 19_cluster.sql 2012-12-04 17:57:20 MSG test2[11694]: # loading sql script: 20_vacuum.sql 2012-12-04 17:57:20 MSG test2[11694]: # loading sql script: 21_dependency_functions.sql 2012-12-04 17:57:20 MSG test2[11694]: # loading sql script: 22_clients.sql 2012-12-04 17:57:20 MSG test2[11694]: # loading sql script: 23_skyserver.sql 2012-12-04 17:57:20 MSG test2[11694]: # loading sql script: 24_zorder.sql 2012-12-04 17:57:20 MSG test2[11694]: # loading sql script: 25_debug.sql 2012-12-04 17:57:20 MSG test2[11694]: # loading sql script: 40_geom.sql 2012-12-04 17:57:20 MSG test2[11694]: # loading sql script: 80_udf.sql 2012-12-04 17:57:20 MSG test2[11694]: # loading sql script: 99_system.sql How will i manually Lode this script because if i have change in any *.sql file each time I need to create new database How will i avoid this manual work? Please help me!!!! :) -- Regards, Swapnil K. Joshi _______________________________________________ users-list mailing list users-list(a)monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list

2 1

Can Monetdb drop Calculation of dropped column?
by swapnil joshi 04 Dec '12

04 Dec '12

Hi I have executed following SQL Statement : *sql>select product_id, sum(unit_sales),avg(unit_sales), max(unit_sales), min(unit_sales),sum(store_sales),avg(store_sales), max(store_sales), min(store_sales), sum(store_cost),avg(store_cost), max(store_cost), min(store_cost) from sales_fact_1997 group by product_id; It Gives following result: (In my table very large data there for i have given starting and ending rows of result set) +-----------+--------------+--------------------------+--------------+--------------+--------------+--------------------------+--------------+--------------+ | product_i | L1 | L2 | L3 | L4 | L5 | L6 | L7 | L10 |> : d : : : : : : : : :> +===========+==============+==========================+==============+==============+==============+==========================+==============+==============+ | 337 | 333644.7415 | 3.4849771407382648 | 6.9998 | 0.0001 | 1145678.6601 | 11.966812134157813 | 23.9986 | 0.0005 | | 1512 | 336475.1207 | 3.4816655356884185 | 6.9999 | 0.0001 | 1158967.1049 | 11.992375001552128 | 23.9995 | 0.0011 | | 963 | 333727.8589 | 3.4994427668141643 | 6.9997 | 0.0003 | 1139917.6212 | 11.953082033429096 | 23.9998 | 0.0008 | | 181 | 334323.6059 | 3.4942578847801946 | 6.9999 | 0.0003 | 1145948.0178 | 11.977131815046297 | 23.9980 | 0.0002 | | 1383 | 334298.1724 | 3.4923130291254028 | 6.9999 | 0.0002 | 1145319.9025 | 11.964814492708198 | 23.9992 | 0.0015 | | 1116 | 337661.6147 | 3.4923165958195015 | 6.9999 | 0.0003 | 1161142.4618 | 12.009292477789144 | 23.9996 | 0.0003 | | 488 | 336083.8006 | 3.5025147266948111 | 7.0000 | 0.0008 | 1149952.2337 | 11.984286735448883 | 23.9992 | 0.0004 | | 121 | 336465.6749 | 3.486221285215461 | 6.9999 | 0.0001 | 1154632.2251 | 11.963489116492081 | 23.9998 | 0.0001 | | 1338 | 337204.2797 | 3.478592072170585 | 7.0000 | 0.0001 | 1162678.8747 | 11.994170179601195 | 23.9996 | 0.0015 | | 910 | 338799.1540 | 3.4927387758889044 | 6.9999 | 0.0001 | 1166834.3104 | 12.029095683549643 | 23.9996 | 0.0001 | | 438 | 335488.9579 | 3.5050823580421069 | 6.9999 | 0.0002 | 1150602.4234 | 12.021129639032544 | 23.9982 | 0.0005 | | 1560 | 338661.5694 | 3.4991844580143199 | 6.9997 | 0.0001 | 1154747.3055 | 11.93130307492018 | 23.9994 | 0.0019 | +-----------+--------------+--------------------------+--------------+--------------+--------------+--------------------------+--------------+--------------+ 1560 tuples (3m 55s) !4 columns dropped! note: to disable dropping columns and/or truncating fields use \w-1 Hear, Monetdb gives only 9 column in result table and gives message 4 columns dropped as i show in following query it gives result of only yellow highlighted statement / columns result. Monetdb can not show blue highlighted part's result. **sql>select product_id, sum(unit_sales),avg(unit_sales), max(unit_sales), min(unit_sales),sum(store_sales),avg(store_sales), max(store_sales), min(store_sales), sum(store_cost),avg(store_cost), max(store_cost), min(store_cost) from sales_fact_1997 group by product_id; my question is that, Monetdb calculate result of all SQL statement or it does not calculate dropped column value. My Next Question How will I see this Dropped result? I hope you will give me proper guidance. My Monetdb version : MonetDB-11.11.07 * * * -- Regards, Swapnil K. Joshi _______________________________________________ users-list mailing list users-list(a)monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list

3 2