Upgrade to 2023.4.4 (with mariadb), no more history, slow queries on db, is it the migration?

wouf · April 15, 2023, 2:03pm

Hello,

I’m stuck on a problem with my installation runing latest version (with mariadb addon latest version too). HA is not able to access history, the UI is working, sensors are working, but everything linked to statistic/history looks broken.

Spining for hours where I should see a sensor graph of last 24h.

I know the migration could be long, but I did the upgrade more than 24h ago and my db is < 3 GB.

What I did so far :

check space on host : ok
check sd card performance : ok (hdparm + dd)
check logs : no error with sql nor mariadb
restart mariadb addon : problem still there
restart core : problem still there (logical, the addon is not restarted)
restart host (rpi4 8gb) : ok for a minute or two, then problem occurs.
connect with an external sql client to mariadb to check processlist : I have some really slow queries running, if I kill them, they come back.
connect with SSH to host on 22222 and check journalctl everything looks ok (I mean no error with sql, I have errors but not related - with bluetooth)

This is the kind of sql query always running :

WITH anon_1 AS 
(SELECT anon_2.context_id_bin AS context_id_bin 
FROM (SELECT events.context_id_bin AS context_id_bin 
FROM events LEFT OUTER JOIN event_types ON events.event_type_id = event_types.event_type_id LEFT OUTER JOIN event_data ON events.data_id = event_data.data_id 
WHERE events.time_fired_ts > 1680643068.831e0 AND events.time_fired_ts < 1681560448.005736e0 AND events.event_type_id IN (SELECT event_types.event_type_id 
FROM event_types 
WHERE event_types.event_type IN ('logbook_entry', 'automation_triggered', 'script_started')) AND (JSON_EXTRACT(CAST(event_data.shared_data AS CHAR), '$.\"entity_id\"') IS NOT NULL AND CAST(JSON_EXTRACT(CAST(event_data.shared_data AS CHAR), '$.\"entity_id\"') AS CHAR) IN ('\"binary_sensor.mouvement_buanderie_occupancy\"', '\"binary_sensor.mouvement_cuisine_occupancy\"', '\"binary_sensor.mouvement_garage_occupancy\"', '\"binary_sensor.mouvement_salon_jardin_occupancy\"', '\"binary_sensor.mouvement_salon_rue_occupancy\"', '\"binary_sensor.porte_entree_contact\"', '\"binary_sensor.porte_ext_buanderie_contact\"', '\"binary_sensor.porte_ext_cuisine_contact\"', '\"binary_sensor.porte_garage_ext_contact\"', '\"binary_sensor.porte_salon_jardin_contact\"', '\"binary_sensor.porte_salon_rue_1_contact\"', '\"binary_sensor.porte_salon_rue_2_contact\"', '\"binary_sensor.fenetre_bureau_contact\"', '\"binary_sensor.fenetre_cuisine_contact\"', '\"binary_sensor.fenetre_mathieu_contact\"', '\"binary_sensor.fenetre_parents_jardin_contact\"', '\"binary_sensor.fenetre_parents_rue_1_contact\"', '\"binary_sensor.fenetre_parents_rue_2_contact\"', '\"binary_sensor.fenetre_romane_contact\"', '\"binary_sensor.detecteur_fuite_de_gaz_gas\"', '\"binary_sensor.detecteur_incendie_buanderie_smoke\"', '\"binary_sensor.detecteur_incendie_cuisine_smoke\"', '\"binary_sensor.detecteur_incendie_salon_smoke\"', '\"lock.nuki_porte_entree_lock\"') OR JSON_EXTRACT(CAST(events.event_data AS CHAR), '$.\"entity_id\"') IS NOT NULL AND CAST(JSON_EXTRACT(CAST(events.event_data AS CHAR), '$.\"entity_id\"') AS CHAR) IN ('\"binary_sensor.mouvement_buanderie_occupancy\"', '\"binary_sensor.mouvement_cuisine_occupancy\"', '\"binary_sensor.mouvement_garage_occupancy\"', '\"binary_sensor.mouvement_salon_jardin_occupancy\"', '\"binary_sensor.mouvement_salon_rue_occupancy\"', '\"binary_sensor.porte_entree_contact\"', '\"binary_sensor.porte_ext_buanderie_contact\"', '\"binary_sensor.porte_ext_cuisine_contact\"', '\"binary_sensor.porte_garage_ext_contact\"', '\"binary_sensor.porte_salon_jardin_contact\"', '\"binary_sensor.porte_salon_rue_1_contact\"', '\"binary_sensor.porte_salon_rue_2_contact\"', '\"binary_sensor.fenetre_bureau_contact\"', '\"binary_sensor.fenetre_cuisine_contact\"', '\"binary_sensor.fenetre_mathieu_contact\"', '\"binary_sensor.fenetre_parents_jardin_contact\"', '\"binary_sensor.fenetre_parents_rue_1_contact\"', '\"binary_sensor.fenetre_parents_rue_2_contact\"', '\"binary_sensor.fenetre_romane_contact\"', '\"binary_sensor.detecteur_fuite_de_gaz_gas\"', '\"binary_sensor.detecteur_incendie_buanderie_smoke\"', '\"binary_sensor.detecteur_incendie_cuisine_smoke\"', '\"binary_sensor.detecteur_incendie_salon_smoke\"', '\"lock.nuki_porte_entree_lock\"')) UNION ALL SELECT states.context_id_bin AS context_id_bin 
FROM states FORCE INDEX (ix_states_metadata_id_last_updated_ts) 
WHERE states.last_updated_ts > 1680643068.831e0 AND states.last_updated_ts < 1681560448.005736e0 AND states.metadata_id IN (5, 393, 373, 381, 382, 371, 365, 370, 372, 396, 383, 384, 3, 366, 380, 367, 368, 375, 374, 83, 389, 385, 376, 209)) AS anon_2 GROUP BY anon_2.context_id_bin)
 SELECT events.event_id AS event_id, event_types.event_type AS event_type, events.event_data AS event_data, events.time_fired_ts AS time_fired_ts, events.context_id_bin AS context_id_bin, events.context_user_id_bin AS context_user_id_bin, events.context_parent_id_bin AS context_parent_id_bin, event_data.shared_data AS shared_data, 0 AS state_id, NULL AS state, NULL AS entity_id, NULL AS icon, NULL AS old_format_icon, NULL AS context_only 
FROM events LEFT OUTER JOIN event_types ON events.event_type_id = event_types.event_type_id LEFT OUTER JOIN event_data ON events.data_id = event_data.data_id 
WHERE events.time_fired_ts > 1680643068.831e0 AND events.time_fired_ts < 1681560448.005736e0 AND events.event_type_id IN (SELECT event_types.event_type_id 
FROM event_types 
WHERE event_types.event_type IN ('logbook_entry', 'automation_triggered', 'script_started')) AND (JSON_EXTRACT(CAST(event_data.shared_data AS CHAR), '$.\"entity_id\"') IS NOT NULL AND CAST(JSON_EXTRACT(CAST(event_data.shared_data AS CHAR), '$.\"entity_id\"') AS CHAR) IN ('\"binary_sensor.mouvement_buanderie_occupancy\"', '\"binary_sensor.mouvement_cuisine_occupancy\"', '\"binary_sensor.mouvement_garage_occupancy\"', '\"binary_sensor.mouvement_salon_jardin_occupancy\"', '\"binary_sensor.mouvement_salon_rue_occupancy\"', '\"binary_sensor.porte_entree_contact\"', '\"binary_sensor.porte_ext_buanderie_contact\"', '\"binary_sensor.porte_ext_cuisine_contact\"', '\"binary_sensor.porte_garage_ext_contact\"', '\"binary_sensor.porte_salon_jardin_contact\"', '\"binary_sensor.porte_salon_rue_1_contact\"', '\"binary_sensor.porte_salon_rue_2_contact\"', '\"binary_sensor.fenetre_bureau_contact\"', '\"binary_sensor.fenetre_cuisine_contact\"', '\"binary_sensor.fenetre_mathieu_contact\"', '\"binary_sensor.fenetre_parents_jardin_contact\"', '\"binary_sensor.fenetre_parents_rue_1_contact\"', '\"binary_sensor.fenetre_parents_rue_2_contact\"', '\"binary_sensor.fenetre_romane_contact\"', '\"binary_sensor.detecteur_fuite_de_gaz_gas\"', '\"binary_sensor.detecteur_incendie_buanderie_smoke\"', '\"binary_sensor.detecteur_incendie_cuisine_smoke\"', '\"binary_sensor.detecteur_incendie_salon_smoke\"', '\"lock.nuki_porte_entree_lock\"') OR JSON_EXTRACT(CAST(events.event_data AS CHAR), '$.\"entity_id\"') IS NOT NULL AND CAST(JSON_EXTRACT(CAST(events.event_data AS CHAR), '$.\"entity_id\"') AS CHAR) IN ('\"binary_sensor.mouvement_buanderie_occupancy\"', '\"binary_sensor.mouvement_cuisine_occupancy\"', '\"binary_sensor.mouvement_garage_occupancy\"', '\"binary_sensor.mouvement_salon_jardin_occupancy\"', '\"binary_sensor.mouvement_salon_rue_occupancy\"', '\"binary_sensor.porte_entree_contact\"', '\"binary_sensor.porte_ext_buanderie_contact\"', '\"binary_sensor.porte_ext_cuisine_contact\"', '\"binary_sensor.porte_garage_ext_contact\"', '\"binary_sensor.porte_salon_jardin_contact\"', '\"binary_sensor.porte_salon_rue_1_contact\"', '\"binary_sensor.porte_salon_rue_2_contact\"', '\"binary_sensor.fenetre_bureau_contact\"', '\"binary_sensor.fenetre_cuisine_contact\"', '\"binary_sensor.fenetre_mathieu_contact\"', '\"binary_sensor.fenetre_parents_jardin_contact\"', '\"binary_sensor.fenetre_parents_rue_1_contact\"', '\"binary_sensor.fenetre_parents_rue_2_contact\"', '\"binary_sensor.fenetre_romane_contact\"', '\"binary_sensor.detecteur_fuite_de_gaz_gas\"', '\"binary_sensor.detecteur_incendie_buanderie_smoke\"', '\"binary_sensor.detecteur_incendie_cuisine_smoke\"', '\"binary_sensor.detecteur_incendie_salon_smoke\"', '\"lock.nuki_porte_entree_lock\"')) UNION ALL SELECT NULL AS event_id, NULL AS event_type, NULL AS event_data, states.last_updated_ts AS time_fired_ts, states.context_id_bin AS context_id_bin, states.context_user_id_bin AS context_user_id_bin, states.context_parent_id_bin AS context_parent_id_bin, NULL AS shared_data, states.state_id AS state_id, states.state AS state, states_meta.entity_id AS entity_id, CASE JSON_EXTRACT(CAST(state_attributes.shared_attrs AS CHAR), '$.\"icon\"') WHEN 'null' THEN NULL ELSE JSON_UNQUOTE(JSON_EXTRACT(CAST(state_attributes.shared_attrs AS CHAR), '$.\"icon\"')) END AS icon, CASE JSON_EXTRACT(CAST(states.attributes AS CHAR), '$.\"icon\"') WHEN 'null' THEN NULL ELSE JSON_UNQUOTE(JSON_EXTRACT(CAST(states.attributes AS CHAR), '$.\"icon\"')) END AS old_format_icon, NULL AS context_only 
FROM states FORCE INDEX (ix_states_metadata_id_last_updated_ts) LEFT OUTER JOIN states AS old_state ON states.old_state_id = old_state.state_id LEFT OUTER JOIN state_attributes ON states.attributes_id = state_attributes.attributes_id LEFT OUTER JOIN states_meta ON states.metadata_id = states_meta.metadata_id 
WHERE states.last_updated_ts > 1680643068.831e0 AND states.last_updated_ts < 1681560448.005736e0 AND old_state.state_id IS NOT NULL AND states.state != old_state.state AND states.state IS NOT NULL AND ((states_meta.entity_id NOT LIKE 'proximity.%' AND states_meta.entity_id NOT LIKE 'counter.%' AND states_meta.entity_id NOT LIKE 'sensor.%') OR ((states_meta.entity_id LIKE 'sensor.%') AND (state_attributes.shared_attrs NOT LIKE '%\"unit_of_measurement\":%' OR states.attributes NOT LIKE '%\"unit_of_measurement\":%'))) AND (states.last_updated_ts = states.last_changed_ts OR states.last_changed_ts IS NULL) AND states.metadata_id IN (5, 393, 373, 381, 382, 371, 365, 370, 372, 396, 383, 384, 3, 366, 380, 367, 368, 375, 374, 83, 389, 385, 376, 209) UNION ALL SELECT events.event_id AS event_id, event_types.event_type AS event_type, events.event_data AS event_data, events.time_fired_ts AS time_fired_ts, events.context_id_bin AS context_id_bin, events.context_user_id_bin AS context_user_id_bin, events.context_parent_id_bin AS context_parent_id_bin, event_data.shared_data AS shared_data, 0 AS state_id, NULL AS state, NULL AS entity_id, NULL AS icon, NULL AS old_format_icon, '1' AS context_only 
FROM anon_1 LEFT OUTER JOIN events FORCE INDEX (ix_events_context_id_bin) ON anon_1.context_id_bin = events.context_id_bin LEFT OUTER JOIN event_types ON events.event_type_id = event_types.event_type_id LEFT OUTER JOIN event_data ON events.data_id = event_data.data_id UNION ALL SELECT NULL AS event_id, NULL AS event_type, NULL AS event_data, states.last_updated_ts AS time_fired_ts, states.context_id_bin AS context_id_bin, states.context_user_id_bin AS context_user_id_bin, states.context_parent_id_bin AS context_parent_id_bin, NULL AS shared_data, states.state_id AS state_id, states.state AS state, states_meta.entity_id AS entity_id, NULL AS icon, NULL AS old_format_icon, '1' AS context_only 
FROM anon_1 LEFT OUTER JOIN states FORCE INDEX (ix_states_context_id_bin) ON anon_1.context_id_bin = states.context_id_bin LEFT OUTER JOIN states_meta ON states.metadata_id = states_meta.metadata_id ORDER BY time_fired_ts

Are they related to the migration process ?

On discord someone told me that I should have a notification for the migration process in the frontend, I never see it.

Is there any way to redo the migration process, maybe it has failed for me ?

Thank you for your help.

HA_n00b · April 15, 2023, 2:59pm

I am following this thread.

OllieK · April 17, 2023, 3:58pm

Same here - history data is extremely slow. Running 2023.4.4 on current HASS-OS on a virtual Machine. History was fine on 2023.3.

wouf · April 17, 2023, 4:00pm

We are talking about it there too : Ultraslow logbook queries · Issue #91514 · home-assistant/core · GitHub

catsmanac · April 17, 2023, 4:56pm

Same here, since upgrade to 2023.4.4 (coming from 2023.3.6) the history-graph are extremely slow. Using Docker and Mariadb Docker

bdraco · April 18, 2023, 1:27am

So far all the ones I’ve seen have been missing an index because the migration ran out of disk space or something else failed on the disk.

You can run an EXPLAIN on that statement to figure out what is missing https://dev.mysql.com/doc/refman/8.0/en/explain.html

wouf · April 18, 2023, 7:01am

We can try to explain all running queries to find out how to create indexes or add FK, I already did it on some and answer is not easy because of the complexity of the requests. In the end it will be way faster if someone can just do a structure dump of their working mariadb/postgres database to understand what is missing on ours.

bdraco · April 18, 2023, 7:29am

Schema for 2023.4.5

-- MariaDB dump 10.19  Distrib 10.11.2-MariaDB, for osx10.18 (arm64)
--
-- Host: localhost    Database: fresh
-- ------------------------------------------------------
-- Server version	10.11.2-MariaDB

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `event_data`
--

DROP TABLE IF EXISTS `event_data`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `event_data` (
  `data_id` int(11) NOT NULL AUTO_INCREMENT,
  `hash` bigint(20) DEFAULT NULL,
  `shared_data` longtext DEFAULT NULL,
  PRIMARY KEY (`data_id`),
  KEY `ix_event_data_hash` (`hash`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `event_types`
--

DROP TABLE IF EXISTS `event_types`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `event_types` (
  `event_type_id` int(11) NOT NULL AUTO_INCREMENT,
  `event_type` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`event_type_id`),
  UNIQUE KEY `ix_event_types_event_type` (`event_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `events`
--

DROP TABLE IF EXISTS `events`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `events` (
  `event_id` int(11) NOT NULL AUTO_INCREMENT,
  `event_type` varchar(64) DEFAULT NULL,
  `event_data` longtext DEFAULT NULL,
  `origin` varchar(32) DEFAULT NULL,
  `origin_idx` smallint(6) DEFAULT NULL,
  `time_fired` datetime(6) DEFAULT NULL,
  `time_fired_ts` double DEFAULT NULL,
  `context_id` varchar(36) DEFAULT NULL,
  `context_user_id` varchar(36) DEFAULT NULL,
  `context_parent_id` varchar(36) DEFAULT NULL,
  `data_id` int(11) DEFAULT NULL,
  `context_id_bin` tinyblob DEFAULT NULL,
  `context_user_id_bin` tinyblob DEFAULT NULL,
  `context_parent_id_bin` tinyblob DEFAULT NULL,
  `event_type_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`event_id`),
  KEY `ix_events_data_id` (`data_id`),
  KEY `ix_events_context_id_bin` (`context_id_bin`(16)),
  KEY `ix_events_event_type_id_time_fired_ts` (`event_type_id`,`time_fired_ts`),
  KEY `ix_events_context_id` (`context_id`),
  KEY `ix_events_time_fired_ts` (`time_fired_ts`),
  CONSTRAINT `events_ibfk_1` FOREIGN KEY (`data_id`) REFERENCES `event_data` (`data_id`),
  CONSTRAINT `events_ibfk_2` FOREIGN KEY (`event_type_id`) REFERENCES `event_types` (`event_type_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `recorder_runs`
--

DROP TABLE IF EXISTS `recorder_runs`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `recorder_runs` (
  `run_id` int(11) NOT NULL AUTO_INCREMENT,
  `start` datetime(6) NOT NULL,
  `end` datetime(6) DEFAULT NULL,
  `closed_incorrect` tinyint(1) NOT NULL,
  `created` datetime(6) NOT NULL,
  PRIMARY KEY (`run_id`),
  KEY `ix_recorder_runs_start_end` (`start`,`end`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `schema_changes`
--

DROP TABLE IF EXISTS `schema_changes`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `schema_changes` (
  `change_id` int(11) NOT NULL AUTO_INCREMENT,
  `schema_version` int(11) DEFAULT NULL,
  `changed` datetime(6) NOT NULL,
  PRIMARY KEY (`change_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `state_attributes`
--

DROP TABLE IF EXISTS `state_attributes`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `state_attributes` (
  `attributes_id` int(11) NOT NULL AUTO_INCREMENT,
  `hash` bigint(20) DEFAULT NULL,
  `shared_attrs` longtext DEFAULT NULL,
  PRIMARY KEY (`attributes_id`),
  KEY `ix_state_attributes_hash` (`hash`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `states`
--

DROP TABLE IF EXISTS `states`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `states` (
  `state_id` int(11) NOT NULL AUTO_INCREMENT,
  `entity_id` varchar(255) DEFAULT NULL,
  `state` varchar(255) DEFAULT NULL,
  `attributes` longtext DEFAULT NULL,
  `event_id` int(11) DEFAULT NULL,
  `last_changed` datetime(6) DEFAULT NULL,
  `last_changed_ts` double DEFAULT NULL,
  `last_updated` datetime(6) DEFAULT NULL,
  `last_updated_ts` double DEFAULT NULL,
  `old_state_id` int(11) DEFAULT NULL,
  `attributes_id` int(11) DEFAULT NULL,
  `context_id` varchar(36) DEFAULT NULL,
  `context_user_id` varchar(36) DEFAULT NULL,
  `context_parent_id` varchar(36) DEFAULT NULL,
  `origin_idx` smallint(6) DEFAULT NULL,
  `context_id_bin` tinyblob DEFAULT NULL,
  `context_user_id_bin` tinyblob DEFAULT NULL,
  `context_parent_id_bin` tinyblob DEFAULT NULL,
  `metadata_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`state_id`),
  KEY `old_state_id` (`old_state_id`),
  KEY `attributes_id` (`attributes_id`),
  KEY `ix_states_metadata_id_last_updated_ts` (`metadata_id`,`last_updated_ts`),
  CONSTRAINT `states_ibfk_1` FOREIGN KEY (`old_state_id`) REFERENCES `states` (`state_id`),
  CONSTRAINT `states_ibfk_2` FOREIGN KEY (`attributes_id`) REFERENCES `state_attributes` (`attributes_id`),
  CONSTRAINT `states_ibfk_3` FOREIGN KEY (`metadata_id`) REFERENCES `states_meta` (`metadata_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `states_meta`
--

DROP TABLE IF EXISTS `states_meta`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `states_meta` (
  `metadata_id` int(11) NOT NULL AUTO_INCREMENT,
  `entity_id` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`metadata_id`),
  UNIQUE KEY `ix_states_meta_entity_id` (`entity_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `statistics`
--

DROP TABLE IF EXISTS `statistics`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `statistics` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `created` datetime(6) DEFAULT NULL,
  `created_ts` double DEFAULT NULL,
  `metadata_id` int(11) DEFAULT NULL,
  `start` datetime(6) DEFAULT NULL,
  `start_ts` double DEFAULT NULL,
  `mean` double DEFAULT NULL,
  `min` double DEFAULT NULL,
  `max` double DEFAULT NULL,
  `last_reset` datetime(6) DEFAULT NULL,
  `last_reset_ts` double DEFAULT NULL,
  `state` double DEFAULT NULL,
  `sum` double DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `ix_statistics_statistic_id_start_ts` (`metadata_id`,`start_ts`),
  KEY `ix_statistics_start` (`start`),
  KEY `ix_statistics_start_ts` (`start_ts`),
  CONSTRAINT `statistics_ibfk_1` FOREIGN KEY (`metadata_id`) REFERENCES `statistics_meta` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `statistics_meta`
--

DROP TABLE IF EXISTS `statistics_meta`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `statistics_meta` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `statistic_id` varchar(255) DEFAULT NULL,
  `source` varchar(32) DEFAULT NULL,
  `unit_of_measurement` varchar(255) DEFAULT NULL,
  `has_mean` tinyint(1) DEFAULT NULL,
  `has_sum` tinyint(1) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `ix_statistics_meta_statistic_id` (`statistic_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `statistics_runs`
--

DROP TABLE IF EXISTS `statistics_runs`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `statistics_runs` (
  `run_id` int(11) NOT NULL AUTO_INCREMENT,
  `start` datetime(6) NOT NULL,
  PRIMARY KEY (`run_id`),
  KEY `ix_statistics_runs_start` (`start`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `statistics_short_term`
--

DROP TABLE IF EXISTS `statistics_short_term`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `statistics_short_term` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `created` datetime(6) DEFAULT NULL,
  `created_ts` double DEFAULT NULL,
  `metadata_id` int(11) DEFAULT NULL,
  `start` datetime(6) DEFAULT NULL,
  `start_ts` double DEFAULT NULL,
  `mean` double DEFAULT NULL,
  `min` double DEFAULT NULL,
  `max` double DEFAULT NULL,
  `last_reset` datetime(6) DEFAULT NULL,
  `last_reset_ts` double DEFAULT NULL,
  `state` double DEFAULT NULL,
  `sum` double DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `ix_statistics_short_term_statistic_id_start_ts` (`metadata_id`,`start_ts`),
  KEY `ix_statistics_short_term_start` (`start`),
  KEY `ix_statistics_short_term_start_ts` (`start_ts`),
  CONSTRAINT `statistics_short_term_ibfk_1` FOREIGN KEY (`metadata_id`) REFERENCES `statistics_meta` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2023-04-17 21:28:40
bdraco@MacBook-Pro-3 ~ % mysqldump -d fresh
-- MariaDB dump 10.19  Distrib 10.11.2-MariaDB, for osx10.18 (arm64)
--
-- Host: localhost    Database: fresh
-- ------------------------------------------------------
-- Server version	10.11.2-MariaDB

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `event_data`
--

DROP TABLE IF EXISTS `event_data`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `event_data` (
  `data_id` int(11) NOT NULL AUTO_INCREMENT,
  `hash` bigint(20) DEFAULT NULL,
  `shared_data` longtext DEFAULT NULL,
  PRIMARY KEY (`data_id`),
  KEY `ix_event_data_hash` (`hash`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `event_types`
--

DROP TABLE IF EXISTS `event_types`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `event_types` (
  `event_type_id` int(11) NOT NULL AUTO_INCREMENT,
  `event_type` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`event_type_id`),
  UNIQUE KEY `ix_event_types_event_type` (`event_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `events`
--

DROP TABLE IF EXISTS `events`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `events` (
  `event_id` int(11) NOT NULL AUTO_INCREMENT,
  `event_type` varchar(64) DEFAULT NULL,
  `event_data` longtext DEFAULT NULL,
  `origin` varchar(32) DEFAULT NULL,
  `origin_idx` smallint(6) DEFAULT NULL,
  `time_fired` datetime(6) DEFAULT NULL,
  `time_fired_ts` double DEFAULT NULL,
  `context_id` varchar(36) DEFAULT NULL,
  `context_user_id` varchar(36) DEFAULT NULL,
  `context_parent_id` varchar(36) DEFAULT NULL,
  `data_id` int(11) DEFAULT NULL,
  `context_id_bin` tinyblob DEFAULT NULL,
  `context_user_id_bin` tinyblob DEFAULT NULL,
  `context_parent_id_bin` tinyblob DEFAULT NULL,
  `event_type_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`event_id`),
  KEY `ix_events_data_id` (`data_id`),
  KEY `ix_events_context_id_bin` (`context_id_bin`(16)),
  KEY `ix_events_event_type_id_time_fired_ts` (`event_type_id`,`time_fired_ts`),
  KEY `ix_events_context_id` (`context_id`),
  KEY `ix_events_time_fired_ts` (`time_fired_ts`),
  CONSTRAINT `events_ibfk_1` FOREIGN KEY (`data_id`) REFERENCES `event_data` (`data_id`),
  CONSTRAINT `events_ibfk_2` FOREIGN KEY (`event_type_id`) REFERENCES `event_types` (`event_type_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `recorder_runs`
--

DROP TABLE IF EXISTS `recorder_runs`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `recorder_runs` (
  `run_id` int(11) NOT NULL AUTO_INCREMENT,
  `start` datetime(6) NOT NULL,
  `end` datetime(6) DEFAULT NULL,
  `closed_incorrect` tinyint(1) NOT NULL,
  `created` datetime(6) NOT NULL,
  PRIMARY KEY (`run_id`),
  KEY `ix_recorder_runs_start_end` (`start`,`end`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `schema_changes`
--

DROP TABLE IF EXISTS `schema_changes`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `schema_changes` (
  `change_id` int(11) NOT NULL AUTO_INCREMENT,
  `schema_version` int(11) DEFAULT NULL,
  `changed` datetime(6) NOT NULL,
  PRIMARY KEY (`change_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `state_attributes`
--

DROP TABLE IF EXISTS `state_attributes`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `state_attributes` (
  `attributes_id` int(11) NOT NULL AUTO_INCREMENT,
  `hash` bigint(20) DEFAULT NULL,
  `shared_attrs` longtext DEFAULT NULL,
  PRIMARY KEY (`attributes_id`),
  KEY `ix_state_attributes_hash` (`hash`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `states`
--

DROP TABLE IF EXISTS `states`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `states` (
  `state_id` int(11) NOT NULL AUTO_INCREMENT,
  `entity_id` varchar(255) DEFAULT NULL,
  `state` varchar(255) DEFAULT NULL,
  `attributes` longtext DEFAULT NULL,
  `event_id` int(11) DEFAULT NULL,
  `last_changed` datetime(6) DEFAULT NULL,
  `last_changed_ts` double DEFAULT NULL,
  `last_updated` datetime(6) DEFAULT NULL,
  `last_updated_ts` double DEFAULT NULL,
  `old_state_id` int(11) DEFAULT NULL,
  `attributes_id` int(11) DEFAULT NULL,
  `context_id` varchar(36) DEFAULT NULL,
  `context_user_id` varchar(36) DEFAULT NULL,
  `context_parent_id` varchar(36) DEFAULT NULL,
  `origin_idx` smallint(6) DEFAULT NULL,
  `context_id_bin` tinyblob DEFAULT NULL,
  `context_user_id_bin` tinyblob DEFAULT NULL,
  `context_parent_id_bin` tinyblob DEFAULT NULL,
  `metadata_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`state_id`),
  KEY `old_state_id` (`old_state_id`),
  KEY `attributes_id` (`attributes_id`),
  KEY `ix_states_metadata_id_last_updated_ts` (`metadata_id`,`last_updated_ts`),
  CONSTRAINT `states_ibfk_1` FOREIGN KEY (`old_state_id`) REFERENCES `states` (`state_id`),
  CONSTRAINT `states_ibfk_2` FOREIGN KEY (`attributes_id`) REFERENCES `state_attributes` (`attributes_id`),
  CONSTRAINT `states_ibfk_3` FOREIGN KEY (`metadata_id`) REFERENCES `states_meta` (`metadata_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `states_meta`
--

DROP TABLE IF EXISTS `states_meta`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `states_meta` (
  `metadata_id` int(11) NOT NULL AUTO_INCREMENT,
  `entity_id` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`metadata_id`),
  UNIQUE KEY `ix_states_meta_entity_id` (`entity_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `statistics`
--

DROP TABLE IF EXISTS `statistics`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `statistics` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `created` datetime(6) DEFAULT NULL,
  `created_ts` double DEFAULT NULL,
  `metadata_id` int(11) DEFAULT NULL,
  `start` datetime(6) DEFAULT NULL,
  `start_ts` double DEFAULT NULL,
  `mean` double DEFAULT NULL,
  `min` double DEFAULT NULL,
  `max` double DEFAULT NULL,
  `last_reset` datetime(6) DEFAULT NULL,
  `last_reset_ts` double DEFAULT NULL,
  `state` double DEFAULT NULL,
  `sum` double DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `ix_statistics_statistic_id_start_ts` (`metadata_id`,`start_ts`),
  KEY `ix_statistics_start` (`start`),
  KEY `ix_statistics_start_ts` (`start_ts`),
  CONSTRAINT `statistics_ibfk_1` FOREIGN KEY (`metadata_id`) REFERENCES `statistics_meta` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `statistics_meta`
--

DROP TABLE IF EXISTS `statistics_meta`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `statistics_meta` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `statistic_id` varchar(255) DEFAULT NULL,
  `source` varchar(32) DEFAULT NULL,
  `unit_of_measurement` varchar(255) DEFAULT NULL,
  `has_mean` tinyint(1) DEFAULT NULL,
  `has_sum` tinyint(1) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `ix_statistics_meta_statistic_id` (`statistic_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `statistics_runs`
--

DROP TABLE IF EXISTS `statistics_runs`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `statistics_runs` (
  `run_id` int(11) NOT NULL AUTO_INCREMENT,
  `start` datetime(6) NOT NULL,
  PRIMARY KEY (`run_id`),
  KEY `ix_statistics_runs_start` (`start`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `statistics_short_term`
--

DROP TABLE IF EXISTS `statistics_short_term`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `statistics_short_term` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `created` datetime(6) DEFAULT NULL,
  `created_ts` double DEFAULT NULL,
  `metadata_id` int(11) DEFAULT NULL,
  `start` datetime(6) DEFAULT NULL,
  `start_ts` double DEFAULT NULL,
  `mean` double DEFAULT NULL,
  `min` double DEFAULT NULL,
  `max` double DEFAULT NULL,
  `last_reset` datetime(6) DEFAULT NULL,
  `last_reset_ts` double DEFAULT NULL,
  `state` double DEFAULT NULL,
  `sum` double DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `ix_statistics_short_term_statistic_id_start_ts` (`metadata_id`,`start_ts`),
  KEY `ix_statistics_short_term_start` (`start`),
  KEY `ix_statistics_short_term_start_ts` (`start_ts`),
  CONSTRAINT `statistics_short_term_ibfk_1` FOREIGN KEY (`metadata_id`) REFERENCES `statistics_meta` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2023-04-17 21:28:42

Note that AUTO_INCREMENT will need to be adjusted

catsmanac · April 18, 2023, 8:30am

My case solved itself in time. This morning history-graph performed as before. Turns out that dropping indexes was still going on, even after the schema upgrade notification was removed and completion of scema 41 was in the log. Couple of seconds after upgrade to 41 was done a dropping index message showed.

2023-04-16 18:30:47.220 WARNING (Recorder) [homeassistant.components.recorder.migration] Database is about to upgrade from schema version: 35 to: 41
.
.
2023-04-16 18:49:48.033 WARNING (Recorder) [homeassistant.components.recorder.migration] Upgrade to version 41 done
2023-04-16 18:49:55.826 WARNING (Recorder) [homeassistant.components.recorder.migration] Dropping index `ix_states_event_id` from table `states`. Note: this can take several minutes on large databases and slow computers. Please be patient!
.
.
2023-04-16 18:52:39.058 WARNING (Recorder) [homeassistant.components.recorder.migration] Dropping index `ix_events_context_id` from table `events`. Note: this can take several minutes on large databases and slow computers. Please be patient!
.
.
2023-04-17 06:54:25.342 WARNING (Recorder) [homeassistant.components.recorder.migration] Dropping index `ix_states_context_id` from table `states`. Note: this can take several minutes on large databases and slow computers. Please be patient!
.
.
2023-04-17 20:34:44.577 WARNING (Recorder) [homeassistant.components.recorder.migration] Dropping index `ix_states_entity_id_last_updated_ts` from table `states`. Note: this can take several minutes on large databases and slow computers. Please be patient!

That was last entry in log. It might still be active on the last drop, but history-graph works fine again. States indexes:
afbeelding

bdraco · April 18, 2023, 8:35am

After the schema migration finishes it will reorganize the database in the background and than drop any indexes that it doesn’t need anymore. It does this in batch jobs like purge does so its like any other database job except its going to do more I/O.

catsmanac · April 18, 2023, 8:37am

Thanks for the explanation. Will there be some log entry when it’s done as with schema migration?

bdraco · April 18, 2023, 8:39am

Normally background jobs aren’t logged (there are A LOT of them), but if you set homeassistant.components.recorder to debug logging you’ll be able to see it.

wouf · April 18, 2023, 8:54am

Thank you.

I have many many differences missing indexes and foreign keys. I will rollback to 2023.3 and retry a migration. Something obviously went wrong in the first place.

bdraco · April 18, 2023, 9:17am

If you create a fresh database and point your 2023.3.x instance at it, it will make fresh schema.

If you fix any incorrect schema when still on 2023.3 before upgrading to 2023.4.x the upgrade should go much smoother

wouf · April 18, 2023, 11:22am

As mentionned in the issue, I did the rollback and new update, and everything is running smoothly now.

Thanks for your help.

HA_n00b · April 18, 2023, 12:53pm

Nick, do you think that we should put some effort to migrate back from MariaDB to SQLite?

Or, is it OK to stay with MariaDB?

I saw people doing migration to SQLite, but for me the process is a bit complex and there’s chance of loosing data. Thinking to stay with MariaDB, but…

So, what’s your experts opinion on this?

bdraco · April 18, 2023, 6:15pm

Its ok to stay on MariaDB if you are comfortable being the database admin and handling anything that goes wrong. If you are running the official addon with default settings, its likely not well tuned for your system. If you haven’t done the tuning yourself already or aren’t comfortable being in the DBA role you should probably migrate back to SQLite.

nkgiovannivl · April 21, 2023, 4:16am

Hi All,

I’m getting missing information on states table :

Using local add-on of MariaDB and PHP My Admin, even with a external MariaDBs too.

Try delete all DBs and restart like a new, but it does not help…

bdraco · April 21, 2023, 5:43am

entity_id is stored in states_meta in schema 41. Which means its only stored once per entity which is what enabled rename support in 2023.4.x (and a whole lot smaller states table)

nkgiovannivl · April 21, 2023, 4:46pm

Thanks!!! @bdraco I’ll be update my querys including this INNER JOIN, but just for curiosity, is there some link or document explaining schema information and tables relation? I was googling but I can not find any formal.