ArchiveTestCase.test_archive_deleted_rows_fk_constraint fails on RHEL6

[Dan Prince]

The following unit test appears to fail consistently on RHEL6:

2013-03-22 14:43:56.374 | FAIL: nova.tests.test_db_api.ArchiveTestCase.test_archive_deleted_rows_fk_constraint
2013-03-22 14:43:56.375 | tags: worker-1
2013-03-22 14:43:56.376 | ----------------------------------------------------------------------
2013-03-22 14:43:56.377 | Empty attachments:
2013-03-22 14:43:56.378 | pythonlogging:'nova'
2013-03-22 14:43:56.380 | stderr
2013-03-22 14:43:56.380 | stdout
2013-03-22 14:43:56.381 |
2013-03-22 14:43:56.382 | Traceback (most recent call last):
2013-03-22 14:43:56.382 | File "/home/jenkins/workspace/gate-nova-python26-rhel6/nova/tests/test_db_api.py", line 2537, in test_archive_deleted_rows_fk_constraint
2013-03-22 14:43:56.383 | self.assertEqual(num, 1)
2013-03-22 14:43:56.384 | MismatchError: 0 != 1

[Dan Prince (dan-prince)]

Okay. This is a new testcase just added on Mar 14th:

commit bb1977718d697565d79322f305a401caf82d5553
Author: David Ripton <email address hidden>
Date: Thu Mar 14 13:41:25 2013 -0400

    Fix db archiving bug with foreign key constraints.

    Fixes bug 1137977

[Dan Prince (dan-prince)]

I'm actually a bit perplexed by this failure. The code/fix in this commit seemed reasonable. We seem to be hitting an IntegrityError (which returns 0 rows deleted) for the last assertion in that test case. This leads me to believe that perhaps 'PRAGMA foreign_keys = ON' isn't working correctly. One thing I've discovered is that removing the initial:

        # The first try to archive console_pools should fail, due to FK.
        num = db.archive_deleted_rows_for_table(self.context, "console_pools")
        self.assertEqual(num, 0)

seems to allow the test case to pass.

[David Ripton (dripton)]

The test passes on Fedora 17 (SQLite 3.7.11), on Ubuntu oneiric (3.7.7), and on Ubuntu precise (3.7.9). It fails on RHEL 6.1 and 6.4 (3.6.20). So I think it might be an issue with the older SQLite version. The SQLite changelog does not say anything about PRAGMA foreign_keys changing after 3.6, but the SQLAlchemy docs say that the minimum supported SQLite version for foreign key support is 3.6.19. Technically RHEL 6 just meets that requirement, but maybe there were still problems with it in early versions.

I'm going to skip this test if the sqlite version is less than 3.7.

[OpenStack Infra (hudson-openstack)]

Fix proposed to branch: master
Review: https://review.openstack.org/25275

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.openstack.org/25275
Committed: http://github.com/openstack/nova/commit/c07ec51dba9b1b3a3b45d6b73dd0cac736d633b8
Submitter: Jenkins
Branch: master

commit c07ec51dba9b1b3a3b45d6b73dd0cac736d633b8
Author: David Ripton <email address hidden>
Date: Mon Mar 25 10:30:06 2013 -0400

    Do not test foreign keys with SQLite version < 3.7

    Fixes bug 1158828

    Even though SQLAlchemy is documented to support foreign keys (with
    the pragma set) on SQLite >= 3.6.19, we're seeing consistent problems
    with that support on RHEL 6.4 with SQLite 3.6.20. It works fine on
    Fedora and Ubuntu with SQLite 3.7.x.

    To work around these problems, if we're using SQLite, test the SQLite
    version inside the test that requires foreign key support,
    and skip the test if it's < 3.7

    Change-Id: I1c26f29371376b271a4e384ea7b5d2009651acc1

[OpenStack Infra (hudson-openstack)]

Fix proposed to branch: stable/grizzly
Review: https://review.openstack.org/28329

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.openstack.org/28329
Committed: http://github.com/openstack/nova/commit/e49e38cbd9d8df2281c0bfddfe6e4aaea90b889b
Submitter: Jenkins
Branch: stable/grizzly

commit e49e38cbd9d8df2281c0bfddfe6e4aaea90b889b
Author: David Ripton <email address hidden>
Date: Mon Mar 25 10:30:06 2013 -0400

    Do not test foreign keys with SQLite version < 3.7

    Fixes bug 1158828

    Even though SQLAlchemy is documented to support foreign keys (with
    the pragma set) on SQLite >= 3.6.19, we're seeing consistent problems
    with that support on RHEL 6.4 with SQLite 3.6.20. It works fine on
    Fedora and Ubuntu with SQLite 3.7.x.

    To work around these problems, if we're using SQLite, test the SQLite
    version inside the test that requires foreign key support,
    and skip the test if it's < 3.7

    Change-Id: I1c26f29371376b271a4e384ea7b5d2009651acc1