You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@airflow.apache.org by GitBox <gi...@apache.org> on 2021/12/02 23:28:40 UTC
[GitHub] [airflow] SamWheating opened a new pull request #19999: Don't use CREATE TABLE AS SELECT ... with mySQL
SamWheating opened a new pull request #19999:
URL: https://github.com/apache/airflow/pull/19999
Closes: https://github.com/apache/airflow/issues/19988
Splitting this `CREATE TABLE AS SELECT` query into two queries (`CREATE TABLE LIKE ..` followed by `INSERT INTO`) because the former doesn't play nicely with MySQL.
This PR is inherently difficult to test/validate, but I ran the proposed queries against the metadata database in our development environment (where we first ran into this issue) and was able to replicate the issue and verify the fix:
```
// replicating the issue:
MySQL [airflow] > create table _airflow_moved__2_2__task_instance as select source.* from task_instance as source;
ERROR 1786 (HY000): Statement violates GTID consistency: CREATE TABLE ... SELECT.
// Validating that splitting the query fixes the issue
MySQL [airflow] > create table _airflow_moved__2_2__task_instance like task_instance;
Query OK, 0 rows affected (0.063 sec)
MySQL [airflow] > INSERT INTO _airflow_moved__2_2__task_instance 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;
Query OK, 22900 rows affected (2.110 sec)
```
Tomorrow I can create a patched image with these changes and validate the fix in our development environment.
---
**^ Add meaningful description above**
Read the **[Pull Request Guidelines](https://github.com/apache/airflow/blob/main/CONTRIBUTING.rst#pull-request-guidelines)** for more information.
In case of fundamental code change, Airflow Improvement Proposal ([AIP](https://cwiki.apache.org/confluence/display/AIRFLOW/Airflow+Improvements+Proposals)) is needed.
In case of a new dependency, check compliance with the [ASF 3rd Party License Policy](https://www.apache.org/legal/resolved.html#category-x).
In case of backwards incompatible changes please leave a note in [UPDATING.md](https://github.com/apache/airflow/blob/main/UPDATING.md).
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] ashb commented on pull request #19999: Don't use CREATE TABLE AS SELECT ... with mySQL
Posted by GitBox <gi...@apache.org>.
ashb commented on pull request #19999:
URL: https://github.com/apache/airflow/pull/19999#issuecomment-985480614
Can you define "doesn't play nicely with"? Cos in my testing it did work (but I didn't test mysql beyond one or two rows.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] github-actions[bot] commented on pull request #19999: Don't use CREATE TABLE AS SELECT ... with mySQL
Posted by GitBox <gi...@apache.org>.
github-actions[bot] commented on pull request #19999:
URL: https://github.com/apache/airflow/pull/19999#issuecomment-985115099
The PR most likely needs to run full matrix of tests because it modifies parts of the core of Airflow. However, committers might decide to merge it quickly and take the risk. If they don't merge it quickly - please rebase it to the latest main at your convenience, or amend the last commit of the PR, and push it with --force-with-lease.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] ashb commented on pull request #19999: Don't use CREATE TABLE AS SELECT ... with mySQL
Posted by GitBox <gi...@apache.org>.
ashb commented on pull request #19999:
URL: https://github.com/apache/airflow/pull/19999#issuecomment-985481670
I am amazed at MySQLs ability to find new ways to make me sad.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] ashb commented on a change in pull request #19999: Don't use CREATE TABLE AS SELECT ... with mySQL
Posted by GitBox <gi...@apache.org>.
ashb commented on a change in pull request #19999:
URL: https://github.com/apache/airflow/pull/19999#discussion_r761896632
##########
File path: airflow/utils/db.py
##########
@@ -737,13 +737,24 @@ def _move_dangling_table(session, source_table: "Table", target_table_name: str,
)
)
else:
- # Postgres, MySQL and SQLite all have the same CREATE TABLE a AS SELECT ... syntax
- session.execute(
- text(
- f"create table {target_table_name} as select source.* from {source_table} as source "
- + where_clause
+ if dialect_name == "mysql":
+ # CREATE TABLE AS SELECT must be broken into two queries in mySQL for GTID consistency.
+ # More info: https://github.com/apache/airflow/issues/19988
Review comment:
```suggestion
# CREATE TABLE AS SELECT must be broken into two queries for MySQL as the single query
# approach fails when replication is enabled ("Statement violates GTID consistency")```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] ashb merged pull request #19999: Don't use CREATE TABLE AS SELECT ... with mySQL
Posted by GitBox <gi...@apache.org>.
ashb merged pull request #19999:
URL: https://github.com/apache/airflow/pull/19999
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] potiuk edited a comment on pull request #19999: Don't use CREATE TABLE AS SELECT ... with mySQL
Posted by GitBox <gi...@apache.org>.
potiuk edited a comment on pull request #19999:
URL: https://github.com/apache/airflow/pull/19999#issuecomment-985662622
> I am amazed at MySQLs ability to find new ways to make me sad.
Very, very, very much so. I expressed my feelings on MySQL today at the talk I gave at Data Science Summit a bit after I found out that about half of the audience used it, unfortunately. I hope that brings more people to Postgres.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] ashb edited a comment on pull request #19999: Don't use CREATE TABLE AS SELECT ... with mySQL
Posted by GitBox <gi...@apache.org>.
ashb edited a comment on pull request #19999:
URL: https://github.com/apache/airflow/pull/19999#issuecomment-985480614
Can you define "doesn't play nicely with"? Cos in my testing it did work (but I didn't test mysql beyond one or two rows.
Duh, read the full error message Ash.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] ashb commented on pull request #19999: Don't use CREATE TABLE AS SELECT ... with mySQL
Posted by GitBox <gi...@apache.org>.
ashb commented on pull request #19999:
URL: https://github.com/apache/airflow/pull/19999#issuecomment-985481366
Ah, This doesn't play nicely when replication is turned on.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] potiuk commented on pull request #19999: Don't use CREATE TABLE AS SELECT ... with mySQL
Posted by GitBox <gi...@apache.org>.
potiuk commented on pull request #19999:
URL: https://github.com/apache/airflow/pull/19999#issuecomment-985662622
> I am amazed at MySQLs ability to find new ways to make me sad.
Very, very, very much so. I expressed my feeling son MySQL today at the talk I gave at Data Science Summit a bit after I found out that about half of the audience used it, unfortunately. I hope that brings more people to Postgres.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org