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

Bug #993663 reported by Dan Prince on 2012-05-03
32
This bug affects 3 people
Affects Status Importance Assigned to Milestone
OpenStack Compute (nova)
Medium
Dan Prince
Essex
Undecided
Unassigned
nova (Ubuntu)
Undecided
Unassigned
Precise
Undecided
Unassigned

Bug Description

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.

Related branches

CVE References

Dan Prince (dan-prince) on 2012-05-03
Changed in nova:
assignee: nobody → Dan Prince (dan-prince)
status: New → In Progress
Dan Prince (dan-prince) on 2012-05-03
Changed in nova:
importance: Undecided → Medium

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.

tags: added: db

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) wrote :

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.

Dan Prince (dan-prince) wrote :

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) wrote :

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) wrote :

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

Changed in nova:
status: In Progress → Fix Committed
Devin Carlen (devcamcar) on 2012-05-22
Changed in nova:
milestone: none → folsom-1
Thierry Carrez (ttx) on 2012-05-23
Changed in nova:
status: Fix Committed → Fix Released

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

tags: added: in-stable-essex
Chuck Short (zulcss) on 2012-06-11
Changed in nova (Ubuntu):
status: New → In Progress
Changed in nova (Ubuntu Precise):
status: New → In Progress
summary: - dns_domains table mysql charset is 'latin1'. Should be 'utf8'
+ [SRU] dns_domains table mysql charset is 'latin1'. Should be 'utf8'
Chuck Short (zulcss) wrote :

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

Changed in nova (Ubuntu Precise):
status: In Progress → Fix Committed
tags: added: verification-needed
Adam Gandelman (gandelman-a) wrote :

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)

Dave Walker (davewalker) on 2012-07-03
tags: added: verification-done
removed: verification-needed
Launchpad Janitor (janitor) wrote :

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

Changed in nova (Ubuntu Precise):
status: Fix Committed → Fix Released
Chuck Short (zulcss) on 2012-07-31
Changed in nova (Ubuntu):
status: In Progress → Fix Released

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.

Thierry Carrez (ttx) on 2012-09-27
Changed in nova:
milestone: folsom-1 → 2012.2
Sam Stoelinga (sammiestoel) wrote :

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/

To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Duplicates of this bug

Other bug subscribers