[mythtv-users] Fixing minor errors in Myth database

Michael T. Dean mtdean at thirdcontact.com
Mon Apr 29 00:13:30 UTC 2013


On 04/28/2013 06:23 PM, Paul Gardiner wrote:
> 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?

It's not strictly required as the first thing MythTV does when upgrading 
the database is set the default character set--not to mention the fact 
that the default character set isn't even used unless you create a table 
without specifying the character set and we specify the character set 
when creating all the tables we use.

>> 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?

Correct.  It's maintained in the mysql database (yes, a database managed 
by the MySQL database management system called mysql that stores 
information used by the server, such as authentication and authorization 
information).

Mike


More information about the mythtv-users mailing list