CODEWITHSUNDEEP
phpmysqltransactionspdo
Dave
Dave

Reputation: 2765

PHP PDO Creating multiple tables in a transaction

I have the following bit of PHP code, which creates three database tables, then tries to roll back the transaction.

$dbh = new \PDO("mysql:host=localhost;dbname=dbname", 'usernamehere', 'passwordhere');
$dbh->setAttribute(\PDO::ATTR_AUTOCOMMIT,FALSE);
$dbh->beginTransaction();
$sql = "CREATE TABLE IF NOT EXISTS `a` (`id` int(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=latin1;";
$dbh->exec($sql);
$sql = "CREATE TABLE IF NOT EXISTS `b` (`id` int(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=latin1;";
$dbh->exec($sql);
$sql = "CREATE TABLE IF NOT EXISTS `c` (`id` int(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=latin1;";
$dbh->exec($sql);
$dbh->rollBack();

I expect the tables to not be created, but they are. Any thoughts?

Upvotes: 5

Views: 2579

Answers (1)

dakdad
dakdad

Reputation: 2955

The manual answers this question.

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. The implicit COMMIT will prevent you from rolling back any other changes within the transaction boundary.

Upvotes: 8

Related Questions