understanding pdo rollback() in sql errors and autoincrement values

Question

i have a question...

Im using pdo and beginTransaction() but i dont understand some things.

My SQL table have 3 columns : id(autoincrement), username(unique), and password

And in my code i have this:

$pdo->beginTransaction();
$prepared = $pdo->prepare("INSERT INTO (username,password) VALUES(?,?)");
$prepared->$pdo->bindParam(1,"stefan");
$prepared->bindParam(2,"111111");
$prepared->execute();
$count= $prepared->rowCount();

if($count === FALSE):
    $pdo->rollback();
    var_dump($prepared->errorInfo());
else:
    $pdo->commit();
    echo "row count: {$count}";
endif;

When i execute this code, all works fine, generates id=1, username="stefan" and password="111111".

Now, I am generating an error on purpose to test rollback() function. I tried to insert same data 4 times in a row and i got unique error in $prepared->errorInfo().Works fine too.

The problem is, when im insert a new record after those 4 errors (for example, username="luv", password="222222") this new record has been inserted, but with ID=6 and not ID=2.

That is ok? rollback() should not leave the autoincrement at the last value that was correct? I miss something?

Thanks for help.

Answer 1

auto inc values get "burned" when you do rollbacks. this is normal and expected.

reference this stackoverflow entry