MySQL Bugs: #36649: Condition area is not properly cleaned up after stored routine invocation

Bug #36649	Condition area is not properly cleaned up after stored routine invocation
Submitted:	11 May 2008 2:53	Modified:	21 Aug 2010 1:30
Reporter:	Davi Arnaut (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S3 (Non-critical)
Version:	5.0, 5.1, 6.0	OS:	Any
Assigned to:	Davi Arnaut	CPU Architecture:	Any

Description:
The problem is that the diagnostics area of a trigger is not isolated from the area of the statement that caused the trigger invocation. In MySQL terms, it means that warnings generated during the execution of the trigger are not removed from the "warning area" at the end of the execution. Another problem is that statements inside a trigger don't follow the warning list cleanup rules and never clears the list (warning area).

How to repeat:
create table t1 (a int);
delimiter |;
create trigger t1_ai before insert on t1 for each row
begin
  declare buf varchar(1);
  select 10 into buf;
  select 10 into buf;
end|
delimiter ;|
insert into t1 values (1);
Warnings:
Warning	1265	Data truncated for column 'buf' at row 1
Warning	1265	Data truncated for column 'buf' at row 1

Suggested fix:
MySQL must allow or emulate multiple condition areas. A stored function or trigger will get its own "warning area". At the beginning of the stored function or trigger, all warnings from the caller area will be copied to the warning area of the trigger (or, in standard terminology, pushed). During execution, the warnings should be cleared according to the MySQL rules. At the end of the function/trigger, the "warning area" should be destroyed along with all warnings it contains (or, in standard terminology, popped).

Problem is repeatable with version 5.0 and 5.1 as well

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/61267

2777 Davi Arnaut	2008-12-10
      Bug#36649: Condition area is not properly cleaned up after stored routine invocation
      
      The problem is that the diagnostics area of a trigger is not
      isolated from the area of the statement that caused the trigger
      invocation. In MySQL terms, it means that warnings generated
      during the execution of the trigger are not removed from the
      "warning area" at the end of the execution.
      
      Before this fix, the rules for MySQL message list life cycle (see
      manual entry for SHOW WARNINGS) did not apply to statements
      inside stored programs:
      
       - The manual says that the list of messages is cleared by a
         statement that uses a table (any table). However, such
         statement, if run inside a stored program did not clear the
         message list.
       - The manual says that the list is cleared by a statement that
         generates a new error or a warning, but this was not the case
         with stored program statements either and is changed to be the
         case as well.
      
      In other words, after this fix, a statement has the same effect
      on the message list regardless of whether it's executed inside a
      stored program/sub-statement or not.
      
      This introduces an incompatible change:
      
       - before this fix, a, e.g. statement inside a trigger could
         never clear the global warning list
       - after this fix, a trigger that generates a warning or uses a
         table, clears the global warning list
      
      This change is not backward compatible as it is intended to make
      MySQL behavior similar to the SQL standard behavior:
      
      A stored function or trigger will get its own "warning area" (or,
      in standard terminology, diagnostics area).  At the beginning of
      the stored function or trigger, all messages from the caller area
      will be copied to the area of the trigger.  During execution, the
      message list will be cleared according to the MySQL rules
      described on the manual (SHOW WARNINGS entry).  At the end of the
      function/trigger, the "warning area" will be destroyed along with
      all warnings it contains, except that if the last statement of
      the function/trigger generated messages, these are copied into
      the "warning area" of the caller.
      
      Consequently, statements that use a table or generate a warning
      *will* clear warnings inside the trigger, but that will have no
      effect to the warning list of the calling (outer) statement.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/61287

2777 Davi Arnaut	2008-12-10
      Bug#36649: Condition area is not properly cleaned up after stored routine invocation
      
      The problem is that the diagnostics area of a trigger is not
      isolated from the area of the statement that caused the trigger
      invocation. In MySQL terms, it means that warnings generated
      during the execution of the trigger are not removed from the
      "warning area" at the end of the execution.
      
      Before this fix, the rules for MySQL message list life cycle (see
      manual entry for SHOW WARNINGS) did not apply to statements
      inside stored programs:
      
       - The manual says that the list of messages is cleared by a
         statement that uses a table (any table). However, such
         statement, if run inside a stored program did not clear the
         message list.
       - The manual says that the list is cleared by a statement that
         generates a new error or a warning, but this was not the case
         with stored program statements either and is changed to be the
         case as well.
      
      In other words, after this fix, a statement has the same effect
      on the message list regardless of whether it's executed inside a
      stored program/sub-statement or not.
      
      This introduces an incompatible change:
      
       - before this fix, a, e.g. statement inside a trigger could
         never clear the global warning list
       - after this fix, a trigger that generates a warning or uses a
         table, clears the global warning list
       - however, when we leave a trigger or a function, the caller's
         warning information is restored (see more on this below).
      
      This change is not backward compatible as it is intended to make
      MySQL behavior similar to the SQL standard behavior:
      
      A stored function or trigger will get its own "warning area" (or,
      in standard terminology, diagnostics area).  At the beginning of
      the stored function or trigger, all messages from the caller area
      will be copied to the area of the trigger.  During execution, the
      message list will be cleared according to the MySQL rules
      described on the manual (SHOW WARNINGS entry).  At the end of the
      function/trigger, the "warning area" will be destroyed along with
      all warnings it contains, except that if the last statement of
      the function/trigger generated messages, these are copied into
      the "warning area" of the caller.
      
      Consequently, statements that use a table or generate a warning
      *will* clear warnings inside the trigger, but that will have no
      effect to the warning list of the calling (outer) statement.

Queued to 6.0-runtime

Pushed into 6.0.10-alpha (revid:davi.arnaut@sun.com-20090114101000-u02ac63f9ee13x84) (version source revid:davi.arnaut@sun.com-20081210215359-i876m4zgc2d6rzs3) (merge vers: 6.0.9-alpha) (pib:6)

Noted in 6.0.10 changelog.

Previously, statements inside a stored program did not clear the
warning list. For example, warnings or errors generated by statements
within a trigger or stored function would be accumulated and added to
the message list for the statement that activated the trigger or 
invoked the function, "polluting" the output of SHOW WARNINGS or SHOW
ERRORS for the outer statement. Normally, messages for a statement
that can generate messages replace messages from the previous such
statement. The effect was that a statement could have a different 
effect on the message list depending on whether it executed inside or
outside of a stored program.

Now within a stored program, successive statements that can generate
messages update the message list and replace messages from the 
previous such statement. Only messages from the last of these
statements is copied to the message list for the outer statement.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/89989

2915 Davi Arnaut	2009-11-10
      Backport of Bug#36649 to mysql-next-mr
      ------------------------------------------------------------
      revno: 2630.39.3
      revision-id: davi.arnaut@sun.com-20081210215359-i876m4zgc2d6rzs3
      parent: kostja@sun.com-20081208222938-9es7wl61moli71ht
      committer: Davi Arnaut <Davi.Arnaut@Sun.COM>
      branch nick: 36649-6.0
      timestamp: Wed 2008-12-10 19:53:59 -0200
      message:
        Bug#36649: Condition area is not properly cleaned up after stored routine invocation
      
        The problem is that the diagnostics area of a trigger is not
        isolated from the area of the statement that caused the trigger
        invocation. In MySQL terms, it means that warnings generated
        during the execution of the trigger are not removed from the
        "warning area" at the end of the execution.
      
        Before this fix, the rules for MySQL message list life cycle (see
        manual entry for SHOW WARNINGS) did not apply to statements
        inside stored programs:
      
          - The manual says that the list of messages is cleared by a
            statement that uses a table (any table). However, such
            statement, if run inside a stored program did not clear the
            message list.
          - The manual says that the list is cleared by a statement that
            generates a new error or a warning, but this was not the case
            with stored program statements either and is changed to be the
            case as well.
      
        In other words, after this fix, a statement has the same effect
        on the message list regardless of whether it's executed inside a
        stored program/sub-statement or not.
      
        This introduces an incompatible change:
      
          - before this fix, a, e.g. statement inside a trigger could
            never clear the global warning list
          - after this fix, a trigger that generates a warning or uses a
            table, clears the global warning list
          - however, when we leave a trigger or a function, the caller's
            warning information is restored (see more on this below).
      
        This change is not backward compatible as it is intended to make
        MySQL behavior similar to the SQL standard behavior:
      
        A stored function or trigger will get its own "warning area" (or,
        in standard terminology, diagnostics area).  At the beginning of
        the stored function or trigger, all messages from the caller area
        will be copied to the area of the trigger.  During execution, the
        message list will be cleared according to the MySQL rules
        described on the manual (SHOW WARNINGS entry).  At the end of the
        function/trigger, the "warning area" will be destroyed along with
        all warnings it contains, except that if the last statement of
        the function/trigger generated messages, these are copied into
        the "warning area" of the caller.
      
        Consequently, statements that use a table or generate a warning
        *will* clear warnings inside the trigger, but that will have no
        effect to the warning list of the calling (outer) statement.
     @ mysql-test/r/sp.result
        Fix test case results.
     @ mysql-test/r/trigger.result
        Fix test case results.
     @ mysql-test/t/sp.test
        Add test case for Bug#36649
     @ mysql-test/t/trigger.test
        Add test case for Bug#36649
     @ sql/sp_head.cc
        Emulate multiple warning areas -- one per stored program instance.
     @ sql/sql_parse.cc
        Message list reset rules are the same for statements inside
        or outside compound statements.

Queued to mysql-next-mr-runtime

Pushed into 5.6.0-beta (revid:davi.arnaut@sun.com-20091119234808-xbjpkwaxjt5x5c0b) (version source revid:sp1r-davi@mysql.com/endora.local-20080418131946-26951) (merge vers: 6.0.6-alpha) (pib:13)

Pushed into 6.0.14-alpha (revid:kostja@sun.com-20091120124947-yi6h2jbgw0kbciwm) (version source revid:sp1r-davi@mysql.com/endora.local-20080418131946-26951) (merge vers: 6.0.6-alpha) (pib:13)

Noted in 5.6.0 changelog.

Pushed into 5.5.3-m3 (revid:alik@sun.com-20100306103849-hha31z2enhh7jwt3) (version source revid:sp1r-davi@mysql.com/endora.local-20080418131946-26951) (merge vers: 6.0.6-alpha) (pib:16)

Moved 5.6.0 changelog entry to 5.5.3.

Pushed into mysql-trunk 5.6.1-m4 (revid:alik@sun.com-20100816062701-qo9dpnk5tkt1pksb) (version source revid:alik@sun.com-20100816062603-xc16eftmv7rmktyq) (merge vers: 5.6.1-m4) (pib:20)

Pushed into mysql-next-mr (revid:alik@sun.com-20100816062819-bluwgdq8q4xysmlg) (version source revid:alik@sun.com-20100816062612-enatdwnv809iw3s9) (pib:20)

Noted in 5.6.1 changelog.