[SRU] dns_domains table mysql charset is 'latin1'. Should be 'utf8'

I think you meant create a unique index on the 'domain' column, but yeah, I agree that 'domain' shouldn't be the primary key in this case.

I agree that there should be a surrogate primary key (id auto_increment), with a unique secondary key on the 'domain' column. AFAICT, no other tables reference the 'domain' column, and all the references in api.py which modify the 'dns_domains' table include the 'domain' column in the WHERE clause, so I think this change should be pretty straight forward.

Yeah. Looks like things are worse than I thought. We can't even cleanly drop this primary key:

mysql> desc dns_domains;
+-------------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------------+--------------+------+-----+---------+-------+
| created_at | datetime | YES | | NULL | |
| updated_at | datetime | YES | | NULL | |
| deleted_at | datetime | YES | | NULL | |
| deleted | tinyint(1) | YES | | NULL | |
| domain | varchar(512) | NO | PRI | NULL | |
| scope | varchar(255) | YES | | NULL | |
| availability_zone | varchar(255) | YES | | NULL | |
| project_id | varchar(255) | YES | MUL | NULL | |
+-------------------+--------------+------+-----+---------+-------+
8 rows in set (0.01 sec)

mysql> ALTER TABLE dns_domains DROP PRIMARY KEY;
ERROR 1025 (HY000): Error on rename of './nova/#sql-6cf_855' to './nova/dns_domains' (errno: 150)
mysql> desc dns_domains;
ERROR 1146 (42S02): Table 'nova.dns_domains' doesn't exist

----

What we'll need to do is create a new table, migration the data over, drop the old table, and then rename the new table to 'dns_domains'. Quite a mess!

Need to correct my previous comment -- InnoDB does not support a unique index longer than 767 bytes, even with the newer file formats in 5.1 or 5.5. (You can have a regular index on longer columns, though.)

One solution is to shorten the 'domain' column to 255 characters.

Thanks Devanda,

You are correct. A column w/ a unique key can only be a max of 255 characters. So I guess we'll go with that.

The trunk (Folsom) fix for this issue is here:

https://review.openstack.org/#/c/7139/

This creates a new dns_domains table with a varchar 255 domain column that is utf8. This fixes issues where the MySQL schema can't be imported after a backup.

I'd like to do a similar fix for Essex that runs on 'nova-manage db sync' but doesn't claim a new migration number. In this manner we should be able to fix the issue for Essex users (fixing MySQL backup/restore) is probably important. In any case running the Folsom migration on any Essex patch will re-create the table again... but that won't hurt anything. I do like keeping it as a migration for Folsom.

Dan: wrt to Essex, consider two things

  1) what high impact, user-visible problem does it fix which justifies the risk of doing something in Essex?

  2) will upgrades from any stable versions Essex to the Folsom release continue to work?

Reviewed: https://review.openstack.org/7139
Committed: http://github.com/openstack/nova/commit/7b5fdac84b513c6b606848cc353995d6abb9a7c9
Submitter: Jenkins
Branch: master

commit 7b5fdac84b513c6b606848cc353995d6abb9a7c9
Author: Dan Prince <email address hidden>
Date: Fri May 4 17:01:14 2012 -0400

    Create a utf8 version of the dns_domains table.

    The old dns_domains table was encoded in the 'latin1'
    charset and has its primary key length set to 512.
    This is too long to be a valid pkey in a proper 'utf8' encoded
    table and seems to cause a slew of errors like:

    1) Dumping a database with mysqldump and trying to import it fails
    because this table is latin1 but fkeys to utf8 tables (projects).

    2) Trying to alter the old dns_domains table fails with errors
    like:
     mysql> ALTER TABLE dns_domains DROP PRIMARY KEY;
     ERROR 1025 (HY000): Error on rename of './nova/#sql-6cf_855'....

    This patch creates a new migration that re-creates the dns_domains
    table with a shorter 'domain' column which works with the utf8
    charset.

    Fixes LP Bug #993663.

    Change-Id: I2060d39cbdabd20f410ebd501160a04c51641755

Fix proposed to branch: stable/essex
Review: https://review.openstack.org/8344

Fix proposed to branch: stable/essex
Review: https://review.openstack.org/8356

Reviewed: https://review.openstack.org/8356
Committed: http://github.com/openstack/nova/commit/f0a9f475c5d82493f90debd1082b913511ebacf0
Submitter: Jenkins
Branch: stable/essex

commit f0a9f475c5d82493f90debd1082b913511ebacf0
Author: Dan Prince <email address hidden>
Date: Fri Jun 8 16:59:27 2012 -0400

    Create a utf8 version of the dns_domains table.

    The old dns_domains table was encoded in the 'latin1'
    charset and has its primary key length set to 512.
    This is too long to be a valid pkey in a proper 'utf8' encoded
    table and seems to cause a slew of errors like:

    1) Dumping a database with mysqldump and trying to import it fails
    because this table is latin1 but fkeys to utf8 tables (projects).

    2) Trying to alter the old dns_domains table fails with errors
    like:
    mysql> ALTER TABLE dns_domains DROP PRIMARY KEY;
    ERROR 1025 (HY000): Error on rename of './nova/#sql-6cf_855'....

    This patch creates an Essex fix that re-creates the dns_domains
    table with a shorter 'domain' column which works with the utf8
    charset. This database upgrade will run on MySQL databases only
    which have a dns_domains table with a domain column length of
    VARCHAR(512).

    Fixes LP Bug #993663 on Essex.

    Change-Id: I97888fbc18ebfc0e25c9ba31160588c9cdf5c448

** Impact **

Create a utf8 version of the dns_domains table.

    The old dns_domains table was encoded in the 'latin1'
    charset and has its primary key length set to 512.
    This is too long to be a valid pkey in a proper 'utf8' encoded
    table and seems to cause a slew of errors like:

    1) Dumping a database with mysqldump and trying to import it fails
    because this table is latin1 but fkeys to utf8 tables (projects).

    2) Trying to alter the old dns_domains table fails with errors
    like:
    mysql> ALTER TABLE dns_domains DROP PRIMARY KEY;
    ERROR 1025 (HY000): Error on rename of './nova/#sql-6cf_855'....

    This patch creates an Essex fix that re-creates the dns_domains
    table with a shorter 'domain' column which works with the utf8
    charset. This database upgrade will run on MySQL databases only
    which have a dns_domains table with a domain column length of
    VARCHAR(512).

    Fixes LP Bug #993663 on Essex.

** Development Fix **

This is fixed in the development trunk at https://review.openstack.org/7139 and in quantal

** Stable Fix **

This is fixed in the stable/essex branch at https://review.openstack.org/8356.

** Test Case **

1. Make a database backup of the nova database.
2. Drop the nova database.
3. Try to restore the database.

** Regression Impact **

Minimal this has been tested in the openstack-ci lab.

Hello Dan, or anyone else affected,

Accepted nova into precise-proposed. The package will build now and be available in a few hours. Please help us by testing this new package. See https://wiki.ubuntu.com/Testing/EnableProposed for documentation how to enable and use -proposed. Your feedback will aid us getting this update out to other Ubuntu users. If this package fixes the bug for you please change the bug tag from verification-needed to verification-done. If it does not, change the tag to verification-failed. In either case details of your testing will help us make a better decision. Further information regarding the verification process can be found at https://wiki.ubuntu.com/QATeam/PerformingSRUVerification . Thank you in advance!

Ran through two verifications, one on a single node virtual instance with mysql and nova-api running locally and another on a live mulit node cluster with running instances and a populated database.

Overview of the latter captured here:

After deployment and configuration of nova packages 2012.1-0ubuntu2.3.

Before upgrade, verification of bug:

(...on mysql server)
# mysql -u root
mysql> create database test_nova;
mysql> grant all privileges on test_nova.* to 'nova'@'localhost' identified by 'nova';
mysql> Bye
# mysqldump -u nova -p nova > nova-backup-1.sql
Enter password:
# mysql -u nova -p test_nova < nova-backup-1.sql
Enter password:
ERROR 1005 (HY000) at line 1071: Can't create table 'test_nova.projects' (errno: 150)

After upgrade, verification of fix:

(...on Nova node, precise-proposed enabled.)
~# apt-get upgrade
~# dpkg -l | grep nova-common
ii nova-common 2012.1+stable~20120612-3ee026e-0ubuntu1 OpenStack Compute - common files
~# nova-manage db sync
2012-07-02 16:30:00 AUDIT nova.db.sqlalchemy.fix_dns_domains [-] Applying database fix for Essex dns_domains table.
(...on mysql server)
# mysqldump -u nova -p nova > nova-backup-2.sql
Enter password:
# mysql -u nova -p test_nova < nova-backup-2.sql
Enter password:

The database can be successfully restored from a backup. I've also verified there are no adverse affects post-upgrade in terms of usability of the cloud (instances still spawn, security groups can be created, floating IPs associated, API requests serviced fine, etc)

This bug was fixed in the package nova - 2012.1+stable~20120612-3ee026e-0ubuntu1

---------------
nova (2012.1+stable~20120612-3ee026e-0ubuntu1) precise-proposed; urgency=low

  * New upstream snapshot. (LP: #1010473)
  * Dropped, superseeded by new snapshot:
    - debian/patches/upstream/0001-fix-bug-where-nova-ignores-glance-host-in-imageref.patch
    - debian/patches/upstream/0002-Stop-libvirt-test-from-deleting-instances-dir.patch
    - debian/patches/upstream/0003-Allow-unprivileged-RADOS-users-to-access-rbd-volumes.patch
    - debian/patches/upstream/0004-Fixed-bug-962840-added-a-test-case.patch
    - debian/patches/upstream/0005-Populate-image-properties-with-project_id-again.patch
    - debian/patches/upstream/0006-Use-project_id-in-ec2.cloud._format_image.patc
    - debian/patches/CVE-2012-2101.patch
    - debian/patches/CVE-2012-2654.patch
  * Resynchronize with stable/essex:
    - 3ee026e Only invoke .lower() on non-None protocols. (LP: #1010514)
    - f0a9f47 Create a utf8 version of the dns_domains table. (LP: #993663)
    - 84a43e1 Report memory correctly on Xen. (LP: #997014)
    - 8c72924 Add libvirt get_console_output tests: pty and file. (LP: #990237)
    - 4e423cd Fix Multi_Scheduler to process host capabilities. (LP: #1000403)
    - 4aea7f1 Nail pep8 dependencies to 1.0.1
    - 2b3bbc4 handle updated qemu-img info output. (LP: #1000261)
    - 2d7d51c Fix type of snapshot_id column to match db. (LP: #962615)
    - ec70c69 Generate a Changelog for Nova
    - e5e890f Fix nova.tests.test_nova_rootwrap on Fedora 17. (LP: #992916)
    - 9e9a554 Ec2 handle strings with "0x" (LP: #983206)
    - 26dc6b7 QuantumManager will start dnsmasq during startup. Fixes (LP: #977759)
    - 7028d66 Introduced flag base_dir_name. (LP: #973194)
    - 76b525a Get unit tests functional in OS X.
    - facb936 Update KillFilter to handle 'deleted' exe's. (LP: #967931)
    - 1209af4 Checks if value is string or not before decode. (LP: #952176)
    - 1209af4 Fix timeout in EC2 CloudController.create_image(). (LP: #989764)
    - 108e74b Re-add console_log from console_console_output(). (LP: #987335)
    - 48a0768 Don't leak RPC connections on timeouts or other exceptions. (LP: #968843)
    - 7c64de9 Cloudpipe tap vpn not always working. (LP: #975043)
    - 5ab5051 add libvirt_inject_key flag fix (LP: #971640)
    - 6c68ef5 Xen: Pass session to destroy_vdi. (LP: #988615)
    - 015744e Delete fixed_ips when network is deleted. (LP: #754900)
  * Add debian/scripts/changelog.sh to help generate the changelog.
  * Add debian/nova-common.docs:
    - Include changelog and README.rst
  * debian/rules: Generate a tarball from git snapshot.
  * debian/patches/fix-pep8-errors.patch: Fix pep8 errors due to pep8 upstream
    migration.
 -- Chuck Short <email address hidden> Tue, 05 Jun 2012 09:50:59 -0400

The verification of this Stable Release Update has completed successfully and the package has now been released to -updates. Subsequently, the Ubuntu Stable Release Updates Team is being unsubscribed and will not receive messages about this bug report. In the event that you encounter a regression using the package from -updates please report a new bug using ubuntu-bug and tag the bug report regression-update so we can easily find any regresssions.

I tried using this patch on essex on an environment which got already corrupted, will it only work on new installations?

I thought it should fix a corrupted environment? The environment is built from source though and the patch has been applied individually. (I know this is not a normal environment and not suggested, its using devstack to install essex on production envs)

This is the error log: http://paste.openstack.org/show/28622/