[Arctic_Wolf]

(Mod edit: originally posted in Tricks & Tips, but moved here as general support.)

---

@Prof Yaffle , thanks at least for posting something up about this process long ago. Do you happen to have a more up to date procedure for migrating a remote MySQL database when updating from one Kodi version to the next please?  (I.e. in this instance I'm thinking ahead for the change from Kodi 18 to 19 in the not too distant future.)

I'm running a MySQL (MariaDB 10) server just for Kodi alone on a Syno NAS which a number of individual client devices with varying OS's all have access to for the centralised library/DB.

I use PHPMyAdmin alone to admin the DB which I obviously managed to use to initially create my MySQL server, although, I'm ultimately an utter dummy with how it all actually works after that initial configuration had been carried out. I also haven't used Linux/command line for (minimum) 15 years, so would prefer to do it with what I already have available to me if it's possible.  I haven't got a clue where to start how to update & from the research I have carried out so far I really don't want tens of thousands of .NFO files everywhere creating endless clutter.  My current setup is very lean & clean indeed, obviously it would be preferable to remain that way but I'm getting worried about the general process involved here owing to the size of my media library.  I can't find anything to explain how to achieve all of this in simpler terms for a complete halfwit unfamiliar with the inner workings of MySQL or PHPMyAdmin .

Any additional advise you're able to give or tutorials you could point me in the direction of (for dummies) please ?  Or, know anyone else who has successfully updated with a similar setup to mine too whose brain I could pick?

Any info much appreciated

[Prof Yaffle]

@Arctic_Wolf You may well be in for a pleasant surprise. I've moved XBMC/Kodi version very many times, and the upgrade process brings the database with it. Off the top of my head, the new application version starts, creates the new remote tables, ports/reformats the data and populates it all into the new tables - leaving the old ones untouched. So long as the mysql/MariaDB user has the right permissions, all you see is a pause, and then life carries on as normal.

The gotcha is if you have different clients of different versions. In this instance, your old clients will see the old db tables, and the new ones will see... well, the new ones. In practical terms, it simply means your clients are now out of sync, in that new additions, watched statuses, watch counts and similar are now diverging because the two Kodi versions are reading from and writing to different tables. Once all clients catch up to the same version, you can safely drop the old tables if you wish, as they're now unused.

As always, though - if it's precious, back it up before any significant change.

[Arctic_Wolf]

@Arctic_Wolf You may well be in for a pleasant surprise. I've moved XBMC/Kodi version very many times, and the upgrade process brings the database with it. Off the top of my head, the new application version starts, creates the new remote tables, ports/reformats the data and populates it all into the new tables - leaving the old ones untouched. So long as the mysql/MariaDB user has the right permissions, all you see is a pause, and then life carries on as normal.

The gotcha is if you have different clients of different versions. In this instance, your old clients will see the old db tables, and the new ones will see... well, the new ones. In practical terms, it simply means your clients are now out of sync, in that new additions, watched statuses, watch counts and similar are now diverging because the two Kodi versions are reading from and writing to different tables. Once all clients catch up to the same version, you can safely drop the old tables if you wish, as they're now unused.

As always, though - if it's precious, back it up before any significant change.

Many thanks indeed for this additional info & for the clarification .

I've just had a complete double take moment on this, so just to make sure I'm definitely understanding you correctly here if you wouldn't mind please lol....
 
1) I just leave my current Kodi 18 MySQL library in place as is for the time being, no fuss or messing?
2) Install Kodi 19 (when applicable) onto just one client device initially & drop the same advancedsettings & sources .XML files I always use into the new Kodi 19 client userdata directory as usual?
3) Run the new Kodi 19 client, & it will then automatically port & build entirely new DB tables respectively from the old Kodi 18 DB tables, also automatically placing them under the existing MySQL user/root for Kodi 18 in the process?
4) If successful & all being well, then update all other client devices to Kodi 19 after that obviously adding the advancedsettings & sources .XML files to each one as I go & that's it, job done ?
5) Once double checked & happy that the new DB tables are all seemingly properly populated & functioning well, & the new Kodi 19 clients are correctly seeing the freshly created libraries, simply drop the old DB tables when happy/ready that they're no longer required?

Am I getting that right & is it really this simple to do now, (or am I missing something) ?

(Geees, I've been trawling around online for some time looking for an explanation & all I could find were a group of older conversations that were considerably more involved.  (No recent tutorials/guides I could find explaining the possibility of how simple this process could now be . Hope others find your response too as I'm sure they could find it very useful as well.).  I was absolutely dreading the prospect of either potentially starting an entire new library from scratch which would have been a nightmare as my library isn't exactly small, messing the whole process up trying to somehow export/import the library, & I especially don't want to have countless thousands of the .NFO files everywhere with my media too lol .)

"Pleasantly surprised" is an understatement but I'll refrain from using any expletives to emphasise my absolute sheer joy at this news providing I am in fact understanding you correctly here !!?!?!?!!

Info greatly appreciated, & thanks for updating Kodi itself to make this whole process so much easier now as well.  I can definitely see another well warranted donation to Kodi coming up again in the very near future for me too .

[Prof Yaffle]

You are entirely correct in your understanding. Kodi handles the DB upgrade, whether that's the local SQLite database or a remote MySQL/MariaDB - it basically squirts in the relevant SQL code to create a new database, create the tables with the right format, and then re-arrange, extend, or otherwise manipulate the existing data before populating the new tables. Most people wouldn't ever worry about dropping the old database and tables, but a good clean-up would do just that when they're no longer required.

As part of the upgrade, you simply get a new database with a new version number as per the wiki: Databases#Database_Versions (wiki) - that's why, in my original example, you've got multiple MyVideosXX and MyMusicXX databases, as Frodo to Gotham to Helix created the new ones. Indeed, if I check now, you can see that history continues to be written as I've gone on all the way to Leia on this setup (my Matrix installation is standalone, so it doesn't break production systems):

Code:

$ date
Wed  2 Sep 14:20:54 BST 2020

$ mysql -u root -p
Enter password:

<snippy mcsnipface>

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| MyMusic56          |
| MyMusic60          |
| MyMusic72          |
| MyVideos107        |
| MyVideos116        |
| MyVideos99         |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
10 rows in set (0.01 sec)

It's important to get your advancedsettings.xml in place, as you say, as that's what points the new Kodi installation to the database - likewise, sources.xml if you need this client to be able to scan in new media (you could probably skip it if you only ever update from one client, but it's probably wise to keep it all in sync). The only reason to even really worry about this How-to is if something's changing in those paths - if you want to tidy up paths, move media around, rename your NAS or give it a new IP address, that sort of thing - or, as in my original case, to move the database to a completely different system. Otherwise, it should all be pretty automated.


And contributions are always gratefully received, so thank you

(I'll leave these posts here for a bit, but will probably move them out of Tips & Tricks and into their own support thread in due course, so be aware of that in case they seemingly vanish. You can always look at your profile to find your own threads/posts if you find you've mislaid them).

[Arctic_Wolf]

You are entirely correct in your understanding. Kodi handles the DB upgrade, whether that's the local SQLite database or a remote MySQL/MariaDB - it basically squirts in the relevant SQL code to create a new database, create the tables with the right format, and then re-arrange, extend, or otherwise manipulate the existing data before populating the new tables. Most people wouldn't ever worry about dropping the old database and tables, but a good clean-up would do just that when they're no longer required.

As part of the upgrade, you simply get a new database with a new version number as per the wiki: Databases#Database_Versions (wiki) - that's why, in my original example, you've got multiple MyVideosXX and MyMusicXX databases, as Frodo to Gotham to Helix created the new ones. Indeed, if I check now, you can see that history continues to be written as I've gone on all the way to Leia on this setup (my Matrix installation is standalone, so it doesn't break production systems):

Code:

$ date
Wed  2 Sep 14:20:54 BST 2020

$ mysql -u root -p
Enter password:

<snippy mcsnipface>

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| MyMusic56          |
| MyMusic60          |
| MyMusic72          |
| MyVideos107        |
| MyVideos116        |
| MyVideos99         |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
10 rows in set (0.01 sec)

It's important to get your advancedsettings.xml in place, as you say, as that's what points the new Kodi installation to the database - likewise, sources.xml if you need this client to be able to scan in new media (you could probably skip it if you only ever update from one client, but it's probably wise to keep it all in sync). The only reason to even really worry about this How-to is if something's changing in those paths - if you want to tidy up paths, move media around, rename your NAS or give it a new IP address, that sort of thing - or, as in my original case, to move the database to a completely different system. Otherwise, it should all be pretty automated.


And contributions are always gratefully received, so thank you

(I'll leave these posts here for a bit, but will probably move them out of Tips & Tricks and into their own support thread in due course, so be aware of that in case they seemingly vanish. You can always look at your profile to find your own threads/posts if you find you've mislaid them).

Awesome, thanks again .

[Klojum]

@Arctic_Wolf

Please use the quote function sensibly. Quoting an entire large post and respond with a single line of text is a bit over the top.

Perhaps you are not aware yet of the other, very useful quote option:
- Select a text part of the post you want to react to. A "Reply" option at the bottom left of that post will appear.
- Click on the Reply bar, and the selected text will be copy/pasted in the reply section.

You'll be able to pinpoint your replies much easier with much less quoted text.