Mysql table running out of ids

Question

We have a mysql table that is running out of ids soon (the primary id is a 32-bit integer and the max id is already about 1.5 billion). What is the best way to fix this issue? Alter the id type to 64-bit is an option but that would bring down the database for too long because the table has billion of rows.

What is the actual datatype of that column used in your schema — RiggsFolly, Jan 23 '16 at 01:39
Let's talk about what you are doing to run out of ids. Do you have 1.5 billion rows? Or are most of the ids deleted? Burned? Otherwise lost? It _may_ be possible to slow down the consumption so that you won't run out. — Rick James, Jan 23 '16 at 23:07

score 3 · Answer 1 · answered Jan 23 '16 at 01:41

3

All versions of MYSQL (both 32bit and 64bit) use an 8 byte field for a column defined as BIGINT.

So amend the column to be BIGINT

Check the documentation

answered Jan 23 '16 at 01:41

RiggsFolly

93,638
21
103
149

As stated in my question, amend the column type of such a big table would require a very long down time for our db server. – Pinch Jan 25 '16 at 20:10
Well it does not look like you have the option of doing nothing. – RiggsFolly Jan 26 '16 at 01:02

score 3 · Answer 2 · answered Jan 23 '16 at 02:34

3

then try changing the type of the field. Try some type with a big range like "BIGINT".

answered Jan 23 '16 at 02:34

Iresha Rubasinghe

913
1
10
27

Mysql table running out of ids

2 Answers2