mysql,auto-increment – Reached Maximum Value for Auto Increment in MySQL

auto-incrementMySQL

I'm using a MySQL database. I have set an auto_increment function on "ID" column (which is also set as Primary key).

What will happen when it reaches its maximum possible value, according to the data type set for that column?

Can someone help me out what to do in such cases? Will it be handled automatically? Or will I have to handle it?

Best Answer

While inserting single record (by both INSERT .. VALUES and INSERT .. SELECT) you will obtain

Duplicate entry 'max_value_for_datatype' for key 'PRIMARY'

While inserting multiple records you will obtain (even when only one of them causes overflow)

Failed to read auto-increment value from storage engine

fiddle

Will it be handled automatically?

No. You must handle it - and the best way is to solve this issue before it occures.

PS. If you are interested in to know what messages will be produced while inserting record(s) by LOAD DATA/XML you may test it by yourelf.

Related Solutions

Mysql – Getting duplicate primary key for auto increment column

I reached the max possible value of an int column. Problem resolved by altering table and setting big int than int.

The signed range of MIDINT is –8388608 to 8388607. The unsigned range is 0 to 16777215

Reference: http://help.scibit.com/mascon/masconMySQL_Field_Types.html

MySQL Primary Key – Can a Complex Primary Key Have Only One Field Set to AUTO INCREMENT?

You can do this:

CREATE TABLE mytable
(
    `ID` MEDIUMINT NOT NULL AUTO_INCREMENT,
    `C1` MEDIUMINT NOT NULL,
    `C2` MEDIUMINT NOT NULL,
    `Year` MEDIUMINT NOT NULL,
    PRIMARY KEY (ID),
    FOREIGN KEY (C1, C2)
            REFERENCES othertable(pkcol1, pkcol2)
);

ALTER TABLE mytable ADD UNIQUE INDEX(`C1`, `C2`, `Year`, `ID`);

Adding the ID column to the unique index will guarantee that you can have the same values for C1, C2 and Year in your table. If you don't want that, remove ID from the ALTER TABLE statement.

The syntax of the FOREIGN KEY is for a multiple column key in another table called othertable. pkcol1 and pkcol2 would be the corresponding columns in that table.

ID would be your fourth column in the question example code you presented.

Best Answer

Related Solutions

Mysql – Getting duplicate primary key for auto increment column

MySQL Primary Key – Can a Complex Primary Key Have Only One Field Set to AUTO INCREMENT?

Related Question