25

I am doing a little research for a problem that might occur someday. Lets say you have an InnoDB MySQL table with an id and a name field. the id field has BIGINT(20) and is AUTO_INCREMENT plus its the primary key.

What do you do in a case that this table is full which means we have reached the limit on the id and no auto increment number can be generated anymore.

Lexib0y
  • 519
  • 10
  • 27
  • `code`tinyint: 1 byte, -128 to +127 / 0 to 255 (unsigned) smallint: 2 bytes, -32,768 to +32,767 / 0 to 65,535 (unsigned) mediumint: 3 bytes, -8,388,608 to 8,388,607 / 0 to 16,777,215 (unsigned) int/integer: 4 bytes, -2,147,483,648 to +2,147,483,647 / 0 to 4,294,967,295 (unsigned) bigint: 8 bytes, -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 / 0 to 18,446,744,073,709,551,615 (unsigned)`code` – mohamad mohamad Sep 22 '17 at 13:06
  • 2
    Then you convert the bigint to an unsigned bigint. When that is full, you have probably set the record of having the most data in a MySQL database ever. Assuming you don't mess around with the auto_increment column and just let it do it's work. What I mean to say is, that you don't have to worry about that. Have you looked at how huge the highest possible bigint value is? I'd say that 90% of all database users never reach the upper limit of a 32bit integer in an auto_increment column. And you're talking about a 64bit value. – fancyPants Sep 22 '17 at 13:06
  • 2
    Your system will be sunset before you reach that number. – Juan Sep 22 '17 at 13:06
  • Thanks guys to all the answers. with the unsigned integer thats a good tip! thanks :) –  Sep 22 '17 at 13:21
  • You should be good with BIGINT(20), it's more likely the sun will expand and consume the earth before you reach the limit. – Max S. Jul 01 '23 at 10:32

3 Answers3

41

Let's assume a table structure like:

CREATE TABLE `tbl` (
    `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (`id`)
);

and INSERT queries like:

INSERT INTO tbl(id) VALUES (NULL);

In the real code there are also other columns in the table and they are also present in the INSERT query but we can safely ignore them because they don't bring any value to this specific issue.

When the value of column id reaches its maximum value no more rows can be inserted in the table using the query above. The next INSERT fails with the error:

SQL Error (167): Out of range value for column 'id'.

If there are gaps in the values of the id column then you can still insert rows that use values not present in the table but you have to specify the values for id in the INSERT query.

Anyway, if the type of your AUTO_INCREMENT column is BIGINT you don't have to worry.

Assuming the code inserts one million records each second (this is highly overrated, to not say impossible), there are enough values for the id column for the next half of million years. Or just 292,277 years if the column is not UNSIGNED.

I witnessed the behaviour on a live web server that was using INT(11) (and not UNSIGNED) as the AUTO_INCREMENTed PK for a table that records information about the visits of the web site. It failed in the middle of the night, after several years of running smoothly, when the visits number reached 2^31 (2 billions and something).

Changing the column type from INT to BIGINT is not a solution on a 2-billion records table (it takes ages to complete and when the system is live, there is never enough time). The solution was to create a new table with the same structure but with BIGINT for the PK column and an initial value for the AUTO_INCREMENT column and then switch the tables:

CREATE TABLE `tbl_new` (
    `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (`id`)
) AUTO_INCREMENT=2200000000;

RENAME TABLE `tbl` TO `tbl_old`, `tbl_new` TO `tbl`;
axiac
  • 68,258
  • 9
  • 99
  • 134
  • I have the same solution in my mind and now I have confirmation from you. Thanks – Lakhan Sep 25 '19 at 19:17
  • What exactly is the rename command doing? Is it merging "tbl" and "tbl_new"? – Xavier Mukodi Sep 10 '22 at 22:01
  • The statement `RENAME TABLE` does exactly what it says: it changes the name of an existing table. Here we need to rename `tbl_new` to `tbl` but the table `tbl` already exists and we still need it. That's why we first rename `tbl` to `tbl_old` then put `tbl_new` in place of `tbl`. There are two renames needed but using two `RENAME TABLE` statements to do them produces a gap in time during which a table named `tbl` does not exist and this can break other queries. By doing both renames with a single statement the operation is atomic and there is no gap during which the table `tbl` does not exist. – axiac Sep 11 '22 at 09:27
  • @XavierMukodi Read about the [`RENAME TABLE`](https://dev.mysql.com/doc/refman/8.0/en/rename-table.html) MySQL statement. – axiac Sep 11 '22 at 09:28
  • @axiac I understand that this is tried & tested and works, but I still don't get the intuition. We are basically just replacing ```tbl``` with a new and empty table. But our goal is to change our PK type to ```BIGINT```. Unless you omitted the part where you copy the data from ```tbl_old``` to ```tbl```. – Xavier Mukodi Sep 12 '22 at 12:15
  • In the last part of the answer I describe how we solved the problem in our situation, at 3 AM. Next day we updated the code to search for the values again in the old table if it could not find them in the new table and so on. Copying the data is not an option, not only at 3 AM but at any hour of the day. It takes ages and slows down the website. – axiac Sep 12 '22 at 12:47
5
tinyint: 1 byte, -128 to +127 / 0 to 255 (unsigned)
smallint: 2 bytes, -32,768 to +32,767 / 0 to 65,535 (unsigned)
mediumint: 3 bytes, -8,388,608 to 8,388,607 / 0 to 16,777,215 (unsigned)
int/integer: 4 bytes, -2,147,483,648 to +2,147,483,647 / 0 to 4,294,967,295 (unsigned)
bigint: 8 bytes, -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 / 0 to 18,446,744,073,709,551,615 (unsigned)

Did you think this number is a small number ? maybe you will be dead before you achieve this number

mohamad mohamad
  • 613
  • 9
  • 24
-1

Use id varchar(255) then generate it manually (eg. concatenate date in HH:mm:ss.SSS and table name)before inserting each record.

Cedrickkk
  • 1
  • If you have a new question, please ask it by clicking the [Ask Question](https://stackoverflow.com/questions/ask) button. Include a link to this question if it helps provide context. - [From Review](/review/late-answers/33909450) – Dhaval Purohit Feb 28 '23 at 17:51