Bug #69103 triggers should attempt recompile when faced with an error
Submitted: 30 Apr 2013 16:53
Reporter: Trey Raymond Email Updates:
Status: Open Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S4 (Feature request)
Version:5.1.48 OS:Linux (rhel)
Assigned to: CPU Architecture:Any
Tags: trigger, udf

[30 Apr 2013 16:53] Trey Raymond
Description:
An annoyance during larger deploys - if a trigger can't find something when it's created, then what it's looking for is created, it won't recompile and look for it until you drop/recreate the trigger.  I've only seen this on UDFs, but it's probably a good idea globally.  You might apply trigger code to a few hundred hosts, then find out a dozen were missing a udf, and you'd have to undo and redo the code deploy rather than just adding the functions where missing.

How to repeat:
1. Create a trigger calling a UDF on a server where the UDF has not been added yet.  

2. Test.  Any calls to the trigger will error as expected with cannot find function dbname.funcname.

3. Add the UDF.

4. Test.  Calls still error with that, as when the trigger was created it expected a local sql function - even though it's a UDF and the code should be able to find it.

5. Drop and recreate the trigger.

6. Test.  Now things work fine.

Suggested fix:
Let the trigger attempt to recompile itself when faced with an error - that's the only thing I can think of that's causing this to fail in this manner.