WordPress.org

Make WordPress Core

Opened 2 years ago

Last modified 11 months ago

#40418 assigned defect (bug)

ID columns in multisite database tables should be unsigned

Reported by: johnjamesjacoby Owned by: flixos90
Milestone: Awaiting Review Priority: normal
Severity: major Version: 3.0
Component: Database Keywords: 2nd-opinion has-patch needs-testing needs-refresh
Focuses: multisite Cc:

Description

See #8751 for ye'olde single-site effort to normalize the respective object ID columns.

All multisite ID columns are bigint(20), but none of them are unsigned which has 2 unintended consequences:

  • Negative numbers can be stored as values instead of being set to 0
  • Maximum int of 9223372036854775807 instead of intended 18446744073709551615

Changes are necessary to every multisite database table, as they all touch site or network IDs.

Patch imminent

Attachments (1)

40418.patch (4.2 KB) - added by johnjamesjacoby 2 years ago.

Download all attachments as: .zip

Change History (12)

#1 @johnjamesjacoby
2 years ago

  • Component changed from General to Database
  • Focuses multisite added
  • Keywords 2nd-opinion has-patch needs-testing added
  • Severity changed from normal to major
  • Version set to 3.0

#2 @johnjamesjacoby
2 years ago

40418.patch

  • Bumps the database version (will likely need a patch refresh by the time it's time)
  • Updates $ms_global_tables in wp_get_db_schema() with the unsigned data types
  • Adds ALTERS to pre_schema_upgrade() for existing installations

Related to work from #38203.

This ticket was mentioned in Slack in #core-multisite by jjj. View the logs.


2 years ago

#4 @johnjamesjacoby
2 years ago

  • Summary changed from ID columns in multisite database tables are all unsigned to ID columns in multisite database tables should be unsigned

#5 @johnjamesjacoby
2 years ago

A few questions came up in Slack about this patch, so I'll answer them as best I can here:

  • These ALTERs will take some time, because indices need to be rebuilt
  • MySQL will invisibly perform table locks & copies for signature changes
  • MySQL will perform string to integer conversions on really large numbers, so places where values would be higher than PHP_INT_MAX could be turned into strings in PHP, and MySQL will save them correctly
  • My research on signed vs unsigned leads me to believe there is no performance benefit for WordPress with this change, as the range of available index values has not changed (it's only shifted out of negative numbers and into higher ones)

#6 @johnjamesjacoby
2 years ago

If you're curious about how I even discovered this in the first place, wonder over here but maybe come back quickly. :)

#7 @spacedmonkey
12 months ago

  • Keywords needs-refresh added
  • Owner set to flixos90
  • Status changed from new to assigned

This ticket needs to merged before 5.0.0 now that blogmeta (#37923) is in core.

The patch needs to refreshed a little before merge. I have assigned to @flixos90 to own, as he merged blog meta. Will to review and support on this.

This ticket was mentioned in Slack in #core-multisite by spacedmonkey. View the logs.


12 months ago

This ticket was mentioned in Slack in #core-multisite by spacedmonkey. View the logs.


11 months ago

This ticket was mentioned in Slack in #core-multisite by flixos90. View the logs.


11 months ago

This ticket was mentioned in Slack in #core-multisite by spacedmonkey. View the logs.


11 months ago

Note: See TracTickets for help on using tickets.