table is full in innodb

Question

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