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

问题描述

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

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

由于数据库将位于与Web服务器不同的计算机上，因此在此之前需要使用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命令行(使用--local_infile = 1)加载DATA LOCAL INFILE
• 我无法从php加载数据本地文件.

在这两种有效的方法之间，它排除了以下情况:

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

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

我得到的错误是: 错误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参数.)

I've used phpinfo() to confirm that mysql.allow_local_infile = On

I've tried it on Amazon RDS (in case it was a problem in my dev server) and it doesn't work there either. (With the local_infile param turned on.)

我唯一未曾尝试过的事情是在我的开发服务器上编译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来解决此问题:

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屋！