Bug #113683 Error on create multiply trigger that have same trigger event and action time
Submitted: 18 Jan 2024 16:58 Modified: 22 Jan 2024 14:34
Reporter: raffaele barone Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Workbench: Migration Severity:S3 (Non-critical)
Version:8.036 build 3737333 OS:Windows (windows 10 )
Assigned to: MySQL Verification Team CPU Architecture:Any
Tags: create multiply trigger;

[18 Jan 2024 16:58] raffaele barone
Description:
I have 1 trigger for 1 table (after update event)
-Trigger_After_update

I create new second trigger Trigger_After_update_1 (after update event)

ERROR 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
SQL Statement:
CREATE DEFINER = `root`@`localhost` TRIGGER `qnc_innodb`.`Trigger_After_update_1 ` AFTER UPDATE ON `table` FOR EACH ROW PRECEDES `Trigger_After_update`

How to repeat:
/* 2 simply table */
CREATE TABLE `table1` (
  `field1` int NOT NULL AUTO_INCREMENT,
  `field2` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`campo1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `table2` (
  `field1` int NOT NULL AUTO_INCREMENT,
  `field2` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`campo1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

/* create first trigger is ok */
CREATE DEFINER=`root`@`localhost` TRIGGER `table1_AFTER_UPDATE` AFTER UPDATE ON `table1` FOR EACH ROW BEGIN

update table2
set campo2= 'Bye Bye'
where campo1 = new.campo1;

END

/* create second trigger ERROR */
CREATE DEFINER = CURRENT_USER TRIGGER `qnc_innodb`.`table1_AFTER_UPDATE_1` AFTER UPDATE ON `table1` FOR EACH ROW
BEGIN

update table2
set campo2= 'Hello'
where campo1 <> new.campo1;

END

this is ERROR ON APPLY:

Operation failed: There was an error while applying the SQL script to the database.
Executing:
DROP TRIGGER IF EXISTS `qnc_innodb`.`table1_AFTER_UPDATE_1`;

DELIMITER $$
USE `qnc_innodb`$$
CREATE DEFINER = CURRENT_USER TRIGGER `qnc_innodb`.`table1_AFTER_UPDATE_1` AFTER UPDATE ON `table1` FOR EACH ROW FOLLOWS `table1_AFTER_UPDATE`
$$
DELIMITER ;

ERROR 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
SQL Statement:
CREATE DEFINER = CURRENT_USER TRIGGER `qnc_innodb`.`table1_AFTER_UPDATE_1` AFTER UPDATE ON `table1` FOR EACH ROW FOLLOWS `table1_AFTER_UPDATE`
[18 Jan 2024 19:49] raffaele barone
screenshot dashboard Server Status

Attachment: server status.png (image/png, text), 3.45 MiB.

[19 Jan 2024 11:28] MySQL Verification Team
Hi Mr. barone,

Thank you for your bug report.

However, we are not able to repeat your test case, since it is full of syntax errors.

For the start, none of the tables contains a column named 'campol'.

Next, in several places you have not set nor unset delimiter clause properly.

Last, but not least, your trigger definition:

CREATE DEFINER = CURRENT_USER TRIGGER
`qnc_innodb`.`table1_AFTER_UPDATE_1` AFTER UPDATE ON `table1` FOR EACH
ROW FOLLOWS `table1_AFTER_UPDATE`

runs completely against the syntax as stipulated by the SQL standard.

You should have used this definition:

CREATE TRIGGER ins_transaction AFTER UPDATE  ON table1     FOR EACH ROW FOLLOWS qnc_innodb        update table2 set field2= 'Bye Bye' WHERE field1=2;

Of course, the above definition can be changed to your likings.

This is all described in our Reference Manual and in our tests, for example in the test named trigger_multiple.test.

Can't repeat.
[19 Jan 2024 17:21] raffaele barone
Hi
I apologize for my English suitable for survival :)
The script I sent you was copied from the Workbench editor
The presence of delimiters has nothing to do with it.
I sent you a simple example, in reality my original database (Interbase by Embarcadero) contains over 250 tables and over 15,000 rows of trigger and procedure sources.
We are trying to migrate the DataBase part of the application to MySql. This application is written in Delphi and is a "Government" application that manages over 500 users distributed across the national territory (Italy).
The Interbase Database is over 2 TB.
This is a premise to try to make you understand what we do.

And now I'll try to explain the problem better.

Creating two triggers on the same event via Workbench DOES NOT WORK!

The same sequence carried out in the WampServer environment (windows) through PhPMyAdmin works perfectly and with the same MySql 8.0 engine.
In the MySql 8.0 documentation it is stated that it is possible to have multiple triggers on the same event and this is true on WampServer, with WorkBench this gives an error.
I also tried installing WorkBench and MySql on another machine and the problem repeats.
Just create even a single table and create two even empty triggers on the same event and the error will repeat, it doesn't take much if you want to verify the problem.
If you need further information, my development team is available to send you everything you need.
We consider this a serious bug.
In any case, thank you for your immediate response and I wish you a good day.
Raffaele Barone
Senior developer Eu.Consulting SPA
Italy
[19 Jan 2024 17:26] raffaele barone
screenshot error on WorkBench

Attachment: workbench error.png (image/png, text), 154.76 KiB.

[19 Jan 2024 17:28] raffaele barone
The same sequence carried out in the WampServer environment (windows) through PhPMyAdmin works perfectly and with the same MySql 8.0 engine.
[22 Jan 2024 10:54] MySQL Verification Team
Hi Mr. Barone,

Your bug report, so far,  was full of syntax errors and  missing facts.

You have finally explained here what you are trying to do.

However, this is not a forum for support. This is only a forum for reporting bugs with fully repeatable test cases.

In this case, you are reporting a bug in MySQL Workbench. In order to process your report and discover whether there is or there is not any bug in Workbench, we require a fully repeatable test case.

You simply have to repeat ALL actions that you have attempted and tried with a Workbench. In case that we manage to repeat it, which will take lot's of time, then this report could be, eventually, verified. After that, you will have to wait for the bug to be fixed, which will take some time .....

Hence, this is the only path that you can undertake on this forum. Hence, you have to provide a fully detailed description of every single action that you have undertaken, ONLY with MySQL Workbench and how did it fail. If it is a bug, it will be , as we explained above, verified and will come into the long queue of the Workbench bugs that are waiting for the bug fix.

For the start, we are re-assigning this report to the engineers that are only trying to process the report and see whether this can be repeated by us or not.

This is all that can be done on this forum.

Beside this forum, we have forums for the free and paid support, where you could be helped with the entire procedure of transferring your data from another RDBMS to MySQL.

If you wish us to send you the links to those forums, please let us know.

There is another
[22 Jan 2024 13:59] MySQL Verification Team
HI Mr. barone,

One small additional note.

The entire syntax of chaining triggers with FOLLOWS or PRECEDES is specified in the SQL Standard 2016, paragraph 4.39.

MySQL strictly follows SQL standard and will not change the syntax in order to comply with some other implementation that does not follow the specified SQL standard strictly.
[22 Jan 2024 14:34] MySQL Verification Team
Hi Mr. barone,

One last comment.

If you read documentation on Workbench Migration, here:

https://dev.mysql.com/doc/workbench/en/wb-migration.html

You will find the following important comment:

"
Convert tables and copy data, but will not convert stored procedures, views, or triggers.
"
That clear statement in Workbench Manual, changes the status of your report to "Not a Bug".