Bug #50454 Creation time changes for database objects after restore.
Submitted: 19 Jan 2010 19:44 Modified: 21 Jan 2010 13:54
Reporter: Hema Sridharan Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Backup Severity:S3 (Non-critical)
Version:5.6.0, 6.0.14 OS:Any
Assigned to: CPU Architecture:Any
Triage: Needs Triage: D3 (Medium)

[19 Jan 2010 19:44] Hema Sridharan
Description:
Create database db1 and table db1.t1
Create procedures and functions in db1
Check the creation and modification time for objects.
Execute backup of database db1
Drop the database
Perform restore and check the time when restore was performed
Now check the creation time of all the objects (tables, procedures and functions)

We can notice that create time for procedures and functions are changed after restore and shows the time when restore was performed.
Database objects should always show the "create time" when they are actually created.

Create database db1;
Use db1;
create table t1(id int);
create procedure foo42() insert into db1.t1 values(10);
create function f1()returns integer return (select count(*) from db1.t1);
Show procedure status where Db='db1'\G
Show function status where Db='db1'\G
Backup database db1 to 'db1.bak';
Drop database db1;
Restore from 'db1.bak';
# check the create and modified time for procedure and functions
Show procedure status where Db='db1'\G
Show function status where Db='db1'\G
show table status;

How to repeat:
mysql> Create database db1;
Query OK, 1 row affected (0.00 sec)

mysql> Use db1;
create table t1(id int);
Database changed
mysql> create table t1(id int);
Query OK, 0 rows affected (0.03 sec)

mysql> create procedure foo42() insert into db1.t1 values(10);
Query OK, 0 rows affected (0.01 sec)

mysql> create function f1()returns integer return (select count(*) from db1.t1);
Query OK, 0 rows affected (0.00 sec)

mysql> Show procedure status where Db='db1'\G
*************************** 1. row ***************************
                  Db: db1
                Name: foo42
                Type: PROCEDURE
             Definer: root@localhost
            Modified: 2010-01-19 22:18:22 
             Created: 2010-01-19 22:18:22 ====> Creation time
       Security_type: DEFINER
             Comment:
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci
1 row in set (0.01 sec)

mysql> Show function status where Db='db1'\G
*************************** 1. row ***************************
                  Db: db1
                Name: f1
                Type: FUNCTION
             Definer: root@localhost
            Modified: 2010-01-19 22:18:22
             Created: 2010-01-19 22:18:22 ====> Creation time
       Security_type: DEFINER
             Comment:
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci
1 row in set (0.00 sec)

mysql> show table status\G
*************************** 1. row ***************************
           Name: t1
         Engine: MyISAM
        Version: 10
     Row_format: Fixed
           Rows: 0
 Avg_row_length: 0
    Data_length: 0
Max_data_length: 1970324836974591
   Index_length: 1024
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2010-01-19 22:18:22 ====> Creation time
    Update_time: 2010-01-19 22:18:22 
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

mysql> Backup database db1 to 'db1.bak';
+-----------+
| backup_id |
+-----------+
| 276       |
+-----------+
1 row in set (0.49 sec)

mysql> Drop database db1;
Query OK, 1 row affected (0.00 sec)

mysql> Restore from 'db1.bak';
+-----------+
| backup_id |
+-----------+
| 277       |
+-----------+
1 row in set (0.07 sec)

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2010-01-19 22:18:56 |
+---------------------+
1 row in set (0.00 sec)

mysql> Show procedure status where Db='db1'\G
*************************** 1. row ***************************
                  Db: db1
                Name: foo42
                Type: PROCEDURE
             Definer: root@localhost
            Modified: 2010-01-19 22:18:51
             Created: 2010-01-19 22:18:51 ===> The creation time is changed Restore time
       Security_type: DEFINER
             Comment:
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci
1 row in set (0.01 sec)

mysql> Show function status where Db='db1'\G
*************************** 1. row ***************************
                  Db: db1
                Name: f1
                Type: FUNCTION
             Definer: root@localhost
            Modified: 2010-01-19 22:18:51
             Created: 2010-01-19 22:18:51 ==> Creation time is changed
       Security_type: DEFINER
             Comment:
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci
1 row in set (0.00 sec)

mysql> show table status\G
*************************** 1. row ***************************
           Name: t1
         Engine: MyISAM
        Version: 10
     Row_format: Fixed
           Rows: 0
 Avg_row_length: 0
    Data_length: 0
Max_data_length: 1970324836974591
   Index_length: 1024
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2010-01-19 22:18:22 ==> Creation time not changed.
    Update_time: 2010-01-19 22:18:51 
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options:
        Comment:

Notice that for procedures and functions, the create time gets changed to time when restore was performed. In tables this does not happen.

Suggested fix:
The creation time should remain the same and should not change after restore is performed.
[19 Jan 2010 22:33] Sveta Smirnova
Thank you for the report.

Verified as described.
[21 Jan 2010 13:54] Chuck Bell
This is a duplicate of BUG#44514 which is marked as "won't fix".

[from the bug report]

Backup team decided, not to fix this, on its meeting on 2009-09-29.

Documented in the MySQL Backup manual Chapter 5, "Restrictions on MySQL Backup":

Restore operations create database objects with a creation or modification time of their creation within the restore operation. For some database objects, such as stored procedures, timestamps for creation or modification are maintained in system tables. These are not reset to the value they had at backup time.