Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Not a Bug
-
5.5(EOL), 10.0(EOL), 10.1(EOL), 10.2(EOL), 10.3(EOL)
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.
Attachments
Issue Links
- blocks
-
MDEV-16029 mysqldump: dump and restore historical data
-
- Closed
-
- relates to
-
-
- Stalled
-
-
-
- Closed
-
Activity
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 ?
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' */ ;