[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