From the addon it seems started…
Looking at logs I see this message
ERROR: Got unexpected response from the API: There is already a MySQL service in use from core_mariadb
I installed it twice?
can’ tell what is going on, but you might want to ask this question in another group that deals specifically with the mariadb add-on here , or in the home assistant discord section for add ons. This topic is related to just migrating the db.
dunno what is wrong as the error i responded to says the db isn’t running.
good luck.
Uninstalled the mariadb addon… reinstalled and database seems to work (didn’t get what was the problem!)
I stopped the recorder, but I got
“attempt to write a readonly database”
when running the migration command
For some reason the phpMyAdmin add-on was not start for me, but the below equivalent CLI within the mysql session:
ALTER TABLE states MODIFY last_updated_ts double;
ALTER TABLE states MODIFY last_changed_ts double;
was working.
Hi,
I’ve now migrated my database twice from sqlite to MariaDB, both times not succesful.
First time was with method from post #97.
Second time was with method from post #123
Migrations complete without any errors reported. After completion, I restart HA core. No errors in the logs.
recorder:
db_url: "mysql://hass:[email protected]/hass?charset=utf8mb4"
All my historic data is “rolled up” like below.
This is what it looks like with sqlite.
New states are also not recorded or displayed in live data.
Would appreciate any assistance.
@rynoster
I solved this Issue, but it was a little bit complicated.
At my side the Problem was, that at the migration some Colums are created with the wrong Type.
For examle “last_update_ts” is created at the mirgation with TYPE float, but need double.
So what I did to solve this Issue was:
- Activated the MaraDB Recorder just for a few Seconds.
- Exportet ONLY the Structure
- Drop the homeassistant database
- create a new homeassistant database
- Import the Structure to the new Database
- created the missing colums wich will be normaly created from the SQLite Migration (f.e. created colum and some more
- Deleted all Index_key
- Deleted All Foreign Keys
- Exportet the Structure Again (just to be save)
- Migrate the actuall home_assistant2.db
- Activated the Recorder Again.
That solves My Issue with the Values.
Explanation:
SQLite Migrate the last_update_ts Values without the Point, So for Example in the SQLite is a Values “123456.7890”
The Migration is only “123456” because of the Type FLOAT
So that Home Assistant can not view the Data correct and shows this Square History.
Maybe the Post from @boti above yours Helps also, I did not saw that to try. But it seems that it is the Same Problem.
1 Like
Same issue here. The tables created by a fresh depolyment of MariaDB are little bit different from the one imported by the conversion from sqlite to mariadb. Probably something is changed in the SQlite structure breaking the previous import method. Have you solved in some way?
HI, at point 6, how to create the missing column? I’m using phpMyadmin
Thanks
In phpmyadmin, open the Table and Choose Colums, here you can click “New”. See SCreenshot. Sorry My SystemLanguage is German.
Then in the Right Side you can Add Columns, just Type in the Name “created” for Example, choose the Type and IMPORTANT: Choose at Standart → NULL, so that also the Hook at “Null” is ON.
For created is the Righttype “Datetime”.
@SirACEair Thank you so much for the detailed steps! I will attempt this again over the weekend and provide feedback.
On steps 7 and 8, do I need to re-add the index keys and foreign keys later, or will this be done during step 10 (migration).
Then for step 10, to confirm, I use the sqlite3-to-mysql script?
Solved! renaming the value in the column _ts to “double” instead of float. Thanks !
The process used is the following. After a first import (failed) I saved the DB structure (only). After that I dropped the DB, modified the saved structure via txt editor replacing float with double, recreated the empty db, reimported the modified structure and reimported date from sqlite db. The reimported data, found the structure ready with “double” type instead of “float”, so avoiding discarding decimal values and solving the issue.
When creating a new table with HA from scratch, the format is indeed double. If you want it to be precise to the us it would have to be DECIMAL(16,6) which avoids some rounding, etc.
It’s unclear why the “last_updated” and “last_changed” columns still exist - they are NULL in all of my entries… .
Hello everyone, I successfully migrated from SQLite to MariaDB. I also use InfluxDB and Grafana. Do I need to update any configuration information in InfluxDB to connect to the MariaDB?
I successfully migrated from SQlite to MariaDB. I also use InfluxDB and Grafana. Do I need to update any configuration information in InfluxDB to connect to the MariaDB?
The issue seems to be caused by the pypi sqlite-to-mysql tool. It does not recreate the new tables with the correct data types needed for mysql.
I created a new empty database in the mariadb addon, and then set HA to that and restarted, just to get the correct expected table schema from HA. Then I altered all the incorrect table schemas and switched HA back to my database, and that resolved the issues. There were other columns as well that were different: datetime → datetime(6), int(11) → smallint(6), text → longtext, _ts columns from float → double, and changing table collation to utf8mb4_unicode_ci
Full diff of the schema as created by pypi’s sqlite-to-mysql compared to the schema as created by Home Assistant directly on an empty db: schema.diff · GitHub
How can I use this? Do I need to dump sqlite to .sql file and then patch it somehow?
It’s only for information. I used it to make changes to the tables to bring them back up to match HA.
The specific problems in logbook timestamps is the float->double change in the comments I replied to. The fix is to modify the column data type.
The diff shows you the table and column, what the PyPI script migrated its data type to, and what HA wants its data type to be.
Hi,
Why doesn’t home assistant provide a normal migration to MariaDB? I don’t want to loose my data (a few years of statistics) and for what I read, it can be pretty risky migrating ourselves. There are so many good developers in the HA community.
Regards,
Frank