René's URL Explorer Experiment

{"@context":"https://schema.org","@type":"DiscussionForumPosting","headline":"Usage Server repeatedly reprocesses historical usage and creates duplicate cloud_usage records for usage_type=1","articleBody":"### problem\n\nWe are experiencing what appears to be the same issue described in #13112, however in our environment it affects `usage_type = 1` (default VM usage) instead of the usage type reported in the original issue.\nThe Usage Server repeatedly reprocesses historical usage records from a fixed timestamp and continuously inserts duplicate records into the `cloud_usage` table.\n\n### **observed behaviour**\n\nThe Usage Server repeatedly processes the same historical records and generates duplicate rows in `cloud_usage`.\n\n* start date repeatedly reprocessed: `2026-05-29T22:00:00+0200`\n\n### **relation to #13112**\n\nThis issue appears very similar to #13112.\n\nDifferences:\n\n* #13112 reported the problem for usage_type = `13`\n* We observe the same behaviour for usage_type = `1`\n* The issue was reported as fixed in a later CloudStack release by the original Poster, however we are running `4.22.1.0` which is newer than the reported version.\n\nBecause the symptoms are effectively identical, it indicates that the issue still persists in newer versions.\n\n### **evidence**\n\nRelevant Usage Server logs:\n\nDuring the period where duplicate usage records are generated, the Usage Server repeatedly processes historical time ranges and emits SQL integrity constraint violations.\nA log snippet is attached. VM and account names have been redacted from the logs for data protection purposes. \n\n[_usage.2026-05-28.log](https://github.com/user-attachments/files/28835661/_usage.2026-05-28.log)\n\nA representative example is shown below:\n`2026-05-29 23:15:00,005 INFO Parsing usage records between [2026-05-29T20:00:00+0000] and [2026-05-29T20:59:59+0000]\nERROR: Duplicate entry '570-2026-05-29 20:50:40' for key 'id' java.sql.SQLIntegrityConstraintViolationException\nWARN: Failed to create usage event id: 1703940 type: VOLUME.CREATE due to Entity already exists`\nThe exception indicates that the Usage Server is attempting to insert records that already exist. This appears to coincide with the repeated processing of historical usage intervals and the continuous growth of duplicate usage records described above.\n\nTo quantify the issue, we wrote a Python script that hashes usage records and counts identical entries for a single virtual machine grouped by hour.\nThe expected result is exactly one record per hourly interval (`count = 1`).\nThis expectation is met until `2026-05-29 22:00`, after which duplicate records begin appearing. The duplication follows a distinct pattern:\n\n* `2026-05-29 22:00` → 204 identical records\n* `2026-05-29 23:00` → 203 identical records\n* `2026-05-30 00:00` → 202 identical records\n* `2026-05-30 01:00` → 201 identical records\n\nThe pattern continues with each subsequent hourly bucket containing one fewer duplicate than the previous bucket.\nThis suggests that historical usage intervals are being repeatedly regenerated or reprocessed by the Usage Server. While the total number of records continues to increase over time, the number of duplicates associated with each successive hourly interval decreases by one.\nExample output for a single VM:\n\n`{`\\\n`  \"2026-05-29\": {`\\\n`    \"21\": 1,`\\\n`    \"22\": 204,`\\\n`    \"23\": 203`\\\n`  },`\\\n`  \"2026-05-30\": {`\\\n`    \"00\": 202,`\\\n`    \"01\": 201,`\\\n`    \"02\": 200,`\\\n`    \"03\": 199`\\\n`  }`\\\n`}`\n\n### versions\n\nOS: `Ubuntu 24.04.4 LTS`\nHypervisor: KVM, `libvirt 10.0.0-2ubuntu8.13`\nDatabase: `MariaDB 10.11.14-MariaDB-0ubuntu0.24.04.1-log Ubuntu 24.04`\nCloudStack: `4.22.1.0`\n\n### The steps to reproduce the bug\n\nThe exact trigger is currently unknown. In our environment, the issue can be observed with the following setup:\n\n1. Enable the Usage Server in Apache CloudStack.\n2. Configure:\n   - `usage.stats.job.aggregation.range = 60`\n   - `usage.stats.job.exec.time = 00:15`\n3. Allow the Usage Server to run normally for an extended period.\n4. Observe the `cloud_usage` table and Usage Server logs.\n5. Duplicate usage records begin appearing and continue to accumulate over time.\n\n### What to do about it?\n\nOne observation that might be relevant, is a start-timestamp on 2026-05-29 at 20:00:14 (UTC). This is the only differing usage record. All other duplicates are exactly the same (within VM, day and hour) and their start-timestamps are all for second \"00\". Without knowing the actual contexts, it does look as if this one existing usage record for second \"14\" kind of congests the system now. Meaning the server did create an entry for second \"14\". At the same time it tried to create an entry for second \"00\". This failed due to the constraint-violation by having the same ID as the entry for second \"14\". This is observable in the logs.\n\nStarting the following hour the system is indeed creating entries for all subsequent usage records including the one for the timestamp in question. But somehow maybe because of this one \"failed\" entry, the system does not recognize all subsequently created entries as valid/processed and recreates all of them again every hour.","author":{"url":"https://github.com/mwa-sudo","@type":"Person","name":"mwa-sudo"},"datePublished":"2026-06-11T11:14:10.000Z","interactionStatistic":{"@type":"InteractionCounter","interactionType":"https://schema.org/CommentAction","userInteractionCount":1},"url":"https://github.com/13399/cloudstack/issues/13399"}