| Bug #44514 | si_objects do not preserve creation/alter time for objects | ||
|---|---|---|---|
| Submitted: | 28 Apr 2009 12:55 | Modified: | 2 Nov 2009 14:20 |
| Reporter: | Rafal Somla | Email Updates: | |
| Status: | Won't fix | Impact on me: | |
| Category: | MySQL Server: Backup | Severity: | S4 (Feature request) |
| Version: | 6.0 | OS: | Any |
| Assigned to: | Ingo Strüwing | CPU Architecture: | Any |
[28 Apr 2009 14:13]
Valeriy Kravchuk
Verified just as described with recent 6.0.11 from bzr: valeriy-kravchuks-macbook-pro:mysql-test openxs$ diff footprint.pre footprint.post 16c16 < CREATE_TIME: 2009-04-28 17:10:12 --- > CREATE_TIME: 2009-04-28 17:10:16 40c40 < UPDATE_TIME: 2009-04-28 17:10:12 --- > UPDATE_TIME: 2009-04-28 17:10:16 70,71c70,71 < CREATED: 2009-04-28 17:10:12 < LAST_ALTERED: 2009-04-28 17:10:12 --- > CREATED: 2009-04-28 17:10:16 > LAST_ALTERED: 2009-04-28 17:10:16 95,96c95,96 < CREATED: 2009-04-28 17:10:12 < LAST_ALTERED: 2009-04-28 17:10:12 --- > CREATED: 2009-04-28 17:10:16 > LAST_ALTERED: 2009-04-28 17:10:16
[28 Apr 2009 15:11]
Chuck Bell
I do not think this is a bug. Other systems do not preserve the creation time. At most, I would say this is a feature request and a low priority one at that.
[30 Apr 2009 14:34]
Chuck Bell
This is a feature request and not a bug. Note also that this is not supported in mysqldump. The table create and update times are taken from the file status of the .frm file. If you examine the sql_show.cc code you will see that this information is not stored in a table anywhere but rather read from the file when the SELECT is run. In order to accommodate this feature request, a significant will be required to force the file status information to change.
[30 Apr 2009 23:05]
Chuck Bell
For other objects, it is possible to alter the creation and update/modified (where appropriate) information as these values are stored in the mysql tables. Note that this would require an ALTER TABLE command inserted into the metadata from si_objects. The change to preserve file stat data is much more complicated. I recommend this be postponed to 6.1 and left as a feature request.
[4 May 2009 5:36]
Rafal Somla
Small comment: the eventual ALTER statements which would change create/alter times do not necessarily have to be stored as metadata. They could be constructed and executed by RESTORE command, e.g. based on the validity point information already stored in the image.
[2 Nov 2009 14:20]
Ingo Strüwing
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.
Description: Si_objects contains a service for serializing server objects and then re-creating them from the serialization string. This is used by BACKUP/RESTORE commands. After serialize/re-create cycle, the creation time and/or alter time for some objects is not preserved. This can be seen in the attached test. Note: it is not obvious if creation/modification time should be preserved by backup/restore operations. Depending on the design decision made, this is a bug or not. How to repeat: Run the following test case: --------------------8<--------------------- --source include/have_innodb.inc CREATE USER usr1; CREATE DATABASE db1 CHARACTER SET utf8; USE db1; # table (creation time diff noted only for InnoDB tables) CREATE TABLE t1 (a int) ENGINE=InnoDB; CREATE TABLE t2 (a int) ENGINE=MyISAM; # routine CREATE FUNCTION f4 () RETURNS int RETURN 0; #RETURNS ENUM( 'w' ) CHARACTER SET ucs2 #RETURN 0; # event CREATE DEFINER=usr1 EVENT e1 ON SCHEDULE AT now() + INTERVAL 1 YEAR DO SET @foo=1; # trigger CREATE DEFINER= CURRENT_USER TRIGGER r1 AFTER INSERT ON t2 FOR EACH ROW SET @foo=1; --error 0,1 --remove_file check_objects.sql --write_file check_objects.sql USE information_schema; SELECT * FROM tables WHERE table_schema LIKE 'db%'; SELECT * FROM routines WHERE routine_schema LIKE 'db%'; SELECT * FROM events WHERE event_schema LIKE 'db%'; SELECT * FROM triggers WHERE trigger_schema LIKE 'db%'; EOF --exec $MYSQL --vertical <check_objects.sql >footprint.pre BACKUP DATABASE db1 TO 'db1.bak'; DROP DATABASE db1; sleep 3; # so that create/alter time changes RESTORE FROM 'db1.bak'; --exec $MYSQL --vertical <check_objects.sql >footprint.post --diff_files footprint.pre footprint.post DROP DATABASE db1; DROP USER usr1; --exit -------------------->8--------------------- The test should fail on --diff_files command. Use diff on footprint.{pre,post} to see which attributes were not preserved. Suggested fix: If it is decided that creation/alter time of objects should be preserved, Fix si_objects so that this information is stored in the serialization string and then used when re-creating objects.