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

Deprecated System Tables used on queries won't work on SQL Azure

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 2.0, 3.3.3, 3.4, 3.5
    • Fix Version/s: 3.3.4, 3.4.1
    • Component/s: Database SQL/XMLDB
    • Labels:
    • Environment:
      Windows Azure Guest OS 1.5 + SQL Azure
      PHP 5.3.3
      Native SQL Connector
    • Database:
      Microsoft SQL
    • Testing Instructions:
      Hide
      1. Pull a fresh copy of Moodle and install on mssql (you can use the Moodle docker.)
      2. Verify the install runs successfully.
      3. Go to the xmldb page
      4. Load the 'admin/tool/usertours/db' schema and click edit.
      5. Click tool_usertours_tours
      6. Click the 'enabled' column to bring up the edit form.
      7. Change the default from 0 to 1 and click 'Change'.
      8. Now, above the table fields list, click the 'View php code' link.
      9. Copy that code somewhere
      10. Now, edit admin/tool/usertours/version.php and bump the version number by 1 and copy that somewhere too.
      11. Now, edit admin/tool/usertours/db/upgrade.php and past the upgrade code below the existing steps, swapping in the new (bumped) version number.
      12. Paste "$dbman = $DB->get_manager();" at the start of that conditional so we can use $dbman.
      13. Now, hit the admin setting page of Moodle, and you should see an upgrade triggered for user tours.
      14. Run through the upgrade and confirm that it succeeds without any warnings/errors.
      15. Log out of your site and log back in again.
      16. Go to xmldb and load the user tours again.
      17. Click through to the tool_usertours_tours table again and edit the 'enabled' column.
      18. Confirm this column is listed with a default of '1' now.
      Show
      Pull a fresh copy of Moodle and install on mssql (you can use the Moodle docker .) Verify the install runs successfully. Go to the xmldb page Load the 'admin/tool/usertours/db' schema and click edit. Click tool_usertours_tours Click the 'enabled' column to bring up the edit form. Change the default from 0 to 1 and click 'Change'. Now, above the table fields list, click the 'View php code' link. Copy that code somewhere Now, edit admin/tool/usertours/version.php and bump the version number by 1 and copy that somewhere too. Now, edit admin/tool/usertours/db/upgrade.php and past the upgrade code below the existing steps, swapping in the new (bumped) version number. Paste "$dbman = $DB->get_manager();" at the start of that conditional so we can use $dbman. Now, hit the admin setting page of Moodle, and you should see an upgrade triggered for user tours. Run through the upgrade and confirm that it succeeds without any warnings/errors. Log out of your site and log back in again. Go to xmldb and load the user tours again. Click through to the tool_usertours_tours table again and edit the 'enabled' column. Confirm this column is listed with a default of '1' now.
    • Affected Branches:
      MOODLE_20_STABLE, MOODLE_33_STABLE, MOODLE_34_STABLE, MOODLE_35_STABLE
    • Fixed Branches:
      MOODLE_33_STABLE, MOODLE_34_STABLE
    • Pull from Repository:
    • Pull Master Branch:
      MDL-23887-master-2
    • Pull Master Diff URL:

      Description

      While installing Moodle using SQL Azure, you get a "dmlreadexception".
      Tracking down this error, I found that some queries on lib/ddl/mssql_sql_generator.php uses System Tables, which was deprecated on SQL Server 2005. It works correctly on SQL Server 2008 R2, but it seems Microsoft removed System Tables support on SQLAzure version.

      I changed the queries to use System Views, according to this documentation on MSDN <http://msdn.microsoft.com/en-us/library/ms187997.aspx>, it worked, but I don't know if it will work as expected by other modules, as I am not a T-SQL expert.

      The patch I have created is attached.

        Attachments

          Issue Links

          has a non-specific relationship to

          Task - A task that needs to be completed. Tasks usually refer to work that must be done outside the product. MDL-59581 Remove mssql DML

          • Minor - Minor loss of function where workaround is possible
          • Closed
          Testing discovered

          Bug - A problem which impairs or prevents Moodle from functioning correctly. MDL-61150 Wrong PATH attribute in some XMLDB install.xml files

          • Minor - Minor loss of function where workaround is possible
          • Closed
          will help resolve

          New Feature - A new Moodle feature which has yet to be developed. MDL-44709 Support for Azure SQL Database

          • Minor - Minor loss of function where workaround is possible
          • Open

            Activity

              People

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

                Dates

                • Created:
                  Updated:
                  Resolved:
                  Fix Release Date:
                  15/Jan/18