load data infile and unbuffered queries error

Question

I'm experimenting with loading a table from a file, and having difficulty. The code below is trying 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've got more work to do comparing the two tables before I let go of the temporary one. (Hints welcome if I should do this a different way). I get the error:

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

I've tried many of the suggestions from similar questions, but haven't cracked it yet. Thanks for your help! Here's 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 option to true.

That got rid of "unbuffered queries" error, but it then started reporting a "LOAD DATA LOCAL INFILE forbidden" error on the LOAD query.

The solution to that was to set the PDO::MYSQL_ATTR_USE_BUFFERED_QUERY option to true as well.

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 don't understand why these options are necessary, but they worked for me.