Qabout1: What’s a HSD? sorry thats indeed a typo, it must be homeassistant.db
Qabout2: How to create a database manually? i updated the tutorial
Qabout3: you ment the red ones when writing orange? i updated the tutorial, it clearly shows all keys what need to be deleted but in the screenshot the red key was selected.
Qabout4: don’t get it whats a dump_database thats the name i used for the tutorial database name, i updated the tutorial and change the name and made clear what is it.
if i have time i maybe can make a step by step video, but its a lot of work.
Everything fine, won’t need a video at all. And yes summing up my luckless attempts to get things migrated wasn’t work at all, but eating up time like hell.
And since your hints from the past days/weeks did bring me most closest to the finishing line it was a bit strange since I got stuck at the starting grid (point 1)
Thanks a lot for you effort in making it more readable/understandable.
Thanks for that super howto, one question regarding step 8 & 9:
In some tables there aren’t the “created” row. so, it is important that all tables have a created row or it is important that the columns are equal?
In my case, there is no “created” row in the events table homeassistant_test:
therefore I don’t create one in the homeassistant db, right?
EDIT: it works without the created row, so I assume, that only the rows between the 2 db have to be equal.
Thanks for your Guide, that was the only one which helped in my case
I’m glad i could help, for me everything still working on 3 servers.
About the CREATED row is only needed to be inserted if its missing in the new created database.
Just compare the old and new and its ok, it doesn’t have to be in every table
I recommand all people who have very less knowledge about the migration to MARIADB, just don’t do it!.
See what the developers have to say at the 2023.4 release party.
I have captured the above information regarding the recommendations to not use mysql / mariadb unless you KNOW what you are doing as a database administrator to the beginning post for this topic.
wondering the same if anyone switched back to SQLite.
Is it worth it if we have a good working setup with mariadb right now?
I don’t need external access to the database… I just want the best performance.
Yes I switched back and succeeded. If it’s worth, is up to you.
The HA developers recommend using SQLite as the DB and made some really spectacular improvements on the performance.
See my ‘manual’ (no guarantee):
Finished - I was in the process of migrating during the announcement of 2023.4 - the migration to MariaDB.
Created a full backup of HA with the new migrated MariaDB.
Restored a previous day old full back up with SQlite 2023.3
Updated to the announced HA 2023.4 with SQLite Turbo
Still working with the updated SQLite, sailing very smooth.
I my experience the new 2023.4 SQLite is working really great, very snappy and light weighted. I realize this is not a scientifically approach to comparing DB’s.
Hello,
are there any news or update about this topic ?
I tried to migrate (SQLite → MariaDB) following these notes but with core 2023.5 it seems that doesn’t work.
I’m going crazy because it would be very useful for me to have one steady database shared between a couple of homeassistant nodes (a sort of cluster, let’s say, only one node working at time, the second node only for redundancy/manteinance).
Traceback (most recent call last):
File “/usr/bin/sqlite3mysql”, line 8, in
sys.exit(cli())
File “/usr/lib/python3.10/site-packages/click/core.py”, line 1130, in call
return self.main(*args, **kwargs)
File “/usr/lib/python3.10/site-packages/click/core.py”, line 1055, in main
rv = self.invoke(ctx)
File “/usr/lib/python3.10/site-packages/click/core.py”, line 1404, in invoke
return ctx.invoke(self.callback, **ctx.params)
File “/usr/lib/python3.10/site-packages/click/core.py”, line 760, in invoke
return __callback(*args, **kwargs)
File “/usr/lib/python3.10/site-packages/sqlite3_to_mysql/cli.py”, line 204, in cli
converter.transfer()
File “/usr/lib/python3.10/site-packages/sqlite3_to_mysql/transporter.py”, line 736, in transfer
self._create_table(table[“name”], transfer_rowid=transfer_rowid)
File “/usr/lib/python3.10/site-packages/sqlite3_to_mysql/transporter.py”, line 360, in _create_table
column_type = self._translate_type_from_sqlite_to_mysql(column[“type”])
File “/usr/lib/python3.10/site-packages/sqlite3_to_mysql/transporter.py”, line 287, in _translate_type_from_sqlite_to_mysql
raise ValueError(“Invalid column_type!”)
ValueError: Invalid column_type!
The connection is being made successfully, as I’ve deleted the homassistant database manually and it recreated it with sqlite3mysql.
