This is an automated email from the ASF dual-hosted git repository.
jerryshao 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 3d2bd431bb [#12516] fix(scripts): make 1.3 to 2.0 upgrades retryable
(#13689)
3d2bd431bb is described below
commit 3d2bd431bbec3f4e47db70ba3a3f47f385bc9f90
Author: Qi Yu <[email protected]>
AuthorDate: Thu Oct 8 17:22:09 2026 +0800
[#12516] fix(scripts): make 1.3 to 2.0 upgrades retryable (#13689)
### What changes were proposed in this pull request?
Make the MySQL, PostgreSQL, and H2 1.3.0-to-2.0.0 upgrade scripts
retryable after partial execution. Guard column additions, index
creation/deletion/renaming, and primary-key conversion while preserving
existing data and reporting unexpected schema errors.
Add tests for repeated upgrades, retries after persistent structural
changes and data updates, and unexpected MySQL primary keys. Sample the
first and last MySQL index rename; compare upgraded schemas with fresh
2.0.0 installs, excluding the deliberately retained
`policy_relation_meta` table. Earlier upgrade scripts are unchanged.
### Why are the changes needed?
A failed upgrade can leave committed changes behind. Re-running the
current scripts then fails on already-applied operations, preventing
recovery through a normal retry.
Related to #12516. This overlaps the 1.3.0-to-2.0.0 index-creation
changes in #12968 and extends retry handling to the rest of that
migration.
### Does this PR introduce _any_ user-facing change?
Users can retry a partially completed 1.3.0-to-2.0.0 upgrade after
fixing the failure cause. Unexpected schema differences require manual
repair before retrying; the upgrade guide now documents this. No API or
configuration changes.
### How was this patch tested?
- Previous head `e6d4a0a`: full `TestSQLScripts` suite passed (21 tests
across H2, MySQL, and PostgreSQL).
- Previous head `e6d4a0a`: the partial-execution matrix passed for all
three backends: 78 checkpoints (30 MySQL, 27 PostgreSQL, 21 H2), with
data preservation and fresh-2.0.0 schema comparisons.
- MySQL regression tests reject unrelated or reordered primary keys and
an incomplete conversion retaining the legacy unique index.
- Follow-up `d8fd07b`: only `testUpgradeToTwoZeroIsIdempotent` was
rerun, passing on H2, MySQL, and PostgreSQL (three invocations). It now
verifies that renaming an explicit H2 index changes the schema
comparison; only metadata-confirmed generated suffixes are normalized.
The MySQL error assertion distinguishes the quoted source index, and the
checkpoint selector checks its lower bound. No full core or retry-matrix
rerun was performed for this follow-up.
- Core Spotless formatting and `git diff --check` passed.
```sh
./gradlew :core:test --tests
org.apache.gravitino.storage.TestSQLScripts.testUpgradeToTwoZeroIsIdempotent
-PskipITs -PskipDockerTests=false -PskipWeb=true
```
---
.../apache/gravitino/storage/TestSQLScripts.java | 339 ++++++++++++++
docs/how-to-upgrade.md | 5 +
scripts/h2/upgrade-1.3.0-to-2.0.0-h2.sql | 28 +-
scripts/mysql/upgrade-1.3.0-to-2.0.0-mysql.sql | 508 ++++++++++++++++++---
.../upgrade-1.3.0-to-2.0.0-postgresql.sql | 4 +-
5 files changed, 802 insertions(+), 82 deletions(-)
diff --git
a/core/src/test/java/org/apache/gravitino/storage/TestSQLScripts.java
b/core/src/test/java/org/apache/gravitino/storage/TestSQLScripts.java
index 0997134152..e3ccdb44cc 100644
--- a/core/src/test/java/org/apache/gravitino/storage/TestSQLScripts.java
+++ b/core/src/test/java/org/apache/gravitino/storage/TestSQLScripts.java
@@ -23,6 +23,7 @@ import java.io.IOException;
import java.nio.file.Files;
import java.nio.file.Path;
import java.sql.Connection;
+import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
@@ -32,6 +33,9 @@ import java.util.Comparator;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
+import java.util.Set;
+import java.util.TreeMap;
+import java.util.TreeSet;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import org.apache.gravitino.storage.relational.TestJDBCBackend;
@@ -39,6 +43,7 @@ import
org.apache.gravitino.storage.relational.session.SqlSessionFactoryHelper;
import org.apache.ibatis.session.SqlSession;
import org.junit.jupiter.api.Assertions;
import org.junit.jupiter.api.TestTemplate;
+import org.opentest4j.AssertionFailedError;
public class TestSQLScripts extends TestJDBCBackend {
@@ -255,6 +260,340 @@ public class TestSQLScripts extends TestJDBCBackend {
}
}
+ /** Verifies a completed upgrade can be repeated without resetting migrated
data. */
+ @TestTemplate
+ public void testUpgradeToTwoZeroIsIdempotent() throws SQLException,
IOException {
+ Path scriptDir = upgradeScriptDirectory();
+ String suffix = "-" + backendType.toLowerCase() + ".sql";
+ File upgrade = scriptDir.resolve("upgrade-1.3.0-to-2.0.0" +
suffix).toFile();
+ dropAllTables();
+ executeScript(scriptDir.resolve("schema-1.3.0" + suffix).toFile());
+ seedUpgradeData();
+ executeScript(upgrade);
+ executeStatements(
+ List.of(
+ "UPDATE idp_user_meta SET enabled = FALSE, audit_info = 'preserved
audit'",
+ "UPDATE idp_group_meta SET group_comment = 'preserved comment'",
+ "UPDATE tag_meta SET allowed_values = '[\"blue\"]'",
+ "UPDATE tag_relation_meta SET tag_value = 'blue'",
+ "UPDATE fileset_meta SET occ_version = 42",
+ "UPDATE policy_meta SET occ_version = 43"),
+ "customized migrated data");
+ Map<String, Set<String>> expectedColumns = readSchemaColumns();
+ List<String> expectedIndexes = readSchemaIndexes(expectedColumns.keySet());
+ Map<String, List<List<String>>> expectedData = readUpgradeData();
+ executeScript(upgrade);
+ executeScript(upgrade);
+ Assertions.assertEquals(expectedColumns, readSchemaColumns());
+ Assertions.assertEquals(expectedIndexes,
readSchemaIndexes(expectedColumns.keySet()));
+ Assertions.assertEquals(expectedData, readUpgradeData(), "Retry must
preserve migrated values");
+ if ("mysql".equals(backendType)) {
+ // Neither rename source nor target exists: this is damage, not an
already-applied DDL.
+ executeStatements(
+ List.of("ALTER TABLE schema_meta DROP INDEX schema_meta_idx_mid"),
+ "missing target index");
+ AssertionFailedError failure =
+ Assertions.assertThrows(AssertionFailedError.class, () ->
executeScript(upgrade));
+ Assertions.assertInstanceOf(SQLException.class, failure.getCause());
+ Assertions.assertTrue(
+ failure.getCause().getMessage().contains("'idx_mid'"),
failure.getMessage());
+ }
+ if ("h2".equals(backendType)) {
+ executeStatements(
+ List.of("ALTER INDEX idx_tid_value RENAME TO wrong_idx_tid_value"),
+ "rename explicitly named H2 index");
+ try {
+ Assertions.assertNotEquals(
+ expectedIndexes,
+ readSchemaIndexes(expectedColumns.keySet()),
+ "An explicitly named H2 index must retain its name in schema
comparisons");
+ } finally {
+ executeStatements(
+ List.of("ALTER INDEX wrong_idx_tid_value RENAME TO idx_tid_value"),
+ "restore explicitly named H2 index");
+ }
+ }
+ assertSchemaMatchesFreshInstall(expectedColumns, expectedIndexes);
+ }
+
+ /** Verifies retries after structural DDL and data updates, including
prepared DDL execution. */
+ @TestTemplate
+ public void testUpgradeToTwoZeroRetriesPartialExecution() throws
SQLException, IOException {
+ Path scriptDir = upgradeScriptDirectory();
+ String suffix = "-" + backendType.toLowerCase() + ".sql";
+ File schema = scriptDir.resolve("schema-1.3.0" + suffix).toFile();
+ File upgrade = scriptDir.resolve("upgrade-1.3.0-to-2.0.0" +
suffix).toFile();
+ List<String> statements = extractStatements(upgrade.toPath());
+ dropAllTables();
+ executeScript(schema);
+ seedUpgradeData();
+ executeScript(upgrade);
+ Map<String, Set<String>> expectedColumns = readSchemaColumns();
+ List<String> expectedIndexes = readSchemaIndexes(expectedColumns.keySet());
+ Map<String, List<List<String>>> expectedData = readUpgradeData();
+ assertSchemaMatchesFreshInstall(expectedColumns, expectedIndexes);
+
+ String lastIndexRename =
+ statements.stream()
+ .filter(sql -> sql.contains("RENAME INDEX"))
+ .reduce((first, last) -> last)
+ .orElse("");
+ boolean testedIndexRename = false;
+ for (int completed = 1; completed <= statements.size(); completed++) {
+ String last = statements.get(completed - 1).toUpperCase();
+ if (!(last.startsWith("ALTER TABLE")
+ || last.startsWith("CREATE ")
+ || last.startsWith("UPDATE ")
+ || last.startsWith("EXECUTE "))) {
+ continue;
+ }
+ // MySQL renames share one guard shape; sample the first and last rename.
+ if (last.startsWith("EXECUTE ")
+ && completed >= 3
+ && statements.get(completed - 3).contains("RENAME INDEX")) {
+ if (testedIndexRename && !statements.get(completed -
3).equals(lastIndexRename)) {
+ continue;
+ }
+ testedIndexRename = true;
+ }
+ dropAllTables();
+ executeScript(schema);
+ seedUpgradeData();
+ // Close the connection after the prefix, as if the migration process
stopped here.
+ String checkpoint = "Interrupted after statement " + completed + ": " +
last;
+ executeStatements(statements.subList(0, completed), checkpoint);
+ executeScript(upgrade);
+ Assertions.assertEquals(expectedColumns, readSchemaColumns(),
checkpoint);
+ Assertions.assertEquals(
+ expectedIndexes, readSchemaIndexes(expectedColumns.keySet()),
checkpoint);
+ Assertions.assertEquals(expectedData, readUpgradeData(), checkpoint);
+ if ("mysql".equals(backendType)) {
+ try (SqlSession session =
+
SqlSessionFactoryHelper.getInstance().getSqlSessionFactory().openSession(true);
+ Connection connection = session.getConnection();
+ ResultSet keys =
+ connection
+ .getMetaData()
+ .getPrimaryKeys(connection.getCatalog(), null,
"table_version_info")) {
+ List<String> columns = new ArrayList<>();
+ while (keys.next()) {
+ columns.add(keys.getString("COLUMN_NAME"));
+ }
+ Assertions.assertEquals(
+ Set.of("table_id", "version", "deleted_at"),
Set.copyOf(columns), checkpoint);
+ }
+ }
+ }
+ }
+
+ /** Verifies unexpected MySQL primary keys are rejected rather than silently
accepted. */
+ @TestTemplate
+ public void testUpgradeToTwoZeroRejectsUnexpectedPrimaryKey() throws
SQLException, IOException {
+ if (!"mysql".equals(backendType)) {
+ return;
+ }
+ Path scriptDir = upgradeScriptDirectory();
+ for (String key :
+ List.of("table_id", "version, table_id, deleted_at", "table_id,
version, deleted_at")) {
+ dropAllTables();
+ executeScript(scriptDir.resolve("schema-1.3.0-mysql.sql").toFile());
+ seedUpgradeData();
+ // The target key is incomplete while the old unique index remains.
+ executeStatements(
+ List.of("ALTER TABLE table_version_info ADD PRIMARY KEY (" + key +
")"),
+ "unexpected primary key");
+ boolean keepLegacyIndex = !key.startsWith("version");
+ if (!keepLegacyIndex) {
+ // Check column order independently of the legacy-index guard.
+ executeStatements(
+ List.of("ALTER TABLE table_version_info DROP INDEX
uk_table_id_version_deleted_at"),
+ "remove legacy index");
+ }
+ AssertionFailedError failure =
+ Assertions.assertThrows(
+ AssertionFailedError.class,
+ () ->
executeScript(scriptDir.resolve("upgrade-1.3.0-to-2.0.0-mysql.sql").toFile()));
+ Assertions.assertInstanceOf(SQLException.class, failure.getCause());
+ String expectedError = keepLegacyIndex ? "primary key" :
"uk_table_id_version_deleted_at";
+ Assertions.assertTrue(
+
failure.getCause().getMessage().toLowerCase().contains(expectedError),
+ failure.getMessage());
+ Assertions.assertEquals(
+ keepLegacyIndex,
+ readSchemaIndexes(Set.of("table_version_info")).stream()
+ .anyMatch(index ->
index.contains("uk_table_id_version_deleted_at")),
+ "Failed conversion must preserve the existing indexes");
+ }
+ }
+
+ private void assertSchemaMatchesFreshInstall(
+ Map<String, Set<String>> upgradedColumns, List<String> upgradedIndexes)
+ throws SQLException, IOException {
+ Map<String, Set<String>> expectedColumns = new TreeMap<>(upgradedColumns);
+ // The migration intentionally retains this legacy table; fresh 2.0
installs omit it.
+ expectedColumns.keySet().removeIf(table ->
table.equalsIgnoreCase("policy_relation_meta"));
+ List<String> expectedIndexes =
+ upgradedIndexes.stream()
+ .filter(index ->
!index.toLowerCase().startsWith("policy_relation_meta:"))
+ .toList();
+ dropAllTables();
+ executeScript(
+ upgradeScriptDirectory().resolve("schema-2.0.0-" + backendType +
".sql").toFile());
+ Map<String, Set<String>> freshColumns = readSchemaColumns();
+ Assertions.assertEquals(freshColumns, expectedColumns, "Upgrade must match
fresh 2.0 columns");
+ Assertions.assertEquals(
+ readSchemaIndexes(freshColumns.keySet()),
+ expectedIndexes,
+ "Upgrade must match fresh 2.0 indexes");
+ }
+
+ private Path upgradeScriptDirectory() {
+ String home = System.getenv("GRAVITINO_HOME");
+ Assertions.assertNotNull(home, "GRAVITINO_HOME environment variable is not
set");
+ return Path.of(home, "scripts", backendType.toLowerCase());
+ }
+
+ private void seedUpgradeData() throws SQLException {
+ executeStatements(
+ List.of(
+ "INSERT INTO idp_user_meta (user_id, user_name, password_hash, "
+ + "current_version, last_version) VALUES (1, 'user', 'hash',
7, 9)",
+ "INSERT INTO idp_group_meta (group_id, group_name) VALUES (1,
'group')",
+ "INSERT INTO tag_meta (tag_id, tag_name, metalake_id, audit_info)
VALUES (1, "
+ + "'tag', 1, '{}')",
+ "INSERT INTO tag_relation_meta (id, tag_id, metadata_object_id, "
+ + "metadata_object_type, audit_info) VALUES (1, 1, 1, 'TABLE',
'{}')",
+ "INSERT INTO fileset_meta (fileset_id, fileset_name, metalake_id,
catalog_id, "
+ + "schema_id, type, audit_info, current_version, last_version)
VALUES (1, "
+ + "'fileset', 1, 1, 1, 'MANAGED', '{}', 7, 9)",
+ "INSERT INTO policy_meta (policy_id, policy_name, policy_type,
metalake_id, "
+ + "audit_info, current_version, last_version) VALUES (1,
'policy', 'custom', 1, "
+ + "'{}', 7, 9)",
+ "INSERT INTO table_version_info (table_id, version, deleted_at)
VALUES (1, 7, 0)"),
+ "source schema data");
+ }
+
+ private Map<String, List<List<String>>> readUpgradeData() throws
SQLException {
+ Map<String, List<List<String>>> data = new TreeMap<>();
+ Map<String, String> selections =
+ Map.of(
+ "idp_user_meta",
+ "user_id, user_name, password_hash, current_version,
last_version, enabled, audit_info",
+ "idp_group_meta", "group_id, group_name, group_comment,
audit_info",
+ "tag_meta", "tag_id, tag_name, allowed_values",
+ "tag_relation_meta", "id, tag_id, metadata_object_id, tag_value",
+ "fileset_meta", "fileset_id, current_version, last_version,
occ_version",
+ "policy_meta", "policy_id, current_version, last_version,
occ_version",
+ "table_version_info", "table_id, version, deleted_at");
+ try (SqlSession session =
+
SqlSessionFactoryHelper.getInstance().getSqlSessionFactory().openSession(true);
+ Connection connection = session.getConnection();
+ Statement statement = connection.createStatement()) {
+ for (Map.Entry<String, String> entry : selections.entrySet()) {
+ List<List<String>> rows = new ArrayList<>();
+ try (ResultSet result =
+ statement.executeQuery(
+ "SELECT " + entry.getValue() + " FROM " + entry.getKey() + "
ORDER BY 1")) {
+ while (result.next()) {
+ List<String> row = new ArrayList<>();
+ for (int column = 1; column <=
result.getMetaData().getColumnCount(); column++) {
+ row.add(result.getString(column));
+ }
+ rows.add(row);
+ }
+ }
+ data.put(entry.getKey(), rows);
+ }
+ }
+ return data;
+ }
+
+ private Map<String, Set<String>> readSchemaColumns() throws SQLException {
+ Map<String, Set<String>> columns = new TreeMap<>();
+ try (SqlSession session =
+
SqlSessionFactoryHelper.getInstance().getSqlSessionFactory().openSession(true);
+ Connection connection = session.getConnection();
+ ResultSet result =
+ connection
+ .getMetaData()
+ .getColumns(connection.getCatalog(), connection.getSchema(),
"%", "%")) {
+ while (result.next()) {
+ columns
+ .computeIfAbsent(result.getString("TABLE_NAME"), key -> new
TreeSet<>())
+ .add(result.getString("COLUMN_NAME"));
+ }
+ }
+ Assertions.assertFalse(columns.isEmpty(), "Schema metadata must contain
columns");
+ return columns;
+ }
+
+ private List<String> readSchemaIndexes(Set<String> tables) throws
SQLException {
+ List<String> indexes = new ArrayList<>();
+ try (SqlSession session =
+
SqlSessionFactoryHelper.getInstance().getSqlSessionFactory().openSession(true);
+ Connection connection = session.getConnection()) {
+ Set<String> generatedIndexes = new TreeSet<>();
+ if ("h2".equals(backendType)) {
+ try (PreparedStatement statement =
+ connection.prepareStatement(
+ "SELECT INDEX_NAME FROM INFORMATION_SCHEMA.INDEXES "
+ + "WHERE TABLE_SCHEMA = ? AND IS_GENERATED = TRUE")) {
+ statement.setString(1, connection.getSchema());
+ try (ResultSet result = statement.executeQuery()) {
+ while (result.next()) {
+ generatedIndexes.add(result.getString("INDEX_NAME"));
+ }
+ }
+ }
+ }
+ for (String table : tables) {
+ try (ResultSet result =
+ connection
+ .getMetaData()
+ .getIndexInfo(
+ connection.getCatalog(), connection.getSchema(), table,
false, false)) {
+ while (result.next()) {
+ if (result.getShort("ORDINAL_POSITION") == 0) {
+ continue;
+ }
+ // H2 appends counters to generated constraint indexes. Keep the
constraint name so
+ // a named unique index and its fresh-install constraint compare
equally; explicit
+ // index names are never normalized.
+ String name = result.getString("INDEX_NAME");
+ if (generatedIndexes.contains(name)) {
+ name =
+ name.replaceFirst("_INDEX_[0-9A-F]+$", "")
+ .replaceFirst("^PRIMARY_KEY_[0-9A-F]+$", "");
+ }
+ indexes.add(
+ table
+ + ":"
+ + name
+ + ":"
+ + result.getShort("ORDINAL_POSITION")
+ + ":"
+ + result.getString("COLUMN_NAME")
+ + ":"
+ + result.getBoolean("NON_UNIQUE"));
+ }
+ }
+ }
+ }
+ indexes.sort(Comparator.naturalOrder());
+ return indexes;
+ }
+
+ private void executeStatements(List<String> statements, String context)
throws SQLException {
+ try (SqlSession session =
+
SqlSessionFactoryHelper.getInstance().getSqlSessionFactory().openSession(true);
+ Connection connection = session.getConnection();
+ Statement statement = connection.createStatement()) {
+ for (String sql : statements) {
+ Assertions.assertDoesNotThrow(() -> statement.execute(sql), context +
", sql: " + sql);
+ }
+ }
+ }
+
private void executeScript(File scriptFile) throws IOException, SQLException
{
List<String> ddls = extractStatements(scriptFile.toPath());
try (SqlSession sqlSession =
diff --git a/docs/how-to-upgrade.md b/docs/how-to-upgrade.md
index b8b465d5b5..12a8b7ae38 100644
--- a/docs/how-to-upgrade.md
+++ b/docs/how-to-upgrade.md
@@ -160,6 +160,11 @@ These scripts should run to completion without any errors.
If you
do encounter errors you need to analyze the cause and attempt to
trace it back to one of the preceding steps.
+For the MySQL, PostgreSQL, and H2 1.3.0-to-2.0.0 upgrade scripts, you can
rerun the
+script after fixing the cause of a partial failure; earlier upgrade scripts
are unchanged.
+Unexpected schema differences, such as a missing MySQL index during a rename,
require
+manual schema repair before retrying.
+
### Step 6: Verify the Upgrade
The final step of the upgrade process is validating your freshly
diff --git a/scripts/h2/upgrade-1.3.0-to-2.0.0-h2.sql
b/scripts/h2/upgrade-1.3.0-to-2.0.0-h2.sql
index f34df6f3e8..6b66d9e791 100644
--- a/scripts/h2/upgrade-1.3.0-to-2.0.0-h2.sql
+++ b/scripts/h2/upgrade-1.3.0-to-2.0.0-h2.sql
@@ -26,29 +26,29 @@
ALTER TABLE `table_column_version_info`
ALTER COLUMN `column_comment` VARCHAR(4096) DEFAULT '';
-ALTER TABLE `model_meta` ADD COLUMN `current_version` INT UNSIGNED NOT NULL
DEFAULT 1 COMMENT 'model current version' AFTER `model_latest_version`;
-ALTER TABLE `model_meta` ADD COLUMN `last_version` INT UNSIGNED NOT NULL
DEFAULT 1 COMMENT 'model last allocated version' AFTER `current_version`;
+ALTER TABLE `model_meta` ADD COLUMN IF NOT EXISTS `current_version` INT
UNSIGNED NOT NULL DEFAULT 1 COMMENT 'model current version' AFTER
`model_latest_version`;
+ALTER TABLE `model_meta` ADD COLUMN IF NOT EXISTS `last_version` INT UNSIGNED
NOT NULL DEFAULT 1 COMMENT 'model last allocated version' AFTER
`current_version`;
-ALTER TABLE `tag_meta` ADD COLUMN `allowed_values` CLOB DEFAULT NULL COMMENT
'tag allowed values as a JSON string array, NULL allows any value, [] allows no
value' AFTER `properties`;
+ALTER TABLE `tag_meta` ADD COLUMN IF NOT EXISTS `allowed_values` CLOB DEFAULT
NULL COMMENT 'tag allowed values as a JSON string array, NULL allows any value,
[] allows no value' AFTER `properties`;
-ALTER TABLE `tag_relation_meta` DROP INDEX `uk_ti_mi_del`;
+ALTER TABLE `tag_relation_meta` DROP CONSTRAINT IF EXISTS `uk_ti_mi_del`;
-ALTER TABLE `tag_relation_meta` ADD COLUMN `tag_value` VARCHAR(256) NOT NULL
DEFAULT '' COMMENT 'tag assignment value, empty string means no value' AFTER
`metadata_object_type`;
+ALTER TABLE `tag_relation_meta` ADD COLUMN IF NOT EXISTS `tag_value`
VARCHAR(256) NOT NULL DEFAULT '' COMMENT 'tag assignment value, empty string
means no value' AFTER `metadata_object_type`;
-ALTER TABLE `idp_user_meta` ADD COLUMN `enabled` TINYINT(1) NOT NULL DEFAULT 1
COMMENT 'whether the user is enabled, 0 is disabled, 1 is enabled' AFTER
`password_hash`;
+ALTER TABLE `idp_user_meta` ADD COLUMN IF NOT EXISTS `enabled` TINYINT(1) NOT
NULL DEFAULT 1 COMMENT 'whether the user is enabled, 0 is disabled, 1 is
enabled' AFTER `password_hash`;
-ALTER TABLE `idp_group_meta` ADD COLUMN `group_comment` VARCHAR(1024) DEFAULT
'' COMMENT 'idp group comment' AFTER `group_name`;
+ALTER TABLE `idp_group_meta` ADD COLUMN IF NOT EXISTS `group_comment`
VARCHAR(1024) DEFAULT '' COMMENT 'idp group comment' AFTER `group_name`;
-ALTER TABLE `idp_user_meta` ADD COLUMN `audit_info` CLOB NOT NULL DEFAULT '{}'
COMMENT 'idp user audit info' AFTER `enabled`;
+ALTER TABLE `idp_user_meta` ADD COLUMN IF NOT EXISTS `audit_info` CLOB NOT
NULL DEFAULT '{}' COMMENT 'idp user audit info' AFTER `enabled`;
-ALTER TABLE `idp_group_meta` ADD COLUMN `audit_info` CLOB NOT NULL DEFAULT
'{}' COMMENT 'idp group audit info' AFTER `group_comment`;
+ALTER TABLE `idp_group_meta` ADD COLUMN IF NOT EXISTS `audit_info` CLOB NOT
NULL DEFAULT '{}' COMMENT 'idp group audit info' AFTER `group_comment`;
-ALTER TABLE `idp_user_group_rel` ADD COLUMN `audit_info` CLOB NOT NULL DEFAULT
'{}' COMMENT 'idp user group relation audit info' AFTER `group_id`;
+ALTER TABLE `idp_user_group_rel` ADD COLUMN IF NOT EXISTS `audit_info` CLOB
NOT NULL DEFAULT '{}' COMMENT 'idp user group relation audit info' AFTER
`group_id`;
CREATE UNIQUE INDEX IF NOT EXISTS `uk_ti_mi_mo_tv_del` ON `tag_relation_meta`
(`tag_id`, `metadata_object_id`, `metadata_object_type`, `tag_value`,
`deleted_at`);
CREATE INDEX IF NOT EXISTS `idx_tid_value` ON `tag_relation_meta` (`tag_id`,
`tag_value`);
-ALTER TABLE `job_run_meta` ADD COLUMN `job_started_at` BIGINT(20) UNSIGNED NOT
NULL DEFAULT 0 COMMENT 'job started at' AFTER `job_run_status`;
+ALTER TABLE `job_run_meta` ADD COLUMN IF NOT EXISTS `job_started_at`
BIGINT(20) UNSIGNED NOT NULL DEFAULT 0 COMMENT 'job started at' AFTER
`job_run_status`;
CREATE TABLE IF NOT EXISTS `policy_tag_relation_meta` (
`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'auto increment
id',
@@ -64,7 +64,7 @@ CREATE TABLE IF NOT EXISTS `policy_tag_relation_meta` (
KEY `policy_tag_relation_meta_idx_tag_id` (`tag_id`)
) ENGINE=InnoDB;
-ALTER TABLE `job_run_meta` ADD COLUMN `runtime_job_template` CLOB DEFAULT NULL
COMMENT 'job run runtime job template' AFTER `job_finished_at`;
+ALTER TABLE `job_run_meta` ADD COLUMN IF NOT EXISTS `runtime_job_template`
CLOB DEFAULT NULL COMMENT 'job run runtime job template' AFTER
`job_finished_at`;
CREATE TABLE IF NOT EXISTS `semantic_model_meta` (
`semantic_model_id` BIGINT(20) UNSIGNED NOT NULL COMMENT 'semantic model
id',
@@ -122,7 +122,7 @@ UPDATE `owner_meta`
-- only when the stored snapshot changes. The default is the whole backfill,
because `occ_version`
-- is only ever compared against itself on the same row.
ALTER TABLE `fileset_meta`
- ADD COLUMN `occ_version` INT UNSIGNED NOT NULL DEFAULT 1 COMMENT 'fileset
optimistic concurrency version' AFTER `last_version`;
+ ADD COLUMN IF NOT EXISTS `occ_version` INT UNSIGNED NOT NULL DEFAULT 1
COMMENT 'fileset optimistic concurrency version' AFTER `last_version`;
ALTER TABLE `policy_meta`
- ADD COLUMN `occ_version` INT UNSIGNED NOT NULL DEFAULT 1 COMMENT 'policy
optimistic concurrency version' AFTER `last_version`;
+ ADD COLUMN IF NOT EXISTS `occ_version` INT UNSIGNED NOT NULL DEFAULT 1
COMMENT 'policy optimistic concurrency version' AFTER `last_version`;
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 121271190b..2815471571 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
@@ -17,30 +17,64 @@
-- under the License.
--
+-- MySQL has no ADD COLUMN / CREATE INDEX IF NOT EXISTS. Guard completed DDL
+-- so a failed upgrade can be restarted from the beginning without --force.
+-- Keep each operation in this session; no stored routines or extra privileges
are required.
+
-- Preserve policy_relation_meta from pre-2.0 installations, including its
existing data.
-- The 2.0 server no longer reads direct object-policy assignments from this
table.
ALTER TABLE `table_column_version_info`
MODIFY COLUMN `column_comment` VARCHAR(4096) DEFAULT '' COMMENT 'column
comment';
-ALTER TABLE `tag_meta`
- ADD COLUMN `allowed_values` MEDIUMTEXT DEFAULT NULL COMMENT 'tag allowed
values as a JSON string array, NULL allows any value, [] allows no value' AFTER
`properties`;
+SET @ddl = IF(EXISTS(SELECT 1 FROM information_schema.columns
+ WHERE table_schema = DATABASE() AND table_name = 'tag_meta' AND
column_name = 'allowed_values'),
+ 'SELECT 1',
+ 'ALTER TABLE `tag_meta` ADD COLUMN `allowed_values` MEDIUMTEXT DEFAULT
NULL COMMENT ''tag allowed values as a JSON string array, NULL allows any
value, [] allows no value'' AFTER `properties`');
+PREPARE stmt FROM @ddl;
+EXECUTE stmt;
+DEALLOCATE PREPARE stmt;
-ALTER TABLE `tag_relation_meta`
- DROP INDEX `uk_ti_mi_mo_del`;
+SET @ddl = IF(NOT EXISTS(SELECT 1 FROM information_schema.statistics
+ WHERE table_schema = DATABASE() AND table_name = 'tag_relation_meta' AND
index_name = 'uk_ti_mi_mo_del'),
+ 'SELECT 1',
+ 'ALTER TABLE `tag_relation_meta` DROP INDEX `uk_ti_mi_mo_del`');
+PREPARE stmt FROM @ddl;
+EXECUTE stmt;
+DEALLOCATE PREPARE stmt;
-ALTER TABLE `tag_relation_meta`
- ADD COLUMN `tag_value` VARCHAR(256) NOT NULL DEFAULT '' COMMENT 'tag
assignment value, empty string means no value' AFTER `metadata_object_type`;
+SET @ddl = IF(EXISTS(SELECT 1 FROM information_schema.columns
+ WHERE table_schema = DATABASE() AND table_name = 'tag_relation_meta' AND
column_name = 'tag_value'),
+ 'SELECT 1',
+ 'ALTER TABLE `tag_relation_meta` ADD COLUMN `tag_value` VARCHAR(256) NOT
NULL DEFAULT '''' COMMENT ''tag assignment value, empty string means no value''
AFTER `metadata_object_type`');
+PREPARE stmt FROM @ddl;
+EXECUTE stmt;
+DEALLOCATE PREPARE stmt;
-ALTER TABLE `idp_user_meta`
- ADD COLUMN `enabled` TINYINT(1) NOT NULL DEFAULT 1 COMMENT 'whether the
user is enabled, 0 is disabled, 1 is enabled' AFTER `password_hash`;
+SET @ddl = IF(EXISTS(SELECT 1 FROM information_schema.columns
+ WHERE table_schema = DATABASE() AND table_name = 'idp_user_meta' AND
column_name = 'enabled'),
+ 'SELECT 1',
+ 'ALTER TABLE `idp_user_meta` ADD COLUMN `enabled` TINYINT(1) NOT NULL
DEFAULT 1 COMMENT ''whether the user is enabled, 0 is disabled, 1 is enabled''
AFTER `password_hash`');
+PREPARE stmt FROM @ddl;
+EXECUTE stmt;
+DEALLOCATE PREPARE stmt;
-ALTER TABLE `idp_group_meta`
- ADD COLUMN `group_comment` VARCHAR(1024) DEFAULT '' COMMENT 'idp group
comment' AFTER `group_name`;
+SET @ddl = IF(EXISTS(SELECT 1 FROM information_schema.columns
+ WHERE table_schema = DATABASE() AND table_name = 'idp_group_meta' AND
column_name = 'group_comment'),
+ 'SELECT 1',
+ 'ALTER TABLE `idp_group_meta` ADD COLUMN `group_comment` VARCHAR(1024)
DEFAULT '''' COMMENT ''idp group comment'' AFTER `group_name`');
+PREPARE stmt FROM @ddl;
+EXECUTE stmt;
+DEALLOCATE PREPARE stmt;
-- add audit_info as nullable first for MySQL 5.7 compatibility (TEXT cannot
have defaults)
-ALTER TABLE `idp_user_meta`
- ADD COLUMN `audit_info` MEDIUMTEXT COMMENT 'idp user audit info' AFTER
`enabled`;
+SET @ddl = IF(EXISTS(SELECT 1 FROM information_schema.columns
+ WHERE table_schema = DATABASE() AND table_name = 'idp_user_meta' AND
column_name = 'audit_info'),
+ 'SELECT 1',
+ 'ALTER TABLE `idp_user_meta` ADD COLUMN `audit_info` MEDIUMTEXT COMMENT
''idp user audit info'' AFTER `enabled`');
+PREPARE stmt FROM @ddl;
+EXECUTE stmt;
+DEALLOCATE PREPARE stmt;
UPDATE `idp_user_meta`
SET `audit_info` = '{}'
@@ -49,8 +83,13 @@ UPDATE `idp_user_meta`
ALTER TABLE `idp_user_meta`
MODIFY COLUMN `audit_info` MEDIUMTEXT NOT NULL COMMENT 'idp user audit
info' AFTER `enabled`;
-ALTER TABLE `idp_group_meta`
- ADD COLUMN `audit_info` MEDIUMTEXT COMMENT 'idp group audit info' AFTER
`group_comment`;
+SET @ddl = IF(EXISTS(SELECT 1 FROM information_schema.columns
+ WHERE table_schema = DATABASE() AND table_name = 'idp_group_meta' AND
column_name = 'audit_info'),
+ 'SELECT 1',
+ 'ALTER TABLE `idp_group_meta` ADD COLUMN `audit_info` MEDIUMTEXT COMMENT
''idp group audit info'' AFTER `group_comment`');
+PREPARE stmt FROM @ddl;
+EXECUTE stmt;
+DEALLOCATE PREPARE stmt;
UPDATE `idp_group_meta`
SET `audit_info` = '{}'
@@ -59,8 +98,13 @@ UPDATE `idp_group_meta`
ALTER TABLE `idp_group_meta`
MODIFY COLUMN `audit_info` MEDIUMTEXT NOT NULL COMMENT 'idp group audit
info' AFTER `group_comment`;
-ALTER TABLE `idp_user_group_rel`
- ADD COLUMN `audit_info` MEDIUMTEXT COMMENT 'idp user group relation audit
info' AFTER `group_id`;
+SET @ddl = IF(EXISTS(SELECT 1 FROM information_schema.columns
+ WHERE table_schema = DATABASE() AND table_name = 'idp_user_group_rel' AND
column_name = 'audit_info'),
+ 'SELECT 1',
+ 'ALTER TABLE `idp_user_group_rel` ADD COLUMN `audit_info` MEDIUMTEXT
COMMENT ''idp user group relation audit info'' AFTER `group_id`');
+PREPARE stmt FROM @ddl;
+EXECUTE stmt;
+DEALLOCATE PREPARE stmt;
UPDATE `idp_user_group_rel`
SET `audit_info` = '{}'
@@ -69,48 +113,351 @@ UPDATE `idp_user_group_rel`
ALTER TABLE `idp_user_group_rel`
MODIFY COLUMN `audit_info` MEDIUMTEXT NOT NULL COMMENT 'idp user group
relation audit info' AFTER `group_id`;
-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`);
+SET @ddl = IF(EXISTS(SELECT 1 FROM information_schema.statistics
+ WHERE table_schema = DATABASE() AND table_name = 'tag_relation_meta' AND
index_name = 'uk_ti_mi_mo_tv_del'),
+ 'SELECT 1',
+ '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`)');
+PREPARE stmt FROM @ddl;
+EXECUTE stmt;
+DEALLOCATE PREPARE stmt;
+
+SET @ddl = IF(EXISTS(SELECT 1 FROM information_schema.statistics
+ WHERE table_schema = DATABASE() AND table_name = 'tag_relation_meta' AND
index_name = 'idx_tid_value'),
+ 'SELECT 1',
+ 'CREATE INDEX `idx_tid_value` ON `tag_relation_meta` (`tag_id`,
`tag_value`)');
+PREPARE stmt FROM @ddl;
+EXECUTE stmt;
+DEALLOCATE PREPARE stmt;
-- 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_meta`
- ADD COLUMN `current_version` INT UNSIGNED NOT NULL DEFAULT 1 COMMENT
'model current version' AFTER `model_latest_version`,
- ADD COLUMN `last_version` INT UNSIGNED NOT NULL DEFAULT 1 COMMENT 'model
last allocated version' AFTER `current_version`;
-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 `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`;
+SET @ddl = IF(NOT EXISTS(SELECT 1 FROM information_schema.statistics
+ WHERE table_schema = DATABASE() AND table_name = 'schema_meta' AND
index_name = 'idx_mid')
+ AND EXISTS(SELECT 1 FROM information_schema.statistics
+ WHERE table_schema = DATABASE() AND table_name = 'schema_meta' AND
index_name = 'schema_meta_idx_mid'),
+ 'SELECT 1',
+ 'ALTER TABLE `schema_meta` RENAME INDEX `idx_mid` TO
`schema_meta_idx_mid`');
+PREPARE stmt FROM @ddl;
+EXECUTE stmt;
+DEALLOCATE PREPARE stmt;
+
+SET @ddl = IF(NOT EXISTS(SELECT 1 FROM information_schema.statistics
+ WHERE table_schema = DATABASE() AND table_name = 'table_meta' AND
index_name = 'uk_sid_tn_del')
+ AND EXISTS(SELECT 1 FROM information_schema.statistics
+ WHERE table_schema = DATABASE() AND table_name = 'table_meta' AND
index_name = 'table_meta_uk_sid_tn_del'),
+ 'SELECT 1',
+ 'ALTER TABLE `table_meta` RENAME INDEX `uk_sid_tn_del` TO
`table_meta_uk_sid_tn_del`');
+PREPARE stmt FROM @ddl;
+EXECUTE stmt;
+DEALLOCATE PREPARE stmt;
+
+SET @ddl = IF(NOT EXISTS(SELECT 1 FROM information_schema.statistics
+ WHERE table_schema = DATABASE() AND table_name = 'table_meta' AND
index_name = 'idx_mid')
+ AND EXISTS(SELECT 1 FROM information_schema.statistics
+ WHERE table_schema = DATABASE() AND table_name = 'table_meta' AND
index_name = 'table_meta_idx_mid'),
+ 'SELECT 1',
+ 'ALTER TABLE `table_meta` RENAME INDEX `idx_mid` TO `table_meta_idx_mid`');
+PREPARE stmt FROM @ddl;
+EXECUTE stmt;
+DEALLOCATE PREPARE stmt;
+
+SET @ddl = IF(NOT EXISTS(SELECT 1 FROM information_schema.statistics
+ WHERE table_schema = DATABASE() AND table_name = 'table_meta' AND
index_name = 'idx_cid')
+ AND EXISTS(SELECT 1 FROM information_schema.statistics
+ WHERE table_schema = DATABASE() AND table_name = 'table_meta' AND
index_name = 'table_meta_idx_cid'),
+ 'SELECT 1',
+ 'ALTER TABLE `table_meta` RENAME INDEX `idx_cid` TO `table_meta_idx_cid`');
+PREPARE stmt FROM @ddl;
+EXECUTE stmt;
+DEALLOCATE PREPARE stmt;
+
+SET @ddl = IF(NOT EXISTS(SELECT 1 FROM information_schema.statistics
+ WHERE table_schema = DATABASE() AND table_name =
'table_column_version_info' AND index_name = 'idx_mid')
+ AND EXISTS(SELECT 1 FROM information_schema.statistics
+ WHERE table_schema = DATABASE() AND table_name =
'table_column_version_info' AND index_name =
'table_column_version_info_idx_mid'),
+ 'SELECT 1',
+ 'ALTER TABLE `table_column_version_info` RENAME INDEX `idx_mid` TO
`table_column_version_info_idx_mid`');
+PREPARE stmt FROM @ddl;
+EXECUTE stmt;
+DEALLOCATE PREPARE stmt;
+
+SET @ddl = IF(NOT EXISTS(SELECT 1 FROM information_schema.statistics
+ WHERE table_schema = DATABASE() AND table_name =
'table_column_version_info' AND index_name = 'idx_cid')
+ AND EXISTS(SELECT 1 FROM information_schema.statistics
+ WHERE table_schema = DATABASE() AND table_name =
'table_column_version_info' AND index_name =
'table_column_version_info_idx_cid'),
+ 'SELECT 1',
+ 'ALTER TABLE `table_column_version_info` RENAME INDEX `idx_cid` TO
`table_column_version_info_idx_cid`');
+PREPARE stmt FROM @ddl;
+EXECUTE stmt;
+DEALLOCATE PREPARE stmt;
+
+SET @ddl = IF(NOT EXISTS(SELECT 1 FROM information_schema.statistics
+ WHERE table_schema = DATABASE() AND table_name =
'table_column_version_info' AND index_name = 'idx_sid')
+ AND EXISTS(SELECT 1 FROM information_schema.statistics
+ WHERE table_schema = DATABASE() AND table_name =
'table_column_version_info' AND index_name =
'table_column_version_info_idx_sid'),
+ 'SELECT 1',
+ 'ALTER TABLE `table_column_version_info` RENAME INDEX `idx_sid` TO
`table_column_version_info_idx_sid`');
+PREPARE stmt FROM @ddl;
+EXECUTE stmt;
+DEALLOCATE PREPARE stmt;
+
+SET @ddl = IF(NOT EXISTS(SELECT 1 FROM information_schema.statistics
+ WHERE table_schema = DATABASE() AND table_name = 'fileset_meta' AND
index_name = 'uk_sid_fn_del')
+ AND EXISTS(SELECT 1 FROM information_schema.statistics
+ WHERE table_schema = DATABASE() AND table_name = 'fileset_meta' AND
index_name = 'fileset_meta_uk_sid_fn_del'),
+ 'SELECT 1',
+ 'ALTER TABLE `fileset_meta` RENAME INDEX `uk_sid_fn_del` TO
`fileset_meta_uk_sid_fn_del`');
+PREPARE stmt FROM @ddl;
+EXECUTE stmt;
+DEALLOCATE PREPARE stmt;
+
+SET @ddl = IF(NOT EXISTS(SELECT 1 FROM information_schema.statistics
+ WHERE table_schema = DATABASE() AND table_name = 'fileset_meta' AND
index_name = 'idx_mid')
+ AND EXISTS(SELECT 1 FROM information_schema.statistics
+ WHERE table_schema = DATABASE() AND table_name = 'fileset_meta' AND
index_name = 'fileset_meta_idx_mid'),
+ 'SELECT 1',
+ 'ALTER TABLE `fileset_meta` RENAME INDEX `idx_mid` TO
`fileset_meta_idx_mid`');
+PREPARE stmt FROM @ddl;
+EXECUTE stmt;
+DEALLOCATE PREPARE stmt;
+
+SET @ddl = IF(NOT EXISTS(SELECT 1 FROM information_schema.statistics
+ WHERE table_schema = DATABASE() AND table_name = 'fileset_meta' AND
index_name = 'idx_cid')
+ AND EXISTS(SELECT 1 FROM information_schema.statistics
+ WHERE table_schema = DATABASE() AND table_name = 'fileset_meta' AND
index_name = 'fileset_meta_idx_cid'),
+ 'SELECT 1',
+ 'ALTER TABLE `fileset_meta` RENAME INDEX `idx_cid` TO
`fileset_meta_idx_cid`');
+PREPARE stmt FROM @ddl;
+EXECUTE stmt;
+DEALLOCATE PREPARE stmt;
+
+SET @ddl = IF(NOT EXISTS(SELECT 1 FROM information_schema.statistics
+ WHERE table_schema = DATABASE() AND table_name = 'fileset_version_info'
AND index_name = 'idx_mid')
+ AND EXISTS(SELECT 1 FROM information_schema.statistics
+ WHERE table_schema = DATABASE() AND table_name = 'fileset_version_info'
AND index_name = 'fileset_version_info_idx_mid'),
+ 'SELECT 1',
+ 'ALTER TABLE `fileset_version_info` RENAME INDEX `idx_mid` TO
`fileset_version_info_idx_mid`');
+PREPARE stmt FROM @ddl;
+EXECUTE stmt;
+DEALLOCATE PREPARE stmt;
+
+SET @ddl = IF(NOT EXISTS(SELECT 1 FROM information_schema.statistics
+ WHERE table_schema = DATABASE() AND table_name = 'fileset_version_info'
AND index_name = 'idx_cid')
+ AND EXISTS(SELECT 1 FROM information_schema.statistics
+ WHERE table_schema = DATABASE() AND table_name = 'fileset_version_info'
AND index_name = 'fileset_version_info_idx_cid'),
+ 'SELECT 1',
+ 'ALTER TABLE `fileset_version_info` RENAME INDEX `idx_cid` TO
`fileset_version_info_idx_cid`');
+PREPARE stmt FROM @ddl;
+EXECUTE stmt;
+DEALLOCATE PREPARE stmt;
+
+SET @ddl = IF(NOT EXISTS(SELECT 1 FROM information_schema.statistics
+ WHERE table_schema = DATABASE() AND table_name = 'fileset_version_info'
AND index_name = 'idx_sid')
+ AND EXISTS(SELECT 1 FROM information_schema.statistics
+ WHERE table_schema = DATABASE() AND table_name = 'fileset_version_info'
AND index_name = 'fileset_version_info_idx_sid'),
+ 'SELECT 1',
+ 'ALTER TABLE `fileset_version_info` RENAME INDEX `idx_sid` TO
`fileset_version_info_idx_sid`');
+PREPARE stmt FROM @ddl;
+EXECUTE stmt;
+DEALLOCATE PREPARE stmt;
+
+SET @ddl = IF(NOT EXISTS(SELECT 1 FROM information_schema.statistics
+ WHERE table_schema = DATABASE() AND table_name = 'topic_meta' AND
index_name = 'uk_sid_tn_del')
+ AND EXISTS(SELECT 1 FROM information_schema.statistics
+ WHERE table_schema = DATABASE() AND table_name = 'topic_meta' AND
index_name = 'topic_meta_uk_sid_tn_del'),
+ 'SELECT 1',
+ 'ALTER TABLE `topic_meta` RENAME INDEX `uk_sid_tn_del` TO
`topic_meta_uk_sid_tn_del`');
+PREPARE stmt FROM @ddl;
+EXECUTE stmt;
+DEALLOCATE PREPARE stmt;
+
+SET @ddl = IF(NOT EXISTS(SELECT 1 FROM information_schema.statistics
+ WHERE table_schema = DATABASE() AND table_name = 'topic_meta' AND
index_name = 'idx_mid')
+ AND EXISTS(SELECT 1 FROM information_schema.statistics
+ WHERE table_schema = DATABASE() AND table_name = 'topic_meta' AND
index_name = 'topic_meta_idx_mid'),
+ 'SELECT 1',
+ 'ALTER TABLE `topic_meta` RENAME INDEX `idx_mid` TO `topic_meta_idx_mid`');
+PREPARE stmt FROM @ddl;
+EXECUTE stmt;
+DEALLOCATE PREPARE stmt;
+
+SET @ddl = IF(NOT EXISTS(SELECT 1 FROM information_schema.statistics
+ WHERE table_schema = DATABASE() AND table_name = 'topic_meta' AND
index_name = 'idx_cid')
+ AND EXISTS(SELECT 1 FROM information_schema.statistics
+ WHERE table_schema = DATABASE() AND table_name = 'topic_meta' AND
index_name = 'topic_meta_idx_cid'),
+ 'SELECT 1',
+ 'ALTER TABLE `topic_meta` RENAME INDEX `idx_cid` TO `topic_meta_idx_cid`');
+PREPARE stmt FROM @ddl;
+EXECUTE stmt;
+DEALLOCATE PREPARE stmt;
+
+SET @ddl = IF(NOT EXISTS(SELECT 1 FROM information_schema.statistics
+ WHERE table_schema = DATABASE() AND table_name = 'user_role_rel' AND
index_name = 'idx_rid')
+ AND EXISTS(SELECT 1 FROM information_schema.statistics
+ WHERE table_schema = DATABASE() AND table_name = 'user_role_rel' AND
index_name = 'user_role_rel_idx_rid'),
+ 'SELECT 1',
+ 'ALTER TABLE `user_role_rel` RENAME INDEX `idx_rid` TO
`user_role_rel_idx_rid`');
+PREPARE stmt FROM @ddl;
+EXECUTE stmt;
+DEALLOCATE PREPARE stmt;
+
+SET @ddl = IF(NOT EXISTS(SELECT 1 FROM information_schema.statistics
+ WHERE table_schema = DATABASE() AND table_name = 'group_role_rel' AND
index_name = 'idx_rid')
+ AND EXISTS(SELECT 1 FROM information_schema.statistics
+ WHERE table_schema = DATABASE() AND table_name = 'group_role_rel' AND
index_name = 'group_role_rel_idx_rid'),
+ 'SELECT 1',
+ 'ALTER TABLE `group_role_rel` RENAME INDEX `idx_rid` TO
`group_role_rel_idx_rid`');
+PREPARE stmt FROM @ddl;
+EXECUTE stmt;
+DEALLOCATE PREPARE stmt;
+
+SET @ddl = IF(NOT EXISTS(SELECT 1 FROM information_schema.statistics
+ WHERE table_schema = DATABASE() AND table_name = 'tag_relation_meta' AND
index_name = 'idx_mid')
+ AND EXISTS(SELECT 1 FROM information_schema.statistics
+ WHERE table_schema = DATABASE() AND table_name = 'tag_relation_meta' AND
index_name = 'tag_relation_meta_idx_mid'),
+ 'SELECT 1',
+ 'ALTER TABLE `tag_relation_meta` RENAME INDEX `idx_mid` TO
`tag_relation_meta_idx_mid`');
+PREPARE stmt FROM @ddl;
+EXECUTE stmt;
+DEALLOCATE PREPARE stmt;
+
+SET @ddl = IF(NOT EXISTS(SELECT 1 FROM information_schema.statistics
+ WHERE table_schema = DATABASE() AND table_name = 'model_meta' AND
index_name = 'idx_mid')
+ AND EXISTS(SELECT 1 FROM information_schema.statistics
+ WHERE table_schema = DATABASE() AND table_name = 'model_meta' AND
index_name = 'model_meta_idx_mid'),
+ 'SELECT 1',
+ 'ALTER TABLE `model_meta` RENAME INDEX `idx_mid` TO `model_meta_idx_mid`');
+PREPARE stmt FROM @ddl;
+EXECUTE stmt;
+DEALLOCATE PREPARE stmt;
+
+SET @ddl = IF(NOT EXISTS(SELECT 1 FROM information_schema.statistics
+ WHERE table_schema = DATABASE() AND table_name = 'model_meta' AND
index_name = 'idx_cid')
+ AND EXISTS(SELECT 1 FROM information_schema.statistics
+ WHERE table_schema = DATABASE() AND table_name = 'model_meta' AND
index_name = 'model_meta_idx_cid'),
+ 'SELECT 1',
+ 'ALTER TABLE `model_meta` RENAME INDEX `idx_cid` TO `model_meta_idx_cid`');
+PREPARE stmt FROM @ddl;
+EXECUTE stmt;
+DEALLOCATE PREPARE stmt;
+
+SET @ddl = IF(EXISTS(SELECT 1 FROM information_schema.columns
+ WHERE table_schema = DATABASE() AND table_name = 'model_meta' AND
column_name = 'current_version'),
+ 'SELECT 1',
+ 'ALTER TABLE `model_meta` ADD COLUMN `current_version` INT UNSIGNED NOT
NULL DEFAULT 1 COMMENT ''model current version'' AFTER `model_latest_version`');
+PREPARE stmt FROM @ddl;
+EXECUTE stmt;
+DEALLOCATE PREPARE stmt;
+
+SET @ddl = IF(EXISTS(SELECT 1 FROM information_schema.columns
+ WHERE table_schema = DATABASE() AND table_name = 'model_meta' AND
column_name = 'last_version'),
+ 'SELECT 1',
+ 'ALTER TABLE `model_meta` ADD COLUMN `last_version` INT UNSIGNED NOT NULL
DEFAULT 1 COMMENT ''model last allocated version'' AFTER `current_version`');
+PREPARE stmt FROM @ddl;
+EXECUTE stmt;
+DEALLOCATE PREPARE stmt;
+
+SET @ddl = IF(NOT EXISTS(SELECT 1 FROM information_schema.statistics
+ WHERE table_schema = DATABASE() AND table_name = 'model_version_info' AND
index_name = 'idx_mid')
+ AND EXISTS(SELECT 1 FROM information_schema.statistics
+ WHERE table_schema = DATABASE() AND table_name = 'model_version_info' AND
index_name = 'model_version_info_idx_mid'),
+ 'SELECT 1',
+ 'ALTER TABLE `model_version_info` RENAME INDEX `idx_mid` TO
`model_version_info_idx_mid`');
+PREPARE stmt FROM @ddl;
+EXECUTE stmt;
+DEALLOCATE PREPARE stmt;
+
+SET @ddl = IF(NOT EXISTS(SELECT 1 FROM information_schema.statistics
+ WHERE table_schema = DATABASE() AND table_name = 'model_version_info' AND
index_name = 'idx_cid')
+ AND EXISTS(SELECT 1 FROM information_schema.statistics
+ WHERE table_schema = DATABASE() AND table_name = 'model_version_info' AND
index_name = 'model_version_info_idx_cid'),
+ 'SELECT 1',
+ 'ALTER TABLE `model_version_info` RENAME INDEX `idx_cid` TO
`model_version_info_idx_cid`');
+PREPARE stmt FROM @ddl;
+EXECUTE stmt;
+DEALLOCATE PREPARE stmt;
+
+SET @ddl = IF(NOT EXISTS(SELECT 1 FROM information_schema.statistics
+ WHERE table_schema = DATABASE() AND table_name = 'model_version_info' AND
index_name = 'idx_sid')
+ AND EXISTS(SELECT 1 FROM information_schema.statistics
+ WHERE table_schema = DATABASE() AND table_name = 'model_version_info' AND
index_name = 'model_version_info_idx_sid'),
+ 'SELECT 1',
+ 'ALTER TABLE `model_version_info` RENAME INDEX `idx_sid` TO
`model_version_info_idx_sid`');
+PREPARE stmt FROM @ddl;
+EXECUTE stmt;
+DEALLOCATE PREPARE stmt;
+
+SET @ddl = IF(NOT EXISTS(SELECT 1 FROM information_schema.statistics
+ WHERE table_schema = DATABASE() AND table_name = 'policy_version_info' AND
index_name = 'idx_mid')
+ AND EXISTS(SELECT 1 FROM information_schema.statistics
+ WHERE table_schema = DATABASE() AND table_name = 'policy_version_info' AND
index_name = 'policy_version_info_idx_mid'),
+ 'SELECT 1',
+ 'ALTER TABLE `policy_version_info` RENAME INDEX `idx_mid` TO
`policy_version_info_idx_mid`');
+PREPARE stmt FROM @ddl;
+EXECUTE stmt;
+DEALLOCATE PREPARE stmt;
+
+SET @ddl = IF(NOT EXISTS(SELECT 1 FROM information_schema.statistics
+ WHERE table_schema = DATABASE() AND table_name = 'function_meta' AND
index_name = 'uk_sid_fn_del')
+ AND EXISTS(SELECT 1 FROM information_schema.statistics
+ WHERE table_schema = DATABASE() AND table_name = 'function_meta' AND
index_name = 'function_meta_uk_sid_fn_del'),
+ 'SELECT 1',
+ 'ALTER TABLE `function_meta` RENAME INDEX `uk_sid_fn_del` TO
`function_meta_uk_sid_fn_del`');
+PREPARE stmt FROM @ddl;
+EXECUTE stmt;
+DEALLOCATE PREPARE stmt;
+
+SET @ddl = IF(NOT EXISTS(SELECT 1 FROM information_schema.statistics
+ WHERE table_schema = DATABASE() AND table_name = 'function_meta' AND
index_name = 'idx_mid')
+ AND EXISTS(SELECT 1 FROM information_schema.statistics
+ WHERE table_schema = DATABASE() AND table_name = 'function_meta' AND
index_name = 'function_meta_idx_mid'),
+ 'SELECT 1',
+ 'ALTER TABLE `function_meta` RENAME INDEX `idx_mid` TO
`function_meta_idx_mid`');
+PREPARE stmt FROM @ddl;
+EXECUTE stmt;
+DEALLOCATE PREPARE stmt;
+
+SET @ddl = IF(NOT EXISTS(SELECT 1 FROM information_schema.statistics
+ WHERE table_schema = DATABASE() AND table_name = 'function_meta' AND
index_name = 'idx_cid')
+ AND EXISTS(SELECT 1 FROM information_schema.statistics
+ WHERE table_schema = DATABASE() AND table_name = 'function_meta' AND
index_name = 'function_meta_idx_cid'),
+ 'SELECT 1',
+ 'ALTER TABLE `function_meta` RENAME INDEX `idx_cid` TO
`function_meta_idx_cid`');
+PREPARE stmt FROM @ddl;
+EXECUTE stmt;
+DEALLOCATE PREPARE stmt;
+
+SET @ddl = IF(NOT EXISTS(SELECT 1 FROM information_schema.statistics
+ WHERE table_schema = DATABASE() AND table_name = 'function_version_info'
AND index_name = 'idx_mid')
+ AND EXISTS(SELECT 1 FROM information_schema.statistics
+ WHERE table_schema = DATABASE() AND table_name = 'function_version_info'
AND index_name = 'function_version_info_idx_mid'),
+ 'SELECT 1',
+ 'ALTER TABLE `function_version_info` RENAME INDEX `idx_mid` TO
`function_version_info_idx_mid`');
+PREPARE stmt FROM @ddl;
+EXECUTE stmt;
+DEALLOCATE PREPARE stmt;
+
+SET @ddl = IF(NOT EXISTS(SELECT 1 FROM information_schema.statistics
+ WHERE table_schema = DATABASE() AND table_name = 'function_version_info'
AND index_name = 'idx_cid')
+ AND EXISTS(SELECT 1 FROM information_schema.statistics
+ WHERE table_schema = DATABASE() AND table_name = 'function_version_info'
AND index_name = 'function_version_info_idx_cid'),
+ 'SELECT 1',
+ 'ALTER TABLE `function_version_info` RENAME INDEX `idx_cid` TO
`function_version_info_idx_cid`');
+PREPARE stmt FROM @ddl;
+EXECUTE stmt;
+DEALLOCATE PREPARE stmt;
+
+SET @ddl = IF(NOT EXISTS(SELECT 1 FROM information_schema.statistics
+ WHERE table_schema = DATABASE() AND table_name = 'function_version_info'
AND index_name = 'idx_sid')
+ AND EXISTS(SELECT 1 FROM information_schema.statistics
+ WHERE table_schema = DATABASE() AND table_name = 'function_version_info'
AND index_name = 'function_version_info_idx_sid'),
+ 'SELECT 1',
+ 'ALTER TABLE `function_version_info` RENAME INDEX `idx_sid` TO
`function_version_info_idx_sid`');
+PREPARE stmt FROM @ddl;
+EXECUTE stmt;
+DEALLOCATE PREPARE stmt;
-- `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
@@ -121,14 +468,28 @@ ALTER TABLE `function_version_info` RENAME INDEX
`idx_sid` TO `function_version_
-- 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`);
+-- Skip only the exact completed conversion; unexpected keys must fail the
ALTER.
+SET @ddl = IF((SELECT COUNT(*) = 3 AND SUM(
+ (seq_in_index = 1 AND column_name = 'table_id') OR
+ (seq_in_index = 2 AND column_name = 'version') OR
+ (seq_in_index = 3 AND column_name = 'deleted_at')) = 3
+ FROM information_schema.statistics
+ WHERE table_schema = DATABASE() AND table_name = 'table_version_info' AND
index_name = 'PRIMARY')
+ AND NOT EXISTS(SELECT 1 FROM information_schema.statistics
+ WHERE table_schema = DATABASE() AND table_name = 'table_version_info' AND
index_name = 'uk_table_id_version_deleted_at'),
+ 'SELECT 1',
+ '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`)');
+PREPARE stmt FROM @ddl;
+EXECUTE stmt;
+DEALLOCATE PREPARE stmt;
-ALTER TABLE `job_run_meta`
- ADD COLUMN `job_started_at` BIGINT(20) UNSIGNED NOT NULL DEFAULT 0 COMMENT
'job started at' AFTER `job_run_status`;
+SET @ddl = IF(EXISTS(SELECT 1 FROM information_schema.columns
+ WHERE table_schema = DATABASE() AND table_name = 'job_run_meta' AND
column_name = 'job_started_at'),
+ 'SELECT 1',
+ 'ALTER TABLE `job_run_meta` ADD COLUMN `job_started_at` BIGINT(20)
UNSIGNED NOT NULL DEFAULT 0 COMMENT ''job started at'' AFTER `job_run_status`');
+PREPARE stmt FROM @ddl;
+EXECUTE stmt;
+DEALLOCATE PREPARE stmt;
CREATE TABLE IF NOT EXISTS `policy_tag_relation_meta` (
`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'auto increment
id',
@@ -144,8 +505,13 @@ CREATE TABLE IF NOT EXISTS `policy_tag_relation_meta` (
KEY `policy_tag_relation_meta_idx_tag_id` (`tag_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT 'policy
tag relation';
-ALTER TABLE `job_run_meta`
- ADD COLUMN `runtime_job_template` MEDIUMTEXT DEFAULT NULL COMMENT 'job run
runtime job template' AFTER `job_finished_at`;
+SET @ddl = IF(EXISTS(SELECT 1 FROM information_schema.columns
+ WHERE table_schema = DATABASE() AND table_name = 'job_run_meta' AND
column_name = 'runtime_job_template'),
+ 'SELECT 1',
+ 'ALTER TABLE `job_run_meta` ADD COLUMN `runtime_job_template` MEDIUMTEXT
DEFAULT NULL COMMENT ''job run runtime job template'' AFTER `job_finished_at`');
+PREPARE stmt FROM @ddl;
+EXECUTE stmt;
+DEALLOCATE PREPARE stmt;
CREATE TABLE IF NOT EXISTS `semantic_model_meta` (
`semantic_model_id` BIGINT(20) UNSIGNED NOT NULL COMMENT 'semantic model
id',
@@ -204,8 +570,18 @@ UPDATE `owner_meta` o
-- in that snapshot changed. `occ_version` takes over the CAS;
`current_version` again advances
-- only when the stored snapshot changes. The default is the whole backfill,
because `occ_version`
-- is only ever compared against itself on the same row.
-ALTER TABLE `fileset_meta`
- ADD COLUMN `occ_version` INT UNSIGNED NOT NULL DEFAULT 1 COMMENT 'fileset
optimistic concurrency version' AFTER `last_version`;
+SET @ddl = IF(EXISTS(SELECT 1 FROM information_schema.columns
+ WHERE table_schema = DATABASE() AND table_name = 'fileset_meta' AND
column_name = 'occ_version'),
+ 'SELECT 1',
+ 'ALTER TABLE `fileset_meta` ADD COLUMN `occ_version` INT UNSIGNED NOT NULL
DEFAULT 1 COMMENT ''fileset optimistic concurrency version'' AFTER
`last_version`');
+PREPARE stmt FROM @ddl;
+EXECUTE stmt;
+DEALLOCATE PREPARE stmt;
-ALTER TABLE `policy_meta`
- ADD COLUMN `occ_version` INT UNSIGNED NOT NULL DEFAULT 1 COMMENT 'policy
optimistic concurrency version' AFTER `last_version`;
+SET @ddl = IF(EXISTS(SELECT 1 FROM information_schema.columns
+ WHERE table_schema = DATABASE() AND table_name = 'policy_meta' AND
column_name = 'occ_version'),
+ 'SELECT 1',
+ 'ALTER TABLE `policy_meta` ADD COLUMN `occ_version` INT UNSIGNED NOT NULL
DEFAULT 1 COMMENT ''policy optimistic concurrency version'' AFTER
`last_version`');
+PREPARE stmt FROM @ddl;
+EXECUTE stmt;
+DEALLOCATE PREPARE stmt;
diff --git a/scripts/postgresql/upgrade-1.3.0-to-2.0.0-postgresql.sql
b/scripts/postgresql/upgrade-1.3.0-to-2.0.0-postgresql.sql
index 06d0412acf..771b9ccd67 100644
--- a/scripts/postgresql/upgrade-1.3.0-to-2.0.0-postgresql.sql
+++ b/scripts/postgresql/upgrade-1.3.0-to-2.0.0-postgresql.sql
@@ -170,8 +170,8 @@ UPDATE owner_meta
-- that snapshot changed. occ_version takes over the CAS; current_version
again advances only when
-- the stored snapshot changes. The default is the whole backfill, because
occ_version is only ever
-- compared against itself on the same row.
-ALTER TABLE fileset_meta ADD COLUMN occ_version INT NOT NULL DEFAULT 1;
+ALTER TABLE fileset_meta ADD COLUMN IF NOT EXISTS occ_version INT NOT NULL
DEFAULT 1;
COMMENT ON COLUMN fileset_meta.occ_version IS 'fileset optimistic concurrency
version';
-ALTER TABLE policy_meta ADD COLUMN occ_version INT NOT NULL DEFAULT 1;
+ALTER TABLE policy_meta ADD COLUMN IF NOT EXISTS occ_version INT NOT NULL
DEFAULT 1;
COMMENT ON COLUMN policy_meta.occ_version IS 'policy optimistic concurrency
version';
