Issue with flyway clean command on EDB (EnterpriseDB)
smuthaluru
Posts: 1 New member
in PostgreSQL
Our EnterpriseDB is configured with multi-node with BDR is enabled. We use flyway clean command to wipe everything from existing schema and rebuild with latest flyway scripts (DDLs). Our EDB database is hosting multiple schemas and our flyway configuration is setup to target a specific application schema independently. With that, the flyway_schema_history table co-exists with other application specific tables within same schema.
Below is the error when we use flyway clean command:
024-09-19 08:50:26.759 CDT [2525581]: u=[db_user] db=[db_name] app=[PostgreSQL JDBC Driver] c=[xx.xxx.xx.xx] s=[66ec29c9.26898d:16] tx=[324/21749:665906] CONTEXT: while handling global lock GLOBAL_LOCK_DML ("app_schema"."flyway_schema_history") in database 16693 requested by node-id 983066778 in local acquire stage epoch_replay
"xact rep": true, "ddl rep": true, "ddl locking": all
2024-09-19 08:50:26.759 CDT [2525581]: u=[db_user] db=[db_name] app=[PostgreSQL JDBC Driver] c=[xx.xxx.xx.xx] s=[66ec29c9.26898d:17] tx=[324/21749:665906] LOCATION: consume_ddl_epoch, bdr_lockmgr.c:1822
2024-09-19 08:50:26.779 CDT [2525581]: u=[db_user] db=[db_name] app=[PostgreSQL JDBC Driver] c=[xx.xxx.xx.xx] s=[66ec29c9.26898d:19] tx=[324/21749:665906] ERROR: 55P03: canceling statement due to global lock timeout
2024-09-19 08:50:26.779 CDT [2525581]: u=[db_user] db=[db_name] app=[PostgreSQL JDBC Driver] c=[xx.xxx.xx.xx] s=[66ec29c9.26898d:20] tx=[324/21749:665906] CONTEXT: while handling global lock GLOBAL_LOCK_DML ("app_schema"."flyway_schema_history") in database 16693 requested by node-id 983066778 in local acquire stage epoch_replay
I assume that having flyway_schema_history within application schema may be causing the issue as clean command is trying to drop the table which is required.
Please advice.
Thank you.
Below is the error when we use flyway clean command:
024-09-19 08:50:26.759 CDT [2525581]: u=[db_user] db=[db_name] app=[PostgreSQL JDBC Driver] c=[xx.xxx.xx.xx] s=[66ec29c9.26898d:16] tx=[324/21749:665906] CONTEXT: while handling global lock GLOBAL_LOCK_DML ("app_schema"."flyway_schema_history") in database 16693 requested by node-id 983066778 in local acquire stage epoch_replay
"xact rep": true, "ddl rep": true, "ddl locking": all
2024-09-19 08:50:26.759 CDT [2525581]: u=[db_user] db=[db_name] app=[PostgreSQL JDBC Driver] c=[xx.xxx.xx.xx] s=[66ec29c9.26898d:17] tx=[324/21749:665906] LOCATION: consume_ddl_epoch, bdr_lockmgr.c:1822
2024-09-19 08:50:26.779 CDT [2525581]: u=[db_user] db=[db_name] app=[PostgreSQL JDBC Driver] c=[xx.xxx.xx.xx] s=[66ec29c9.26898d:19] tx=[324/21749:665906] ERROR: 55P03: canceling statement due to global lock timeout
2024-09-19 08:50:26.779 CDT [2525581]: u=[db_user] db=[db_name] app=[PostgreSQL JDBC Driver] c=[xx.xxx.xx.xx] s=[66ec29c9.26898d:20] tx=[324/21749:665906] CONTEXT: while handling global lock GLOBAL_LOCK_DML ("app_schema"."flyway_schema_history") in database 16693 requested by node-id 983066778 in local acquire stage epoch_replay
I assume that having flyway_schema_history within application schema may be causing the issue as clean command is trying to drop the table which is required.
Please advice.
Thank you.
Comments
It seems the issue stems from the global lock conflict on
flyway_schema_historyduring theflyway cleanprocess. Try excluding this table or review locking configurations.The issue appears to originate from a global lock conflict on the flyway_schema_history table during the Flyway clean process. Consider excluding this specific table from the operation or thoroughly reviewing the database’s locking configurations to identify potential bottlenecks, ensuring a smoother and more efficient Flyway clean execution without conflicts.