Problem with COPY INTO

Knezevic Nikola nikkne at gmx.ch
Fri Apr 15 17:49:09 CEST 2016


Hi Niels,

thanks for the pointer. I'm pretty sure I installed the latest thing from yum, so I went to repeat the process. Also, is Jun2016 some next release? (as we're still in April). If so, it would be great if documentation would mention which version it applies to (it now says "In the feature release following Jul2015", which for me is Jul2015-SP4).

BTW, if I follow the steps from:
http://dev.monetdb.org/downloads/epel/
it fails after
% yum install http://dev.monetdb.org/downloads/epel/MonetDB-release-epel-1.1-1.monetdb.noarch.rpm
% yum update
http://dev.monetdb.org/downloads/epel/7.2/x86_64/repodata/repomd.xml: [Errno 14] HTTP Error 404 - Not Found

because monetdb.repo has $releasever , which is 7.2 on my system, and server only exposes /7/.

Best,
Nikola


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


More information about the users-list mailing list