Page MenuHomePhabricator

The primary key of recentchanges (rc_id) table should be unsigned
Closed, ResolvedPublic

Description

rc_id will hit its max at 2147483647 rather than 4294967295 because it's an int rather than an unsigned int. Therefore, it should be changed to unsigned.

Maybe this is also a good opportunity to change it to an unsigned bigint, since it's at 600 million already on enwiki and at the current rate of increase could reach its limit in our lifetime.

Details

Reference
bz60962

Event Timeline

bzimport raised the priority of this task from to Lowest.Nov 22 2014, 3:02 AM
bzimport set Reference to bz60962.
bzimport added a subscriber: Unknown Object (MLST).

Some fields that should be unsigned, that aren't:
*change_tag.ct_rc_id
*change_tag.ct_log_id
*change_tag.ct_rev_id
*filearchive.fa_id
*filearchive.fa_deleted_user
*ipblocks.ipb_id
*page_props.pp_page
*page_restrictions.pr_page
*recentchanges.rc_id
*tag_summary.ts_rc_id
*tag_summary.ts_log_id
*tag_summary.ts_rev_id
*user_newtalk.user_id
*user_properties.up_user

See also bug 61111, proposing changing primary keys to bigint unsigned.

Change 112825 had a related patch set uploaded by MZMcBride:
Make the following fields int unsigned:

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

Krinkle claimed this task.
Krinkle added a subscriber: Krinkle.

In the current MediaWiki master (d056d4c2), there are no signed fields for any primary keys.

rc_id is still signed as far as I can tell – at least, it’s not explicitly unsigned in the documentation, [in maintenance/tables.sql](https://gerrit.wikimedia.org/r/plugins/gitiles/mediawiki/core/+/d056d4c273a069d572ceb2160346ee697d7b87de/maintenance/tables.sql#1377), on the replicas, or on mwdebug1002. Or am I misunderstanding something? (I’m not very familiar with (My)SQL, sorry, but as far as I can tell from a quick Google, signed is the default.)

@LucasWerkmeister Yeah, I checked the schema for any explicit use of signed, and didn't think about the implicit ones until I saw T63111.

As a generic task, these are basically the same, but rather than merging it, perhaps we can keep this as sub-task focussed only on the rc_id field which is the most pressing one, and might make sense to adopt a non-bigint solution for first.

Krinkle renamed this task from Primary keys and fields that reference them should be unsigned to The primary key of recentchanges (rc_id) table should be unsigned.Feb 10 2019, 6:52 PM
Krinkle reopened this task as Open.
Krinkle removed Krinkle as the assignee of this task.
Krinkle raised the priority of this task from Lowest to High.
Krinkle added a project: DBA.
Krinkle updated the task description. (Show Details)
Marostegui added a subscriber: Marostegui.

I am fine going to unsigned for rc_id . Once tables.sql is merged with this change, just add Blocked-on-schema-change as a tag (https://wikitech.wikimedia.org/wiki/Schema_changes#Workflow_of_a_schema_change) and we can take care of it :)

I agree with start working in this soon with high priority, rc_id will run out of range "soon" (worst auto_increment to have into account). Some stats:

1enwiki:
2
3root@db1114[sys]> select * FROM schema_auto_increment_columns LIMIT 4 \G
4*************************** 1. row ***************************
5 table_schema: enwiki
6 table_name: recentchanges
7 column_name: rc_id
8 data_type: int
9 column_type: int(8)
10 is_signed: 1
11 is_unsigned: 0
12 max_value: 2147483647
13 auto_increment: 1137690279
14auto_increment_ratio: 0.5298
15*************************** 2. row ***************************
16 table_schema: enwiki
17 table_name: cu_changes
18 column_name: cuc_id
19 data_type: int
20 column_type: int(11)
21 is_signed: 1
22 is_unsigned: 0
23 max_value: 2147483647
24 auto_increment: 854445063
25auto_increment_ratio: 0.3979
26*************************** 3. row ***************************
27 table_schema: enwiki
28 table_name: text
29 column_name: old_id
30 data_type: int
31 column_type: int(8) unsigned
32 is_signed: 0
33 is_unsigned: 1
34 max_value: 4294967295
35 auto_increment: 898750098
36auto_increment_ratio: 0.2093
37*************************** 4. row ***************************
38 table_schema: enwiki
39 table_name: revision
40 column_name: rev_id
41 data_type: int
42 column_type: int(8) unsigned
43 is_signed: 0
44 is_unsigned: 1
45 max_value: 4294967295
46 auto_increment: 887709732
47auto_increment_ratio: 0.2067
484 rows in set, 36 warnings (0.04 sec)
49
50wikidatawiki:
51
52root@db1071.eqiad.wmnet[sys]> select * FROM schema_auto_increment_columns LIMIT 4 \G
53*************************** 1. row ***************************
54 table_schema: wikidatawiki
55 table_name: recentchanges
56 column_name: rc_id
57 data_type: int
58 column_type: int(11)
59 is_signed: 1
60 is_unsigned: 0
61 max_value: 2147483647
62 auto_increment: 919219099
63auto_increment_ratio: 0.4280
64*************************** 2. row ***************************
65 table_schema: wikidatawiki
66 table_name: cu_changes
67 column_name: cuc_id
68 data_type: int
69 column_type: int(11)
70 is_signed: 1
71 is_unsigned: 0
72 max_value: 2147483647
73 auto_increment: 899023427
74auto_increment_ratio: 0.4186
75*************************** 3. row ***************************
76 table_schema: wikidatawiki
77 table_name: text
78 column_name: old_id
79 data_type: int
80 column_type: int(10) unsigned
81 is_signed: 0
82 is_unsigned: 1
83 max_value: 4294967295
84 auto_increment: 889703558
85auto_increment_ratio: 0.2072
86*************************** 4. row ***************************
87 table_schema: wikidatawiki
88 table_name: revision
89 column_name: rev_id
90 data_type: int
91 column_type: int(10) unsigned
92 is_signed: 0
93 is_unsigned: 1
94 max_value: 4294967295
95 auto_increment: 882728203
96auto_increment_ratio: 0.2055
974 rows in set, 52 warnings (0.37 sec)

Ladsgroup added a subscriber: Ladsgroup.

I will do this as part of migrating the rc table to abstract schema (T230428: Migrate tables.sql to abstract schema)

Change 667356 had a related patch set uploaded (by Ladsgroup; owner: Ladsgroup):
[mediawiki/core@master] Make rc_id unsigned

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

Change 667356 merged by jenkins-bot:
[mediawiki/core@master] Make rc_id unsigned

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

The code-wise work for the schema change is done. Production schema change is being tracked in T276150: Schema change to make rc_id unsigned and rc_timestamp BINARY.