LOAD DATA INFILE + MySQL error 28000

Question

I'm having problems with the following SQL Query I want to execute:

LOAD DATA INFILE 'thelocationofmyfile.csv'
INTO TABLE test_import 
FIELDS TERMINATED BY ';'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES ( ArtID, ArtNamePharmLang, ArtNameFr, ArtNameNl, PubPrice, PercentageRebate, RebateAmount, SellingPrice, Localisation, CnkNr, EanNr, SoldQty, MinThd, MaxThd, QtyInStock, DateLastSale, VatRate, SupplierManufName, BuyPrice, InvCatCode, ArtType, ApbCatCode, ApbLegCode, PharmApbNr );

I want to load the data of an excel file into a table in my database. When I run this locally everything works. But when I do this on the server I get the following error:

Uncaught exception 'PDOException' with message 'SQLSTATE[28000]: Invalid authorization specification: 1045 Access denied for user 'myuser'@'localhost' (using password: YES)'

I'm trying to do this in PHP (in Zend Framework). When I contacted the hosting they said I needed the FILE permission to do this. But this is bad practice and not adviced.

I also tried to do this in a shell script like this:

#!/bin/bash
/usr/bin/mysql --host=localhost --user=theuser --password=password --database=db_database<<EOFMYSQL
LOAD DATA INFILE 'locationofmyfile.csv'
INTO TABLE test_import 
FIELDS TERMINATED BY ';'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES ( ArtID, ArtNamePharmLang, ArtNameFr, ArtNameNl, PubPrice, PercentageRebate, RebateAmount, SellingPrice, Localisation, CnkNr, EanNr, SoldQty, MinThd, MaxThd, QtyInStock, DateLastSale, VatRate, SupplierManufName, BuyPrice, InvCatCode, ArtType, ApbCatCode, ApbLegCode, PharmApbNr );
EOFMYSQL

But I got the same error:

ERROR 1045 (28000) at line 1: Access denied for user 'user'@'localhost' (using password: YES)

UPDATE:

I've tried to add LOCAL like this:

LOAD DATA LOCAL INFILE 'thelocationofmyfile.csv'

But then I get this error:

ERROR 1148 (42000) at line 1: The used command is not allowed with this MySQL version

Also tried to add --local-infile=1 like this but got same error

/usr/local/bin/mysql --host=127.0.0.1 --user=theuser --local-infile=1 --password=password --database=db_database

SECOND UPDATE:

My config file my.cnf looks like this:

[mysqld]
local-infile=0

max_connections         = 50
connect_timeout         = 5
wait_timeout            = 300
max_allowed_packet      = 16M
thread_cache_size       = 128
sort_buffer_size        = 4M
bulk_insert_buffer_size = 16M
tmp_table_size          = 16M
max_heap_table_size     = 16M
key_buffer_size         = 32M
open-files-limit        = 2000
table_cache             = 400
myisam_sort_buffer_size = 8M
concurrent_insert       = 2
read_buffer_size        = 2M
read_rnd_buffer_size    = 1M
query_cache_limit       = 1M
query_cache_size        = 32M
innodb_log_file_size    = 48M
max_allowed_packet  = 32M

The location where my connection is established doesn't really matter because I'm testing it with a shell script where I make the connection.

I don't get an error when I run

/usr/bin/mysql --host=localhost --user=theuser --password=password --database=db_database<<EOFMYSQL
show tables;
EOFMYSQL

(just a list of all the tables in my database)

When I run SHOW GRANTS; I get :

+------------------------------------------------------------------------------------------------------------------------+
| Grants for theuser@localhost                                                                                           |
+------------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'theuser'@'localhost' IDENTIFIED BY PASSWORD '*password' |
| GRANT ALL PRIVILEGES ON `mydomain\_live`.* TO 'theuser'@'localhost'                                                    |
| GRANT ALL PRIVILEGES ON `mydomain\_staging`.* TO 'theuser'@'localhost' WITH GRANT OPTION                               |
+------------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.01 sec)

Answer 1

You can try LOAD DATA LOCAL INFILE and use a file stored in the client's disk. This way you don't need to have FILE permission. It will be slower since the file has to be uploaded to server.

Hope this help

Answer 2

To begin with, please, show us your config file (my.cnf) and verison of the database. Also, code output where connection is estableshed would be helpful. It should be something like:

/*** mysql hostname ***/
$hostname = 'localhost';
/*** mysql username ***/
$username = 'user';
/*** mysql password ***/
$password = 'pass';

function testdb_connect ($hostname, $username, $password){
    $dbh = new PDO("mysql:host=$hostname;dbname=database", $username, $password);
    return $dbh;
}

try {
    $dbh = testdb_connect ($hostname, $username, $password);
    echo 'Connected to database';
} catch(PDOException $e) {
    echo $e->getMessage();
}

As for the errors:

ERROR 1045 (28000) at line 1: Access denied for user 'user'@'localhost'
and
ERROR 1148 (42000) at line 1: The used command is not allowed with this MySQL version

Do you get an error if you execute:

/usr/bin/mysql --host=localhost --user=theuser --password=password --database=db_database<<EOFMYSQL
show tables;
EOFMYSQL

One thing you might check is (which requires you to login to the MySQL console) - check to make sure that you have permissions to login to MySQL via localhost:

mysql -h 127.0.0.1 -u user -p

mysql> select user,host from mysql.user;
+------+--------------------------------+
| user | host                           |
+------+--------------------------------+
| user | 127.0.0.1                      | 
| user | ::1                            |
| user | localhost                      | <-- Make sure you have a localhost entry for root
+------+--------------------------------+
3 rows in set (0.00 sec)

Also, you may check GRANTS for your user. To list the privileges granted to the account that you are using to connect to the server, you can use any of the following statements:

SHOW GRANTS;
SHOW GRANTS FOR CURRENT_USER;
SHOW GRANTS FOR CURRENT_USER();

Just to be sure that there is the issue with privileges, you mat try the following:

sudo service mysql stop
sudo mysqld --skip-grant-tables

And execute LOAD command then.

UPDATE

As for your config file, for security reasons you have:

[mysqld]
local-infile=0

If LOAD DATA LOCAL is disabled, either in the server or the client, a client that attempts to issue such a statement receives the following error message:

ERROR 1148: The used command is not allowed with this MySQL version

What you can it is set all users except 'admin' and 'root' (for instance) to not have that privilege at the mysql level, then login to MySQL and run the query:

UPDATE mysql.user SET File_priv='N' WHERE user!='da_admin' AND user!='root';
FLUSH PRIVILEGES;

For the command line try to use only "--local-infile" insted of "--local-infile=1" and let us know the result.