Laravel - PDO Prepared Statement - Cannot execute queries while other unbuffered queries are active

Question

I am having trouble running the following prepared statement in Laravel:

$pdo = DB::connection()->getPdo();
$ps_TempTable_PushCsv = $pdo->prepare(
    "LOAD DATA LOCAL INFILE '123'
    INTO TABLE `123`
    CHARACTER SET utf8mb4
    FIELDS TERMINATED BY ','
    OPTIONALLY ENCLOSED BY '\"'
    LINES TERMINATED BY '\\n'"
);
$ps_TempTable_PushCsv->execute();
$ps_TempTable_PushCsv->closeCursor();
$rowCount = $ps_TempTable_PushCsv->rowCount();

I get the following error:

[2017-06-08 03:41:35] local.ERROR: PDOException: SQLSTATE[HY000]: General error: 2014 Cannot execute queries while other unbuffered queries are active. Consider using PDOStatement::fetchAll(). Alternatively, if your code is only ever going to run against mysql, you may enable query buffering by setting the PDO::MYSQL_ATTR_USE_BUFFERED_QUERY attribute.

This is the entry-point of my controller, so there are definitely no prior queries running.

What do?

Thanks

Answer 1

If you go forward with the accepted answer, make sure to add the following code when creating your connection:

PDO::MYSQL_ATTR_LOCAL_INFILE => true

For example:

$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false,
    PDO::MYSQL_ATTR_LOCAL_INFILE => true
];
$pdo = new PDO("mysql:host=" . MYSQL_HOST . ";dbname=" . MYSQL_DB, MYSQL_USER, MYSQL_PASSWORD, $options);

Answer 2

The only way I could get it working was to replace the prepared statement with an 'exec' call:

$rowCount = DB::connection()->getpdo()->exec(
            "LOAD DATA LOCAL INFILE '$fileName'
            INTO TABLE $tableName
            CHARACTER SET utf8mb4
            FIELDS TERMINATED BY ','
            OPTIONALLY ENCLOSED BY '\"'
            LINES TERMINATED BY '\\n'"
        );

I have no idea why it wouldn't work using a prepared statement in Laravel - it definitely does work with a pure PDO prepared statement.