[MonetDB-users] ODBC

newer
[MonetDB-users] Persistent Stored...

older
[MonetDB-users] Copy command and...

Colin Foss

30 Mar 2007 30 Mar '07

10:29 p.m.

I have been trying to test ODBC with MonetDB with little success. JDBC and MapiClient are working. MS*Excel, R, and other utilities all fail while trying to initialize a connection via ODBC to MonetDB. Does anyone have a valid ODBC configuration to Monet?

Show replies by date

Sjoerd Mullender

2 Apr 2 Apr

8:27 a.m.

Colin Foss wrote:

...

I have been trying to test ODBC with MonetDB with little success. JDBC and MapiClient are working.

MS*Excel, R, and other utilities all fail while trying to initialize a connection via ODBC to MonetDB.

Does anyone have a valid ODBC configuration to Monet?

I am assuming you're trying this on Windows. I just tried MonetDB4-SQL-2.16.2.msi and MonetDB5-SQL-2.16.2.msi on a clean 32 bit Windows XP Professional, and I had no problems. Perhaps you can define your environment a bit more completely? A few things to check/keep in mind: The uninstall isn't robust. If the MonetDB ODBC driver is still somehow active during uninstall, some files remain in C:\windows\system32 which may then not be replaced on a subsequent install. Check whether the files libMapi.dll libMonetODBC.dll libMonetODBCs.dll libmutils.dll libstream.dll (in my installation, that's all files matching the pattern lib*.dll in C:\windows\system32) are identical to the files in your installation folder (typically C:\Program Files\CWI\MonetDB*\lib) are identical, and if not, copy the ones from the installation folder over the ones in C:\windows\system32. Windows programs tend to not follow standards. I have encountered many programs (among them Excel) that produce SQL queries that MonetDB refuses. Typically the error is General Error, i.e. useless. The problem is usually column names that are the same as SQL keywords and that are not surrounded by double quotes. You can produce debug output by setting an environment variable in the environment of the program using the ODBC driver (e.g. Excel). The variable name is ODBCDEBUG and the value should be the full path name of a file. This file will contain the debug output. Basically all calls to the driver will be logged there. -- Sjoerd Mullender

Colin Foss

3:55 p.m.

My configuration: Server is windows 64bit, MonetDB SQL 2.16.2 with ODBC 2.16.2.1. Client is windows 32bit, MonetDB SQL 2.16.2 with ODBC 2.16.2.1. I compared the contents of C:\program files\CWI\MonetDB4\lib with C:\windows\system32. All files with the same name match file size, version (where available), and timestamp. libmonet.dll and libbat.dll existed only in C:\program files\CWI\MonetDB4\lib so I copied them to the system32 directory and rebooted. The effect was the same.

...

From MSExcel 2003, the Microsoft query app reports "Drivers's SQLSetConnectAttr failed".

========= ODBC DEBUG ============== DllMain 1 SQLAllocHandle Env 0 new env 1163bc0 SQLSetEnvAttr 1163bc0 200 2 SQLAllocHandle Dbc 1163bc0 new dbc 1163be0 SQLGetInfoW 1163be0 77 SQLSetConnectAttrW 1163be0 115 addDbcError 1163be0 HY092 Invalid attribute/option identifier 0 SQLSetConnectOptionW 1163be0 103 2d addDbcError 1163be0 HYC00 Optional feature not implemented 0 SQLDriverConnectW 1163be0 "DSN=dev_test;" 1 SQLConnect: DSN=dev_test UID=monetdb PWD=monetdb host=localhost port=50000 database=(null) addDbcError 1163be0 08001 Client unable to establish connection 0 SQLGetDiagRecW Dbc 1163be0 1 SQLGetDiagRecW Dbc 1163be0 1 SQLGetDiagFieldW Dbc 1163be0 SQLGetDiagFieldW Dbc 1163be0 SQLFreeHandle Dbc 1163be0 SQLFreeHandle Env 1163bc0 DllMain 3 DllMain 0 ========= ODBC DEBUG ============== I noticed in the log that the ODBC driver is trying to incorrectly connect to localhost using the incorrect username and password. I had this problem last year but I thought it was fixed in 2.16.*. --- Sjoerd Mullender wrote:

...

...
I have been trying to test ODBC with MonetDB with little success. JDBC and MapiClient are working.

MS*Excel, R, and other utilities all fail while

Colin Foss wrote: trying

...
to initialize a connection via ODBC to MonetDB.

Does anyone have a valid ODBC configuration to Monet?

I am assuming you're trying this on Windows. I just tried MonetDB4-SQL-2.16.2.msi and MonetDB5-SQL-2.16.2.msi on a clean 32 bit Windows XP Professional, and I had no problems.

Perhaps you can define your environment a bit more completely?

A few things to check/keep in mind:

The uninstall isn't robust. If the MonetDB ODBC driver is still somehow active during uninstall, some files remain in C:\windows\system32 which may then not be replaced on a subsequent install. Check whether the files libMapi.dll libMonetODBC.dll libMonetODBCs.dll libmutils.dll libstream.dll (in my installation, that's all files matching the pattern lib*.dll in C:\windows\system32) are identical to the files in your installation folder (typically C:\Program Files\CWI\MonetDB*\lib) are identical, and if not, copy the ones from the installation folder over the ones in C:\windows\system32.

Windows programs tend to not follow standards. I have encountered many programs (among them Excel) that produce SQL queries that MonetDB refuses. Typically the error is General Error, i.e. useless. The problem is usually column names that are the same as SQL keywords and that are not surrounded by double quotes.

You can produce debug output by setting an environment variable in the environment of the program using the ODBC driver (e.g. Excel). The variable name is ODBCDEBUG and the value should be the full path name of a file. This file will contain the debug output. Basically all calls to the driver will be logged there.

-- Sjoerd Mullender

Sjoerd Mullender

3 Apr 3 Apr

8:11 a.m.

Colin Foss wrote:

...

My configuration:

Server is windows 64bit, MonetDB SQL 2.16.2 with ODBC 2.16.2.1. Client is windows 32bit, MonetDB SQL 2.16.2 with ODBC 2.16.2.1.

Are these different systems? I must admit, I haven't had time to test ODBC on 64bit Windows yet, and I don't have much time now either. One thing to watch out for when they are different systems is that the MonetDB server by default only accepts connections from localhost, i.e. the system itself. The error looks like this might be the problem. You can open up the server by editing the file ...\etc\MonetDB.conf (MonetDB4) or ...\etc\monetdb5.conf (MonetDB5) and changing the value of the variable mapi_open to true (in MonetDB5 you need to add it: mapi_open=true). And of course, the Windows firewall has to allow connections to the port (50000 by default).

...

I compared the contents of C:\program files\CWI\MonetDB4\lib with C:\windows\system32. All files with the same name match file size, version (where available), and timestamp. libmonet.dll and libbat.dll existed only in C:\program files\CWI\MonetDB4\lib so I copied them to the system32 directory and rebooted. The effect was the same.

...
From MSExcel 2003, the Microsoft query app reports "Drivers's SQLSetConnectAttr failed".

========= ODBC DEBUG ============== DllMain 1 SQLAllocHandle Env 0 new env 1163bc0 SQLSetEnvAttr 1163bc0 200 2 SQLAllocHandle Dbc 1163bc0 new dbc 1163be0 SQLGetInfoW 1163be0 77 SQLSetConnectAttrW 1163be0 115 addDbcError 1163be0 HY092 Invalid attribute/option identifier 0 SQLSetConnectOptionW 1163be0 103 2d addDbcError 1163be0 HYC00 Optional feature not implemented 0 SQLDriverConnectW 1163be0 "DSN=dev_test;" 1 SQLConnect: DSN=dev_test UID=monetdb PWD=monetdb host=localhost port=50000 database=(null) addDbcError 1163be0 08001 Client unable to establish connection 0 SQLGetDiagRecW Dbc 1163be0 1 SQLGetDiagRecW Dbc 1163be0 1 SQLGetDiagFieldW Dbc 1163be0 SQLGetDiagFieldW Dbc 1163be0 SQLFreeHandle Dbc 1163be0 SQLFreeHandle Env 1163bc0 DllMain 3 DllMain 0 ========= ODBC DEBUG ==============

I noticed in the log that the ODBC driver is trying to incorrectly connect to localhost using the incorrect username and password. I had this problem last year but I thought it was fixed in 2.16.*.

--- Sjoerd Mullender wrote:

...
...
I have been trying to test ODBC with MonetDB with little success. JDBC and MapiClient are working.

MS*Excel, R, and other utilities all fail while

Colin Foss wrote: trying

...
to initialize a connection via ODBC to MonetDB.

Does anyone have a valid ODBC configuration to Monet?

I am assuming you're trying this on Windows. I just tried MonetDB4-SQL-2.16.2.msi and MonetDB5-SQL-2.16.2.msi on a clean 32 bit Windows XP Professional, and I had no problems.

Perhaps you can define your environment a bit more completely?

A few things to check/keep in mind:

The uninstall isn't robust. If the MonetDB ODBC driver is still somehow active during uninstall, some files remain in C:\windows\system32 which may then not be replaced on a subsequent install. Check whether the files libMapi.dll libMonetODBC.dll libMonetODBCs.dll libmutils.dll libstream.dll (in my installation, that's all files matching the pattern lib*.dll in C:\windows\system32) are identical to the files in your installation folder (typically C:\Program Files\CWI\MonetDB*\lib) are identical, and if not, copy the ones from the installation folder over the ones in C:\windows\system32.

Windows programs tend to not follow standards. I have encountered many programs (among them Excel) that produce SQL queries that MonetDB refuses. Typically the error is General Error, i.e. useless. The problem is usually column names that are the same as SQL keywords and that are not surrounded by double quotes.

You can produce debug output by setting an environment variable in the environment of the program using the ODBC driver (e.g. Excel). The variable name is ODBCDEBUG and the value should be the full path name of a file. This file will contain the debug output. Basically all calls to the driver will be logged there.

-- Sjoerd Mullender

------------------------------------------------------------------------- Take Surveys. Earn Cash. Influence the Future of IT Join SourceForge.net's Techsay panel and you'll get the chance to share your opinions on IT & business topics through brief surveys-and earn cash http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

-- Sjoerd Mullender

Colin Foss

2:54 p.m.

Yes, these are two different systems. I am testing a normal database connection. i.e. database server on one host and database client on another. My startup script sets mapi_opne=true. jdbc and mapiclient connections are working just great in this configuration. I also have success with a windows client and a linux server, as well as a linux client and a windows server. The ODBC driver is not honoring the hostname, username, nor password configuration settings. --- Sjoerd Mullender wrote:

...

Colin Foss wrote:

...
My configuration:

Server is windows 64bit, MonetDB SQL 2.16.2 with ODBC 2.16.2.1. Client is windows 32bit, MonetDB SQL 2.16.2 with ODBC 2.16.2.1.

Are these different systems?

I must admit, I haven't had time to test ODBC on 64bit Windows yet, and I don't have much time now either.

One thing to watch out for when they are different systems is that the MonetDB server by default only accepts connections from localhost, i.e. the system itself. The error looks like this might be the problem.

You can open up the server by editing the file ...\etc\MonetDB.conf (MonetDB4) or ...\etc\monetdb5.conf (MonetDB5) and changing the value of the variable mapi_open to true (in MonetDB5 you need to add it: mapi_open=true). And of course, the Windows firewall has to allow connections to the port (50000 by default).

...
I compared the contents of C:\program files\CWI\MonetDB4\lib with C:\windows\system32. All files with the same name match file size, version (where available), and timestamp. libmonet.dll and libbat.dll existed only in C:\program files\CWI\MonetDB4\lib so I copied them to the system32 directory and rebooted. The effect was the same.

...
From MSExcel 2003, the Microsoft query app reports "Drivers's SQLSetConnectAttr failed".

========= ODBC DEBUG ============== DllMain 1 SQLAllocHandle Env 0 new env 1163bc0 SQLSetEnvAttr 1163bc0 200 2 SQLAllocHandle Dbc 1163bc0 new dbc 1163be0 SQLGetInfoW 1163be0 77 SQLSetConnectAttrW 1163be0 115 addDbcError 1163be0 HY092 Invalid attribute/option identifier 0 SQLSetConnectOptionW 1163be0 103 2d addDbcError 1163be0 HYC00 Optional feature not implemented 0 SQLDriverConnectW 1163be0 "DSN=dev_test;" 1 SQLConnect: DSN=dev_test UID=monetdb PWD=monetdb host=localhost port=50000 database=(null) addDbcError 1163be0 08001 Client unable to establish connection 0 SQLGetDiagRecW Dbc 1163be0 1 SQLGetDiagRecW Dbc 1163be0 1 SQLGetDiagFieldW Dbc 1163be0 SQLGetDiagFieldW Dbc 1163be0 SQLFreeHandle Dbc 1163be0 SQLFreeHandle Env 1163bc0 DllMain 3 DllMain 0 ========= ODBC DEBUG ==============

I noticed in the log that the ODBC driver is trying to incorrectly connect to localhost using the incorrect username and password. I had this problem last year but I thought it was fixed in 2.16.*.

--- Sjoerd Mullender wrote:

...
...
I have been trying to test ODBC with MonetDB with little success. JDBC and MapiClient are working.

MS*Excel, R, and other utilities all fail while

Colin Foss wrote: trying

...
to initialize a connection via ODBC to MonetDB.

Does anyone have a valid ODBC configuration to Monet?

I am assuming you're trying this on Windows. I just tried MonetDB4-SQL-2.16.2.msi and MonetDB5-SQL-2.16.2.msi on a clean 32 bit Windows XP Professional, and I had no problems.

Perhaps you can define your environment a bit more completely?

A few things to check/keep in mind:

The uninstall isn't robust. If the MonetDB ODBC driver is still somehow active during uninstall, some files remain in C:\windows\system32 which may then not be replaced on a subsequent install.

...
...
Check whether the files libMapi.dll libMonetODBC.dll libMonetODBCs.dll libmutils.dll libstream.dll (in my installation, that's all files matching the pattern lib*.dll in C:\windows\system32) are identical to the files in your installation folder (typically C:\Program Files\CWI\MonetDB*\lib) are identical, and if not, copy the ones from the installation folder over the ones in C:\windows\system32.

Windows programs tend to not follow standards. I have encountered many programs (among them Excel) that produce SQL queries that MonetDB refuses. Typically the error is General Error, i.e. useless. The problem is usually column names that are the same as SQL keywords and that are not surrounded by double quotes.

You can produce debug output by setting an environment variable in the environment of the program using the ODBC driver (e.g. Excel). The variable name is ODBCDEBUG and the value should be the full path name of a file. This file will contain the debug output.

...
...
Basically all calls to the driver will be logged there.

-- Sjoerd Mullender

-------------------------------------------------------------------------

...

...
Take Surveys. Earn Cash. Influence the Future of IT Join SourceForge.net's Techsay panel and you'll get the chance to share your opinions on IT & business topics through brief surveys-and earn cash

http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV

...

...
_______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net

https://lists.sourceforge.net/lists/listinfo/monetdb-users

...

-- Sjoerd Mullender

Sjoerd Mullender

3:34 p.m.

Colin Foss wrote:

...

Yes, these are two different systems. I am testing a normal database connection. i.e. database server on one host and database client on another. My startup script sets mapi_opne=true.

jdbc and mapiclient connections are working just great in this configuration. I also have success with a windows client and a linux server, as well as a linux client and a windows server.

The ODBC driver is not honoring the hostname, username, nor password configuration settings.

Looks like a bug. Thanks for submitting it to the bug tracker.

...

--- Sjoerd Mullender wrote:

...
Colin Foss wrote:

...
My configuration:

Server is windows 64bit, MonetDB SQL 2.16.2 with ODBC 2.16.2.1. Client is windows 32bit, MonetDB SQL 2.16.2 with ODBC 2.16.2.1. Are these different systems?

I must admit, I haven't had time to test ODBC on 64bit Windows yet, and I don't have much time now either.

One thing to watch out for when they are different systems is that the MonetDB server by default only accepts connections from localhost, i.e. the system itself. The error looks like this might be the problem.

You can open up the server by editing the file ...\etc\MonetDB.conf (MonetDB4) or ...\etc\monetdb5.conf (MonetDB5) and changing the value of the variable mapi_open to true (in MonetDB5 you need to add it: mapi_open=true). And of course, the Windows firewall has to allow connections to the port (50000 by default).

...
I compared the contents of C:\program files\CWI\MonetDB4\lib with C:\windows\system32. All files with the same name match file size, version (where available), and timestamp. libmonet.dll and libbat.dll existed only in C:\program files\CWI\MonetDB4\lib so I copied them to the system32 directory and rebooted. The effect was the same.

...
From MSExcel 2003, the Microsoft query app reports "Drivers's SQLSetConnectAttr failed".

========= ODBC DEBUG ============== DllMain 1 SQLAllocHandle Env 0 new env 1163bc0 SQLSetEnvAttr 1163bc0 200 2 SQLAllocHandle Dbc 1163bc0 new dbc 1163be0 SQLGetInfoW 1163be0 77 SQLSetConnectAttrW 1163be0 115 addDbcError 1163be0 HY092 Invalid attribute/option identifier 0 SQLSetConnectOptionW 1163be0 103 2d addDbcError 1163be0 HYC00 Optional feature not implemented 0 SQLDriverConnectW 1163be0 "DSN=dev_test;" 1 SQLConnect: DSN=dev_test UID=monetdb PWD=monetdb host=localhost port=50000 database=(null) addDbcError 1163be0 08001 Client unable to establish connection 0 SQLGetDiagRecW Dbc 1163be0 1 SQLGetDiagRecW Dbc 1163be0 1 SQLGetDiagFieldW Dbc 1163be0 SQLGetDiagFieldW Dbc 1163be0 SQLFreeHandle Dbc 1163be0 SQLFreeHandle Env 1163bc0 DllMain 3 DllMain 0 ========= ODBC DEBUG ==============

I noticed in the log that the ODBC driver is trying to incorrectly connect to localhost using the incorrect username and password. I had this problem last year but I thought it was fixed in 2.16.*.

--- Sjoerd Mullender wrote:

...
...
I have been trying to test ODBC with MonetDB with little success. JDBC and MapiClient are working. MS*Excel, R, and other utilities all fail while

Colin Foss wrote: trying

...
to initialize a connection via ODBC to MonetDB.

Does anyone have a valid ODBC configuration to Monet?

I am assuming you're trying this on Windows. I just tried MonetDB4-SQL-2.16.2.msi and MonetDB5-SQL-2.16.2.msi on a clean 32 bit Windows XP Professional, and I had no problems.

Perhaps you can define your environment a bit more completely?

A few things to check/keep in mind:

The uninstall isn't robust. If the MonetDB ODBC driver is still somehow active during uninstall, some files remain in C:\windows\system32 which may then not be replaced on a subsequent install. Check whether the files libMapi.dll libMonetODBC.dll libMonetODBCs.dll libmutils.dll libstream.dll (in my installation, that's all files matching the pattern lib*.dll in C:\windows\system32) are identical to the files in your installation folder (typically C:\Program Files\CWI\MonetDB*\lib) are identical, and if not, copy the ones from the installation folder over the ones in C:\windows\system32.

Windows programs tend to not follow standards. I have encountered many programs (among them Excel) that produce SQL queries that MonetDB refuses. Typically the error is General Error, i.e. useless. The problem is usually column names that are the same as SQL keywords and that are not surrounded by double quotes.

You can produce debug output by setting an environment variable in the environment of the program using the ODBC driver (e.g. Excel). The variable name is ODBCDEBUG and the value should be the full path name of a file. This file will contain the debug output. Basically all calls to the driver will be logged there.

-- Sjoerd Mullender

...
...
Take Surveys. Earn Cash. Influence the Future of IT Join SourceForge.net's Techsay panel and you'll get the chance to share your opinions on IT & business topics through brief surveys-and earn cash http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net

https://lists.sourceforge.net/lists/listinfo/monetdb-users

-- Sjoerd Mullender

------------------------------------------------------------------------- Take Surveys. Earn Cash. Influence the Future of IT Join SourceForge.net's Techsay panel and you'll get the chance to share your opinions on IT & business topics through brief surveys-and earn cash http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

-- Sjoerd Mullender

Colin Foss

12 Apr 12 Apr

3:28 p.m.

New subject: [MonetDB-users] stability, v4 vs v5

For the second time in a few weeks I've got a corrupted database. Each time the corruption occurs when trying to insert (via COPY into) data into a large table and the row count exceeds 100-110M. Each time the database terminates and a restart never allows another connection to the SQL interface. I've checked and no column is coming close to the 2G limit as well. The largest column is approximately 900M. My question is this: Would a switch to version five improve my results? Is version five more stable w.r.t. large (100M+) table handling? This stability issue is rather disappointing because MonetDB has done so well in all of my other testing. Query performance compared to other (commercial/non-commercial) products was outstanding for result sets of 5 - 750000 rows. Also, I wish there were better controls over maximum RAM usage.

Martin Kersten

4:59 p.m.

New subject: [MonetDB-users] stability, v4 vs v5

Dear Colin, Crash/recovery problems are really hard to analyse. Any hint on what is going on during recovery is highly appreciated. E.g. a stack trace and watching the instructions being executed indicate make all the differences. Also the result of 'top' may indicate the system behavior. I wouldn't be surprised if the cpu load has dropped to <1%, which indicate severe disk page management problems in the OS. Perhaps Niels should at least add some progress warnings when a large log file is recovered. It is a known fact that if you built a table from scratch, without any clue on the ultimate size, the system may become dreadfully slow. Roughly speaking, if a BAT is full, a new one is allocated in (virtual) memory using 1.2* oldsize; This quickly creates a disk bound setting with all the consequences (IO thrashing). In a SQL setting this is even worse, because the event is triggered on all columns at the same time. Nevertheless, it has our full attention. In particular, since we are currently scaling a 1Gb database to a 100Gb, directly followed by a jump to 2.7Tb. The stability in this area between M4/M5 is hard to tell, because we don't know at what level the problem occurs. For M5 we have, however, a program called the stethoscope (in the head branch), which can be attached to any running mserver to inspect what's happening. (We would also like to attach a MAL debugger the same way) RAM usage in M5 is better then M4, due to a different garbage collection scheme. A TPCH batch run on SF-5 (ca 5 Gb, all queries executed one after the other, system had only 2GB of memory) showed improvements between 2x up to 20x better response time on individual queries over M4. Colin Foss wrote:

...

For the second time in a few weeks I've got a corrupted database. Each time the corruption occurs when trying to insert (via COPY into) data into a large table and the row count exceeds 100-110M. Each time the database terminates and a restart never allows another connection to the SQL interface.

I've checked and no column is coming close to the 2G limit as well. The largest column is approximately 900M.

My question is this:

Would a switch to version five improve my results? Is version five more stable w.r.t. large (100M+) table handling?

This stability issue is rather disappointing because MonetDB has done so well in all of my other testing. Query performance compared to other (commercial/non-commercial) products was outstanding for result sets of 5 - 750000 rows.

Also, I wish there were better controls over maximum RAM usage.

------------------------------------------------------------------------- Take Surveys. Earn Cash. Influence the Future of IT Join SourceForge.net's Techsay panel and you'll get the chance to share your opinions on IT & business topics through brief surveys-and earn cash http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

Colin Foss

14 Apr 14 Apr

4:32 p.m.

New subject: [MonetDB-users] M5+SQL, COPY into error

This is strange. Using MonetDB4 this script ran without error, in MonetDB5 I get this error: QUERY = copy INTO my_big_table FROM 'E:/data/migrate/big_table_input-ab.tab' ERROR = !MALException:batmtime.EQ:Unexpected input type always on the second input file. Here is my script: ------------------------------------- set SCHEMA myschema; declare fname varchar(100); delete from my_big_table; -- this one is good set fname='big_table_input-aa.tab'; select fname as phase; COPY into my_big_table from 'E:/data/migrate/big_table_input-aa.tab' using delimiters '\t','\n'; -- this one will error set fname='big_table_input-ab.tab'; select fname as phase; COPY into my_big_table from 'E:/data/migrate/big_table_input-ab.tab' using delimiters '\t','\n'; select 'total amount' as phase; select count(*) from my_big_table; ------------------------------------- Yet, if I run the files one at a time, the will each load without error.

Niels Nes

7:22 p.m.

New subject: [MonetDB-users] M5+SQL, COPY into error

On Sat, Apr 14, 2007 at 09:32:24AM -0700, Colin Foss wrote:

...

This is strange. Using MonetDB4 this script ran without error, in MonetDB5 I get this error:

QUERY = copy INTO my_big_table FROM 'E:/data/migrate/big_table_input-ab.tab' ERROR = !MALException:batmtime.EQ:Unexpected input type

sounds like a bug. Which version of M5/sql are you using? The fact that it runs fine on M4 and in 2 runs indicates that it maybe related too one of the m5 optimizers. These can be controlled using the optimizer session variable. See http://monetdb.cwi.nl/projects/monetdb/SQL/Documentation/Optimizer-Control.h... for more information. Niels

...

always on the second input file.

Here is my script: ------------------------------------- set SCHEMA myschema; declare fname varchar(100); delete from my_big_table;

-- this one is good set fname='big_table_input-aa.tab'; select fname as phase; COPY into my_big_table from 'E:/data/migrate/big_table_input-aa.tab' using delimiters '\t','\n';

-- this one will error set fname='big_table_input-ab.tab'; select fname as phase; COPY into my_big_table from 'E:/data/migrate/big_table_input-ab.tab' using delimiters '\t','\n';

select 'total amount' as phase; select count(*) from my_big_table; -------------------------------------

Yet, if I run the files one at a time, the will each load without error.

------------------------------------------------------------------------- This SF.net email is sponsored by DB2 Express Download DB2 Express C - the FREE version of DB2 express and take control of your XML. No limits. Just data. Click to get it now. http://sourceforge.net/powerbar/db2/ _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

-- Niels Nes, Centre for Mathematics and Computer Science (CWI) Kruislaan 413, 1098 SJ Amsterdam, The Netherlands room C0.02, phone ++31 20 592-4098, fax ++31 20 592-4312 url: http://www.cwi.nl/~niels e-mail: Niels.Nes@cwi.nl

Colin Foss

16 Apr 16 Apr

4:04 p.m.

New subject: [MonetDB-users] M5+SQL, COPY into error

Niels, Perhaps I do not understand the documentation but I assumed you could run COPY into multiple times on a single table. I am able to run COPY INTO <table> once on an empty table. If I run COPY INTO <table> again on a non-empty table I receive the error (!MALException:batmtime.EQ:Unexpected input type). I can choose any single input file and it will load properly into an empty table. I did try combining all of my input files and running that way but after 36 hours I killed the load. Colin --- Niels Nes wrote:

...

On Sat, Apr 14, 2007 at 09:32:24AM -0700, Colin Foss wrote:

...
This is strange. Using MonetDB4 this script ran without error, in MonetDB5 I get this error:

QUERY = copy INTO my_big_table FROM 'E:/data/migrate/big_table_input-ab.tab' ERROR = !MALException:batmtime.EQ:Unexpected input type

sounds like a bug. Which version of M5/sql are you using? The fact that it runs fine on M4 and in 2 runs indicates that it maybe related too one of the m5 optimizers. These can be controlled using the optimizer session variable. See

http://monetdb.cwi.nl/projects/monetdb/SQL/Documentation/Optimizer-Control.h...

...

for more information.

Niels

...
always on the second input file.

Here is my script: ------------------------------------- set SCHEMA myschema; declare fname varchar(100); delete from my_big_table;

-- this one is good set fname='big_table_input-aa.tab'; select fname as phase; COPY into my_big_table from 'E:/data/migrate/big_table_input-aa.tab' using delimiters '\t','\n';

-- this one will error set fname='big_table_input-ab.tab'; select fname as phase; COPY into my_big_table from 'E:/data/migrate/big_table_input-ab.tab' using delimiters '\t','\n';

select 'total amount' as phase; select count(*) from my_big_table; -------------------------------------

Yet, if I run the files one at a time, the will

each

...
load without error.

-------------------------------------------------------------------------

...

...
This SF.net email is sponsored by DB2 Express Download DB2 Express C - the FREE version of DB2 express and take control of your XML. No limits. Just data. Click to get it now. http://sourceforge.net/powerbar/db2/ _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net

https://lists.sourceforge.net/lists/listinfo/monetdb-users

...

--

Niels Nes, Centre for Mathematics and Computer Science (CWI) Kruislaan 413, 1098 SJ Amsterdam, The Netherlands room C0.02, phone ++31 20 592-4098, fax ++31 20 592-4312 url: http://www.cwi.nl/~niels e-mail: Niels.Nes@cwi.nl

Colin Foss

4:26 p.m.

New subject: [MonetDB-users] M5+SQL, COPY into error

Niels, I also tried a work-around like the following: create table tmp_my_table like my_table; -- this works delete from tmp_my_table; copy into tmp_my_table from 'file1'; insert into my_table select * from tmp_my_table; -- this fails delete from tmp_my_table; copy into tmp_my_table from 'file2'; insert into my_table select * from tmp_my_table; with error: ERROR = !MALException:mkey.bulk_rotate_xor_hash:command failed !ERROR: CMDbulk_rotate_xor_hash: (tmp_20414,4,tmp_20377): not synced on head. --- Colin Foss wrote:

...

Niels,

Perhaps I do not understand the documentation but I assumed you could run COPY into multiple times on a single table.

I am able to run COPY INTO <table> once on an empty table. If I run COPY INTO <table> again on a non-empty table I receive the error (!MALException:batmtime.EQ:Unexpected input type). I can choose any single input file and it will load properly into an empty table.

I did try combining all of my input files and running that way but after 36 hours I killed the load.

Colin

--- Niels Nes wrote:

...
On Sat, Apr 14, 2007 at 09:32:24AM -0700, Colin Foss wrote:

...
This is strange. Using MonetDB4 this script ran without error, in MonetDB5 I get this error:

QUERY = copy INTO my_big_table FROM 'E:/data/migrate/big_table_input-ab.tab' ERROR = !MALException:batmtime.EQ:Unexpected input type

sounds like a bug. Which version of M5/sql are you using? The fact that it runs fine on M4 and in 2 runs indicates that it maybe related too one of the m5 optimizers. These can be controlled using the optimizer session variable. See

http://monetdb.cwi.nl/projects/monetdb/SQL/Documentation/Optimizer-Control.h...

...

...
for more information.

Niels

...
always on the second input file.

Here is my script: ------------------------------------- set SCHEMA myschema; declare fname varchar(100); delete from my_big_table;

-- this one is good set fname='big_table_input-aa.tab'; select fname as phase; COPY into my_big_table from 'E:/data/migrate/big_table_input-aa.tab' using delimiters '\t','\n';

-- this one will error set fname='big_table_input-ab.tab'; select fname as phase; COPY into my_big_table from 'E:/data/migrate/big_table_input-ab.tab' using delimiters '\t','\n';

select 'total amount' as phase; select count(*) from my_big_table; -------------------------------------

Yet, if I run the files one at a time, the will

each

...
load without error.

-------------------------------------------------------------------------

...

...
...
This SF.net email is sponsored by DB2 Express Download DB2 Express C - the FREE version of DB2 express and take control of your XML. No limits. Just data. Click to get it now. http://sourceforge.net/powerbar/db2/ _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net

https://lists.sourceforge.net/lists/listinfo/monetdb-users

...

...
--

Niels Nes, Centre for Mathematics and Computer Science (CWI) Kruislaan 413, 1098 SJ Amsterdam, The Netherlands room C0.02, phone ++31 20 592-4098, fax ++31 20 592-4312 url: http://www.cwi.nl/~niels e-mail: Niels.Nes@cwi.nl

-------------------------------------------------------------------------

...

This SF.net email is sponsored by DB2 Express Download DB2 Express C - the FREE version of DB2 express and take control of your XML. No limits. Just data. Click to get it now. http://sourceforge.net/powerbar/db2/ _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net

https://lists.sourceforge.net/lists/listinfo/monetdb-users

...

6465

Age (days ago)

6482

Last active (days ago)

List overview

Download

11 comments

4 participants

participants (4)

Colin Foss
Martin Kersten
Niels Nes
Sjoerd Mullender