LOAD DATA INFILE + MySQL错误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 '
'
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 '
'
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