切换到mysqli后LOCAL INFILE的问题 [英] Problems with LOCAL INFILE after switching to mysqli

问题描述

我以前在本人的PHP脚本中使用了LOCAL INFILE，

  mysql_connect（DB_SERVER，DB_USER，DB_PASS，false，128）
通过在连接调用中使用128标志 

现在我正试图切换到mysqli，因为旧的mysql已经折旧了。
我已经尝试了一切，我可以找到每一个论坛上没有运气。

我在mysqli的连接调用似乎很好：

  $ connection = mysqli_init（）; 
 if（！$ connection）{die（'mysqli_init failed'）;} 
 if（！mysqli_options（$ connection，MYSQLI_INIT_COMMAND，'SET AUTOCOMMIT = 0））{die（'设置MYSQLI_INIT_COMMAND失败' （）;} 
 if（！mysqli_options（$ connection，MYSQLI_OPT_CONNECT_TIMEOUT，5））{die（'设置MYSQLI_OPT_CONNECT_TIMEOUT失败'）;} 
 if（！mysqli_options（$ connection，MYSQLI_OPT_LOCAL_INFILE，true））{ （'设置MYSQLI_OPT_LOCAL_INFILE失败'）;} 
 
 if（！mysqli_reconnect_connect（$ connection，DB_SERVER，DB_USER，DB_PASS，DB_SELECT））{
 die（'Error Error（'。mysqli_connect_errno '）'
。mysqli_connect_error（））; 
 
 $ / code> 

我的my.cnf文件包含相应的参数：

pre code $ mysql
local-infile =


我的php.ini也包含相应的参数：
$ b $在
$ b $ MySQL $ b $ mysql_allow_local_infile =
$ MySQL
$ mysqli.allow_local_infile = / code>

查询甚至在执行时返回true！但是我仍然没有在我的表中加载任何数据。

$ $ $ $ $ $ $ $查询=LOAD DATA LOCAL INFILE'{$ file}'INTO $ {$ lineTerm}'IGNORE {$ skipLines} LINES';
if（mysqli_query（$ connection， ）$ {$ message。=加载完成< br />;} / /消息返回与加载完成


除了进行system（）调用之外，我不知道该怎么做才能做到这一点，它在mysql中工作得很好，而不是现在用mysqli。 b

为什么LOCAL INFILE如此困难？这是一个长期持续的问题。

编辑：

奇怪的是，当我在查询后运行mysqli_info（）时，它表示它正确执行。

 记录：4删除：0跳过：0警告：0 
  

测试文件中有4条记录，所以这是正确的。

解决方案

快速的谷歌搜索引起了以下的PHP错误条目： https ：//bugs.php.net/bug.php?id=55737 。

这个问题似乎围绕着php.ini设置open_basedir：
http://www.php.net/ manual / en / ini.core.php＃ini.open-basedir
$ b

LOAD DATA LOCAL INFILE函数不起作用在Debian Wheezy上使用mysql5.5和php5.4.4，并使用open_basedir限制。
当open_basedir被禁用或设置为空时，它工作得很好。

编辑（解决方案参考）：OP的问题是用SET AUTOCOMMIT = 0初始化命令。这是加载和处理，但没有被提交给数据库。

Anyone that is aware of the issues with using LOCAL INFILE in PHP will understand my frustration with this.

I previously got LOCAL INFILE working properly in all my PHP scripts by using the 128 flag in my connection call.

mysql_connect(DB_SERVER,DB_USER,DB_PASS,false,128)

I am now trying to switch to mysqli since the old mysql is depreciated. I have tried everything I could find on every forum out there with no luck.

My connection call in mysqli seems fine:

$connection = mysqli_init();
if (!$connection) {die('mysqli_init failed');}
if (!mysqli_options($connection, MYSQLI_INIT_COMMAND, 'SET AUTOCOMMIT = 0')) {die('Setting MYSQLI_INIT_COMMAND failed');}
if (!mysqli_options($connection, MYSQLI_OPT_CONNECT_TIMEOUT, 5)) {die('Setting MYSQLI_OPT_CONNECT_TIMEOUT failed');}
if (!mysqli_options($connection, MYSQLI_OPT_LOCAL_INFILE,true)) {die('Setting MYSQLI_OPT_LOCAL_INFILE failed');}

if (!mysqli_real_connect($connection, DB_SERVER, DB_USER, DB_PASS, DB_SELECT)) {
    die('Connect Error (' . mysqli_connect_errno() . ') '
            . mysqli_connect_error());
}

My my.cnf file contains the appropriate parameters:

[mysql]
local-infile=1

[mysqld]
local-infile=1

my php.ini also contains the appropriate parameters:

[MySQLi]
mysqli.allow_local_infile = On

[MySQL]
mysql.allow_local_infile = On

The query even returns true when executed! But i still get no data loaded in my table.

$query = "LOAD DATA LOCAL INFILE '{$file}' INTO TABLE {$table} FIELDS TERMINATED BY '{$fieldTerm}' OPTIONALLY ENCLOSED BY '\"' LINES TERMINATED BY '{$lineTerm}' IGNORE {$skipLines} LINES";
if(mysqli_query($connection,$query)) {$message .= "Load Complete <br />";} //Message returns with "Load Complete".

Other than doing a system() call, I have no idea what to do to make this work. It worked fine with mysql, just not now with mysqli.

Why is LOCAL INFILE so difficult? This has been an ongoing problem for way too long.

EDIT:

Strange, when I run mysqli_info() after the query, it says it executed properly.

Records: 4 Deleted: 0 Skipped: 0 Warnings: 0

There are 4 records in the test file, so this is correct.

解决方案

A quick google search brought up the following PHP Bug entry: https://bugs.php.net/bug.php?id=55737.

This issue seems to revolve around the php.ini setting open_basedir: http://www.php.net/manual/en/ini.core.php#ini.open-basedir

The LOAD DATA LOCAL INFILE function does not work with mysql5.5 and php5.4.4 on Debian Wheezy with open_basedir restrictions in place. It works perfectly fine when open_basedir is disabled or set to nothing.

EDIT (solution for reference): OP's issue was with the "SET AUTOCOMMIT = 0" init command. It was loading and processing, but not being committed to the db.

这篇关于切换到mysqli后LOCAL INFILE的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！