We're updating the issue view to help you get more done. 

No ALGORITHM information in I_S.VIEWS

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:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 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

Environment

None

Status

Assignee

Sergei Golubchik

Reporter

Hartmut Holzgraefe

Labels

External issue ID

None

External issue ID

None

Components

Fix versions

Priority

Major