index - mysql phpmyadmin auto_increment

¿Por qué el autoincrement de MySQL aumenta en las inserciones fallidas?

Question

Un compañero de trabajo me hizo conocer un comportamiento MySQL muy extraño.

Suponiendo que tiene una tabla con un campo auto_increment y otro campo que está configurado como único (por ejemplo, un campo de nombre de usuario). Al intentar insertar una fila con un nombre de usuario que ya está en la tabla, la inserción falla, como se esperaba. Sin embargo, el valor de auto_increment aumenta como se puede ver cuando inserta una nueva entrada válida después de varios intentos fallidos.

Por ejemplo, cuando nuestra última entrada se ve así ...

ID: 10
Username: myname

... y probamos cinco nuevas entradas con el mismo valor de nombre de usuario en nuestra próxima inserción, crearemos una nueva fila como esta:

ID: 16
Username: mynewname

Si bien este no es un gran problema en sí mismo, parece un vector de ataque muy tonto para matar una tabla inundándola con solicitudes de inserción fallidas, como dice el Manual de Referencia de MySQL:

"El comportamiento del mecanismo de incremento automático no se define si [...] el valor se vuelve más grande que el entero máximo que se puede almacenar en el tipo de entero especificado".

¿Es este comportamiento esperado?

Answer 1

Sé que este es un artículo antiguo, pero como tampoco pude encontrar la respuesta correcta, encontré la forma de hacerlo. Tienes que ajustar tu consulta dentro de una declaración if. Por lo general, inserta una consulta o inserción y en consultas duplicadas que arruinan el orden organizado de incrementos automáticos, por lo que para el uso de inserciones regulares:

$check_email_address = //select query here//

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

y en lugar de INSERTAR Y EN DUPLICAR utilice un ACTUALIZAR CONJUNTO DONDE LA CONSULTA dentro o fuera de una instrucción if no es importante y un REEMPLAZAR EN LA CONSULTA también parece funcionar

Answer 2

Sin conocer las partes internas exactas, diría que sí, el autoincremento DEBERÍA permitir que los valores omitidos hagan inserciones de falla. Digamos que está haciendo una transacción bancaria, u otra donde toda la transacción y múltiples registros van como un todo o nada. Si prueba su inserción, obtenga una ID, luego selle todos los detalles subsiguientes con esa ID de transacción e inserte los registros de detalles, debe asegurarse de que su exclusividad calificada. Si tiene varias personas que cierran de golpe la base de datos, ellos también deberán asegurarse de obtener su propia identificación de transacción para no entrar en conflicto con la suya cuando se compromete la transacción. Si algo falla en la primera transacción, no se produce ningún daño ni elementos que cuelguen río abajo.

Answer 3

innodb_autoinc_lock_mode , pero esto puede ayudar a las personas. Es posible que innodb_autoinc_lock_mode establecer innodb_autoinc_lock_mode en 0 o 2 .

Las variables del sistema que toman un valor numérico se pueden especificar como --var_name=value en la línea de comando o como var_name=value en los archivos de opciones.

Formato de parámetro de línea de comandos:

--innodb-autoinc-lock-mode=0 

O abra su mysql.ini y agregue la siguiente línea:

innodb_autoinc_lock_mode=0

Answer 4

InnoDB es un motor transaccional.

Esto significa que en el siguiente escenario:

1. Session A inserta el registro 1
2. Session B inserta el registro 2
3. Session A retrocede

, existe la posibilidad de una brecha o la session B se bloquearía hasta que la session A comprometido o revertido.

InnoDB diseñadores de InnoDB (como la mayoría de los otros diseñadores de motores transaccionales) optaron por dejar vacíos.

De la documentation :

Al acceder al contador de incremento automático, InnoDB utiliza un bloqueo AUTO-INC nivel de tabla especial que mantiene hasta el final de la instrucción de SQL actual, no hasta el final de la transacción. Se introdujo la estrategia especial de liberación de bloqueo para mejorar la concurrencia de inserciones en una tabla que contiene una columna AUTO_INCREMENT

...

InnoDB usa el contador de incremento automático en la memoria siempre que el servidor se ejecute. Cuando el servidor se detiene y se reinicia, InnoDB reinicia el contador de cada tabla para el primer INSERT a la tabla, como se describió anteriormente.

Si tiene miedo de que la columna de id se BIGINT , BIGINT (8 bytes de longitud).