CA API Gateway - mysql.proc

Products

• CA API Gateway
• API SECURITY
• CA API Gateway Precision API Monitoring Module for API Gateway (Layer 7)
• CA API Gateway Enterprise Service Manager (Layer 7)
• STARTER PACK-7
• CA Microgateway

Issue/Introduction

For some reason we are not able to see mysql.proc table. Could you help check?

# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 282
Server version: 8.0.18-commercial MySQL Enterprise Server - CommercialCopyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> select name from mysql.proc;
ERROR 1146 (42S02): Table 'mysql.proc' doesn't exist
mysql>

Environment

Release : 10.0

Component : API GATEWAY

Cause

Update Gateway to 10 also requires upgrade mysql from 5.7 to 8.x for SSG database  - Store procedure are handled different in 8.x No more PROC table

Resolution

No more PROC table

https://dev.mysql.com/doc/refman/8.0/en/faqs-stored-procs.html

Stored procedures are stored in the mysql.routines and mysql.parameters tables, which are part of the data dictionary. You cannot access these tables directly. Instead, query the INFORMATION_SCHEMA ROUTINES and PARAMETERS tables. See Section 25.29, “The INFORMATION_SCHEMA ROUTINES Table”, and Section 25.19, “The INFORMATION_SCHEMA PARAMETERS Table”.

You can also use SHOW CREATE FUNCTION to obtain information about stored functions, and SHOW CREATE PROCEDURE to obtain information about stored procedures. See Section 13.7.7.9, “SHOW CREATE PROCEDURE Statement”.