Wednesday, May 14, 2014

If you use much the MySQL + Java setup, you may have faced the ‘noAccessToProcedureBodies’ error while accessing stored procedures (mostly remotely).
The chances are that the procedure was created by another user (i.e. the DEFINER attribute), and the calling user is different.
I recently faced this issue in one of my projects, where-in we were trying to execute a remote DB’s stored procedure from a java application.
A common solution on the internet is to use the “noAccessToProcedureBodies=true” option in the JDBC connection string.
This seems to work for most of the people out there, but sadly didn’t work for us. What we tried then is another option called the “useInformationSchema=true”.
We removed the earlier option ‘noAccessToProcedureBodies’ option and added this in the JDBC string. This solution too eluded us, but then I tried upgrading the mysql-connector. We were using the mysql-connector-java-5.1.9 version which ideally should have worked (as we just needed a > 5.0.4 version for the solution)
But then I upgraded this to the mysql_connector_java_5.1.30 and this functionality started behaving as expected!
So, the end solution was to use the ‘useInformationSchema’ in the JDBC conn string and have the latest connector in place!
Technorati Tags: MySQL,Java,Issue,Read,error,procedures,setup,procedure,user,DEFINER,solution,option,JDBC,connection,version,noAccessToProcedureBodies,useInformationSchema,connector