Large homeassistant database files

[firstof9]

What type of database are you using?

[Coolie1101 (Coolie)]

I followed @markbajaj config, using MySQL on a Qnap,

Large homeassistant database files

Hi All, A little late to the party , but I just moved the database to a MySQL database hosted on my network on a QNAP NAS. I have HA in VirtualEnv on a pi and the user I use is hass for HA. Here are the steps I took to get it working: Create user in MySQL and create database (I used the same name ) and grant all privileges to that user on the database on the QNAP. On the Pi (as pi user) run: sudo apt-get install libmysqlclient-dev change to hass user: sudo su -s /bin/bash hass A…

[Coolie1101 (Coolie)]

Here is my config:

recorder:
  purge_interval: 1
  purge_keep_days: 5
  db_url: mysql://hass_db:[email protected]:3306/hass_db?charset=utf8

[firstof9]

Check information_schema.tables in MySQL and see if there’s anything in there.

[Coolie1101 (Coolie)]

I have no idea how to check that.

Configuration of the DB is done via phpMyAdmin with the following interface options:

[VDRainer]

@Coolie1101

(sqlite3.OperationalError) no such table

Looks like the sensor queries a sqlite database.
Have you tried it with the db_url?

EDIT:
Same problem here.
Works when providing the db_url.

No difference if the db_url is in configuration.yaml or provided in secrets.yaml.
The sensor does not read it.
cc @dgomes

[Coolie1101 (Coolie)]

Yes, same when I provide the URL.

It has something to do with the table query but, I have no idea what the query should be.

**no such table:** information_schema.tables [SQL: 'SELECT table_schema "database", Round(Sum(data_length + index_length) / 1024, 1) "value" FROM information_schema.tables WHERE table_schema="hass_db" GROUP BY table_schema LIMIT 1;'] (Background on this error at: http://sqlalche.me/e/e3q8)

[dgomes (Diogo Gomes)]

Very strange since I’ve got the very same scenario (MySQL db in my QNAP accessed external from HA on a RPI3)

Can you run the query in phpMyAdmin while logged as user: hass_db ?

[fanaticDavid (David)]

You are indeed missing the table. Here’s a list of mine:

As you can see, the table information_schema is present in my setup. I’m just not sure how it got there, to be quite honest…

[Coolie1101 (Coolie)]

I’m not sure if I’ doing this correctly, but here are a couple screen shots:

[dgomes (Diogo Gomes)]

remove the ’ from the beginning and end

[Coolie1101 (Coolie)]

I actualy have that as well"

[fanaticDavid (David)]

Yeah, I just realised your printscreen shows what’s inside hass_db. My bad…

[Coolie1101 (Coolie)]

Looks promising:

[dgomes (Diogo Gomes)]

You logged into phpMyAdmin with which user ?

My guess is you don’t have permission to query the information_schema.tables

[Coolie1101 (Coolie)]

Is your SQL Sensor working with the default config?

[dgomes (Diogo Gomes)]

The default db_url literally points to the default sqlite file (not to the currently used db_url in the recorder)

Point being that it makes life easier for newbies, but the sensor is NOT exclusive to the recorder DB

[Coolie1101 (Coolie)]

[dgomes (Diogo Gomes)]

@Coolie1101

no such table:

This means the user/password used in the db_url can’t see the information_schema.tables

can you try with user root ?

[dgomes (Diogo Gomes)]

This assures that the SQL query is right… the issue has to be on the db_url / user / password