Get timestamp of last modify of MySQL table

Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

Hello, I am trying to get the date when a table in a MySQL database has been changed. Table is MyISAM. This approach is returning nothing. Any suggestion?

#!/usr/bin/perl -w
use warnings;
use strict;
use CGI qw(-utf8);
use CGI::Carp qw(fatalsToBrowser);
use Data::Dumper qw(Dumper);
use DBI;

print "Content-type: text/html\n\n";

    my $db   = "";
    my $host = "localhost";
    my $user = "";
    my $pass = "";

    my $table = '2qPwZ7vr';
    my $dbh = DBI->connect( "DBI:mysql:$db:$host", $user, $pass );
    
    my $sql = "SHOW TABLE STATUS FROM your_database LIKE ?";
    my $sth = $dbh->prepare($sql);
    $sth->execute($table);

    print "Last modified: ";

    while ( my $row = $sth->fetchrow_hashref ) {
        print Dumper "> " . $row;
    }
[download]

Comment on Get timestamp of last modify of MySQL table Download Code

Replies are listed 'Best First'.
Re: Get timestamp of last modify of MySQL table by LanX (Saint) on Mar 05, 2022 at 19:03 UTC
maybe have a look at https://mariadb.com/kb/en/myisam-log/ ? edit I remember seeing meta-tables with extensive schema information. You should have a look there. Like `UPDATE_TIME` in https://dev.mysql.com/doc/refman/8.0/en/information-schema-tables-table.html No guaranty, sorry. This is not really a Perl question ... Cheers Rolf _{(addicted to the Perl Programming Language :) Wikisyntax for the Monastery}	[reply] [d/l]
Re^2: Get timestamp of last modify of MySQL table by Anonymous Monk on Mar 05, 2022 at 21:59 UTC
Thank you. This brought me to the solution: `my $sql = "SELECT UPDATE_TIME FROM information_schema.tables WHERE TAB +LE_NAME = 'name_of_table'";` [download]	[reply] [d/l]
Re^3: Get timestamp of last modify of MySQL table by perlfan (Parson) on Mar 10, 2022 at 15:40 UTC
Beware, > Timestamps are not persisted when the server is restarted or when the table is evicted from the InnoDB data dictionary cache. And given that file time stamps (on the system) are unreliable and fragile, I suggest if there is a "time" you want to track reliably/correctly and precision is not a priority, using time stamp fields in your tables is going to give you the best and most reliable result.	[reply]

edit