|
Home > Archive > MySQL ODBC Connector > April 2006 > Random 'select permission denied' since upgrade to 5.0.18
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Random 'select permission denied' since upgrade to 5.0.18
|
|
| Jorrit Kronjee 2006-03-28, 9:28 am |
| Hello list,
Recently we upgraded from 3.23.3 to 5.0.18 and started noticing some
weird behaviour. We have update scripts running on a regular interval
and some of these scripts randomily exited with MySQL error:
SELECT command denied to user 'user'@'host' for table 'example'
However, this error message only appears sporadically. Usually these
scripts run without any problems.
tcpdump shows nothing more than we already know. I see the error message
appear on different queries.
We have the server running on Gentoo Linux (mysql-5.0.18-r30) as a
master (with one slave on another Gentoo machine).
Any ideas to debug this problem any further?
Thanks in advance,
Jorrit
By the way, there's been a previous posting to this list describing
similar problems (no solution though):
http://marc.theaimsgroup.com/?l=mys...50497323853&w=2
--
System Developer
Infopact Network Solutions
Hoogvlietsekerkweg 170
3194 AM Rotterdam Hoogvliet
tel. +31 (0)88 - 4636700
fax. +31 (0)88 - 4636799
mob. +31 (0)6 - 14105968
j.kronjee@infopact.nl
http://www.infopact.nl/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
| |
| Jorrit Kronjee 2006-03-28, 8:26 pm |
| Keith,
Thanks for answering me.
Yes I did. Nothing in the MySQL log either. Keep in mind that these
errors only sometimes show up and usually in the middle of a few queries
in a row (while permissions are set for the whole table). Because the
script runs fine most of the time, I don't think privilege settings are
the key here.
Could this message appear when, for instance, a maximum amount of
threads has been spawned or MySQL has reached its connection limit?
Jorrit
Keith Roberts wrote:
> Check your mysql log and see if it says anything about not
> being able to use the new password format.
>
> Did run mysql_fix_privilege_
tables to update your
> mysql passwords in the mysql privileges database?
>
> Regards
>
> Keith
>
> In theory, theory and practice are the same;
> in practice they are not.
>
> On Tue, 28 Mar 2006, Jorrit Kronjee wrote:
>
>
--
System Developer
Infopact Network Solutions
Hoogvlietsekerkweg 170
3194 AM Rotterdam Hoogvliet
tel. +31 (0)88 - 4636700
fax. +31 (0)88 - 4636799
mob. +31 (0)6 - 14105968
j.kronjee@infopact.nl
http://www.infopact.nl/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
| |
| mysql@karsites.net 2006-03-28, 8:26 pm |
| On Tue, 28 Mar 2006, Jorrit Kronjee wrote:
> To: mysql@lists.mysql.com
> From: Jorrit Kronjee <j.kronjee@infopact.nl>
> Subject: Re: Random 'select permission denied' since upgrade to 5.0.18
snip
> Could this message appear when, for instance, a maximum amount of threads
> has been spawned or MySQL has reached its connection limit?
Possible - what are your settings for the relevant mysql
server variables?
You could try something like:
show variables like "max%" \G
I'm not really sure what all the server variables do, but
they may be relevant to your problem.
Keith
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
| |
| Jorrit Kronjee 2006-03-28, 8:26 pm |
| mysql@karsites.net wrote:
> On Tue, 28 Mar 2006, Jorrit Kronjee wrote:
>
> snip
>
> Possible - what are your settings for the relevant mysql
> server variables?
>
> You could try something like:
>
> show variables like "max%" \G
>
> I'm not really sure what all the server variables do, but
> they may be relevant to your problem.
>
> Keith
>
Keith,
Here's the output:
mysql> show variables like "max%" \G
********************
******* 1. row ********************
*******
Variable_name: max_allowed_packet
Value: 1047552
********************
******* 2. row ********************
*******
Variable_name: max_binlog_cache_siz
e
Value: 4294967295
********************
******* 3. row ********************
*******
Variable_name: max_binlog_size
Value: 1073741824
********************
******* 4. row ********************
*******
Variable_name: max_connect_errors
Value: 10
********************
******* 5. row ********************
*******
Variable_name: max_connections
Value: 300
********************
******* 6. row ********************
*******
Variable_name: max_delayed_threads
Value: 20
********************
******* 7. row ********************
*******
Variable_name: max_error_count
Value: 64
********************
******* 8. row ********************
*******
Variable_name: max_heap_table_size
Value: 16777216
********************
******* 9. row ********************
*******
Variable_name: max_insert_delayed_t
hreads
Value: 20
********************
******* 10. row ********************
*******
Variable_name: max_join_size
Value: 4294967295
********************
******* 11. row ********************
*******
Variable_name: max_length_for_sort_
data
Value: 1024
********************
******* 12. row ********************
*******
Variable_name: max_relay_log_size
Value: 0
********************
******* 13. row ********************
*******
Variable_name: max_seeks_for_key
Value: 4294967295
********************
******* 14. row ********************
*******
Variable_name: max_sort_length
Value: 1024
********************
******* 15. row ********************
*******
Variable_name: max_sp_recursion_dep
th
Value: 0
********************
******* 16. row ********************
*******
Variable_name: max_tmp_tables
Value: 32
********************
******* 17. row ********************
*******
Variable_name: max_user_connections
Value: 0
********************
******* 18. row ********************
*******
Variable_name: max_write_lock_count
Value: 4294967295
18 rows in set (0.00 sec)
--
System Developer
Infopact Network Solutions
Hoogvlietsekerkweg 170
3194 AM Rotterdam Hoogvliet
tel. +31 (0)88 - 4636700
fax. +31 (0)88 - 4636799
mob. +31 (0)6 - 14105968
j.kronjee@infopact.nl
http://www.infopact.nl/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
| |
| Jorrit Kronjee 2006-03-28, 8:26 pm |
|
> is your access control by hostname or IPnumber? if hostname you could
> be having transient DNS issues - where the IPnumber on the client
> connect can't be resolved into the permitted hostname (fast enough).
> try using IPnumber in the access control and see if the problem goes
> away - if it does you'll want to look into your inverse-map DNS issues.
I checked it. It's by IP number. However, I wonder, could this be the
case even after multiple queries in the same TCP session?
Jorrit
--
System Developer
Infopact Network Solutions
Hoogvlietsekerkweg 170
3194 AM Rotterdam Hoogvliet
tel. +31 (0)88 - 4636700
fax. +31 (0)88 - 4636799
mob. +31 (0)6 - 14105968
j.kronjee@infopact.nl
http://www.infopact.nl/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
| |
| mysql@karsites.net 2006-03-28, 8:26 pm |
| On Tue, 28 Mar 2006, Jorrit Kronjee wrote:
> To: mysql@lists.mysql.com
> From: Jorrit Kronjee <j.kronjee@infopact.nl>
> Subject: Re: Random 'select permission denied' since upgrade to 5.0.18
>
> mysql@karsites.net wrote:
>
> Keith,
>
> Here's the output:
>
> mysql> show variables like "max%" \G
> ********************
******* 1. row ********************
*******
> Variable_name: max_allowed_packet
> Value: 1047552
> ********************
******* 2. row ********************
*******
> Variable_name: max_binlog_cache_siz
e
> Value: 4294967295
> ********************
******* 3. row ********************
*******
> Variable_name: max_binlog_size
> Value: 1073741824
> ********************
******* 4. row ********************
*******
> Variable_name: max_connect_errors
> Value: 10
> ********************
******* 5. row ********************
*******
> Variable_name: max_connections
> Value: 300
> ********************
******* 6. row ********************
*******
> Variable_name: max_delayed_threads
> Value: 20
is this relevant ?
> ********************
******* 7. row ********************
*******
> Variable_name: max_error_count
> Value: 64
> ********************
******* 8. row ********************
*******
> Variable_name: max_heap_table_size
> Value: 16777216
> ********************
******* 9. row ********************
*******
> Variable_name: max_insert_delayed_t
hreads
> Value: 20
ditto
> ********************
******* 10. row ********************
*******
> Variable_name: max_join_size
> Value: 4294967295
> ********************
******* 11. row ********************
*******
> Variable_name: max_length_for_sort_
data
> Value: 1024
> ********************
******* 12. row ********************
*******
> Variable_name: max_relay_log_size
> Value: 0
> ********************
******* 13. row ********************
*******
> Variable_name: max_seeks_for_key
> Value: 4294967295
> ********************
******* 14. row ********************
*******
> Variable_name: max_sort_length
> Value: 1024
> ********************
******* 15. row ********************
*******
> Variable_name: max_sp_recursion_dep
th
> Value: 0
> ********************
******* 16. row ********************
*******
> Variable_name: max_tmp_tables
> Value: 32
> ********************
******* 17. row ********************
*******
> Variable_name: max_user_connections
> Value: 0
> ********************
******* 18. row ********************
*******
> Variable_name: max_write_lock_count
> Value: 4294967295
> 18 rows in set (0.00 sec)
>
>
> --
> System Developer
>
> Infopact Network Solutions
> Hoogvlietsekerkweg 170
> 3194 AM Rotterdam Hoogvliet
> tel. +31 (0)88 - 4636700
> fax. +31 (0)88 - 4636799
> mob. +31 (0)6 - 14105968
> j.kronjee@infopact.nl
> http://www.infopact.nl/
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql? unsub...ites
.net
>
>
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
| |
| Jorrit Kronjee 2006-03-29, 3:27 am |
| mysql@karsites.net wrote:
> is this relevant ?
We've been looking at connection graphs, but MySQL doesn't seem to reach
that limit. However, these are timely based measurements, so it could've
peaked in between, although highly unlikely.
I'm not very comfortable tweaking these values in a production
environment while I have no indication that it could solve the problem.
Jorrit
--
System Developer
Infopact Network Solutions
Hoogvlietsekerkweg 170
3194 AM Rotterdam Hoogvliet
tel. +31 (0)88 - 4636700
fax. +31 (0)88 - 4636799
mob. +31 (0)6 - 14105968
j.kronjee@infopact.nl
http://www.infopact.nl/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
| |
| Mark Leith 2006-03-29, 7:29 am |
| Hi Jorrit,
Jorrit Kronjee wrote:
> mysql@karsites.net wrote:
>
> We've been looking at connection graphs, but MySQL doesn't seem to
> reach that limit. However, these are timely based measurements, so it
> could've peaked in between, although highly unlikely.
>
> I'm not very comfortable tweaking these values in a production
> environment while I have no indication that it could solve the problem.
>
> Jorrit
>
It seems you are running in to Bug #7209:
http://bugs.mysql.com/bug.php?id=7209
This is fixed in 5.0.19 now.
Best regards
Mark
--
Mark Leith,
Support Engineer MySQL AB, Worcester, England, www.mysql.com
Are you MySQL certified? www.mysql.com/certification
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
| |
| Jorrit Kronjee 2006-03-29, 7:29 am |
| Mark Leith wrote:
> Hi Jorrit,
>
> Jorrit Kronjee wrote:
> It seems you are running in to Bug #7209:
>
> http://bugs.mysql.com/bug.php?id=7209
>
> This is fixed in 5.0.19 now.
>
> Best regards
>
> Mark
>
Mark,
Apparently so, thanks for the hint! We'll try to upgrade as soon as
possible.
I'll supply the mailing list with the results of the upgrade.
Jorrit
--
System Developer
Infopact Network Solutions
Hoogvlietsekerkweg 170
3194 AM Rotterdam Hoogvliet
tel. +31 (0)88 - 4636700
fax. +31 (0)88 - 4636799
mob. +31 (0)6 - 14105968
j.kronjee@infopact.nl
http://www.infopact.nl/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
| |
| Jorrit Kronjee 2006-04-03, 7:32 am |
| On 3/29/2006 2:10 PM, Jorrit Kronjee wrote:
>
> Mark,
>
> Apparently so, thanks for the hint! We'll try to upgrade as soon as
> possible.
>
> I'll supply the mailing list with the results of the upgrade.
Mark,
We've been testing it over the weekend and it seems that the bugs we ran
into before completely disappeared. Thanks for your support!
Jorrit
--
System Developer
Infopact Network Solutions
Hoogvlietsekerkweg 170
3194 AM Rotterdam Hoogvliet
tel. +31 (0)88 - 4636700
fax. +31 (0)88 - 4636799
mob. +31 (0)6 - 14105968
j.kronjee@infopact.nl
http://www.infopact.nl/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
| |
| Mark Leith 2006-04-03, 7:32 am |
| Jorrit Kronjee wrote:
> On 3/29/2006 2:10 PM, Jorrit Kronjee wrote
>
>
> Mark,
>
> We've been testing it over the weekend and it seems that the bugs we ran
> into before completely disappeared. Thanks for your support!
>
> Jorrit
>
>
Hi Jorrit,
Great news - that bug was one that we tried for a long time to replicate
(as can be seen from the bug report), eventually I managed to repeat it
a few months ago, and we got the fix turned around fairly quickly - so
it's a fairly obvious one every time I see it now ;)
Best regards
Mark
--
Mark Leith, Support Engineer
MySQL AB, Worcester, England, www.mysql.com
Are you MySQL certified? www.mysql.com/certification
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
|
|
|
|
|