MySQL: Support `:size` option to change text and blob size #35071

kamipo · 2019-01-28T18:37:09Z

In MySQL, the text column size is 65,535 bytes by default (1 GiB in
PostgreSQL). It is sometimes too short when people want to use a text
column, so they sometimes change the text size to mediumtext (16 MiB) or
longtext (4 GiB) by giving the limit option.

Unlike MySQL, PostgreSQL doesn't allow the limit option for a text
column (raises ERROR: type modifier is not allowed for type "text").
So limit: 4294967295 (longtext) couldn't be used in Action Text.

I've allowed changing text and blob size without giving the limit
option, it prevents that migration failure on PostgreSQL.

jeremy

Nice feature for Active Record migrations! And great to expand Action Text support to PostgreSQL. Changelog entries for both, too?

actiontext/test/dummy/db/migrate/2018052816_create_action_text_tables.rb

activerecord/lib/active_record/connection_adapters/mysql/schema_definitions.rb

activerecord/lib/active_record/connection_adapters/mysql/schema_dumper.rb

kamipo · 2019-01-28T19:32:13Z

hm... I've realized that limit: 16777215 is ignored on PostgreSQL adapter, so Action Text originally work on PostgreSQL.

rails/activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb

Lines 553 to 558 in 5a8f0c7

    
           when "text" 
        
             # PostgreSQL doesn't support limits on text columns. 
        
             # The hard limit is 1GB, according to section 8.3 in the manual. 
        
             case limit 
        
             when nil, 0..0x3fffffff; super(type) 
        
             else raise(ActiveRecordError, "The limit on text can be at most 1GB - 1byte.")

I think that using limit: 16777215 (mediumtext 16 MiB) not limit: 4294967295 (longtext 4 GiB) is to ignore the limit option on PostgreSQL adapter.
So how about changing mediumtext to longtext for MySQL? 16 MiB is still too shorter than PostgreSQL's limit (1 GiB).

kamipo · 2019-01-28T20:53:21Z

actiontext/db/migrate/201805281641_create_action_text_tables.rb

@@ -2,7 +2,7 @@ class CreateActionTextTables < ActiveRecord::Migration[6.0]
  def change
    create_table :action_text_rich_texts do |t|
      t.string     :name, null: false
-      t.text       :body, limit: 16777215
+      t.text       :body, size: :long


Changed from mediumtext (16 MiB) to longtext (4 GiB).

activerecord/lib/active_record/connection_adapters/mysql/schema_definitions.rb

In MySQL, the text column size is 65,535 bytes by default (1 GiB in PostgreSQL). It is sometimes too short when people want to use a text column, so they sometimes change the text size to mediumtext (16 MiB) or longtext (4 GiB) by giving the `limit` option. Unlike MySQL, PostgreSQL doesn't allow the `limit` option for a text column (raises ERROR: type modifier is not allowed for type "text"). So `limit: 4294967295` (longtext) couldn't be used in Action Text. I've allowed changing text and blob size without giving the `limit` option, it prevents that migration failure on PostgreSQL.

…ther options When I've added new `:size` option in rails#35071, I've found that invalid `:limit` and `:precision` raises `ActiveRecordError` unlike other invalid options. I think that is hard to distinguish argument errors and statement invalid errors since the `StatementInvalid` is a subclass of the `ActiveRecordError`. https://github.com/rails/rails/blob/c9e4c848eeeb8999b778fa1ae52185ca5537fffe/activerecord/lib/active_record/errors.rb#L103 ```ruby begin # execute any migration rescue ActiveRecord::StatementInvalid # statement invalid rescue ActiveRecord::ActiveRecordError, ArgumentError # `ActiveRecordError` except `StatementInvalid` is maybe an argument error end ``` I'd say this is the inconsistency worth fixing. Before: ```ruby add_column :items, :attr1, :binary, size: 10 # => ArgumentError add_column :items, :attr2, :decimal, scale: 10 # => ArgumentError add_column :items, :attr3, :integer, limit: 10 # => ActiveRecordError add_column :items, :attr4, :datetime, precision: 10 # => ActiveRecordError ``` After: ```ruby add_column :items, :attr1, :binary, size: 10 # => ArgumentError add_column :items, :attr2, :decimal, scale: 10 # => ArgumentError add_column :items, :attr3, :integer, limit: 10 # => ArgumentError add_column :items, :attr4, :datetime, precision: 10 # => ArgumentError ```

MySQL: Support `:size` option to change text and blob size rails/rails#35071

rails-bot bot added actiontext activerecord labels Jan 28, 2019

rafaelfranca approved these changes Jan 28, 2019

View reviewed changes

jeremy reviewed Jan 28, 2019

View reviewed changes

kamipo force-pushed the text_without_limit branch from a0af7d5 to 76b93c9 Compare January 28, 2019 20:43

kamipo commented Jan 28, 2019

View reviewed changes

activerecord/lib/active_record/connection_adapters/mysql/schema_definitions.rb Show resolved Hide resolved

kamipo changed the title ~~Allow using Action Text on PostgreSQL~~ MySQL: Support :size option to change text and blob size Jan 28, 2019

kamipo force-pushed the text_without_limit branch from 76b93c9 to 1745e90 Compare January 28, 2019 21:51

rails-bot bot added the actionmailbox label Jan 28, 2019

kamipo merged commit 8309cd2 into rails:master Jan 29, 2019

kamipo deleted the text_without_limit branch January 29, 2019 08:02

kamipo mentioned this pull request Apr 7, 2019

Raise ArgumentError for invalid :limit and :precision like as other options #35887

Merged

suketa added a commit to suketa/rails_sandbox that referenced this pull request Jul 27, 2019

try067

c5e28e8

MySQL: Support `:size` option to change text and blob size rails/rails#35071

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

MySQL: Support `:size` option to change text and blob size #35071

MySQL: Support `:size` option to change text and blob size #35071

kamipo commented Jan 28, 2019 •

edited

jeremy left a comment

kamipo commented Jan 28, 2019

kamipo Jan 28, 2019

MySQL: Support :size option to change text and blob size #35071

MySQL: Support :size option to change text and blob size #35071

Conversation

kamipo commented Jan 28, 2019 • edited

jeremy left a comment

Choose a reason for hiding this comment

kamipo commented Jan 28, 2019

kamipo Jan 28, 2019

Choose a reason for hiding this comment

MySQL: Support `:size` option to change text and blob size #35071

MySQL: Support `:size` option to change text and blob size #35071

kamipo commented Jan 28, 2019 •

edited