| Bug #32842 | If ALTER VIEW spans more rows VIEW_DEFINITION is incorrect updated. | ||
|---|---|---|---|
| Submitted: | 29 Nov 2007 12:35 | Modified: | 4 Jan 2008 13:07 |
| Reporter: | Santo Leto | Email Updates: | |
| Status: | Duplicate | Impact on me: | |
| Category: | MySQL Server: Views | Severity: | S2 (Serious) |
| Version: | 5.1.22-rc, 6.0.3 | OS: | Windows (XPSP2) |
| Assigned to: | Assigned Account | CPU Architecture: | Any |
| Tags: | ALTER VIEW, VIEW_DEFINITION | ||
[29 Nov 2007 12:41]
Santo Leto
Output 1 <-- Test Script 1 - ALL WORKS FINE
mysql> SELECT CURRENT_USER(), VERSION();
+----------------+------------------------------+
| CURRENT_USER() | VERSION() |
+----------------+------------------------------+
| root@localhost | 5.0.50-enterprise-gpl-nt-log |
+----------------+------------------------------+
1 row in set (0.00 sec)
mysql> DROP DATABASE IF EXISTS `view-bug`;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> CREATE DATABASE `view-bug`;
Query OK, 1 row affected (0.02 sec)
mysql> USE `view-bug`;
Database changed
mysql> DROP VIEW IF EXISTS `mysql-test-bug`;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> CREATE ALGORITHM = UNDEFINED DEFINER = 'root'@'localhost' SQL SECURITY DE
FINER VIEW `mysql-test-bug` AS SELECT * FROM mysql.user;
Query OK, 0 rows affected (0.01 sec)
mysql> SELECT * FROM `information_schema`.`VIEWS` WHERE `table_schema`='view-bug
' AND `table_name`='mysql-test-bug'\G
*************************** 1. row ***************************
TABLE_CATALOG: NULL
TABLE_SCHEMA: view-bug
TABLE_NAME: mysql-test-bug
VIEW_DEFINITION: /* ALGORITHM=UNDEFINED */ select `mysql`.`user`.`Host` AS `Host
`,`mysql`.`user`.`User` AS `User`,`mysql`.`user`.`Password` AS `Password`,`mysql
`.`user`.`Select_priv` AS `Select_priv`,`mysql`.`user`.`Insert_priv` AS `Insert_
priv`,`mysql`.`user`.`Update_priv` AS `Update_priv`,`mysql`.`user`.`Delete_priv`
AS `Delete_priv`,`mysql`.`user`.`Create_priv` AS `Create_priv`,`mysql`.`user`.`
Drop_priv` AS `Drop_priv`,`mysql`.`user`.`Reload_priv` AS `Reload_priv`,`mysql`.
`user`.`Shutdown_priv` AS `Shutdown_priv`,`mysql`.`user`.`Process_priv` AS `Proc
ess_priv`,`mysql`.`user`.`File_priv` AS `File_priv`,`mysql`.`user`.`Grant_priv`
AS `Grant_priv`,`mysql`.`user`.`References_priv` AS `References_priv`,`mysql`.`u
ser`.`Index_priv` AS `Index_priv`,`mysql`.`user`.`Alter_priv` AS `Alter_priv`,`m
ysql`.`user`.`Show_db_priv` AS `Show_db_priv`,`mysql`.`user`.`Super_priv` AS `Su
per_priv`,`mysql`.`user`.`Create_tmp_table_priv` AS `Create_tmp_table_priv`,`mys
ql`.`user`.`Lock_tables_priv` AS `Lock_tables_priv`,`mysql`.`user`.`Execute_priv
` AS `Execute_priv`,`mysql`.`user`.`Repl_slave_priv` AS `Repl_slave_priv`,`mysql
`.`user`.`Repl_client_priv` AS `Repl_client_priv`,`mysql`.`user`.`Create_view_pr
iv` AS `Create_view_priv`,`mysql`.`user`.`Show_view_priv` AS `Show_view_priv`,`m
ysql`.`user`.`Create_routine_priv` AS `Create_routine_priv`,`mysql`.`user`.`Alte
r_routine_priv` AS `Alter_routine_priv`,`mysql`.`user`.`Create_user_priv` AS `Cr
eate_user_priv`,`mysql`.`user`.`ssl_type` AS `ssl_type`,`mysql`.`user`.`ssl_ciph
er` AS `ssl_cipher`,`mysql`.`user`.`x509_issuer` AS `x509_issuer`,`mysql`.`user`
.`x509_subject` AS `x509_subject`,`mysql`.`user`.`max_questions` AS `max_questio
ns`,`mysql`.`user`.`max_updates` AS `max_updates`,`mysql`.`user`.`max_connection
s` AS `max_connections`,`mysql`.`user`.`max_user_connections` AS `max_user_conne
ctions` from `mysql`.`user`
CHECK_OPTION: NONE
IS_UPDATABLE: YES
DEFINER: root@localhost
SECURITY_TYPE: DEFINER
1 row in set (0.02 sec)
mysql> ALTER ALGORITHM = UNDEFINED DEFINER = 'root'@'localhost' SQL SECURITY DEF
INER VIEW `mysql-test-bug` AS Select * From `mysql`.`db`;
Query OK, 0 rows affected (0.03 sec)
mysql> SELECT * FROM `information_schema`.`VIEWS` WHERE `table_schema`='view-bug
'\G
*************************** 1. row ***************************
TABLE_CATALOG: NULL
TABLE_SCHEMA: view-bug
TABLE_NAME: mysql-test-bug
VIEW_DEFINITION: /* ALGORITHM=UNDEFINED */ select `mysql`.`db`.`Host` AS `Host`,
`mysql`.`db`.`Db` AS `Db`,`mysql`.`db`.`User` AS `User`,`mysql`.`db`.`Select_pri
v` AS `Select_priv`,`mysql`.`db`.`Insert_priv` AS `Insert_priv`,`mysql`.`db`.`Up
date_priv` AS `Update_priv`,`mysql`.`db`.`Delete_priv` AS `Delete_priv`,`mysql`.
`db`.`Create_priv` AS `Create_priv`,`mysql`.`db`.`Drop_priv` AS `Drop_priv`,`mys
ql`.`db`.`Grant_priv` AS `Grant_priv`,`mysql`.`db`.`References_priv` AS `Referen
ces_priv`,`mysql`.`db`.`Index_priv` AS `Index_priv`,`mysql`.`db`.`Alter_priv` AS
`Alter_priv`,`mysql`.`db`.`Create_tmp_table_priv` AS `Create_tmp_table_priv`,`m
ysql`.`db`.`Lock_tables_priv` AS `Lock_tables_priv`,`mysql`.`db`.`Create_view_pr
iv` AS `Create_view_priv`,`mysql`.`db`.`Show_view_priv` AS `Show_view_priv`,`mys
ql`.`db`.`Create_routine_priv` AS `Create_routine_priv`,`mysql`.`db`.`Alter_rout
ine_priv` AS `Alter_routine_priv`,`mysql`.`db`.`Execute_priv` AS `Execute_priv`
from `mysql`.`db`
CHECK_OPTION: NONE
IS_UPDATABLE: YES
DEFINER: root@localhost
SECURITY_TYPE: DEFINER
1 row in set (0.02 sec)
[29 Nov 2007 12:43]
Santo Leto
Output 2 <-- Test Script 2 - ALL WORKS FINE
mysql> SELECT CURRENT_USER(), VERSION();
+----------------+------------------------------+
| CURRENT_USER() | VERSION() |
+----------------+------------------------------+
| root@localhost | 5.0.50-enterprise-gpl-nt-log |
+----------------+------------------------------+
1 row in set (0.00 sec)
mysql> DROP DATABASE IF EXISTS `view-bug`;
Query OK, 1 row affected (0.02 sec)
mysql> CREATE DATABASE `view-bug`;
Query OK, 1 row affected (0.00 sec)
mysql> USE `view-bug`;
Database changed
mysql> DROP VIEW IF EXISTS `mysql-test-bug`;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> CREATE ALGORITHM = UNDEFINED DEFINER = 'root'@'localhost' SQL SECURITY DE
FINER VIEW `mysql-test-bug` AS SELECT * FROM mysql.user;
Query OK, 0 rows affected (0.02 sec)
mysql> SELECT * FROM `information_schema`.`VIEWS` WHERE `table_schema`='view-bug
' AND `table_name`='mysql-test-bug'\G
*************************** 1. row ***************************
TABLE_CATALOG: NULL
TABLE_SCHEMA: view-bug
TABLE_NAME: mysql-test-bug
VIEW_DEFINITION: /* ALGORITHM=UNDEFINED */ select `mysql`.`user`.`Host` AS `Host
`,`mysql`.`user`.`User` AS `User`,`mysql`.`user`.`Password` AS `Password`,`mysql
`.`user`.`Select_priv` AS `Select_priv`,`mysql`.`user`.`Insert_priv` AS `Insert_
priv`,`mysql`.`user`.`Update_priv` AS `Update_priv`,`mysql`.`user`.`Delete_priv`
AS `Delete_priv`,`mysql`.`user`.`Create_priv` AS `Create_priv`,`mysql`.`user`.`
Drop_priv` AS `Drop_priv`,`mysql`.`user`.`Reload_priv` AS `Reload_priv`,`mysql`.
`user`.`Shutdown_priv` AS `Shutdown_priv`,`mysql`.`user`.`Process_priv` AS `Proc
ess_priv`,`mysql`.`user`.`File_priv` AS `File_priv`,`mysql`.`user`.`Grant_priv`
AS `Grant_priv`,`mysql`.`user`.`References_priv` AS `References_priv`,`mysql`.`u
ser`.`Index_priv` AS `Index_priv`,`mysql`.`user`.`Alter_priv` AS `Alter_priv`,`m
ysql`.`user`.`Show_db_priv` AS `Show_db_priv`,`mysql`.`user`.`Super_priv` AS `Su
per_priv`,`mysql`.`user`.`Create_tmp_table_priv` AS `Create_tmp_table_priv`,`mys
ql`.`user`.`Lock_tables_priv` AS `Lock_tables_priv`,`mysql`.`user`.`Execute_priv
` AS `Execute_priv`,`mysql`.`user`.`Repl_slave_priv` AS `Repl_slave_priv`,`mysql
`.`user`.`Repl_client_priv` AS `Repl_client_priv`,`mysql`.`user`.`Create_view_pr
iv` AS `Create_view_priv`,`mysql`.`user`.`Show_view_priv` AS `Show_view_priv`,`m
ysql`.`user`.`Create_routine_priv` AS `Create_routine_priv`,`mysql`.`user`.`Alte
r_routine_priv` AS `Alter_routine_priv`,`mysql`.`user`.`Create_user_priv` AS `Cr
eate_user_priv`,`mysql`.`user`.`ssl_type` AS `ssl_type`,`mysql`.`user`.`ssl_ciph
er` AS `ssl_cipher`,`mysql`.`user`.`x509_issuer` AS `x509_issuer`,`mysql`.`user`
.`x509_subject` AS `x509_subject`,`mysql`.`user`.`max_questions` AS `max_questio
ns`,`mysql`.`user`.`max_updates` AS `max_updates`,`mysql`.`user`.`max_connection
s` AS `max_connections`,`mysql`.`user`.`max_user_connections` AS `max_user_conne
ctions` from `mysql`.`user`
CHECK_OPTION: NONE
IS_UPDATABLE: YES
DEFINER: root@localhost
SECURITY_TYPE: DEFINER
1 row in set (0.01 sec)
mysql> ALTER ALGORITHM = UNDEFINED DEFINER = 'root'@'localhost' SQL SECURITY DEF
INER VIEW `mysql-test-bug` AS Select *
-> From `mysql`.`db`;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM `information_schema`.`VIEWS` WHERE `table_schema`='view-bug
'\G
*************************** 1. row ***************************
TABLE_CATALOG: NULL
TABLE_SCHEMA: view-bug
TABLE_NAME: mysql-test-bug
VIEW_DEFINITION: /* ALGORITHM=UNDEFINED */ select `mysql`.`db`.`Host` AS `Host`,
`mysql`.`db`.`Db` AS `Db`,`mysql`.`db`.`User` AS `User`,`mysql`.`db`.`Select_pri
v` AS `Select_priv`,`mysql`.`db`.`Insert_priv` AS `Insert_priv`,`mysql`.`db`.`Up
date_priv` AS `Update_priv`,`mysql`.`db`.`Delete_priv` AS `Delete_priv`,`mysql`.
`db`.`Create_priv` AS `Create_priv`,`mysql`.`db`.`Drop_priv` AS `Drop_priv`,`mys
ql`.`db`.`Grant_priv` AS `Grant_priv`,`mysql`.`db`.`References_priv` AS `Referen
ces_priv`,`mysql`.`db`.`Index_priv` AS `Index_priv`,`mysql`.`db`.`Alter_priv` AS
`Alter_priv`,`mysql`.`db`.`Create_tmp_table_priv` AS `Create_tmp_table_priv`,`m
ysql`.`db`.`Lock_tables_priv` AS `Lock_tables_priv`,`mysql`.`db`.`Create_view_pr
iv` AS `Create_view_priv`,`mysql`.`db`.`Show_view_priv` AS `Show_view_priv`,`mys
ql`.`db`.`Create_routine_priv` AS `Create_routine_priv`,`mysql`.`db`.`Alter_rout
ine_priv` AS `Alter_routine_priv`,`mysql`.`db`.`Execute_priv` AS `Execute_priv`
from `mysql`.`db`
CHECK_OPTION: NONE
IS_UPDATABLE: YES
DEFINER: root@localhost
SECURITY_TYPE: DEFINER
1 row in set (0.00 sec)
[29 Nov 2007 17:50]
Valeriy Kravchuk
Thank you for a bug report. Verified just as described.
[4 Jan 2008 13:07]
Martin Hansson
This is a duplicate of Bug#30689, apparently the fix did not make it into 5.1.22-rc.
Description: If ALTER VIEW spans more rows VIEW_DEFINITION is incorrect updated. How to repeat: Test Script 1 (ALTER VIEW on a single row): ALL WORKS FINE SELECT CURRENT_USER(), VERSION(); DROP DATABASE IF EXISTS `view-bug`; CREATE DATABASE `view-bug`; USE `view-bug`; DROP VIEW IF EXISTS `mysql-test-bug`; CREATE ALGORITHM = UNDEFINED DEFINER = 'root'@'localhost' SQL SECURITY DEFINER VIEW `mysql-test-bug` AS SELECT * FROM mysql.user; SELECT * FROM `information_schema`.`VIEWS` WHERE `table_schema`='view-bug' AND `table_name`='mysql-test-bug'\G ALTER ALGORITHM = UNDEFINED DEFINER = 'root'@'localhost' SQL SECURITY DEFINER VIEW `mysql-test-bug` AS Select * From `mysql`.`db`; SELECT * FROM `information_schema`.`VIEWS` WHERE `table_schema`='view-bug'\G Test Script 2 (ALTER VIEW on two rows): POSSIBLE BUG SELECT CURRENT_USER(), VERSION(); DROP DATABASE IF EXISTS `view-bug`; CREATE DATABASE `view-bug`; USE `view-bug`; DROP VIEW IF EXISTS `mysql-test-bug`; CREATE ALGORITHM = UNDEFINED DEFINER = 'root'@'localhost' SQL SECURITY DEFINER VIEW `mysql-test-bug` AS SELECT * FROM mysql.user; SELECT * FROM `information_schema`.`VIEWS` WHERE `table_schema`='view-bug' AND `table_name`='mysql-test-bug'\G ALTER ALGORITHM = UNDEFINED DEFINER = 'root'@'localhost' SQL SECURITY DEFINER VIEW `mysql-test-bug` AS Select * From `mysql`.`db`; SELECT * FROM `information_schema`.`VIEWS` WHERE `table_schema`='view-bug'\G Output 1 <-- Test Script 1 - ALL WORKS FINE mysql> SELECT CURRENT_USER(), VERSION(); +----------------+-------------------------+ | CURRENT_USER() | VERSION() | +----------------+-------------------------+ | root@localhost | 5.1.22-rc-community-log | +----------------+-------------------------+ 1 row in set (0.00 sec) mysql> DROP DATABASE IF EXISTS `view-bug`; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> CREATE DATABASE `view-bug`; Query OK, 1 row affected (0.03 sec) mysql> USE `view-bug`; Database changed mysql> DROP VIEW IF EXISTS `mysql-test-bug`; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> CREATE ALGORITHM = UNDEFINED DEFINER = 'root'@'localhost' SQL SECURITY DE FINER VIEW `mysql-test-bug` AS SELECT * FROM mysql.user; Query OK, 0 rows affected (0.00 sec) mysql> SELECT * FROM `information_schema`.`VIEWS` WHERE `table_schema`='view-bug ' AND `table_name`='mysql-test-bug'\G *************************** 1. row *************************** TABLE_CATALOG: NULL TABLE_SCHEMA: view-bug TABLE_NAME: mysql-test-bug VIEW_DEFINITION: SELECT * FROM mysql.user CHECK_OPTION: NONE IS_UPDATABLE: YES DEFINER: root@localhost SECURITY_TYPE: DEFINER CHARACTER_SET_CLIENT: latin1 COLLATION_CONNECTION: latin1_swedish_ci 1 row in set (0.02 sec) mysql> ALTER ALGORITHM = UNDEFINED DEFINER = 'root'@'localhost' SQL SECURITY DEF INER VIEW `mysql-test-bug` AS Select * From `mysql`.`db`; Query OK, 0 rows affected (0.01 sec) mysql> SELECT * FROM `information_schema`.`VIEWS` WHERE `table_schema`='view-bug '\G *************************** 1. row *************************** TABLE_CATALOG: NULL TABLE_SCHEMA: view-bug TABLE_NAME: mysql-test-bug VIEW_DEFINITION: Select * From `mysql`.`db` CHECK_OPTION: NONE IS_UPDATABLE: YES DEFINER: root@localhost SECURITY_TYPE: DEFINER CHARACTER_SET_CLIENT: latin1 COLLATION_CONNECTION: latin1_swedish_ci 1 row in set (0.02 sec) Output 2 <-- Test Script 2 - POSSIBLE BUG mysql> SELECT CURRENT_USER(), VERSION(); +----------------+-------------------------+ | CURRENT_USER() | VERSION() | +----------------+-------------------------+ | root@localhost | 5.1.22-rc-community-log | +----------------+-------------------------+ 1 row in set (0.00 sec) mysql> DROP DATABASE IF EXISTS `view-bug`; Query OK, 1 row affected (0.02 sec) mysql> CREATE DATABASE `view-bug`; Query OK, 1 row affected (0.05 sec) mysql> USE `view-bug`; Database changed mysql> DROP VIEW IF EXISTS `mysql-test-bug`; Query OK, 0 rows affected, 1 warning (0.01 sec) mysql> CREATE ALGORITHM = UNDEFINED DEFINER = 'root'@'localhost' SQL SECURITY DE FINER VIEW `mysql-test-bug` AS SELECT * FROM mysql.user; Query OK, 0 rows affected (0.03 sec) mysql> SELECT * FROM `information_schema`.`VIEWS` WHERE `table_schema`='view-bug ' AND `table_name`='mysql-test-bug'\G *************************** 1. row *************************** TABLE_CATALOG: NULL TABLE_SCHEMA: view-bug TABLE_NAME: mysql-test-bug VIEW_DEFINITION: SELECT * FROM mysql.user CHECK_OPTION: NONE IS_UPDATABLE: YES DEFINER: root@localhost SECURITY_TYPE: DEFINER CHARACTER_SET_CLIENT: latin1 COLLATION_CONNECTION: latin1_swedish_ci 1 row in set (0.03 sec) mysql> ALTER ALGORITHM = UNDEFINED DEFINER = 'root'@'localhost' SQL SECURITY DEF INER VIEW `mysql-test-bug` AS Select * -> From `mysql`.`db`; Query OK, 0 rows affected (0.06 sec) mysql> SELECT * FROM `information_schema`.`VIEWS` WHERE `table_schema`='view-bug '\G *************************** 1. row *************************** TABLE_CATALOG: NULL TABLE_SCHEMA: view-bug TABLE_NAME: mysql-test-bug VIEW_DEFINITION: Select * CHECK_OPTION: NONE IS_UPDATABLE: YES DEFINER: root@localhost SECURITY_TYPE: DEFINER CHARACTER_SET_CLIENT: latin1 COLLATION_CONNECTION: latin1_swedish_ci 1 row in set (0.02 sec)