Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

wrong update_time from SHOW TABLE STATUS

Posted on 2006-04-06
14
Medium Priority
?
977 Views
Last Modified: 2012-06-21
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
0
Comment
Question by:Dan_82
13 Comments
 
LVL 11

Expert Comment

by:star_trek
ID: 16391644
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.
0
 
LVL 4

Author Comment

by:Dan_82
ID: 16392200
Thanks, but i need to do it througth an SQL statement
0
 
LVL 30

Expert Comment

by:todd_farmer
ID: 16392881
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.
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 4

Author Comment

by:Dan_82
ID: 16393030
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
0
 
LVL 30

Expert Comment

by:todd_farmer
ID: 16393104
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.

0
 
LVL 30

Assisted Solution

by:todd_farmer
todd_farmer earned 800 total points
ID: 16393130
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.
0
 
LVL 4

Author Comment

by:Dan_82
ID: 16393146
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
0
 
LVL 30

Expert Comment

by:todd_farmer
ID: 16393216
Can you issue a FLUSH TABLES before the SHOW TABLE STATUS command?
0
 
LVL 4

Author Comment

by:Dan_82
ID: 16394703
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 :)
0
 
LVL 5

Accepted Solution

by:
tdterry earned 1200 total points
ID: 16407891
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
0
 
LVL 4

Author Comment

by:Dan_82
ID: 16411046
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
0
 
LVL 4

Author Comment

by:Dan_82
ID: 16411055
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 :(
0
 
LVL 30

Expert Comment

by:todd_farmer
ID: 16411723
You can post a free question in the Support topic area to have this question reopened for regrading.
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Creating and Managing Databases with phpMyAdmin in cPanel.
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses
Course of the Month21 days, 6 hours left to enroll

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question