[MDEV-16684] Wrong timestamps on restore from mysqldump --compact Created: 2018-07-04  Updated: 2021-02-28  Resolved: 2019-09-20

Status: Closed
Project: MariaDB Server
Component/s: Scripts & Clients, Time zones
Affects Version/s: 5.5, 10.0, 10.1, 10.2, 10.3
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Aleksey Midenkov Assignee: Aleksey Midenkov
Resolution: Not a Bug Votes: 0
Labels: need_feedback

Issue Links:
Blocks
blocks MDEV-16029 mysqldump: dump and restore historica... Closed
Relates
relates to MDEV-16013 During the extra hour upon DST switch... Stalled
relates to MDEV-16752 mysqldump: racing condition in STDERR... Closed

 Description   

Reproduce

1. Set system timezone to UTC+3
2.

create or replace table d (t timestamp);
 
insert into d values ('2000-01-01 00:00');

3.

mysqldump --compact test d > /tmp/dump.sql

4.

drop table d;
 
source /tmp/dump.sql
 
select * from d;

Result

Data is different from original:

+---------------------+
 
| t                   |
 
+---------------------+
 
| 1999-12-31 22:00:00 |
 
+---------------------+

Expected

mysqldump and subsequent restore should be idempotent without any additional manipulations.

Fix

mysqldump must insert correct SET time_zone directive regardless --compact setting. In case when opt_tz_utc is 0 it must detect server timezone as well.

 Comments   
Comment by Aleksey Midenkov [ 2018-07-11 ]

Testing

main.mysqldump, main.mysqldump-no-binlog fail on Windows:

mysqldump.exe: Couldn't find table: "non_existing"
 
mysqldump.exe: Got error: 1356: "View 'test.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them" when using LOCK TABLES


--- D:/win32-debug/build/src/mysql-test/main/mysqldump-no-binlog.result	2018-07-11 11:59:13.915781400 +0000
 
+++ D:\win32-debug\build\src\mysql-test\main\mysqldump-no-binlog.reject	2018-07-11 12:44:58.356747300 +0000
 
@@ -1,3 +1,3 @@
 
-mysqldump: Error: Binlogging on server not active
 
 /*!40103 SET @OLD_TIME_ZONE      = @@time_zone */ ;
 
 /*!40103 SET time_zone             = '+00:00' */ ;
 
+mysqldump: Error: Binlogging on server not active

Comment by Aleksey Midenkov [ 2018-07-13 ]

main.mysqldump-no-binlog failure is caused by MDEV-16752.

Comment by Sergei Golubchik [ 2018-07-17 ]

I'm not sure I agree it's a bug.

SET time_zone is disabled by --compact apparently quite intentionally. So is SET NAMES, that is if you change the server's character set, the dump in --compact will not corrupt the data just the same.

I guess you need this fix to use mysqldump in tests with timestamps and without comments (to have a stable output). Perhaps you should use --skip-comments instead of --compact ?

Comment by Aleksey Midenkov [ 2021-02-28 ]

I'm disabling dump history for --compact mode then.

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