Mysql – Find MySQL InnoDB index corruption

indexinnodbMySQL

I have large MySQL InnoDB table on my localhost. In this table primary key is id, unique index is set on fields a, b, c.

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

or with:

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.

Edit:

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 timestamp type.

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:

screenshot1

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:

screenshot2

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.

Edit 2

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.

Best Answer

On 26 October 2014, following another change in the law, the clocks in most of the country were moved back one hour, but summer Daylight Time was not reintroduced; Moscow Time returned to UTC+03:00 permanently.

-- https://en.wikipedia.org/wiki/Time_in_Russia

It sounds like your system (OS and/or MySQL) need an update to the TZ tables.

Down with DST!

Related Question