Public bug reported:
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:~#
** Affects: nova (Ubuntu)
Importance: Undecided
Status: New
** Tags: canonistack
--
You received this bug notification because you are a member of Ubuntu
Server Team, which is subscribed to nova in Ubuntu.
https://bugs.launchpad.net/bugs/975085
Title:
nova mysql DB can't be restored from backups
To manage notifications about this bug go to:
https://bugs.launchpad.net/ubuntu/+source/nova/+bug/975085/+subscriptions
--
Ubuntu-server-bugs mailing list
Ubuntu-server-bugs@lists.ubuntu.com
Modify settings or unsubscribe at:
https://lists.ubuntu.com/mailman/listinfo/ubuntu-server-bugs
