Duplicate records on the 'image_locations' table are not eliminated fully in upgrade.

[Takashi Natsume]

When upgrading from stable/havana to master, database schema and records are upgraded(migrated) by 'glance-manage db_sync' command.
On the 'image_locations' table, duplicate records that have same set of image_id, value and meta_data should be deleted, only leaving one record.
But the number of the records is 3 or more, they are not eliminated fully.
(Only one record is deleted.)

glance/db/sqlalchemy/migrate_repo/versions/031_remove_duplicated_locations.py should be fixed.

===
mysql> select * from image_locations;
+----+--------------------------------------+---------------------------------------------------------------------------+---------------------+---------------------+------------+---------+-----------+
| id | image_id | value | created_at | updated_at | deleted_at | deleted | meta_data |
+----+--------------------------------------+---------------------------------------------------------------------------+---------------------+---------------------+------------+---------+-----------+
| 1 | 125baa71-a295-4f78-9e9a-e642ecf5c4a5 | file:///opt/stack/data/glance/images/125baa71-a295-4f78-9e9a-e642ecf5c4a5 | 2014-01-15 01:26:12 | 2014-01-15 01:26:12 | NULL | 0 | {} |
| 2 | 4a8cb854-4807-4fe5-8db0-c76e3d297879 | file:///opt/stack/data/glance/images/4a8cb854-4807-4fe5-8db0-c76e3d297879 | 2014-01-15 01:26:15 | 2014-01-15 01:26:15 | NULL | 0 | {} |
| 3 | bbeb25f8-70ed-4989-9f34-d857b8cc1887 | file:///opt/stack/data/glance/images/bbeb25f8-70ed-4989-9f34-d857b8cc1887 | 2014-01-15 01:26:19 | 2014-01-15 01:26:19 | NULL | 0 | {} |
+----+--------------------------------------+---------------------------------------------------------------------------+---------------------+---------------------+------------+---------+-----------+
3 rows in set (0.00 sec)

stack@devstack-havana:/git/repos/devstack$ glance image-list
+--------------------------------------+---------------------------------+-------------+------------------+----------+--------+
| ID | Name | Disk Format | Container Format | Size | Status |
+--------------------------------------+---------------------------------+-------------+------------------+----------+--------+
| bbeb25f8-70ed-4989-9f34-d857b8cc1887 | cirros-0.3.1-x86_64-uec | ami | ami | 25165824 | active |
| 125baa71-a295-4f78-9e9a-e642ecf5c4a5 | cirros-0.3.1-x86_64-uec-kernel | aki | aki | 4955792 | active |
| 4a8cb854-4807-4fe5-8db0-c76e3d297879 | cirros-0.3.1-x86_64-uec-ramdisk | ari | ari | 3714968 | active |
+--------------------------------------+---------------------------------+-------------+------------------+----------+--------+

stack@devstack-havana:/git/repos/devstack$ keystone token-get
stack@devstack-havana:/git/repos/devstack$ curl -i -X PATCH -H "X-Auth-Token: $AUTH" -H "Content-Type: application/openstack-images-v2.1-json-patch" http://10.0.2.15:9292/v2/images/bbeb25f8-70ed-4989-9f34-d857b8cc1887 -d '[{"op": "add", "path": "/locations/-", "value": {"url": "file:///opt/stack/data/glance/images/bbeb25f8-70ed-4989-9f34-d857b8cc1887", "metadata": {}}}]'
HTTP/1.1 200 OK
Content-Length: 638
Content-Type: application/json; charset=UTF-8
X-Openstack-Request-Id: req-4d0908ba-cc6c-41fb-96e6-d23e4d95caaf
Date: Wed, 15 Jan 2014 01:41:38 GMT

{"status": "active", "name": "cirros-0.3.1-x86_64-uec", "tags": [], "kernel_id": "125baa71-a295-4f78-9e9a-e642ecf5c4a5", "container_format": "ami", "created_at": "2014-01-15T01:26:18Z", "ramdisk_id": "4a8cb854-4807-4fe5-8db0-c76e3d297879", "disk_format": "ami", "updated_at": "2014-01-15T01:41:38Z", "visibility": "public", "self": "/v2/images/bbeb25f8-70ed-4989-9f34-d857b8cc1887", "protected": false, "id": "bbeb25f8-70ed-4989-9f34-d857b8cc1887", "file": "/v2/images/bbeb25f8-70ed-4989-9f34-d857b8cc1887/file", "checksum": "f8a2eeee2dc65b3d9b6e63678955bd83", "min_disk": 0, "size": 25165824, "min_ram": 0, "schema": "/v2/schemas/image"}
stack@devstack-havana:/git/repos/devstack$ curl -i -X PATCH -H "X-Auth-Token: $AUTH" -H "Content-Type: application/openstack-images-v2.1-json-patch" http://10.0.2.15:9292/v2/images/bbeb25f8-70ed-4989-9f34-d857b8cc1887 -d '[{"op": "add", "path": "/locations/-", "value": {"url": "file:///opt/stack/data/glance/images/bbeb25f8-70ed-4989-9f34-d857b8cc1887", "metadata": {}}}]'
HTTP/1.1 200 OK
Content-Length: 638
Content-Type: application/json; charset=UTF-8
X-Openstack-Request-Id: req-bfa7464e-ccb4-4764-8c13-aa744f3b00a5
Date: Wed, 15 Jan 2014 01:43:09 GMT

{"status": "active", "name": "cirros-0.3.1-x86_64-uec", "tags": [], "kernel_id": "125baa71-a295-4f78-9e9a-e642ecf5c4a5", "container_format": "ami", "created_at": "2014-01-15T01:26:18Z", "ramdisk_id": "4a8cb854-4807-4fe5-8db0-c76e3d297879", "disk_format": "ami", "updated_at": "2014-01-15T01:43:09Z", "visibility": "public", "self": "/v2/images/bbeb25f8-70ed-4989-9f34-d857b8cc1887", "protected": false, "id": "bbeb25f8-70ed-4989-9f34-d857b8cc1887", "file": "/v2/images/bbeb25f8-70ed-4989-9f34-d857b8cc1887/file", "checksum": "f8a2eeee2dc65b3d9b6e63678955bd83", "min_disk": 0, "size": 25165824, "min_ram": 0, "schema": "/v2/schemas/image"}

mysql> select * from image_locations where deleted != 1;
+----+--------------------------------------+---------------------------------------------------------------------------+---------------------+---------------------+------------+---------+-----------+
| id | image_id | value | created_at | updated_at | deleted_at | deleted | meta_data |
+----+--------------------------------------+---------------------------------------------------------------------------+---------------------+---------------------+------------+---------+-----------+
| 1 | 125baa71-a295-4f78-9e9a-e642ecf5c4a5 | file:///opt/stack/data/glance/images/125baa71-a295-4f78-9e9a-e642ecf5c4a5 | 2014-01-15 01:26:12 | 2014-01-15 01:26:12 | NULL | 0 | {} |
| 2 | 4a8cb854-4807-4fe5-8db0-c76e3d297879 | file:///opt/stack/data/glance/images/4a8cb854-4807-4fe5-8db0-c76e3d297879 | 2014-01-15 01:26:15 | 2014-01-15 01:26:15 | NULL | 0 | {} |
| 6 | bbeb25f8-70ed-4989-9f34-d857b8cc1887 | file:///opt/stack/data/glance/images/bbeb25f8-70ed-4989-9f34-d857b8cc1887 | 2014-01-15 01:43:09 | 2014-01-15 01:43:09 | NULL | 0 | {} |
| 7 | bbeb25f8-70ed-4989-9f34-d857b8cc1887 | file:///opt/stack/data/glance/images/bbeb25f8-70ed-4989-9f34-d857b8cc1887 | 2014-01-15 01:43:09 | 2014-01-15 01:43:09 | NULL | 0 | {} |
| 8 | bbeb25f8-70ed-4989-9f34-d857b8cc1887 | file:///opt/stack/data/glance/images/bbeb25f8-70ed-4989-9f34-d857b8cc1887 | 2014-01-15 01:43:09 | 2014-01-15 01:43:09 | NULL | 0 | {} |
+----+--------------------------------------+---------------------------------------------------------------------------+---------------------+---------------------+------------+---------+-----------+
5 rows in set (0.00 sec)

mysql> select image_id,value,meta_data,count(*) from image_locations where deleted != 1 group by image_id,value,meta_data;
+--------------------------------------+---------------------------------------------------------------------------+-----------+----------+
| image_id | value | meta_data | count(*) |
+--------------------------------------+---------------------------------------------------------------------------+-----------+----------+
| 125baa71-a295-4f78-9e9a-e642ecf5c4a5 | file:///opt/stack/data/glance/images/125baa71-a295-4f78-9e9a-e642ecf5c4a5 | {} | 1 |
| 4a8cb854-4807-4fe5-8db0-c76e3d297879 | file:///opt/stack/data/glance/images/4a8cb854-4807-4fe5-8db0-c76e3d297879 | {} | 1 |
| bbeb25f8-70ed-4989-9f34-d857b8cc1887 | file:///opt/stack/data/glance/images/bbeb25f8-70ed-4989-9f34-d857b8cc1887 | {} | 3 |
+--------------------------------------+---------------------------------------------------------------------------+-----------+----------+
3 rows in set (0.04 sec)

stack@devstack-havana:/opt/stack/glance$ git checkout master
stack@devstack-havana:/opt/stack/glance$ glance-manage db_sync

mysql> select * from image_locations where deleted != 1;
+----+--------------------------------------+---------------------------------------------------------------------------+---------------------+---------------------+------------+---------+-----------+
| id | image_id | value | created_at | updated_at | deleted_at | deleted | meta_data |
+----+--------------------------------------+---------------------------------------------------------------------------+---------------------+---------------------+------------+---------+-----------+
| 1 | 125baa71-a295-4f78-9e9a-e642ecf5c4a5 | file:///opt/stack/data/glance/images/125baa71-a295-4f78-9e9a-e642ecf5c4a5 | 2014-01-15 01:26:12 | 2014-01-15 01:26:12 | NULL | 0 | {} |
| 2 | 4a8cb854-4807-4fe5-8db0-c76e3d297879 | file:///opt/stack/data/glance/images/4a8cb854-4807-4fe5-8db0-c76e3d297879 | 2014-01-15 01:26:15 | 2014-01-15 01:26:15 | NULL | 0 | {} |
| 7 | bbeb25f8-70ed-4989-9f34-d857b8cc1887 | file:///opt/stack/data/glance/images/bbeb25f8-70ed-4989-9f34-d857b8cc1887 | 2014-01-15 01:43:09 | 2014-01-15 01:43:09 | NULL | 0 | {} |
| 8 | bbeb25f8-70ed-4989-9f34-d857b8cc1887 | file:///opt/stack/data/glance/images/bbeb25f8-70ed-4989-9f34-d857b8cc1887 | 2014-01-15 01:43:09 | 2014-01-15 01:43:09 | NULL | 0 | {} |
+----+--------------------------------------+---------------------------------------------------------------------------+---------------------+---------------------+------------+---------+-----------+
4 rows in set (0.00 sec)

mysql> select image_id,value,meta_data,count(*) from image_locations where deleted != 1 group by image_id,value,meta_data;
+--------------------------------------+---------------------------------------------------------------------------+-----------+----------+
| image_id | value | meta_data | count(*) |
+--------------------------------------+---------------------------------------------------------------------------+-----------+----------+
| 125baa71-a295-4f78-9e9a-e642ecf5c4a5 | file:///opt/stack/data/glance/images/125baa71-a295-4f78-9e9a-e642ecf5c4a5 | {} | 1 |
| 4a8cb854-4807-4fe5-8db0-c76e3d297879 | file:///opt/stack/data/glance/images/4a8cb854-4807-4fe5-8db0-c76e3d297879 | {} | 1 |
| bbeb25f8-70ed-4989-9f34-d857b8cc1887 | file:///opt/stack/data/glance/images/bbeb25f8-70ed-4989-9f34-d857b8cc1887 | {} | 2 |
+--------------------------------------+---------------------------------------------------------------------------+-----------+----------+
3 rows in set (0.00 sec)

[Ian Cordasco (icordasc)]

We need to reproduce this and come up with a fix. If this is still an issue we'll want to fix this for Kilo and maybe backport to Juno

[Ian Cordasco (icordasc)]

Also, for what it's worth, we cannot fix the existing migration. We'll need to create a new one. https://review.openstack.org/#/c/33102/ is an example of a previous time where this using a new migration to fix an old one was done.