Newton on 16.04 Xenial and mysql's new default utf8mb4 charset

Bug #1575688 reported by romy varga on 2016-04-27
This bug affects 4 people
Affects Status Importance Assigned to Milestone

Bug Description

Fresh install of Mitaka on default install of Xenial fails during populating the database steps.

The default character set of mysqld in Debian has been changed to utf8mb4 (from utf8), and "glance-manage db_sync" fails with:

2016-04-26 16:09:13.091 21061 ERROR oslo_db.sqlalchemy.exc_filters [-] DBAPIError exception wrapped from (pymysql.err.InternalError) (1071, u'Specified key was too long; max key length 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']

The length of the key for 'repository_id' with max 250 chars and 4 bytes per char makes it longer than the 767 allowed by innodb.

Note:this was a multi-region setup, so glance was first component that needed a database sync. Most likely keystone install would also fail if one did install the identity service (during "keystone-manage db_sync") -

The settings in /etc/mysql/conf.d/openstack.cnf (eg, "character-set-server = utf8", init-connect and collation-server) don't seem to suffice.

Need to also set the following:

/etc/mysql/mariadb.conf.d/50-server.cnf:character-set-server = utf8
/etc/mysql/mariadb.conf.d/50-server.cnf:collation-server = utf8_general_ci
/etc/mysql/mariadb.conf.d/50-client.cnf:default-character-set = utf8
/etc/mysql/mariadb.conf.d/50-mysql-clients.cnf:default-character-set = utf8

Chason Chan (chen-xing) on 2016-04-27
Changed in openstack-manuals:
assignee: nobody → Chason (chen-xing)
Tim Josefski (timski) wrote :

I got the same error when trying to populate the keystone-db.

I've created a file /etc/mysql/mariadb.conf.d/60-openstack.cnf to overwrite the defaults containing
default-character-set = utf8

bind-address =
default-storage-engine = innodb
collation-server = utf8_general_ci
character-set-server = utf8
init-connect='SET NAMES utf8'

default-character-set = utf8

and still get the error

romy varga (romyv) wrote :

from what I've seen - if you had the database already created when utf8mb4 was still in the configs, the database got created with those settings and changing them in the config files makes no diff on future calls for table create and index out of range.

drop the database - there is nothing in it yet anyway - and recreate it after config file changes are in and mysqld restarted, exactly the same way you just created them in the first place. after this the table create will fly right through.

Matt Kassawara (ionosphere80) wrote :

The installation guide only supports Ubuntu 14.04.

Changed in openstack-manuals:
status: New → Invalid
vance morris (jedi-holocron) wrote :

But for how long? Surely with 16.04 LTS the installation guide will be updated to support it. Shouldn't this issue at least be backlogged?

Tom Fifield (fifieldt) wrote :

I think probably what's needed is someone to go "naively" through the entire guide and test that it works in 16.04, since it probably should aside from a few more changes like these. Anyone up for it?

Namgon Lucas Kim (namgon) wrote :

I also got the same error with Ubuntu 16.04, and solved as following:

1) reconfigure utf8m4 to utf8
2) restart mysql daemon
3) drop database glance
4) create database glance
5) try db_sync; it's working.

Confirmed @namgon's fix (this is building Openstack Liberty on 16.04; I was seeing the problem populating the keystone database):

1) Replace all instances of 'utf8mb4' with 'utf8' in /etc/mysql/mariadb.conf.d/*

2) Add the below to /etc/mysql/conf.d/mysqld_openstack.cnf:
default-character-set = utf8

bind-address =
default-storage-engine = innodb
collation-server = utf8_general_ci
init-connect = 'SET NAMES utf8'
character-set-server = utf8

default-character-set = utf8

3) Drop the keystone database
4) Restart mysql service
5) Run 'keystone-manage db_sync'

For some reason I was still getting the error when /etc/mysql/conf.d/mysqld_openstack.cnf had the above values, and modifying the files in /etc/mysql/mariadb.conf.d/ was also necessary to fix.

Hosam Al Ali (a-hosam) wrote :

after change all config file from utt8mb4 to utf8 the issue gone (ubuntu 16 and Mitaka)

Tom Fifield (fifieldt) wrote :

Retargetting bug for Newton fix.

Changed in openstack-manuals:
status: Invalid → New
assignee: Chason (chen-xing) → nobody
milestone: none → newton
summary: - Mitaka on 16.04 Xenial and mysql's new default utf8mb4 charset
+ Newton on 16.04 Xenial and mysql's new default utf8mb4 charset
Tom Fifield (fifieldt) wrote :

Looks like this was fixed in the newton install updates.

Changed in openstack-manuals:
status: New → Fix Released
murtazasolangi (amurtaza14) wrote :

Facing issue even after change all config file from utt8mb4 to utf8 the (ubuntu 16 and NEWTON)

murtazasolangi (amurtaza14) wrote :

I'm facing issue in Keystone [16.04 Xenial, Newton], when I replace utf8mb4 with utf8 in /etc/mysql/mariadb.conf.d/50-client.cnf
Then, it triggers error
root@Mil-controller:/home/super# mysql -u root -p
mysql: unknown variable 'defualt-character-set=utf8'

But, if I leave it same with utf8mb4, then keystone doesn't sycn

Tim Josefski (timski) wrote :

you spelled 'default' wrong, I guess that's all

murtazasolangi (amurtaza14) wrote :

@Tim, you are right, changed charset from utf8mb4 to utf8 but no luck to "create openstack project create --domain default "
I have initiated new bug you can add your input here.

Walt Corey (waltc) wrote :

The Newton install tutorial and guide for Ubuntu still doesn't work. Tom's comment from May 15th should have been followed up on.

To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers