nova mysql DB can't be restored from backups

[James Troup]

TL;DR: a freshly created nova database can't be restored from backup
because of two problems with the `dns_domains` table.

This is using nova 2012.1-0ubuntu1 from/on Precise.

Steps to reproduce:

 * Create a new database
 * Point nova at the new database
 * Run 'nova-manage db sync'
 * Backup the new database
 * Try to restore it
 * Watch it all burn

The two problems appears to be:

 a) ERROR 1005 (HY000) at line 955: Can't create table 'fakenova.projects' (errno: 150)

The charset for `dns_domains` is set to latin1 and every other table
is utf8. This causes foreign keys from `dns_domains` to any other
table to fail.

 b) ERROR 1071 (42000) at line 424: Specified key was too long; max key length is 767 bytes

`dns_domains.domain` is a varchar with length 512 which appears to be
too big for mysql's default config.

Example session below:

| root@dziban:~# mysql -v -u root -p nova
| Enter password:
| Reading table information for completion of table and column names
| You can turn off this feature to get a quicker startup with -A
|
| Welcome to the MySQL monitor. Commands end with ; or \g.
| Your MySQL connection id is 75
| Server version: 5.5.22-0ubuntu1 (Ubuntu)
|
| Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.
|
| Oracle is a registered trademark of Oracle Corporation and/or its
| affiliates. Other names may be trademarks of their respective
| owners.
|
| Reading history-file /root/.mysql_history
| Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
|
| mysql> create database testnova;
| --------------
| create database testnova
| --------------
|
| Query OK, 1 row affected (0.00 sec)
|
| mysql> grant all privileges on `testnova`.* TO 'nova'@'%';
| --------------
| grant all privileges on `testnova`.* TO 'nova'@'%'
| --------------
|
| Query OK, 0 rows affected (0.00 sec)
|
| mysql>
|
| root@dziban:~# sed -i -e "s%\(--sql_connection=.*\)/di.*$%\1/testnova%" /etc/nova/nova.conf
|
| root@dziban:~# nova-manage db sync
| 2012-04-06 11:39:29 DEBUG nova.utils [-] backend <module 'nova.db.sqlalchemy.migration' from '/usr/lib/python2.7/dist-packages/nova/db/sqlalchemy/migration.pyc'> from (pid=17392) __get_backend /usr/lib/python2.7/dist-packages/nova/utils.py:658
| root@dziban:~# mysql -u root -p
| Enter password:
| Welcome to the MySQL monitor. Commands end with ; or \g.
| Your MySQL connection id is 108
| Server version: 5.5.22-0ubuntu1 (Ubuntu)
|
| Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.
|
| Oracle is a registered trademark of Oracle Corporation and/or its
| affiliates. Other names may be trademarks of their respective
| owners.
|
| Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
|
| mysql> drop database fakenova; create database fakenova;
| Query OK, 49 rows affected (0.03 sec)
|
| Query OK, 1 row affected (0.00 sec)
|
| mysql> Bye
| root@dziban:~# mysql -u root -p fakenova < /tmp/test-backup
| Enter password:
| ERROR 1005 (HY000) at line 955: Can't create table 'fakenova.projects' (errno: 150)
| root@dziban:~# cp -iv /tmp/test-backup{,.orig} ; vi /tmp/test-backup
| root@dziban:~# diff -u /tmp/test-backup{.orig,}
| --- /tmp/test-backup.orig 2012-04-06 12:02:52.369900149 +0000
| +++ /tmp/test-backup 2012-04-06 12:03:05.510289473 +0000
| @@ -426,14 +426,14 @@
| `updated_at` datetime DEFAULT NULL,
| `deleted_at` datetime DEFAULT NULL,
| `deleted` tinyint(1) DEFAULT NULL,
| - `domain` varchar(512) NOT NULL,
| + `domain` varchar(255) NOT NULL,
| `scope` varchar(255) DEFAULT NULL,
| `availability_zone` varchar(255) DEFAULT NULL,
| `project_id` varchar(255) DEFAULT NULL,
| PRIMARY KEY (`domain`),
| KEY `project_id` (`project_id`),
| CONSTRAINT `dns_domains_ibfk_1` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`)
| -) ENGINE=InnoDB DEFAULT CHARSET=latin1;
| +) ENGINE=InnoDB DEFAULT CHARSET=utf8;
| /*!40101 SET character_set_client = @saved_cs_client */;
|
| --
| root@dziban:~#
| root@dziban:~# mysql -u root -p fakenova < /tmp/test-backup
| Enter password:
| root@dziban:~#