Why do I get this error when loading a script into mysql?

Question

I want to use this script:

-- De drop table if exists zorgt ervoor als tabellen die later in dit script
worden aangemaakt al bestaan, dat deze worden verwijderd.
drop table if exists Oligos;
drop table if exists Genen;

-- In de table Genen wordt de tabel Genen aangemaakt. Ook staat hier in welke
waardes worden geaccepteerd in elke kolom.
create table Genen
(gen_id      int     not null unique,
chromosoom  int     null,
sequentie   text     not null,
primary key(gen_id)
);
-- In de table Oligos wordt de tabel Oligos aangemaakt. Ook staat hier in welke
waardes worden geaccepteerd in elke kolom.
create Table Oligos
(oligo_id      int     auto_increment,
gen  int     null,
sequentie   text     not null,
startpositie    int     not null,
stoppositie     int     not null,
smeltpunt   float       not null,
valide_probe     boolean    null,
foreign key(gen) references Genen(gen_id),
primary key(oligo_id)
);

LOAD DATA INFILE 'test.csv'
INTO TABLE Genen FIELDS TERMINATED BY ';'
LINES TERMINATED BY '\n' (gen_id, chromosoom, sequentie);

Within test.csv I have 2 simple lines for testing purposes:

323;4;skdjksdjkk
123;4;jknfrkfn

This is my command:

$ mysql --local-infile=1 -u rnieuwenhuis -D Rnieuwenhuis -p < Plasmodium_falciparum.sql

and the error it returns is:

ERROR 1045 (28000) at line 25: Access denied for user 'rnieuwenhuis'@'%' (using password: YES)

I can't find out what causes this. Line 25 is LOAD DATA line. The mysql help page for acces denied leaves so much possibilities. But maybe you guys know what is most often the cause of this.

Looks like a normal access error. Your user does not have the required privileges. Since it's only in line 25 and not at start, login works but privileges arent enough. It's likely CREATE that is missing. — ToBe
– ToBe, Commented Oct 9, 2014 at 13:11

ToBe · Accepted Answer · 2014-10-09 14:31:20Z

1

Looks like a normal access error. Your user does not have the required privileges. Since it's only in line 25 and not at start, login works but privileges arent enough. It's likely CREATE that is missing.

Please check your users privileges.

1) Login via mysql -u rnieuwenhuis -D Rnieuwenhuis -p If this works, your username and pq is correct. But we are already certain of this.

2) Execute this query SHOW GRANTS 'rnieuwenhuis'@'localhost'; or even SHOW GRANTS FOR CURRENT_USER(); This should show you a list of privileges that you currently possess.

3) Make sure the granted privileges contain anything you need for your script. Especially CREATE.

4) If not, login as root (or another user with necessary rights) and grant the right to the user. GRANT CREATE ON *.* TO 'rnieuwenhuis'@'localhost'; This assumes that your DB is on the same user you are working on and that you want to grant the right on all databases and not only one. Change *.* as in the following link if you want to do something different.

Some interesting links:

Show Grants http://dev.mysql.com/doc/refman/5.7/en/show-grants.html
Available Privileges http://dev.mysql.com/doc/refman/5.7/en/privileges-provided.html
Grant Privileges to someone http://dev.mysql.com/doc/refman/5.7/en/grant.html

answered Oct 9, 2014 at 14:31

ToBe

2,6791 gold badge20 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

RonaldN Over a year ago

My useraccount already had the neccesary privileges, I could chack them with the provided query you gave me so thanks for that. The problem however has been solved by adding 'local' between LOAD DATA <> INFILE. It was a wild guess I did and it worked somehow..

Collectives™ on Stack Overflow

Why do I get this error when loading a script into mysql?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related