[mythtv-users] Possible bug in database schema upgrade (0.21 -> 0.22)

Lars Michael Jogbäck lmjogback at gmail.com
Thu Nov 5 22:27:46 UTC 2009


I've found problem when I was trying to upgrade my database

When I ran mythtv-setup, it spitted out the infamous "Database
corruption detected. Unable to proceed with database upgrade. (Table:
oldrecorded, Warnings: 157)"

After lots of debugging and database checks, I turned my attention to
what MythTV actually does in the database upgrade process and ran
those commands by myself in the mysql client.

mysql> CREATE TEMPORARY TABLE temp_oldrecorded SELECT * FROM oldrecorded;
Query OK, 9415 rows affected (0.07 sec)
Records: 9415  Duplicates: 0  Warnings: 0

- Ok, now problem here...

mysql> ALTER TABLE temp_oldrecorded  MODIFY description varbinary(255)
NOT NULL default '';
Query OK, 9415 rows affected, 3657 warnings (0.06 sec)
Records: 9415  Duplicates: 0  Warnings: 3657

- Many warnings, all because "description" is a field of type TEXT and
contain far longer strings than 255 chars in my database.

mysql> ALTER TABLE temp_oldrecorded MODIFY description char(255)
CHARACTER SET utf8 COLLATE utf8_bin NOT NULL default '';
Query OK, 9415 rows affected, 157 warnings (0.13 sec)
Records: 9415  Duplicates: 0  Warnings: 0

- Hmm, 157 warnings, I know that number :-) Let's look on a couple of those.

mysql> SHOW WARNINGS LIMIT 5;
+---------+------+--------------------------------------------------------------------+
| Level   | Code | Message
               |
+---------+------+--------------------------------------------------------------------+
| Warning | 1366 | Incorrect string value: '\xC3' for column
'description' at row 61  |
| Warning | 1366 | Incorrect string value: '\xC3' for column
'description' at row 68  |
| Warning | 1366 | Incorrect string value: '\xC3' for column
'description' at row 140 |
| Warning | 1366 | Incorrect string value: '\xC3' for column
'description' at row 141 |
| Warning | 1366 | Incorrect string value: '\xC3' for column
'description' at row 157 |
+---------+------+--------------------------------------------------------------------+
5 rows in set (0.00 sec)

After more carefully examining those it turns out, that when changing
the datatype of description from TEXT to VARBINARY(255) it got
truncated just between the two bytes in an existing utf8 character and
by that causing an false positive.

I've changed the dbcheck.cpp to not do the check on the description
field, and it did work for me.

Index: libs/libmythtv/dbcheck.cpp
===================================================================
--- libs/libmythtv/dbcheck.cpp  (revision 22742)
+++ libs/libmythtv/dbcheck.cpp  (working copy)
@@ -3609,10 +3609,10 @@
               "oldtitle",
               "oldtitle"},
             { "oldrecorded",
-              "title:subtitle:description",
+              "title:subtitle",
               "station, starttime, title"},
             { "recorded",
-              "title:subtitle:description",
+              "title:subtitle",
               ""},
             { "",
               "",

Best Regards,
/LM


More information about the mythtv-users mailing list