[ 
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:
us...@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)

Reply via email to