MySQL Bugs: #4164: update

Bug #4164	update_time property
Submitted:	16 Jun 2004 13:52	Modified:	19 Aug 2008 14:18
Reporter:	d-jay Expell	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	4.0	OS:	Windows (WinXp Home SP1)
Assigned to:	Sergei Golubchik	CPU Architecture:	Any

Description:
The update_time table property, only set their value when i change a record. The property must be automaticaly set when the user adds or changes a record.

 

How to repeat:
Insert a new record and watch if the update_time property was chaged.

Suggested fix:
The property must be automaticaly set when the user adds or changes a record.

Notice that when a new row is inserted, the property is changed
however the update no.

C:\mysql\bin>mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 4.0.20a-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table nu (id int);
Query OK, 0 rows affected (0.09 sec)

mysql> show table status from test\G
*************************** 1. row ***************************
           Name: nu
           Type: MyISAM
     Row_format: Fixed
           Rows: 0
 Avg_row_length: 0
    Data_length: 0
Max_data_length: 21474836479
   Index_length: 1024
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2004-06-18 00:09:57
    Update_time: 2004-06-18 00:09:57
     Check_time: NULL
 Create_options:
        Comment:
1 row in set (0.01 sec)

mysql> insert into nu values (1);
Query OK, 1 row affected (0.00 sec)

mysql> show table status from test\G
*************************** 1. row ***************************
           Name: nu
           Type: MyISAM
     Row_format: Fixed
           Rows: 1
 Avg_row_length: 5
    Data_length: 5
Max_data_length: 21474836479
   Index_length: 1024
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2004-06-18 00:09:57
    Update_time: 2004-06-18 00:10:21
     Check_time: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

mysql> update nu set id=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> show table status from test\G
*************************** 1. row ***************************
           Name: nu
           Type: MyISAM
     Row_format: Fixed
           Rows: 1
 Avg_row_length: 5
    Data_length: 5
Max_data_length: 21474836479
   Index_length: 1024
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2004-06-18 00:09:57
    Update_time: 2004-06-18 00:10:21
     Check_time: NULL
 Create_options:
        Comment:
1 row in set (0.01 sec)

mysql>

Works for me. Miguel please check whether it's a windows-specific problem

Yes Sergei it is Windows specify I tested with latest BK source tree
both on Suse and Windows.

It's how Windows work, we cannot change it.

MyISAM uses file's timestamp  for Update_time that you see in SHOW TABLE STATUS. Windows does not update timestamp of the open file (according to my experiments) even if you do fflush().

Workaround for you is to use FLUSH TABLES - MySQL will close and reopen all tables and file's timestamp will be updated.

Cant reproduce, with miguels test:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.68-debug-log Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table nu (id int);
Query OK, 0 rows affected (0.02 sec)

mysql> show table status from test\G
*************************** 1. row ***************************
           Name: nu
         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: 2008-08-19 17:13:11
    Update_time: 2008-08-19 16:13:11
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

mysql> insert into nu values(1);
Query OK, 1 row affected (0.00 sec)

mysql> show table status from test\G
*************************** 1. row ***************************
           Name: nu
         Engine: MyISAM
        Version: 10
     Row_format: Fixed
           Rows: 1
 Avg_row_length: 7
    Data_length: 7
Max_data_length: 1970324836974591
   Index_length: 1024
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2008-08-19 17:13:11
    Update_time: 2008-08-19 16:14:25
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

mysql> update nu set id=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> show table status from test\G
*************************** 1. row ***************************
           Name: nu
         Engine: MyISAM
        Version: 10
     Row_format: Fixed
           Rows: 1
 Avg_row_length: 7
    Data_length: 7
Max_data_length: 1970324836974591
   Index_length: 1024
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2008-08-19 17:13:11
    Update_time: 2008-08-19 16:14:45
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

An intersting detail is that create time seems to be 1 hour ahead, but it is not what the bug is about