#33507 closed New feature (fixed)
Use native UUID data type on MariaDB 10.7+
Reported by: | Mariusz Felisiak | Owned by: | raydeal |
---|---|---|---|
Component: | Database layer (models, ORM) | Version: | 4.0 |
Severity: | Normal | Keywords: | mariadb |
Cc: | Adam Johnson | Triage Stage: | Ready for checkin |
Has patch: | yes | Needs documentation: | no |
Needs tests: | no | Patch needs improvement: | no |
Easy pickings: | no | UI/UX: | no |
Description (last modified by )
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)
.
Change History (21)
comment:1 by , 3 years ago
Description: | modified (diff) |
---|
comment:2 by , 3 years ago
Description: | modified (diff) |
---|---|
Triage Stage: | Unreviewed → Accepted |
comment:4 by , 3 years ago
Replying to Simon Charette:
Small note that we should make sure that changing this flag won't break installs with
UUIDField
backend bychar(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 aUUIDField
primary key that is backed by achar(32)
and hit a MariaDB error telling them that a foreign constraint cannot be created from auuid
to achar(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;
comment:5 by , 3 years ago
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)
".
comment:6 by , 3 years ago
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.
follow-up: 14 comment:7 by , 3 years ago
unless we adapt the schema editor to avoid
ALTER
when the generated SQL column offrom_field
andto_field
remains the same.
This sounds like generally a good idea any way!
comment:8 by , 3 years ago
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.
comment:9 by , 3 years ago
Owner: | changed from | to
---|---|
Status: | new → assigned |
comment:10 by , 3 years ago
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 :)
comment:11 by , 2 years ago
Has patch: | set |
---|
comment:12 by , 2 years ago
Patch needs improvement: | set |
---|
comment:13 by , 2 years ago
Patch needs improvement: | unset |
---|
comment:14 by , 2 years ago
Replying to Adam Johnson:
unless we adapt the schema editor to avoid
ALTER
when the generated SQL column offrom_field
andto_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)
comment:15 by , 21 months ago
Needs documentation: | set |
---|---|
Patch needs improvement: | set |
comment:16 by , 18 months ago
Needs documentation: | unset |
---|---|
Patch needs improvement: | unset |
comment:17 by , 17 months ago
Patch needs improvement: | set |
---|
comment:18 by , 17 months ago
Patch needs improvement: | unset |
---|
comment:19 by , 16 months ago
Triage Stage: | Accepted → Ready for checkin |
---|
Small note that we should make sure that changing this flag won't break installs with
UUIDField
backend bychar(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 aUUIDField
primary key that is backed by achar(32)
and hit a MariaDB error telling them that a foreign constraint cannot be created from auuid
to achar(32)
column.