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

mod_chat breaks moodle installation on MySQL 8.0.3

    XMLWordPrintable

Details

    • MySQL
    • MOODLE_32_STABLE, MOODLE_33_STABLE, MOODLE_34_STABLE
    • MOODLE_33_STABLE, MOODLE_34_STABLE
    • m35_MDL-60793_Mod_Chat_Reserved_Words_In_MySQL8p0p3_And_Above
    • Hide

      Note: It's much more convenient to run MySQL 8 via docker:

      docker run -p 3306:3306 -e MYSQL_ROOT_PASSWORD=moodle -d mysql:8 --character-set-server=utf8mb4 --collation-server=utf8mb4_bin --innodb_file_per_table=On
      

      Test 1
      1. Install a new site running at least MySQL 8.0.3 - 8.0.5 will be fine too unless new issues will be introduced by this new version, which should be fixed but in a new separate issue -  on integration.
      2. Confirm you can install with no errors.
      3. Create a course.
      4. Add a chat.
      5. Write a few messages.
      6. Confirm there is no error.
      7. Backup the course: confirm the 'system' is still there as part of the description of the 'message' payload.
      8. Restore it as a new course.
      9. Confirm the 'system' field values in the backup persist as 'issystem' in the DB.
      Test 2
      1. Install a new site running MySQL 5.x or Postgres on Stable.
      2. Upgrade to Integration.
      3. Check the tables 'chat_messages' and 'chat_messages_current' have the column 'issystem' and not 'system'.
      Test 3
      1. Create a site in 3.2, running on another DB instance since MySQL 8 support comes with 3.3.4+ and 3.4.1+ (--MDL-59100-, ---MDL-59099--)
      2. Create a course.
      3. Add a chat.
      4. Write a few messages.
      5. Backup the course.
      6. Restore it as a new course on your testing site above (in Test 1).
      7. Confirm the 'issystem' field into the DB reflects the 'system' values from the backup.

      Same tests should be performed using:

      1. chat as daemon
      2. Moodle Mobile as client
      Show
      Note: It's much more convenient to run MySQL 8 via docker: docker run -p 3306:3306 -e MYSQL_ROOT_PASSWORD=moodle -d mysql:8 --character-set-server=utf8mb4 --collation-server=utf8mb4_bin --innodb_file_per_table=On Test 1 Install a new site running at least MySQL 8.0.3 - 8.0.5 will be fine too unless new issues will be introduced by this new version, which should be fixed but in a new separate issue -  on integration. Confirm you can install with no errors. Create a course. Add a chat. Write a few messages. Confirm there is no error. Backup the course: confirm the 'system' is still there as part of the description of the 'message' payload. Restore it as a new course. Confirm the 'system' field values in the backup persist as 'issystem' in the DB. Test 2 Install a new site running MySQL 5.x or Postgres on Stable. Upgrade to Integration. Check the tables 'chat_messages' and 'chat_messages_current' have the column 'issystem' and not 'system'. Test 3 Create a site in 3.2, running on another DB instance since MySQL 8 support comes with 3.3.4+ and 3.4.1+ (-- MDL-59100 - , --- MDL-59099 --) Create a course. Add a chat. Write a few messages. Backup the course. Restore it as a new course on your testing site above (in Test 1). Confirm the 'issystem' field into the DB reflects the 'system' values from the backup. Same tests should be performed using: chat as daemon Moodle Mobile as client

    Description

      SYSTEM has been set as a reserved word starting MySQL 8.0.3: https://dev.mysql.com/doc/refman/8.0/en/keywords.html#keywords-8-0-detailed-S

      This breaks moodle, especially during installation since mod_chat's chat_messages table has a column named "system".

      Attachments

        Issue Links

          Activity

            People

              matteo Matteo Scaramuccia
              jpataleta Jun Pataleta
              Mark Nelson Mark Nelson
              Jun Pataleta Jun Pataleta
              David Monllaó David Monllaó
              Amaia Anabitarte, Carlos Escobedo, Ferran Recio, Ilya Tregubov, Laurent David, Raquel Ortega, Sara Arjona (@sarjona), Andrew Lyons, Huong Nguyen, Jun Pataleta, Michael Hawkins, Shamim Rezaie, Simey Lameze, Stevani Andolo
              Votes:
              0 Vote for this issue
              Watchers:
              6 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:
                15/Jan/18