MySQL Bugs: #31077: mysqldump appending date to dump breaks dump semantics

Bug #31077	mysqldump appending date to dump breaks dump semantics
Submitted:	18 Sep 2007 9:19	Modified:	15 Nov 2007 14:47
Reporter:	Edward Buck	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Backup	Severity:	S3 (Non-critical)
Version:	5.0.32	OS:	Linux (Debian Etch)
Assigned to:	Gleb Shchepa	CPU Architecture:	Any

Description:
This is a duplicate of bug #29815 but that one appears to be closed (not sure why).

The purpose of mysqldump is to dump the contents of a database such that it can be recreated on another server. As such, two dumps of exactly the same data should be identical. This is semantically and logically correct. Appending the date line to the dump changes the dump and gives the impression that the data has changed even when it has not.

The date of the dump has nothing to do with the contents of the dump itself (the data that is to be restored). As such, it shouldn't be included in the dump. The date of the dump is implicit in the file timestamp.

The impact of this new feature is that it breaks backup schemes that compare dumps to check for changes. Incremental backup schemes often rely on timestamps and inodes changes to look for changed files. With this new feature, two dumps always look different. Workarounds exist but the dump semantics are still broken by this change.

The date of the dump has nothing to do with the dumped data. Please keep that data outside or add a switch to mysqldump to exclude this data.

Thanks!

How to repeat:
Run mysqldump twice on a database that has not changed. The appended timestamp will ensure that the two databases look different (to any diff or binary compare program).

Suggested fix:
Add a command line option to mysqldump to remove this last line. Actually, the default should be to exclude this line. There should be a command line switch to ADD the date.

Alternatively, just remove this new feature.

Thank you for the bug report.

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/commits/34583

ChangeSet@1.2535, 2007-09-27 18:00:37+05:00, gshchepa@gleb.loc +4 -0
  Fixed bug #31077.
  mysqldump adds the "-- Dump completed on YYYY-MM-DD hh:mm:ss" string
  to the end of output if the --comments switch is on.
  The only way to suppress this line is to use --skip-comments/--compact
  switch.
  
  Two new switches have been added to the mysqldump client command line:
  --dump-date and --skip-dump-date.
  
  For the compatibility with previous releases, by default the --dump-date
  is on and the --skip-dump-date is off.
  
  The --skip-dump-date option suppresses appending of the "Dump completed"
  string to the output.
  The --dump-date option forces output of the "Dump completed" string.
  
  --skip-comments or --compact switches disable that output as usual.

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/commits/34584

ChangeSet@1.2535, 2007-09-27 18:21:59+05:00, gshchepa@gleb.loc +4 -0
  Fixed bug #31077.
  mysqldump adds the "-- Dump completed on YYYY-MM-DD hh:mm:ss" string
  to the end of output if the --comments switch is on.
  The only way to suppress this line is to use --skip-comments/--compact
  switch.
  
  Two new switches have been added to the mysqldump client command line:
  --dump-date and --skip-dump-date.
  
  For the compatibility with previous releases, by default the --dump-date
  is on and the --skip-dump-date is off.
  
  The --skip-dump-date option suppresses appending of the "Dump completed"
  string to the output.
  The --dump-date option forces output of the "Dump completed" string.
  
  --skip-comments or --compact switches disable that output as usual.

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/commits/34720

ChangeSet@1.2535, 2007-10-01 20:23:35+05:00, gshchepa@gleb.loc +4 -0
  Fixed bug #31077.
  mysqldump adds the "-- Dump completed on YYYY-MM-DD hh:mm:ss" string
  to the end of output if the --comments switch is on.
  The only way to suppress this line is to use --skip-comments/--compact
  switch.
  
  Two new switches have been added to the mysqldump client command line:
  --dump-date and --skip-dump-date.
  
  For the compatibility with previous releases, by default the --dump-date
  is on and the --skip-dump-date is off.
  
  The --skip-dump-date option suppresses appending of the "Dump completed"
  string to the output.
  The --dump-date option forces output of the "Dump completed" string.
  
  --skip-comments or --compact switches disable that output as usual.

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/commits/34722

ChangeSet@1.2535, 2007-10-01 20:35:51+05:00, gshchepa@gleb.loc +4 -0
  Fixed bug #31077.
  mysqldump adds the "-- Dump completed on YYYY-MM-DD hh:mm:ss" string
  to the end of output if the --comments switch is on.
  The only way to suppress this line is to use --skip-comments/--compact
  switch.
  
  New switch has been added to the mysqldump client command line:
  --dump-date.
  
  For the compatibility with previous releases, by default the --dump-date
  is on.
  The --dump-date switch forces mysqldump to add date to the
  "-- Dump completed on ..." string at the end of output.
  The --skip-dump-date switch supresses the output of date string
  and uses short form of that commentary: "-- Dump completed".
  --skip-comments or --compact switches disable the whole commentary
  as usual.

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/commits/34788

ChangeSet@1.2536, 2007-10-03 02:50:38+05:00, gshchepa@gleb.loc +3 -0
  mysqldump.c, mysqldump.test, mysqldump.result:
    Bug #31077: post-commit fix.

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/commits/34799

ChangeSet@1.2537, 2007-10-03 11:36:42+05:00, gshchepa@gleb.loc +2 -0
  mysqldump.test, mysqldump.result:
    Bug #31077: post-commit fix.

Pushed into 5.0.52

Pushed into 5.1.23-beta

Pushed into 6.0.4-alpha

Noted in 5.0.52, 5.1.23, 6.0.4 changelogs.

mysqldump produces a "-- Dump completed on DATE" comment at the end of
the dump if --comments is given. The date causes dump files for
identical data take at different times to appear to be different. The
new options --dump-date and --skip-dump-date control whether the date
is added to the comment. --skip-dump-date suppresses date printing.
The default is --dump-date (include the date in the comment).