1

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.

  • Extremely strange. Can you provide a console screenshot with 2 above queries and their results in real, not modelling, form? of course, include only fields mentioned in WHERE clause as output ones. – Akina Oct 17 '18 at 11:11
  • I have added screenshots and some additional info to question – dima.rus Oct 17 '18 at 14:06
  • actually, this update must do nothing. Nevertheless 23 rows affected... this means there was 23 records which had not matched c = FROM_UNIXTIME(UNIX_TIMESTAMP(c)); condition. – Akina Oct 17 '18 at 17:01
  • I have no idea, how can I select these 23 rows. When I run select * from mytable where NOT c = from_unixtime(unix_timestamp(c)), it returns 0 rows. – dima.rus Oct 17 '18 at 18:05
  • it returns 0 rows. Of course - these records are already updated. I have no idea If there exists any non-updated record, try to obtain "raw" binary content of it before and after update. For example copy this record into separate table, store the copy of table' body file, then update the value and perform binary compare of table' body file and stored copy of it... – Akina Oct 18 '18 at 4:52
0

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!

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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