Bug #31077 mysqldump appending date to dump breaks dump semantics
Submitted: 18 Sep 2007 11:19 Modified: 15 Nov 2007 15:47
Reporter: Edward Buck
Status: Closed
Category:Server: Backup Severity:S3 (Non-critical)
Version:5.0.32 OS:Linux (Debian Etch)
Assigned to: Gleb Shchepa Target Version:

[18 Sep 2007 11: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 18:27] Miguel Solorzano 
Thank you for the bug report.
[27 Sep 2007 14: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 15: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 17: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 17: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 23: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 8: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 9:43] Bugs System 
Pushed into 5.0.52
[29 Oct 2007 9:46] Bugs System 
Pushed into 5.1.23-beta
[29 Oct 2007 9:49] Bugs System 
Pushed into 6.0.4-alpha
[15 Nov 2007 15: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).