MySQL: Check If Table's Data Has modified Without trigger and UDF?

Question

Is there a way to know if a MySQL table's data has been modified?

Perhaps add a `modified_date` column and update it with `CURRENT_TIMESTAMP` — Rob W, Jun 06 '13 at 21:30

score 2 · Answer 1 · answered Jun 06 '13 at 21:39

2

You can query the INFORMATION_SCHEMA database, table TABLES and there is a column "UPDATE_TIME". (SHOW TABLES is somewhat equivalent).

The other way is to give the PHP access to db files (if you can locate them) and check system date of file modification. EDIT read permission is enough for this operation.

answered Jun 06 '13 at 21:39

Voitcus

4,463
4
24
40

nice answer +1 (at least the first part ;) I'm not sure if PHP should have access to the db files. – hek2mgl Jun 08 '13 at 17:25
PHP has access if you grant, it's running as root/administrator etc. To get what files to look check eg. [this thread](http://stackoverflow.com/q/12947668/2088851). This is of course some workaround; I'm not sure if `SHOW TABLES` has UPDATE_TIME field in every db engine. – Voitcus Jun 08 '13 at 20:18
No, I just wanted to point out that it could be a potential risk, to let php have access to the files. However, `information_schema` should suite well. (didn't know that the update time is stored there, thanks for this) – hek2mgl Jun 08 '13 at 20:33
Yes, I agree, thanks for pointing this, but -- as I put in **EDIT** -- you may grant PHP only read privileges. If you take a look at the [two last comments on MySQL page](http://dev.mysql.com/doc/refman/5.0/en/tables-table.html) this might not work for InnoDB. – Voitcus Jun 08 '13 at 21:07
yes, having read privileges it would be - potentially - possible to access data, which cannot being access using the mysql permission system, through a potential bug in php. :) – hek2mgl Jun 08 '13 at 21:13
Yeah.. I have no idea how to access InnoDB files. In fact we need only update time of the file, not the contents. Maybe another program could check this date and return to PHP? Maybe some cron job/scheduled task would update PHP-accessible file? – Voitcus Jun 08 '13 at 21:38
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/31464/discussion-between-hek2mgl-and-voitcus) – hek2mgl Jun 08 '13 at 21:43

hek2mgl · Accepted Answer · 2013-06-08T23:01:48.763

In chat with @Voitcus, we figured out, that the information_schema approach isn't available for InnoDb tables. The column UPDATE_TIME will being NULL for such tables.

That's why we worked out a solution for InnoDB tables that does not require read access to the db file on hard disk (what I think would be a security issue and no admin should allow this, also the db server could be on a different host)

Solution:

As you said, that you cannot use trigger functions for this, you'll have to fix this on application level, in PHP. Update: Note that with current versions of MySQL it wouldn't be stable to use triggers anyway as they won't get called on cascading foreign key actions.

First create a table, let's say stats:

CREATE TABLE `yourdb`.`stats` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY ,
    `table_name` VARCHAR( 255 ) NOT NULL ,
    `last_update` DATETIME NOT NULL
) ENGINE = InnoDB;

Insert a row for every table in your application.

In PHP modify existing queries so that they use transactions and update the stats table. This is possible using mysqli or PDO for example. I'm using PDO here, we deleting a row for example.

try {
    $pdo = new PDO($host, $user, $pass, $db, array((
        // this option is nice when working with transactions
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
    ));
} catch (PDOException $e) {
    die('cannot connect to mysql');
}


try {
    // start transaction
    $pdo->beginTransaction();
    // delete a row
    $pdo->query('DELETE FROM `table1` WHERE `id` = 1');
    // update stats
    $pdo->query('UPDATE `stats` SET `last_update` = NOW() WHERE `table_name` = `table1`');
    // commit both queries at once
    $pdo->commit();
} catch (Exception $e) {
    // rollback both queries if one of them failed
    $pdo->rollback();
}

Now you can use the following query to obtain the last update time:

SELECT `last_updated` FROM `stats` WHERE `table_name` = 'table1'

MySQL: Check If Table's Data Has modified Without trigger and UDF?

2 Answers2