MySQL Bugs: #30962: Triggers for table... have no creation context warning after live 5.0 to5.1 upg

Bug #30962	Triggers for table... have no creation context warning after live 5.0 to5.1 upg
Submitted:	12 Sep 2007 1:10	Modified:	4 Dec 2007 19:54
Reporter:	Omer Barnir (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Command-line Clients	Severity:	S3 (Non-critical)
Version:	5.1.22	OS:	Any
Assigned to:	Paul DuBois	CPU Architecture:	Any

Description:
Following a live upgrade from 5.0 to 5.1 the following warning is returned when adding triggers in tables created in 5.0:
+---------+------+----------------------------------------------------------+
| Level   | Code | Message                                                  |
+---------+------+----------------------------------------------------------+
| Warning | 1600 | Triggers for table `test`.`tb1` have no creation context |
+---------+------+----------------------------------------------------------+
1 row in set (0.00 sec)

The problem is most likely casused from the same issues described in bug=30707
and bug=30731.

How to repeat:
1) Create a 5.0 system and using 
   cd <5.0 install>/mysql-test
   perl ./mysql-test-run.pl --start-and-exit --vardir=/tmp/var
2) Using the mysql client create a trigger on a table 
   USE test
   CREATE TABLE tb1 (i INT);
   CREATE TRIGGER tg1 BEFORE INSERT ON tb1 FOR EACH ROW SET @var1=@var1+1;
3) Stop the system and start a 5.1 system pointing to the same database 
   cd <5.1 install>/mysql-test
   perl ./mysql-test-run.pl --start-dirty --vardir=/tmp/var
4) Run mysql-upgrade on the system 
   <5.1 install>/bin/mysql_upgrade --datadir=/tmp/var --basedir=<5.1 install> 
   --force --socket=/tmp/var/tmp/master.sock --user=root
5) Using the mysql client run 
   USE test
   CREATE TRIGGER tg2 AFTER INSERT ON tb1 FOR EACH ROW SET @var1=2*@var1;
>> following the result set you wil see '1 warning'
   SHOW WARNINGS; will dispay:
+---------+------+----------------------------------------------------------+
| Level   | Code | Message                                                  |
+---------+------+----------------------------------------------------------+
| Warning | 1600 | Triggers for table `test`.`tb1` have no creation context |
+---------+------+----------------------------------------------------------+

Suggested fix:
Have the mysql_upgrade program update all needed structures and values to the correct/valid 5.1 formats/values

Issue has been noted under "Server Changes" at:

http://dev.mysql.com/doc/refman/5.1/en/upgrading-from-5-0.html

See also http://dev.mysql.com/doc/refman/5.1/en/news-5-1-21.html
(under "Bugs fixed")

It was decided to document rather than fix this and 2 other bugs of the same nature.
According to Paul's comment above, this has been done. Reassigning and closing.

see bug #44665

The URL is now http://dev.mysql.com/doc/refman/5.1/en/upgrading-from-previous-series.html (not the upgrading-from-5-0.html URL above)

See bug #45235