Find MySQL InnoDB index corruption

Question

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:

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.

---

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.

Answer 1

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!

Answer 2

I can add steps to reproduce this error for my system (Ubuntu 18.04.1 LTS).

For example, next daylight saving time change to 1 hour back in the United States will be on 04 November 2018 at 2:00 a.m (local time). So I will use timestamp 1541322000, which is 2018-11-04 01:00:00 in US/Pacific timezone.

On test database create table with structure:

CREATE TABLE `mytable` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `t` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index2` (`t`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Change session timezone to UTC:

mysql> set session time_zone = 'UTC';
Query OK, 0 rows affected

If you got an error like "unknown or incorrect time zone", first add this timezome (from this answer):

$ mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -uroot -p123 mysql

Insert one row into table:

mysql> insert into mytable (t) values (from_unixtime(1541322000));
Query OK, 1 row affected

Switch timezone to US/Pacific (In my case I dumped table from external server with UTC timezone to my localhost with Europe/Moscow timezone):

mysql> set session time_zone = 'US/Pacific';
Query OK, 0 rows affected

Then run two queries:

mysql> select t from mytable where t = '2018-11-04 01:00:00';
Empty set

mysql> select t from mytable where t = '2018-11-04 01:00:00' and id = 1;
2018-11-04 01:00:00

Results of these two queries look as incompatible. First select must return non-empty result.

Note, that if you remove index from field t or use MyISAM engine, first select will return correct (non-empty) result.