CODEWITHSUNDEEP
phpmysqltransactionspdo
Placido
Placido

Reputation: 1385

Multiple CREATE TABLE with errors within one transaction - no PDO exception

I'm trying to create four tables within one PDO transaction.

When the first "CREATE TABLE ..." part of the statement contains errors I successfully get an exception, an error message, and rollback. But when the first "CREATE TABLE ..." part are written correctly (as in the example below) I get no exception, commit, and only the first table created.

Here's the code:

$conn = Connection::getInstance();

$conn->dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$conn->dbh->beginTransaction();
     
$stmt = $conn->dbh->prepare("
CREATE TABLE IF NOT EXISTS `1st table` (valid SQL-code)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;

CREATE  TABLE IF NOT EXISTS `2nd table` (SQL-code with an error)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;

CREATE  TABLE IF NOT EXISTS `3rd table`...
CREATE  TABLE IF NOT EXISTS `4th table`...
");
try
{
    $stmt->execute();
    $conn->dbh->commit();
}
catch (Exception $e)
{
    $conn->dbh->rollBack();
    echo $e->getMessage();
}
    
unset($stmt);

After some research I found the following note at php.net:

Some databases, including MySQL, automatically issue an implicit COMMIT when a database definition language (DDL) statement such as DROP TABLE or CREATE TABLE is issued within a transaction.
Is it that what causes the problem and how to solve it?

Upvotes: 3

Views: 787

Answers (1)

O. Jones
O. Jones

Reputation: 108641

Like several commenters suggested, transactional semantics are not available for data definition language statements (DDL) in mySQL.

The PDO implementers obviously did not attempt to add some kind of client-side transactional semantics where there is no support in the DBMS.

Upvotes: 1

Related Questions