OpenStack Error – Index column size too large. The maximum column size is 767 bytes [Solved]

If you are getting an error while syncing or populating database of services such as Keystone, Glance, Nova and Neutron, then here’s what I did to fix it. Before that, have a look at the snapshot of the error thrown while executing keystone-manage db_sync and glance-manage db_sync commands. However, the same error was seen while executing nova-manage db_sync and neutron-manage db_sync as well. The error is not related to OpenStack directly, but due to the unsupported database character set.

The solution that we are going to see will work for all the above commands.

Snapshot of the error while populating Keystone database:

# su -s /bin/sh -c "keystone-manage db_sync" keystone
 ERROR keystone DBError: (pymysql.err.InternalError) (1709, u'Index column size too large. The maximum column size is 767 bytes.') [SQL: u'\nCREATE TABLE migrate_version (\n\trepository_id VARCHAR(250) NOT NULL, \n\trepository_path TEXT, \n\tversion INTEGER, \n\tPRIMARY KEY (repository_id)\n)\n\n']

Snapshot of the error while populating Glance database:

# su -s /bin/sh -c "glance-manage db_sync" glance
 ERROR glance DBError: (pymysql.err.InternalError) (1709, u'Index column size too large. The maximum column size is 767 bytes.') [SQL: u'\nCREATE TABLE migrate_version (\n\trepository_id VARCHAR(250) NOT NULL, \n\trepository_path TEXT, \n\tversion INTEGER, \n\tPRIMARY KEY (repository_id)\n)\n\n']

Solution:

The actual error here’s Index column size too large. The maximum column size is 767 bytes, which resulted while executing query CREATE TABLE migrate_version (repository_id VARCHAR(250) NOT NULL, repository_path TEXT, version INTEGER, PRIMARY KEY (repository_id)

The reason for the error is, the length used for column or key repository_id is 250 VARCHAR and 4 bytes per character makes it longer than the limit allowed by InnoDB, which is 767.

Now to fix this issue we need to understand how applications store data in database using Character set and collation. A character encoding is a way to encode characters so that they fit in memory and collation is a group of rules for comparing characters in a character set. It means, we need to adjust the character set and collation in MySQL configuration file or alter the database to use the correct character set and collation.

The first thing to check is my.cnf to see the current value of character set and collation.

Note: Verifying my.cnf might not suffice, you may need to check all the configuration files under conf.d or mariadb.conf.d folders.

character-set-server = utf8mb4
 collation-server = utf8mb4_general_ci

The character-set utf8mb4 and collation utf8mb4_general_ci is not enough to hold the length of repository_id in the above SQL query. So, the solution is to replace those values with utf8 and  utf8_general_ci respectively.

Reference.

Fix 1:

You can quickly verify all the configuration files of mysql and replace character-set-server and collation-server values to utf8 as shown below and restart mysqld service:

/etc/mysql# grep -lr "utf8mb4" *
 conf.d/openstack.cnf
 conf.d/mysql.cnf
 mariadb.conf.d/50-mysql-clients.cnf
 mariadb.conf.d/50-server.cnf
 mariadb.conf.d/50-client.cnf

# grep utf8 conf.d/mysql.cnf
character-set-server = utf8
collation-server = utf8_general_ci

Overall, you need to perform below steps:

1. Replace utf8mb4 to utf8 in all configuration files
2. Reload mysqld daemon
3. Drop database keystone or glance or nova or neutron (for whichever service you were getting error and don’t worry, you haven’t populated the database yet and it’s safe to remove)
4. Create database keystone or glance or nova or neutron
5. Try db_sync or populate the database using OpenStack commands. It should probably work, else try Fix 2.

Fix 2:

Step 1: Login to MySQL

# mysql -u root -p

Step 2: Connect to database

MariaDB [(none)]> use glance

Step 3: Check the character-set value

MariaDB > select @@character_set_database;
 +--------------------------+
 | @@character_set_database |
 +--------------------------+
 | utf8mb4 |
 +--------------------------+
 1 row in set (0.00 sec)

Step 4: Alter the value utf8mb4 to utf8 and set collate to utf8_general_ci

MariaDB > ALTER DATABASE glance CHARACTER SET utf8 COLLATE utf8_general_ci;
 Query OK, 1 row affected (0.00 sec)

Note: Remember to replace database name accordingly.

Step 5: Confirm the change

MariaDB> select @@character_set_database;
 +--------------------------+
 | @@character_set_database |
 +--------------------------+
 | utf8 |
 +--------------------------+

Step 6: Trying db_sync the database and it should work.

 su -s /bin/sh -c "glance-manage db_sync" glance