find out the last update date of MySQL table

Dear all,

how to find out the last update date of all MySQL table other than the system table?

we might like to check out why and archive them out.
LVL 1
marrowyungSenior Technical architecture (Data)Asked:
Who is Participating?
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.

SharathData EngineerCommented:
SELECT UPDATE_TIME FROM information_schema.tables WHERE  TABLE_SCHEMA = 'dbName' AND TABLE_NAME = 'tableName'

<link to competing site deleted by COBOLdinosaur, Topic Advisor>
0
marrowyungSenior Technical architecture (Data)Author Commented:
but this one:

SELECT UPDATE_TIME FROM information_schema.tables WHERE  TABLE_SCHEMA   = 'dbName' AND TABLE_NAME = 'tableName'

means I have to plug-in the dbname and tableName valarble ?
0
marrowyungSenior Technical architecture (Data)Author Commented:
and this statement is not going to do for all tables, right?
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

marrowyungSenior Technical architecture (Data)Author Commented:
I might need to do this:

DECLARE cursor-name CURSOR FOR
SELECT DISTINCT TABLE_NAME
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA='<DB name>';  
?

how to construct this kind of CURSOR ?

e.g.:
 OPEN cursor-name;                                
FETCH cursor-name INTO variable [, variable];    
CLOSE cursor-name;
0
marrowyungSenior Technical architecture (Data)Author Commented:
how to make use of this:

1.DELIMITER //  
2.  
3.CREATE PROCEDURE `proc_CURSOR` (OUT param1 INT)  
4.BEGIN  
5.    DECLARE a, b, c INT;  
6.    DECLARE cur1 CURSOR FOR SELECT col1 FROM table1;  
7.    DECLARE CONTINUE HANDLER FOR NOT FOUND SET b = 1;  
8.    OPEN cur1;  
9.  
10.    SET b = 0;  
11.    SET c = 0;  
12.     
13.    WHILE b = 0 DO  
14.        FETCH cur1 INTO a;  
15.        IF b = 0 THEN  
16.            SET c = c + a;  
17.    END IF;    
18.    END WHILE;  
19.  
20.    CLOSE cur1;  
21.    SET param1 = c;  
22.  
23.END //  

Open in new window

0
SharathData EngineerCommented:
For all tables, you just need to remove the filter on table name.

SELECT TABLE_NAME,UPDATE_TIME FROM information_schema.tables
WHERE  TABLE_SCHEMA   = 'dbName'
ORDER BY UPDATE_TIME DESC
0

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
marrowyungSenior Technical architecture (Data)Author Commented:
why all update time return NULL ?
0
marrowyungSenior Technical architecture (Data)Author Commented:
it seems that the query is not that one, right?
0
Ray PaseurCommented:
Add your own credentials at line 10 and run this to see the moving parts.  HTH, ~Ray
<?php // RAY_temp_marrowyung.php
ini_set('display_errors', TRUE);
error_reporting(E_ALL);
echo '<pre>';

// THIS SCRIPT DEMONSTRATES HOW TO FIND THE LAST UPDATE FOR TABLES
// http://www.experts-exchange.com/Database/MySQL/Q_28269817.html

// DATABASE CONNECTION AND SELECTION VARIABLES - GET THESE FROM YOUR HOSTING COMPANY
$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "??";
$db_user = "??";
$db_word = "??";

// OPEN A CONNECTION TO THE DATA BASE SERVER AND SELECT THE DB
$mysqli = new mysqli($db_host, $db_user, $db_word, $db_name);

// DID THE CONNECT/SELECT WORK OR FAIL?
if ($mysqli->connect_errno)
{
    $err
    = "CONNECT FAIL: "
    . $mysqli->connect_errno
    . ' '
    . $mysqli->connect_error
    ;
    trigger_error($err, E_USER_ERROR);
}

// FIND THE TABLES IN THE DATA BASE
$sql = "SHOW TABLES FROM $db_name";
if (!$res = $mysqli->query($sql))
{
    $err
    = 'QUERY FAILURE:'
    . ' ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    . ' QUERY: '
    . $sql
    ;
    trigger_error($err, E_USER_ERROR);
}
// ACCUMULATE THE TABLE NAMES IN AN ARRAY
while ($row = $res->fetch_object())
{
    $key = 'Tables_in_' . $db_name;
    $arr[] = $row->$key;
}

// CHECK THE INFORMATION SCHEMA FOR EACH TABLE NAME
foreach ($arr as $dbt)
{
    $dbt = $mysqli->real_escape_string($dbt);
    $sql = "SELECT UPDATE_TIME FROM information_schema.tables WHERE TABLE_SCHEMA = '$db_name' AND TABLE_NAME = '$dbt' LIMIT 1";
    $res = $mysqli->query($sql);
    if (!$res)
	{
	    $err
	    = 'QUERY FAILURE:'
	    . ' ERRNO: '
	    . $mysqli->errno
	    . ' ERROR: '
	    . $mysqli->error
	    . ' QUERY: '
	    . $sql
	    ;
	    trigger_error($err, E_USER_ERROR);
	}

    // SHOW THE RESULTS
    $row = $res->fetch_object();
    $tme = $row->UPDATE_TIME;
    if (empty($tme)) $tme = 'NOT UPDATED';
    echo PHP_EOL . "$tme $dbt";
}

Open in new window

0
marrowyungSenior Technical architecture (Data)Author Commented:
ok, this is PHP script ? someone surprising to grep me the update file date of the MySQL data file from linux much reliable, surprise !
0
marrowyungSenior Technical architecture (Data)Author Commented:
you are right that this one works, not from MyPHPadmin but Toad or any UI.

SELECT TABLE_NAME,UPDATE_TIME FROM information_schema.tables
WHERE  TABLE_SCHEMA   = 'dbName'
ORDER BY UPDATE_TIME DESC
0
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.