MySQL/Upgrading: Difference between revisions

From Official Kodi Wiki
Jump to navigation Jump to search
m (Ned Scott moved page MySQL/Upgrading XBMC to MySQL/Upgrading)
No edit summary
Line 1: Line 1:
{{:MySQL/Contents}}
{{:MySQL/Contents}}
{{mininav| [[MySQL]] }}
{{mininav| [[MySQL]] }}
When updating to a new version, XBMC will make a copy of the current DB and upgrade the copy. That way if anything goes wrong in the upgrading you still have an original untouched copy of your DB.
When updating to a new version, {{kodi}} will make a copy of the current DB and upgrade the copy. That way if anything goes wrong in the upgrading you still have an original untouched copy of your DB.




Starting in XBMC v12 "Frodo", you no longer need to use [[path substitution]] to sync thumbnails and other art between XBMC devices. Using path subs is still supported, which is useful for XBMC devices with small amounts of memory, but is discouraged for sharing thumbnails between multiple clients.
Starting in {{kodi}} v12 "Frodo", you no longer need to use [[path substitution]] to sync thumbnails and other art between {{kodi}} devices. Using path subs is still supported, which is useful for {{kodi}} devices with small amounts of memory, but is discouraged for sharing thumbnails between multiple clients.




{{big|{{note|Updating your library may take a few minutes, so be sure to give XBMC time to do its thing. Stopping midway will mess up the updated DB. }} }}
{{big|{{note|Updating your library may take a few minutes, so be sure to give {{kodi}} time to do its thing. Stopping midway will mess up the updated DB. }} }}




Line 13: Line 13:
{{big|'''BACKUP YOUR DATABASE JUST IN CASE.'''}}
{{big|'''BACKUP YOUR DATABASE JUST IN CASE.'''}}


Even though XBMC makes a copy of your DB and updates that, it is still highly recommended to [[Import - Export Video Library#Import .2F Export options|export]] your library from your current version before beginning the upgrade process. You don't want to find yourself starting your library from scratch, and it's better to be safe than sorry.
Even though {{kodi}} makes a copy of your DB and updates that, it is still highly recommended to [[Import - Export Video Library#Import .2F Export options|export]] your library from your current version before beginning the upgrade process. You don't want to find yourself starting your library from scratch, and it's better to be safe than sorry.




Line 21: Line 21:
To backup simply [[export]] the library to a single file using {{highlight|bordered=yes|[[Settings/Videos#Library|Settings -> Videos -> Library]] -> Export library}} and select "multiple files". "Single file" also works, but "multiple files" is recommended as it is the most fail-safe method, especially for saving/resorting images. For more information see '''[[HOW-TO:Backup the library]]'''.
To backup simply [[export]] the library to a single file using {{highlight|bordered=yes|[[Settings/Videos#Library|Settings -> Videos -> Library]] -> Export library}} and select "multiple files". "Single file" also works, but "multiple files" is recommended as it is the most fail-safe method, especially for saving/resorting images. For more information see '''[[HOW-TO:Backup the library]]'''.


==Making sure XBMC can update the library==
==Making sure {{kodi}} can update the library==
'''Note:''' If you originally set up MySQL using this XBMC Wiki guide then you should not have to worry about this section. It never hurts to double check the settings, though.
'''Note:''' If you originally set up MySQL using this {{kodi}} Wiki guide then you should not have to worry about this section. It never hurts to double check the settings, though.




For XBMC to update the library from a previous version, you may need to make changes to your MySQL server. The user made for the MySQL DB ("xbmc" in most guides) will need access to create new databases. If the user already has that access then you don't need to do anything. If not, you will have to use the following command on your MySQL server:
For {{kodi}} to update the library from a previous version, you may need to make changes to your MySQL server. The user made for the MySQL DB ("xbmc" in most guides) will need access to create new databases. If the user already has that access then you don't need to do anything. If not, you will have to use the following command on your MySQL server:


:<code>GRANT ALL ON *.* TO 'xbmc';</code>
:<code>GRANT ALL ON *.* TO 'xbmc';</code>




If you are worried about security then you can limit XBMC's access in MySQL once it has created the new video DB. However, you will need to re-grant these privileges the next time you update XBMC. If the only thing you use MySQL for is XBMC then you don't really need to worry about security.
If you are worried about security then you can limit XBMC's access in MySQL once it has created the new video DB. However, you will need to re-grant these privileges the next time you update XBMC. If the only thing you use MySQL for is {{kodi}} then you don't really need to worry about security.


==Thumbnails==
==Thumbnails==
{{note|This only applies for migrating from using [[pathsubs]] for thumbnails to using the normal local image cache (faster). Most users can ignore this section.}}  
{{note|This only applies for migrating from using [[pathsubs]] for thumbnails to using the normal local image cache (faster). Most users can ignore this section.}}  
{{hidden|Show instructions -->|2=
{{hidden|Show instructions -->|2=
XBMC v12 and later no longer needs path substitution for syncing thumbnails.
{{kodi}} v12 and later no longer needs path substitution for syncing thumbnails.




Line 42: Line 42:


;If you used path subs for the entire Thumbnails folder - ''Extra safe method''<ref name="extra safe method">The extra safe part is because we copy the thumbnails artwork manually to each {{name}} device, instead of having {{name}} just download the art from the internet. If you have library items that scanned their art in a long time ago then the internet copy might not exist anymore, hence this extra safe method.</ref>
;If you used path subs for the entire Thumbnails folder - ''Extra safe method''<ref name="extra safe method">The extra safe part is because we copy the thumbnails artwork manually to each {{name}} device, instead of having {{name}} just download the art from the internet. If you have library items that scanned their art in a long time ago then the internet copy might not exist anymore, hence this extra safe method.</ref>
# Quit all XBMC devices.
# Quit all {{kodi}} devices.
# Update all instances to XBMC v12 "Frodo", but don't open/launch XBMC yet.
# Update all instances to {{kodi}} v12 "Frodo", but don't open/launch {{kodi}} yet.
# Choose one XBMC device and open XBMC
# Choose one {{kodi}} device and open XBMC
# Say yes when it asks to update art.
# Say yes when it asks to update art.
# Copy your path-substituted thumbnails folder onto all XBMC devices, replacing the local thumbnails folder in the [[userdata]] folder.
# Copy your path-substituted thumbnails folder onto all {{kodi}} devices, replacing the local thumbnails folder in the [[userdata]] folder.
# On the first XBMC device go into {{highlight|userdata/database/}} and copy the "Textures13.db" file to all the other XBMC devices in their {{highlight|userdata/database/}} folders.
# On the first {{kodi}} device go into {{highlight|userdata/database/}} and copy the "Textures13.db" file to all the other {{kodi}} devices in their {{highlight|userdata/database/}} folders.
# Remove path substitution for thumbnails from the [[advancedsettings.xml]] file of each XBMC device.
# Remove path substitution for thumbnails from the [[advancedsettings.xml]] file of each {{kodi}} device.
# You can now open/launch XBMC on your other devices.
# You can now open/launch {{kodi}} on your other devices.
# From that point on, XBMC will sync art/thumbs directly to each XBMC box, but will keep them in sync (whereas before XBMC would only add art on one box, and boxes could have different artwork if set manually). Having the thumbnails folder locally on each box will also speed up loading the library, and what art to use is stored in the videos DB that MySQL is already sharing.
# From that point on, {{kodi}} will sync art/thumbs directly to each {{kodi}} box, but will keep them in sync (whereas before {{kodi}} would only add art on one box, and boxes could have different artwork if set manually). Having the thumbnails folder locally on each box will also speed up loading the library, and what art to use is stored in the videos DB that MySQL is already sharing.




;If you used path subs for only userdata/thumbnails/videos/ and userdata/thumbnails/music/ - ''Extra safe method''<ref name="extra safe method" />
;If you used path subs for only userdata/thumbnails/videos/ and userdata/thumbnails/music/ - ''Extra safe method''<ref name="extra safe method" />
# Quit all XBMC devices.
# Quit all {{kodi}} devices.
# Choose one XBMC device to do the initial thumbnails update.
# Choose one {{kodi}} device to do the initial thumbnails update.
# Copy the path-substituted thumbnails folder(s) for {{highlight|userdata/thumbnails/videos/}} and/or {{highlight|userdata/thumbnails/music/}} to the local {{highlight|[[userdata]]/thumbnails/}} folder on your first XBMC device.
# Copy the path-substituted thumbnails folder(s) for {{highlight|userdata/thumbnails/videos/}} and/or {{highlight|userdata/thumbnails/music/}} to the local {{highlight|[[userdata]]/thumbnails/}} folder on your first {{kodi}} device.
#:''If your thumbnails folders are being shared from another XBMC device's thumbnails folder then you don't need to do this. Just make that XBMC device your "first device" for these instructions.''
#:''If your thumbnails folders are being shared from another {{kodi}} device's thumbnails folder then you don't need to do this. Just make that {{kodi}} device your "first device" for these instructions.''
# Update all instances to XBMC v12 "Frodo", but don't open/launch XBMC yet.
# Update all instances to {{kodi}} v12 "Frodo", but don't open/launch {{kodi}} yet.
# Open XBMC on your first device.
# Open {{kodi}} on your first device.
# Say yes when it asks to update art.
# Say yes when it asks to update art.
# Copy your the thumbnails folder from the first XBMC device onto all the other XBMC devices.
# Copy your the thumbnails folder from the first {{kodi}} device onto all the other {{kodi}} devices.
# On the first XBMC device go into {{highlight|userdata/database/}} and copy the "Textures13.db" file to all the other XBMC devices in their {{highlight|userdata/database/}} folders.
# On the first {{kodi}} device go into {{highlight|userdata/database/}} and copy the "Textures13.db" file to all the other {{kodi}} devices in their {{highlight|userdata/database/}} folders.
# Remove path substitution for thumbnails from the [[advancedsettings.xml]] file of each XBMC device.
# Remove path substitution for thumbnails from the [[advancedsettings.xml]] file of each {{kodi}} device.
# You can now open/launch XBMC on your other devices.
# You can now open/launch {{kodi}} on your other devices.
# From that point on, XBMC will sync art/thumbs directly to each XBMC box, but will keep them in sync (whereas before XBMC would only add art on one box, and boxes could have different artwork if set manually). Having the thumbnails folder locally on each box will also speed up loading the library, and what art to use is stored in the videos DB that MySQL is already sharing.
# From that point on, {{kodi}} will sync art/thumbs directly to each {{kodi}} box, but will keep them in sync (whereas before {{kodi}} would only add art on one box, and boxes could have different artwork if set manually). Having the thumbnails folder locally on each box will also speed up loading the library, and what art to use is stored in the videos DB that MySQL is already sharing.
}}
}}


Line 72: Line 72:
;Method 1
;Method 1


# Downgrade XBMC on one of your XBMC devices to the previous version of XBMC (you only need to do this to one device). [[Export]] the library to multiple files using {{highlight|bordered=yes|[[Settings/Videos#Library|Settings -> Videos -> Library]] -> Export library}} and select "multiple files".
# Downgrade {{kodi}} on one of your {{kodi}} devices to the previous version of {{kodi}} (you only need to do this to one device). [[Export]] the library to multiple files using {{highlight|bordered=yes|[[Settings/Videos#Library|Settings -> Videos -> Library]] -> Export library}} and select "multiple files".
#:'''''Note:''' If you backed up your XBMC database, you can use that backup in place of this first step.''
#:'''''Note:''' If you backed up your {{kodi}} database, you can use that backup in place of this first step.''
# Quit XBMC
# Quit XBMC
# Place {{highlight|<nowiki><importwatchedstate>true</importwatchedstate></nowiki>}} in your [[advancedsettings.xml]] file on the downgraded XBMC device.
# Place {{highlight|<nowiki><importwatchedstate>true</importwatchedstate></nowiki>}} in your [[advancedsettings.xml]] file on the downgraded {{kodi}} device.
# If you used the <name> tag in your [[advancedsettings.xml]] file, remove these lines. XBMC will simply use default database names instead (MyVideos and MyMusic). Sometimes using the <name> tag will cause issues when updating the MySQL libraries, but the tag is not needed as of XBMC v11 and later.
# If you used the <name> tag in your [[advancedsettings.xml]] file, remove these lines. {{kodi}} will simply use default database names instead (MyVideos and MyMusic). Sometimes using the <name> tag will cause issues when updating the MySQL libraries, but the tag is not needed as of {{kodi}} v11 and later.
# Use the MySQL configuration utility to drop/delete the existing MySQL copies of the XBMC database(s).  
# Use the MySQL configuration utility to drop/delete the existing MySQL copies of the {{kodi}} database(s).  
## When using phpMyAdmin: select ''Databases'' option from the top of the screen, put a checkbox in front of all databases called '''MyMusic''' or '''MyVideos''' followed by some digits, and select ''Drop'' underneath the list. Confirm that you want to drop the databases.
## When using phpMyAdmin: select ''Databases'' option from the top of the screen, put a checkbox in front of all databases called '''MyMusic''' or '''MyVideos''' followed by some digits, and select ''Drop'' underneath the list. Confirm that you want to drop the databases.
## When using the command line tools: enter <code>show databases;</code> including the semicolon. Type <code>drop database <name>;</code> for every name in the resulting list starting with '''MyMusic''' or '''MyVideos''' and followed by some digits, for example: <code>drop database MyVideos78; drop database MyMusic46;</code> Note that actual names may vary per XBMC version, and do not forget the trailing semicolon.
## When using the command line tools: enter <code>show databases;</code> including the semicolon. Type <code>drop database <name>;</code> for every name in the resulting list starting with '''MyMusic''' or '''MyVideos''' and followed by some digits, for example: <code>drop database MyVideos78; drop database MyMusic46;</code> Note that actual names may vary per {{kodi}} version, and do not forget the trailing semicolon.
# You can now re-update the device you downgraded in step 1.
# You can now re-update the device you downgraded in step 1.
# Import the library by simply scanning it in as a new file source. XBMC will pick up the exported data as it scans the files in.
# Import the library by simply scanning it in as a new file source. {{kodi}} will pick up the exported data as it scans the files in.
# XBMC will now rebuild a fresh MySQL library DB, but using all your existing information.
# {{kodi}} will now rebuild a fresh MySQL library DB, but using all your existing information.
# You can now open/launch XBMC on the other devices.
# You can now open/launch {{kodi}} on the other devices.




Line 89: Line 89:


# Quit XBMC
# Quit XBMC
# Use the MySQL configuration utility to drop/delete the existing MySQL copies of the XBMC database(s).  
# Use the MySQL configuration utility to drop/delete the existing MySQL copies of the {{kodi}} database(s).  
## Consult the table on '''[[XBMC databases/Versions]]''' and note the version number for the downgraded version. You will want to get rid of all of the versions '''after''' that version.
## Consult the table on '''[[Databases/Versions]]''' and note the version number for the downgraded version. You will want to get rid of all of the versions '''after''' that version.
## When using phpMyAdmin: select ''Databases'' option from the top of the screen, put a checkbox in front of all databases (''after'' the stable version from step 2) called '''MyMusic''' or '''MyVideos''' followed by some digits, and select ''Drop'' underneath the list. Confirm that you want to drop the databases.
## When using phpMyAdmin: select ''Databases'' option from the top of the screen, put a checkbox in front of all databases (''after'' the stable version from step 2) called '''MyMusic''' or '''MyVideos''' followed by some digits, and select ''Drop'' underneath the list. Confirm that you want to drop the databases.
## When using the command line tools: enter <code>show databases;</code> including the semicolon. Type <code>drop database <name>;</code> for every name in the resulting list, ''after'' the stable version from step 2, starting with '''MyMusic''' or '''MyVideos''' and followed by some digits, for example: <code>drop database MyVideos78; drop database MyMusic46;</code> Note that actual names may vary per XBMC version, and do not forget the trailing semicolon.
## When using the command line tools: enter <code>show databases;</code> including the semicolon. Type <code>drop database <name>;</code> for every name in the resulting list, ''after'' the stable version from step 2, starting with '''MyMusic''' or '''MyVideos''' and followed by some digits, for example: <code>drop database MyVideos78; drop database MyMusic46;</code> Note that actual names may vary per {{kodi}} version, and do not forget the trailing semicolon.
# Start one XBMC instance that is using the version you want to use and give it lots of time to update the database.
# Start one {{kodi}} instance that is using the version you want to use and give it lots of time to update the database.
# XBMC will make a new updated DB using the last working DB from the previous XBMC installation.
# {{kodi}} will make a new updated DB using the last working DB from the previous {{kodi}} installation.
# You can now open/launch XBMC on the other devices.
# You can now open/launch {{kodi}} on the other devices.


===Different XBMC versions===
===Different {{kodi}} versions===
Using MySQL to sync the library only works if all XBMC devices are using the same version of XBMC.
Using MySQL to sync the library only works if all {{kodi}} devices are using the same version of XBMC.


==Notes==
==Notes==

Revision as of 06:52, 29 December 2014

Share libraries w/MySQL, guide:

  1. Introduction
  2. Setting up MySQL
  3. Setting up Kodi
Home icon grey.png   ▶ MySQL ▶ Upgrading

When updating to a new version, Kodi will make a copy of the current DB and upgrade the copy. That way if anything goes wrong in the upgrading you still have an original untouched copy of your DB.


Starting in Kodi v12 "Frodo", you no longer need to use path substitution to sync thumbnails and other art between Kodi devices. Using path subs is still supported, which is useful for Kodi devices with small amounts of memory, but is discouraged for sharing thumbnails between multiple clients.


Note: Updating your library may take a few minutes, so be sure to give Kodi time to do its thing. Stopping midway will mess up the updated DB.


Backing up the database

BACKUP YOUR DATABASE JUST IN CASE.

Even though Kodi makes a copy of your DB and updates that, it is still highly recommended to export your library from your current version before beginning the upgrade process. You don't want to find yourself starting your library from scratch, and it's better to be safe than sorry.


It's also a good idea to make periodic backups of your library even if you are not updating XBMC. Disks can fail, bugs can happen, and if you've put a lot of time in setting up your library then you should play it safe.


To backup simply export the library to a single file using Settings -> Videos -> Library -> Export library and select "multiple files". "Single file" also works, but "multiple files" is recommended as it is the most fail-safe method, especially for saving/resorting images. For more information see HOW-TO:Backup the library.

Making sure Kodi can update the library

Note: If you originally set up MySQL using this Kodi Wiki guide then you should not have to worry about this section. It never hurts to double check the settings, though.


For Kodi to update the library from a previous version, you may need to make changes to your MySQL server. The user made for the MySQL DB ("xbmc" in most guides) will need access to create new databases. If the user already has that access then you don't need to do anything. If not, you will have to use the following command on your MySQL server:

GRANT ALL ON *.* TO 'xbmc';


If you are worried about security then you can limit XBMC's access in MySQL once it has created the new video DB. However, you will need to re-grant these privileges the next time you update XBMC. If the only thing you use MySQL for is Kodi then you don't really need to worry about security.

Thumbnails

Note: This only applies for migrating from using pathsubs for thumbnails to using the normal local image cache (faster). Most users can ignore this section.

Show instructions -->

Kodi v12 and later no longer needs path substitution for syncing thumbnails.


NOTICE: You can still use path substitution if you want to! Path subs are still useful for devices with low memory, like the Apple TV2 and various devices using small USB drives or SSDs. If you still want to use path subs then skip this entire "Thumbnails" section. Loading the library will be slowing with path subs, but this might be a reasonable trade-off for your situation.


If you used path subs for the entire Thumbnails folder - Extra safe method[1]
  1. Quit all Kodi devices.
  2. Update all instances to Kodi v12 "Frodo", but don't open/launch Kodi yet.
  3. Choose one Kodi device and open XBMC
  4. Say yes when it asks to update art.
  5. Copy your path-substituted thumbnails folder onto all Kodi devices, replacing the local thumbnails folder in the userdata folder.
  6. On the first Kodi device go into userdata/database/ and copy the "Textures13.db" file to all the other Kodi devices in their userdata/database/ folders.
  7. Remove path substitution for thumbnails from the advancedsettings.xml file of each Kodi device.
  8. You can now open/launch Kodi on your other devices.
  9. From that point on, Kodi will sync art/thumbs directly to each Kodi box, but will keep them in sync (whereas before Kodi would only add art on one box, and boxes could have different artwork if set manually). Having the thumbnails folder locally on each box will also speed up loading the library, and what art to use is stored in the videos DB that MySQL is already sharing.


If you used path subs for only userdata/thumbnails/videos/ and userdata/thumbnails/music/ - Extra safe method[1]
  1. Quit all Kodi devices.
  2. Choose one Kodi device to do the initial thumbnails update.
  3. Copy the path-substituted thumbnails folder(s) for userdata/thumbnails/videos/ and/or userdata/thumbnails/music/ to the local userdata/thumbnails/ folder on your first Kodi device.
    If your thumbnails folders are being shared from another Kodi device's thumbnails folder then you don't need to do this. Just make that Kodi device your "first device" for these instructions.
  4. Update all instances to Kodi v12 "Frodo", but don't open/launch Kodi yet.
  5. Open Kodi on your first device.
  6. Say yes when it asks to update art.
  7. Copy your the thumbnails folder from the first Kodi device onto all the other Kodi devices.
  8. On the first Kodi device go into userdata/database/ and copy the "Textures13.db" file to all the other Kodi devices in their userdata/database/ folders.
  9. Remove path substitution for thumbnails from the advancedsettings.xml file of each Kodi device.
  10. You can now open/launch Kodi on your other devices.
  11. From that point on, Kodi will sync art/thumbs directly to each Kodi box, but will keep them in sync (whereas before Kodi would only add art on one box, and boxes could have different artwork if set manually). Having the thumbnails folder locally on each box will also speed up loading the library, and what art to use is stored in the videos DB that MySQL is already sharing.

Common problems

Upgrade fails

Method 1
  1. Downgrade Kodi on one of your Kodi devices to the previous version of Kodi (you only need to do this to one device). Export the library to multiple files using Settings -> Videos -> Library -> Export library and select "multiple files".
    Note: If you backed up your Kodi database, you can use that backup in place of this first step.
  2. Quit XBMC
  3. Place <importwatchedstate>true</importwatchedstate> in your advancedsettings.xml file on the downgraded Kodi device.
  4. If you used the <name> tag in your advancedsettings.xml file, remove these lines. Kodi will simply use default database names instead (MyVideos and MyMusic). Sometimes using the <name> tag will cause issues when updating the MySQL libraries, but the tag is not needed as of Kodi v11 and later.
  5. Use the MySQL configuration utility to drop/delete the existing MySQL copies of the Kodi database(s).
    1. When using phpMyAdmin: select Databases option from the top of the screen, put a checkbox in front of all databases called MyMusic or MyVideos followed by some digits, and select Drop underneath the list. Confirm that you want to drop the databases.
    2. When using the command line tools: enter show databases; including the semicolon. Type drop database <name>; for every name in the resulting list starting with MyMusic or MyVideos and followed by some digits, for example: drop database MyVideos78; drop database MyMusic46; Note that actual names may vary per Kodi version, and do not forget the trailing semicolon.
  6. You can now re-update the device you downgraded in step 1.
  7. Import the library by simply scanning it in as a new file source. Kodi will pick up the exported data as it scans the files in.
  8. Kodi will now rebuild a fresh MySQL library DB, but using all your existing information.
  9. You can now open/launch Kodi on the other devices.


Method 2
  1. Quit XBMC
  2. Use the MySQL configuration utility to drop/delete the existing MySQL copies of the Kodi database(s).
    1. Consult the table on Databases/Versions and note the version number for the downgraded version. You will want to get rid of all of the versions after that version.
    2. When using phpMyAdmin: select Databases option from the top of the screen, put a checkbox in front of all databases (after the stable version from step 2) called MyMusic or MyVideos followed by some digits, and select Drop underneath the list. Confirm that you want to drop the databases.
    3. When using the command line tools: enter show databases; including the semicolon. Type drop database <name>; for every name in the resulting list, after the stable version from step 2, starting with MyMusic or MyVideos and followed by some digits, for example: drop database MyVideos78; drop database MyMusic46; Note that actual names may vary per Kodi version, and do not forget the trailing semicolon.
  3. Start one Kodi instance that is using the version you want to use and give it lots of time to update the database.
  4. Kodi will make a new updated DB using the last working DB from the previous Kodi installation.
  5. You can now open/launch Kodi on the other devices.

Different Kodi versions

Using MySQL to sync the library only works if all Kodi devices are using the same version of XBMC.

Notes

  1. ↑ 1.0 1.1 The extra safe part is because we copy the thumbnails artwork manually to each XBMC device, instead of having XBMC just download the art from the internet. If you have library items that scanned their art in a long time ago then the internet copy might not exist anymore, hence this extra safe method.
Retrieved from "https://kodi.wiki/index.php?title=MySQL/Upgrading&oldid=82739"