ID columns in multisite database tables should be unsigned

[johnjamesjacoby]

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

[johnjamesjacoby]

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.

[johnjamesjacoby]

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)

[johnjamesjacoby]

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

[spacedmonkey]

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.

[johnjamesjacoby]

Refreshed for 6.9, includes new wp_blogmeta table

[johnjamesjacoby]

Refreshed patch for 6.9, now including the wp_blogmeta table per @spacedmonkey's comment.

Moving this to the 6.9 milestone and adding the commit keyword.

[spacedmonkey]

This looks good to commit 👍

[johnjamesjacoby]

Related to #9422.

[johnjamesjacoby]

In 60497:

Multisite: Enforce consistent types on ID columns in multisite database tables, to better allow for foreign keys to more reliably be defined between them.

This change adjusts the install & upgrade routines so all of ID-based database columns in the multisite database tables are unsigned, bringing them up-to-speed with ID-based columns in single-site tables.

Additionally, the $wp_db_version number is bumped, and the pre_schema_upgrade() upgrade function is modified to accommodate & use that new version.

Follow-up to [10852].

Props spacedmonkey, johnjamesjacoby.

Fixes #40418.