Migrate back from MariaDB to the default SQLite

Community Guides
3

For step 2.1 I would add to tick the checkbox before Repack and make sure to toggle the switch on the right side. It’s correct in your screenshot, but better to mention it in the text as well :slight_smile:

Maybe you can also add that the repacking process can take hours. instead of a decent amount of time. Maybe wait something like 24 hours or so.

Finally it would be great if @bdraco can ‘approve’ this message with his wisdom :laughing:

4

Thank you for your input. I made some updates. More info on whether or not repack is beneficial and how long one needs to wait would be nice to have.

5

Hi, just performed the migration thanks to this guide as well. :slight_smile:
Created an account here just to help out in case some other people got stuck on this point.

Had some issues due to a too big database, the system only has 4 GB.
What I did is performed the migration on another system (VM) with plenty of RAM available and the migration went through without a problem. (quick as well on a XEON) :wink:
After that I copied the new DBfile to the HA instance and started HA core again. All long term statistics still there :slight_smile:

1 Like
6

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.

7

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 :kissing_heart:
I don’t remember why I moved to MariaDB in first place :tipping_hand_man:

8

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.

2 Likes
9

Thank you for this, all migrated back before moving to a new server. Thanks!

10

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?

11

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

12

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
13

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.

4 Likes
14

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?

15

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

1 Like
16

I just migrated from a 2.3G MySql to a 1.6G sqlite, took 22mins…thanks again @AJediIAm @jyavenard @Nardol

1 Like
17

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.

1 Like
18

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 ?

1 Like
19

When you deleted the addon, all of the files in that addon should have been deleted, including the database.

1 Like
20

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.

21

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.

22

Thanks for the guide!

I had to use mysql2sqlite with -h core-mariadb.