0

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?

  • 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 at 18:23
  • What version of MySQL are you using? – Rick James Feb 1 at 16:00
  • We are using 8.0 and trying this with Group replication on. – Fayiz Musthafa Feb 4 at 9:26
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
  • 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 – Fayiz Musthafa Feb 1 at 13:31

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.