Upgrade DB failed

Configuration
1

upgrade to latest hass, but seems some problems in DB upgrade, any bug here?

INFO:homeassistant.components.recorder.migration:Adding index `ix_states_event_id` to database. Note: this can take several minutes on large databases and slow computers. Please be patient!
ERROR:homeassistant.components.recorder.util:Error executing query: (_mysql_exceptions.OperationalError) (1061, "Duplicate key name 'ix_states_event_id'") [SQL: 'CREATE INDEX ix_states_event_id ON states (event_id)'] (Background on this error at: http://sqlalche.me/e/e3q8)
INFO:homeassistant.core:Bus:Handling <Event system_log_event[L]: timestamp=1520848015.800632, level=ERROR, message=Error executing query: (_mysql_exceptions.OperationalError) (1061, "Duplicate key name 'ix_states_event_id'") [SQL: 'CREATE INDEX ix_states_event_id ON states (event_id)'] (Background on this error at: http://sqlalche.me/e/e3q8), exception=, source=components/recorder/util.py>
ERROR:homeassistant.components.recorder:Error during connection setup: (_mysql_exceptions.OperationalError) (1061, "Duplicate key name 'ix_states_event_id'") [SQL: 'CREATE INDEX ix_states_event_id ON states (event_id)'] (Background on this error at: http://sqlalche.me/e/e3q8) (retrying in 3 seconds)
INFO:homeassistant.core:Bus:Handling <Event system_log_event[L]: timestamp=1520848015.8056524, level=ERROR, message=Error during connection setup: (_mysql_exceptions.OperationalError) (1061, "Duplicate key name 'ix_states_event_id'") [SQL: 'CREATE INDEX ix_states_event_id ON states (event_id)'] (Background on this error at: http://sqlalche.me/e/e3q8) (retrying in 3 seconds), exception=, source=components/recorder/__init__.py>
2

Had the same problem, because i created the index by hand before.
You have to drop the index ‘ix_states_event_id’.

  • stop homeassistant
  • with your favorite mysql client:
set FOREIGN_KEY_CHECKS=0
DROP INDEX ix_states_event_id ON states
  • start homeassistant
1 Like
3

thanks. new error ERROR 1553 (HY000): Cannot drop index 'ix_states_event_id': needed in a foreign key constraint, any way to fix?

4

That error went away for me after stopping HA.
Maybe this helps.

5

Thank you.

6

After upgrading to 0.65 I had the same issue and could not get it to work by dropping the ix_states_event_id index on the states table in MySQL 5.5.57 because after a restart of Home assistant, the error remained.

I noticed another error indicating a problem inserting a record in the schema_changes table where the change_id column contained a 0 value for the row containing schema_version with a value of 5. After manually setting a value for the change_id column, that error disappeared.

After a reboot of my Rpi3, another problem surfaced where Home Assistant could not find the states table mentioning the folowing error

Load table 'homeassistant/states' failed, the table has missing foreign key indexes. Turn off 'foreign_key_checks' and try again.InnoDB: Load table 'homeassistant/states' failed, the table has missing foreign key indexes.

The solution was to look for which indexes existed on the states table against which foreign keys by using the following statements:

-- Show initial statement to create the STATES table
SHOW CREATE TABLE states;

-- Show existing indexes in the database called 'homeassistant'
SELECT DISTINCT table_name, index_name
   FROM information_schema.statistics
WHERE table_schema = 'homeassistant';

For some reason the states_ibfk_1 foreign key to the events table existed, but the corresponding index with the same name was missing. Normally this index is automatically created in the InnoDB storage engine when adding a foreign key.

CREATE INDEX states_ibfk_1 ON homeassistant.states (event_id);

By manually adding the index with the following statement, all went well:

Not sure it this helps somebody, but at least for me it worked :slight_smile:

1 Like
7

Reading the comments I did (for a database names ha)

USE ha;
SET FOREIGN_KEY_CHECKS=0;
DROP INDEX ix_states_event_id ON states;
CREATE INDEX states_ibfk_1 ON ha.states (event_id);

And it is back up. Thanks!