Stories
Slash Boxes
Comments

NOTE: use Perl; is on undef hiatus. You can read content, but you can't post it. More info will be forthcoming forthcomingly.

use Perl

All the Perl that's Practical to Extract and Report

use Perl Log In

[ Create a new account ]

Ovid (2709)

Ovid
(email not shown publicly)
http://publius-ovidius.livejournal.com/
AOL IM: ovidperl (Add Buddy, Send Message)

Stuff with the Perl Foundation. A couple of patches in the Perl core. A few CPAN modules. That about sums it up.

Journal of Ovid (2709)

Friday February 09, 2007

05:32 AM

MySQL Tables Altered?

[ #32357 ]

Anyone happen to know, offhand, how I can check to see if any table in MySQL has been altered? This is MySQL 4.1.15. Obviously "SELECT count(*) ..." could fail as could checking which keys are present in a given table. Something as simple as an UPDATE statement can easily foil both of those checks. I've tried 'SHOW STATUS ...', but that also fails. The only thing I can think of is to use a DBI trace to capture SQL and look for INSERT, UPDATE, or DELETE statements and try and parse them for the appropriate tables.

Google's given me no love on this problem.

List all Journal entries

MySQL Tables Altered? More | Login | Reply

The Fine Print: The following comments are owned by whoever posted them. We are not responsible for them in any way.

Without JavaScript enabled, you might want to use the classic discussion system instead. If you login, you can remember this preference.

Are you using InnoDB? (Score:1)

by Adrian (66)

In which case I think you're stuffed since AFAIK this bug [mysql.com] is still open.

Add some triggers and doing it yourself would seem to be the obvious solution :-(
- Re: (Score:2)
  
  by Ovid (2709)
  
  Can't use triggers with this version of MySQL (as I'm sure you're aware). However, even though the tables are MyISAM, the Update_time is not being set for me which is why 'SHOW STATUS' seems virtually useless.
  
  (God I hate MySQL)
logs? (Score:2)

by vek (2312)

Does the database have logging enabled? With your version of MySQL, you can run mysqld with --log-update to capture any SQL statements that update data as described in the fine manual [mysql.com].
Calculating Mysql table checksums (Score:1)

by gozer (186)

With MySQL 5.x you have CHECKSUM TABLE, but it's only for MyIASM anyways.

http://dev.mysql.com/doc/refman/5.0/en/checksum-table.html [mysql.com]

Check out this dude's elegant solution:

http://www.xaprb.com/blog/2007/01/25/how-to-calculate-table-checksums-in-mysql/ [xaprb.com]

--
There's more than 9 ways to do it
Triggers (Score:2)

by bart (450)

With triggers, and implementing journalling tables (storing and timestamping every change), this comes almost for free. It's a reason good enough for not using (that old version of) Mysql.
mtime (Score:2)

by drhyde (1683)

Check the mtimes on the files containing the relevant tables?