MariaDB privileges IPv6 to resolve localhost

Virtualmin
1

When assigning privileges to a MySQL database user, this is done:

GRANT ALL PRIVILEGES ON databasename.* TO ‘username’@‘localhost’;

when IPv6 is enabled and prioritized (or IPv4 disabled), as most modern systems will have (Happy Eyeballs), for example PHP will resolve to ::1 and you will get errors like HY000/2002:

Warning: mysqli_real_connect(): (HY000/2002): No such file or directory in /home/username/public_html/wp-includes/class-wpdb.php

when you do:

GRANT ALL PRIVILEGES ON databasename.* TO ‘username’@‘::1’;
FLUSH PRIVILEGES;

This solves that resolving issue. It would be good if Virtualmin would by default assign (and the other CRUDs; to keep IPv4 in sync) these IPv6 user privileges (until MySQL handles “localhost” better with an upstream solution; but that might not happen, as it would break setups that rely on the distinction).

The goal would be that users can use any of these 4 valid options and get the same result: ::1, 127.0.0.1, ::ffff:127.0.0.1 (IPv4 expressed as IPv6), and localhost.

PS: your “cat /etc/hosts” file should of course also resolve it:

::1 localhost
127.0.0.1 localhost

the order matters in older systems (newer systems often comply with RFC 8305).

PPS: If this does not solve your problem, it might be related to the socket creation. Look into mysqli.default_socket / pdo_mysql.default_socket.

2

See also related issues like Wordpress Error establishing DB if using "localhost"

3

So this only applies to WordPress DB or which specific OS or only MySQL (and not MariaDB) ? as I do not see this on any of my VMs (all of which not have IPv6)

4

This applies to PHP resolving a connection to MySQL/MariaDB in general, not WP specific (but I used it as an example, as that question/problem is posted on many forums without a proper root-cause solution (the only recommendations generally given are to either add a specific GRANT or to just change the hostname variable for the database connection).

I’ve posted here as well to see what the current thinking of the community is on where/how to best solve this. https://bugs.mysql.com/bug.php?id=117236

5

Tx for the clarification - though still not sure why I have not come across the problem I now have 6 VM across several regions using Ubuntu and Debian OS with IPv6 active and MariaDB - and not had to make any such change. :magic_wand:

6

Neither have I