MySQL Bugs: #89093: Trigger order, not works as I expect

Bug #89093	Trigger order, not works as I expect
Submitted:	2 Jan 2018 13:08	Modified:	9 Feb 2018 4:37
Reporter:	tapin13 none	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.7.20	OS:	Ubuntu (14.04)
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	order, trigger

Description:
I have simple table, named test. I have 3 fields: aaa, step1, step2.

And 2 triggers: master_test_a (have to works first) and master_test_b (that based on what master_test_a did).

If I put in `master_test_a` PRECEDES `master_test_b` it's works perfect, doesn't matter in what order I compile triggers.

When I use ordering in master_test_b only, like this 'FOLLOWS `master_test_a`' and compile it in order `master_test_b`, then `master_test_a`, it's not works (order not works). Can you check it plz.

P.S. Main problem to use only PRECEDES, is that trigger b exists only on replication. So it's not possible to make on master PRECEDES `master_test_b`.

How to repeat:
CREATE TABLE `test` (
`aaa` int(11) DEFAULT NULL,
`step1` int(11) DEFAULT NULL,
`step2` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

CREATE
/*!50017 DEFINER = 'root'@'localhost' */
TRIGGER `master_test_a` BEFORE INSERT ON `test`
FOR EACH ROW #PRECEDES `master_test_b`

BEGIN

SET NEW.`step1` = NEW.`aaa` + 1;

END;
$$

CREATE
/*!50017 DEFINER = 'root'@'localhost' */
TRIGGER `master_test_b` BEFORE INSERT ON `test`
FOR EACH ROW FOLLOWS `master_test_a`

BEGIN

SET NEW.`step2` = NEW.`step1` + 1;

END;
$$

Then start add values to field aaa.
aaa 1, then step_1 have to be 2 and step_2 = 3

But got aaa 1, then step_1 = 2 and step_2 = NULL

mysql [localhost] {msandbox} (test) > CREATE TABLE `test` (
    -> `aaa` int(11) DEFAULT NULL,
    -> `step1` int(11) DEFAULT NULL,
    -> `step2` int(11) DEFAULT NULL
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
    ->
    -> ;
Query OK, 0 rows affected (0.01 sec)

mysql [localhost] {msandbox} (test) > delimiter $$
mysql [localhost] {msandbox} (test) >
mysql [localhost] {msandbox} (test) > CREATE
    -> /*!50017 DEFINER = 'root'@'localhost' */
    -> TRIGGER `master_test_a` BEFORE INSERT ON `test`
    -> FOR EACH ROW #PRECEDES `master_test_b`
    ->
    -> BEGIN
    ->
    -> SET NEW.`step1` = NEW.`aaa` + 1;
    ->
    -> END;
    -> $$
Query OK, 0 rows affected (0.01 sec)

mysql [localhost] {msandbox} (test) >
mysql [localhost] {msandbox} (test) > CREATE
    -> /*!50017 DEFINER = 'root'@'localhost' */
    -> TRIGGER `master_test_b` BEFORE INSERT ON `test`
    -> FOR EACH ROW FOLLOWS `master_test_a`
    ->
    -> BEGIN
    ->
    -> SET NEW.`step2` = NEW.`step1` + 1;
    ->
    -> END;
    -> $$
Query OK, 0 rows affected (0.01 sec)

mysql [localhost] {msandbox} (test) > delimiter ;
mysql [localhost] {msandbox} (test) > insert into test (aaa) values (1);
Query OK, 1 row affected (0.00 sec)

mysql [localhost] {msandbox} (test) > insert into test (aaa) values (2);
Query OK, 1 row affected (0.04 sec)

mysql [localhost] {msandbox} (test) > insert into test (aaa) values (3);
Query OK, 1 row affected (0.00 sec)

mysql [localhost] {msandbox} (test) > insert into test (aaa) values (4);
Query OK, 1 row affected (0.00 sec)

mysql [localhost] {msandbox} (test) > insert into test (aaa) values (5);
Query OK, 1 row affected (0.00 sec)

mysql [localhost] {msandbox} (test) > select * from test;
+------+-------+-------+
| aaa  | step1 | step2 |
+------+-------+-------+
|    1 |     2 |     3 |
|    2 |     3 |     4 |
|    3 |     4 |     5 |
|    4 |     5 |     6 |
|    5 |     6 |     7 |
+------+-------+-------+
5 rows in set (0.00 sec)

mysql [localhost] {msandbox} (test) > insert into test (aaa) values (10);
Query OK, 1 row affected (0.00 sec)

mysql [localhost] {msandbox} (test) > insert into test (aaa) values (9);
Query OK, 1 row affected (0.02 sec)

mysql [localhost] {msandbox} (test) > insert into test (aaa) values (8);
Query OK, 1 row affected (0.00 sec)

mysql [localhost] {msandbox} (test) > insert into test (aaa) values (7);
Query OK, 1 row affected (0.00 sec)

mysql [localhost] {msandbox} (test) > select * from test;
+------+-------+-------+
| aaa  | step1 | step2 |
+------+-------+-------+
|    1 |     2 |     3 |
|    2 |     3 |     4 |
|    3 |     4 |     5 |
|    4 |     5 |     6 |
|    5 |     6 |     7 |
|   10 |    11 |    12 |
|    9 |    10 |    11 |
|    8 |     9 |    10 |
|    7 |     8 |     9 |
+------+-------+-------+
9 rows in set (0.00 sec)

mysql [localhost] {msandbox} (test) >

mysql [localhost] {msandbox} (test) > delete from test;
Query OK, 9 rows affected (0.00 sec)

mysql [localhost] {msandbox} (test) > alter table test add primary key (aaa);
Query OK, 0 rows affected (0.17 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql [localhost] {msandbox} (test) > insert into test (aaa) values (1);
Query OK, 1 row affected (0.01 sec)

mysql [localhost] {msandbox} (test) > insert into test (aaa) values (2);
Query OK, 1 row affected (0.03 sec)

mysql [localhost] {msandbox} (test) > insert into test (aaa) values (3);
Query OK, 1 row affected (0.00 sec)

mysql [localhost] {msandbox} (test) > insert into test (aaa) values (4);
Query OK, 1 row affected (0.00 sec)

mysql [localhost] {msandbox} (test) > insert into test (aaa) values (10);
Query OK, 1 row affected (0.00 sec)

mysql [localhost] {msandbox} (test) > insert into test (aaa) values (9);
Query OK, 1 row affected (0.00 sec)

mysql [localhost] {msandbox} (test) > insert into test (aaa) values (8);
Query OK, 1 row affected (0.01 sec)

mysql [localhost] {msandbox} (test) > insert into test (aaa) values (5);
Query OK, 1 row affected (0.00 sec)

mysql [localhost] {msandbox} (test) > select * from test;
+-----+-------+-------+
| aaa | step1 | step2 |
+-----+-------+-------+
|   1 |     2 |     3 |
|   2 |     3 |     4 |
|   3 |     4 |     5 |
|   4 |     5 |     6 |
|   5 |     6 |     7 |
|   8 |     9 |    10 |
|   9 |    10 |    11 |
|  10 |    11 |    12 |
+-----+-------+-------+
8 rows in set (0.00 sec)

mysql [localhost] {msandbox} (test) >

mysql [localhost] {msandbox} (test) > drop table test;
Query OK, 0 rows affected (0.09 sec)

mysql [localhost] {msandbox} (test) > CREATE TABLE `test` (
    -> `aaa` int(11) DEFAULT NULL,
    -> `step1` int(11) DEFAULT NULL,
    -> `step2` int(11) DEFAULT NULL
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
    -> ;
Query OK, 0 rows affected (0.01 sec)

mysql [localhost] {msandbox} (test) > delimiter $$
mysql [localhost] {msandbox} (test) > CREATE
    -> /*!50017 DEFINER = 'root'@'localhost' */
    -> TRIGGER `master_test_b` BEFORE INSERT ON `test`
    -> FOR EACH ROW #FOLLOWS `master_test_a`
    ->
    -> BEGIN
    ->
    -> SET NEW.`step2` = NEW.`step1` + 1;
    ->
    -> END;
    -> $$
Query OK, 0 rows affected (0.01 sec)
mysql [localhost] {msandbox} (test) > CREATE
    -> /*!50017 DEFINER = 'root'@'localhost' */
    -> TRIGGER `master_test_a` BEFORE INSERT ON `test`
    -> FOR EACH ROW PRECEDES `master_test_b`
    ->
    -> BEGIN
    ->
    -> SET NEW.`step1` = NEW.`aaa` + 1;
    ->
    -> END;
    -> $$
Query OK, 0 rows affected (0.00 sec)
mysql [localhost] {msandbox} (test) > show triggers;
    -> ;
    -> $$
+---------------+--------+-------+------------------------------------------------+--------+------------------------+-------------                                                             ------------------------------------------------------------------------------------------------------------------------------+---                                                             -------------+----------------------+----------------------+--------------------+
| Trigger       | Event  | Table | Statement                                      | Timing | Created                | sql_mode                                                                                                                                                                                               | De                                                             finer        | character_set_client | collation_connection | Database Collation |
+---------------+--------+-------+------------------------------------------------+--------+------------------------+-------------                                                             ------------------------------------------------------------------------------------------------------------------------------+---                                                             -------------+----------------------+----------------------+--------------------+
| master_test_a | INSERT | test  | BEGIN

SET NEW.`step1` = NEW.`aaa` + 1;

END   | BEFORE | 2018-01-09 05:31:47.98 | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_B                                                             Y_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | root@localhost | utf8                 | utf8_general_ci      | latin1_swedish_                                                             ci  |
| master_test_b | INSERT | test  | BEGIN

SET NEW.`step2` = NEW.`step1` + 1;

END | BEFORE | 2018-01-09 05:31:43.98 | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_                                                             ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | root@localhost | utf8                 | utf8_general_ci      | latin1_swedish_ci                                                               |
+---------------+--------+-------+------------------------------------------------+--------+------------------------+-------------                                                             ------------------------------------------------------------------------------------------------------------------------------+---                                                             -------------+----------------------+----------------------+--------------------+
2 rows in set (0.00 sec)

mysql [localhost] {msandbox} (test) > delimiter ;
mysql [localhost] {msandbox} (test) > insert into test (aaa) values (1);
Query OK, 1 row affected (0.00 sec)

mysql [localhost] {msandbox} (test) > insert into test (aaa) values (2);
Query OK, 1 row affected (0.05 sec)

mysql [localhost] {msandbox} (test) > insert into test (aaa) values (10);
Query OK, 1 row affected (0.00 sec)

mysql [localhost] {msandbox} (test) > insert into test (aaa) values (9);
Query OK, 1 row affected (0.00 sec)

mysql [localhost] {msandbox} (test) > insert into test (aaa) values (3);
Query OK, 1 row affected (0.00 sec)

mysql [localhost] {msandbox} (test) > insert into test (aaa) values (5);
Query OK, 1 row affected (0.00 sec)

mysql [localhost] {msandbox} (test) > select * from test;
+------+-------+-------+
| aaa  | step1 | step2 |
+------+-------+-------+
|    1 |     2 |     3 |
|    2 |     3 |     4 |
|   10 |    11 |    12 |
|    9 |    10 |    11 |
|    3 |     4 |     5 |
|    5 |     6 |     7 |
+------+-------+-------+
6 rows in set (0.00 sec)

mysql [localhost] {msandbox} (test) >

Hi,
I don't see a bug here please elaborate?
thanks
Bogdan

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".