SQL Zone is brought to you in partnership with:

For the past eight(8) years Schalk Neethling has been working as a freelance developer under the pseudo of Volume4 and is now the president of Overt Strategy Consulting. During this period he has completed over 300 projects ranging from full web application development to complete branding. As president and lead developer of Overt Strategy Consulting, Schalk Neethling and his team has released a 100% Java standards based content management system called AlliedBridge and business document exchange and review system, called Doc-Central. Schalk Neethling is also actively involved on a daily basis in the open source, web standards and accessibility areas and is a current active member of the Web Standards Group. Schalk is also the co-founder and president of the non-profit The South Web Standards and Accessibility Group, which aims to actively educate and raise awareness of web standards and accessibility to both the developer society as well as business large and small. Schalk also has a long relationship with DZone and is currently zone leader for both the web builder, css.dzone.com, as well as the .NET zone, dotnet.dzone.com, and you can find a lot of his writing there as well as on his blog located at schalkneethling.alliedbridge.com. Schalk is constantly expanding on his knowledge of various aspects of technology and loves to stay in touch with the latest happenings. For Schalk web development and the internet is not just a job, it is a love, a passion and a life style. Schalk has posted 173 posts at DZone. View Full User Profile

Quick Tip: Granting access to meta-data on MySQL

03.22.2008
| 27655 views |
  • submit to reddit
Ever seen this exception in your log files when working with MySQL as your database back-end?

User does not have access to metadata required to determine stored procedure parameter types. If rights can not be granted, configure connection with "noAccessToProcedureBodies=true" to have driver generate parameters that represent INOUT strings irregardless of actual parameter types.

Well, I have come across this now a couple of times and for some reason I just cannot remember how to either configure the connection to use the "noAccessToProcedureBodies=true" switch or what the exact SQL is that I need to execute to give the user the appropriate access. This time however I decided it is time that I place this information somewhere I can easily find it again and where it can potentially also help out others. Believe me to find the solution to this via Google searches takes a while... So, I naturally thought of the DZone network and the SQL zone in particular. Enough talk then, below follows the solutions for both scenarios. For the second one however, you will need a version of the Connector/J equal to or above version 5.0.3.

If you have root access to your MySQL database then you can simply run a query on the database to resolve the problem. What the query will do is give the specified user the needed rights on the mysql.proc table. This takes the form of the following GRANT SQL query:
GRANT [SELECT, INSERT, UPDATE] ON `mysql`.`proc` TO 'myuser'@'myhost';
That's it, after running this query on the database the above exception goes away. If however, you do not have the needed root access to run this query on the database then below is the connection string to use to get around this problem. Very simple, just add &noAccessToProcedureBodies=true to your current connection string and you are done.
String connectionURL = "jdbc:mysql://localhost:3306/mydatabase?user=myuser&password=mypassword&noAccessToProcedureBodies=true"
I sincerely hope this tip will help out someone else. If not, well, then at least I now know where to go the next time I run into this problem :)
4
Your rating: None Average: 4 (1 vote)
Published at DZone with permission of its author, Schalk Neethling.

(Note: Opinions expressed in this article and its replies are the opinions of their respective authors and not those of DZone, Inc.)

Comments

roger james replied on Wed, 2008/07/23 - 11:05pm

Faced this problem and Yahoo Search engine returned two results. Luckily this solution worked for me. phew!

Aravind Kumar replied on Tue, 2009/05/12 - 1:35am

Buddy, It worked. Thanks for giving the solution :-).

Bala Mohan replied on Mon, 2010/03/29 - 9:04am

Hi,

I was unable to execute stored procedures earlier and with this additionaly parameter, i was able to execute the stored procedure. At the same time, i am facing a peculiar problem. With this parameter included in the connection string, the first time execution of the stored procedure causes error but when i re-execute the same procedure it works fine.  Any solution to overcome this issue?

Greatly appreciate your time and reply.

Regards

 

John Smith replied on Tue, 2011/07/05 - 7:36am

I ran into this problem, but unfortunatley, I copied and pasted the GRANT statement:

 GRANT [SELECT, INSERT, UPDATE] ON `mysql`.`proc` TO 'myuser'@'myhost';

 

from another website that had "MySQL" as the database name instead of "mysql". This resolved the issue in my Windows development environment, but when I tried to apply the script to an Amazon RDS instance, I got the error:

 Error Code: 1146
Table 'MySQL.proc' doesn't exist

Unfortunately, the noAccessToProcedureBodies=true didn't work for me (I don't know why, I tried it in the URL as suggested, and also as a connection property), and we spent ages trying to work out the MySQL.proc table was missing (lots of posts suggested it was because the database system tables had changed in upgrades of MySQL, which threw us off the scent). 

So, if you run:

show databases

 

this should confirm the case of your database name. And here's a link to MySQL's case sensitvity: http://dev.mysql.com/doc/refman/5.5/en/identifier-case-sensitivity.html

 

Cheers,

 JS.

 

Todd Trimmer replied on Wed, 2011/09/28 - 5:03pm

SELECT I can understand, but why is INSERT and UPDATE needed on mysql.proc for metadata just to EXECUTE?

Carlo Gherisi replied on Wed, 2012/11/21 - 12:58pm

Wonderful, it perfectly worked for me!!

Regarding the issue of placing your code in some place easily retrievable at any time, I recently came across an interesting service called Snip2code: http://www.snip2code.com.

It allows the user to collect his own snippets, share them to the public or just to your colleagues or friends.

It comes also with few interesting plugins for VisualStudio and Eclipse, and a Windows8 app...

It solved my problem of collecting my snippets in a single place, as I need to have such snippets available from a bunch of computers...


Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.