You've created a procedure in MySQL 5. You've granted EXECUTE privileges to your application's user account. But when your application prepares a statement to call that procedure, an exception is thrown. Perhaps it's a NullPointerException deep inside of ConnectorJ. Perhaps the exception claims that the procedure does not exist. Or perhaps you've gotten lucky enough to get the exception that leads you down the right path (Driver requires declaration of procedure to either contain a ''\nbegin'' or ''\n'' to follow argument declaration, or SELECT privilege on mysql.proc to parse column types). Here are the troubleshooting steps that I've gleaned from hours of searching the MySQL forums.
First, open two console windows. In one, log into MySQL as the application user. In the other, log in as root. In your app window, list the databases.
If your application's database does not appear, then you should go to your root window and grant database privileges.
USE mydatabase; GRANT ALL ON mydatabase TO appuser;
Now go back to the app window. List the databases again to ensure that it appears. Now that it does, go into it.
Once you're in, try to view the procedure.
SHOW CREATE PROCEDURE myproc /G
One of three things will happen. If you get a message saying "PROCEDURE myproc does not exist" then the app user does not have privileges for it. Go back to the root window and grant them.
GRANT EXECUTE ON PROCEDURE myproc TO appuser;
The second thing that might happen is that the procedure will be found, but the body will not be listed. Instead, it will say "Create Procedure: NULL". If so, go back to the root window and take care of that.
GRANT SELECT ON mysql.proc TO appuser;
The third thing that could happen is what you want to happen; the body of the procedure will be listed. Now you should be able to call the procedure from your application.
You may be tempted to take a couple of short-cuts to resolve problems like these. Please don't. Shortcuts may cause bigger problems down the road.
The first thing you might try is to use the root account from within your application. This is extremely dangerous. Security comes in layers. While you should use stored procedures and prepared statements to avoid SQL injection attacks, you should also apply the principle of least privilege just in case they occur anyway. Allowing your application to connect as root gives an attacker access to your most valuable resource: your data. But giving each part of the application a separate account with only the privilege that it requires quarantines the bad guy.
The second thing that you might try after hours of research is to set the "noAccessToProcedureBodies" flag in the ConnectorJ connection string. Please avoid this flag, as it circumvents the parameter type checking that the JDBC driver provides for you. This flag causes ConnectorJ to convert all of the parameters to strings, which MySQL will then convert back to the required type.
But by walking through the problem step-by-step, these short-cuts should not be necessary.