nova mysql DB can't be restored from backups
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
OpenStack Compute (nova) |
Confirmed
|
Medium
|
Dan Prince | ||
nova (Ubuntu) |
New
|
Medium
|
Unassigned |
Bug Description
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.
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/.
| 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%\(--
|
| root@dziban:~# nova-manage db sync
| 2012-04-06 11:39:29 DEBUG nova.utils [-] backend <module 'nova.db.
| 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-
| root@dziban:~# diff -u /tmp/test-
| --- /tmp/test-
| +++ /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_
| -) ENGINE=InnoDB DEFAULT CHARSET=latin1;
| +) ENGINE=InnoDB DEFAULT CHARSET=utf8;
| /*!40101 SET character_
|
| --
| root@dziban:~#
| root@dziban:~# mysql -u root -p fakenova < /tmp/test-backup
| Enter password:
| root@dziban:~#
Changed in nova (Ubuntu): | |
importance: | Undecided → Medium |
Changed in nova: | |
status: | New → Confirmed |
importance: | Undecided → Medium |
Changed in nova: | |
assignee: | nobody → Dan Prince (dan-prince) |