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

Unable to upgrade moodle database (upgrading to 2.0) due to MSSQL incompatible SQL command (mod_wiki)

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Critical
    • Resolution: Fixed
    • Affects Version/s: 2.0
    • Fix Version/s: 2.0
    • Component/s: Database SQL/XMLDB
    • Labels:
      None
    • Environment:
      Windows Server Server 2008, SQL 2005, FreeTDS, IIS 7
    • Database:
      Microsoft SQL
    • Affected Branches:
      MOODLE_20_STABLE
    • Fixed Branches:
      MOODLE_20_STABLE

      Description

      Upgrade from 1.9.10 to 2.0 RC1. Upgrade process gets stuck on upgrading mod_data

      comparable error: http://tracker.moodle.org/browse/MDL-22553

      mod_wiki gets stuck with error:

      Debug info: The ntext data type cannot be selected as DISTINCT because it is not comparable.
      SELECT DISTINCT po.pagename, w.id AS wikiid, po.userid,
      po.meta AS filemeta, eo.id AS entryid, eo.groupid, s.id AS subwiki,
      w.course AS courseid, cm.id AS cmid
      FROM mdl_wiki_pages_old po
      LEFT OUTER JOIN mdl_wiki_entries_old eo
      ON eo.id=po.wiki
      LEFT OUTER JOIN mdl_wiki w
      ON w.id = eo.wikiid
      LEFT OUTER JOIN mdl_wiki_subwikis s
      ON s.groupid = eo.groupid AND s.wikiid = eo.wikiid AND eo.userid = s.userid
      JOIN mdl_modules m ON m.name = 'wiki'
      JOIN mdl_course_modules cm ON (cm.module = m.id AND cm.instance = w.id)
      [array (
      )]
      Stack trace:
      line 391 of \lib\dml\moodle_database.php: dml_read_exception thrown
      line 247 of \lib\dml\mssql_native_moodle_database.php: call to moodle_database->query_end()
      line 699 of \lib\dml\mssql_native_moodle_database.php: call to mssql_native_moodle_database->query_end()
      line 234 of \mod\wiki\db\upgrade.php: call to mssql_native_moodle_database->get_recordset_sql()
      line 490 of \lib\upgradelib.php: call to xmldb_wiki_upgrade()
      line 265 of \lib\upgradelib.php: call to upgrade_plugins_modules()
      line 1352 of \lib\upgradelib.php: call to upgrade_plugins()
      line 302 of \admin\index.php: call to upgrade_noncore()

      This is expected since MSSQL 2005 cannot have an ntext field in a distinct statement.

      Solution

      /moodle/mod/wiki/db/upgrade.php

      alter line 222 from:
      po.meta AS filemeta
      to:
      convert(nvarchar(MAX),po.meta) AS filemeta

      I do not have alternative databases to test the MSSQL fix.

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              nebgor Aparup Banerjee
              Reporter:
              kk20 mark williams
              Tester:
              Nobody
              Participants:
              Component watchers:
              Andrew Nicols, Jun Pataleta, Michael Hawkins, Shamim Rezaie, Simey Lameze
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved:
                Fix Release Date:
                24/Nov/10