[mythtv-users] Fixing minor errors in Myth database

[Paul Gardiner]

On 28/04/2013 21:47, Michael T. Dean wrote:
> On 04/28/2013 09:16 AM, Paul Gardiner wrote:
>> On 28/04/2013 00:50, Fred Hamilton wrote:
>>> Thanks Mike.  You wrote:
>>>> To fix corruption, the only supported solution is to throw away
>>>> all re-creatable data (settings/configuration/plugin data/...)
>>>> and keep only the essential non-re-creatable data (recording
>>>> information and history).  You do so with a partial restore
>>>>
>>>>
>>>> http://www.mythtv.org/wiki/Database_Backup_and_Restore#Partial_restore_of_a_backup
>>>
>>>
>>>
>>> OK.  Here are the steps I've taken:
>>> mythconverg_backup.pl <http://mythconverg_backup.pl> --verbose
>>> That seemed to work fine.
>>>
>>> The next step in the wiki is to delete the existing database:
>>>      mysql -uroot -p -e 'DROP DATABASE IF EXISTS mythconverg;'
>>> That also seemed to work (no error messages anyway)
>>>
>>> Then the wiki says "then create a new database (and note that doing so
>>> may change MySQL passwords/permissions, so may cause authentication
>>> problems).", pointing to
>>> http://www.mythtv.org/wiki/MythTV-HOWTO#Setting_up_the_initial_database
>>> which seems to say to:
>>>      $ cd database
>>>      $ mysql < mc.sql
>>>
>>> So I got stuck with these three questions:
>>> 1. Where is this database directory I'm supposed to cd to?
>
> http://code.mythtv.org/cgit/mythtv/tree/mythtv/database
>
> in your source checkout
>
>>> 2. where can I find mc.sql?
>
> It's always available at:
>
> http://code.mythtv.org/cgit/mythtv/tree/mythtv/database/mc.sql
>
>>>   Is it the 320 byte file already on my
>>> system at /usr/share/mythtv/sql/mc.sql
>
> Yes, your distro put it there for your use--different distros put it in
> different places, so the HOWTO doesn't know where to tell you to look.
>
> That said, the HOWTO is now a wiki page, with the intention of making it
> easier to edit/update.  It's protected against editing so that all
> proposed changes must be approved/accepted, so we ask that anyone adding
> suggested changes do so on the discussion/talk page (
> http://www.mythtv.org/wiki/Talk:MythTV-HOWTO_-_0.26 ).  If you can come
> up with a list of directories where different distros place the mc.sql,
> we could refer to them in the HOWTO, but I'd want it to be a rather
> complete list.
>
> And, feel free to submit suggestions for any other changes you feel
> could be helpful.
>
>>> 3. This step has the ominous but not particularly helpful warning that
>>> "doing so may change MySQL passwords/permissions, so may cause
>>> authentication problems".  Can I prevent that by using my old MySQL
>>> password or something?
>>
>> I've recently performed a partial restore. Here's the sequence you need
>> (assuming I'm remembering it correctly).
>>
>> First start up mysql as root:
>>
>>   mysql -u root -p
>>
>> Then type at the mysql prompt:
>>
>>   DROP DATABASE mythconverg;
>>   CREATE DATABASE mythconverg;
>>   GRANT ALL ON mythconverg.* TO mythtv@"%" IDENTIFIED BY mythtv;
>>   FLUSH PRIVILEGES;
>>   QUIT;
>
> If you're not using mc.sql (and there are benefits to not doing
> so--specifically, to prevent breaking authorization set up by your
> distro, as Fred realized), you're better off doing:
>
> DROP DATABASE IF EXISTS mythconverg;
> CREATE DATABASE IF NOT EXISTS mythconverg;
> ALTER DATABASE mythconverg DEFAULT CHARACTER SET utf8 COLLATE
> utf8_general_ci;

When I preparing for my recent partial restore, I didn't set the default
character set. Is that something that can be done after running
mythtvsetup and partial restore, or does it have to be done directly
after creating the database?

> This approach will work fine as long as you've previously set up a
> mythtv user--which should be the case for anyone who's replacing the
> existing mythconverg database.

Ah, so the GRANT line I suggested was unnecessary because the
privileges information is in a separate database and unaltered
by the drop and create. Is that right?

Paul.