Apps are down due to the "Maximum Column Size is 767 bytes" constraint in PCF for MySQL
search cancel

Apps are down due to the "Maximum Column Size is 767 bytes" constraint in PCF for MySQL

book

Article ID: 297856

calendar_today

Updated On:

Products

VMware Tanzu Application Service for VMs

Issue/Introduction

Symptoms:

The following are the symptoms associated with this issue:

  • When creating new tables or indexes
  • After an upgrade, an impact on the system apps for the Pivotal Application Service MySQL instances
  • After an 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.

Environment


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

 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

Resolution

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


Powered by Wolken Software