I created a temp folder and created the home-assistant_v2.db in there until finished and then copied into config directory.
this was the exit.
it had finished creating that file⦠so somehow that fails. not sure what I can do to check that.
Try to add the port 192.168.79.54 -P 3306
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?
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.
[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
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.
May I Ask, if you are purging to 1 day, would it make more sense to just delete the database and start all over?
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.
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
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.
I hear you! I Starting from scratch is not for everyone, for sure.
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?
I have the same question β¦ same messageβ¦
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.
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)
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
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
Thanks! The old version for mysql2sqlite did the trick for me!
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.