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

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

                Dates

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