|
Replies:
14
-
Pages:
1
-
Last Post:
Jan 30, 2013 7:51 AM
by: Francesco
|
Threads:
[
Previous
|
Next
]
|
|
|
|
|
|
MySQL Database Error - SELECT Command denied for user 'xxx' for table 'proc'
Posted:
Mar 9, 2010 10:32 AM
|
|
|
The mysql db I am using is in a Hosted platform. I use the connection type to be TCP and provide all the necessary details like HOST, DB name, username etc. I am able to successfully login into the DB via TOAD for mysql and all the tables, views get reflected.
When I click on Procedures, I see the MySQL Database error
SELECT Command denied for user 'xxx' for table 'proc'
I would like to have the group members input on how to overcome this issue.
When I execute the below script, it reflects all the procedures in the DB which I created using a different tool.
select * from information_schema.routines
|
|
|
Posts:
68
Registered:
8/22/07
|
|
|
|
Re: MySQL Database Error - SELECT Command denied for user 'xxx' for table 'proc'
Posted:
Mar 9, 2010 12:17 PM
in response to: sandsmiles123
|
|
|
Hi,
It looks like there is a permissions issue. Since you are working on a hosted instance of MySQL, can you tell me what kind of administration rights you have?
Dwight
|
|
|
Posts:
1
Registered:
8/4/10
|
|
|
|
Re: MySQL Database Error - SELECT Command denied for user 'xxx' for table 'proc'
Posted:
Aug 4, 2010 1:38 PM
in response to: Dwight
|
|
|
Hi, I too have the same problem with a hosted db.
Seems the client is attempting to read the information schema in a way that a hosted db is not allowed.
This, when moving the mouse over the users, hosts, procedures selections.
It can be overcome on a private host, but not on hosted where access to the schema is limited.
The following would seem to be similiar to what your client is attempting and is disallowed to all but the root account:
SELECT * FROM mysql.proc; Whereas it should be using the following syntax:
SHOW PROCEDURE STATUS;
Keith
|
|
|
Posts:
1
Registered:
10/13/10
|
|
|
|
Re: MySQL Database Error - SELECT Command denied for user 'xxx' for table 'proc'
Posted:
Oct 13, 2010 4:10 AM
in response to: KeithG
|
|
|
I think it was a bug because the right way to retreive procedures list is to use "select * from information_schema.routines where routine_schema='databaseName'" instead of "select * from mysql.proc where db='databaseName'".
Using the second way, that pre-supposes to have extended rights on the 'mysql' database ! But it's not always possible !
Thierry
|
|
|
Posts:
1
Registered:
7/19/11
|
|
|
|
Re: MySQL Database Error - SELECT Command denied for user 'xxx' for table 'proc'
Posted:
Jul 19, 2011 8:49 AM
in response to: misterT
|
|
|
Toad Moderators,
Is there any plan to fix this apparent bug? Any ETA, Targeted Release? Additional information?
Joe
|
|
|
Posts:
314
Registered:
8/23/07
|
|
|
|
Re: MySQL Database Error - SELECT Command denied for user 'xxx' for table 'proc'
Posted:
Jul 20, 2011 11:23 AM
in response to: JoeTheToad
|
|
|
First of all this is not a bug at all.
Using the proc table to determine what stored procedures are available is just as valid as using the INFORMATION_SCHEMA tables. It isn't Toad's fault that the administrators of this specific instance of MySQL has set up these privileges.
You can edit the SQL that Toad uses to fetch this data yourself though by going into the Tools|Options. Then select the page Database|SQL Dictionary. Go through the list on the top right and find the SQL Db.CommonSql|ListFunctions & Db.CommonSql|ListProcedures. Then you can put in whatever SQL you want Toad to use for fetching this list. However be careful to use the same kind of bind variables and columns as the original SQL or otherwise you might be unexpected results.
|
|
|
Posts:
314
Registered:
8/23/07
|
|
|
|
Re: MySQL Database Error - SELECT Command denied for user 'xxx' for table 'proc'
Posted:
Jul 20, 2011 11:47 AM
in response to: Mauritz
|
|
|
The correct SQL to use FYI is this:
SELECT {{Count(Start)}} routine_schema, specific_name {{Count(End)}} FROM INFORMATION_SCHEMA.ROUTINES WHERE routine_type = 'PROCEDURE' and routine_schema = ?
SELECT {{Count(Start)}} routine_schema, specific_name {{Count(End)}} FROM INFORMATION_SCHEMA.ROUTINES WHERE routine_type = 'FUNCTION' and routine_schema = ?
This will also be changed a beta or two from now (We have one beta already ready to post and it wont be in that one, but it will be in the one after that).
|
|
|
Posts:
4
Registered:
9/23/11
|
|
|
|
Re: MySQL Database Error - SELECT Command denied for user 'xxx' for table 'proc'
Posted:
Sep 23, 2011 1:26 PM
in response to: Mauritz
|
|
|
I added the SQL from Mauritz's post above but am still getting the same error:
SELECT command denied to user 'xxx' for table 'proc'.
This is confusing to me because it is not even trying to access the 'proc' table anymore. According to the SQL it should be trying to access INFORMATION_SCHEMA.ROUTINES.
Anyone have any suggestions?
|
|
|
Posts:
314
Registered:
8/23/07
|
|
|
|
Re: MySQL Database Error - SELECT Command denied for user 'xxx' for table 'proc'
Posted:
Sep 24, 2011 1:06 PM
in response to: fonnae
|
|
|
Could it be doing something in regards to updating the right hand side. If you turn on the connection trace (Should be in the tools menu) you can see the exact SQL being executed in the output window.
|
|
|
Posts:
4
Registered:
9/23/11
|
|
|
|
Re: MySQL Database Error - SELECT Command denied for user 'xxx' for table 'proc'
Posted:
Oct 3, 2011 8:47 AM
in response to: Mauritz
|
|
|
|
|
I think you might be on to something. I activated the trace and, indeed, it is still executing the old query:
SELECT COUNT(1) FROM mysql.proc WHERE type = 'FUNCTION' and db = @f0;
I also attached a picture showing the change I made to the SQL dictionary so you can tell me if I did it right.
|
|
|
Posts:
4
Registered:
9/23/11
|
|
|
|
Re: MySQL Database Error - SELECT Command denied for user 'xxx' for table 'proc'
Posted:
Oct 11, 2011 8:52 AM
in response to: fonnae
|
|
|
Does anyone have any idea how to resolve the discrepancy above? I put the appropriate query in the SQL dictionary, but when I click on the 'Functions' tab in Toad it runs a different query and produces an error. Any help is GREATLY appreciated.
|
|
|
Posts:
13
Registered:
9/30/11
|
|
|
|
Re: MySQL Database Error - SELECT Command denied for user 'xxx' for table 'proc'
Posted:
Oct 11, 2011 9:43 AM
in response to: fonnae
|
|
|
Try 'Tools | Options | Database | Cache' > 'Clear Cache'.
One step further is uncheck <same dialogue> 'Enable caching'.
McS
|
|
|
Posts:
13
Registered:
9/30/11
|
|
|
|
Re: MySQL Database Error - SELECT Command denied for user 'xxx' for table 'proc'
Posted:
Oct 11, 2011 11:24 AM
in response to: fonnae
|
|
|
|
|
Found another potential solution, under the Administer tool there is a Flush and Reset command. I'm guessing the RESET QUERY CACHE may be the one:
toad_menu01.bmp
toad_menu02.bmp
McS
|
|
|
Posts:
4
Registered:
9/23/11
|
|
|
|
Re: MySQL Database Error - SELECT Command denied for user 'xxx' for table 'proc'
Posted:
Oct 12, 2011 10:47 AM
in response to: McScreech
|
|
|
I finally figured out the problem. It turns out that I had changed the code in the SQL Dictionary for the wrong version of mysql. I needed to go into the 'Selection:' dropdown and select the other version of mysql and then put the the code that mauritz provided. Everything worked perfect after that. Thanks to everyone for your help.
|
|
|
Posts:
1
Registered:
1/30/13
|
|
|
|
Re: MySQL Database Error - SELECT Command denied for user 'xxx' for table 'proc'
Posted:
Jan 30, 2013 7:40 AM
in response to: fonnae
|
|
|
|
|
I'm wondering if there is a DIY procedure to overcome this kind of error...
Message was edited by: Francesco
|
|
|
|
Legend
|
|
Guru: 2001
+
pts
|
|
Expert: 751
- 2000
pts
|
|
Enthusiast: 31
- 750
pts
|
|
Novice: 0
- 30
pts
|
|
Moderators
|
|
Helpful answer
(5 pts)
|
|
Answered
(10 pts)
|
|
