WordPress.org

Make WordPress Core

Opened 3 months ago

Closed 7 weeks ago

#47738 closed defect (bug) (fixed)

Misleading database server version shown when using proxySQL

Reported by: wpfed Owned by: SergeyBiryukov
Milestone: 5.3 Priority: normal
Severity: minor Version: 5.2.2
Component: Site Health Keywords: has-patch commit
Focuses: Cc:
PR Number:

Description

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

Attachments (2)

47738.patch (1.6 KB) - added by Clorith 3 months ago.
47738.2.patch (1.1 KB) - added by Clorith 7 weeks ago.

Download all attachments as: .zip

Change History (17)

#1 follow-up: @Clorith
3 months ago

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?

#2 in reply to: ↑ 1 @wpfed
3 months ago

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
3 months ago

#3 @Clorith
3 months ago

  • Keywords has-patch commit added
  • Milestone changed from Awaiting Review to 5.2.3

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.

#4 @SergeyBiryukov
3 months ago

  • Owner set to SergeyBiryukov
  • Resolution set to fixed
  • Status changed from new to closed

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.

#5 @SergeyBiryukov
3 months ago

  • Keywords fixed-major added
  • Resolution fixed deleted
  • Status changed from closed to reopened

Reopening for 5.2.3 consideration.

This ticket was mentioned in Slack in #core by jeffpaul. View the logs.


2 months ago

#7 @Clorith
2 months ago

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).

#8 @JeffPaul
2 months ago

  • Keywords dev-feedback 2nd-opinion added

This ticket was mentioned in Slack in #core by marybaum. View the logs.


2 months ago

#10 @marybaum
2 months ago

  • Milestone changed from 5.2.3 to 5.3

#11 @SergeyBiryukov
2 months ago

  • Keywords fixed-major removed

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

This ticket was mentioned in Slack in #core-site-health by sergey. View the logs.


7 weeks ago

@Clorith
7 weeks ago

#14 @Clorith
7 weeks ago

  • Keywords dev-feedback 2nd-opinion removed

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.

#15 @SergeyBiryukov
7 weeks ago

  • Resolution set to fixed
  • Status changed from reopened to closed

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.

Note: See TracTickets for help on using tickets.