| Bug #30217 | Views: changes in metadata behaviour between 5.0 and 5.1 | ||
|---|---|---|---|
| Submitted: | 2 Aug 2007 23:55 | Modified: | 18 Apr 2008 15:32 |
| Reporter: | Peter Gulutzan | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: DDL | Severity: | S3 (Non-critical) |
| Version: | 5.1.21-beta-debug | OS: | Any |
| Assigned to: | Alexander Nozdrin | CPU Architecture: | Any |
[3 Aug 2007 1:34]
MySQL Verification Team
Thank you for the bug report. Verified as described.
[3 Aug 2007 7:11]
Konstantin Osipov
Peter, what exactly does not behave as documented in the manual?
[3 Aug 2007 15:44]
Peter Gulutzan
The MySQL Reference Manual 22.15. The INFORMATION_SCHEMA VIEWS Table http://dev.mysql.com/doc/refman/5.1/en/views-table.html says: "The VIEW_DEFINITION column has most of what you see in the Create Table field that SHOW CREATE VIEW produces. Skip the words before SELECT and skip the words WITH CHECK OPTION." That statement has become false. For example, SHOW CREATE VIEW has: "... select concat(`v1`.`a1`,_latin2'c') AS `a2` from `v1` ..." but INFORMATION_SCHEMA.VIEWS.VIEW_DEFINITION (5.1.21) has: "select a1 || 'c' as a2 from v1" while INFORMATION_SCHEMA.VIEWS.VIEW_DEFINITION (5.0.48) has: "... select concat(`v1`.`a1`,_latin2'c') AS `a2` from `test`.`v1`" Also, Chapter 22. The INFORMATION_SCHEMA Database http://dev.mysql.com/doc/refman/5.1/en/information-schema.html "The SELECT ... FROM INFORMATION_SCHEMA statement is intended as a more consistent way to provide access to the information provided by the various SHOW statements that MySQL supports (SHOW DATABASES, SHOW TABLES, and so forth). Using SELECT has these advantages, compared to SHOW: ..." But if INFORMATION_SCHEMA doesn't have all that I need to recreate a view, and SHOW has all that I need, then SELECT has a disadvantage compared to SHOW.
[6 Aug 2007 11:26]
Konstantin Osipov
Peter, I don't see where
(b) view usage, e.g. select from view_name,
produces different results in 5.1.21 versus 5.0.48.
View usage did not change, and your test case confirms that.
The discrepancy between SELECT view_definition FROM information_schema.views and
SHOW CREATE VIEW needs to be addressed separately.
The bug is in SHOW CREATE VIEW output, not in IFNORAMTION_SCHEMA.VIEWS.VIEW_DEFINITION output.
The remaining should be addressed in documentation.
To sum up, I suggest this bug is split, so that the docs team can update the manual with the new output from SHOW CREATE VIEW/SELECT * FROM information_schema.views and we can address SHOW CREATE VIEW.
[6 Aug 2007 14:15]
Peter Gulutzan
Konstantin,
I cannot edit the original statement:
"
The storage of view definitions has changed. This means that:
(a) metadata access, e.g. select from information_schema.views,
produces different results in 5.1.21 versus 5.0.48
(b) view usage, e.g. select from view_name,
produces different results in 5.1.21 versus 5.0.48.
I received a note that this is due to a 'fix' for bug#11986.
But Bug#11986 wasn't about this. It's a surprise feature change.
"
I now see that I drew the wrong conclusions from your note about
bug#11986. I'm sorry. The corrected bug report is:
"
The storage of view definitions has changed. This means that:
(a) metadata access, e.g. select from information_schema.views,
produces different results in 5.1.21 versus 5.0.48
This is apparently due to a 'fix' for another bug, possibly
Bug#11986 or Bug#16291. There are related feature requests.
"
I have supplied an example that shows the behaviour
changed significantly in 5.1.21. I have shown that
the previous behaviour was documented. The documentation
correctly described the previous behaviour. Changing the
documentation is not proper unless it can be shown that
the change was done properly.
[28 Jan 2008 13:19]
Bugs System
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/41301 ChangeSet@1.2661, 2008-01-28 16:20:15+03:00, anozdrin@quad. +14 -0 Fix for Bug#30217: Views: changes in metadata behaviour between 5.0 and 5.1. The problem was that in the patch for Bug#11986 it was decided to store original query in UTF8 encoding for the INFORMATION_SCHEMA. This approach however turned out to be quite difficult to implement properly. So, the fix is to rollback to the previous functionality, but also to fix it to support multi-character-set-queries properly. The idea is to generate UTF8-query from the item-tree after parsing view declaration. The UTF8-query should: - be completely in UTF8; - not contain character set introducers. For more information, see WL4052.
[28 Jan 2008 16:23]
Alexander Nozdrin
The real problem in this bug is the following: INFORMATION_SCHEMA output
is not stable/consistent/ with regard to mysqldump/restore operation.
Here is the explanation:
- at the CREATE VIEW time, UTF8-query is translated from the original
query and stored in the FRM-file for the future use in INFORMATION_SCHEMA;
- at the CREATE VIEW time, normalized SELECT-statement is generated
from the item-tree, built on the original query by the parser. That
normalized SELECT-statement is stored in the FRM-file as
view definition;
- the original SELECT-statement is stored in FRM-file, but never used;
- mysqldump uses SHOW CREATE VIEW statement;
- SHOW CREATE VIEW returns normalized SELECT-statement of the original
CREATE VIEW statement;
- there is no way mysqldump can retrieve the original CREATE VIEW
from the server;
- INFORMATION_SCHEMA uses UTF8-query, which is translated from
the CREATE VIEW statement at the view-loading time;
- So, mysqldump dumps normalized SELECT-statement, and after restoration
UTF8-query is built from that normalized SELECT-statement.
I.e. at the first CREATE VIEW time, UTF8-query is built from
the "truly" *original* SELECT-statement. At the CREATE VIEW time
during restoration, UTF8-query is built from the *normalized*
SELECT-statement.
[22 Feb 2008 10:29]
Bugs System
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/42812 ChangeSet@1.2567, 2008-02-22 13:30:33+03:00, anozdrin@quad. +39 -0 Fix for Bug#30217: Views: changes in metadata behaviour between 5.0 and 5.1. The problem was that in the patch for Bug#11986 it was decided to store original query in UTF8 encoding for the INFORMATION_SCHEMA. This approach however turned out to be quite difficult to implement properly. The main problem is to preserve the same IS-output after dump/restore. So, the fix is to rollback to the previous functionality, but also to fix it to support multi-character-set-queries properly. The idea is to generate INFORMATION_SCHEMA-query from the item-tree after parsing view declaration. The IS-query should: - be completely in UTF8; - not contain character set introducers. For more information, see WL4052.
[22 Feb 2008 10:59]
Alexander Nozdrin
Pushed into 5.1-runtime.
[22 Feb 2008 11:27]
Bugs System
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/42817 ChangeSet@1.2584, 2008-02-22 14:27:58+03:00, anozdrin@quad. +4 -0 Fix the merge of the patch for Bug#30217.
[3 Mar 2008 18:19]
Bugs System
Pushed into 5.1.24-rc
[3 Mar 2008 18:19]
Bugs System
Pushed into 6.0.5-alpha
[30 Mar 2008 18:54]
Jon Stephens
Fix also available in MySQL 5.1.23-ndb-6.3.11.
[18 Apr 2008 15:32]
Paul DuBois
Noted in 5.1.24, 6.0.5 changelogs. View metadata returned from INFORMATION_SCHEMA.VIEWS was changed by the fix for Bug#11986, causing the information returned in MySQL 5.1 to differ from that returned in 5.0.
Description: The storage of view definitions has changed. This means that: (a) metadata access, e.g. select from information_schema.views, produces different results in 5.1.21 versus 5.0.48 (b) view usage, e.g. select from view_name, produces different results in 5.1.21 versus 5.0.48. I received a note that this is due to a 'fix' for bug#11986. But Bug#11986 wasn't about this. It's a surprise feature change. How to repeat: This is what views look like with 5.0.48 ... mysql> create database kl; Query OK, 1 row affected (0.03 sec) mysql> use kl Database changed mysql> set sql_mode=''; Query OK, 0 rows affected (0.00 sec) mysql> create view v as select 'a' || 'a'; Query OK, 0 rows affected (0.00 sec) mysql> select * from v; +------------+ | 'a' || 'a' | +------------+ | 0 | +------------+ 1 row in set, 2 warnings (0.00 sec) mysql> set sql_mode='ansi'; Query OK, 0 rows affected (0.00 sec) mysql> select * from v; +------------+ | 'a' || 'a' | +------------+ | 0 | +------------+ 1 row in set, 2 warnings (0.00 sec) mysql> select view_definition from information_schema.views where table_schema = 'kl'; +-----------------------------------------------------------------------------+ | view_definition | +-----------------------------------------------------------------------------+ | /* ALGORITHM=UNDEFINED */ select (_latin1'a' or _latin1'a') AS `'a' || 'a'` | +-----------------------------------------------------------------------------+ 1 row in set (0.01 sec) mysql> drop view v; Query OK, 0 rows affected (0.00 sec) mysql> create view v as select 'a' || 'a'; Query OK, 0 rows affected (0.00 sec) mysql> select * from v; +------------+ | 'a' || 'a' | +------------+ | aa | +------------+ 1 row in set (0.00 sec) mysql> select view_definition from information_schema.views -> where table_schema = 'kl'; +--------------------------------------------------------------------------------+ | view_definition | +--------------------------------------------------------------------------------+ | /* ALGORITHM=UNDEFINED */ select concat(_latin1'a',_latin1'a') AS `'a' || 'a'` | +--------------------------------------------------------------------------------+ 1 row in set (0.01 sec) This is what views look like with 5.1.21 ... mysql> create database kl; Query OK, 1 row affected (0.02 sec) mysql> use kl Database changed mysql> set sql_mode=''; Query OK, 0 rows affected (0.00 sec) mysql> create view v as select 'a' || 'a'; Query OK, 0 rows affected (0.00 sec) mysql> select * from v; +------------+ | 'a' || 'a' | +------------+ | 0 | +------------+ 1 row in set, 2 warnings (0.00 sec) mysql> set sql_mode='ansi'; Query OK, 0 rows affected (0.00 sec) mysql> select * from v; +------------+ | 'a' || 'a' | +------------+ | 0 | +------------+ 1 row in set, 2 warnings (0.00 sec) mysql> select view_definition from information_schema.views where -> table_schema = 'kl'; +-------------------+ | view_definition | +-------------------+ | select 'a' || 'a' | +-------------------+ 1 row in set (0.01 sec) mysql> drop view v; Query OK, 0 rows affected (0.00 sec) mysql> create view v as select 'a' || 'a'; Query OK, 0 rows affected (0.00 sec) mysql> select * from v; +------------+ | 'a' || 'a' | +------------+ | aa | +------------+ 1 row in set (0.01 sec) mysql> select view_definition from information_schema.views -> where table_schema = 'kl'; +-------------------+ | view_definition | +-------------------+ | select 'a' || 'a' | +-------------------+ 1 row in set (0.01 sec)