Attempting to rollback a transaction with MySQL (InnoDB)

After some rare weeks of smooth running I have now encountered another SQL error…



Logger: homeassistant.components.recorder.util
Source: components/recorder/util.py:106
Integration: Recorder (documentation, issues)
First occurred: 23:26:35 (6 occurrences)
Last logged: 23:26:35

    Error executing query: (MySQLdb._exceptions.OperationalError) (2013, 'Lost connection to MySQL server during query') [SQL: SELECT states.domain AS states_domain, states.entity_id AS states_entity_id, states.state AS states_state, states.attributes AS states_attributes, states.last_changed AS states_last_changed, states.last_updated AS states_last_updated FROM states INNER JOIN (SELECT max(states.state_id) AS max_state_id FROM states INNER JOIN (SELECT states.entity_id AS max_entity_id, max(states.last_updated) AS max_last_updated FROM states WHERE states.last_updated >= %s AND states.last_updated < %s GROUP BY states.entity_id) AS anon_2 ON states.entity_id = anon_2.max_entity_id AND states.last_updated = anon_2.max_last_updated GROUP BY states.entity_id) AS anon_1 ON states.state_id = anon_1.max_state_id WHERE states.entity_id IN (%s, %s)] [parameters: (datetime.datetime(2020, 11, 1, 21, 34, 15), datetime.datetime(2020, 11, 17, 23, 26, 21, 123000, tzinfo=<UTC>), 'binary_sensor.add_ht_step_1', 'binary_sensor.add_ht_step_2')] (Background on this error at: http://sqlalche.me/e/13/e3q8)
    Error executing query: (MySQLdb._exceptions.OperationalError) (2013, 'Lost connection to MySQL server during query') [SQL: SELECT states.domain AS states_domain, states.entity_id AS states_entity_id, states.state AS states_state, states.attributes AS states_attributes, states.last_changed AS states_last_changed, states.last_updated AS states_last_updated FROM states INNER JOIN (SELECT max(states.state_id) AS max_state_id FROM states INNER JOIN (SELECT states.entity_id AS max_entity_id, max(states.last_updated) AS max_last_updated FROM states WHERE states.last_updated >= %s AND states.last_updated < %s GROUP BY states.entity_id) AS anon_2 ON states.entity_id = anon_2.max_entity_id AND states.last_updated = anon_2.max_last_updated GROUP BY states.entity_id) AS anon_1 ON states.state_id = anon_1.max_state_id WHERE states.entity_id IN (%s, %s, %s)] [parameters: (datetime.datetime(2020, 11, 1, 21, 34, 15), datetime.datetime(2020, 11, 17, 11, 26, 21, 115000, tzinfo=<UTC>), 'sensor.gt02_outdoor', 'sensor.bme680_temperature', 'sensor.gt01_rad_return')] (Background on this error at: http://sqlalche.me/e/13/e3q8)
    Error executing query: (sqlalchemy.exc.InvalidRequestError) Can't reconnect until invalid transaction is rolled back [SQL: SELECT states.domain AS states_domain, states.entity_id AS states_entity_id, states.state AS states_state, states.attributes AS states_attributes, states.last_changed AS states_last_changed, states.last_updated AS states_last_updated FROM states INNER JOIN (SELECT max(states.state_id) AS max_state_id FROM states INNER JOIN (SELECT states.entity_id AS max_entity_id, max(states.last_updated) AS max_last_updated FROM states WHERE states.last_updated >= %s AND states.last_updated < %s GROUP BY states.entity_id) AS anon_2 ON states.entity_id = anon_2.max_entity_id AND states.last_updated = anon_2.max_last_updated GROUP BY states.entity_id) AS anon_1 ON states.state_id = anon_1.max_state_id WHERE states.entity_id IN (%s, %s)] [parameters: [immutabledict({})]]
    Error executing query: (sqlalchemy.exc.InvalidRequestError) Can't reconnect until invalid transaction is rolled back [SQL: SELECT states.domain AS states_domain, states.entity_id AS states_entity_id, states.state AS states_state, states.attributes AS states_attributes, states.last_changed AS states_last_changed, states.last_updated AS states_last_updated FROM states INNER JOIN (SELECT max(states.state_id) AS max_state_id FROM states INNER JOIN (SELECT states.entity_id AS max_entity_id, max(states.last_updated) AS max_last_updated FROM states WHERE states.last_updated >= %s AND states.last_updated < %s GROUP BY states.entity_id) AS anon_2 ON states.entity_id = anon_2.max_entity_id AND states.last_updated = anon_2.max_last_updated GROUP BY states.entity_id) AS anon_1 ON states.state_id = anon_1.max_state_id WHERE states.entity_id IN (%s, %s, %s)] [parameters: [immutabledict({})]]

I have tried various suggestions including setting the option to ignore the transaction but it still occurs. The mysql error log has an entry about the log sequence number - probably due to me removing the ib_logfiles in an attempt to clean things up:

2020-11-18 23:26:42 1988325168 [Note] InnoDB: Highest supported file format is Barracuda.
2020-11-18 23:26:42 1988325168 [Note] InnoDB: The log sequence number 4120158908 in ibdata file do not match the log sequence number 4120159729 in the ib_logfiles!
2020-11-18 23:26:42 1988325168 [Note] InnoDB: Restoring possible half-written data pages from the doublewrite buffer...
2020-11-18 23:26:42 1988325168 [Note] InnoDB: 128 rollback segment(s) are active.
2020-11-18 23:26:42 1988325168 [Note] InnoDB: Waiting for purge to start
2020-11-18 23:26:42 1988325168 [Note] InnoDB:  Percona XtraDB (http://www.percona.com) 5.6.47-87.0 started; log sequence number 4120159729
2020-11-18 23:26:42 1379922752 [Note] InnoDB: Dumping buffer pool(s) not yet started
2020-11-18 23:26:42 1988325168 [Note] Plugin 'FEEDBACK' is disabled.
2020-11-18 23:26:42 1988325168 [Note] Recovering after a crash using tc.log
2020-11-18 23:26:42 1988325168 [Note] Starting crash recovery...
2020-11-18 23:26:42 1988325168 [Note] Crash recovery finished.
2020-11-18 23:26:42 1988325168 [Note] Server socket created on IP: '::'.
2020-11-18 23:26:42 1988325168 [Note] /usr/sbin/mysqld: ready for connections.
Version: '10.1.45-MariaDB-0+deb9u1'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  Raspbian 9.11

Any suggestions would be gratefully received. What I would really like to do is to configure the database to ignore previous errors.

Thank you for the work you put into HA though!

Ah - it seems that only the graphing is not working when including multiple sensors. The data is being stored and displayed for single temp censors. I shall investigate further.

I have had to drop the database and start again. Umpteenth time I have done that.

I suspect that there is some mySql param somewhere which I need to tweak. Perhaps I am trying to use HA for a purpose it was not originally designed: storing months of data.