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).

  • Please help me to how to fix this issue .i cant able to alter because it is a huge data table.
    – ashuthosh
    Feb 11, 2013 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, 2013 at 13:59
  • 1
    Are there FKs in other tables that are referencing this id? Feb 11, 2013 at 15:04
  • 1
    Just checking your third paragraph: You did run ALTER TABLE tblname AUTO_INCREMENT=2147483648; ??? Feb 11, 2013 at 17:03
  • 1
    Please run SHOW CREATE TABLE t_keyword_stat_ga\G and post it in the question body. Feb 11, 2013 at 21:15

1 Answer 1

-- Step 1: Backup Your Data
-- Make sure to backup your data before proceeding with any changes to the table structure.

-- Step 2: Alter the Column Type
-- Change the column type from INT to BIGINT to accommodate larger values.

-- Step 3: Reset Auto-increment Value
-- Reset the auto-increment value to start from a desired value to prevent gaps.
ALTER TABLE t_keyword_stat_ga AUTO_INCREMENT = 2147483648;

-- Step 4: Monitor Inserts
-- After making the changes, monitor inserts into the table to ensure they're functioning as expected.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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