Zones are returned in a random order

[dpassante]

ISSUE TYPE

• Bug Report

COMPONENT NAME

API

CLOUDSTACK VERSION

4.13.0.0

CONFIGURATION

N/A

OS / ENVIRONMENT

N/A

SUMMARY

listZones returns the zones in a "random" order when it has not been explicitly defined with the new sort key feature.

This impacts the correct functioning of the Ansible Cloudstack modules which use the first zone if no zone parameter is given.

STEPS TO REPRODUCE

Keep zones unsorted:

mysql> select id, name, sort_key from data_center;
+----+----------------------------+----------+
| id | name                       | sort_key |
+----+----------------------------+----------+
|  1 | Sandbox-simulator-basic    |        0 |
|  2 | Sandbox-simulator-advanced |        0 |
+----+----------------------------+----------+
2 rows in set (0.00 sec)

And use a playbook in which the zone param is not given.
Several calls to listZones also do the trick.

EXPECTED RESULTS

A list of zones sorted by SQL ID when no sort key are configured to avoid breaking some existing Ansible deployments.

Ensure that a sort key is automatically incremented for new zones and existing zones in the case of a Cloudstack upgrade could also be a nice feature.

ACTUAL RESULTS

$ for in in `seq 1 1 10`; do cs listZones |grep -m1 name; done
      "name": "Sandbox-simulator-basic",
      "name": "Sandbox-simulator-basic",
      "name": "Sandbox-simulator-basic",
      "name": "Sandbox-simulator-basic",
      "name": "Sandbox-simulator-basic",
      "name": "Sandbox-simulator-basic",
      "name": "Sandbox-simulator-advanced",
      "name": "Sandbox-simulator-advanced",
      "name": "Sandbox-simulator-advanced",
      "name": "Sandbox-simulator-advanced",

[dpassante]

In fact, the "problem" seems to come from the fact that zones are created with the sort key 0 which has priority by default.

+----+--------------------------------------+----------+
| id | name                                 | sort_key |
+----+--------------------------------------+----------+
|  1 | Sandbox-simulator-basic              |        0 |
|  2 | Sandbox-simulator-advanced           |        0 |
|  3 | ansible-test-dpassante-55511421-zone |        0 |
|  4 | ansible-test-dpassante-46776470-zone |        0 |
+----+--------------------------------------+----------+

The sort keys are not unique, we also have the same behavior when we assign the same rank to 2 zones from the API.

Once the sorting has been done, the new zones are returned first.

+----+--------------------------------------+----------+
| id | name                                 | sort_key |
+----+--------------------------------------+----------+
|  1 | Sandbox-simulator-basic              |        1 |
|  2 | Sandbox-simulator-advanced           |        2 |
|  3 | ansible-test-dpassante-91763618-zone |        0 |
|  4 | ansible-test-dpassante-38650808-zone |        0 |
+----+--------------------------------------+----------+

$ cs listZones |grep name
      "name": "ansible-test-dpassante-38650808-zone",
      "name": "ansible-test-dpassante-91763618-zone",
      "name": "Sandbox-simulator-basic",
      "name": "Sandbox-simulator-advanced",

[rohityadavcloud]

@dpassante I tested 4.14/master I'm not able to see this. In UI as root admin you can re-order the zones and the order is honoured.

[rohityadavcloud]

Tested latest master, could not see the issue.

[resmo]

I see this with in 4.13.1
note the zone that changes from basic to advance from first test to second test.

https://github.com/ngine-io/ansible-collection-cloudstack/pull/40/checks?check_run_id=1162936780#step:6:3610

TASK [cs_vmsnapshot : test revert snapshot] ************************************
changed: [testhost] => {"account": "admin", "api_http_method": "get", "api_key": "DL3Be8XtxsrKJOMRIIPR_2LUdknE5n8V33u41NWaetVcnKAQTeQhkDDVKAJCIyHV1I4fY3Phw8B49BpSnfLKtg", "api_region": "cloudstack", "api_timeout": 60, "api_url": "http://cloudstack-sim:8888/client/api", "api_verify_ssl_cert": null, "changed": true, "created": "2020-09-24T22:12:33+0000", "current": true, "display_name": "ansible-test-fv-az176-89231118_snapshot", "domain": "ROOT", "id": "230bab25-dcd6-4641-97c5-79396730eef6", "name": "i-2-12-QA_VS_20200924221233", "state": "Ready", "tags": [], "type": "DiskAndMemory", "zone": "Sandbox-simulator-basic"}

TASK [cs_vmsnapshot : verify test revert snapshot] *****************************
ok: [testhost] => {
    "changed": false,
    "msg": "All assertions passed"
}

TASK [cs_vmsnapshot : test remove snapshot in check mode] **********************
fatal: [testhost]: FAILED! => {"api_http_method": "get", "api_key": "DL3Be8XtxsrKJOMRIIPR_2LUdknE5n8V33u41NWaetVcnKAQTeQhkDDVKAJCIyHV1I4fY3Phw8B49BpSnfLKtg", "api_region": "cloudstack", "api_timeout": 60, "api_url": "http://cloudstack-sim:8888/client/api", "api_verify_ssl_cert": null, "changed": false, "msg": "Virtual machine 'ansible-test-fv-az176-89231118-vm-snapshot' not found", "zone": "Sandbox-simulator-advanced"}

this is the code for the zone selection: https://github.com/ngine-io/ansible-collection-cloudstack/blob/master/plugins/module_utils/cloudstack.py#L467

If this is a wanted behavior, what is the correct way to select the default zone?

[rvalle]

@resmo @rhtyd

The result that @resmo expect, or ansible modules expect, would require zones to be sorted by the expression (sortkey,id), as you know when sortkey has the same value, id is used... etc.

So I have been looking at the code to check what the actual implemented order criteria is:

looks to me like the method is here:

cloudstack/server/src/main/java/com/cloud/api/query/QueryManagerImpl.java

Line 3053 in 82b6971

private Pair<List<DataCenterJoinVO>, Integer> listDataCentersInternal(ListZonesCmd cmd) {

But this method seems to rely on an utility:

cloudstack/server/src/main/java/com/cloud/api/query/QueryManagerImpl.java

Line 3075 in 82b6971

Filter searchFilter = new Filter(DataCenterJoinVO.class, "sortKey", SortKeyAscending.value(), cmd.getStartIndex(), cmd.getPageSizeVal());

Which seems to take a single field as order to build the underlying SQL.

This utility would need to be restructured so to allow more than one field to be specified as order key.

On the other hand @resmo, are you sure this has ever worked? I mean that cloudstack has returned zones in a deterministic order in the past? It is common for databases not to assure any row order when no order clause is specified but still to return the rows in the order they were created...

[weizhouapache]

@rvalle agrees. It should be a regression issue caused by #3242
sort_key are all 0 in cloud.data_center table.

[DaanHoogland]

@resmo @dpassante do you have a fix or work around? it seems to me not a serious issue but please correct me on that.
@rhtyd @shwstppr @davidjumani is sort order still used the same way in primate? should the default be -1 instead of 0 for instance?

[resmo]

@DaanHoogland well, we have to make the previous optional zone argument a required argument in almost all modules. This will break backwards compatibly, which means we make most likely many users angry.

You can call this ""work around" I call it a bad user experience for a bug introduced by cloudstack and which should be fixed by cloudstack and not by its clients or users.

[weizhouapache]

@resmo this workaround should work for you.

UPDATE `cloud`.`data_center` SET `sort_key` = `id` WHERE `sort_key` = 0;

this change will break the ordering of zones if users set sort_key to a non-zero value and use sort_key to order zones. so I will not sure if we can merge this change into 4.14.1.0 or 4.15.0.0

this issue might exists with template, disk offering, service offering, network offering as well.