Autoscale Errand is failing for the full Elastic Runtime install on Google Cloud Platform

Products

• VMware Tanzu Application Service for VMs

Issue/Introduction

Symptoms:

Pivotal Cloud Foundry autoscale application is crashing with an error.

Error Message

"ERR panic: Error 1067: Invalid default value for 'refresh_token_expires_at' handling 30_update_sessions_table.sql " . This is with respect to full ERT install on GCP with external mysql db on GCP . All other errand completed good except autosclaer errand . Error from mysql stackdriver logs: ""severity: "ERROR" 
textPayload: "2017-12-01T18:03:40.653191Z 33177 [Note] Aborted connection 33177 to db: 'autoscale' user: 'pcflab' host: '*******' (Got an error reading communication packets)" "

 

Environment

Cause

The main cause of this is the default value for refresh_token_expires_at in conjunction with the default database flag NO_ZERO_DATE in sql_mode:

30_update_sessions_table.sql:
ALTER TABLE sessions
CHANGE COLUMN expires_at access_token_expires_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
CHANGE COLUMN token access_token VARCHAR(2048) NOT NULL,
ADD refresh_token VARCHAR(2048) NOT NULL DEFAULT "",
ADD refresh_token_expires_at TIMESTAMP NOT NULL DEFAULT "0000-00-00 00:00:00",
ADD validity_period INT(11) NOT NULL DEFAULT 0;

Resolution

To resolve this problem, change the default sql_mode on the GCP DB (MySQL 5.7.18) to remove the flags 'NO_ZERO_DATE' (https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html).

GCP only accepts 1 flag at time, so you need to change the default list from

"ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

to

"NO_ENGINE_SUBSTITUTION"