[Bug 975085] [NEW] nova mysql DB can't be restored from backups

[James Troup]

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 at 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 at dziban:~# sed -i -e "s%\(--sql_connection=.*\)/di.*$%\1/testnova%" /etc/nova/nova.conf 
| 
| root at 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 at 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 at 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 at dziban:~# cp -iv /tmp/test-backup{,.orig} ; vi /tmp/test-backup
| root at 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 at dziban:~# 
| root at dziban:~# mysql -u root -p fakenova < /tmp/test-backup
| Enter password:
| root at 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