- users-list - monetdb.org

[MonetDB-users] Repeatedly writing to MonetDB
by Jerry Ramey 29 Jul '12

29 Jul '12

We are encountering a frequent error when writing to MonetDb repeatedly using the Java MAPI socket. We are running the April 2012 release. The error is: [7/29/12 12:10:26 PM] Cory Isaacson: 000!TypeException:user.s0_2[2]:'calc.str' undefined in: _3:any := calc.str(_4:str) 22000!TypeException:user.s0_2[3]:'calc.str' undefined in: _5:any := calc.str(_6:str) 22000!TypeException:user.s0_2[4]:'calc.str' undefined in: _7:any := calc.str(_8:str) 22000!TypeException:user.s0_2[5]:'calc.str' undefined in: _9:any := calc.str(_10:str) 22000!TypeException:user.s0_2[6]:'calc.str' undefined in: _11:any := calc.str(_12:str) The only solution is to stop the farm and restart the farm/database. We have a single Java process writing to the database using MAPI, in batches of 1000 rows at a time (or less), using the COPY INTO syntax based on the example Java loader.We have a single Java process writing to the database using MAPI, in batches of 1000 rows at a time (or less), using the COPY INTO syntax based on the example Java loader. Here are the important snippets of the Java code. You can see that we are opening/closing a connection for each batch, and we have only a single thread performing this function, no other processes are writing to the database. We do have other connections reading from the database (but in the most recent case we only had idle connections). This is running on CentOS 5.4, with Java 1.6. There is no swap space configured on this server (we read that this may be a requirement). The data volumes are very small at this point, only a few million rows. String query = "COPY " + (dataRowList.size() + 10) + " RECORDS INTO " + tableName + " FROM STDIN USING DELIMITERS '\\t','\\n' NULL AS '\\\\N';"; if(TRACE) logger.trace("commit: query : " + query); // the leading 's' is essential, since it is a protocol // marker that should not be omitted, likewise the // trailing semicolon out.write('s'); out.write(query); out.newLine(); // Write all data rows. for(String dataRow : dataRowList) { if(TRACE) logger.trace("commit: Writing data row: " + dataRow + " dataRow ends in newline: " + dataRow.endsWith("\n")); // The dataRow is \n terminated, so no need to output newLine to output stream. out.write(dataRow); } out.writeLine(""); // need this one for synchronisation over flush() error = in.waitForPrompt(); if (error != null) throw new Exception(error); out.writeLine(""); // server wants more, we're going to tell it, this is it error = in.waitForPrompt(); if (error != null) throw new Exception(error); // // disconnect from server // server.close(); } catch (IOException e) { logger.error("Unable to connect", e); throw new RuntimeException("Unable to connect", e); } catch (Throwable th) { logger.error("Commit error", th); ... throw new RuntimeException("Commit error: tableName: " + tableName, th); } finally { try { if(server != null) { server.close(); server = null; } } catch (Throwable th) { logger.error("Error closing MonetDb server connection", th); } } Thanks, Jerry

1 0

[MonetDB-users] Repeatedly writing to MonetDB
by Jerry Ramey 29 Jul '12

29 Jul '12

We are encountering a frequent error when writing to MonetDb repeatedly using the Java MAPI socket. We are running the April 2012 release. The error is: [7/29/12 12:10:26 PM] Cory Isaacson: 000!TypeException:user.s0_2[2]:'calc.str' undefined in: _3:any := calc.str(_4:str) 22000!TypeException:user.s0_2[3]:'calc.str' undefined in: _5:any := calc.str(_6:str) 22000!TypeException:user.s0_2[4]:'calc.str' undefined in: _7:any := calc.str(_8:str) 22000!TypeException:user.s0_2[5]:'calc.str' undefined in: _9:any := calc.str(_10:str) 22000!TypeException:user.s0_2[6]:'calc.str' undefined in: _11:any := calc.str(_12:str) The only solution is to stop the farm and restart the farm/database. We have a single Java process writing to the database using MAPI, in batches of 1000 rows at a time (or less), using the COPY INTO syntax based on the example Java loader.We have a single Java process writing to the database using MAPI, in batches of 1000 rows at a time (or less), using the COPY INTO syntax based on the example Java loader. Here are the important snippets of the Java code. You can see that we are opening/closing a connection for each batch, and we have only a single thread performing this function, no other processes are writing to the database. We do have other connections reading from the database (but in the most recent case we only had idle connections). This is running on CentOS 5.4, with Java 1.6. There is no swap space configured on this server (we read that this may be a requirement). The data volumes are very small at this point, only a few million rows. String query = "COPY " + (dataRowList.size() + 10) + " RECORDS INTO " + tableName + " FROM STDIN USING DELIMITERS '\\t','\\n' NULL AS '\\\\N';"; if(TRACE) logger.trace("commit: query : " + query); // the leading 's' is essential, since it is a protocol // marker that should not be omitted, likewise the // trailing semicolon out.write('s'); out.write(query); out.newLine(); // Write all data rows. for(String dataRow : dataRowList) { if(TRACE) logger.trace("commit: Writing data row: " + dataRow + " dataRow ends in newline: " + dataRow.endsWith("\n")); // The dataRow is \n terminated, so no need to output newLine to output stream. out.write(dataRow); } out.writeLine(""); // need this one for synchronisation over flush() error = in.waitForPrompt(); if (error != null) throw new Exception(error); out.writeLine(""); // server wants more, we're going to tell it, this is it error = in.waitForPrompt(); if (error != null) throw new Exception(error); // // disconnect from server // server.close(); } catch (IOException e) { logger.error("Unable to connect", e); throw new RuntimeException("Unable to connect", e); } catch (Throwable th) { logger.error("Commit error", th); ... throw new RuntimeException("Commit error: tableName: " + tableName, th); } finally { try { if(server != null) { server.close(); server = null; } } catch (Throwable th) { logger.error("Error closing MonetDb server connection", th); } } Thanks, Jerry

1 0

[MonetDB-users] substing made monetdb very very slow
by li, xiaohua 27 Jul '12

27 Jul '12

Hi all: we are testing monetdb on a sql select substring(fact_bk,1,1) as aa,sum(1) as recordcount from f_activity group by aa the table f_activity have about 5 million rows. when i run this sql, monetdb need about 30 seconds . (if we use another no calculated column, it need only 1-3 seconds) when I run this sql on sql server, it needs only 7 seconds. is monetdb not good at calculated column? or we have made some mistake? please tell me the reason, thank you. lixiaohua Shanghai,China

2 4

[MonetDB-users] how to access remote server in windows
by li, xiaohua 26 Jul '12

26 Jul '12

Hi all: we want to copy a text file into a remote server, we don't know how to set the mapi_open = yes on windows. we try add --set "mapi_open= yes" in the M5server.bat . but we don't know whether it take effect. how can we know? when we use jdbc or odbc to access a remote monetdb database. we try to use jdbc:monetdb://dvs001:5000/demo. or change the odbc host to an ip address: 192.168.1.100. we can't connect to the database. only we use localhost can we connect to the database. does monetdb support remote access in windows? lixiaohua Shanghai,China

3 7

[MonetDB-users] can't copy into a remote server in windows
by li, xiaohua 26 Jul '12

26 Jul '12

Hi all: we want to copy a txt file into a remote monetdb (windows). if we copy into local monetdb via ipaddress, it's OK. but if we copy the same txt file into a remote monetdb, it can't. Error: could not open file 'd:\\share\\book1.csv': Operation not permitted SQLState: 22000 ErrorCode: 0 what's the problem? is it a bug? or something we don't know? lixiaohua Shanghai,China

3 3

[MonetDB-users] where is demo database physiquely in my SERVER
by DADOU 25 Jul '12

25 Jul '12

hello, i don't find in my PC the demo database, i had setup monetdb (MonetDB5-SQL-Installer-x86_64-20120607.msi) in my PC and in load the server with C:\Program Files\MonetDB\MonetDB5\M5server.bat I create user and connect with him in default database "demo", i created tables and loaded data. but i don't know where are data is stored in my machine, I want the path of database demo. thunks -- View this message in context: http://old.nabble.com/where-is-demo-database-physiquely-in-my-SERVER-tp3421… Sent from the monetdb-users mailing list archive at Nabble.com.

2 1

[MonetDB-users] COPY INTO: insufficient privileges: binary COPY INTO requires administrator rights
by 万萌 25 Jul '12

25 Jul '12

Hi guys, i got the following error when COPY INTO TABLE test: sql>COPY INTO test from ('/home/svom/log3'); COPY INTO: insufficient privileges: binary COPY INTO requires administrator rights the file "log3" is in mode 777, so i dont know why this happend, thanks for help! Wan meng SVOM, NAOC

5 6

[MonetDB-users] Billions of rows
by Benjie Chen 20 Jul '12

20 Jul '12

Hi >From one big dataset, we already have 0.7 billion rows. And we are looking at having 100 datasets eventually over the period of a year. That's 70 billion rows. From what I understand, column data are loaded into virtual address space, then dynamically indexed for querying? Does that mean that on a query "SELECT a FROM table WHERE b = N", it'd have to load all of 70 billion values of b, index, then provide results for a? Is this a case where statically created index would help? Does MonetDB use static created index? Thanks, Benjie

2 1

[MonetDB-users] CREATE AGGREGATE error
by Asterios Katsifodimos 18 Jul '12

18 Jul '12

Hello, I am trying to define an aggregate function as I have seen in an old message in this list: http://sourceforge.net/mailarchive/message.php?msg_id=20582949 I am trying to define it as: CREATE AGGREGATE group_concat (BASETYPE = text, SFUNC = _group_concat, STYPE = text); And I get this error: 42000!syntax error, unexpected '=' in: "create aggregate group_concat (basetype =" Any ideas on what I'm doing wrong? I have already defined the _group_concat function as described in the same message mentioned earlier. Running MonetDB v11.9.5 (Apr2012-SP1) on Linux. thank you, Asterios

3 4

[MonetDB-users] Announcement: New Jul2012 Feature release of MonetDB suite
by Fabian Groffen 17 Jul '12

17 Jul '12

The MonetDB team at CWI/MonetDB BV is pleased to announce the Jul2012 feature 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/>. Jul 2012 feature release Build Environment * Created seperate RPM and DEB packages for MonetDB/JAQL. * Removed --enable-bits option from configure. * Split the MonetDB-client-ruby RPM package into two and named them in accordance with the Fedora packaging guidelines as rubygem-<gem-name>. SQL * COPY INTO now accepts optional parenthesis for file argument. Binary COPY INTO now requires 'COPY BINARY INTO'. Client Package * ODBC: Fixed a bug where SQLNativeSql expected a statment handle instead of a connection handle. MonetDB5 Server * Crackers code has been removed. Development continues in the holindex branch. Merovingian * Fixed misc memory leaks, which caused monetdbd to grow in memory size over time. * Removed erroneously (re-)added master and slave properties, this functionality is currently not working. MonetDB Common * Fixed intermittent problem that joins and selects return incorrect results and possibly inconsistent databases. The problems only occurred after a series of queries and updates, therefore it was hard to reproduce. * The sorted property, which was used to maintain whether a column in a BAT was sorted or reverse sorted, has been replaced by a pair of properties, sorted and revsorted. These new properties can be set independently (unlike the old sorted property), and so if both are set, the column must be constant. In addition, internal property checking has been overhauled. Now, when a property is set incorrectly, and when assertions are enabled, an assertion will go off. There is also a function which can derive properties. * Implemented proper overflow checking on all arithmetic operations. Bug Fixes * 3016: Overflowing double multiplication leads to incorrect result * 3091: Since Apr2012-SP1 !TypeException:user.s11_1[59]:'bat.reverse' undefined in: _89:any := bat.reverse(_86:wrd) * 3099: ambiguous attributes in query with WITH clause and EXISTS clause * 3100: charset problem with the odbc driver for windows * 3103: display_size and internal_size of Cursor.description or incorrect * 3105: Leaking using Python * 3109: Wrong order in autoload mal script causes warning on startup * 3111: memory leak, monetdb set/get command * 3112: Median over temporal domains * 3113: Cannot set value of indexed column when doing UPDATE * 3114: Foreign Key prblems * 3116: Segfault in SQL

1 0

[MonetDB-users] how to use DataCell
by Greg Aumann 17 Jul '12

17 Jul '12

I would like to start experimenting with MonetDB/DataCell but am having trouble getting started. I read M. Kersten, E. Liarou, and R. Goncalves. A Query Language for a Data Refinery Cell. In EDA-PS, 2007. In section 2.1 of that paper it has the following syntax for creating a basket: create basket X( tag timestamp default now(), id serial, payload integer ); When I input this to mclient (Apr2012-SP2 version) I get: 42000!syntax error, unexpected IDENT, expecting INDEX in: "create basket" In the source code in the file sql/test/BugTracker-2011/Tests/UDF_in_schema.Bug-2817.sql begins with: create schema datacell; create table datacell.basket_X( id integer auto_increment, tag timestamp default now(), payload integer ); This obviously resembles the above but seems like it is creating a normal table rather than a basket. Has the syntax for creating a basket changed? Or should I load/enable the datacell extension in some way? thanks Greg

2 1

[MonetDB-users] Arrays or BATs as SQL column?
by Stefan de Konink 13 Jul '12

13 Jul '12

-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA512 Is there currently any possibility to store arrays or full BATs in a MonetDB5/SQL column which can be referenced later on? I'm looking for a support "database native" serialisation. Stefan -----BEGIN PGP SIGNATURE----- Version: GnuPG v2.0.18 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEAREKAAYFAlAAGyUACgkQYH1+F2Rqwn0mgwCdEBGeN+ULO0wH3nOG5SVhuna8 OqwAniKRa9qq6pr5pUnr90m+9MtlWbtH =XLkD -----END PGP SIGNATURE-----

2 3

[MonetDB-users] MonetDB connector for Matlab
by BOOM Bas 13 Jul '12

13 Jul '12

Hi, I developed a monetdb connector for matlab based on the mysql connector for matlab. If you have comments or suggestion how to improve it let me know. Cheers, Bas Below are the instruction how to compile and use the connector (hopes this is clear) Install Monetdb: ./configure --prefix=/usr/local/MonetDB --exec-prefix=/usr/local/MonetDB make make install Linux (not tested for Window yet): In matlab mex -I/usr/local/MonetDB/include/monetdb -L/usr/local/MonetDB/lib -lmapi monetdbv2.cpp monetdbv2(host,user,password,dbName,'OPEN'); monetdbv2(host,user,password,dbName,'INSERT INTO table VALUES (value1, 1)' ); [value1 value2] = monetdbv2(host,user,password,dbName,'SELECT * FROM table' ); monetdbv2(host,user,password,dbName,'CLOSE'); Simple matlab interface to MonetDB server Bastiaan Boom, University of Edinburgh, 13 July 2012 Based on the MySQL connector of Matlab (www.mathworks.com/matlabcentral/fileexchange/8663-mysql-database-connector) -- The University of Edinburgh is a charitable body, registered in Scotland, with registration number SC005336.

1 0

[MonetDB-users] Master-slave replication support
by Tapomay Dey 12 Jul '12

12 Jul '12

I found monetdb options in the docs to setup master and slave. However no documentation explaining what to do after setting the properties. I have an older monetdb setup with lot of data, set master=yes. I installed monetdb on another machine(nightly build says slave option not found, so using stable build for the new installation) set slave=mapi:monetdb://host:port/db I have no idea what to do next. I saw http://www.monetdb.org/Documentation/Cookbooks/Recipes/Cluster, but that's not what I want. I am guessing its a work in progress. If it is so, are you planning to release it in July? Regards, Tapomay.

3 3

Re: [MonetDB-users] MonetDB Python client
by Alexey Mints 12 Jul '12

12 Jul '12

Dear Fabian! The problem appears with "classical" mapi2.py when server drops the connection without closing it correctly. This happens with some incorrect SQLs (sometimes error diagnostics simply fails). recv(bytes) hangs in this case. I have faced this problem before with sockets, and the only solution I know so far is select() method. I raise socket.error only in the case of such hard connection failure. So it does not seem too harsh. At least it is better than thread-blocking. Regards, Alexey Mints

2 1

[MonetDB-users] Scale
by Benjie Chen 11 Jul '12

11 Jul '12

Hi We are currently setup to use MonetDB to capture data from our mass spec machines. We generate about 30GB of data per day, so looking at a few TB of data per year. We are curious to hear what others who have used MonetDB, handle storage scaling. What kind of hardware do you use? Is there distributed/federated support in MonetDB? Are there map/reduce techniques you've used over the data, etc. Thanks, Benjie

1 0

[MonetDB-users] mserver crash under load -16G data, 10 queries each from 2 remote clients
by Tapomay Dey 10 Jul '12

10 Jul '12

I have a database with Single table. 15 million rows. 16G of data. 58 columns. MonetDB stores it consuming 32G of data. mserver Machine specs: Main memory: 4G Processor: Intel(R) Xeon(R) CPU E5645 @ 2.40GHz. Single core. I am performing a test to see how much time it takes if I fire this query multiple times from different machines. Case 1: I fire an aggregation query 10 times from a remote machine with 5sec interval between each firing. The result hs 17000 rows with around 4 MB of data. Memory usage of mserver process reahes a peak of 73% mclients finish within 43 to 48 seconds. Case 2: I do the same as above but now from 2 remote machines simultaneously. Memory usage of mserver process reahes a peak of 73% again. It keeps fluctuating between 70 and 73%. But after some time mserver restarts and the clients terminate with all the 10 clients on each of both machines saying: ERROR = !Connection terminated MAPI = tapomay@54.251.11.181:3306 ACTION= read_line QUERY = select id1, id2, id3, id4, id5, count(*), sum(metric1), avg(metric2), sum(metric3), sum(metric4), sum(metric5), sum(metric6), sum(metric7), sum(metric8), sum(metric9), sum(metric9), sum(metric10), sum(metric11), sum(metric12), sum(metric13) from tablename group by id1, id2, id3, id4, id5; Each query terminates within 5 to 50 seconds with the above error. In both cases intial state of mserver consumes around 0.5% memory. Also tested the same under other competing columnar DBs. They complete the case 2 in around 350 or more seconds average. They complete case 1 in 200 seconds average. Following is a last few lines of tail of merovingian.log 2012-07-05 06:30:42 MSG merovingian[31153]: proxying client 1.compute.amazonaws.com:45476 for database 'tablename' to mapi:monetdb:/$ 2012-07-05 06:30:42 MSG merovingian[31153]: target connection is on local UNIX domain socket, passing on filedescriptor instead of proxying 2012-07-05 06:30:45 MSG merovingian[31153]: proxying client 2.amazonaws.com:46225 for database 'tablename' to mapi:monetdb:///mnt/tapomay$ 2012-07-05 06:30:45 MSG merovingian[31153]: target connection is on local UNIX domain socket, passing on filedescriptor instead of proxying 2012-07-05 06:30:47 MSG merovingian[31153]: proxying client 1.compute.amazonaws.com:45477 for database 'tablename' to mapi:monetdb:/$ 2012-07-05 06:30:47 MSG merovingian[31153]: target connection is on local UNIX domain socket, passing on filedescriptor instead of proxying 2012-07-05 06:30:50 MSG merovingian[31153]: proxying client 2.amazonaws.com:46226 for database 'tablename' to mapi:monetdb:///mnt/tapomay$ 2012-07-05 06:30:50 MSG merovingian[31153]: target connection is on local UNIX domain socket, passing on filedescriptor instead of proxying 2012-07-05 06:30:52 MSG merovingian[31153]: proxying client 1.compute.amazonaws.com:45478 for database 'tablename' to mapi:monetdb:/$ 2012-07-05 06:30:52 MSG merovingian[31153]: target connection is on local UNIX domain socket, passing on filedescriptor instead of proxying 2012-07-05 06:30:55 MSG merovingian[31153]: proxying client 2.amazonaws.com:46241 for database 'tablename' to mapi:monetdb:///mnt/tapomay$ 2012-07-05 06:30:55 MSG merovingian[31153]: target connection is on local UNIX domain socket, passing on filedescriptor instead of proxying 2012-07-05 06:30:59 MSG merovingian[31153]: database 'tablename' (1048) was killed by signal SIGKILL Is there a config that would ask mserver to not kill itself and keep processing at the cost of query execution time. A few more tens of seconds won't harm much. Thanks and Regards, Tapomay.

4 10

Re: [MonetDB-users] MonetDB Python client
by Alexey Mints 09 Jul '12

09 Jul '12

Hi, Fabian! Yeh, I have shifted try outside of loop. I am not a big expert in this, but does it make any difference? Alexey.

2 1

[MonetDB-users] Expected query to be much faster, but is actually two orders of magnitude slower!
by Shahbaz 05 Jul '12

05 Jul '12

Hi All, I did some experiments with monetdb over the weekend and found some strange and unexpected behavior. My database has a single table which stores stock trades. The table is defined as follows: create table trades( td varchar(2), // just a dummy field date int, time int, ticker varchar(17), price double, qty int, vol int, conditioncode int, exch varchar(8)); This table has a little under 1 Billion rows and takes up around 52 gigabytes. The source data is also around 48 gigabytes. I expected monetdb to aggressively compress the columns and reduce the disk usage by 10 fold or so! In any case, that is not the main issue. [select sum(date), sum(time), count(ticker) from trades where date = 20120501;] This query took 1.1 seconds the first time and around 173 ms the second two times (count(ticker) returned 31,268,285). [select sum(date), sum(time), count(ticker) from trades where date = 20120501 and time > 34200000;] This query took about 250 ms the first, second and third times I ran it. count(ticker) returned 31,176,190. (I doing the same thing as previous query, but asking to ignore all trades before the market opened...obviously very very few trades occur before the market opens). [select sum(date), sum(time), count(ticker) from trades where date = 20120501 and time > 34200000 and time < 37800000;] This query took 1 minute and 15 seconds! The second time I ran it, it took 1 minute and 11 seconds. Notice that count(ticker) in this query only returned 5,969,337. This query is essentially the same as the previous one, but now I am only looking to process trades between 9:30 am and 10:30 am. So this query processes 1 hours' worth of trades while previous queries process the whole day. Notice that all three queries process the same columns. Between the second and third columns, all the columns used in the select clause and where clause are exactly the same. The second query just add an additional filter on the time column. Why is the third query two orders of magnitude slower than the second query? This experiment was done on an ubuntu server: >uname -a Linux abcdefg 3.2.0-25-generic #40-Ubuntu SMP Wed May 23 20:30:51 UTC 2012 x86_64 x86_64 x86_64 GNU/Linux >cat /proc/meminfo |fgrep Mem MemTotal: 8168280 kB MemFree: 3588848 kB >hdparm -t ...disk... Timing buffered disk reads: 510 MB in 3.00 seconds = 169.80 MB/sec Please let me know if you require more information. Thanks, Shahbaz

4 3

[MonetDB-users] MonetDB Python client
by Alexey Mints 05 Jul '12

05 Jul '12

Hello! I had a problem with Python client hanging up if MonetDB connection was closed unexpectedly. This happened for example, when I have passed a request that caused "ERROR = !Connection terminated" in mclient. Such things should not occur in a ideal life, but they do =( The problem was that socket.recv cannot handle this situation properly. The solution is to use select.select statement. I attach the diff to mapi2.py file that solves the problem. Regards, Alexey

2 1

[MonetDB-users] importing data from csv
by Flavio Coelho 30 Jun '12

30 Jun '12

I am trying to import data from a CSV file separated by ';' and with string values surrounded by double quotes: " but I keep getting the following error messages: missing separator ';' line 225387 expecting 8 got 7 fields missing separator ';' line 75129 expecting 8 got 7 fields missing separator ';' line 0 expecting 8 got 7 fields missing separator ';' line 450774 expecting 8 got 7 fields missing separator ';' line 300516 expecting 8 got 7 fields missing separator ';' line 150258 expecting 8 got 7 fields missing separator ';' line 525903 expecting 8 got 7 fields missing separator ';' line 375645 expecting 8 got 7 fields failed to import table I am using the following command: copy 300000000 offset 2 records into mobilidade.ligacoes from '/Users/fccoelho/Documents/dados_mobilidade/bilhetagem.txt' using delimiters ';','\n','"' ; after creating the table like this: sql dump of my table: CREATE TABLE "mobilidade"."ligacoes" ( "id" INTEGER NOT NULL DEFAULT next value for "mobilidade"."seq_5840", "DT_INI_ATIVIDADE_REDE" VARCHAR(10), "HR_INI_ATIVIDADE_REDE" VARCHAR(7), "NU_SETOR_CELULA_ORIG" INTEGER, "NU_AREA_CELULA_ORIG" INTEGER, "NU_DDD_ORIG" INTEGER, "NU_DDD_DESTINO" INTEGER, "CD_TELEFONE_ORIG_ALTER" INTEGER DEFAULT NULL, "CD_TELEFONE_DESTINO_ALTER" VARCHAR(16) DEFAULT NULL, CONSTRAINT "ligacoes_id_pkey" PRIMARY KEY ("id") ); Naturally, I checked line 0 and it does have 8 fields. What am I doing wrong? -- Flávio Codeço Coelho

2 2

[MonetDB-users] COPY INTO
by DADOU 29 Jun '12

29 Jun '12

COPY INTO test from '/tmp/bla'; COPY INTO: insufficient privileges: COPY INTO from file(s) requires administrator rights, use 'COPY INTO "test" FROM STDIN' instead can you help me -- View this message in context: http://old.nabble.com/COPY-INTO-tp34088907p34088907.html Sent from the monetdb-users mailing list archive at Nabble.com.

3 2

[MonetDB-users] COPY INTO
by DADOU 29 Jun '12

29 Jun '12

I have a Problem with copy into copy into "LINEORDER" from ('E:\TEST.tbl'); COPY INTO: insufficient privileges: binary COPY INTO requires administrator rights TEST.tbl is a file with 6 000 000 rows. these a format for two rows in file TEST.tbl 1|1|7381|155190|4137|19960102|TRUCK| 1|2|7381|67310|815|19960102|MAIL| can help me please -- View this message in context: http://old.nabble.com/COPY-INTO-tp34088847p34088847.html Sent from the monetdb-users mailing list archive at Nabble.com.

2 1

[MonetDB-users] Basic sum aggregation returning incorrect value
by Tapomay Dey 28 Jun '12

28 Jun '12

I performed the following operations sequentially. Please look at the result when I use SUM. Sum values are incorrect. They should be 60/600/6000/60000.AVG returns correct values though. What could be going wrong? Is it something I can fix? sql> sql>create table table1 (id int, value int); operation successful (64.427ms) sql>create table table2 (id int, value int); operation successful (78.679ms) sql>insert into table1 values(1, 10); 1 affected row (25.389ms) sql>insert into table1 values(1, 20); 1 affected row (28.254ms) sql>insert into table1 values(1, 30); 1 affected row (27.512ms) sql>insert into table1 values(2, 300); 1 affected row (28.255ms) sql>insert into table1 values(2, 200); 1 affected row (28.658ms) sql>insert into table1 values(2, 100); 1 affected row (29.606ms) sql>insert into table2 values(1, 1000); 1 affected row (25.144ms) sql>insert into table2 values(1, 2000); 1 affected row (26.130ms) sql>insert into table2 values(1, 3000); 1 affected row (27.490ms) sql>insert into table2 values(2, 30000); 1 affected row (18.623ms) sql>insert into table2 values(2, 20000); 1 affected row (40.441ms) sql>insert into table2 values(2, 20000); 1 affected row (29.716ms) sql>insert into table2 values(2, 10000); 1 affected row (25.907ms) sql>select t1.id, sum(t1.value) as v1, sum(t2.value) as v2 from table1 t1, table2 t2 where t1.id=t2.id group by t1.id; +------+------+--------+ | id | v1 | v2 | +======+======+========+ | 1 | 180 | 18000 | | 2 | 2400 | 240000 | +------+------+--------+ 2 tuples (1.769ms) sql>select t1.id, avg(t1.value) as v1, avg(t2.value) as v2 from table1 t1, table2 t2 where t1.id=t2.id group by t1.id; +------+--------------------------+--------------------------+ | id | v1 | v2 | +======+==========================+==========================+ | 1 | 20 | 2000 | | 2 | 200 | 20000 | +------+--------------------------+--------------------------+ 2 tuples (2.275ms) sql> Regards, Tapomay.

2 1

[MonetDB-users] last inserted id
by Tomas Mikula 26 Jun '12

26 Jun '12

Hi, how can I get the ID (value of an AUTO_INCREMENT column) of the last inserted row, from within an SQL script? I am able to get it through the Python API, so I suppose there is a way to get it inside an SQL script, too. Thanks, Tomas

2 1