CODEWITHSUNDEEP
mysqlruby-on-rails-4
David Moles
David Moles

Reputation: 51229

MySQL row size too large, but only in 5.7

When I use rake db:schema:load to create the following table on my laptop running MySQL 5.6.22, it loads fine:

  create_table "inv_audits", force: :cascade do |t|
    t.integer  "inv_node_id",         limit: 2,                         null: false
    t.integer  "inv_object_id",       limit: 4,                         null: false
    t.integer  "inv_version_id",      limit: 4,                         null: false
    t.integer  "inv_file_id",         limit: 4,                         null: false
    t.string   "url",                 limit: 16383
    t.string   "status",              limit: 18,    default: "unknown", null: false
    t.datetime "created",                                               null: false
    t.datetime "verified"
    t.datetime "modified"
    t.integer  "failed_size",         limit: 8,     default: 0,         null: false
    t.string   "failed_digest_value", limit: 255
    t.text     "note",                limit: 65535
  end

However, when I try to do the same thing on my desktop running 5.7.21, it fails with Mysql2::Error: Row size too large.

The generated SQL, from the trace, is:

CREATE TABLE `inv_audits` (
  `id`                  int(11) auto_increment PRIMARY KEY,
  `inv_node_id`         smallint                      NOT NULL,
  `inv_object_id`       int(11)                       NOT NULL,
  `inv_version_id`      int(11)                       NOT NULL,
  `inv_file_id`         int(11)                       NOT NULL,
  `url`                 varchar(16383),
  `status`              varchar(18) DEFAULT 'unknown' NOT NULL,
  `created`             datetime                      NOT NULL,
  `verified`            datetime,
  `modified`            datetime,
  `failed_size`         bigint DEFAULT 0              NOT NULL,
  `failed_digest_value` varchar(255),
  `note`                text
) ENGINE=InnoDB

Why would this fail in 5.7 but succeed in 5.6?

Both machines are 64-bit Macs running macOS High Sierra (10.13.4).

Upvotes: 2

Views: 134

Answers (1)

spencer7593
spencer7593

Reputation: 108510

The characterset for the character type columns, in particular, the url VARCHAR column.

The length attribute on the datatype definition VARCHAR(16383) specifies a number of characters.

But the InnoDB row size limit is in bytes.

With a single-byte characterset (e.g. latin1) the column would have maximum number of bytes of 16383 (plus space for the hidden length). This would be fine.

With a multi-byte characterset e.g. utf8 (up to 3 bytes per character) or utf8mb4 (up to 4 bytes per character), the number of bytes required will blow past the maximum allowed bytes for InnoDB row.

On the 5.6 system, we can do a 

 SHOW CREATE TABLE inv_audits

If there's no characterset/collation specified for the column, the default for the table is in effect. (The characterset for the table can be specified, if it's not, it inherits the default set for the database.)

To get the table created on 5.7, as test, cut the VARCHAR size down to 1/4 of the current value, and then take a look at the CREATE TABLE. 

 SHOW CREATE TABLE inv_audits

And it's going to be a multi-byte characterset like utf8 or utf8mb4.

I'd bet the root cause of the issue is that the default characterset is different in 5.6 and 5.7.

Upvotes: 4

Related Questions