[FIXED] I can't see or edit MySQL stored procedures in NetBeans

Issue

I can connect to MySQL database (5.7.23) from NetBeans (8.2) using JDBC (MySQL-AB JDBC Driver, mysql-connector-java-5.1.23). I can see tables and views, execute SQL statements and everything else with database but I cannot see or edit stored procedures (or functions) in NetBeans. I have them inside database, I can see and manage them in MySQL Workbench but cannot see them in NetBeans (folder "Procedures" in NetBeans is empty). What could be the problem?

Left in NetBeans, right in Workbench:

I have 64-bit Windows 10 Pro (1709), 64-bit NetBeans 8.2 (PHP) and 64-bit MySQL 5.7.14 (from WAMP).

Solution

There are possibly two reasons:

1. User doesn't have access to mysql.proc

NetBeans uses something like that to get the list of procedures and functions (see the source code):

SELECT 
    db, name, type, param_list, returns, body
FROM
    mysql.proc
WHERE
    TYPE = 'PROCEDURE' OR TYPE = 'FUNCTION'; 

Try to execute the query in NetBeans and see the result.

See the screenshot. On the first one, the user root has access to the mysql schema and you can see all system schemas including the mysql. On the second one, the user has access only to the information_schema and performance_schema and can not see procedures and functions:

Try to add to your user access to the MySQL schema. In workbench go to Server -> Users and Privileges -> Schema Privileges:

2. There is no mysql.proc in 8.0 mysql

Again, NetBeans uses mysql.pros extensively to get metadata about procedures and funcstions (see in source code). I suppose a simple workaround we can get is to create a view that would mimic the behavior of original mysql.proc:

CREATE VIEW mysql.proc AS
    SELECT 
        ROUTINE_SCHEMA AS db,
        ROUTINE_NAME AS name,
        ROUTINE_TYPE AS type,
        ROUTINE_DEFINITION AS body,
        CONCAT(DTD_IDENTIFIER,
                ' CHARSET ',
                CHARACTER_SET_NAME) AS returns,
        (SELECT 
                GROUP_CONCAT(CONCAT(parameter_name, ' ', dtd_identifier))
            FROM
                information_schema.parameters p
            WHERE
                p.specific_name = outertable.routine_name
                    AND ordinal_position > 0) AS param_list
    FROM
        information_schema.routines outertable

After creating a view we can view procedures and functions from NetBeans 11.2 and view their bodies and param list. Also, I would not recommend editing procedures using NetBeans because it can't get full metadata. For example, it could not get declaration options such as DETERMINISTIC SQL SECURITY INVOKER.

Answered By - Dmitry.M