Details
-
Task
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
Description
See http://bugs.mysql.com/bug.php?id=73886
and older http://bugs.mysql.com/bug.php?id=16832
"ALGORITHM missing from "information_schema.views"
The 5.0 solution back then was not to add an ALGORITHM column as requested, but to add the ALGORITHM as comment to the I_S.VIEWS.VIEW_DEFINITION column as e.g.
/* ALGORITHM=MERGE */ select `test`.`t1`.`id` AS `id` from `test`.`t1`
I can verify that these comments are shown by MySQL 5.0.45, but not e.g. MySQL 5.1.67 or MySQL 5.6.20 ...
... and I also think that a new ALGORITHM column would have been the better solution as with the comment approach parsing of the VIEW_DEFINITION result would be necessary to extract the ALGORITHM instead of just saying
SELECT ALGORITHM FROM I_S.VIEWS WHERE ...
How to repeat:
mysql> drop table t1;
|
Query OK, 0 rows affected (0.00 sec)
|
|
|
mysql> create table t1(id int primary key);
|
Query OK, 0 rows affected (0.05 sec)
|
|
|
mysql> create algorithm=merge view v1 as select * from t1;
|
ERROR 1050 (42S01): Table 'v1' already exists
|
mysql> drop view v1;
|
Query OK, 0 rows affected (0.00 sec)
|
|
|
mysql> drop view v2;
|
ERROR 1051 (42S02): Unknown table 'v2'
|
mysql> create algorithm=merge view v1 as select * from t1;
|
Query OK, 0 rows affected (0.04 sec)
|
|
|
mysql> create algorithm=temptable view v2 as select * from t1;
|
Query OK, 0 rows affected (0.04 sec)
|
|
|
mysql> select * from information_schema.views
|
-> ;
|
+---------------+--------------+------------+--------------------------------------------------+--------------+--------------+----------------+---------------+----------------------+----------------------+
|
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | VIEW_DEFINITION | CHECK_OPTION | IS_UPDATABLE | DEFINER | SECURITY_TYPE | CHARACTER_SET_CLIENT | COLLATION_CONNECTION |
|
+---------------+--------------+------------+--------------------------------------------------+--------------+--------------+----------------+---------------+----------------------+----------------------+
|
| NULL | test | v1 | select `test`.`t1`.`id` AS `id` from `test`.`t1` | NONE | YES | root@localhost | DEFINER | utf8 | utf8_general_ci |
|
| NULL | test | v2 | select `test`.`t1`.`id` AS `id` from `test`.`t1` | NONE | NO | root@localhost | DEFINER | utf8 | utf8_general_ci |
|
+---------------+--------------+------------+--------------------------------------------------+--------------+--------------+----------------+---------------+----------------------+----------------------+
|
2 rows in set (0.01 sec)
|
Both views only differ by their name in the INFORMATION_SCHEMA.VIEWS table, the difference in the ALGORITHM used is invisible when relying on INFORMATION_SCHEMA unless using a 5.0.x version ... (with x>=25)
Suggested fix:
Add an ALGORITHM column to the VIEWS table as originally requested
Attachments
Issue Links
- relates to
-
MDEV-7406 get from INFORMATION_SCHEMA original SQL code creating VIEW
-
- Open
-