27

I need to change a column from type TEXT to MEDIUMTEXT. As per this discussion: How to store long text to MySql DB using Rails?, I will need to specify a limit on size as half of the allowable range for that particular TEXT type in MySQL. However, what I am experiencing is that even by halving, I'm still getting the type that's one level above what I need. Does anyone have a better doc where this was documented or know why this is happening? Thanks!

Community
  • 1
  • 1
gtr32x
  • 2,033
  • 4
  • 20
  • 32
  • Is having a field that's "too big" really going to be the end of the world? The overhead is a couple of bytes per row at the absolute maximum. – tadman Mar 12 '12 at 16:49
  • It's not. I just would like to understand further about how Rails handle this issue though. – gtr32x Mar 12 '12 at 17:32
  • If you could be more specific about what you want and what you're getting and how you got it, it would be easier to diagnose the problem. – tadman Mar 12 '12 at 18:56

2 Answers2

51

See this answer: 'Rails 3 Migration with longtext'

The reason why the limit values you're inputting are being ignored is due to how MySQL works. It has four text types, each with their own size limit:

  • TINYTEXT - 256 bytes
  • TEXT - 65,535 bytes
  • MEDIUMTEXT - 16,777,215 bytes
  • LONGTEXT - 4,294,967,295 bytes

A text column needs to be one of those four types. You can't specify a custom length for these types in MySQL.

So if you set a limit on a :text type column, Rails will automatically pick the smallest of those types that can accommodate that value, silently rounding up the limiting value you inputted to one of those four limits above.

Example:

t.text :example_text_field, limit: 20

will produce a TINYTEXT field with a limit of 256 bytes, whereas

t.text :example_text_field, limit: 16.megabytes - 1

will produce a MEDIUMTEXT field with a limit of 16,777,215 bytes.

Update

For the shake of the question: "I need to change a column"

change_column :example_table, :example_text_field, :text, limit: 16.megabytes - 1
Stu Blair
  • 1,323
  • 15
  • 24
12
change_column :example_table, :example_text_field, :mediumtext

Example:

def change
   change_column :users, :bio, :mediumtext
end
Abel
  • 3,989
  • 32
  • 31
araslanov_e
  • 309
  • 2
  • 5
  • 2
    Can you provide more context? – Nico Haase May 14 '18 at 12:35
  • https://github.com/rails/rails/blob/master/activerecord/lib/active_record/connection_adapters/abstract_mysql_adapter.rb#L567 – araslanov_e Feb 21 '19 at 11:04
  • 1
    A little more future proof with the commit hash instead of "master": https://github.com/rails/rails/blob/5220c6e/activerecord/lib/active_record/connection_adapters/abstract_mysql_adapter.rb#L536 – LeEnno Jan 15 '20 at 16:01