The important difference between the output of a misconfigured versus a properly-configured server is that some or all of the character sets in a misconfigured database will be utf8. For a MythTV 0.21-fixes or below system, the character sets will be latin1.

+

The important difference between the output of a misconfigured versus a properly-configured server is that some or all of the character sets in a misconfigured database will be utf8. For a properly-configured MythTV 0.21-fixes or below system, the character sets will be latin1.

Note that once upgraded to post-r16789 SVN trunk (or 0.22 or above), the output of the status command will differ--namely, the <code>Db characterset</code> will be <code>utf8</code> in a properly-configured system. The value of the others is not important on post-r16789 SVN trunk/0.22+ versions of MythTV.

Note that once upgraded to post-r16789 SVN trunk (or 0.22 or above), the output of the status command will differ--namely, the <code>Db characterset</code> will be <code>utf8</code> in a properly-configured system. The value of the others is not important on post-r16789 SVN trunk/0.22+ versions of MythTV.

Background

In 0.21-fixes and below, MythTV required that the database be set up such that MySQL thought that text columns were encoded using the latin1 encoding and that all communications with the database be done using the latin1 encoding. Some distros (notably, Gentoo) and some users have misconfigured their MySQL servers with the effect that all 8-bit characters are corrupted in the database.

The end result of this misconfiguration is that any user with a misconfigured database can not successfully upgrade to post-r16789 SVN trunk (which means that upgrades to 0.22, when released, will also fail) and any non-Latin characters in data will be improperly displayed, even in MythTV 0.21-fixes and below. Before attempting to upgrade to post-r16789 SVN trunk or to 0.22, those users who have been running MythTV against a misconfigured database must fix the data encoding as described below. Those users running MythTV 0.21-fixes or below with non-Latin characters in their data should probably fix the data encoding as soon as possible.

When do I need to fix my database configuration?

You must fix your database configuration before upgrading to post-r16789 SVN trunk or to 0.22. If you plan to run MythTV 0.21-fixes, you may continue to use your system without fixing your database configuration. However, it doesn't hurt to fix the database configuration, now. If you decide not to fix your configuration now, make sure you remember to do so before upgrading MythTV in the future.

Note also that those users whose data contains non-Latin characters will see character/data corruption even in MythTV 0.21-fixes until they fix their database configurations. Those who notice character corruption should fix their database configurations as soon as possible.

If the MySQL server you're using to run the mythconverg database is also used for other databases, you should wait until you upgrade to current trunk or 0.22 to fix your database, as the other databases may require conversions to run with a latin1 configuration (or just may not work with a latin1 configuration), and trunk/0.22 do not require any specific encoding configuration to work properly. At that point, you'll want to backup all your databases, then drop all your databases, then fix the mythconverg database (including upgrading it to 0.22) as described below, then backup the (good, 0.22) mythconverg database. Once you've got a good 0.22 mythconverg database, drop the mythconverg database, then reconfigure your MySQL server as it was previously configured, and finally, restore all your databases, including the good 0.22 mythconverg database.

Note on MythTV 0.21-fixes and below character encoding

Note that the use of the latin1 encoding does not prevent MythTV from using non-Latin characters. Instead, MythTV actually writes UTF-8 characters into the column and does all required conversions when reading/writing database data. The fact that the database uses latin1 encoding simply means that the MySQL server is unable to understand the data in the database--i.e. any 8-bit or multi-byte characters will be encoded in such a way that the MySQL server or any mysql client will see gibberish rather than the expected character. However, MythTV programs (including mythfrontend's GUI and OSD and MythWeb) will be able to interpret the data properly, meaning the user will see the proper characters through MythTV programs.

For example, in a misconfigured database, the character, "å", would appear as shown when using MySQL programs, such as the mysql command-line client. When properly encoded for MythTV, the character would be shown as, "Ã¥", by MySQL clients. Similarly, the character, "á", would be represented as "Ã¡" in a properly-encoded database. Note that--properly encoded--these characters (which are both represented as 2-byte characters in UTF-8) seem to MySQL programs to be 2 distinct characters. Similarly, 3-byte UTF-8 characters would be represented as 3 characters when viewed using MySQL programs.

Determining if your database is misconfigured

To determine if your database is misconfigured, execute the following command from the shell (fixing the path to the mysql command-line client and/or the username/database name as required) and type in the password when prompted:

echo 'status;' | mysql -umythtv -p mythconverg

Note that the default USE flags for configuring MySQL on Gentoo-based systems include the -latin1 flag. This flag configures the MySQL server incorrectly for MythTV. While Gentoo users may check which USE flags were specified for MySQL, they should also verify their current configuration is incorrect by querying the server status, as shown above, and comparing results with the examples below.

Recognizing the difference

The important difference between the output of a misconfigured versus a properly-configured server is that some or all of the character sets in a misconfigured database will be utf8. For a properly-configured MythTV 0.21-fixes or below system, the character sets will be latin1.

Note that once upgraded to post-r16789 SVN trunk (or 0.22 or above), the output of the status command will differ--namely, the Db characterset will be utf8 in a properly-configured system. The value of the others is not important on post-r16789 SVN trunk/0.22+ versions of MythTV.

Fixing the database corruption

Note that the approach described here will only work on a database that was corrupted due to a misconfigured MySQL server, as described above. If you have any other type of corruption, applying the procedure outlined below will further corrupt your database--likely breaking it.

Also, this approach assumes that all the data in your database is corrupted. If you have, over time, run MythTV systems using multiple differently-configured systems (i.e. a mix of different distros)--primarily, if you've run the mythconverg database on differently-configured MySQL servers--then your database is likely only partially-corrupt, and fixing only the corrupt portions will require a significant amount of manual work. If this is the case, your best bet is likely to do a full backup of your 0.21-fixes database, then drop the database, ensure your database server is properly configured for 0.21-fixes, create a new database by running the mc.sql script, then create a new schema by running the 0.21-fixes version of mythtv-setup or mythbackend, then do a partial restore. Finally, create a backup of the partially-restored database and upgrade using the partial 0.21-fixes database. If you take this approach, do not perform the steps listed below for fixing your database.

Backing up the MythTV database

To fix the broken encoding, you will first need to create a database backup, as described by Database Backup and Restore. Note that this is not an optional step that's recommended solely for safety--the backup will actually be used to "uncorrupt" the data.

Changing the backup file

The backup file created above was encoded using UTF-8 encoding. However, restoring it, unchanged, will restore an exact copy of the corrupt database. Instead, we will modify the backup to "re-encode" the data into the format that MythTV expects. To do so, execute the following command from the shell while in the directory that contains the backup created above (change the backup filename, as appropriate):

If your backup file is not gzip'ed (if it is uncompressed), use cat rather than zcat.

Changing the MySQL server configuration

Then, once you have successfully created a database backup and modified it to "uncorrupt" your database, you will need to reconfigure your MySQL server such that it does not specify a database server default character set. In so doing, rather than forcing all database clients to use utf8 encoding for all communications (even though a program, such as MythTV, may have been written to use a different encoding), you will have configured your server such that, by default, clients use the character encoding of the database to which they connect (but may still request a specific character encoding). Therefore, changing this should not break other programs using other databases on the server; however, verifying this is up to the user. Note, also, that the original configuration--the one that won't work with MythTV--may have been causing data corruption for data used by the other applications the same way it did for MythTV applications, and changing the configuration may suddenly make the corruption visible.

The Gentoo database configuration file, /etc/mysql/my.cnf, should be similar to the one shown below. To fix the server configuration to work with MythTV 0.21-fixes, we must remove the lines starting with, "character-set-server" and "default-character-set". You can do so by executing the following commands as root (or with root permissions, using sudo):

These commands will preserve the original my.cnf (as /etc/mysql/my.cnf-orig ) and create a copy at /etc/mysql/my.cnf with the incorrect lines commented.

Once this is done, restart the mysql server.

Clearing the corrupt database

For the following commands, you will need to use the mysql command-line client. The MySQL user you choose to use must have sufficient permissions to perform the commands. The MySQL root user will have sufficient permissions. You can log in to the database as the root user by executing the following command from the shell (change the database name--here, mythconverg--if appropriate):

mysql -uroot -p mythconverg

Once logged in, drop the corrupt the database (change the database name, if appropriate):

DROP DATABASE IF EXISTS mythconverg;

Then, create a new empty database (change the database name, if appropriate):

CREATE DATABASE IF NOT EXISTS mythconverg;

Then, set the appropriate character set (change the database name, if appropriate):

ALTER DATABASE mythconverg DEFAULT CHARACTER SET latin1;

Finally, exit the mysql client:

quit

Restoring the backup

Restore the backup, making sure to specify the filename using --filename mythconverg-to_uncorrupt.sql and, if required, the directory (using --directory /path/to/backup). During this process, the data stored inside the database will be converted to the format required by MythTV.

Backing up the uncorrupted database

Now that the database server and database data have been fixed, you should back up your database again, as described by Database Backup and Restore. Once complete, you should ensure you never restore a backup created before this time (a backup of the corrupt database).

What to do if problems occur

If problems occur while attempting to follow the above steps, please post a message to the [mailing list] explaining your configuration (distro/output of MySQL status), what part failed, and the error messages you received (please copy/paste the messages to ensure all relevant info is provided). You may also get a faster response if you mention my nick (sphery) in the message.

In the meantime, you may "revert" to the invalid configuration you were using previously by restoring your /etc/mysql/my.cnf-orig and the backup file you made at the beginning of the process (i.e. the one from before the zcat/sed script above (in the example, the one called, mythconverg-1214-20081217145744.sql.gz).

After Upgrading to current SVN trunk/0.22

Once you've upgraded MythTV to current SVN trunk or 0.22 (when released), you may restore the old mysql configuration file, if desired.