[mythtv-users] Fixing minor errors in Myth database

[Michael T. Dean]

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;

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.

I may add in a separate .sql file that does the DB creation without 
messing with authorization just so we can reference it directly for 
these cases.

Mike