WordPress.org

Make WordPress Core

Opened 4 weeks ago

Last modified 3 days ago

#41716 new defect (bug)

Check for utf8mb4 incorrectly assumes auto-truncating indexes

Reported by: straussd Owned by:
Milestone: Awaiting Review Priority: normal
Severity: normal Version: trunk
Component: Database Keywords:
Focuses: Cc:

Description (last modified by danielbachhuber)

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 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 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 Barracuda row format (i.e. COMPRESSED}} or {{{DYNAMIC). This will indicate that the database uses Barracuda for all new tables.

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

Change History (3)

This ticket was mentioned in Slack in #hosting-community by danielbachhuber. View the logs.


3 weeks ago

#2 @danielbachhuber
3 days ago

  • Description modified (diff)

#3 @danielbachhuber
3 days ago

@pento @jdgrimes Do either of you have opinions on the best way to approach this?

Note: See TracTickets for help on using tickets.