Innodb auto increment - did it reset itself automatically?

Dev Shed Forums Sponsor:

February 11th, 2005, 10:16 PM

rishijd

Contributing User

Join Date: Feb 2004

Posts: 337

Time spent in forums: 3 Days 1 h 57 m 10 sec
Reputation Power: 10

Innodb auto increment - did it reset itself automatically?

Hi,

My database is mostly made up of MyIsam tables, and some InnoDB tables.

One particular Innodb table works fine with an auto increment field. The table is updated often, records being added and deleted at pretty much the same rate.

So, there are only a very few records in the table at any given time.

It was being used and the "auto increment" value was around 21. Recently, after the records were deleted in the system (by my client - through a database system that I created - NOT directly via the db), I noticed that new records to the table start with the auto increment field '1'.

I don't understand this. The autoincrement field seems to have reset itself back to 0. I know this because there are now 3 records in that table with ids 1,2,3

I tested it again by adding a new record, which was assigned the id 4. Then, i deleted that, and added a new record, which was assigned the id 5.

So its working as normal again! I just dont understand how Mysql reset the autoincrement field from 21 back to 0

Can anyone think of a reason why this would happen? I'm really worried about the database now

Thanks very much!

February 12th, 2005, 07:14 AM

Guelphdad

Hockey face

Dev Shed God 7th Plane (8000 - 8499 posts)

Join Date: Nov 2001

Location: St. Catharines, Canada

Posts: 8,141

Time spent in forums: 2 Months 3 Weeks 3 Days 20 h 34 m 13 sec
Reputation Power: 1315

Did you delete the values or truncate the table? IIRC truncating will restart the autoincrement at 1 (you can't have a value of 0 for an autoincrement field), whereas deleting the rows will not have an effect on the autoincrement field.

February 12th, 2005, 08:22 AM

rishijd

Contributing User

Join Date: Feb 2004

Posts: 337

Time spent in forums: 3 Days 1 h 57 m 10 sec
Reputation Power: 10

Thanks Guelphdad
I didnt truncate the table, I dont even know what that means

However, i'm pretty sure my client deleted all the records in that table (through his table).

I received a reply on this from the MySql mailing list - and they said to look here:
http://dev.mysql.com/doc/mysql/en/i...ent-column.html

I don't really understand all that tech stuff, but one line of this document caught my attention:

The auto-increment counter is stored only in main memory, not on disk.
So the server was probably restarted after the table was empied. Thats what I can gather.

They also mention:

The value retrieved by the statement is incremented by one and assigned to the column and the auto-increment counter of the table. If the table is empty, the value 1 is assigned.

Not sure if they mean 'if the table is empty for the first time' or 'if the table is empty, at any given time'

Anyway, I think this all means that I have nothing to worry about.. if you have any opinion please let me know. Thanks very much!

November 15th, 2012, 03:08 PM

Gremio

Registered User

Join Date: Nov 2012

Posts: 1

Time spent in forums: 23 m 18 sec
Reputation Power: 0

I know this is an old thread, but I wanted to provide some clarification just in case anyone stumbles across this as I did looking for an answer.

This is indeed an issue from the following statement:
The auto-increment counter is stored only in main memory, not on disk.
Updated Link:
hxxp://dev.mysql.com/doc/refman/4.1/en/innodb-auto-increment-handling.html
(Apparently as a new user I'm not allowed to properly include URLs.... :/)

Because of this when the service (or server) restarts the following will happen:

Quote:

After a server startup, for the first insert into a table t, InnoDB executes the equivalent of this statement:
SELECT MAX(ai_col) FROM t FOR UPDATE;

InnoDB increments by one the value retrieved by the statement and assigns it to the column and to the auto-increment counter for the table. If the table is empty, InnoDB uses the value 1.

So in plain English, after the MySQL service starts it has no idea what the auto-increment value for your table should be. So when you first insert a row, it finds the max value of the field that uses auto-increment, adds 1 to this value, and uses the resulting value. If there are no rows, it will start at 1.

This was a problem for us, as we were using the table and mysql's auto-increment feature to neatly manage IDs in a multi-threaded environment where users were getting re-directed to a third-party payment site. So we had to make sure the ID the third party got and sent back to us was unique and would stay that way (and of course there's the possibility the user would cancel the transaction after they had been redirected).

So we were creating a row, obtaining the generated auto-increment value, deleting the row to keep the table clean, and forwarding the value to the payment site. What we ended up doing to to fix the issue of the way InnoDB handles AI values was the following:

PHP Code:


		
			
 $query = "INSERT INTO transactions_counter () VALUES ();";

mysql_query($query);

$transactionId = mysql_insert_id();

$previousId = $transactionId - 1;

$query = "DELETE FROM transactions_counter WHERE transactionId='$previousId';";

mysql_query($query);

This always keeps the latest transactionId generated as a row in the table.

Hope that helps anyone else that might run into this.

-Gremio

Innodb auto increment - did it reset itself automatically?

Developer Shed Advertisers and Affiliates