PDO and LOAD DATA LOCAL INFILE do not work

Question

I was just trying to use LOAD DATA LOCL INFILE with pdo. It didn’t work for me. Here is my function

function connect($db_name,$db_host,$db_user,$db_pass) { try { $this->connect = new PDO("mysql:host=$db_host;dbname=$db_name", $db_user, $db_pass); $this->connect->exec("LOAD DATA LOCAL INFILE 'http://localhost/testoo.csv' INTO TABLE 'parsed' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' LINES TERMINATED BY '\n' ('name','link','price','brand','imageurl')"); } catch(PDOException $e) { echo $e->getMessage(); } } 

Nothing is happening yet. The same query works with regular mysql_query. Any pointers to this problem?

Answer 1

• LOAD DATA LOCAL INFILE is prohibited in ... PHP
• Make sure that both mySQL and www users have access to the corresponding file.

Alternatively: use fgetcsv() and programmatically create the inserts.

change

To avoid using resources using fgetcsv() [because it tries to read the entire file right away], you can create a loop like the one below to read / paste managed snippets.

 <?php $fname = 'myfile.csv'; $chunksize = 50; if( ! $fh = fopen($myfile, 'r') ) { throw new Exception("Could not open $fname for reading."); } $i=0; $buffer = array() while(!feof($fh)) { $buffer[] = fgets($fh); $i++; if( ($i % $chunksize) == 0 ) { commit_buffer($buffer); //run inserts $buffer = array(); //blank out the buffer. } } //clean out remaining buffer entries. if( count($buffer) ) { commit_buffer($buffer); } function commit_buffer($buffer) { foreach( $buffer as $line ) { $fields = explode(',', $line); //create inserts } //run inserts $buffer = array(); //blank out the buffer. } 

Thus, at any given time, only $chunksize strings are stored in memory.

You will most likely need additional code to handle things like encapsulated strings containing commas and line breaks, but if you cannot get LOAD DATA LOCAL INFILE , I see no other choice.

Answer 2

Please, try

Set PDO Connection Parameters

Set the PDO :: MYSQL_ATTR_LOCAL_INFILE attribute

 function connect($db_name,$db_host,$db_user,$db_pass) { try { $this->connect = new PDO("mysql:host=$db_host;dbname=$db_name", $db_user, $db_pass,array(PDO::MYSQL_ATTR_LOCAL_INFILE => true)); $this->connect->exec("LOAD DATA LOCAL INFILE 'http://localhost/testoo.csv' INTO TABLE 'parsed' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' LINES TERMINATED BY '\n' ('name','link','price','brand','imageurl')"); } catch(PDOException $e) { echo $e->getMessage(); } } 

Answer 3

I had the same problem. My MySQL server had the correct local conf conf, PHP/PDO had the right PDO::MYSQL_ATTR_LOCAL_INFILE conf too. The solution was to (re) install php5-mysqlnd.

 $> apt-get update $> apt-get install php5-mysqlnd 

... and it worked :)