[wp-trac] [WordPress Trac] #58871: support uca14.0.0 collation in database where available

[WordPress Trac]

#58871: support uca14.0.0 collation in database where available
-------------------------------------------------+-------------------------
 Reporter:  danielblack                          |       Owner:  (none)
     Type:  enhancement                          |      Status:  new
 Priority:  normal                               |   Milestone:  Awaiting
                                                 |  Review
Component:  Database                             |     Version:  6.3
 Severity:  normal                               |  Resolution:
 Keywords:  has-patch has-unit-tests needs-      |     Focuses:
  testing                                        |
-------------------------------------------------+-------------------------

Comment (by danielblack):

 At the minium an ALTER TABLE changing collation of an index column will
 result in rebuilding that index. There's nothing explicit about changing
 collation - https://mariadb.com/kb/en/innodb-online-ddl-operations-with-
 the-instant-alter-algorithm so I'd expect a rebuild.

 So what's the implication here, need to pin to the charset/collation of
 the original install?

 There are 10.11 packages for 22.04 - https://mariadb.org/download/?t=repo-
 config&d=22.04+%22jammy%22&v=10.11


 Have a SQL query that's working: https://dbfiddle.uk/-AipUQih for
 detect_charset to pick the optimal:

 {{{
 select * from  INFORMATION_SCHEMA.COLLATION_CHARACTER_SET_APPLICABILITY
  where CHARACTER_SET_NAME IN ('utf8mb4', 'utf8mb3','utf8')
   and  COLLATION_NAME IN ('uca1400_ai_ci','utf8mb4_0900_ai_ci',
 'utf8mb4_unicode_520_ci','utf8mb4_unicode_ci','utf8mb4_general_ci','utf8_general_ci','utf8_general_ci')
   ORDER BY
 FIND_IN_SET(COLLATION_NAME,'uca1400_ai_ci,utf8mb4_unicode_520_ci,utf8mb4_unicode_ci,utf8mb4_general_ci,utf8_general_ci,utf8_general_ci'),
   FIND_IN_SET(CHARACTER_SET_NAME, 'utf8mb4,utf8mb3,utf8') LIMIT 1;
 }}}

 Code (that isn't quite working yet).
 https://github.com/grooverdan/wordpress-
 develop/commit/370444364ca6455cc9dd28e61abdbe8f0b9bd6c5

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