Bug #106819 In-place upgrade of views that select from performance_schema is broken
Submitted: 24 Mar 12:46 Modified: 25 Mar 13:17
Reporter: Jaime Sicam Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Data Dictionary Severity:S3 (Non-critical)
Version:8.0.28 OS:Any
Assigned to: CPU Architecture:Any

[24 Mar 12:46] Jaime Sicam
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.
[24 Mar 13:24] Jaime Sicam
Let me just add that the worst case is where in-place MySQL upgrade is aborted with an error so MySQL 8.0 fails to start. Unfortunately, I do not have a test case for this yet.
[25 Mar 13:17] MySQL Verification Team
Hello Jaime,

Thank you for the report and test case.
Verified as described.

regards,
Umesh