MariaDB - how to delete and start from scratch?

Configuration
1

After restoring HA (on Rpi4) from a full snapshot, logbook no longer works. It just spins indefinitely. There are no pertinent errors in the log. History works fine. Recorder is set to use mariadb in config.yaml. Enabling or disabling the Logbook cache integration does nothing.

I tried to delete and reinstall mariadb addon, to no avail. How do I solve this? Thanks.

2

That should definitely have worked. When you uninstall it should delete the entire database docker container.

Maybe itโ€™s not the MariaDB addon that is at fault?

What are the relevant errors in your system log?

3

Did it a few times. It should have worked.

Save configuration in a txt file. Deinstall MariaDB addon. Reinstall it. Set configuration back. Start the addon. Done.

4

Thatโ€™s the thing, thereโ€™s nothing related to history, logbook or recorder.

5

I uninstalled/reinstalled MariaDB addon once again and this time it works. Very weird but oh well. Third time is a charm I guess. Thanks guys!

2 Likes
6

Iโ€™m on HA 0.116.4 i performed this, I had lots of errors in logs next day. it seems there has been minor schema change over my upgrades, those schema changes were applied to mariadb during HA upgrade, but uninstalling/reinstalling mariadb addon those changes are missing.

in table โ€œstatesโ€ there are now 2 extra columns context_id, context_user_id
and 1 less self referencing FK from column old_state_id to column state_id

I luckily had another HA i did not drop/recreate mariadb, i exported the schema from the good HA mariadb, i dropped recreated the tables manually to correct the schema.

in the future I will export the tables/index DDL if I have to do this again.

7

Anywhere to find a new (export) MariaDB default (no data) homeassistant database with the new changes (schema) to try?

8

hereโ€™s what mysqldump gave me without data for HA 0.116.4. you do have to drop the states table 1st so i moved that DDL to the top. I forgot to change auto_increment start value back to 1. I used dbeaver to connect to HA mariadb then ran the DDLโ€™s below and restart HA as I dropped the tables while my automations were still running. But iโ€™m no longer getting constraint error during recorder purge caused by the old self referential Foreign Key Constraint in the states table.

DROP TABLE IF EXISTS `states`;
DROP TABLE IF EXISTS `events`;
CREATE TABLE `events` (
  `event_id` int(11) NOT NULL AUTO_INCREMENT,
  `event_type` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `event_data` text COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `origin` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `time_fired` datetime DEFAULT NULL,
  `created` datetime DEFAULT NULL,
  `context_id` varchar(36) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `context_user_id` varchar(36) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `context_parent_id` char(36) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`event_id`),
  KEY `ix_events_time_fired` (`time_fired`),
  KEY `ix_events_context_user_id` (`context_user_id`),
  KEY `ix_events_context_id` (`context_id`),
  KEY `ix_events_context_parent_id` (`context_parent_id`),
  KEY `ix_events_event_type_time_fired` (`event_type`,`time_fired`)
) ENGINE=InnoDB AUTO_INCREMENT=4788819 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `recorder_runs`;
CREATE TABLE `recorder_runs` (
  `run_id` int(11) NOT NULL AUTO_INCREMENT,
  `start` datetime DEFAULT NULL,
  `end` datetime DEFAULT NULL,
  `closed_incorrect` tinyint(1) DEFAULT NULL,
  `created` datetime DEFAULT NULL,
  PRIMARY KEY (`run_id`),
  KEY `ix_recorder_runs_start_end` (`start`,`end`),
  CONSTRAINT `CONSTRAINT_1` CHECK (`closed_incorrect` in (0,1))
) ENGINE=InnoDB AUTO_INCREMENT=237 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `schema_changes`;
CREATE TABLE `schema_changes` (
  `change_id` int(11) NOT NULL AUTO_INCREMENT,
  `schema_version` int(11) DEFAULT NULL,
  `changed` datetime DEFAULT NULL,
  PRIMARY KEY (`change_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `states` (
  `state_id` int(11) NOT NULL AUTO_INCREMENT,
  `domain` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `entity_id` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `state` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `attributes` text COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `event_id` int(11) DEFAULT NULL,
  `last_changed` datetime DEFAULT NULL,
  `last_updated` datetime DEFAULT NULL,
  `created` datetime DEFAULT NULL,
  `context_id` varchar(36) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `context_user_id` varchar(36) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `old_state_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`state_id`),
  KEY `ix_states_last_updated` (`last_updated`),
  KEY `ix_states_entity_id_last_updated` (`entity_id`,`last_updated`),
  KEY `ix_states_event_id` (`event_id`),
  CONSTRAINT `states_ibfk_1` FOREIGN KEY (`event_id`) REFERENCES `events` (`event_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4604826 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
2 Likes
9

Thank you! That fixed mine!

10

None of that should be necessary. If starting from scratch home assistant should build the correct tables. If it is not, you should raise an issue.

11

I uninstalled/reinstalled mariadb addon at least 3 times in HA 0.116.4, always got an issue in logs sometime next day probably after recorder purge kicked in. I searched for clues in the forums found none.

this was the states table I found I had on the HA when I uninstalled/reinstalled
it was missing columns context_id, context_user_id and it had the extra FK constraint
states_ibfk_2 that broke recorder purge also an extra index on column old_state_id.

CREATE TABLE `states` (
  `state_id` int(11) NOT NULL AUTO_INCREMENT,
  `domain` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `entity_id` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `state` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `attributes` text COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `event_id` int(11) DEFAULT NULL,
  `last_changed` datetime DEFAULT NULL,
  `last_updated` datetime DEFAULT NULL,
  `created` datetime DEFAULT NULL,
  `old_state_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`state_id`),
  KEY `old_state_id` (`old_state_id`),
  KEY `ix_states_entity_id_last_updated` (`entity_id`,`last_updated`),
  KEY `ix_states_last_updated` (`last_updated`),
  KEY `ix_states_event_id` (`event_id`),
  CONSTRAINT `states_ibfk_1` FOREIGN KEY (`event_id`) REFERENCES `events` (`event_id`),
  CONSTRAINT `states_ibfk_2` FOREIGN KEY (`old_state_id`) REFERENCES `states` (`state_id`)
) ENGINE=InnoDB AUTO_INCREMENT=98846 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
;

the events table had a minor difference on column context_parent_id it was varchar(36)
its been changed to just char(36).

I havenโ€™t yet went to the addon github site to create a new issue. I put it here 1st so other people donโ€™t get the issue in the 1st place.