| Bug #13052 | mysqldump timestamp reloads broken when servers have different time zones | ||
|---|---|---|---|
| Submitted: | 8 Sep 2005 3:29 | Modified: | 21 Oct 2005 17:26 |
| Reporter: | James Day | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Replication | Severity: | S2 (Serious) |
| Version: | 5.0.10-beta-log and 4.1 | OS: | Linux (RHES 3.0) |
| Assigned to: | Bugs System | CPU Architecture: | Any |
[8 Sep 2005 23:30]
James Day
To clarify, this also applies in shared hosting situations when multiple clients or customers have different time zone settings, not only when moving data between servers. In this situation, the hosting company seems unable to make a complete and reloadable database dump. I see no practical workaround for this case other than a patched mysqldump, so I've changed the severity to S1.
[7 Oct 2005 23:03]
Patrick Galbraith
going to change this to set UTC, since GMT isn't always set on some platforms (such as my OSX 10 mac), name option to --set-tz-utc/opt_tz_utc
[8 Oct 2005 0:01]
James Day
That sounds fine. I'll let the customer know.
[10 Oct 2005 18:26]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/internals/30872
[11 Oct 2005 16:44]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/internals/30927
[12 Oct 2005 17:50]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/internals/30971
[12 Oct 2005 22:23]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/internals/30993
[13 Oct 2005 5:45]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/internals/31002
[13 Oct 2005 20:45]
Patrick Galbraith
pushed fix to 5.0 tree. In build for 5.0.15, 20051013
[13 Oct 2005 22:41]
James Day
Any word on presence in 4.1?
[17 Oct 2005 19:31]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/internals/31193
[18 Oct 2005 20:13]
Paul DuBois
Noted in 5.0.15 changelog, and added option description to mysqldump section.
[21 Oct 2005 1:08]
James Day
Patrick, I've reopened this because it appears that this is also needed in 4.1, on which it was originally reported, which has the same timestamp support and problem dumping and reloading. No customer is waiting specifically for a 4.1 version, though. I've dropped the severity from S1 to S2 because I assume the 5.0.15 mysqldump will work with 4.1 as a workaround.
[21 Oct 2005 3:09]
James Day
Please change the option help text from:
{"tz-utc", OPT_TZ_UTC,
"SET TIME_ZONE='UTC' at top of dump to allow dumping of TIMESTAMP data between servers with different time zones.",
to:
{"tz-utc", OPT_TZ_UTC,
"SET TIME_ZONE='UTC' at top of dump to allow dumping of TIMESTAMP data when a server has data in different time zones or data is being moved between servers with different time zones.",
[21 Oct 2005 17:26]
Patrick Galbraith
Per Serg, Monty has stated that there will be no new features or changes in behaviour for 4.1, and this is a change in behaviour, so for 4.1 it won't be changed. Plus, this is fixed in 5.0, being a client program, the user can download 5.0's mysqldump.
Description: When using the timestamp fields with time zone support, mysqldump always dumps out the dates in the timezone of the server. So if it's reimported into a another server using a different time zone the timesamps are wrong by the difference between the time zones of the two servers. If the time zone changes result in duplicate records, the dump can't be reloaded without withdrawing he server from normal service and changing its global time zone to match the source server's time zone. Originally reported on RHES3.0, reproduced on Windows XP. One workaround is to use a second server set to the time zone of the orignal server, load the data, change the global time zone to match that of the destination serer, then dump and reload the new dump into the production server. How to repeat: -- set the global server time zone to be used for dump and reload set global time_zone='PST8PDT'; create table dates (date timestamp, unique key dates$date (date)); set time_zone='GMT'; insert into dates values ('2003-10-26 08:00:00'),('2003-10-26 09:00:00'); mysql> select * from dates; +---------------------+ | date | +---------------------+ | 2003-10-26 08:00:00 | | 2003-10-26 09:00:00 | +---------------------+ 2 rows in set (0.01 sec) mysqldump this table and observe that the text file with the dumped timestamps contains: 2003-10-26 01:00:00 2003-10-26 01:00:00 Try to reload the dump (after removing the rows either by playing back the SQL file or by truncating the table!) and you will get "mysqlimport -u root test \dates.txt mysqlimport: Error: Duplicate entry '2003-10-26 01:00:00' for key 1, when using table: dates". Suggested fix: Dump and reload in UTC so the dump will always be loaded correctly, regardless of server time zone changes.