I have large MySQL InnoDB table on my localhost. In this table primary key is
id, unique index is set on fields
When I run query:
SELECT * FROM mytable WHERE a = 1 AND b = 2 AND c = 3;
the result is empty set, but such row certainly exists in the table. When I add " AND id = 1234" at the end (where 1234 is id of expected result):
SELECT * FROM mytable WHERE a = 1 AND b = 2 AND c = 3 AND id = 1234;
it returns 1 row, as expected.
The only explanation is that unique index is corrupted.
But when I tried to check table with:
mysqlcheck -c mydb mytable
ANALYZE table mydb.mytable;
both checks returned OK with no errors.
So the question is:
How to identify this error in index? Is there a way to find this error with some query?
MySQL version is 5.7.23, in error.log no errrors.
It turned out, that after creating empty table mytable1 with same structure and filling it with data by query:
INSERT INTO mytable1 (SELECT * FROM mytable);
this error is present in mytable1, which is impossible in case of index curruption.
After some research I have found, that the trouble is in field c, which has
Actual table structure is:
CREATE TABLE `mytable` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `a` int(10) unsigned NOT NULL, `b` int(10) unsigned NOT NULL, `c` timestamp NOT NULL DEFAULT '1999-01-01 00:00:00', `d` tinyint(1) NOT NULL DEFAULT '0', `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`), UNIQUE KEY `index2` (`a`,`b`,`c`,`d`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
There are screenshots of mysql queries:
Third query differs from first by 'LIKE' instead of '='
I don't know the reason why first query returned empty set, it looks like some bug in MySQL related with timestamp field.
I run query, which fixed this error:
UPDATE mytable SET c = FROM_UNIXTIME(UNIX_TIMESTAMP(c));
After this update the problem query began work fine. But actually, this update must do nothing.
I have found, that the cause of this problem is in clock change in Russia on October 26, 2014.
Because my local setting were:
mysql> select @@global.time_zone, @@session.time_zone; SYSTEM | SYSTEM
so on my localhost datetime '2014-10-26 01:00:00' had two timestamps:
mysql> select from_unixtime(1414270800); 2014-10-26 01:00:00 mysql> select from_unixtime(1414274400); 2014-10-26 01:00:00
So select by value returned empty set:
mysql> select * from test_db.mytable where c = '2014-10-26 01:00:00'; Empty set
but select by id returned correct data:
mysql> select c from test_db.mytable where id = 316572297; 2014-10-26 01:00:00
I have changed
default-time-zone in my.cnf to '+03:00', and everything began work fine.
Note: to reproduce error you must also have index on field c. Without it select by value c = ... works fine.