After multiple and failed attempts to fix the mySql database I decided to try to create it from scratch using a script I found. On HA start the following errors are produced:
Error executing query: (MySQLdb._exceptions.OperationalError) (1054, "Unknown column 'states.context_id' in 'field list'") [SQL: SELECT states.state_id AS states_state_id, states.domain AS states_domain, states.entity_id AS states_entity_id, states.state AS states_state, states.attributes AS states_attributes, states.event_id AS states_event_id, states.last_changed AS states_last_changed, states.last_updated AS states_last_updated, states.created AS states_created, states.context_id AS states_context_id, states.context_user_id AS states_context_user_id FROM states WHERE (states.domain IN (%s, %s, %s) OR states.last_changed = states.last_updated) AND states.last_updated > %s AND states.entity_id IN (%s) AND states.last_updated < %s ORDER BY states.last_updated] [parameters: ('thermostat', 'climate', 'water_heater', datetime.datetime(2020, 3, 25, 22, 38, 52, 650000, tzinfo=<UTC>), 'sensor.efergy_728324', datetime.datetime(2020, 3, 26, 22, 38, 52, 650000, tzinfo=<UTC>))] (Background on this error at: http://sqlalche.me/e/e3q8)
Here is the command to create the table. May I assume that the new column has been added in a recent HA version?..
CREATE TABLE IF NOT EXISTS `homeassistant`.`states` (
`state_id` INT(11) NOT NULL AUTO_INCREMENT ,
`domain` VARCHAR(64) NULL DEFAULT NULL,
`entity_id` VARCHAR(255) NULL DEFAULT NULL,
`state` VARCHAR(255) NULL DEFAULT NULL,
`attributes` TEXT NULL DEFAULT NULL,
`event_id` INT(11) NULL DEFAULT NULL,
`last_changed` DATETIME(6) NULL DEFAULT NULL,
`last_updated` DATETIME(6) NULL DEFAULT NULL,
`created` DATETIME(6) NULL DEFAULT NULL,
PRIMARY KEY (`state_id`),
INDEX `event_id_idx` (`event_id` ASC),
INDEX `states__state_changes` (`last_changed` ASC, `last_updated` ASC, `entity_id` ASC),
INDEX `states__significant_changes` (`domain` ASC, `last_updated` ASC, `entity_id` ASC),
CONSTRAINT `event_id`
FOREIGN KEY (`event_id`)
REFERENCES `homeassistant`.`events` (`event_id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;