CODEWITHSUNDEEP
mysqlintprimary-keyauto-increment
FosAvance
FosAvance

Reputation: 2469

What happens when auto increment primary key in MySQL comes to maximum

I have auto increment, primary key in MySQL called ID and it is INT. As my app grows and I'm getting more users they are making more entries. What happens when my INT comes to its maximum value 2 147 483 647? Can I just switch to BIGINT?

Upvotes: 9

Views: 11625

Answers (3)

Samuel Casimiro
Samuel Casimiro

Reputation: 11

I would like to share a personal experience I just had about this.

Using Nagios + Check_MK + NDOUtils. NDOUtils stores all the checks in a table called nagios_servicechecks. The primary key is an auto_increment int signed.

What happens with MySQL when this limit is ranged? Well, in my case, MySQL deleted all the records but the last one. The table is now almost empty. Everytime a new record is inserted the old one is deleted.

I don't why this happens, but the fact is that I lost all my records.

IDOUtils, used with Icinga (not Nagios), fixed this issue changing int by a bigint.

Upvotes: 0

Cristian Meneses
Cristian Meneses

Reputation: 4041

Yes, you can alter the table and change from INT to BIGINT without problems.

Also, you may need to change the datatype wherever it is as an FK

Upvotes: 6

Halim Qarroum
Halim Qarroum

Reputation: 14103

The MySQL official documentation states that :

When the column reaches the upper limit of the data type, the next attempt to generate a sequence number fails. Use the UNSIGNED attribute if possible to allow a greater range.

And yes, you can switch the types of the auto_increment value. On this point, the documentation advises to use :

[...] the smallest integer data type for the AUTO_INCREMENT column that is large enough to hold the maximum sequence value you will need.

Upvotes: 5

Related Questions