Upgrades and mysql
#1
Okay, preparing for the shift to Gotham some day in the nearing future...

I currently have half-a-dozen clients - Windows, Android, 'buntu Linux and OpenElec Linux. I share most things via a centralised mysql database on my server/NAS. All is good.

Now, I understand entirely that moving to a new version will generate new database tables - hence the need to keep versions in sync.

Just testing my understanding, though...

1. Presumably, the first Gotham upgraded client would generate new tables/schemas (e.g. MyVideos78) and leave the old ones untouched (e.g. MyVideos75)

2. All Gotham clients (subject to them using the precise table names - XBMC_databases/Version_table (wiki)) would then use that new table

3. Any existing Frodo clients would continue to use the previous tables

4. Both sets of tables could be updated independently, but there'd be no sharing between them for obvious reasons

5. Once all clients are on the same later table version, you can clear out the old versions as nothing's using them any more

... yes? It's that last step that I'm thinking about... otherwise, over time, you'd end up with stale dbs and data that's simply taking up space for the sheer fun of it.

(UPnP sharing doesn't work for me as none of the XBMC instances is constantly running).

Thanks....
Reply
#2
Just went through this same scenario myself. 2 Ubuntu, 1 CrystalBuntu and 5 android. I updated the primary HTPC first, then went on to each client from there. Exactly as you've thought, the prmary updated the video & music db's to the latest and all other clients used the new ones. From there, once I ensured they were all ok, I went in and deleted the old db's. Very easy.
Reply
#3
Thanks for that. Given that my SQL is somewhat rusty (last used c. 1993ish, I think...), I'm guessing that it's really just a straightforward "drop" of the old databases when you're done, though I've got some homework to do before then.

If I connect with mysql -u root -p and show databases, I can see:

Code:
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| MyMusic32          |
| MyMusic43          |
| MyVideos75         |
| mysql              |
| performance_schema |
| test               |
+--------------------+
7 rows in set (0.03 sec)

... with MyMusic32 and MyVideos75 being what I'd expect for 12.3. I've no idea where MyMusic43 has come from (perhaps one of the Android builds I played with) , but that's likely to be a casualty when I'm clearing out.
Reply
#4
Thanks for this thread...

Just one or two aditional questions :-)
1. What about data migration? I mean once I will upgrade one XBMC from Frodo to Gotham will this also move all data from the old databases to new one automaticly? Or there will be any manual export/import needed?
2. I supose that there will be still one step needed between the software update and the first run of it: Changing advancedsettings.xml to navigate XBMC to MySQL database... Or will be upgrade able to copy all my advanced settings to the new one?
Reply
#5
I find the best and safe way to upgrade is to:

1- Do a Library export from frodo using separate Folder, export Thumbs = yes and actors = yes if you need. If you have music...do the same.

2- Install Gotham in a new folder so you can have both Frodo and Gotham installed.

3- Create a shorcut to the xbmc.exe and add the /p parameter to allow xbmc to start in portable mode. This will avoid the overwritting of your current frodo userdata folder because xbmc will create a new userdata folder in the installation folder.. (if your paranoid backup userdata prior to starting)

4- Close xbmc after the first launch and copy your advancedsettings.xml, Sources.xml, favorites.xml(if needed) and all smartplaylist (if needed) over to the gotham userdata

5- Start the xbmc Gotham and go into files and set the content of your sources and let it scan everything in.

6- Once its done (which should be fast since its using your local nfo and image files), check if everything is to your liking. If it is, you can take the content of the userdata gotham and copy it all to a new userdata folder located in the XBMC default userdata location (rename the other to Frodo_userdata to not get confused). Start xbmc without the /p parameter and it should work.

***Make sure you have importwatched setup in your advancedsettings.xml (wiki) before starting scan so you have your watched flags****

This method avoids any tampering with your frodo installation(and MYSQL DB) till your 100% good with your gotham install and enables you to easily move back and forth between versions. If you want, you can also port your addon and addon_data folder to your gotham userdata if you dont want to reinstall all your addons but from personal experience i always find its better to start fresh to avoid any errors duty to migration.
Image

If my replies help you, please click on my reputation Image below :) thanks :)
Reply
#6
(2014-02-17, 17:49)LAS0 Wrote: 1. What about data migration? I mean once I will upgrade one XBMC from Frodo to Gotham will this also move all data from the old databases to new one automaticly? Or there will be any manual export/import needed?

My understanding is that XBMC will migrate the data to the new database. That's really what I'm hoping to establish. I expect a new db to be created and populated, leaving the old one behind for previous versions to use until you want to delete it - no manual export/import required.

(2014-02-17, 17:49)LAS0 Wrote: 2. I supose that there will be still one step needed between the software update and the first run of it: Changing advancedsettings.xml to navigate XBMC to MySQL database... Or will be upgrade able to copy all my advanced settings to the new one?

If you're already using mysql, and the database is to be created on the same server - no, probably not. Again, I'm guessing until I try it, but I'm expecting the Gotham installation to pick up the advancedsettings.xml, see that you've got an old db version already, create the new one (because user xbmc has full permissions, as was needed when the original db was created) and then simply migrate the contents. You'd only have issues if you've named the database in advancedsettings rather than using the defaults.

(2014-02-17, 18:56)saitoh183 Wrote: I find the best and safe way to upgrade is to: ....

Thanks for that... let me pick it apart, I need to put my Windows eyes on first, though!

At the end of the day, I export everything to separate files once in a while, so I always have a database backup. I might just give it a go, and if it all dies... well, it's simply a matter of restoring sources.xml and importing everything. Your comment about importwatched is worth further investigation, though...
Reply
#7
(2014-02-17, 20:32)Prof Yaffle Wrote:
(2014-02-17, 18:56)saitoh183 Wrote: I find the best and safe way to upgrade is to: ....

Thanks for that... let me pick it apart, I need to put my Windows eyes on first, though!

At the end of the day, I export everything to separate files once in a while, so I always have a database backup. I might just give it a go, and if it all dies... well, it's simply a matter of restoring sources.xml and importing everything. Your comment about importwatched is worth further investigation, though...

in wiki advancedsettings.xml (wiki) section 3.3.16 videolibrary.

Code:
<importwatchedstate>false</importwatchedstate>  <!-- import previously exported playdate and playcount from .nfo files. Defaults to false -->

and if you had resume points that you dont want to loose

Code:
<importresumepoint>false</importresumepoint>  <!-- import previously exported resume point from .nfo files. Defaults to false -->
Image

If my replies help you, please click on my reputation Image below :) thanks :)
Reply
#8
OK... I will keep eye on this thread.

I am just preparing my mediafiles for the first "import" to proper libraries. It is not so simple (especially with Audio) so I would be sure that once I will finish building my libraries I will not lost anything during further upgrades ;-)
(Yes - back-up is the must...)
Reply
#9
(2014-02-17, 20:32)Prof Yaffle Wrote: My understanding is that XBMC will migrate the data to the new database. That's really what I'm hoping to establish. I expect a new db to be created and populated, leaving the old one behind for previous versions to use until you want to delete it - no manual export/import required.
That is correct. You don't need to do any any manual export/import.

(2014-02-17, 20:32)Prof Yaffle Wrote: If you're already using mysql, and the database is to be created on the same server - no, probably not. Again, I'm guessing until I try it, but I'm expecting the Gotham installation to pick up the advancedsettings.xml, see that you've got an old db version already, create the new one (because user xbmc has full permissions, as was needed when the original db was created) and then simply migrate the contents. You'd only have issues if you've named the database in advancedsettings rather than using the defaults...
Correct - The advancedsettings.xml doesn't need to be touched in any way if you're already using MySQL. Also as Saitoh has said, you should add the <importwatchedstate> and <importresumepoint> flags into your advancedsettings.xml before doing the update.
Reply
#10
(2014-02-18, 16:24)Aenima99x Wrote:
(2014-02-17, 20:32)Prof Yaffle Wrote: My understanding is that XBMC will migrate the data to the new database. That's really what I'm hoping to establish. I expect a new db to be created and populated, leaving the old one behind for previous versions to use until you want to delete it - no manual export/import required.
That is correct. You don't need to do any any manual export/import.

Yes you can do it this way as well but the downside is that if something goes wrong you loose everything. You wont have a your frodo Userdata anymore...again you could backup your entire userdata before proceeding. My method insures that you dont touch your current installation and can always easily revert back if there is a problem. I have try the standard migration method of installing over and simply letting XBMC migrate the data when i went from Eden to Frodo and overall it went well but in the end i had to start fresh because of random small issues arising due to the migration.
Image

If my replies help you, please click on my reputation Image below :) thanks :)
Reply
#11
Time for an update on this, as I've just taken the plunge and upgraded my master system to Gotham - the other six clients are this afternoon's work :-)

A straight Ubuntu upgrade in situ worked fine for the Video dbs. I cleaned Video and Music libraries first, then exported to single files as a backup, then added the unstable PPA and installed... a restart of XBMC, a pause while things were migrated, and all seemed well.

However, the Music database would not come across: it was blank. I deleted and re-added the source; I re-scanned the media; I deleted, cleaned and re-scanned. Nothing.

In the end, I dropped the music databases and let XBMC try again... here's the command history, although you'll have to imagine the XBMC restarts that happened in parallel:

Code:
ian@Server:/etc/mysql$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 27040
Server version: 5.5.35-0ubuntu0.12.04.2 (Ubuntu)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| MyMusic32          |
| MyMusic43          |
| MyMusic46          |
| MyVideos75         |
| MyVideos78         |
| mysql              |
| performance_schema |
| test               |
+--------------------+
9 rows in set (0.04 sec)

mysql> GRANT ALL ON *.* TO 'xbmc';
Query OK, 0 rows affected (0.17 sec)

mysql> DROP DATABASE MyMusic43;
Query OK, 18 rows affected (1.26 sec)

mysql> DROP DATABASE MyMusic46;
Query OK, 15 rows affected (0.73 sec)

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| MyMusic32          |
| MyVideos75         |
| MyVideos78         |
| mysql              |
| performance_schema |
| test               |
+--------------------+
7 rows in set (0.00 sec)

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| MyMusic32          |
| MyMusic46          |
| MyVideos75         |
| MyVideos78         |
| mysql              |
| performance_schema |
| test               |
+--------------------+
8 rows in set (0.00 sec)

mysql>

After the restart, I could see the dbs, but I still couldn't apparently populate them: a full re-scan and nothing showed up on XBMC. However, another restart and all my music swam back into life, fanart, thumbnails and all.

One of those times that I wish I could now recreate it with debugging enabled, but that's easier said than done - drop all the new tables, downgrade, upgrade again, etc. So I leave this here as a pointer if anyone else gets issues - there seems to be a previous problem with upgraidng the MyMusic database, with a fix in the pipeline:

http://forum.xbmc.org/showthread.php?tid=184435

Now, on to the other systems, see what life holds.
Reply
#12
Okay, Win8 upgraded with no further issues, so I expect the other clients to be the same.

Re: the db versions, I thought I'd try to recreate the problem with debugging anyway - so I dropped the new tables and restarted B2 - but it upgraded with no problem at all, re-creating MyVideos78 and MyMusic46 without complaint. So I'll have to consider that a quirk unless someone else gets it.
Reply
#13
(2014-02-17, 18:56)saitoh183 Wrote: I find the best and safe way to upgrade is to:

1- Do a Library export from frodo using separate Folder, export Thumbs = yes and actors = yes if you need. If you have music...do the same.

2- Install Gotham in a new folder so you can have both Frodo and Gotham installed.

3- Create a shorcut to the xbmc.exe and add the /p parameter to allow xbmc to start in portable mode. This will avoid the overwritting of your current frodo userdata folder because xbmc will create a new userdata folder in the installation folder.. (if your paranoid backup userdata prior to starting)

4- Close xbmc after the first launch and copy your advancedsettings.xml, Sources.xml, favorites.xml(if needed) and all smartplaylist (if needed) over to the gotham userdata

5- Start the xbmc Gotham and go into files and set the content of your sources and let it scan everything in.

6- Once its done (which should be fast since its using your local nfo and image files), check if everything is to your liking. If it is, you can take the content of the userdata gotham and copy it all to a new userdata folder located in the XBMC default userdata location (rename the other to Frodo_userdata to not get confused). Start xbmc without the /p parameter and it should work.

***Make sure you have importwatched setup in your advancedsettings.xml (wiki) before starting scan so you have your watched flags****

This method avoids any tampering with your frodo installation(and MYSQL DB) till your 100% good with your gotham install and enables you to easily move back and forth between versions. If you want, you can also port your addon and addon_data folder to your gotham userdata if you dont want to reinstall all your addons but from personal experience i always find its better to start fresh to avoid any errors duty to migration.

Hi

Thanks for sharing your update instructions, I was planning on following this method to update my current Frodo / MYSQL installation to the final release version of Gotham. All my HTPCs are OpenElec, however I planned to put the first Gotham instance on my Windows 7 laptop so I could run it in portable mode etc and get everything correct in Gotham on there before I then updated the OpenElec machines.

I have just ran the Music and Video Library export routines in Frodo, however I am having some issues with the resulting artwork images that do or don't get created please see here is this normal? I posted some screen shots on that thread so you can see what I mean.

Many thanks
Reply

Logout Mark Read Team Forum Stats Members Help
Upgrades and mysql0