Description:
In-place upgrade of views that select performance_schema tables are not imported properly from 5.7 to 8.0.
How to repeat:
1. Create view in 5.7.37:
use test;
CREATE VIEW v1 AS SELECT gs1.* FROM performance_schema.global_status gs1 JOIN performance_schema.global_status gs2 ON gs1.VARIABLE_NAME = gs2.VARIABLE_NAME;
2. Use 5.7.37 datadir in 8.0.28 and start it up. The error log will show issues with the in-place upgrade.
2022-03-24T12:36:10.694891Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2022-03-24T12:36:12.177698Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2022-03-24T12:36:16.387553Z 2 [Warning] [MY-010200] [Server] Resolving dependency for the view 'test.v1' failed. View is no more valid to use
3. It may seem that the view is working:
mysql> SELECT * FROM test.v1 LIMIT 3;
+-----------------------+----------------+
| VARIABLE_NAME | VARIABLE_VALUE |
+-----------------------+----------------+
| Aborted_clients | 0 |
| Aborted_connects | 0 |
| Acl_cache_items_count | 0 |
+-----------------------+----------------+
3 rows in set (0.00 sec)
~/opt/mysql/8.0.28/bin/mysql --port=8028 -e 'SHOW CREATE VIEW test.v1'
+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| View | Create View | character_set_client | collation_connection |
+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| v1 | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `test`.`v1` AS select `gs1`.`VARIABLE_NAME` AS `VARIABLE_NAME`,`gs1`.`VARIABLE_VALUE` AS `VARIABLE_VALUE` from (`performance_schema`.`global_status` `gs1` join `performance_schema`.`global_status` `gs2` on((`gs1`.`VARIABLE_NAME` = `gs2`.`VARIABLE_NAME`))) | utf8 | utf8_general_ci |
+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
4. Try a mysqldump and you will get this error:
~/opt/mysql/8.0.28/bin/mysqldump --port=8028 test
-- MySQL dump 10.13 Distrib 8.0.28, for Linux (x86_64)
--
-- Host: 127.0.0.1 Database: test
-- ------------------------------------------------------
-- Server version 8.0.28
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!50503 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Temporary view structure for view `v1`
--
DROP TABLE IF EXISTS `v1`;
mysqldump: Couldn't execute 'SHOW FIELDS FROM `v1`': View 'test.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them (1356)
5. If you drop and recreate the view in 8.0.28, the issue is gone:
mysql> DROP VIEW v1;
Query OK, 0 rows affected (0.02 sec)
mysql> CREATE VIEW v1 AS SELECT gs1.* FROM performance_schema.global_status gs1 JOIN performance_schema.global_status gs2 ON gs1.VARIABLE_NAME = gs2.VARIABLE_NAME;
Query OK, 0 rows affected (0.02 sec)
mysql> SHOW FIELDS FROM test.v1;
+----------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+---------------+------+-----+---------+-------+
| VARIABLE_NAME | varchar(64) | NO | | NULL | |
| VARIABLE_VALUE | varchar(1024) | YES | | NULL | |
+----------------+---------------+------+-----+---------+-------+
2 rows in set (0.01 sec)
Something went wrong during the in-place upgrade of this view.
