Il particolare comportamento dell’auto-increment nelle tabelle MySQL InnoDB

Quando si creano tabelle MySQL è abbastanza comune utilizzare una colonna ID come chiave primaria dichiarata AUTO_INCREMENT in modo che si incrementi automaticamente.

CREATE TABLE `user` (
 `id` INT AUTO_INCREMENT,
 first_name VARCHAR(255) NOT NULL, 
 last_name VARCHAR(255) NOT NULL, 
 PRIMARY KEY (`id`)
} ENGINE=INNODB;

L’uso dell’auto-incremento di un’entità può essere problematico, se si desidera farvi riferimento in un altro database.

Ho notato che MySQL ha avuto un comportamento particolare con questi incrementi automatici sulle tabelle InnoDB: in sostanza quando si riavvia il service, MySQL ricalcolerà il successivo valore delle colonne dichiarate AUTO_INCREMENT di tutte le tabelle.

In MySQL 5.7 e precedenti, il contatore di incremento automatico è memorizzato solo nella memoria principale, non su disco.

https://dev.mysql.com/doc/refman/5.7/en/innodb-auto-increment-handling.html

Dopo il riavvio del server, per il primo inserimento in una tabella table_name, InnoDB eseguirà l’equivalente della seguente istruzione: SELECT MAX(ai_col) FROM table_name FOR UPDATE;

InnoDB incrementa di 1 il valore recuperato dalla query sopra, lo assegna alla colonna e al contatore di auto-increment della tabella. Se la tabella è vuota, InnoDB utilizza il valore 1.

Quindi, in parole povere, dopo l’avvio del service, MySQL non ha idea di quale dovrebbe essere il valore per la colonna dichiarata AUTO_INCREMENT e, quando inserisce il primo record, trova il valore sommando 1 al valore massimo della colonna.

Di seguito un esempio:

1. Inseriamo due utenti nella tabella user dichiarata sopra

INSERT INTO `user` VALUES (NULL, 'Maico', 'Orazio');
INSERT INTO `user` VALUES (NULL, 'Mario', 'Rossi');
SELECT * FROM `user`;

output

+ — — + — — — — — — + — — — — — — +
| id  | first_name  | last_name   |
+ — — + — — — — — — + — — — — — — +
| 1   | Maico       | Orazio      |
| 2   | Mario       | Rossi       |
+ — — + — — — — — — + — — — — — — +

2. Eliminiamo l’ultimo utente inserito e inseriamone uno nuovo

DELETE FROM `user` WHERE id = 2;
INSERT INTO `user` VALUES (NULL, ‘Giuseppe’, ‘Verdi’);
SELECT * FROM `user`;

output

+ — — + — — — — — — + — — — — — — +
| id  | first_name  | last_name   |
+ — — + — — — — — — + — — — — — — +
| 1   | Maico       | Orazio      |
| 3   | Giuseppe    | Verdi       |
+ — — + — — — — — — + — — — — — — +

3. Eliminiamo l’ultimo utente inserito

DELETE FROM `user` WHERE id = 3;
SELECT * FROM `user`;

output

+ — — + — — — — — — + — — — — — — +
| id  | first_name  | last_name   |
+ — — + — — — — — — + — — — — — — +
| 1   | Maico       | Orazio      |
+ — — + — — — — — — + — — — — — — +

Visualizziando il DDL della nostra tabella user, vediamo che il prossimo auto-increment utilizzato sarà 4

SHOW CREATE TABLE `user`;
| user | CREATE TABLE `user` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 first_name VARCHAR(255) NOT NULL, 
 last_name VARCHAR(255) NOT NULL, 
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1

4. Riavviamo il service MySQL

service mysql restart

5. Verifichiamo il prossimo valore di auto-increment

| user | CREATE TABLE `user` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 first_name VARCHAR(255) NOT NULL, 
 last_name VARCHAR(255) NOT NULL, 
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1

MySQL ha ricalcolato il valore del successivo auto-increment disponibile (AUTO_INCREMENT=2).
Il prossimo utente inserito avrà come id il valore 2; potremmo quindi ritrovarci con uno storico dati di un utente che non lo riguarda.

Una possibile soluzione

Utilizziamo il valore di incremento automatico solo nel database in cui è stato generato, ad esempio come chiave esterna.
Troviamo un altro modo per fare riferimento alle entità in modo univoco valido anche per altri database: utilizziamo un UUID.

CREATE TABLE `user` (
 `id` INT AUTO_INCREMENT,
 uuid varchar(36) NOT NULL,
 first_name VARCHAR(255) NOT NULL, 
 last_name VARCHAR(255) NOT NULL, 
 PRIMARY KEY (`id`)
} ENGINE=INNODB;

È possibile utilizzare la libreria ramsey/uuid in PHP per generare
questo UUID prima di inserirlo nel database.

Sembra che il problema sia stato risolto in MySQL 8.0, supportando
la persistenza del valore auto-increment durante i riavvii https://dev.mysql.com/worklog/task/?id=6204

Buon lavoro 👨‍💻