Support for PK changes from signed int to unsigned bigint

[dveeden]

Feature Request

Is your feature request related to a problem? Please describe:

A table is created with INT (signed). Then the volume of the data changes. Changing it to BIGINT might help a bit but eventually changing it to BIGINT UNSIGNED is needed.

This can be seen as a sub-issue of #18090

Describe the feature you'd like:

Allow this to work:

CREATE TABLE t (id INT, PRIMARY KEY (id));
ALTER TABLE t MODIFY COLUMN id BIGINT UNSIGNED;

Describe alternatives you've considered:

Something like this might be possible:

CREATE TABLE t2 (id BIGINT UNSIGNED, PRIMARY KEY (id));
INSERT INTO t2 SELECT * FROM t;
RENAME TABLE t TO t_old, t2 TO t;
DROP TABLE t_old;

However:

• This would likely cause and/or need downtime for the application
• This is not very efficient
• This would cause a big stream of data on TiCDC
• This needs TiDB specific handling of this change (not MySQL Compatible behavior)
• This either needs the BATCH statement or external tools for larger tables, adding operational overhead.
• This needs a lot of time.

Another option:

ALTER TABLE t DROP PRIMARY KEY;
ALTER TABLE t MODIFY COLUMN id BIGINT UNSIGNED;
ALTER TABLE t ADD PRIMARY KEY(id);

However:

• This only works for NONCLUSTERED tables, which isn't the default
• This leaves a gap in which PK's are not enforced.
• Doing these 3 operations in a single statement isn't supported by TiDB.

Teachability, Documentation, Adoption, Migration Strategy:

1. New negative values should be rejected
2. Then the table contents should be checked for negative values
3. Then the change should be made. This should be a metadata only change if possible.
4. Then values bigger than the max-signed value should be allowed.