[wp-trac] [WordPress Trac] #51740: Test failures using MySQL 8

[WordPress Trac]

#51740: Test failures using MySQL 8
--------------------------+-----------------------------
 Reporter:  desrosj       |       Owner:  SergeyBiryukov
     Type:  defect (bug)  |      Status:  closed
 Priority:  normal        |   Milestone:  6.1
Component:  Database      |     Version:
 Severity:  normal        |  Resolution:  fixed
 Keywords:  has-patch     |     Focuses:
--------------------------+-----------------------------
Changes (by SergeyBiryukov):

 * status:  accepted => closed
 * resolution:   => fixed


Comment:

 In [changeset:"53901" 53901]:
 {{{
 #!CommitTicketReference repository="" revision="53901"
 Query: Cast the meta key to `BINARY` for case-sensitive key comparisons in
 `WP_Meta_Query`.

 This addresses an error on MySQL 8.0.22 or later:
 {{{
 Character set 'utf8mb4_unicode_520_ci' cannot be used in conjunction with
 'binary' in call to regexp_like
 }}}

 From the [https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-22.html
 MySQL 8.0.22 changelog]:
 > Regular expression functions such as `REGEXP_LIKE()` yielded
 inconsistent results with binary string arguments. These functions now
 reject binary strings with an error.
 ([https://bugs.mysql.com/bug.php?id=98951 Bug #98951],
 [https://bugs.mysql.com/bug.php?id=98950 Bug #98950])

 WordPress meta queries use the `BINARY` data type for case-sensitive meta
 key comparisons using regular expressions. By explicitly casting the meta
 key to `BINARY`, we can make sure the values being compared use the same
 character set and produce consistent results.

 The change is covered by existing meta query unit tests: three tests which
 previously failed on MySQL 8.0.22 or later now pass.

 References:
 * [https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-22.html MySQL
 8.0.22 changelog]
 * [https://bugs.mysql.com/bug.php?id=104387 Bug #104387
 CHARACTER_SET_MISMATCH issue with regex comparisons]

 Follow-up to [46188].

 Fixes #51740.
 }}}

-- 
Ticket URL: <https://core.trac.wordpress.org/ticket/51740#comment:12>
WordPress Trac <https://core.trac.wordpress.org/>
WordPress publishing platform