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

    • Bug
    • Resolution: Fixed
    • Major
    • 3.4.6, 3.5.3
    • 3.3.7, 3.4.4, 3.5.1, 3.6
    • Database SQL/XMLDB
    • MySQL, Microsoft SQL
    • MOODLE_33_STABLE, MOODLE_34_STABLE, MOODLE_35_STABLE, MOODLE_36_STABLE
    • MOODLE_34_STABLE, MOODLE_35_STABLE
    • m36_MDL-63319_MySQL_MSSQL_Rename_Field_Reserved_Word
    • Easy
    • 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)

    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

              matteo Matteo Scaramuccia
              matteo Matteo Scaramuccia
              Mike Churchward Mike Churchward
              Jun Pataleta Jun Pataleta
              Simey Lameze Simey Lameze
              Votes:
              2 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Clockify

                  Error rendering 'clockify-timesheets-time-tracking-reports:timer-sidebar'. Please contact your Jira administrators.