1

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:

DELIMITER $$

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

   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;
   else
       SIGNAL SQLSTATE '45001' SET MESSAGE_TEXT = 'Unknown SQL statement';
   end if;
   return 1;
END$$
DELIMITER ;

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

One way is:

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

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?

5
  • 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
    Jan 31, 2019 at 18:23
  • What version of MySQL are you using?
    – Rick James
    Feb 1, 2019 at 16:00
  • We are using 8.0 and trying this with Group replication on. Feb 4, 2019 at 9:26
  • 2
    Does this answer your question? DDL Trigger in MySQL? 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. Dec 12, 2021 at 15:40

1 Answer 1

0

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' ) )
THEN
    issue error message
END IF;
DROP TABLE ...;  -- probably need prepare+execute+deallocate_prepare
1
  • 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 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.