Migrating from MySQL (mariaDB) back to sqlite

Configuration
86

After using MariaDB for more then half a year today I reverted back to the default SQlite database.

This manual was extremely helpfull! Thanks everyone for typing your experiences.

87 
[core-ssh ~]$ mysql2sqlite -f /config/db2/home-assistant_v2.db -d homeassistant -u homeassistant -h 192.168.2.3 -P 3306 -p -V --use-buffered-cursors
MySQL password:
2023-06-06 13:57:10 INFO     Transferring table event_data
100%|█████████████████████████████████████████████| 1/1 [00:00<00:00,  5.17it/s]
2023-06-06 13:57:10 INFO     Transferring table event_types
100%|█████████████████████████████████████████████| 1/1 [00:00<00:00,  7.65it/s]
2023-06-06 13:57:11 INFO     Transferring table events
100%|█████████████████████████████████████████████| 1/1 [00:01<00:00,  1.08s/it]
2023-06-06 13:57:13 INFO     Transferring table recorder_runs
100%|█████████████████████████████████████████████| 1/1 [00:00<00:00,  7.39it/s]
2023-06-06 13:57:13 INFO     Transferring table schema_changes
100%|█████████████████████████████████████████████| 1/1 [00:00<00:00, 12.66it/s]
2023-06-06 13:57:13 INFO     Transferring table state_attributes
100%|█████████████████████████████████████████████| 7/7 [00:18<00:00,  2.59s/it]
2023-06-06 13:57:48 INFO     Transferring table states
Killed

any idea why the process gets killed when it tries traferring the states?
I tried 2 times with the same result

88

@TheFes

I had the same issue today. It was related to a too big database.

My solution was to purge the MariaDb until there was 1 day left.

After that the process worked like a charm.

1 Like
89

May I Ask, if you are purging to 1 day, would it make more sense to just delete the database and start all over?

1 Like
90

I noticed half way stops too (no kills though…), when not starting without a .db file. I had to let the script re-create a file, and not overwrite an existing one.

fwiw, I had a 10 day MariaDB DB, and it took less than 6 min.

91

OK. I did a more brute force way, no migration just switched back. As expected, all my entity history and energy is gone (but, I have influxdb on another server which is my true long term database).

So, if you don’t care about history and energy dashboard, here’s how I went back to sqlite

  1. change configuration.yaml,
    recorder:
    #db_url (now sqlite is the defaultDB)

  2. delete file ‘home-assistant_v2.db’ if it exist in the config folder

  3. Stop and uninstall the MariaDB addon

  4. Remove all references to MariaDB in your configuration file like password, database size sensor, etc If you want to create the database size sensor again, follow these instructions.

  1. restart HA
2 Likes
92

I’m not so interested in the states history, but do want to have the long term statistics. Starting over would mean I lose that too.

93

I hear you! I Starting from scratch is not for everyone, for sure.

94

I’m trying to conversion tool but I get an error.

Traceback (most recent call last):
File “/usr/bin/mysql2sqlite”, line 5, in
from mysql_to_sqlite3.cli import cli
File “/usr/lib/python3.10/site-packages/mysql_to_sqlite3/init.py”, line 4, in
from .transporter import MySQLtoSQLite
File “/usr/lib/python3.10/site-packages/mysql_to_sqlite3/transporter.py”, line 16, in
from mysql.connector import CMySQLConnection, MySQLConnection, errorcode
ImportError: cannot import name ‘CMySQLConnection’ from ‘mysql.connector’ (/usr/lib/python3.10/site-packages/mysql/connector/init.py)

Any ideas about what might be wrong?

1 Like
95

I have the same question … same message…

96

I had the same message as well when attempting to install via these instructions. I wound up using the instructions to install in a WSL instance on Windows 10, then copied the exported DB to Home Assistance using the Advanced Terminal and SSH addon. Worked great, and running 2023.6.2 is seems extremely snappy when viewing graphs and logbook entries.

97

Anyone Else knows why this Error : cannot import name ‘CMySQLConnection’ from ‘mysql.connector’ (/usr/lib/python3.10/site-packages/mysql/connector/init .py) comes from and how to adress this issue??

Use advaced Terminal to connect with ssl … I am root with admin rights also installed the required packages with no issues…

OS Version: Home Assistant OS 9.5
Home Assistant Core: 2023.6.3

Any help would be gratefull… (am a nooby with Linux)

98

Today I did a succesful migration from mariaDB to sqlite after a lot of errors I found the way to go.
HA: 2023.6.3
OS: 10.3

Lessons learned:

  • Use a old version of mysql2sqlite (v1.4.17)
  • Purge via dev tools recoder to 1 day
  • Set the mariadb add-on port setting hardcoded / manual on 3306
  • Delete existing home-assistant_v2.db

Then run this in a external ssh client:

apk add python3
apk add py3-pip
pip install mysql-to-sqlite3==1.4.17 
ha core stop
mysql2sqlite -f /config/home-assistant_v2.db -d *databasename-probably-homeassistant* -u *usernameforthedatabase* -p -V --use-buffered-cursors -h *iphomeassistant* -P 3306

Then the process start and takes a few minutes. After that edit configuration.yaml and remove the db_url line. After that start home assistant again with the command ha core start

3 Likes
99

Problem was the HASSOS version… 9.5 … After I upgraded to 10.3 it worked fine… DB size went frome 910 to 580 mb…

Migration went smooth toolk about 17 minutes… rasbPI-4

Happy Camper

100

Thanks! The old version for mysql2sqlite did the trick for me!

101

What size is too big? I’ve run into this as well and have a 30 day purge set.

I started mine without a .db file and the transfer “killed” after a minute. I then tried it again (where a .db had been started) and it again, “killed” after a minute. So should an existing .db be in the folder or not?

I’m going to purge my db down and try again, so hopefully this works. I’m following the steps in post 98 on HAOS 10.3 with HA Core 2023.7.3.

102

I eventually set it to a purge of 1 day. Because I didn’t have the time to test which setting worked best.

5 Days was too much in my case. Perhaps 3 would do.

My advice….put the purge to 1 day. Execute the purge manually. Wait….and then migrate.

After that you can put the purge back to 30 days if you like.

103

I kept lowering the retention days and finally got it to work at 3 days, where it put my MariaDB Size at 1.65GB (from 9GB+). Now I’m up and running…and will need to figure out how to translate my Grafana queries from MariaDB to MySQL…on to the next adventure!

Thanks again for this awesome information and help here. @Robbin @Nardol @mneuron @HA_n00b ,and everyone else that contributed to this.

UPDATE - one interesting note, which I have yet to figure out. While I decreased my database size to 1.65GB as reported by MariaDB, the MySQL database is reporting as 3.0GB…weird. I wonder if another purge is required or something.

1 Like
104

mysql2sqlite needs quite a lot of RAM in order to work, so if it gets killed while shovelling large tables, that’s simply because the system ran out of memory.

If running within a virtual machine, simply increase available ram to the VM. My instance killed mysql2sqlite with 2GB of RAM, but finished successfully after increasing it to 8GB.

105

It took some trial and error, but all the information was already in this post.

I want astray on my impatience and stopped Home Assistant too soon after triggering the purge. @Panzer_V 's advise to wait made me realize this mistake :pray:.

Since it was a lot of reading, I took the time to summarize this topic in a guide

Please let me know if any improvements are needed. I’ll be expending the guide to contain all variations for anyone to migrate