[wp-trac] [WordPress Trac] #41716: Check for utf8mb4 incorrectly assumes auto-truncating indexes

[WordPress Trac]

#41716: Check for utf8mb4 incorrectly assumes auto-truncating indexes
--------------------------+-----------------------------
 Reporter:  straussd      |      Owner:
     Type:  defect (bug)  |     Status:  new
 Priority:  normal        |  Milestone:  Awaiting Review
Component:  Database      |    Version:  trunk
 Severity:  normal        |   Keywords:
  Focuses:                |
--------------------------+-----------------------------
 The current test makes an incorrect assumption about MySQL/MariaDB
 configuration. Specifically, it assumes that presence of utf8mb4
 capabilities means that either Barracuda (the necessary InnoDB file format
 to pass the test) is the default file format for all newly created tables
 (or that the database silently truncates indexes).

 This is not always the case for MariaDB 10.0 or 10.1 and is probably an
 issue for any MySQL<5.7.7 or MariaDB<10.2 with the following
 configuration:

 {{{
 innodb_file_per_table=true
 innodb_file_format=Barracuda
 innodb_large_prefix=true
 }}}

 Enabling {{{innodb_large_prefix}}} (which depends on the other two
 options) causes the database to stop silently truncating long indexes, but
 the database also won't simply switch to Barracuda for long indexes to
 make creation succeed, either.

 Despite "innodb_file_format" being [https://mariadb.com/kb/en/mariadb
 /xtradbinnodb-server-system-variables/#innodb_file_format documented in
 MariaDB] as "File format for new InnoDB tables," it only takes effect for
 tables explicitly created with a Barracuda-based row format. (The
 [https://dev.mysql.com/doc/refman/5.6/en/innodb-
 parameters.html#sysvar_innodb_file_format MySQL documentation] is better
 because it doesn't suggest that it applies to all new tables.)

 I see two possible fixes:

 (1) Expand the criteria for markTestSkipped to include creating a table
 and verifying that it uses a [https://dev.mysql.com/doc/refman/5.7/en
 /innodb-file-format.html Barracuda row format] (i.e. {{{COMPRESSED}} or
 {{{DYNAMIC}}}). This will indicate that the database uses Barracuda for
 all new tables.

 (2) [https://dev.mysql.com/doc/refman/5.7/en/innodb-row-format-
 specification.html Explicitly specify the row format] for the table
 creation test itself. This would require adding {{{ROW_FORMAT=DYNAMIC}}}
 to the existing {{{CREATE TABLE}}}. This would have no effect on the
 latest MySQL and MariaDB releases, which default to that anyway.

 In a related issue, the test is misnamed based on the behavior it tests.
 In older MySQL releases (and with no long index support enabled), the test
 succeeds if the database silently truncates the long index to just a
 prefix. However, in the latest releases, no truncation occurs at all. The
 reason the test passes in MySQL 5.7 and MariaDB 10.2 is a new default of
 long index support and Barracuda. So, at a minimum, the test should be
 renamed.

--
Ticket URL: <https://core.trac.wordpress.org/ticket/41716>
WordPress Trac <https://core.trac.wordpress.org/>
WordPress publishing platform