MySQL Bugs: #108005: MySQL Shell does not prevent about orphaned routines

Bug #108005	MySQL Shell does not prevent about orphaned routines
Submitted:	27 Jul 2022 23:26	Modified:	28 Nov 2022 11:04
Reporter:	Juan Arruti	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Shell Upgrade Checker	Severity:	S3 (Non-critical)
Version:	8.0.30	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
MySQL Upgrade Utility does not check for orphaned routines, which makes the upgrade procedure fail.

How to repeat:
# Create database MySQL 5.7.39
$ dbdeployer deploy single /opt/mysql/5.7.39 --port=8996 --force

# Create database db1
mysql [localhost:8996] {msandbox} ((none)) > create database db1 ; 
Query OK, 1 row affected (0.00 sec)

# Create routine example
mysql [localhost:8996] {msandbox} ((none)) > delimiter //
mysql [localhost:8996] {msandbox} ((none)) > CREATE PROCEDURE db1.example ()
    -> BEGIN
    -> SELECT COUNT(*) FROM db1.t1 ;
    -> END//
Query OK, 0 rows affected (0.00 sec)
mysql [localhost:8996] {msandbox} ((none)) > delimiter ;

# Dump the proc table
$ mysqldump --defaults-file=./my.sandbox.cnf mysql proc > proc.sql

# Drop the schema db1
$ mysql [localhost:8996] {msandbox} ((none)) > drop database db1 ;
Query OK, 0 rows affected (0.00 sec)

# Import the proc table
$ ./use mysql < proc.sql

# Run MySQL Upgrade Utility Checker - It does not report any fatal issue
$ ./mysqlsh -- util checkForServerUpgrade root@127.0.0.1:8996 --target-version=8.0.30 --config-path=/home/juan.arruti/sandboxes/msb_5_7_39/my.sandbox.cnf
...
The MySQL server at 127.0.0.1:8996, version 5.7.39 - MySQL Community Server
(GPL), will now be checked for compatibility issues for upgrade to MySQL
8.0.30...
...
Errors:   0
Warnings: 27
Notices:  2

NOTE: No fatal errors were found that would prevent an upgrade, but some potential issues were detected. Please ensure that the reported issues are not significant before upgrading.

# When upgrading to 8.0.30 it fails due to orphaned routines
2022-07-27T23:19:01.964907Z 2 [ERROR] [MY-013669] [Server] The schema "db1" referenced by routine "example" does not exist. Please clean up any orphan routines before upgrading.
2022-07-27T23:19:01.964943Z 2 [ERROR] [MY-010768] [Server] Error in creating stored program 'db1.example'
2022-07-27T23:19:02.409613Z 0 [ERROR] [MY-010022] [Server] Failed to Populate DD tables.
2022-07-27T23:19:02.409649Z 0 [ERROR] [MY-010119] [Server] Aborting

Suggested fix:
A simple query like the one below should show orphaned routines:

mysql [localhost:8996] {msandbox} ((none)) > select db, name from mysql.proc where not exists (select * from mysql.db where mysql.proc.db = mysql.db.db);
+-----+---------+
| db  | name    |
+-----+---------+
| db1 | example |
+-----+---------+
1 row in set (0.00 sec)

Hello Juan Arruti,

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

regards,
Umesh

I suppose this is pretty same issue and should be in this topic:

Bug is reproduced with orphaned views (but in case of view table should be dropped, because view belongs to a database):

CREATE TABLE t2 (a INT);
CREATE VIEW v2 AS SELECT * FROM t2;
DROP TABLE t2;

Checker doesn't show any related errors, and in-place upgrade log we see:

[Warning] [MY-010200] [Server] Resolving dependency for the view 'test.v1' failed. View is no more valid to use

Versions are almost the same as in original post, but for clarity:

mysqlsh   Ver 8.0.29 for Linux on x86_64 - for MySQL 8.0.29-21
mysqld  Ver 5.7.38-41 for Linux on x86_64
mysqld  Ver 8.0.28-20-debug for Linux on x86_64

Posted by developer:
 
Added the following note to the MySQL Shell 8.0.32 release notes:

MySQL Shell Upgrade Checker utility did not check for orphaned stored
        routines. That is, stored routines which reference non-existent
        schemas. As a result, the upgrade procedure could fail.

        As of this release, the Upgrade Checker utility checks for such
        orphaned routines and returns an error if they are found.