Sauts dans l'auto-incrément

**CinePhil** · 05/09/2017, 09h23

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 ?

**Artemus24** · 01/10/2017, 15h12

Salut Cinephil.

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

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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.

@+

**CinePhil** · 01/10/2017, 23h28

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

**Artemus24** · 02/10/2017, 10h41

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.

@+

**SQLpro** · 02/10/2017, 14h20

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 +

**Artemus24** · 02/10/2017, 18h45

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.

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.

@+

**CinePhil** · 03/10/2017, 10h19

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.

**Artemus24** · 03/10/2017, 10h24

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.

@+

**Dendrite** · 01/06/2023, 18h05

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 ?

**Séb.** · 01/06/2023, 18h51

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/

**CinePhil** · 02/06/2023, 11h15

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.

**Dendrite** · 02/06/2023, 17h19

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.

**Dendrite** · 02/06/2023, 23h49

Voici la réponse que j'ai obtenu.

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.

Sauts dans l'auto-incrément

Administration MySQL

Vue hybride

Discussions similaires

Partager

Partager