[MDEV-16246] insert timestamp into spider table from mysqldump gets wrong time zone. - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 10.3.7
Fix Version/s: 10.3.9, 10.4.0
Component/s: Storage Engine - Spider
Labels:
None
Environment:
Linux

Description

From a dump file generated by:

mysqldump --comments=0 --complete-insert=1 --default-character-set=utf8mb4 --disable-keys=1 --hex-blob=1 --no-create-db=1 --no-create-info=1 --set-charset=1 --tz-utc=1 test t1 > timestamp.dump

Load it to a spider table:

mysql test < timestamp.dump

And it fails with:

ERROR 1292 (22007) at line 13: Incorrect datetime value: '2018-03-25 02:40:00' for column 'b' at row 1

How to reproduce:
Create tables on at least two datanodes (spider children):

CREATE TABLE `t1` (

  `a` int(10) unsigned NOT NULL AUTO_INCREMENT,

  `b` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),

  PRIMARY KEY (`a`);

create the servers:

CREATE SERVER testserver1 FOREIGN DATA WRAPPER mysql options ( host 'testserver1.example.com', user 'test_user', password 'test_password', port 3306);

CREATE SERVER testserver2 FOREIGN DATA WRAPPER mysql options ( host 'testserver2.example.com', user 'test_user', password 'test_password', port 3306);

Create the spider table:

CREATE TABLE `t1` (

  `a` int(10) unsigned NOT NULL AUTO_INCREMENT,

  `b` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),

  PRIMARY KEY (`a`)

) ENGINE=SPIDER AUTO_INCREMENT=17 DEFAULT CHARSET=utf8mb4

 PARTITION BY HASH (`a`)

(PARTITION `p0` COMMENT = 'srv "testserver1", tbl "t1"' ENGINE = SPIDER,

 PARTITION `p1` COMMENT = 'srv "testserver2", tbl "t1"' ENGINE = SPIDER);

Use the dumpfile:

cat timestamp.dump
/!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT /;
/!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS /;
/!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION /;
/!40101 SET NAMES utf8mb4 /;
/!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE /;
/!40103 SET TIME_ZONE='+00:00' /;
/!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 /;
/!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' /;
/!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 /;

LOCK TABLES `t1` WRITE;
/!40000 ALTER TABLE `t1` DISABLE KEYS /;
INSERT INTO `t1` (`a`, `b`) VALUES (3,'2018-03-25 02:40:00'),(5,'2018-03-25 00:30:00'),(13,'2018-03-24 23:30:00'),(15,'2018-03-25 01:30:00');
/!40000 ALTER TABLE `t1` ENABLE KEYS /;
UNLOCK TABLES;
/!40103 SET TIME_ZONE=@OLD_TIME_ZONE /;

/!40101 SET SQL_MODE=@OLD_SQL_MODE /;
/!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS /;
/!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT /;
/!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS /;
/!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION /;
/!40111 SET SQL_NOTES=@OLD_SQL_NOTES /;

and load with mysql test < timestamp.dump and it fails with:

ERROR 1292 (22007) at line 13: Incorrect datetime value: '2018-03-25 02:40:00' for column 'b' at row 1

This is of course a simplified table structure etc. from what we are using, but it shows the bug in an easier fashion.

Attachments

Issue Links

relates to

MDEV-16157 federated corrupts timestamps

Closed

MDEV-16508 spider: sql_mode not maintained between spider node and data nodes

Closed

Activity

Ascending order - Click to sort in descending order

Jacob Mathew (Inactive) added a comment - 2018-05-24 01:20

Upon precisely following the instructions for reproducing in the bug description using the latest MariaDB Server 10.3 code, the load succeeds. The reported problem does not reproduce:

Wed May 23 18:07 jacob@JM-Linux-Server-VM ~/MariaDB

160> my -u root test

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Welcome to the MariaDB monitor.  Commands end with ; or \g.

Your MariaDB connection id is 37

Server version: 10.3.7-MariaDB-debug-log Source distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [test]> /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;

Query OK, 0 rows affected (0.000 sec)

MariaDB [test]> /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;

Query OK, 0 rows affected (0.000 sec)

MariaDB [test]> /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;

Query OK, 0 rows affected (0.000 sec)

MariaDB [test]> /*!40101 SET NAMES utf8mb4 */;

Query OK, 0 rows affected (0.000 sec)

MariaDB [test]> /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;

Query OK, 0 rows affected (0.000 sec)

MariaDB [test]> /*!40103 SET TIME_ZONE='+00:00' */;

Query OK, 0 rows affected (0.000 sec)

MariaDB [test]> /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;

Query OK, 0 rows affected (0.000 sec)

MariaDB [test]> /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;

Query OK, 0 rows affected (0.000 sec)

MariaDB [test]> /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

Query OK, 0 rows affected (0.000 sec)

MariaDB [test]>

MariaDB [test]> LOCK TABLES `t1` WRITE;

Query OK, 0 rows affected (0.003 sec)

MariaDB [test]> /*!40000 ALTER TABLE `t1` DISABLE KEYS */;

Query OK, 0 rows affected (0.000 sec)

MariaDB [test]> INSERT INTO `t1` (`a`, `b`) VALUES (3,'2018-03-25 02:40:00'),(5,'2018-03-25 00:30:00'),(13,'2018-03-24 23:30:00'),(15,'2018-03-25 01:30:00');

Query OK, 4 rows affected (0.011 sec)

Records: 4  Duplicates: 0  Warnings: 0

MariaDB [test]> /*!40000 ALTER TABLE `t1` ENABLE KEYS */;

Query OK, 0 rows affected (0.000 sec)

MariaDB [test]> UNLOCK TABLES;

Query OK, 0 rows affected (0.001 sec)

MariaDB [test]> /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

Query OK, 0 rows affected (0.000 sec)

MariaDB [test]>

MariaDB [test]> /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;

Query OK, 0 rows affected (0.000 sec)

MariaDB [test]> /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;

Query OK, 0 rows affected (0.000 sec)

MariaDB [test]> /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

Query OK, 0 rows affected (0.000 sec)

MariaDB [test]> /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;

Query OK, 0 rows affected (0.000 sec)

MariaDB [test]> /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

Query OK, 0 rows affected (0.000 sec)

MariaDB [test]> /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

Query OK, 0 rows affected (0.000 sec)

MariaDB [test]>

MariaDB [test]> SELECT * FROM `t1`;

+----+---------------------+

| a  | b                   |

+----+---------------------+

|  3 | 2018-03-25 02:40:00 |

|  5 | 2018-03-25 00:30:00 |

| 13 | 2018-03-24 23:30:00 |

| 15 | 2018-03-25 01:30:00 |

+----+---------------------+

4 rows in set (0.004 sec)

MariaDB [test]> Ctrl-C -- exit!

Aborted

Wed May 23 18:14 jacob@JM-Linux-Server-VM ~/MariaDB

160>

Jacob Mathew (Inactive) added a comment - 2018-05-24 01:20 Upon precisely following the instructions for reproducing in the bug description using the latest MariaDB Server 10.3 code, the load succeeds. The reported problem does not reproduce: Wed May 23 18:07 jacob@JM-Linux-Server-VM ~/MariaDB 160> my -u root test Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 37 Server version: 10.3.7-MariaDB-debug-log Source distribution Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [test]> /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */ ; Query OK, 0 rows affected (0.000 sec) MariaDB [test]> /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */ ; Query OK, 0 rows affected (0.000 sec) MariaDB [test]> /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */ ; Query OK, 0 rows affected (0.000 sec) MariaDB [test]> /*!40101 SET NAMES utf8mb4 */ ; Query OK, 0 rows affected (0.000 sec) MariaDB [test]> /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */ ; Query OK, 0 rows affected (0.000 sec) MariaDB [test]> /*!40103 SET TIME_ZONE='+00:00' */ ; Query OK, 0 rows affected (0.000 sec) MariaDB [test]> /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */ ; Query OK, 0 rows affected (0.000 sec) MariaDB [test]> /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */ ; Query OK, 0 rows affected (0.000 sec) MariaDB [test]> /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */ ; Query OK, 0 rows affected (0.000 sec) MariaDB [test]> MariaDB [test]> LOCK TABLES `t1` WRITE; Query OK, 0 rows affected (0.003 sec) MariaDB [test]> /*!40000 ALTER TABLE `t1` DISABLE KEYS */ ; Query OK, 0 rows affected (0.000 sec) MariaDB [test]> INSERT INTO `t1` (`a`, `b`) VALUES (3, '2018-03-25 02:40:00' ),(5, '2018-03-25 00:30:00' ),(13, '2018-03-24 23:30:00' ),(15, '2018-03-25 01:30:00' ); Query OK, 4 rows affected (0.011 sec) Records: 4 Duplicates: 0 Warnings: 0 MariaDB [test]> /*!40000 ALTER TABLE `t1` ENABLE KEYS */ ; Query OK, 0 rows affected (0.000 sec) MariaDB [test]> UNLOCK TABLES; Query OK, 0 rows affected (0.001 sec) MariaDB [test]> /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */ ; Query OK, 0 rows affected (0.000 sec) MariaDB [test]> MariaDB [test]> /*!40101 SET SQL_MODE=@OLD_SQL_MODE */ ; Query OK, 0 rows affected (0.000 sec) MariaDB [test]> /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */ ; Query OK, 0 rows affected (0.000 sec) MariaDB [test]> /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */ ; Query OK, 0 rows affected (0.000 sec) MariaDB [test]> /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */ ; Query OK, 0 rows affected (0.000 sec) MariaDB [test]> /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */ ; Query OK, 0 rows affected (0.000 sec) MariaDB [test]> /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */ ; Query OK, 0 rows affected (0.000 sec) MariaDB [test]> MariaDB [test]> SELECT * FROM `t1`; + ----+---------------------+ | a | b | + ----+---------------------+ | 3 | 2018-03-25 02:40:00 | | 5 | 2018-03-25 00:30:00 | | 13 | 2018-03-24 23:30:00 | | 15 | 2018-03-25 01:30:00 | + ----+---------------------+ 4 rows in set (0.004 sec) MariaDB [test]> Ctrl-C -- exit! Aborted Wed May 23 18:14 jacob@JM-Linux-Server-VM ~/MariaDB 160>

Mattias Jonsson added a comment - 2018-05-24 06:10 - edited

Hi, sorry I missed a piece for reproducing this: time_zone on the spider head and datanodes was set to 'SYSTEM' which was CEST. I.e. the reason for this failure is that the time in the dump is in UTC, but when inserting to spider (which locally and remote are set to CEST due to the SYSTEM time_zone) it loses the session time_zone (UTC) in between the spider head and the datanodes and tries to insert it as CET/CEST which is in DST transition, meaning there is no such hour between 02:00 and 02:59:59 resulting in a failure to insert.

Another way to check this bug should be to SELECT FROM_UNIXTIME(timestamp_col) which should show the difference in time zone from the INSERT session and the time zone on the head/datanode.

I believe it should be possible to adjust the test for other time zones.

Mattias Jonsson added a comment - 2018-05-24 06:10 - edited Hi, sorry I missed a piece for reproducing this: time_zone on the spider head and datanodes was set to 'SYSTEM' which was CEST. I.e. the reason for this failure is that the time in the dump is in UTC, but when inserting to spider (which locally and remote are set to CEST due to the SYSTEM time_zone) it loses the session time_zone (UTC) in between the spider head and the datanodes and tries to insert it as CET/CEST which is in DST transition, meaning there is no such hour between 02:00 and 02:59:59 resulting in a failure to insert. Another way to check this bug should be to SELECT FROM_UNIXTIME(timestamp_col) which should show the difference in time zone from the INSERT session and the time zone on the head/datanode. I believe it should be possible to adjust the test for other time zones.

Sergei Golubchik added a comment - 2018-05-24 17:29

I've recently fixed a similar bug FederatedX (~~MDEV-16157~~). Probably Spider has the same issue. Test cases from ~~MDEV-16157~~ should work with Spider with minimal modifications, I believe.

Sergei Golubchik added a comment - 2018-05-24 17:29 I've recently fixed a similar bug FederatedX ( MDEV-16157 ). Probably Spider has the same issue. Test cases from MDEV-16157 should work with Spider with minimal modifications, I believe.

Jacob Mathew (Inactive) added a comment - 2018-06-22 19:25 - edited

This reported problem will not occur if the spider_sync_time_zone configuration variable is set to TRUE. It can be initialized to TRUE in my.cnf. It can also be set to TRUE before executing a block of SQL statements that uses a different time zone, and then reset afterwards:

SET spider_sync_time_zone=TRUE;

SET time_zone='+00:00';

SET time_zone=DEFAULT;

SET spider_sync_time_zone=FALSE;

This will cause the time zone to be synchronized at the beginning of every transaction until the variable is reset to FALSE. I am looking at other ways to do the synchronization that synchronize less frequently and which do not involve setting and resetting the configuration variable.

Jacob Mathew (Inactive) added a comment - 2018-06-22 19:25 - edited This reported problem will not occur if the spider_sync_time_zone configuration variable is set to TRUE. It can be initialized to TRUE in my.cnf. It can also be set to TRUE before executing a block of SQL statements that uses a different time zone, and then reset afterwards: SET spider_sync_time_zone= TRUE ; SET time_zone= '+00:00' ; . . . SET time_zone= DEFAULT ; SET spider_sync_time_zone= FALSE ; This will cause the time zone to be synchronized at the beginning of every transaction until the variable is reset to FALSE. I am looking at other ways to do the synchronization that synchronize less frequently and which do not involve setting and resetting the configuration variable.

Sergei Golubchik added a comment - 2018-06-22 19:45 - edited

RIght, spider_sync_time_zone=TRUE indeed fixes the original reported problem.

But it generally doesn't fix the third issue mentioned in ~~MDEV-16157~~. Please either fix it or report it separately, to make sure the bug won't be lost.

Sergei Golubchik added a comment - 2018-06-22 19:45 - edited RIght, spider_sync_time_zone=TRUE indeed fixes the original reported problem. But it generally doesn't fix the third issue mentioned in MDEV-16157 . Please either fix it or report it separately, to make sure the bug won't be lost.

Jacob Mathew (Inactive) added a comment - 2018-06-22 20:58 - edited

The third issue in the above comment refers to timestamps for the hour in October or November when we transition from daylight savings time to standard time, when the 2:00 am to 3:00 am hour occurs twice. We need to differentiate between time values in the two occurrences of that hour on that day. A problem can occur when the time zone on a data node is not UTC nor another time zone that does not have daylight savings time.

Jacob Mathew (Inactive) added a comment - 2018-06-22 20:58 - edited The third issue in the above comment refers to timestamps for the hour in October or November when we transition from daylight savings time to standard time, when the 2:00 am to 3:00 am hour occurs twice. We need to differentiate between time values in the two occurrences of that hour on that day. A problem can occur when the time zone on a data node is not UTC nor another time zone that does not have daylight savings time.

Sergei Golubchik added a comment - 2018-06-29 14:58 - edited

Note, according to comments above, the bug reporter uses CET/CEST time zone and this timestamp ambiguity is a real, not a hypothetical, issue for him.

Sergei Golubchik added a comment - 2018-06-29 14:58 - edited Note, according to comments above, the bug reporter uses CET/CEST time zone and this timestamp ambiguity is a real, not a hypothetical, issue for him.

Jacob Mathew (Inactive) added a comment - 2018-06-29 20:47

Kentoku, please review my fix for this problem in commit 4c4c4f9 on my branch.

Jacob Mathew (Inactive) added a comment - 2018-06-29 20:47 Kentoku, please review my fix for this problem in commit 4c4c4f9 on my branch .

Kentoku Shiba (Inactive) added a comment - 2018-07-05 12:00

Jacob,
For initialize "UTC" time zone, if it is possible, it should be initialized at spider_db_init().
I think it is better to change time_zone like "thd->variables.time_zone = UTC" instead of "table->in_use->variables.time_zone = UTC". It reduces changing it.

Kentoku Shiba (Inactive) added a comment - 2018-07-05 12:00 Jacob, For initialize "UTC" time zone, if it is possible, it should be initialized at spider_db_init(). I think it is better to change time_zone like "thd->variables.time_zone = UTC" instead of "table->in_use->variables.time_zone = UTC". It reduces changing it.

Jacob Mathew (Inactive) added a comment - 2018-07-09 21:47

Kentoku,
The UTC initialization cannot be done in spider_db_init(), which is called during plugin initialization, because MariaDB's time zone infrastructure is initialized AFTER plugin initialization.
I have made the "thd->variables.time_zone = UTC" changes that you suggested.

Jacob Mathew (Inactive) added a comment - 2018-07-09 21:47 Kentoku, The UTC initialization cannot be done in spider_db_init(), which is called during plugin initialization, because MariaDB's time zone infrastructure is initialized AFTER plugin initialization. I have made the "thd->variables.time_zone = UTC" changes that you suggested.

Jacob Mathew (Inactive) added a comment - 2018-07-09 23:23

Fix is pushed to 10.3.

Jacob Mathew (Inactive) added a comment - 2018-07-09 23:23 Fix is pushed to 10.3.

Jacob Mathew (Inactive) added a comment - 2018-07-24 23:51

Fix is pushed to 10.4.

Jacob Mathew (Inactive) added a comment - 2018-07-24 23:51 Fix is pushed to 10.4.

Mattias Jonsson added a comment - 2018-07-31 11:38

We have tested and verified that the fix works.

Mattias Jonsson added a comment - 2018-07-31 11:38 We have tested and verified that the fix works.

People

Assignee:: Jacob Mathew (Inactive)

Reporter:: Mattias Jonsson

Votes:: 0 Vote for this issue

Watchers:: 6 Start watching this issue

Dates

Created:: 2018-05-22 15:03

Updated:: 2024-09-10 05:45

Resolved:: 2018-07-09 23:23

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.