[MDEV-16246] insert timestamp into spider table from mysqldump gets wrong time zone. Created: 2018-05-22  Updated: 2020-08-25  Resolved: 2018-07-09

Status: Closed
Project: MariaDB Server
Component/s: Storage Engine - Spider
Affects Version/s: 10.3.7
Fix Version/s: 10.3.9, 10.4.0

Type: Bug Priority: Major
Reporter: Mattias Jonsson Assignee: Jacob Mathew (Inactive)
Resolution: Fixed Votes: 0
Labels: None
Environment:

Linux

Issue Links:
Relates
relates to MDEV-16157 federated corrupts timestamps Closed
relates to MDEV-16508 spider: sql_mode not maintained betwe... Closed

 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.

 Comments   
Comment by Jacob Mathew (Inactive) [ 2018-05-24 ]

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>

Comment by Mattias Jonsson [ 2018-05-24 ]

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.

Comment by Sergei Golubchik [ 2018-05-24 ]

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.

Comment by Jacob Mathew (Inactive) [ 2018-06-22 ]

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.

Comment by Sergei Golubchik [ 2018-06-22 ]

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.

Comment by Jacob Mathew (Inactive) [ 2018-06-22 ]

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.

Comment by Sergei Golubchik [ 2018-06-29 ]

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.

Comment by Jacob Mathew (Inactive) [ 2018-06-29 ]

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

Comment by Kentoku Shiba (Inactive) [ 2018-07-05 ]

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.

Comment by Jacob Mathew (Inactive) [ 2018-07-09 ]

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.

Comment by Jacob Mathew (Inactive) [ 2018-07-09 ]

Fix is pushed to 10.3.

Comment by Jacob Mathew (Inactive) [ 2018-07-24 ]

Fix is pushed to 10.4.

Comment by Mattias Jonsson [ 2018-07-31 ]

We have tested and verified that the fix works.

Generated at Thu Feb 08 08:27:28 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.