[mythtv-users] error when fixing database forced by mythtv upgrade

[Michael T. Dean]

On 09/27/2009 06:14 PM, Petr Stehlik wrote:
> Michael T. Dean píše v Ne 27. 09. 2009 v 16:08 -0400:
>   
>> hmmm...  41, actually, which makes sense, kind of (I
>>     
> very interesting yet unfinished sentence :-)
>   

Oops.  I meant to say, "I'm seeing a problem at 40, but it should be 
41..."  (See below for why.)

>> So, can you do me a favor and drop your database:
>> mysql -umythtv -p mythconverg -e 'DROP DATABASE IF EXISTS mythconverg;'
>> then run mc.sql:
>> mysql -uroot -p mythconverg < mc.sql
>>     
> here I've got "ERROR 1049 (42000): Unknown database 'mythconverg'"
> because the database was dropped in the previous step. Changing
> mythconverg to mysql fixes this little problem.
>   

Oops, again.  Copy/paste issue.  Glad you figured it out.

>> grep -v "INSERT INTO \`people\` " \
>>     /var/tmp/mythconverg-to_uncorrupt.sql > /var/tmp/restore.sql
>>
>> and then do your restore:
>>
>> /home/joy/myth/22/share/mythtv/mythconverg_restore.pl \
>>     --filename restore.sql --directory /var/tmp
>>     
> and here I get "ERROR: The database does not exist." Took me a little
> while to figure out that this error means that I forgot to update the
> other occurence of IDENTIFIED BY 'mythtv' in the mc.sql so it happened
> to change the password to mythconverg by accident. It would be great if
> the restore script could handle that by reporting error accessing the
> database instead of reporting that the database is not there :-)
>   

I'll look at it.  Had you run the restore with --verbose, you would have 
seen the error, "Unable to connect to database."  However, I was 
intentionally suppressing the actual connection error messages because 
they are rather ugly and I wasn't able to control them according to 
verbosity level.  I'll see if I can come up with a way of getting that 
actual error message (in some short version) and still controlling its 
output.

> After fixing the mc.sql and restarting this process from scratch the
> mythconverg_restore went fine.
>   

So, that's because there's a "prefix" index on the name column of the 
people table.  Only the first 41 (of 128) characters are checked to 
determine if the values are unique.  I'm actually about to commit a fix 
to the corruption-detection thanks to your noticing this.  It will 
actually make it a more-stringent check, so it won't cause your DB to 
pass, so don't worry about updating to get it.  :)

>> and see if that works--basically, exactly what you tried before
>>     
> it's actually not exactly what I tried before. Before I was following
> the webpage that instead of using mc.sql said to create the database
> manually and alter it to use latin1 (in the paragraph "Clearing the
> corrupt database"). The mc.sql alters it to use utf8. That could be an
> important difference.
>   

No, that doesn't make any difference with a full restore of a backup 
(yours counts because we just got rid of the inserts for people) or a 
new DB.  MythTV trunk actually changes the DB encoding to utf8 on 
startup/before the DB upgrade (which is why you saw the DB charset 
showing as utf8 in the one post).  The DB charset is really not 
important in this process, as it only sets the charset that's used by 
default for new tables (which simply set the charset that's used by 
default for new columns).  Since all we're doing in this process is 
working with existing tables/columns, it won't be used at all.

I think I just put the DB creation commands directly in the wiki page 
because then I didn't have to worry about the complexities of the 
privilege system or "breaking" users' login capability if they had 
modified username/password.

>> Then, if that restores without issue, try to run the trunk upgrade and 
>> let me know what happens.
>>     
> 2009-09-28 00:09:56.529 Database corruption detected. Unable to proceed
> with database upgrade. (Table: oldrecorded, Warnings: 265)

In your other message, you mentioned that you were thinking of 
re-encoding back to latin1.  However, we need the data encoded as UTF-8 
whose bytes are stored as latin1 characters.  Therefore, if you go back 
to latin1, you'll have data that can /not/ be converted to UTF-8.

The whole point of the 
http://www.mythtv.org/wiki/Fixing_Corrupt_Database_Encoding page is to 
take the UTF-8 backup of UTF-8 data and then lie to mysql, telling it 
that the data is actually latin1 data.  That causes mysql to insert the 
UTF-8 bytes as latin1 characters--what we need for 0.21-fixes/the trunk 
charset conversion code.

The problem is that some of your data is corrupt and some is not.  So, 
you have to go through every character-based value in the DB and see if 
it's UTF-8 or latin1 then encode it/store it properly /and/ handle 
duplicates/foreign-key constraints/etc.  This is exactly what I plan to 
work on.  Therefore, if you're patient, I'll have some code to fix 
things up for you.  I just need to get a chance to work on it.  (So, if 
I don't respond farther, it's because I'm working on code to fix your 
(and other users') DB(s).  :)

I'm basing my statement that your data is partially corrupt on the 
assumption that you have actually recorded more than 265 episodes of 
shows since you started using MythTV.  If that's not the case, then 
throwing away data may not be such a bad idea.  (Now, since I've 
recorded 9433 episodes, I /definitely/ wouldn't throw all my recording 
history out.)

Mike