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