CODEWITHSUNDEEP
mysqlcsv
RonaldN
RonaldN

Reputation: 127

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

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.

Upvotes: 1

Views: 47

Answers (1)

ToBe
ToBe

Reputation: 2681

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:

Upvotes: 1

Related Questions