Pivotal Knowledge Base

Follow

"Apps are down" due to the "maximum column size is 767 bytes" constraint in MySQL

Environment

 Product  Version
 Pivotal Cloud Foundry  1.10
 MySQL  1.8.x

Symptom

The following are the symptoms associated with this issue; you may see one or more of these in your environment:

  • When creating new tables or indexes
  • After upgrade, an impact on the system apps for the Elastic Runtime MySQL instances and
  • After upgrade, an impact on the user apps for the MySQL tile instances with the error message:
"Index column size too large. The maximum column size is 767 bytes."
  • While upgrading the MySQL cluster, the Ops Manager has pending changes for SSO and MySQL. The proxy dashboard may report the nodes as unhealthy. The error message is:
"APPS ARE DOWN, due to mysql proxy."

Error Log:

20XX-XX-XXT11:24:XX.XX+0000 [APP/0] OUT Caused by: java.sql.SQLException: Index column size too large. 
The maximum column size is 767 bytes. 

Cause

innodb_large_prefix was enabled in the MySQL version 1.8 to support recent file format changes made to "Barracuda." Barracuda added new supported row formats of DYNAMIC and COMPRESSED. Table or Index creation may fail with the above-mentioned error "Index column size too large" as a result. 

Currently Defined Defaults 

 show variables like 'innodb_larg%' ;
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| innodb_large_prefix | ON    |
+---------------------+-------+
1 row in set (0.00 sec)
show variables like 'innodb_file_format%' ;
+--------------------------+-----------+
| Variable_name            | Value     |
+--------------------------+-----------+
| innodb_file_format       | Barracuda |
| innodb_file_format_check | ON        |
| innodb_file_format_max   | Barracuda |
+--------------------------+-----------+

innodb_large_prefix Supported Row formats

The table below shows that the COMPACT row format type does not support large index prefixes and it is the default row format type in Pivotal MySQL.

 COMPACT  innodb_large_prefix = off  DEFAULT
 DYNAMIC  innodb_large_prefix = on  NON-DEFAULT
 COMPRESSED  innodb_large_prefix = on  NON-DEFAULT

Default Character set for Pivotal MySQL is utf8 

SELECT schema_name,default_character_set_name FROM information_schema.SCHEMATA ;
+--------------------+----------------------------+
| schema_name        | default_character_set_name |
+--------------------+----------------------------+
| information_schema | utf8                       |
| mysql              | utf8                       |
| performance_schema | utf8                       |
| test               | utf8                       |
+--------------------+----------------------------+

By default Pivotal MySQL uses the character set utf8 which means that we use 3 bytes for every 1 character. This means, column type of varchar(10) uses 30 bytes resulting in the max prefix size for compact row format to be equivalent to varchar(255). That is 255 * 3bytes = 765 bytes which is, two bytes less than the max of 767 bytes. Here is a table of common character set types and their index maximums for COMPACT row format tables.

 Charset  Bytes Per Char  Max Char
 utf8  3 bytes  255
 utf8mb4  4 bytes  191
 latin1  1 byte  767

With innodb_large_prefix set to on and using row format COMPRESSED or DYNAMIC, you can increase the max prefix character size to 65536 bytes instead of 767 bytes. The below chart shows the max character length with InnoDB large prefix and [COMPRESSED| DYNAMIC] row formats. These values, expect for utf8mb4, are higher than the maximum row size of a table, so there is no way to hit these limits.  

 Charset  Bytes Per Char  Max Char
 utf8  3 bytes  21845
 utf8mb4  4 bytes  16384
 latin1  1 byte  65536

Workaround 

Unique Constraints

In most cases, simply changing the table row format will solve this problem. For example, the given DDL will result in an index column error when the innodb_large_prefix is set to on as default. Note, the "UNIQUE KEY" constraint is creating an index on column "lastname."

drop table if exists testtable;
create table testtable
(
  id int(10) not null auto_increment,
  firstname varchar(255),
  lastname varchar(256),
  PRIMARY KEY (id),
  UNIQUE KEY (lastname) 
) ROW_FORMAT=COMPACT;

ERROR 1709 (HY000): Index column size too large. The maximum column size is 767 bytes.

If we simply change the row format to DYNAMIC or COMPRESSED, the same DDL statement will succeed.

drop table if exists testtable;
create table testtable
(
  id int(10) not null auto_increment,
  firstname varchar(255),
  lastname varchar(256),
  PRIMARY KEY (id),
  UNIQUE KEY (lastname) 
) ROW_FORMAT=DYNAMIC;

Query OK, 0 rows affected (0.02 sec)

We can see an index was created for column "lastname"

show index from testtable;
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table     | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| testtable |          0 | PRIMARY  |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| testtable |          0 | lastname |            1 | lastname    | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

Index Creation 1

As long as the column does not have any unique constraint, and you're are not trying to index the large column, then you will not see this error. But when you do see the Index size error, the row format matters. If for some reason you can not change the table's row format then you can disable innodb_large_prefix. Keep in mind that this is a global setting and will limit the features available from row format COMPRESSED and DYNAMIC for all other tables. 

drop table if exists testindex;
CREATE TABLE testindex (
 id int(10),
 firstname VARCHAR(511),
 PRIMARY KEY(`id`)
);
CREATE INDEX idx_testindex_firstname ON testindex (firstname); ERROR 1709 (HY000): Index column size too large. The maximum column size is 767 bytes.

So, to avoid the index creation error, you can set innodb_large_prefix to off

set global innodb_large_prefix = off;

CREATE INDEX idx_testindex_firstname ON testindex (firstname);
Query OK, 0 rows affected, 1 warning (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 1

Index creation 2

If changing the DDL of your table is possible then you could alter the table's row format as per the following example where we convert from the default row format COMPACT to DYNAMIC. Using "testindex" table from the previous example, and having innodb_large_prefix set to on, we can still get the index creation to succeed after executing the following alter table statement. 

ALTER TABLE testindex ROW_FORMAT=DYNAMIC;

CREATE INDEX idx_testindex_firstname ON testindex (firstname);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

 

Comments

Powered by Zendesk