1

I have tried to find any way to track changes in tables where the ALTER TABLE statement was used.

It is known that the trigger event can be INSERT, UPDATE or DELETE according to ==> Mysql triggers

Anyway I tried to use this query to enable trigger for ALTER TABLE ==> CREATE TRIGGER my_trigger ALTER TABLE ON my table ... but it did not work.

Is there any way to watch changes in a mysql table if it was changed by using ALTER TABLE statement?

2
  • 2
    Can you not trust the developers to inform you when the schema changes? Or are you altering the tables all the time?
    – Ed Heal
    Oct 23 '16 at 17:20
  • Tables are changed on a client side and I am trying to track this. This will help me to avoid failure during the update of the server, because custom fields cause the conflict with the perl code of my product.
    – fuser
    Oct 24 '16 at 5:21
1

Just some ideas:

Set a trigger on INFORMATION_SCHEMA

Not possible, according to docs:

The INFORMATION_SCHEMA database contains several read-only tables. They are actually views, not base tables, so there are no files associated with them, and you cannot set triggers on them. Also, there is no database directory with that name.

Monitor the DDL log

I wouldn't go down that road (docs):

The DDL log, or metadata log, records metadata operations generated by data definition statements such as DROP TABLE and ALTER TABLE. [...] This is a binary file; it is not intended to be human-readable...

Use events and track changes yourself

You could keep a copy of the relevant INFORMATION_SCHEMA tables, schedule a recurring event and check for any difference between your copy and the current state.

You would need a very strong use case to justify the effort to develop this, I think.

Track the binary log or general log

If you just want to get a quick heads up, when the schema has changed, you might try to set up a cron job and enable either (a) binary logging to use mysqlbinlog to check for ALTER TABLE statements or (b) to enable the general log and do the same. You could then send yourself an email, when that happens.

Might be more or less reliable than trusting the developers to notify you - depending on your implementation and on the developers ;)

2
  • 1
    Or turn on the "general log".
    – Rick James
    Oct 24 '16 at 2:09
  • I think it will help!:) thank you:) But aforementioned ideas I will take into consideration too.
    – fuser
    Oct 24 '16 at 6:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.