| Bug #24712 | SHOW TABLE STATUS for file-per-table showing incorrect time fields | ||
|---|---|---|---|
| Submitted: | 30 Nov 2006 1:50 | Modified: | 20 Jun 2010 1:14 |
| Reporter: | Arjen Lentz | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: InnoDB storage engine | Severity: | S3 (Non-critical) |
| Version: | 4.1, 5.0 BK | OS: | MacOS (OS X, Linux) |
| Assigned to: | Heikki Tuuri | CPU Architecture: | Any |
[30 Nov 2006 1:50]
Arjen Lentz
[30 Nov 2006 10:43]
Heikki Tuuri
4.1.16, ::info() in ha_innodb.cc:
if (flag & HA_STATUS_TIME) {
/* In sql_show we call with this flag: update then statistics
so that they are up-to-date */
prebuilt->trx->op_info = (char*)"updating table statistics";
dict_update_statistics(ib_table);
prebuilt->trx->op_info = (char*)
"returning various info to MySQL";
if (ib_table->space != 0) {
my_snprintf(path, sizeof(path), "%s/%s%s",
mysql_data_home, ib_table->name,
".ibd");
unpack_filename(path,path);
} else {
my_snprintf(path, sizeof(path), "%s/%s%s",
mysql_data_home, ib_table->name,
reg_ext);
unpack_filename(path,path);
}
/* Note that we do not know the access time of the table,
nor the CHECK TABLE time, nor the UPDATE or INSERT time. */
if (os_file_get_status(path,&stat_info)) {
create_time = stat_info.ctime;
}
}
os0file.c:
int ret;
struct stat statinfo;
ret = stat(path, &statinfo);
if (ret && (errno == ENOENT || errno == ENOTDIR)) {
/* file does not exist */
return(FALSE);
} else if (ret) {
/* file exists, but stat call failed */
os_file_handle_error_no_exit(path, "stat");
return(FALSE);
}
if (S_ISDIR(statinfo.st_mode)) {
stat_info->type = OS_FILE_TYPE_DIR;
} else if (S_ISLNK(statinfo.st_mode)) {
stat_info->type = OS_FILE_TYPE_LINK;
} else if (S_ISREG(statinfo.st_mode)) {
stat_info->type = OS_FILE_TYPE_FILE;
} else {
stat_info->type = OS_FILE_TYPE_UNKNOWN;
}
stat_info->ctime = statinfo.st_ctime;
stat_info->atime = statinfo.st_atime;
stat_info->mtime = statinfo.st_mtime;
stat_info->size = statinfo.st_size;
return(TRUE);
[30 Nov 2006 12:36]
Sveta Smirnova
Removed wrong defined Linux from OS field.
On Mac OS X it repeatable on 5.0 BK sources:
mysql> show variables like 'innodb%';
+---------------------------------+--------------------------------------+
| Variable_name | Value |
+---------------------------------+--------------------------------------+
......
| innodb_file_per_table | ON |
......
+---------------------------------+--------------------------------------+
33 rows in set (0.00 sec)
mysql> create table t2(id int) engine =innodb;
Query OK, 0 rows affected (0.01 sec)
mysql> show table status like 't2'\G
*************************** 1. row ***************************
Name: t2
Engine: InnoDB
......
Create_time: 2006-11-30 15:33:05
Update_time: NULL
......
Comment: InnoDB free: 0 kB
1 row in set (0.00 sec)
mysql> insert into t2 values(7);
Query OK, 1 row affected (0.00 sec)
mysql> show table status like 't2'\G
*************************** 1. row ***************************
Name: t2
Engine: InnoDB
......
Create_time: 2006-11-30 15:33:25
Update_time: NULL
......
1 row in set (0.01 sec)
mysql>
[30 Nov 2006 12:47]
Heikki Tuuri
I am testing the latest 5.0. Strange, I do see with ls -l that the 'ctime' of k.ibd changes:
mysql> update k set a = 30;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0
mysql> show table status like 'k'\G
*************************** 1. row ***************************
Name: k
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 1
Avg_row_length: 16384
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: NULL
Create_time: 2006-11-30 14:38:05
Update_time: NULL
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment: InnoDB free: 0 kB
1 row in set (0.05 sec)
mysql> show table status like 'k'\G
*************************** 1. row ***************************
Name: k
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 1
Avg_row_length: 16384
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: NULL
Create_time: 2006-11-30 14:43:17
Update_time: NULL
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment: InnoDB free: 0 kB
1 row in set (0.23 sec)
mysql>
But what inode operation does a simple write to the .ibd file do? Could fsync() update the ctime field?
man 2 stat:
...
They all return a stat structure, which contains the following fields:
struct stat {
dev_t st_dev; /* device */
ino_t st_ino; /* inode */
mode_t st_mode; /* protection */
nlink_t st_nlink; /* number of hard links */
uid_t st_uid; /* user ID of owner */
gid_t st_gid; /* group ID of owner */
dev_t st_rdev; /* device type (if inode device) */
off_t st_size; /* total size, in bytes */
blksize_t st_blksize; /* blocksize for filesystem I/O */
blkcnt_t st_blocks; /* number of blocks allocated */
time_t st_atime; /* time of last access */
time_t st_mtime; /* time of last modification */
time_t st_ctime; /* time of last status change */
};
The value st_size gives the size of the file (if it is a regular file
or a symlink) in bytes. The size of a symlink is the length of the
pathname it contains, without trailing NUL.
The value st_blocks gives the size of the file in 512-byte blocks.
(This may be smaller than st_size/512 e.g. when the file has holes.)
The value st_blksize gives the "preferred" blocksize for efficient file
system I/O. (Writing to a file in smaller chunks may cause an ineffi-
cient read-modify-rewrite.)
Not all of the Linux filesystems implement all of the time fields.
Some file system types allow mounting in such a way that file accesses
do not cause an update of the st_atime field. (See `noatime' in
mount(8).)
The field st_atime is changed by file accesses, e.g. by execve(2),
mknod(2), pipe(2), utime(2) and read(2) (of more than zero bytes).
Other routines, like mmap(2), may or may not update st_atime.
The field st_mtime is changed by file modifications, e.g. by mknod(2),
truncate(2), utime(2) and write(2) (of more than zero bytes). More-
over, st_mtime of a directory is changed by the creation or deletion of
files in that directory. The st_mtime field is not changed for changes
in owner, group, hard link count, or mode.
The field st_ctime is changed by writing or by setting inode informa-
tion (i.e., owner, group, link count, mode, etc.).
...
Linux 127 2.6.8-2-386 #1 Tue Aug 16 12:46:35 UTC 2005 i686 GNU/Linux
[30 Nov 2006 13:04]
Heikki Tuuri
The fix is simply to remove this from ha_innodb.cc
if (ib_table->space != 0) {
my_snprintf(path, sizeof(path), "%s/%s%s",
mysql_data_home, ib_table->name,
".ibd");
unpack_filename(path,path);
[30 Nov 2006 15:07]
Ware Adams
We submitted this via MySQL Enterprise. Would it be possible to have the code that caused the bug in the create_time field fill in a value for the modified_time field for InnoDB tables stored in file_per_table? It would be very helpful for us to be able to tell which tables have changed without access to the local file system. Thanks, Ware
[30 Nov 2006 15:24]
Heikki Tuuri
Ware, the file modification time that stat() shows is not the actual modification time of the table, since the modification first goes to the buffer pool and is only later written to the .ibd file. But maybe you would benefit also from this kind of an approximate modified time? Regards, Heikki
[30 Nov 2006 15:33]
Ware Adams
Heikki, I figured that was the case as I watched the ibd file time change up to a minute after I ran an update. Still, even this is good enough for our purposes. We have dozens of 10 GB tables that we'd like to always have a mysqldump copy of. Many of them change only once every few months, though we don't know exactly when they will change. Right now we back them up weekly, but even this approximation would let us shift to backing them up only on days they change. Thanks, Ware
[30 Nov 2006 15:47]
Heikki Tuuri
Ware, hmm... the problem is that people will believe that SHOW TABLE STATUS shows the actual modification time if it is not NULL. We already have a lot of confusion from the fact that the row count is only approximate. I am sorry but I do not dare to introduce an approximate modification time to SHOW TABLE STATUS. But note that before the above patch is applied, versions of InnoDB show the .ibd file modification time as the create time! When InnoDB writes to the .ibd file, it always calls fsync() after that. And the above tests suggests that fsync() modifies the inode and therefore the 'ctime', which shows as the table create time in SHOW TABLE STATUS. Regards, Heikki
[30 Nov 2006 16:14]
Ware Adams
Heikki, I understand, though I don't understand why that confuses so many people. Still, this would _really_ help us make backups more efficient. Would you consider putting this data in the Comment field, maybe labeled as something other than update time so it doesn't cause support headaches? I have to believe others doing table level backups would benefit from it. Thanks, Ware
[5 Dec 2006 17:23]
Ware Adams
Heikki, Would it be possible to note the .ibd file modification time in the comments section of SHOW TABLE STATUS as the InnoDB free space is currently done? Thanks, Ware
[7 Dec 2006 14:07]
Heikki Tuuri
Ware, the problem is that the comment field is already overcrowded, and some users have complained about the extra information that is shown there. Regards, Heikki
[23 Jan 2007 21:00]
Paul DuBois
Noted in 5.0.32 changelog. With innodb_file_per_table enabled, InnoDB displayed incorrect file times in the output from SHOW TABLE STATUS.
[10 Feb 2007 17:52]
Mark Callaghan
Why does the handler make all implementations (InnoDB, MyISAM, ...) implement storage and retrieve of create_time? Wouldn't it be better for this to be implemented once (in handler.cc). As it is, InnoDB uses values from the stat system call, and this is the create time for the file, not the create time for the table.
[7 Mar 2007 0:01]
Arjen Lentz
"the patch" ... which patch? what does the patch do? Over the course of the bug report, various issues have been discussed, but it mainly came down to Heikki not wanting to fix the exact issue that the bug described. I'm not debating that decision, but it makes a reference to "the patch" unclear. In the end, what was actually changed? What is the new behaviour? Please clarify Thanks
[22 May 2007 4:23]
Trent Lloyd
Has there been any updates on this or to the question Arjen asked?
[5 May 2010 15:16]
Bugs System
Pushed into 5.1.47 (revid:joro@sun.com-20100505145753-ivlt4hclbrjy8eye) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[6 May 2010 2:32]
Paul DuBois
Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug. Re-closing.
[28 May 2010 5:46]
Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100524190136-egaq7e8zgkwb9aqi) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (pib:16)
[28 May 2010 6:16]
Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20100524190941-nuudpx60if25wsvx) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[28 May 2010 6:43]
Bugs System
Pushed into 5.5.5-m3 (revid:alik@sun.com-20100524185725-c8k5q7v60i5nix3t) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[29 May 2010 23:16]
Paul DuBois
Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug. Re-closing.
[17 Jun 2010 11:46]
Bugs System
Pushed into 5.1.47-ndb-7.0.16 (revid:martin.skold@mysql.com-20100617114014-bva0dy24yyd67697) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[17 Jun 2010 12:23]
Bugs System
Pushed into 5.1.47-ndb-6.2.19 (revid:martin.skold@mysql.com-20100617115448-idrbic6gbki37h1c) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[17 Jun 2010 13:11]
Bugs System
Pushed into 5.1.47-ndb-6.3.35 (revid:martin.skold@mysql.com-20100617114611-61aqbb52j752y116) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
