auto_increment at the top mariadb

Question

auto_increment at the top mariadb

Navigation

#1 by (0 votes)
#2 by (0 votes)

0

I am creating an information system fincareport.com and one of my doubts that I have something thoughtful is that it happens with the tables where the column has id with auto_increment of typo Int reaches its maximum value. In case you change the Int type by BigInt what happens if it also reaches the maximum value.

How to reuse the deleted id ?, ie how to make the ids (2,3,4) re-take the auto_increment and not continue with the 6 until it uses (2,3,4). Example

nodejs mariadb sequelize.js backend

asked by Julio Cesar Caicedo 16.02.2018 в 17:14

source

2 answers

Save a file in the database without creating a virtual directory Android markers in a distance radius

score 0 · Answer 1

Well the top for an INT in mariadb is 2147483648, those are really many records so I do not think you'll need so many.

Anyway, if there was to be a case in which that day come, the database would give you an error Error Code: 167. Out of range value for column 'id' at row 1 you can make an empirical test on your own with the following SQL:

CREATE TABLE t(
    id INT AUTO_INCREMENT PRIMARY KEY,
    val CHAR(1)
);

ALTER TABLE t AUTO_INCREMENT=2147483648;


INSERT INTO t(val) VALUE('a');

A solution for the reuse of ids can be to reassign the ids, go register by record giving a new id to each one and then reset the auto_incremente to the value of the last id that you have recreated. Although it can become cumbersome if that table has many relationships.

score 0 · Answer 2

You have two options:

You could use BITINT UNSIGNED that your cap is 18446744073709551615, for another side configures your monitoring tool and alerts to send you an alert when it reaches a certain percentage and so it does not catch you off guard and you will have time to act.

Define the PRIMARY KEY that uses UUID . I do not recommend it.

First use the BITINT UNSIGNED option that will give you a lot of time to live.