Mailing List Archive

On 7/27/20 9:48 AM, DryHeat122 wrote:
>
>
> On Mon, Jul 27, 2020 at 2:36 AM Stephen Worthington <stephen_agent@jsw.gen.nz <mailto:stephen_agent@jsw.gen.nz>> wrote:
>
> On Sun, 26 Jul 2020 14:59:41 -0400, you wrote:
>
>
> I would second this.  You do need the "steve" user set up before doing
> a database restore.  You need a config.xml file in all the right
> places:
>
> /etc/mythtv/config.xml
> /home/mythtv/.mythtv/config.xml
> /home/steve/.mythtv/config.xml
>
> And you do need both the .mythtv subdirectories set up properly do be
> able to have config.xml files in them.
>
>
> I followed Jim's advice and things seem to be set up this way now.  I must say, I don't get why Myth was designed this way, with config files
> sprinkled all over the place.  Seems like one ought to suffice.   But whatever.

It's possible to do it with one. Ubuntu's solution uses one *file*
with *symbolic links* to it from the login user and mythtv user.
Ubuntu also has a template in usr/share/mythtv/config.xml. It's
only used during setup, not run time (and needn't be changed.)

The entries in both users should look like:

/home/mythtv/.mythtv/config.xml -> /etc/mythtv/config.xml
/home/steve/.mythtv/config.xml -> /etc/mythtv/config.xml

MythTV's design for accessing config.xml is very flexible. Which also
seems to cause confusion.

--
Bill
_______________________________________________
mythtv-users mailing list
mythtv-users@mythtv.org
http://lists.mythtv.org/mailman/listinfo/mythtv-users
http://wiki.mythtv.org/Mailing_List_etiquette
MythTV Forums: https://forum.mythtv.org

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/


_______________________________________________
mythtv-users mailing list
mythtv-users@mythtv.org
http://lists.mythtv.org/mailman/listinfo/mythtv-users
http://wiki.mythtv.org/Mailing_List_etiquette
MythTV Forums: https://forum.mythtv.org

> On 6 Sep 2020, at 12:12 pm, lists@arewethere.net wrote:
>
> 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.

I’ve, for ages. just done a db restore (mainly because I wanted to keep meta data)
All good except lots of tiny funies have crept in

All so trivial to not remember detail but for example ‘Forget Old’ is broken.
I think I’ll try a new database just preserving videometadata and recordings to see if a new db fixes the funnies but in any event be prepared to have to nurse the db.
Also mythweb may have messed with the db in past times, so corruption there is possible.
IIRC I setup a recording schedule, cancelled it, then tried to re-create almost the same schedule.

James
_______________________________________________
mythtv-users mailing list
mythtv-users@mythtv.org
http://lists.mythtv.org/mailman/listinfo/mythtv-users
http://wiki.mythtv.org/Mailing_List_etiquette
MythTV Forums: https://forum.mythtv.org

On 06/09/2020 05:12, lists@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

_______________________________________________
mythtv-users mailing list
mythtv-users@mythtv.org
http://lists.mythtv.org/mailman/listinfo/mythtv-users
http://wiki.mythtv.org/Mailing_List_etiquette
MythTV Forums: https://forum.mythtv.org

On Sun, Sep 6, 2020 at 3:48 AM John Pilkington <johnpilk222@gmail.com>
wrote:

> 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.
>
Mysql AB is a Swedish company (now owned by Oracle but originally Swedish)
so that's where the initial default of swedish for collation came in...
The latin1 encoding was due to multibyte not being a very widely used thing
when mysql was originally coded (mid 90s), and both of these were default
in all versions before Mysql 8.

Took me a bit of time to find that history, but I knew at one point they
changed and forgot when.

FWIW reloading a dump explicitly stating latin1_swedish_ci will simply make
a structure with that encoding and collation.

I'd suggest not changing it unless you go down the rabbit hole of verifying
the charset is converted properly before simply telling the database the
data is now in a new charset. Otherwise you end up with mojibake such as
characters like curly quotes (opening and closing, versus a straight) '
quote, turning into 3 or 4 different symbols.

For example:
[image: image.png]

I doubt you'd get much horribleness other than these sort of punctuation
issues, but I suggest keeping things how it was dumped in the first place
for continuity.

Mike

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

>On 06/09/2020 05:12, lists@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.
_______________________________________________
mythtv-users mailing list
mythtv-users@mythtv.org
http://lists.mythtv.org/mailman/listinfo/mythtv-users
http://wiki.mythtv.org/Mailing_List_etiquette
MythTV Forums: https://forum.mythtv.org