Unit test failure: openstack_citest" is being accessed by other users\nDETAIL: There are 1 other session(s) using the database.

[Joe Gordon]

We are periodically seeing this nova unit test failure in our CI system:

openstack_citest" is being accessed by other users\nDETAIL: There are 1 other session(s) using the database.

http://logs.openstack.org/76/98376/1/gate/gate-nova-python27/d2a0593/console.html

2014-06-11 06:26:40.002 | FAIL: nova.tests.db.test_migrations.TestNovaMigrations.test_postgresql_opportunistically
2014-06-11 06:26:40.002 | tags: worker-6
2014-06-11 06:26:40.003 | ----------------------------------------------------------------------
2014-06-11 06:26:40.003 | Empty attachments:
2014-06-11 06:26:40.003 | pythonlogging:''
2014-06-11 06:26:40.003 | stderr
2014-06-11 06:26:40.003 | stdout
2014-06-11 06:26:40.003 |
2014-06-11 06:26:40.004 | Traceback (most recent call last):
2014-06-11 06:26:40.004 | File "nova/tests/db/test_migrations.py", line 139, in test_postgresql_opportunistically
2014-06-11 06:26:40.004 | self._test_postgresql_opportunistically()
2014-06-11 06:26:40.004 | File "nova/tests/db/test_migrations.py", line 428, in _test_postgresql_opportunistically
2014-06-11 06:26:40.004 | self._reset_database(database)
2014-06-11 06:26:40.004 | File "nova/tests/db/test_migrations.py", line 335, in _reset_database
2014-06-11 06:26:40.004 | self._reset_pg(conn_pieces)
2014-06-11 06:26:40.005 | File "nova/openstack/common/lockutils.py", line 249, in inner
2014-06-11 06:26:40.005 | return f(*args, **kwargs)
2014-06-11 06:26:40.005 | File "nova/tests/db/test_migrations.py", line 244, in _reset_pg
2014-06-11 06:26:40.005 | self.execute_cmd(droptable)
2014-06-11 06:26:40.005 | File "nova/tests/db/test_migrations.py", line 227, in execute_cmd
2014-06-11 06:26:40.005 | "Failed to run: %s\n%s" % (cmd, output))
2014-06-11 06:26:40.005 | File "/home/jenkins/workspace/gate-nova-python27/.tox/py27/local/lib/python2.7/site-packages/testtools/testcase.py", line 321, in assertEqual
2014-06-11 06:26:40.006 | self.assertThat(observed, matcher, message)
2014-06-11 06:26:40.006 | File "/home/jenkins/workspace/gate-nova-python27/.tox/py27/local/lib/python2.7/site-packages/testtools/testcase.py", line 406, in assertThat
2014-06-11 06:26:40.006 | raise mismatch_error
2014-06-11 06:26:40.006 | MismatchError: !=:
2014-06-11 06:26:40.006 | reference = ''
2014-06-11 06:26:40.006 | actual = '''\
2014-06-11 06:26:40.007 | Unexpected error while running command.
2014-06-11 06:26:40.007 | Command: psql -w -U openstack_citest -h localhost -c 'drop database if exists openstack_citest;' -d template1
2014-06-11 06:26:40.007 | Exit code: 1
2014-06-11 06:26:40.007 | Stdout: ''
2014-06-11 06:26:40.007 | Stderr: 'ERROR: database "openstack_citest" is being accessed by other users\\nDETAIL: There are 1 other session(s) using the database.\\n\''''
2014-06-11 06:26:40.007 | : Failed to run: psql -w -U openstack_citest -h localhost -c 'drop database if exists openstack_citest;' -d template1

elastic-search query: message:"Stderr: \'ERROR:  database \"openstack_citest\" is being accessed by other users\\nDETAIL:  There are 1 other session(s) using the database.\\n\'" AND project:"openstack/nova"

[Joe Gordon (jogo)]

Marking as critical because we should not have any race conditions in the unit tests, and because we are seeing this in the gate

[aeva black (tenbrae)]

Adding Ironic since we inherited the db migration test code from Nova, and while it turns out that we're not testing db migrations in the gate due to a misconfiguration, if we were to enable them, we'd face the same race conditions in our unit tests.

[Matt Riedemann (mriedem)]

And oslo.db sync was recently merged on 5/22: https://review.openstack.org/89778

[Matt Riedemann (mriedem)]

Revert is up: https://review.openstack.org/#/c/99442/

[Viktor Serhieiev (vsergeyev)]

my 2 cents - Nova (and Ironic) do not use common code for migration tesing. So it's not nesessary to make this revert

[Roman Podoliaka (rpodolyaka)]

I'm almost sure this is caused by opportunistic db test case (which creates new schemas on demand using openstack_citest user credentials) not playing nicely with Nova migrations tests (which drop and recreate openstack_citest database).

I believe we've been seeing this since we merged a few commits which actually use new opportunistic db test cases (e.g. https://github.com/openstack/nova/commit/e43fa9571b436b7734213e34aaaaea650a7317c7). I'd argue, that the proper fix for this would be to make Nova migration tests not drop openstack_citest database, but rather use openstack_citest user credentials to create schemas on demand. FYI, we have a similar patch to oslo.db on review - https://review.openstack.org/#/c/93424/ and could provide a back port to oslo.incubator, if you like this approach.

A short term work around could be file locks.

[Hans Lindgren (hanlind)]

This looks similar to https://bugs.launchpad.net/nova/+bug/1157625 which has been around a long time.

[Viktor Serhieiev (vsergeyev)]

With the fresh mind I agree with Roman, that this issue caused by opportunistic db test case.
Here is the change in common code, with fixes this bug in Nova locally - http://paste.openstack.org/show/83776/

[OpenStack Infra (hudson-openstack)]

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

[Viktor Serhieiev (vsergeyev)]

See patch in Nova - https://review.openstack.org/#/c/99614/

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.openstack.org/99592
Committed: https://git.openstack.org/cgit/openstack/oslo-incubator/commit/?id=11273cfe53e1069ff2a233c74f2559ea7c68943c
Submitter: Jenkins
Branch: master

commit 11273cfe53e1069ff2a233c74f2559ea7c68943c
Author: Roman Podoliaka <email address hidden>
Date: Thu Jun 12 11:00:37 2014 +0300

    Prevent races in opportunistic db test cases

    Opportunistic db test cases create schemas on demand, so that each
    test case which inherits the base test case class, will get its own
    db schema (i. e. races between tests are not possible).

    In order to do schema provisioning we have to connect to RDBMS server
    first. So far we've been connecting to the openstack_citest database,
    which is guaranteed to exist on CI nodes. It turns out, there are a
    few test cases in Nova (maybe in other projects as well), that drop
    and recreate the openstack_citest database. If they happen to do that
    when the opportunistic db fixture is in the middle of provisioning a
    schema, those tests will fail (as there is an an open session to the
    database and thus it can't be dropped).

    This can be solved easily by changing the way we provision new
    schemas in opportunistic db test cases as actually, we don't have to
    connect to the openstack_citest database at all:

     - for MySQL we can use an empty db name to connect to MySQL server,
       but not to a particular database
     - PostgreSQL requires us to specify the database name. We can use
       the service postgres database here (PostgreSQL shell utils such
       as createdb, createuser, etc use it for the very same reason)

    Closes-Bug: #1328997

    Change-Id: Id82090743e8b93f6c960de066fd05d8a2f9fb934

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.openstack.org/99608
Committed: https://git.openstack.org/cgit/openstack/oslo.db/commit/?id=314496a5815f9e946f1ec87e6e6610c44bfc07bd
Submitter: Jenkins
Branch: master

commit 314496a5815f9e946f1ec87e6e6610c44bfc07bd
Author: Roman Podoliaka <email address hidden>
Date: Thu Jun 12 12:22:49 2014 +0300

    Prevent races in opportunistic db test cases

    Opportunistic db test cases create schemas on demand, so that each
    test case which inherits the base test case class, will get its own
    db schema (i. e. races between tests are not possible).

    In order to do schema provisioning we have to connect to RDBMS server
    first. So far we've been connecting to the openstack_citest database,
    which is guaranteed to exist on CI nodes. It turns out, there are a
    few test cases in Nova (maybe in other projects as well), that drop
    and recreate the openstack_citest database. If they happen to do that
    when the opportunistic db fixture is in the middle of provisioning a
    schema, those tests will fail (as there is an an open session to the
    database and thus it can't be dropped).

    This can be solved easily by changing the way we provision new
    schemas in opportunistic db test cases as actually, we don't have to
    connect to the openstack_citest database at all:

     - for MySQL we can use an empty db name to connect to MySQL server,
       but not to a particular database
     - PostgreSQL requires us to specify the database name. We can use
       the service postgres database here (PostgreSQL shell utils such
       as createdb, createuser, etc use it for the very same reason)

    Closes-Bug: #1328997

    Change-Id: I0dc0becc5cb40d3dab3289c865a96113522a0b9a

[OpenStack Infra (hudson-openstack)]

Change abandoned by Sean Dague (<email address hidden>) on branch: master
Review: https://review.openstack.org/99442

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.openstack.org/99614
Committed: https://git.openstack.org/cgit/openstack/nova/commit/?id=2e12e4b7a8a39397f56a290ee8113d7607d7f46b
Submitter: Jenkins
Branch: master

commit 2e12e4b7a8a39397f56a290ee8113d7607d7f46b
Author: Victor Sergeyev <email address hidden>
Date: Thu Jun 12 13:02:10 2014 +0300

    Sync "Prevent races in opportunistic db test cases"

    This sync change Id82090743e8b93f6c960de066fd05d8a2f9fb934 from
    oslo-incubator

    Opportunistic db test cases create schemas on demand, so that each
    test case which inherits the base test case class, will get its own
    db schema (i. e. races between tests are not possible).

    In order to do schema provisioning we have to connect to RDBMS server
    first. So far we've been connecting to the openstack_citest database,
    which is guaranteed to exist on CI nodes. It turns out, there are a
    few test cases in Nova (maybe in other projects as well), that drop
    and recreate the openstack_citest database. If they happen to do that
    when the opportunistic db fixture is in the middle of provisioning a
    schema, those tests will fail (as there is an open session to the
    database and thus it can't be dropped).

    This can be solved easily by changing the way we provision new
    schemas in opportunistic db test cases as actually, we don't have to
    connect to the openstack_citest database at all:

     - for MySQL we can use an empty db name to connect to MySQL server,
       but not to a particular database
     - PostgreSQL requires us to specify the database name. We can use
       the service postgres database here (PostgreSQL shell utils such
       as createdb, createuser, etc use it for the very same reason)

    Partial-Bug: #1328997

    Change-Id: Idc62b24b6ae51ea14f10e39d7b14115e36069637

[Mark McLoughlin (markmc)]

Victor - could you summarize which issues remain in Nova and whether the status should really still be InProgress (i.e. are there fixes posted for the other issues)

[OpenStack Infra (hudson-openstack)]

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

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.openstack.org/93424
Committed: https://git.openstack.org/cgit/openstack/oslo.db/commit/?id=c34c32e09ed1f170dfe62c7daedf0cd60f57a833
Submitter: Jenkins
Branch: master

commit c34c32e09ed1f170dfe62c7daedf0cd60f57a833
Author: Ilya Pekelny <email address hidden>
Date: Tue May 13 14:44:54 2014 +0300

    Opportunistic migration tests

    Migrations should be tested with real database backends. For this goal
    intended number of base test cases which used in current implementation.
    Previously there were two ways to run migration tests: using
    opportunistic test cases (default way we've been using on CI) and
    by using database connection strings, specified in test_migrations.conf,
    for every particular database test case. For the sake of simplicity and
    consistency we are moving to using of opportunistic db test cases here.

    With this change we are free from locking, so we don't need `lockfile`
    anymore.

    Closes-Bug: #1327397
    Closes-Bug: #1328997
    Change-Id: I92b1dcd830c4755f429a0f6529911e607c2c7de7

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.openstack.org/107053
Committed: https://git.openstack.org/cgit/openstack/ironic/commit/?id=ffd83dc3af170608a867ed1721a90747e67d2934
Submitter: Jenkins
Branch: master

commit ffd83dc3af170608a867ed1721a90747e67d2934
Author: Victor Sergeyev <email address hidden>
Date: Tue Jul 15 12:41:34 2014 +0300

    Use opportunistic approach for migration testing

    Refactored migration tests due to use OpportunisticTestCase, removed
    unused code and ``test_migrations.conf`` file.

    This change allows tests use database ``openstack_citest`` only
    for connection to the database backend. The main feature of this
    approach is - for each migration test will be created new database
    with random name. This will avoid migration tests of race conditions
    and reduce tests intersection.

    ``test_migrations.conf`` file was removed, because we create test
    database for migration test, so we no longer need test database credentials.

    Closes-Bug: #1327397
    Closes-Bug: #1328997

    Change-Id: I95ad140ba5f483cd3dc36e2b78f140826d57624f

[Tracy Jones (tjones-i)]

looks like all patches for this merged so this should be released for nova

[OpenStack Infra (hudson-openstack)]

Fix proposed to branch: stable/icehouse
Review: https://review.openstack.org/115059