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

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

    XMLWordPrintable

Details

    • Bug
    • Status: Closed
    • Major
    • Resolution: Fixed
    • 2.0, 3.3.3, 3.4, 3.5
    • 3.3.4, 3.4.1
    • Database SQL/XMLDB
    • Windows Azure Guest OS 1.5 + SQL Azure
      PHP 5.3.3
      Native SQL Connector
    • Microsoft SQL
    • MOODLE_20_STABLE, MOODLE_33_STABLE, MOODLE_34_STABLE, MOODLE_35_STABLE
    • MOODLE_33_STABLE, MOODLE_34_STABLE
    • MDL-23887-master-2
    • 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.

    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

              jaked Jake Dallimore
              rescbr Renato Schmidt
              Jun Pataleta Jun Pataleta
              Eloy Lafuente (stronk7) Eloy Lafuente (stronk7)
              Marina Glancy Marina Glancy
              David Woloszyn, Huong Nguyen, Jake Dallimore, Michael Hawkins, Stevani Andolo
              Votes:
              0 Vote for this issue
              Watchers:
              8 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:
                15/Jan/18