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

Components

Fix versions

Priority

Major