app-usage-worker fetching service usage event failure with "Mysql2::Error: Incorrect string value ..."

Products

• VMware Tanzu Application Service for VMs

Issue/Introduction

On Heathwatch2 Grafana dashboard it's showing increasing async failure for usage service event fetching. And the status panel started to show constant failure.

"Mysql2::Error: Incorrect string value" errors were also produced to app-usage-worker app logs.

   2023-06-21T17:46:48.42+1000 [APP/PROC/WEB/0] OUT I, [2023-06-21T07:46:48.421197 #7]  INFO -- : ActiveRecord::StatementInvalid: Mysql2::Error: Incorrect string value: '\xE2\x80\xA8' for column 'service_instance_name' at row 1
   2023-06-21T17:46:48.42+1000 [APP/PROC/WEB/0] OUT I, [2023-06-21T07:46:48.421280 #7]  INFO -- : /home/vcap/deps/0/vendor_bundle/ruby/3.1.0/gems/mysql2-0.5.5/lib/mysql2/client.rb:151:in `_query'
   2023-06-21T17:46:48.42+1000 [APP/PROC/WEB/0] OUT /home/vcap/deps/0/vendor_bundle/ruby/3.1.0/gems/mysql2-0.5.5/lib/mysql2/client.rb:151:in `block in query'
   2023-06-21T17:46:48.42+1000 [APP/PROC/WEB/0] OUT /home/vcap/deps/0/vendor_bundle/ruby/3.1.0/gems/mysql2-0.5.5/lib/mysql2/client.rb:150:in `handle_interrupt'
   2023-06-21T17:46:48.42+1000 [APP/PROC/WEB/0] OUT /home/vcap/deps/0/vendor_bundle/ruby/3.1.0/gems/mysql2-0.5.5/lib/mysql2/client.rb:150:in `query'
   2023-06-21T17:46:48.42+1000 [APP/PROC/WEB/0] OUT /home/vcap/deps/0/vendor_bundle/ruby/3.1.0/gems/activerecord-6.1.7.3/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:206:in `block (2 levels) in execute'
   2023-06-21T17:46:48.42+1000 [APP/PROC/WEB/0] OUT /home/vcap/deps/0/vendor_bundle/ruby/3.1.0/gems/activesupport-6.1.7.3/lib/active_support/dependencies/interlock.rb:48:in `block in permit_concurrent_loads'
......

When checking data in service_instance_usages table in app_usage_service database, the latest event was actually loaded many days ago.


This was the matching record in service_usage_events table in ccdb database.


Dumping all remaining event data, which had not been loaded into app_usage_service database, from service_usage_events table in ccdb database into a local sql file, then review service_instance_name values in the file and found a record with unrecognized character in service_instance_name. 

Environment

Product Version: 3.0

Resolution

Manually updating the problematic record with same name but removing the unrecognized character. For example,

update ccdb.service_usage_events set service_instance_name = 'newrelic-check-registration-ui' where id=21715;

Check the events data in databases after a while to confirm new events data had been loaded into service_instance_usages table in app_usage_service database. 

Check Grafana dashboard to confirm usage service event fetching status returning to constant success.