[CLOUDSTACK-2625] Duplicate usage records when listing large number of records

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 4.2.0
• Fix Version/s: 4.5.0
• Component/s: Usage
• Security Level: Public (Anyone can view this level - this is the default.)
• Assignee: Kishan
• Reporter: Kishan
• Created: 22/May/13 13:00
• Updated: 09/Oct/14 05:28
• Resolved: 09/Oct/14 05:01

Description

listUsageRecords sorts results by start_date. Using pagination, sample mysql query is as follows.

SELECT cloud_usage.id, cloud_usage.zone_id, cloud_usage.account_id,
cloud_usage.domain_id, cloud_usage.description, cloud_usage.usage_display,
cloud_usage.usage_type, cloud_usage.raw_usage, cloud_usage.vm_instance_id,
cloud_usage.vm_name, cloud_usage.offering_id, cloud_usage.template_id,
cloud_usage.usage_id, cloud_usage.type, cloud_usage.size,
cloud_usage.virtual_size, cloud_usage.network_id, cloud_usage.start_date,
cloud_usage.end_date FROM cloud_usage WHERE cloud_usage.usage_type = 9 AND
cloud_usage.start_date BETWEEN '2012-01-01 00:00:00' AND '2013-01-31 23:59:59'
AND cloud_usage.end_date BETWEEN '2012-01-01 00:00:00' AND '2013-01-31
23:59:59' ORDER BY cloud_usage.start_date DESC LIMIT 18000, 2000

start_date is not unique and when querying large datasets, multiple pages can return the same data.
ORDER BY cloud_usage.start_date doesn't create a stable sorted dataset.

Query should include id in ORDER BY to make it stable.

Issue Links

• is duplicated by: CLOUDSTACK-3401 Small page sizes return duplicate results