Problem with COPY INTO

Stefan Manegold Stefan.Manegold at cwi.nl
Fri Apr 15 00:28:16 CEST 2016


Hi,

one question I have is why you specify that your string delimiter is a single quote,
while your data does not seem to have any string delimiters?

As for the granting of premissions, you might want to file a bug report;
either the documentation of the implementation is wrong, or at least they do not match
or are misleading.
(well, I assume you you replaced "copy_role" by the actual role's name that you
want to grant the permission to, right?)

Best,
Stefan

----- On Apr 14, 2016, at 10:54 PM, Knezevic Nikola nikkne at gmx.ch wrote:

> 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
> _______________________________________________
> users-list mailing list
> users-list at monetdb.org
> https://www.monetdb.org/mailman/listinfo/users-list

-- 
| Stefan.Manegold at CWI.nl | DB Architectures   (DA) |
| www.CWI.nl/~manegold/  | Science Park 123 (L321) |
| +31 (0)20 592-4212     | 1098 XG Amsterdam  (NL) |


More information about the users-list mailing list