Error loading data and unbuffered queries

Question

I am experimenting with loading a table from a file and having difficulty. The code below tries to take an existing database and copy it to a temporary table, then replace the original with imported data from a CSV file, and then I have more work comparing the two tables before I let go of the temporary one. (Tips are welcome if I do it differently). I get an error message:

'SQLSTATE [HY000]: general error: 2014 Unable to execute queries while other unbuffered queries are active. Consider using PDOStatement :: fetchAll () ...

I have tried many suggestions on similar issues, but have not yet cracked them. Thank you for your help! Here is my code:

<?php //database connection $data_source = 'mysql:host=localhost;dbname=myDB'; $db_user = 'root'; $db_password = 'pass'; $conn = new PDO($data_source, $db_user, $db_password, array(PDO::ATTR_EMULATE_PREPARES => false, PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_PERSISTENT)); if ( isset($_GET['submit']) ){ $stmt = $conn->prepare("CREATE TEMPORARY TABLE mfsw_dupe AS SELECT * FROM mfsw_test"); $stmt->execute(); $stmt = $conn->prepare("TRUNCATE mfsw_test"); $stmt->execute(); $stmt = $conn->prepare("LOAD DATA LOCAL INFILE 'C:\\xampp\htdocs\assets\mfsw_test.csv' INTO TABLE mfsw_test FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' IGNORE 1 LINES"); $stmt->execute(); } ?> 

Answer 1

After trying all the recommended solutions to this problem, I found that the answer was to set the PDO::ATTR_EMULATE_PREPARES to true.

This eliminated the "unbuffered query" error, but then proceeded to report the "LOAD DATA LOCAL INFILE forbidden" error in the LOAD request.

The solution was to set the PDO::MYSQL_ATTR_USE_BUFFERED_QUERY to true.

In short, your initial connection should look like this:

 $conn = new PDO($data_source, $db_user, $db_password, array(PDO::ATTR_EMULATE_PREPARES => true, PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => true, PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_PERSISTENT)); 

I do not understand why these parameters are necessary, but they worked for me.