#36748 closed defect (bug) (fixed)
Updating tables to utf8mb4 causes some columns to change type unexpectedly
Reported by: | tollmanz | Owned by: | pento |
---|---|---|---|
Milestone: | 4.5.3 | Priority: | normal |
Severity: | normal | Version: | 4.2 |
Component: | Upgrade/Install | Keywords: | fixed-major |
Focuses: | performance | Cc: |
Description
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:
- Install WP version 4.1.10
- Upgrade to WP version 4.4.2, making sure to run the DB upgrade routine
- Notice the columns mentioned above do not match the schema
- Upgrade to WP version 4.5.1, making sure to run the DB upgrade routine
- 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)
Change History (16)
#1
@
9 years ago
- Focuses performance added
- Keywords needs-patch needs-unit-tests added
- Milestone changed from Awaiting Review to 4.5.2
This ticket was mentioned in Slack in #core by adamsilverstein. View the logs.
8 years ago
This ticket was mentioned in Slack in #core by adamsilverstein. View the logs.
8 years ago
#6
@
8 years ago
- Keywords fixed-ma added; needs-patch needs-unit-tests removed
- Resolution fixed deleted
- Status changed from closed to reopened
[37525] will need to be backported to the 4.2-4.5 branches.
#8
@
8 years ago
@pento Should the DB schema also be updated? Seems like it would be ideal for the schema to match what the database will actually look like.
#9
@
8 years ago
I'm inclined to leave it as is, particularly as many sites have already gone through the upgrade-then-downsize dance. Upsizing them again is just unnecessary downtime.
I'm inclined to fix this by preventing
dbDelta()
from downgrading the size of a*text
(or*blob
) field.We're already allowing for custom sized columns in the WPDB preflight checks, so this is mostly about preventing an unnecessary
ALTER TABLE
.Because of the significant performance implications, this will need to be backported to the 4.2 branch, in preparation for major release auto updates.