Details
-
Bug
-
Status: Closed (View Workflow)
-
Minor
-
Resolution: Not a Bug
-
None
-
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).