Airflow db init fails on mysql 5.7

[pingzh]

Apache Airflow version

2.2.4

and versions after it

What happened

Airflow db init fails on mysql 5.7 due to :

Specified key was too long; max key length is 767 bytes

On mysql 5.7, the index key prefix limit is 767 bytes for tables see: https://dev.mysql.com/doc/refman/5.7/en/innodb-limits.html when innodb_large_prefix is OFF or it use the REDUNDANT or COMPACT row format.
and mysql 5.7 uses utf8mb3 charset (which is utf8), thus the max length for
index key should be 251

The following key length leads to the error:

airflow/airflow/models/pool.py

Line 48 in 808035e

pool = Column(String(256), unique=True)

airflow/airflow/models/xcom.py

Line 61 in 808035e

key = Column(String(512, **COLLATION_ARGS), nullable=False, primary_key=True)

(we don't really need that long key ^^)

airflow/airflow/migrations/versions/0061_2_0_0_increase_length_of_pool_name.py

Line 44 in 808035e

batch_op.alter_column('pool', type_=sa.String(256, **COLLATION_ARGS))

What you think should happen instead

airflow db init should works for mysql 5.7

How to reproduce

No response

Operating System

Apple M1 Max, version: 12.2

Versions of Apache Airflow Providers

No response

Deployment

Other

Deployment details

No response

Anything else

No response

Are you willing to submit PR?

• Yes I am willing to submit a PR!

Code of Conduct

• I agree to follow this project's Code of Conduct

[potiuk]

This is the problem of your definition See the notes https://airflow.apache.org/docs/apache-airflow/stable/howto/set-up-database.html#setting-up-a-mysql-database about collation used (you need to use utf8_mb3 collation).

[potiuk]

BTW. We NEED that long of IDs. This is part of the product.

BTW. MySQL has a number of problems like that that had to be workaround - we recommend to switch to Postgres - which is rock-solid.

[potiuk]

(And you probably has wrong collation set - you likely have utf8_mb4 not utf8_mb3) - all our tests are run with MySQL 5.7.

[potiuk]

(actually your problem is that innodb_large_prefix is off) - you should enable it. We are not going to change that requirement.

[pingzh]

This is the problem of your definition See the notes https://airflow.apache.org/docs/apache-airflow/stable/howto/set-up-database.html#setting-up-a-mysql-database about collation used (you need to use utf8_mb3 collation).

hi @potiuk thanks for the comments. In fact for mysql 5.7, the default charset is utf8 (with collation is utf8_general_ci ,which is utf8mb3 in mysql 5.7 , so using that wont solve the issue.

my question is when setting the db columns an indexes, we might be extra careful about the length. for example, for the key of xcom, do we really need 512 that long?

[potiuk]

(actually your problem is that innodb_large_prefix is off) - you should enable it. We are not going to change that requirement.

As explained: actually your problem is that innodb_large_prefix is off) - you should enable it. We are not going to change that requirement.

my question is when setting the db columns an indexes, we might be extra careful about the length. for example, for the key of xcom, do we really need 512 that long?

Yes. Unless you want to break compatibliity of about 10000 installations out there.

[potiuk]

The really, really big problem is that we cannot make a migration. To decrease the size. This ship has sailed long ago in 1.10. There is no sane way to "cut" ids any more

[potiuk]

Personally - I would also vote for decreasing the lenght. But there is simply no sane way to release airlfow 2.* with decreased length of id.

[pingzh]

@potiuk agree that we don't need to decrease it now. my concern is more in the future changes. I think we should be more careful when changing the database schema, as it has implications for users when upgrading.

[potiuk]

@potiuk agree that we don't need to decrease it now. my concern is more in the future changes. I think we should be more careful when changing the database schema, as it has implications for users when upgrading.

Agreee. I think we;ve learned something. Back then when it was changed (before me as committer) we become super careful - we have now not only much better migrations (including being able to go back and forth), automated testing of upgrade and downgrade on CI and very careful reviews of what has changeed precisely beceuse we learned from that experience. Collation and index length for MySQL was something that gave us a lot fo trouble.

You are complaining about a change that was implemented ~3 years ago (you simply were a bit disconnected since).

Check out those:

• Actually run against the version of the DB we select in the matrix. #12591
• Fix: Mysql 5.7 id utf8mb3 #14535
• Fix MySQL database character set instruction #17603
• Fix MySQL database character set instruction #17603
• Automatically use utf8mb3_general_ci collation for mysql #17729
• Change id collation for MySQL to case-sensitive #18072
• Fix MySQL db migration with default encoding/collation #19268

BTW. Speaking of migratioins. If you want to avoid troubles I heartily recomend to move to Postgres. You will not regret it. MySQL is a really bad choice you shoulld reconsider at AirBnB.

[potiuk]

BTW. The limited index size in MySQL is a bummer. This is an extremely bad design choice especially connected with the fact that the actual size taken by the index depends on collation.

Sorry for the long RANT, but I am so fed-up with the problems MySQL caused use because of they bad design choices and wrong (IMHO) decisions that it is a blueprint of "how not to treat your users". And whenever I see someone smart who understands how it all works I will use all the strenght I have to convince them to dump MySQL because their decision make their users to suffer (and in our case - our users and us).

When I first learned (2 years ago when it caused us the problem) was when a user wanted to use 🦅 in the id of the DAG in 5.6. This is extended UTF-8 set and required utf8mb4 and it was not possible - precisely because suddenly when you used utf8mb4 the index was to big on (back then) MySQL 5.6. And well - this is a very legitimate request. Why not? Chinese characters? Korean? Why not?

And then it got worse - if you look at how engine/ collaction approach evolved in MySQL 5.7. 8 and possibly in the future - this is nothing but madness.

5.6: default charset in 5.6 was .... Latin2 and collation is Swedish. This is what - unknowingly by the admins - is by far biggest number of charset/collation most MySQL databases have (historically). Yep. Swedish. BTW. Back then even if indexes of MySQL 5.6 were smaller, this was not a problem because the index for latin2 has big capacity. If you wanted to change to utf8 you were immediately cutting it down - which basically means that definition of your database had to take into account which encoding/collation should be used. And what's worse - utf8mb3 or mb4 is designed in the way that it requires 1,2,3 (or 4) characters - Why on earth the limit for index for utf8mb3 is 1/3 of latin-2? and utf8mb4 ? You can fit the same number of utf8mb* characters in the same space as latin-2 if you do not use special characters. UTF8 was DESIGNED for that. It was supposed to take exactly the same amount of space as ASCII for ASCII characters. Why on earth MySQL decided that - no matter which characters you have you can only fit 1/3 of them in the index? Beats me. It must have been for performance, but it is a terrible choice for unsuspecting user who - if they want to go utf route has to basically redesign their database.

And (as you noticed) max index size of your InnoDB storage is different, depending on your database configuration. What you might also not be aware - the size of the index can go down depending on the page size you choose for the DB. So generally speaking if you want to have design of schema that will work for all the different charset, page sizes, COMPACT setting etc. then your index has to have ..... bear with me ..... "SOME SMALL-ISH SIZE". This is not defined what exactly is "Small enough". The index size in 767 (strange round number isn't it???)

Is it 300 characters ? maybe, in some cases of latin2, but if you use utf8mb4, COMPACT and small page size it is WAY to much . worst possible case and your index size cannot be ~ 80-ish characters or so (or maybe less, don't know). And remember - that if you have unique columns or foreign keys, where mutliple columns are are involved, the size of the index is COMBINED size of the columns. If you want to combine four columns, then - each of them has to have ~ < 20 characters. And if you want to add more, then, well, you are out of luck.

Good enough? Not really.

But the really BAD thing is that when you create your schema - you do not KNOW what the limit is. It is arbitrary based on decisions of the deployment side.

What's more - I am not sure if you know, but you can specify different encoding/collation not only for the database, but also different for a schema, table and different for the column (the last one BTW is a trick we managed to achieve Utf8mb4 databases with our index - we default ALL ID columns to be utf8mb3 if utf8mb4 is used for the database). But what's more you can also specifiy encoding and collation on the client (WHAT?) which defaults to what is your LANGUAAGE on the client (????) and BAD things happen if those two encodings/collations do not agree with each other.

5.7: This was apparently noticed they increased the size of index to 3072 (Andther round number). But all the problems remained:

If you reduce the InnoDB page size to 8KB or 4KB by specifying the innodb_page_size option when creating the MySQL instance, the maximum length of the index key is lowered proportionally, based on the limit of 3072 bytes for a 16KB page size. That is, the maximum index key length is 1536 bytes when the page size is 8KB, and 768 bytes when the page size is 4KB.

And then it got worse - if you look at how engine/ collaction approach evolved in MySQL 5.7. 8 and possibly in the future - this is nothing but madness.

How on earth shoudl I decide on the column size when I am designing my database??????? For database - basicallly they force me to use autoincremented ids as primary keys. This was maybe good idea in 1980s - but this is an antipattern now.

While you can have it for primary keys, the problem cannot be solved for other field. When you want to have unique indexes on the actual text fields you have exactly the same problem ..... Because this limit applies to ALL indexes - not only primary keys. How do I make sure my long text column is unique together with another long text column? Should I write a TRIGGER to protect against accidenal entering of non-unique value ?????

But the real fun thing starts with 8. Bear with me. They had a chance to fix it all and they screwed up even more. Instead of simplifying it, they complicated it even further and made even more unobvious choices and deliberately forward-incompatible ones.

Enter MySQL 8:

Default encoding for 8 utf8mb4 and collation utf8mb4_0900_ai_ci. But all the limits did not change. This means that when previously your DB schema installation worked on 5.7 no "stock" mysql (latin2) - they suddenly might stop working on 8 (because effective size of the index decreased).

For us it means that if we did not use the utf8mb3 id field collation trick - users who previously installed Airlfow on MySQL 5.7 with default settings (vast majo rity of people do not change the encoding/collation and use default) suddenly it would stop working. This is why actually we implemented the trick. Because suddenly people started to raise issues that Airflow cannot be installed on MySQL 8. Very nice "feature"- thank you Oracle.

But this is nothing yet.

They actually built-in another trap for their unsuspecting users (and us). If you are a bit "smarter admin" and in the past you actually did the right thing and chose utf8 as a character encoding (sounds legit right?) then you set a trap on yourself.

Bear with me.

In MySQL 8 when you use utf8 you end up with..... yeah utf8mb3 ... WHAT? The default encodid is 'utf8mb4' right? But if you use utf8 you aactua use utf8mb3 .... Sounds logical? Not really.

But this is not the worst part. The worst part is deeply hidden in the documentation.

https://dev.mysql.com/doc/refman/8.0/en/charset-unicode.html

utf8: An alias for utf8mb3. In MySQL 8.0, this alias is deprecated; use utf8mb4 instead. utf8 is expected in a future release to become an alias for utf8mb4.

Let that sink in for a while. .......

There are no plans to increase the index size in the future versions. And what this basically means that if you use 'utf8' as your character set and migrate to MySql 10 (assuming this will be 10) - then, yes, you guessed it , Airflow database will stop working because suddenly the same utf8 you had before will have smaller indexes to use.

This is a trap. Again.

---

End of a RANT.

Sorry @pingzh but - I lost a lot of hair because of that already and whenever I can I try to convince everyone - if you only can, switch to Postgres.

BTW. You know that Postgres has virtually no limits on sizes of the indexes. do you?

There is no PostgreSQL-imposed limit on the number of indexes you can create on a table. Of course, performance may degrade if you choose to create more and more indexes on a table with more and more columns. PostgreSQL has a limit of 1GB for the size of any one field in a table.

[potiuk]

I hope the above will explain that I was not mean @pingzh - it was not my intention for sure. But when someone says that "We should be more careful with our design choices" when it comes to MySQL, there are NO GOOD DESIGN CHOICES. Any design choice you might make might be broken by very bad decisions of their.

[potiuk]

So just to add one more comment: THIS IS EMBODIMENT what MySQL bad decisions lead to:

#24526 (comment)

Precisely the resuilt of all the mess above.

[potiuk]

And I just learned by answering #24526 that they are planning to remove utf8mb3 altogether (but this in another part of documentation):

https://dev.mysql.com/doc/refman/8.0/en/charset-unicode-utf8.html

The utf8mb3 character set is deprecated and you should expect it to be removed in a future MySQL release. Please use utf8mb4 instead. utf8 is currently an alias for utf8mb3, but it is now deprecated as such, and utf8 is expected subsequently to become a reference to utf8mb4. Beginning with MySQL 8.0.28, utf8mb3 is also displayed in place of utf8 in columns of Information Schema tables, and in the output of SQL SHOW statements.

To avoid ambiguity about the meaning of utf8, consider specifying utf8mb4 explicitly for character set references.
This is a nightmare

I am at a total loss.