You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@airflow.apache.org by GitBox <gi...@apache.org> on 2022/06/06 08:35:31 UTC
[GitHub] [airflow] Mottimo opened a new issue, #24247: DB init and upgrade for MariaDB does not work on Airflow 2.3
Mottimo opened a new issue, #24247:
URL: https://github.com/apache/airflow/issues/24247
### Apache Airflow version
2.3.1
### What happened
The execution of of the command "airflow db upgrade" to upgrade the version from 2.2.5 to 2.3.0 or 2.3.1 fails with the following error:
(airflow) [airflow@rabbit01 airflow]$ export LD_LIBRARY_PATH=/opt/python-bit/lib:$LD_LIBRARY_PATH
(airflow) [airflow@rabbit01 airflow]$ airflow db upgrade
DB: mysql+mysqldb://airflow:***@localdb.xyste.it:3306/airflow
Performing upgrade with database mysql+mysqldb://airflow:***@localdb.localdomain.it:3306/airflow
[2022-06-06 10:18:48,523] {db.py:1445} 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 45ba3f1493b9 -> 849da589634d, Prefix DAG permissions.
Traceback (most recent call last):
File "/app/x01/airflow/environments/airflow/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1783, in _cursor_execute
self.dialect.do_execute(cursor, statement, parameters, context)
File "/app/x01/airflow/environments/airflow/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 716, in do_execute
cursor.execute(statement, parameters)
File "/app/x01/airflow/environments/airflow/lib/python3.8/site-packages/MySQLdb/cursors.py", line 206, in execute
res = self._query(query)
File "/app/x01/airflow/environments/airflow/lib/python3.8/site-packages/MySQLdb/cursors.py", line 319, in _query
db.query(q)
File "/app/x01/airflow/environments/airflow/lib/python3.8/site-packages/MySQLdb/connections.py", line 254, in query
_mysql.connection.query(self, query)
**MySQLdb._exceptions.ProgrammingError: (1146, "Table 'airflow.ab_permission_id_seq' doesn't exist")**
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File "/app/x01/airflow/environments/airflow/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1608, in _execute_context
context = constructor(
File "/app/x01/airflow/environments/airflow/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 998, in _init_compiled
self._process_executesingle_defaults()
File "/app/x01/airflow/environments/airflow/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 1866, in _process_executesingle_defaults
val = self.get_insert_default(c)
File "/app/x01/airflow/environments/airflow/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 "/app/x01/airflow/environments/airflow/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 1672, in _exec_default
return self.fire_sequence(default, type_)
File "/app/x01/airflow/environments/airflow/lib/python3.8/site-packages/sqlalchemy/dialects/mysql/base.py", line 1401, in fire_sequence
return self._execute_scalar(
File "/app/x01/airflow/environments/airflow/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 1269, in _execute_scalar
conn._cursor_execute(self.cursor, stmt, parameters, context=self)
File "/app/x01/airflow/environments/airflow/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1785, in _cursor_execute
self._handle_dbapi_exception(
File "/app/x01/airflow/environments/airflow/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1929, in _handle_dbapi_exception
util.raise_(
File "/app/x01/airflow/environments/airflow/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 211, in raise_
raise exception
File "/app/x01/airflow/environments/airflow/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1783, in _cursor_execute
self.dialect.do_execute(cursor, statement, parameters, context)
File "/app/x01/airflow/environments/airflow/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 716, in do_execute
cursor.execute(statement, parameters)
File "/app/x01/airflow/environments/airflow/lib/python3.8/site-packages/MySQLdb/cursors.py", line 206, in execute
res = self._query(query)
File "/app/x01/airflow/environments/airflow/lib/python3.8/site-packages/MySQLdb/cursors.py", line 319, in _query
db.query(q)
File "/app/x01/airflow/environments/airflow/lib/python3.8/site-packages/MySQLdb/connections.py", line 254, in query
_mysql.connection.query(self, query)
**sqlalchemy.exc.ProgrammingError: (MySQLdb._exceptions.ProgrammingError) (1146, "Table 'airflow.ab_permission_id_seq' doesn't exist")**
[SQL: select nextval(ab_permission_id_seq)]
(Background on this error at: http://sqlalche.me/e/14/f405)
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File "/app/x01/airflow/environments/airflow/bin/airflow", line 8, in <module>
sys.exit(main())
File "/app/x01/airflow/environments/airflow/lib/python3.8/site-packages/airflow/__main__.py", line 38, in main
args.func(args)
File "/app/x01/airflow/environments/airflow/lib/python3.8/site-packages/airflow/cli/cli_parser.py", line 51, in command
return func(*args, **kwargs)
File "/app/x01/airflow/environments/airflow/lib/python3.8/site-packages/airflow/utils/cli.py", line 99, in wrapper
return f(*args, **kwargs)
File "/app/x01/airflow/environments/airflow/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 "/app/x01/airflow/environments/airflow/lib/python3.8/site-packages/airflow/utils/session.py", line 77, in wrapper
return func(*args, session=session, **kwargs)
File "/app/x01/airflow/environments/airflow/lib/python3.8/site-packages/airflow/utils/db.py", line 1446, in upgradedb
command.upgrade(config, revision=to_revision or 'heads')
File "/app/x01/airflow/environments/airflow/lib/python3.8/site-packages/alembic/command.py", line 320, in upgrade
script.run_env()
File "/app/x01/airflow/environments/airflow/lib/python3.8/site-packages/alembic/script/base.py", line 563, in run_env
util.load_python_file(self.dir, "env.py")
File "/app/x01/airflow/environments/airflow/lib/python3.8/site-packages/alembic/util/pyfiles.py", line 92, in load_python_file
module = load_module_py(module_id, path)
File "/app/x01/airflow/environments/airflow/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 783, in exec_module
File "<frozen importlib._bootstrap>", line 219, in _call_with_frames_removed
File "/app/x01/airflow/environments/airflow/lib/python3.8/site-packages/airflow/migrations/env.py", line 107, in <module>
run_migrations_online()
File "/app/x01/airflow/environments/airflow/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 "/app/x01/airflow/environments/airflow/lib/python3.8/site-packages/alembic/runtime/environment.py", line 851, in run_migrations
self.get_context().run_migrations(**kw)
File "/app/x01/airflow/environments/airflow/lib/python3.8/site-packages/alembic/runtime/migration.py", line 620, in run_migrations
step.migration_fn(**kw)
File "/app/x01/airflow/environments/airflow/lib/python3.8/site-packages/airflow/migrations/versions/0073_2_0_0_prefix_dag_permissions.py", line 231, in upgrade
migrate_to_new_dag_permissions(db)
File "/app/x01/airflow/environments/airflow/lib/python3.8/site-packages/airflow/migrations/versions/0073_2_0_0_prefix_dag_permissions.py", line 161, in migrate_to_new_dag_permissions
can_read_action = get_or_create_action(db.session, 'can_read')
File "/app/x01/airflow/environments/airflow/lib/python3.8/site-packages/airflow/migrations/versions/0073_2_0_0_prefix_dag_permissions.py", line 109, in get_or_create_action
session.commit()
File "<string>", line 2, in commit
File "/app/x01/airflow/environments/airflow/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 1423, in commit
self._transaction.commit(_to_root=self.future)
File "/app/x01/airflow/environments/airflow/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 829, in commit
self._prepare_impl()
File "/app/x01/airflow/environments/airflow/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 808, in _prepare_impl
self.session.flush()
File "/app/x01/airflow/environments/airflow/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 3255, in flush
self._flush(objects)
File "/app/x01/airflow/environments/airflow/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 3395, in _flush
transaction.rollback(_capture_exception=True)
File "/app/x01/airflow/environments/airflow/lib/python3.8/site-packages/sqlalchemy/util/langhelpers.py", line 70, in __exit__
compat.raise_(
File "/app/x01/airflow/environments/airflow/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 211, in raise_
raise exception
File "/app/x01/airflow/environments/airflow/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 3355, in _flush
flush_context.execute()
File "/app/x01/airflow/environments/airflow/lib/python3.8/site-packages/sqlalchemy/orm/unitofwork.py", line 453, in execute
rec.execute(self)
File "/app/x01/airflow/environments/airflow/lib/python3.8/site-packages/sqlalchemy/orm/unitofwork.py", line 627, in execute
util.preloaded.orm_persistence.save_obj(
File "/app/x01/airflow/environments/airflow/lib/python3.8/site-packages/sqlalchemy/orm/persistence.py", line 242, in save_obj
_emit_insert_statements(
File "/app/x01/airflow/environments/airflow/lib/python3.8/site-packages/sqlalchemy/orm/persistence.py", line 1219, in _emit_insert_statements
result = connection._execute_20(
File "/app/x01/airflow/environments/airflow/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1520, in _execute_20
return meth(self, args_10style, kwargs_10style, execution_options)
File "/app/x01/airflow/environments/airflow/lib/python3.8/site-packages/sqlalchemy/sql/elements.py", line 313, in _execute_on_connection
return connection._execute_clauseelement(
File "/app/x01/airflow/environments/airflow/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1389, in _execute_clauseelement
ret = self._execute_context(
File "/app/x01/airflow/environments/airflow/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1614, in _execute_context
self._handle_dbapi_exception(
File "/app/x01/airflow/environments/airflow/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1929, in _handle_dbapi_exception
util.raise_(
File "/app/x01/airflow/environments/airflow/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 211, in raise_
raise exception
File "/app/x01/airflow/environments/airflow/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1608, in _execute_context
context = constructor(
File "/app/x01/airflow/environments/airflow/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 998, in _init_compiled
self._process_executesingle_defaults()
File "/app/x01/airflow/environments/airflow/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 1866, in _process_executesingle_defaults
val = self.get_insert_default(c)
File "/app/x01/airflow/environments/airflow/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 "/app/x01/airflow/environments/airflow/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 1672, in _exec_default
return self.fire_sequence(default, type_)
File "/app/x01/airflow/environments/airflow/lib/python3.8/site-packages/sqlalchemy/dialects/mysql/base.py", line 1401, in fire_sequence
return self._execute_scalar(
File "/app/x01/airflow/environments/airflow/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 1269, in _execute_scalar
conn._cursor_execute(self.cursor, stmt, parameters, context=self)
File "/app/x01/airflow/environments/airflow/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1785, in _cursor_execute
self._handle_dbapi_exception(
File "/app/x01/airflow/environments/airflow/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1929, in _handle_dbapi_exception
util.raise_(
File "/app/x01/airflow/environments/airflow/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 211, in raise_
raise exception
File "/app/x01/airflow/environments/airflow/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1783, in _cursor_execute
self.dialect.do_execute(cursor, statement, parameters, context)
File "/app/x01/airflow/environments/airflow/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 716, in do_execute
cursor.execute(statement, parameters)
File "/app/x01/airflow/environments/airflow/lib/python3.8/site-packages/MySQLdb/cursors.py", line 206, in execute
res = self._query(query)
File "/app/x01/airflow/environments/airflow/lib/python3.8/site-packages/MySQLdb/cursors.py", line 319, in _query
db.query(q)
File "/app/x01/airflow/environments/airflow/lib/python3.8/site-packages/MySQLdb/connections.py", line 254, in query
_mysql.connection.query(self, query)
**sqlalchemy.exc.StatementError: (sqlalchemy.exc.ProgrammingError) (MySQLdb._exceptions.ProgrammingError) (1146, "Table 'airflow.ab_permission_id_seq' doesn't exist")**
It seems that the upgrade looks for a table that is not present and not created by the upgrade itself. The same error is returned when the command "airflow db init" is executed on a clean environment.
### What you think should happen instead
The db upgrade should create first the missing table and any other related (and missing) object.
### How to reproduce
Just running the command using a MariaDB (or Mysql maybe) database.
### Operating System
CentOS Linux release 7.9.2009
### Versions of Apache Airflow Providers
apache-airflow-providers-amazon==3.4.0
apache-airflow-providers-celery==2.1.4
apache-airflow-providers-elasticsearch==3.0.3
apache-airflow-providers-ftp==2.1.2
apache-airflow-providers-http==2.1.2
apache-airflow-providers-imap==2.2.3
apache-airflow-providers-jdbc==2.1.3
apache-airflow-providers-jenkins==2.1.0
apache-airflow-providers-jira==2.0.5
apache-airflow-providers-mysql==2.2.3
apache-airflow-providers-odbc==2.0.4
apache-airflow-providers-oracle==2.2.3
apache-airflow-providers-postgres==4.1.0
apache-airflow-providers-redis==2.0.4
apache-airflow-providers-samba==3.0.4
apache-airflow-providers-sftp==2.6.0
apache-airflow-providers-sqlite==2.1.3
apache-airflow-providers-ssh==2.4.4
### Deployment
Virtualenv installation
### Deployment details
Virtualenv based on Python 3.8.7
### Anything else
_No response_
### Are you willing to submit PR?
- [ ] Yes I am willing to submit a PR!
### Code of Conduct
- [X] I agree to follow this project's [Code of Conduct](https://github.com/apache/airflow/blob/main/CODE_OF_CONDUCT.md)
--
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.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] potiuk commented on issue #24247: DB init and upgrade for MariaDB does not work on Airflow 2.3
Posted by GitBox <gi...@apache.org>.
potiuk commented on issue #24247:
URL: https://github.com/apache/airflow/issues/24247#issuecomment-1159816055
The 2022 survey (soon will be published) shows that we are far below 0.5% with MariaDB. Certainly it is FAR BELOW 90% claimed by @AlekseiSaff . Should we update our docs and make it clear that MariaDB is not supported and we are not going to support it in the future? I think that is the only reasonable thing to do, otherwise we will loose time on people who run MariaDB despite our clear statement that it is not supported.
I think we have really no time as maintainers to look and fix those and being very direct and explicit and closing all issues with MariaDB is far better and very straightforward approach @kaxil @jedcunningham @ephraimbuddy @ashb - WDYT?
I am happy to revive the MariaDB thread and basically announce "MariaDB" as not supported and add all doc updates to direct all MariaDB users to switch to MySQL.
Anyone has any objections there? If not - I will raise a discussion in couple of days.
--
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.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] AlekseiSaff commented on issue #24247: DB init and upgrade for MariaDB does not work on Airflow 2.3
Posted by GitBox <gi...@apache.org>.
AlekseiSaff commented on issue #24247:
URL: https://github.com/apache/airflow/issues/24247#issuecomment-1159889436
I'm happy to switch to MySQL and have fully supported solution, but seems with a bug somewhere we have a sequence in MariaDB instead of autoinc fields. And it makes it impossible to switch to MySQL.
--
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.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] potiuk commented on issue #24247: DB init and upgrade for MariaDB does not work on Airflow 2.3
Posted by GitBox <gi...@apache.org>.
potiuk commented on issue #24247:
URL: https://github.com/apache/airflow/issues/24247#issuecomment-1147383055
I tihnk the important thing is WHY the missing index is there. I think it would be great to find out why the indexes are missing in your 2.2.5 schema on MariaDB.
Is it possible to run initialization on MariaDB and see if the indexes are missing there too?
It is in a way good that we expect that indexes are there to be dropped. They are supposed to be there. I think this cannot be solved by just checking for missing indexes. If there are other problems just checking for stuff before dropping them is not enough. I think we need to get to the bottom of "why the schema in 2.2.5 you have is different than what Airflow migration scripts expect @Mottimo . Maybe the fact that MariaDB is used caused it, or maybe some earlier migration scripts were brokent in-the-middle and not completed or re-run?
--
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.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] AlekseiSaff commented on issue #24247: DB init and upgrade for MariaDB does not work on Airflow 2.3
Posted by GitBox <gi...@apache.org>.
AlekseiSaff commented on issue #24247:
URL: https://github.com/apache/airflow/issues/24247#issuecomment-1147516986
here is the squences in my DB (2.31 and 2.32) that you can create manually
DROP SEQUENCE IF EXISTS `ab_permission_id_seq`;
CREATE SEQUENCE `ab_permission_id_seq` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=InnoDB;
SELECT SETVAL(`ab_permission_id_seq`, 1001, 0);
--
-- Sequence structure for `ab_permission_view_id_seq`
--
DROP SEQUENCE IF EXISTS `ab_permission_view_id_seq`;
CREATE SEQUENCE `ab_permission_view_id_seq` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=InnoDB;
SELECT SETVAL(`ab_permission_view_id_seq`, 6001, 0);
--
-- Sequence structure for `ab_permission_view_menu_id_seq`
--
DROP SEQUENCE IF EXISTS `ab_permission_view_menu_id_seq`;
CREATE SEQUENCE `ab_permission_view_menu_id_seq` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=InnoDB;
SELECT SETVAL(`ab_permission_view_menu_id_seq`, 1, 0);
--
-- Sequence structure for `ab_permission_view_role_id_seq`
--
DROP SEQUENCE IF EXISTS `ab_permission_view_role_id_seq`;
CREATE SEQUENCE `ab_permission_view_role_id_seq` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=InnoDB;
SELECT SETVAL(`ab_permission_view_role_id_seq`, 6001, 0);
--
-- Sequence structure for `ab_view_menu_id_seq`
--
DROP SEQUENCE IF EXISTS `ab_view_menu_id_seq`;
CREATE SEQUENCE `ab_view_menu_id_seq` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=InnoDB;
SELECT SETVAL(`ab_view_menu_id_seq`, 10001, 0);
--
-- Sequence structure for `task_id_sequence`
--
DROP SEQUENCE IF EXISTS `task_id_sequence`;
CREATE SEQUENCE `task_id_sequence` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=InnoDB;
SELECT SETVAL(`task_id_sequence`, 49001, 0);
--
-- Sequence structure for `taskset_id_sequence`
--
DROP SEQUENCE IF EXISTS `taskset_id_sequence`;
CREATE SEQUENCE `taskset_id_sequence` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=InnoDB;
SELECT SETVAL(`taskset_id_sequence`, 1, 0);
--
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.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] ephraimbuddy commented on issue #24247: DB init and upgrade for MariaDB does not work on Airflow 2.3
Posted by GitBox <gi...@apache.org>.
ephraimbuddy commented on issue #24247:
URL: https://github.com/apache/airflow/issues/24247#issuecomment-1147625469
> Can anyone test if `return Sequence(sequence_name)` actually works with MariaDB?
>
> * Airflow 2.3.1 (SQLAlchemy== 1.4.9)
> * MariaDB 10.3
>
> https://github.com/apache/airflow/blob/2.3.1/airflow/www/fab_security/sqla/models.py#L56-L83 https://github.com/apache/airflow/blob/2.3.1/airflow/migrations/versions/0073_2_0_0_prefix_dag_permissions.py
Sequence, was used in model but can't find where it was used in migrations. I might be missing something. Any Idea? How did you create your db in the first place?
--
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.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] potiuk commented on issue #24247: DB init and upgrade for MariaDB does not work on Airflow 2.3
Posted by GitBox <gi...@apache.org>.
potiuk commented on issue #24247:
URL: https://github.com/apache/airflow/issues/24247#issuecomment-1160146637
And @AlekseiSaff - trully sorry for that - but if we are talking about 0.5% of our users, who did not listen to our advice, I thin it is simply FAIR approach that we live you with that problem on your own. Clearly those who did not listen to our advice are in a trouble - precisely because they did not listen to it.
There is a balance between being helpful and asseritive and I think this is just this. I am really, truly sad we can't help you but when we develop a product, we have to focus on things that matter rather than being distracted by wrong choices made by very small fraction of our users. Maybe that will be a good learning for the future.
--
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.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] ephraimbuddy commented on issue #24247: DB init and upgrade for MariaDB does not work on Airflow 2.3
Posted by GitBox <gi...@apache.org>.
ephraimbuddy commented on issue #24247:
URL: https://github.com/apache/airflow/issues/24247#issuecomment-1147687019
> @ephraimbuddy, using your patch we got the same error for the missing foreign key task_reschedule_ti_fkey
The patch was to get through the sequence. Should have explained
--
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.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] Mottimo commented on issue #24247: DB init and upgrade for MariaDB does not work on Airflow 2.3
Posted by GitBox <gi...@apache.org>.
Mottimo commented on issue #24247:
URL: https://github.com/apache/airflow/issues/24247#issuecomment-1147342618
First test: initialization successfully done on MySQL 5.7.36 (Macports ) using Airflow 2.3.1, starting from an empty schema.
Second test: dump of the current schema used by Airflow 2.2.5, import on MySQL without errors then upgrade. The result is a failure with the following error (already seen upgrading from 2.2.5 to 2.3.0):
`(airflow) [airflow@rabbit airflow]$ airflow db upgrade
DB: mysql+mysqldb://airflow:***@localdb:3306/airflow
Performing upgrade with database mysql+mysqldb://airflow:***@localdb:3306/airflow
[2022-06-06 13:05:00,689] {db.py:1445} 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 f9da662e7089 -> e655c0453f75, Add ``map_index`` column to TaskInstance to identify task-mapping,
and a ``task_map`` table to track mapping values from XCom.
Traceback (most recent call last):
File "/app/x01/airflow/environments/airflow/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1705, in _execute_context
self.dialect.do_execute(
File "/app/x01/airflow/environments/airflow/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 716, in do_execute
cursor.execute(statement, parameters)
File "/app/x01/airflow/environments/airflow/lib/python3.8/site-packages/MySQLdb/cursors.py", line 206, in execute
res = self._query(query)
File "/app/x01/airflow/environments/airflow/lib/python3.8/site-packages/MySQLdb/cursors.py", line 319, in _query
db.query(q)
File "/app/x01/airflow/environments/airflow/lib/python3.8/site-packages/MySQLdb/connections.py", line 254, in query
_mysql.connection.query(self, query)
MySQLdb._exceptions.OperationalError: (1091, "Can't DROP 'task_reschedule_ti_fkey'; check that column/key exists")
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File "/app/x01/airflow/environments/airflow/bin/airflow", line 8, in <module>
sys.exit(main())
File "/app/x01/airflow/environments/airflow/lib/python3.8/site-packages/airflow/__main__.py", line 38, in main
args.func(args)
File "/app/x01/airflow/environments/airflow/lib/python3.8/site-packages/airflow/cli/cli_parser.py", line 51, in command
return func(*args, **kwargs)
File "/app/x01/airflow/environments/airflow/lib/python3.8/site-packages/airflow/utils/cli.py", line 99, in wrapper
return f(*args, **kwargs)
File "/app/x01/airflow/environments/airflow/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 "/app/x01/airflow/environments/airflow/lib/python3.8/site-packages/airflow/utils/session.py", line 77, in wrapper
return func(*args, session=session, **kwargs)
File "/app/x01/airflow/environments/airflow/lib/python3.8/site-packages/airflow/utils/db.py", line 1446, in upgradedb
command.upgrade(config, revision=to_revision or 'heads')
File "/app/x01/airflow/environments/airflow/lib/python3.8/site-packages/alembic/command.py", line 320, in upgrade
script.run_env()
File "/app/x01/airflow/environments/airflow/lib/python3.8/site-packages/alembic/script/base.py", line 563, in run_env
util.load_python_file(self.dir, "env.py")
File "/app/x01/airflow/environments/airflow/lib/python3.8/site-packages/alembic/util/pyfiles.py", line 92, in load_python_file
module = load_module_py(module_id, path)
File "/app/x01/airflow/environments/airflow/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 783, in exec_module
File "<frozen importlib._bootstrap>", line 219, in _call_with_frames_removed
File "/app/x01/airflow/environments/airflow/lib/python3.8/site-packages/airflow/migrations/env.py", line 107, in <module>
run_migrations_online()
File "/app/x01/airflow/environments/airflow/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 "/app/x01/airflow/environments/airflow/lib/python3.8/site-packages/alembic/runtime/environment.py", line 851, in run_migrations
self.get_context().run_migrations(**kw)
File "/app/x01/airflow/environments/airflow/lib/python3.8/site-packages/alembic/runtime/migration.py", line 620, in run_migrations
step.migration_fn(**kw)
File "/app/x01/airflow/environments/airflow/lib/python3.8/site-packages/airflow/migrations/versions/0100_2_3_0_add_taskmap_and_map_id_on_taskinstance.py", line 49, in upgrade
batch_op.drop_index("idx_task_reschedule_dag_task_run")
File "/opt/python-bit/lib/python3.8/contextlib.py", line 120, in __exit__
next(self.gen)
File "/app/x01/airflow/environments/airflow/lib/python3.8/site-packages/alembic/operations/base.py", line 374, in batch_alter_table
impl.flush()
File "/app/x01/airflow/environments/airflow/lib/python3.8/site-packages/alembic/operations/batch.py", line 108, in flush
fn(*arg, **kw)
File "/app/x01/airflow/environments/airflow/lib/python3.8/site-packages/alembic/ddl/mysql.py", line 153, in drop_constraint
super(MySQLImpl, self).drop_constraint(const)
File "/app/x01/airflow/environments/airflow/lib/python3.8/site-packages/alembic/ddl/impl.py", line 336, in drop_constraint
self._exec(schema.DropConstraint(const))
File "/app/x01/airflow/environments/airflow/lib/python3.8/site-packages/alembic/ddl/impl.py", line 193, in _exec
return conn.execute(construct, multiparams)
File "/app/x01/airflow/environments/airflow/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1200, in execute
return meth(self, multiparams, params, _EMPTY_EXECUTION_OPTS)
File "/app/x01/airflow/environments/airflow/lib/python3.8/site-packages/sqlalchemy/sql/ddl.py", line 77, in _execute_on_connection
return connection._execute_ddl(
File "/app/x01/airflow/environments/airflow/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1290, in _execute_ddl
ret = self._execute_context(
File "/app/x01/airflow/environments/airflow/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1748, in _execute_context
self._handle_dbapi_exception(
File "/app/x01/airflow/environments/airflow/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1929, in _handle_dbapi_exception
util.raise_(
File "/app/x01/airflow/environments/airflow/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 211, in raise_
raise exception
File "/app/x01/airflow/environments/airflow/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1705, in _execute_context
self.dialect.do_execute(
File "/app/x01/airflow/environments/airflow/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 716, in do_execute
cursor.execute(statement, parameters)
File "/app/x01/airflow/environments/airflow/lib/python3.8/site-packages/MySQLdb/cursors.py", line 206, in execute
res = self._query(query)
File "/app/x01/airflow/environments/airflow/lib/python3.8/site-packages/MySQLdb/cursors.py", line 319, in _query
db.query(q)
File "/app/x01/airflow/environments/airflow/lib/python3.8/site-packages/MySQLdb/connections.py", line 254, in query
_mysql.connection.query(self, query)
sqlalchemy.exc.OperationalError: (MySQLdb._exceptions.OperationalError) (1091, "Can't DROP 'task_reschedule_ti_fkey'; check that column/key exists")
[SQL: ALTER TABLE task_reschedule DROP FOREIGN KEY task_reschedule_ti_fkey]
(Background on this error at: http://sqlalche.me/e/14/e3q8)
`
In my view it's not a matter of support of MariaDB, given that the schema is exactly the same, and not a matter of DDL but a matter of the code used for the upgrade. The migration scripts seem to not completely cover all possible scenarios, previously the missing table and now the missing index. In facts, this second time the code is doing:
_Excerpt from 0100_2_3_0_add_taskmap_and_map_id_on_taskinstance.py)_
`def upgrade():
42 """
43 Add ``map_index`` column to TaskInstance to identify task-mapping,
44 and a ``task_map`` table to track mapping values from XCom.
45 """
46 # We need to first remove constraints on task_reschedule since they depend on task_instance.
47 with op.batch_alter_table("task_reschedule") as batch_op:
48 batch_op.drop_constraint("task_reschedule_ti_fkey", "foreignkey")
49 batch_op.drop_index("idx_task_reschedule_dag_task_run")`
Then, it tries to drop a foreign key without checking if that key is in place or not. Fixing that foreign key another similar error is fired.
--
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.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] Mottimo commented on issue #24247: DB init and upgrade for MariaDB does not work on Airflow 2.3
Posted by GitBox <gi...@apache.org>.
Mottimo commented on issue #24247:
URL: https://github.com/apache/airflow/issues/24247#issuecomment-1150051803
Hi, I run another test on a fresh environment. These the steps using MariaDB:
1. New env prepared starting from Airflow 2.2.5, DB initialized and never used;
2. Dump of the DB and update of the COLLATE to utf8mb3_general_ci;
3. Airflow 2.3.1 updated using @ephraimbuddy's patch for sequences;
4. `airflow db upgrade` successfully executed without any further intervention. Service was not started;
5. upgrade to Airflow 2.3.2, vanilla code;
6. `airflow db upgrade` successfully executed;
7. Service started without issues;
8. The creation of a new admin user has fired the following error:
`[2022-06-08 16:58:09,460] {manager.py:351} ERROR - Add Permission: (sqlalchemy.exc.ProgrammingError) (MySQLdb._exceptions.ProgrammingError) (1146, "Table 'airflow.ab_permission_id_seq' doesn't exist")`
It seems that the issue with sequences still be there, then...;
9. ... applying the SQL patch from @AlekseiSaff has fixed the issue.
The service is now up & running with 2.3.2 using a set of testing DAGs to validate. Basing on the above, the only patch for models.py does not fix all the sequences issue.
--
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.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] Mottimo commented on issue #24247: DB init and upgrade for MariaDB does not work on Airflow 2.3
Posted by GitBox <gi...@apache.org>.
Mottimo commented on issue #24247:
URL: https://github.com/apache/airflow/issues/24247#issuecomment-1147391782
Understood, I agree with @potiuk that if an index or a key should be there it's useless to check. Maybe also that a previous init or upgrade has broken something - in that case: why are we not seeing it in production? We are running a quite big installation on multiple environments.
Give me some time to run the init test on MariaDB with 2.2.5 and let you know.
--
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.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] bmoon4 commented on issue #24247: DB init and upgrade for MariaDB does not work on Airflow 2.3
Posted by GitBox <gi...@apache.org>.
bmoon4 commented on issue #24247:
URL: https://github.com/apache/airflow/issues/24247#issuecomment-1147581509
I had a same issue when upgrading from 2.2.4 to 2.3.1 with MariaDB. Had to manually create sequences before schedulers `airflow db upgrade` command (like what @AlekseiSaff did). I think this is more like SQLAlchemy `1.4.x` problem. I suspect It is not firing `CREATE SEQUENCE` properly with MariaDB dialect.
--
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.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] AlekseiSaff commented on issue #24247: DB init and upgrade for MariaDB does not work on Airflow 2.3
Posted by GitBox <gi...@apache.org>.
AlekseiSaff commented on issue #24247:
URL: https://github.com/apache/airflow/issues/24247#issuecomment-1147684248
that is due to collation issue. my way to fix this
My way of upgrading 2.2.5 to 2.3.0:
create text DB dump
change all "utf8mb3_unicode_ci" to "utf8mb3_bin" in the backup dump
drop DB and recreate from modified backup
one more issue with step:
UPDATE rendered_task_instance_fields, dag_run SET rendered_task_instance_fields.run_id=dag_run.run_id WHERE dag_run.dag_id = rendered_task_instance_fields.dag_id AND dag_run.execution_date = rendered_task_instance_fields.execution_date;
to fix this on the newly created DB run:
alter table rendered_task_instance_fields change execution_date execution_date timestamp NOT NULL default CURRENT_TIMESTAMP;
--
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.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] potiuk commented on issue #24247: DB init and upgrade for MariaDB does not work on Airflow 2.3
Posted by GitBox <gi...@apache.org>.
potiuk commented on issue #24247:
URL: https://github.com/apache/airflow/issues/24247#issuecomment-1147256075
That should at least fix some class of errors (I guess @Mottimo the problem was that you stopped migration in between initially when you run it ? )
--
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.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] potiuk commented on issue #24247: DB init and upgrade for MariaDB does not work on Airflow 2.3
Posted by GitBox <gi...@apache.org>.
potiuk commented on issue #24247:
URL: https://github.com/apache/airflow/issues/24247#issuecomment-1147515636
> we might need to have a solution to move MySQL to Postgres and close support of both MariaDB and MySQL DB
I quite agree with supporting MariaDB but my proposal of "oifficielly" supporting it (i.e. running tests in CI has not been well received so far, hence I am raising it here (and likely re-raise soon in the devlist) . We need to have devlist agreement on it and voting that pass. So if you are all for it - I will post some discsusion to the devlist and you would be welcome to state your voice there. In this case "if it did not happen in the devlist, it did not happen" is truly what it is.
--
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.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] potiuk commented on issue #24247: DB init and upgrade for MariaDB does not work on Airflow 2.3
Posted by GitBox <gi...@apache.org>.
potiuk commented on issue #24247:
URL: https://github.com/apache/airflow/issues/24247#issuecomment-1147493711
https://blog.devart.com/mysql-vs-mariadb.html
```
SQL
Both MariaDB and MySQL support standard SQL, but MariaDB has developed and implemented a bit more here. For example, when it comes to database objects, MariaDB supports sequences, which are not available in MySQL.
```
--
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.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] potiuk commented on issue #24247: DB init and upgrade for MariaDB does not work on Airflow 2.3
Posted by GitBox <gi...@apache.org>.
potiuk commented on issue #24247:
URL: https://github.com/apache/airflow/issues/24247#issuecomment-1160141384
I am closing this solution as "won't fix" until we collectively agree that we do something with Maria DB. Until then I am just going to close all the MariaDB issues as "won't fix" as well.
--
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.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] Mottimo commented on issue #24247: DB init and upgrade for MariaDB does not work on Airflow 2.3
Posted by GitBox <gi...@apache.org>.
Mottimo commented on issue #24247:
URL: https://github.com/apache/airflow/issues/24247#issuecomment-1148413639
Finally I got the first upgrade from 2.2.5 to 2.3.1 without errors. The applied steps are:
1. Dump of the 2.2.5 DB and replacement of collate to utf8mb3_general_ci. This change was required to prevent two errors, one during the creation of new table task_map (type of foreign key does not match the referenced table), the other for incoherence between collates utf8mb3_unicode_ci and utf8mb3_general_ci;
2. Update of the schema with the following statements, required because the migration script asks for them during drop operations:
`
--
-- Drop column map_index from task_instance
ALTER TABLE task_instance DROP COLUMN IF EXISTS map_index;
--
-- Drop column map_index from task_instance
ALTER TABLE task_reschedule DROP COLUMN IF EXISTS map_index;
--
-- Create non-unique indexes on task_instance
CREATE INDEX IF NOT EXISTS idx_task_reschedule_dag_task_run USING BTREE ON task_instance (dag_id, task_id, run_id);
--
-- Create non-unique indexes on task_reschedule
CREATE INDEX IF NOT EXISTS idx_task_reschedule_dag_task_run USING BTREE ON task_reschedule (dag_id, task_id, run_id);
--
-- Create foreign key on task_reschedule
ALTER TABLE task_reschedule ADD CONSTRAINT task_reschedule_ti_fkey FOREIGN KEY (dag_id, task_id, run_id) REFERENCES task_instance (dag_id, task_id, run_id);
--
-- Create primary key on task_instance
ALTER TABLE task_instance ADD PRIMARY KEY (dag_id, task_id, run_id);
`
No change was applied to the DB for the sequences, just the patch provided by @ephraimbuddy was used.
After these steps the upgrade was successfully completed, I don't know yet if it really works with the application.
--
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.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] potiuk commented on issue #24247: DB init and upgrade for MariaDB does not work on Airflow 2.3
Posted by GitBox <gi...@apache.org>.
potiuk commented on issue #24247:
URL: https://github.com/apache/airflow/issues/24247#issuecomment-1147254902
> MariaDB is not officially supported yet. For fresh installs, this PR #24156, which will create the DB without going through the migration files will fix it for `airflow db init`.
Q: I think #24156 will only fix "fresh" installs. I wonder - is this a MariaDB-only error @ephraimbuddy ? If so, we can close it ? But maybe it can also happen for MySQL migrations? This is a "migration" from 2.2.5, so I'd say if this is is likely to happen when migrating - maybe we should simply ignore errors about not-exisitng objects like that in the migrations?
--
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.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] AlekseiSaff commented on issue #24247: DB init and upgrade for MariaDB does not work on Airflow 2.3
Posted by GitBox <gi...@apache.org>.
AlekseiSaff commented on issue #24247:
URL: https://github.com/apache/airflow/issues/24247#issuecomment-1147477153
MariaDB is using sequences that are not supported by MariaDB, but from an SQL point of view, sequences are a kind of a virtual table. On my setup, I just created them manually. On the same upgrade path. 2.2.5 to 2.3
--
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.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] ephraimbuddy commented on issue #24247: DB init and upgrade for MariaDB does not work on Airflow 2.3
Posted by GitBox <gi...@apache.org>.
ephraimbuddy commented on issue #24247:
URL: https://github.com/apache/airflow/issues/24247#issuecomment-1147299275
> > MariaDB is not officially supported yet. For fresh installs, this PR #24156, which will create the DB without going through the migration files will fix it for `airflow db init`.
>
> Q: I think #24156 will only fix "fresh" installs. I wonder - is this a MariaDB-only error @ephraimbuddy ? If so, we can close it ? But maybe it can also happen for MySQL migrations? This is a "migration" from 2.2.5, so I'd say if this is is likely to happen when migrating - maybe we should simply ignore errors about not-exisitng objects being dropped the migrations?
We currently run upgrades for MySQL in the CI, so I think it's only Mariadb that it would affect
--
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.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] potiuk commented on issue #24247: DB init and upgrade for MariaDB does not work on Airflow 2.3
Posted by GitBox <gi...@apache.org>.
potiuk commented on issue #24247:
URL: https://github.com/apache/airflow/issues/24247#issuecomment-1147402688
Yeah. If you run muiltiple envs it would be great to see if it's a recurring problem or once-off!
--
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.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] ephraimbuddy commented on issue #24247: DB init and upgrade for MariaDB does not work on Airflow 2.3
Posted by GitBox <gi...@apache.org>.
ephraimbuddy commented on issue #24247:
URL: https://github.com/apache/airflow/issues/24247#issuecomment-1147243906
MariaDB is not officially supported yet. For fresh installs, this PR https://github.com/apache/airflow/pull/24156, which will create the DB without going through the migration files will fix it for `airflow db init`.
--
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.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] AlekseiSaff commented on issue #24247: DB init and upgrade for MariaDB does not work on Airflow 2.3
Posted by GitBox <gi...@apache.org>.
AlekseiSaff commented on issue #24247:
URL: https://github.com/apache/airflow/issues/24247#issuecomment-1147648047
My base was created in airflow 1.x than it was normal airflow db upgrade steps
--
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.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] Mottimo commented on issue #24247: DB init and upgrade for MariaDB does not work on Airflow 2.3
Posted by GitBox <gi...@apache.org>.
Mottimo commented on issue #24247:
URL: https://github.com/apache/airflow/issues/24247#issuecomment-1148453808
If can help with your decision, we are using MariaDB with Galera Cluster over three nodes. This architecture is active on multiple environments and it will become - within few months - a reference architecture, mission critical, for a wide group of industries around Europe. We patched a lot Airflow to reach such goal, our work can be shared with you if you like. This is the reason why we would like to keep MariaDB supported by Airflow.
--
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.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] potiuk commented on issue #24247: DB init and upgrade for MariaDB does not work on Airflow 2.3
Posted by GitBox <gi...@apache.org>.
potiuk commented on issue #24247:
URL: https://github.com/apache/airflow/issues/24247#issuecomment-1147501889
Yeah. From our point of view what is important is that it:
1) MySQL vs. MariaDB is different
2) causes migration problems (sequence handling is different)
3) We never supported MariaDB - so if the scripts do not work, it's because user used it against our recommendation
4) Our scripts are only run against MySQL only. We have not done a single test in our CI against MariaDB because, well, we do not support it.
So the question is:
1) should we officially say "MariaDB" is not supported and start immediately closing/converting into discussion any tickets where users use MariaDB
2) should we make an effort to support MariaDB as community
I think proposal of saying "we do best-effort" to support MariaDB is jsut wrong - because by not having CI to check stuff we effectively don't do any efffort to support it (as community) and we already know users who use MariaDB will have problems (and other than advice from other users we cannot offer them any more actually).
--
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.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] Mottimo commented on issue #24247: DB init and upgrade for MariaDB does not work on Airflow 2.3
Posted by GitBox <gi...@apache.org>.
Mottimo commented on issue #24247:
URL: https://github.com/apache/airflow/issues/24247#issuecomment-1147671337
My DB was create with Airflow 2.0, then migrated till now. The suggestion from @AlekseiSaff has fixed the first part of the upgrade, now we got another error:
`sqlalchemy.exc.OperationalError: (MySQLdb._exceptions.OperationalError) (1091, "Can't DROP FOREIGN KEY `task_reschedule_ti_fkey`; check that it exists")`
This foreign key seems that cannot be created as reported into the migration script, that is this statement does not work:
`ALTER TABLE task_reschedule ADD CONSTRAINT task_reschedule_ti_fkey FOREIGN KEY (dag_id, task_id, run_id, map_index) REFERENCES task_instance (dag_id, task_id, run_id, map_index);`
ERROR 1005 (HY000): Can't create table `bee`.`task_reschedule` (errno: 150 "Foreign key constraint is incorrectly formed")
but this one does:
`ALTER TABLE task_reschedule ADD CONSTRAINT task_reschedule_ti_fkey FOREIGN KEY (dag_id, task_id, run_id) REFERENCES task_instance (dag_id, task_id, run_id);`
Query OK, 0 rows affected (0.009 sec)
Records: 0 Duplicates: 0 Warnings: 0
The portion of the script should be this one:
`with op.batch_alter_table("task_reschedule") as batch_op:
batch_op.add_column(Column("map_index", Integer, nullable=False, server_default=text("-1")))
batch_op.create_foreign_key(
"task_reschedule_ti_fkey",
"task_instance",
["dag_id", "task_id", "run_id", "map_index"],
["dag_id", "task_id", "run_id", "map_index"],
ondelete="CASCADE",
)
batch_op.create_index(
"idx_task_reschedule_dag_task_run",
["dag_id", "task_id", "run_id", "map_index"],
unique=False,
)`
Then, something does not work in our schema with field map_index...
--
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.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] AlekseiSaff commented on issue #24247: DB init and upgrade for MariaDB does not work on Airflow 2.3
Posted by GitBox <gi...@apache.org>.
AlekseiSaff commented on issue #24247:
URL: https://github.com/apache/airflow/issues/24247#issuecomment-1147652445
so it's interesting. officially you don't support mariaDB, but someone added functionality specific to MariaDB?
--
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.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] bmoon4 commented on issue #24247: DB init and upgrade for MariaDB does not work on Airflow 2.3
Posted by GitBox <gi...@apache.org>.
bmoon4 commented on issue #24247:
URL: https://github.com/apache/airflow/issues/24247#issuecomment-1147616516
Can anyone test if `return Sequence(sequence_name)` actually works with MariaDB?
- Airflow 2.3.1 (SQLAlchemy== 1.4.9)
- MariaDB 10.3
https://github.com/apache/airflow/blob/2.3.1/airflow/www/fab_security/sqla/models.py#L56-L83
https://github.com/apache/airflow/blob/2.3.1/airflow/migrations/versions/0073_2_0_0_prefix_dag_permissions.py
--
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.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] AlekseiSaff commented on issue #24247: DB init and upgrade for MariaDB does not work on Airflow 2.3
Posted by GitBox <gi...@apache.org>.
AlekseiSaff commented on issue #24247:
URL: https://github.com/apache/airflow/issues/24247#issuecomment-1147792921
you can always check
"SHOW ENGINE INNODB STATUS"
after failure and it will show you where is your problem. usually it's collation (based on my experience)
--
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.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] tonjo commented on issue #24247: DB init and upgrade for MariaDB does not work on Airflow 2.3
Posted by GitBox <gi...@apache.org>.
tonjo commented on issue #24247:
URL: https://github.com/apache/airflow/issues/24247#issuecomment-1147305091
Actually, `airflow db init` also fails with MariaDB, with exactly the same error.
PR #24156 seems to do the trick.
In order to make it work, I checked out branch `compare-type` and merged the PR commit.
--
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.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] ephraimbuddy commented on issue #24247: DB init and upgrade for MariaDB does not work on Airflow 2.3
Posted by GitBox <gi...@apache.org>.
ephraimbuddy commented on issue #24247:
URL: https://github.com/apache/airflow/issues/24247#issuecomment-1147299633
> The upgrade was never interrupted, during this operation or before it. I can double test if the same issue happen also on MySQL and let you know.
Awesome. Please let us know
--
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.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] Mottimo commented on issue #24247: DB init and upgrade for MariaDB does not work on Airflow 2.3
Posted by GitBox <gi...@apache.org>.
Mottimo commented on issue #24247:
URL: https://github.com/apache/airflow/issues/24247#issuecomment-1147671755
@ephraimbuddy I will test your patch and let you know
--
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.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] Mottimo commented on issue #24247: DB init and upgrade for MariaDB does not work on Airflow 2.3
Posted by GitBox <gi...@apache.org>.
Mottimo commented on issue #24247:
URL: https://github.com/apache/airflow/issues/24247#issuecomment-1147276418
The upgrade was never interrupted, during this operation or before it. I can double test if the same issue happen also on MySQL and let you know.
--
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.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] potiuk commented on issue #24247: DB init and upgrade for MariaDB does not work on Airflow 2.3
Posted by GitBox <gi...@apache.org>.
potiuk commented on issue #24247:
URL: https://github.com/apache/airflow/issues/24247#issuecomment-1147384459
Maybe there is some specific setting in your MariaDB that leads to that behaviour for example - we do not support MariaDB and we do not test our scripts nor have "recommended settings" there - so this might very well be some of the settings that is specific for your case.
--
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.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] Mottimo commented on issue #24247: DB init and upgrade for MariaDB does not work on Airflow 2.3
Posted by GitBox <gi...@apache.org>.
Mottimo commented on issue #24247:
URL: https://github.com/apache/airflow/issues/24247#issuecomment-1147509478
@potiuk, re-initalizing the DB using Airflow 2.2.5 the table airflow.ab_permission_id_seq is not created:
`MariaDB [airflow]> show tables;
+-------------------------------+
| Tables_in_airflow |
+-------------------------------+
| ab_permission |
| ab_permission_view |
| ab_permission_view_role |
| ab_register_user |
| ab_role |
| ab_user |
| ab_user_role |
| ab_view_menu |
| alembic_version |
| connection |
| dag |
| dag_code |
| dag_pickle |
| dag_run |
| dag_tag |
| import_error |
| job |
| log |
| rendered_task_instance_fields |
| sensor_instance |
| serialized_dag |
| session |
| sla_miss |
| slot_pool |
| task_fail |
| task_instance |
| task_reschedule |
| trigger |
| variable |
| xcom |
+-------------------------------+
30 rows in set (0.003 sec)
MariaDB [airflow]> SELECT VERSION();
+----------------+
| VERSION() |
+----------------+
| 10.7.3-MariaDB |
+----------------+
1 row in set (0.005 sec)`
About the support to MariaDB, we are interested to keep it: do you think the development team I'm member of could provide any help for that?
--
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.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] Mottimo commented on issue #24247: DB init and upgrade for MariaDB does not work on Airflow 2.3
Posted by GitBox <gi...@apache.org>.
Mottimo commented on issue #24247:
URL: https://github.com/apache/airflow/issues/24247#issuecomment-1147783514
Yeah @ephraimbuddy, I tested your patch starting from a schema 2.2.5 without @AlekseiSaff's suggestion about the sequences and it works. The issue still be the foreign key, even using the character set change suggested again by @AlekseiSaff. But, creating a foreign key without map_index the upgrade goes ahead, stopping on another key named idx_task_reschedule_dag_task_run for a similar reason. Tomorrow I will go ahead from here.
--
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.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] Mottimo commented on issue #24247: DB init and upgrade for MariaDB does not work on Airflow 2.3
Posted by GitBox <gi...@apache.org>.
Mottimo commented on issue #24247:
URL: https://github.com/apache/airflow/issues/24247#issuecomment-1160089666
Hi @potiuk, thanks for your update. For the time being it will be an issue for our use case to switch to MySQL, mainly because we will loose the availability of the native support of Galera cluster provided by MariaDB. In effects there is a port of this cluster to MySQL, our tests have shown a less overall quality at least in terms of support and documentation.
We will review your decision soon, I will update you with our comments.
Looking to the result of your survey, it seems strange to me a ratio of 99.5% for MySQL against 0.5% for MariaDB. Comparing your data with what Gartner provides, the ratio should be roughly 80% MySQL vs 20% MariaDB as visible from the number of certified reviews reported here:
https://www.gartner.com/reviews/market/cloud-database-management-systems/compare/product/mariadb-platform-vs-oracle-mysql-database-service
--
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.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] potiuk commented on issue #24247: DB init and upgrade for MariaDB does not work on Airflow 2.3
Posted by GitBox <gi...@apache.org>.
potiuk commented on issue #24247:
URL: https://github.com/apache/airflow/issues/24247#issuecomment-1147492212
> MariaDB is using sequences that are not supported by MariaDB, but from an SQL point of view, sequences are a kind of a virtual table. On my setup, I just created them manually. On the same upgrade path. 2.2.5 to 2.3
@kaxil : re our discusison about best-effort - seems that Maria DB does have some fundamental differences vs. MySQL (sequence handling). I think we should decide on whether this is still "best-effort" case. I think it's either we bluntly drop all support for MariaDB and say "we do not support it" or we just add it and solve all issues.
There will be more cases like this along the road I am afraid.
--
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.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] ephraimbuddy commented on issue #24247: DB init and upgrade for MariaDB does not work on Airflow 2.3
Posted by GitBox <gi...@apache.org>.
ephraimbuddy commented on issue #24247:
URL: https://github.com/apache/airflow/issues/24247#issuecomment-1147659869
> so it's interesting. officially you don't support mariaDB, but someone added functionality specific to MariaDB?
It's not specific to MariaDB. I would say MariaDB seems not to support sequence. Sequence works in the all the DBs we support. MSSQL uses Identity
--
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.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] potiuk commented on issue #24247: DB init and upgrade for MariaDB does not work on Airflow 2.3
Posted by GitBox <gi...@apache.org>.
potiuk commented on issue #24247:
URL: https://github.com/apache/airflow/issues/24247#issuecomment-1147754576
> so it's interesting. officially you don't support mariaDB, but someone added functionality specific to MariaDB?
BTW. Even if someone did, it wouldd not be surprising. We do not test stuff on MariaDB, we do test on MySQL so if the change which targets MariaDB passed the MySQL, we would merge it. This is was kind of implied by the "best-effort" informal policy - if someone would like to make a fix that would make MariaDB work, they were allowed to merge it (as long as it would not break MySQL) but we would not test it nor made sure it keeps running.
And yes. I am personally not a fan of such policy to be honest, but it is what it is for now.
--
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.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] tonjo commented on issue #24247: DB init and upgrade for MariaDB does not work on Airflow 2.3
Posted by GitBox <gi...@apache.org>.
tonjo commented on issue #24247:
URL: https://github.com/apache/airflow/issues/24247#issuecomment-1148382508
> here is the squences in my DB (2.31 and 2.32) that you can create manually
>
Nice "patch", I think another sequence `ab_role_id_seq` is missing, though:
```
-- Sequence structure for ab_role_id_seq
DROP SEQUENCE IF EXISTS ab_role_id_seq;
CREATE SEQUENCE ab_role_id_seq start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE = InnoDB;
SELECT SETVAL(ab_role_id_seq, 1001, 0);
```
--
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.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] potiuk commented on issue #24247: DB init and upgrade for MariaDB does not work on Airflow 2.3
Posted by GitBox <gi...@apache.org>.
potiuk commented on issue #24247:
URL: https://github.com/apache/airflow/issues/24247#issuecomment-1148436936
FYI. I actually added MariaDB as option to our 2022 survey (closed yesterday) and hopefully we will learn how many of our users are actually using MariaDB (at least we will have some rough numbers). I do think that at least the migration steps (but likely whole suite of tests) should run for MariaDB and we should start "supporting" it officiall - but that's my personal opinion, as I have completely no idea what is the actually MariaDB usage.
I don't think the (exaggerated of course by @AlekseiSaff ) holds true:
> 90% when IT is hearing mysqldb they will give you mariadb :(
But I hope with the survey we will have at least some indication how much it is used in our community even if we do not recommend it, and it might give us better data to make decision as a community what to do it with it.
--
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.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] potiuk commented on issue #24247: DB init and upgrade for MariaDB does not work on Airflow 2.3
Posted by GitBox <gi...@apache.org>.
potiuk commented on issue #24247:
URL: https://github.com/apache/airflow/issues/24247#issuecomment-1148467853
I will post a thread to devlist here when I start it - I think speaking and describing your case at the devlist might add it a weight - so I invite you to chime in when we start discussing it @Mottimo .
--
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.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] potiuk commented on issue #24247: DB init and upgrade for MariaDB does not work on Airflow 2.3
Posted by GitBox <gi...@apache.org>.
potiuk commented on issue #24247:
URL: https://github.com/apache/airflow/issues/24247#issuecomment-1160136278
> Looking to the result of your survey, it seems strange to me a ratio of 99.5% for MySQL against 0.5% for MariaDB. Comparing your data with what Gartner provides, the ratio should be roughly 80% MySQL vs 20% MariaDB as visible from the number of certified reviews reported here:
You seem to forget @Mottimo - that MariaDB is NOT SUPPORTED officially by Airflow. So if people (like @AlekseiSaff ) are REALLY adventurous, they will choose MariaDB even if we do not support it. Then it does not give those people the right to EXPECT things works - because we explicitly said they are on their own. Maybe simply 95% percent of those 20% went the reasonable path and chose the supported solution? I don't know. I know that if I had been told that my choice is not supported I'd think 10 times, and accept the consequences that if it breaks I am on my own if I decide to choose it - rather than expect that someone will fix it (and likely would not even choose MariaDB if I knew it's that risky).
--
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.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] potiuk commented on issue #24247: DB init and upgrade for MariaDB does not work on Airflow 2.3
Posted by GitBox <gi...@apache.org>.
potiuk commented on issue #24247:
URL: https://github.com/apache/airflow/issues/24247#issuecomment-1160138138
> I'm happy to switch to MySQL and have fully supported solution, but seems with a bug somewhere we have a sequence in MariaDB instead of autoinc fields. And it makes it impossible to switch to MySQL.
I recommend to delete all the data then and switch to Postgres. It's far more stable than MySQL. You can also try to migrate your data manually. But well, You chose MariaDB, despite it being not supported, and i am afraid, that you have to live with the consequences. This is just reasonable to assume that you have to deal with it somehow.
--
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.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] potiuk closed issue #24247: DB init and upgrade for MariaDB does not work on Airflow 2.3
Posted by GitBox <gi...@apache.org>.
potiuk closed issue #24247: DB init and upgrade for MariaDB does not work on Airflow 2.3
URL: https://github.com/apache/airflow/issues/24247
--
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.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] potiuk commented on issue #24247: DB init and upgrade for MariaDB does not work on Airflow 2.3
Posted by GitBox <gi...@apache.org>.
potiuk commented on issue #24247:
URL: https://github.com/apache/airflow/issues/24247#issuecomment-1185226360
We are not going to fix it. Maria DB is not officially supported and will break. See:
https://airflow.apache.org/docs/apache-airflow/stable/installation/prerequisites.html?highlight=mariadb
> Despite big similarities between MariaDB and MySQL, we DO NOT support MariaDB as a backend for Airflow. There are known problems (for example index handling) between MariaDB and MySQL and we do not test our migration scripts nor application execution on Maria DB. We know there were people who used MariaDB for Airflow and that cause a lot of operational headache for them so we strongly discourage attempts of using MariaDB as a backend and users cannot expect any community support for it because the number of users who tried to use MariaDB for Airflow is very small.
--
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.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] liuning89757 commented on issue #24247: DB init and upgrade for MariaDB does not work on Airflow 2.3
Posted by GitBox <gi...@apache.org>.
liuning89757 commented on issue #24247:
URL: https://github.com/apache/airflow/issues/24247#issuecomment-1185105663
> > Can anyone test if `return Sequence(sequence_name)` actually works with MariaDB?
> >
> > * Airflow 2.3.1 (SQLAlchemy== 1.4.9)
> > * MariaDB 10.3
> >
> > https://github.com/apache/airflow/blob/2.3.1/airflow/www/fab_security/sqla/models.py#L56-L83 https://github.com/apache/airflow/blob/2.3.1/airflow/migrations/versions/0073_2_0_0_prefix_dag_permissions.py
>
> Sequence, was used in model but can't find where it was used in migrations. I might be missing something. Any Idea? How did you create your db in the first place?
@ephraimbuddy I think the migration file might be https://github.com/apache/airflow/blob/2.3.1/airflow/migrations/versions/0057_1_10_13_add_fab_tables.py . In this migration alembic create table with out explicit create sequence. And according to https://stackoverflow.com/questions/17196234/how-to-create-postgresqls-sequences-in-alembic#comment115841207_17196812 , the sequence in MariaDB need to be create manually
--
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.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] AlekseiSaff commented on issue #24247: DB init and upgrade for MariaDB does not work on Airflow 2.3
Posted by GitBox <gi...@apache.org>.
AlekseiSaff commented on issue #24247:
URL: https://github.com/apache/airflow/issues/24247#issuecomment-1147511204
we might need to have a solution to move MySQL to Postgres and close support of both MariaDB and MySQL DB
in the modern world it's like 90% when IT is hearing mysqldb they will give you mariadb :(
--
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.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] ephraimbuddy commented on issue #24247: DB init and upgrade for MariaDB does not work on Airflow 2.3
Posted by GitBox <gi...@apache.org>.
ephraimbuddy commented on issue #24247:
URL: https://github.com/apache/airflow/issues/24247#issuecomment-1148220492
> Yeah @ephraimbuddy, I tested your patch starting from a schema 2.2.5 without @AlekseiSaff's suggestion about the sequences and it works. The issue still be the foreign key, even using the character set change suggested again by @AlekseiSaff. But, creating a foreign key without map_index the upgrade goes ahead, stopping on another key named idx_task_reschedule_dag_task_run for a similar reason. Tomorrow I will go ahead from here.
You can create a non-unique index on `task_instance` involving `map_index`, `run_id`, `task_id`, and `dag_id` after applying my patch and the migration stops. Please do this in a test environment.
--
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.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] ephraimbuddy commented on issue #24247: DB init and upgrade for MariaDB does not work on Airflow 2.3
Posted by GitBox <gi...@apache.org>.
ephraimbuddy commented on issue #24247:
URL: https://github.com/apache/airflow/issues/24247#issuecomment-1149527685
> @ephraimbuddy any ideas what to do for those who created sequences manually?
I don't think it's a problem if you have already created it. You can drop it as well but I don't really know how MariaDB would handle that. The purpose of the sequence is to handle the auto-update of the table id field.
--
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.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] ephraimbuddy commented on issue #24247: DB init and upgrade for MariaDB does not work on Airflow 2.3
Posted by GitBox <gi...@apache.org>.
ephraimbuddy commented on issue #24247:
URL: https://github.com/apache/airflow/issues/24247#issuecomment-1147670126
Can someone test with this patch for your upgrade if you're in 2.2.5:
```diff
diff --git a/airflow/www/fab_security/sqla/models.py b/airflow/www/fab_security/sqla/models.py
index c6eb65ead..d6dc18e0c 100644
--- a/airflow/www/fab_security/sqla/models.py
+++ b/airflow/www/fab_security/sqla/models.py
@@ -87,7 +87,7 @@ class Action(Model):
"""Represents permission actions such as `can_read`."""
__tablename__ = "ab_permission"
- id = Column(Integer, get_sequence_or_identity("ab_permission_id_seq"), primary_key=True)
+ id = Column(Integer, primary_key=True)
name = Column(String(100), unique=True, nullable=False)
def __repr__(self):
@@ -98,7 +98,7 @@ class Resource(Model):
"""Represents permission object such as `User` or `Dag`."""
__tablename__ = "ab_view_menu"
- id = Column(Integer, get_sequence_or_identity("ab_view_menu_id_seq"), primary_key=True)
+ id = Column(Integer, primary_key=True)
name = Column(String(250), unique=True, nullable=False)
def __eq__(self, other):
@@ -114,7 +114,7 @@ class Resource(Model):
assoc_permission_role = Table(
"ab_permission_view_role",
Model.metadata,
- Column("id", Integer, get_sequence_or_identity("ab_permission_view_role_id_seq"), primary_key=True),
+ Column("id", Integer, primary_key=True),
Column("permission_view_id", Integer, ForeignKey("ab_permission_view.id")),
Column("role_id", Integer, ForeignKey("ab_role.id")),
UniqueConstraint("permission_view_id", "role_id"),
@@ -126,7 +126,7 @@ class Role(Model):
__tablename__ = "ab_role"
- id = Column(Integer, get_sequence_or_identity("ab_role_id_seq"), primary_key=True)
+ id = Column(Integer, primary_key=True)
name = Column(String(64), unique=True, nullable=False)
permissions = relationship("Permission", secondary=assoc_permission_role, backref="role", lazy="joined")
@@ -139,7 +139,7 @@ class Permission(Model):
__tablename__ = "ab_permission_view"
__table_args__ = (UniqueConstraint("permission_id", "view_menu_id"),)
- id = Column(Integer, get_sequence_or_identity("ab_permission_view_id_seq"), primary_key=True)
+ id = Column(Integer, primary_key=True)
action_id = Column("permission_id", Integer, ForeignKey("ab_permission.id"))
action = relationship(
"Action",
@@ -160,7 +160,7 @@ class Permission(Model):
assoc_user_role = Table(
"ab_user_role",
Model.metadata,
- Column("id", Integer, get_sequence_or_identity("ab_user_role_id_seq"), primary_key=True),
+ Column("id", Integer, primary_key=True),
Column("user_id", Integer, ForeignKey("ab_user.id")),
Column("role_id", Integer, ForeignKey("ab_role.id")),
UniqueConstraint("user_id", "role_id"),
@@ -171,7 +171,7 @@ class User(Model):
"""Represents an Airflow user which has roles assigned to it."""
__tablename__ = "ab_user"
- id = Column(Integer, get_sequence_or_identity("ab_user_id_seq"), primary_key=True)
+ id = Column(Integer, primary_key=True)
first_name = Column(String(64), nullable=False)
last_name = Column(String(64), nullable=False)
username = Column(String(256), unique=True, nullable=False)
@@ -264,7 +264,7 @@ class RegisterUser(Model):
"""Represents a user registration."""
__tablename__ = "ab_register_user"
- id = Column(Integer, get_sequence_or_identity("ab_register_user_id_seq"), primary_key=True)
+ id = Column(Integer, primary_key=True)
first_name = Column(String(64), nullable=False)
last_name = Column(String(64), nullable=False)
username = Column(String(256), unique=True, nullable=False)
```
--
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.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] Mottimo commented on issue #24247: DB init and upgrade for MariaDB does not work on Airflow 2.3
Posted by GitBox <gi...@apache.org>.
Mottimo commented on issue #24247:
URL: https://github.com/apache/airflow/issues/24247#issuecomment-1147677559
@ephraimbuddy, using your patch we got the same error for the missing foreign key task_reschedule_ti_fkey
--
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.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] AlekseiSaff commented on issue #24247: DB init and upgrade for MariaDB does not work on Airflow 2.3
Posted by GitBox <gi...@apache.org>.
AlekseiSaff commented on issue #24247:
URL: https://github.com/apache/airflow/issues/24247#issuecomment-1147494380
A sequence is an object that generates a sequence of numeric values, as specified by the [CREATE SEQUENCE](https://mariadb.com/kb/en/create-sequence/) statement. Sequences are an alternative to [AUTO_INCREMENT](https://mariadb.com/kb/en/auto_increment/) when you want more control over how sequence numbers are generated.
so it seems when you are asking for auto inc field mariadb is creating sequence
--
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.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] ephraimbuddy commented on issue #24247: DB init and upgrade for MariaDB does not work on Airflow 2.3
Posted by GitBox <gi...@apache.org>.
ephraimbuddy commented on issue #24247:
URL: https://github.com/apache/airflow/issues/24247#issuecomment-1147649539
Looks like the sequences were added at this migration(implicitly) https://github.com/apache/airflow/blob/2.3.1/airflow/migrations/versions/0073_2_0_0_prefix_dag_permissions.py by instantiating the models.
--
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.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] AlekseiSaff commented on issue #24247: DB init and upgrade for MariaDB does not work on Airflow 2.3
Posted by GitBox <gi...@apache.org>.
AlekseiSaff commented on issue #24247:
URL: https://github.com/apache/airflow/issues/24247#issuecomment-1149421036
@ephraimbuddy any ideas what to do for those who created sequences manually?
--
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.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] boring-cyborg[bot] commented on issue #24247: DB init and upgrade for MariaDB does not work on Airflow 2.3
Posted by GitBox <gi...@apache.org>.
boring-cyborg[bot] commented on issue #24247:
URL: https://github.com/apache/airflow/issues/24247#issuecomment-1147192790
Thanks for opening your first issue here! Be sure to follow the issue template!
--
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.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org