我如何能在行走LOAD DATA LOCAL INFILE？ [英] How do I enable LOAD DATA LOCAL INFILE in Propel?

问题描述

我试着从其他答案的解决方案，但至今没有一家解决：

  PDOException 42000 SQLSTATE [42000]：语法错误或访问冲突：
1148所使用的命令没有与此MySQL版本允许
 

我是preparing使用 PropelPDO 查询。我尝试过：

  $ CNCT = \行走::的getConnection（）;
$ cnct-＆GT;的setAttribute（\ PDO :: MYSQL_ATTR_LOCAL_INFILE，真正的）;
 

但这并没有prevent的错误，所以我也尝试过：

  $prepare = $ cnct-＆GT; prepare（$ sql中，阵列（
    \ PDO :: MYSQL_ATTR_LOCAL_INFILE =＆GT;真正，
））;
$prepare-＆GT;执行（）;
 

最后，我在运行时conf.xml行走的设置：

 ＆LT;选项＆gt;
    ＆LT;选择ID =MYSQL_ATTR_LOCAL_INFILE＆GT;真＆LT; /选项＆GT;
＆LT; /选项＆gt;
 

我还试图将其定义为一个属性：

 ＆LT;属性＆GT;
    ＆LT;选择ID =MYSQL_ATTR_LOCAL_INFILE＆GT;真＆LT; /选项＆GT;
＆LT; /属性＆GT;
 

下面是code块尝试使用这个命令：

 的foreach（$文件，$文件名=＆GT; $文件）{
    error_log中（[。日期（YMD H：我：秒。）]。导入'$文件名'...'）;
    $ SQL =＆LT;＆LT;＆LT; SQL
LOAD DATA LOCAL INFILE'$文件名'REPLACE
INTO TABLE`my_table`
FIELDS TERMINATED BY'，'OPTIONALLY ENCLOSED BY'\'
LINES TERMINATED BY'\ N'
（...）;
SQL;
    error_log中（$ SQL）;
    $prepare = $ cnct-＆GT; prepare（$ sql中，阵列（
        \ PDO :: MYSQL_ATTR_LOCAL_INFILE =＆GT;真正，
    ））;
    $prepare-＆GT;执行（）;
}
 

$文件是从网上下载文件从亚马逊S3的创建的临时PHP文件的列表。该文件名看起来像这样：的/ tmp / php7U5bgd

我没有访问的my.cnf。同样的数据库和用户允许 LOAD DATA LOCAL INFILE 在Java中运行。我也有使用MySQL的CLI，它让我运行此命令。

这prevents被抛出PDOException，但它并没有任何数据保存到我的数据库：\

  $ CONF =包括运行时conf.php;
$ CNCT =新\ PDO（
    $ CONF ['数据源'] ['MY_DATABASE'] ['连接'] ['DSN']，
    $ CONF ['数据源'] ['MY_DATABASE'] ['连接'] ['用户']，
    $ CONF ['数据源'] ['MY_DATABASE'] ['连接'] ['密码']，
    阵列（
        \ PDO :: MYSQL_ATTR_LOCAL_INFILE =＆GT;真正，
    ）
）;
 

最有价值的相关问题：

1. LOAD DATA LOCAL INFILE禁止在... PHP
2. MySQL的：启用LOAD DATA LOCAL INFILE

解决方案

这是为我工作的唯一的解决办法：

  $ CONF =包括运行时conf.php;

$ DSN = $ CONF ['数据源'] ['adstudio'] ['连接'] ['DSN'];
$ USER = $ CONF ['数据源'] ['adstudio'] ['连接'] ['用户'];
$密码= $ CONF ['数据源'] ['adstudio'] ['连接'] ['密码'];

$ hoststart = strpos（$ DSN，'主机='）+ 5;
$ hostend = strpos（$ DSN，';'，$ hoststart）;
$主机= SUBSTR（$ DSN，$ hoststart，$ hostend  -  $ hoststart）;

$ portstart = strpos（$ DSN，'口='）+ 5;
$预示着= strpos（$ DSN，';'，$ portstart）;
$端口= SUBSTR（$ DSN，$ portstart，$预示着 -  $ portstart）;

EXEC（'MySQL的-h'。$主机名。-P。$口。'-u'。$用户。'。
    -p。 $密码。 --local-INFILE = 1'。
    -eUSE MY_DATABASE; LOAD DATA LOCAL INFILE \'。$ TMP\更换成TERMINATED BY \表my_table的场'，\'OPTIONALLY ENCLOSED BY \'\\\'LINES TERMINATED BY \'\ñ \'（...）;'）;
 

根据关这个答案。

I've tried the solutions from other answers, but so far none have resolved:

PDOException 42000 SQLSTATE[42000]: Syntax error or access violation: 
1148 The used command is not allowed with this MySQL version

I'm preparing a query using PropelPDO. I've tried:

$cnct = \Propel::getConnection();
$cnct->setAttribute(\PDO::MYSQL_ATTR_LOCAL_INFILE, true);

But this did not prevent the error, so I also tried:

$prepare = $cnct->prepare($sql, array(
    \PDO::MYSQL_ATTR_LOCAL_INFILE => true,
));
$prepare->execute();

And finally, I set it in the runtime-conf.xml of Propel:

<options>
    <option id="MYSQL_ATTR_LOCAL_INFILE">true</option>
</options>

I also tried defining it as an attribute:

<attributes>
    <option id="MYSQL_ATTR_LOCAL_INFILE">true</option>
</attributes>

Here is the block of code trying to use this command:

foreach ($files as $filename => $file) {
    error_log('[' . date('Y-m-d h:i:s') . '] Importing ' . $filename . '... ');
    $sql = <<<SQL
LOAD DATA LOCAL INFILE '$filename' REPLACE 
INTO TABLE `my_table`  
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' 
LINES TERMINATED BY '\n' 
(...);
SQL;
    error_log($sql);
    $prepare = $cnct->prepare($sql, array(
        \PDO::MYSQL_ATTR_LOCAL_INFILE => true,
    ));
    $prepare->execute();
}

$files is a list of temporary PHP files created from downloading files off of Amazon S3. The filenames look like this: /tmp/php7U5bgd

I do not have access to the my.cnf. The same database and user allow the LOAD DATA LOCAL INFILE to run in Java. I have also used the MySQL CLI and it allowed me to run this command.

This prevents the PDOException from being thrown, but it does not save any data to my database :\

$conf = include 'runtime-conf.php';
$cnct = new \PDO(
    $conf['datasources']['my_database']['connection']['dsn'], 
    $conf['datasources']['my_database']['connection']['user'], 
    $conf['datasources']['my_database']['connection']['password'], 
    array(
        \PDO::MYSQL_ATTR_LOCAL_INFILE => true,
    )
);

Most helpful related questions:

1. LOAD DATA LOCAL INFILE forbidden in... PHP
2. MySQL: Enable LOAD DATA LOCAL INFILE

解决方案

This is the only solution that worked for me:

$conf = include 'runtime-conf.php';

$dsn = $conf['datasources']['adstudio']['connection']['dsn'];
$user = $conf['datasources']['adstudio']['connection']['user'];
$password = $conf['datasources']['adstudio']['connection']['password'];

$hoststart = strpos($dsn, 'host=') + 5;
$hostend = strpos($dsn, ';', $hoststart);
$hostname = substr($dsn, $hoststart, $hostend - $hoststart);

$portstart = strpos($dsn, 'port=') + 5;
$portend = strpos($dsn, ';', $portstart);
$port = substr($dsn, $portstart, $portend - $portstart);

exec('mysql -h ' . $hostname . ' -P ' . $port . ' -u ' . $user . ' ' .
    '-p' . $password . ' --local-infile=1 ' .
    '-e "USE my_database;LOAD DATA LOCAL INFILE \'' . $tmp . '\' REPLACE INTO TABLE my_table FIELDS TERMINATED BY \',\' OPTIONALLY ENCLOSED BY \'\\"\' LINES TERMINATED BY \'\n\' (...);"');

Based off of this answer.

这篇关于我如何能在行走LOAD DATA LOCAL INFILE？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！