[wp-trac] [WordPress Trac] #49364: dbDelta() should not change display width for integer data types on MySQL 8.0.17+

[WordPress Trac]

#49364: dbDelta() should not change display width for integer data types on MySQL
8.0.17+
-------------------------------------------------+-------------------------
 Reporter:  SergeyBiryukov                       |       Owner:
                                                 |  SergeyBiryukov
     Type:  defect (bug)                         |      Status:  closed
 Priority:  normal                               |   Milestone:  6.1
Component:  Database                             |     Version:
 Severity:  normal                               |  Resolution:  fixed
 Keywords:  has-patch needs-testing early        |     Focuses:
  needs-testing-info                             |
-------------------------------------------------+-------------------------
Changes (by SergeyBiryukov):

 * owner:  (none) => SergeyBiryukov
 * status:  new => closed
 * resolution:   => fixed


Comment:

 In [changeset:"53897" 53897]:
 {{{
 #!CommitTicketReference repository="" revision="53897"
 Database: Ignore display width for integer data types in `dbDelta()` on
 MySQL 8.0.17 or later.

 MySQL 8.0.17 deprecated the display width attribute for integer data
 types:
 > As of MySQL 8.0.17, the `ZEROFILL` attribute is deprecated for numeric
 data types, as is the display width attribute for integer data types. You
 should expect support for `ZEROFILL` and display widths for integer data
 types to be removed in a future version of MySQL. Consider using an
 alternative means of producing the effect of these attributes. For
 example, applications can use the `LPAD()` function to zero-pad numbers up
 to the desired width, or they can store the formatted numbers in `CHAR`
 columns.

 In practice, this means that display width is removed for integer types
 when creating a table:
 * `BIGINT(20)` → `BIGINT`
 * `INT(11)` → `INT`
 * `MEDIUMINT(9)` → `MEDIUMINT`
 * `SMALLINT(6)` → `SMALLINT`
 * `TINYINT(4)` → `TINYINT`

 Note: This only applies specifically to MySQL 8.0.17 or later. In MariaDB,
 display width for integer types is still available and expected.

 This commit ensures that `dbDelta()`, which relies on the `DESCRIBE` SQL
 command to get the existing table structure and field types, when running
 on MySQL 8.0.17 or later, does not unnecessarily attempt to convert
 `BIGINT` fields back to `BIGINT(20)`, `INT` back to `INT(11)`, etc. When
 comparing the field type in the query with the existing field type, if
 display width is the only difference, it can be safely ignored to match
 MySQL behavior.

 The change is covered by existing `dbDelta()` unit tests:
 * A test for not altering `wp_get_db_schema()` queries on an existing
 install using MySQL 8.0.17+ now passes.
 * More than twenty tests which previously failed on PHP 8.0.x + MariaDB
 due to incorrect expectations, caused by MariaDB version reporting not
 being consistent between PHP versions, now pass.

 References:
 * [https://dev.mysql.com/doc/refman/8.0/en/numeric-type-attributes.html
 MySQL: Nymeric Type Attributes]
 * [https://mariadb.com/kb/en/data-types-numeric-data-types/ MariaDB:
 Numeric Data Types]

 Follow-up to [1575], [18899], [37525], [47183], [47184].

 Props SergeyBiryukov, pbearne, leewillis77, JavierCasares, desrosj,
 costdev, johnbillion.
 Fixes #49364. See #51740.
 }}}

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