I want to add a few sanity checks on my DDL statements. Unfortunately, I cannot run a validation separately due to some constraints. I am thinking to run validation inside the DB by calling a stored function like this:


CREATE DEFINER='root'@'localhost' FUNCTION checksql (
       stmt TEXT
   returns int

   if locate("create", stmt) = 1 then
       if locate("primary key", stmt) = 0 then
           SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'No Primary key defined';
       end if;
       SIGNAL SQLSTATE '45001' SET MESSAGE_TEXT = 'Unknown SQL statement';
   end if;
   return 1;

but I could not find a way to trigger a function whenever a DDL get executed.

One way is:

SET autocommit=0;
SELECT checksql("CREATE TABLE ...");

But this forces me to make the changes in my code. Is there any methods that i can use to trigger the function without making any code changes?

  • CREATE TABLE test (id INT COMMENT 'fake primary key', val TEXT DEFAULT 'One more fake primary key');... I could not find a way to trigger a function whenever a DDL get executed Probably because MySQL does not have DDL triggers?
    – Akina
    Commented Jan 31, 2019 at 18:23
  • What version of MySQL are you using?
    – Rick James
    Commented Feb 1, 2019 at 16:00
  • We are using 8.0 and trying this with Group replication on. Commented Feb 4, 2019 at 9:26
  • 2
    Does this answer your question? DDL Trigger in MySQL? Commented Dec 12, 2021 at 15:37
  • I found a WorkLog for adding the feature of triggers on DDL for MySQL, but it is unassigned. It says it's for "after 7.1" but MySQL 7.x was renumbered to 8.0 about five years ago, so I guess no one has even looked at that WorkLog for a long time. Commented Dec 12, 2021 at 15:40

1 Answer 1


DDL statements terminate transactions. So your attempt with START TRANSACTION; ... COMMIT; does not work. (MySQL 8.0 may change that.)

If you are just checking for the existence of a PRIMARY KEY, suggest this (in a Stored Procedure) (and with a pre-created DATABASE test_area):

USE test_area;
CREATE TABLE ...  -- probably need prepare+execute+deallocate_prepare
abort if errors
IF ( ( SELECT ... FROM information_schema ...
    WHERE table_schema = 'test_area' ) )
    issue error message
DROP TABLE ...;  -- probably need prepare+execute+deallocate_prepare
  • Thanks for your replay. I am looking for a solution that triggers the validation whenever someone executes the create statement and terminate the transaction if validation fails. I don't want to include this logic in my code Commented Feb 1, 2019 at 13:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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