Bug #11509 SELECT's abort with 'SELECT command denied' when using tables_priv
Submitted: 22 Jun 2005 15:53 Modified: 4 Jul 2005 19:08
Reporter: Wouter de Jong Email Updates:
Status: Can't repeat Impact on me:
Category:MySQL Server Severity:S2 (Serious)
Version:4.1.12 OS:Linux (Linux Glibc 2.2/2.3)
Assigned to: CPU Architecture:Any

[22 Jun 2005 15:53] Wouter de Jong
We have a simple table with about 69093 records.

When executing a SELECT, and running a 2nd query inside the result-loop, the loop randomly get's aborted with an error:

SELECT command denied to user 'test'@'' for table 'test' at ./test.pl line 27.

Despite having 'query-logging' enabled, this does not get logged (log in my.cnf)
Also, it does not happen every time, but still pretty often.

I did a: GRANT SELECT ON test.test TO 'test'@'10.0.0.%' IDENTIFIED BY 's0m3p4ss';

So I have one record in mysql.user and one record in mysql.tables_priv. No record in 'mysql.db'.

I haven't had a time te reproduce this on another Platform then Linux, but it occurs on multiple machines with either MySQL 4.1.11 or 4.1.12.

It happens on a self-compiled install, but also on MySQL 4.1.12 binary install. (glibc 2.2 .tar.gz)

How to repeat:
The script is very basic:

my $sth  = $dbh -> prepare ("SELECT DISTINCT(domain) FROM test");
$sth->execute ();
while (my @row = $sth -> fetchrow) {
 my $q2 = "SELECT ID FROM test WHERE (domain='$domain' AND recipient NOT LIKE '[bounce]' AND alias='')";
 my $sth2 = $dbh->prepare($q2);
(.. result loop ..)
 $sth2->finish ();
$sth->finish ();

The first query returns about 8438 rows of the 69093 in total.

It looks like that for some reason the tables_priv privilege rights are lost ... memory problem ? (The machines have 2 - 4 GB RAM)

OS: RedHat + Debian + Fedora Core 3
Client: Perl 5.8.5, modules: DBI && DBD::mysql
[22 Jun 2005 15:54] Wouter de Jong
I forgot to paste the line:

my $domain = $row[0];

inside the loop.
[23 Jun 2005 8:47] Hartmut Holzgraefe
This looks similar to bug 7209, but it seems as if you have a rather reliable test case?

Could you provide us with a dump of the database (or even better, a tar or zip archive
of your data directory) and a fixed version of the test script?

I'd also like to know wheter you are using a fresh installation or a database previously
created by an older MySQL version ...
[23 Jun 2005 10:25] Wouter de Jong

The .tar.gz of the table and thbe Perl-script are uploaded to you.
If you'd like to have the full var/ directory let me know.

I run the test-script every minute from crontab. After 5 - 45 minutes, the script begins to fail with the SELECT denied errors at random offsets in the loop.

The table was orginally created on MySQL 3.23, then updated to 4.0.x and eventually I upgraded to 4.1.x.

The problems seems to appear at least since MySQL version 4.1.10.
4.0.x seems not be affected.

I uploaded the table to a FreeBSD 5.4 machine with custom built MySQL (but no special options, eg. not linked to Linuxthreads) and the errors also appeared.

Also, the clients are using libs varying from 3.23.x to 4.0.x and 4.1.x
They only do SELECT's, modifying only happens with recent 4.1.x libraries.
[4 Jul 2005 19:08] Jorge del Conde
I was unable to reproduce this bug using a recent pull of 4.1 from bk
[14 Jul 2005 7:31] Dan Makovec
I see similar behaviour on 4.1.11 installed from RPM on an Fedora Core 1 box running kernel 2.4.22.

For our purposes, we're hitting the computer continuously at 117 qps to an all InnoDB database.

The error given is:
SELECT command denied to user 'dhcp_user'@'uw.prod.dhcp.1' for table 'modem' Query: select sv_id from modem where eid=4289376995

The client runs on a separate host and is linked against libmysqlclient.so.14

We're seeing this error about once a minute.  Had the same server running 4.0.15 this morning with the same clients connected and didn't have this problem.

All we did was upgrade our RPMs to:

Any suggestions?
[14 Jul 2005 7:33] Dan Makovec
Apologies, the structure of the table in question is:
CREATE TABLE `lease` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `sid` int(10) unsigned NOT NULL default '0',
  `eid` int(10) unsigned NOT NULL default '0',
  `sv_id` int(10) unsigned NOT NULL default '0',
  `ip_address` varchar(16) NOT NULL default '',
  `when_start_incl` datetime NOT NULL default '0000-00-00 00:00:00',
  `when_end_excl` datetime NOT NULL default '0000-00-00 00:00:00',
  `mac_address` varchar(18) NOT NULL default '00:00:00:00:00:00',
  `host_name` varchar(32) default NULL,
  `server_name` varchar(32) NOT NULL default 'unknown',
  PRIMARY KEY  (`id`),
  KEY `LEASE_IP_ADR_IDX` (`ip_address`),
  KEY `LEASE_EID_IDX` (`eid`),
  KEY `LEASE_SID_IDX` (`sid`)