I added a note of the alternative approach to the guide. Originally, I considered this as well, but decided it was enough work to document the local migration.
Feel free to copy this guide and adapt it to a remote migration. I’ll link to it from here.
Or you can provide the steps below so I can link to your post.
Anyone else performed this migration lately without issues?
I’m running MariaDB now without issues but I want to migrate to the recommended default DB because of KISS 
I don’t remember why I moved to MariaDB in first place 
MariaDB used to be a lot faster, but it was not well suited for an SD card. Those who had an SSD could gain very significant performance boosts by using MariaDB.
The developers explained during a release party a few months back that the SQLite is now (after a ton of hard work) as fast as MariaDB and more stable. MariaDB is no longer recommended. That sparked this forum topic: https://community.home-assistant.io/t/migrating-from-mysql-mariadb-back-to-sqlite
As you can read in the topic, there has been quite some intrest. After the migration process was fully figured out, I created this guide to make it more accessible for a wider audience.
Thank you for this, all migrated back before moving to a new server. Thanks!
I have HA and MariaDB in separate docker containers, following along and changing the mysql2sqlite command goes fine, but:
2023-11-26 12:57:13 INFO Transferring table states
0%| | 0/12 [00:00<?, ?it/s]
2023-11-26 12:57:36 ERROR SQLite transfer failed inserting data into table states: table states has no column named domain
table states has no column named domain
What am I overlooking?
Unfortunately, I have not seen this before.
Check the original topic since it has more trail and error information: https://community.home-assistant.io/t/migrating-from-mysql-mariadb-back-to-sqlite
I am getting something similar:
2023-11-26 16:02:22 ERROR SQLite transfer failed inserting data into table events: table events has no column named created
table events has no column named created
Additional note for those of you get an error similar to “inserting data into table xxx no column” or similar, delete the previously failed /config/home_assistant_v2.db. The failure is caused by the prior failed migration, but the error is not obvious.
I want to revert to SQLite, and I don’t mind if my data is lost. Is it acceptable to simply remove the MariaDB addon and eliminate the configuration.yaml entry for MariaDB, allowing for the deployment of a new SQLite database?
Not 100% sure but pretty close sure…this will also mean loosing more, e.g. for my docker install I would have to reinstall each component/device from scratch
If you want a fresh install then check other posts how to best accomplish this
I just migrated from a 2.3G MySql to a 1.6G sqlite, took 22mins…thanks again @AJediIAm @jyavenard @Nardol
This is exactly what I did, and it worked perfectly fine. After a day or two, I had all of the historical data that I normally refer to.
Thank you Dave. I just did that and it worked fine. I can see that there is a new DB. Is there a way to see if there is left over files from mariaDB ?
When you deleted the addon, all of the files in that addon should have been deleted, including the database.
Thanks for the instructions. If you have an installation that has been running with (a lot of) statistics since the beginning of statistics, 4GB of memory is not enough to do the migration. It runs out of memory on the statistics table.
10 days of state information was no problem (1000+ entities).
Did the migration with a vm on my laptop in the end, even with 8GB I was afraid I would run out of memory.
Running on SQLite now with HA Blue and USB SSD. I get the feeling that HA is running smoother now.
Forum topics in the guides section should be editable as a wiki by everyone. Feel free to add/update the guide to help out others.
Thanks for the guide!
I had to use mysql2sqlite with -h core-mariadb.
Any idea how to fix this?
2023-12-21 11:47:26 ERROR SQLite transfer failed inserting data into table events: table events has no column named data_id
Unfortunately step 5.1 now no longer works so we’re stuck:
when trying to pip install the conversion tool: pip refuses to install it with an error:
~ # pip install mysql-to-sqlite3
error: externally-managed-environment
× This environment is externally managed
╰─>
The system-wide python installation should be maintained using the system
package manager (apk) only.
If the package in question is not packaged already (and hence installable via
"apk add py3-somepackage"), please consider installing it inside a virtual
environment, e.g.:
python3 -m venv /path/to/venv
. /path/to/venv/bin/activate
pip install mypackage
To exit the virtual environment, run:
deactivate
The virtual environment is not deleted, and can be re-entered by re-sourcing
the activate file.
To automatically manage virtual environments, consider using pipx (from the
pipx package).
note: If you believe this is a mistake, please contact your Python installation or OS distribution provider. You can override this, at the risk of breaking your Python installation or OS, by passing --break-system-packages.
hint: See PEP 668 for the detailed specification.
Which means there’s no way now to do the actual conversion. I don’t know what the ramifications will be of using the --break-system-packages flag they mention either.
Thank you for your feedback. Can you provide some more details about your setup?
If anyone has encountered and/or solved this problem, please update the original post. There is an edit button at the top.