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

Error reading from database after upgrade to 3.7.1 (MySQL 8.0.2)

    XMLWordPrintable

    Details

    • Testing Instructions:
      Hide

      Setup

      1. Use a MySQL 8.0.2 server. Docker would be the simplest way to do this.

        docker run --name mysql802 -p 3307:3306 -e MYSQL_ROOT_PASSWORD=moodle -d mysql:8.0.2 --character-set-server=utf8mb4 --collation-server=utf8mb4_bin

        Note: We're using port 3307 just in case there's already another MySQL server running on your machine.

      2. Connect to the MySQL8 server.

        mysql -h 127.0.0.1 -P 3307 -uroot -pmoodle

      3. Create a new database schema for your Moodle instance.

        CREATE SCHEMA `integration[VERSION]` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

        where "[VERSION]" is the version of the instance you're testing.

      Test

      1. Go to your moodle instance's page and proceed with the install
      2. On the Database settings page, enter the following details:
        Database host 127.0.0.1
        Database name integration[VERSION]
        Database user root
        Database password moodle
        Tables prefix  
        Database port 3307
      3. Proceed with the install.
      4. Confirm that the installation completes successfully.
      5. Proceed to set up the Moodle instance.
      6. Confirm that you can successfully set up your Moodle instance.
      7. Create a course, enrol some students.
      8. Auto-create some groups.
      9. Confirm that groups auto-creation works as expected.
      10. Edit one of the created groups and save the changes.
      11. Confirm that the group is successfully updated.
      12. Delete one of the groups.
      13. Confirm that the selected group gets successfully deleted.
      Show
      Setup Use a MySQL 8.0.2 server. Docker would be the simplest way to do this. docker run --name mysql802 -p 3307:3306 -e MYSQL_ROOT_PASSWORD=moodle -d mysql:8.0.2 --character-set-server=utf8mb4 --collation-server=utf8mb4_bin Note: We're using port 3307 just in case there's already another MySQL server running on your machine. Connect to the MySQL8 server. mysql -h 127.0.0.1 -P 3307 -uroot -pmoodle Create a new database schema for your Moodle instance. CREATE SCHEMA `integration[VERSION]` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin; where " [VERSION] " is the version of the instance you're testing. Test Go to your moodle instance's page and proceed with the install On the Database settings page, enter the following details: Database host 127.0.0.1 Database name integration [VERSION] Database user root Database password moodle Tables prefix   Database port 3307 Proceed with the install. Confirm that the installation completes successfully. Proceed to set up the Moodle instance. Confirm that you can successfully set up your Moodle instance. Create a course, enrol some students. Auto-create some groups. Confirm that groups auto-creation works as expected. Edit one of the created groups and save the changes. Confirm that the group is successfully updated. Delete one of the groups. Confirm that the selected group gets successfully deleted.
    • Workaround:
      Hide

      Don't use MySQL 8.0.2 or up in a site with empty $CFG->prefix

      Show
      Don't use MySQL 8.0.2 or up in a site with empty $CFG->prefix
    • Affected Branches:
      MOODLE_37_STABLE
    • Fixed Branches:
      MOODLE_36_STABLE, MOODLE_37_STABLE
    • Pull 3.6 Branch:
    • Pull 3.7 Branch:
    • Pull Master Branch:
      MDL-66110-master
    • Story Points:
      2
    • Sprint:
      Sprint 4 - Road to Redemption

      Description

      When trying to upgrade from version 3.3 to 3.7.1, we get an error:

      Default exception handler: Error reading from database Debug: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'groups g
      ON mc.itemid = g.id
      WHERE mc.compo' at line 3
      SELECT mc.id
      FROM message_conversations mc
      LEFT JOIN groups g
      ON mc.itemid = g.id
      WHERE mc.component = ?
      AND mc.itemtype = ?
      AND g.id is NULL
      [array (
      0 => 'core_group',
      1 => 'groups',
      )]
      Error code: dmlreadexception

      • line 486 of \lib\dml\moodle_database.php: dml_read_exception thrown
      • line 1245 of \lib\dml\mysqli_native_moodle_database.php: call to moodle_database->query_end()
      • line 2743 of \lib\db\upgrade.php: call to mysqli_native_moodle_database->get_records_sql()
      • line 1809 of \lib\upgradelib.php: call to xmldb_main_upgrade()
      • line 518 of \admin\index.php: call to upgrade_core()

       

      Probable reason in the keyword "GROUPS" - from version MySQL 8.0.2 this word is reserved and forbidden by standard SQL as column or table names  (https://dev.mysql.com/doc/refman/8.0/en/keywords.html)

        Attachments

        1. MDL-66110_01.png
          MDL-66110_01.png
          129 kB
        2. MDL-66110_02.png
          MDL-66110_02.png
          91 kB
        3. MDL-66110_passed.png
          MDL-66110_passed.png
          55 kB
        4. reserved_words_xmldb.png
          reserved_words_xmldb.png
          37 kB

          Issue Links

            Activity

              People

              • Votes:
                1 Vote for this issue
                Watchers:
                9 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved:
                  Fix Release Date:
                  9/Sep/19

                  Time Tracking

                  Estimated:
                  Original Estimate - Not Specified
                  Not Specified
                  Remaining:
                  Remaining Estimate - 0 minutes
                  0m
                  Logged:
                  Time Spent - 1 day, 5 hours, 46 minutes
                  1d 5h 46m