Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-11033

"follows" does not show up in "show create trigger" output

Details

    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.

      Attachments

        Issue Links

          Activity

            elenst Elena Stepanova added a comment - - edited

            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.

            elenst Elena Stepanova added a comment - - edited 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.

            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.

            alvinr Alvin Richards (Inactive) added a comment - 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.
            elenst Elena Stepanova added a comment - - edited

            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.

            elenst Elena Stepanova added a comment - - edited 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.

            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)
            

            alvinr Alvin Richards (Inactive) added a comment - 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)

            People

              monty Michael Widenius
              alvinr Alvin Richards (Inactive)
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.