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?
1 Like
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
3 Likes
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.
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.
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
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.
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 
.
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
1 Like
has anyone encountered:
2023-09-12 15:09:54.771 WARNING (MainThread) [homeassistant.components.http.security_filter] Filtered a request with a potential harmful query string: /magmi/web/ajax_pluginconf.php?file=../../../../../../../../../../../etc/passwd&plugintype=utilities&pluginclass=CustomSQLUtility
2023-09-12 15:09:54.801 WARNING (MainThread) [homeassistant.components.http.security_filter] Filtered a request with a potential harmful query string: /magmi/web/magmi.php?configstep=2&profile=%3C%2Fscript%3E%3Cscript%3Ealert%28document.domain%29%3C%2Fscript%3E
before?
It suddenly showed (once) and the only thing I can think of is the add-on SQLite Web, the I had installed for this migration.
it wasnt even running, it never is, I just left it in the system for you never know…
took it out now, but might be good to check if anything else needs to be done.
hey alex put it this way:
I have just done the migration, on my side from PostgreSQL, basically following the procedure from @koying described above. That said I migrated all the tables including the states and the events. To make it faster I did the migration on my laptop, as SQLite guarantees the binary compatibility of the databases between architectures. I noticed that SQLite seems to trigger a disk write for every INSERT clause, which makes the import quite slow. I therefore used eatmydata to avoid those writes and make the conversion way faster (around 2 minutes for a ~1GB SQLite database). Warning: don’t use this tool to run home assistant, there is a high risk of data corruption in case of power failure. Overall the Home Assistant downtime was less than 10 minutes.
Following the migration, I haven’t noticed any speed difference, but that was not my goal. I migrated to PostgreSQL many years ago to improve the performance, I am glad that nowadays we can get the same performance without additional complexity. The SQLite database uses roughly half of the disk space of the PostgreSQL one. In terms of I/O, the SD card reads have been divided by 3 (because the database is smaller and can now fit entirely in cache?), the SD card writes are identical, however the average write sizes have increased by 40%. Overall that is still low enough, I don’t think it will have a significant impact on the SD card lifetime.
So i have a decently big home assistant installation. 1 year ago i decided to move to mariadb cause i wanted to keep more than 30days of states and events data.
A couple of months ago i noticed home assistant restarting its own for no particoular reason. Logs were also even better (no entries). I was able to pull some log from supervisor which only showed. “Watchdog found a problem with Home Assistant API!” followed by “Restarting homeassistant”.
After some investigation I lurked in the DB and the states table had 88M entries (18Gig table). I purged that and was happy (no restarts) for a week.
when the states table reached 10M rows again I started having the random restarts once again.
For those of you reading this it’s also worth noting that home assistant runs on decent hardware. (4c8t amd ryzen7 2700u + 32G ram + 1TB nvme).
That said i decided to pull off from mariadb and switch back to sqlite. migration took 10m and i can tell that the energy data loads faster (for some reason) than previously on mariadb.
Obviously i’d need to wait at least 7-10 days to check performance wise AND “restart wise”. but at a first glance it seems that sqlite improved perf for my usecase.
I also considered running mariadb in an external container but in the end it would result in an extra thing to maintain.
The system was redesigned specifically targeting sqlite databases. Mariadb is slightly different and it is known that it will be slower than an sqlite database. If you can migrate, you should.
I’m not interested in preserving current contents of the Mariadb. Is this as simple as changing the Recorder db_URL target to sqlite:////PATH/TO/HOMEASSISTANT, restarting HA Core, and deleting the Mariadb add-on? If so, what is the default path?
Just fully remove the db_url: ... line and it will go to the default location, which is your config folder.
1 Like
Thank you petro.
1 Like