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.

One of the table t_keyword_stat_ga in the my DB on server(currently the master) was set as INT for a column id. This column is also set as the auto_increment. As noticed today the INT column reached its maximum allowable range for a value i.e. 2147483647 (the column by default is signed). Now the auto_increment detects the maximum value, but it doesnt have a mechanism to report any error or warnings related to that, instead as noticed later, it inserted new records with the same id value i.e. 2147483647, for almost 1.77Million records.

Following this, updates queries ran updating any row with id 2147483647, unfortunately there were 1.7M of them, this caused all those statement to be logged in binary logs in "Row" format (current binlog format is mixed, however mysql will choose "row" based for Update statements). This instantly filled up the disks containing binary logs (every update changed 1.7M rows). And the slaves were unable to cope up with this much data, and that filled up the relay logs volume on them too. This caused us to reset the slaves with a plan to re-build them again in future (Once master is fixed).

We have changed the id column type from INT to BIGINT to allow a larger range for the integer values. However, the auto_increment behavior made us to execute the Alter again to initialise it to a proper start point i.e. 2147483648. As per the mysql documentation the auto_increment will get the maximum value currently in the column and then start with that value + 1. However, when we tested the same behavior with a test table, we found that auto_increment will start with value as follows :

start value = (current max value for the column) + (number of rows in the table) + 1

This unusual behavior could have resulted in large gaps in auto_increment, which would not be beneficial to client (especially with any queries where they use a range condition based on id column).

share|improve this question
    
Please help me to how to fix this issue .i cant able to alter because it is a huge data table. –  ashuthosh Feb 11 '13 at 11:57
1  
What version of MySQL do you have? The docs for 5.1 say that once the autoincrement max has been reached, stuff should fail not insert with the same max id. (Please also clean up your tags or title. One says MyISAM, the other InnoDB, and you talk about something called DB2 in the text which is confusing.) –  Mat Feb 11 '13 at 13:59
1  
Are there FKs in other tables that are referencing this id? –  ypercube Feb 11 '13 at 15:04
1  
Just checking your third paragraph: You did run ALTER TABLE tblname AUTO_INCREMENT=2147483648; ??? –  RolandoMySQLDBA Feb 11 '13 at 17:03
1  
Please run SHOW CREATE TABLE t_keyword_stat_ga\G and post it in the question body. –  RolandoMySQLDBA Feb 11 '13 at 21:15
show 2 more comments

Your Answer

 
discard

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

Browse other questions tagged or ask your own question.