1

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)

migrated from stackoverflow.com Apr 28 '17 at 22:03

This question came from our site for professional and enthusiast programmers.

  • Can you please run the individual queries with EXPLAIN and show which index each is using? Obviously it shouldn't matter which index it's using or if it is using no index. But it would tell you which index, if any, may be corrupt. Then you could drop that index and perhaps recreate it without rebuilding the whole table. – Bill Karwin Apr 28 '17 at 22:10
  • 1
    And please post the output of SHOW CREATE TABLE licitaciones so we can see what indexes you have in this table. – Bill Karwin Apr 28 '17 at 22:12
  • Ok, just added the extra info... – jacmkno May 1 '17 at 15:45
2

The Rick James's answer is right, I just could not add a comment.

We got a very similar problem, the query with special conditions can not find the record, but that record is here by other where conditions. The problem is the "index_merge", it solved by avoiding the "index_merge_intersection":

SET SESSION optimizer_switch='index_merge_intersection=off';

or

SET GLOBAL optimizer_switch='index_merge=off';

It also worked by adding or removing some indexes to avoid the "index_merge".

0

The index is not corrupt.

There is nothing controlling the order of the rows. In fact, if the two counts had been the same you would have gotten only one row of output -- because UNION means UNION DISTINCT.

Do the query this way instead:

SELECT "with tipo" As the_case,
       count(*)
    FROM licitaciones
    WHERE entidad_id = 103 AND estado_id = 7 and tipo_id > 0
UNION DISTINCT
SELECT "without",
       count(*)
    FROM licitaciones
    WHERE entidad_id = 103 AND estado_id = 7;

Better yet, do both in a single SELECT:

SELECT count(*)         AS "without tipo test",
       SUM(tipo_id > 0) AS "with tipo test"
    FROM licitaciones
    WHERE entidad_id = 103 AND estado_id = 7;
  • Thanks for the effort but the union has nothing to do with te problem. The result is the same if I perform the queries in separate statements. – jacmkno Apr 28 '17 at 21:52
  • But thanks for pointing the fact that people may thin that´s the problem. I added an explanation about that in the question... – jacmkno Apr 28 '17 at 21:59
  • The single-query version should not have a condition in the WHERE clause for tipo_id > 0 if you want to count all rows. – Bill Karwin Apr 28 '17 at 22:06
  • @jacmkno - Which version of 5.6 are you running? And, is it Oracle's not Percona's? – Rick James Apr 28 '17 at 23:13
  • I installed this version with apt-get install mysql-server-5.6. select @@version_comment shows "(Ubuntu)" so it should by Oracle´s version (I think) as pointed by stackoverflow.com/questions/37317869/…. – jacmkno May 1 '17 at 12:55
0

Thanks for updating with the EXPLAIN and SHOW CREATE TABLE output.

It appears you have only single-column indexes, and MySQL is trying to use the index-merge optimization to make use of more than one index. Index-merge is not a well-tested feature of MySQL.

I can't find a reference to a bug regarding index-merge except some that involve partitioned tables. But it's possible a bug exists that have not been reported.

I would suggest that you learn how to use multi-column indexes instead of relying on the index-merge feature. It'll be more reliable, and probably have much better performance too.

This index should help both your queries:

ALTER TABLE licitaciones ADD KEY (entidad_id, estado_id, tipo_id);

Read my presentation How to Design Indexes, Really. Watch the video of me presenting this talk here: https://www.youtube.com/watch?v=ELR7-RdU9XU

  • I´ll give it a try and update the correct answer if it works, but I´m afraid I need a multicolumn index starting with each column and including every column because those columns are exposed filters... That´s probably going to take a lot of disk space which I don´t have... Thanks for the tip about the single-field index combination feature (index-merging) not being trustworthy in MySQL, do you know if it can be trusted in any other DB engine like PostgreSQL? – jacmkno May 1 '17 at 17:08
  • I'm not an expert with PostgreSQL optimization, but the manual has some information about it: postgresql.org/docs/current/static/indexes-bitmap-scans.html But I don't know if it's buggy or not. – Bill Karwin May 1 '17 at 19:07

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.