[ 
https://issues.apache.org/jira/browse/AIRFLOW-5884?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16970829#comment-16970829
 ] 

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
 
 
   
 
----------------------------------------------------------------
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