Bug #33573 Backup: timestamp column gets current default
Submitted: 29 Dec 2007 1:04 Modified: 11 Aug 2008 20:30
Reporter: Peter Gulutzan Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Backup Severity:S3 (Non-critical)
Version:6.0.5-alpha-debug OS:Linux (SUSE 10 64-bit)
Assigned to: Chuck Bell CPU Architecture:Any

[29 Dec 2007 1:04] Peter Gulutzan 
Description:
I create a table with a TIMESTAMP column.
I insert a value.
I say BACKUP DATABASE.
I say RESTORE.
I select from the table.
The value is not what I originally inserted.

Built from mysql-6.0-backup source with BUILD/compile-pentium-debug-max.
Last changeset = "ChangeSet@1.2753, 2007-12-20 15:32:22-05:00".

How to repeat:
use test
drop database wi;
create database wi;
use wi
create table t (s1 timestamp default current_timestamp);
insert into t values (null);
select * from t;
select sleep(1);
backup database wi to '/usr/local/mysql/var/t70';
drop database wi;
restore from '/usr/local/mysql/var/t70';
select * from t;
[29 Dec 2007 6:13] MySQL Verification Team 
Thank you for the bug report.
[25 Jan 2008 2:38] Chuck Bell 
The cause of this anomoly has been identified. On restore, the code is not correctly disabling the TIMESTAMP auto set control for the table. The code should follow the same mechanism that replication follows to disable the TIMESTAMP auto set by turning it off using:

===== sql/backup/be_default.cc 1.9 vs edited =====
--- 1.9/sql/backup/be_default.cc	2008-01-24 21:36:26 -05:00
+++ edited/sql/backup/be_default.cc	2008-01-24 21:33:11 -05:00
@@ -602,6 +602,7 @@
   if ((last_write_res != 0) && (last_write_res != HA_ERR_WRONG_COMMAND))
     DBUG_RETURN(ERROR);
   num_rows= 0;
+  tbl->timestamp_field_type= TIMESTAMP_NO_AUTO_SET;
   DBUG_RETURN(OK);
 }

Note: This needs to be implemented for both the default and snapshot drivers.
[10 Jun 2008 20: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/commits/47711

2633 Chuck Bell	2008-06-10
      BUG#33573 Backup: timestamp column gets current default 
      
      Tables with timestamp column were being assigned incorrect values on restore.
      This patch corrects the problem where timestamp values were being regenerated
      on restore.
[10 Jun 2008 20:46] Chuck Bell 
Many thanks to Svoj for helping me fix a problem found during testing! Patch is (finally) ready for review.

http://lists.mysql.com/commits/47711
[12 Jun 2008 14:15] Rafal Somla 
I think that this patch will fix BUG#35806 - please check that.
[12 Jun 2008 18:33] 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/47813

2634 Chuck Bell	2008-06-12 [merge]
      Manual merge before push of BUG#33573
[12 Jun 2008 18:43] 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/47816

2635 Chuck Bell	2008-06-12
      BUG#33573 Backup: timestamp column gets current default 
      
      Tables with timestamp column were being assigned incorrect values on restore.
      This patch corrects the problem where timestamp values were being regenerated
      on restore.
[12 Jun 2008 19:55] 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/47818

2635 Chuck Bell	2008-06-12
      BUG#33573 Backup: timestamp column gets current default 
      
      Tables with timestamp column were being assigned incorrect values on restore.
      This patch corrects the problem where timestamp values were being regenerated
      on restore.
[12 Jun 2008 19:55] 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/47819

2635 Chuck Bell	2008-06-12
      BUG#33573 Backup: timestamp column gets current default 
      
      Tables with timestamp column were being assigned incorrect values on restore.
      This patch corrects the problem where timestamp values were being regenerated
      on restore.
[12 Jun 2008 19:57] 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/47820

2635 Chuck Bell	2008-06-12
      BUG#33573 Backup: timestamp column gets current default 
      
      Tables with timestamp column were being assigned incorrect values on restore.
      This patch corrects the problem where timestamp values were being regenerated
      on restore.
[12 Jun 2008 19:57] Chuck Bell 
Patch queued.

Note: had to recommit patch due to bzr merge issue (yet unresolved) on Windows.

New commit (same patch) is:

http://lists.mysql.com/commits/47818
[8 Aug 2008 13:29] Chuck Bell 
Confirmed patch is in main.
[8 Aug 2008 16:29] Chuck Bell 
Fix was pushed into 6.0.6.
[11 Aug 2008 20:30] Paul DuBois 
Noted in 6.0.6 changelog.

TIMESTAMP columns were restored to the current date and time (not
their actual values) by a RESTORE operation.
[13 Sep 2008 22:08] Bugs System 
Pushed into 6.0.6-alpha  (revid:cbell@mysql.com-20080612195419-f6boi205fzcvc0ch) (version source revid:hakan@mysql.com-20080716105246-eg0utbybp122n2w9) (pib:3)