CODEWITHSUNDEEP
phpmysqlpdoprepared-statementsetattribute
Jimmix
Jimmix

Reputation: 6526

PHP PDO fails when PDO::ATTR_EMULATE_PREPARE = false

I have an application where I pass a class of PDO connection to a MySQL 8 as a parameter to a class that interacts with the database by using that PDO connection instance.

For creating PDO Connection to the database I must use the option PDO::ATTR_EMULATE_PREPARES set to false for PDO connection. Class that uses PDO connection has no problem with executing SELECT or INSERT statements but in case of statements like:

USE database-name;
CREATE TRIGGER
DROP TRIGGER

I receive an error:

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.

But statements like:

CREATE DATABASE
CREATE TABLE

work fine.

Here's an example code to replicate the issue:

<?php

header("Content-Type: text/plain");

$user = '';
$pass = '';
$host = '';
$dbname = 'pdo_snippet';

/**
 * Use at first run, the script fails at USE database-name statement
 * but creates database schema $dbName
 */
$dsn = 'mysql:host=' . $host;

/**
 * Use at second run, baypasses fail at USE database-name statement
 * but fails at DROP TRIGGER IF EXISTS;
 */
// $dsn = 'mysql:dbname=' .$dbname. ';host=' . $host;

$options = [];
$options[\PDO::ATTR_ERRMODE] = \PDO::ERRMODE_EXCEPTION;
$options[\PDO::ATTR_EMULATE_PREPARES] = false; # with true no problem

//---------- CONNECT TO DATABASE SERVER ---------
echo "Connect to database";
try {
    $pdo = new \PDO($dsn, $user, $pass, $options);
    
} catch (\Exception $e) {
    $msg = 'PDO could not establish connection to dsn: ' . $dsn;
    $msg .= '. PDO exception Msg: ' . $e->getMessage();
    throw new \RuntimeException($msg);
}
echo ", DONE.\n";
//----------------------------------------------

//--------- CREATE SCHEMA IF NOT EXISTS --------
$sqlQuery = 'CREATE DATABASE IF NOT EXISTS `' . $dbname . '`;';
echo "Create database $dbname IF NOT EXISTS";
try {
    $stmt = $pdo->query($sqlQuery);

} catch (\Exception $e) {
    $msg = 'Could not execute query: "' . $sqlQuery . '"';
    $msg .= '. PDO exception Msg: ' . $e->getMessage();
    throw new \RuntimeException($msg);
}
echo ", DONE.\n";
//------------------------------------------------

//----------- SELECT DEFAULT DATABASE ------------
echo "SELECT FROM default database";
$sqlQuery = "SELECT DATABASE();";
try {
    $stmt = $pdo->query($sqlQuery);

} catch (\Exception $e) {
    $msg = 'Could not execute query: "' . $sqlQuery . '"';
    $msg .= '. PDO exception Msg: ' . $e->getMessage();
    throw new \RuntimeException($msg);
}
echo ", DONE.\n";
//------------------------------------------------
$defaultDbName = $stmt->fetchColumn();
//------------------------------------------------

//----------- USE datatabase as default ----------
if (empty($defaultDbName)) {
    echo "USE $dbname";
    $sqlQuery = 'USE `' . $dbname . '`;';
    try {
        $stmt = $pdo->query($sqlQuery);
        
    } catch (\Exception $e) {
        $msg = 'Could not execute query: "' . $sqlQuery . '"';
        $msg .= '. PDO exception Msg: ' . $e->getMessage();
        throw new \RuntimeException($msg);
    }
    echo ", DONE.\n";
}
//------------------------------------------------

//------------ CREATE TABLE ----------------------
echo "CREATE TABLE example IF NOT EXISTS";
$sqlQuery = 'CREATE TABLE IF NOT EXISTS `example` (`id` INT AUTO_INCREMENT, `name` VARCHAR(255), PRIMARY KEY(`id`)) ENGINE=InnoDb;';
try {
    $stmt = $pdo->query($sqlQuery);
    
} catch (\Exception $e) {
    $msg = 'Could not execute query: "' . $sqlQuery . '"';
    $msg .= '. PDO exception Msg: ' . $e->getMessage();
    throw new \RuntimeException($msg);
}
echo ", DONE.\n";
//------------------------------------------------

//------------ DROP TRIGGER IF EXISTS ------------
echo "DROP TRIGGER IF EXISTS";
$sqlQuery = 'DROP TRIGGER IF EXISTS `tr_example_bi_fill`;';
try {
    $stmt = $pdo->query($sqlQuery);
    
} catch (\Exception $e) {
    $msg = 'Could not execute query: "' . $sqlQuery . '"';
    $msg .= '. PDO exception Msg: ' . $e->getMessage();
    throw new \RuntimeException($msg);
}
echo ", DONE.\n";
//------------------------------------------------

//--------------- CREATE TRIGGER ---------------------
echo "CREATE TRIGGER";
$sqlQuery = "
    CREATE TRIGGER 
        `tr_example_bi_fill`
    BEFORE INSERT ON 
        `example`
    FOR EACH ROW BEGIN
        SET new.`name` = CONCAT(new.`name`, '_TRIGGER');
    END
    ;
";
echo ", DONE.\n";

try {
    $stmt = $pdo->query($sqlQuery);

} catch (\Exception $e) {
    $msg = 'Could not execute query: "' . $sqlQuery . '"';
    $msg .= '. PDO exception Msg: ' . $e->getMessage();
    throw new \RuntimeException($msg);
}
//------------------------------------------------

//--------------- INSERT INTO --------------------
$value = rand(0,9999);
echo "INSERT INTO TABLE example VALUE '$value'";
$sqlQuery = "INSERT INTO `example` (`name`) VALUES ('$value')";
try {
    $stmt = $pdo->query($sqlQuery);

} catch (\Exception $e) {
    $msg = 'Could not execute query: "' . $sqlQuery . '"';
    $msg .= '. PDO exception Msg: ' . $e->getMessage();
    throw new \RuntimeException($msg);
}
echo ", DONE.\n";
//------------------------------------------------

// --------------- SELECT FROM -------------------
echo "SELECT FROM example";
$sqlQuery = "SELECT `name` FROM `example` ORDER BY `id` DESC LIMIT 1;";
try {
    $stmt = $pdo->query($sqlQuery);

} catch (\Exception $e) {
    $msg = 'Could not execute query: "' . $sqlQuery . '"';
    $msg .= '. PDO exception Msg: ' . $e->getMessage();
    throw new \RuntimeException($msg);
}
echo ", DONE.\n";
//------------------------------------------------

$result = $stmt->fetchColumn();

echo "Result:\n";
print_r($result);


echo "\n=================\n";

If I run it at the first time with:

$dsn = 'mysql:host=' . $host;

then I get:

Fatal error: Uncaught RuntimeException: Could not execute query: "USE pdo_snippet;". PDO exception Msg: 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.

Running the script at the second time with:

$dsn = 'mysql:dbname=' .$dbname. ';host=' . $host;

to baypass first the exception thrown by USE statement gives another exception:

Fatal error: Uncaught RuntimeException: Could not execute query: "DROP TRIGGER IF EXISTS tr_example_bi_fill;". PDO exception Msg: 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.

I would like to ask how to use a signle instance of PDO connection to a database with the option PDO::ATTR_EMULATE_PREPARES set to false and be able to execute statements like USE database-name; or DROP TRIGGER ...; CREATE TRIGGER ...;?

It confuses me the error message that focuses on unbuffered queries.
IMO there is no issue with buffered/unbuffered queries and using $options[\PDO::MYSQL_ATTR_USE_BUFFERED_QUERY] = false; or $options[\PDO::MYSQL_ATTR_USE_BUFFERED_QUERY] = true; does not help.
Perhaps you could make that error clear to me.

After some additional testing it looks to me that the problem may be somehow related to the unbuffered queries because if at the second time of running the script I run it with adding:

$options[\PDO::MYSQL_ATTR_USE_BUFFERED_QUERY] = false;

then the script fails much earlier, at:

Fatal error: Uncaught RuntimeException: Could not execute query: "CREATE TABLE IF NOT EXISTS example (id INT AUTO_INCREMENT, name VARCHAR(255), PRIMARY KEY(id)) ENGINE=InnoDb;". PDO exception Msg: 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.

not reaching the problematic line with DROP TRIGGER IF EXISTS.

So it looks that at least for statements like CREATE TABLE the solution could be to use $options[\PDO::MYSQL_ATTR_USE_BUFFERED_QUERY] = true which is true by default I believe. But still the problme with USE ... and CREATE TRIGGER ..., DROP TRIGGER ... remains.

I'm Using: PHP 7.3.8 (cli) (built: Aug 2 2019 05:16:32) ( NTS ) with MySQL related extensions: PDO 7.3.8, mysqli 7.3.8, mysqlnd 5.0.12-dev, pdo_mysq 7.3.8

MySQL 8.0.19

both based on the official Docker images.

Upvotes: 1

Views: 761

Answers (1)

nbk
nbk

Reputation: 49395

Your code is wrong

See manual

DROP TRIGGER IF EXISTS tr_example_bi_fill

Upvotes: 0

Related Questions