Use native UUID data type on MariaDB 10.7+

[Mariusz Felisiak]

MariaDB 10.7 introduces UUID data type, see ​docs and ​blog post. We should correct the has_native_uuid_field feature flag and use uuid instead of char(32).

[Simon Charette]

Small note that we should make sure that changing this flag won't break installs with UUIDField backend by char(32) columns as it might be impractical for large installs to rebuild their whole table primary keys otherwise.

If we don't document a clear upgrade path I foresee a few reports when users attempt to add a ForeignKey referencing a model with a UUIDField primary key that is backed by a char(32) and hit a MariaDB error telling them that a foreign constraint cannot be created from a uuid to a char(32) column.

[Mariusz Felisiak]

Replying to Simon Charette:

Small note that we should make sure that changing this flag won't break installs with UUIDField backend by char(32) columns as it might be impractical for large installs to rebuild their whole table primary keys otherwise.

If we don't document a clear upgrade path I foresee a few reports when users attempt to add a ForeignKey referencing a model with a UUIDField primary key that is backed by a char(32) and hit a MariaDB error telling them that a foreign constraint cannot be created from a uuid to a char(32) column.

According to the ​blog post, it should be possible to change a data type without manual data conversion:

With these basic conversion rules, you can migrate from your existing CHAR/VARCHAR/TEXT hexadecimal text or BINARY/VARBINARY/BLOB encoded to UUID using:

CREATE TABLE t1 (id BINARY(16));
ALTER TABLE t1 MODIFY COLUMN id UUID;

[Adam Johnson]

That ALTER TABLE still requires a table rebuild, which is impractical or impossible for large installations’ PK's. But maybe the "upgrade path" there would be "create a field subclass always backed by char(32)".

[Simon Charette]

With these basic conversion rules, you can migrate from your existing CHAR/VARCHAR/TEXT hexadecimal text or BINARY/VARBINARY/BLOB encoded to UUID using:

Unfortunately, this statement doesn't account for the fact that UUIDs are often used as primary keys and thus referenced by other tables where this approach simply won't work as you get in a chicken-egg problem with foreign references. To perform this properly a manual dropping of references, followed by type alterations of all tables involved, and final step to recreate constraints is required which possibly will require table rebuilds as Adam pointed out.

But maybe the "upgrade path" there would be "create a field subclass always backed by char(32)".

That seems like the most suitable upgrade path. It will likely require documenting that all the generated migration operations should be wrapped in SeparateDatabaseAndState unless we adapt the schema editor to avoid ALTER when the generated SQL column of from_field and to_field remains the same.

[Adam Johnson]

unless we adapt the schema editor to avoid ALTER when the generated SQL column of from_field and to_field remains the same.

This sounds like generally a good idea any way!

[Mariusz Felisiak]

Agreed, this can be tricky. I'd really like to come up with a doable pattern that could be used in the future, because other emulated fields may be implemented in the databases.

[raydeal]

I changed type to UUID but it has broken prefetch_related tests. I have tried in myslq/operations.py add UUIDField to cast_data_types and override unification_cast_sql but it didn't help.
The issue is because value of related field is still visible as str type not as UUID type. I suspect that there is lack of cast type to uuid in query, but I can't find place where to fix it.
Work in progress :)

[Mariusz Felisiak]

Replying to Adam Johnson:

unless we adapt the schema editor to avoid ALTER when the generated SQL column of from_field and to_field remains the same.

This sounds like generally a good idea any way!

Looks like it already ​works. For example, I've changed models.CharField(max_length=50, unique=True) to models.SlugField(unique=True) on PostgreSQL and Django didn't alter the db type:

--
-- Alter field field_2 on mytestmodel
--
-- (no-op)

[Mariusz Felisiak <felisiak.mariusz@…>]

In 7cd187a:

Fixed #33507 -- Used UUID data type on MariaDB 10.7+.

Co-Authored-By: Mariusz Felisiak <felisiak.mariusz@…>

[GitHub <noreply@…>]

In 9c37103:

Refs #33507 -- Doc'd using UUID data type on MariaDB 10.7+ in UUIDField docs.

Follow up to 7cd187a5ba58d7769039f487faeb9a5a2ff05540.

[Klaas van Schelven]

I just ran into this ​here.

Caused by not carefully reading the release notes, but quite hard to debug.

Having actually read those notes now, I'm left to wonder:

1. what will happen with existing databases that are from a post-5.0 world? They will have been created with uuid columns. Will the suggested migration path accidentally brake _those_?
2. the suggested path is "varchars everywhere". But my project is deployed on many DB backends, also postgres. So the suggested solution is a downgrade for those and or create the risk of breakage on those...

I realise these questions may be "more on me than on Django" but OTOH the present issue seems like exactly the spot where people with such problems would gather.

[Klaas van Schelven]

I also think there's another problem with this change: someone upgrading mariadb _itself_ (to 10.7 from a lower version) would have to go through the exact same migration instructions. These are currently undocumented, and even if they were it is unlikely that someone would actually find them (because one would be looking at the maria instructions, not the django ones)