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

      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()

        Gliffy Diagrams

          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: