[mythtv-users] Database migration failure (0.21 to 0.22) temp_people table failure

[Michael T. Dean]

On 01/21/2010 01:47 PM, Kirby Bakken wrote:
> In trying to convert my myth 0.21 installation to myth 0.22, I had
> problems...  I finally found the cause.  I ran mythbackend -v database
> and the 'tail end' of output was this:
>
> ----------------------- start output -------------------------------
>
> 2010-01-21 09:09:11.805 MSqlQuery::exec("CREATE TEMPORARY TABLE
> temp_people SELECT * FROM people;")
> 2010-01-21 09:09:11.916 MSqlQuery::exec("ALTER TABLE temp_people 
> MODIFY name varbinary(255)            NOT NULL default '';")
> 2010-01-21 09:09:11.973 MSqlQuery::exec("ALTER TABLE temp_people  
> MODIFY name char(255)             CHARACTER SET utf8            
> COLLATE utf8_bin             NOT NULL default '';")
> 2010-01-21 09:09:12.301 MSqlQuery::exec("SHOW COUNT(*) WARNINGS;")
> 2010-01-21 09:09:12.302 MSqlQuery::exec("ALTER TABLE temp_people   ADD
> UNIQUE INDEX verify_unique ( name(41) );")
> QSqlQuery::exec: database not open
> 2010-01-21 09:09:12.674 DB Error (Index creation failed.):
> Query was:
>
> Driver error was [1/-1]:
> Driver not loaded
> Database error was:
> Driver not loaded
>
> ------------------------------ end output
> --------------------------------
>
> I then ran those commands one-at-a-time until I found this:
>
> ALTER TABLE temp_people   ADD UNIQUE INDEX verify_unique ( name(41) );
> ERROR 1062 (23000): Duplicate entry 'Ren' for key 'verify_unique'
>
> But then my knowledge of sql, how myth is setup ran out...  I ran a
> query on temp_people, and found that something like 8 duplicate names
> with 'Ren' in them.
> I ended up deleting most of the 'names' table, and got past this, but
> what IS this failure, and could it be avoided somehow within the 0.22
> schema update?

That is a failure during the test to see if your database is corrupt. 
Since it's failing, your database is corrupt.

See the page that should have been linked in the log output (after the
error you quoted).

http://www.mythtv.org/wiki/Fixing_Corrupt_Database_Encoding

Mike