[mythtv-users] sanity check or repairing DB for 0.22 upgrade.

[Michael T. Dean]

On 11/18/2009 09:39 PM, Alan Anderson wrote:
> On Wednesday 18 November 2009 18:20:56 Michael T. Dean wrote:
>   
>> On 11/18/2009 08:09 PM, Alan Anderson wrote:
>>     
>>> Sanity check please.
>>>
>>> I have one master backend  mythbackend version: 0.21.20080304-1 and three
>>> front ends.   I have had the same data base for 5 or more years. 
>>> Typically when I upgrade I backup /var/lib/mysql upgrade the version  of
>>> fedora and restore /var/lib/mysql.  So far it has always worked.  I have
>>> backups of mythconverg but never needed them.  I also run the script
>>> /usr/share/doc/mythtv-docs-0.21/contrib/optimize_mythdb.pl weekly.
>>>
>>>
>>> When I tried to install V0.22 I ran into the following scheme update
>>> issue.
>>>
>>> Query was:
>>> ALTER DATABASE mythconverg DEFAULT CHARACTER SET latin1;
>>> Driver error was [2/1283]:
>>> QMYSQL3: Unable to execute statement
>>> Database error was:
>>> Column 'filename' cannot be part of FULLTEXT index
>>>
>>> Every time I start a front end the error occurs.
>>>
>>>
>>> From my understanding this is issue where some UTF characters got into
>>> the data base when everything should be latin1.   I need to do a partial
>>> restore to fix this.  I need to fix the data base before I can upgrade to
>>> V0.22.
>>>       
>> That's something very different.  I'd bet you're running with MySQL
>> strict mode enabled (which is the default for MySQL on Windows)--which
>> will break Myth.
>>
>> http://www.gossamer-threads.com/lists/mythtv/dev/391418#391418
>> http://www.gossamer-threads.com/lists/mythtv/dev/391420#391420
>>
>> (and I'm 99% positive that even TRADITIONAL mode will break myth).
>>
>> If not, the problem is just the way you restored with binary data
>> files.  You should /always/ use a SQL-based mythconverg backup when
>> upgrading the MySQL server***.
>>
>> Also note that dropping a 0.21-fixes database data files on top of a
>> 0.22-fixes database schema's database files will break Myth.  You need
>> to ensure you DROP the 0.22-fixes database (or otherwise remove it
>> completely).
>>
>> Mike
>>
>>
>> ***Yeah, I said "mythconverg backup," thereby implicitly excluding
>> backups performed by MySQL database admins on work database systems.
>> Basically, you have to know what you're doing /and/ what MySQL changes
>> occured to reliably do binary-file restores.  There's no reason any
>> MythTV user should /ever/ be expected to do this, so just use SQL-based
>> backups.
> I am not trying to drop a 0.21 database on a 0.22 mythtv.  The current running 
> database was never touched by 0.22 mythtv.  
>
> I put 0.22 aside for now.  I am trying to do a partial restore of a 0.21 
> backup on a new 0.21 database.  
>
> If I can get this done then I'll try another 0.22 upgrade.
>
> I don't know how MS windows mysql strict mode setting would have been set I 
> only use Fedora core in mythtv (started FC2 current is FC11).
>
> I'll research it further to see if there is a way to identify  if strict mode 
> is set or not.  What mode does mythtv need?
>
> I don't see a sql-mode in the /etc/my.cnf file  so I'll keep looking

The point behind all of my rambling was:

a) You do not have any indication of charset corruption, so do /not/ do 
the fixes from the 
http://www.mythtv.org/wiki/Fixing_Corrupt_Database_Encoding page
b) You should try again (with /either/ 0.21-fixes or 0.22-fixes) using a 
database that was properly restored from a SQL-based backup of your 
0.21-fixes database using a full restore (not a 
partial-restore/lose-all-your-configuration approach).

All of the other stuff was just "things that might be have caused the 
breakage"--as your database itself was broken when you tried the upgrade 
to 0.22-fixes.

Note, also, that strict mode isn't just a Windows thing--it's just that 
it happens to be enabled by default on Windows, and most of the (sane) 
GNU/Linux distros seem to not mess with it/leave it at default/leave it off.

Mike