Support UUID data type for MySQL 8.0+ and MariaDB 10.7+

[juangacovas]

Describe the feature or enhancement

Is it possible to support prisma migrations / table structs for specific versions of MySQL / MariaDB? Or require at least mysql and mariadb versions that support UUID data type...

MySQL 8.0 and MariaDB 10.7 added support for UUID data type respectively. MariaDB 10.11 is the current LTS for MariaDB and 8.0 is the current LTS for MySQL.

The data type to use is UUID, instead of VARCHAR(36) which is a performance and storage killer despite the "compatibility" it offers. The varchar 'hack' works for some thousands of referenced rows in umami, but severely degrades once you have more, leaving you thinking your only option is to migrate to PosgreSQL instead of using MySQL or MariaDB.

• The only required change is use UUID instead of VARCHAR(36) on table structs where an ID primary key or a reference to one is needed.

• This also make the migration at v2.11.0 more simpler -and in order to work if you have UUID-. So the file (db/mysql/migrations/05_add_visit_id/migration.sql) fails:

Instead of BIN_TO_UUID(RANDOM_BYTES(16) & 0xffffffffffff0fff3fffffffffffffff | 0x00000000000040008000000000000000) uuid you can simply use UUID() as uuid (I had to rollback the failed migration and amend it to make it work once you have UUID data type...)

Thanks.

[franciscao633]

Unfortunately we only support 1 schema file per database. It is possible to detect the db version before the build-db step and target a different schema file / migration folder, but it will add more maintenance and support on our side. Ideally we just cut-off support for older MySQL / MariaDB versions, update the schema off of VARCHAR(36) and move forward.

[juangacovas]

Agree, just moving forward and require proper versions of MySQL/MariaDB that support the UUID data type seems OK to me too.

I don't have very high traffic sites right now and the degradation using VARCHAR(36) was noticeable to the point we observed timeouts (i.e. when deleting a site or just asking for data), so I gave a try and manually converting to UUID all of the column IDs and references really worked like a charm.

Anyway I understand this could require some testing for the initial structure and migrations. I can try help with MariaDB and MySQL, drop me a message and I can try to help / test.

[Maxime-J]

A quick warning about this, because it isn't totally accurate.

There are two issues :

• MySQL indeed supports UUID, but not as a data type, contrary to MariaDB.
  So moving from varchar to uuid is only possible with MariaDB.

• Even though a Prisma string field apparently works with a MariaDB UUID column,
  Prisma doesn't offer @db.Uuid attribute with it.
  It doesn't look good having a difference between schema and actual database,
  and this could lead to some issues, SQL may be incorrectly generated by Prisma, which should be manually tweaked.

varchar isn't probably the ideal solution, but I don't think there's an easy alternative at the moment.