[mythtv-users] Database update schema error with trunk utf8
Michael T. Dean
mtdean at thirdcontact.com
Wed Dec 17 04:11:37 UTC 2008
On 12/16/2008 08:21 PM, freedenizen wrote:
> On Tue, Dec 16, 2008 at 4:19 PM, Michael T. Dean wrote:
>> What distro are you running? Does the distro do stuff to help set up
>> your database for you?
> I am using gentoo, but I have never installed myth through gentoo,
> always compiled from source. mysql was install through gentoo with
> the following use flags:
> [ebuild R ] dev-db/mysql-5.0.70-r1 USE="berkdb perl ssl
> -big-tables -cluster -debug -embedded -extraengine -latin1
> -max-idx-128 -minimal (-selinux) -static"
And that probably explains why it's broken--your MySQL server is
Any system that handles MythTV data that was created with a version of
MythTV that uses database schema version 1215 or before /must/ have
support for latin1 in the MySQL server.
(Based on my /extremely/ limited knowledge of Gentoo/emerge/use flags,
it seems that) Since you emerged MySQL with the "-latin1" use flag, you
disabled latin1 support. You can verify that you're lacking latin1
SHOW CHARSET LIKE 'latin1%';
if you don't get at least 1 row, you're lacking latin1 support.
If you don't have latin1 support, when you created your database, you
got a broken database schema (that probably used utf8 for the character
set and may have even used a case sensitive collation). That means that
the conversion from the old format to the new format could not proceed
because the database schema was /not/ in the old format--which would
have been a database with tables having latin1 as the default character
set and columns that MySQL believes are latin1 data.
So, most likely MySQL was doing conversions it shouldn't have been doing
and your data got corrupted. At that point, Myth can't convert the data
properly because it's not the data it should be.
I don't know exactly what effect the lack of latin1 support would have
on other tables, as I felt that removing the latin1 charset (which
requires destroying the MySQL information_schema database and
re-initializing the mysql server) was more work than it's worth
(especially since I then have to undo/fix it again). So, at this point,
I'd say the data in your database /may/ be suspect--even in tables other
than oldprogram and people including some important tables. (I've
already mentioned that oldprogram and people aren't that important, but
I have no idea what effect the lack of latin1 support would have on
other, important tables.)
If I'm correct about -latin1 disabling latin1 support, is that a
standard flag used on Gentoo systems? If so, you're likely not the only
one who will experience this problem (and I'd now guess that the users
commenting on #5070 had the exact same problem).
More information about the mythtv-users