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.
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.