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

    • 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

      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)

        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

            ryanwyllie Ryan Wyllie
            evolik Eugene Volik
            Jake Dallimore Jake Dallimore
            Jun Pataleta Jun Pataleta
            Jennifer Bauzon Jennifer Bauzon
            Votes:
            1 Vote for this issue
            Watchers:
            9 Start watching this issue

              Created:
              Updated:
              Resolved:

                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

                  Error rendering 'clockify-timesheets-time-tracking-reports:timer-sidebar'. Please contact your Jira administrators.