bzr commit into mysql-6.0 branch (cbell:2635) Bug#33573

[Chuck Bell]

#At file:///D:/source/bzr/mysql-6.0-bug-33573/

 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.
modified:
  mysql-test/r/backup.result
  mysql-test/t/backup.test
  sql/backup/be_default.cc
  sql/backup/be_default.h
  sql/si_objects.cc

per-file messages:
  mysql-test/r/backup.result
    New result file.
  mysql-test/t/backup.test
    New test added to test backup and restore of timestamp columns
  sql/backup/be_default.cc
    Added code to save the original value of timestamp_field_type before a restore,
    set it to TIMESTAMP_NO_AUTO_SET (which allows writing specific values to the field),
    and resetting the variable to the original setting after restore.
  sql/backup/be_default.h
    Added variable to save timestamp_field_type original value before restore.
  sql/si_objects.cc
    Corrected error found during testing where timestamp values were not
    being generated correctly for client that ran restore.
=== modified file 'mysql-test/r/backup.result'
--- a/mysql-test/r/backup.result	2008-05-14 16:28:33 +0000
+++ b/mysql-test/r/backup.result	2008-06-12 19:54:19 +0000
@@ -557,5 +557,54 @@ id	ccode
 1	aa
 2	bb
 4	dd
+DROP TABLE bup_default.t1;
+DROP TABLE bup_default.t2;
+DROP TABLE bup_default.t1_blob;
+DROP TABLE bup_default.wide;
+Create table with timestamp and populate.
+CREATE TABLE bup_default.time_t1 (a INT, b TIMESTAMP);
+Set known timestamp value and insert a row
+SET TIMESTAMP= UNIX_TIMESTAMP('2008-06-10 17:00:00');
+INSERT INTO bup_default.time_t1 VALUES (1, NULL);
+Set known timestamp value and insert a row
+SET TIMESTAMP= UNIX_TIMESTAMP('2008-06-11 08:15:21');
+INSERT INTO bup_default.time_t1 VALUES (2, NULL);
+Set known timestamp value and insert a row
+SET TIMESTAMP= UNIX_TIMESTAMP('2008-06-12 00:00:59');
+INSERT INTO bup_default.time_t1 VALUES (3, NULL);
+Show the data
+SELECT * FROM bup_default.time_t1;
+a	b
[phone]:00:00
[phone]:15:21
[phone]:00:59
+Backup the database.
+BACKUP DATABASE bup_default TO 'bup_default_timestamp.bak';
+backup_id
+#
+Restore the database.
+RESTORE FROM 'bup_default_timestamp.bak';
+backup_id
+#
+Show data after restore (timestamp should be same as above).
+SELECT * FROM bup_default.time_t1;
+a	b
[phone]:00:00
[phone]:15:21
[phone]:00:59
+Set known timestamp value and insert a row
+SET TIMESTAMP= UNIX_TIMESTAMP('2008-06-13 23:59:59');
+INSERT INTO bup_default.time_t1 VALUES (4, NULL);
+Set known timestamp value and insert a row
+SET TIMESTAMP= UNIX_TIMESTAMP('2008-04-01 00:00:01');
+INSERT INTO bup_default.time_t1 VALUES (5, NULL);
+Show data that was added after restore
+SELECT * FROM bup_default.time_t1;
+a	b
[phone]:00:00
[phone]:15:21
[phone]:00:59
[phone]:59:59
[phone]:00:01
 DROP DATABASE IF EXISTS bup_delete;
 DROP DATABASE IF EXISTS bup_default;

=== modified file 'mysql-test/t/backup.test'
--- a/mysql-test/t/backup.test	2008-05-14 16:28:33 +0000
+++ b/mysql-test/t/backup.test	2008-06-12 19:54:19 +0000
@@ -422,12 +422,62 @@ RESTORE FROM 'bup_delete.bak';
 --echo show the data
 SELECT * FROM bup_delete.me;
 
+DROP TABLE bup_default.t1;
+DROP TABLE bup_default.t2;
+DROP TABLE bup_default.t1_blob;
+DROP TABLE bup_default.wide;
+
+#
+# BUG#33573 : Check timestamp field values on restore.
+#
+
+--echo Create table with timestamp and populate.
+CREATE TABLE bup_default.time_t1 (a INT, b TIMESTAMP);
+
+--echo Set known timestamp value and insert a row
+SET TIMESTAMP= UNIX_TIMESTAMP('2008-06-10 17:00:00');
+INSERT INTO bup_default.time_t1 VALUES (1, NULL);
+
+--echo Set known timestamp value and insert a row
+SET TIMESTAMP= UNIX_TIMESTAMP('2008-06-11 08:15:21');
+INSERT INTO bup_default.time_t1 VALUES (2, NULL);
+
+--echo Set known timestamp value and insert a row
+SET TIMESTAMP= UNIX_TIMESTAMP('2008-06-12 00:00:59');
+INSERT INTO bup_default.time_t1 VALUES (3, NULL);
+
+--echo Show the data
+SELECT * FROM bup_default.time_t1;
+
+--echo Backup the database.
+--replace_column 1 #
+BACKUP DATABASE bup_default TO 'bup_default_timestamp.bak';
+
+--echo Restore the database.
+--replace_column 1 #
+RESTORE FROM 'bup_default_timestamp.bak';
+
+--echo Show data after restore (timestamp should be same as above).
+SELECT * FROM bup_default.time_t1;
+
+--echo Set known timestamp value and insert a row
+SET TIMESTAMP= UNIX_TIMESTAMP('2008-06-13 23:59:59');
+INSERT INTO bup_default.time_t1 VALUES (4, NULL);
+
+--echo Set known timestamp value and insert a row
+SET TIMESTAMP= UNIX_TIMESTAMP('2008-04-01 00:00:01');
+INSERT INTO bup_default.time_t1 VALUES (5, NULL);
+
+--echo Show data that was added after restore
+SELECT * FROM bup_default.time_t1;
+
 --disable_warnings
 DROP DATABASE IF EXISTS bup_delete;
 DROP DATABASE IF EXISTS bup_default;
 --enable_warnings
 
 --remove_file $MYSQLTEST_VARDIR/master-data/bup_delete.bak
+--remove_file $MYSQLTEST_VARDIR/master-data/bup_default_timestamp.bak
 --remove_file $MYSQLTEST_VARDIR/master-data/bup_default.bak
 --error 0,1
 --remove_file $MYSQLTEST_VARDIR/master-data/test.ba

=== modified file 'sql/backup/be_default.cc'
--- a/sql/backup/be_default.cc	2008-06-12 17:43:47 +0000
+++ b/sql/backup/be_default.cc	2008-06-12 19:54:19 +0000
@@ -699,6 +699,11 @@ int Restore::next_table()
     cur_table= tables_in_backup->table;
   else
   {
+    /*
+      Restore original timestamp autoset type.
+    */
+    if (cur_table && cur_table->timestamp_field)
+      cur_table->timestamp_field_type= old_tm;
     tables_in_backup= tables_in_backup->next_global;
     if (tables_in_backup != NULL)
     {
@@ -711,6 +716,16 @@ int Restore::next_table()
       DBUG_RETURN(-1);
     }
   } 
+  /*
+    Save original timestamp autoset type and switch to TIMESTAMP_NO_AUTO_SET
+    so that any restored data with timestamp fields will not have their values
+    reset on write.
+  */
+  if (cur_table && cur_table->timestamp_field)
+  {
+    old_tm= cur_table->timestamp_field->get_auto_set_type();
+    cur_table->timestamp_field_type= TIMESTAMP_NO_AUTO_SET;
+  }
   DBUG_RETURN(0);
 }
 

=== modified file 'sql/backup/be_default.h'
--- a/sql/backup/be_default.h	2008-06-12 17:43:47 +0000
+++ b/sql/backup/be_default.h	2008-06-12 19:54:19 +0000
@@ -213,6 +213,7 @@ class Restore: public Restore_driver
     THD *m_thd;                    ///< Pointer to current thread struct.
     TABLE_LIST *all_tables;        ///< Reference to list of tables used.
     ulonglong num_rows;            ///< Number of rows in table
+    timestamp_auto_set_type old_tm;///< Save old timestamp auto set type.
     my_bool m_cleanup;             ///< Is call to cleanup() needed?
 
     result_t cleanup();

=== modified file 'sql/si_objects.cc'
--- a/sql/si_objects.cc	2008-05-27 19:47:15 +0000
+++ b/sql/si_objects.cc	2008-06-12 19:54:19 +0000
@@ -56,7 +56,7 @@ int silent_exec(THD *thd, String *query)
   thd->query=         query->c_ptr();
   thd->query_length=  query->length();
 
-  thd->set_time(time(NULL));
+  thd->set_time();
   pthread_mutex_lock(&::LOCK_thread_count);
   thd->query_id= ::next_query_id();
   pthread_mutex_unlock(&::LOCK_thread_count);