How to Det. When Table Last Mod. (INSERT, UPDATE) 1

[smays]

Is there some way to determine when a table was last modified? I am using 5.0.27 community and as best as I can determine, there are no fields in either the mysql or the information_schema schemas (what is the plural version of schema?) that tell when a given table was last modified. Do some of the newer servers keep track of what tables are modified and when?

I have some software I wrote that twice daily creates SQL files. There are plenty of tables that do not get modified at all every day I would like to avoid backing them up every day.

If none of the servers keep track of when tables get modified, can I set up a trigger (?) so when an INSERT or UPDATE query is executed against a table something happens such as a mod_table is updated with a mod date? I can then check the date field of the mod_table to determine whether the table should be backed up.

Steve.

 

[vacunita]

You can use the "Show Table Status" query, and look at the
Update_Time field it returns.

Show Table Status FROM dbname WHERE name='mytablename';

----------------------------------
Phil AKA Vacunita
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[smays]

I tried the query, but the Update_Time was returned as NULL. I then updated a random record (a record I knew represented junk data), and repeated the SHOW query. The Update_Time still returned NULL. Is there some special way I am supposed to set up the table so the Update_Time is maintained somewhere and accessible via the SHOW query?

Thanks,
Steve.

 

[smays]

Well, duh, apparently the Update_Time field exists in the `TABLES` table in the information_schema schema. Unfortunately, the Update_Time fields for all the tables I created are NULL. If the information could have been useful, the fact the data is not updated(?) rends it useless. Does anybody think I have my schema ('des_center_mstr') set up 'incorrectly' so as not to track the Update_Time of each table?

Thanks,
Steve.

 

[vacunita]

Sorry, there are some caveats in using the Show Table Status that I forgot to mention:

When the data file was last updated. For some storage engines, this value is NULL. For example, InnoDB stores multiple tables in its tablespace and the data file timestamp does not apply. For MyISAM, the data file timestamp is used; however, on Windows the timestamp is not updated by updates so the value is inaccurate.

With that said, if you need such granular control, perhaps having a table that holds your table names and last modified time may be a better alternative.

Though you'd have to issue an insert or update on that other table after your perform operations on your regular tables.


----------------------------------
Phil AKA Vacunita
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[smays]

Vacunita,

I appreciate your help and research into my post. Although I think the trigger idea is worth considering, I would like to explore using either the `AUTO_INCREMENT` field or the `TABLE_ROWS` field from the `TABLES` table. I am thinking if I kept track of either of these two fields on a daily basis I could compare the values from the previous day to determine whether the associated tables have been updated.

Steve.

 

[vacunita]

You may be able to do it, if you have a way of storing the table's table_rows data, and then comparing them.

Though that would only tell you if insertions or deletions have happened. As the row count would be affected.

But if there were updates you would never know.

You could use the Data_length field as well, if its different than a prior value then you know the table has been modified in some way. Though its possible that an update will have no impact on the overall data length.








----------------------------------
Phil AKA Vacunita
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.