[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 977
  • Last Modified:

Find the last date a mysql table was written to

Is there a way to find the last date a mysql table was written to? The last written activity would have been an upload of data using a LOAD DATA INTO statement.
0
birwin
Asked:
birwin
  • 4
  • 3
  • 2
  • +2
1 Solution
 
sammySeltzerCommented:
did you mean select max(activitydate) from your table?

sorry, question not too clear?
0
 
birwinAuthor Commented:
This is a table that does not have a date field. I was hoping that mysql may have some native way to access the last write date, perhaps from a system log or some internal monitoring module.
0
 
rockiroadsCommented:
I would of thought this would be part of your table design. Does this load data import new records only? Do you have some sort of primary key that is a autoincrement number? if so then you could find that highest number (select max(id) from table)

otherwise good idea to change table to have a last created/last updated date as well.
0
Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

 
sammySeltzerCommented:
If you have the ability to modify the db to append a dateCreated datefield, that would make your job much easier.

Otherwise, like @rockiroads suggested, if you have a pk that autoincrements, then use that by either grabbing the latest id (select max(id)...)

or order by id desc - samething, though first option is more reliable.
0
 
K VDatabase ConsultantCommented:
Two ways:
1. Check modified date of ur myi / myd / frm file.

2. Use information schema:
SELECT update_time FROM information_schema.`TABLES` T where table_name='YOURTABLENAME' AND TABLE_SCHEMA='databasename';
0
 
Ray PaseurCommented:
Learn about the CURRENT_TIMESTAMP data type.  Add this as the first such column in your table definition, and MySQL will keep track of this information for you, without any further programming on your part.

Check out DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP here:
http://dev.mysql.com/doc/refman/5.0/en/timestamp.html

HTH, ~Ray
0
 
birwinAuthor Commented:
This is a set of legacy tables, not a table that I am designing. There was some confusion over the date of last use of some of the tables, so I was hoping their may be a system log or some method of determining the last write time. If I was designing the tables myself, I would definately use a timestamp or system created date field, but these tables were designed long ago by others.
0
 
rockiroadsCommented:
I really dont know how you are going to achieve this. is it not possible to add a auto incrementing key in this legacy table?
0
 
birwinAuthor Commented:
It would be, but that doesn't answer my current problem, which is determining the time of last write for the existing, archived tables.
It appears that this is not possible.
0
 
Ray PaseurCommented:
If the question is more like, "What can I point to for a past event?" you may not be able to find an answer.  MySQL is well equipped to help you keep track of updates, but if the tables were designed in such a way that these features of MySQL were deliberately omitted, your audit trail simply does not exist.

Going forward, you can easily use ALTER TABLE to add the CURRENT TIMESTAMP column.  It adds nearly zero overhead.  Transaction logging is not a bad idea, either.

Best regards, ~Ray
0
 
rockiroadsCommented:
>> It appears that this is not possible.

AFAIK - that is the case.
0
 
birwinAuthor Commented:
When I first tried this , I got an error. But I had misread the dot between informationa_schema and TABLES. When I tried it again, with a period between them, it worked. Thank you.
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 4
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now