Getting Error: "Error in database connectivity: (sqlite3.OperationalError) no such table: events"

Configuration
1

I am getting the error “Error in database connectivity: (sqlite3.OperationalError) no such table: events” and my info panel tells me it is a config error, but I cannot figure out what would be causing it. Please help.

2

you have a malformed database file. Delete it and restart home assistant. home-assistant.db

3

I’m having the same problem. I delete the database, restart the system, a new smaller fresh .db file is created, but I still get the error. My integrations seem to work, but, they work very very slowly. I enter a command to open/close/turn off/turn on a device, and it takes 10x longer than normal.

Any ideas? This is on current version 0.98.5.

4

I ended up deleting my database and adding MariaDB. I haven’t done anything beyond installing the addon and letting it run, but the error is no longer there. You may want to try that.

5

This is old but I’m facing the same problem:
This is my sensor definition:

  - platform: sql
    queries:
      - name: kitchen_outlet_fridge_energy_state_old
        unit_of_measurement: "kWh"
        query: "select state from states where entity_id = 'sensor.kitchen_outlet_fridge_energy_total' and created < datetime('now','-2 hour') order by created DESC LIMIT 1;"
        column: 'state'

The query works fine, when executed in sqlite-commandline, but in HA get the this log error:

Error executing query select state from states where entity_id = ‘sensor.kitchen_outlet_fridge_energy_total’ and created < datetime(‘now’,‘-2 hour’) order by created DESC LIMIT 1;: (sqlite3.OperationalError) no such table: states [SQL: select state from states where entity_id = ‘sensor.kitchen_outlet_fridge_energy_total’ and created < datetime(‘now’,‘-2 hour’) order by created DESC LIMIT 1;] (Background on this error at: Error Messages — SQLAlchemy 1.4 Documentation)

The database shows no signs of corruption. I can successfully execute a “vacuum” which would otherwise show corruption.
Since we now store long term statistics in the database, just deleting it is no longer an option.

I did however move the database to a different location:

recorder:
  purge_keep_days: 10
  commit_interval: 30
  db_url: sqlite:////share/hassio_db/home-assistant_v2.db

Any ideas?

6

Ups, sorry to wake you all up.
Should have testet this before, but when you move the database from it’s default location, you have to specify the new location in the sensor:

  - platform: sql
    db_url: sqlite:////share/hassio_db/home-assistant_v2.db
    queries:
      - name: kitchen_outlet_fridge_energy_state_old
        unit_of_measurement: "kWh"
        query: "select state from states where entity_id = 'sensor.kitchen_outlet_fridge_energy_total' and created < datetime('now','-2 hour') order by created DESC LIMIT 1;"
        column: 'state'
1 Like