This is an automated email from the ASF dual-hosted git repository.
yuqi1129 pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/gravitino.git
The following commit(s) were added to refs/heads/main by this push:
new e7b6816a4f [#12363] improvement(storage): Make MySQL index names
unique and give table_version_info a primary key (#12364)
e7b6816a4f is described below
commit e7b6816a4f4b2e8cbb131ac6f80f304f1052c416
Author: shaoyu-li <[email protected]>
AuthorDate: Tue Aug 18 02:54:02 2026 -0700
[#12363] improvement(storage): Make MySQL index names unique and give
table_version_info a primary key (#12364)
### What changes were proposed in this pull request?
Two changes to the MySQL DDL under `scripts/mysql/`:
1. Six index names that were reused across 15 tables (`idx_mid`,
`idx_cid`, `idx_sid`, `idx_rid`, `uk_sid_tn_del`, `uk_sid_fn_del`) are
each prefixed with their table name, e.g. `table_meta_idx_mid`.
32 index declarations are renamed in total.
2. `table_version_info`: `version` and `deleted_at` become `NOT NULL`,
and `uk_table_id_version_deleted_at` is promoted to
`PRIMARY KEY (table_id, version, deleted_at)`. No surrogate `id`
column is added.
16 files are touched: 10 `schema-*.sql` and 6 `upgrade-*.sql`. The
`table_version_info` change affects 5 of them.
This PR deliberately does not add a migration script. Only the
`CREATE TABLE` definitions change, so the new index names and the new
primary key apply to freshly created databases; an existing deployment
keeps its current index names and its primary-key-less
`table_version_info`. Renaming an index on a live table is metadata-only
from MySQL 5.7 onward, but turning the unique key of
`table_version_info`
into a primary key is a table rebuild, so migrating existing deployments
deserves its own change with a proper lock and duration analysis rather
than being folded in here.
### Why are the changes needed?
MySQL scopes index names per table, so the reused names were valid, but
they are ambiguous: `idx_mid` indexes `metalake_id` on most tables yet
`metadata_object_id` on `tag_relation_meta` and `policy_relation_meta`.
An `EXPLAIN` or slow query log that mentions `idx_mid` therefore does
not
identify the indexed column on its own. Unique names also let these
scripts be consumed by tooling that requires index names to be unique
across the whole schema.
`table_version_info` having no primary key means InnoDB clusters it on
an
invisible generated key, so every lookup through the unique key costs a
secondary index traversal plus a lookup into the clustered index.
Promoting the existing unique key removes that without widening the row.
Fix: #(issue)
### Does this PR introduce _any_ user-facing change?
No API or configuration changes.
The DDL for new installations changes: index names differ, and
`table_version_info` gains a primary key with two columns becoming
`NOT NULL`. Existing databases are left untouched by this PR.
### How was this patch tested?
No new tests; this is a DDL-only change.
- Verified that no index or unique-key name is declared more than once
across any of the 16 modified files.
- Confirmed no code selects an index by name: there is no `FORCE INDEX`
or `USE INDEX` in the tree, and no mapper references an index name.
- Confirmed `TableVersionBaseSQLProvider` always supplies `version` and
`deleted_at` on insert, so marking them `NOT NULL` cannot break
existing writes.
---
scripts/mysql/schema-2.0.0-mysql.sql | 70 +++++++++++++-------------
scripts/mysql/upgrade-1.3.0-to-2.0.0-mysql.sql | 52 +++++++++++++++++++
2 files changed, 87 insertions(+), 35 deletions(-)
diff --git a/scripts/mysql/schema-2.0.0-mysql.sql
b/scripts/mysql/schema-2.0.0-mysql.sql
index 7c5935016d..8b23ebca77 100644
--- a/scripts/mysql/schema-2.0.0-mysql.sql
+++ b/scripts/mysql/schema-2.0.0-mysql.sql
@@ -60,7 +60,7 @@ CREATE TABLE IF NOT EXISTS `schema_meta` (
`deleted_at` BIGINT(20) UNSIGNED NOT NULL DEFAULT 0 COMMENT 'schema
deleted at',
PRIMARY KEY (`schema_id`),
UNIQUE KEY `uk_cid_sn_del` (`catalog_id`, `schema_name`, `deleted_at`),
- KEY `idx_mid` (`metalake_id`)
+ KEY `schema_meta_idx_mid` (`metalake_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT 'schema
metadata';
CREATE TABLE IF NOT EXISTS `table_meta` (
@@ -74,9 +74,9 @@ CREATE TABLE IF NOT EXISTS `table_meta` (
`last_version` INT UNSIGNED NOT NULL DEFAULT 1 COMMENT 'table last
version',
`deleted_at` BIGINT(20) UNSIGNED NOT NULL DEFAULT 0 COMMENT 'table deleted
at',
PRIMARY KEY (`table_id`),
- UNIQUE KEY `uk_sid_tn_del` (`schema_id`, `table_name`, `deleted_at`),
- KEY `idx_mid` (`metalake_id`),
- KEY `idx_cid` (`catalog_id`)
+ UNIQUE KEY `table_meta_uk_sid_tn_del` (`schema_id`, `table_name`,
`deleted_at`),
+ KEY `table_meta_idx_mid` (`metalake_id`),
+ KEY `table_meta_idx_cid` (`catalog_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT 'table
metadata';
CREATE TABLE IF NOT EXISTS `table_column_version_info` (
@@ -99,9 +99,9 @@ CREATE TABLE IF NOT EXISTS `table_column_version_info` (
`audit_info` MEDIUMTEXT NOT NULL COMMENT 'column audit info',
PRIMARY KEY (`id`),
UNIQUE KEY `uk_tid_ver_cid_del` (`table_id`, `table_version`, `column_id`,
`deleted_at`),
- KEY `idx_mid` (`metalake_id`),
- KEY `idx_cid` (`catalog_id`),
- KEY `idx_sid` (`schema_id`)
+ KEY `table_column_version_info_idx_mid` (`metalake_id`),
+ KEY `table_column_version_info_idx_cid` (`catalog_id`),
+ KEY `table_column_version_info_idx_sid` (`schema_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT 'table
column version info';
CREATE TABLE IF NOT EXISTS `fileset_meta` (
@@ -116,9 +116,9 @@ CREATE TABLE IF NOT EXISTS `fileset_meta` (
`last_version` INT UNSIGNED NOT NULL DEFAULT 1 COMMENT 'fileset last
version',
`deleted_at` BIGINT(20) UNSIGNED NOT NULL DEFAULT 0 COMMENT 'fileset
deleted at',
PRIMARY KEY (`fileset_id`),
- UNIQUE KEY `uk_sid_fn_del` (`schema_id`, `fileset_name`, `deleted_at`),
- KEY `idx_mid` (`metalake_id`),
- KEY `idx_cid` (`catalog_id`)
+ UNIQUE KEY `fileset_meta_uk_sid_fn_del` (`schema_id`, `fileset_name`,
`deleted_at`),
+ KEY `fileset_meta_idx_mid` (`metalake_id`),
+ KEY `fileset_meta_idx_cid` (`catalog_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT 'fileset
metadata';
CREATE TABLE IF NOT EXISTS `fileset_version_info` (
@@ -135,9 +135,9 @@ CREATE TABLE IF NOT EXISTS `fileset_version_info` (
`deleted_at` BIGINT(20) UNSIGNED NOT NULL DEFAULT 0 COMMENT 'fileset
deleted at',
PRIMARY KEY (`id`),
UNIQUE KEY `uk_fid_ver_sto_del` (`fileset_id`, `version`,
`storage_location_name`, `deleted_at`),
- KEY `idx_mid` (`metalake_id`),
- KEY `idx_cid` (`catalog_id`),
- KEY `idx_sid` (`schema_id`)
+ KEY `fileset_version_info_idx_mid` (`metalake_id`),
+ KEY `fileset_version_info_idx_cid` (`catalog_id`),
+ KEY `fileset_version_info_idx_sid` (`schema_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT 'fileset
version info';
CREATE TABLE IF NOT EXISTS `topic_meta` (
@@ -153,9 +153,9 @@ CREATE TABLE IF NOT EXISTS `topic_meta` (
`last_version` INT UNSIGNED NOT NULL DEFAULT 1 COMMENT 'topic last
version',
`deleted_at` BIGINT(20) UNSIGNED NOT NULL DEFAULT 0 COMMENT 'topic deleted
at',
PRIMARY KEY (`topic_id`),
- UNIQUE KEY `uk_sid_tn_del` (`schema_id`, `topic_name`, `deleted_at`),
- KEY `idx_mid` (`metalake_id`),
- KEY `idx_cid` (`catalog_id`)
+ UNIQUE KEY `topic_meta_uk_sid_tn_del` (`schema_id`, `topic_name`,
`deleted_at`),
+ KEY `topic_meta_idx_mid` (`metalake_id`),
+ KEY `topic_meta_idx_cid` (`catalog_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT 'topic
metadata';
CREATE TABLE IF NOT EXISTS `user_meta` (
@@ -214,7 +214,7 @@ CREATE TABLE IF NOT EXISTS `user_role_rel` (
`deleted_at` BIGINT(20) UNSIGNED NOT NULL DEFAULT 0 COMMENT 'relation
deleted at',
PRIMARY KEY (`id`),
UNIQUE KEY `uk_ui_ri_del` (`user_id`, `role_id`, `deleted_at`),
- KEY `idx_rid` (`role_id`)
+ KEY `user_role_rel_idx_rid` (`role_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT 'user role
relation';
CREATE TABLE IF NOT EXISTS `group_meta` (
@@ -243,7 +243,7 @@ CREATE TABLE IF NOT EXISTS `group_role_rel` (
`deleted_at` BIGINT(20) UNSIGNED NOT NULL DEFAULT 0 COMMENT 'relation
deleted at',
PRIMARY KEY (`id`),
UNIQUE KEY `uk_gi_ri_del` (`group_id`, `role_id`, `deleted_at`),
- KEY `idx_rid` (`group_id`)
+ KEY `group_role_rel_idx_rid` (`group_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT 'group
role relation';
CREATE TABLE IF NOT EXISTS `idp_user_meta` (
@@ -308,7 +308,7 @@ CREATE TABLE IF NOT EXISTS `tag_relation_meta` (
PRIMARY KEY (`id`),
UNIQUE KEY `uk_ti_mi_mo_tv_del` (`tag_id`, `metadata_object_id`,
`metadata_object_type`, `tag_value`, `deleted_at`),
KEY `idx_tid` (`tag_id`),
- KEY `idx_mid` (`metadata_object_id`),
+ KEY `tag_relation_meta_idx_mid` (`metadata_object_id`),
KEY `idx_tid_value` (`tag_id`, `tag_value`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT 'tag
metadata object relation';
@@ -344,8 +344,8 @@ CREATE TABLE IF NOT EXISTS `model_meta` (
`deleted_at` BIGINT(20) UNSIGNED NOT NULL DEFAULT 0 COMMENT 'model deleted
at',
PRIMARY KEY (`model_id`),
UNIQUE KEY `uk_sid_mn_del` (`schema_id`, `model_name`, `deleted_at`),
- KEY `idx_mid` (`metalake_id`),
- KEY `idx_cid` (`catalog_id`)
+ KEY `model_meta_idx_mid` (`metalake_id`),
+ KEY `model_meta_idx_cid` (`catalog_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT 'model
metadata';
CREATE TABLE IF NOT EXISTS `model_version_info` (
@@ -363,9 +363,9 @@ CREATE TABLE IF NOT EXISTS `model_version_info` (
`deleted_at` BIGINT(20) UNSIGNED NOT NULL DEFAULT 0 COMMENT 'model version
deleted at',
PRIMARY KEY (`id`),
UNIQUE KEY `uk_mid_ver_uri_del` (`model_id`, `version`,
`model_version_uri_name`, `deleted_at`),
- KEY `idx_mid` (`metalake_id`),
- KEY `idx_cid` (`catalog_id`),
- KEY `idx_sid` (`schema_id`)
+ KEY `model_version_info_idx_mid` (`metalake_id`),
+ KEY `model_version_info_idx_cid` (`catalog_id`),
+ KEY `model_version_info_idx_sid` (`schema_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT 'model
version info';
CREATE TABLE IF NOT EXISTS `model_version_alias_rel` (
@@ -403,7 +403,7 @@ CREATE TABLE IF NOT EXISTS `policy_version_info` (
`deleted_at` BIGINT(20) UNSIGNED NOT NULL DEFAULT 0 COMMENT 'policy
deleted at',
PRIMARY KEY (`id`),
UNIQUE KEY `uk_pod_ver_del` (`policy_id`, `version`, `deleted_at`),
- KEY `idx_mid` (`metalake_id`)
+ KEY `policy_version_info_idx_mid` (`metalake_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT 'policy
version info';
CREATE TABLE IF NOT EXISTS `policy_relation_meta` (
@@ -418,7 +418,7 @@ CREATE TABLE IF NOT EXISTS `policy_relation_meta` (
PRIMARY KEY (`id`),
UNIQUE KEY `uk_pi_mi_mo_del` (`policy_id`, `metadata_object_id`,
`metadata_object_type`, `deleted_at`),
KEY `idx_pid` (`policy_id`),
- KEY `idx_mid` (`metadata_object_id`)
+ KEY `policy_relation_meta_idx_mid` (`metadata_object_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT 'policy
metadata object relation';
CREATE TABLE IF NOT EXISTS `statistic_meta` (
@@ -479,9 +479,9 @@ CREATE TABLE IF NOT EXISTS `table_version_info` (
`sort_orders` MEDIUMTEXT DEFAULT NULL COMMENT 'table sort order info',
`indexes` MEDIUMTEXT DEFAULT NULL COMMENT 'table index info',
`comment` MEDIUMTEXT DEFAULT NULL COMMENT 'table comment',
- `version` BIGINT(20) UNSIGNED COMMENT 'table current version',
- `deleted_at` BIGINT(20) UNSIGNED DEFAULT 0 COMMENT 'table deletion
timestamp, 0 means not deleted',
- UNIQUE KEY `uk_table_id_version_deleted_at` (`table_id`, `version`,
`deleted_at`)
+ `version` BIGINT(20) UNSIGNED NOT NULL COMMENT 'table current version',
+ `deleted_at` BIGINT(20) UNSIGNED NOT NULL DEFAULT 0 COMMENT 'table
deletion timestamp, 0 means not deleted',
+ PRIMARY KEY (`table_id`, `version`, `deleted_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT 'table
detail information including format, location, properties, partition,
distribution, sort order, index and so on';
CREATE TABLE IF NOT EXISTS `function_meta` (
@@ -497,9 +497,9 @@ CREATE TABLE IF NOT EXISTS `function_meta` (
`audit_info` MEDIUMTEXT NOT NULL COMMENT 'function audit info',
`deleted_at` BIGINT(20) UNSIGNED NOT NULL DEFAULT 0 COMMENT 'function
deleted at',
PRIMARY KEY (`function_id`),
- UNIQUE KEY `uk_sid_fn_del` (`schema_id`, `function_name`, `deleted_at`),
- KEY `idx_mid` (`metalake_id`),
- KEY `idx_cid` (`catalog_id`)
+ UNIQUE KEY `function_meta_uk_sid_fn_del` (`schema_id`, `function_name`,
`deleted_at`),
+ KEY `function_meta_idx_mid` (`metalake_id`),
+ KEY `function_meta_idx_cid` (`catalog_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT 'function
metadata';
CREATE TABLE IF NOT EXISTS `function_version_info` (
@@ -515,9 +515,9 @@ CREATE TABLE IF NOT EXISTS `function_version_info` (
`deleted_at` BIGINT(20) UNSIGNED NOT NULL DEFAULT 0 COMMENT 'function
version deleted at',
PRIMARY KEY (`id`),
UNIQUE KEY `uk_fid_ver_del` (`function_id`, `version`, `deleted_at`),
- KEY `idx_mid` (`metalake_id`),
- KEY `idx_cid` (`catalog_id`),
- KEY `idx_sid` (`schema_id`)
+ KEY `function_version_info_idx_mid` (`metalake_id`),
+ KEY `function_version_info_idx_cid` (`catalog_id`),
+ KEY `function_version_info_idx_sid` (`schema_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT 'function
version info';
CREATE TABLE IF NOT EXISTS `view_meta` (
diff --git a/scripts/mysql/upgrade-1.3.0-to-2.0.0-mysql.sql
b/scripts/mysql/upgrade-1.3.0-to-2.0.0-mysql.sql
index 32a0f84158..077c21c0c4 100644
--- a/scripts/mysql/upgrade-1.3.0-to-2.0.0-mysql.sql
+++ b/scripts/mysql/upgrade-1.3.0-to-2.0.0-mysql.sql
@@ -41,3 +41,55 @@ ALTER TABLE `tag_relation_meta`
CREATE UNIQUE INDEX `uk_ti_mi_mo_tv_del` ON `tag_relation_meta` (`tag_id`,
`metadata_object_id`, `metadata_object_type`, `tag_value`, `deleted_at`);
CREATE INDEX `idx_tid_value` ON `tag_relation_meta` (`tag_id`, `tag_value`);
+
+-- Index names are only scoped per-table in MySQL, so the same name could be
+-- reused across tables. Prefix each reused name with its table name so that
+-- every index name is unique across the whole schema. A database upgraded
+-- from 1.3.0 ends up with the same index names as a fresh 2.0.0 install.
+ALTER TABLE `schema_meta` RENAME INDEX `idx_mid` TO `schema_meta_idx_mid`;
+ALTER TABLE `table_meta` RENAME INDEX `uk_sid_tn_del` TO
`table_meta_uk_sid_tn_del`;
+ALTER TABLE `table_meta` RENAME INDEX `idx_mid` TO `table_meta_idx_mid`;
+ALTER TABLE `table_meta` RENAME INDEX `idx_cid` TO `table_meta_idx_cid`;
+ALTER TABLE `table_column_version_info` RENAME INDEX `idx_mid` TO
`table_column_version_info_idx_mid`;
+ALTER TABLE `table_column_version_info` RENAME INDEX `idx_cid` TO
`table_column_version_info_idx_cid`;
+ALTER TABLE `table_column_version_info` RENAME INDEX `idx_sid` TO
`table_column_version_info_idx_sid`;
+ALTER TABLE `fileset_meta` RENAME INDEX `uk_sid_fn_del` TO
`fileset_meta_uk_sid_fn_del`;
+ALTER TABLE `fileset_meta` RENAME INDEX `idx_mid` TO `fileset_meta_idx_mid`;
+ALTER TABLE `fileset_meta` RENAME INDEX `idx_cid` TO `fileset_meta_idx_cid`;
+ALTER TABLE `fileset_version_info` RENAME INDEX `idx_mid` TO
`fileset_version_info_idx_mid`;
+ALTER TABLE `fileset_version_info` RENAME INDEX `idx_cid` TO
`fileset_version_info_idx_cid`;
+ALTER TABLE `fileset_version_info` RENAME INDEX `idx_sid` TO
`fileset_version_info_idx_sid`;
+ALTER TABLE `topic_meta` RENAME INDEX `uk_sid_tn_del` TO
`topic_meta_uk_sid_tn_del`;
+ALTER TABLE `topic_meta` RENAME INDEX `idx_mid` TO `topic_meta_idx_mid`;
+ALTER TABLE `topic_meta` RENAME INDEX `idx_cid` TO `topic_meta_idx_cid`;
+ALTER TABLE `user_role_rel` RENAME INDEX `idx_rid` TO `user_role_rel_idx_rid`;
+ALTER TABLE `group_role_rel` RENAME INDEX `idx_rid` TO
`group_role_rel_idx_rid`;
+ALTER TABLE `tag_relation_meta` RENAME INDEX `idx_mid` TO
`tag_relation_meta_idx_mid`;
+ALTER TABLE `model_meta` RENAME INDEX `idx_mid` TO `model_meta_idx_mid`;
+ALTER TABLE `model_meta` RENAME INDEX `idx_cid` TO `model_meta_idx_cid`;
+ALTER TABLE `model_version_info` RENAME INDEX `idx_mid` TO
`model_version_info_idx_mid`;
+ALTER TABLE `model_version_info` RENAME INDEX `idx_cid` TO
`model_version_info_idx_cid`;
+ALTER TABLE `model_version_info` RENAME INDEX `idx_sid` TO
`model_version_info_idx_sid`;
+ALTER TABLE `policy_version_info` RENAME INDEX `idx_mid` TO
`policy_version_info_idx_mid`;
+ALTER TABLE `policy_relation_meta` RENAME INDEX `idx_mid` TO
`policy_relation_meta_idx_mid`;
+ALTER TABLE `function_meta` RENAME INDEX `uk_sid_fn_del` TO
`function_meta_uk_sid_fn_del`;
+ALTER TABLE `function_meta` RENAME INDEX `idx_mid` TO `function_meta_idx_mid`;
+ALTER TABLE `function_meta` RENAME INDEX `idx_cid` TO `function_meta_idx_cid`;
+ALTER TABLE `function_version_info` RENAME INDEX `idx_mid` TO
`function_version_info_idx_mid`;
+ALTER TABLE `function_version_info` RENAME INDEX `idx_cid` TO
`function_version_info_idx_cid`;
+ALTER TABLE `function_version_info` RENAME INDEX `idx_sid` TO
`function_version_info_idx_sid`;
+
+-- `table_version_info` had no primary key. `version` and `deleted_at` become
+-- NOT NULL and the existing unique key is promoted to the primary key, which
+-- also makes it the InnoDB clustered index.
+--
+-- Both columns are always supplied on insert by TableVersionBaseSQLProvider,
+-- so no row should hold NULL. Verify before running if you are unsure:
+-- SELECT COUNT(*) FROM `table_version_info`
+-- WHERE `version` IS NULL OR `deleted_at` IS NULL;
+-- The statement below fails rather than silently coercing NULL to 0.
+ALTER TABLE `table_version_info`
+ MODIFY COLUMN `version` BIGINT(20) UNSIGNED NOT NULL COMMENT 'table
current version',
+ MODIFY COLUMN `deleted_at` BIGINT(20) UNSIGNED NOT NULL DEFAULT 0 COMMENT
'table deletion timestamp, 0 means not deleted',
+ DROP INDEX `uk_table_id_version_deleted_at`,
+ ADD PRIMARY KEY (`table_id`, `version`, `deleted_at`);
