Schema.db "Unknown type 'uuid' for column 'id'" when using MySQL

[Shuttleu]

Steps to reproduce

When creating a new rails app with nothing but using the MySQL adaptor, creating a migration that uses UUID as the primary key type, does not allow schema.db to be generated.

rails new app --database=mysql

Migration

class CreateUsers < ActiveRecord::Migration[7.0]
    def change
        create_table :users, id: :uuid, default: -> { "UUID()" } do |t|
            t.string :name
      
            t.timestamps
        end
    end
end

Expected behavior

schema.db should have the table definitions for the Users table

Actual behavior

Instead of table definitions, it has this comment instead

# Could not dump table "users" because of following StandardError
#   Unknown type 'uuid' for column 'id'

The database is created correctly, and works as expected in use.

System configuration

Rails version: Rails 7.0.3.1

Ruby version: Ruby 3.0.2

MySQL server version: MariaDB 10.7.4

[fatkodima]

Thanks for reporting, I am looking into this.

[fatkodima]

Because of:

1. inability to get the generated id on record creation (mysql does not support RETURNING) - https://github.com/rails/rails/issues/45795
2. different types needed for uuid (uuid for mysql and binary(16) for mariadb)
3. different support for different versions (older mariadb does not support uuid)
4. inability to define DEFAULT as a function call for older db versions (like DEFAULT uuid())
5. mysql vs mariadb accepts differently formatted uuid strings (mariadb supports one formats, while the mysql does not support all of them, but supports others)

Thats why I think uuid support is not added for the mysql adapter, and is hard to add. Probably you should use the existing workarounds, like https://stackoverflow.com/questions/43222114/rails-5-mysql-uuid. Unless I am missing something.

[Shuttleu]

Because of:

1. inability to get the generated id on record creation (mysql does not support RETURNING) - Create returns "id: 0" when id type is set to UUID when using MySQL #45795

I believe MariaDB 10.5 added the INSERT...RETURNING statement (I don't believe MySQL has support for this though)

Maybe we can query the database for the row we just inserted, and return that rather than relying on RETURNING?

Or if we are running MariaDB > 10.5, use the INSERT...RETURNING statement and if not have the behaviour we currently have?

2. different types needed for uuid (uuid for mysql and binary(16) for mariadb)

MariaDB supports uuid with no issues as of 10.7

3. different support for different versions (older mariadb does not support uuid)

Obviously nothing we can do in Rails 7.0 as we need to support whatever servers are currently supported

4. inability to define DEFAULT as a function call for older db versions (like DEFAULT uuid())

Not sure I understand this one, I had no issues defining a default function call for MariaDB.

Is this a MySQL issue? I know you'd have to put DEFULT (UUID()) for MySQL whereas you can use DEFAULT UUID() or DEFAULT(UUID()) for MariaDB.

Can we not do what I did above?

5. mysql vs mariadb accepts differently formatted uuid strings (mariadb supports one formats, while the mysql does not support all of them, but supports others)

Both servers support uuid types and UUID() functions, surely if we're using the built in UUID() calls, we shouldn't have any issues?

I guess it could be an issue if the user is giving their own UUID's (Generated client side and saved in the DB)

Thats why I think uuid support is not added for the mysql adapter, and is hard to add. Probably you should use the existing workarounds, like https://stackoverflow.com/questions/43222114/rails-5-mysql-uuid. Unless I am missing something.

I will use the workarounds, although with what I currently have, the only real issue is that when creating a new record, I then have to query the DB to get the correct ID.

It seems that if newer server versions were required for both MySQL and MariaDB, these wouldn't be an issue.

So maybe this can be something for a future version of Rails when the newer server versions are more mainstream?

[fatkodima]

I believe MariaDB 10.5 added the INSERT...RETURNING statement (I don't believe MySQL has support for this though)

Yes, MySQL does not have this as far as I remember.

Maybe we can query the database for the row we just inserted, and return that rather than relying on RETURNING?

Am I missing something or how we can do this without the id?

MariaDB supports uuid with no issues as of 10.7

Yes, sorry, I mean for MySQL we need binary(16).

Is this a MySQL issue? I know you'd have to put DEFULT (UUID()) for MySQL whereas you can use DEFAULT UUID() or DEFAULT(UUID()) for MariaDB.

Yes, I mean parens. For older versions MariaDB or MySQL or both does not support functions as a DEFAULT. For newer, seems like supported, but we need to wrap in ().

I guess it could be an issue if the user is giving their own UUID's (Generated client side and saved in the DB)

Yes. MySQL supports values in one set of formats for uuids, but MariaDB in others and do not support some formats from MySQL. Omg.

It seems that if newer server versions were required for both MySQL and MariaDB, these wouldn't be an issue. So maybe this can be something for a future version of Rails when the newer server versions are more mainstream?

Probably, but I am not sure 😄 Needs more investigation.

[Shuttleu]

Maybe we can query the database for the row we just inserted, and return that rather than relying on RETURNING?

Am I missing something or how we can do this without the id?

I may have not thought that bit through. Although maybe if the ID is set to type uuid and the server is MariaDB >= 10.7, we set the DEFAULT UUID() when creating the table and use RETURNING. Otherwise we dont set a DEFAULT, but instead set a temporary variable when we run a INSERT

SET @uuid = UUID();
INSERT INTO `users` (`id`, `name`) VALUES (@uuid, "Foo");
SELECT * FROM `users` WHERE `id`=@uuid;

MariaDB supports uuid with no issues as of 10.7

Yes, sorry, I mean for MySQL we need binary(16).

Oh, I didn't realise that MySQL doesn't support uuid (I tend to only work with MariaDB as it had some features that I required for a project back in the day)

I don't actually have MySQL installed to see how it would react, but I imagine the solution would be to set the id to binary(16) altering the queries above to

SET @uuid = UUID_TO_BIN(UUID());
INSERT INTO `users` (`id`, `name`) VALUES (@uuid, "Foo");
SELECT * FROM `users` WHERE `id`=@uuid;

It seems that if newer server versions were required for both MySQL and MariaDB, these wouldn't be an issue. So maybe this can be something for a future version of Rails when the newer server versions are more mainstream?

Probably, but I am not sure 😄 Needs more investigation.

It will obviously get rather messy with there being three potential options: MySQL < 8 || MariaDB < 10.7 Do nothing as we are now MariaDB >= 10.7 Set DEFAULT UUID() and use RETURNING MySQL >= 8 Set id type to binary(16), use UUID_TO_BIN() in SELECT queries and set a temporary variable when running INSERT queries

If those three scenarios were to be implemented, that would prevent us from having to exclude older server versions too 🤞

EDIT: in regards to getting the last inserted id to select it from the DB when inserting a new row, how do we do it with AI integers? LAST_INSERT_ID()?

EDIT 2: Never mind, found the bit responsible for getting the last ID, it's from the mysql2 gem, specifically

/* call-seq:
 *    client.last_id
 *
 * Returns the value generated for an AUTO_INCREMENT column by the previous INSERT or UPDATE
 * statement.
 */
static VALUE rb_mysql_client_last_id(VALUE self) {
  GET_CLIENT(self);
  REQUIRE_CONNECTED(wrapper);
  return ULL2NUM(mysql_insert_id(wrapper->client));
}

This uses the libmysqlclient C library which only returns an id for an INSERT or UPDATE statement if the id has the AUTO_INCREMENT attribute https://mariadb.com/kb/en/mysql_insert_id/ https://dev.mysql.com/doc/c-api/8.0/en/mysql-insert-id.html

[pnegri]

We currently have UUID working here at Iugu (Payment Company at Brazil) for MySQL 6 to 8. We could share our code and try to create a PR for full UUID support for MySQL (Rails 4 to 6). Used at production with tables with more than 200 million records.

[fatkodima]

@pnegri Please do 👍

[pnegri]

Created draft and shared code at: #45847

[yahonda]

We don't take feature requests on the issue tracker, we reserve it for issues. Would you start a discussion at https://discuss.rubyonrails.org/ or create a pull request? Thanks.

Refer to https://github.com/rails/rails/issues/45847#issuecomment-1240340180