[MDEV-11033] "follows" does not show up in "show create trigger" output Created: 2016-10-11  Updated: 2018-08-31  Resolved: 2016-10-11

Status: Closed
Project: MariaDB Server
Component/s: Triggers
Affects Version/s: 10.2.3
Fix Version/s: 10.2.3

Type: Bug Priority: Major
Reporter: Alvin Richards (Inactive) Assignee: Michael Widenius
Resolution: Not a Bug Votes: 0
Labels: Compatibility, need_feedback
Environment:

Centos7

Issue Links:
Relates
relates to MDEV-6112 multiple triggers per table Closed

 Description   

Problem

The output of "show create trigger" does not include the "follows" keyword defined in the create trigger statement.

Reproduce

create database if not exists test;
 
 
 
use test
 
 
 
create table table_a (id int(10) not null,
 
                      value varchar(20) not null, 
                      created datetime(6),
 
                      updated datetime(6)
 
);
 
 
 
create table table_a_audit(id int(10) not null,
 
                           ts datetime(6),
 
                           old_value varchar(20),
 
                           new_value varchar(20)
 
);
 
 
 
delimiter $$
 
create or replace trigger table_a_trig_1
 
before insert on table_a
 
for each row
 
begin
 
  set new.created = current_timestamp(6);
 
  set new.updated = current_timestamp(6);
 
end$$
 
 
 
create or replace trigger table_a_trig_2
 
before insert on table_a
 
for each row
 
follows table_a_trig_1
 
begin
 
  insert into table_a_audit (id, ts, new_value)
 
    value (new.id, current_timestamp(6), new.value);
 
end$$
 
 
 
delimiter ;
 
 
 
insert into table_a (id, value) values (1, "foo");
 
commit;
 
 
 
select * from table_a;
 
 
 
select * from table_a_audit;
 
 
 
show create table table_a;
 
 
 
show create trigger table_a_trig_1;
 
 
 
show create trigger table_a_trig_2;

The output of this last statement is incorrect, in build-11611 on bb-10.2-compatibility branch you get the following output

MariaDB [test]> show create trigger table_a_trig_2;
 
+----------------+--------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+------------------------+
 
| Trigger        | sql_mode                                   | SQL Original Statement                                                                                                                                                                                        | character_set_client | collation_connection | Database Collation | Created                |
 
+----------------+--------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+------------------------+
 
| table_a_trig_2 | NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | CREATE DEFINER=`root`@`localhost` trigger table_a_trig_2
 
before insert on table_a
 
for each row
 
begin
 
  insert into table_a_audit (id, ts, new_value)
 
    value (new.id, current_timestamp(6), new.value);
 
end | latin1               | latin1_swedish_ci    | latin1_swedish_ci  | 2016-10-11 17:29:58.31 |
 
+----------------+--------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+------------------------+
 
1 row in set (0.00 sec)

Solution

The :show create trigger" should include the full trigger definition.

 Comments   
Comment by Elena Stepanova [ 2016-10-11 ]

alvinr, are you sure about that? Having FOLLOWS / PRECEDES in the trigger definition will do literally nothing good at all, and the price will be that it will break the dump.
Consider the following simple example.

MariaDB [test]> create table t1 (i int);
 
MariaDB [test]> create trigger tr1 after insert on t1 for each row set @a=1;
 
MariaDB [test]> create trigger tr3 after insert on t1 for each row follows tr1 set @a=3;
 
MariaDB [test]> create trigger tr2 after insert on t1 for each row follows tr1 set @a=2;

The dump will look like this:

/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 trigger tr1 after insert on t1 for each row set @a=1 */;;
 
...
 
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 trigger tr2 after insert on t1 for each row set @a=2 */;;
 
..
 
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 trigger tr3 after insert on t1 for each row set @a=3 */;;

So, the order is preserved in the dump and will be restored properly.

But assume we would still have FOLLOWS clause in the definition:

/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 trigger tr1 after insert on t1 for each row set @a=1 */;;
 
...
 
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 trigger tr2 after insert on t1 for each row follows tr1 set @a=2 */;;
 
..
 
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 trigger tr3 after insert on t1 for each row follows tr1 set @a=3 */;;

After restoring the dump, tr2 and tr3 will be swapped.

Comment by Alvin Richards (Inactive) [ 2016-10-11 ]

elenst this was found as part of testing MDEV-6112 that allows for multiple triggers, with the FOLLOWS and PROCEEDS syntax and its a port of functionality from MySQL 5.7. The MySQL documentation states

As of MySQL 5.7.2, it is possible to define multiple triggers for a given table that have the same trigger event and action time. For example, you can have two BEFORE UPDATE triggers for a table. By default, triggers that have the same trigger event and action time activate in the order they were created. To affect trigger order, specify a clause after FOR EACH ROW that indicates FOLLOWS or PRECEDES and the name of an existing trigger that also has the same trigger event and action time. With FOLLOWS, the new trigger activates after the existing trigger. With PRECEDES, the new trigger activates before the existing trigger.

http://dev.mysql.com/doc/refman/5.7/en/trigger-syntax.html

The assumption would be that is the order is specified, then at execution time the order is followed. Therefore a dump and restore would also need to preserve this order.

Showing this clause in the "show create tigger" is just a usability issue for that command. You may have found additional issues that need to be tested and verified. It may also be worth checking what MySQL 5.7 does in this regard.

Comment by Elena Stepanova [ 2016-10-11 ]

alvinr, the current implementation complies with the quote above, but it has nothing to do with SHOW CREATE output.

The assumption would be that is the order is specified, then at execution time the order is followed. Therefore a dump and restore would also need to preserve this order.

The whole point of my previous example was to demonstrate that the dump and restore do preserve the order now, but if this JIRA request is implemented, they won't be able to.
Here is an even simpler example:

MariaDB [test]> create table t1 (i int);
 
Query OK, 0 rows affected (0.42 sec)
 
 
 
MariaDB [test]> create trigger tr1 after insert on t1 for each row set @a=1;
 
Query OK, 0 rows affected (0.13 sec)
 
 
 
MariaDB [test]> create trigger tr2 after insert on t1 for each row follows tr1 set @a=2;
 
Query OK, 0 rows affected (0.11 sec)
 
 
 
MariaDB [test]> drop trigger tr1;
 
Query OK, 0 rows affected (0.08 sec)

Now, after trigger tr1 is dropped, tr2 automatically becomes the first trigger, and all is well. But if we had the FOLLOWS clause in the definition, the definition would have become invalid.

All in all, I wouldn't say that showing FOLLOWS in SHOW CREATE is a usability improvement; on the contrary, it will make the definition unreliable and force a user to edit it manually.

It may also be worth checking what MySQL 5.7 does in this regard.

Nothing, naturally MySQL doesn't preserve PRECEDES / FOLLOWS in SHOW CREATE.

I suppose if there is a real justified demand to see it in SHOW CREATE, it can be put there as a comment (not executable comment!). But frankly I don't know why it can be needed at all, it's really only relevant at the time of creation.

Comment by Alvin Richards (Inactive) [ 2016-10-11 ]

Confirming with MySQL 5.7.15, that "show create trigger" does not include the "following" expression. Therefore closing this as not a bug.

MySQL 5.7.15 output

mysql> show create trigger table_a_trig_2;
 
+----------------+-------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+------------------------+
 
| Trigger        | sql_mode                                                                                                                                  | SQL Original Statement                                                                                                                                                                                | character_set_client | collation_connection | Database Collation | Created                |
 
+----------------+-------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+------------------------+
 
| table_a_trig_2 | 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 | CREATE DEFINER=`root`@`%` trigger table_a_trig_2
 
before insert on table_a
 
for each row
 
begin
 
  insert into table_a_audit (id, ts, new_value)
 
    value (new.id, current_timestamp(6), new.value);
 
end | latin1               | latin1_swedish_ci    | latin1_swedish_ci  | 2016-10-11 20:45:21.34 |
 
+----------------+-------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+------------------------+
 
1 row in set (0.00 sec)

Generated at Thu Feb 08 07:46:47 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.