Migrating from MySQL (mariaDB) back to sqlite

Holdestmade · June 5, 2023, 7:46am

I had something similar and had to add host (-h)

mysql2sqlite -f /config/db2/home-assistant_v2.db -d homeassistant -u homeassistant -h 192.168.1.40 -P 3306 --mysql-password xxxxxxxxxxxxxxx -V --use-buffered-cursors

Mariusthvdb · June 5, 2023, 7:55am

aha, adding the ip address helped:

 mysql2sqlite -f /config/home-assistant_v2.db -h 192.168.1.61 -d homeassistant -u mariusthvdb -p -V --use-buffered-cursors
MySQL password: 
2023-06-05 09:52:48 INFO     Transferring table event_data
100%|█████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████| 1/1 [00:00<00:00,  2.52it/s]
2023-06-05 09:52:49 INFO     Transferring table event_types
100%|█████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████| 1/1 [00:00<00:00,  4.87it/s]
2023-06-05 09:52:49 INFO     Transferring table events
  0%|                                                                                                                                 | 0/1 [00:00<?, ?it/s]
2023-06-05 09:52:50 ERROR    SQLite transfer failed inserting data into table events: table events has no column named created
table events has no column named created

note I could use the -p

there s only the error on the table events.

update:
Ouch: all history gone from the frontend … no graphs, no energy dashboard.
quickly switch back to MariaDB…

Holdestmade · June 5, 2023, 8:07am

I created a temp folder and created the home-assistant_v2.db in there until finished and then copied into config directory.

Mariusthvdb · June 5, 2023, 8:08am

this was the exit.
it had finished creating that file… so somehow that fails. not sure what I can do to check that.

gieljnssns · June 5, 2023, 9:01am

Try to add the port 192.168.79.54 -P 3306

Mariusthvdb · June 5, 2023, 9:12am

seems the port isnt the issue, as it does find it, and starts writing that file, and transferred the 2 first tables?

I mean, I can give it another go, sure (btw I did do that before and it made no difference, with or without explicitly setting that port in the MariaBD add-on. it is the default after al)

udate:
Bingo!!
turns out, I had to completely delete the existing db file in /config, and have it create anew.

I couldnt make it happen in a subfolder though.

[core-ssh ~]$ mysql2sqlite -f /config/home-assistant_v2.db -h 192.168.1.61 -d homeassistant -u mariusthvdb -p -V --use-buffered-cursors
MySQL password: 
2023-06-05 16:48:32 INFO     Transferring table event_data
100%|█████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████| 1/1 [00:00<00:00,  2.51it/s]
2023-06-05 16:48:33 INFO     Transferring table event_types
100%|█████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████| 1/1 [00:00<00:00,  5.13it/s]
2023-06-05 16:48:33 INFO     Transferring table events
100%|█████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████| 1/1 [00:00<00:00,  1.53it/s]
2023-06-05 16:48:34 INFO     Transferring table recorder_runs
100%|█████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████| 1/1 [00:00<00:00,  5.18it/s]
2023-06-05 16:48:34 INFO     Transferring table schema_changes
100%|█████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████| 1/1 [00:00<00:00,  5.16it/s]
2023-06-05 16:48:35 INFO     Transferring table state_attributes
100%|█████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████| 1/1 [00:01<00:00,  1.05s/it]
2023-06-05 16:48:37 INFO     Transferring table states
100%|███████████████████████████████████████████████████████████████████████████████████████████████████████████████████████| 11/11 [01:38<00:00,  8.98s/it]
2023-06-05 16:51:06 INFO     Transferring table states_meta
100%|█████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████| 1/1 [00:00<00:00,  5.15it/s]
2023-06-05 16:51:07 INFO     Transferring table statistics
100%|███████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████| 19/19 [01:46<00:00,  5.58s/it]
2023-06-05 16:54:10 INFO     Transferring table statistics_meta
100%|█████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████| 1/1 [00:00<00:00,  5.13it/s]
2023-06-05 16:54:12 INFO     Transferring table statistics_runs
100%|█████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████| 1/1 [00:00<00:00,  4.55it/s]
2023-06-05 16:54:12 INFO     Transferring table statistics_short_term
100%|█████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████| 4/4 [00:17<00:00,  4.43s/it]
2023-06-05 16:54:42 INFO     Vacuuming created SQLite database file.
This might take a while.
2023-06-05 16:55:08 INFO     Done!
[core-ssh ~]$

now restarting and fingers crossed

update:

seems to do alright, and my energy dashboard is live… yeah.

only see this in the log:

Logger: homeassistant.components.recorder.util
Source: components/recorder/util.py:243 
Integration: Recorder (documentation, issues) 
First occurred: 16:59:15 (1 occurrences) 
Last logged: 16:59:15

The system could not validate that the sqlite3 database at //config/home-assistant_v2.db was shutdown cleanly

maybe because of a first restart?

Panzer_V · June 6, 2023, 10:57am

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.

TheFes · June 6, 2023, 12:00pm

[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

Panzer_V · June 6, 2023, 4:08pm

@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.

Anto79-ops · June 6, 2023, 9:20pm

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

Mariusthvdb · June 6, 2023, 9:43pm

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.

Anto79-ops · June 7, 2023, 4:50am

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

change configuration.yaml,
recorder:
#db_url (now sqlite is the defaultDB)
delete file ‘home-assistant_v2.db’ if it exist in the config folder
Stop and uninstall the MariaDB addon
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.

restart HA

TheFes · June 7, 2023, 4:50am

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.

Anto79-ops · June 7, 2023, 4:56am

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

RetroTim · June 18, 2023, 3:51pm

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?

djalexnl · June 21, 2023, 7:38pm

I have the same question … same message…

mterry63 · June 21, 2023, 10:28pm

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.

djalexnl · June 24, 2023, 11:35am

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)

rdekruyf · June 24, 2023, 4:27pm

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

djalexnl · June 25, 2023, 1:31pm

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