stats: Inaccurate counter data on banner of stats page

[hhq365]

counters data (banner of stats page) could not updated for long time.

The scan home page: https://scan.alg2-test.algen.network/

Error logs of stats container ,It's similar for all other chart groups :

stats  | 2024-11-06T12:00:00.017023Z  INFO update_charts{update_group="TotalBlocksGroup" update_time="2024-11-06 12:00:00.012949238 UTC"}:last_accurate_point{min_blockscout_block=0 chart=ChartKey { name: "totalBlocks", resolution: Day }}: stats::charts::db_interaction::read: close time.busy=1.18ms time.idle=1.45ms
stats  | 2024-11-06T12:00:00.017051Z ERROR update_charts{update_group="TotalBlocksGroup" update_time="2024-11-06 12:00:00.012949238 UTC"}: stats::data_source::kinds::local_db: error during updating chart: internal error: Failure while reading chart data: did not return accurate data (with `fill_missing_dates`=true) chart=totalBlocks_DAY
stats  | 2024-11-06T12:00:00.017060Z  INFO update_charts{update_group="TotalBlocksGroup" update_time="2024-11-06 12:00:00.012949238 UTC"}: stats::update_groups: close time.busy=1.85ms time.idle=2.27ms
stats  | 2024-11-06T12:00:00.017068Z ERROR stats_server::update_service: error during updating group: internal error: Failure while reading chart data: did not return accurate data (with `fill_missing_dates`=true) update_group="TotalBlocksGroup"
stats  | 2024-11-06T12:00:00.017077Z  INFO stats_server::update_service: scheduled next run of group update in 3599.982925848s update_group="TotalBlocksGroup"

stats  | 2024-11-06T12:00:38.310916Z  INFO update_charts{update_group="NewAccountsGroup" update_time="2024-11-06 12:00:00.002986605 UTC"}:last_accurate_point{min_blockscout_block=0 chart=ChartKey { name: "totalAccounts", resolution: Day }}: stats::charts::db_interaction::read: new
stats  | 2024-11-06T12:00:38.312722Z  INFO update_charts{update_group="NewAccountsGroup" update_time="2024-11-06 12:00:00.002986605 UTC"}:last_accurate_point{min_blockscout_block=0 chart=ChartKey { name: "totalAccounts", resolution: Day }}: stats::charts::db_interaction::read: close time.busy=936µs time.idle=870µs
stats  | 2024-11-06T12:00:38.312748Z ERROR update_charts{update_group="NewAccountsGroup" update_time="2024-11-06 12:00:00.002986605 UTC"}: stats::data_source::kinds::local_db: error during updating chart: internal error: Failure while reading chart data: did not return accurate data (with `fill_missing_dates`=true) chart=totalAccounts_DAY
stats  | 2024-11-06T12:00:38.312758Z  INFO update_charts{update_group="NewAccountsGroup" update_time="2024-11-06 12:00:00.002986605 UTC"}: stats::update_groups: close time.busy=35.0ms time.idle=38.3s
stats  | 2024-11-06T12:00:38.312766Z ERROR stats_server::update_service: error during updating group: internal error: Failure while reading chart data: did not return accurate data (with `fill_missing_dates`=true) update_group="NewAccountsGroup"
stats  | 2024-11-06T12:00:38.312780Z  INFO stats_server::update_service: scheduled next run of group update in 3561.6872244s update_group="NewAccountsGroup"

[bragov4ik]

(leaving mostly as a note to myself, to not forget where I left)
looked into it briefly - the issue might be because last_accurate_point uses get_line_chart_data for counter charts, which might not be correct. however, I don't see what exactly could be wrong, so need to investigate more

[bragov4ik]

I found the exact same issue on some old testnet;
Leaving the DB state I found for the future debug/fix :)

stats=> SELECT * FROM chart_data WHERE chart_id=9;
  id   | chart_id |    date    |   value   |          created_at           | min_blockscout_block
-------+----------+------------+-----------+-------------------------------+----------------------
 13271 |        9 | 2024-04-18 | 307678931 | 2024-04-25 18:21:25.247763+00 |
(1 row)

stats=> SELECT * FROM charts WHERE id=9;
 id |     name      | chart_type |          created_at           |    last_updated_at     | resolution
----+---------------+------------+-------------------------------+------------------------+------------
  9 | completedTxns | COUNTER    | 2024-04-25 18:03:18.180831+00 | 2024-04-18 00:00:00+00 | DAY

also logs:

2024-12-06 15:05:00.008	{"timestamp":"2024-12-06T06:05:00.008592Z","level":"ERROR","message":"error during updating group: internal error: Failure while reading chart data: did not return accurate data (with `fill_missing_dates`=true)","update_group":"CompletedTxnsGroup","target":"stats_server::update_service"}
2024-12-06 15:05:00.008	{"timestamp":"2024-12-06T06:05:00.008578Z","level":"ERROR","message":"error during updating chart: internal error: Failure while reading chart data: did not return accurate data (with `fill_missing_dates`=true)","chart":"completedTxns_DAY","target":"stats::data_source::kinds::local_db","span":{"source_mutex_id":"completedTxns_DAY","name":"update_recursively"}}
2024-12-06 15:05:00.008	{"timestamp":"2024-12-06T06:05:00.008535Z","level":"DEBUG","summary":"SELECT \"chart_data\".\"id\", \"chart_data\".\"chart_id\", \"chart_data\".\"date\", …","db.statement":"\n\nSELECT\n  \"chart_data\".\"id\",\n  \"chart_data\".\"chart_id\",\n  \"chart_data\".\"date\",\n  \"chart_data\".\"value\",\n  \"chart_data\".\"created_at\",\n  \"chart_data\".\"min_blockscout_block\",\n  \"chart_data\".\"date\",\n  \"chart_data\".\"value\"\nFROM\n  \"chart_data\"\nWHERE\n  \"chart_data\".\"chart_id\" = $1\n  AND (\n    date >= (\n      SELECT\n        COALESCE(MAX(date), '1900-01-01' :: date)\n      FROM\n        chart_data\n      WHERE\n        chart_id = $2\n        AND date <= $3\n    )\n  )\n  AND (\n    date <= (\n      SELECT\n        COALESCE(MIN(date), '9999-12-31' :: date)\n      FROM\n        chart_data\n      WHERE\n        chart_id = $4\n        AND date >= $5\n    )\n  )\nORDER BY\n  \"chart_data\".\"date\" ASC\n","rows_affected":1,"rows_returned":1,"elapsed":"348.779µs","elapsed_secs":0.000348779,"target":"sqlx::query","span":{"chart":"ChartKey { name: \"completedTxns\", resolution: Day }","min_blockscout_block":0,"name":"last_accurate_point"}}
2024-12-06 15:05:00.007	{"timestamp":"2024-12-06T06:05:00.007650Z","level":"DEBUG","summary":"SELECT \"charts\".\"id\", \"charts\".\"name\", CAST(\"charts\".\"chart_type\" …","db.statement":"\n\nSELECT\n  \"charts\".\"id\",\n  \"charts\".\"name\",\n  CAST(\"charts\".\"chart_type\" AS text),\n  \"charts\".\"created_at\",\n  \"charts\".\"last_updated_at\",\n  CAST(\"charts\".\"resolution\" AS text),\n  \"charts\".\"id\"\nFROM\n  \"charts\"\nWHERE\n  \"charts\".\"name\" = $1\n  AND \"charts\".\"resolution\" = (CAST($2 AS chart_resolution))\nLIMIT\n  $3\n","rows_affected":0,"rows_returned":1,"elapsed":"300.967µs","elapsed_secs":0.000300967,"target":"sqlx::query","span":{"chart":"ChartKey { name: \"completedTxns\", resolution: Day }","min_blockscout_block":0,"name":"last_accurate_point"}}
2024-12-06 15:05:00.007	{"timestamp":"2024-12-06T06:05:00.006966Z","level":"DEBUG","summary":"SELECT \"charts\".\"id\", \"charts\".\"name\", CAST(\"charts\".\"chart_type\" …","db.statement":"\n\nSELECT\n  \"charts\".\"id\",\n  \"charts\".\"name\",\n  CAST(\"charts\".\"chart_type\" AS text),\n  \"charts\".\"created_at\",\n  \"charts\".\"last_updated_at\",\n  CAST(\"charts\".\"resolution\" AS text),\n  \"charts\".\"id\"\nFROM\n  \"charts\"\nWHERE\n  \"charts\".\"name\" = $1\n  AND \"charts\".\"resolution\" = (CAST($2 AS chart_resolution))\nLIMIT\n  $3\n","rows_affected":0,"rows_returned":1,"elapsed":"254.266µs","elapsed_secs":0.000254266,"target":"sqlx::query","span":{"chart":"ChartKey { name: \"completedTxns\", resolution: Day }","min_blockscout_block":0,"name":"last_accurate_point"}}
2024-12-06 15:05:00.006	{"timestamp":"2024-12-06T06:05:00.006280Z","level":"DEBUG","summary":"SELECT \"chart_data\".\"id\", \"chart_data\".\"chart_id\", \"chart_data\".\"date\", …","db.statement":"\n\nSELECT\n  \"chart_data\".\"id\",\n  \"chart_data\".\"chart_id\",\n  \"chart_data\".\"date\",\n  \"chart_data\".\"value\",\n  \"chart_data\".\"created_at\",\n  \"chart_data\".\"min_blockscout_block\",\n  \"chart_data\".\"min_blockscout_block\"\nFROM\n  \"chart_data\"\nWHERE\n  \"chart_data\".\"chart_id\" = $1\nORDER BY\n  \"chart_data\".\"date\" DESC\nLIMIT\n  $2\n","rows_affected":0,"rows_returned":1,"elapsed":"721.004µs","elapsed_secs":0.000721004,"target":"sqlx::query","span":{"chart":"ChartKey { name: \"completedTxns\", resolution: Day }","min_blockscout_block":0,"name":"last_accurate_point"}}
2024-12-06 15:05:00.005	{"timestamp":"2024-12-06T06:05:00.005152Z","level":"DEBUG","summary":"SELECT MIN(\"number\") AS \"min_block\" …","db.statement":"\n\nSELECT\n  MIN(\"number\") AS \"min_block\"\nFROM\n  \"blocks\"\nWHERE\n  \"blocks\".\"consensus\" = $1\nLIMIT\n  $2\n","rows_affected":0,"rows_returned":1,"elapsed":"586.56µs","elapsed_secs":0.00058656,"target":"sqlx::query","span":{"source_mutex_id":"completedTxns_DAY","name":"update_recursively"}}
2024-12-06 15:05:00.004	{"timestamp":"2024-12-06T06:05:00.004158Z","level":"DEBUG","message":"Performing an update","last_updated_at":"2024-04-18T00:00:00Z","update_timestamp":"2024-12-06T06:05:00.003202110Z","target":"stats::data_source::kinds::local_db","span":{"source_mutex_id":"completedTxns_DAY","name":"update_recursively"}}
2024-12-06 15:05:00.004	{"timestamp":"2024-12-06T06:05:00.004120Z","level":"DEBUG","summary":"SELECT \"charts\".\"id\", \"charts\".\"name\", CAST(\"charts\".\"chart_type\" …","db.statement":"\n\nSELECT\n  \"charts\".\"id\",\n  \"charts\".\"name\",\n  CAST(\"charts\".\"chart_type\" AS text),\n  \"charts\".\"created_at\",\n  \"charts\".\"last_updated_at\",\n  CAST(\"charts\".\"resolution\" AS text),\n  \"charts\".\"id\"\nFROM\n  \"charts\"\nWHERE\n  \"charts\".\"name\" = $1\n  AND \"charts\".\"resolution\" = (CAST($2 AS chart_resolution))\nLIMIT\n  $3\n","rows_affected":0,"rows_returned":1,"elapsed":"319.986µs","elapsed_secs":0.000319986,"target":"sqlx::query","span":{"source_mutex_id":"completedTxns_DAY","name":"update_recursively"}}
2024-12-06 15:05:00.003	{"timestamp":"2024-12-06T06:05:00.003286Z","level":"INFO","message":"started chart update","chart":"completedTxns_DAY","target":"stats::data_source::kinds::local_db","span":{"source_mutex_id":"completedTxns_DAY","name":"update_recursively"}}

[bragov4ik]

The steps to fix I see:

• create a test case based on this data
• in the test check why get_line_chart_data in last_accurate_point does not return accurate data with these inputs
• fix the reason