| Bug #75405 | I can't get from INFORMATION_SCHEMA original SQL code creating VIEW | ||
|---|---|---|---|
| Submitted: | 4 Jan 2015 22:30 | Modified: | 6 Jan 2015 10:24 |
| Reporter: | Mikhail Gavrilov | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Information schema | Severity: | S3 (Non-critical) |
| Version: | 5.6.21, 5.6.23, 5.7.6 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | creating, information_schema, SQL code, VIEW | ||
[4 Jan 2015 22:30]
Mikhail Gavrilov
[5 Jan 2015 13:27]
Peter Laursen
@Mihail. Nice to see you complaining another place that in our support system! :-) This has been reported before. It surprised a lot of people when Views were introduced in MySQL 5.0, and such reports re-surface from time to time. But the explanations is that the view is *frozen at the very moment it is created* - and SQL standards require this. If you add more columns to the underlying table the new columns are not *and should not be* included in the View. If you DROP or RENAME columns in the underlying table, the View is invalidated. And this is intentional and standardized behaviour according to explanations having appeared here earlier. -- Peter -- Webyog
[5 Jan 2015 13:32]
MySQL Verification Team
this sounds like http://bugs.mysql.com/bug.php?id=10713 that was apparently rejected!
[6 Jan 2015 10:24]
MySQL Verification Team
Hello Mikhail, Thank you for the report. This behavior exists from 5.0 days but imho VIEW_DEFINITION should match what SHOW CREATE VIEW.. shows. Thanks, Umesh
[6 Jan 2015 10:46]
MySQL Verification Team
// 5.0.96
mysql> show variables like '%version%';
+-------------------------+------------------------------+
| Variable_name | Value |
+-------------------------+------------------------------+
| protocol_version | 10 |
| version | 5.0.96-log |
| version_comment | MySQL Community Server (GPL) |
| version_compile_machine | x86_64 |
| version_compile_os | unknown-linux-gnu |
+-------------------------+------------------------------+
5 rows in set (0.00 sec)
mysql> show variables like 'sql_mode';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_mode | |
+---------------+-------+
1 row in set (0.00 sec)
mysql> CREATE TABLE `test` (
-> `a` MEDIUMTEXT,
-> `b` MEDIUMTEXT
-> ) ENGINE=MYISAM DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE VIEW `view_test` AS
-> (SELECT * FROM test);
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW CREATE VIEW `view_test`\G
*************************** 1. row ***************************
View: view_test
Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_test` AS (select `test`.`a` AS `a`,`test`.`b` AS `b` from `test`)
1 row in set (0.00 sec)
mysql> SELECT `TABLE_NAME`
-> ,`TABLE_NAME`
-> ,`VIEW_DEFINITION`
-> FROM `information_schema`.`VIEWS` IS_V
-> WHERE IS_V.`TABLE_SCHEMA`='test' AND IS_V.table_name = 'view_test'
-> ;
+------------+------------+---------------------------------------------------------------------------------------------------------+
| TABLE_NAME | TABLE_NAME | VIEW_DEFINITION |
+------------+------------+---------------------------------------------------------------------------------------------------------+
| view_test | view_test | /* ALGORITHM=UNDEFINED */ (select `test`.`test`.`a` AS `a`,`test`.`test`.`b` AS `b` from `test`.`test`) |
+------------+------------+---------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
[6 Jan 2015 10:46]
MySQL Verification Team
// 5.6.23
mysql> show variables like '%version%';
+-------------------------+---------------------------------------------------------+
| Variable_name | Value |
+-------------------------+---------------------------------------------------------+
| innodb_version | 5.6.23 |
| protocol_version | 10 |
| slave_type_conversions | |
| version | 5.6.23-enterprise-commercial-advanced |
| version_comment | MySQL Enterprise Server - Advanced Edition (Commercial) |
| version_compile_machine | x86_64 |
| version_compile_os | linux-glibc2.5 |
+-------------------------+---------------------------------------------------------+
7 rows in set (0.00 sec)
mysql> show variables like 'sql_mode';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_mode | |
+---------------+-------+
1 row in set (0.00 sec)
mysql> CREATE TABLE `test` (
-> `a` MEDIUMTEXT,
-> `b` MEDIUMTEXT
-> ) ENGINE=MYISAM DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.01 sec)
mysql> CREATE VIEW `view_test` AS
-> (SELECT * FROM test);
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW CREATE VIEW `view_test`\G
*************************** 1. row ***************************
View: view_test
Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_test` AS (select `test`.`a` AS `a`,`test`.`b` AS `b` from `test`)
character_set_client: utf8
collation_connection: utf8_general_ci
1 row in set (0.00 sec)
mysql> SELECT `TABLE_NAME`
-> ,`TABLE_NAME`
-> ,`VIEW_DEFINITION`
-> FROM `information_schema`.`VIEWS` IS_V
-> WHERE IS_V.`TABLE_SCHEMA`='test' AND IS_V.table_name = 'view_test';
+------------+------------+-------------------------------------------------------------------------------+
| TABLE_NAME | TABLE_NAME | VIEW_DEFINITION |
+------------+------------+-------------------------------------------------------------------------------+
| view_test | view_test | (select `test`.`test`.`a` AS `a`,`test`.`test`.`b` AS `b` from `test`.`test`) |
+------------+------------+-------------------------------------------------------------------------------+
1 row in set (0.00 sec)
[6 Jan 2015 10:46]
MySQL Verification Team
// 5.6.7
mysql> CREATE TABLE `test` (
-> `a` MEDIUMTEXT,
-> `b` MEDIUMTEXT
-> ) ENGINE=MYISAM DEFAULT CHARSET=utf8;
ERROR 1050 (42S01): Table 'test' already exists
mysql> drop table test;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> show variables like '%version%';
+-------------------------+---------------------------------------------------------+
| Variable_name | Value |
+-------------------------+---------------------------------------------------------+
| innodb_version | 5.7.6 |
| protocol_version | 10 |
| slave_type_conversions | |
| version | 5.7.6-m16-enterprise-commercial-advanced |
| version_comment | MySQL Enterprise Server - Advanced Edition (Commercial) |
| version_compile_machine | x86_64 |
| version_compile_os | linux-glibc2.5 |
+-------------------------+---------------------------------------------------------+
7 rows in set (0.00 sec)
mysql> show variables like '%sql_mode%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_mode | |
+---------------+-------+
1 row in set (0.00 sec)
mysql> CREATE TABLE `test` (
-> `a` MEDIUMTEXT,
-> `b` MEDIUMTEXT
-> ) ENGINE=MYISAM DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE VIEW `view_test` AS
-> (SELECT * FROM test);
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> SHOW CREATE VIEW `view_test`\G
*************************** 1. row ***************************
View: view_test
Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_test` AS (select `test`.`a` AS `a`,`test`.`b` AS `b` from `test`)
character_set_client: utf8
collation_connection: utf8_general_ci
1 row in set (0.00 sec)
mysql> SELECT `TABLE_NAME`
-> ,`TABLE_NAME`
-> ,`VIEW_DEFINITION`
-> FROM `information_schema`.`VIEWS` IS_V
-> WHERE IS_V.`TABLE_SCHEMA`='test' AND IS_V.table_name = 'view_test'
-> \G
*************************** 1. row ***************************
TABLE_NAME: view_test
TABLE_NAME: view_test
VIEW_DEFINITION: (select `test`.`test`.`a` AS `a`,`test`.`test`.`b` AS `b` from `test`.`test`)
1 row in set (0.00 sec)
