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

[18 Sep 2007 9:19] Edward Buck
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.
[18 Sep 2007 16:27] Miguel Solorzano
Thank you for the bug report.
[27 Sep 2007 12:53] 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/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.
[27 Sep 2007 13:06] 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/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.
[1 Oct 2007 15:05] 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/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.
[1 Oct 2007 15:17] 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/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.
[2 Oct 2007 21:32] 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/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.
[3 Oct 2007 6:17] 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/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.
[29 Oct 2007 8:43] Bugs System
Pushed into 5.0.52
[29 Oct 2007 8:46] Bugs System
Pushed into 5.1.23-beta
[29 Oct 2007 8:49] Bugs System
Pushed into 6.0.4-alpha
[15 Nov 2007 14:47] Paul Dubois
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).