0

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?

2
  • 2
    Have you tried FT indices rebuild? – Akina Aug 16 '20 at 19:15
  • 1
    Also you should check if a combined index would not be better., becuse i doubt that all your query only ask for one WHERE criteria – nbk Aug 16 '20 at 21:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.