[mythtv-users] Backend DB migration--replace or just restore?

[lists at arewethere.net]

Following up on an old thread as I am about to upgrade to 20.04/v31
and I have a question about the collation.
I've run a fresh install of 20.04.1, added mariadb,
then added the mythtv packages and sourced mc.sql,
and tweaked the database permissions so the old password is respected
 (ALTER USER 'myhttv'@'localhost' IDENTIFIED BY 'the-old-password')

On Sun, Jul 26, 2020 at 04:03:39AM +1200, Stephen Worthington wrote:
<snip>
> 
> You want to do a full restore with drop and create.  The other options
> are for when you have a bad backup or partially corrupt database. When
> you have a good backup of a full database, you always want to just
> completely replace the existing database.
> 
> It does not matter if you want to run mythtv-setup first and play with
> the empty database - anything you do will be overwritten with the
> restored database.
> 
> You do want the MythTV packages installed before you restore your old
> database.  The packages set up the database system so that restoring
> databases will work.  See this file for the commands that get run to
> set up the mythconverg database correctly:
> 
> /usr/share/mythtv/sql/mc.sql
> 
> If those commands are not run (if you create the mythconverg database
> by restoring your backup before an existing mythconverg database has
> been created), then the mythconverg database will not work correctly.
> For example, its collating sequences will not be correct and data will
> be listed and processed in the wrong order.  If you ever need to
> manually create a mythconverg database, use the mc.sql file:
> 
> sudo mysql
> source /usr/share/mythtv/sql/mc.sql
> quit
> 
> Then a backup can be restored over the newly created mythconverg
> database and it will work correctly.  This is part of what the
> packages do when they are installed.
> 
> If you are copying your old config.xml files to the new system, then
> you will need to run "sudo mysql" and do the necessary GRANT commands
> to allow access using the user and password in the config.xml files.
> Do that before restoring the old database - mythconverg_restore.pl
> uses the config.xml files and restoring will fail if the config.xml
> username/password does not match a GRANT.
> 
> After the restore, you immediately want to run mythtv-setup.  That
> ensures that any database schema upgrades necessary to run with the
> version of MythTV on the new system will get done.  If you are using
> frontend only plugins like MythMusic, then you also need to run
> mythfrontend to ensure they get schema upgrades done also.


I'm struggling a bit here.

In (v31) mythconvert_restore.pl the create procedure includes this

   verbose($verbose_level_debug, 'Setting database character set.');
   $query = qq{ALTER DATABASE $mysql_conf{'db_name'}
               DEFAULT CHARACTER SET latin1
               COLLATE latin1_swedish_ci;};

but mc.sql does that step like this

   ALTER DATABASE mythconverg
   DEFAULT CHARACTER SET utf8
   COLLATE utf8_general_ci;

so the different approaches seem inconsistent with each other.

If you source mc.sql you'll get the databse set up with the
modern default for character set and collation, but if
you then restore your backup with --drop_database --create_database,
you will set the database back to the old defaults.

I checked my backups (v29) and they seem to have the right
incantations at the top to make things compatible with utf8:

      /*!40101 SET NAMES utf8 */;
      ...
      DROP TABLE IF EXISTS `archiveitems`;
      /*!40101 SET @saved_cs_client     = @@character_set_client */;
      /*!40101 SET character_set_client = utf8 */;
      CREATE TABLE `archiveitems` (
       ...
      ) ENGINE=MyISAM DEFAULT CHARSET=utf8;

     and so on

I went and looked up what the SET NAMES utf8 magic really means[1].
It seems it is equivalent to

  SET NAMES utf8 COLLATION utf8_general_ci

So my understanding is in my situation I should be fine to restore
without the --drop and --create options for  mythconverg_restore.pl.

I guess the question is: given the backup is specifying the
encoding (and implicitly, a collation) does it matter how
mythconverg_restore.pl re-creates the empty database?

Comments welcome

[1] https://mariadb.com/kb/en/supported-character-sets-and-collations/