You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@airflow.apache.org by "ASF GitHub Bot (Jira)" <ji...@apache.org> on 2019/11/09 14:00:08 UTC
[jira] [Commented] (AIRFLOW-5884) Airflow cant create migration on
MySQL
[ https://issues.apache.org/jira/browse/AIRFLOW-5884?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16970802#comment-16970802 ]
ASF GitHub Bot commented on AIRFLOW-5884:
-----------------------------------------
blcksrx commented on pull request #6534: [AIRFLOW-5884] Fix MySQL migrations bug related to create varchar columns greatear than 767 bytes
URL: https://github.com/apache/airflow/pull/6534
Make sure you have checked _all_ steps below.
### Jira
- [x] My PR addresses the following [Airflow Jira](https://issues.apache.org/jira/browse/AIRFLOW/) issues and references them in the PR title. For example, "\[AIRFLOW-XXX\] My Airflow PR"
- https://issues.apache.org/jira/browse/AIRFLOW-XXX
- In case you are fixing a typo in the documentation you can prepend your commit with \[AIRFLOW-XXX\], code changes always need a Jira issue.
- In case you are proposing a fundamental code change, you need to create an Airflow Improvement Proposal ([AIP](https://cwiki.apache.org/confluence/display/AIRFLOW/Airflow+Improvements+Proposals)).
- In case you are adding a dependency, check if the license complies with the [ASF 3rd Party License Policy](https://www.apache.org/legal/resolved.html#category-x).
### Description
- [x] Here are some details about my PR, including screenshots of any UI changes:
Airflow can't set up with **Mysql**. Here is the log of `airflow db initdb`:
```
2019-11-09 13:57:48,695] {settings.py:213} INFO - settings.configure_orm(): Using pool settings. pool_size=5, max_overflow=10, pool_recycle=1800, pid=59502019-11-09 13:57:48,695] {settings.py:213} INFO - settings.configure_orm(): Using pool settings. pool_size=5, max_overflow=10, pool_recycle=1800, pid=5950[2019-11-09 13:57:49,237] {default_celery.py:90} WARNING - You have configured a result_backend of pyamqp://admin:Al0peykHadoop@172.16.2.230:5672/airflow, it is highly recommended to use an alternative result_backend (i.e. a database).[2019-11-09 13:57:49,239] {__init__.py:51} INFO - Using executor CeleryExecutorDB: mysql://root:***@172.16.2.230:3306/airflowThis will drop existing tables if they exist. Proceed? (y/n)y[2019-11-09 13:57:50,627] {db.py:390} INFO - Dropping tables that exist[2019-11-09 13:57:50,679] {migration.py:130} INFO - Context impl MySQLImpl.[2019-11-09 13:57:50,680] {migration.py:137} INFO - Will assume non-transactional DDL.[2019-11-09 13:57:50,691] {db.py:369} INFO - Creating tablesINFO [alembic.runtime.migration] Context impl MySQLImpl.INFO [alembic.runtime.migration] Will assume non-transactional DDL.INFO [alembic.runtime.migration] Running upgrade -> e3a246e0dc1, current schemaTraceback (most recent call last): File "/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1244, in _execute_context cursor, statement, parameters, context File "/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/default.py", line 552, in do_execute cursor.execute(statement, parameters) File "/usr/local/lib/python3.7/site-packages/MySQLdb/cursors.py", line 255, in execute self.errorhandler(self, exc, value) File "/usr/local/lib/python3.7/site-packages/MySQLdb/connections.py", line 50, in defaulterrorhandler raise errorvalue File "/usr/local/lib/python3.7/site-packages/MySQLdb/cursors.py", line 252, in execute res = self._query(query) File "/usr/local/lib/python3.7/site-packages/MySQLdb/cursors.py", line 378, in _query db.query(q) File "/usr/local/lib/python3.7/site-packages/MySQLdb/connections.py", line 280, in query _mysql.connection.query(self, query)_mysql_exceptions.OperationalError: (1071, 'Specified key was too long; max key length is 767 bytes')
The above exception was the direct cause of the following exception:
Traceback (most recent call last): File "/usr/local/bin/airflow", line 32, in <module> args.func(args) File "/usr/local/lib/python3.7/site-packages/airflow/bin/cli.py", line 1112, in resetdb db.resetdb(settings.RBAC) File "/usr/local/lib/python3.7/site-packages/airflow/utils/db.py", line 406, in resetdb initdb(rbac) File "/usr/local/lib/python3.7/site-packages/airflow/utils/db.py", line 106, in initdb upgradedb() File "/usr/local/lib/python3.7/site-packages/airflow/utils/db.py", line 377, in upgradedb command.upgrade(config, 'heads') File "/usr/local/lib/python3.7/site-packages/alembic/command.py", line 276, in upgrade script.run_env() File "/usr/local/lib/python3.7/site-packages/alembic/script/base.py", line 475, in run_env util.load_python_file(self.dir, "env.py") File "/usr/local/lib/python3.7/site-packages/alembic/util/pyfiles.py", line 90, in load_python_file module = load_module_py(module_id, path) File "/usr/local/lib/python3.7/site-packages/alembic/util/compat.py", line 177, in load_module_py spec.loader.exec_module(module) File "<frozen importlib._bootstrap_external>", line 728, in exec_module File "<frozen importlib._bootstrap>", line 219, in _call_with_frames_removed File "/usr/local/lib/python3.7/site-packages/airflow/migrations/env.py", line 92, in <module> run_migrations_online() File "/usr/local/lib/python3.7/site-packages/airflow/migrations/env.py", line 86, in run_migrations_online context.run_migrations() File "<string>", line 8, in run_migrations File "/usr/local/lib/python3.7/site-packages/alembic/runtime/environment.py", line 839, in run_migrations self.get_context().run_migrations(**kw) File "/usr/local/lib/python3.7/site-packages/alembic/runtime/migration.py", line 362, in run_migrations step.migration_fn(**kw) File "/usr/local/lib/python3.7/site-packages/airflow/migrations/versions/e3a246e0dc1_current_schema.py", line 73, in upgrade sa.PrimaryKeyConstraint('dag_id') File "<string>", line 8, in create_table File "<string>", line 3, in create_table File "/usr/local/lib/python3.7/site-packages/alembic/operations/ops.py", line 1248, in create_table return operations.invoke(op) File "/usr/local/lib/python3.7/site-packages/alembic/operations/base.py", line 345, in invoke return fn(self, operation) File "/usr/local/lib/python3.7/site-packages/alembic/operations/toimpl.py", line 101, in create_table operations.impl.create_table(table) File "/usr/local/lib/python3.7/site-packages/alembic/ddl/impl.py", line 252, in create_table self._exec(schema.CreateTable(table)) File "/usr/local/lib/python3.7/site-packages/alembic/ddl/impl.py", line 134, in _exec return conn.execute(construct, *multiparams, **params) File "/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 988, in execute return meth(self, multiparams, params) File "/usr/local/lib/python3.7/site-packages/sqlalchemy/sql/ddl.py", line 72, in _execute_on_connection return connection._execute_ddl(self, multiparams, params) File "/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1050, in _execute_ddl compiled, File "/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1248, in _execute_context e, statement, parameters, cursor, context File "/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1466, in _handle_dbapi_exception util.raise_from_cause(sqlalchemy_exception, exc_info) File "/usr/local/lib/python3.7/site-packages/sqlalchemy/util/compat.py", line 398, in raise_from_cause reraise(type(exception), exception, tb=exc_tb, cause=cause) File "/usr/local/lib/python3.7/site-packages/sqlalchemy/util/compat.py", line 152, in reraise raise value.with_traceback(tb) File "/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1244, in _execute_context cursor, statement, parameters, context File "/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/default.py", line 552, in do_execute cursor.execute(statement, parameters) File "/usr/local/lib/python3.7/site-packages/MySQLdb/cursors.py", line 255, in execute self.errorhandler(self, exc, value) File "/usr/local/lib/python3.7/site-packages/MySQLdb/connections.py", line 50, in defaulterrorhandler raise errorvalue File "/usr/local/lib/python3.7/site-packages/MySQLdb/cursors.py", line 252, in execute res = self._query(query) File "/usr/local/lib/python3.7/site-packages/MySQLdb/cursors.py", line 378, in _query db.query(q) File "/usr/local/lib/python3.7/site-packages/MySQLdb/connections.py", line 280, in query _mysql.connection.query(self, query)sqlalchemy.exc.OperationalError: (_mysql_exceptions.OperationalError) (1071, 'Specified key was too long; max key length is 767 bytes')[SQL: CREATE TABLE dag ( dag_id VARCHAR(250) NOT NULL, is_paused BOOL, is_subdag BOOL, is_active BOOL, last_scheduler_run DATETIME, last_pickled DATETIME, last_expired DATETIME, scheduler_lock BOOL, pickle_id INTEGER, fileloc VARCHAR(2000), owners VARCHAR(2000), PRIMARY KEY (dag_id), CHECK (is_paused IN (0, 1)), CHECK (is_subdag IN (0, 1)), CHECK (is_active IN (0, 1)), CHECK (scheduler_lock IN (0, 1)))
](Background on this error at: http://sqlalche.me/e/e3q8)
```
It seems this issue related to the dag table because mysql can't create **varchar** column more than **767** bytes. I tried to create this table on MySQL and the results were the same. here is the result:
```
MariaDB [airflow]> CREATE TABLE dag (
-> dag_id VARCHAR(250) NOT NULL,
-> is_paused BOOL,
-> is_subdag BOOL,
-> is_active BOOL,
-> last_scheduler_run DATETIME,
-> last_pickled DATETIME,
-> last_expired DATETIME,
-> scheduler_lock BOOL,
-> pickle_id INTEGER,
-> fileloc VARCHAR(2000),
-> owners VARCHAR(2000),
-> PRIMARY KEY (dag_id),
-> CHECK (is_paused IN (0, 1)),
-> CHECK (is_subdag IN (0, 1)),
-> CHECK (is_active IN (0, 1)),
-> CHECK (scheduler_lock IN (0, 1))
-> )
-> ;
ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes
```
### Tests
- [x] My PR adds the following unit tests __OR__ does not need testing for this extremely good reason:
### Commits
- [x] My commits all reference Jira issues in their subject lines, and I have squashed multiple commits if they address the same issue. In addition, my commits follow the guidelines from "[How to write a good git commit message](http://chris.beams.io/posts/git-commit/)":
1. Subject is separated from body by a blank line
1. Subject is limited to 50 characters (not including Jira issue reference)
1. Subject does not end with a period
1. Subject uses the imperative mood ("add", not "adding")
1. Body wraps at 72 characters
1. Body explains "what" and "why", not "how"
### Documentation
- [x] In case of new functionality, my PR adds documentation that describes how to use it.
- All the public functions and the classes in the PR contain docstrings that explain what it does
- If you implement backwards incompatible changes, please leave a note in the [Updating.md](https://github.com/apache/airflow/blob/master/UPDATING.md) so we can assign it to a appropriate 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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
> Airflow cant create migration on MySQL
> --------------------------------------
>
> Key: AIRFLOW-5884
> URL: https://issues.apache.org/jira/browse/AIRFLOW-5884
> Project: Apache Airflow
> Issue Type: Bug
> Components: cli, database
> Affects Versions: 1.10.5, 1.10.6
> Environment: python3.7
> 10.1.38-MariaDB-0+deb9u1 Debian 9.8
> Reporter: Sayed Mohammad Hossein Torabi
> Assignee: Sayed Mohammad Hossein Torabi
> Priority: Major
>
> Airflow can't set up with *Mysql*. Here is the log of *airflow db initdb*:
>
> {code:java}
> 2019-11-09 13:57:48,695] {settings.py:213} INFO - settings.configure_orm(): Using pool settings. pool_size=5, max_overflow=10, pool_recycle=1800, pid=59502019-11-09 13:57:48,695] {settings.py:213} INFO - settings.configure_orm(): Using pool settings. pool_size=5, max_overflow=10, pool_recycle=1800, pid=5950[2019-11-09 13:57:49,237] {default_celery.py:90} WARNING - You have configured a result_backend of pyamqp://admin:Al0peykHadoop@172.16.2.230:5672/airflow, it is highly recommended to use an alternative result_backend (i.e. a database).[2019-11-09 13:57:49,239] {__init__.py:51} INFO - Using executor CeleryExecutorDB: mysql://root:***@172.16.2.230:3306/airflowThis will drop existing tables if they exist. Proceed? (y/n)y[2019-11-09 13:57:50,627] {db.py:390} INFO - Dropping tables that exist[2019-11-09 13:57:50,679] {migration.py:130} INFO - Context impl MySQLImpl.[2019-11-09 13:57:50,680] {migration.py:137} INFO - Will assume non-transactional DDL.[2019-11-09 13:57:50,691] {db.py:369} INFO - Creating tablesINFO [alembic.runtime.migration] Context impl MySQLImpl.INFO [alembic.runtime.migration] Will assume non-transactional DDL.INFO [alembic.runtime.migration] Running upgrade -> e3a246e0dc1, current schemaTraceback (most recent call last): File "/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1244, in _execute_context cursor, statement, parameters, context File "/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/default.py", line 552, in do_execute cursor.execute(statement, parameters) File "/usr/local/lib/python3.7/site-packages/MySQLdb/cursors.py", line 255, in execute self.errorhandler(self, exc, value) File "/usr/local/lib/python3.7/site-packages/MySQLdb/connections.py", line 50, in defaulterrorhandler raise errorvalue File "/usr/local/lib/python3.7/site-packages/MySQLdb/cursors.py", line 252, in execute res = self._query(query) File "/usr/local/lib/python3.7/site-packages/MySQLdb/cursors.py", line 378, in _query db.query(q) File "/usr/local/lib/python3.7/site-packages/MySQLdb/connections.py", line 280, in query _mysql.connection.query(self, query)_mysql_exceptions.OperationalError: (1071, 'Specified key was too long; max key length is 767 bytes')
> The above exception was the direct cause of the following exception:
> Traceback (most recent call last): File "/usr/local/bin/airflow", line 32, in <module> args.func(args) File "/usr/local/lib/python3.7/site-packages/airflow/bin/cli.py", line 1112, in resetdb db.resetdb(settings.RBAC) File "/usr/local/lib/python3.7/site-packages/airflow/utils/db.py", line 406, in resetdb initdb(rbac) File "/usr/local/lib/python3.7/site-packages/airflow/utils/db.py", line 106, in initdb upgradedb() File "/usr/local/lib/python3.7/site-packages/airflow/utils/db.py", line 377, in upgradedb command.upgrade(config, 'heads') File "/usr/local/lib/python3.7/site-packages/alembic/command.py", line 276, in upgrade script.run_env() File "/usr/local/lib/python3.7/site-packages/alembic/script/base.py", line 475, in run_env util.load_python_file(self.dir, "env.py") File "/usr/local/lib/python3.7/site-packages/alembic/util/pyfiles.py", line 90, in load_python_file module = load_module_py(module_id, path) File "/usr/local/lib/python3.7/site-packages/alembic/util/compat.py", line 177, in load_module_py spec.loader.exec_module(module) File "<frozen importlib._bootstrap_external>", line 728, in exec_module File "<frozen importlib._bootstrap>", line 219, in _call_with_frames_removed File "/usr/local/lib/python3.7/site-packages/airflow/migrations/env.py", line 92, in <module> run_migrations_online() File "/usr/local/lib/python3.7/site-packages/airflow/migrations/env.py", line 86, in run_migrations_online context.run_migrations() File "<string>", line 8, in run_migrations File "/usr/local/lib/python3.7/site-packages/alembic/runtime/environment.py", line 839, in run_migrations self.get_context().run_migrations(**kw) File "/usr/local/lib/python3.7/site-packages/alembic/runtime/migration.py", line 362, in run_migrations step.migration_fn(**kw) File "/usr/local/lib/python3.7/site-packages/airflow/migrations/versions/e3a246e0dc1_current_schema.py", line 73, in upgrade sa.PrimaryKeyConstraint('dag_id') File "<string>", line 8, in create_table File "<string>", line 3, in create_table File "/usr/local/lib/python3.7/site-packages/alembic/operations/ops.py", line 1248, in create_table return operations.invoke(op) File "/usr/local/lib/python3.7/site-packages/alembic/operations/base.py", line 345, in invoke return fn(self, operation) File "/usr/local/lib/python3.7/site-packages/alembic/operations/toimpl.py", line 101, in create_table operations.impl.create_table(table) File "/usr/local/lib/python3.7/site-packages/alembic/ddl/impl.py", line 252, in create_table self._exec(schema.CreateTable(table)) File "/usr/local/lib/python3.7/site-packages/alembic/ddl/impl.py", line 134, in _exec return conn.execute(construct, *multiparams, **params) File "/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 988, in execute return meth(self, multiparams, params) File "/usr/local/lib/python3.7/site-packages/sqlalchemy/sql/ddl.py", line 72, in _execute_on_connection return connection._execute_ddl(self, multiparams, params) File "/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1050, in _execute_ddl compiled, File "/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1248, in _execute_context e, statement, parameters, cursor, context File "/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1466, in _handle_dbapi_exception util.raise_from_cause(sqlalchemy_exception, exc_info) File "/usr/local/lib/python3.7/site-packages/sqlalchemy/util/compat.py", line 398, in raise_from_cause reraise(type(exception), exception, tb=exc_tb, cause=cause) File "/usr/local/lib/python3.7/site-packages/sqlalchemy/util/compat.py", line 152, in reraise raise value.with_traceback(tb) File "/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1244, in _execute_context cursor, statement, parameters, context File "/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/default.py", line 552, in do_execute cursor.execute(statement, parameters) File "/usr/local/lib/python3.7/site-packages/MySQLdb/cursors.py", line 255, in execute self.errorhandler(self, exc, value) File "/usr/local/lib/python3.7/site-packages/MySQLdb/connections.py", line 50, in defaulterrorhandler raise errorvalue File "/usr/local/lib/python3.7/site-packages/MySQLdb/cursors.py", line 252, in execute res = self._query(query) File "/usr/local/lib/python3.7/site-packages/MySQLdb/cursors.py", line 378, in _query db.query(q) File "/usr/local/lib/python3.7/site-packages/MySQLdb/connections.py", line 280, in query _mysql.connection.query(self, query)sqlalchemy.exc.OperationalError: (_mysql_exceptions.OperationalError) (1071, 'Specified key was too long; max key length is 767 bytes')[SQL: CREATE TABLE dag ( dag_id VARCHAR(250) NOT NULL, is_paused BOOL, is_subdag BOOL, is_active BOOL, last_scheduler_run DATETIME, last_pickled DATETIME, last_expired DATETIME, scheduler_lock BOOL, pickle_id INTEGER, fileloc VARCHAR(2000), owners VARCHAR(2000), PRIMARY KEY (dag_id), CHECK (is_paused IN (0, 1)), CHECK (is_subdag IN (0, 1)), CHECK (is_active IN (0, 1)), CHECK (scheduler_lock IN (0, 1)))
> ](Background on this error at: http://sqlalche.me/e/e3q8)
> {code}
> It seems this issue related to the *dag table* because mysql can't create varchar column more than 767 bytes. I tried to create this table on MySQL and the results were the same. here is the result:
>
>
> {code:java}
> MariaDB [airflow]> CREATE TABLE dag (
> -> dag_id VARCHAR(250) NOT NULL,
> -> is_paused BOOL,
> -> is_subdag BOOL,
> -> is_active BOOL,
> -> last_scheduler_run DATETIME,
> -> last_pickled DATETIME,
> -> last_expired DATETIME,
> -> scheduler_lock BOOL,
> -> pickle_id INTEGER,
> -> fileloc VARCHAR(2000),
> -> owners VARCHAR(2000),
> -> PRIMARY KEY (dag_id),
> -> CHECK (is_paused IN (0, 1)),
> -> CHECK (is_subdag IN (0, 1)),
> -> CHECK (is_active IN (0, 1)),
> -> CHECK (scheduler_lock IN (0, 1))
> -> )
> -> ;
> ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes
> {code}
>
>
--
This message was sent by Atlassian Jira
(v8.3.4#803005)