search

eclipse-apoapsis / ort-server

A scalable server implementation of the OSS Review Toolkit.
https://eclipse-apoapsis.github.io/ort-server/
Apache License 2.0
19 stars 8 forks source link

Listing runs fails due to error with missing timestamp #1302

Closed lamppu closed 1 month ago

lamppu commented 1 month ago

Some /runs and /repositories/{repositoryId}/runs queries in our instance fail with this error message: org.postgresql.util.PSQLException: ERROR: column issues.timestamp does not exist Position: 19

mmurto commented 1 month ago

Our deployment hasn't been able to run the migration added in the issue deduplication PR. @oheger-bosch do you know what this is about?

Exception in thread "main" org.flywaydb.core.internal.exception.FlywayMigrateException: Script V84__deduplicateIssues.sql failed
----------------------------------------
SQL State  : 23503
Error Code : 0
Message    : ERROR: update or delete on table "issues" violates foreign key constraint "identifiers_ort_issues_ort_issue_id_fkey" on table "identifiers_issues"
  Detail: Key (id)=(96) is still referenced from table "identifiers_issues".
Location   : db/migration/V84__deduplicateIssues.sql (/file:/app/libs/dao-0.0.1-RC.001.sha.d1b85fd.jar!/db/migration/V84__deduplicateIssues.sql)
Line       : 93
Statement  : DELETE FROM issues i
WHERE NOT EXISTS (
  SELECT FROM issues_dedup id
  WHERE id.id = i.id
)
    at org.flywaydb.core.internal.command.DbMigrate.doMigrateGroup(DbMigrate.java:399)
    at org.flywaydb.core.internal.command.DbMigrate.lambda$applyMigrations$1(DbMigrate.java:283)
    at org.flywaydb.core.internal.jdbc.TransactionalExecutionTemplate.execute(TransactionalExecutionTemplate.java:59)
    at org.flywaydb.core.internal.command.DbMigrate.applyMigrations(DbMigrate.java:282)
    at org.flywaydb.core.internal.command.DbMigrate.migrateGroup(DbMigrate.java:255)
    at org.flywaydb.core.internal.command.DbMigrate.lambda$migrateAll$0(DbMigrate.java:153)
    at org.flywaydb.database.postgresql.PostgreSQLAdvisoryLockTemplate.execute(PostgreSQLAdvisoryLockTemplate.java:77)
    at org.flywaydb.database.postgresql.PostgreSQLAdvisoryLockTemplate.lambda$execute$0(PostgreSQLAdvisoryLockTemplate.java:60)
    at org.flywaydb.core.internal.jdbc.TransactionalExecutionTemplate.execute(TransactionalExecutionTemplate.java:59)
    at org.flywaydb.database.postgresql.PostgreSQLAdvisoryLockTemplate.execute(PostgreSQLAdvisoryLockTemplate.java:60)
    at org.flywaydb.database.postgresql.PostgreSQLConnection.lock(PostgreSQLConnection.java:105)
    at org.flywaydb.core.internal.schemahistory.JdbcTableSchemaHistory.lock(JdbcTableSchemaHistory.java:149)
    at org.flywaydb.core.internal.command.DbMigrate.migrateAll(DbMigrate.java:153)
    at org.flywaydb.core.internal.command.DbMigrate.migrate(DbMigrate.java:104)
    at org.flywaydb.core.Flyway.lambda$migrate$1(Flyway.java:230)
    at org.flywaydb.core.FlywayExecutor.execute(FlywayExecutor.java:213)
    at org.flywaydb.core.Flyway.migrate(Flyway.java:176)
    at org.eclipse.apoapsis.ortserver.dao.DatabaseKt.migrate(Database.kt:65)
    at org.eclipse.apoapsis.ortserver.core.plugins.DatabaseKt.configureDatabase(Database.kt:48)
    at org.eclipse.apoapsis.ortserver.core.ApplicationKt.module(Application.kt:42)
    at java.base/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(DirectMethodHandleAccessor.java:103)
    at java.base/java.lang.reflect.Method.invoke(Method.java:580)
    at kotlin.reflect.jvm.internal.calls.CallerImpl$Method.callMethod(CallerImpl.kt:97)
    at kotlin.reflect.jvm.internal.calls.CallerImpl$Method$Static.call(CallerImpl.kt:106)
    at kotlin.reflect.jvm.internal.KCallableImpl.callDefaultMethod$kotlin_reflection(KCallableImpl.kt:207)
    at kotlin.reflect.jvm.internal.KCallableImpl.callBy(KCallableImpl.kt:112)
    at io.ktor.server.engine.internal.CallableUtilsKt.callFunctionWithInjection(CallableUtils.kt:119)
    at io.ktor.server.engine.internal.CallableUtilsKt.executeModuleFunction(CallableUtils.kt:36)
    at io.ktor.server.engine.ApplicationEngineEnvironmentReloading$launchModuleByName$1.invoke(ApplicationEngineEnvironmentReloading.kt:332)
    at io.ktor.server.engine.ApplicationEngineEnvironmentReloading$launchModuleByName$1.invoke(ApplicationEngineEnvironmentReloading.kt:331)
    at io.ktor.server.engine.ApplicationEngineEnvironmentReloading.avoidingDoubleStartupFor(ApplicationEngineEnvironmentReloading.kt:356)
    at io.ktor.server.engine.ApplicationEngineEnvironmentReloading.launchModuleByName(ApplicationEngineEnvironmentReloading.kt:331)
    at io.ktor.server.engine.ApplicationEngineEnvironmentReloading.access$launchModuleByName(ApplicationEngineEnvironmentReloading.kt:32)
    at io.ktor.server.engine.ApplicationEngineEnvironmentReloading$instantiateAndConfigureApplication$1.invoke(ApplicationEngineEnvironmentReloading.kt:312)
    at io.ktor.server.engine.ApplicationEngineEnvironmentReloading$instantiateAndConfigureApplication$1.invoke(ApplicationEngineEnvironmentReloading.kt:310)
    at io.ktor.server.engine.ApplicationEngineEnvironmentReloading.avoidingDoubleStartup(ApplicationEngineEnvironmentReloading.kt:338)
    at io.ktor.server.engine.ApplicationEngineEnvironmentReloading.instantiateAndConfigureApplication(ApplicationEngineEnvironmentReloading.kt:310)
    at io.ktor.server.engine.ApplicationEngineEnvironmentReloading.createApplication(ApplicationEngineEnvironmentReloading.kt:150)
    at io.ktor.server.engine.ApplicationEngineEnvironmentReloading.start(ApplicationEngineEnvironmentReloading.kt:277)
    at io.ktor.server.netty.NettyApplicationEngine.start(NettyApplicationEngine.kt:216)
    at io.ktor.server.netty.EngineMain.main(EngineMain.kt:23)
Caused by: org.flywaydb.core.internal.sqlscript.FlywaySqlScriptException: Script V84__deduplicateIssues.sql failed
----------------------------------------
SQL State  : 23503
Error Code : 0
Message    : ERROR: update or delete on table "issues" violates foreign key constraint "identifiers_ort_issues_ort_issue_id_fkey" on table "identifiers_issues"
  Detail: Key (id)=(96) is still referenced from table "identifiers_issues".
Location   : db/migration/V84__deduplicateIssues.sql (/file:/app/libs/dao-0.0.1-RC.001.sha.d1b85fd.jar!/db/migration/V84__deduplicateIssues.sql)
Line       : 93
Statement  : DELETE FROM issues i
WHERE NOT EXISTS (
  SELECT FROM issues_dedup id
  WHERE id.id = i.id
)
    at org.flywaydb.core.internal.sqlscript.DefaultSqlScriptExecutor.handleException(DefaultSqlScriptExecutor.java:256)
    at org.flywaydb.core.internal.sqlscript.DefaultSqlScriptExecutor.executeStatement(DefaultSqlScriptExecutor.java:217)
    at org.flywaydb.core.internal.sqlscript.DefaultSqlScriptExecutor.execute(DefaultSqlScriptExecutor.java:137)
    at org.flywaydb.core.internal.resolver.sql.SqlMigrationExecutor.executeOnce(SqlMigrationExecutor.java:75)
    at org.flywaydb.core.internal.resolver.sql.SqlMigrationExecutor.lambda$execute$0(SqlMigrationExecutor.java:66)
    at org.flywaydb.core.internal.database.DefaultExecutionStrategy.execute(DefaultExecutionStrategy.java:31)
    at org.flywaydb.core.internal.resolver.sql.SqlMigrationExecutor.execute(SqlMigrationExecutor.java:65)
    at org.flywaydb.core.internal.command.DbMigrate.doMigrateGroup(DbMigrate.java:391)
    ... 40 more
Caused by: org.postgresql.util.PSQLException: ERROR: update or delete on table "issues" violates foreign key constraint "identifiers_ort_issues_ort_issue_id_fkey" on table "identifiers_issues"
  Detail: Key (id)=(96) is still referenced from table "identifiers_issues".
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2733)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2420)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:372)
    at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:517)
    at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:434)
    at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:356)
    at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:341)
    at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:317)
    at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:312)
    at com.zaxxer.hikari.pool.ProxyStatement.execute(ProxyStatement.java:94)
    at com.zaxxer.hikari.pool.HikariProxyStatement.execute(HikariProxyStatement.java)
    at org.flywaydb.core.internal.jdbc.JdbcTemplate.executeStatement(JdbcTemplate.java:210)
    at org.flywaydb.core.internal.sqlscript.ParsedSqlStatement.execute(ParsedSqlStatement.java:88)
    at org.flywaydb.core.internal.sqlscript.DefaultSqlScriptExecutor.executeStatement(DefaultSqlScriptExecutor.java:212)
oheger-bosch commented 1 month ago

That's not good. Looks that on the DB I used for testing a foreign key constraint is missing. Therefore, this problem did not occur to me. Will see whether I can adapt the V84 migration to correctly handle this.

oheger-bosch commented 1 month ago

In #1305, the migration script has been extended to cover the identifiers_issues table as well. @mmurto, could you please check whether this solves your problem?

mmurto commented 1 month ago

In #1305, the migration script has been extended to cover the identifiers_issues table as well. @mmurto, could you please check whether this solves your problem?

Thanks! We have no easy way to try it, so if you think it should fix the above error message and it did so on some test database, let's merge and I'll try it after that.

oheger-bosch commented 1 month ago

Unfortunately, now that I found out that my test DB seems to be corrupt, it is for me not easy as well to test the migration on production-like data. I am sure that this change fixes the issue you reported, but there may be still some other lurking around.

mmurto commented 1 month ago

Unfortunately, now that I found out that my test DB seems to be corrupt, it is for me not easy as well to test the migration on production-like data. I am sure that this change fixes the issue you reported, but there may be still some other lurking around.

Let's ship it and try then. :)

mmurto commented 1 month ago

@oheger-bosch it uncovered another error:

SQL State  : 23505
Error Code : 0
Message    : ERROR: duplicate key value violates unique constraint "identifiers_ort_issues_identifier_id_ort_issue_id_key"
  Detail: Key (identifier_id, issue_id)=(2526, 102) already exists.
Location   : db/migration/V84__deduplicateIssues.sql (/file:/app/libs/dao-0.0.1-RC.001.sha.2490be3.jar!/db/migration/V84__deduplicateIssues.sql)
Line       : 137
Statement  : INSERT INTO identifiers_issues
("identifier_id", "issue_id")
SELECT
  iih.identifier_id,
  ih.id
FROM
  identifiers_issues_hashes iih
INNER JOIN issues_with_hashes ih ON iih.issue_hash = ih.hash
    at org.flywaydb.core.internal.sqlscript.DefaultSqlScriptExecutor.handleException(DefaultSqlScriptExecutor.java:256)
    at org.flywaydb.core.internal.sqlscript.DefaultSqlScriptExecutor.executeStatement(DefaultSqlScriptExecutor.java:217)
    at org.flywaydb.core.internal.sqlscript.DefaultSqlScriptExecutor.execute(DefaultSqlScriptExecutor.java:137)
    at org.flywaydb.core.internal.resolver.sql.SqlMigrationExecutor.executeOnce(SqlMigrationExecutor.java:75)
    at org.flywaydb.core.internal.resolver.sql.SqlMigrationExecutor.lambda$execute$0(SqlMigrationExecutor.java:66)
    at org.flywaydb.core.internal.database.DefaultExecutionStrategy.execute(DefaultExecutionStrategy.java:31)
    at org.flywaydb.core.internal.resolver.sql.SqlMigrationExecutor.execute(SqlMigrationExecutor.java:65)
    at org.flywaydb.core.internal.command.DbMigrate.doMigrateGroup(DbMigrate.java:391)
    ... 40 more
Caused by: org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "identifiers_ort_issues_identifier_id_ort_issue_id_key"
  Detail: Key (identifier_id, issue_id)=(2526, 102) already exists.
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2733)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2420)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:372)
    at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:517)
    at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:434)
    at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:356)
    at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:341)
    at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:317)
    at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:312)
    at com.zaxxer.hikari.pool.ProxyStatement.execute(ProxyStatement.java:94)
    at com.zaxxer.hikari.pool.HikariProxyStatement.execute(HikariProxyStatement.java)
    at org.flywaydb.core.internal.jdbc.JdbcTemplate.executeStatement(JdbcTemplate.java:210)
    at org.flywaydb.core.internal.sqlscript.ParsedSqlStatement.execute(ParsedSqlStatement.java:88)
    at org.flywaydb.core.internal.sqlscript.DefaultSqlScriptExecutor.executeStatement(DefaultSqlScriptExecutor.java:212)
    ... 46 more
oheger-bosch commented 1 month ago

Oh no. It would have been too easy otherwise. I guess, this could be fixed by adding a DISTINCT to the statement. Will prepare a PR.

oheger-bosch commented 1 month ago

Next attempt: #1306. This trial-and-error approach is really unprofessional, but I see not better way until I can fix my DB, which will probably take a while.

mmurto commented 1 month ago

Next attempt: #1306. This trial-and-error approach is really unprofessional, but I see not better way until I can fix my DB, which will probably take a while.

We should probably prepare a test seed dataset that could be easily used as the base database for local testing.

mmurto commented 1 month ago

1306 resolved the issue. Thanks for the quick fix @oheger-bosch!

oheger-bosch commented 1 month ago

Thanks for confirming. I am rather sorry that it took multiple iterations.