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

    • MOODLE_37_STABLE
    • MOODLE_36_STABLE, MOODLE_37_STABLE
    • MDL-66110-master
    • 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
    • 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.
    • 2
    • 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

              ryanwyllie Ryan Wyllie
              evolik Eugene Volik
              Jake Dallimore Jake Dallimore
              Jun Pataleta Jun Pataleta
              Jennifer Bauzon Jennifer Bauzon
              Andrew Lyons, Huong Nguyen, Jun Pataleta, Michael Hawkins, Shamim Rezaie, Simey Lameze, Amaia Anabitarte, Carlos Escobedo, Ferran Recio, Ilya Tregubov, Laurent David, Sara Arjona (@sarjona)
              Votes:
              1 Vote for this issue
              Watchers:
              9 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:
                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