Reset Auto Increment for a Table - ERROR 1062 (23000): Duplicate entry '2147483647' for key 1

PROVSERVICE table on VMS ANYPROV has a column which is auto increment

 

+---------------------+--------------+------+-----+---------------------+----------------+

| Field               | Type         | Null | Key | Default             | Extra          |

+---------------------+--------------+------+-----+---------------------+----------------+

| PROVSERVICEID       | int(11)      | NO   | PRI | NULL                | auto_increment |

 

Development were getting an error suggesting an issue with the auto increment column

 

ERROR 1062 (23000): Duplicate entry '2147483647' for key 1

 

When we checked the next value for the auto increment field it was as follows

 

mysql> SHOW TABLE STATUS LIKE 'PROVSERVICE';

+-------------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-----------------+----------+----------------+---------+

| Name        | Engine | Version | Row_format | Rows    | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time          | Collation       | Checksum | Create_options | Comment |

+-------------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-----------------+----------+----------------+---------+

| PROVSERVICE | MyISAM |      10 | Dynamic    | 2025198 |            108 |   219489492 | 281474976710655 |    190609408 |         0 |     2147483648 | 2009-10-23 15:50:01 | 2009-10-23 16:12:06 | 2009-10-23 15:53:03 | utf8_general_ci |     NULL |                |         |

+-------------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-----------------+----------+----------------+---------+

1 row in set (0.00 sec)

 

Although the PROVSERVICEID column is int(11) it would appear that 2147483647 is the maximum allowable value.

 

When we checked the data in the table we found that although this was the max value for PROVSERVICEID in the PROVSERVICE table, the next value below it was 2043538

 

select max(PROVSERVICEID) from PROVSERVICE where PROVSERVICEID < 2147483647;

+--------------------+

| max(PROVSERVICEID) |

+--------------------+

|            2043538 |

+--------------------+

1 row in set (0.00 sec)

 

So somehow all values for PROVSERVICEID between 2043538 and 2147483647 had been missed.

 

To fix this, and to be able to use the missing values we had to

 

1. Update the row with PROVSERVICEID=2147483647 to have the next available value after 2043538

 

update PROVSERVICE set PROVSERVICEID=2043539 where PROVSERVICEID=2147483647;

 

2. Reset the auto increment value for the table

 

alter table PROVSERVICE auto_increment=1;

           

            This takes the current max value and adds 1 to give the next auto increment value

 

3. Check that auto increment value had been reset correctly

 

SHOW TABLE STATUS LIKE 'PROVSERVICE';

 

+-------------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-----------------+----------+----------------+---------+

| Name        | Engine | Version | Row_format | Rows    | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time          | Collation       | Checksum | Create_options | Comment |

+-------------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-----------------+----------+----------------+---------+

| PROVSERVICE | MyISAM |      10 | Dynamic    | 2025198 |            108 |   219489492 | 281474976710655 |    190608384 |         0 |        2043540 | 2009-10-23 16:34:42 | 2009-10-23 16:35:08 | 2009-10-23 16:37:45 | utf8_general_ci |     NULL |                |         |

+-------------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-----------------+----------+----------------+---------+

1 row in set (0.00 sec)