How do I enable LOAD DATA LOCAL INFILE in Propel?

Question

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

Abrasive

can you modify the php.ini? there's also mysql.allow_local_infile – Tunesmith

Trying it now @RoyalBg. Nope, didn't work. – Abrasive

What's the value of $filename? What host are you using to run your php script? What host is running your MySQL server? – Fractionate

@OllieJones $filename is the path of a temporary file created after downloading a CSV off of Amazon S3. As such, the PHP server is running off of EC2, and the MySQL server is running off of RDS. – Abrasive

Answer 1

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.

Abrasive

I got a -1 on an answer to my own question lolwut. Maybe if it didn't help someone else they should have posted a comment? Or created a new question and linked it to me here? – Abrasive

Answer 2

The only place it's valid to set the PDO::MYSQL_ATTR_LOCAL_INFILE attribute to true is while constructing a new Connection handle. You're trying to set it after constructing the handle: the getConnection() method simply retrieves the handle from the Propel runtime.

Propel appears to construct that handle under the covers.

Without changing Propel's source code, your best bet here is to use PDO directly to create your own connection handle, then do your LOAD DATA INFILE, then close that handle. In other words, go around Propel for this bulk-load operation.

Fractionate

Well technically the <options> element is supposed to control that. I had considered doing this before, but I did not have access to the database properties outside of the runtime-conf.php, so I loaded it straight from there. – Abrasive

Alright, so... the PDOException went away... but it's not inserting any data into my database Q_Q – Abrasive