MySQL Bugs: #58491: --compact removes --tz-utc in mysqldump

Bug #58491	--compact removes --tz-utc in mysqldump
Submitted:	25 Nov 2010 12:59	Modified:	22 Oct 2024 22:20
Reporter:	Daniel Fiske	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: mysqldump Command-line Client	Severity:	S3 (Non-critical)
Version:	5.1.52, 5.0, 5.1, 5.6.1	OS:	Any
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	mysqldump, tz-utc

Description:
Due to --compact implying --skip-comments, --tz-utc is rendered disabled.

How to repeat:
execute

mysqldump --compact --tz-utc |grep -i "time_zone"

vs

mysqldump --tz-utc |grep -i "time_zone"

Suggested fix:
Unknown....considering that --skip-tz-utc can essentially "break" backup/restore or script based synchronisation and that --tz-utc is default (suggesting it is important) it seems there should be some fix that forces --tz-utc output even in compact mode.

It could be "fixed" by documenting it, however I believe this behaviour is unintentional and not the intended behaviour.

Sorry, --compact's inclusion of --skip-comments has nothing to do with it.

Seems the output from

--compact 

is not equivalent to 

--skip-add-drop-table --skip-add-locks --skip-comments --skip-disable-keys --skip-set-charset 

as suggested by the documentation.

http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html#option_mysqldump_compact

--compact seems to drop the following lines.

/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!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 */;

Thank you for the report.

Verified as described.

Please consider raising the severity of this bug report. I wouldn't consider it to be just an omission in the documentation.

When the database time zone is not +00:00, then each value in a timestamp column exported with --compact and then re-imported (even in the same database) will change.

The problem is that --compact just omits SET TIME_ZONE='+00:00' from the output but does not actually disable --tz-utc, so time_zone is still set to +00:00 when fetching the data for the output.

The problem could be solved by actually letting --compact disable --tz-utc, or by writing SET TIME_ZONE='+00:00' when both --compact and --tz-utc are enabled.

Thanks Henning for taking the time to analyze this.

I believe that, to maintain backward compatibility as much as possible, we will go with a complete disablement of --tz-utc by --compact.

Expect a fix soon please.

Posted by developer:
 
Fixed as of the upcoming MySQL Server 9.2.0 release, and here's the proposed changelog entry from the documentation team for review:

The mysqldump --compact option now disables --tz-utc.

Previously, with --tz-utc enabled (default), --compact would execute
"/*!40103 SET TIME_ZONE='+00:00' */" before reading data but did not write
"/*!40103 SET TIME_ZONE='+00:00' */;" to the dump file. This caused an
inconsistency, as the data was extracted using UTC but did not write it to
the dump file.

Thank you for the bug report.