db:schema:dump generates different code between development and test environments

[vitobotta]

Steps to reproduce

• Run db:schema:dump in the test and development environments

Expected behavior

The db/schema.rb generated should be the same for both environments and have valid syntax.

Actual behavior

In the test environment, the dump or migrate tasks generate create_table blocks like this:

create_table "active_storage_attachments", options: "ENGINE=InnoDB DEFAULT CHARSET=utf8", force: :cascade do |t|
    t.string "name", null: false
    t.string "record_type", null: false
    t.bigint "record_id", null: false
    t.bigint "blob_id", null: false
    t.datetime "created_at", null: false
    t.index ["blob_id"], name: "index_active_storage_attachments_on_blob_id"
    t.index ["record_type", "record_id", "name", "blob_id"], name: "index_active_storage_attachments_uniqueness", unique: true
  end

as you can see the options setting is set to the MySQL engine etc. This is valid syntax and works if I run db:schema:load.

However, in development in dump/migrate tasks generate something like this:

create_table "active_storage_attachments", options: "CREATE TABLE \"active_storage_attachments\" (\n  \"id\" bigint(20) NOT NULL AUTO_INCREMENT,\n  \"name\" varchar(255) NOT NULL,\n  \"record_type\" varchar(255) NOT NULL,\n  \"record_id\" bigint(20) NOT NULL,\n  \"blob_id\" bigint(20) NOT NULL,\n  \"created_at\" datetime NOT NULL,\n  PRIMARY KEY (\"id\"),\n  UNIQUE KEY \"index_active_storage_attachments_uniqueness\" (\"record_type\",\"record_id\",\"name\",\"blob_id\"),\n  KEY \"index_active_storage_attachments_on_blob_id\" (\"blob_id\"),\n  CONSTRAINT \"fk_rails_c3b3935057\" FOREIGN KEY (\"blob_id\") REFERENCES \"active_storage_blobs\" (\"id\")\n)", force: :cascade do |t|
    t.string "name", null: false
    t.string "record_type", null: false
    t.bigint "record_id", null: false
    t.bigint "blob_id", null: false
    t.datetime "created_at", null: false
    t.index ["blob_id"], name: "index_active_storage_attachments_on_blob_id"
    t.index ["record_type", "record_id", "name", "blob_id"], name: "index_active_storage_attachments_uniqueness", unique: true
  end

It sets options to the full SQL statement required to create the table. This isn't valid syntax and db:schema:load fails.

System configuration

Rails version:

I've tried with both 6.0.1 and master.

Ruby version:

2.6.5

[vitobotta]

brianmario/mysql2#1078

[vitobotta]

I am starting to think this is something related to DigitalOcean. My development environment is in Kubernetes (I use Telepresence for development) and uses DigitalOcean's managed Kubernetes. I connected a container in the development environment to the test database running locally and the dump is correct. Not sure what to do next...

[pixeltrix]

@vitobotta when I just checked, it seems that SHOW CREATE TABLE <tablename> on managed Digital Ocean MySQL databases doesn't return any table options and that's confusing the table_options code in AbstractMysqlAdapter. Definitely a bug that needs fixing, though hopefully not too many people are using a managed database cluster for development work.

[vitobotta]

Hi @pixeltrix ! I was coming to update the issue because I looked into the source code and came to the same conclusion! :)

The issue doesn't affect deployments with existing databases but it would be good to fix.

Thanks a lot for the confirmation! :)

[theasteve]

@pixeltrix is this issue up for grabs? Would like the opportunity to work on a PR, if possible. I can see how the Digital Ocean MySQL databases not returning any tables options can cause issues with the table_options code.

https://github.com/rails/rails/blob/master/activerecord/lib/active_record/connection_adapters/abstract_mysql_adapter.rb#L409

[pixeltrix]

@theasteve yes, a PR would be appreciated 👍

[prashuchaudhary]

@pixeltrix i have created a PR to fix this issue

[pixeltrix]

@prashuchaudhary sorry for not noticing sooner - GitHub doesn't appear to send notifications when PR is made that mentions the issue I'm watching and I don't following the PR firehose day-to-day so didn't see it.

[kamipo]

Fixed by #37991.