Mysql – Frequent Corrupt Indexes in MySQL 5.6. Is this a MySQL bug

I´m having problems with mysql indexes with INNODB getting corrupted. Initially I solved it by rebuilding the tables but now the tables are to large to rebuild, and i´m tired of mysql not showing consistent results.

I´m inserting close to 10000 records daily among 4 or 5 tables, and I have some jobs continuously updating data on those records (probably 100000 updates a day)

Is this a bug in MySQL? How would you solve this issue?

In the following statements you can see the discrepancy because the row with 3 conditions shows 3433 results and if I remove one condition I get 0 results. I use the UNION to show that the queries are done almost at the same time so it´s very unlikely that the records are actually disappearing between the queries and coming back right after, because when I repeat the query I get the same results.

mysql> SELECT count(*) FROM licitaciones WHERE entidad_id = 103 AND estado_id = 7 and tipo_id > 0                                                                                              
       UNION 
       SELECT count(*) FROM licitaciones WHERE entidad_id = 103 AND estado_id = 7;                                                                                                       
+----------+                                                                                                                                                                                     
| count(*) |                                                                                                                                                                                     
+----------+                                                                                                                                                                                     
|     3433 |                                                                                                                                                                                     
|        0 |                                                                                                                                                                                     
+----------+                                                                                                                                                                                     
2 rows in set (0.16 sec)      

Here is the mysql version… I´m using INNODB by the way.

mysql> select version();                                                                                                                                                                         
+-------------------------+                                                                                                                                                                      
| version()               |                                                                                                                                                                      
+-------------------------+                                                                                                                                                                      
| 5.6.33-0ubuntu0.14.04.1 |                                                                                                                                                                      
+-------------------------+                                                                                                                                                                      
1 row in set (0.00 sec)  

Just to clarify. The UNION was only to expose the fact that the rows were not changing from one statement to the other, but the result is the same if the queries are performed in separate statements:

mysql> SELECT count(*) FROM licitaciones WHERE entidad_id = 103 AND estado_id = 7 and tipo_id > 0;
+----------+
| count(*) |
+----------+
|     3436 |
+----------+
1 row in set (0.55 sec)

mysql> SELECT count(*) FROM licitaciones WHERE entidad_id = 103 AND estado_id = 7;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.06 sec)

MORE INFO:

mysql> EXPLAIN SELECT count(*) FROM licitaciones WHERE entidad_id = 103 AND estado_id = 7 and tipo_id > 0;
+----+-------------+--------------+-------------+------------------------------+----------------------+---------+------+------+----------------------------------------------------+
| id | select_type | table        | type        | possible_keys                | key                  | key_len | ref  | rows | Extra                                              |
+----+-------------+--------------+-------------+------------------------------+----------------------+---------+------+------+----------------------------------------------------+
|  1 | SIMPLE      | licitaciones | index_merge | entidad_id,tipo_id,estado_id | entidad_id,estado_id | 5,4     | NULL | 3557 | Using intersect(entidad_id,estado_id); Using where |
+----+-------------+--------------+-------------+------------------------------+----------------------+---------+------+------+----------------------------------------------------+

mysql> EXPLAIN SELECT count(*) FROM licitaciones WHERE entidad_id = 103 AND estado_id = 7;
+----+-------------+--------------+-------------+----------------------+----------------------+---------+------+------+-----------------------------------------------------------------+
| id | select_type | table        | type        | possible_keys        | key                  | key_len | ref  | rows | Extra                                                           |
+----+-------------+--------------+-------------+----------------------+----------------------+---------+------+------+-----------------------------------------------------------------+
|  1 | SIMPLE      | licitaciones | index_merge | entidad_id,estado_id | entidad_id,estado_id | 5,4     | NULL | 3557 | Using intersect(entidad_id,estado_id); Using where; Using index |
+----+-------------+--------------+-------------+----------------------+----------------------+---------+------+------+-----------------------------------------------------------------+
1 row in set (0.00 sec)



mysql> show create table licitaciones;
+--------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table        | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
+--------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| licitaciones | CREATE TABLE `licitaciones` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `detected` datetime DEFAULT CURRENT_TIMESTAMP,
  `num_constancia` varchar(60) DEFAULT NULL,
  `url` varchar(255) DEFAULT NULL,
  `fila` int(11) DEFAULT NULL,
  `num_proceso` varchar(255) DEFAULT NULL,
  `tipo_id` tinyint(4) DEFAULT NULL,
  `estado_id` mediumint(9) DEFAULT NULL,
  `entidad_id` int(11) DEFAULT NULL,
  `objeto` longtext,
  `cuantia` bigint(20) DEFAULT NULL,
  `tipo_fecha_id` smallint(6) DEFAULT NULL,
  `fecha` date DEFAULT NULL,
  `extra_updated` date DEFAULT NULL,
  `extra_json` mediumtext,
  `fastcodes` text,
  PRIMARY KEY (`id`),
  UNIQUE KEY `num_constancia` (`num_constancia`),
  KEY `entidad_id` (`entidad_id`),
  KEY `tipo_id` (`tipo_id`),
  KEY `estado_id` (`estado_id`),
  KEY `tipo_fecha_id` (`tipo_fecha_id`),
  KEY `fecha` (`fecha`),
  KEY `cuantia` (`cuantia`),
  KEY `num_proceso` (`num_proceso`),
  KEY `extra_updated` (`extra_updated`),
  KEY `detected` (`detected`),
  FULLTEXT KEY `fulltextidx` (`objeto`,`fastcodes`,`extra_json`)
) ENGINE=InnoDB AUTO_INCREMENT=32050336 DEFAULT CHARSET=latin1 |
+--------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)