[mythtv-users] upgrade to 0.22 problems - database encoding

[Michael T. Dean]

On 03/08/2010 05:09 PM, Scott Lipcon wrote:
> I've been running 0.21-fixes on gentoo for quite a while, and thought 
> I had already done the database encoding fix on that... in any case, 
> I've double checked my configuration - when I restore the 0.21 
> database, i get the following mysql status:
...
> Then I start 0.22 mythbackend, and it says in the log:2010-03-08 
> 11:44:16.553 Newest MythTV Schema Version : 1244
...
> 2010-03-08 11:44:16.980 Database corruption detected. Unable to 
> proceed with database upgrade. (Table: people, Warnings: 269)
> 2010-03-08 11:44:16.982 Your database must be fixed before you can 
> upgrade beyond 0.21-fixes. Please see 
> http://www.mythtv.org/wiki/Fixing_Corrupt_Database_Encoding for 
> information on fixing your database.
> 2010-03-08 11:44:16.984 Database Schema upgrade FAILED, unlocking.
> 2010-03-08 11:44:16.985 Couldn't upgrade database to new schema

This means you have some corrupt data in your database that uses the 
wrong character encoding.

> At this point, if I stop the backend and do another mysql status, it 
> now says:
>
> --------------
> mysql  Ver 14.12 Distrib 5.0.84, for pc-linux-gnu (x86_64) using 
> readline 6.0
>
> Connection id:          21
> Current database:       mythconverg
> Current user:           root at localhost
> SSL:                    Not in use
> Current pager:          stdout
> Using outfile:          ''
> Using delimiter:        ;
> Server version:         5.0.84 Gentoo Linux mysql-5.0.84-r1
> Protocol version:       10
> Connection:             Localhost via UNIX socket
> Server characterset:    latin1
> Db     characterset:    utf8
> Client characterset:    latin1
> Conn.  characterset:    latin1
> UNIX socket:            /var/run/mysqld/mysqld.sock
> Uptime:                 31 min 35 sec
>
> Threads: 1  Questions: 4172  Slow queries: 0  Opens: 1103  Flush 
> tables: 1  Open tables: 64  Queries per second avg: 2.202
> --------------
>
>
> (note the Db characterset changed from latin1 to utf8 - it seems that 
> starting 0.22 actually corrupts the character set?)

Yes, because the attempted upgrade (which failed) actually changed the 
DB character set to UTF-8.  That's normal.

> Is this the dreaded partial corruption?    I've tried doing a partial 
> restore on top of the 0.21 blank.sql file posted here a few months ago 
> and it still is complaining - this time only 1 warning, in the 
> oldrecorded table.   Is there any way to find the "bad" characters in 
> the database backup and fix them by hand?   I really don't want to 
> lose all my recordings but I'm somewhat at a loss as to what to try 
> next (besides re-installing 0.21...)

It is definitely corruption.  Because you had tried to reconfigure your 
MythTV box in 0.21 (where there was no way to test if the 
reconfiguration worked--as you wouldn't get the errors that only appear 
on database upgrade), you almost definitely have partial corruption.

If it is partial corruption, it just means you'll need to use this fix:

http://www.mythtv.org/wiki/Fixing_Corrupt_Database_Encoding#Partial_vs_.22equal.22_corruption
http://www.gossamer-threads.com/lists/mythtv/users/406111#406111

Mike