Repository navigation
Migrations fail due to GTID Consistency Violation when using GTID Replication #19988
Description
Apache Airflow version
2.2.2 (latest released)
Operating System
Debian GNU/Linux 10 (buster)
Versions of Apache Airflow Providers
No response
Deployment
Other 3rd-party Helm chart
Deployment details
Using MySQL 8.0 via Google CloudSQL
What happened
When upgrading from 2.1.2 to 2.2.2 the airflow db upgrade command failed with the following command:
sqlalchemy.exc.OperationalError: (MySQLdb._exceptions.OperationalError) (1786, 'Statement violates GTID consistency: CREATE TABLE ... SELECT.')
[SQL: create table _airflow_moved__2_2__task_instance as select source.* from task_instance as source
left join dag_run as dr
on (source.dag_id = dr.dag_id and source.execution_date = dr.execution_date)
where dr.id is null
]
On further investigation, it looks like CREATE TABLE AS SELECT ... queries will cause GTID consistency violations whenever mySQL GTID-based replication is used (source).
This is used by default on all Google CloudSQL mySQL instances, so they will always block these queries (source)
It looks like these queries are created here:
Lines 739 to 747 in eaa8ac7
Could we refactor this into two separate queries like:
else:
# Postgres, MySQL and SQLite all have the same CREATE TABLE a AS SELECT ... syntax
session.execute(
text(
f"create table {target_table_name} like {source_table}"
)
)
session.execute(
text(
f"INSERT INTO {target_table_name} select source.* from {source_table} as source " + where_clause
)
) In order to preserve the GTID consistency?
Are you willing to submit PR?
- Yes I am willing to submit a PR!
Code of Conduct
- I agree to follow this project's Code of Conduct
Activity
potiuk commentedon Dec 2, 2021
This has been raised before (#19871), but since this is CloudSQL limitation, I believe we will need to fix it @uranusjr - wdyt?
potiuk commentedon Dec 2, 2021
Oh. Did I forget to mention that MYSQL is our "Elephant in the Room", BTW ? https://lists.apache.org/thread/dp78j0ssyhx62008lbtblrc856nbmlfb
SamWheating commentedon Dec 2, 2021
Ah cool, I hadn't found that report when I searched previously, I'll make a note of checking the discussions tab next time.
If it was under our control I would be happy to just disable GTID replication but unfortunately this is a mandatory setting in CloudSQL (the cost of using a managed platform I suppose 😞) I can submit a PR for this tomorrow, if y'all are in agreement with the proposed fix.
Also thanks for the advanced notice of possible MySQL removal - I don't think this would be a huge issue for us as we could switch to Postgres (which would then give us nice features like
CREATE TABLE AS SELECTandUPDATE .. RETURNING)potiuk commentedon Dec 2, 2021
naaa. removal is probably out of question for quite a while. But I DO recommend migrating to Postgres. Saves time, money and likely a lot of hair not pulled out of desperation.
Yeah. Please Submit PR :)