MySQL index unique error aumenta el valor del auto increment [Solucionado]

Estamos trabajando en un script con base de datos en MySQL y terminamos de diseñar nuestra tabla. Preparamos todo lo necesario para la inserción de datos con un index unique (para que cierto valor no se pueda repetir) y nos encontramos que cuando hay un elemento duplicado no se inserta pero sí que sume el número de nuestro campo AUTO INCREMENT.

Este problema puede parecer una tontería, pero aun así a más de uno le habrá traído de cabeza y le habrá hecho perder el tiempo. Para que a tí no te pase vas a tener que tomar una de estas medidas que te propongo.

Utiliza una tabla en modo MyISAM para evitar auto increment con registros existentes

Si estás en un hosting normal o compartido, posiblemente no tengas acceso a los archivos del sistema, o simplemente no entiendas sobre esto y no te quieras arriesgar a «cargarte» algo del servidor. La forma más fácil va a ser que revises el sistema que utiliza la tabla que has creado y que posiblemente sea InnoDB (ahora por defecto en MySQL).

Para este tipo de tablas hay alguna opción que veremos abajo como modificar, que provocan que al hacer un INSERT sobre una tabla en la que tenemos un INDEX UNIQUE para un campo determinado, y este valor ya exista no realizando el insert, nos encontremos con que igualmente aumenta el valor de la id (o el que tengamos con auto increment), lo que provoca que cada vez que un registro ya exista haya un salto de id y no sean corelativas.

En este caso, lo más fácil va a ser cambiar el motor de esa tabla que provoca saltos de ids a MyISAM, ya que este tipo de tabla no aumenta el valor del auto increment antes de realizar el insert.

Evitar auto increment en campos existentes de InnoDB con index unique

La otra opción, pero un poco más compleja ya que tendremos que editar el archivo de configuración de mysql (normalmente /etc/mysql/my.cnf), pasa por añadir un parámetro a la configuración de InnoDB para que no realize esta modificación del Auto Increment hasta que no haya realizado el insert de forma correcta.

1. Para ello entraremos por SSH
   (o sftp) a nuestro servidor (si es local simplemente buscamos el archivo my.cnf), y tenemos dos opciones para encontrar el my.cnf; una es escribir en la consola lo siguiente:

   updatedb
   
   locate my.cnf

   la primera línea acutalizará nuestra base de datos de archivos del sistema para que esté la lista de todos los archivos, y la segunda buscará y nos mostrará la ruta a my.cnf

   La segunda opción es probar en /etc/mysqld/my.cnf o /etc/mysql/my.cnf y posiblemente una de las dos sea donde tenemos guardado el archivo de configuración que editaremos con nano:

   nano /etc/mysql/my.cnf

2. Una vez abierto nos vamos a la sección [mysqld]
   y en una línea debajo colocaremos el siguiente código

   innodb_autoinc_lock_mode = 0

   siempre con una sola configuración por línea. Esto cambiará el modo de gestión que hemos comentado de nuestras tablas InnoDB.

3. Ahora solo nos queda reiniciar el servidor MySQL

   service mysqld restart

   o

   service mysql restart

   y ya empezará a funcionar de forma correcta según nuestras necesidades.

¿Te ha servido?¿Cómo lo has solucionado tú?