search

yugabyte / yugabyte-db

YugabyteDB - the cloud native distributed SQL database for mission-critical applications.
https://www.yugabyte.com
Other
8.99k stars 1.07k forks source link

[YSQL] org.postgresql.util.PSQLException: ERROR: Catalog Version Mismatch: A DDL occurred while processing this query. Try Again #2323

Open rao-vasireddy opened 5 years ago

rao-vasireddy commented 5 years ago

Jira Link: DB-2206 org.postgresql.util.PSQLException: ERROR: Catalog Version Mismatch: A DDL occurred while processing this query. Try Again error raised on a previously running query when a new secondary index table and index were created

jurjenvg commented 4 years ago

I have the same issue. Is there a workaround to do live schema updates on YDB?

humblewolf commented 3 years ago

Hey, we are also facing this issue, any workarounds or fixes ?

m-iancu commented 3 years ago

Hi @humblewolf, could you share a bit more about the error? Does the error occur during schema initialization or during regular operation? Also, do you use any ORM framework (e.g. from Spring/Hibernate, Django, etc) or migration framework like Flyway or Liquibase?

humblewolf commented 3 years ago

hey @m-iancu It happens during schema initalization, We are using Rails as a framework.

dorian-stream commented 3 years ago

@humblewolf would something like disabling transactions in active record migrations work ? Can you try that ?

pilshchikov commented 3 years ago

@m-iancu hi, reproduced this issue with running SqlInserts and SqlDataLoad together against 2.4.7.0-b7 (can be reproduced and on 2.4.6)

java -jar yb-sample-apps.jar --workload SqlInserts  --num_writes 10000000 --num_threads_write 50 --num_threads_read 0 --num_reads 0 --num_unique_keys 100000000 --create_table_name table_10_9_13_31_da72af_SqlInserts --uuid 166b964d-6de9-4349-ac62-d552b6efe8b0 --nodes [nodes]
java -jar yb-sample-apps.jar --workload SqlDataLoad  --num_writes 10000000 --num_threads_write 50 --num_threads_read 0 --num_reads 0 --num_unique_keys 100000000 --num_foreign_key_table_rows 1000000 --create_table_name table_10_9_5_4_3d45d5_SqlDataLoad --uuid 87b30d48-a07a-492b-89dd-de5330065651 --nodes [nodes]

Exception on SqlInserts log:

0 [main] INFO com.yugabyte.sample.Main  - Starting sample app...
25 [main] INFO com.yugabyte.sample.common.CmdLineOpts  - Using given UUID : 166b964d-6de9-4349-ac62-d552b6efe8b0
31 [main] INFO com.yugabyte.sample.common.CmdLineOpts  - App: SqlInserts
31 [main] INFO com.yugabyte.sample.common.CmdLineOpts  - Run time (seconds): -1
32 [main] INFO com.yugabyte.sample.common.CmdLineOpts  - Num reader threads: 0, num writer threads: 50
32 [main] INFO com.yugabyte.sample.common.CmdLineOpts  - Create table name: table_10_9_13_31_da72af_SqlInserts
32 [main] INFO com.yugabyte.sample.common.CmdLineOpts  - Num unique keys to insert: 100000000
32 [main] INFO com.yugabyte.sample.common.CmdLineOpts  - Num keys to update: -90000000
33 [main] INFO com.yugabyte.sample.common.CmdLineOpts  - Num keys to read: 0
33 [main] INFO com.yugabyte.sample.common.CmdLineOpts  - Value size: 0
33 [main] INFO com.yugabyte.sample.common.CmdLineOpts  - Restrict values to ASCII strings: false
33 [main] INFO com.yugabyte.sample.common.CmdLineOpts  - Perform sanity check at end of app run: false
33 [main] INFO com.yugabyte.sample.common.CmdLineOpts  - Table TTL (secs): -1
33 [main] INFO com.yugabyte.sample.common.CmdLineOpts  - Local reads: false
33 [main] INFO com.yugabyte.sample.common.CmdLineOpts  - Read only load: false
1153 [main] INFO com.yugabyte.sample.apps.SqlInserts  - Created table: table_10_9_13_31_da72af_sqlinserts
6174 [Thread-0] INFO com.yugabyte.sample.common.metrics.MetricsTracker  - Read: 0.00 ops/sec (0.00 ms/op), 0 total ops  |  Write: 9193.30 ops/sec (3.75 ms/op), 45978 total ops  |  Uptime: 5020 ms | 
7976 [Thread-28] INFO com.yugabyte.sample.apps.SqlInserts  - Failed writing key: 166b964d-6de9-4349-ac62-d552b6efe8b0:46019
org.postgresql.util.PSQLException: ERROR: Catalog Version Mismatch: A DDL occurred while processing this query. Try again.
  Detail: Internal error: Query error: Catalog Version Mismatch: A DDL occurred while processing this query. Try again.
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2553)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2285)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:323)
    at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:473)
    at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:393)
    at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:164)
    at org.postgresql.jdbc.PgPreparedStatement.executeUpdate(PgPreparedStatement.java:130)
    at com.yugabyte.sample.apps.SqlInserts.doWrite(SqlInserts.java:176)
    at com.yugabyte.sample.apps.AppBase.performWrite(AppBase.java:761)
    at com.yugabyte.sample.common.IOPSThread.run(IOPSThread.java:87)
ddorian commented 3 years ago

@pilshchikov this is a common error and it's pretty easy to reproduce, see docs https://docs.yugabyte.com/latest/troubleshoot/nodes/trouble-common/#catalog-version-mismatch-a-ddl-occurred-while-processing-this-query-try-again

simon-mottram-cucumber commented 1 year ago

This is happening to us and it happens when flyway tries to update it's table of migrations, it occurs on this select

"SELECT \"installed_rank\",\"version\",\"description\",\"type\",\"script\",\"checksum\",\"installed_on\",\"installed_by\",\"execution_time\",\"success\" FROM \"public\".\"flyway_schema_history\" WHERE \"installed_rank\" > 0 ORDER BY \"installed_rank\""

It's currently preventing us from using Flyway at all. disabling the transaction had no effect.

Caused by: org.postgresql.util.PSQLException: ERROR: The catalog snapshot used for this transaction has been invalidated: MISMATCHED_SCHEMA Where: Catalog Version Mismatch: A DDL occurred while processing this query. Try again. at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2676) ~[postgresql-42.5.4.jar:42.5.4] at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2366) ~[postgresql-42.5.4.jar:42.5.4] at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:356) ~[postgresql-42.5.4.jar:42.5.4] at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:496) ~[postgresql-42.5.4.jar:42.5.4] at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:413) ~[postgresql-42.5.4.jar:42.5.4] at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:190) ~[postgresql-42.5.4.jar:42.5.4] at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:134) ~[postgresql-42.5.4.jar:42.5.4] at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52) ~[HikariCP-5.0.1.jar:na] at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java) ~[HikariCP-5.0.1.jar:na] at org.flywaydb.core.internal.jdbc.JdbcTemplate.query(JdbcTemplate.java:340) ~[flyway-core-9.5.1.jar:na] at org.flywaydb.core.internal.schemahistory.JdbcTableSchemaHistory.refreshCache(JdbcTableSchemaHistory.java:200) ~[flyway-core-9.5.1.jar:na] ... 46 common frames omitted

ddorian commented 1 year ago

@simon-mottram-cucumber

Have you seen https://docs.yugabyte.com/preview/integrations/flyway/ ?

simon-mottram-cucumber commented 1 year ago

Nope but I have reading for tomorrow thanks

simon-mottram-cucumber commented 1 year ago

I have tracked down our particular issue, it was caused by an upgrade. We did a clean reinstall of 2.17 and it's all working well.

s-aliasgar commented 2 months ago

We are specifically facing this issue when any of our flyway scripts contains a "rename column" statement.

After executing the Queries in the script, When flyway tries to update its schem_history table, we get the below error ERROR: The catalog snapshot used for this transaction has been invalidated: expected: 894, got: 892: MISMATCHED_SCHEMA

ddorian commented 2 months ago

@s-aliasgar are you using our plugin https://docs.yugabyte.com/preview/integrations/flyway/ ?

s-aliasgar commented 2 months ago

@ddorian we were on an older version (9.22.3) of flyway-core, which did not support plugins Upgrading flyway-core, and adding flyway-database-yugabytedb plugin resolved the issue

Does yugabyte officially maintain flyway-database-yugabytedb plugin and provide support for the same in case of issues?

ddorian commented 2 months ago

Does yugabyte officially maintain flyway-database-yugabytedb plugin and provide support for the same in case of issues?

They maintain it and we also contribute. We provide support wherever you use yugabytedb.

s-aliasgar commented 2 months ago

@ddorian the flyway SQL scripts with alter/rename column work with yugabyte-2.19.3.0 after adding flyway-database-yugabytedb plugin to the application, But the same flyway SQL scripts failed in a setup with yugabyte-2.21.1.0

ddorian commented 2 months ago

@s-aliasgar Please specify the exact queries you're running and errors you're getting.

s-aliasgar commented 2 months ago

@s-aliasgar Please specify the exact queries you're running and errors you're getting.

yugabyte-2.21.1.0 flyway-core: 10.12.0 flyway-database-yugabytedb: 10.12.0

Script 1 (2.0.0.10 - DUMMY1) CREATE TABLE DUMMY ( id VARCHAR(255) NOT NULL, version BIGINT NOT NULL, status VARCHAR(255), phone_number VARCHAR(255) NOT NULL, validated_on TIMESTAMPTZ, CONSTRAINT pk_dummy PRIMARY KEY (id) );

Script 2 (2.0.0.11 - DUMMY2) ALTER table DUMMY rename column phone_number to phone_number_1;

Error 2024-09-04 17:03:19,580 my-service INFO main org.flywaydb.core.internal.command.DbMigrate Migrating schema "my_schema" to version "2.0.0.10 - DUMMY1" 2024-09-04 17:03:19,727 my-service INFO main org.flywaydb.core.internal.command.DbMigrate Migrating schema "my_schema" to version "2.0.0.11 - DUMMY2" 2024-09-04 17:03:19,995 my-service ERROR main com.someorg.infra.config.AbstractInfraInitializer Exception occurred in runFlywayForApp() : {} org.flywaydb.core.internal.exception.FlywaySqlException: Unable to check whether table "my_schema"."my_schema_schema_history" exists SQL State : 40001 Error Code : 0 Message : ERROR: The catalog snapshot used for this transaction has been invalidated: expected: 1013, got: 1012: MISMATCHED_SCHEMA Where: Catalog Version Mismatch: A DDL occurred while processing this query. Try again.

at org.flywaydb.core.internal.database.base.Table.exists(Table.java:49)
at org.flywaydb.core.internal.schemahistory.JdbcTableSchemaHistory.exists(JdbcTableSchemaHistory.java:104)
at org.flywaydb.core.internal.schemahistory.JdbcTableSchemaHistory.allAppliedMigrations(JdbcTableSchemaHistory.java:193)
at org.flywaydb.core.internal.schemahistory.SchemaHistory.calculateInstalledRank(SchemaHistory.java:212)
at org.flywaydb.core.internal.schemahistory.SchemaHistory.addAppliedMigration(SchemaHistory.java:193)
at org.flywaydb.core.internal.command.DbMigrate.doMigrateGroup(DbMigrate.java:405)
at org.flywaydb.core.internal.command.DbMigrate.lambda$applyMigrations$1(DbMigrate.java:277)
at org.flywaydb.core.internal.jdbc.TransactionalExecutionTemplate.execute(TransactionalExecutionTemplate.java:59)
at org.flywaydb.core.internal.command.DbMigrate.applyMigrations(DbMigrate.java:276)
at org.flywaydb.core.internal.command.DbMigrate.migrateGroup(DbMigrate.java:249)
at org.flywaydb.core.internal.command.DbMigrate.lambda$migrateAll$0(DbMigrate.java:144)
at org.flywaydb.community.database.postgresql.yugabytedb.YugabyteDBExecutionTemplate.execute(YugabyteDBExecutionTemplate.java:31)
at org.flywaydb.community.database.postgresql.yugabytedb.YugabyteDBConnection.lock(YugabyteDBConnection.java:37)
at org.flywaydb.core.internal.schemahistory.JdbcTableSchemaHistory.lock(JdbcTableSchemaHistory.java:149)
at org.flywaydb.core.internal.command.DbMigrate.migrateAll(DbMigrate.java:144)
at org.flywaydb.core.internal.command.DbMigrate.migrate(DbMigrate.java:102)
at org.flywaydb.core.Flyway.lambda$migrate$0(Flyway.java:206)
at org.flywaydb.core.FlywayExecutor.execute(FlywayExecutor.java:210)
at org.flywaydb.core.Flyway.migrate(Flyway.java:151)`
ddorian commented 4 weeks ago

@s-aliasgar

can you check if it's fixed with https://github.com/yugabyte/yugabyte-db/issues/23882 in the next 2024.1.3 release (should be out in a week)?

If not, can you upload the migrations that you do in a zip here?