I came across a problem on site yesterday. In moving the development environment to a new server and creating more appropriate permissions for users (they were using ALL on *.*) I found that the Java application would crash with a NullPointerException. The permissions were standard, and calling the Stored Procedure worked via the mysql prompt.
CREATE USER devuser@99.99.99.99; GRANT SELECT,INSERT,UPDATE,DELETE,EXECUTE ON devdb.* to devuser@99.99.99.99; CALL sp_test()
You can spend a lot of time looking into problems, luckily this development configuration had taken my advice to enabled the General Query Log. (Something everybody should do to know your SQL).
In closer inspection the following command was being sent to the MySQL Server. SHOW CREATE PROCEDURE sp_test; Attempting to run this command via the mysql prompt works.
SHOW CREATE PROCEDURE sp_test; +-----------+----------+------------------+ | Procedure | sql_mode | Create Procedure | +-----------+----------+------------------+ | sp_test | | NULL | +-----------+----------+------------------+
On closer inspection, the Create Procedure content contains NULL, seems suspicious. Running this command as the ‘root’ MySQL user I get the content of the procedure.
RFTM sheds light that this command requires privileges to mysql.proc. Running the following was necessary to get operation.
GRANT SELECT ON mysql.proc to devuser@99.99.99.99;
This requirement is rather stupid in my book. I’ve yet to determine why SHOW CREATE PROCEDURE is called, probably some requirement via Connector/J but this permission problem definitely needs fixing.
