4296

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 <a href="http://api.propelorm.org/1.7.1/PropelPDO.html" rel="nofollow">PropelPDO</a>. 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:

<ol><li><a href="https://stackoverflow.com/questions/7638090/load-data-local-infile-forbidden-in-php/" rel="nofollow">LOAD DATA LOCAL INFILE forbidden in... PHP</a></li> <li><a href="https://stackoverflow.com/questions/10762239/mysql-enable-load-data-local-infile" rel="nofollow">MySQL: Enable LOAD DATA LOCAL INFILE</a></li> </ol>

Answer1:

The only place it's valid to set the PDO::MYSQL_ATTR_LOCAL_INFILE attribute to true is <em>while constructing</em> 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.

Answer2:

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 <a href="https://stackoverflow.com/a/13349862/904344" rel="nofollow">this answer</a>.

Recommend

  • Run custom script after database tables are created in Spring Boot
  • array from php to JavaScript
  • how to redirect http to https in nginx docker elastic beanstalk
  • Importing matplotlib on Ubuntu
  • How to import Navit into Eclipse and use it in own android project
  • How to remove comma or any characters from Python dataframe column name
  • How to give custom name to Sqoop output files
  • distinct values from multiple fields within one table ORACLE SQL
  • Select inner HTML item in CSS
  • Double-click autofill - dynamic based on adjacent cell
  • how to get data attributes of dynamically generated element
  • Responsive design method for collapsing a div
  • Extract All Possible Paths from Expression-Tree and evaluate them to hold TRUE
  • Iron Router: How do I send data to the layout?
  • Building Qt project for C++11 standard
  • XSLT foreach repeating nodes to flat
  • List images(01.png) and descriptions(01.txt) from directory
  • how to upload multiple files in c# windows application
  • Keep this build forever option - Jenkins
  • How to define custom class, title, and target in Link Browser for content elements and the new rte_c
  • How do I access an unhandled exception in an MVC Error view?
  • D3 nodes and links from JSON with nested arrays of children
  • Google Custom Search with transparent background
  • How to install a .deb file on a jailbroken iphone programmatically?
  • Handling un-mapped Rest path
  • vba code to select only visible cells in specific column except heading
  • jqPlot EnhancedLegendRenderer plugin does not toggle series for Pie charts
  • Run Powershell script from inside other Powershell script with dynamic redirection to file
  • How do I rollback to a specific git commit
  • Is there a mandatory requirement to switch app.yaml?
  • using conditional logic : check if record exists; if it does, update it, if not, create it
  • Load html files in TinyMce
  • How can I get HTML syntax highlighting in my editor for CakePHP?
  • Hits per day in Google Big Query
  • coudnt use logback because of log4j
  • How can I remove ASP.NET Designer.cs files?
  • Busy indicator not showing up in wpf window [duplicate]
  • reshape alternating columns in less time and using less memory
  • To Get the radio button value in ruby on rails
  • java string with new operator and a literal