No. HA expects the default database in the default setup. Migration is needed.
Of course. For the SQLite (default database) you can simply use the File Size - Home Assistant integration. Just create a sensor and use it in an automation to notify yourself.
1 Like
Worked a treat. Thank you!
With the new updates in HAOS is there a good reason to change to mariadb or are they now nearly “the same”?
2 Likes
Since converting to MariaDB last week I notice that the times in my log are not correct. Regularly events get reported in the future and a lot of events happen at the same time. I can’t see anything in the log files. Any idea what the problem may be?
[screenshot at 19:17…]
Wrong time settings?
Check How to set time?
Otherwise:
Then, check whether ntpd-service is running and what time it is.
Tx. Will give that a try.
I had the exact same issue, wierd timestamps in the future for all events, a lot at the same time. Time settings for Home Assistant were just fine.
I managed to fix this by changing the column type for last_changed_ts and last_updated_ts in the states table from float to double.
2 Likes
That seems to have fixed it. Thanks!
Thanks, this seemes to have fixed my issue as well. But ist this an issue froim migrating or a current issue with mariadb?
Tried to migrate using the script, but I got the error:
ERROR 2003: Can't connect to MySQL server on '%-.100s:%u' (%s) (Warning: %u format: a number is required, not str) 2003: Can't connect to MySQL server on '%-.100s:%u' (%s) (Warning: %u format: a number is required, not str)
Looking at the docker container it seems to exists with that name.
I tried adding domain → core-mariadb.local.hass.io and I got
ERROR 2003: Can't connect to MySQL server on 'core-mariadb.local.hass.io:3306' (111 Connection refused) 2003: Can't connect to MySQL server on 'core-mariadb.local.hass.io:3306' (111 Connection refused)
What am I doing wrong?
ERROR 2003: Can't connect to MySQL server on 'core-mariadb.local.hass.io:3306' (111 Connection refused) 2003: Can't connect to MySQL server on 'core-mariadb.local.hass.io:3306' (111 Connection refused)
the error above indicates the mariadb server is not running. you need to start it.
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