connection is refused while excuting "flush privileges"
| Affects | Status | Importance | Assigned to | Milestone | ||
|---|---|---|---|---|---|---|
| MariaDB |
New
|
Undecided
|
Unassigned | |||
| MySQL Server |
Unknown
|
Unknown
|
||||
| Percona Server moved to https://jira.percona.com/projects/PS | Status tracked in 5.7 | |||||
| 5.1 |
Won't Fix
|
Medium
|
Unassigned | |||
| 5.5 |
Triaged
|
Medium
|
Unassigned | |||
| 5.6 |
Triaged
|
Medium
|
Unassigned | |||
| 5.7 |
Fix Released
|
Medium
|
Unassigned | |||
Bug Description
In such scenario, we hit the infinite looping of 'flush privileges' events between master and
slave(it's also a master but read-only), which caused the app connections periodic error
of "host is not allowed to connect to this MySQL server".
As for the "flush privileges" passing between master and slave, back and forth, could refer to
https:/
The root problems caused the connection issue is due to the logic defect which explained
as below:
For 'flush privileges', acl_reload(）would be called, and then acl_load() is called. The
global variable allow_all_hosts will set to 0 under the lock, as well as the modification
for acl_check_hosts.
But when client connections to server, acl_check_hosts() will be called, which logic is
list below:
bool acl_check_
1496 {
1497 if (allow_all_hosts)
1498 return 0;
1499 VOID(pthread_
1500
1501 if ((host && hash_search(
1502 (ip && hash_search(
1503 {
in line 1497,we find that allow_all_hosts is used without any lock, which caused
inconsistent between allow_all_hosts and acl_check_hosts.
So there is a race risk, and in some special case which like we hit, that might be a
problem.
How to repeat:
NoN
Suggested fix:
check allow_all_hosts under the lock, just like this:
VOID(pthread_
if (allow_all_hosts){
VOID(
return 0;
}
| yinfeng (yinfeng-zwx) wrote | #1 |
| yinfeng (yinfeng-zwx) wrote | #2 |
| Changed in percona-server: | |
| importance: | Undecided → Medium |
| yinfeng (yinfeng-zwx) wrote | #3 |
| Changed in maria: | |
| milestone: | none → 5.1 |
| Changed in percona-server: | |
| status: | New → Confirmed |
| Raghavendra D Prabhu (raghavendra-prabhu) wrote | #4 |
| tags: | added: contribution |
| Sergei Golubchik (sergii) wrote | #5 |
| tags: | added: upstream |
| Shahriyar Rzayev (rzayev-sehriyar) wrote | #6 |
this bug is very easy to repeat
1.create user xx1
root@(none) 05:10:33>grant all on *.* to xx1@'127.0.0.1' identified by '123';
Query OK, 0 rows affected (0.00 sec)
2. run sh:
$cat flush_pri.sh
#!/bin/sh
for i in {1..100000}; do
mysql -uroot -e 'flush privileges' &
mysql -uxx1 -p123 -h127.0.0.1 -P3320 -e "select user()"
done
3.
$sh flush_pri.sh | grep 'host'
ERROR 1130 (HY000): Host '127.0.0.1' is not allowed to connect to this MySQL server
ERROR 1130 (HY000): Host '127.0.0.1' is not allowed to connect to this MySQL server
ERROR 1130 (HY000): Host '127.0.0.1' is not allowed to connect to this MySQL server
ERROR 1130 (HY000): Host '127.0.0.1' is not allowed to connect to this MySQL server
ERROR 1130 (HY000): Host '127.0.0.1' is not allowed to connect to this MySQL server
ERROR 1130 (HY000): Host '127.0.0.1' is not allowed to connect to this MySQL server
ERROR 1130 (HY000): Host '127.0.0.1' is not allowed to connect to this MySQL server
ERROR 1130 (HY000): Host '127.0.0.1' is not allowed to connect to this MySQL server
……
……
……