[MDEV-13066] EXECUTE IMMEDIATE and Triggers Clarification Created: 2017-06-13  Updated: 2020-08-25  Resolved: 2017-06-13

Status: Closed
Project: MariaDB Server
Component/s: Parser
Affects Version/s: None
Fix Version/s: N/A

Type: Bug Priority: Minor
Reporter: Chris Calender (Inactive) Assignee: Sergei Golubchik
Resolution: Not a Bug Votes: 0
Labels: None


 Description   

This is regarding EXECUTE IMMEDIATE and Triggers.

EXECUTE IMMEDIATE was recently added in MariaDB 10.2.3:

https://mariadb.com/kb/en/mariadb/execute-immediate/

For limitations, it says:

"Limitations: subselects and stored function calls are not supported as a prepare source."

Are triggers supported in EXECUTE IMMEDIATE?

For instance, I can create the following simple trigger:

CREATE TABLE `t1` (
 
  `id1` int(11) NOT NULL,
 
  `id2` char(5) DEFAULT NULL,
 
  PRIMARY KEY (`id1`)
 
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
 
 
 
CREATE TABLE `t2` (
 
  `id1` int(11) NOT NULL,
 
  `id2` char(5) DEFAULT NULL,
 
  PRIMARY KEY (`id1`)
 
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
 
 
 
delimiter $$
 
create trigger trigger1 
after insert ON t1
 
for each row
 
begin
 
insert into test.t2 (id1, id2) VALUES (999,'zzz');
 
end $$
 
delimiter ;

In this case, the trigger works fine.

Now if I delete that trigger and attempt to execute it via EXECUTE IMMEDIATE, I see an error:

mysql> execute immediate('create trigger trigger1'||
 
    ->  ' after insert ON t1'||
 
    ->  ' for each row'||
 
    ->  ' begin'||
 
    ->  ' insert into test.t2 (id1, id2) VALUES (999,"zzz");'
 
    -> );
 
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '0' at line 1

This error is not clear. If this is allowed, then there should not be an error. If this is not allowed, then the error message should be more clear (and the docs should be updated).

Now if I try to add the above EXECUTE IMMEDIATE command into a stored procedure, it allows me to do so, but then provides the same error when I invoke the SP:

delimiter $$
 
CREATE PROCEDURE chris1()
 
begin
 
	execute immediate('create or replace trigger trigger1'||
 
 ' after insert ON t1'||
 
 ' for each row'||
 
 ' begin'||
 
 ' insert into test.t2 (id1, id2) VALUES (999,"zzz");'
 
);
 
end $$
 
delimiter ;
 
 
 
mysql> call chris1();
 
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '0' at line 1

In another instance, we saw this error returned:

sql> call chris1();
 
ERROR 1295 (HY000): This command is not supported in the prepared statement protocol yet

This latter one is more specific, assuming it is referring to the "EXECUTE IMMEDIATE" part, but I can only assume, since it does not refer to the line nor command. Also, I cannot reproduce this latter error message, it was just reported.

So essentially, I am looking for clarification from this bug report, in the form of an error message (assuming this is not allowed) and in the form of an addition to the manual (again, assuming this is not allowed).

 Comments   
Comment by Sergei Golubchik [ 2017-06-13 ]

This is not a bug. Indeed, the syntax error "at 0" should've been a hint.

MariaDB [test]> select 'create or replace trigger trigger1'|| ' after insert ON t1'|| ' for each row'|| ' begin'|| ' insert into test.t2 (id1, id2) VALUES (999,"zzz");';
 
+---------------------------------------------------------------------------------------------------------------------------------------------------+
 
| 'create or replace trigger trigger1'|| ' after insert ON t1'|| ' for each row'|| ' begin'|| ' insert into test.t2 (id1, id2) VALUES (999,"zzz");' |
 
+---------------------------------------------------------------------------------------------------------------------------------------------------+
 
|                                                                                                                                                 0 |
 
+---------------------------------------------------------------------------------------------------------------------------------------------------+
 
1 row in set, 10 warnings (0.00 sec)

So, if you think you have enabled PIPES_AS_CONCAT sql mode — I can tell you that you haven't.

Generated at Thu Feb 08 08:02:38 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.