Problem with COPY INTO

Niels Nes Niels.Nes at cwi.nl
Fri Apr 15 17:19:08 CEST 2016


On Fri, Apr 15, 2016 at 05:10:32PM +0200, Knezevic Nikola wrote:
> Hi Martin,
> 
> I had it created, but this still fails
This feature (granting copy from/to) is only available in the Jun2016 version.

Niels
> 
> mclient -d some_db -u monetdb
> 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>CREATE ROLE copy_role;
> CREATE ROLE: role 'copy_role' already exists
> sql>GRANT COPY FROM TO copy_role;
> syntax error, unexpected COPY in: "grant copy"
> sql>drop role copy_role;
> operation successful (2.564ms)
> sql>CREATE ROLE copy_role;
> operation successful (3.659ms)
> sql>GRANT COPY FROM TO copy_role;
> syntax error, unexpected COPY in: "grant copy"
> 
> Cheers,
> Nikola
> 
> 
> > On 14/04/16 22:54, Knezevic Nikola 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"
> > 
> > You forgot to first create the role.
> > 
> > CREATE ROLE copy_role;
> > operation successful (34.549ms)
> > GRANT COPY FROM TO copy_role;
> > operation successful (15.332ms)
> > CREATE SCHEMA copy_schema AUTHORIZATION copy_role;
> > operation successful (15.921ms)
> > CREATE USER user1 WITH PASSWORD 'user1' NAME 'copy user' SCHEMA copy_schema;
> > operation successful (233.594ms)
> > CREATE USER user2 WITH PASSWORD 'user2' NAME 'copy user' SCHEMA copy_schema;
> > operation successful (207.331ms)
> > GRANT copy_role TO user1;
> > operation successful (8.123ms)
> > GRANT copy_role TO user2;
> > operation successful (8.272ms)
> > 
> > >
> > > 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
> > 
> > You mix here different notions for STDIN
> > The single statement is not a prelude for reading from 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?
> > There are about 100 examples of copy into in the SQL test directory
> > for further inspiration.
> > 
> > >
> > > Thanks,
> > > Nikola
> > > _______________________________________________
> > > users-list mailing list
> > > users-list at monetdb.org
> > > https://www.monetdb.org/mailman/listinfo/users-list
> > >
> > 
> > _______________________________________________
> > users-list mailing list
> > users-list at monetdb.org
> > https://www.monetdb.org/mailman/listinfo/users-list
> > 
> _______________________________________________
> users-list mailing list
> users-list at monetdb.org
> https://www.monetdb.org/mailman/listinfo/users-list

-- 
Niels Nes, Manager ITF, Centrum Wiskunde & Informatica (CWI)
Science Park 123, 1098 XG Amsterdam, The Netherlands
room L3.14,  phone ++31 20 592-4098 	sip:4098 at sip.cwi.nl
url: https://www.cwi.nl/people/niels	e-mail: Niels.Nes at cwi.nl


More information about the users-list mailing list