Default engine `ENGINE=InnoDB` is no longer dumped to make schema more agnostic by kamipo · Pull Request #39365 · rails/rails

kamipo · 2020-05-20T11:50:49Z

5 years ago, I made dumping full table options at #17569, especially to
dump ENGINE=InnoDB ROW_FORMAT=DYNAMIC to use utf8mb4 with large key
prefix.

In that time, omitting the default engine ENGINE=InnoDB was not useful
since ROW_FORMAT=DYNAMIC always remains as long as using utf8mb4 with
large key prefix.

But now, MySQL 5.7.9 has finally changed the default row format to
DYNAMIC, utf8mb4 with large key prefix can be used without dumping the
default engine and the row format explicitly.

So now is a good time to make the default engine is omitted.

Before:

create_table "accounts", options: "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci", force: :cascade do |t|
end

After:

create_table "accounts", charset: "utf8mb4", collation: "utf8mb4_0900_ai_ci", force: :cascade do |t|
end

To entirely omit :options option to make schema agnostic, I've added
:charset and :collation table options to exclude CHARSET and
COLLATE from :options.

Fixes #26209.
Closes #29472.

See also #33608, #33853, and #34742.

…e agnostic 5 years ago, I made dumping full table options at rails#17569, especially to dump `ENGINE=InnoDB ROW_FORMAT=DYNAMIC` to use utf8mb4 with large key prefix. In that time, omitting the default engine `ENGINE=InnoDB` was not useful since `ROW_FORMAT=DYNAMIC` always remains as long as using utf8mb4 with large key prefix. But now, MySQL 5.7.9 has finally changed the default row format to DYNAMIC, utf8mb4 with large key prefix can be used without dumping the default engine and the row format explicitly. So now is a good time to make the default engine is omitted. Before: ```ruby create_table "accounts", options: "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci", force: :cascade do |t| end ``` After: ```ruby create_table "accounts", charset: "utf8mb4", collation: "utf8mb4_0900_ai_ci", force: :cascade do |t| end ``` To entirely omit `:options` option to make schema agnostic, I've added `:charset` and `:collation` table options to exclude `CHARSET` and `COLLATE` from `:options`. Fixes rails#26209. Closes rails#29472. See also rails#33608, rails#33853, and rails#34742.

Otherwise we cannot handle the same name options for both column and table (e.g. `:comment`, `:charset`, `:collation`).

Follow rails/rails#39365

This pull request restores Schema Dumper behavior changed by rsim#2019 because rails/rails#39365 does not intend to dump the default primary key configuration. Related to rails/rails#39365 Follow up rsim#2019

This pull request restores Schema Dumper behavior changed by rsim#2019 because rails/rails#39365 does not intend to dump the default primary key configuration. To avoid hash creation every time, it has been frozen and saved as a constant. Related to rails/rails#39365 Follow up rsim#2019 Refer http://code.jeremyevans.net/presentations/rubykaigi2019/index.html#58

table options are parsed charset/collation in 6.1.0. related: rails/rails#39365 ``` // before // < 6.1.0 create_table "users", options: "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci" do |t| end // after // >= 6.1.0 create_table "users", charset: "utf8mb4", collation: "utf8mb4_0900_ai_ci" do |t| end ``` If `dump_without_table_options` is disabled, ridgepole doesn't dump charset/collation.

In 6.1, table options are parsed to charset/collation. rails/rails#39365 The following cases should be same result. ``` create_table "items", options: "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci" do |t| end create_table "items", charset: "utf8mb4", collation: "utf8mb4_0900_ai_ci" do |t| end ``` To solve this I handled that if new-style `charset/collation` given, it converts to old-style `options`. By doing so, I solved the problem by a little changes. https://github.com/rails/rails/pull/39365/files#diff-868f1dccfcbed26a288bf9f3fd8a39c863a4413ab0075e12b6805d9798f556d1R427

rails/rails#39365 introduced new-style id options like the followings. ``` // < 6.1 old-style create_table(:items, id: :bigint, unsigned: true) {} // >= 6.1 new-style create_table(:items, id: { type: :bigint, unsigned: true }) {} ``` To compare those styles to get diff, I handled that I normalizes id options from those styles.

table options are parsed charset/collation in 6.1.0. related: rails/rails#39365 ``` // before // < 6.1.0 create_table "users", options: "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci" do |t| end // after // >= 6.1.0 create_table "users", charset: "utf8mb4", collation: "utf8mb4_0900_ai_ci" do |t| end ``` If `dump_without_table_options` is disabled, ridgepole doesn't dump charset/collation.

In 6.1, table options are parsed to charset/collation. rails/rails#39365 The following cases should be same result. ``` create_table "items", options: "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci" do |t| end create_table "items", charset: "utf8mb4", collation: "utf8mb4_0900_ai_ci" do |t| end ``` To solve this I handled that if new-style `charset/collation` given, it converts to old-style `options`. By doing so, I solved the problem by a little changes. https://github.com/rails/rails/pull/39365/files#diff-868f1dccfcbed26a288bf9f3fd8a39c863a4413ab0075e12b6805d9798f556d1R427

rails/rails#39365 introduced new-style id options like the followings. ``` // < 6.1 old-style create_table(:items, id: :bigint, unsigned: true) {} // >= 6.1 new-style create_table(:items, id: { type: :bigint, unsigned: true }) {} ``` To compare those styles to get diff, I handled that I normalizes id options from those styles.

table options are parsed charset/collation in 6.1.0. related: rails/rails#39365 ``` // before // < 6.1.0 create_table "users", options: "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci" do |t| end // after // >= 6.1.0 create_table "users", charset: "utf8mb4", collation: "utf8mb4_0900_ai_ci" do |t| end ``` If `dump_without_table_options` is disabled, ridgepole doesn't dump charset/collation.

In 6.1, table options are parsed to charset/collation. rails/rails#39365 The following cases should be same result. ``` create_table "items", options: "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci" do |t| end create_table "items", charset: "utf8mb4", collation: "utf8mb4_0900_ai_ci" do |t| end ``` To solve this I handled that if new-style `charset/collation` given, it converts to old-style `options`. By doing so, I solved the problem by a little changes. https://github.com/rails/rails/pull/39365/files#diff-868f1dccfcbed26a288bf9f3fd8a39c863a4413ab0075e12b6805d9798f556d1R427

rails/rails#39365 introduced new-style id options like the followings. ``` // < 6.1 old-style create_table(:items, id: :bigint, unsigned: true) {} // >= 6.1 new-style create_table(:items, id: { type: :bigint, unsigned: true }) {} ``` To compare those styles to get diff, I handled that I normalizes id options from those styles.

table options are parsed charset/collation in 6.1.0. related: rails/rails#39365 ``` // before // < 6.1.0 create_table "users", options: "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci" do |t| end // after // >= 6.1.0 create_table "users", charset: "utf8mb4", collation: "utf8mb4_0900_ai_ci" do |t| end ``` If `dump_without_table_options` is disabled, ridgepole doesn't dump charset/collation.

In 6.1, table options are parsed to charset/collation. rails/rails#39365 The following cases should be same result. ``` create_table "items", options: "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci" do |t| end create_table "items", charset: "utf8mb4", collation: "utf8mb4_0900_ai_ci" do |t| end ``` To solve this I handled that if new-style `charset/collation` given, it converts to old-style `options`. By doing so, I solved the problem by a little changes. https://github.com/rails/rails/pull/39365/files#diff-868f1dccfcbed26a288bf9f3fd8a39c863a4413ab0075e12b6805d9798f556d1R427

rails/rails#39365 introduced new-style id options like the followings. ``` // < 6.1 old-style create_table(:items, id: :bigint, unsigned: true) {} // >= 6.1 new-style create_table(:items, id: { type: :bigint, unsigned: true }) {} ``` To compare those styles to get diff, I handled that I normalizes id options from those styles.

table options are parsed charset/collation in 6.1.0. related: rails/rails#39365 ``` // before // < 6.1.0 create_table "users", options: "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci" do |t| end // after // >= 6.1.0 create_table "users", charset: "utf8mb4", collation: "utf8mb4_0900_ai_ci" do |t| end ``` If `dump_without_table_options` is disabled, ridgepole doesn't dump charset/collation.

In 6.1, table options are parsed to charset/collation. rails/rails#39365 The following cases should be same result. ``` create_table "items", options: "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci" do |t| end create_table "items", charset: "utf8mb4", collation: "utf8mb4_0900_ai_ci" do |t| end ``` To solve this I handled that if new-style `charset/collation` given, it converts to old-style `options`. By doing so, I solved the problem by a little changes. https://github.com/rails/rails/pull/39365/files#diff-868f1dccfcbed26a288bf9f3fd8a39c863a4413ab0075e12b6805d9798f556d1R427

rails/rails#39365 introduced new-style id options like the followings. ``` // < 6.1 old-style create_table(:items, id: :bigint, unsigned: true) {} // >= 6.1 new-style create_table(:items, id: { type: :bigint, unsigned: true }) {} ``` To compare those styles to get diff, I handled that I normalizes id options from those styles.

table options are parsed charset/collation in 6.1.0. related: rails/rails#39365 ``` // before // < 6.1.0 create_table "users", options: "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci" do |t| end // after // >= 6.1.0 create_table "users", charset: "utf8mb4", collation: "utf8mb4_0900_ai_ci" do |t| end ``` If `dump_without_table_options` is disabled, ridgepole doesn't dump charset/collation.

In 6.1, table options are parsed to charset/collation. rails/rails#39365 The following cases should be same result. ``` create_table "items", options: "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci" do |t| end create_table "items", charset: "utf8mb4", collation: "utf8mb4_0900_ai_ci" do |t| end ``` To solve this I handled that if new-style `charset/collation` given, it converts to old-style `options`. By doing so, I solved the problem by a little changes. https://github.com/rails/rails/pull/39365/files#diff-868f1dccfcbed26a288bf9f3fd8a39c863a4413ab0075e12b6805d9798f556d1R427

rails/rails#39365 introduced new-style id options like the followings. ``` // < 6.1 old-style create_table(:items, id: :bigint, unsigned: true) {} // >= 6.1 new-style create_table(:items, id: { type: :bigint, unsigned: true }) {} ``` To compare those styles to get diff, I handled that I normalizes id options from those styles.

table options are parsed charset/collation in 6.1.0. related: rails/rails#39365 ``` // before // < 6.1.0 create_table "users", options: "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci" do |t| end // after // >= 6.1.0 create_table "users", charset: "utf8mb4", collation: "utf8mb4_0900_ai_ci" do |t| end ``` If `dump_without_table_options` is disabled, ridgepole doesn't dump charset/collation.

In 6.1, table options are parsed to charset/collation. rails/rails#39365 The following cases should be same result. ``` create_table "items", options: "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci" do |t| end create_table "items", charset: "utf8mb4", collation: "utf8mb4_0900_ai_ci" do |t| end ``` To solve this I handled that if new-style `charset/collation` given, it converts to old-style `options`. By doing so, I solved the problem by a little changes. https://github.com/rails/rails/pull/39365/files#diff-868f1dccfcbed26a288bf9f3fd8a39c863a4413ab0075e12b6805d9798f556d1R427

rails/rails#39365 introduced new-style id options like the followings. ``` // < 6.1 old-style create_table(:items, id: :bigint, unsigned: true) {} // >= 6.1 new-style create_table(:items, id: { type: :bigint, unsigned: true }) {} ``` To compare those styles to get diff, I handled that I normalizes id options from those styles.

table options are parsed charset/collation in 6.1.0. related: rails/rails#39365 ``` // before // < 6.1.0 create_table "users", options: "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci" do |t| end // after // >= 6.1.0 create_table "users", charset: "utf8mb4", collation: "utf8mb4_0900_ai_ci" do |t| end ``` If `dump_without_table_options` is disabled, ridgepole doesn't dump charset/collation.

In 6.1, table options are parsed to charset/collation. rails/rails#39365 The following cases should be same result. ``` create_table "items", options: "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci" do |t| end create_table "items", charset: "utf8mb4", collation: "utf8mb4_0900_ai_ci" do |t| end ``` To solve this I handled that if new-style `charset/collation` given, it converts to old-style `options`. By doing so, I solved the problem by a little changes. https://github.com/rails/rails/pull/39365/files#diff-868f1dccfcbed26a288bf9f3fd8a39c863a4413ab0075e12b6805d9798f556d1R427

rails/rails#39365 introduced new-style id options like the followings. ``` // < 6.1 old-style create_table(:items, id: :bigint, unsigned: true) {} // >= 6.1 new-style create_table(:items, id: { type: :bigint, unsigned: true }) {} ``` To compare those styles to get diff, I handled that I normalizes id options from those styles.

table options are parsed charset/collation in 6.1.0. related: rails/rails#39365 ``` // before // < 6.1.0 create_table "users", options: "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci" do |t| end // after // >= 6.1.0 create_table "users", charset: "utf8mb4", collation: "utf8mb4_0900_ai_ci" do |t| end ``` If `dump_without_table_options` is disabled, ridgepole doesn't dump charset/collation.

In 6.1, table options are parsed to charset/collation. rails/rails#39365 The following cases should be same result. ``` create_table "items", options: "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci" do |t| end create_table "items", charset: "utf8mb4", collation: "utf8mb4_0900_ai_ci" do |t| end ``` To solve this I handled that if new-style `charset/collation` given, it converts to old-style `options`. By doing so, I solved the problem by a little changes. https://github.com/rails/rails/pull/39365/files#diff-868f1dccfcbed26a288bf9f3fd8a39c863a4413ab0075e12b6805d9798f556d1R427

rails/rails#39365 introduced new-style id options like the followings. ``` // < 6.1 old-style create_table(:items, id: :bigint, unsigned: true) {} // >= 6.1 new-style create_table(:items, id: { type: :bigint, unsigned: true }) {} ``` To compare those styles to get diff, I handled that I normalizes id options from those styles.

`ridgepole` doesn't know indexes that created by automatically. So checking to avoid those. https://github.com/ridgepole/ridgepole/blob/4a18cef152027d1d02a6f7bb004c9c32d09bb581/lib/ridgepole/dsl_parser.rb#L33-L46 This check depends on `ENGINE=InnoDB` option. But, since Rails 6.1, Rails doesn't output it by default. rails/rails#39365 So the current check doesn't work as expected. This PR fixed to check foreign key when table options is nil or InnoDB.

rails-bot bot added the activerecord label May 20, 2020

kamipo force-pushed the table_options branch from 4ec801d to 380a025 Compare May 23, 2020 22:53

Separate primary key column options from table options

859681e

Otherwise we cannot handle the same name options for both column and table (e.g. `:comment`, `:charset`, `:collation`).

kamipo force-pushed the table_options branch from 380a025 to 859681e Compare May 23, 2020 23:16

kamipo merged commit e7317a2 into rails:master May 24, 2020

kamipo deleted the table_options branch May 24, 2020 00:27

koic mentioned this pull request May 24, 2020

Follow rails/rails#39365 rsim/oracle-enhanced#2019

Merged

yahonda added a commit to rsim/oracle-enhanced that referenced this pull request May 25, 2020

Merge pull request #2019 from koic/follow_rails_39365

49ba348

Follow rails/rails#39365

yahonda mentioned this pull request Sep 10, 2020

Restore Schema Dumper behavior changed by #2019 rsim/oracle-enhanced#2047

Merged

ritikesh mentioned this pull request Nov 16, 2020

issue with schema:load when a MySQL table definition contains partitions #40631

Closed

alpaca-tc mentioned this pull request Dec 27, 2020

Support AR 6.1.0 ridgepole/ridgepole#323

Merged

Bialogs mentioned this pull request Jan 6, 2021

Upgrade to Rails 6.1 SaraAlert/SaraAlert#618

Merged

4 tasks

This was referenced Feb 5, 2021

Migrations for new received order state (s. #779) foodcoops/foodsoft#804

Closed

Closes #805 - updated db/schema.rb to Rails 5.2 dump foodcoops/foodsoft#806

Merged

y-yagi mentioned this pull request Aug 24, 2025

Fix checking foreign key without index ridgepole/ridgepole#571

Merged

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Comments

Default engine `ENGINE=InnoDB` is no longer dumped to make schema more agnostic#39365

Default engine `ENGINE=InnoDB` is no longer dumped to make schema more agnostic#39365
kamipo merged 2 commits intorails:masterfrom
kamipo:table_options

kamipo commented May 20, 2020 •

edited

Loading

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

1 participant

Comments

Conversation

kamipo commented May 20, 2020 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

1 participant

kamipo commented May 20, 2020 •

edited

Loading