Database schema upgrade error from 1216 to 1217.

[nlabadie@…]

I just updated MythTV to the latest trunk SVN from the trunk version a few weeks ago. I'm receiving a database error when attempting to run mythbackend and it tries to update the schema from 1216 to 1217. I've attached the output. I've also run optimize_mythdb.pl and it does not report any errors with the database. Here's the version from mythbackend:

Please include all output in bug reports.
MythTV Version   : 16842
MythTV Branch    : trunk
Library API      : 0.22.20080320-2
Network Protocol : 40
Options compiled in:
 linux profile using_oss using_alsa using_arts using_jack using_backend using_frontend using_hdhomerun using_iptv using_ivtv using_lirc using_opengl_vsync using_v4l using_x11 using_xrandr using_xv using_xvmc using_bindings_perl using_bindings_python using_opengl using_ffmpeg_threads using_live

[nlabadie@…]

Error given during schema update from 1216 to 1217.

[nlabadie@…]

Just FYI, here's the actual error:

2008-03-30 10:45:29.822 DB Error (Performing database upgrade): 
Query was: ALTER TABLE oldprogram  DEFAULT CHARACTER SET default,  MODIFY oldtitle varchar(128) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL default ''; 
Error was: Driver error was [2/1062]:
QMYSQL: Unable to execute query
Database error was:
Duplicate entry 'Pok' for key 1

[anonymous]

Also forgot to include the MySQL version:

mysql  Ver 14.12 Distrib 5.0.56, for pc-linux-gnu (x86_64) using readline 5.2

[Janne Grunau]

I don't think this is mythtv fault. Can you please attach the output of

SELECT HEX(oldtitle) FROM oldrecorded WHERE oldtitle LIKE "P_k";

[nlabadie@…]

Hi Janne,

I get the following when attempting to run the query:

ERROR 1054 (42S22): Unknown column 'oldtitle' in 'field list'

Here's the list of tables:

+--------------------------------+
| Tables_in_mythconverg          |
+--------------------------------+
| archiveitems                   | 
| callsignnetworkmap             | 
| capturecard                    | 
| cardinput                      | 
| channel                        | 
| codecparams                    | 
| credits                        | 
| customexample                  | 
| diseqc_config                  | 
| diseqc_tree                    | 
| displayprofilegroups           | 
| displayprofiles                | 
| dtv_multiplex                  | 
| dtv_privatetypes               | 
| dvdbookmark                    | 
| dvdinput                       | 
| dvdtranscode                   | 
| eit_cache                      | 
| favorites                      | 
| filemarkup                     | 
| gallerymetadata                | 
| gamemetadata                   | 
| gameplayers                    | 
| housekeeping                   | 
| inputgroup                     | 
| inuseprograms                  | 
| jobqueue                       | 
| jumppoints                     | 
| keybindings                    | 
| keyword                        | 
| movies_movies                  | 
| movies_showtimes               | 
| movies_theaters                | 
| music_albumart                 | 
| music_albums                   | 
| music_artists                  | 
| music_directories              | 
| music_genres                   | 
| music_playlists                | 
| music_smartplaylist_categories | 
| music_smartplaylist_items      | 
| music_smartplaylists           | 
| music_songs                    | 
| music_stats                    | 
| musicmetadata                  | 
| musicplaylist                  | 
| mythlog                        | 
| mythweb_sessions               | 
| netflix                        | 
| networkiconmap                 | 
| oldfind                        | 
| oldprogram                     | 
| oldrecorded                    | 
| people                         | 
| phonecallhistory               | 
| phonedirectory                 | 
| pidcache                       | 
| playgroup                      | 
| powerpriority                  | 
| profilegroups                  | 
| program                        | 
| programgenres                  | 
| programrating                  | 
| recgrouppassword               | 
| record                         | 
| recorded                       | 
| recordedcredits                | 
| recordedfile                   | 
| recordedmarkup                 | 
| recordedprogram                | 
| recordedrating                 | 
| recordedseek                   | 
| recordingprofiles              | 
| recordmatch                    | 
| romdb                          | 
| schemalock                     | 
| settings                       | 
| storagegroup                   | 
| tvchain                        | 
| upnpmedia                      | 
| videocast                      | 
| videocategory                  | 
| videocountry                   | 
| videogenre                     | 
| videometadata                  | 
| videometadatacast              | 
| videometadatacountry           | 
| videometadatagenre             | 
| videosource                    | 
| videotypes                     | 
| weatherdatalayout              | 
| weatherscreens                 | 
| weathersourcesettings          | 
| websites                       | 
+--------------------------------+

[Janne Grunau]

sorry, wrong table. it's oldprogram

SELECT HEX(oldtitle) FROM oldprogram WHERE oldtitle LIKE "P_k";

[nlabadie@…]

Hi Janne,

Here's the output:

mysql> SELECT HEX(oldtitle) FROM oldprogram WHERE oldtitle LIKE "P_k";
Empty set (0.03 sec)

mysql> 

[Janne Grunau]

can you please try to find the too values resulting in the duplicate key.

try

"P__k" or "P%k"

[anonymous]

I didn't get any output from "PK", but "P%k" seemed to work:

mysql> SELECT HEX(oldtitle) FROM oldprogram WHERE oldtitle LIKE "P%k";
+----------------------------------------------------------------------------------------+
| HEX(oldtitle)                                                                          |
+----------------------------------------------------------------------------------------+
| 502E532E2049204C6F766520596F753A2048424F204669727374204C6F6F6B                         | 
| 5061726B                                                                               | 
| 5061747269636B                                                                         | 
| 5061796261636B                                                                         | 
| 506179636865636B                                                                       | 
| 5065656B                                                                               | 
| 50656F706C652057696C6C2054616C6B                                                       | 
| 50657465722C2042657468616E7920262052756675733A20537069726974206F6620576F6F6473746F636B | 
| 506974636820426C61636B                                                                 | 
| 506F696E7420426C616E6B                                                                 | 
| 507265686973746F726963205061726B                                                       | 
| 507265737320596F7572204C75636B                                                         | 
| 50726574747920696E2050696E6B                                                           | 
| 5072696D6172696C792050696E6B                                                           | 
| 507269736F6E20427265616B                                                               | 
| 50757265204C75636B                                                                     | 
+----------------------------------------------------------------------------------------+

[nlabadie@…]

Just FYI, I tried removing those entries and received the exact same error.

[danielk]

Janne, I'm seeing the same problem. But I think my maybe mysql is also pretty old for MythTV.

mythtv@cuy ~ $ mythbackend
2008-03-30 16:06:27.017 Using runtime prefix = /usr/local, libdir = /usr/local/lib
2008-03-30 16:06:27.018 Empty LocalHostName.
2008-03-30 16:06:27.018 Using localhost value of cuy
2008-03-30 16:06:27.031 New DB connection, total: 1
2008-03-30 16:06:27.036 Connected to database 'mythconverg' at host: localhost
2008-03-30 16:06:27.037 Closing DB connection named 'DBManager0'
2008-03-30 16:06:27.037 Connected to database 'mythconverg' at host: localhost
2008-03-30 16:06:27.038 New DB connection, total: 2
2008-03-30 16:06:27.038 Connected to database 'mythconverg' at host: localhost
2008-03-30 16:06:27.040 Current Schema Version: 1216
2008-03-30 16:06:27.043 New DB connection, total: 3
2008-03-30 16:06:27.043 Connected to database 'mythconverg' at host: localhost
2008-03-30 16:06:27.045 Backing up database to file: /video/testing/mythconverg-1216-20080330160627.sql
2008-03-30 16:06:29.959 Compressing database backup file.
2008-03-30 16:06:35.090 Database Backup filename: /video/testing/mythconverg-1216-20080330160627.sql.gz
2008-03-30 16:06:35.090 Database Backup complete.

Warning: MythTV wants to upgrade your database schema, from 1216 to 1219.

If your system becomes unstable, a database backup is located in /video/testing/mythconverg-1216-20080330160627.sql.gz


Shall I upgrade this database? [yes]
2008-03-30 16:06:39.415 Newest Schema Version : 1219
2008-03-30 16:06:39.416 Upgrading to schema version 1217
2008-03-30 16:06:41.946 DB Error (Performing database upgrade):
Query was: ALTER TABLE oldprogram  DEFAULT CHARACTER SET default,  MODIFY oldtitle varchar(128) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL default '';
Error was: Driver error was [2/1062]:
QMYSQL: Unable to execute query
Database error was:
Duplicate entry 'As' for key 1

new version: 1217
2008-03-30 16:06:41.946 Database Schema upgrade FAILED, unlocking.
2008-03-30 16:06:41.946 Couldn't upgrade database to new schema
QSqlDatabasePrivate::removeDatabase: connection 'DBManager0' is still in use, all queries will cease to work.
QSqlDatabasePrivate::removeDatabase: connection 'DBManager1' is still in use, all queries will cease to work.
QSqlDatabasePrivate::removeDatabase: connection 'DBManager2' is still in use, all queries will cease to work.
mythtv@cuy ~ $ mysql -u mythtv -pmythtv mythconverg
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 5.0.44 Gentoo Linux mysql-5.0.44-r1

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SELECT HEX(oldtitle) from oldprogram WHERE oldtitle LIKE "As";
Empty set (0.00 sec)

mysql>

[Janne Grunau]

Replying to nlabadie@gmail.com:

Just FYI, I tried removing those entries and received the exact same error.

yeah, the 'o' doesn't seem to be the problem. A dump of the oldprogram table which exhibits the problem would be useful. I supsect mysql does something like normalizing the utf8 strings.

[Janne Grunau]

I don't think that the mysql version plays a role in this error.

[nlabadie@…]

Another FYI, I emptied the 'oldprogram' and 'people' tables and the upgrade was able to complete.

[Miwer]

Performed part of 1215 and 1216 changes manually - results enclosed.

[Miwer]

I just tried performing the changes in 1215 and 1216 manually for a table with international chars...

It seems, that converting the columns to binary works OK, but when converting the binaries to varchar, it truncates the string when it hits a special char, and thus in some cases (especially for oldprogram and people tables) results in a duplicate key error.

Se my results in the attached files...

[Miwer]

... continued from my previous post ...

What is the reason for first converting the strings to varbinary ?

I just tried only applying the changes in 1216 (converting varchar directly from latin1 to utf8 - no conversion to varbinary first) and it worked fine...

My entries are still correct, and Hex has changed into this:

mysql> select hex(title) from recorded;
+----------------------------------------------------------------------------------------+
| hex(title)                                                                             |
+----------------------------------------------------------------------------------------+
| 416668C3A66E67696720616620736578                                                       |
| 426174746C65737461722047616C616374696361                                               |
| 426174746C65737461722047616C616374696361202D2064656C32                                 |
| 42657665726C792048696C6C7320393032313020436C6173736963                                 |
| 466F72204C6F7665206F72204D6F6E6579                                                     |
| 46524120534B524F542054494C20534C4F54202D20424544524520424F4C49472C204245445245204C4956 |
| 476F27206D6F7267656E2044616E6D61726B                                                   |
| 496E73696465205468652053696D70736F6E73                                                 |
| 4B6F6E74616E74                                                                         |
| 4C616C616E64696120747572                                                               |
| 4F7065726174696F6E2058202D2067696674656E2069206472696B6B6576616E646574                 |
| 4F7065726174696F6E2058202D206A616774656E2070C3A5206E65747376696E646C65726E65           |
| 4F7065726174696F6E2058202D206A616774656E2070C3A5206E65747376696E646C65726E65           |
| 4F7065726174696F6E2058202D206A616774656E2070C3A5206E65747376696E646C65726E65           |
| 526567696F6E616C70726F6772616D                                                         |
| 53757065726E6F7661                                                                     |
| 5465726D696E61746F7220333A2052697365206F6620746865204D616368696E6573                   |
| 546865205468726565204D75736B657465657273                                               |
| 546F70204D6F64656C2035                                                                 |
| 54562032205465646479                                                                   |
| 582D4D656E                                                                             |
+----------------------------------------------------------------------------------------+
21 rows in set (0.00 sec)

[henrik.lilljebjorn@…]

This bug affects me too.

When upgrading from 0.21 to SVN trunk (17449) the schema update fails with a "duplicate entry"-error similar to the original report. Emptying the 'oldprogram' and 'people' tables enables the update too finish but all strings in the updated mysql tables have now been truncated before the first non-ascii character (such as the swedish characters åäö).

By the way Miwer, what mysql command do you use for manually converting the varchar directly from latin1 to utf8 without converting to varbinary first? (I guess this reveals that mysql-skills are very limited)

[anonymous]

After studying this issue a bit more I have come to the same conclusion as Miwer, the update seems to handle international characters better if not converting to varbinary in the first step. I have attached a very rough patch simply removing the 1215-1216 db version upgrade that performs the conversion to varbinary. The whole db update worked great for me with this patch, but simply removing a db version upgrade seems like an ugly hack. It would not handle an upgrade from a database stuck at version 1215 for example. Is there a better way to do this?

[Janne Grunau]

Replying to Miwer:

I just tried performing the changes in 1215 and 1216 manually for a table with international chars...

It seems, that converting the columns to binary works OK, but when converting the binaries to varchar, it truncates the string when it hits a special char, and thus in some cases (especially for oldprogram and people tables) results in a duplicate key error.

Your problem is not a mythtv bug, you have changed the pre-Qt4 database to utf8 instead of only supported latin1. You're on your own.

If it is a valid utf8 schema the only needed change is to increase DB schema variable to 1217. No guarantees though.

[Janne Grunau]

Replying to anonymous:

After studying this issue a bit more I have come to the same conclusion as Miwer, the update seems to handle international characters better if not converting to varbinary in the first step.

wrong, a proper mythtv DB would be totally broken if that step is ommitted.

I have attached a very rough patch simply removing the 1215-1216 db version upgrade that performs the conversion to varbinary. The whole db update worked great for me with this patch, but simply removing a db version upgrade seems like an ugly hack.

I've deleted the patch since it would cause severe damage on a correct mythtv DB. If the char set was already proper utf8 (against all mythtv setup instructions), changing the DB schema version manually to 1217 is the only needed step.

[sphery <mtdean@…>]

Detect database corruption and stop DB upgrade before character set conversion (at 0.21-fixes DB schema).

[sphery <mtdean@…>]

The attached patch, mythtv-5070-detect_db_charset_corruption.patch , detects database corruption before performing the character set conversion and stops the database upgrade at the 0.21-fixes DB schema.

The users experiencing duplicate keys had misconfigured databases that caused the DB connection to use the utf8 character set rather than the latin1 character set that MythTV 0.21-fixes and below required, as described at http://www.mythtv.org/wiki/index.php/Fixing_Corrupt_Database_Encoding. Since the connection character set differed from the column character set, MySQL performed a conversion of all the UTF8 data MythTV sent to the DB into latin1 before storing it in the DB. Therefore, the data in the users' databases was latin1, not utf8, so when the DB upgrade attempted to interpret the data as UTF8, it found cases where the data was improperly encoded and truncated the data at the first invalid character (i.e. 8-bit latin1 characters), causing duplicate entries.

The patch only allows the DB upgrade to proceed past 0.21-fixes DB schema version if the data in people.name and oldprogram.oldtitle (2 columns which caused the most problems) can successfully be converted to UTF8 without any truncations (which are shown by MySQL as warnings). In the event that corruption is detected, the DB upgrade failes and users are directed to the wiki page http://www.mythtv.org/wiki/index.php/Fixing_Corrupt_Database_Encoding , which provides a means of fixing the corruption. The patch does not, however, fix the corruption.

I wrote the patch to loop over the tables on which to perform trial conversions, rather than on the list of SQL commands to execute because the approach allows for easily adding additional test conversions.

Some users above mentioned that skipping the 1215-1216 upgrade (conversion to varbinary) caused the upgrade to succeed. Note that this will only succeed for users whose data contain only latin1 characters. For users with misconfigured databases whose data contained non-latin1 characters, data was corrupted upon MySQL's conversion from utf8 to latin1 (i.e. the latin1 representation is wrong), so skipping the varbinary step will cause issues.

[Janne Grunau]

(In [21255]) check for charset conversion errors before DB update 1215. Fixes #5070

the database charset handling was fragile before the change to UTF-8. We abused latin1 as tranparent byte storage of UTF-8 string. If for example the default connection charset was set to something else than latin1 charset conversions in the mysql server would have stored data, our charset updates 1215 and 1216 will destroy data sometimes resulting in duplicate keys. This patch checks if the conversions succeed in temporary tables before doing the real update.

based on patch by: Micheal T Dean > mtdean thirdcontact com <