[wil.fortune]

I have a script that has served me well when I needed to move files from one location to another. Recently, I started tweaking it and forgot to rename the two variables that make up the `SET [strpath|c22|etc] = REPLACE([strpath|c22|etc], @old_val, @new_val)`. Can you guess what happened about five minutes ago when I ran the script again? Yeah, I'm an idiot. Bye, bye entire database.

Obviously I was a little too flippant (even if MySQL Workbench does catch/prevent most mistakes), and the last backup I created of the database was months and months ago. I've moved so many things around since then, it would be next to useless to try and use it as a restore option.

I am virtually certain that each of the connected clients has a copy of the DB, because after failing to connect to the DB, I've noticed they still show all the same content from before their last shutdown.

I would be importing from LibreELEC-based clients. Can anyone offer me any pointers or should I just start poking around on one of the clients? I imagine that I'll probably have to work with some form of XML-SQL conversion software (i.e. I am pretty sure I won't be lucky enough to find .sql files on the clients, more likely .xml). Trying to keep calm here and not screw up any more than I already have (opened my one Windows client, "just to see", and now that client's DB is surely just as trashed), so any help at all would be greatly appreciated.

I could always wipe the DB and start over, but I'm praying I won't have to do that. Took a tremendous amount of time the last time I had to do that.

[Klojum]

Is this all on a remote MySQL server, or are you working on local SQlite databases
As you mention MySQL Workbench in one moment, and you mention database copies on connected clients in another moment.
so I'm a little confused.

Right now in Kodi 17 a number of table fields contents need editing if you want to change the location of your media server(s).

sql:
# EXAMPLE:

UPDATE art SET url = replace(url,'smb://192.168.0.1/',' nfs://192.168.2.2/');

UPDATE path SET strPath = replace(strPath,'smb://192.168.0.1/',' nfs://192.168.2.2/');

UPDATE movie SET c22 = replace(c22,'smb://192.168.0.1/',' nfs://192.168.2.2/');

UPDATE tvshow set c16 = replace(c16,'smb://192.168.0.1/',' nfs://192.168.2.2/');

UPDATE episode SET c18 = replace(c18,'smb://192.168.0.1/',' nfs://192.168.2.2/');

For backups, do a Video Library Export every now and then. You can re-scrape your collection quickly and without most internet interference via the .nfo files and stored fanart.
For hacking into a database, do a sqldump before you start. You now know why.

[Karellen]

Nothing that will help you in this instance, but for future... The importance of the Export facility in Kodi Import-export_library (wiki)

But if you already have those, then just re-scan.

[wil.fortune]

Is this all on a remote MySQL server, or are you working on local SQlite databases
As you mention MySQL Workbench in one moment, and you mention database copies on connected clients in another moment.
so I'm a little confused.

I was assuming that each of the clients (connected to the same local network as MySQL server) stored a copy of the video database, and then updated it as necessary (i.e. updating/cleaning the library, renaming a title, etc). The MySQL server is installed on a Windows machine that I modify using MySQL Workbench.

Pretty bummed right now. I'm SSH'ed into the client I updated last night, and I can't find a full copy of "myvideos107" anywhere. There's a .DB file, but that appears to be leftover from the first run of the device.

Do clients download a full copy of the DB at start, and then cache it in memory? I could've sworn that I was able to view my existing library a couple of times when the DB server was down, but I'm not able to replicate that as of now. Must've been that the internet was down, but the Server was still up..

Man is this ever going to hurt. I got so used to making little changes here and there, which was a big timesaver over using the Kodi UI + rescraping, that I absolutely took it for granted. I can't even find the dumps I had from before.

If I were to rescan the whole library on one client, so that when finished it resembled its former self, is there a chance I won't have to redownload all of the cover art on each of the other clients? Kind of like "nothing happened" sort of a deal, as far as any client not "aware" of the "new" database?

[wil.fortune]

Nothing that will help you in this instance, but for future... The importance of the Export facility in Kodi Import-export_library (wiki)

But if you already have those, then just re-scan.

 Oh if only I were so wise...

[Klojum]

Pretty bummed right now. I'm SSH'ed into the client I updated last night, and I can't find a full copy of "myvideos107" anywhere. There's a .DB file, but that appears to be leftover from the first run of the device.

Do clients download a full copy of the DB at start, and then cache it in memory? I could've sworn that I was able to view my existing library a couple of times when the DB server was down, but I'm not able to replicate that as of now. Must've been that the internet was down, but the Server was still up..

The answer is simple... No, there is no cached "copy" of the video database on each Kodi client. It is the idea of a single centralized metadata database, that no clients need to have their own copy. The whole Kodi database setup is a bit unorthodox though. Some data can be centralized (video and music metadata), while other data is only stored locally on each Kodi client (tracking thumbnail storage).

The upside of this whole situation? You will never make this mistake again.

[wil.fortune]

Yeah, I didn't think there was much hope. And then I go the "bright idea" that a copy of the db could exist on each of the clients.

I understand though. It wouldn't make much sense to have to track every change to the DB in order to sync a connected-client's copy, or worse, compare each entry and then have to decide which is the authoritative version. Or whatever. Still pretty green when it comes to DBs.

Thank you for your responses. Re-scan is now complete. Would it be exceedingly foolish to try and write something that would attempt to copy things like the Watched Status from the "old" DB over to the "new"?

[Karellen]

Would it be exceedingly foolish to try and write something that would attempt to copy things like the Watched Status from the "old" DB over to the "new"?

No it wouldn't be foolish, as long as you have the knowledge and skill with SQL. Remember to backup the DB before you run the script.

The Import-Export function also handles Watched states, Resume Points and Play Counts. This is the main reason I run my Export at least every fortnight.

[wil.fortune]

Would it be exceedingly foolish to try and write something that would attempt to copy things like the Watched Status from the "old" DB over to the "new"?

No it wouldn't be foolish, as long as you have the knowledge and skill with SQL. Remember to backup the DB before you run the script.

The Import-Export function also handles Watched states, Resume Points and Play Counts. This is the main reason I run my Export at least every fortnight.  

 I'd certainly feel pretty accomplished if I figured it out

Just so that I understand you correctly, you are saying you run an Export via Kodi on a semi-regular basis, manually?

Would you happen to know if dumping a database is more reliable when output into a .SQL file or to folder/individual files? It looks like there may be a way to schedule DB backups (possibly via the paid MySQL Enterprise Backup). Regardless, I am getting in the habit of exporting the DB regularly (to both folder/files and .sql format, because I don't know what I don't know ).