Pourquoi L'auto-incrémentation MySQL augmente-t-elle sur les insertions échouées?

Un collègue m'a juste fait prendre conscience d'un comportement MySQL très étrange.

En supposant que vous avez une table avec un champ auto_increment et un autre champ qui est défini sur unique (par exemple un champ Nom d'utilisateur). Lorsque vous essayez d'insérer une ligne avec un nom d'utilisateur c'est déjà dans la table de l'insertion échoue, comme prévu. Pourtant, la valeur auto_increment est augmentée comme on peut le voir lorsque vous insérez une nouvelle entrée valide après plusieurs tentatives infructueuses.

Par exemple, lorsque notre dernière entrée ressemble à ce...

ID: 10
Username: myname

...et nous essayons cinq nouvelles entrées avec la même valeur de nom d'utilisateur sur notre prochain insert, nous aurons créé une nouvelle ligne comme ceci: 

ID: 16
Username: mynewname

Bien que ce ne soit pas un gros problème en soi, il semble être un vecteur d'attaque très stupide pour tuer une table en l'inondant de demandes d'insertion échouées, comme l'indique le manuel de référence MySQL:

"le comportement du mécanisme d'auto-incrément n'est pas défini si [...] la valeur devient plus grande que l'entier maximum qui peut être stocké dans le type entier."

Ce comportement est-il attendu?

36
demandé sur Sorcy 0000-00-00 00:00:00

4 réponses

InnoDB est un moteur transactionnel.

Cela signifie que dans le scénario suivant:

  1. Session A insère l'enregistrement 1
  2. Session B insère l'enregistrement 2
  3. Session A annule

, Il y a soit une possibilité d'écart, soit {[6] } se verrouille jusqu'à ce que session A soit validé ou annulé.

InnoDB les concepteurs (comme la plupart des autres concepteurs de moteurs transactionnels) ont choisi d'autoriser les lacunes.

De la la documentation:

Lors de l'accès au compteur d'incrémentation automatique, InnoDB utilise un verrou spécial au niveau de la table AUTO-INC qu'il conserve jusqu'à la fin de l'instruction SQL actuelle, pas jusqu'à la fin de la transaction. La stratégie de libération de verrou spécial a été introduite pour améliorer la concurrence pour les insertions dans une table contenant une colonne AUTO_INCREMENT

…

InnoDB utilise le compteur d'incrémentation automatique en mémoire tant que le serveur s'exécute. Lorsque le serveur est arrêté et redémarré, InnoDB réinitialise le compteur pour chaque table pour la première INSERT de la table, comme décrit précédemment.

Si vous avez peur que la colonne id s'enroule, faites-la BIGINT (8 octets de long).

27
répondu Quassnoi 2010-05-07 11:02:44

Sans connaître les internes exacts, je dirais oui, l'auto-incrément devrait permettre aux valeurs ignorées de faire des insertions d'échec. Disons que vous faites une transaction bancaire, ou autre où la transaction entière et plusieurs enregistrements vont comme un tout ou rien. Si vous essayez votre insert, obtenez un ID, puis tamponnez tous les détails suivants avec cet ID de transaction et insérez les enregistrements de détail, vous devez vous assurer de votre caractère unique qualifié. Si vous avez plusieurs personnes qui claquent la base de données, elles aussi devra s'assurer qu'ils obtiennent leur propre ID de transaction pour ne pas entrer en conflit avec le vôtre lorsque leur transaction est validée. Si quelque chose échoue lors de la première transaction, pas de mal fait et pas d'éléments pendants en aval.

5
répondu DRapp 2010-05-07 10:55:08

Ancien poste, mais cela peut aider les gens, Vous pouvez avoir à définir innodb_autoinc_lock_mode à 0 ou 2.

Les variables système qui prennent une valeur numérique peuvent être spécifiées comme --var_name=value sur la ligne de commande ou comme var_name=value dans les fichiers d'options.

Format de paramètre de ligne de commande: 

--innodb-autoinc-lock-mode=0

OU Ouvrez votre mysql.ini et ajouter la ligne suivante: 

innodb_autoinc_lock_mode=0
2
répondu DavidA 2018-06-05 09:49:36

Je sais que c'est un vieil article mais comme je ne pouvais pas non plus trouver la bonne réponse, j'ai trouvé un moyen de le faire. Vous devez envelopper votre requête dans une instruction if. Sa requête d'insertion ou d'insertion et sur les requêtes en double qui gâchent l'ordre d'incrément automatique organisé, donc pour les insertions régulières, utilisez: 

$check_email_address = //select query here\\

if ( $check_email_address == false ) {
    your query inside of here
}

Et au lieu de insérer et sur Dupliquer utilisez un mettre à jour la requête où dans ou à l'extérieur d'une instruction if n'a pas d'importance et un remplacer dans la requête aussi semble fonctionner

-2
répondu ThinkkSo 2014-06-24 07:01:20