Moodle
  1. Moodle
  2. MDL-14083

Course Shortname is unique in interface and code but not in database schema

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Minor Minor
    • Resolution: Won't Fix
    • Affects Version/s: 1.9
    • Fix Version/s: None
    • Component/s: Database SQL/XMLDB
    • Labels:
      None
    • Database:
      Any
    • Affected Branches:
      MOODLE_19_STABLE
    • Rank:
      12452

      Description

      If you attempt to create or edit a course so that the Course Shortname is the same as another course, the change is refused and it gives you a warning. Similarly backup and restore appears to modify the name of courses if the shortname is going to be a duplicate.

      However this constraint is not reflected in the database schema which has perfomance ramifications.

        Issue Links

          Activity

          Hide
          Howard Miller added a comment -

          Re-assigning this for Dave. I think it's more of a policy decision if this should be unique, as there is some potential pain in actually fixing it.

          Show
          Howard Miller added a comment - Re-assigning this for Dave. I think it's more of a policy decision if this should be unique, as there is some potential pain in actually fixing it.
          Hide
          Eloy Lafuente (stronk7) added a comment -

          Hi David and Howard,

          there is a cause for this and it's that the uniqueness cannot be guaranteed because fields can contain empty strings and those break uniqueness.

          This is caused by a historic problem in the DB layer for old MySQL versions and since then we have all the varchar(xxx) fields with a default '' instead of using proper NULLs.

          So, something like that:

          create table test (
          id bigint(10) not null primary key auto_increment,
          shortname varchar(100) not null default '',
          fullname varchar(255) not null default '');

          create unique index test_uix on test (shortname);

          insert into test (fullname) values ('One fullname');

          insert into test (fullname) values ('Another fullname');

          simply breaks, with duplicate key found error.

          This is more noticeable with the idnumber field (MDL-14084) because it's optional in the course configuration page, but internals are the same for both fields.

          Until we switch to proper NULLs in those fields (unique indexes use to ignore NULLs so it's safe to have them in more than one record), we only can make application checks, instead of DB checks.

          About performance... well... I would measure it as really minor...

          So I'm going to close this as won't fix, due to the reasons above. Feel free to reopen, comment or whatever.

          Thanks and ciao

          Show
          Eloy Lafuente (stronk7) added a comment - Hi David and Howard, there is a cause for this and it's that the uniqueness cannot be guaranteed because fields can contain empty strings and those break uniqueness. This is caused by a historic problem in the DB layer for old MySQL versions and since then we have all the varchar(xxx) fields with a default '' instead of using proper NULLs. So, something like that: create table test ( id bigint(10) not null primary key auto_increment, shortname varchar(100) not null default '', fullname varchar(255) not null default ''); create unique index test_uix on test (shortname); insert into test (fullname) values ('One fullname'); insert into test (fullname) values ('Another fullname'); simply breaks, with duplicate key found error. This is more noticeable with the idnumber field ( MDL-14084 ) because it's optional in the course configuration page, but internals are the same for both fields. Until we switch to proper NULLs in those fields (unique indexes use to ignore NULLs so it's safe to have them in more than one record), we only can make application checks, instead of DB checks. About performance... well... I would measure it as really minor... So I'm going to close this as won't fix, due to the reasons above. Feel free to reopen, comment or whatever. Thanks and ciao
          Hide
          David Scotson added a comment -

          Is there an existing bug for moving all such "unique but not enforced in the database yet" columns to be unique and use nulls? I'm assuming there are more than just these two. It would be good to have all such instances documented somewhere (maybe in XMLDB?) until they're fixed.

          Show
          David Scotson added a comment - Is there an existing bug for moving all such "unique but not enforced in the database yet" columns to be unique and use nulls? I'm assuming there are more than just these two. It would be good to have all such instances documented somewhere (maybe in XMLDB?) until they're fixed.

            People

            • Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: