Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I recently read that because of how InnoDB recalculates the AUTO_INCREMENT value when the server restarts, any records on the high end of the ID list may have their IDs reused.

Normally, this isn't a problem, because when a user is deleted everything associated with the ID is deleted from other tables too.

But I'm deliberately leaving their forum posts orphaned, labelled as "Posted by =User #123=", so that past conversations are retained. Clearly, should an ID be reused, this will be a problem.

I've never had this issue before because there were always enough new users to make it unlikely for an ID to be reused in this way. However on my new project signups are rare and inactive user deletions frequent (especially since the "Open Alpha" accounts only last for three days as a preview), and such ID reuse has happened three for three now.

I have "fixed" the issue by saving the correct value for AUTO_INCREMENT elsewhere and using that instead of relying on the internal value. Is there an actual way to have InnoDB remember the actual last value?

share|improve this question
    
Do you have the article you read? –  gbn Apr 17 '12 at 6:43
    
@gbn The link for the article dev.mysql.com/doc/refman/5.1/en/… –  Naveen Kumar Apr 17 '12 at 7:04
    
For the reference this is bugs.mysql.com/bug.php?id=199 –  Laurynas Biveinis May 6 '13 at 9:37
    
ALTER TABLE table_name ENGINE=MyISAM Workes for me. Our table is always kept very small, so no need for InnoDB. –  user35885 Mar 20 at 0:53
1  
@QuickFix You should add some details about why this works. –  Max Vernon Mar 20 at 4:56

4 Answers 4

(avoiding the issue by never deleting)

Since you want to keep the "Posted by =User #123=" information after you delete the user with id=123, you could also consider using 2 tables for storing users data. One for Active users and one for all (including deleted ones from the active users). And never delete those ids from the AllUser table:

CREATE TABLE AllUser
( user_id INT AUTO_INCREMENT
, ...
, PRIMARY KEY (user_id)
) ;

------
--- Forum posts FK should reference the `AllUser` table

CREATE TABLE ActiveUser
( user_id INT 
, ...
, PRIMARY KEY (user_id)
, FOREIGN KEY (user_id)
    REFERENCES AllUser (user_id)
) ;

------
--- All other FKs should reference the `ActiveUser` table

This will of course complicate the insert new user operation. Any new user will mean 2 inserts, one in each table. Deleting a user though will be by deleting from the ActiveUser table only. All FKs will be deleted with cascading, except the forum posts, which will be referencing the Alluser table (where no deleting will ever happen).

share|improve this answer

The 5.5 docs suggests storing the auto-increment value elsewhere as you already have.

An alternative solution would be to emulate a SEQUENCE so you don't use auto-increment in the actual table itself. This has been discussed on SO before and again. The MySQL Performance blog mentions it.

Yet another MySQL data screwing that other RDBMS don't have...

share|improve this answer

There is no natural way to do this except to use information_schema.tables to record all columns with auto_increment option.

You could collect those columns as follows:

CREATE TABLE mysql.my_autoinc ENGINE=MyISAM
SELECT table_schema,table_name,auto_increment
FROM information_schema.tables WHERE 1=2;
ALTER TABLE mysql.my_autoinc ADD PRIMARY KEY (table_schema,table_name);
INSERT INTO mysql.my_autoinc
SELECT table_schema,table_name,auto_increment
FROM information_schema.tables WHERE auto_increment IS NOT NULL;

Create a script that will reset the auto_increment values

AUTOINC_SCRIPT=/var/lib/mysql/ResetAutoInc.sql
mysql -u... -p... -AN -e"SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' AUTO_INCREMENT=',auto_increment,';') FROM mysql.my_autoinc" > ${AUTOINC_SCRIPT}

You could then do one of two things:

OPTION #1 : Run script manually after startup

mysql> source /var/lib/mysql/ResetAutoInc.sql

OPTION #2 : Have mysqld execute script before allowing connections

You would have to add this option

[mysqld]
init-file=/var/lib/mysql/ResetAutoInc.sql

That way, every time you restart mysql, this script is executed in the beginning. You will have to remember to regenerate /var/lib/mysql/ResetAutoInc.sql before doing a planned mysql restart.

share|improve this answer

Just don't delete the user. Relational integrity is more important. If you have to because of privacy reasons or whatever, simply change the username to 'deleted' and clear out any other fields.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.