Transaction rollback doesn't work

I have made a database wrapper with extra functionality around the PDO system (yes, i know a wrapper around a wrapper, but it is just PDO with some extra functionality). But i have noticed a problem.

The folowing doesn't work like it should be:

<?php
var_dump($db->beginTransaction());

$db->query('
 INSERT INTO test
 (data) VALUES (?)
 ;',
 array(
  'Foo'
 )
);
print_r($db->query('
 SELECT *
 FROM test
开发者_运维知识库 ;'
)->fetchAll());

var_dump($db->rollBack());

print_r($db->query('
 SELECT *
 FROM test
 ;'
)->fetchAll());
?>

The var_dump's shows that the beginTransaction and rollBack functions return true, so no errors.

I expected that the first print_r call show a array of N items and the second call show N-1 items. But that issn't true, they both show same number of items.

My $db->query(< sql >, < values >) call nothing else then $pdo->prepare(< sql >)->execute(< values >) (with extra error handling ofcourse).

So i think or the transaction system of MySQL doesn't work, or PDO's implenmentaties doesn't work or i see something wrong.

Does anybody know what the problem is?

Check if your type of database equals innoDB. In one word you must check if your database supports transactions.

Two possible problems:

1. The table is MyISAM which doesn't support transaction. Use InnoDB.

2. Check to make sure auto-commit is OFF.

http://www.php.net/manual/en/pdo.transactions.php

I'm entering this as an answer, as a comment is to small to contain the following:

PDO is just a wrapper around the various lower level database interface libraries. If the low-level library doesn't complain, either will PDO. Since MySQL supports transactions, no transaction operations will return a syntax error or whatever. You can use MyISAM tables within transactions, but any operations done on them will be done as if auto-commit was still active:

mysql> create table myisamtable (x int) engine=myisam;
Query OK, 0 rows affected (0.00 sec)

mysql> create table innodbtable (x int) engine=innodb;
Query OK, 0 rows affected (0.00 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into myisamtable (x) values (1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into innodbtable (x) values (2);
Query OK, 1 row affected (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> select * from myisamtable;
+------+
| x    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql> select * from innodbtable;
Empty set (0.00 sec)

mysql>

As you can see, even though a transaction was active, and some actions were performed on the MyISAM table, no errors were thrown.

MySQL doesn't support transactions on the MyISAM table type, which is unfortunately the default table type.

If you need transactions, you should switch to the InnoDB table type.

Another reason this may happen is certain types of SQL statements cause an immediate auto-commit. I had a large script that ran in a transaction that was getting committed immediately and ignored the transaction. I eventually found out it was because any ALTER TABLE statement immediately causes a commit to happen.

Types of statements that cause auto commits are:

• Anything that modifies a table or the database, such as ALTER TABLE, CREATE TABLE, etc.
• Anything that modifies table permissions, such as ALTER USER or SET PASSWORD
• Anything that locks that tables or starts a new transaction
• Data loading statements
• Administrative statements, such as ANALYZE TABLE, FLUSH, or CACHE INDEX
• Replication control statements, such as anything to do with a slave or master

More info and a complete list can be found here: https://dev.mysql.com/doc/refman/8.0/en/implicit-commit.html

If you're having this problem only with a specific script and you're sure you're using InnoDB, you might want to look to see if any SQL statements in your script match these.