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

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

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Blocker
    • Resolution: Fixed
    • Affects Version/s: 2.0
    • Fix Version/s: 2.0
    • Component/s: Installation
    • Labels:
      None
    • Environment:
      Windows Server Server 2008, SQL 2005, FreeTDS, IIS 7
    • Database:
      Microsoft SQL
    • Difficulty:
      Easy
    • 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

      Originally commented http://moodle.org/mod/forum/discuss.php?d=153527

      Debug info: The data types ntext and varchar are incompatible in the not equal to operator.
      SELECT COUNT('x') FROM mdl_data_content c
      JOIN mdl_data_fields f ON f.id = c.fieldid
      JOIN mdl_data_records r ON r.id = c.recordid
      JOIN mdl_data d ON d.id = r.dataid
      JOIN mdl_modules m ON m.name = 'data'
      JOIN mdl_course_modules cm ON (cm.module = m.id AND cm.instance = d.id)
      WHERE c.content <> '' AND c.content IS NOT NULL
      AND (f.type = 'file' OR f.type = 'picture')
      [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 728 of \lib\dml\mssql_native_moodle_database.php: call to mssql_native_moodle_database->get_recordset_sql()
      line 1243 of \lib\dml\moodle_database.php: call to mssql_native_moodle_database->get_records_sql()
      line 1318 of \lib\dml\moodle_database.php: call to moodle_database->get_record_sql()
      line 1489 of \lib\dml\moodle_database.php: call to moodle_database->get_field_sql()
      line 90 of \mod\data\db\upgrade.php: call to moodle_database->count_records_sql()
      line 490 of \lib\upgradelib.php: call to xmldb_data_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()

      MSSQL would have issues with the SQL:

      WHERE c.content <> '' for an ntext datatype (the code had changed slightly to <> '$empty' but the issue still stands where the <> operator cannot be used for an ntext field in MSSQL)
      Solution:

      WHERE (c.content LIKE '_%' AND c.content IS NOT NULL)

      This fix will need to be propagated to other such SQL calls - this happens to be the first -line 87 (moodle\mod\data\db\upgrade.php)

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

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              skodak Petr Skoda
              Reporter:
              kk20 mark williams
              Tester:
              Nobody
              Participants:
              Component watchers:
              Matteo Scaramuccia, Andrew Nicols, Jun Pataleta, Michael Hawkins, Shamim Rezaie, Simey Lameze
              Votes:
              0 Vote for this issue
              Watchers:
              0 Start watching this issue

                Dates

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