Solved - Migrating my NAS-based Kodi database from MariaDB 5 to MariaDB 10

Solved Migrating my NAS-based Kodi database from MariaDB 5 to MariaDB 10 - Printable Version

+- Kodi Community Forum (https://forum.kodi.tv)
+-- Forum: Support (https://forum.kodi.tv/forumdisplay.php?fid=33)
+--- Forum: General Support (https://forum.kodi.tv/forumdisplay.php?fid=111)
+---- Forum: OS independent / Other (https://forum.kodi.tv/forumdisplay.php?fid=228)
+---- Thread: Solved Migrating my NAS-based Kodi database from MariaDB 5 to MariaDB 10 (/showthread.php?tid=366635)

Migrating my NAS-based Kodi database from MariaDB 5 to MariaDB 10 - andrewilley - 2022-01-21

Due to upcoming version obsolescence, I need to migrate my current working Kodi 18.9.0 database from MariaDB 5 to MariaDB 10, both of them on my Synology NAS. The database is accessed from various locations and devices on our home network, and works fine.

Synology have a nice simple 'how-to' guide (https://kb.synology.com/en-global/DSM/tutorial/How_to_migrate_database_MariaDB5_to_MariaDB10) which looked too good to be true. Unfortunately, it was. Sad

I was able to Export all my existing databases to a file using phpMyAdmin. I also exported the Kodi databases individually to Kodi_video116.sql etc. However, importing that big file or the individual database files into MariaDB 10 fails, for example:

SQL query:

sql:
-- Database: `Kodi_video116`

--

-- -------------------------------------------------------

--

-- Table structure for table `actor`

--

CREATE TABLE `actor` (

actor_id` int(11) NOT NULL,

`name` text,

`art_urls` text

) ENGINE=InnoDB DEFAULT CHARSET=utf8

MySQL said: #1046 - No database selected

Any ideas what might be causing this? Do I have do adjust some settings in phpMyAdmin when either exporting or importing a Kodi database? (I just used the default settings as suggested by Synology)

Andre

RE: Migrating my NAS-based Kodi database from MariaDB 5 to MariaDB 10 - Klojum - 2022-01-21

(2022-01-21, 22:13)andrewilley Wrote: MySQL said: #1046 - No database selected

Apparently the target database was not yet selected. It's possible that you overlooked the extra options for recreating a database and/or its tables when doing the SQLdump in PHPMyAdmin, as they are not enabled by default. If these options were set, the following command (or something similar) would have been added to the SQL export file. USE `MyVideos116`; is what selects the database for the import.

sql:
--

-- Current Database: `MyVideos116`

--

/*!40000 DROP DATABASE IF EXISTS `MyVideos116`*/;

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `MyVideos116` /*!40100 DEFAULT CHARACTER SET utf8 */;

USE `MyVideos116`;

Also, for importing the views and triggers that come with the Kodi database, the database user will require database root access and not just solely both Kodi databases.

RE: Migrating my NAS-based Kodi database from MariaDB 5 to MariaDB 10 - andrewilley - 2022-01-22

Thanks for the great advice. So Synology's instructions were somewhat lacking when they said just "Select an export method and desired export format. Click Go to export".

Which of the following options should I select when exporting the backup? This was the default when I opened the Custom Options menu, I assume "Add DROP DATABASE IF EXISTS" and "Add DROP TABLE / VIEW / PROCEDURE / FUNCTION / EVENT / TRIGGER" at least:

As all instances of Kodi are on a LAN with no external access, the Kodi SQL login account has quite a lot of access (probably too much, most of the Admin section is presumably not needed?). Should I set anything else:

Sorry for all the questions, I wouldn't be doing this at all other than Synology shutting down support for MariaDB 5, and I was hoping their migration procedure would be a fully automated click'n'go process.

Andre

RE: Migrating my NAS-based Kodi database from MariaDB 5 to MariaDB 10 - Klojum - 2022-01-22

(2022-01-22, 01:16)andrewilley Wrote: Which of the following options should I select when exporting the backup?

All "Object creation statements". It's necessary when you want to fully recreate a database instead of just the tables and their data.

(2022-01-22, 01:16)andrewilley Wrote: the Kodi SQL login account has quite a lot of access

Yep, most of it is unnecessary for normal operations, but others are necessary during a Kodi upgrade process when new databases, tables, triggers etc need to be created.

(2022-01-22, 01:16)andrewilley Wrote: I wouldn't be doing this at all other than Synology shutting down support for MariaDB 5, and I was hoping their migration procedure would be a fully automated click'n'go process.

If that were the case, everyone could be a database administrator. No, there still is some manual handywork necessary. Perhaps only because of PHPMyAdmin's default settings, but on the plus side, you testdrive the new database and still go back to v5 in case of troubles and then try the export/import into v10 again.

RE: Migrating my NAS-based Kodi database from MariaDB 5 to MariaDB 10 - andrewilley - 2022-01-22

Thanks again. I re-exported my databases with all of the 'Object' items ticked, and when I tried to import into MariaDB 10, the first three (empty) Kodi Music databases went fine, but upon importing the first Video database it stopped with:

sql:
Error SQL query: 

CREATE ALGORITHM=UNDEFINED DEFINER=`KodiMedia`@`%` SQL SECURITY DEFINER VIEW `tvshow_view` AS SELECT `tvshow`.`idShow` AS `idShow`, `tvshow`.`c00` AS `c00`, `tvshow`.`c01` AS `c01`, `tvshow`.`c02` AS `c02`, `tvshow`.`c03` AS `c03`, `tvshow`.`c04` AS `c04`, `tvshow`.`c05` AS `c05`, `tvshow`.`c06` AS `c06`, `tvshow`.`c07` AS `c07`, `tvshow`.`c08` AS `c08`, `tvshow`.`c09` AS `c09`, `tvshow`.`c10` AS `c10`, `tvshow`.`c11` AS `c11`, `tvshow`.`c12` AS `c12`, `tvshow`.`c13` AS `c13`, `tvshow`.`c14` AS `c14`, `tvshow`.`c15` AS `c15`, `tvshow`.`c16` AS `c16`, `tvshow`.`c17` AS `c17`, `tvshow`.`c18` AS `c18`, `tvshow`.`c19` AS `c19`, `tvshow`.`c20` AS `c20`, `tvshow`.`c21` AS `c21`, `tvshow`.`c22` AS `c22`, `tvshow`.`c23` AS `c23`, `tvshow`.`userrating` AS `userrating`, `tvshow`.`duration` AS `duration`, `path`.`idParentPath` AS `idParentPath`, `path`.`strPath` AS `strPath`, `tvshowcounts`.`dateAdded` AS `dateAdded`, `tvshowcounts`.`lastPlayed` AS `lastPlayed`, `tvshowcounts`.`totalCount` AS `t[...]



MySQL said: 

#1449 - The user specified as a definer ('KodiMedia'@'%') does not exist

Does this mean I have to pre-define any user account (such as the user KodiMedia) on the target system that will be accessing the databases first? i.e. as per the <user> and <pass> values defined in Kodi's advancedsettings.xml .

[Edit] I created the user account and the DB then imported without further errors. Now to test it.

Andre

RE: Migrating my NAS-based Kodi database from MariaDB 5 to MariaDB 10 - andrewilley - 2022-01-23

All worked fine in the end. Took a few tweaks, but all sorted now. MariaDB 5 can now be disabled. Thanks for the help.

Andre

RE: Migrating my NAS-based Kodi database from MariaDB 5 to MariaDB 10 - Klojum - 2022-01-23

(2022-01-22, 13:41)andrewilley Wrote: Does this mean I have to pre-define any user account (such as the user KodiMedia) on the target system that will be accessing the databases first

Yes, because the database users and their user rights are stored in a system database named mysql of the database server.

You can create new users in a new database as per the instructions on the Kodi Wiki page when creating a new database, and grant them full access.

Migrating my NAS-based Kodi database from MariaDB 5 to MariaDB 10 - Klojum - 2022-01-23

Thread marked solved.