Rails5: Why is a migration breaking my production environment but not my test environment?

Question

I have a migration in rails that does the following:

class AddMissingIndexes < ActiveRecord::Migration[5.1]
  def change
    # Applications
    add_index :applications, :evid, length: { evid: 255 }
  end
end

This seems to run smoothly in my test environment

However when I run the migration in my production environment I get this error:

Mysql2::Error: Specified key was too long; max key length is 767 bytes: CREATE  INDEX `index_applications_on_evid`  ON `applications` (`evid`(255)) 

I'm trying to fix this issue using the second answer from this question

class AddMissingIndexes < ActiveRecord::Migration[5.1]
  def change
    # Applications
    add_index "applications", ["evid"], :name => :evid, :length => { :evid => 255 }
  end
end

However I want to make sure this works before making any further changes to the schema. So I need to be able to reproduce this error in my test environment.

Test environment:

+-------------------------+------------------+
| Variable_name           | Value            |
+-------------------------+------------------+
| innodb_version          | 5.5.62           |
| protocol_version        | 10               |
| slave_type_conversions  |                  |
| version                 | 5.5.62-0+deb8u1  |
| version_comment         | (Debian)         |
| version_compile_machine | x86_64           |
| version_compile_os      | debian-linux-gnu |
+-------------------------+------------------+

Production environment

+-------------------------+---------------------+
| Variable_name           | Value               |
+-------------------------+---------------------+
| innodb_version          | 5.6.40              |
| protocol_version        | 10                  |
| slave_type_conversions  |                     |
| version                 | 5.6.40-log          |
| version_comment         | Source distribution |
| version_compile_machine | x86_64              |
| version_compile_os      | Linux               |
+-------------------------+---------------------+

Using SHOW GLOBAL VARIABLES LIKE 'innodb_%'; I'm able to see my test environment database and this is what I found that my local db environment has the following variables like this:

Test Environment

innodb_file_format=Barracuda;
innodb_large_prefix=1;
innodb_file_per_table=1;
innodb_file_format_max=Barracuda;
innodb_strict_mode=1;
character_set_server='utf8mb4';

Production Environment

innodb_file_format=Antelope;
innodb_large_prefix=OFF;
innodb_file_per_table=ON;
innodb_file_format_max=Antelope;
innodb_strict_mode=OFF;
character_set_server='utf8mb4';

I tried to reproduce my production environment setting the variables going 1 at a time. But to no avail.

Answer 1

Run SHOW CREATE TABLE applications\G in both your test and production environment. I predict the difference will be:

Test:

CREATE TABLE `applications` (
  ...
  `evid` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Production:

CREATE TABLE `applications` (
  ...
  `evid` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

An index must be able to fit in 767 bytes, as the error says. The utf8 character set counts 3 bytes per character toward this limit, so 3*255 = 765, which fits.

Whereas utf8mb4 counts 4 bytes per character. 4*255 = 1020, which is too long.

You can index VARCHAR(191) when you use utf8mb4, to stay within 767 bytes.

Alternatively, you can use newer InnoDB row format to support index size up to 3072 bytes. See mysql change innodb_large_prefix

If you want to avoid these sorts of surprises, it's important to run the same version of MySQL in your testing and production, and make sure you make the MySQL config options as close as possible, and make sure the table definitions are identical.

---

Re your updated question with innodb config variables.

I see your production environment has settings that mean it is unable to define tables with the Barracuda file format, which means no DYNAMIC row format, which means no innodb_large_prefix.

You need to make the settings match your test environment, and then you probably need to rebuild your table so it's really in Barracuda format with DYNAMIC row format.

I also recommend (again) that you upgrade your test server to the same MySQL version you run in production.

Also compare other config settings, to see if there are other differences (besides those that are appropriate to be different from production, like innodb_buffer_pool_size).

You should also make sure you use the same version of other parts of your tech stack like Linux version, Ruby version, etc. It's a well-known source of project instability and schedule delays if you are surprised by version incompatibilities, if you fail to make your dev and test environments match your production environment.