Moving to MySQL causes UUID wrong behavior

[advanc3dUA]

Describe the bug

Hey, guys,

During the deployment of my app to the server, I decided to drop docker (because its minimum system requirements are 4GB of RAM while I want to use a cheaper server with less RAM) and change to MySQL DB. I successfully connected the app with DB, it compiles, starts, etc.

But there is a strange problem with UUID types. Before moving to MySQL the app was working pretty fine - my UUIDs were successfully generated before adding an object in the DB. Now I do get something like "ùïðé¡ Eô + u:Bh " instead of normal UUID in the id cell of the table.

More to the point, the table _fluent_migrations also has the same style of corrupted IDs of the applied migrations. So, looks like that is a global problem of my app and not because I set the wrong type for the field in the table.

How can I get the UUIDs back to normal behavior when I use MySQL?

To Reproduce

Same behavior on MacOS and Ubuntu. The only requirement is to use MySQL.

• My class which is stored in DB has the id variable:

  MyClass: Model { 
  static var schema: String = "myscheme"
  @ID(key: .id) var id: UUID? 
  ... other 
  }

• Migration:

  struct MyClassTable: AsyncMigration {
  func prepare(on database: FluentKit.Database) async throws {
      try await database.schema("myscheme")
          .id()
           .... other
  }
  }

• configure method of configure.swift:

  var tls = TLSConfiguration.makeClientConfiguration()
  tls.certificateVerification = .none
  app.databases.use(DatabaseConfigurationFactory.mysql(
  hostname: Environment.get("DATABASE_HOST") ?? "0.0.0.0",
  port: Environment.get("DATABASE_PORT").flatMap(Int.init(_:)) ?? MySQLConfiguration.ianaPortNumber,
  username: Environment.get("DATABASE_USERNAME") ?? "username",
  password: Environment.get("DATABASE_PASSWORD") ?? "password",
  database: Environment.get("DATABASE_NAME") ?? "tablename",
  tlsConfiguration: tls
  ), as: .mysql)

Expected behavior

I expect to get F9EFF0E9-A19D-45F4-862B-1D753A426898 this kind of UUIDs in the MySQL table instead of ùïðé¡ Eô + u:Bh ".

Environment

• Vapor Framework version: 4.92.4
• Vapor Toolbox version: 18.7.4
• OS version: Ventura 13.6.1
• Additional package: fluent-mysql-driver 4.0.0

Additional context

I am working on my first Vapor project. So, I mean, I am a newbie and this can be not a bug but my low skill. In this case please help me to fix it.

[gwynne]

This is normal behavior for the MySQL driver, unfortunately - UUIDs are stored as raw bytes, and the MySQL driver maps the .uuid type to VARBINARY(16), but the MySQL commandline and most other MySQL tools will attempt to read this as if it were just text with binary charset/collation (i.e. none); the result is the gibberish you're seeing. You can see results more like what you were expecting with a query of the form SELECT HEX(id) FROM table.

It is worth noting that the most recent versions of the mysql commandline monitor (8.3.0 or later) will display such data in a readable format without additional intervention.

(Worth noting, this behavior will be fixed in Fluent 5, but in the meantime, it's normal and expected, and nothing is wrong with your setup.)

[gwynne]

Congratulations, incidentally, on opening the very first issue to be officially assigned to Fluent 5!

[advanc3dUA]

Well, as a workaround I can create UUID, convert it into the String, and store it in the DB as String but not the UUID type. I need it in both directions - store and get from the table inside the app. Sounds like a plan?:)

[gwynne]

I'm not sure I follow - are you encountering an issue on the Fluent side, something beyond the inarguably annoying fact that the UUIDs don't usually display properly if you look at the database directly?

[advanc3dUA]

I'm not sure I follow - are you encountering an issue on the Fluent side, something beyond the inarguably annoying fact that the UUIDs don't usually display properly if you look at the database directly?

UDIDs are saving wrong; they are wrong when I open the DB in DBeaver (DB viewer). Even in the _fluent_migrations table which I don't even know how to touch by myself: all added rows in it (my migrations) have wrong IDs. The UUIDs are stably wrong everywhere :).

[vzsg]

But they aren't "wrong", they're just in binary form.

As gwynne already mentioned, if you want to prettify it, you could use the HEX or even the BIN_TO_UUID function in your SQL queries in DBeaver. Your application will still behave correctly with them if you use UUID in your Swift code.

[gwynne]

It's also worth noting that with any of the SQL drivers (PostgreSQL, MySQL, SQLite), you're usually much better off using Int for your IDs rather than UUID - it's much smaller, much, MUCH faster, and has no meaningful drawbacks unless you expect to interoperate with a system which does use UUIDs for its identifiers. Fluent only defaults to using UUIDs because of MongoDB support, which Fluent 5 Will Not Have™. You can switch over by changing your @ID var id: UUID? (or @ID(key: .id) etc.) properties to @ID(custom: .id) var id: Int? in your models and using .field(.id, .int, .identifier(auto: true)) instead of .id() in your migrations (this also gives you the use of auto-increment primary keys, which is another minor speed win).