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

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

XMLWordPrintable

    • Icon: Bug Bug
    • Resolution: Fixed
    • Icon: Major 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)

      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

            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

              Created:
              Updated:
              Resolved:

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