Sorry for the late reply.
I’m glad i could help, for me everything still working on 3 servers.
About the CREATED row is only needed to be inserted if its missing in the new created database.
Just compare the old and new and its ok, it doesn’t have to be in every table 
I recommand all people who have very less knowledge about the migration to MARIADB, just don’t do it!.
See what the developers have to say at the 2023.4 release party.
Here is the video at the correct timestamp
I have captured the above information regarding the recommendations to not use mysql / mariadb unless you KNOW what you are doing as a database administrator to the beginning post for this topic.
anybody moved back to sqlite after 2023.4 improvements?
wondering the same if anyone switched back to SQLite.
Is it worth it if we have a good working setup with mariadb right now?
I don’t need external access to the database… I just want the best performance.
Yes I switched back and succeeded. If it’s worth, is up to you.
The HA developers recommend using SQLite as the DB and made some really spectacular improvements on the performance.
See my ‘manual’ (no guarantee):
I switched back to SQlite as well:
I my experience the new 2023.4 SQLite is working really great, very snappy and light weighted. I realize this is not a scientifically approach to comparing DB’s.
All together a great revert. Moving on…
Great update HA Team.
Hello,
are there any news or update about this topic ?
I tried to migrate (SQLite → MariaDB) following these notes but with core 2023.5 it seems that doesn’t work.
I’m going crazy because it would be very useful for me to have one steady database shared between a couple of homeassistant nodes (a sort of cluster, let’s say, only one node working at time, the second node only for redundancy/manteinance).
Thank you so much, appreciate the help!
Had to delete the database and igoner duplicate keys to get my energy data to transfer
I’m having issues on converting to Mariadb. I have an empty database ‘homeassisant’.
When I try to run sqlite3mysql:
Command:
sqlite3mysql --sqlite-file /config/home-assistant_v2.db --mysql-database homeassistant --mysql-host 192.168.1.x --mysql-user peter --mysql-password 'SECRET' --debug
Response:
Traceback (most recent call last):
File “/usr/bin/sqlite3mysql”, line 8, in
sys.exit(cli())
File “/usr/lib/python3.10/site-packages/click/core.py”, line 1130, in call
return self.main(*args, **kwargs)
File “/usr/lib/python3.10/site-packages/click/core.py”, line 1055, in main
rv = self.invoke(ctx)
File “/usr/lib/python3.10/site-packages/click/core.py”, line 1404, in invoke
return ctx.invoke(self.callback, **ctx.params)
File “/usr/lib/python3.10/site-packages/click/core.py”, line 760, in invoke
return __callback(*args, **kwargs)
File “/usr/lib/python3.10/site-packages/sqlite3_to_mysql/cli.py”, line 204, in cli
converter.transfer()
File “/usr/lib/python3.10/site-packages/sqlite3_to_mysql/transporter.py”, line 736, in transfer
self._create_table(table[“name”], transfer_rowid=transfer_rowid)
File “/usr/lib/python3.10/site-packages/sqlite3_to_mysql/transporter.py”, line 360, in _create_table
column_type = self._translate_type_from_sqlite_to_mysql(column[“type”])
File “/usr/lib/python3.10/site-packages/sqlite3_to_mysql/transporter.py”, line 287, in _translate_type_from_sqlite_to_mysql
raise ValueError(“Invalid column_type!”)
ValueError: Invalid column_type!
The connection is being made successfully, as I’ve deleted the homassistant database manually and it recreated it with sqlite3mysql.
Any idea on how to fight this?
I just want to add that I used dbeaver to migrate data without any problem (homeassistant in docker, mariadb installed locally):
Some data types may be different in MariaDB than in SQLite (and it may cause corruption of some states/other data), have you checked this?
I haven’t done rigorous check, just checked if “updates” from firsts posts were necessary and they weren’t.
I remember some of my graphs were seriously messed up after clean import, but I haven’t noticed that immediately.
I have trouble with connecting to the MariaDB that the port number is not an integer
I did already some checks in the transporter.py from sqlite3mysql and the port number shows with type as <class ‘int’>
I am running phyton 3.10 on Ubuntu 22.04
Found the solution to solve all dependences, on the site source location from the tool GitHub - techouse/sqlite3-to-mysql: Transfer data from SQLite to MySQL there is the solution to use a docker container which contain the whole tooling.
you may need to add in --break-system-packages when runinng “pip3 install sqlite3-to-mysql” command (i had to)
I know this is an old thread. But I did find it useful! I used the method that used the ‘sqlite3-to-mysql’ script and it did work; but my long term statistics are not currently found. I’ve been searching this thread quite a lot and reviewing various methods but I’ve not yet found a solution.
If you are not an admin for mysql, the recommendation is to not migrate to mysql, and instead remain with sqlite. There is no benefit . I have not migrated back to sqlite from mysql, only because I am ok with it at this time. There may come a time when I migrate back to sqlite. If you still want to try to migrate to mysql, you will be on your own. I have not tried using history, and there are several comments in this thread indicating troubles using history after migration. with no real solutions as you have discovered.
You may want to start a new topic specifically for your request as this thread is pretty much dead.
Regards.
As far as I remember the values were stored as a different type, after correcting that it all worked. The information on how to do it (which was working at the time people were trying it) is all in this thread.
