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

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.