Autoscaler errand fails with Error 1091 Can't DROP time

Products

• VMware Tanzu Application Service for VMs

Issue/Introduction

Symptoms:
The deploy autoscaling errand fails with the error: 

2018-05-24T19:05:21.87+0000 [APP/PROC/WEB/0] ERR panic: Error 1091: Can't DROP 'time'; check that column/key exists handling 2_add_created_at_to_readings_service_bindings_services_instances.sql [recovered]

Environment

Cause

This issue is due to the autoscaler database becoming corrupted. This may be caused by previous failed attempts to deploy the autoscaler app.

Resolution

Follow the steps to resolve this issue:

1. Stop any existing autoscaler apps. 

   cf target -o system -s autoscaling
   cf stop autoscale

2. Retrieve the MySQL admin credentials from BOSH Credhub.
   https://docs.pivotal.io/pivotalcf/2-1/customizing/credentials.html#credhub 
   The cf deployment credential variables?name=mysql-admin-credentials is needed.
3. bosh ssh into one of the PAS MySQL nodes.
4. Run the command

   mysql -u root -p

   and enter the password found in Step 2.
5. Once you are logged in, create a new schema with:

   create schema autoscale_bad;

6. Run the following MySQL statement: 

    SELECT CONCAT('RENAME TABLE ',table_schema,'.', table_name, ' TO ','autoscale_bad.', table_name,';') FROM information_schema. TABLES WHERE table_schema LIKE 'autoscale';

7. Copy and paste each row returned and run as a MySQL statement. This will empty the autoscale database.
8. Run the deploy-autoscaling errand again manually: 

   bosh -e <ENV> -d <CF_DEPLOYMENT> run-errand deploy-autoscaling

9. If the errand is successful, remove the bad autoscaler by running drop database autoscale_bad;