Help requested for migration to mariadb

Third party integrations
1

I’m running HA in a docker (not hass.io).

Been following this guy’s guide (since I can’t find any other more up to date):

When I try to load the converted mysql sql file, I get the following error:

ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘SQLite format 3’ at line 1

Now, this database is 1.2GB big, so it’s a pain to open and edit on any computer, any ideas what might have gone wrong?

Thanks

2

Why go through the hassle, just start fresh with mariadb. I only keep a few days to keep the database to a minimum and its still 500mb.

3

I keep 2w worth of data for selected sensors (whitelist in recorder) which is about 1.2GB. At first I just wanted to see if I could migrate the data, didn’t seem such a hassle.

But indeed, I didn’t want to waste time on it so I just went along with a fresh MariaDB database. Still, I wonder what the issue is, I’m sure other people have done it before without any problems.

4

Well a hint would be that you have a statement of “sql lite” in your database import you want to use from mariadb. If you make a dump of sqllite into sql statements, you cannot have the sql lite part in it at the start. I presume if you removed that, it would have worked. (just out of my head)

maybe i should have mentioned that as first post, but it was late :stuck_out_tongue:

5

I expected the conversion script to do that I guess, opening up a plain text file of 1.2GB takes forever (even on a desktop PC) hence why I didn’t bother to spend time on it. OK I could have grepped and changed it like that now that I think of it, but who knows what else would have been incorrect :wink:

6

Your better of starting fresh, you never know what legacy you will drag on for years :slight_smile:

7

True that, next up is also pushing to InfluxDB and whipping up some Continuous Queries to downsample the data :slight_smile:

I wonder if anyone made a tutorial about that yet. :thinking:

8

I gave up on all the influx / grafana / database / query whatever stuff… i have to much devices and it grinds most to a halt. Other then that, if you really think about it, when do i need to see graphs? Usually i only need that for the day or the day before and from HA itself it works fine. A good automation doesn’t need a GUI, it runs the house itself.