Migrate back from MariaDB to the default SQLite

SteffenDE · November 21, 2024, 10:40am

Thank you. Working perfect.

Convert the database on external Linux VM is running only some minutes for 3GB SQLite database.

Back to SQLite after many years on MariaDB my HA is a little bit more responsive.

SteffenDE · November 22, 2024, 9:23am

So I think I go back to MariaDB after one day on SQLite.

After restart I must wait 10 Minutes to see data from recoder like graph and some statistic sensor do not work anymore. So I think with some more values a database is better choice.

AJediIAm · November 22, 2024, 11:08am

Home Assistant does periodic optimizations of the database so there is a good chance the issue will disappear on their own. I didn’t dive into the details since I trust Home Assistant to know best (which works for me).

A few gigabytes of database file should not be a problem on a raspberry pi 4.

If MariaDB works better for you, don’t let me hold you back from doing what works for you.

The-May · November 22, 2024, 11:51am

Nice instruction, but from what I understood: you can just turn off the mariaDB and configs that point to that DB when long term data and history does not mean anything to me, right?
So Basically the “easy” version without that much overhead would be just turn off and the system (re)creates the sqlite db, wont it?

AJediIAm · November 22, 2024, 12:04pm

Correct.
If you don’t want to migrate for example energy data, you can remove the MariaDB config and HA will start using the default database again.

dominikandreas · November 29, 2024, 8:31pm

Thanks a lot for the tutorial! Spent a lot of time trying to get this to work because I can’t update home assistant anymore while using mariadb for some reason (waiting for recorder to start until it gives up, no error messages in 2024.11.4, no response in github issue). In the end it took ~60GB of memory to finish the conversion process but looks like it finally worked.
celebrated too early… home assistant starts, but history (also long term statistics) are gone

AJediIAm · November 30, 2024, 11:39am

Did you keep the MariaDB add-on? If so, you can go back and forth until it works. There is link to another forum topic: https://community.home-assistant.io/t/migrating-from-mysql-mariadb-back-to-sqlite
Maybe you can find some tips on how to solve uour issue. Hope you figure it out.

dominikandreas · November 30, 2024, 11:58am

I did, but the latest home assistant version doesn’t work with my MariaDB anymore (no error message besides “timeout waiting for recorder”) and other integrations don’t work on the previous version anymore so can’t easily go back.

dominikandreas · November 30, 2024, 12:57pm

Finally got it working again using this for conversion from mariadb to sqlite: GitHub - mysql2sqlite/mysql2sqlite: Converts MySQL dump to SQLite3 compatible dump
Not sure if this really made the difference or whether I made some mistake in the first export using the instructions above. I may have started the first export while home assistant was still using the database.

thomine · December 15, 2024, 12:06pm

Hello,
With the help of Perplexity, i was able to install mysql2sqlite and make it works.

To install the mysql-to-sqlite3 package on your Debian 12 system hosting Home Assistant, you need to create a virtual environment. This is because Debian 12 uses an externally managed environment for system-wide Python packages. Here’s how to proceed:

First, ensure you have the necessary tools installed:

sudo apt update
sudo apt install python3-venv python3-pip

Create a virtual environment:

python3 -m venv ~/mysql_to_sqlite_env

Activate the virtual environment:

source ~/mysql_to_sqlite_env/bin/activate

Now install the package within the virtual environment:

pip install mysql-to-sqlite3

You can now use the mysql2sqlite command from within this virtual environment. To deactivate the environment when you’re done, simply type:

deactivate

Remember to activate the virtual environment each time you want to use mysql-to-sqlite3[1][6].

AJediIAm · December 15, 2024, 3:04pm

Feel free to add your instructions to the original post and give yourself the credits for it. It’s a community maintained topic. These replies are much harder to find.

Salamandar · December 23, 2024, 12:28pm

There is a way to know that.

When connected via ssh, launch a shell in the MariaDB container:

docker exec -it addon_core_mariadb bash

Once in it, run mysql and:

show processlist;

This will show the currently running processes and you can see this:

MariaDB [(none)]> show processlist;
+----+---------------+-------------------+---------------+---------+------+----------------+------------------------------------------------------------------------------------------------------+----------+
| Id | User          | Host              | db            | Command | Time | State          | Info                                                                                                 | Progress |
+----+---------------+-------------------+---------------+---------+------+----------------+------------------------------------------------------------------------------------------------------+----------+
| 30 | homeassistant | 172.30.32.1:37912 | homeassistant | Query   |  315 | altering table | OPTIMIZE TABLE states,state_attributes,events,event_data,event_types,recorder_runs,schema_changes,mi |   50.000 |
| 41 | root          | localhost         | NULL          | Query   |    0 | starting       | show processlist                                                                                     |    0.000 |
+----+---------------+-------------------+---------------+---------+------+----------------+------------------------------------------------------------------------------------------------------+----------+
2 rows in set (0.001 sec)

This OPTIMIZE TABLE will run for a while, and should disappear when you can go to the next step. (it’s still running for me )
Also, i think you can run ha core stop while it is still running.

I’ll let you edit your original post to add this information.

FleischFlori · January 28, 2025, 1:43pm

Hello,

I am trying to migrate my MariaDB to SQLite using this great guide.

But when executing mysql2sqlite it ends with an error:


mysql: Deprecated program name. It will be removed in a future release, use '/usr/bin/mariadb' instead
MySQL password: 
mysql2sqlite version 2.3.0 Copyright (c) 2019-2025 Klemen Tusar
2025-01-27 23:44:56 ERROR    2002: Can't connect to local MySQL server through socket '%-.100s' (%s) (Warning: not all arguments converted during string formatting)
2002: Can't connect to local MySQL server through socket '%-.100s' (%s) (Warning: not all arguments converted during string formatting)

Also my command seems to look strange because the db names and users are underlined.

All is happening directly on my HA mini pc. OS and core are uptodate.

How can I get this to work?

Thanks for you help. Have a nice day.

PS: Could resolve this by setting port 3307 as external port.

strikeir13 · February 3, 2025, 10:51pm

I just migrated using this guide as a starting point. I ran into memory issues where it prevented me from migrating on my Pi4 HAOS machine, so I instead used a newer Windows laptop with a WSL Ubuntu installation and just copied the home-assistant_v2.db file over when it was done. Doing it that way was much faster, too.

My system logs are showing this error now, though:

 Error executing query SELECT table_schema "database", Round(Sum(data_length + index_length) / 1048576, 2) "value" FROM information_schema.tables WHERE table_schema="homeassistant" GROUP BY table_schema LIMIT 1;: (sqlite3.OperationalError) no such table: information_schema.tables [SQL: SELECT table_schema "database", Round(Sum(data_length + index_length) / 1048576, 2) "value" FROM information_schema.tables WHERE table_schema="homeassistant" GROUP BY table_schema LIMIT 1;] (Background on this error at: https://sqlalche.me/e/20/e3q8)

My history and statistics are all still there, so I’m not sure what this error is affecting. Anyone else run into this?

Edit: Found my issue - this was a SQL integration I’d setup as a MariaDB database size sensor. Once I updated the sensor to use the SQLite code on the SQL page (SQL - Home Assistant), the error went away and my sensor was fixed.

grzywek · March 7, 2025, 10:08am

Does this mean that sqlite has a data limit and we can’t store as many states etc. as in MariaDB? Or is it just about the migration process and then the database can easily grow as large as in MariaDB?

AJediIAm · March 7, 2025, 11:15am

No. It means you don’t have enough ram to process the conversion. It is unrelated to the size or capabilities of MariaDB and SQLite.

pietdevries · May 6, 2025, 11:11am

Thank you for this excellent guide. I read the earlier topic you linked to first and was a bit daunted by the steps in that one, fortunately this method was much easier. I took a full VM backup first but it was not required as it went fairly smoothly. I added a few small things to the guide that I ran in to, but the short of it is that in 2025 this method still works perfectly.

Try2Fly · August 3, 2025, 9:13am

Thank you so much for the guide. I was holding of for the migration for years! This helped me to do it without (almost) no pain. I got the killed message at tranfering table state too, but this give me the excellent excuse to migrate the whole HA installation to a stand alone system with more RAM first (which I also wanted to do for years), and then migrate back to SQLite. This time no errors occured!

h3llrais3r · August 17, 2025, 7:35am

I just did the migration last week. It went flawlessly. Thanks for the guide!
I didn’t shrink the db size before starting, so it took a bit longer.
I also ran the conversion on a different device (laptop) with more power than my HA machine.