Bug #30707 View ...has no creation context" warning following live upgrade 5.0-->5.1
Submitted: 29 Aug 2007 22:15 Modified: 2 Oct 2007 8:07
Reporter: Omer Barnir (OCA) Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Views Severity:S3 (Non-critical)
Version:5.1.21 OS:Any
Assigned to: Alexander Nozdrin CPU Architecture:Any

[29 Aug 2007 22:15] Omer Barnir
Description:
Following a 'live' upgrade from 5.0 to 5.1 all actions performed on 5.0 created views report include a "View ... has no creation context" warning message

Example:
mysql> select * from v1;
+------+-----------+
| user | host      |
+------+-----------+
| root | 127.0.0.1 |
| root | linux     |
| root | localhost |
+------+-----------+
3 rows in set, 1 warning (0.00 sec)

mysql> show warnings;
+-------+------+------------------------------------------+
| Level | Code | Message                                  |
+-------+------+------------------------------------------+
| Note  | 1596 | View `test`.`v1` has no creation context |
+-------+------+------------------------------------------+
1 row in set (0.00 sec)

The warning is observed in selects from the view, selects from information schema (that include the view and show create for the view.

The problem is a result of the fact that the 5.1 frm file includes additional information:
 - client_cs_name=latin1
 - connection_cl_name=latin1_swedish_ci
 - view_body_utf8=select user from mysql.user
that dod not exist in 5.0.

As a result the above warning is displayed.

If the frm file is modified manualy and the 
 - client_cs_name=latin1
 - connection_cl_name=latin1_swedish_ci
line are added, the warning is not displayed (the "view_body_utf8" entry does not seem to matter for this warning.
Note: this step was done for perposes of analyzing the bug, it should NOT be 
      used as a workaround.  

The only workararound to this issue is to ave the upgrade include an export from 5.0 and an import to 5.1 (a 'non-live' upgrade) that creates the view frm files in the correct 5.1 format

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 view 
   USE test
   SELECT user, host FROM mysql.user;
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
   SELECT * from v1;
>> following the result set you wil see '1 warning'
   SHOW WARNINGS; will dispay:
+-------+------+------------------------------------------+
| Level | Code | Message                                  |
+-------+------+------------------------------------------+
| Note  | 1596 | View `test`.`v1` has no creation context |
+-------+------+------------------------------------------+

                                   

Suggested fix:
Have the mysql_upgrade program update the view frm file to the correct 5.1 format
[13 Sep 2007 0:35] Paul Dubois
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")
[2 Oct 2007 8:07] Alexander Nozdrin
As Paul noted, this is intended behavior:

<quote>
Incompatible change: Several issues were identified for stored programs (stored functions and procedures, triggers, and events) and views containing non-ASCII symbols. These issues involved conversion errors due to incomplete character set information when translating these objects to and from stored format.

To address these problems, the representation for these objects was changed in MySQL 5.1.21. However, the fixes affect all stored programs and views. (For example, you will see warnings about “no creation context.”) To avoid warnings from the server about the use of old definitions from any release prior to 5.1.21, you should dump stored programs and views with mysqldump after upgrading to 5.1.21 or higher, and then reload them to recreate them with new definitions. Invoke mysqldump with a --default-character-set option that names the non-ASCII character set that was used for the definitions when the objects were originally defined.
</quote>

We can not set proper values during upgrade, because this information was lost.
It should have been preserved at the time a view is created.
This warning signals the user that his views are not properly constructed.