I inadvertently hijacked a separate issue regarding MySQL GRANTs when using the Pack module:

#1555398: MySQL GRANTs aren't given for multiple servers in Web Pack

However, the edge case that I discovered is actually separate from that one.

In my setup, I had two webservers (in a Pack) and those two webservers were also running MySQL database servers (all on one machine). The database servers were running MySQL-MMM, with a third separate server running the monitoring agent, which is responsible for re-allocating a floating IP on the LAN to whichever database server is considered the 'writer' (there is only ever one writer, e.g it is an active/passive MySQL cluster).

I found an edge case when the webserver/database server has the 'floating IP' allocated to it (as seen with ip addr sh - note that the floating IP is not allocated to an interface in /etc/network/interfaces). In Provision, we make a MySQL test connection from the webserver that is deliberately designed to fail. My webserver made the MySQL connection from its floating IP address instead of its 'real' eth0 interface (both are on the same /24).

This resulted in Provision's preg_match of the IP address sent back in the MySQL error message, to detect the floating IP address and use that for the GRANT. But that meant that that webserver's real IP didn't get a GRANT, meaning sites running on that webserver could not bootstrap their databases since the bootstrap would correctly use the 'real' IP.

In short: it seems there are cases whereby if the webserver has more than one IP allocated to it, there is a chance that the MySQL client binary might use the second IP instead of the 'main' one, to instantiate the MySQL test connection. There is no way to force an interface to bind to - note that 'mysql --bind-address' only works for users using the special MySQL NDB cluster and not regular MySQL server, according to the manpage.

We should take extra care on how we 'detect' the IP of the webserver. I am not sure that relying on what the database server sees as the correct IP, is the right way to do it. It is also quite feasible that a webserver might have multiple IPs on the same LAN. We already save an array of 'ip_addresses' to the server's context - I think we should just iterate over those and do the GRANTs, rather than any of this test connection 'failed to fail' stuff. It is up to the end user to ensure that those IP addresses they've allocated to the server node, do actually work.


anarcat’s picture

One thing we should consider is having the grant be given on % instead of guessing around like this...

ergonlogic’s picture

Version: 6.x-1.8 » 7.x-3.x-dev
Status: Active » Postponed (maintainer needs more info)

Is this still an issue in Aegir 3.x?

Jon Pugh’s picture

This is very much still an issue, and is coming up today when using Docker.

Sometimes the detected IP address changes, which is causing access denied on container restarts.

See #2794915: Refactor database permissions granting to allow scalability to arbitrary web servers.