CODEWITHSUNDEEP
phpmysqlpdo
csupak
csupak

Reputation: 145

load data infile and unbuffered queries error

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();
    }
?>

Upvotes: 6

Views: 1257

Answers (2)

Alex
Alex

Reputation: 89

I have had exactly the same problem, tried everything mentioned above and a lot more and still couldn't avoid the "Cannot execute queries while other unbuffered queries are active" error. However, after several hours of googling, I got it working - instead of using a prepared statement, or just query, I used 'exec' to run the 'LOAD DATA LOCAL INFILE' statement:

$stmt = $conn->exec($sql_import_csv);

I didn't need any of the aforementioned changes to the PDO options - this is my options array:

$options = array(
  PDO::ATTR_EMULATE_PREPARES => false,
  PDO::MYSQL_ATTR_SSL_CA => "xxxxxxxx.crt.pem",
  PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
  PDO::MYSQL_ATTR_LOCAL_INFILE => true
);

Hope this helps someone else out there.

Upvotes: -1

James Holderness
James Holderness

Reputation: 23001

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.

Upvotes: 9

Related Questions