alarm creation with sqlalchemy fails with intermittent IntegrityError

[Eoghan Glynn]

Alarm creation fails intermittently when the sqlalchemy driver is used:

(IntegrityError) (1452, 'Cannot add or update a child row: a foreign key constraint fails (`ceilometer`.`alarm`, CONSTRAINT `fk_alarm_project_id` FOREIGN KEY (`project_id`) REFERENCES `project` (`id`))') 'INSERT INTO alarm (id, enabled, name, type, description, timestamp, user_id, project_id, state, state_timestamp, ok_actions, alarm_actions, insufficient_data_actions, repeat_actions, rule) VALUES ...

This appears to be an artifact of how the alarm creation logic was added to the sqlalchemy driver in such a way as to rely on sqlalchemy session merge to create the corresponding entries in the project & user tables[1].

Then subsequently a *later* migration[2] was added to assert foreign key constraints for those project & user IDs.

I don't think session merge is a reliable way to ensure creation of the corresponding rows in the related tables in order to satisfy the foreign key constraints. Instead we should create the project & user IDs explicitly.

[1] https://github.com/openstack/ceilometer/commit/2c84007b
[2] https://github.com/openstack/ceilometer/commit/fa6f9807

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.openstack.org/58587
Committed: http://github.com/openstack/ceilometer/commit/b2b500c83cf5a81269c84a16f9a30ab7fa4c8ee6
Submitter: Jenkins
Branch: master

commit b2b500c83cf5a81269c84a16f9a30ab7fa4c8ee6
Author: Eoghan Glynn <email address hidden>
Date: Tue Nov 26 16:35:36 2013 +0000

    Avoid intermittent integrity error on alarm creation

    Fixes bug 1255107

    Previously, alarm creation failed intermittently with the
    sqlalchemy driver, due to the reliance on session merge to create
    the project & user IDs if not already encountered. This approach
    conflicted with the strict foreign key constraints that were
    added to the alarms table as an after-thought.

    Now, we avoid the potential problem by explicitly creating the
    project and user IDs if necessary.

    Unfortunately, this cannot be tested via the scenario tests,
    as there we use sqlite (the problematic migration does not
    apply the foreign key constraints for that engine).

    Change-Id: Ieaed676a0a68725242fadbf658646b874d6851ce

[OpenStack Infra (hudson-openstack)]

Fix proposed to branch: stable/havana
Review: https://review.openstack.org/58818

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.openstack.org/58818
Committed: http://github.com/openstack/ceilometer/commit/59f25a4f062a6e9d3a75baf38400ea5fc41c0a68
Submitter: Jenkins
Branch: stable/havana

commit 59f25a4f062a6e9d3a75baf38400ea5fc41c0a68
Author: Eoghan Glynn <email address hidden>
Date: Tue Nov 26 16:35:36 2013 +0000

    Avoid intermittent integrity error on alarm creation

    Fixes bug 1255107

    Previously, alarm created failed intermittently with the
    sqlalchemy driver, due to the reliance on session merge to create
    the project & user IDs if not already encountered. This approach
    conflicted with the strict foreign key constraints that were
    added to the alarms table as an after-thought.

    Now, we avoid the potential problem by creating the project and
    user IDs explicitly.

    Unfortunately, this cannot be tested via the scenario tests,
    as there we use sqlite (the problematic migration does not
    apply the foreign key constraints for that engine).

    Change-Id: Ieaed676a0a68725242fadbf658646b874d6851ce
    (cherry picked from commit b2b500c83cf5a81269c84a16f9a30ab7fa4c8ee6)