MySQL Bugs: #93623: ER_SPECIFIC_ACCESS_DENIED_ERROR for user with proper access

Bug #93623	ER_SPECIFIC_ACCESS_DENIED_ERROR for user with proper access
Submitted:	14 Dec 2018 21:42	Modified:	17 Dec 2018 17:29
Reporter:	Rick Reynolds	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S2 (Serious)
Version:	5.5.*	OS:	Any
Assigned to:	Bogdan Kecman	CPU Architecture:	Any
Tags:	ER_SPECIFIC_ACCESS_DENIED_ERROR

Description:
I'm often (but not always) getting ER_SPECIFIC_ACCESS_DENIED_ERROR error when running SHOW SLAVE STATUS with a user that does indeed have the proper access. This is from a thread that monitors the slave. When the thread starts up it checks and gets a valid response but usually after its been running for a while it starts getting ER_SPECIFIC_ACCESS_DENIED_ERROR and when that starts it usually happens until the thread is restarted.

Does ER_SPECIFIC_ACCESS_DENIED_ERROR have multiple meanings? Is it trying to tell me to call SHOW SLAVE STATUS less?

*Note this thread is either in NodeJS, Python or C++ depending on the process. Its not language or platform specific.

How to repeat:
Using any programming language,  execute  "slow slave status" every 15 seconds with a user with the proper roles.  After about 10 - 30 minutes  it will start failing with ER_SPECIFIC_ACCESS_DENIED_ERROR

I cannot reproduce this and we'r doing it for ages so there must be something on your end. You are sure your client is not arriving from a new IP being seen as different user?

all best
Bogdan

Additionally, you set category to "MySQL Cluster: Replication", and version to 5.5.* - There is no MySQL Cluster 5.5 in the wild, what exactly are you having issues with?

kind regards
Bogdan

Sorry I wasn't specific enough.   First  I chose Replication because this is a slave issue (isn't slaving considered replication?

This issue happens when both the master and the slave are under heavy load.  The show slave status call is made on the slave  while  the slave is serving read requests and handling replication from the master which is handling LOTS of writes.  I've noticed this will start when the slave is at or close to 100% CPU usage (spiked, not continuous).  Even when load drops back down to ~2% CPU usage show slave status keeps responding with  ER_SPECIFIC_ACCESS_DENIED_ERROR

> First  I chose Replication because this is a slave issue 
> (isn't slaving considered replication?

Yes, replication part is not a problem, but how is this related to MySQL Cluster?!

https://www.mysql.com/products/cluster/

I assume this is regular MySQL Community Server 5.5.x in replication?

> This issue happens when both the master and the slave are under heavy load.
> The show slave status call is made on the slave  while  the slave is serving read
> requests and handling replication from the master which is handling
> LOTS of writes.  I've noticed this will start when the slave is at or close to
> 100% CPU usage (spiked, not continuous).  Even when load drops back down to ~2%
> CPU usage show slave status keeps responding with  ER_SPECIFIC_ACCESS_DENIED_ERROR

I cannot reproduce this with 
 5.5.62
 5.6.42
 5.7.24
 8.0.13

tried both statement, mixed and row based, both single and multi threaded slave and I cannot reproduce the problem. Are you sure you are using Oracle made binaries of your MySQL Server?

all best
Bogdan

Sorry about the classification. This has nothing to do with clustering etc.  

Yes.  MySQL server is Oracle build AFAIK. I was installed on  Ubuntu 14.4 with apt-get install mysql-server.  on fresh install of Ubuntu with no other non-default packages installed  This occurs on two separate databases on different servers.

Did you attempt to reproduce under heavy load?

Hi,

> Sorry about the classification. This has nothing to do with clustering etc.  

No worries, I changed the Category to "MySQL Server: Replication"

> MySQL server is Oracle build AFAIK. I was installed on 
> Ubuntu 14.4 with apt-get install mysql-server.
> on fresh install of Ubuntu with no other non-default
> packages installed  This occurs on two separate databases
> on different servers.

I'm not Ubuntu aficionado (Oracle Linux is IMO way better solution for servers then anything Ubuntu based) but AFAIK regular Ubuntu apt-get does not install binaries created by Oracle but some community made binaries (that we can't guarantee are ok). Here: https://dev.mysql.com/downloads/repo/apt/ you can see repo that you can add to your system (both Ubuntu and Debian) that will allow you to install Oracle made binaries to your system. So please make sure you are using either a MySQL installed from these repositories or you download and manually install tar.gz from https://dev.mysql.com/downloads/mysql/

Distro made binaries are "probably" ok, but I cannot guarantee they are ok and I can't go test everyone's binary for every possible bug report. In order to verify bug report it has to be reproduced on oracle binary, and latest current version of one (so in your case 5.5.62 - https://dev.mysql.com/get/Downloads/MySQL-5.5/mysql-5.5.62-linux-glibc2.12-x86_64.tar.gz or https://dev.mysql.com/get/Downloads/MySQL-5.5/mysql-5.5.62-linux-glibc2.12-i686.tar.gz )

> Did you attempt to reproduce under heavy load?

Of course, single core slave, 100% CPU, load 20+. Only difference is some versions (as I said I tried lates 5.5, 5.6, 5.7 and 8.0) I tried on Fedora Linux and some I tried on Oracle Linux 7, all with Oracle binaries.

all best
Bogdan