Bug #45235 5.1 does not support 5.0-only syntax triggers in any way
Submitted: 1 Jun 2009 6:42 Modified: 16 Jun 2011 19:31
Reporter: Domas Mituzas Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S2 (Serious)
Version: OS:Any
Assigned to: CPU Architecture:Any

[1 Jun 2009 6:42] Domas Mituzas
Description:
If a trigger uses syntax which is supported in 5.0, but not 5.1, 5.1 will ignore all triggers on that table, and will not allow any DBA tasks to be done on those triggers, thus making it impossible for DBA fixes.

How to repeat:
5.0:

(deliberately hitting Bug#30234 incompatible change)

create trigger tx before insert on t1 for each row delete from t1 a using t1 a;

5.1:

mysql> show triggers;
Empty set, 2 warnings (0.00 sec)

mysql> show warnings;
+---------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                                                        |
+---------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 1603 | Triggers for table `meh`.`t1` have no creation context                                                                                                         | 
| Warning | 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 'a using t1 a' at line 1 | 
+---------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> drop trigger tx;
ERROR 1064 (42000): 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 'a using t1 a' at line 1

Suggested fix:
allow dropping/recreating bad triggers, don't just fail miserably
[10 Jun 2009 5:32] Roel Van de Paar
Also note bug #45422
[17 Jun 2009 13:33] Martin Hansson
I can't see any mentioning of which 5.1 version this was verified on, let alone that it was verified in the first place. Please refrain from setting bugs to 'verified' when reporting them.
[17 Jun 2009 13:48] Domas Mituzas
we always verify against bzr trunks, 5.1+ means 'bzr trunk at this moment'.
[18 Jun 2009 7:38] Martin Hansson
I get different results. The attached test case below causes core dump on DROP TRIGGER tx. Note that the result is the same for SHOW TRIGGERS, however. Maybe I've hit a different bug?

I branched the tree from June 1st:

SELECT version();
version()
5.1.36-debug-log
[18 Jun 2009 7:40] Martin Hansson
Suggestion for test case

Attachment: bug45235.test (application/octet-stream, text), 508 bytes.

[18 Aug 2009 9:20] Bugs System
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/80980

2936 Martin Hansson	2009-08-18
      Bug#45235: 5.1 does not support 5.0-only syntax triggers in any way
      
      Execution of DROP TRIGGER expected the .TRG files to be fullly parsed before
      deleting them. This is not always possible, however, as the trigger files may
      have been created with an older verion of MySQL and contain deprecated syntax.
      Fixed by making DROP TRIGGER silently delete .TRN and .TRG files if .TRG file
      is not parseable.
     @ mysql-test/r/trigger.result
        Bug#45235: Test result.
     @ mysql-test/t/trigger.test
        Bug#45235: Test case.
     @ sql/sql_base.cc
        Bug#45235: Setting the trigger list member to NULL in order to avoid double-deletes
        in case of a parse failure in trigger file.
     @ sql/sql_class.h
        Bug#45235: Addded utility class for suppression of parse errors in trigger file read.
     @ sql/sql_parse.cc
        Bug#45235: Removed assertion which can no longer be guaranteed to be true.
     @ sql/sql_trigger.cc
        Bug#45235: 
        - The fix: Added code for silently deleting trigger files in case of
        parse errors in .TRG files during DROP TRIGGER.
        - Corrected a doxgen comment.
     @ sql/sql_trigger.h
        Bug#45235: Declaration of new method for deleting trigger files.
[18 Aug 2009 9:26] Bugs System
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/80981

2936 Martin Hansson	2009-08-18
      Bug#45235: 5.1 does not support 5.0-only syntax triggers in any way
      
      Execution of DROP TRIGGER expected the .TRG file to be fully parsed before
      deleting the trigger files. This is not always possible, however, as the 
      trigger files may have been created with an older verion of MySQL and 
      contain deprecated syntax. Fixed by making DROP TRIGGER silently delete 
      trigger files if .TRG file is not parseable.
     @ mysql-test/r/trigger.result
        Bug#45235: Test result.
     @ mysql-test/t/trigger.test
        Bug#45235: Test case.
     @ sql/sql_base.cc
        Bug#45235: Setting the trigger list member to NULL in order to avoid 
        double-deletes in case of a parse failure in trigger file.
     @ sql/sql_class.h
        Bug#45235: Addded utility class for suppression of parse errors in trigger file read.
     @ sql/sql_parse.cc
        Bug#45235: Removed assertion which can no longer be guaranteed to be true.
     @ sql/sql_trigger.cc
        Bug#45235: 
        - The fix: Added code for silently deleting trigger files in case of
        parse errors in .TRG files during DROP TRIGGER.
        - Corrected a doxgen comment.
     @ sql/sql_trigger.h
        Bug#45235: Declaration of new method for deleting trigger files.
[28 Aug 2009 15:19] Dmitry Lenev
Some review comments were sent by e-mail.
[1 Sep 2009 8:56] Bugs System
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/82088

2936 Martin Hansson	2009-09-01
      Bug#45235: 5.1 does not support 5.0-only syntax triggers in any way
      
      A parse error when opening a trigger file would disallow the user to 
      drop the trigger, the most common scenario being upgrade from 5.0 to 5.1
      where a lot of syntax is deprecated. The problem was that the trigger 
      name has to be parsed out in order to successfully drop the trigger. 
      Obviously this cannot be guaranteed if there was a parse error.
      
      Fixed by analyzing LEX structure in the event of parse errors during 
      trigger load and recovering the trigger name if present by making a local
      copy before the LEX structure is cleaned up. The error is then silenced.
      We will end up with a partially initialized trigger structure, but it 
      serves to execute a DROP TRIGGER command.
     @ mysql-test/r/trigger-compat.result
        Bug#45235: Test result.
     @ mysql-test/t/trigger-compat.test
        Bug#45235: Test case.
     @ sql/sql_parse.cc
        Bug#45235: Removed assertion which can no longer be maintained.
     @ sql/sql_trigger.cc
        Bug#45235: Various code changes.
        1) Error handler class that silences parse errors and picks up trigger name if possible.
        2) Warnings elimination
        3) Comment correction
        4, 5) Fix.
        6) Indentation correction
        7) Split up giant assertion
[11 Sep 2009 10:08] Dmitry Lenev
Review for new version of patch was sent by e-mail.
[22 Sep 2009 7:49] Bugs System
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/84055

3118 Martin Hansson	2009-09-22
      Bug#45235: 5.1 does not support 5.0-only syntax triggers in any way
      
      A parse error when opening a trigger file would disallow the user to drop the
      trigger causing it. The most common scenario is a server upgrade, when the
      trigger uses deprecated syntax. The problem is that the trigger name has to be
      parsed out in order to successfully drop the trigger, which obviously cannot
      be guaranteed. For the upgrade case, however, it is worthwhile to analyze the
      parse tree and try to recover it. The error is then silenced, but stored. The
      Trigger Manager then enters an error state, and throws the error whenever a
      trigger on the table is invoked or manipulated. The error state is exited from
      when all broken triggers are dropped.
     @ mysql-test/r/trigger-compat.result
        Bug#45235: Test result.
     @ mysql-test/t/trigger-compat.test
        Bug#45235: Test case.
     @ sql/sql_parse.cc
        Bug#45235: Removed assertion for a state that can't be maintained.
     @ sql/sql_trigger.cc
        Bug#45235: 
        - New class to implement error suppression and recovery of trigger name.
        - Comment correction
        - Errors for trigger manipulation statements
        - More comment correction
        - Fix exploiting new class.
        - indentation correction.
        - Split up a conjunction in an assert.
        - Added code for handling completely broken triggers
        - More errors for trigger manipulation statements. 
        - Method for setting broken triggers flag & error message.
     @ sql/sql_trigger.h
        Bug#45235: New members to handle broken triggers and error messages.
[27 Nov 2009 21:02] Dmitry Lenev
Sent review for new version of patch over e-mail.
[17 Mar 2010 9:09] Roel Van de Paar
See bug #30962, bug #44665
[16 Jun 2011 19:31] Paul DuBois
Noted in 5.1.58, 5.5.14, 5.6.3 changelogs.

In MySQL 5.1 and up, if a table had triggers that used syntax
supported in 5.0 but not 5.1, the table became unavailable. Now the 
table is marked as having broken triggers. 

CHANGESET - http://lists.mysql.com/commits/13906