[wp-trac] [WordPress Trac] #36748: Updating tables to utf8mb4 causes some columns to change type unexpectedly

[WordPress Trac]

#36748: Updating tables to utf8mb4 causes some columns to change type unexpectedly
-----------------------------+-----------------------------
 Reporter:  tollmanz         |      Owner:
     Type:  defect (bug)     |     Status:  new
 Priority:  normal           |  Milestone:  Awaiting Review
Component:  Upgrade/Install  |    Version:  4.2
 Severity:  normal           |   Keywords:
  Focuses:                   |
-----------------------------+-----------------------------
 When updating WordPress to a version greater than 4.2 from a version less
 than 4.2 and invoking the utf8mb4 DB upgrade, it causes some text column
 types to unexpectedly change in order to accommodate the space for the
 extra bytes. This change is unintended and causes the DB to get out of
 sync with the specified schema since the `dbDelta` update runs prior to
 the utf8mb4 updates.

 Once this has happened, WordPress will fix the issue once the next update
 occurs (e.g., if the issue is introduced updating 4.1.10 to 4.4.2, the
 correct schema will be reapplied when updating to 4.5.1). This is a major
 annoyance as, in my experience, this schema correction is hanging the DB
 for ~6 mins to perform this update.

 Mathia Bynens documented this issue on an excellent post on upgrading
 MySQL for utf8mb4 support (https://mathiasbynens.be/notes/mysql-utf8mb4):

 {{{
 Also of note when converting a table to utf8mb4 from utf8: if you have a
 column that is TEXT, MySQL
  will automagically promote that column to MEDIUMTEXT to accomodate for
 the additional storage
  space needed for the new encoding. I only tested this with TEXT and
 assume it is similar with
 TINYTEXT etc.
 }}}

 For WordPress, I've found the following issues:

 {{{
 | column                     | should be | changed to |
 | -------------------------- | --------- | ---------- |
 | wp_posts.post_title        | text      | mediumtext |
 | wp_posts.post_excerpt      | text      | mediumtext |
 | wp_posts.to_ping           | text      | mediumtext |
 | wp_posts.pinged            | text      | mediumtext |
 | wp_comments.comment_author | tinytext  | text       |
 | wp_links.link_notes        | text      | mediumtext |
 }}}

 To reproduce:

 1. Install WP version 4.1.10
 2. Upgrade to WP version 4.4.2, making sure to run the DB upgrade routine
 3. Notice the columns mentioned above do not match the schema
 4. Upgrade to WP version 4.5.1, making sure to run the DB upgrade routine
 5. Notice that the columns are correct according to the schema

 Note that WordPress will not correct the schema during a minor release
 (e.g., 4.4.1 to 4.4.2), only during a major release.

 Please see these Travis CI builds that show the bug in action:

 * Beginning at WP 4.1.10, updating to 4.4.2, then updating to 4.5.1 =>
 https://travis-ci.org/tollmanz/mediumtext/jobs/127707747 (bug is present
 in 4.4.2 and corrected by 4.5.1 update)
 * Beginning at WP 4.1.10, updating to 4.4.1, then updating to 4.4.2 =>
 https://travis-ci.org/tollmanz/mediumtext/jobs/127707748 (bug is present
 in 4.4.1 and not corrected by 4.4.2 update)
 * Beginning at WP 4.2.7, updating to 4.4.2, then updating to 4.5.1 =>
 https://travis-ci.org/tollmanz/mediumtext/jobs/127707749 (bug is never
 present)

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