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

[romy varga]

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.

http://docs.openstack.org/mitaka/install-guide-ubuntu/glance-install.html#prerequisites

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") - http://docs.openstack.org/mitaka/install-guide-ubuntu/keystone-install.html

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

[foo (timski-deactivatedaccount)]

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
~~~~~~~~~~~
[client]
default-character-set = utf8

[mysqld]
bind-address = 127.0.1.1
default-storage-engine = innodb
innodb_file_per_table
collation-server = utf8_general_ci
character-set-server = utf8
init-connect='SET NAMES utf8'

[mysql]
default-character-set = utf8
~~~~~~~~~~

and still get the error

[romy varga (romyv)]

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)]

The installation guide only supports Ubuntu 14.04.

[vance morris (jedi-holocron)]

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)]

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)]

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.

[Mitchell Zubarev-Foxworth (mitch-foxworth)]

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:
[client]
default-character-set = utf8

[mysqld]
bind-address = 172.16.3.32
default-storage-engine = innodb
innodb_file_per_table
collation-server = utf8_general_ci
init-connect = 'SET NAMES utf8'
character-set-server = utf8

[mysql]
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)]

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

[Tom Fifield (fifieldt)]

Retargetting bug for Newton fix.

[Tom Fifield (fifieldt)]

Looks like this was fixed in the newton install updates.

[murtazasolangi (amurtaza14)]

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

[murtazasolangi (amurtaza14)]

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

[foo (timski-deactivatedaccount)]

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

[murtazasolangi (amurtaza14)]

@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 https://bugs.launchpad.net/openstack-manuals/+bug/1644641 you can add your input here.

[Walt Corey (waltc)]

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