Large homeassistant database files

berniebl · August 4, 2017, 5:42pm

Thanks @VDRainer I will definitely do that.
I wasn’t aware of the purge time (every 2 days) that’s really good to know. I am usually in the habit of rebooting my system after I make any significant changes just to make sure that it comes up fine, so I presume that I kept resetting the clock. Thanks for you input.

VDRainer · August 4, 2017, 5:52pm

Here’s a closed issue about that problem, and a way you can set (don’t tell the devs ) the purge interval.

Coolie1101 · August 5, 2017, 4:19am

@markbajaj

Can you provide some more details on the above process, tried attempting this setup but not sure how/where to create user/db.

I tried using phpMyadmin but can’t login.

Thanks.

markbajaj · August 5, 2017, 10:49am

I used PHP admin on the QNAP (I needed to install that on the QNAP as well as MySQL). I did need to google the default password etc

Coolie1101 · August 5, 2017, 5:40pm

Did you use localhost as the host name?

I’m getting the following recurring error, I even tried using the root account:

2017-08-05 13:30:02 ERROR (Recorder) [homeassistant.components.recorder] Error during connection setup: (_mysql_exceptions.OperationalError) (1130, "Host 'Hassbian_Pi3.home' is not allowed to connect to this MariaDB server") (retrying in 3 seconds)

markbajaj · August 6, 2017, 10:58pm

Hi

Is that on a QNAP? i have not seen that screen before?

Coolie1101 · August 6, 2017, 10:59pm

Yes phpAdmin on Qnap.

markbajaj · August 6, 2017, 11:16pm

Ah, I get it. I used the below:

Hope that helps

Coolie1101 · August 6, 2017, 11:40pm

Works great, thanks.

Coolie1101 · August 6, 2017, 11:50pm

Is there anyway to specify an alternate DB location in case the current path isn’t available?

Ideally, specify two db_url’s, one as primary and one as a backup.

markbajaj · August 7, 2017, 12:04am

Not sure - I think they would need to be on separate servers with a cluster address?

bnetse · September 5, 2017, 7:01am

Hi all. I have been trying to setup and use MySQL for my database following the instructions within this thread.
When I try and install mysqlclient in the home assistant virtual environment

pip3 install --upgrade mysqlclient

I get the errors below. Something about Cython not being installed?

(homeassistant_venv) homeassistant@raspberrypi:/home/pi$ pip3 install --upgrade mysqlclient
Collecting mysqlclient
  Using cached mysqlclient-1.3.12.tar.gz
Building wheels for collected packages: mysqlclient
  Running setup.py bdist_wheel for mysqlclient ... error
  Complete output from command /srv/homeassistant/homeassistant_venv/bin/python3 -u -c "import setuptools, tokenize;__file__='/tmp/pip-build-6p6lm0hs/mysqlclient/setup.py';f=getattr(tokenize, 'open', open)(__file__);code=f.read().replace('\r\n', '\n');f.close();exec(compile(code, __file__, 'exec'))" bdist_wheel -d /tmp/tmpylduaybxpip-wheel- --python-tag cp34:
  running bdist_wheel
  running build
  running build_py
  creating build
  creating build/lib.linux-armv7l-3.4
  copying _mysql_exceptions.py -> build/lib.linux-armv7l-3.4
  creating build/lib.linux-armv7l-3.4/MySQLdb
  copying MySQLdb/__init__.py -> build/lib.linux-armv7l-3.4/MySQLdb
  copying MySQLdb/compat.py -> build/lib.linux-armv7l-3.4/MySQLdb
  copying MySQLdb/connections.py -> build/lib.linux-armv7l-3.4/MySQLdb
  copying MySQLdb/converters.py -> build/lib.linux-armv7l-3.4/MySQLdb
  copying MySQLdb/cursors.py -> build/lib.linux-armv7l-3.4/MySQLdb
  copying MySQLdb/release.py -> build/lib.linux-armv7l-3.4/MySQLdb
  copying MySQLdb/times.py -> build/lib.linux-armv7l-3.4/MySQLdb
  creating build/lib.linux-armv7l-3.4/MySQLdb/constants
  copying MySQLdb/constants/__init__.py -> build/lib.linux-armv7l-3.4/MySQLdb/constants
  copying MySQLdb/constants/CLIENT.py -> build/lib.linux-armv7l-3.4/MySQLdb/constants
  copying MySQLdb/constants/CR.py -> build/lib.linux-armv7l-3.4/MySQLdb/constants
  copying MySQLdb/constants/ER.py -> build/lib.linux-armv7l-3.4/MySQLdb/constants
  copying MySQLdb/constants/FIELD_TYPE.py -> build/lib.linux-armv7l-3.4/MySQLdb/constants
  copying MySQLdb/constants/FLAG.py -> build/lib.linux-armv7l-3.4/MySQLdb/constants
  copying MySQLdb/constants/REFRESH.py -> build/lib.linux-armv7l-3.4/MySQLdb/constants
  running build_ext
  failed to import Cython: /srv/homeassistant/homeassistant_venv/lib/python3.4/site-packages/Cython/Compiler/Lexicon.cpython-34m.so: cannot open shared object file: Value too large for defined data type
  error: Cython does not appear to be installed
  
  ----------------------------------------
  Failed building wheel for mysqlclient
  Running setup.py clean for mysqlclient
Failed to build mysqlclient
Installing collected packages: mysqlclient
  Running setup.py install for mysqlclient ... error
    Complete output from command /srv/homeassistant/homeassistant_venv/bin/python3 -u -c "import setuptools, tokenize;__file__='/tmp/pip-build-6p6lm0hs/mysqlclient/setup.py';f=getattr(tokenize, 'open', open)(__file__);code=f.read().replace('\r\n', '\n');f.close();exec(compile(code, __file__, 'exec'))" install --record /tmp/pip-ilaxc5vz-record/install-record.txt --single-version-externally-managed --compile --install-headers /srv/homeassistant/homeassistant_venv/include/site/python3.4/mysqlclient:
    running install
    running build
    running build_py
    creating build
    creating build/lib.linux-armv7l-3.4
    copying _mysql_exceptions.py -> build/lib.linux-armv7l-3.4
    creating build/lib.linux-armv7l-3.4/MySQLdb
    copying MySQLdb/__init__.py -> build/lib.linux-armv7l-3.4/MySQLdb
    copying MySQLdb/compat.py -> build/lib.linux-armv7l-3.4/MySQLdb
    copying MySQLdb/connections.py -> build/lib.linux-armv7l-3.4/MySQLdb
    copying MySQLdb/converters.py -> build/lib.linux-armv7l-3.4/MySQLdb
    copying MySQLdb/cursors.py -> build/lib.linux-armv7l-3.4/MySQLdb
    copying MySQLdb/release.py -> build/lib.linux-armv7l-3.4/MySQLdb
    copying MySQLdb/times.py -> build/lib.linux-armv7l-3.4/MySQLdb
    creating build/lib.linux-armv7l-3.4/MySQLdb/constants
    copying MySQLdb/constants/__init__.py -> build/lib.linux-armv7l-3.4/MySQLdb/constants
    copying MySQLdb/constants/CLIENT.py -> build/lib.linux-armv7l-3.4/MySQLdb/constants
    copying MySQLdb/constants/CR.py -> build/lib.linux-armv7l-3.4/MySQLdb/constants
    copying MySQLdb/constants/ER.py -> build/lib.linux-armv7l-3.4/MySQLdb/constants
    copying MySQLdb/constants/FIELD_TYPE.py -> build/lib.linux-armv7l-3.4/MySQLdb/constants
    copying MySQLdb/constants/FLAG.py -> build/lib.linux-armv7l-3.4/MySQLdb/constants
    copying MySQLdb/constants/REFRESH.py -> build/lib.linux-armv7l-3.4/MySQLdb/constants
    running build_ext
    failed to import Cython: /srv/homeassistant/homeassistant_venv/lib/python3.4/site-packages/Cython/Compiler/Lexicon.cpython-34m.so: cannot open shared object file: Value too large for defined data type
    error: Cython does not appear to be installed
    
    ----------------------------------------
Command "/srv/homeassistant/homeassistant_venv/bin/python3 -u -c "import setuptools, tokenize;__file__='/tmp/pip-build-6p6lm0hs/mysqlclient/setup.py';f=getattr(tokenize, 'open', open)(__file__);code=f.read().replace('\r\n', '\n');f.close();exec(compile(code, __file__, 'exec'))" install --record /tmp/pip-ilaxc5vz-record/install-record.txt --single-version-externally-managed --compile --install-headers /srv/homeassistant/homeassistant_venv/include/site/python3.4/mysqlclient" failed with error code 1 in /tmp/pip-build-6p6lm0hs/mysqlclient/

Has anyone else experienced this or have any ideas how to fix it.

Thanks

Darbos · September 6, 2017, 4:06pm

Ive been trying to move to mysql for a week now and just keep getting stuck. Ive been following the instrucinos below that I got from this thread.

However when I run:

sudo pip3 install --upgrade mysql-connector

I get:

Cleaning up...
Command /usr/bin/python3 -c "import setuptools, tokenize;__file__='/tmp/pip-build-d3th41x9/mysql-connector/setup.py';exec(compile(getattr(tokenize, 'open', open)(__file__).read().replace('\r\n', '\n'), __file__, 'exec'))" install --record /tmp/pip-qrqghvp2-record/install-record.txt --single-version-externally-managed --compile failed with error code 1 in /tmp/pip-build-d3th41x9/mysql-connector
Storing debug log for failure in /root/.pip/pip.log

Ive tried upgrading setupttols with no luck…any helo would be appreciated!

sudo apt-get update && sudo apt-get upgrade
sudo apt-get install mysql-server && sudo apt-get install mysql-client
sudo apt-get install libmysqlclient-dev
sudo apt-get install python-dev python3-dev
sudo pip3 install --upgrade mysql-connector
sudo pip3 install mysqlclient


mysql -uroot -p

CREATE DATABASE homeassistant;
CREATE USER 'hass'@'localhost' IDENTIFIED BY '********';
GRANT ALL PRIVILEGES ON homeassistant.* TO 'hass'@'localhost';
FLUSH PRIVILEGES;
exit;
Test if user works:

mysql -uhass homeassistant -p
exit;
Switch to homeassistant user:

sudo su -s /bin/bash homeassistant
source /srv/homeassistant/bin/activate
pip3 install --upgrade mysqlclient
exit

ntalekt · October 24, 2017, 3:41am

Although this doesn’t help the main point of this thread, I created a quick sensor to log the size of the mysql database so I can monitor it.

Create a script (e.g. check_db_size.sh) with the following command in it.

mysql -N -B -p'<YourPassword>' -u <YourDBUser> -e 'SELECT Sum(Round(( ( data_length + index_length ) / 1024 / 1024 / 1024 ), 2)) AS "Size in GB" FROM information_schema.tables WHERE table_schema = "<YourDBName>"'

Update <YourPassword>, <YourDBUser> & <YourDBName> in the above command.
Make the script executable.

chmod +x check_db_size.sh

Create a command_line sensor

  - platform: command_line
    command: ./home/homeassistant/.homeassistant/check_db_size.sh
    name: HA DB Size
    unit_of_measurement: "GB"
    scan_interval: 300

Enjoy your handiwork.

philhawthorne · October 24, 2017, 4:18am

This is awesome! Thanks for sharing!!

squirtbrnr · October 24, 2017, 4:30am

I do the command line sensor like what ntalekt posted. Unfortunately the way docker runs on my Synology NAS, I had to run the command as root on my NAS and send the output to a file in the shared config folder, then within HA read that file to grab the value.

As for the database size, mine grows about 20-30MB per day. currently sitting at about 550MB and just restarted HA to update (though it was from docker) and it booted right up. Never had a real issue with the database or restarting HA since switching to MySQL/MariaDB.

philhawthorne · October 24, 2017, 4:58am

That’s a good tip. I might do something similar! Thanks

ntalekt · October 24, 2017, 12:53pm

No problem Phil. Thanks for your glorious blog posts, they’ve helped me a lot.

fanaticDavid · October 24, 2017, 6:13pm

Thanks for sharing, @ntalekt ! I used your code, and it’s up and running. For what it’s worth, I had to do the following extra steps to get it working:

install mysql-client (as I’m not using a MySQL server on my Raspberry Pi 3) with the following command: sudo apt-get install mysql-client
add the options -h 192.168.xxx.x -P 3307 to the command as my database is located on my Synology NAS

kdex · October 25, 2017, 5:12pm

Ha ha, great minds think alike! Just yesterday I made a database size sensor so I could monitor the growth of the file. I’m using Hassbian with the default SQLite database so my sensor is just a filesize sensor.

For those wanting to do that for the basic SQLite db file (or any other file for that matter), here is the setup:

Add a command_line sensor to your config.
This uses the stat command with -c %s format option to retrieve the total file size in bytes. I divide by 1024 twice to get from bytes to MB. You can tweak the template to your liking. I also bumped the scan_interval up from the default of 60s to 900s (15 minutes) as I’ve been trying to reduce how much data is going into my database.

  - platform: command_line
    name: 'filesize_db'
    command: 'stat -c %s "/home/homeassistant/.homeassistant/home-assistant_v2.db"'
    unit_of_measurement: 'MB'
    value_template: '{{ (value|int / 1024 / 1024)|round(1) }}'
    scan_interval: 900

I like to add an entry to customize too for name and icon (remember that’s in customize under homeassistant)

- sensor.filesize_db:
  friendly_name: SQLite DB size
  icon: mdi:database

Hope that helps others using the default database setup.