LOAD DATA LOCAL INFILE 失败 - 从 php 到 mysql(在 Amazon rds 上) [英] LOAD DATA LOCAL INFILE fails - from php, to mysql (on Amazon rds)

问题描述

我们正在将我们的数据库从网络服务器转移到单独的服务器(从 Amazon EC2 网络服务器转移到 RDS 实例.)

We're moving our database from being on the webserver to a separate server (from an Amazon EC2 webserver to an RDS instance.)

我们有一个 LOAD DATA INFILE 之前可以使用，现在需要添加 LOCAL 关键字，因为数据库将位于与网络服务器不同的机器上.

We have a LOAD DATA INFILE that worked before that is going to need the LOCAL keyword added now that the database will be on a different machine to the webserver.

在我的开发服务器上测试，结果不工作:

Testing on my dev server, it turns out that it doesn't work:

• 我仍然可以像以前一样从 php 加载数据文件
• 我可以从 mysql 命令行加载 DATA LOCAL INFILE(使用 --local_infile=1)
• 我无法从 php 加载 DATA LOCAL INFILE.

在这两项有效的情况下，排除了:

Between those 2 things that do work, it rules out:

• sql 或 php 代码的问题
• 上传文件的问题，包括语法和文件权限
• mysql 服务器设置问题

我得到的错误是:ERROR 1148 (42000): 此 MySQL 版本不允许使用的命令(如果我不使用 --local_infile=1，我会从 mysql 命令行收到该错误)

The error I get is: ERROR 1148 (42000): The used command is not allowed with this MySQL version (I get that error from the mysql commandline if I don't use --local_infile=1)

其他一些相关信息:

• Ubuntu 12.04、mysql 5.5.24、php 5.3.10
• 我正在使用 php 的 mysql_connect(而不是 mysqli，因为我们计划使用不支持 mysqli 的 facebook 的 hiphop 编译器.)

• 因此，connect 命令需要设置额外的标志:

• Ubuntu 12.04, mysql 5.5.24, php 5.3.10
• I'm using php's mysql_connect (instead of mysqli, because we're planning on using facebook's hiphop compiler which doesn't support mysqli.)

• Because of that, the connect command needs an extra flag set:

mysql_connect($dbHost, $dbUser, $dbPass, false, 128);

我已经使用 phpinfo() 来确认 mysql.allow_local_infile = On

我已经在 Amazon RDS 上尝试过它(以防我的开发服务器出现问题)，但它也无法在那里工作.(打开 local_infile 参数.)

我读到的唯一我没有尝试过的事情是在我的开发服务器上编译 mysql 服务器，并打开标志以允许本地 infile ......但即使我在我的开发服务器上运行它也是不会帮助我使用 Amazon RDS.(除此之外，LOAD DATA LOCAL INFILE 确实可以从 mysql 命令行工作.)

The only thing I've read about that I haven't tried is to compile mysql server on my dev server with the flag turned on to allow local infile... but even if I get that working on my dev server it's not going to help me with Amazon RDS. (Besides which, LOAD DATA LOCAL INFILE does work from the mysql commandline.)

这似乎是 php 的 mysql_connect() 的具体问题

It seems like it's specifically a problem with php's mysql_connect()

任何使用 LOAD DATA LOCAL INFILE(可能来自 Amazon RDS)的人都知道如何让它发挥作用?

Anybody using LOAD DATA LOCAL INFILE (maybe from Amazon RDS) that knows the trick to getting this to work?

推荐答案

我已经放弃了，因为我认为这是 php 中的一个错误 - 特别是 mysql_connect 代码，现在已弃用.它可能可以通过使用类似于@eggyal 提到的错误报告中提到的步骤自己编译 php 并更改源代码来解决:https://bugs.php.net/bug.php?id=54158

I've given up on this, as I think it's a bug in php - in particular the mysql_connect code, which is now deprecated. It could probably be solved by compiling php yourself with changes to the source using steps similar to those mentioned in the bug report that @eggyal mentioned: https://bugs.php.net/bug.php?id=54158

相反，我将通过调用 system() 并使用 mysql 命令行来解决这个问题:

Instead, I'm going to work around it by doing a system() call and using the mysql command line:

$sql = "LOAD DATA LOCAL INFILE '$csvPathAndFile' INTO TABLE $tableName FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' ESCAPED BY '\\\\' LINES TERMINATED BY '\\r\\n';";
system("mysql -u $dbUser -h $dbHost --password=$dbPass --local_infile=1 -e "$sql" $dbName");

这对我有用.

这篇关于LOAD DATA LOCAL INFILE 失败 - 从 php 到 mysql(在 Amazon rds 上)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！