Solved

Find the last date a mysql table was written to

Posted on 2010-09-21
12
966 Views
Last Modified: 2013-12-12
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
Comment
Question by:birwin
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +2
12 Comments
 
LVL 29

Expert Comment

by:sammySeltzer
ID: 33730679
did you mean select max(activitydate) from your table?

sorry, question not too clear?
0
 
LVL 6

Author Comment

by:birwin
ID: 33730700
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
 
LVL 65

Expert Comment

by:rockiroads
ID: 33730771
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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 29

Expert Comment

by:sammySeltzer
ID: 33731245
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
 
LVL 21

Accepted Solution

by:
theGhost_k8 earned 500 total points
ID: 33732723
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
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 33733553
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
 
LVL 6

Author Comment

by:birwin
ID: 33741048
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
 
LVL 65

Expert Comment

by:rockiroads
ID: 33741091
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
 
LVL 6

Author Comment

by:birwin
ID: 33741420
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
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 33743019
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
 
LVL 65

Expert Comment

by:rockiroads
ID: 33744535
>> It appears that this is not possible.

AFAIK - that is the case.
0
 
LVL 6

Author Closing Comment

by:birwin
ID: 33818622
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

691 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