Bug #52025 support automatic upgrade of .trg files from old format to new format
Submitted: 13 Mar 2010 6:56 Modified: 13 Mar 2010 10:21
Reporter: Shane Bester (Platinum Quality Contributor) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S4 (Feature request)
Version:5.1.44 OS:Any
Assigned to: CPU Architecture:Any
Triage: Needs Triage: D5 (Feature request)

[13 Mar 2010 6:56] Shane Bester
Description:
Known issue: A .trg file from a 5.0.X installation will lack the following three
lines:

client_cs_names=
connection_cl_names=
db_cl_names=

Known issue: Whenever the trigger is invoked it issues this warning:
| Warning | 1603 | Triggers for table `test`.`t1` have no creation context |

The 5.1 server in fact can and does repair this .trg filling in default values for those three missing lines, but it only does this when rewriting the .trg file (when adding/removing triggers).

ref: bug #44665 and bug #30962

How to repeat:
on 5.0.90:
----------
drop table if exists t1;
create table t1 (i int)engine=myisam;
create trigger trg1 before insert on t1 
for each row set @i=@i+1;

#upgrade to 5.1.44 and try insert into the table. observe warnings.
insert into t1 values (1);

#now fix it by adding another trigger (enforcing .trg to be rewritten in new format).

create trigger trg2 after insert on t1 
for each row set @i=@i+1;

#tada .trg is written in new format!

Suggested fix:
This is a feature request to enable the 5.1.44 server to rewrite the .trg file immediately when reading it in Table_triggers_list::check_n_load, if it misses those three fields.

Dump/reload of triggers is not a user-friendly workaround, nor is it technically required, since the server can fix it itself.
[13 Mar 2010 10:21] Valeriy Kravchuk
It would be nice to upgrade to new format automatically, while reading .TRG file for the first time in 5.1.x.
[15 Mar 2010 1:40] Roel Van de Paar
IMO, mysql_upgrade should fix this. See comments on bug #44665