Problem with COPY INTO

Knezevic Nikola nikkne at gmx.ch
Thu Apr 14 22:54:25 CEST 2016


Hi all,

I've lost a significant time trying to get data from mysql database into monetdb. Since I have a lot of data, I wanted to make it as fast and automated as possible, but to no avail.

There are several issues I encountered, and I'd appreciate any help on any of them. I'm running the latest monetDB on CentOS:
% mclient --version
mclient, the MonetDB interactive terminal (Jul2015-SP4)
support for command-line editing compiled-in
character encoding: UTF-8

1. I tried following the last example on https://www.monetdb.org/Documentation/SQLreference/Permissions in order to get another user to be able to perform COPY INTO, but it fails:

% mclient -u monetdb -d some_db
password:
Welcome to mclient, the MonetDB/SQL interactive terminal (Jul2015-SP4)
Database: MonetDB v11.21.19 (Jul2015-SP4), 'mapi:monetdb://internal:50000/some_db'
Type \q to quit, \? for a list of available commands
auto commit mode: on
sql>GRANT COPY FROM TO copy_role;
syntax error, unexpected COPY in: "grant copy"

2. Ok, since I can't run COPY INTO FROM FILE, I decided to go with FROM STDIN version. To do so, I dump the mysql database and import that in monetdb. That also fails:

% mysql -N -B -u reader -pxxx some_db -h some_host -e "SELECT * FROM errortable;"|mclient -d some_db -s "COPY 15 RECORDS INTO errortable FROM STDIN USING DELIMITERS '\t','\n','\'';" -
Failed to import table Column value 1 missing
syntax error, unexpected sqlINT in: "100"

% mclient -u writer -d some_db
password:
Welcome to mclient, the MonetDB/SQL interactive terminal (Jul2015-SP4)
Database: MonetDB v11.21.19 (Jul2015-SP4), 'mapi:monetdb://internal:50000/some_db'
Type \q to quit, \? for a list of available commands
auto commit mode: on
sql>\d errortable
CREATE TABLE "some_db"."errortable" (
	"rejectreason" INTEGER       NOT NULL,
	"text"         CHARACTER LARGE OBJECT
);

Another try also fails (still using STDIN):

% mysql -N -B -u reader -pxxx some_db -h some_host -e "SELECT * FROM errortable;" >test.out
% mclient -d some_db -s "COPY 15 RECORDS INTO errortable FROM STDIN USING DELIMITERS '\t','\n','\'';"  <test.out
Failed to import table Column value 1 missing
% mclient -d some_db -s "COPY 15 RECORDS INTO errortable FROM STDIN USING DELIMITERS '\t','\n','\'';" - <test.out
Failed to import table Column value 1 missing
syntax error, unexpected sqlINT in: "100"

(note different error messages, and also note that 100 is a perfectly good sqlINT)

If I try the following, this succeeds. However, this is something I'm trying to avoid, as I'd had to create huge files (100+GB), which is an absolute waste of space and time.

% mysql -N -B -u reader -pxxx some_db -h some_host -e "SELECT * FROM errortable;" >test.out
% mclient -d some_db -s "COPY 15 RECORDS INTO errortable FROM '/home/nikola/test.out' USING DELIMITERS '\t','\n','\'';"
COPY INTO: insufficient privileges: COPY INTO from file(s) requires database administrator rights, use 'COPY INTO "errortable" FROM STDIN' instead
# obviously, fails due to (1)

% mclient -u monetdb -d some_db -s "COPY 15 RECORDS INTO some_db.errortable FROM '/home/nikola/test.out' USING DELIMITERS '\t','\n','\'';"
password:
15 affected rows

The file in question is super simple, a list of integers with textual description:
100	Some limit exceeded
101	Another limit exceeded
404	Service temporarily not available
405	Service not available
500	Server not available
700	End of world as we know it
701	User already logged in
10000	Book not found
10001	Price reasonability doubtfull
10002	Client is well known
20001	Activation in progress
20002	Pre-load limit exceeded
30000	Pre-load limit exceeded again
1	test
5	Invalid value 0 to divide with

Any hints on how to solve this super annoying problem?

Thanks,
Nikola


More information about the users-list mailing list