Fail to deploy airflow with external mysql database

Question

Fail to deploy airflow with external mysql database

vutuong opened this issue a year ago · 2 comments

vutuong commented a year ago

Checks

I have checked for existing issues.
This report is about the User-Community Airflow Helm Chart.

Chart Version

airflow-1.6.0

Kubernetes Version

kubectl version
WARNING: This version information is deprecated and will be replaced with the output from kubectl version --short.  Use --output=yaml|json to get the full version.
Client Version: version.Info{Major:"1", Minor:"26", GitVersion:"v1.26.5", GitCommit:"890a139214b4de1f01543d15003b5bda71aae9c7", GitTreeState:"clean", BuildDate:"2023-05-17T14:14:46Z", GoVersion:"go1.19.9", Compiler:"gc", Platform:"linux/amd64"}
Kustomize Version: v4.5.7
Server Version: version.Info{Major:"1", Minor:"26", GitVersion:"v1.26.5", GitCommit:"890a139214b4de1f01543d15003b5bda71aae9c7", GitTreeState:"clean", BuildDate:"2023-05-17T14:08:49Z", GoVersion:"go1.19.9", Compiler:"gc", Platform:"linux/amd64"}

Helm Version

version.BuildInfo{Version:"v3.12.0", GitCommit:"c9f554d75773799f72ceef38c51210f1842a1dea", GitTreeState:"clean", GoVersion:"go1.20.3"}

Description

I was trying to deploy airflow helm with metadataConnection to connect to mysql external. But pod can not run. The log show that airflow-migrations pod lost connection to mysql db during doing sometask. But how to handle it? I still can connect to my mysql database with cli.

Every 2.0s: kubectl get pod Fri Sep 15 11:34:54 2023

NAME READY STATUS RESTARTS AGE
airflow-redis-0 1/1 Running 0 26m
airflow-scheduler-6c69c784c7-lbz8h 0/2 Init:0/1 8 (5m19s ago) 26m
airflow-statsd-c65959df5-nz84t 1/1 Running 0 26m
airflow-triggerer-5647fc9585-kxwhz 0/1 Init:CrashLoopBackOff 7 (3m41s ago) 26m
airflow-webserver-b69fc664c-bfqmv 0/1 Init:0/1 8 (5m21s ago) 26m
airflow-worker-6b565fc5b6-2x8kf 0/1 Init:CrashLoopBackOff 7 (4m19s ago) 26m
airflow-worker-6b565fc5b6-gz2wl 0/1 Init:0/1 8 (5m19s ago) 26m

Relevant Logs

kubectl logs -f airflow-run-airflow-migrations-h5slh

[2023-09-15 04:08:46,908] {cli_action_loggers.py:105} WARNING - Failed to log action with (MySQLdb._exceptions.ProgrammingError) (1146, "Table 'airflow_db.log' doesn't exist")
[SQL: INSERT INTO log (dttm, dag_id, task_id, event, execution_date, owner, extra) VALUES (%s, %s, %s, %s, %s, %s, %s)]
[parameters: (datetime.datetime(2023, 9, 15, 4, 8, 46, 891548), None, None, 'cli_upgradedb', None, 'airflow', '{"host_name": "airflow-run-airflow-migrations-h5slh", "full_command": "[\'/home/airflow/.local/bin/airflow\', \'db\', \'upgrade\']"}')]
(Background on this error at: http://sqlalche.me/e/14/f405)
DB: mysql://airflow_user:***@10.98.3.1:3306/airflow_db
Performing upgrade with database mysql://airflow_user:***@10.98.3.1:3306/airflow_db
[2023-09-15 04:08:47,301] {db.py:1410} INFO - Creating tables
INFO  [alembic.runtime.migration] Context impl MySQLImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
INFO  [alembic.runtime.migration] Running upgrade  -> e3a246e0dc1, current schema
INFO  [alembic.runtime.migration] Running upgrade e3a246e0dc1 -> 1507a7289a2f, Add ``is_encrypted`` column in ``connection`` table
INFO  [alembic.runtime.migration] Running upgrade 1507a7289a2f -> 13eb55f81627, Maintain history for compatibility with earlier migrations
INFO  [alembic.runtime.migration] Running upgrade 13eb55f81627 -> 338e90f54d61, Add ``operator`` and ``queued_dttm`` to ``task_instance`` table
INFO  [alembic.runtime.migration] Running upgrade 338e90f54d61 -> 52d714495f0, Add indices in ``job`` table
INFO  [alembic.runtime.migration] Running upgrade 52d714495f0 -> 502898887f84, Adding ``extra`` column to ``Log`` table
INFO  [alembic.runtime.migration] Running upgrade 502898887f84 -> 1b38cef5b76e, Add ``dag_run`` table
INFO  [alembic.runtime.migration] Running upgrade 1b38cef5b76e -> 2e541a1dcfed, Change ``task_instance.task_duration`` type to ``FLOAT``
INFO  [alembic.runtime.migration] Running upgrade 2e541a1dcfed -> 40e67319e3a9, Add ``conf`` column in ``dag_run`` table
INFO  [alembic.runtime.migration] Running upgrade 40e67319e3a9 -> 561833c1c74b, Add ``password`` column to ``user`` table
INFO  [alembic.runtime.migration] Running upgrade 561833c1c74b -> 4446e08588, Add ``start_date`` and ``end_date`` in ``dag_run`` table
INFO  [alembic.runtime.migration] Running upgrade 4446e08588 -> bbc73705a13e, Add ``notification_sent`` column to ``sla_miss`` table
INFO  [alembic.runtime.migration] Running upgrade bbc73705a13e -> bba5a7cfc896, Add a column to track the encryption state of the 'Extra' field in connection
INFO  [alembic.runtime.migration] Running upgrade bba5a7cfc896 -> 1968acfc09e3, Add ``is_encrypted`` column to variable table
INFO  [alembic.runtime.migration] Running upgrade 1968acfc09e3 -> 2e82aab8ef20, Rename user table
INFO  [alembic.runtime.migration] Running upgrade 2e82aab8ef20 -> 211e584da130, Add TI state index
INFO  [alembic.runtime.migration] Running upgrade 211e584da130 -> 64de9cddf6c9, Add ``task_fail`` table
INFO  [alembic.runtime.migration] Running upgrade 64de9cddf6c9 -> f2ca10b85618, Add ``dag_stats`` table
INFO  [alembic.runtime.migration] Running upgrade f2ca10b85618 -> 4addfa1236f1, Add fractional seconds to MySQL tables
INFO  [alembic.runtime.migration] Running upgrade 4addfa1236f1 -> 8504051e801b, Add indices on ``xcom`` table
INFO  [alembic.runtime.migration] Running upgrade 8504051e801b -> 5e7d17757c7a, Add ``pid`` field to ``TaskInstance``
INFO  [alembic.runtime.migration] Running upgrade 5e7d17757c7a -> 127d2bf2dfa7, Add ``dag_id``/``state`` index on ``dag_run`` table
INFO  [alembic.runtime.migration] Running upgrade 127d2bf2dfa7 -> cc1e65623dc7, Add ``max_tries`` column to ``task_instance``
INFO  [alembic.runtime.migration] Running upgrade cc1e65623dc7 -> bdaa763e6c56, Make xcom value column a large binary
INFO  [alembic.runtime.migration] Running upgrade bdaa763e6c56 -> 947454bf1dff, Create index on ``job_id`` column in ``task_instance`` table
INFO  [alembic.runtime.migration] Running upgrade 947454bf1dff -> d2ae31099d61, Increase text size for MySQL (not relevant for other DBs' text types)
INFO  [alembic.runtime.migration] Running upgrade d2ae31099d61 -> 0e2a74e0fc9f, Add time zone awareness
Traceback (most recent call last):
  File "/home/airflow/.local/lib/python3.8/site-packages/airflow/utils/db.py", line 1597, in create_global_lock
    yield
  File "/home/airflow/.local/lib/python3.8/site-packages/airflow/utils/db.py", line 1411, in upgradedb
    command.upgrade(config, revision=to_revision or 'heads')
  File "/home/airflow/.local/lib/python3.8/site-packages/alembic/command.py", line 320, in upgrade
    script.run_env()
  File "/home/airflow/.local/lib/python3.8/site-packages/alembic/script/base.py", line 563, in run_env
    util.load_python_file(self.dir, "env.py")
  File "/home/airflow/.local/lib/python3.8/site-packages/alembic/util/pyfiles.py", line 92, in load_python_file
    module = load_module_py(module_id, path)
  File "/home/airflow/.local/lib/python3.8/site-packages/alembic/util/pyfiles.py", line 108, in load_module_py
    spec.loader.exec_module(module)  # type: ignore
  File "<frozen importlib._bootstrap_external>", line 843, in exec_module
  File "<frozen importlib._bootstrap>", line 219, in _call_with_frames_removed
  File "/home/airflow/.local/lib/python3.8/site-packages/airflow/migrations/env.py", line 107, in <module>
    run_migrations_online()
  File "/home/airflow/.local/lib/python3.8/site-packages/airflow/migrations/env.py", line 101, in run_migrations_online
    context.run_migrations()
  File "<string>", line 8, in run_migrations
  File "/home/airflow/.local/lib/python3.8/site-packages/alembic/runtime/environment.py", line 851, in run_migrations
    self.get_context().run_migrations(**kw)
  File "/home/airflow/.local/lib/python3.8/site-packages/alembic/runtime/migration.py", line 620, in run_migrations
    step.migration_fn(**kw)
  File "/home/airflow/.local/lib/python3.8/site-packages/airflow/migrations/versions/0027_0e2a74e0fc9f_add_time_zone_awareness.py", line 45, in upgrade
    raise Exception("Global variable explicit_defaults_for_timestamp needs to be on (1) for mysql")
Exception: Global variable explicit_defaults_for_timestamp needs to be on (1) for mysql

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/home/airflow/.local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1705, in _execute_context
    self.dialect.do_execute(
  File "/home/airflow/.local/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 716, in do_execute
    cursor.execute(statement, parameters)
  File "/home/airflow/.local/lib/python3.8/site-packages/MySQLdb/cursors.py", line 206, in execute
    res = self._query(query)
  File "/home/airflow/.local/lib/python3.8/site-packages/MySQLdb/cursors.py", line 319, in _query
    db.query(q)
  File "/home/airflow/.local/lib/python3.8/site-packages/MySQLdb/connections.py", line 254, in query
    _mysql.connection.query(self, query)
MySQLdb._exceptions.OperationalError: (2013, 'Lost connection to MySQL server during query')

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/home/airflow/.local/bin/airflow", line 8, in <module>
    sys.exit(main())
  File "/home/airflow/.local/lib/python3.8/site-packages/airflow/__main__.py", line 38, in main
    args.func(args)
  File "/home/airflow/.local/lib/python3.8/site-packages/airflow/cli/cli_parser.py", line 51, in command
    return func(*args, **kwargs)
  File "/home/airflow/.local/lib/python3.8/site-packages/airflow/utils/cli.py", line 99, in wrapper
    return f(*args, **kwargs)
  File "/home/airflow/.local/lib/python3.8/site-packages/airflow/cli/commands/db_command.py", line 82, in upgradedb
    db.upgradedb(to_revision=to_revision, from_revision=from_revision, show_sql_only=args.show_sql_only)
  File "/home/airflow/.local/lib/python3.8/site-packages/airflow/utils/session.py", line 71, in wrapper
    return func(*args, session=session, **kwargs)
  File "/home/airflow/.local/lib/python3.8/site-packages/airflow/utils/db.py", line 1411, in upgradedb
    command.upgrade(config, revision=to_revision or 'heads')
  File "/usr/local/lib/python3.8/contextlib.py", line 131, in __exit__
    self.gen.throw(type, value, traceback)
  File "/home/airflow/.local/lib/python3.8/site-packages/airflow/utils/db.py", line 1605, in create_global_lock
    conn.execute(text("select RELEASE_LOCK(:id)"), id=str(lock))
  File "/home/airflow/.local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1200, in execute
    return meth(self, multiparams, params, _EMPTY_EXECUTION_OPTS)
  File "/home/airflow/.local/lib/python3.8/site-packages/sqlalchemy/sql/elements.py", line 313, in _execute_on_connection
    return connection._execute_clauseelement(
  File "/home/airflow/.local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1389, in _execute_clauseelement
    ret = self._execute_context(
  File "/home/airflow/.local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1748, in _execute_context
    self._handle_dbapi_exception(
  File "/home/airflow/.local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1929, in _handle_dbapi_exception
    util.raise_(
  File "/home/airflow/.local/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 211, in raise_
    raise exception
  File "/home/airflow/.local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1705, in _execute_context
    self.dialect.do_execute(
  File "/home/airflow/.local/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 716, in do_execute
    cursor.execute(statement, parameters)
  File "/home/airflow/.local/lib/python3.8/site-packages/MySQLdb/cursors.py", line 206, in execute
    res = self._query(query)
  File "/home/airflow/.local/lib/python3.8/site-packages/MySQLdb/cursors.py", line 319, in _query
    db.query(q)
  File "/home/airflow/.local/lib/python3.8/site-packages/MySQLdb/connections.py", line 254, in query
    _mysql.connection.query(self, query)
sqlalchemy.exc.OperationalError: (MySQLdb._exceptions.OperationalError) (2013, 'Lost connection to MySQL server during query')
[SQL: select RELEASE_LOCK(%s)]
[parameters: ('airflow_MIGRATIONS',)]
(Background on this error at: http://sqlalche.me/e/14/e3q8)

Custom Helm Values

metadataConnection:
    user: airflow_user
    pass: airflow_pass
    protocol: mysql
    host: 10.98.3.1
    port: 3306
    db: airflow_db
    sslmode: disable
postgresql:
  enabled: false

Answer 1 · 2023-09-15T07:29:24.000Z

I realize that the above mentioned error related to the connection timeout. As the default connect_timeout of my database is 10s. So I increase it to 600. I then have to change the explicit_defaults_for_timestamp to ON as SET GLOBAL explicit_defaults_for_timestamp = 1;.

After all now it raise the another error, and now i don't know how to fix it becasue it looks like related to the airflow code and not related to mysql setup anymore.

File "/home/airflow/.local/lib/python3.8/site-packages/MySQLdb/connections.py", line 254, in query
_mysql.connection.query(self, query)
sqlalchemy.exc.OperationalError: (MySQLdb._exceptions.OperationalError) (4091, "Unknown SEQUENCE: 'ab_permission_id_seq'")
[SQL: select nextval(ab_permission_id_seq)]
(Background on this error at: http://sqlalche.me/e/14/e3q8)

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/home/airflow/.local/bin/airflow", line 8, in <module>
    sys.exit(main())
  File "/home/airflow/.local/lib/python3.8/site-packages/airflow/__main__.py", line 38, in main
    args.func(args)
  File "/home/airflow/.local/lib/python3.8/site-packages/airflow/cli/cli_parser.py", line 51, in command
    return func(*args, **kwargs)
  File "/home/airflow/.local/lib/python3.8/site-packages/airflow/utils/cli.py", line 99, in wrapper
    return f(*args, **kwargs)
  File "/home/airflow/.local/lib/python3.8/site-packages/airflow/cli/commands/db_command.py", line 82, in upgradedb
    db.upgradedb(to_revision=to_revision, from_revision=from_revision, show_sql_only=args.show_sql_only)
  File "/home/airflow/.local/lib/python3.8/site-packages/airflow/utils/session.py", line 71, in wrapper
    return func(*args, session=session, **kwargs)
  File "/home/airflow/.local/lib/python3.8/site-packages/airflow/utils/db.py", line 1411, in upgradedb
    command.upgrade(config, revision=to_revision or 'heads')
  File "/home/airflow/.local/lib/python3.8/site-packages/alembic/command.py", line 320, in upgrade
    script.run_env()
  File "/home/airflow/.local/lib/python3.8/site-packages/alembic/script/base.py", line 563, in run_env
    util.load_python_file(self.dir, "env.py")
  File "/home/airflow/.local/lib/python3.8/site-packages/alembic/util/pyfiles.py", line 92, in load_python_file
    module = load_module_py(module_id, path)
  File "/home/airflow/.local/lib/python3.8/site-packages/alembic/util/pyfiles.py", line 108, in load_module_py
    spec.loader.exec_module(module)  # type: ignore
  File "<frozen importlib._bootstrap_external>", line 843, in exec_module
  File "<frozen importlib._bootstrap>", line 219, in _call_with_frames_removed
  File "/home/airflow/.local/lib/python3.8/site-packages/airflow/migrations/env.py", line 107, in <module>
    run_migrations_online()
  File "/home/airflow/.local/lib/python3.8/site-packages/airflow/migrations/env.py", line 101, in run_migrations_online
    context.run_migrations()
  File "<string>", line 8, in run_migrations
  File "/home/airflow/.local/lib/python3.8/site-packages/alembic/runtime/environment.py", line 851, in run_migrations
    self.get_context().run_migrations(**kw)
  File "/home/airflow/.local/lib/python3.8/site-packages/alembic/runtime/migration.py", line 620, in run_migrations
    step.migration_fn(**kw)
  File "/home/airflow/.local/lib/python3.8/site-packages/airflow/migrations/versions/0073_849da589634d_prefix_dag_permissions.py", line 231, in upgrade
    migrate_to_new_dag_permissions(db)
  File "/home/airflow/.local/lib/python3.8/site-packages/airflow/migrations/versions/0073_849da589634d_prefix_dag_permissions.py", line 161, in migrate_to_new_dag_permissions
    can_read_action = get_or_create_action(db.session, 'can_read')
  File "/home/airflow/.local/lib/python3.8/site-packages/airflow/migrations/versions/0073_849da589634d_prefix_dag_permissions.py", line 109, in get_or_create_action
    session.commit()
  File "<string>", line 2, in commit
  File "/home/airflow/.local/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 1423, in commit
    self._transaction.commit(_to_root=self.future)
  File "/home/airflow/.local/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 829, in commit
    self._prepare_impl()
  File "/home/airflow/.local/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 808, in _prepare_impl
    self.session.flush()
  File "/home/airflow/.local/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 3255, in flush
    self._flush(objects)
  File "/home/airflow/.local/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 3395, in _flush
    transaction.rollback(_capture_exception=True)
  File "/home/airflow/.local/lib/python3.8/site-packages/sqlalchemy/util/langhelpers.py", line 70, in __exit__
    compat.raise_(
  File "/home/airflow/.local/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 211, in raise_
    raise exception
  File "/home/airflow/.local/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 3355, in _flush
    flush_context.execute()
  File "/home/airflow/.local/lib/python3.8/site-packages/sqlalchemy/orm/unitofwork.py", line 453, in execute
    rec.execute(self)
  File "/home/airflow/.local/lib/python3.8/site-packages/sqlalchemy/orm/unitofwork.py", line 627, in execute
    util.preloaded.orm_persistence.save_obj(
  File "/home/airflow/.local/lib/python3.8/site-packages/sqlalchemy/orm/persistence.py", line 242, in save_obj
    _emit_insert_statements(
  File "/home/airflow/.local/lib/python3.8/site-packages/sqlalchemy/orm/persistence.py", line 1219, in _emit_insert_statements
    result = connection._execute_20(
  File "/home/airflow/.local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1520, in _execute_20
    return meth(self, args_10style, kwargs_10style, execution_options)
  File "/home/airflow/.local/lib/python3.8/site-packages/sqlalchemy/sql/elements.py", line 313, in _execute_on_connection
    return connection._execute_clauseelement(
  File "/home/airflow/.local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1389, in _execute_clauseelement
    ret = self._execute_context(
  File "/home/airflow/.local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1614, in _execute_context
    self._handle_dbapi_exception(
  File "/home/airflow/.local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1929, in _handle_dbapi_exception
    util.raise_(
  File "/home/airflow/.local/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 211, in raise_
    raise exception
  File "/home/airflow/.local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1608, in _execute_context
    context = constructor(
  File "/home/airflow/.local/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 998, in _init_compiled
    self._process_executesingle_defaults()
  File "/home/airflow/.local/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 1866, in _process_executesingle_defaults
    val = self.get_insert_default(c)
  File "/home/airflow/.local/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 1811, in get_insert_default
    return self._exec_default(column, column.default, column.type)
  File "/home/airflow/.local/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 1672, in _exec_default
    return self.fire_sequence(default, type_)
  File "/home/airflow/.local/lib/python3.8/site-packages/sqlalchemy/dialects/mysql/base.py", line 1401, in fire_sequence
    return self._execute_scalar(
  File "/home/airflow/.local/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 1269, in _execute_scalar
    conn._cursor_execute(self.cursor, stmt, parameters, context=self)
  File "/home/airflow/.local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1785, in _cursor_execute
    self._handle_dbapi_exception(
  File "/home/airflow/.local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1929, in _handle_dbapi_exception
    util.raise_(
  File "/home/airflow/.local/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 211, in raise_
    raise exception
  File "/home/airflow/.local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1783, in _cursor_execute
    self.dialect.do_execute(cursor, statement, parameters, context)
  File "/home/airflow/.local/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 716, in do_execute
    cursor.execute(statement, parameters)
  File "/home/airflow/.local/lib/python3.8/site-packages/MySQLdb/cursors.py", line 206, in execute
    res = self._query(query)
  File "/home/airflow/.local/lib/python3.8/site-packages/MySQLdb/cursors.py", line 319, in _query
    db.query(q)
  File "/home/airflow/.local/lib/python3.8/site-packages/MySQLdb/connections.py", line 254, in query
    _mysql.connection.query(self, query)
sqlalchemy.exc.StatementError: (sqlalchemy.exc.OperationalError) (MySQLdb._exceptions.OperationalError) (4091, "Unknown SEQUENCE: 'ab_permission_id_seq'")
[SQL: INSERT INTO ab_permission (id, name) VALUES (%s, %s)]
[parameters: [{'name': 'can_read'}]]
(Background on this error at: http://sqlalche.me/e/14/e3q8)

Answer 2 · 2023-09-15T18:35:12.000Z

@vutuong you are using a different helm chart than the one in this repo, you should ask on the apache/airflow repo for help with their helm chart.

Or, you could try our one from this repo!