MySQL Bugs: #11509: SELECT's abort with 'SELECT command denied' when using tables

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:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.1.12	OS:	Linux (Linux Glibc 2.2/2.3)
Assigned to:		CPU Architecture:	Any

Description:
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'@'10.0.0.1' 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);
 $sth2->execute();
(.. 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

I forgot to paste the line:

my $domain = $row[0];

inside the loop.

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 ...

Hi,

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.

I was unable to reproduce this bug using a recent pull of 4.1 from bk

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:
MySQL-shared-4.1.11-0
MySQL-shared-compat-4.1.11-0
MySQL-server-4.1.11-0
MySQL-client-4.1.11-0

Any suggestions?

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 '0.0.0.0',
  `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`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1