wrong update_time from SHOW TABLE STATUS

Hi, i want to retreive the time that a table in my database was last updated.
I currently have the following query that returns an update_time but it appears to be wrong as when i perform an update on the data in the table the time normally remains the same.

SHOW TABLE STATUS LIKE 'tests'

Also is their anyway to restrict the values this query returns to just update_time as the rest are not required?
Then format the date returned possibly using DATE_FORMAT to another arrangement or to a unix timestamp?

Thanks
Dan

PS i'm running MySQL 4.1.14
and the table type is MyISAM
LVL 4
Dan_82Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

star_trekCommented:
If you have the table test
You can do ls from command prompt on that particular database/folder
ls -l test.MYD
It will give you last updated date and time.
Dan_82Author Commented:
Thanks, but i need to do it througth an SQL statement
todd_farmerCommented:
SHOE TABLE STATUS shows the last time the table was altered.  There is no mechanism in MySQL to do what you are wanting to do - you need to add a TIMESTAMP column to the table to track changes to the data contents.
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

Dan_82Author Commented:
Thanks, i thought it maybe something like that, but was suprised that it updated the time after and INSERT but not an UPDATE, if their anything else that can cause this value change other then altering the table structure and performing an insert?

Dan
todd_farmerCommented:
I'm sorry - I was wrong.  Here's what the MySQL documentation says on it:

#

Update_time

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.

todd_farmerCommented:
It seems like issuing a FLUSH TABLES command prior to the SHOW TABLE STATUS will update the time if an update has been executed.  This behavior raises more questions for me, though - it seems like updates are being held in memory rather than written directly to the underlying data file until the FLUSH TABLES command is executed, which doesn't jive with my understanding of MyISAM storage engine behavior.
Dan_82Author Commented:
thanks, i had read that, thats why i said about using MyISAM tables above, thanks for looking thou

I'm going to have another table to remember settings so if i can't firgure it out i'll just store the updated timestamp in their

Thank
Dan
todd_farmerCommented:
Can you issue a FLUSH TABLES before the SHOW TABLE STATUS command?
Dan_82Author Commented:
Hi havn't tested it fully but it looks like when using flush tables the time returned is the time the table was flushed, i just need the exact time the data was last altered.

Don't think i'll use SHOW TABLE STATUS as it doesn't seem to work as i require and i also want the system to be as universal as possible, i.e. run on as may version of MYSQL as resonably possible basically 3.23 to 5.1+, and storing this in another table will be more universal i hope.

Thanks for trying
Dan

PS I'll leave the question open til 2morow just incase anyone has an engenius idea :)
tdterryCommented:
You want ingenious?  Here you go...

Caveat: Personally, I would go with todd_farmer's suggestion to add a TIMESTAMP field.  This is very useful to track the last-modified time of each row, and to get the last-modified time for the table ( SELECT max(last_modified) FROM mytable ).  However, if this would cause compatibility problems with your existing queries, try this:

Set up another table to store last-modification times of the tables you need to track.  Something like

CREATE TABLE db_modifications (
  table_name varchar(50),
  last_modified datetime
) TYPE=MyISAM;

In MySQL 5.0, use the CREATE TRIGGER command to automatically update the db_modifications table on any INSERT or UPDATE to your target TABLE (http://dev.mysql.com/doc/refman/5.0/en/create-trigger.html).

CREATE TRIGGER update_modification_time_mytable AFTER UPDATE
  FOR EACH ROW
    UPDATE db_modifications SET last_modified = NOW() WHERE table_name = 'mytable';

CREATE TRIGGER update_modification_time_mytable AFTER INSERT
  FOR EACH ROW
    UPDATE db_modifications SET last_modified = NOW() WHERE table_name = 'mytable';

Since triggers are new to MySQL 5.0, you would need to provide your own query to update db_modifications and call it after every INSERT or UPDATE statement in your application if you want to run on previous versions also.

if (db_version < 5) { run_query("UPDATE db_modifications SET last_modified = NOW() WHERE table_name = 'mytable'"); }

Travis

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Dan_82Author Commented:
thanks tdterry thats exactlly wot i was going to do, and updating exisiting queries isn't a problem because its only needed in one query. I already need an setting table to store things such as selected date format mm/dd/yy or dd/mm/yy, and number of entiries to display on one page so i'm just going to add the update timestamp for this table into the 'settings' table, i'm not going to create a new table to store all the update times for all table as this is the only table that will requires this functionality, and more importantly it needs to run on mysql 4 at least so i can't use triggers, thanks for the suggestions thou, i'll remeber that for next time :)

Dan
Dan_82Author Commented:
sorry, todd, i tried to spilt the points but i've never had to do it before and done it wrong :( checked that help section so i know how to now, so it won't happen again :(
todd_farmerCommented:
You can post a free question in the Support topic area to have this question reopened for regrading.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.