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

[Gary Dawes]

2009/10/16 Gary Dawes <gary.dawes at gmail.com>

>
>
> 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
>

Hmm, been spending far too much time on this today.

I have created a fresh install of Knopppmyth R5.5 with Mythtv 0.21 installed
on a VM. Checked the mysql config which was OK. Created a clean database,
and ran mythtv-setup to populate the tables, and restored the existing data
OK.

I then backed up the data, and restored ok into the box with 0.22 installed
after dropping the existing database. The restore worked, but failed on
schema upgrade using mythtv-setup, with the "DB charset conversion failed"
error.

I then tried all the above again, running the sed command to change the
encoding. Again, the restore worked, but the schema upgrade again failed
with the same error.

I then dropped the database again on the 0.21 box, and recreated. ran
mythtv-setup to create a empty schema, and then performed a partial-restore
into it. Checked for errors, and then backed that DB up. Once again a
restore onto the 0.22 box with a fresh empty database was sucessful, but
once again the schema upgrade in mythtv-setup failed with the same error.

Any ideas? I really really do not want to start a database from scratch as
there is 6 years worth of data in there.

Thanks

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