Incorrect Sorting with UUID Data Type in MariaDB about framework

[staudenmeir]

Thanks @driesvints. I was thinking about "only" switching the HasUuids trait to UUID v7 while leaving Str::uuid() unchanged. This would improve performance (at least with the DBMS we've checked so far) but not introduce any data leaks.

When upgrading to Laravel 12, existing users of the HasUuids trait that rely on the sortability would have to override newUniqueId() and continue using Str::orderedUuid() to avoid the breaking change.

[staudenmeir]

Thanks for the PR @Karem-sobhy. IMO, the HasV7Uuid trait is the best solution to the MariaDB issue right now.

For Laravel 12, I think we should consider again to switch to UUID v7 by default. Just last week, there was a tweet about the performance gains from v7 on PostgreSQL.

@driesvints Do you remember why #44210 got reverted in the end?

[Karem-sobhy]

@staudenmeir exactly what I was thinking about when creating the PR we leave Str::Uuid as is and have Str:Uuid7 instead but like you i think that HasUuid trait should use uuid7 by default because that if uuid7 leaks date so ordered uuid4 leaks the same data and is not a standard uuid also.

[driesvints]

Thanks @Karem-sobhy. Pinging you @staudenmeir as you were the author of #50192. Seems UUID columns aren't compatible with Laravel? I think we'll need more extensive tests in our MariaDB test suite for this one.

[github-actions]

Thank you for reporting this issue!

As Laravel is an open source project, we rely on the community to help us diagnose and fix issues as it is not possible to research and fix every issue reported to us via GitHub.

If possible, please make a pull request fixing the issue you have described, along with corresponding tests. All pull requests are promptly reviewed by the Laravel team.

Thank you!

[staudenmeir]

Interesting issue. I don't order by UUID columns in my apps and so haven't run into it.

This issue makes the new UUID data type in MariaDB useless and not compatible with Laravel.

Not a very constructive statement. The issue is not that Laravel supports a native database data type. When you don't need your UUIDs to be sortable or already use v7 UUIDs, the native type is a great benefit. MariaDB added the type for a reason.

IMO, the best solution would be using v7 UUIDs instead the current of the v4 ordered UUIDs, but altering the HasUuids trait's default behavior is basically an unsolvable breaking change.

Instead, I would say that this needs to be addressed by MariaDB users in their applications:

• We add a helper method for generating v7 UUIDs and explain in the documentation that users should override newUniqueId() in their models and generate v7 UUIDs.
• Or we recommend to use $table->char('uuid', 36); columns when records need to be sortable.

[driesvints]

Thanks @staudenmeir. I do feel we need some documentation around this and would be grateful if anyone could provide a PR.

@Karem-sobhy please see @staudenmeir's reply around this topic.

[Karem-sobhy]

@staudenmeir using the old char(36) may be a fix but using the orderedUuid with mariadb is just bad for indexing and performance of the insertion and also non informed users maybe don't know all this because the documentation says clearly the uuid will be ordered which is not true when using mariadb default uuids

I can help with a pr but don't know what will be best action to do so can you help me to chose from:

• if the driver is mariadb then use v7 by default
• make another HasV7Uuid trait
• make new Str helper for v7 uuid and ask users to implement it in the newUniqueId method
  @driesvints @staudenmeir I think you are more informed and can chose the best solution for this to implement it.

[driesvints]

I honestly don't know. We can't make the breaking change and having a HasV7Uuid which should technically be the better default sits a bit ill with me. I just wouldn't change anything atm.

[driesvints]

Hey @staudenmeir. I guess it's mainly this remark from @tillkruss:

IMO it makes no sense to change the default Str::uuid() to v7, which will always reveal the timestamp (minor data leak),

Seems v7 has a data leak that's unwanted?

[driesvints]

@staudenmeir thanks for explaining. Right now I don't have the time to dig deeper into this matter. The best way forward is that you attempt a PR with whatever you want to try so Taylor can review it. Thanks 👍