Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Full support for altering primary key #18090

Open
scsldb opened this issue Jun 17, 2020 · 9 comments
Open

Full support for altering primary key #18090

scsldb opened this issue Jun 17, 2020 · 9 comments
Labels
compatibility-mysql8 This is a compatibility issue with MySQL 8.0(but NOT 5.7) feature/accepted This feature request is accepted by product managers priority/P1 The issue has P1 priority. type/compatibility type/feature-request Categorizes issue or PR as related to a new feature.

Comments

@scsldb
Copy link

scsldb commented Jun 17, 2020

Description

Full support for altering primary key. The current "alter-primary-key" can only be treated as a workaround: it is with a lot of limitations and the semantic of it is confusing:

If an existing table defined a single integer column as its primary key, the primary key cannot be altered.

Altering integer primary key for is challenging in TiDB because it is special: the row id of the data is mapped from the integers, so it determines where the actual row data is stored. When Cluster Index is supported, such limitation will be extended to all explicit primary keys.

Category

Feature, Usability

Value

The ability to change the primary key is an important feature of a maintainable database. Users usually starts their deployments in a simple scenario, and with the growth of the application there will be likely the requirement to change their primary keys.

Workload Estimation

Seems the only way to implement this feature is the 'COPY' algorithm, if we want to make it available online:

  1. Create a hidden table that is with the changed(new) primary key.
  2. All updates of the existing table are applied to the changed table as well.
  3. Backfill all rows from existing table to the new table.

75

@scsldb scsldb added priority/P1 The issue has P1 priority. type/feature-request Categorizes issue or PR as related to a new feature. labels Jun 17, 2020
@scsldb scsldb modified the milestones: v5.0-alpha, v5.0-alpha.1 Jun 17, 2020
@scsldb scsldb modified the milestones: v5.0.0-alpha.1-tmp, pools Jul 15, 2020
@scsldb scsldb added the feature/accepted This feature request is accepted by product managers label Jul 16, 2020
@zz-jason zz-jason removed their assignment Jul 22, 2020
@tirsen
Copy link
Contributor

tirsen commented Sep 11, 2020

This will be able to convert a non-clustered table to a clustered table as well? Even if there are no other changes to the table? What would be the SQL syntax for doing that?

@zz-jason
Copy link
Member

zz-jason commented Sep 11, 2020

This will be able to convert a non-clustered table to a clustered table as well? Even if there are no other changes to the table?

If tidb_enable_clustered_index is true, the correct behavior of this feature is to cluster the new primary key with the table records. So I think the answer should be yes if tidb_enable_clustered_index is true before altering the primary key.

@zz-jason
Copy link
Member

What would be the SQL syntax for doing that?

Seems MySQL doesn't provide the direct syntax to alter a primary key, users have to drop the originally primary key firstly, then add a new primary key.

@nullnotnil do you have any suggestions on the syntax to alter the primary key? or should we keep the same behavior with MySQL?

@tirsen
Copy link
Contributor

tirsen commented Sep 11, 2020

No I actually don't want to alter the primary key. I want to rebuild a table that is using a composite key from non-clustered to clustered.

For example if I have a table like this:

create table order (
    id bigint,
    customer_id bigint,
    -- other columns
    primary key (customer_id, id)
)

On TiDB 4.0 this table will be non-clustered with a hidden _tidb_rowid column.

Then I upgrade to TiDB 5.0. Now I want to rebuild the table to become clustered without changing anything else about the schema. What would be the syntax for that?

(I think we also need a syntax for changing primary key btw, but that's a separate issue. Probably the syntax should be similar.)

@ghost
Copy link

ghost commented Sep 11, 2020

Currently, it's also not possible to either add or remove partitioning from a table. Presumably this will require the COPY algorithm as well:

mysql> ALTER TABLE t1 PARTITION BY RANGE ( YEAR(`start_date`) ) (
    ->   PARTITION `p2010` VALUES LESS THAN (2011),
    ->   PARTITION `p2011` VALUES LESS THAN (2012),
    ->   PARTITION `p2012` VALUES LESS THAN (2013),
    ->   PARTITION `p2013` VALUES LESS THAN (2014),
    ->   PARTITION `p2014` VALUES LESS THAN (2015),
    ->   PARTITION `p2015` VALUES LESS THAN (2016),
    ->   PARTITION `p2016` VALUES LESS THAN (2017),
    ->   PARTITION `p2017` VALUES LESS THAN (2018),
    ->   PARTITION `p2018` VALUES LESS THAN (2019),
    ->   PARTITION `p2019` VALUES LESS THAN (2020),
    ->   PARTITION `pmax` VALUES LESS THAN (MAXVALUE)
    -> );
ERROR 1105 (HY000): alter table partition is unsupported

mysql> ALTER TABLE trips_partitioned REMOVE PARTITIONING;
ERROR 8200 (HY000): Unsupported remove partitioning

@ghost
Copy link

ghost commented Sep 11, 2020

@nullnotnil do you have any suggestions on the syntax to alter the primary key? or should we keep the same behavior with MySQL?

No reason to differ from MySQL here:

DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (pk INT NOT NULL auto_increment, b INT, PRIMARY KEY(pk));
ALTER TABLE t1 DROP PRIMARY KEY, ADD PRIMARY KEY (pk, b);

MySQL supports adding a primary key online, but dropping a primary key is not. See Table 14.11 Online DDL Support for Primary Key Operations. It would be nice of course if TiDB can support online for both aspects.

Note: For the case @tirsen mentions, MySQL supports the ability to rebuild a table using what the manual describes as a "null" alter table:

Running ALTER TABLE tbl_name ENGINE=INNODB on an existing InnoDB table performs a “null” ALTER TABLE operation, which can be used to defragment an InnoDB table, as described in Section 14.12.4, “Defragmenting a Table”. Running ALTER TABLE tbl_name FORCE on an InnoDB table performs the same function.

MySQL had a feature to show if temporals were in an old format at one point, which made it easy to identify tables that needed upgrades. I expect that similar could be done to expose that a table is not using a clustered index. It would be nice to show this info in information_schema too.

@dveeden dveeden added type/compatibility compatibility-mysql8 This is a compatibility issue with MySQL 8.0(but NOT 5.7) labels Jul 11, 2023
@devhaozi
Copy link

I'm waiting for support this.

@dveeden
Copy link
Contributor

dveeden commented Sep 4, 2023

I'm waiting for support this.

  • Please give the description of this issue a 👍 as that might help others when sorting issues by popularity.
  • I think everyone agrees that implementing this would be very useful, but maybe you can share some more details about your situation and why/how this is causing issues for you.

@devhaozi
Copy link

devhaozi commented Sep 4, 2023

No I actually don't want to alter the primary key. I want to rebuild a table that is using a composite key from non-clustered to clustered.

For example if I have a table like this:

create table order (
    id bigint,
    customer_id bigint,
    -- other columns
    primary key (customer_id, id)
)

On TiDB 4.0 this table will be non-clustered with a hidden _tidb_rowid column.

Then I upgrade to TiDB 5.0. Now I want to rebuild the table to become clustered without changing anything else about the schema. What would be the syntax for that?

(I think we also need a syntax for changing primary key btw, but that's a separate issue. Probably the syntax should be similar.)

In my needs, sometimes it is necessary to add new columns to existing tables. In order to improve performance, it may be necessary to delete the primary key, and then create a compound index with several columns.

Currently, TiDB does not support deleting primary keys, so it is impossible to create a compound index specifically.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
compatibility-mysql8 This is a compatibility issue with MySQL 8.0(but NOT 5.7) feature/accepted This feature request is accepted by product managers priority/P1 The issue has P1 priority. type/compatibility type/feature-request Categorizes issue or PR as related to a new feature.
Projects
None yet
Development

No branches or pull requests

5 participants