Sauts dans l'auto-incrément

Version imprimable

05/09/2017, 09h23
CinePhil

Sauts dans l'auto-incrément
Bonjour,

Je viens de constater un phénomène curieux avec MariaDB qui saute des numéros dans l'auto-incrément après une insertion en masse.

J'ai fait un premier import de 131 lignes avec une requête du type :
Code:

1 2 3 INSERT INTO la_table (les_colonnes) SELECT les_colonnes FROM une_autre_table
Après l'import, j'ai constaté que l'auto-incrément était non pas à 132 mais à 256 !
J'ai "truncaté" la table, remis l'auto-incrément à zéro et j'ai recommencé l'import... idem !

Comme ce n'est qu'un identifiant technique, je me suis dit que ce n'est pas grave et j'ai fait un second import de 280 lignes (donc au total 411 lignes). La dernière ligne a pour identifiant 535 (255 + 280, c'est normal) mais AUTO_INCREMENT est monté cette fois à 767 !

Quelqu'un aurait une idée d'explication de ce phénomène plus étrange qu'embêtant ?

Salut Cinephil.

J'ai fait le test sous MariaDB 10.2.3, comme on peut le voir ci-après :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
--------------
SET AUTOCOMMIT = 0
--------------
 
--------------
START TRANSACTION
--------------
 
--------------
SHOW VARIABLES LIKE 'version'
--------------
 
+---------------+----------------+
| Variable_name | Value          |
+---------------+----------------+
| version       | 10.2.3-MariaDB |
+---------------+----------------+
--------------
DROP DATABASE IF EXISTS `base`
--------------
 
--------------
CREATE DATABASE `base`
    DEFAULT CHARACTER SET `latin1`
    DEFAULT COLLATE       `latin1_general_ci`
--------------
 
--------------
DROP TABLE IF EXISTS `bis`
--------------
 
--------------
CREATE TABLE `bis`
( `id`       integer unsigned not null auto_increment primary key,
  `libelle`  varchar(255)     not null
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `bis` (`libelle`) values
  ('un'),('deux'),('trois'),('quatre'),('cinq'),('six'),('sept'),('huit'),('neuf'),('dix')
--------------
 
--------------
select * from `bis`
--------------
 
+----+---------+
| id | libelle |
+----+---------+
|  1 | un      |
|  2 | deux    |
|  3 | trois   |
|  4 | quatre  |
|  5 | cinq    |
|  6 | six     |
|  7 | sept    |
|  8 | huit    |
|  9 | neuf    |
| 10 | dix     |
+----+---------+
--------------
SELECT AUTO_INCREMENT as last_id
FROM   INFORMATION_SCHEMA.TABLES
WHERE  table_schema = 'base' and table_name = 'bis'
--------------
 
+---------+
| last_id |
+---------+
|      11 |
+---------+
--------------
commit
--------------
 
--------------
DROP TABLE IF EXISTS `test`
--------------
 
--------------
CREATE TABLE `test`
( `id`       integer unsigned not null auto_increment primary key,
  `libelle`  varchar(255)     not null
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `test` (`libelle`)
  select libelle
    from bis
--------------
 
--------------
select * from `test`
--------------
 
+----+---------+
| id | libelle |
+----+---------+
|  1 | un      |
|  2 | deux    |
|  3 | trois   |
|  4 | quatre  |
|  5 | cinq    |
|  6 | six     |
|  7 | sept    |
|  8 | huit    |
|  9 | neuf    |
| 10 | dix     |
+----+---------+
--------------
commit
--------------
 
--------------
SELECT AUTO_INCREMENT as last_id
FROM   INFORMATION_SCHEMA.TABLES
WHERE  table_schema = 'base' and table_name = 'test'
--------------
 
+---------+
| last_id |
+---------+
|      16 |
+---------+
--------------
insert into `test` (`libelle`)
  select libelle
    from bis
--------------
 
--------------
select * from `test`
--------------
 
+----+---------+
| id | libelle |
+----+---------+
|  1 | un      |
|  2 | deux    |
|  3 | trois   |
|  4 | quatre  |
|  5 | cinq    |
|  6 | six     |
|  7 | sept    |
|  8 | huit    |
|  9 | neuf    |
| 10 | dix     |
| 16 | un      |
| 17 | deux    |
| 18 | trois   |
| 19 | quatre  |
| 20 | cinq    |
| 21 | six     |
| 22 | sept    |
| 23 | huit    |
| 24 | neuf    |
| 25 | dix     |
+----+---------+
--------------
SELECT AUTO_INCREMENT as last_id
FROM   INFORMATION_SCHEMA.TABLES
WHERE  table_schema = 'base' and table_name = 'test'
--------------
 
+---------+
| last_id |
+---------+
|      31 |
+---------+
--------------
COMMIT
--------------
 
--------------
SET AUTOCOMMIT = 1
--------------
 
Appuyez sur une touche pour continuer...

Il semble que l'auto incrément de la première table ("bis") ne soit pas impacté.
Tandis que la seconde table ("test"), l'auto_incrément est modifié en prenant la puissance de 2 qui lui est supérieure.
Par exemple, pour 10, cela donne 16, pour 17, cela donne 32.

@+

01/10/2017, 23h28
CinePhil

Ça confirme ce que j'ai constaté. Ça ressemble à un bug, je trouve... même si ce n'est pas grave.
02/10/2017, 10h41
Artemus24

Salut Cinephil.

En effet, quand on utilise le "insert ... select" nous rencontrons ce genre de problème.
Une solution consiste à renuméroter la colonne auto incrémenté après les insertions, mais c'est pas pratique comme rustine.

Par contre, aucun problème pour des insertions sur la même table.
Et à vrai dire, je n'ai pas approfondi la question.

@+
02/10/2017, 14h20
SQLpro
Citation:
Envoyé par CinePhil

Bonjour,

Je viens de constater un phénomène curieux avec MariaDB qui saute des numéros dans l'auto-incrément après une insertion en masse.

J'ai fait un premier import de 131 lignes avec une requête du type :

Code:

1 2 3 INSERT INTO la_table (les_colonnes) SELECT les_colonnes FROM une_autre_table

Après l'import, j'ai constaté que l'auto-incrément était non pas à 132 mais à 256 !
Probablement une gestion du cache de l'auto incrément totalement débile, come c'est souvent le cas avec MySQmerde...

A +
02/10/2017, 18h45
Artemus24

Salut à tous.

J'ai cru que le problème dépendait de la version que j'ai utilisé, à savoir "mariadb 10.2.3".
J'ai donc installé la dernière version "MariaDB 10.2.9", mais le problème persiste toujours.

J'ai trouvé plusieurs astuces pour obtenir un auto incrément avec la bonne valeur.
Je rappelle que nous sommes dans le cas d'un "insert ... select ...".

1) L'auto incrément aura une valeur correcte, si les insertions se font par paquet de quinze lignes.

2) utiliser "set insert_id = x" pour positionner l'auto incrément à la valeur désirée lors des prochaines insertions.

3) remettre la valeur de l'auto incrément à 1 "alter table `test` auto_increment=1", après chaque insertion de masse.
Cela permet à MariaDB de repositionner l'auto incrément à la valeur souhaitée.

Citation:

Envoyé par SQLPRO

comme c'est souvent le cas avec MySQmerde...

Sauf que ce n'est pas du mysql mais du mariadb, donc pas les même développeurs.

@+
03/10/2017, 10h19
CinePhil

Citation:

Envoyé par Artemus

Sauf que ce n'est pas du mysql mais du mariadb, donc pas les même développeurs.

Mais je pense que 90% du code de MariaDB vient de MySQL puisque c'est un fork de MySQL. ;)
03/10/2017, 10h24
Artemus24

Salut Cinephil.

Si vous m'avez suivi dans mes échanges concernant MySql, je n'aime pas dire du mal des développeurs et des produits qu'ils nous livrent.
Oui, je suis d'accord, il y a des bugs et ils pourraient prendre le temps de tester les nouvelles versions avant de nous les livrer.

@+
01/06/2023, 18h05
Dendrite

Bonjour !
Je fais remonter ce vieux sujet du temps où vous étiez jeunes et beaux.
Au boulot, j'ai le même problème (saut de 3 en 3), mais avec un insert des plus simples.
J'aime pas quand je comprends pas, alors j'ai demandé à l'équipe système de voir s'ils peuvent voir ce qui se passe, mais comme c'est pas un bug bloquant...
Vous avez eu des pistes ces 5 dernières années sur la question ? :pc:
01/06/2023, 18h51
Séb.

Je n'ai pas tout lu, veuillez m'en excuser, mais une bonne piste qui me semble ne pas avoir été abordée est innodb_autoinc_lock_mode qui selon sa valeur entrainera la consommation ou non d'ID autoincrémentés.

Par exemple un INSERT IGNORE INTO ... peut faire exploser le compteur si beaucoup de lignes sont rejetées.

=> https://mariadb.com/kb/en/auto_incre...ing-in-innodb/
02/06/2023, 11h15
CinePhil

Citation:

Envoyé par Dendrite

Au boulot, j'ai le même problème (saut de 3 en 3), mais avec un insert des plus simples.

Vite fait... N'y a t-il pas un paramètre qui définit le pas de l'auto-incrémentation ?
Ce que j'avais constaté à l'époque n'était pas un "saut de 3 en 3" mais des trous plus ou moins grands entre deux INSERT SELECT.
02/06/2023, 17h19
Dendrite

Merci de vos réponses mais pas d'insert ignore... Je vais questionner le pas de l'incrémentation sur le serveur. ce n'est pas moi qui ai la main.
02/06/2023, 23h49
Dendrite

Voici la réponse que j'ai obtenu.

Citation:

ce n'est pas un bug incompréhensible, c'est un fonctionnement normal.

Le nombre d'auto-incrémentation = au nombre de serveurs en cluster. Ceci est pour éviter que 2 serveurs, qui écrivent en même temps dans une table, attribuent la même clé principale à 2 entrées différentes d'une table.

Ici, nous avons 3 serveurs, donc, les 3 peuvent écrire simultanément dans une même table en étant sûre qu'ils attribueront chacun une clé principale différente.

J'me coucherai moins bête ce soir.
03/06/2023, 00h44
binarygirl

Merci pour ce retour. En cherchant des références à la documentation je trouve pas mal de liens comme ceux-cii:

https://dev.mysql.com/doc/mysql-shel...increment.html
https://mariadb.org/auto-increments-in-galera/

Donc, ce comportement est lié à la présence d'un cluster et j'imagine donc que ce n'est pas spécifique à Innodb.
03/06/2023, 03h57
Artemus24

Salut à tous.

Je viens de jeter un œil sur le fonctionnement de l'auto incrément et je trouve que c'est du grand n'importe quoi.
Si tu as trois nœuds, chacun aura un pas de trois dont le premier pas aura la valeur de nœud.
Cela va se traduire par :
--> nœud 1 : 1, 4, 7, 10, ...
--> nœud 2 : 2, 5, 8, 11, ...
--> nœud 3 : 3, 6, 9, 12, ...

Un nœud doit représenter un esclave dans le cas de la réplication master/slave.

C'est à croire qu'il ne savent pas du tout sérialiser une ressource dans ce genre de contexte.
Si le problème est la mise en attente, il existe d'autres astuces.
On peut concaténer le numéro du nœud avec un timestamp descendant au millionième de seconde.

Ce problème existe depuis l'invention des moniteurs transactionnels (CICS sous IBM ou TDS sous BULL).
Je me rappelle très bien que nous faisons un verrouillage sur la colonne stockée dans une table servant d'incrément.
Le verrouillage se faisant sur la lecture afin de savoir si la ressource était disponible.
Si elle n'était pas disponible, nous avons le choix entre tuer le traitement ou se mettre en attente jusqu'à la libération de la ressource.
Si la lecture avait pu se faire, nous verrouillons la ressource.
Nous faisons ensuite l'incrément et l'écriture et enfin nous déverrouillons la ressource.
Nous faisions cela dans un contexte où nous avions plusieurs milliers de terminaux qui accédaient aux mêmes traitements.
Cela n'a jamais posé aucun problème dans le fonctionnement.

Cordialement.
Artemus24.
@+
03/06/2023, 11h11
Dendrite

Merci de ton expertise Artemus.
Notre équipe système m'a confirmé que, travaillant sous le cluster Galera, c'était bien le comportement normal.
J'arriverais à vivre avec ça je crois. lol.
03/06/2023, 16h15
Artemus24

Salut Dendrite.

Ce n'est pas parce que c'est le comportement normal sous MySql que c'est la bonne façon de procéder.

Cordialement.
Artemus24.
@+
03/06/2023, 22h37
binarygirl

Je pense que c'est un choix technologique qui doit être dicté par des considérations de performances entre autres.
Dans les SGBD trouver le juste niveau de locking est justement une thématique où les arbitrages peuvent être délicats.

On pourrait aussi poser d'autres questions en rapport, par exemple: pourquoi SQL Server ne réutilise pas les ID supprimés.
En final, ça crée aussi des gaps mais ça ne devrait pas être gênant.
Là encore, ça touche à des questions de stratégie, de performances (et de travail de gestion).
03/06/2023, 23h46
Artemus24

Salut BinaryGirl.

Citation:

Envoyé par BinaryGirl

Je pense que c'est un choix technologique qui doit être dicté par des considérations de performances entre autres.

Oui, j'en conviens mais cela reste basique comme approche.

Citation:

Envoyé par BinaryGirl

On pourrait aussi poser d'autres questions en rapport, par exemple: pourquoi SQL Server ne réutilise pas les ID supprimés.

Ce n'est pas un problème lié à Microsoft SQL Server.
La recherche de l'ID supprimé peut prendre beaucoup de temps, bien plus que de faire une simple incrémentation.

Ensuite, qu'est-ce que l'on entend par suppression ?
Imaginons que j'archive la partie la plus ancienne de ma base de données.
Physiquement, les anciennes lignes ne sont plus présentes, mais elles existent toujours dans l'archivage.
Et voilà t'y pas que l'entreprise doit subir un contrôle fiscal.
On demande de réinstaller l'archivage, histoire de vérifier s'il n'y a pas eu des malversations.
En France, un contrôle fiscale d'entreprise remonter sur une période de dix ans. Un particulier sur trois ans.
Tu peux comprendre que la réaffectation n'est pas possible car tu vas mettre un bordel pas croyable dans ta base de données.
Un identifiant technique sera toujours incrémenté. On ne doit pas réutiliser une ancienne valeur.

Tu as exactement le même problème avec les numéros des comptes bancaires.
Quand un compte est clôturé, on ne peut plus le rouvrir, même pour le même bénéficiaire.

Rien ne t'empêche de renuméroter tes identifiants, mais à tes risques et périls.
Personnellement, je n'en vois pas l'intérêt car cela risque de prendre beaucoup de temps, juste pour résoudre un problème qui n'en est pas un.

Citation:

Envoyé par BinaryGirl

En final, ça crée aussi des gaps mais ça ne devrait pas être gênant.

Un gap est un intervalle entre deux enregistrements dans une bande magnétique (inter-record gap).
Je préfère plutôt les trous.
Ce n'est pas ce qui est le plus gênant, surtout si ton identifiant est du type "bigint".
Si tu déclares "bigint unsigned", les valeurs vont de 1 jusqu'à 2⁶⁴ - 1, soit 18 446 744 073 709 551 615.
Pour te donner une ordre de grandeur, si tu enregistres 100 lignes par secondes, cela va prendre 5 845 420 460 années avant d'atteindre la valeur maximale.
La durée de vie d'une application est en moyenne de dix ans.

Citation:

Envoyé par BinaryGirl

Là encore, ça touche à des questions de stratégie, de performances (et de travail de gestion).

De toutes façon, un SGBDR ne passe pas son temps à insérer des lignes. Il faut aussi les consulter.
On peut se permettre de sérialiser la demande d'incrémentation par la gestion d'une section critique (sous windows) ou d'un mutex (sous linux).
Cela ne va pas impacter les temps d'insertion dans la table.

Comme ce choix a été fait pour MySql, il faut vivre avec.

Cordialement.
Artemus24.
@+
08/06/2023, 09h54
escartefigue

Bonjour,

Citation:

Envoyé par binarygirl

On pourrait aussi poser d'autres questions en rapport, par exemple: pourquoi SQL Server ne réutilise pas les ID supprimés.
En final, ça crée aussi des gaps mais ça ne devrait pas être gênant.
Là encore, ça touche à des questions de stratégie, de performances (et de travail de gestion).

À ma connaissance, aucun SGBD ne réutilise les ID supprimés, en tout cas pour "boucher les trous".
D'une part ce serait très compliqué, il faudrait analyser en permanence les valeurs disponibles (supprimées par des DELETE, non commitées et donc jamais utilisées, "grillées" par un redémarrage du serveur...), sachant que ces différents phénomènes qui créent des trous se produisent en permanence.
Imaginez une table contenant des millions de lignes et dont celle de valeur "1" est supprimée, il faudrait alors renuméroter tous les identifiants suivants, non seulement dans cette table, mais aussi dans toutes celles dans lesquelles cet identifiant est propagé au moyen des clefs étrangères, on peut facilement dépasser le milliard de mises à jour totalement inutiles !

D'autre part ce serait totalement inutile, un ID attribué par le SGBD n'a aucun intérêt fonctionnel, c'est un identifiant technique dont on DOIT se foutre totalement de la valeur, il n'est pas chronologique et ne doit JAMAIS être utilisé dans un sens fonctionnel.

Pour rappel, certains SGBD distribuent les identifiants par paquets, ça évite de solliciter le moteur du SGBD en permanence.
Se faisant, si le serveur est arrêté, les ID en cache sont perdus.

Il faut aussi savoir que si MySQL/MariaDB ne proposent que des incréments, certains SGBD autorisent aussi le décrément.

La seule réutilisation possible des ID attribués par le SGBD, hors forçage manuel de la valeur lors de l'insert, est de boucler sur la colonne quand la valeur maximale (incrément) ou minimale (décrément) est atteinte.
Et encore, seuls certains SGBD le permettent, pas MySQL. Bien entendu, cette réutilisation n'est possible que si les valeurs ont été supprimées (delete par exemple), unicité oblige.

Voir mon billet de blog ICI au sujet de l'éternelle question sur les "trous" de numérotation.