Mysql falls every day

Question

My database Mysql fallas every day:

Looking at the tail -100 /var/log/mysqld.log

Shows:

2020-08-16 20:56:03 7f787c7fb700  InnoDB: Error (Duplicate key) writing word node to FTS auxiliary index table.
2020-08-16 20:56:07 7f787c7fb700  InnoDB: Error (Duplicate key) writing word node to FTS auxiliary index table.
2020-08-16 20:56:09 7f787c7fb700  InnoDB: Error (Duplicate key) writing word node to FTS auxiliary index table.
2020-08-16 20:56:15 7f787c7fb700  InnoDB: Error (Duplicate key) writing word node to FTS auxiliary index table.
2020-08-16 20:56:16 7f787c7fb700  InnoDB: Error (Duplicate key) writing word node to FTS auxiliary index table.
2020-08-16 20:56:16 7f787c7fb700  InnoDB: Error (Duplicate key) writing word node to FTS auxiliary index table.
2020-08-16 20:56:16 7f787c7fb700  InnoDB: Error (Duplicate key) writing word node to FTS auxiliary index table.
2020-08-16 20:56:21 7f787c7fb700  InnoDB: Error (Duplicate key) writing word node to FTS auxiliary index table.

I think this table "anuncios" is the responsable this is my create table:

CREATE TABLE `anuncios` (
 `id` bigint(20) NOT NULL AUTO_INCREMENT,
 `id_usuario` int(11) DEFAULT NULL,
 `id_categoria` smallint(6) DEFAULT NULL,
 `id_provincia` smallint(6) DEFAULT NULL,
 `id_localidad` mediumint(9) DEFAULT NULL,
 `id_tipo` tinyint(1) DEFAULT '1',
 `id_referencia_ext` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
 `nombre` varchar(255) DEFAULT NULL,
 `precio` decimal(10,2) DEFAULT NULL,
 `descript` text CHARACTER SET latin1,
 `fecha_alta` datetime NOT NULL,
 `id_subcategoria` smallint(6) DEFAULT NULL,
 `id_microcategoria` smallint(6) DEFAULT NULL,
 `online` tinyint(1) DEFAULT NULL COMMENT '0=>offline, 1=>online, 2=>caducado, 3=>borrado, 4=>vendido, 5=>vendido_recien, 8=>desactivado_script, 9=>baneado',
 `vendido` tinyint(1) DEFAULT NULL,
 `patrocinado1` datetime DEFAULT NULL,
 `patrocinado2` datetime DEFAULT NULL,
 `patrocinado3` datetime DEFAULT NULL,
 `patrocinado_extras` varchar(255) CHARACTER SET latin1 NOT NULL COMMENT '"para inmovilla"=>"nombre agencia","tlf_listado"->patrocinado_listado',
 `website` char(1) CHARACTER SET latin1 DEFAULT 'C',
 `tops` int(1) DEFAULT NULL COMMENT 'permite poner un anuncio en top fijo o marcarlo como repetido',
 `email_web` varchar(80) CHARACTER SET latin1 DEFAULT NULL,
 `ultimo_mail_enviado` datetime NOT NULL,
 `filter_1` int(11) NOT NULL COMMENT 'm2, marca',
 `filter_2` int(11) NOT NULL COMMENT 'hab, modelo',
 `filter_3` int(11) NOT NULL COMMENT 'baños, km',
 `filter_4` int(11) NOT NULL COMMENT 'parking, ano',
 `filter_5` int(11) NOT NULL COMMENT 'Categoria Wallapop',
 `filter_6` varchar(255) NOT NULL COMMENT 'palabras_clave',
 `filter_7` date DEFAULT NULL,
 `filter_8` int(15) NOT NULL COMMENT 'número de fotos',
 `filter_9` float NOT NULL COMMENT '1=>Movil,3=>Wallapop, 9=>Crawleado del rastrillo',
 `filter_10` tinyint(1) NOT NULL COMMENT 'es anuncio de la app u.puntos',
 `filter_11` float(10,6) NOT NULL COMMENT 'Puede ser coordenadas LAT(x)',
 `filter_12` float(10,6) NOT NULL COMMENT 'Puede ser coordenadas LNG(y)',
 `foto_main` varchar(200) DEFAULT NULL,
 `profesional` tinyint(1) NOT NULL DEFAULT '0',
 `tipo_pago` tinyint(4) NOT NULL,
 `foto_main_length` int(20) NOT NULL,
 `foto_main_length_x` int(15) NOT NULL,
 `foto_main_length_y` int(15) NOT NULL,
 `modificado` datetime NOT NULL,
 `adm_verificado` tinyint(1) NOT NULL,
 `keywords_busqueda` text,
 PRIMARY KEY (`id`),
 KEY `id_categoria` (`id_categoria`),
 KEY `id_subcategoria` (`id_subcategoria`),
 KEY `fecha_alta` (`fecha_alta`),
 KEY `id_microcategoria` (`id_microcategoria`),
 KEY `id_usuario` (`id_usuario`),
 KEY `modificado` (`modificado`),
 KEY `foto_main` (`foto_main`),
 KEY `id_localidad` (`id_localidad`),
 KEY `id_provincia` (`id_provincia`),
 KEY `patrocinado1` (`patrocinado1`),
 KEY `patrocinado2` (`patrocinado2`),
 KEY `patrocinado3` (`patrocinado3`),
 KEY `geo` (`filter_11`,`filter_12`),
 KEY `id_tipo` (`id_tipo`),
 KEY `id_ref_ext` (`id_referencia_ext`),
 KEY `website_` (`website`),
 KEY `online` (`online`),
 KEY `filter_9` (`filter_9`),
 FULLTEXT KEY `busqueda_keywords` (`keywords_busqueda`),
 FULLTEXT KEY `nombre` (`nombre`)
) ENGINE=InnoDB AUTO_INCREMENT=9223372036854775808 DEFAULT CHARSET=utf8 COMMENT='Activa - Anuncios principal'

I´m so desperate where to look, something wrong?

Answer 1

Quite a bit late to the question, but the issue appears to be with the primary key: you've run out of numbers.

A BIGINT is 8 bytes in length and can store values from -9223372036854775808 to 9223372036854775807 when SIGNED or from 0 to 18446744073709551615 when UNSIGNED. Looking at the AUTO_INCREMENT value for the anuncios, the next value is 9223372036854775808, which is 1 higher than the maximum supported by BIGINT.

There are a couple of options here (for anyone who has a similar issue in the future):

1. Change the id from SIGNED to UNSIGNED

   ALTER TABLE `anuncios` MODIFY COLUMN `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT;
   

   Note: This may take a long time depending on how much data and how many indexes are associated with the table. This may also be rejected by the SQL engine if there are any foreign keys that reference anuncios.id, as they will need to be converted to an unsigned BIGINT as well.

2. If the table sees a lot of deletes, then a BEFORE INSERT trigger can be created to determine an unused id that can be assigned to the new record. This will come with a noticeable performance hit, though.
3. Rather than use a positive ID, start using negative IDs via a BEFORE INSERT trigger that determines MIN(id) and decrements by one. Like the previous option, this would also have a performance hit and may run into issues with LAST_INSERT_ID().
4. If the id is not referenced by other tables, then creating a new table and copying the data over could buy you some more time:

   RENAME TABLE `anuncios` TO `anuncios_old`;
   CREATE TABLE `anuncios` ...
   INSERT INTO `anuncios` ... FROM `anuncios_old` ...
   DROP TABLE `anuncios_old`;
   

   Note: If you go with this option, this would be an opportune time to use BIGINT UNSIGNED, unless you plan on having id values less than 0.

5. If the id is not referenced by other tables, then change the data type to a DECIMAL(38,0) (the largest whole number supported by MySQL) and use a BEFORE INSERT trigger to determine the next increment (because AUTO_INCREMENT does not work with DECIMAL data types). Like the other options that involve a BEFORE TRIGGER, there will be a performance hit. However, with 38 digits to work with, you'll be "safe" up to 99,999,999,999,999,999,999,999,999,999,999,999,999 + 1 ... which would be 100 undecillion 🤐