How to fix "Index column size too large" error when doing keystone db_sync

I was trying to deploy the keystone service manually on my Ubuntu 16.04 desktop:

# su -s /bin/sh -c "keystone-manage db_sync" keystone

and couldn't make it work. I got this error:

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 KE
Y (repository_id)\n)\n\n'] (Background on this error at: http://sqlalche.me/e/2j85)


Then I figured out that the new release of mariadb/mysql  (10.0.33-MariaDB-0ubuntu0.16.04.1 Ubuntu 16.04) uses utf8mb4 as the character encoding which uses more bytes per character than utf8. And the length used for the 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.

So to fix it follow these steps:

1. Change character-set-server and collation-server values of all mysql's config files from utf8mb4 and utf8mb4_general_ci to utf8 and utf8_general_ci respectedly. For example:

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

2. Restart mysql

# systemctl restart mysql

3. Drop the keystone database and recreate it.

4. To make sure the character set of the keystone database is utf8, do this:

MariaDB > use keystone;

# check the current character set of the database
MariaDB > select @@character_set_database;

# change the character set to utf8
MariaDB > ALTER DATABASE keystone CHARACTER SET utf8 COLLATE utf8_general_ci;

# check again the current character set of the database
MariaDB > select @@character_set_database;

5. Enjoy