1

Some time ago I developed a utility Python app which does certain actions if it finds that any data in a database has been updated recently. To do this, for each database I created a table last_modif_info, with a single field dbase_last_modif.

Then I made the following trigger:

CREATE TRIGGER update_dbase_last_modif AFTER UPDATE 
ON information_schema.tables
FOR EACH ROW
UPDATE last_modif_info SET last_modif_info.Dbase_last_modif = CURRENT_TIMESTAMP;

... this works as intended and has been doing so for months.

But now, as part of this utility app, I want somehow to detect that this trigger mechanism has been correctly set up. I'm wondering for example what happens if you create a new table after having created this trigger: presumably you have to recreate the trigger? If so, I'd want to find out which tables do or don't have the trigger set up.

I did a dump of information_schema, structure only but including data for table "tables".

In this dump neither the string "update_dbase_last_modif" nor the string "current_timestamp" appears, so it does not appear to contain instructions for setting up this trigger.

I recall from the time I was finding out how to implement this mechanism that some of the MariaDB/MySQL system databases are a bit more mysterious than normal databases.

Does anyone know how I can check on this trigger mechanism from analysing a dump or in some other way?

mike rodent
  • 111
  • 1
  • 7
  • Extremely strange. information_schema.tables is NOT a table, this is a view (ensure - execute SHOW CREATE TABLE information_schema.tables;). – Akina Mar 21 '23 at 08:40
  • Ah yes, that was one thing I remember finding out. I set this up about 6 months ago. In fact the updating of the field "Dbase_last_modif" seems a bit unreliable. A more reliable solution will probably be for my Python script to check the value of information_schema.tables.UPDATE_TIME for all rows where TABLE_SCHEMA is a user (non-system) database: hopefully this gives a reliable "last change datetime" for each such database (initially NULL at server startup). – mike rodent Mar 21 '23 at 11:44
  • That UPDATE_TIME is not reliable. The best reliable way would be to tail the binary log. – Bill Karwin Mar 21 '23 at 17:38
  • @BillKarwin Thanks. That is way beyond my pay grade. Could you maybe point to more info about how to do that? – mike rodent Mar 21 '23 at 19:06
  • https://www.arcion.io/learn/mysql-cdc – Bill Karwin Mar 21 '23 at 19:17
  • @BillKarwin Thanks – mike rodent Mar 22 '23 at 18:52
  • What version of MySQL/MariaDB? TABLES is implemented in about 3 different ways in the various versions. – Rick James Apr 03 '23 at 16:50
  • mariadb Ver 15.1 Distrib 10.7.3-MariaDB, for Win64 (AMD64), source revision 65f602310c4ea086488bfaa50f36952162541a8c. For the moment I am using tables.UPDATE_TIME to hopefully find when a given table was last changed in some way ... this seems to work OK for my specific purposes, although I'm sure the experts here will all tell me this is fatally flawed. – mike rodent Apr 03 '23 at 19:59

1 Answers1

0

You can dump the triggers to an external file.

See my old post Can mysqldump dump triggers and procedures?

You can also execute this to see the trigger names and their definitions

SELECT
    trigger_name
   ,event_manipulation
   ,created
   ,action_statement
FROM information_schema.triggers
WHERE trigger_schema = 'mydb' \G
RolandoMySQLDBA
  • 182,700
  • 33
  • 317
  • 520