Page MenuHomePhabricator

term_row_id field needs to be converted to a BIGINT.
Closed, ResolvedPublic

Description

term_row_id is currently declared as "INT", which in MySQL is a signed 32 bit integer. On wikidata.org, this field has already reached 83 Million, once it hits 2 Billion, it will overflow. Note that this is an auto-increment field, so it essentially counts how many rows were inserted into the table - this number will hit the 31 bit limit much faster than the size of the table would.

The solution is to declare the term_row_id to be an UNSIGNED BIGINT (full 64 bit). That should give us some head room.

We need to consult with ops about how to perform the conversion on the live site.


Version: unspecified
Severity: normal
Whiteboard: backlog

Details

Reference
bz47135

Event Timeline

bzimport raised the priority of this task from to Medium.Nov 22 2014, 1:30 AM
bzimport set Reference to bz47135.
bzimport added a subscriber: Unknown Object (MLST).

[20:29:12] <Reedy> Surely INT -> BIGINT should be quick at the database level?
[20:29:21] <Reedy> Nothing needs to actually change..
[20:30:26] <Reedy> That and it has a PK so should work with OSC

Couple reminders:

  • if any 32-bit machines remain in the cluster, integers will potentially be 32-bit and esplode
  • if any JavaScript code needs to work with these IDs as numbers, they'll be limited to ~53 bits since numbers are represented as double-precision floats.

As long as 32-bit machines are all gone, shouldn't have trouble though. It'll take a while to hit 53 bits... I hope. :) [This was a big problem on Twitter a couple years ago, they had to add string fields to allow processing IDs as strings.]

I don't think we have to worry about us having 32 bit servers, I believe those were gone long ago.

The database setup and upgrade scripts should still be changed to use UNSIGNED BIGINT

The files that needs updates are:

repo/includes/store/sql/AddRowIDs*sql
repo/includes/store/sql/Wikibase*sql

Change 99637 had a related patch set uploaded by Daniel Kinzler:
(bug 47135) Make row IDs use BIGINT.

https://gerrit.wikimedia.org/r/99637

Change 99637 merged by jenkins-bot:
(bug 47135) Make row IDs use BIGINT.

https://gerrit.wikimedia.org/r/99637