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:
None 
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
Description:
For file-per-table InnoDB, it appears the Create_time in show table status is set to the last update time, while update_time is NULL.

-rwxrwxr-x 1 mysql mysql    8560 10 Apr 2006 tbl.frm
-rwxrwxr-x 1 mysql mysql 114688 29 Nov 17:04 tbl.ibd

mysql>show table status like 'wu_sites'\G
*************************** 1. row ***************************
Name: tbl
Engine: InnoDB
Version: 9
Row_format: Fixed
Rows: 63
Avg_row_length: 260
Data_length: 16384
Max_data_length: NULL
Index_length: 16384
Data_free: 0
Auto_increment: NULL
Create_time: 2006-11-29 17:04:19
Update_time: NULL
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment: InnoDB free: 0 kB
1 row in set (0.01 sec)

How to repeat:
Enable InnoDB file-per-table, create a table, do an update, check info output with show table status.
[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)