升级Artifactory至7.90.5后Access服务启动失败，Derby迁移报错

Artifactory 7.90.5版本后，Access服务改为运行在独立JVM中。使用Derby数据库时，Access的数据库会被复制到$JFROG_HOME/artifactory/var/data/access/derby路径。为避免多JVM访问同一Derby数据库引发冲突，手动配置了system.yaml，具体配置如下：

shared:
    database:
        allowNonPostgresql: true
        type: derby
        driver: org.apache.derby.jdbc.EmbeddedDriver
        url: jdbc:derby:/opt/jfrog/artifactory/var/data/artifactory/derby;create=true
    user: artifactory
    extraJavaOpts: -Xss256k -Dkeystore.pkcs12.keyProtectionAlgorithm=PBEWithHmacSHA256AndAES_256
artifactory:
    port: 8081
    tomcat:
        connector:
            maxThreads: 200
            sendReasonPhrase: true
access:
    http:
        port: 8040
    database:
        type: derby
        driver: org.apache.derby.jdbc.EmbeddedDriver
        url: jdbc:derby:/opt/jfrog/artifactory/var/data/access/derby;create=true
    tomcat:
        connector:
            maxThreads: 50
            sendReasonPhrase: true

启动时Access服务数据库迁移失败，access-service.log报错如下：

2024-08-01T02:15:30.606Z [jfac ] [INFO ] [fa1259ae7c93acf5] [licationContextInitializer:165] [main                ] - Access (jfac) service initialization started. Version: 7.112.5 Revision: 81205900 PID: 14967 Home: /opt/jfrog/artifactory
2024-08-01T02:15:30.954Z [jfac ] [INFO ] [fa1259ae7c93acf5] [o.j.a.AccessApplication:55    ] [main                ] - Starting AccessApplication v7.112.5 using Java 17.0.10 on ip-172-31-10-237 with PID 14967 (/opt/jfrog/artifactory/app/access/tomcat/webapps/access/WEB-INF/lib/access-application-7.112.5.jar started by artifactory in /)
2024-08-01T02:15:30.955Z [jfac ] [INFO ] [fa1259ae7c93acf5] [o.j.a.AccessApplication:632   ] [main                ] - No active profile set, falling back to 1 default profile: "default"
2024-08-01T02:15:53.819Z [jfac ] [ERROR] [fa1259ae7c93acf5] [o.f.c.i.c.DbMigrate:294       ] [main                ] - Migration of schema "APP" to version 7.42 - Alter and remove index access tokens table failed! Changes successfully rolled back.
2024-08-01T02:15:53.839Z [jfac ] [ERROR] [fa1259ae7c93acf5] [.s.d.u.AccessJdbcHelperImpl:81] [main                ] - Could not initialize database:
org.flywaydb.core.internal.dbsupport.FlywaySqlScriptException:
Migration V7_42__Alter_and_remove_index_access_tokens_table.sql failed
----------------------------------------------------------------------
SQL State  : XSAI2
Error Code : 20000
Message    : The conglomerate (5,217) requested does not exist.
Location   : db/derby/migration/V7_42__Alter_and_remove_index_access_tokens_table.sql (/file:/opt/jfrog/artifactory/app/access/tomcat/webapps/access/WEB-INF/lib/access-server-core-7.112.5.jar!/db/derby/migration/V7_42__Alter_and_remove_index_access_tokens_table.sql)
Line       : 1
Statement  : DROP INDEX access_username_idx

经分析，迁移脚本尝试删除的access_username_idx索引不存在，导致迁移失败。

以下两种方法可解决该问题：

方法1：手动创建缺失的索引后重新启动 #

1. 停止Artifactory服务
2. 使用Derby的ij工具连接到Access的Derby数据库：

   java -jar $JFROG_HOME/artifactory/app/third-party/derby/derbyrun.jar ij
   

   在ij控制台中执行连接命令：

   connect 'jdbc:derby:/opt/jfrog/artifactory/var/data/access/derby';
   

3. 创建缺失的索引：

   CREATE INDEX access_username_idx ON access_tokens(USERNAME);
   

4. 退出ij控制台，启动Artifactory服务，此时迁移脚本可正常删除该索引，完成迁移。

方法2：标记Flyway迁移版本为已完成 #

如果确认该索引无需创建，可直接标记对应迁移版本为已完成，跳过该脚本执行：

1. 停止Artifactory服务
2. 使用Derby的ij工具连接到Access的Derby数据库（步骤同方法1）
3. 插入记录到Flyway元数据表SCHEMA_VERSION，标记版本7.42的迁移已完成：

   INSERT INTO SCHEMA_VERSION (VERSION, DESCRIPTION, TYPE, SCRIPT, CHECKSUM, INSTALLED_ON, INSTALLED_BY, EXECUTION_TIME, SUCCESS)
   VALUES ('7.42', 'Alter and remove index access tokens table', 'SQL', 'V7_42__Alter_and_remove_index_access_tokens_table.sql', 0, CURRENT_TIMESTAMP, 'artifactory', 0, 1);
   

   注意：如果需要准确的校验值，可通过Flyway命令行工具计算对应脚本的校验值，或查看Artifactory日志中其他成功迁移记录的校验值格式后替换。

4. 启动Artifactory服务，Access服务将跳过该迁移脚本，继续后续初始化流程。

内容的提问来源于stack exchange，提问作者Lukas Rytz