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                                                                                              
       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)  

A possible workaround: add this

INDEX(entidad_id, estado_id, tipo_id)

Also, please provide SHOW CREATE TABLE licitaciones just in case there is something strange going one.

Do you "rebuild the table" via this?

ALTER TABLE licitaciones ENGINE = InnoDB;

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.