Fail to deploy airflow with external mysql database
vutuong opened this issue · 2 comments
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
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)
@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!