Opened 11 years ago

Closed 11 years ago

#5957 closed patch (fixed)

Preserve collation of people.name to prevent duplicate errors

Reported by: sphery <mtdean@…> Owned by: Janne Grunau
Priority: minor Milestone: unknown
Component: mythtv Version: 0.21
Severity: medium Keywords:
Cc: Ticket locked: no

Description

The 1226 database update changed the collation of the people.name field from utf8_bin (which is case-sensitive) to "default", where the database default is utf8_general_ci (which is case-insensitive). Therefore, for users who have been running trunk since the 1217 update (which set the collation to utf8_bin, for the first time making people names case sensitive), there's a good chance there will be duplicate entries differing only in case.

The attached patch modifies the 1226 update and the initial database creation code to maintain the utf8_bin collation for people.name. It also changes the column to "NOT NULL DEFAULT " (which is a default value of the empty string, which won't show in Trac because of wiki formatting), as it was before the 1226 update (see http://svn.mythtv.org/trac/browser/trunk/mythtv/libs/libmythtv/dbcheck.cpp?rev=19221#L3967 ).

If we want to make people.name case-insensitive, please let me know and I'll make a patch (it will require a patch like the 1225 update to preserve data for references in credits and recordedcredits). Currently, the fields, keyword.phrase, oldprogram.oldtitle, powerpriority.priorityname, recgrouppassword.recgroup, storagegroup.dirname (and after this patch, people.name) are the only utf8_bin fields in the database. Of those, the only one that looks like it should be case-sensitive is storagegroup.dirname. If we want to make the rest of them case-insensitive, I can do so in one patch.

Thanks to gregL in IRC for reporting the issue and getting the information from his database required to track down the issue.

Attachments (1)

mythtv-5957-preserve_collation_for_people_name.patch (1.0 KB) - added by sphery <mtdean@…> 11 years ago.

Download all attachments as: .zip

Change History (3)

Changed 11 years ago by sphery <mtdean@…>

comment:1 Changed 11 years ago by Janne Grunau

Owner: changed from Isaac Richards to Janne Grunau
Status: newassigned

comment:2 Changed 11 years ago by Janne Grunau

Resolution: fixed
Status: assignedclosed

(In [19224]) Revert unintended COLLATE change to utf8_general_ci of people.name in [19221]

people.name might contain duplicates only differing in case. Fixes #5957 patch by: Micheal T Dean < mtdean thirdcontact com >

Note: See TracTickets for help on using tickets.