Hi guys,

I have the strangest problem.

I have an InnoDB table that I'm doing some transactions on, but when I roll back a transaction I find it ALSO rolls back the incremented auto_increment field I have set for the id.

Everything I've read in official documentation, forums, blogs, etc says that this should NEVER happen. By design it should leave the field incremented and you should have a gap in your ids.

This is what I did:

Inserted a blank record:
id: 808065

Attempted a failed transaction on the same table.

Inserted a new blank record:
id: 808066

It's my understanding that by default the second successful insert should have been at 808067.

Server is an Ubuntu server running MySQL server 5.0.

Could there be a MySQL config setting or an InnoDB setting that I'm not clear on? Does something else need to be done besides setting the id field to primary and auto_increment?

Any help is greatly appreciated!

Can you give more concrete example of the failed transaction? What was the error message returned from the failed transaction?

The in memory auto increment counter will reset itself after a server restart and will set itself to the highest value for the auto incremented field during the first insert into the table. But I suspect this is not what has happened.

Hence, this lead me to believe that the issue is related to the type of transaction error you are getting. Something like duplicate key will never get inserted as it will fail before that.

Well the code base used is for a private commercial product so I can't necessarily post code snippets. I tried to recreate the bug using sample tables on the same server but found it did increment correctly each time which leads me to believe that this is a code bug and not a problem with the server itself.

I can tell you that the software is web based, written in php and that I can recreate the bug by making the insert on the second table fail by adding a dummy column to the insert.

So I insert in to orders successfully but the insert into the people table fails because I tell it to set asdf to '' and there is no asdf field name.

The orders table entry rolls back (it doesn't still have an order sitting out there). So I suspect the roll back is getting called appropriately. It, however, does NOT increment the id field in the orders table.

That's where I have to scratch my head.

Other things I can note:

We have a secure database for storing credit card information. This is stored on another server. I start a transaction on both database servers. If either server has a query that fails it rolls back both transactions on both servers.

Some of our tables are MyISAM. These do not roll back. This is the source of our problem. We end up with potentially having some tracking data that belongs to the failed order rather than the one that was processed successfully.

Again, any help is appreciated.

A quick question from PHP are you sending all the SQL statements for the transaction as one string or are SQL statements and executed one at a time? If it is all at once it might be that the SQL server attempts to parse the entire set of statements and the fact that one of them has a syntax error causes none of the statements not to execute.

Turns out it was my own user error. Everything is working as expected.

Good to hear!!