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

[Gary Dawes]

2009/9/29 Michael T. Dean <mtdean at thirdcontact.com>

> On 09/29/2009 10:05 AM, Nicolas Will wrote:
>
>> On Sun, 2009-09-27 at 19:09 -0400, Michael T. Dean wrote:
>>
>>
>>> 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.  :)
>>>
>> I've been hit by the duplicate issue when moving to .22.
>>
>> I cleaned my DB, but it was still the same.
>>
>> In the clean DB file, I changed the 41 to 51 and it restored properly,
>> as it was checking more characters for uniqueness.
>>
>> Then .22 converted its DB version without any problem.
>>
>> Should I expect any ill result from this?
>>
>
> It may break future upgrades, depending on what happens in the future.
>  Changing the index prefix length is /definitely/ not a fix as there is no
> possible way that the first 41 bytes of the UTF-8 data could be unique
> across all rows (as it would have to be for the data to exist in a
> properly-encoded UTF-8 in latin1 column MythTV database) but after
> condensing to actual UTF-8, the first 41 characters are not unique--as the
> first 41 characters encompass more data than the first 41 bytes, presuming
> there are /any/ multi-byte characters.  Therefore, it means the table is
> definitely corrupt--with some latin1 data (which should not be there) and
> some UTF-8 data, such that there exist dups after conversion.  Therefore,
> even after changing the prefix length, you still have corrupt data.
>
> A better approach is to get rid of the people.  They're only used by the
> People Search (which, chances are, you've never used before), and will be
> repopulated quite quickly from new listings.
>
> If http://www.gossamer-threads.com/lists/mythtv/users/399395#399395 (after
> correcting my copy/paste errors as mentioned at
> http://www.gossamer-threads.com/lists/mythtv/users/399413#399413 ) where
> the grep is run against either the "uncorrupt" backup or the original backup
> doesn't work, or if after the database upgrade is performed the data doesn't
> look right, your best bet is to wait a few days.  If you can't wait a few
> days and don't mind corrupting your data, it's your data, so it's your
> decision.  But, it's just a few days...
>
> Hi

I have also been hit by the corruption issue, when trying to go to 0.22 RC1,
currently running 0.21 with a database schema bversion 1215. I have been
though the wiki article on how to repair the database several times now this
morning, and have followed the various instructions by Mike to no avail.

the error I get (with verbose on) is

'mysql' --defaults-extra-file='/tmp/wPMWZ7oQgH' --host='localhost'
--user='mythtv' 'mythconverg'
ERROR 1062 (23000) at line 1915: Duplicate entry
'JÃÂÃâ¬Ã»ÃÂÃâ¬Ã»rÃÂÃâ¬Ã»' for key 2

I have searched for this string in phpmyadmin, and also ran a grep against
the initial backup and the repaired file and cannot find the string. Advice
on how to track this and fix would be appreciated.

My Mysql server config is

mysql  Ver 14.12 Distrib 5.0.32, for pc-linux-gnu (i486) using readline 5.2

Connection id:          55
Current database:       mythconverg
Current user:           mythtv at localhost
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         5.0.32-Debian_7etch1 Debian etch distribution
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    latin1
Conn.  characterset:    latin1
UNIX socket:            /var/run/mysqld/mysqld.sock
Uptime:                 48 min 29 sec

Thanks very much.

Gary
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mythtv.org/pipermail/mythtv-users/attachments/20091016/2fcc9ccf/attachment.htm>