MySQL Bugs: #8223: incorrect update denied messages

Bug #8223	incorrect update denied messages
Submitted:	31 Jan 2005 23:11	Modified:	7 Mar 2005 9:09
Reporter:	Andy	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.2-alpha-max	OS:	Linux (Linux 2.6.10)
Assigned to:		CPU Architecture:	Any

Description:
Mysql gives random update denied messages when multiple updates are running against different tables.

How to repeat:
1.) create table x (a char(1), b char(1))
2.) create table y (a char(1), b char(1))
3.) insert into x (a) values ('a');
4.) insert into y (a) values ('a');
5.) grant select, update on x to someuser@'%.your.domain' identified by somepassword;
6.) grant select, update on y to someuser@'%.your.domain' identified by somepassword;
7.) grant select, update on x to someuser@localhost;
8.) grant select, update on y to someuser@localhost;

perl code to cause bug, change the connect string as needed :

test.pl :

#!/usr/local/bin/perl 
# -d
use DBI;
$dbh=db_connect();

$update_sth=$dbh->prepare("UPDATE x SET b='Y' WHERE a='a'");

for ($x=0;$x<200000;$x++)
{
  $update_sth->execute();
}
 
sub db_connect {
        my $dbh = DBI->connect("DBI:mysql:mysql;host=yourhost;mysql_socket=your_socket","someuser","password" )
        print "Connected\n";
        return $dbh;
}

test2.pl :

#!/usr/local/bin/perl 
# -d
use DBI;
$dbh=db_connect();

$update_sth=$dbh->prepare("UPDATE y SET b='Y' WHERE a='a'");

for ($x=0;$x<200000;$x++)
{
  $update_sth->execute();
}
 
sub db_connect {
        my $dbh = DBI->connect("DBI:mysql:mysql;host=yourhost;mysql_socket=your_socket","someuser","password" )
        print "Connected\n";
        return $dbh;
}

run test.pl and test2.pl at the same time

sometimes one or both of the programs will get an access denied.  If run individually the problem does not occure.  Also, speed of the machine seems to matter.  The problem does not happen very   often on fast machines (3ghz?) but does happen very often on a dual 730mhz machine.

it does not seem to have a problem if only one host is in the tables_priv table.

Can you please add the exact error message and SHOW CREATE TABLE output for both tables involved?

create table statements : 

CREATE TABLE `x` (
  `a` char(1) default NULL,
  `b` char(1) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1

CREATE TABLE `y` (
  `a` char(1) default NULL,
  `b` char(1) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1

Error message :

DBD::mysql::st execute failed: update command denied to user 'f'@'thumper2.emsphone.com' for table 'y' at /mnt/linuxha1/usr/packages/file_find_project/test2.pl line 11.

f@thumper2.emsphone.com has select and update permission on x and y.

I could not repeat the problem using the debugging version of mysql.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

Hi!
I was not able to reproduce it on computer:
vendor_id       : GenuineIntel
cpu family      : 6
model           : 7
model name      : Pentium III (Katmai)
stepping        : 3
cpu MHz         : 547.622

Probably I must know more conditions for reproduce this bug. If you have more ideas how to reproduce it, please let us know.

I can repeat this bug in 4.1.15 client on .  One way is to do an update on the same table and run the script simultaneously about three times in the background.
mysql  Ver 14.7 Distrib 4.1.15, for pc-linux-gnu (i686) using readline 4.3
mysql> select version();
+---------------------+
| version()           |
+---------------------+
| 4.1.10a-pro-gpl-log |
+---------------------+
1 row in set (0.00 sec)

script:
#!/usr/bin/perl
use strict;
use DBI;

my $dbh = do_connect("---", "----", "----", "-----", 3306);
my $sql = qq { update table set x = 'somevalue'  where pk  = ? and pk2= ? };
my $i = 1;
while($i < 1000000) {
       my $sh = $dbh->prepare($sql);
       my $rc = $sh->execute( 888222, 'an@email.com');
       if($rc) {
               print "$i: worked\n";
       } else {
               print "$i: ERROR " . $dbh->errstr;
               enditall();
       }
       $sh->finish;
       $i++;
}

sub enditall{
$dbh->disconnect;
exit;
}

sub do_connect {
  my $db     = shift or return undef;
  my $user   = shift or return undef;
  my $pwd    = shift or return undef;
  my $host   = shift or return undef;
  my $port   = shift or return undef;

  my $dbstr = "DBI:mysql:database=$db";
  $dbstr = $host ? "$dbstr;host=$host" : $dbstr;
  $dbstr = $port ? "$dbstr;port=$port" : $dbstr;
  return DBI->connect( $dbstr, $user, $pwd, {} );
}