boring-cyborg[bot]
commented
2 years ago Thanks for opening your first issue here! Be sure to follow the issue template!
Closed Mottimo closed 2 years ago
boring-cyborg[bot]
commented
2 years ago Thanks for opening your first issue here! Be sure to follow the issue template!
ephraimbuddy
commented
2 years ago 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.
potiuk
commented
2 years ago 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?
potiuk
commented
2 years ago 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 ? ) - MariaDB/MySQL does not have transactional integrity for DDL changes, so if you break migration in-between, those things MIGHT happen.
Mottimo
commented
2 years ago 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.
ephraimbuddy
commented
2 years ago 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
ephraimbuddy
commented
2 years ago 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
tonjo
commented
2 years ago 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.
Mottimo
commented
2 years ago 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
_Excerpt from 0100_2_3_0_add_taskmap_and_map_id_ontaskinstance.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.
potiuk
commented
2 years ago 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 for 2.2.5 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?
potiuk
commented
2 years ago 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.
Mottimo
commented
2 years ago 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.
potiuk
commented
2 years ago Yeah. If you run muiltiple envs it would be great to see if it's a recurring problem or once-off!
AlekseiSaff
commented
2 years ago 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
potiuk
commented
2 years ago 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.
potiuk
commented
2 years ago 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.A sequence is an object that generates a sequence of numeric values, as specified by the CREATE SEQUENCE statement. Sequences are an alternative to 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
potiuk
commented
2 years ago 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 recommendations 4) Our scripts are only run against MySQL in CI/manual tests. 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 (and advise our users to move to MySQL) 2) should we make an effort to support MariaDB as community?
I think "middle ground" proposal of saying "we do best-effort" to support MariaDB is just 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 to manually modify their database we cannot offer them any more actually).
Mottimo
commented
2 years ago @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?
AlekseiSaff
commented
2 years ago 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 :(
potiuk
commented
2 years ago 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 "oifficially" 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 drop link here) 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.
AlekseiSaff
commented
2 years ago 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);
ab_permission_view_id_seqDROP 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);
ab_permission_view_menu_id_seqDROP 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);
ab_permission_view_role_id_seqDROP 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);
ab_view_menu_id_seqDROP 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);
task_id_sequenceDROP 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);
taskset_id_sequenceDROP 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);
bmoon4
commented
2 years ago FYI, it is not a table despite the error message (1146, "Table 'airflow.ab_permission_id_seq' doesn't exist").
It's a SEQUENCE.
Had to spend long long time for troubleshooting because of this wrong message....
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. (Airflow 2.2.X. is using SQLAlchemy == 1.3.24 but from 2.3.X, SQLAlchemy == 1.4.9. ) I suspect SQLAlchemy == 1.4.9 is not firing CREATE SEQUENCE properly with MariaDB dialect.
sh-4.4$ mysql -u root
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 62
Server version: 10.3.32-MariaDB MariaDB Server
...
...
MariaDB [(none)]> use airflow_2_3_dev;
Database changed
MariaDB [airflow_2_3_dev]> show tables;
+-------------------------------+
| Tables_in_airflow_2_3_dev |
+-------------------------------+
| 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 |
| sla_miss |
| slot_pool |
| task_fail |
| task_instance |
| task_reschedule |
| variable |
| xcom |
+-------------------------------+
28 rows in set (0.003 sec)
MariaDB [airflow_2_3_dev]> CREATE SEQUENCE ab_permission_id_seq START WITH 1 INCREMENT BY 1;
Query OK, 0 rows affected (0.048 sec)
MariaDB [airflow_2_3_dev]> CREATE SEQUENCE ab_view_menu_id_seq START WITH 1 INCREMENT BY 1;
Query OK, 0 rows affected (0.144 sec)
...
...Issue https://github.com/apache/airflow/issues/23700 seemed to have same problem tho...(1146, "Table 'airflow.task_id_sequence' doesn't exist")...
bmoon4
commented
2 years ago Can anyone test if return Sequence(sequence_name) actually works with MariaDB?
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)
name = Column(String(100), unique=True, nullable=False)
def __repr__(self):
return self.namedef get_sequence_or_identity(sequence_name: str) -> Union[Sequence, 'Identity']:
"""
Depending on the engine it either returns Sequence, or Identity (in case of MSSQL in SQLAlchemy 1.4).
In SQLAlchemy 1.4 using sequence is not allowed for primary key columns in MsSQL.
Primary columns in MsSQL use IDENTITY keyword to auto increment.
Using Sequence for those fields used to be allowed in SQLAlchemy 1.3 (and essentially ignored
if only name was specified).
See https://docs.sqlalchemy.org/en/14/dialects/mssql.html
Changed in version 1.4: Removed the ability to use a Sequence object to modify IDENTITY
characteristics. Sequence objects now only manipulate true T-SQL SEQUENCE types.
:param sequence_name: name of the sequence
:return: Sequence or Identity
"""
from airflow.settings import SQL_ALCHEMY_CONN
if SQL_ALCHEMY_CONN is not None and SQL_ALCHEMY_CONN.startswith('mssql'):
try:
from sqlalchemy import Identity
return Identity()
except Exception:
# Identity object is only available in SQLAlchemy 1.4.
# For SQLAlchemy 1.3 compatibility we return original Sequence if Identity is missing
pass
return Sequence(sequence_name)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/www/fab_security/sqla/models.py#L86-L94 https://github.com/apache/airflow/blob/2.3.1/airflow/migrations/versions/0073_2_0_0_prefix_dag_permissions.py
ephraimbuddy
commented
2 years ago 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?
AlekseiSaff
commented
2 years ago My base was created in airflow 1.x than it was normal airflow db upgrade steps
ephraimbuddy
commented
2 years ago 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.
AlekseiSaff
commented
2 years ago so it's interesting. officially you don't support mariaDB, but someone added functionality specific to MariaDB?
ephraimbuddy
commented
2 years ago 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
ephraimbuddy
commented
2 years ago Can someone test with this patch for your upgrade if you're in 2.2.5:
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)
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 KEYtask_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...
Mottimo
commented
2 years ago @ephraimbuddy I will test your patch and let you know
Mottimo
commented
2 years ago @ephraimbuddy, using your patch we got the same error for the missing foreign key task_reschedule_ti_fkey
AlekseiSaff
commented
2 years ago 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;
ephraimbuddy
commented
2 years ago @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
potiuk
commented
2 years ago 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 with MariaDB nor made sure it keeps running in the future.
And yes. I am personally not a fan of such policy to be honest, but it is what it is for now.
Mottimo
commented
2 years ago 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.
AlekseiSaff
commented
2 years ago 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)
ephraimbuddy
commented
2 years ago 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.
tonjo
commented
2 years ago here is the squences in my DB (2.31 and 2.32) that you can create manually
Nice "patch", I think another few sequences are 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);
-- Sequence structure for ab_user_role_id_seq
DROP SEQUENCE IF EXISTS ab_user_role_id_seq;
CREATE SEQUENCE ab_user_role_id_seq start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE = InnoDB;
SELECT SETVAL(ab_user_role_id_seq, 1001, 0);
-- Sequence structure for ab_user_id_seq
DROP SEQUENCE IF EXISTS ab_user_id_seq;
CREATE SEQUENCE ab_user_id_seq start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE = InnoDB;
SELECT SETVAL(ab_user_id_seq, 1001, 0);
Finally I got the first upgrade from 2.2.5 to 2.3.1 without errors. The applied steps are:
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;
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 index 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 index 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.
potiuk
commented
2 years ago 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.
Mottimo
commented
2 years ago 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.
potiuk
commented
2 years ago 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 .
AlekseiSaff
commented
2 years ago @ephraimbuddy any ideas what to do for those who created sequences manually?
ephraimbuddy
commented
2 years ago @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.
Mottimo
commented
2 years ago Hi, I run another test on a fresh environment. These the steps using MariaDB:
New env prepared starting from Airflow 2.2.5, DB initialized and never used;
Dump of the DB and update of the COLLATE to utf8mb3_general_ci;
Airflow 2.3.1 updated using @ephraimbuddy's patch for sequences;
airflow db upgrade successfully executed without any further intervention. Service was not started;
upgrade to Airflow 2.3.2, vanilla code;
airflow db upgrade successfully executed;
Service started without issues;
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...;
... 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.
potiuk
commented
2 years ago 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.
AlekseiSaff
commented
2 years ago 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.
Mottimo
commented
2 years ago 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:
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.localdomain.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.firesequence(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_dbapiexception 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
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 "", line 783, in exec_module
File "", 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
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 "", 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 "", 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(_captureexception=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_dbapiexception
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.firesequence(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_dbapiexception
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?
Code of Conduct