Interesting.. I believe UTF-8 is the default for ActiveRecord / rails so I'm guessing this is probably rarely encountered. It seems that 767 bytes is the standard limit for a column using InnoDB. My question is whether the option string you are applying here will only be executed against databases with that setup or whether this could potentially cause errors if attempted to execute against other databases if we added it to the standard migration?

FWIW, my team ran into related issues. We had a database incorrectly configured with a default charset of latin1 and so the versions table got set to latin1 and we started seeing exceptions in production with some Japanese characters. I'm not sure what the preferred solution should be from the paper_trail standpoint, but at the least, it might be worth bringing up the issue of charsets in the README and perhaps provide some options for passing through your desired charset.

Default database character sets

• MySQL: latin1
  • ".. the default MySQL character set and collation .. [is] .. (latin1, latin1_swedish_ci)"
    (https://dev.mysql.com/doc/refman/5.7/en/charset-applications.html)
• Postgres: UTF-8
  • In postgres, the default is probably UTF-8 unless your system is configured with a custom locale. (http://www.postgresql.org/docs/9.4/static/multibyte.html)

Solution

Fix: ... Force CHARSET=utf8 on your versions migration

Makes sense to me, but Ben makes a good point:

[will] the option string you are applying here will only be executed against databases with that setup ..?

Unfortunately, no. We can't simply copy the given solution into our migration template (paper_trail/lib/generators/paper_trail/templates/create_versions.rb) because the CHARSET option don't apply to, e.g. postgres.

create table derp () DEFAULT CHARSET=utf8;
-- ERROR:  syntax error at or near "DEFAULT"

So, we'll have to have some conditional logic. Maybe something like:

def options
  if ActiveRecord::Base.connection.is_a?(ActiveRecord::ConnectionAdapters::Mysql2Adapter)
    'ROW_FORMAT=DYNAMIC ENGINE=InnoDB DEFAULT CHARSET=utf8'
  else
    ''
  end
end

I've never had to write something like the above, so I have no idea if it's the best implementation.

Fixed in #681. Thanks @gczh, Ben @batter, and Ed @ecopony. Gotta love encodings, huh 😬

I am still seeing this bug (using 5.0.1), wasn't using utf8mb4 part of the problem in the first place?

I am still seeing this bug (using 5.0.1),

Hi Ian. You're seeing the "Specified key was too long; max key length is 767 bytes" in 5.0.1? When do you get the error, when you're running the migration that creates the versions table?

wasn't using utf8mb4 part of the problem in the first place?

Possibly? But we must use utf8mb4 because Ruby strings are UTF-8 by default, and utf8mb4 is the only compatible CHARSET in MySQL, right?

According to this SO answer:

Mysql assumes worst case for the number of bytes per character in the string. For .. 'utf8' .. that's 3 bytes per character .. For .. 'utf8mb4' .. it's 4 bytes per character ..

It goes on to say that the maximum length of a utf8mb4 varchar key is 191 chars.

as 191 * 4 = 764 < 767

So, assuming we can reproduce this issue, one solution may be to limit item_type to 191 chars.

Yes— specifically the whole output

-- create_table(:versions, {:options=>"ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci"})
   -> 0.0641s
-- add_index(:versions, [:item_type, :item_id])
rake aborted!
StandardError: An error has occurred, all later migrations canceled:

Mysql2::Error: Specified key was too long; max key length is 767 bytes: CREATE  INDEX `index_versions_on_item_type_and_item_id`  ON `versions` (`item_type`, `item_id`)

As for the column type— utf8 might be enough— utf8mb4 is a superset of UTF-8, which allows for new characters like emojis. Unless you are expecting those in here (I haven't dug deep into this codebase, just working on a small design)

Ian, it all works for me in a new app with rails 5.0.0.rc1, mysql 5.7.12, ruby 2.3.1. What are you using?

Ian, I haven't heard back from you, so I'm going to close this issue again. If anyone knows how to reproduce Ian's issue, please let us know, thanks!

I am still have this bug with mariadb 10.1
mysql Ver 15.1 Distrib 10.1.14-MariaDB, for Linux (x86_64) using readline 5.1

Oh hey! Sorry, missed the notification.

Rails 4.2.1
MySQL 5.6.23 (InnoDB / utf8 for all other tables)
Ruby 2.2.2

Well, it wasn't easy, but I was able to reproduce this on a VM with ubuntu 16.04, rails 4.2.6, mysql 5.6.16, and ruby 2.3.0. Re-opening.

.. one solution may be to limit item_type to 191 chars.

I'm going to try this.

.. one solution may be to limit item_type to 191 chars.

I'm going to try this.

Seems to work.

Fix worked for me. Thanks!

Again, why are we forcing utf8mb4 when utf8 should also correct this? Unless item_type or item_id need emoji support— I cannot think of a reason to use utf8mb4 here.

why are we forcing utf8mb4 when utf8 should also correct this? Unless item_type or item_id need emoji support— I cannot think of a reason to use utf8mb4 here.

Yes, we want PT to have support for emoji (and other 4-byte chars). utf8mb4 is the only encoding in MySQL that matches ruby's default encoding. If your app needs a different encoding, you can edit your migration.

We've run into this and unless you change tables that store user input to utf8mb4, you get exceptions and/or data loss. Phones have a lot of 4byte characters easily accessible on the default keyboards, so users add smiley faces, etc. Since they are a valid part of UTF-8, they are not easy to remove before sticking data into the database.