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

[Dan Prince]

After creating an OpenStack Essex MySQL schema only a single table uses the 'latin1' charset by default: dns_domains.

We should refactor this table format in Folsom such that dns_domains properly supports getting converted to utf8 like everything else.

To reproduce you can do the following in a python-migrate script:

    # NOTE(dprince): Trying to create a fresh utf8 dns_domains tables
    # with a domain primary key length of 512 fails w/
    # 'Specified key was too long; max key length is 767 bytes'.
    dns_domains = Table('dns_domains', meta,
        Column('created_at', DateTime),
        Column('updated_at', DateTime),
        Column('deleted_at', DateTime),
        Column('deleted', Boolean),
        Column('domain', String(length=512), primary_key=True, nullable=False),
        Column('scope', String(length=255)),
        Column('availability_zone', String(length=255)),
        Column('project_id', String(length=255), ForeignKey('projects.id')),
        mysql_engine='InnoDB',
        mysql_charset='utf8'
    )

----

I think one solution would be to quit using 'domain' as the primary key and instead just create an index on the 'domain' column. Having a monotonically increasing primary key is often a good idea anyway.

[Johannes Erdfelt (johannes.erdfelt)]

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.

[aeva black (tenbrae)]

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.

[Dan Prince (dan-prince)]

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!

[aeva black (tenbrae)]

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.

[Dan Prince (dan-prince)]

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.

[Dan Prince (dan-prince)]

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.

[Mark McLoughlin (markmc)]

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?

[OpenStack Infra (hudson-openstack)]

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

[OpenStack Infra (hudson-openstack)]

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

[OpenStack Infra (hudson-openstack)]

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

[OpenStack Infra (hudson-openstack)]

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

[Chuck Short (zulcss)]

** 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.

[Steve Langasek (vorlon)]

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!

[Adam Gandelman (gandelman-a)]

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)

[Launchpad Janitor (janitor)]

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

[Clint Byrum (clint-fewbar)]

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.

[Sam Stoelinga (sammiestoel)]

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/