MySQL/UTF-8: Issue while executing nova-manage db sync

[Rasika Karunathilaka]

When executing "/usr/bin/nova-manage db sync" following error was given at /var/log/manage/nova-manage.log.
Nova compute is trying to configure with MY SQL 5.1.53

(nova): TRACE: raise errorclass, errorvalue(nova): TRACE: OperationalError: (OperationalError) (1071, 'Specified key was too long; max key length is 1000 bytes') '\nCREATE TABLE user_project_association (\n\tcreated_at DATETIME, \n\tupdated_at DATETIME, \n\tdeleted_at DATETIME, \n\tdeleted BOOL, \n\tuser_id VARCHAR(255) NOT NULL, \n\tproject_id VARCHAR(255) NOT NULL, \n\tPRIMARY KEY (user_id, project_id), \n\tCHECK (deleted IN (0, 1)), \n\tFOREIGN KEY(project_id) REFERENCES projects (id), \n\tFOREIGN KEY(user_id) REFERENCES users (id)\n)\n\n' ()(nova): TRACE:

[Thierry Carrez (ttx)]

In UTF-8, PRIMARY KEY (user_id, project_id) with user_id VARCHAR(255) project_id VARCHAR(255) ends up being bigger than 1000 bytes.

Could you try "latin1" as the default charset ?

[Rasika Karunathilaka (rasika-karunathilaka)]

Hi Thierry,
 After setting the CHARACTER SET AS 'latin1' it worked.

[Peng Yong (ppyy)]

two method:
1. don't use UTF-8 as default character-set of mysql server, use latin1

or

2. don't use MYISAM as table type, please convert to INNODB
myisam has a limit for key, it's 1K long.

vi /etc/my.cnf

[mysqld]

default_table_type = InnoDB

character-set-server=utf8
init_connect=’SET NAMES utf8′

[Dolph Mathews (dolph)]

What's the key that's being inserted?

Keystone supports UTF-8 ID's, so switching to latin1 doesn't seem like a real solution to me.

[Dolph Mathews (dolph)]

This might also be fixed by improved UTF-8 support in MySQL 5.5: http://dev.mysql.com/doc/refman/5.5/en/charset-unicode.html

[Sean Dague (sdague)]

nova now requires all database to be innodb, so I think we're ok now (this was released in Folsom)