Bug #13477 no grants to execute SQL command
Submitted: 26 Sep 2005 8:59 Modified: 6 Jun 2006 6:29
Reporter: Andrejs Dubovskis Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.0.15 OS:Linux (Linux)
Assigned to: Sergei Glukhov CPU Architecture:Any

[26 Sep 2005 8:59] Andrejs Dubovskis
Description:
While executing SQL command got an error:
"INSERT command denied to user ...."
At the same time required grants was asigned to user and the same SQL statement has multiply successfull pases before the error was generated.

How to repeat:
We found how to repeat this error in simple manner.
Very important to asign grants to two remote users (on host1 & host2). I am not able to repeat this problem, if access was granted to single remote user only.

1) prepare schema

create database sqltest;
use sqltest;
CREATE TABLE `whitelist` (
 `example` int(11) default NULL
) ENGINE=MyISAM;

grant select,insert,delete on sqltest.whitelist to sqltest@'any.remote.host1';
grant select,insert,delete on sqltest.whitelist to sqltest@'any.remote.host2';

2) create test.pl on remote host1;
for (1..100000) {
  print "INSERT INTO whitelist VALUES(1);\n" ;
}

3) run commands on remote host1;
perl test.pl | mysql -u sqltest -h db sqltest &
perl test.pl | mysql -u sqltest -h db sqltest &

Suggested fix:
Only temporary fix, reducing SQL security:
revoke all priveleges from tables and grant all priveleges on database;

grant all on sqltest.* to sqltest@'any.remote.host';
[26 Sep 2005 13:00] Andrejs Dubovskis
Possibly, this thing may be important: we using --skip-name-resolve on server
[1 Oct 2005 10:56] Valeriy Kravchuk
Just curious, what values are used instead of 'any.remote.host1' and 'any.remote.host2'. IP addresses, I hope? Host names are ignored when --skip-name-resolution is provided...

Why do you need this --skip-name-resolution option? I've just tested with 4.1.14 on Linux and host names instead of addresses - everything works OK, as expected. Can you try to run without it and use host names?

If you can't, the other questions arise. Did I understood you right: granting individual privileges does not work, but granting all on all the tables works?

Is there any other activity on the server when you are running your test.pl scripts? What if you insert a pause among individual inserts (sleep(1) or something like that) - does it change something?
[3 Oct 2005 9:54] Andrejs Dubovskis
When name resolution is disabled, we are using IP addresses only.

Name resolution was disabled, because once we had a problem with DNS, resulting in no MySQL service.

Answering to your question: yes, granting individual table privileges does not work, but granting all on all the tables works.

I had tried the same tests on clean server with enabled name resolution (almost the same software, no load on CPU, no other SQL connections, test clients only). Problem stays.
While testing, i found, that after a play with grant/revoke commands the error may dissapear. After small investigation I found, that order of grants is important. The error happens only for first entry in tables_priv table.

truncate mysql.db;
truncate mysql.tables_priv;
flush privileges;
grant select,insert,delete on sqltest.whitelist to sqltest@host1;
grant select,insert,delete on sqltest.whitelist to sqltest@host2;

After mentioned sequence we have error if script launched on host1. But no error for script on host2.

In case of any pause between inserts (I had random sleep 0..100ms) problem does not appear.
[12 Oct 2005 15:05] Valeriy Kravchuk
OK, so in case of minimal delay among INSERT attempts everything just works. Can you, please, perform the SHOW PROCESSLIST several times on the server while your 2 perl scripts are running? Send me the results.

What operating systems are used to run clients? Have you tried to repeat on other operating systems, or it it just a HP-UX and 64-bit specific issue? I've tried on other hardware and was not able to get this kind of behaviour.

You my.cnf settings may be useful too.
[18 Oct 2005 7:26] Andrejs Dubovskis
I had tested Debian 3.1 on i386 hardware. My steps follow:

* fetch MySQL precompiled version mysql-standard-4.1.14-pc-linux-gnu-i686-glibc23
* unpack into /usr/local
* create link /usr/local/mysql
* run mysqld with --no-defaults --skip-grant --skip-innodb --datadir=/tmp
* create mysql database using mysql_create_system_tables script
* stop mysqld
* run it again with --no-defaults --skip-innodb --datadir=/tmp
* create test database, test table and according grants as written before

Now we have running mysqld.

To run client I using following commands:
perl test.pl | mysql -f -u sqltest -h db sqltest &
perl test.pl | mysql -f -u sqltest -h db sqltest &

Few "show processlist" gathered and attached.

As you see I have no my.cnf (--no-defaults).

Following server and client combination was tested and got the error:

server                   client
hpux 4.1.14           hpux 4.1.14
hpux 4.1.14           linux i386 4.0.24
linux i386 4.1.14    linux i386 4.0.24

Again, following things are VERY IMPORTANT to reproduce the error:
* error reproduced only on first granted host;
* DO NOT use UNIX socket for connection but network (over TCP port 3306)
[18 Oct 2005 7:29] Andrejs Dubovskis
show processlist

Attachment: ps.txt (text/plain), 2.64 KiB.

[18 Oct 2005 7:33] Andrejs Dubovskis
Forgot to mention, that on version linux-i386-4.0.24 I was not able to reproduce this problem.
[21 Oct 2005 7:59] Andrejs Dubovskis
Tested on debian 3.1-i386.
Server and client is 4.1.15 (fetched from mysql.com).
Error does not dissapear.
[25 Oct 2005 8:25] Andrejs Dubovskis
On Debian 3.1-i386 and mysql.com provided binaries the error reproduced as well.
[30 Oct 2005 13:52] Valeriy Kravchuk
I am still unable to use our Debian x86 server for tests, but in the meantime would like to ask you several questions.

1. You had changed version to 5.0.15. So, you were able to repeat this problem on Debain with both 4.1.15 and 5.0.15 server and clients, aren't you? (If yes, I'll have to check with 5.0.x too)
2. Does the client version and OS really important? For me it looks like not important. I simply have no 3 different Debian machines to test.
3. As we figured out, the problem is repetable both with IP-addresses and host names, isn't it?
4. Two remote clients are important. The problem is not repeatable if one of them use local connection, even through TCP port, isn't it?
5. The correct sequence of actions to repeat is the following:

- Install MySQL 4.1.15 or 5.0.15 as usual (I'll compile it from sources) on Debian server, 'db'
- Create the test database and table:

create database sqltest;
use sqltest;
CREATE TABLE `whitelist` (
 `example` int(11) default NULL
) ENGINE=MyISAM;

- Flush and set the privileges:
truncate mysql.db;
truncate mysql.tables_priv;
flush privileges;
grant select,insert,delete on sqltest.whitelist to sqltest@host1;
grant select,insert,delete on sqltest.whitelist to sqltest@host2;

- Run your test.pl on host1 and, immediately, on host2.

perl test.pl | mysql -f -u sqltest -h db sqltest &
perl test.pl | mysql -f -u sqltest -h db sqltest &

Is it correct?

Please, note, that I was not able to repeat the behaviour you described with HP-UX, 4.1.14 as server and RH + FreeBSD (4.1.14) as clients. That is why I ask so many questions.

Thank you for your patience.
[8 Nov 2005 12:03] Andrejs Dubovskis
Sorry for delay. Now answering to your questions:

1. Yes, the problem exists in version 5.0.15 also
2. Client version and OS is not important
3. Yes, problem repeatable with host names or IP addreses
4. To reproduce the error one client is enough. But you should assign grants for two clients. Connection should be using TCP port (doesn't matter, from local or remote host, but according grant should be assigned).
5. Sequence has an error: scripts should be invoked on host1 only (this is a reason why & is used [background job]) 

Today I have very simple environment, mysqld and client are running on the same host. IP = 10.10.1.1

truncate mysql.db;
truncate mysql.tables_priv;
flush privileges;
grant select,insert,delete on sqltest.whitelist to sqltest@'10.10.1.1';
grant select,insert,delete on sqltest.whitelist to sqltest@'10.10.1.2';

(note, second host is not exists in real life, but grant is important);

To reproduce error run BOTH COMMANDS on 10.10.1.1
perl test.pl | mysql -f -u sqltest -h  10.10.1.1 sqltest &
perl test.pl | mysql -f -u sqltest -h  10.10.1.1 sqltest &

I found, that with modern hardware (Pentium 4 3Ghz) error hapens in rare cases. But in case of Pentim 3, 800Mhz it happens each pass.
[8 Nov 2005 14:36] Valeriy Kravchuk
Thank you for a clarification. I'll try on old Pentium II...
[8 Nov 2005 15:03] Valeriy Kravchuk
Sorry, but I still was not able to repeat on old Celeron 300 Mhz with newer 5.0.16-BK build. I tried 3 times, with both empty and not empty whitelist table:

[openxs@Fedora 5.0]$ bin/mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8 to server version: 5.0.16

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select count(*) from sqltest.whitelist;
+----------+
| count(*) |
+----------+
|   400000 |
+----------+
1 row in set (0,00 sec)

mysql> truncate sqltest.whitelist;
Query OK, 0 rows affected (0,01 sec)

mysql> truncate mysql.db;
Query OK, 0 rows affected (0,01 sec)

mysql> truncate mysql.tables_priv;
Query OK, 0 rows affected (0,00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0,00 sec)

mysql> grant select,insert,delete on sqltest.whitelist to sqltest@'192.168.0.46'; -- this is my real IP
Query OK, 0 rows affected (0,00 sec)

mysql> grant select,insert,delete on sqltest.whitelist to sqltest@'192.168.0.47'; -- this IP does not exist
Query OK, 0 rows affected (0,00 sec)

mysql> exit
Bye
[openxs@Fedora 5.0]$ perl test.pl | bin/mysql -f -usqltest -h 192.168.0.46 sqlt
est &
[2] 4569
[openxs@Fedora 5.0]$ perl test.pl | bin/mysql -f -usqltest -h 192.168.0.46 sqlt
est &
[3] 4572

After some time passed...

[openxs@Fedora 5.0]$ bin/mysql -uroot 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11 to server version: 5.0.16

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select count(*) from sqltest.whitelist;
+----------+
| count(*) |
+----------+
|   200000 |
+----------+
1 row in set (0,00 sec)

mysql> exit
Bye
[2]-  Done                    perl test.pl | bin/mysql -f -usqltest -h 192.168.0.46 sqltest
[3]+  Done                    perl test.pl | bin/mysql -f -usqltest -h 192.168.0.46 sqltest

[openxs@Fedora 5.0]$ dmesg
Linux version 2.4.22-1.2115.nptl (bhcompile@daffy.perf.redhat.com) (gcc version 3.2.3 20030422 (Red Hat Linux 3.2.3-6)) #1 Wed Oct 29 15:42:51 EST 2003
BIOS-provided physical RAM map:
 BIOS-e820: 0000000000000000 - 000000000009fc00 (usable)
 BIOS-e820: 000000000009fc00 - 00000000000a0000 (reserved)
 BIOS-e820: 00000000000f0000 - 0000000000100000 (reserved)
 BIOS-e820: 0000000000100000 - 000000000dff0000 (usable)
 BIOS-e820: 000000000dff0000 - 000000000dff3000 (ACPI NVS)
 BIOS-e820: 000000000dff3000 - 000000000e000000 (ACPI data)
 BIOS-e820: 00000000ffff0000 - 0000000100000000 (reserved)
0MB HIGHMEM available.
223MB LOWMEM available.
ACPI: have wakeup address 0xc0001000
On node 0 totalpages: 57328
zone(0): 4096 pages.
zone(1): 53232 pages.
zone(2): 0 pages.
ACPI disabled because your bios is from 98 and too old
You can enable it with acpi=force
ACPI: RSDP (v000 123456                                    ) @ 0x000f7140
ACPI: RSDT (v001 123456 AWRDACPI 0x00000000  0x00000000) @ 0x0dff3000
ACPI: FADT (v001 123456 AWRDACPI 0x00000000  0x00000000) @ 0x0dff3040
ACPI: BOOT (v001 123456          0x00000000  0x00000000) @ 0x0dff4d00
ACPI: DSDT (v001 123456 AWRDACPI 0x00001000 MSFT 0x0100000a) @ 0x00000000
Kernel command line: ro root=LABEL=/1 rhgb
Initializing CPU#0
Detected 300.697 MHz processor.
Console: colour VGA+ 80x25
Calibrating delay loop... 599.65 BogoMIPS
Memory: 222824k/229312k available (1503k kernel code, 6100k reserved, 1110k data, 136k init, 0k highmem)
Dentry cache hash table entries: 32768 (order: 6, 262144 bytes)
Inode cache hash table entries: 16384 (order: 5, 131072 bytes)
Mount cache hash table entries: 512 (order: 0, 4096 bytes)
Buffer cache hash table entries: 16384 (order: 4, 65536 bytes)
Page-cache hash table entries: 65536 (order: 6, 262144 bytes)
CPU: L1 I cache: 16K, L1 D cache: 16K
CPU: L2 cache: 128K
Intel machine check architecture supported.
Intel machine check reporting enabled on CPU#0.
CPU:     After generic, caps: 0183f9ff 00000000 00000000 00000000
CPU:             Common caps: 0183f9ff 00000000 00000000 00000000
CPU: Intel Celeron (Mendocino) stepping 00

I do not know, how to repeat this problem. Any ideas are welcomed.
[9 Nov 2005 12:10] Andrejs Dubovskis
Thank you for your patience.

Your latest test procedure was correct. After small investigation, I found a difference.

Almost in every test I had used servers with 2 CPUs (for HPUX, possibly even more). I was not able to reproduce the error in single CPU environment. Server performance is not a key, today I tried 2CPU 3.4Ghz and catch the problem in first try.
[9 Nov 2005 13:48] Valeriy Kravchuk
OK, let me try on multiprocessor machine. It happend so, that HP-UX machine I used for the initial testing was single processor hp3750 workstation...
[9 Nov 2005 17:31] Valeriy Kravchuk
I've just tried with fresh 5.0.15, the same procedure as before, on 4 CPU nocona server:

-bash-2.05b$ uname -a
Linux nocona.mysql.com 2.4.21-32.0.1.ELsmp #1 SMP Tue May 17 17:46:36 EDT 2005 x86_64 x86_64 x86_64 GNU/Linux
-bash-2.05b$ cat /proc/cpuinfo
processor       : 0
vendor_id       : GenuineIntel
cpu family      : 15
model           : 3
model name      :                   Intel(R) Xeon(TM) CPU 3.20GHz
physical id     : 204
siblings        : 2
stepping        : 4
cpu MHz         : 3200.218
cache size      : 1024 KB
fpu             : yes
fpu_exception   : yes
cpuid level     : 5
wp              : yes
flags           : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov
pat pse36 clflush dts acpi mmx fxsr sse sse2 ss tm ferr syscall lm
bogomips        : 6383.20
clflush size    : 64
address sizes   : 36 bits physical, 48 bits virtual
power management:

...

processor       : 3
vendor_id       : GenuineIntel
cpu family      : 15
model           : 3
model name      :                   Intel(R) Xeon(TM) CPU 3.20GHz
physical id     : 204
siblings        : 2
stepping        : 4
cpu MHz         : 3200.218
cache size      : 1024 KB
fpu             : yes
fpu_exception   : yes
cpuid level     : 5
wp              : yes
flags           : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov
pat pse36 clflush dts acpi mmx fxsr sse sse2 ss tm ferr syscall lm
bogomips        : 6396.31
clflush size    : 64
address sizes   : 36 bits physical, 48 bits virtual
power management:

First time I was not able to repeat the problem you described:

-bash-2.05b$ bin/mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8 to server version: 5.0.15

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use sqltest;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select count(*) from whitelist;
+----------+
| count(*) |
+----------+
|   200000 |
+----------+
1 row in set (0.00 sec)

mysql> show create table whitelist;
+-----------+----------------------------------------------------------------------------------------------------+
| Table     | Create Table                                 |
+-----------+----------------------------------------------------------------------------------------------------+
| whitelist | CREATE TABLE `whitelist` (
  `example` int(11) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-----------+----------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

BUT! I tried to change the storage engine to InnoDB (why I had not asked about it...), and WE GOT IT FINALLY:

mysql> alter table whitelist engine=InnoDB;
Query OK, 200000 rows affected (2.06 sec)
Records: 200000  Duplicates: 0  Warnings: 0

mysql> truncate whitelist;
Query OK, 200324 rows affected (0.02 sec)

mysql> exit
Bye
-bash-2.05b$ perl test.pl | bin/mysql -f -usqltest -h 10.100.1.182 sqltest &
[2] 5738
-bash-2.05b$ perl test.pl | bin/mysql -f -usqltest -h 10.100.1.182 sqltest &
[3] 5741
-bash-2.05b$ ERROR 1142 (42000) at line 26486: INSERT command denied to user 'sqltest'@'nocona.mysql.com' for table 'whitelist'
ERROR 1142 (42000) at line 31332: INSERT command denied to user 'sqltest'@'nocona.mysql.com' for table 'whitelist'
ERROR 1142 (42000) at line 46394: INSERT command denied to user 'sqltest'@'nocona.mysql.com' for table 'whitelist'
ERROR 1142 (42000) at line 79367: INSERT command denied to user 'sqltest'@'nocona.mysql.com' for table 'whitelist'

[2]-  Done                    perl test.pl | bin/mysql -f -usqltest -h 10.100.1.
182 sqltest
[3]+  Done                    perl test.pl | bin/mysql -f -usqltest -h 10.100.1.
182 sqltest
-bash-2.05b$ bin/mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11 to server version: 5.0.15

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select count(*) from sqltest.whitelist;
+----------+
| count(*) |
+----------+
|   199996 |
+----------+
1 row in set (0.18 sec)

And even with InnoDB I was NOT able to repeat on single CPU machine.

So, this bug is verified just as described by the reporter, with 5.0.15 on Linux on multiple CPU machine ONLY (nocona, for example) and with InnoDB ONLY.

Thank you for your patience, persistence and help on the verification of this bug report!
[9 Nov 2005 20:37] Heikki Tuuri
This hardly can be an InnoDB bug, since InnoDB does not check access rights.

Changing the category to 'MySQL Server'.

Regards,

Heikki
[10 Nov 2005 7:44] Andrejs Dubovskis
In all my tests I had server with  --skip-innodb option.
[10 Nov 2005 9:59] Valeriy Kravchuk
It may have nothing to do with InnoDB as a storage engine, but I was able to repeat only with InnoDB table. 

May be, row level locking "helps" to show the problem immediately, by allowing 2 INSERT's to work simultaneously.
[17 Nov 2005 12:36] Andrejs Dubovskis
Any progress in this problem resolution?
Is a silver subscription to mysql network may speedup it?
[3 Feb 2006 13:26] Arnold Greving
I have a similar problem but with SELECT commands instead of INSERT commands. The following error shows up in my apache error log.

DBD::mysql::st execute failed: SELECT command denied to user '<username>'@'spain-3.bizztravel.intern' for table 'property' at ../../lib/DbConn.pm line 63

The mysql error code of this error is 1142

I'm sure that the access rights are good because were are already a few months in production with mysql 5.

There are five webservers quering three mysql slave servers doing an avarage of 120 queries per second each. So, the high volume of queries matches earlier comments of this bug.

Both web and sql servers have two xeon hyperthreading processors. This is also a similarity with other comments.

There are two Xeon models used for the mysql servers (not in the same server of course)

Model 1:

processor       : 3
vendor_id       : GenuineIntel
cpu family      : 15
model           : 2
model name      : Intel(R) Xeon(TM) CPU 3.20GHz
stepping        : 5
cpu MHz         : 3199.221
cache size      : 512 KB
fdiv_bug        : no
hlt_bug         : no
f00f_bug        : no
coma_bug        : no
fpu             : yes
fpu_exception   : yes
cpuid level     : 2
wp              : yes
flags           : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm pbe cid
bogomips        : 6396.31

And model 2:

processor       : 3
vendor_id       : GenuineIntel
cpu family      : 15
model           : 4
model name      : Intel(R) Xeon(TM) CPU 3.40GHz
stepping        : 3
cpu MHz         : 3401.210
cache size      : 2048 KB
physical id     : 3
siblings        : 2
fdiv_bug        : no
hlt_bug         : no
f00f_bug        : no
coma_bug        : no
fpu             : yes
fpu_exception   : yes
cpuid level     : 5
wp              : yes
flags           : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm pbe lm pni monitor ds_cpl tm2 cid
bogomips        : 6799.36

I'm running a mixed debian install. Debian sid for the mysql client and server and Debian sarge for everything else. My mysql server version is 5.0.18-Debian_6

I made a workaround in my perl module which checks if error 
1142 has occured. If so, it wil redo the query with a maximum of 3 times. I'm not sure if this works because the error happens so iradicly.
[6 Jun 2006 6:29] Sergei Glukhov
Verified with InnoDB & MyISAM table on 'nocona', can't repeat.