Uploaded image for project: 'Moodle'
  1. Moodle
  2. MDL-63319

Renaming a column in MySQL or MSSQL could break on a reserved word

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 3.3.7, 3.4.4, 3.5.1, 3.6
    • Fix Version/s: 3.4.6, 3.5.3
    • Component/s: Database SQL/XMLDB
    • Labels:
    • Database:
      MySQL, Microsoft SQL
    • Testing Instructions:
      Hide
      1. Run at least the DDL tests on the following databases (Using docker would be extremely helpful):
        1. MySQL 8.0.11+
        2. MariaDB 10.2.6+ and 10.3.5+

          vendor/bin/phpunit core_ddl_testcase lib/ddl/tests/ddl_test.php
          

      2. Confirm that these tests pass.

      Note: Other databases will be covered by CI (PostgreSQL, MSSQL, Oracle, MySQL 5.7.x, MariaDB 10.1.x)

      Show
      Run at least the DDL tests on the following databases (Using docker would be extremely helpful): MySQL 8.0.11+ MariaDB 10.2.6+ and 10.3.5+ vendor/bin/phpunit core_ddl_testcase lib/ddl/tests/ddl_test.php Confirm that these tests pass. Note: Other databases will be covered by CI (PostgreSQL, MSSQL, Oracle, MySQL 5.7.x, MariaDB 10.1.x)
    • Difficulty:
      Easy
    • Affected Branches:
      MOODLE_33_STABLE, MOODLE_34_STABLE, MOODLE_35_STABLE, MOODLE_36_STABLE
    • Fixed Branches:
      MOODLE_34_STABLE, MOODLE_35_STABLE
    • Pull from Repository:
    • Pull Master Branch:
      m36_MDL-63319_MySQL_MSSQL_Rename_Field_Reserved_Word

      Description

      On reading CONTRIB-7420, where there was the need to rename a column named w/ a reserved word, I found that sql_generator::getRenameFieldSQL properly encodes the column name, while the overridden method in MySQL doesn't take care of it.
      Same broken result when falling into the upgrade step coded for MDL-60793, as already described in the Community some time ago: https://moodle.org/mod/forum/discuss.php?d=373009.

      Again, same end effect appears in MSSQL - there are no public posts about it -, here since the syntax of the stored procedure doesn't handle quotes as coded in getEncQuoted() for general usage.

      Shortly:

      • on MySQL, you cannot rename a field named with a reserved word; this is the major issue affecting the two examples above
      • on MSSQL, you cannot rename a field into a reserved word, this is a minor/cosmetic issue; a peer review should discourage people from renaming a field from a valid name into a reserved word for at least one of the supported DB servers in Moodle
      • on both PostgreSQL and Oracle Moodle DB API supports to go back and forth on renaming a field from/into a reserved word

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              matteo Matteo Scaramuccia
              Reporter:
              matteo Matteo Scaramuccia
              Peer reviewer:
              Mike Churchward Mike Churchward
              Integrator:
              Jun Pataleta Jun Pataleta
              Tester:
              Simey Lameze Simey Lameze
              Participants:
              Component watchers:
              Andrew Lyons, Dongsheng Cai, Huong Nguyen, Jun Pataleta, Michael Hawkins, Shamim Rezaie, Simey Lameze
              Votes:
              2 Vote for this issue
              Watchers:
              4 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved:
                Fix Release Date:
                12/Nov/18