HOW-TO:Update Paths In MySQL
Share libraries w/MySQL, guide: |
MySQL | HOW-TO:Update Paths In MySQL |
Method 1
Easy method to catch ALL instances of the data to change
2014-05-23 Update! Much easier method to catch ALL instances of the data to change. Below is my example!
Note: Read below if you get lost or confused about what I'm talking about. I assume that you have read everything below this update and know what I'm talking about. I also only care about movies and tv shows for my setup.
In my example I have moved to a DFS share so I can just use my domain instead of an actual server name, I have the following...
- My Domain: galaxy.local
- Old Server: \\fn1\
- Old Movie Share: \\fn1\movies\
- Old TV Share: \\fn1\tv\
- New Server: \\ms\
- New Movie Share: \\galaxy.local\media\movies\
- New TV Share: \\galaxy.local\media\tv\
Walkthrough:
- Open HeidiSQL and connect to your mySQL server.
- Right click on your VIDEO database (ex: video75) then "Export database as SQL".
- Make sure you tick both "Create" checkboxes.
- Change the "Data" dropdown to "INSERT".
- Specify a filename (or choose one) in the "Filename" field.
- Click "Export".
- After closing the backup windows, press Shift+Ctrl+F (or at the top click "Search->Find text on server").
- Choose the correct database on the left (ex: video75) by ticking the checkbox.
- In the "Text to find" field type what you're looking to replace (ex: "fn1/"). Make sure you choose "All data types".
- Click "Find". If you did it right and there are in fact results then the "See results" button will be available.
- Click on the "See results" button.
- At this point it will show a tab for EVERY db table that has that in the database! Neat tool!
- At this point it just takes a little massaging but if you know what you're doing then each tab will give you a table name and each column will give you the field name to be edited. Just need to look for something that you know needs to be edited.
For example... The first thing I see was a tab for "art" so I know that's the "art" table. Then under the "art" tab I see the column "url" and I can see it says "smb://fn1/movies/47 Ronin (2013)/fan..." for the first entry. I know that needs to be changed to something along the lines of "smb://galaxy.local/media/movies/47 Ronin (2013)/fan..."
Now just start creating your new sql query... ex: UPDATE art SET url = replace(url,'smb://fn1/','smb://galaxy.local/media/');
Then just do that for every table you see in those results that need it. As per MyVideos116, the following tables need to be altered.
UPDATE art SET url = replace(strPath,'smb://fn1/','smb://galaxy.local/media/'); UPDATE episode SET c18 = replace(c18,'smb://fn1/','smb://galaxy.local/media/'); UPDATE movie SET c22 = replace(c22,'smb://fn1/','smb://galaxy.local/media/'); UPDATE path SET strPath = replace(strPath,'smb://fn1/','smb://galaxy.local/media/');
Method 2
Preamble
This guide is to help you move your media to a new location without losing all of your current watched status, date added, last played, resume time (for shows/movies that you started watching and did not finish), etc. when using a MySQL database. The goal is to be able to relocate your media without disrupting your current library or being forced to scrape all of the media over again.
The target audience here is the complete novice to all of this, so veteran users (particularly those well versed in SQL and SQL queries) will be able to cut out the majority of these instructions and simply follow the queries. With this in mind, I will be doing all of this using a program that will help make novice users more comfortable (hopefully) navigating this process.
These instructions are written from a Windows environment. They should be pretty easy to adapt, however I will be covering this using a Windows based MySQL editor so you will need to adapt for your environment.
When is this necessary or helpful?
- Your primary media hard drive is full and you need to migrate to a larger hard drive
- You are using external hard drives connected to your PC and need to move to a new PC
- You are restoring your media from a backup to a new location/machine
- You are replacing a hard drive that is dying but cannot or do not want to replicate the path exactly as before
- You want to clean up your file paths
- You want to move from local sources to network sources
There are other times when this is necessary, but these examples will give you an idea of the purpose of these instructions. To put it very simply, we are going to keep the same media but move it to a new file path.
Warning
Before continuing, it is very important that you backup your database before you even begin. It's also important that you backup your database when instructed throughout the guide to expedite recovery and resume the process with as few steps as possible if something goes wrong. It is very easy to completely wreck your library database during this process. Pay attention, follow instructions, and most importantly: BACKUP YOUR DATABASE!
Before We Begin
You will need to grab and install a copy of HeidiSQL or similar SQL editing program (note that instructions will be based on HeidiSQL).
Be sure that all of your XBMC clients using your MySQL database are shut down and will not be started again until we are completely finished.
Open HeidiSQL and configure a connection to your database. The only settings that need to be changed are the hostname, username, and password. For your username and password, you can use the same ones you use for XBMC.
Leave HeidiSQL open, we'll come back to it shortly.
Backup Database
This backup will be your control. This is the most important backup of all. If things go sideways, you can simply drop your database and import this backup in order to return to exactly where you are now.
There are several ways to backup your database, but in the interest of keeping this as efficient as possible for the complete novice, we're going to do it with HeidiSQL.
Connect to your database by clicking the Open button in HeidiSQL.
Double click on the "Unnamed" node to expand it.
Right click on the myvideos** database (note that if there are multiple myvideos** databases, we are going to be working with the highest numbered one).
Select "Export database as SQL".
In the popup window, select both "Create" tick boxes. In the "Data" dropdown, select INSERT.
Next to the "Filename" input box, click on the button with the little disk icon. Navigate to the location where you would like to save your backup. Name it Original and click "Save".
Next click on the "Export" button after being sure you have selected all of the options above.
When the export finishes, click the "Close" button.
Restore database notes
If during this process you manage to break something, or something doesn't work for some reason, you can simply import your backup. To import, we will first drop the modified database.
Right click on the myvideos** database that has been modified and select drop. Confirm the drop when prompted.
Next we will import the backup. On the tool bar near the top of the HeidiSQL window, click on the icon with the folder and little magnifying glass.
Navigate to and select your backup file and click "Open".
Depending on the size of your database, you will likely see a dialogue box popup asking if you want to run the file(s) directly or load them into the editor. Select file(s) directly. (If your database is very small, you will not see this. Disregard this step in that case).
The import will begin at this point. Depending on your system and the size of your database, this may take a few minutes. Your machine may appear to be locked up and HeidiSQL may say it is not responding. Just leave it alone and let it run. The larger your library, the longer this will take. Leave the program alone until the import completes. When it is finished, you will be able to click on the "Close" button.
Once the import is finished and you have clicked the "Close" button, you will need to refresh using the button on the toolbar with the 2 green arrows. Expand the "Unnamed" node once again and voila, your original database is back in place and just as it was before we began.
Modifying Sources
Now that we have safely backed up everything and we know how to restore, we are ready to start making our changes. First we want to change our sources in XBMC, so we will modify our sources.xml directly. Simple replace your old paths with your new ones in the sources.xml, then save your changes and close your editor. Be absolutely sure your paths are correct.
Once you have your paths properly configured, be sure you copy the new sources.xml to each of your XBMC clients that share your MySQL database.
Helpful tip
I copy/paste each of my source paths into a text file with one path per line. We are going to be changing our paths multiple times and it is much faster and easier to copy/paste than try to remember each path and potentially typo one or more of them.
Modifying MySQL Paths
This is where things will start getting complicated and attention to detail is absolutely crucial. Remember, you have a safety net in the form of your backup from earlier so if things go horribly wrong you can simply revert to the original database and start again.
We're going to be editing multiple tables so we're going to break it down to a table by table scenario to reduce the risk of mistakes and to be sure you change all of your paths.
First, select your myvideos** database in HeidiSQL.
Scroll down to the "path" table and double click it.
On the row of tabs just below the toolbar, select the data tab. Make note of the strPATH column. As you can see, the full path to every directory within your source is shown in this column. This is the first thing we need to edit.
To do so, select the tab with the blue arrow that says "Query".
In the query box you need to input the following:
update path set strPath = replace(strPath,'OLD/PATH/','NEW/PATH/');
We need to replace OLD/PATH/ and NEW/PATH with your paths.
Example
You stored all of your movies on one hard drive, and all of your tv shows on another hard drive. Now you are upgrading to bigger drives, but still keeping movies on one drive and tv shows on another. So we are going to change our source paths from:
smb://movie_nas/share/movies/ smb://tv_nas/tv/share/tv_shows/
to our new source paths of:
smb://bigger_movies/movies/ smb://bigger_tv/tv_shows/
The query to do this would be:
UPDATE path SET strPath = replace(strPath,'smb://movie_nas/share/','smb://bigger_movies/'); UPDATE path SET strPath = replace(strPath,'smb://tv_nas/tv/share/','smb://bigger_tv/');
Take notice that I only included the parts of the paths that I want changed. You can define how much of the path is changed by what you put inside the 'brackets'. The ONLY parts that will be changed will be the parts defined within the 'brackets' and all the rest will be ignored. Be very careful that you correctly change all of your paths, and watch out for double /'s at the end of your modified paths.
For each path that needs to be changed, you need to add one line, as above. After each path, press enter and add your next path. Continue in this fashion until you have ALL of your changed paths listed. Be sure your punctuation matches that in my examples, or else the queries will simply fail.
Executing the query
Now that you have crafted all of your queries, copy everything in the query window and save it to a text file for future reference. This will save you a lot of time crafting our next series of queries.
Once you've done that, we're ready to execute our queries. Understand that once we do this, you can't undo it without restoring your database backup from earlier-- so triple check that everything is correct before we execute.
To execute, press the button with the blue arrow on the toolbar above the tabs. If you do not have boxes pop up telling you there are errors, you've successfully executed your queries. Look in the log window near the bottom of the HeidiSQL window. If the last line says something like this:
/* 0 rows affected, 0 rows found. Duration for 1 query: 0.093 sec. */
Then you probably borked your old path. In short, that result means that there were no paths found matching your description. Double check your queries in the Query tab.
To check the result
Click on the Data tab again, and scroll down while watching the strPath column. You should see references to your new paths now, and not your old paths. Make sure you didn't forget to change any paths and make any corrections necessary at this point.
Backup again
If everything went smoothly, then backup your database again and name it Step1
Moving on to the next table
Next we need to select our Database: myvideos** tab again. This time scroll down to the movieview table and double click it.
Now select the Data tab again, and scroll to the right until you can see the c19 column.
As you can see, this column contains the path to every trailer you have in your library. We'll want to change those so your trailers show up properly.
Now scroll a little further to the right until you can see the c22 column.
As you can see here, this is yet another instance of paths to your root directory for each movie in your library. Obviously, we want to change those.
Once again, scroll to the right just a little more until you can see the strFileName column. Most of these will simply be the movie file names and extensions. However, you will also see all of your multi-part movies (i.e. diehard.cd1.avi, diehard.cd2.avi) with a "stack://" in front. We need to be sure that these stack paths are updated, or else the next time you run XBMC and update your library it will re-scrape all of those movies as new.
To change all of these, go to the Query tab again. Clear out your previous queries and replace them with these:
update movieview set strFileName = replace(strFileName,'OLD/PATH','NEW/PATH'); update movieview set c19 = replace(c19,'OLD/PATH','NEW/PATH'); update movieview set c22 = replace(c22,'OLD/PATH','NEW/PATH');
You need to have 1 of each of the above queries for each MOVIE source that you are changing. So if you are changing 2 movie sources, you would have the above code twice-- once for each path. Do not add anything for TV sources here.
Once you've crafted and triple checked your queries, go ahead and execute them just as we did for the path table.
Example
You had 2 hard drives with movies on them, and now you are upgrading to 1 much larger drive and combining all of your movies to this single drive. So we're going to change our paths from:
smb://movies1/share/movies/ smb://movies2/movies/share/
to
smb://big_movie_nas/movies/
The queries to accomplish this would be:
UPDATE movie SET c22 = replace(c22,'smb://movies1/share/','smb://big_movie_nas/'); UPDATE movie SET c22 = replace(c22,'smb://movies2/movies/share/','smb://big_movie_nas/movies/');
I threw a bit of a curveball in there so pay attention to what I did. This is to help you better understand the syntax necessary to change your paths however you may need to in your library.
Backup again
If all went smoothly, then backup again and name it Step2
One more table
Now we need to check/update one more table, and we're finished.
On the Database: myvideos** tab, double click on the movie table.
Go to the data tab and scroll right. Take note of columns c19 and c22. They should already reflect the changes made in the previous step, however we need to verify this. They should be the same as c19 and c22 in the movieview table.
If they are already correct, then move on. If they are not correct then go back to your query tab and simply edit our previous queries and reuse most of what we've already done.
First, delete the lines that have strFileName in them.
With the remaining lines, change all instances of movieview to movie. Now you're ready to execute your queries again.
Example
So for this table, we would simply take the queries from the previous example and change them to the following:
UPDATE movie SET c19 = replace(c19,'smb://movies1/share/','smb://big_movie_nas/'); UPDATE movie SET c19 = replace(c19,'smb://movies2/movies/share/','smb://big_movie_nas/movies/'); UPDATE movie SET c22 = replace(c22,'smb://movies1/share/','smb://big_movie_nas/'); UPDATE movie SET c22 = replace(c22,'smb://movies2/movies/share/','smb://big_movie_nas/movies/');
Testing
Assuming that everything went well, your library should now be fully functional with your new paths (except for thumbnails --see below). From within XBMC, you should not be able to tell anything even changed, and everything should appear exactly the same as before.
To test, open up a single instance of XBMC on only one of your machines. Go start a file of each type that you updated paths for and be sure they play properly. If that works like it should, the next test is to do a library update. If none of your existing media is added again, you've been successful.
If you want to go a step further, add some new content and be sure it shows up as expected. Make sure you have placed your new sources.xml in your userdata folder for each XBMC client before starting them up, or you are going to have problems.
Thumbnails
The last remaining obstacle is your thumbnail cache. You will need to disable your MySQL settings in one client (just temporarily remove the settings from your advancedsettings.xml). Then you need to delete your Thumbnail folders from each client. Now fire up XBMC on the client that has MySQL disabled and let it scrape all of your sources again. It will take quite some time, depending on the size of your library.
Once it's finished and you have all of your thumbnails back the way you like them, shut down XBMC. Copy your thumbnails folder to each of your XBMC clients, and restore your MySQL settings to all clients. Now fire up XBMC again, and everything should be back to normal.
Final notes
I did this sometime last winter or early spring while using Eden (or pre-Eden, I don't recall). At that time, it seems like I only had to execute the path table queries, and the other tables synced to that one the next time I fired up XBMC.
I recently went through this again about 3 weeks ago running pre-Frodo and had to manually perform all of the above steps in order to get everything working properly again.
Also, I did not go into any of the TV specific tables to change any paths. On my system, I never needed to. It simply worked. If you have problems with your TV shows on your system, you may need to go edit your paths in the tvshow and tvshowview tables.
Being that it's been a while since my last migration, I could be mistaken, or this could stem from changes to the DB in Frodo-- (or a bug that I am not aware of). That said, you could try simply using the first portion of the guide and just update the path table. Once that's done, backup your database and fire up XBMC. If everything works properly for you, then you've saved some time. If it doesn't (i.e. if it starts scraping existing content again) you can simply exit XBMC and drop the database, then import your backup and continue on with this guide.
It cannot be stressed enough-- backup often. If you backed up after each step as instructed, then you could revert to the previous step if you messed up the next step, which could save you a lot of time. If you messed up something major and you're not sure when or where, you have your original to fall back on and start over.
If you didn't backup as instructed, and you messed something up, then you have just landed yourself in a smelly creek with no paddle.
Backup backup backup.
Obviously, the major hassle with all of this is the thumbnails. I have not yet found a better way to deal with the artwork headache when migrating data to new locations. I do find the inconvenience of the artwork to be worth keeping everything else in tact though-- the idea of rebuilding my library from scratch is beyond appalling to me.
Alternative Command-Line (Linux) Option
Having just undergone this process in Frodo, I found it easier and more thorough to:
- Backup the MySQL database using mysqldump (i.e. create a SQL text file)
- Edit the text file and replace the paths using your favorite text editor. Save as a new file
- Drop all XBMC-related databases in MySQL
- Import edited SQL file into mySQL, recreating XBMC databases, tables, and data
You will still need to backup and change sources.xml to reflect your new paths (don't forget your profiles if you have any), but the database-specific portions can be accomplished from the command line using the steps below. As a precaution, I logged out of XBMC (Shutdown --> Exit) prior to issuing the commands over SSH.
Create Backup
Use mysqldump to create a backup of all databases for your xbmc user ("xbmc" in my example) as a SQL file ("xbmc-backup.sql" in my example) If you are paranoid, make a second copy of the file before continuing.
$ mysqldump --user xbmc -p --all-databases > xbmc-backup.sql
Edit SQL
Open the xbmc-backup.sql dump file with a text editor and make the path corrections using Find / Replace. Save the resulting file with a new name (i.e. xbmc-backup-new.sql). I noticed there are many places where paths are present in the database in a URL encoded format which might cause matching problems (i.e. "/" is stored as "%2F%), so be thorough in creating your search patterns.
Drop Databases
Use the following one-liner to obtain a list of the non-system databases (databases ending with "_schema" and the test database are excluded) that need dropping:
# Requires the gawk package $ mysql -u xbmc -p -e "show databases" | grep -v Database | grep -v mysql | grep -v _schema | grep -v test | gawk '{print "drop database " $1 ";"}' > drop-databases.sql
The same goal could be accomplished by logging into mysql and issuing the MySQL command:
mysql> SHOW DATABASES;
Once you have a list of MySQL databases, manually edit / review them to exclude any non-XBMC data. My XBMC databases were named for the profile (i.e. Kids) and content (i.e. Music) and suffixed with a random number (i.e. 75).
If you don't know what a particular database or table contains, use the following MySQL command to view the layout and help determine if it is XBMC-related:
/* List tables in the database */ mysql> SHOW TABLES IN KidsMusic75; /* Switch to the database */ mysql> USE KidsMusic75; /* View the columns in the 'path' table */ mysql> DESCRIBE path;
Once you have obtained a list of tables needing to be dropped, issue each of the commands separately into MySQL. Mine looked like this:
/* Example commands */ mysql> DROP DATABASE Adultsmusic75; mysql> DROP DATABASE Adultsvideo32; mysql> DROP DATABASE Kidsmusic75; mysql> DROP DATABASE Kidsvideo32;
Import Data
Once the old databases have been cleared out, import them again using the new SQL file containing the updated paths:
$ mysql -u xbmc -p < xbmc-backup-new.sql
The above command will import the SQL in the xbmc-backup-new.sql file under the XBMC user. If you created the backup as per the above instructions, the same databases will be recreated for you automatically and all the data re-populated. Log back in to your XBMC system to verify everything went ok.