[
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:[email protected]: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:
[email protected]
> 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:[email protected]: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)
