MySQL Bugs: #45235: 5.1 does not support 5.0-only syntax triggers in any way

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

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

Also note bug #45422

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.

we always verify against bzr trunks, 5.1+ means 'bzr trunk at this moment'.

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

Suggestion for test case

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

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.

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.

Some review comments were sent by e-mail.

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

Review for new version of patch was sent by e-mail.

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.

Sent review for new version of patch over e-mail.

See bug #30962, bug #44665

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