mySql error after "fresh" installation

Configuration
1

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;
2

As there is a complex integration with a Ground Source Heat Pump I really do not want to start the HA installation from scratch.

Any help would be gratefully received. (At least the Rpi is now stable after moving the data to a powered USB HDD!)

3

I was hoping, somewhat naively, that if I dropped the DB then HA would recreate it. No joy: Error during connection setup: (MySQLdb._exceptions.OperationalError) (1049, “Unknown database ‘homeassistant’”)

Is there an up-to-date sequence of SQL I can use to recreate the db as HA would expect it?

4

I do not understand why that script was so complex when HA has created the necessary fields after I created the database.

I will look at migrating the database to some system off the Rpi.