[mythtv-users] Default character set

[Michael T. Dean]

On 11/14/2008 02:11 AM, Jan Ceuleers wrote:
> Prompted by Mike's statement about the need for the character set in the 
> mythconverg to be correctly set for a future upgrade to 0.22 to work I 
> thought I'd ask some questions.
>
> Firstly, how can we check whether the character set is correctly set in 
> our databases?
>
> I did the following on mine to check the default character set:
>
> mysql> show create database mythconverg;
> +-------------+------------------------------------------------------------------------+
> | Database    | Create Database 
>                 |
> +-------------+------------------------------------------------------------------------+
> | mythconverg | CREATE DATABASE `mythconverg` /*!40100 DEFAULT CHARACTER 
> SET latin1 */ |
> +-------------+------------------------------------------------------------------------+
> 1 row in set (0.00 sec)
>
> I then ran "show create table" on each table that is shown in the output 
> of "show tables" and verified that the default character set is also 
> latin1 for all tables.
>
> Is that enough?
>   

It sounds like your schema is fine, so you're probably in a good place 
for the upgrade to 0.22, but even if that's not the 
case--realistically--what's the benefit of finding out that your data is 
broken /now/ over finding it out when you upgrade 0.22?

Sure, you could drop your database now and start over now so that you'll 
have a few extra months (just a random time, not a promise or a guess) 
of data as opposed to starting over when 0.22 is released, but then 
again, it's possible that other people will have issues when upgrading 
and some will take the time to write scripts or programs to convert the 
data in a way that will allow you to restore most/all of the data you 
currently have.

While I wasn't trying to scare users into thinking that they won't be 
able to upgrade their data to 0.22, I /was/ trying to scare users into 
properly configuring their databases when doing restores or when setting 
up Myth for the first time.  For the most part, the people who will have 
issues will be those who have hacked their database schemas (i.e. those 
who did things like http://www.mythtv.org/wiki/index.php/Utf8_Text_in_OSD ).

> I guess what I'm asking is whether it would be useful to try and 
> diagnose any charset-related issues in an existing database before 
> trying to upgrade it. Are any such issues in fact already being detected 
> and perhaps even repaired by optimize_mythdb.pl ?

No.  The optimize_mythdb.pl script simply checks the database files for 
corruption (not the data or data format) and analyzes the tables (making 
queries faster) and optimizes the data files (getting rid of old deleted 
garbage--similar to compressing a mail folder).  Anything more would 
require a custom tool that would have to change as often as the MythTV 
database schema and data formats (way too often to be maintainable since 
the devs have plenty of stuff to do for Myth without that).

And, it turns out that MythTV is the best possible tool for detecting 
broken databases.  :)  If things don't work, users look into the issues, 
then eventually may determine that there's a problem with their 
data/database schema/...

Mike