Moodle
  1. Moodle
  2. MDL-22553

Wiki upgrade from 1.9 to 2.0 broken on MSSQL and Oracle

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Critical Critical
    • Resolution: Fixed
    • Affects Version/s: 2.0
    • Fix Version/s: 2.0
    • Component/s: Wiki (2.x)
    • Labels:
      None
    • Database:
      Microsoft SQL, Oracle
    • Difficulty:
      Moderate
    • Affected Branches:
      MOODLE_20_STABLE
    • Fixed Branches:
      MOODLE_20_STABLE
    • Rank:
      32211

      Description

      It seems that wiki upgrade is broken, both for MSSQL and Oracle. The cause is that we are using one harcoded LIMIT clause in upgrade, where that clause simply doesn't exists under those DBs.

      So, the query must be rewritten to avoid the use of any LIMIT clause - using MAX() or whatever. Explicit LIMIT clauses are forbidden.

      Stack trace:

      Debug info: Incorrect syntax near 'LIMIT'.
      INSERT into mdl_wiki_pages (subwikiid, title, cachedcontent, timecreated, timemodified, userid, pageviews) SELECT s.id, p.pagename, ?, p.created, p.lastmodified, p.userid, p.hits FROM mdl_wiki_pages_old p LEFT OUTER JOIN mdl_wiki_entries_old e ON e.id = p.wiki LEFT OUTER JOIN mdl_wiki_subwikis s ON s.wikiid = e.wikiid AND s.groupid = e.groupid AND s.userid = e.userid WHERE p.version = ( SELECT po.version FROM mdl_wiki_pages_old po WHERE p.pagename = po.pagename and p.wiki = po.wiki ORDER BY p.version DESC LIMIT 1)
      [array (
      0 => '*reparse needed*',
      )]
      Stack trace:
      line 381 of /lib/dml/moodle_database.php: dml_write_exception thrown
      line 244 of /lib/dml/mssql_native_moodle_database.php: call to moodle_database->query_end()
      line 651 of /lib/dml/mssql_native_moodle_database.php: call to mssql_native_moodle_database->query_end()
      line 189 of /mod/wiki/db/upgrade.php: call to mssql_native_moodle_database->execute()
      line 510 of /lib/upgradelib.php: call to xmldb_wiki_upgrade()
      line 294 of /lib/upgradelib.php: call to upgrade_plugins_modules()
      line 1266 of /lib/upgradelib.php: call to upgrade_plugins()
      line 260 of /admin/index.php: call to upgrade_noncore()

        Issue Links

          Activity

          Hide
          Eloy Lafuente (stronk7) added a comment -

          Another error, later in the process. text columns cannot be used in DISTINCT queries. Subquery must be used instead:

          SELECT list, of, columns, including, text, column FROM (
              SELECT DISTINCT list, of, columns, without, text, column
              ....
              ....
          

          Stack trace:

          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 378 of /lib/dml/moodle_database.php: dml_read_exception thrown
          line 244 of /lib/dml/mssql_native_moodle_database.php: call to moodle_database->query_end()
          line 691 of /lib/dml/mssql_native_moodle_database.php: call to mssql_native_moodle_database->query_end()
          line 236 of /mod/wiki/db/upgrade.php: call to mssql_native_moodle_database->get_recordset_sql()
          line 510 of /lib/upgradelib.php: call to xmldb_wiki_upgrade()
          line 294 of /lib/upgradelib.php: call to upgrade_plugins_modules()
          line 1266 of /lib/upgradelib.php: call to upgrade_plugins()
          line 260 of /admin/index.php: call to upgrade_noncore()

          Show
          Eloy Lafuente (stronk7) added a comment - Another error, later in the process. text columns cannot be used in DISTINCT queries. Subquery must be used instead: SELECT list, of, columns, including, text, column FROM ( SELECT DISTINCT list, of, columns, without, text, column .... .... Stack trace: 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 378 of /lib/dml/moodle_database.php: dml_read_exception thrown line 244 of /lib/dml/mssql_native_moodle_database.php: call to moodle_database->query_end() line 691 of /lib/dml/mssql_native_moodle_database.php: call to mssql_native_moodle_database->query_end() line 236 of /mod/wiki/db/upgrade.php: call to mssql_native_moodle_database->get_recordset_sql() line 510 of /lib/upgradelib.php: call to xmldb_wiki_upgrade() line 294 of /lib/upgradelib.php: call to upgrade_plugins_modules() line 1266 of /lib/upgradelib.php: call to upgrade_plugins() line 260 of /admin/index.php: call to upgrade_noncore()
          Hide
          Martin Dougiamas added a comment -

          Adding Jordi and Dongsheng

          Show
          Martin Dougiamas added a comment - Adding Jordi and Dongsheng
          Hide
          Jordi Piguillem Poch added a comment -

          I have fixed the problem with LIMIT clauses.

          Dongsheng, can you fix the problem with the DISTINC at step 8, I'm not familiar with that part of migration process.

          Thanks.

          Show
          Jordi Piguillem Poch added a comment - I have fixed the problem with LIMIT clauses. Dongsheng, can you fix the problem with the DISTINC at step 8, I'm not familiar with that part of migration process. Thanks.
          Hide
          Dongsheng Cai added a comment -

          Hi, Eloy

          Can you please review my patch on the SQL subquery?

          Show
          Dongsheng Cai added a comment - Hi, Eloy Can you please review my patch on the SQL subquery?
          Hide
          Eloy Lafuente (stronk7) added a comment -

          Hi Dong,

          The query will work if:

          1) The subquery by itself works. Does it? Note I haven't the old tables here to test right now. Looks correct, anyway.
          2) As far as de JOIN is one inline view (virtual table), you must follow table rules, so take out the "AS" keyword. That's the cross-db way.

          In outline, this is the way:

          SELECT innerview.*, list_of_text_fields
            FROM table tablealias
            JOIN (
                  /** One valid query goes here, it must work alone **/
            ) innerview ON innerview.id = tablealias.id;
          
          Show
          Eloy Lafuente (stronk7) added a comment - Hi Dong, The query will work if: 1) The subquery by itself works. Does it? Note I haven't the old tables here to test right now. Looks correct, anyway. 2) As far as de JOIN is one inline view (virtual table), you must follow table rules, so take out the "AS" keyword. That's the cross-db way. In outline, this is the way: SELECT innerview.*, list_of_text_fields FROM table tablealias JOIN ( /** One valid query goes here, it must work alone **/ ) innerview ON innerview.id = tablealias.id;
          Hide
          Dongsheng Cai added a comment -

          Hi, Eloy

          thanks for the explanation.

          Show
          Dongsheng Cai added a comment - Hi, Eloy thanks for the explanation.
          Hide
          Dongsheng Cai added a comment -

          Fixed

          Show
          Dongsheng Cai added a comment - Fixed

            People

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

              Dates

              • Created:
                Updated:
                Resolved: