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 2022/10/17 06:11:11 UTC
[GitHub] [airflow] tirkarthi opened a new issue, #27082: Airflow database migration fails in MySQL group replication mode
tirkarthi opened a new issue, #27082:
URL: https://github.com/apache/airflow/issues/27082
### Apache Airflow version
Other Airflow 2 version (please specify below)
### What happened
We are running MySQL in group replication mode and as per docs a primary key is required in a table. We noticed during our migration from Airflow 2.1.x to 2.3.4 that Airflow creates a temporary table to perform xcom related migrations by inserting the records and then renaming the table __airflow_tmp_xcom to xcom. The insert statement fails since the primary key is created after insertions. We have made a fix to move the primary key creation to be on the temporary table. Another possible solution would be to set the pragma as per docs. I have opened this issue for discussion since this might be helpful to others who might be running a similar MySQL setup and face this issue with this and possibly other migrations to 2.4.x .
https://github.com/apache/airflow/blob/4dd3c6a869497924617d6a6373dd7339b554f5d9/airflow/migrations/versions/0102_2_3_0_switch_xcom_table_to_use_run_id.py#L114-L124
https://dev.mysql.com/doc/refman/8.0/en/group-replication-requirements.html
> Primary Keys. Every table that is to be replicated by the group must have a defined primary key, or primary key equivalent where the equivalent is a non-null unique key. Such keys are required as a unique identifier for every row within a table, enabling the system to determine which transactions conflict by identifying exactly which rows each transaction has modified. Group Replication has its own built-in set of checks for primary keys or primary key equivalents, and does not use the checks carried out by the [sql_require_primary_key](https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_sql_require_primary_key) system variable. You may set sql_require_primary_key=ON for a server instance where Group Replication is running, and you may set the REQUIRE_TABLE_PRIMARY_KEY_CHECK option of the [CHANGE REPLICATION SOURCE TO](https://dev.mysql.com/doc/refman/8.0/en/change-replication-source-to.html) | [CHANGE MASTER TO](https://dev.mysql.com/doc/refman/8.0/en/c
hange-master-to.html) statement to ON for a Group Replication channel. However, be aware that you might find some transactions that are permitted under Group Replication's built-in checks are not permitted under the checks carried out when you set sql_require_primary_key=ON or REQUIRE_TABLE_PRIMARY_KEY_CHECK=ON.
### What you think should happen instead
The migration should succeed without any issues.
### How to reproduce
Perform airflow db upgrade to 2.3.0 with MySQL in group replication mode.
### Operating System
Redhat
### Versions of Apache Airflow Providers
_No response_
### Deployment
Other Docker-based deployment
### Deployment details
_No response_
### Anything else
_No response_
### Are you willing to submit PR?
- [X] Yes I am willing to submit a PR!
### Code of Conduct
- [X] I agree to follow this project's [Code of Conduct](https://github.com/apache/airflow/blob/main/CODE_OF_CONDUCT.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.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] tirkarthi commented on issue #27082: Airflow database migration fails in MySQL group replication mode
Posted by GitBox <gi...@apache.org>.
tirkarthi commented on issue #27082:
URL: https://github.com/apache/airflow/issues/27082#issuecomment-1280486571
> It’s likely not fixed, at least I don’t see anything relevant. Would creating the pks before inserting be a sufficient fix? We can do that only for MySQL.
Yes, that's the fix we did internally. I am also not sure how many users use the group replication setup in MySQL since I couldn't find any existing bug reports related to this but will be happy to add a PR.
--
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] tirkarthi commented on issue #27082: Airflow database migration fails in MySQL group replication mode
Posted by GitBox <gi...@apache.org>.
tirkarthi commented on issue #27082:
URL: https://github.com/apache/airflow/issues/27082#issuecomment-1280481957
Unfortunately, our upgrade target is 2.3.4 . The upgrade actually fails midway from 2.1.x to 2.3.4 and this migration is for moving some invalid data during 2.3.0 adding some new constraints. So the upgrade from 2.1.x will still fail for 2.4.x if I understand alembic migrations correctly since the file in main is the same as 2.3.4 release.
--
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] uranusjr commented on issue #27082: Airflow database migration fails in MySQL group replication mode
Posted by GitBox <gi...@apache.org>.
uranusjr commented on issue #27082:
URL: https://github.com/apache/airflow/issues/27082#issuecomment-1280484267
This can be fixed if a release fixes _the migration file itself_.
--
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] ephraimbuddy commented on issue #27082: Airflow database migration fails in MySQL group replication mode
Posted by GitBox <gi...@apache.org>.
ephraimbuddy commented on issue #27082:
URL: https://github.com/apache/airflow/issues/27082#issuecomment-1280362326
Did you try upgrading to 2.4.1? The issue may have been fixed
--
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] ephraimbuddy commented on issue #27082: Airflow database migration fails in MySQL group replication mode
Posted by GitBox <gi...@apache.org>.
ephraimbuddy commented on issue #27082:
URL: https://github.com/apache/airflow/issues/27082#issuecomment-1280484798
> Unfortunately, our upgrade target is 2.3.4 . The upgrade actually fails midway from 2.1.x to 2.3.4 and this migration is for moving some invalid data during 2.3.0 adding some new constraints. So the upgrade from 2.1.x will still fail for 2.4.x if I understand alembic migrations correctly since the file in main is the same as 2.3.4 release.
Sometimes we fix issues on the migration files in a release
--
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] uranusjr commented on issue #27082: Airflow database migration fails in MySQL group replication mode
Posted by GitBox <gi...@apache.org>.
uranusjr commented on issue #27082:
URL: https://github.com/apache/airflow/issues/27082#issuecomment-1280491368
The migration will work for people not using group replication, it’ll just be a bit slower. The indexes are currently created after insertion because this is faster on Postgres when we did the benchmarks, but that doesn’t necessarily hold on other databases anyway.
--
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
Re: [I] Airflow database migration fails in MySQL group replication mode [airflow]
Posted by "github-actions[bot] (via GitHub)" <gi...@apache.org>.
github-actions[bot] closed issue #27082: Airflow database migration fails in MySQL group replication mode
URL: https://github.com/apache/airflow/issues/27082
--
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] uranusjr commented on issue #27082: Airflow database migration fails in MySQL group replication mode
Posted by GitBox <gi...@apache.org>.
uranusjr commented on issue #27082:
URL: https://github.com/apache/airflow/issues/27082#issuecomment-1280481683
It’s likely not fixed, at least I don’t see anything relevant. Would creating the pks before inserting be a sufficient fix? We can do that only for MySQL.
--
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
Re: [I] Airflow database migration fails in MySQL group replication mode [airflow]
Posted by "github-actions[bot] (via GitHub)" <gi...@apache.org>.
github-actions[bot] commented on issue #27082:
URL: https://github.com/apache/airflow/issues/27082#issuecomment-1935426194
This issue has been closed because it has not received response from the issue author.
--
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
Re: [I] Airflow database migration fails in MySQL group replication mode [airflow]
Posted by "github-actions[bot] (via GitHub)" <gi...@apache.org>.
github-actions[bot] commented on issue #27082:
URL: https://github.com/apache/airflow/issues/27082#issuecomment-1879976338
This issue has been automatically marked as stale because it has been open for 365 days without any activity. There has been several Airflow releases since last activity on this issue. Kindly asking to recheck the report against latest Airflow version and let us know if the issue is reproducible. The issue will be closed in next 30 days if no further activity occurs from the issue author.
--
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 issue #27082: Airflow database migration fails in MySQL group replication mode
Posted by GitBox <gi...@apache.org>.
potiuk commented on issue #27082:
URL: https://github.com/apache/airflow/issues/27082#issuecomment-1288346833
Ideally the fix should be to detect if group replication is on and follow different migration path @tirkarthi.
We should not - for example - disable it for "all mysql".
I think we have to assume that it will be a LOT slower when you have huge database - and it's rather regardless from the database used (seen this happening in all of the relational databases). The root cause of the slowness is that most indexes are some kind of tree structure that have limited size and they require periodic re-organisations when the table grows. While this is usually pretty ok if done as the table grows naturally, when you insert many rows starting from 0, you will very likely hit such re-organisation multiple times during the insert process and it will get slower and slower as inserts progress.
--
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