Bug #107987 DROP FUNCTION ERROR 1109 (442S02) if function references INNODB_SYS tables
Submitted: 26 Jul 2022 19:39 Modified: 27 Jul 2022 12:20
Reporter: Aaron Boudreaux Email Updates:
Status: Can't repeat Impact on me:
Category:MySQL Server: Stored Routines Severity:S3 (Non-critical)
Version:8.0.27 OS:Any
Assigned to: CPU Architecture:Any
Tags: drop function, information schema, information_schema.INNODB_SYS_FOREIGN_COLS

[26 Jul 2022 19:39] Aaron Boudreaux
We created functions in mysql 5.7 that query the INFORMATION_SCHEMA INNODB_SYS_FOREIGN_COLS table. For example `return (select FOR_COL_NAME from INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS)`. In mysql 8 when we attempt to drop the function
with `DROP FUNCTION function_name;` an error is thrown with message "ERROR 1109 (42S02): Unknown table 'INNODB_SYS_FOREIGN_COLS' in information_schema". 
The function appears to be dropped but the sql script immediately exits with an error so no further queries are run.

Removing a function should not result in an error due to non existent tables the function may be referencing.

How to repeat:
In mysql 5.7 create a function that queries one of the INFORMATION_SCHEMA.INNODB_SYS* tables that were replaced with views in mysql 8 like INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS. Upgrade the database to mysql 8. Make a `DROP FUNCTION function_name` query to remove the function.
[27 Jul 2022 12:20] MySQL Verification Team
Hi Mr. Boudreaux,

Thank you for your bug report.

However, we need a test case that will include upgrade from the latest 5.7 release to the latest 8.0 release, which is 8.0.30.

Not enough information was provided for us to be able to handle this bug. Please re-read the instructions at http://bugs.mysql.com/how-to-report.php

We also need a set of SQL statements that lead to the error. It can include the exact steps that you have undertaken in your upgrade process from the latest 5.7 to the latest 8.0 release.

If you can provide more information, feel free to add it to this bug and change the status back to 'Open'.  

Thank you for your interest in MySQL.

We are waiting for your full feedback.