Bug #54766 | Impossible to use view after creation | ||
---|---|---|---|
Submitted: | 24 Jun 2010 10:33 | Modified: | 24 Jun 2010 10:48 |
Reporter: | Sergei Glukhov | Email Updates: | |
Status: | Verified | Impact on me: | |
Category: | MySQL Server: Views | Severity: | S3 (Non-critical) |
Version: | 5.0.92, 5.1.49, 5.5.5-m3 | OS: | Any |
Assigned to: | CPU Architecture: | Any |
[24 Jun 2010 10:33]
Sergei Glukhov
[24 Jun 2010 10:48]
Valeriy Kravchuk
Verified just as described with current 5.1.49 from bzr: valeriy-kravchuks-macbook-pro:5.1 openxs$ bin/mysql -uroot test Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.1.49-debug Source distribution Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved. This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL v2 license Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> CREATE TABLE t1 (a VARCHAR(10), FULLTEXT KEY a (a)); Query OK, 0 rows affected (0.04 sec) mysql> INSERT INTO t1 VALUES (1),(2); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> CREATE TABLE t2 (b INT); Query OK, 0 rows affected (0.08 sec) mysql> INSERT INTO t2 VALUES (1),(2); Query OK, 2 rows affected (0.01 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> CREATE VIEW v1 AS SELECT * FROM t2 UNION SELECT * FROM t2 -> ORDER BY (SELECT * FROM t1 WHERE MATCH `a` AGAINST ('+abc' IN BOOLEAN MODE)); Query OK, 0 rows affected (0.07 sec) mysql> SHOW CREATE VIEW v1\G *************************** 1. row *************************** View: v1 Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t2`.`b` AS `b` from `t2` union select `t2`.`b` AS `b` from `t2` order by (select `*` from `t1` where (match `a` against ('+abc' in boolean mode))) character_set_client: latin1 collation_connection: latin1_swedish_ci 1 row in set (0.00 sec) mysql> SELECT * FROM v1; ERROR 1356 (HY000): View 'test.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
[24 Jun 2010 10:50]
Valeriy Kravchuk
5.0 is also affected, although view is created a bit differently: mysql> SHOW CREATE VIEW v1\G *************************** 1. row *************************** View: v1 Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t2`.`b` AS `b` from `t2` union select `t2`.`b` AS `b` from `t2` order by (select `*` AS `*` from `t1` where (match `a` against (_latin1'+abc' in boolean mode))) 1 row in set (0.00 sec) mysql> SELECT * FROM v1;ERROR 1356 (HY000): View 'test.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them mysql> select version(); +--------------+ | version() | +--------------+ | 5.0.92-debug | +--------------+ 1 row in set (0.00 sec)
[24 Jun 2010 10:52]
Valeriy Kravchuk
Same with current mysql-trunk: mysql> SHOW CREATE VIEW v1\G *************************** 1. row *************************** View: v1 Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t2`.`b` AS `b` from `t2` union select `t2`.`b` AS `b` from `t2` order by (select `*` from `t1` where (match `a` against ('+abc' in boolean mode))) character_set_client: utf8 collation_connection: utf8_general_ci 1 row in set (0.00 sec) mysql> SELECT * FROM v1; ERROR 1356 (HY000): View 'test.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them mysql> select version(); +----------------+ | version() | +----------------+ | 5.5.5-m3-debug | +----------------+ 1 row in set (0.00 sec)
[4 Jul 2018 9:58]
Hartmut Holzgraefe
Still present in 5.6, fixed in 5.7, view will now be created as: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t2`.`b` AS `b` from `t2` union select `t2`.`b` AS `b` from `t2` order by (select `t1`.`a` from `t1` where (match `t1`.`a` against ('+abc' in boolean mode))) listing the columns from table t1 explicitly instead of `*`