MySQL/Upgrading: Difference between revisions

Latest revision as of 05:06, 23 January 2022

Upgrading

MariaDB 10.1.11 users might want to avoid upgrading to Kodi v16 until they have updated MairaDB to 10.1.12.

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.

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 Kodi. 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 using Settings -> Media -> Library. Be aware of the differences between Single File and Separate File exports. For more information see Backup.

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.

Note: if you used 'xbmc' as a username in the past, you can keep using it even though the program has changed names to Kodi. It makes no difference as long as that is the name you set up MySQL with.

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 'kodi';

If you are worried about security then you can limit Kodi'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 Kodi. If the only thing you use MySQL for is Kodi then you don't really need to worry about security.

Common problems

Upgrade fails

Method 1

Downgrade Kodi on one of your Kodi devices to the previous version of Kodi (you only need to do this to one device).
Quit Kodi
Place between the <videolibrary>...</videolibrary> tags, both <importwatchedstate>true</importwatchedstate> and <importresumepoint>true</importresumepoint>, in your advancedsettings.xml file on the downgraded Kodi device.
Start Kodi
Export the libraries using Settings -> Media -> Library and select "Separate Files per entry" to export to separate .nfo files next to your video files.
Select "Export thumbnails and fanart" if you want to save/store such artwork too, next to your video files.
Select "Overwrite old files" so video media details, watched status and/or resume points will be updated too.
Let Kodi export the video library.
Quit Kodi
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 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.
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. Kodi will pick up the exported data as it scans the files in.
Kodi will now rebuild a fresh MySQL library DB, but using all your existing information.
You can now open/launch Kodi on the other devices.

Method 2

Quit Kodi
Use the MySQL configuration utility to drop/delete the existing MySQL copies of the Kodi database(s).
1. Consult the table on Databases#Database_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.
Start one Kodi instance that is using the version you want to use and give it lots of time to update the database.
Kodi will make a new updated DB using the last working DB from the previous Kodi installation.
You can now open/launch Kodi on the other devices.

Different Kodi versions

Using MySQL/MariaDB to sync the video and/or music library only works when all connected Kodi devices are using the same major version of the Kodi application.

Notes

@@ Line 1: / Line 1: @@
-{{incomplete}}
+{{mininav| [[MySQL]] }}
-==If you used Path Substitution for sharing thumbnails==
+{{warning|MariaDB 10.1.11 users might want to avoid upgrading to Kodi v16 until they have updated MairaDB to 10.1.12.}}
-;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 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, the internet copy might not exist, hence this extra safe method.</ref>
+{{:MySQL/Contents}}
-# Quit all XBMC devices.
-# Update all instances to XBMC v12 "Frodo", but don't open/launch XBMC yet.
-# Choose one XBMC device and open XBMC
-# 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.
-# On the first XBMC device go into userdata/database/ and copy the "Textures13.db" file to all the other XBMC devices in their userdata/database/ folders.
-# Remove path substitution for thumbnails from the advancedsettings.xml file of each XBMC box.
-# You can now open/launch XBMC 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.
+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.
-;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.
+{{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. }} }}
-# Choose one XBMC 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 [[userdata]]/thumbnails/ folder on your first XBMC 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.''
+==Backing up the database==
-# Update all instances to XBMC v12 "Frodo", but don't open/launch XBMC yet.
+{{big|'''BACKUP YOUR DATABASE JUST IN CASE.'''}}
-# Open XBMC on your first device.
-# Say yes when it asks to update art.
+Even though Kodi makes a copy of your DB and updates that, it is still highly recommended to '''''[[Import-export_library|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.
-# Copy your the thumbnails folder from the first XBMC device onto all the other XBMC devices.
-# On the first XBMC device go into userdata/database/ and copy the "Textures13.db" file to all the other XBMC devices in their userdata/database/ folders.
-# Remove path substitution for thumbnails from the advancedsettings.xml file of each XBMC box.
+It's also a good idea to make periodic backups of your library even if you are not updating Kodi. 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.
-# You can now open/launch XBMC 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.
+To backup simply '''''[[Import-export_library|export]]''''' the library using {{highlight|bordered=yes|[[Settings/Media/Library|Settings -> Media -> Library]]}}. Be aware of the differences between Single File and Separate File exports. For more information see '''[[Backup]]'''.
+==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.}}
+{{Note|if you used 'xbmc' as a username in the past, you can keep using it even though the program has changed names to Kodi. It makes no difference as long as that is the name you set up MySQL with.}}
+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:
+<syntaxhighlight lang="sql" inline>GRANT ALL ON *.* TO 'kodi';</syntaxhighlight>
+If you are worried about security then you can limit Kodi'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 Kodi. If the only thing you use MySQL for is Kodi then you don't really need to worry about security.
+==Common problems==
+===Upgrade fails===
+;Method 1
+# Downgrade Kodi on one of your Kodi devices to the previous version of Kodi (you only need to do this to one device).
+# Quit Kodi
+# Place between the {{highlight|<nowiki><videolibrary>...</videolibrary></nowiki>}} tags, both {{highlight|<nowiki><importwatchedstate>true</importwatchedstate></nowiki>}} and {{highlight|<nowiki><importresumepoint>true</importresumepoint></nowiki>}}, in your [[advancedsettings.xml]] file on the downgraded Kodi device.
+# Start Kodi
+# '''''[[Import-export_library|Export]]''''' the libraries using {{highlight|bordered=yes|[[Settings/Media/Library|Settings -> Media -> Library]]}} and select "Separate Files per entry" to export to separate .nfo files next to your video files.
+# Select "Export thumbnails and fanart" if you want to save/store such artwork too, next to your video files.
+# Select "Overwrite old files" so video media details, watched status and/or resume points will be updated too.
+# Let Kodi export the video library.
+# Quit Kodi
+# 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 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 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.
+# 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.
+# Kodi will now rebuild a fresh MySQL library DB, but using all your existing information.
+# You can now open/launch Kodi on the other devices.
+;Method 2
+# Quit Kodi
+# Use the MySQL configuration utility to drop/delete the existing MySQL copies of the Kodi database(s).
+## Consult the table on '''[[Databases#Database_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 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 Kodi instance that is using the version you want to use and give it lots of time to update the database.
+# Kodi will make a new updated DB using the last working DB from the previous Kodi installation.
+# You can now open/launch Kodi on the other devices.
+===Different Kodi versions===
+Using MySQL/MariaDB to sync the video and/or music library only works when all connected Kodi devices are using the same major version of the Kodi application.
 ==Notes==
 <references />
+{{Isengard updated}}