Rails 4 Migration: Mysql2::Error: Data too long for column 'xxxx'

Question

Here is my schema.rb

  create_table "users", force: true do |t|
    t.string   "name",       limit: 6
    t.string   "email"
    t.datetime "created_at"
    t.datetime "updated_at"
  end

I set the limit fo string for the column "name".

Then, in console:

 user = User.new(name:"1234567890",email:"[email protected]")
 user.save!

It raised the error:

ActiveRecord::StatementInvalid: Mysql2::Error: Data too long for column 'name' at row 1: INSERT INTO `users` (`created_at`, `email`, `name`, `updated_at`) VALUES ('2014-06-19 15:08:15', '[email protected]', '1234567890', '2014-06-19 15:08:15')

But, when I switched to rails 3.

I found it truncated the string "1234567890" automatically, and inserted "123456" into database without error.

Is there anything about this has been removed in rails 4?

Should I add some truncate functions in the model by myself? Thanks!

Answer 1

What you're seeing is a difference in MySQL, not Rails. By default, MySQL will truncate data that's too long rather than throwing an error. If you set MySQL to strict mode, it will throw errors instead of silently truncating data.

With version 4, Rails turns on strict mode by default. That's why you're seeing different behavior with Rails 3. This is the behavior you probably want. Silently truncating data is almost always bad and can lead to very confusing behavior for users.

If you really want to truncate the data, you could turn off strict mode or use a before filter:

before_save :truncate_username
def truncate_username
  self.username = username.slice(0, 6)
end

Answer 2

I stumbled across this article coincidently written only a few days ago.

The main point of interest is this:

...I recently upgraded from rails 3.2 to rails 4.0. They implemented a major change with ActiveRecords that I can find no mention of any where except in the source and change log.

• mysql and mysql2 connections will set SQL_MODE=STRICT_ALL_TABLES by default to avoid silent data loss. This can be disabled by specifying strict: false in your database.yml.

This would seem to explain why you stopped receiving the error when you reverted back to Rails 3. You can see this in the options of the MySQL connection adapter module, and it looks like it was added way back in May 2012 for the 4.1.2 release candidate (if I'm reading the tags correctly).

This person solved their issue by ...[fixing] code to either have the proper field lengths, or manually truncate the data....

In your case, you might be able to solve your problem in Rails 4 simply by adding strict: false in your database.yml. If you want to customize how the data is truncated, I agree with JKen13579's suggestion about the before_save callback. Otherwise, from what I can see, it appears to truncate the right-most characters, so if that is sufficient, you can probably get away with the default truncation behaviour.