Misleading database server version shown when using proxySQL

[wpfed]

Hi,

My current hosting company uses a proxySQL instance between the site and the actual DB servers, this causes site-health checkup tool to report the proxySQL server version as 5.5.30 when the actual DB MySQL servers(backing cluster) are version 5.7.26.

Is this correct behavior for the site-health checkup tool or misleading?

This information is found in WordPress admin dashboard under tools->Site Health->Info->Database

[Clorith]

Hi there, and welcome to WordPress trac!

Is this only being misreported on the Info page, or is it also incorrect on the Site Health page that runs the automated checks against your site as well for you?

[wpfed]

Replying to Clorith:

Hi there, and welcome to WordPress trac!

Is this only being misreported on the Info page, or is it also incorrect on the Site Health page that runs the automated checks against your site as well for you?

Hi, thanks!

Only on the Info Page, I have no error relating to MySQL version on the status page.
The info page also lists: Database host mysql_proxy

Is their a check done to disregard version number if mysql_proxy is detected?

[Clorith]

I see the version check is doing it in two different ways here, and it looks like the one done for the debug information lands on the proxy server (relying on the connection details provided by mysqli_get_server_info, which would be the proxy, while the site health section actually performs an SQL query of SELECT VERSION(), so that's the actual backend server.

After some investigating, it seems like the debug page is the correct approach here, as doing version verification should be performed using native features when possible.

These areas are also doing the checks within the code, and not using the WP wrapper, we'll resolve this by moving both checks over to using WPDB::db_version() (which uses mysqli_get_server_info() behind the scenes at this time, and is what anything using WP should be checking against).

47738.patch​ changes this behavior in both places. In your case this does mean you'll receive a warning about a version mismatch in the site health module as well in the future, unless the proxy server is configured to report a version that matches the back-end server.

[SergeyBiryukov]

In 45691:

Site Health: Consistently use wpdb::db_version() for checking the database server version, instead of partically replicating its functionality.

Props Clorith, wpfed.
Fixes #47738.

[SergeyBiryukov]

Reopening for 5.2.3 consideration.

[Clorith]

So I just pushed this change to the Health Check plugin, which has shown a possible issue with this implementation.

At some point, I don't know when, PHP's native drivers for MySQL seems to have stopped stripping a prefix-hack away from the MariaDB version string, making the use of $wpdb->db_version() give incorrect outputs in a lot of cases.

I'm not sure if the right approach is to revert this and use the manual approach like before, but in both locations, or to make changes to the db_version() function call. (I see there's quite a few tickets about the latter). The most accurate approach of course being to perform a SQL query, but that's not reliable if an SQL connection doesn't exist (for the core context at least).

[SergeyBiryukov]

Keeping open for now to address the concerns in comment:7.

[SergeyBiryukov]

Related: #27703

[Clorith]

47738.2.patch​ swaps this around, and changes everything to use the SELECT VERSION() query instead for accurate readings due to the MariaDB version declaration potentially causing issues in production environments.

[SergeyBiryukov]

In 45904:

Site Health: Standardize on SELECT VERSION() query for checking the database server version, due to the MariaDB version declaration potentially causing issues in production environments.

Partially reverts [45691].

Props Clorith.
Fixes #47738.