0

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?

0

1 Answer 1

0

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 |
4
  • My cols are datetime, I write table content by my own and I omitted seconds. Sorry for this misunderstanding
    – Tobia
    Commented Feb 5, 2018 at 6:21
  • in this case, because "I don't have this problem anymore and I cannot investigate" - we can only guess, what it was :)
    – a_vlad
    Commented Feb 5, 2018 at 9:21
  • yes, I know. The main problem is that maybe I have other problems in my database but I did not find them yet. This application will report fiscal data, and in that case a row was missed and I found it just because another report by other colum was not consistent.
    – Tobia
    Commented Feb 5, 2018 at 10:00
  • Please provide SHOW CREATE TABLE and enough INSERTs to demonstrate the problem. Then we can help you debug it.
    – Rick James
    Commented Feb 11, 2018 at 1:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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