[MDEV-639] LP:966254 - connection is refused while excuting "flush privileges" - JIRA

Details

Type: Bug
Status: Open
Priority: Minor
Resolution: Unresolved
Affects Version/s: 5.1.67, 5.2.14, 5.3.12, 5.5.36, 10.0.10
Fix Version/s: 10.0, 5.5
Component/s: None
Labels:
- Launchpad

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://bugs.launchpad.net/percona-server/+bug/940404

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_host(const char *host, const char *ip)
1496 {
1497 if (allow_all_hosts)         
1498 return 0;
1499 VOID(pthread_mutex_lock(&acl_cache->lock));
1500
1501 if ((host && hash_search(&acl_check_hosts,(uchar*) host,strlen(host))) ||
1502 (ip && hash_search(&acl_check_hosts,(uchar*) ip, strlen(ip))))
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_mutex_lock(&acl_cache->lock));
if (allow_all_hosts){
   VOID(pthread_mutex_unlock(&acl_cache->lock));         	
   return 0;
}

Gliffy Diagrams

Attachments

Options
- Sort By Name
- Sort By Date
- Ascending
- Descending
- Thumbnails
- List
- Download All

Attachments

LPexportBug966254_flush_priv.diff
0.6 kB
2012-03-28 10:35
LPexportBug966254.xml
13 kB
2012-10-03 11:32

Activity

Ascending order - Click to sort in descending order

Hide

Permalink

Rasmus Johansson added a comment - 2012-03-28 09:15

Re: connection is refused while excuting "flush privileges"
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
……
……
……

Show

Rasmus Johansson added a comment - 2012-03-28 09:15 Re: connection is refused while excuting "flush privileges" 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 …… …… ……

Hide

Permalink

Rasmus Johansson added a comment - 2012-03-28 10:35

a simple patch for this bug.
based on Percona 5.5.18

flush_priv.diff
LPexportBug966254_flush_priv.diff

Show

Rasmus Johansson added a comment - 2012-03-28 10:35 a simple patch for this bug. based on Percona 5.5.18 flush_priv.diff LPexportBug966254_flush_priv.diff

Hide

Permalink

Rasmus Johansson added a comment - 2012-03-28 10:35

Re: connection is refused while excuting "flush privileges"
a simple patch for this bug.
based on Percona 5.5.18

Show

Rasmus Johansson added a comment - 2012-03-28 10:35 Re: connection is refused while excuting "flush privileges" a simple patch for this bug. based on Percona 5.5.18

Hide

Permalink

Rasmus Johansson added a comment - 2012-07-30 02:23

Re: connection is refused while excuting "flush privileges"
http://bugs.mysql.com/bug.php?id=63178

this bug was fixed in MySQL 5.7.0

Noted in 5.7.0 changelog.

The server refused client connections while executing FLUSH
PRIVILEGES

Show

Rasmus Johansson added a comment - 2012-07-30 02:23 Re: connection is refused while excuting "flush privileges" http://bugs.mysql.com/bug.php?id=63178 this bug was fixed in MySQL 5.7.0 Noted in 5.7.0 changelog. The server refused client connections while executing FLUSH PRIVILEGES

Hide

Permalink

Rasmus Johansson added a comment - 2012-09-11 04:01

Re: connection is refused while excuting "flush privileges"
./flush.sh | grep 'not allowed to '
ERROR 1130 (HY000): Host 'localhost' is not allowed to connect to this MySQL server
ERROR 1130 (HY000): Host 'localhost' is not allowed to connect to this MySQL server
ERROR 1130 (HY000): Host 'localhost' is not allowed to connect to this MySQL server
ERROR 1130 (HY000): Host 'localhost' is not allowed to connect to this MySQL server
ERROR 1130 (HY000): Host 'localhost' is not allowed to connect to this MySQL server
ERROR 1130 (HY000): Host 'localhost' is not allowed to connect to this MySQL server
ERROR 1130 (HY000): Host 'localhost' is not allowed to connect to this MySQL server
ERROR 1130 (HY000): Host 'localhost' is not allowed to connect to this MySQL server
ERROR 1130 (HY000): Host 'localhost' is not allowed to connect to this MySQL server
ERROR 1130 (HY000): Host 'localhost' is not allowed to connect to this MySQL server
ERROR 1130 (HY000): Host 'localhost' is not allowed to connect to this MySQL server
ERROR 1130 (HY000): Host 'localhost' is not allowed to connect to this MySQL server
ERROR 1130 (HY000): Host 'localhost' is not allowed to connect to this MySQL server
ERROR 1130 (HY000): Host 'localhost' is not allowed to connect to this MySQL server
ERROR 1130 (HY000): Host 'localhost' is not allowed to connect to this MySQL server
ERROR 1130 (HY000): Host 'localhost' is not allowed to connect to this MySQL server
ERROR 1130 (HY000): Host 'localhost' is not allowed to connect to this MySQL server
ERROR 1130 (HY000): Host 'localhost' is not allowed to connect to this MySQL server
ERROR 1130 (HY000): Host 'localhost' is not allowed to connect to this MySQL server
ERROR 1130 (HY000): Host 'localhost' is not allowed to connect to this MySQL server
ERROR 1130 (HY000): Host 'localhost' is not allowed to connect to this MySQL server
ERROR 1130 (HY000): Host 'localhost' is not allowed to connect to this MySQL server
ERROR 1130 (HY000): Host 'localhost' is not allowed to connect to this MySQL server
ERROR 1130 (HY000): Host 'localhost' is not allowed to connect to this MySQL server
ERROR 1130 (HY000): Host 'localhost' is not allowed to connect to this MySQL server
ERROR 1130 (HY000): Host 'localhost' is not allowed to connect to this MySQL server
ERROR 1130 (HY000): Host 'localhost' is not allowed to connect to this MySQL server
ERROR 1130 (HY000): Host 'localhost' is not allowed to connect to this MySQL server
ERROR 1130 (HY000): Host 'localhost' is not allowed to connect to this MySQL server
ERROR 1130 (HY000): Host 'localhost' is not allowed to connect to this MySQL server
ERROR 1130 (HY000): Host 'localhost' is not allowed to connect to this MySQL server
ERROR 1130 (HY000): Host 'localhost' is not allowed to connect to this MySQL server
ERROR 1130 (HY000): Host 'localhost' is not allowed to connect to this MySQL server
ERROR 1130 (HY000): Host 'localhost' is not allowed to connect to this MySQL server
ERROR 1130 (HY000): Host 'localhost' is not allowed to connect to this MySQL server

Show

Rasmus Johansson added a comment - 2012-09-11 04:01 Re: connection is refused while excuting "flush privileges" ./flush.sh | grep 'not allowed to ' ERROR 1130 (HY000): Host 'localhost' is not allowed to connect to this MySQL server ERROR 1130 (HY000): Host 'localhost' is not allowed to connect to this MySQL server ERROR 1130 (HY000): Host 'localhost' is not allowed to connect to this MySQL server ERROR 1130 (HY000): Host 'localhost' is not allowed to connect to this MySQL server ERROR 1130 (HY000): Host 'localhost' is not allowed to connect to this MySQL server ERROR 1130 (HY000): Host 'localhost' is not allowed to connect to this MySQL server ERROR 1130 (HY000): Host 'localhost' is not allowed to connect to this MySQL server ERROR 1130 (HY000): Host 'localhost' is not allowed to connect to this MySQL server ERROR 1130 (HY000): Host 'localhost' is not allowed to connect to this MySQL server ERROR 1130 (HY000): Host 'localhost' is not allowed to connect to this MySQL server ERROR 1130 (HY000): Host 'localhost' is not allowed to connect to this MySQL server ERROR 1130 (HY000): Host 'localhost' is not allowed to connect to this MySQL server ERROR 1130 (HY000): Host 'localhost' is not allowed to connect to this MySQL server ERROR 1130 (HY000): Host 'localhost' is not allowed to connect to this MySQL server ERROR 1130 (HY000): Host 'localhost' is not allowed to connect to this MySQL server ERROR 1130 (HY000): Host 'localhost' is not allowed to connect to this MySQL server ERROR 1130 (HY000): Host 'localhost' is not allowed to connect to this MySQL server ERROR 1130 (HY000): Host 'localhost' is not allowed to connect to this MySQL server ERROR 1130 (HY000): Host 'localhost' is not allowed to connect to this MySQL server ERROR 1130 (HY000): Host 'localhost' is not allowed to connect to this MySQL server ERROR 1130 (HY000): Host 'localhost' is not allowed to connect to this MySQL server ERROR 1130 (HY000): Host 'localhost' is not allowed to connect to this MySQL server ERROR 1130 (HY000): Host 'localhost' is not allowed to connect to this MySQL server ERROR 1130 (HY000): Host 'localhost' is not allowed to connect to this MySQL server ERROR 1130 (HY000): Host 'localhost' is not allowed to connect to this MySQL server ERROR 1130 (HY000): Host 'localhost' is not allowed to connect to this MySQL server ERROR 1130 (HY000): Host 'localhost' is not allowed to connect to this MySQL server ERROR 1130 (HY000): Host 'localhost' is not allowed to connect to this MySQL server ERROR 1130 (HY000): Host 'localhost' is not allowed to connect to this MySQL server ERROR 1130 (HY000): Host 'localhost' is not allowed to connect to this MySQL server ERROR 1130 (HY000): Host 'localhost' is not allowed to connect to this MySQL server ERROR 1130 (HY000): Host 'localhost' is not allowed to connect to this MySQL server ERROR 1130 (HY000): Host 'localhost' is not allowed to connect to this MySQL server ERROR 1130 (HY000): Host 'localhost' is not allowed to connect to this MySQL server ERROR 1130 (HY000): Host 'localhost' is not allowed to connect to this MySQL server

Hide

Permalink

Rasmus Johansson added a comment - 2012-09-11 06:39

Launchpad bug id: 966254

Show

Rasmus Johansson added a comment - 2012-09-11 06:39 Launchpad bug id: 966254

Hide

Permalink

Elena Stepanova added a comment - 2014-04-14 19:53

I couldn't reproduce the actual problem with refused connections; but based on the code, if it is a bug, it still exists everywhere, so I'm setting the versions accordingly.

Show

Elena Stepanova added a comment - 2014-04-14 19:53 I couldn't reproduce the actual problem with refused connections; but based on the code, if it is a bug, it still exists everywhere, so I'm setting the versions accordingly.

People

Assignee:

Unassigned

Reporter:

Hickey

Votes:

0 Vote for this issue

Watchers:

1 Start watching this issue

Dates

Created:

2012-08-09 07:58

Updated:

2014-06-16 13:27

Agile

View on Board