Just to convert from sqlite to mysql

[vzoltan]

Hi there,

could you please provide a script that just converts home-assistant_v2.db to a proper mysql.sql file to be able to use my homeassistant with mysql?

my installation has no supervisor, i cannot instann MariaDB addon etc however i have mysql on my server and i could connect home assistant to it.

id like to keep all my data from the original db in mysql

its already 9.5 gb, and i dont need lots of data in it, and in mysql i could delete what i dont need anymore. Bulit in pruge in home assistan when i try to run (from the interface) it always ends up with corrupted db file :/

i tried anyway and i got this error :/

Do you want to create the database dump now? [y/N] y

• Create SQLite database dump.
  • Dumping schema... done.
  • Dumping table 'event_data'... done (29445 lines written).
  • Dumping table 'event_types'... done (35 lines written).
  • Dumping table 'state_attributes'... done (12363526 lines written).
  • Dumping table 'states_meta'... done (158 lines written).
  • Dumping table 'statistics_meta'... done (127 lines written).
  • Dumping table 'recorder_runs'... done (297 lines written).
  • Dumping table 'schema_changes'... done (9 lines written).
  • Dumping table 'statistics_runs'... done (56555 lines written).
  • Dumping table 'events'... done (409475 lines written).
  • Dumping table 'states'... Runtime error near line 3: database disk image is malformed (11)

zoltan@ubuntu:~/HA$ sqlite3 home-assistant_v2.db "PRAGMA integrity_check;"

in database main Tree 32 page 2234816 cell 25: invalid page number 89298300 Multiple uses for byte 3600 of page 2234816 Multiple uses for byte 3588 of page 823108 Multiple uses for byte 3828 of page 818957 database disk image is malformed

(i have no idea how to fix this issue and keep my data and then migrate it to mysql)

Thank you

btw my home-assistant_v2.db grows 2-5mb /hrs (i guess becaous of 5-7 smat plug that monitors the consumption and stores them for energy dashboard?) and my recorder is configured properly but purge is removed from it, becaouse it always get is corrupted)

[NicolasGoeddel]

To dump the content of your SQLite database you must stop home assistant completely. SQLite is not meant to be used from more than one application. Did you do that?

If your SQLite database really is corrupt, you might try to recover it like this:

sqlite3 home-assistant_v2.db ".recover" | sqlite3 home-assistant_v2.recovered.db

See also here: https://www.sqlite.org/recovery.html

I can also create a stand-alone script for exporting and migrating the SQLite database to a MySQL/MariaDB compatible dump. I don't have time for that today, maybe tomorrow.

[vzoltan]

ofc HA was stopped, and the db file was copied to a different location and then i run you r script (configured to use the new location and the db file)

result is above.

recover option doesnt work unfortunatelly:

zoltan@ubuntu:~/HA$ sqlite3 home-assistant_v2.db ".recover" | sqlite3 home-assistant_v2.recovered.db sql error: no such table: sqlite_dbpage (1)

[vzoltan]

so i found this site and used 3 command from here: https://support.storj.io/hc/en-us/articles/360029309111-How-to-fix-a-database-disk-image-is-malformed

sqlite3 -bail home-assistant_v2.db ".dump" > dump.sql

{ echo "PRAGMA synchronous = OFF ;"; cat dump.sql; } | grep -v -e TRANSACTION -e ROLLBACK -e COMMIT > dump_all_notrans.sql

sqlite3 new_home-assistant_v2.db ".read dump_all_notrans.sql"

after the above your script does this on the new db file : (on this machine there is no docker nor home assistant nor any msql, here just testiong your script for conversion, on my server i have docker and homeassisntant (core) and mysql)

zoltan@ubuntu:~/HA$ bash migrate.sh 
migrate.sh: sor: 57: docker: parancs nem található
Do you want to create the database dump now? [y/N] y
 * Create SQLite database dump.
   - Dumping schema... done.
   - Dumping table 'event_data'... done (29445 lines written).
   - Dumping table 'event_types'... done (35 lines written).
   - Dumping table 'state_attributes'... done (12363526 lines written).
   - Dumping table 'states_meta'... done (158 lines written).
   - Dumping table 'statistics_meta'... done (127 lines written).
   - Dumping table 'recorder_runs'... done (297 lines written).
   - Dumping table 'schema_changes'... done (9 lines written).
   - Dumping table 'statistics_runs'... done (56555 lines written).
   - Dumping table 'events'... done (409475 lines written).
   - Dumping table 'states'... done (3506064 lines written).
   - Dumping table 'statistics'... done (958961 lines written).
   - Dumping table 'statistics_short_term'... done (5428766 lines written).
   - Dumping table 'migration_changes'... done (7 lines written).
 * Dir listing of the dump:
     -rw-r--r-- 1 zoltan zoltan 5,8G szept 15 18:52 /home/zoltan/HA/new_home-assistant_v2.db
     -rw-rw-r-- 1 zoltan zoltan 8,8M szept 15 18:58 ./sqlite/data_event_data.sql
     -rw-rw-r-- 1 zoltan zoltan  63M szept 15 18:58 ./sqlite/data_events.sql
     -rw-rw-r-- 1 zoltan zoltan 2,1K szept 15 18:58 ./sqlite/data_event_types.sql
     -rw-rw-r-- 1 zoltan zoltan  428 szept 15 18:59 ./sqlite/data_migration_changes.sql
     -rw-rw-r-- 1 zoltan zoltan  37K szept 15 18:58 ./sqlite/data_recorder_runs.sql
     -rw-rw-r-- 1 zoltan zoltan  575 szept 15 18:58 ./sqlite/data_schema_changes.sql
     -rw-rw-r-- 1 zoltan zoltan 4,5G szept 15 18:58 ./sqlite/data_state_attributes.sql
     -rw-rw-r-- 1 zoltan zoltan  12K szept 15 18:58 ./sqlite/data_states_meta.sql
     -rw-rw-r-- 1 zoltan zoltan 669M szept 15 18:59 ./sqlite/data_states.sql
     -rw-rw-r-- 1 zoltan zoltan  14K szept 15 18:58 ./sqlite/data_statistics_meta.sql
     -rw-rw-r-- 1 zoltan zoltan 4,0M szept 15 18:58 ./sqlite/data_statistics_runs.sql
     -rw-rw-r-- 1 zoltan zoltan 839M szept 15 18:59 ./sqlite/data_statistics_short_term.sql
     -rw-rw-r-- 1 zoltan zoltan 137M szept 15 18:59 ./sqlite/data_statistics.sql
     -rw-rw-r-- 1 zoltan zoltan 4,7K szept 15 18:58 ./sqlite/schema.sql
Convert SQLite schema to MySQL? [y/N] y
 * Remove old dumps if existent... done.
 * Convert schema... done.
 * Symbolic link data
   - mysql/data_event_data.sql -> ../sqlite/data_event_data.sql
   - mysql/data_events.sql -> ../sqlite/data_events.sql
   - mysql/data_event_types.sql -> ../sqlite/data_event_types.sql
   - mysql/data_migration_changes.sql -> ../sqlite/data_migration_changes.sql
   - mysql/data_recorder_runs.sql -> ../sqlite/data_recorder_runs.sql
   - mysql/data_schema_changes.sql -> ../sqlite/data_schema_changes.sql
   - mysql/data_state_attributes.sql -> ../sqlite/data_state_attributes.sql
   - mysql/data_states_meta.sql -> ../sqlite/data_states_meta.sql
   - mysql/data_states.sql -> ../sqlite/data_states.sql
   - mysql/data_statistics_meta.sql -> ../sqlite/data_statistics_meta.sql
   - mysql/data_statistics_runs.sql -> ../sqlite/data_statistics_runs.sql
   - mysql/data_statistics_short_term.sql -> ../sqlite/data_statistics_short_term.sql
   - mysql/data_statistics.sql -> ../sqlite/data_statistics.sql
Delete existing data and import schema? [y/N] y
migrate.sh: sor: 108: docker: parancs nem található
Set and update AUTO_INCREMENT values? [y/N] y
migrate.sh: sor: 108: docker: parancs nem található

that gave me a non coccupted sqlite home assistand db file, but half the size, and lost my sensor.* data before september...

on this new db file your script works. and exports the sql files

but first i guess, i need to i need a solution to fix the orignial db and not to lose my sensor.* data

zoltan@ubuntu:~/HA$ sqlite3 home-assistant_v2.db "PRAGMA integrity_check;"

in database main Tree 32 page 2234816 cell 25: invalid page number 89298300 Multiple uses for byte 3600 of page 2234816 Multiple uses for byte 3588 of page 823108 Multiple uses for byte 3828 of page 818957 database disk image is malformed

dont know hot to fix this :/

[vzoltan]

is there any way to find these in database main Tree 32 page 2234816 cell 25: invalid page number 89298300 Multiple uses for byte 3600 of page 2234816 Multiple uses for byte 3588 of page 823108 Multiple uses for byte 3828 of page 818957 database disk image is malformed

and just remove from the db with DB Browser for SQLite ? https://sqlitebrowser.org/

[NicolasGoeddel]

I have not a lot of experience with SQLite besides basic usage. I never had the error you encountered. I don't think I can help you with the malformed disk image expect that I can search the internet for that error what you already did. Do you have any backups of the database by any chance?

[vzoltan]

My home assistant works with this corrugated db file I can even list any previous data in the history page and the logbook

My problem is that i cannot clean the db, purge/remove what i dont need anymore, Also automatic purge cannot be applied via configuration.yml nor from the developer tools / action / recorder purge When i try any of the above, db gets corrupted and ha creates a new empty db and im not able to access all the history, i have to stop ha and copy back the. Corrupted db file as home-assistant_v2.db and restart homeassistant.

So, my db just keeps growing bc of this error

[vzoltan]

Any idea what does this wecond command do?

sqlite3 -bail home-assistant_v2.db ".dump" > dump.sql

{ echo "PRAGMA synchronous = OFF ;"; cat dump.sql; } | grep -v -e TRANSACTION -e ROLLBACK -e COMMIT > dump_all_notrans.sql

sqlite3 new_home-assistant_v2.db ".read dump_all_notrans.sql"

Bc after these, the db is not corrupted anymore But also doesn't contain all the previous data as before.

[NicolasGoeddel]

I really can not help you any further with this problem. Please try to find a solution using other ways like Reddit, Stackoverflow or similar communities.

[vzoltan]

Ok, thank you though. Btw, would you pls make the standalone mysql converter script? thanks.

[NicolasGoeddel]

I added a standalone script for converting a SQLite schema to MariaDB: sqlite2mariadb.sh

Read also the the new entry of the README before trying it out because it needs a certain input format to work properly: https://github.com/NicolasGoeddel/ha-sqlite2mariadb?tab=readme-ov-file#standalone-scripts

[vzoltan]

Thank you

[vzoltan]

im not sure its doing anything,

sqlite3 -readonly home-assistant_v2.db > ".schema --indent" > sqlite_db_schema.sql

and just hangs there

creates two files, but thats all:

-rw-rw-r-- 1 zoltan zoltan 81 szept 19 14:56 sqlite_db_schema.sql -rw-rw-r-- 1 zoltan zoltan 0 szept 19 14:56 '.schema --indent'

sql has this: SQLite version 3.46.1 2024-08-13 09:16:08 Enter ".help" for usage hints. sqlite>

the other is empty.

maybe i didnt understand the description.

[vzoltan]

btw i managed to repair the sqlite home-assistant_v2.db file.