InnoDB cannot find a row – possible index corruption

innodbmariadb

I have this table:

+----+------------------+------------------+
| Id |      Start       |       End        |
+----+------------------+------------------+
|  1 | 2018-02-04 11:30 | 2018-02-05 12:00 |
|  2 | 2018-02-04 15:40 | 2018-02-05 08:00 |
|  3 | 2018-02-05 10:00 | 2018-02-06 09:00 |
+----+------------------+------------------+

I found this strange situation using MariaDB 10.1.18 InnoDB.

I called this query:

SELECT * FROM mytable WHERE start BETWEEN '2018-02-04 00:00' and '2018-02-04 12:00'

and I didn't find any rows! The first row (id=1) should have been shown.

Calling this query using another colum:

SELECT * FROM mytable WHERE end BETWEEN '2018-02-05 10:00' and '2018-02-05 13:00'

I found the first row, so I was sure the row with id=1 was there.

I solved the problem when I updated the first row:

UPDATE mytable SET Start='2018-02-04 11:31' where id=1;
UPDATE mytable SET Start='2018-02-04 11:30' where id=1;

Now with this apparently useless update I can find the first row with both queries.

Now I don't have this problem anymore and I cannot investigate, but I want to understand why it happened.

Can it be connected with index corruption? I have two different indexes in start and end columns. If yes, maybe, I should have repaired the indexed, but I'm a bit scared about it, I could not have noticed the problem.

Should I do anything at this point, to make sure that there isn't any corruption of the indexes now?

Best Answer

looking for Your data - Your columns is VARCHAR, but not date, this is make query non working with any non readable character in data

by run UPDATES:

UPDATE mytable SET Start='2018-02-04 11:31' where id=1;
UPDATE mytable SET Start='2018-02-04 11:30' where id=1;

You are fix this situation

Proper way for work with dates - have datetime type for date time columns

in this case Your data would look like:

+----+---------------------+---------------------+
| Id |      Start          |       End           |
+----+---------------------+---------------------+
|  1 | 2018-02-04 11:30:00 | 2018-02-05 12:00:00 |
|  2 | 2018-02-04 15:40:00 | 2018-02-05 08:00:00 |
|  3 | 2018-02-05 10:00:00 | 2018-02-06 09:00:00 |
Related Question