[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        |  Resolution:
 Keywords:                |     Focuses:
--------------------------+------------------------------
Description changed by danielbachhuber:

Old description:

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

New description:

 The current `Tests_dbDelta::test_truncated_index` test (from #31869,
 r32108) 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#comment:2>
WordPress Trac <https://core.trac.wordpress.org/>
WordPress publishing platform