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

Stephen Worthington stephen_agent at jsw.gen.nz
Sun Sep 6 11:27:44 UTC 2020


On Sun, 6 Sep 2020 10:47:46 +0100, you wrote:

>On 06/09/2020 05:12, lists at arewethere.net wrote:
>
>> I'm struggling a bit here.
>> 
>> In (v31) mythconverg_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.
>> 
>I haven't run mythconverg_restore.pl in years, but that 'swedish' 
>setting is in my recent build from master.  It does look an unlikely 
>default.
>
>John P

That looks like a bug to me.  There is no way that it should be
changing the character set on restore to something other than what the
old database was using.

When schema changes get applied to the database when the MythTV
version is upgraded, the database character set might then be changed,
and you would hope that backing up the entire database and restoring
it would retain the correct character set.  Looking at a recent backup
done by v30, it has the character set specified for each table, so I
think that is fine - backups and restores will have the correct
character sets for the existing tables.  But there does not appear to
be any backup and restore of the default character set.  So if you
manually create a new table without specifying the character set, it
will potentially get the wrong character set from the default setting.
This will not affect most MythTV users though - just those of us who
do things to their databases other than via MythTV.  So I think that
the right thing to do is to run the "ALTER DATABASE mythconverg
DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;" command from
mc.sql after running mythconverg_restore.pl until
mythconverg_restore.pl is fixed.

These commands produce interesting output in my v30 database:

MariaDB [mythconverg]> show variables like 'char%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

MariaDB [mythconverg]> show variables like 'coll%';
+----------------------+-------------------+
| Variable_name        | Value             |
+----------------------+-------------------+
| collation_connection | utf8_general_ci   |
| collation_database   | utf8_general_ci   |
| collation_server     | latin1_swedish_ci |
+----------------------+-------------------+
3 rows in set (0.00 sec)


So it looks like my server default character set has been set to
latin1 and the server default collating sequence to latin1_swedish_ci
by mythconverg_restore.pl at some point.  But I have run the mc.sql
ALTER command, so the current database (mythconverg) has had its
defaults set correctly.  And my client is connecting to MariaDB using
utf8.

From the mysql command line, you can change these settings with:

set character_set_server='utf8';
set collation_server='utf8_general_ci';

but I suspect that they may only remain set until MySQL/MariaDB gets
restarted.  So these lines:

character_set_server='utf8'
collation_server='utf8_general_ci'

may need to be added to one of the config files in /etc/mysql to make
it permanent.  I already have a
/etc/mysql/conf.d/mythtv-jsw-tweaks.cnf file and I am going to add
them there.  After that, when mythconverg_restore.pl gets run, the
server default latin1/latin1_swedish_ci settings will be put in again,
but when MySQL/MariaDB is next restarted, the proper settings will be
back again.


More information about the mythtv-users mailing list