Deleted flavor influences new flavor with same id in 'flavor-show' under psql

[wingwj]

When you create a new flavor with a deleted flavorid, the result in mysql will show the new record. That's OK.

But if you operate these commands under psql, you'll find the result will always be the old deleted one.

That means, the flavorid will always be occupied by the first record who use this flavorid under psql,
even if this flavor has already been deleted.

More info can be found at here: http://paste.openstack.org/show/72752/

[wingwj (wingwj)]

I checked the sql used in 'flavor-show', through the sqlalchemy framework, the sql for mysql and psql is almost the same.
The core sql is like this:
--------------

'SELECT instance_types.created_at AS instance_types_created_at, instance_types.updated_at AS instance_types_updated_at, instance_types.deleted_at AS instance_types_deleted_at, instance_types.deleted AS instance_types_deleted, instance_types.id AS instance_types_id, instance_types.name AS instance_types_name, instance_types.memory_mb AS instance_types_memory_mb, instance_types.vcpus AS instance_types_vcpus, instance_types.root_gb AS instance_types_root_gb, instance_types.ephemeral_gb AS instance_types_ephemeral_gb, instance_types.flavorid AS instance_types_flavorid, instance_types.swap AS instance_types_swap, instance_types.rxtx_factor AS instance_types_rxtx_factor, instance_types.vcpu_weight AS instance_types_vcpu_weight, instance_types.disabled AS instance_types_disabled, instance_types.is_public AS instance_types_is_public
        FROM instance_types
        WHERE instance_types.flavorid = E'666'
         LIMIT 1',

--------------

I inputted "select * from instance_types where flavorid='xxx'; " in mysql and psql respectively,
the result is difference: http://paste.openstack.org/show/72763/

The response of "select * from where" is the same with "select * from" under psql.
The new non-deleted record locates at the bottom.

But the new deleted one under mysql is on the top.

--------------

Therefore, plus 'LIMIT 1' in sql, 'flavor-show' under psql will always show the oldest one.
And the mysql will always give the non-deleted one correctly.

[Rui Chen (kiwik-chenrui)]

Maybe we need to add "order by" in order to maintain consistent behavior in mysql and postgresql.

[wingwj (wingwj)]

Recording in the manual of mysql & psql, if you don't specify the sort-type through 'order by', you will get an unpredictable subset of the query's rows.

[wingwj (wingwj)]

After some tests and comparison, I found the issue is caused by the different procession to index between mysql and psql.

The 'instance_types' has two main indexes, "uniq_instance_types0flavorid0deleted" and "uniq_instance_types0name0deleted".

-------------

Add 'explain' to 'select * from instance_types;' and 'select * from instance_types where flavorid=xxx;',
and execute them under mysql and psql separately.

You'll find mysql use index for specified querying, but psql don't.

The info is registered here in detail: http://paste.openstack.org/show/72993/. Plz check it.

#### Psql ####:

nova=# explain select * from instance_types where flavorid='666';
                           QUERY PLAN
-----------------------------------------------------------------
 Seq Scan on instance_types (cost=0.00..1.06 rows=1 width=1098)
   Filter: ((flavorid)::text = '666'::text)
(2 rows)

#### MySQL ####:

mysql> explain select * from instance_types where flavorid="666";
+----+-------------+----------------+------+--------------------------------------+--------------------------------------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------------+------+--------------------------------------+--------------------------------------+---------+-------+------+-------------+
| 1 | SIMPLE | instance_types | ref | uniq_instance_types0flavorid0deleted | uniq_instance_types0flavorid0deleted | 768 | const | 4 | Using where |
+----+-------------+----------------+------+--------------------------------------+--------------------------------------+---------+-------+------+-------------+
1 row in set (0.02 sec)

###########

So, we need to add 'order by' into the sql, to ensure the sequence returned from psql and mysql is the same.

[OpenStack Infra (hudson-openstack)]

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

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.openstack.org/79252
Committed: https://git.openstack.org/cgit/openstack/nova/commit/?id=4bbb4d88595cfd5f75528d8789be94d0ec9fbe62
Submitter: Jenkins
Branch: master

commit 4bbb4d88595cfd5f75528d8789be94d0ec9fbe62
Author: wingwj <email address hidden>
Date: Mon Mar 10 14:35:13 2014 +0800

    Fix difference between mysql & psql of flavor-show

    If you create a new flavor using a deleted flavorid,
    the result of flavor-show is different between mysql and psql.

    This issue is caused by the different processing for index in each db.

    We need to specify the sort-type to avoid the issue.

    Change-Id: Ib1029e80c1b981e1ec86d954b63f83650c9b1cc1
    Closes-Bug: #1288636

[OpenStack Infra (hudson-openstack)]

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

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.openstack.org/81420
Committed: https://git.openstack.org/cgit/openstack/nova/commit/?id=9f89c30742abc18eb856022d18976e2685257e1a
Submitter: Jenkins
Branch: stable/havana

commit 9f89c30742abc18eb856022d18976e2685257e1a
Author: wingwj <email address hidden>
Date: Mon Mar 10 14:35:13 2014 +0800

    Fix difference between mysql & psql of flavor-show

    If you create a new flavor using a deleted flavorid,
    the result of flavor-show is different between mysql and psql.

    This issue is caused by the different processing for index in each db.

    We need to specify the sort-type to avoid the issue.

    Change-Id: Ib1029e80c1b981e1ec86d954b63f83650c9b1cc1
    Closes-Bug: #1288636
    (cherry picked from commit 4bbb4d88595cfd5f75528d8789be94d0ec9fbe62)