I ran some script which produced these:
I shall just install MQTT at the OS level and see how to get the containerised HA to talk with it.
I ran some script which produced these:
I shall just install MQTT at the OS level and see how to get the containerised HA to talk with it.
How did you get that table? I have looked everywhere in Portainer.
I shall do some reading on Docker terminology! Thanks for the pointers.
Something similar but here is the output for that command:
gerald@Linux-Laptop:~$ sudo find / -iname configuration.yaml
[sudo] password for gerald:
find: ‘/run/user/1000/doc’: Permission denied
find: ‘/run/user/1000/gvfs’: Permission denied
/var/lib/docker/overlay2/456356bb2d6fe60d2d0b2c113ff38b6addbf39297cf6c10d03566dadbd36b226/diff/config/configuration.yaml
/var/lib/docker/overlay2/456356bb2d6fe60d2d0b2c113ff38b6addbf39297cf6c10d03566dadbd36b226/merged/config/configuration.yaml
This is what I am hoping to port across and the ability to hang on to a few months of metrics. Every week or so the Rpi becomes unresponsive - even to SSH access. Then I have to power it off. I think there is then a chance of disk corruption (it boots off sd card but all other files are on USB hard drive). It then requires some commands to clear the corruption. Also, there is an occasional foreign constraint error in the database.
Thank you all for your comments. The support for this software is why I perservere with it even though my technical skills are somewhat lacking!
I hope, you will figure it out. Unfortunately, i’m not able to help more, as i’m also a noob in Linux. I’m just good in following guides exactly, so i get everything working. But troubleshooting is not in my competences
Wish you luck
I‘m a little bit at lost. Your raspi is still running, right? Isn‘t there no possibility to take a backup?
Yes - I can take a backup but as I used Hassbian for the OS I thought I had better start from scratch. I can easily access all the files from the Rpi but the equivalents under Docker are a mystery to me!
Ah - Rpi implementation playing up again on the history:
Logger: homeassistant.components.recorder
Source: components/recorder/__init__.py:462
Integration: Recorder ([documentation](https://www.home-assistant.io/integrations/recorder), [issues](https://github.com/home-assistant/home-assistant/issues?q=is%3Aissue+is%3Aopen+label%3A%22integration%3A+recorder%22))
First occurred: 05:41:18 (35 occurrences)
Last logged: 10:39:33
* Database connection invalidated: (MySQLdb._exceptions.OperationalError) (2013, 'Lost connection to MySQL server during query') [SQL: INSERT INTO events (event_id, event_type, event_data, origin, time_fired, created, context_id, context_user_id, context_parent_id) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)] [parameters: ((1469330, 'state_changed', '{}', 'LOCAL', datetime.datetime(2021, 4, 21, 9, 3, 33, 464583, tzinfo=datetime.timezone.utc), datetime.datetime(2021, 4, 21, 9, 3, 33, 464583, tzinfo=datetime.timezone.utc), '36f54c1d4a56c45aacb72d9439cbd7a6', None, None), (1469325, 'state_changed', '{}', 'LOCAL', datetime.datetime(2021, 4, 21, 9, 3, 18, 800082, tzinfo=datetime.timezone.utc), datetime.datetime(2021, 4, 21, 9, 3, 18, 800082, tzinfo=datetime.timezone.utc), '83c2eb6eb19b99213a98878ec9a086ba', None, None), (1469318, 'state_changed', '{}', 'LOCAL', datetime.datetime(2021, 4, 21, 9, 3, 4, 688570, tzinfo=datetime.timezone.utc), datetime.datetime(2021, 4, 21, 9, 3, 4, 688570, tzinfo=datetime.timezone.utc), 'b12d56c33abeaf08f2659f065b2f90f6', None, None), (1469312, 'state_changed', '{}', 'LOCAL', datetime.datetime(2021, 4, 21, 9, 2, 50, 105188, tzinfo=datetime.timezone.utc), datetime.datetime(2021, 4, 21, 9, 2, 50, 105188, tzinfo=datetime.timezone.utc), '3a7953915c718a4b580abbced7fa7024', None, None), (1469309, 'state_changed', '{}', 'LOCAL', datetime.datetime(2021, 4, 21, 9, 2, 35, 198559, tzinfo=datetime.timezone.utc), datetime.datetime(2021, 4, 21, 9, 2, 35, 198559, tzinfo=datetime.timezone.utc), '17bdec33ab9d9f24e84fb36f1c5b02da', None, None), (1469304, 'state_changed', '{}', 'LOCAL', datetime.datetime(2021, 4, 21, 9, 2, 19, 745969, tzinfo=datetime.timezone.utc), datetime.datetime(2021, 4, 21, 9, 2, 19, 745969, tzinfo=datetime.timezone.utc), 'ae787ecd65e5190bc8ce825dde40964f', None, None), (1469298, 'state_changed', '{}', 'LOCAL', datetime.datetime(2021, 4, 21, 9, 2, 4, 602028, tzinfo=datetime.timezone.utc), datetime.datetime(2021, 4, 21, 9, 2, 4, 602028, tzinfo=datetime.timezone.utc), 'cf6fb59c60fe4e4f3b9a672825982ff4', None, None), (1469292, 'state_changed', '{}', 'LOCAL', datetime.datetime(2021, 4, 21, 9, 1, 50, 17138, tzinfo=datetime.timezone.utc), datetime.datetime(2021, 4, 21, 9, 1, 50, 17138, tzinfo=datetime.timezone.utc), '6836c7408a2c1e1bdc59ddfb976dd9fd', None, None) ... displaying 10 of 547 total bound parameter sets ... (1464614, 'state_changed', '{}', 'LOCAL', datetime.datetime(2021, 4, 21, 3, 15, 6, 299993, tzinfo=datetime.timezone.utc), datetime.datetime(2021, 4, 21, 3, 15, 6, 299993, tzinfo=datetime.timezone.utc), '364f49e426ac50c081337732e6ae411e', None, None), (1464610, 'state_changed', '{}', 'LOCAL', datetime.datetime(2021, 4, 21, 3, 14, 23, 570884, tzinfo=datetime.timezone.utc), datetime.datetime(2021, 4, 21, 3, 14, 23, 570884, tzinfo=datetime.timezone.utc), '60d2f85ccbd7fb01827781ab37fe7d30', None, None))] (Background on this error at: http://sqlalche.me/e/13/e3q8). (retrying in 3 seconds)
* Database connection invalidated: (MySQLdb._exceptions.OperationalError) (2013, 'Lost connection to MySQL server during query') [SQL: INSERT INTO events (event_id, event_type, event_data, origin, time_fired, created, context_id, context_user_id, context_parent_id) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)] [parameters: ((1469410, 'state_changed', '{}', 'LOCAL', datetime.datetime(2021, 4, 21, 9, 7, 11, 241727, tzinfo=datetime.timezone.utc), datetime.datetime(2021, 4, 21, 9, 7, 11, 241727, tzinfo=datetime.timezone.utc), '02100c734bcf3bdc857c00cbb56f3c87', None, None), (1469404, 'state_changed', '{}', 'LOCAL', datetime.datetime(2021, 4, 21, 9, 6, 56, 809452, tzinfo=datetime.timezone.utc), datetime.datetime(2021, 4, 21, 9, 6, 56, 809452, tzinfo=datetime.timezone.utc), '828e5688401e35ef392d69e48a6ea1a2', None, None), (1469399, 'state_changed', '{}', 'LOCAL', datetime.datetime(2021, 4, 21, 9, 6, 42, 696152, tzinfo=datetime.timezone.utc), datetime.datetime(2021, 4, 21, 9, 6, 42, 696152, tzinfo=datetime.timezone.utc), '2447321d7cb1d8fc7a121e563525d4b6', None, None), (1469391, 'state_changed', '{}', 'LOCAL', datetime.datetime(2021, 4, 21, 9, 6, 28, 366982, tzinfo=datetime.timezone.utc), datetime.datetime(2021, 4, 21, 9, 6, 28, 366982, tzinfo=datetime.timezone.utc), '3faee9b3504fff8650becd366730d77a', None, None), (1469387, 'state_changed', '{}', 'LOCAL', datetime.datetime(2021, 4, 21, 9, 6, 14, 264298, tzinfo=datetime.timezone.utc), datetime.datetime(2021, 4, 21, 9, 6, 14, 264298, tzinfo=datetime.timezone.utc), 'eb760a8bc63a2c13a99420e6e3548558', None, None), (1469382, 'state_changed', '{}', 'LOCAL', datetime.datetime(2021, 4, 21, 9, 5, 59, 151746, tzinfo=datetime.timezone.utc), datetime.datetime(2021, 4, 21, 9, 5, 59, 151746, tzinfo=datetime.timezone.utc), 'aa7fe79b4477f67cc8abe4f95b7d1533', None, None), (1469377, 'state_changed', '{}', 'LOCAL', datetime.datetime(2021, 4, 21, 9, 5, 44, 432820, tzinfo=datetime.timezone.utc), datetime.datetime(2021, 4, 21, 9, 5, 44, 432820, tzinfo=datetime.timezone.utc), '721f79a15297820e5b382d963bfb1e8f', None, None), (1469373, 'state_changed', '{}', 'LOCAL', datetime.datetime(2021, 4, 21, 9, 5, 30, 88366, tzinfo=datetime.timezone.utc), datetime.datetime(2021, 4, 21, 9, 5, 30, 88366, tzinfo=datetime.timezone.utc), '8ec91521113006f8e15e52dfc0b60ba3', None, None) ... displaying 10 of 827 total bound parameter sets ... (1464598, 'state_changed', '{}', 'LOCAL', datetime.datetime(2021, 4, 21, 3, 12, 56, 850283, tzinfo=datetime.timezone.utc), datetime.datetime(2021, 4, 21, 3, 12, 56, 850283, tzinfo=datetime.timezone.utc), 'd9b090242a038c01c9c4cd5e1002eea4', None, None), (1464594, 'state_changed', '{}', 'LOCAL', datetime.datetime(2021, 4, 21, 3, 12, 28, 334196, tzinfo=datetime.timezone.utc), datetime.datetime(2021, 4, 21, 3, 12, 28, 334196, tzinfo=datetime.timezone.utc), 'bdaa6add3b9fc607aae9bfe50b666e5b', None, None))] (Background on this error at: http://sqlalche.me/e/13/e3q8). (retrying in 3 seconds)
* Database connection invalidated: (MySQLdb._exceptions.OperationalError) (2013, 'Lost connection to MySQL server during query') [SQL: INSERT INTO events (event_id, event_type, event_data, origin, time_fired, created, context_id, context_user_id, context_parent_id) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)] [parameters: ((1469640, 'state_changed', '{}', 'LOCAL', datetime.datetime(2021, 4, 21, 9, 19, 7, 503906, tzinfo=datetime.timezone.utc), datetime.datetime(2021, 4, 21, 9, 19, 7, 503906, tzinfo=datetime.timezone.utc), '5667834fbf1949420455cf94361c2ada', None, None), (1469636, 'state_changed', '{}', 'LOCAL', datetime.datetime(2021, 4, 21, 9, 18, 51, 706976, tzinfo=datetime.timezone.utc), datetime.datetime(2021, 4, 21, 9, 18, 51, 706976, tzinfo=datetime.timezone.utc), 'ac774e54596f069f4e04b941e0e6b19a', None, None), (1469632, 'state_changed', '{}', 'LOCAL', datetime.datetime(2021, 4, 21, 9, 18, 36, 931145, tzinfo=datetime.timezone.utc), datetime.datetime(2021, 4, 21, 9, 18, 36, 931145, tzinfo=datetime.timezone.utc), '7b8304b9d8b02365add70a45623a6268', None, None), (1469626, 'state_changed', '{}', 'LOCAL', datetime.datetime(2021, 4, 21, 9, 18, 23, 880262, tzinfo=datetime.timezone.utc), datetime.datetime(2021, 4, 21, 9, 18, 23, 880262, tzinfo=datetime.timezone.utc), '3dcdb1b06c231a567c261e55bb0f675d', None, None), (1469622, 'state_changed', '{}', 'LOCAL', datetime.datetime(2021, 4, 21, 9, 18, 8, 174782, tzinfo=datetime.timezone.utc), datetime.datetime(2021, 4, 21, 9, 18, 8, 174782, tzinfo=datetime.timezone.utc), '3310b341ff175d4def2212fbee37355a', None, None), (1469617, 'state_changed', '{}', 'LOCAL', datetime.datetime(2021, 4, 21, 9, 17, 53, 489975, tzinfo=datetime.timezone.utc), datetime.datetime(2021, 4, 21, 9, 17, 53, 489975, tzinfo=datetime.timezone.utc), 'f2b46982bcdb1a347ca6ab6f5647b990', None, None), (1469612, 'state_changed', '{}', 'LOCAL', datetime.datetime(2021, 4, 21, 9, 17, 39, 165783, tzinfo=datetime.timezone.utc), datetime.datetime(2021, 4, 21, 9, 17, 39, 165783, tzinfo=datetime.timezone.utc), 'd4fe959c33734151386c663f6417a54a', None, None), (1469607, 'state_changed', '{}', 'LOCAL', datetime.datetime(2021, 4, 21, 9, 17, 24, 701903, tzinfo=datetime.timezone.utc), datetime.datetime(2021, 4, 21, 9, 17, 24, 701903, tzinfo=datetime.timezone.utc), '0ce29a81f622100ffcbec52df85d74ac', None, None) ... displaying 10 of 744 total bound parameter sets ... (1464877, 'state_changed', '{}', 'LOCAL', datetime.datetime(2021, 4, 21, 3, 40, 46, 996402, tzinfo=datetime.timezone.utc), datetime.datetime(2021, 4, 21, 3, 40, 46, 996402, tzinfo=datetime.timezone.utc), 'dd882404500b19d6123f323e1e0be189', None, None), (1464873, 'state_changed', '{}', 'LOCAL', datetime.datetime(2021, 4, 21, 3, 40, 32, 599628, tzinfo=datetime.timezone.utc), datetime.datetime(2021, 4, 21, 3, 40, 32, 599628, tzinfo=datetime.timezone.utc), '36236bb80d4e69b71363d1a890e46bef', None, None))] (Background on this error at: http://sqlalche.me/e/13/e3q8). (retrying in 3 seconds)
* Database connection invalidated: (MySQLdb._exceptions.OperationalError) (2013, 'Lost connection to MySQL server during query') (Background on this error at: http://sqlalche.me/e/13/e3q8). (retrying in 3 seconds)
* Database connection invalidated: (MySQLdb._exceptions.OperationalError) (2013, 'Lost connection to MySQL server during query') [SQL: INSERT INTO events (event_id, event_type, event_data, origin, time_fired, created, context_id, context_user_id, context_parent_id) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)] [parameters: ((1469782, 'state_changed', '{}', 'LOCAL', datetime.datetime(2021, 4, 21, 9, 27, 18, 288439, tzinfo=datetime.timezone.utc), datetime.datetime(2021, 4, 21, 9, 27, 18, 288439, tzinfo=datetime.timezone.utc), 'b1fe67792546d3a3b07f850c1831baf8', None, None), (1469778, 'state_changed', '{}', 'LOCAL', datetime.datetime(2021, 4, 21, 9, 27, 4, 65987, tzinfo=datetime.timezone.utc), datetime.datetime(2021, 4, 21, 9, 27, 4, 65987, tzinfo=datetime.timezone.utc), '7884746e68412d4b1c69ab31e60fa764', None, None), (1469773, 'state_changed', '{}', 'LOCAL', datetime.datetime(2021, 4, 21, 9, 26, 50, 366763, tzinfo=datetime.timezone.utc), datetime.datetime(2021, 4, 21, 9, 26, 50, 366763, tzinfo=datetime.timezone.utc), '98e6615259e12b8774b87343a74d39ab', None, None), (1469767, 'state_changed', '{}', 'LOCAL', datetime.datetime(2021, 4, 21, 9, 26, 35, 76712, tzinfo=datetime.timezone.utc), datetime.datetime(2021, 4, 21, 9, 26, 35, 76712, tzinfo=datetime.timezone.utc), '54ba60bb654e7963b46af0a5582a10b5', None, None), (1469763, 'state_changed', '{}', 'LOCAL', datetime.datetime(2021, 4, 21, 9, 26, 20, 68351, tzinfo=datetime.timezone.utc), datetime.datetime(2021, 4, 21, 9, 26, 20, 68351, tzinfo=datetime.timezone.utc), 'b4d06996400370818427ac2488321bfc', None, None), (1469758, 'state_changed', '{}', 'LOCAL', datetime.datetime(2021, 4, 21, 9, 26, 5, 295291, tzinfo=datetime.timezone.utc), datetime.datetime(2021, 4, 21, 9, 26, 5, 295291, tzinfo=datetime.timezone.utc), 'a071e601f424430c6b5cb39ecefecc6f', None, None), (1469753, 'state_changed', '{}', 'LOCAL', datetime.datetime(2021, 4, 21, 9, 25, 51, 75893, tzinfo=datetime.timezone.utc), datetime.datetime(2021, 4, 21, 9, 25, 51, 75893, tzinfo=datetime.timezone.utc), '7d83996a8f3c7d5464ebf62747c7e804', None, None), (1469749, 'state_changed', '{}', 'LOCAL', datetime.datetime(2021, 4, 21, 9, 25, 36, 536261, tzinfo=datetime.timezone.utc), datetime.datetime(2021, 4, 21, 9, 25, 36, 536261, tzinfo=datetime.timezone.utc), '16bdc3b35d9fa23a76bf20eb59b8bdf9', None, None) ... displaying 10 of 893 total bound parameter sets ... (1464598, 'state_changed', '{}', 'LOCAL', datetime.datetime(2021, 4, 21, 3, 12, 56, 850283, tzinfo=datetime.timezone.utc), datetime.datetime(2021, 4, 21, 3, 12, 56, 850283, tzinfo=datetime.timezone.utc), 'd9b090242a038c01c9c4cd5e1002eea4', None, None), (1464594, 'state_changed', '{}', 'LOCAL', datetime.datetime(2021, 4, 21, 3, 12, 28, 334196, tzinfo=datetime.timezone.utc), datetime.datetime(2021, 4, 21, 3, 12, 28, 334196, tzinfo=datetime.timezone.utc), 'bdaa6add3b9fc607aae9bfe50b666e5b', None, None))] (Background on this error at: http://sqlalche.me/e/13/e3q8). (retrying in 3 seconds)
Could all be down to the foreign key constraint again. When I initially transferred from the in-build db to mySql I seem to remember having to do something about this…
Logger: homeassistant.components.recorder.util
Source: components/recorder/util.py:43
Integration: Recorder (documentation, issues)
First occurred: 20 April 2021, 04:12:00 (2 occurrences)
Last logged: 04:12:00
Error executing query: (MySQLdb._exceptions.IntegrityError) (1451, 'Cannot delete or update a parent row: a foreign key constraint fails (`homeassistant`.`states`, CONSTRAINT `states_ibfk_2` FOREIGN KEY (`old_state_id`) REFERENCES `states` (`state_id`))') [SQL: DELETE FROM states WHERE states.last_updated < %s] [parameters: (datetime.datetime(2021, 1, 12, 17, 42, 38, tzinfo=<UTC>),)] (Background on this error at: http://sqlalche.me/e/13/gkpj)
I would reinstall the whole thing again and then import the backup (as stevemann already advised). However, I don’t know anything about external databases, so I don’t want to tell you anything wrong.
I have done exactly that twice. I think there are three fundamental problems with running my version of HA on the Rpi (Model 3):
I hope that the Ubuntu system can sort the above out. Actually, I do not know what database has been installed with the Docker HA but will only go away from the default if necessary. I shall put a USB hard drive on the laptop to perform asynch backups too.
By the way, I have another Rpi with a BME680 sensor sending data to HA via MQTT. That runs for months without any trouble but is not really updating many files locally. Awesome bit of technology too! (I seem to remember I had to mod the C program as it was sample rate was far too fast for what I wanted. Python is used to interface with MQTT.)
Exactly. With Pi’s, you have the problem that the SD card sometime becomes broken due to the constant overwriting.
If you want to reinstall, I highly recommend using a solid manual. I started with a Philips Hue Bridge and the Hue App, that was my Smart Home software
Then I discovered Home Assistant and it was a good 2 weeks and countless installations before I had a decent grasp of it.
OK, I read this whole thing…
I think you need to start from scratch.
You want HA Supervised.
So you need to start with a Debian 10 installation on the laptop.
Get that installed first.
THen you can install HA Supervised on top of that. See the community guide for that install here:
Then after you have everything up and running using that procedure you need to ensure that you have SSH and Samba set up (add-on’s are available for those) in order to access your system (I think it might be included in that procedure but I’m not sure).
Then you need to make a complete backup of your config directory in your hassbian install. Including all of the hidden folders in the hidden .storage directory.
from memory it is located in the “/home/homeassistant/.homeassistant” folder.
Using samba copy that backup to the new HA config directory.
as noted above it should be in the “/usr/share/hassio/homeassistant/” folder.
once EVERYTHING is moved over restart HA and see where you are.
If everything goes well then you can start adding other things like MQTT add-ons, etc.
Finity, thank you very much for taking the time to delve through the info and your advice.
As someone who has commented on how “technical” HA’s configuration was in the past and the need to move away from that, I am pleased to see that standards and frameworks have been implemented to minimise hassle further down the line.
In my case, perhaps HA is overkill. However, it is excellent at providing feedback on how any changes to the heat pump have affected its efficiency (by monitoring internal temperatures vs external and the pumps energy usage - via Effergy integration). If I had a spare machine I would certain use the approach you have recommended and I suspect it will give the most robust implementation. However, the system has many hats: It is used as a media server for the house; a testbed for web-facing applications, the latest being a photography site and development environment including Eclipse and others.
I know that I should treat it as I used to treat “Production” environments in my old career and I would if I used HA as some people do. I have seen some excellent examples, both technically and aesthetically!
I am also pleased to see that there is less dependency on manual changes to config files, even though I found the yaml syntax easy to pick up. I think there is a better future for HA if you continue with these standards and the “appliance” approach but not for me at the moment.
I manually installed Mosquitto at OS level and I reckon I can get that to work. Also, I found how to get at the actual HA files via Portainer so I can use commands to manipulate files from there. I can then use SFTP to move things around from some directory which FileZilla can easily see!
Thanks again for your reply. And thank you for moving the development of HA in a direction which is needed if it is to be used by the less “technically aware”.
An update and warnings to anyone tempted to upgrade Ubuntu to v22.04. I did it and lots of things did not work afterwards.
As HA is installed in a Docker container - which I can usually access and administer via Portainer, a problem with Docker stopped HA from functioning. It took me a while to track down as Docker itself was working and I could transfer yaml files to and from the container. As Portainer itself is in a Docker container I could not use that to diagnose. It was all down to a change in how trusted keys are stored. I gleaned the commands (sudo apt-key export) from the web. After a lot of farting about, the containers started ok.
I use MQTT with Mosqitto broker and noticed that although HA was up and running no MQTT data was being received. That also took some tracking down but fixed by putting the explicit listener port in the config file.
I add this information here as it has taken me days to solve the problems and hope it helps anyone else in a similar mess.
tl;dr
How to move home assistant to a new host.
Easy.