php - 加载数据输入 + MySQL 错误 28000

Question

我在执行以下 SQL 查询时遇到问题:

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 );

我想将一个 excel 文件的数据加载到我的数据库中的一个表中。当我在本地运行时，一切正常。但是当我在服务器上执行此操作时，出现以下错误:

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

我正尝试在 PHP 中(在 Zend Framework 中)执行此操作。当我联系主机时，他们说我需要 FILE 权限才能执行此操作。但这是不好的做法，不建议这样做。

我也试过在这样的 shell 脚本中这样做:

#!/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

但是我得到了同样的错误:

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

更新:

我试过像这样添加 LOCAL:

LOAD DATA LOCAL INFILE 'thelocationofmyfile.csv'

但是我得到了这个错误:

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

也尝试像这样添加 --local-infile=1 但得到同样的错误

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

第二次更新:

我的配置文件 my.cnf 如下所示:

[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

建立连接的位置并不重要，因为我正在使用建立连接的 shell 脚本对其进行测试。

运行时没有报错

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

(只是我数据库中所有表的列表)

当我运行 SHOW GRANTS; 时，我得到:

+------------------------------------------------------------------------------------------------------------------------+
| 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

您可以尝试 LOAD DATA LOCAL INFILE 并使用存储在客户端磁盘中的文件。这样您就不需要 FILE 权限。由于文件必须上传到服务器，所以速度会更慢。

希望有帮助