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

Azure hosted MariaDB and MySQL instances report the wrong version number

    XMLWordPrintable

Details

    • MySQL
    • MOODLE_39_STABLE
    • m42_MDL-69581_Improve_MySQL_MariaDB_Server_Version_Detection_v2
    • Hide

      (difficulty: "easy", requires the ability to install a Moodle instance running on a MySQL-ish DB server including that provided by Microsoft Azure, which could be less easier if you're not familiar with Azure resources).
      Note: the version reported by Moodle, e.g. in the GUI, in the Information column in Site administration > Server > Environment is still the one "wrongly" reported by the PHP client to keep that info consistent w/ what the PHP client is telling about that DB connection. Only the version info has been improved to collect what the DB Server instance tells about its actual version.

      Test using a "remote" MySQL & MariaDB server provided by Microsoft Azure

      Create a Azure free account, https://azure.microsoft.com/en-us/free/: you can even use your GitHub account just in case.
      The "big concern" is that your Credit Card is still required for the verification process and to jump later in the pay-as-you-go pricing when the free trial will expire in time (12 months) or in granted credits (USD/€200 for the first 30 days).

      MySQL 5.7 Flexible Server instance

      Then follow https://learn.microsoft.com/en-us/azure/mysql/flexible-server/how-to-deploy-on-azure-free-account to create free instances of MySQL server in Azure, using the Networking > Public access for just testing purposes.
      Search for "Free Services" to select "Azure Database for MySQL" for a development Workload type: you should create a proper Resource Group in Azure e.g. freetrial-rg and a name which will be used even to expose your instance via FQDN e.g. moodle-mysql57; since the connection will be via public network you should create strong admin username and password. SSL is enforced and TLS version is 1.2 but you should disable secure connection enforcement, https://learn.microsoft.com/en-us/azure/mysql/flexible-server/how-to-connect-tls-ssl#disable-ssl-enforcement-on-your-flexible-server, since Moodle doesn't support secure connections out-of-the-box (MDL-54704) yet; otherwise, you'll get a message ending with the following statement:

      Debug info: Connections using insecure transport are prohibited while --require_secure_transport=ON.
      Error code: dbconnectionfailed
      

      Disclaimer: what described above would be an insane configuration out of the testing purposes described here!

      You can also add tags to the resources that will be created to optionally simplify the process of deleting them to avoid extra costs e.g.:

      • app: moodle
      • db: mysql
      • version: 57
      • id: MDL-69581

      Five minutes on average is the time required to have the MySQL instance ready (4m for the instance, 5m for the firewall rule): your instance will be available as e.g. moodle-mysql57.mysql.database.azure.com.

      The Moodle database should be manually created (CLI or GUI):

            CREATE DATABASE `moodle`
      DEFAULT CHARACTER SET utf8mb4
                    COLLATE utf8mb4_unicode_ci;
      

      Use the Moodle Docker Toolbox for the tests, forcing later just a change to the DB server coordinate and credentials:

      # export MOODLE_DOCKER_WWWROOT=/path/to/moodle/branch
      # export MOODLE_DOCKER_DB=mysql
      # cp config.docker-template.php $MOODLE_DOCKER_WWWROOT/config.php
      

      You can explore what is the result of the SELECT VERSION() execution:

      # bin/moodle-docker-compose up -d && bin/moodle-docker-compose exec db mysql -h moodle-mysql57.mysql.database.azure.com -u ******** -p'********' -e "SELECT VERSION();"
      mysql: [Warning] Using a password on the command line interface can be insecure.
      +------------+
      | VERSION()  |
      +------------+
      | 5.7.39-log |
      +------------+
      

      Please note the version, 5.7.39 and the log suffix in the query result.

      Now you should manually edit $MOODLE_DOCKER_WWWROOT/config.php to set the coordinate and the credentials of the Azure MySQL 5.7 instance: $CFG->dbhost, $CFG->dbuser, $CFG->dbpass.
      Besides, you need to add a new db option, dbversionfn, set to true:

      $CFG->dboptions = array (
      ...
        'dbversionfn' => true,
      );
      

      Then launch the unit tests - the setup of the e.g. 470 tables (7c3188b, version 2022111100.00) will be really slow (e.g. 45m ) due to the bandwidth/latency and the micro-sizing of the development instance - and look at the version reported in the header of the tests, still 5.7.39 (without the suffix):

      # bin/moodle-docker-compose exec webserver php admin/tool/phpunit/cli/init.php
      # bin/moodle-docker-compose exec webserver vendor/bin/phpunit lib/dml/tests/dml_test.php
      Moodle 4.1beta (Build: 20221111), eb24f973b223e541452a1816082a3cbc1570a391
      Php: 7.4.32, mysqli: 5.7.39, OS: Linux 3.10.0-1160.76.1.el7.x86_64 x86_64
      PHPUnit 9.5.25 #StandWithUkraine
       
      ...............................................................  63 / 114 ( 55%)
      ...................................................             114 / 114 (100%)
       
      Time: 01:43.901, Memory: 48.00 MB
       
      OK (114 tests, 1261 assertions)
      # bin/moodle-docker-compose down -v
      

      If you try to install Moodle using the GUI, the environment stage will look like:

      Maria DB 10.3 instance

      Same applies to MariaDB (Azure Database for MariaDB servers) including the Networking and _Security setup, now up to version 10.3 - go ahead even if 4.1 doesn't support it (MDL-71747) - which has no free tier so you'll spend some credits of the free account for the time required to run the tests up to when you'll actually dispose the instance related Azure resources.
      If you'll name it moodle-mariadb103 it will be available to Moodle with a DB username suffixed by @<instance-name> e.g. ********@moodle-mariadb103.
      You should manually edit $MOODLE_DOCKER_WWWROOT/config.php to set the coordinate and the credentials of the Azure MySQL 5.7 instance: $CFG->dbhost, $CFG->dbuser, $CFG->dbpass.
      Besides, you need to add a new db option, dbversionfn, set to true:

      $CFG->dboptions = array (
      ...
        'dbversionfn' => true,
      );
      

      The Moodle database should be manually created (CLI or GUI):

            CREATE DATABASE `moodle`
      DEFAULT CHARACTER SET utf8mb4
                    COLLATE utf8mb4_unicode_ci;
      

      # export MOODLE_DOCKER_DB=mariadb
      # bin/moodle-docker-compose up -d && bin/moodle-docker-compose exec db mysql -h moodle-mariadb103.mariadb.database.azure.com -u ********@moodle-mariadb103 -p'********' -e "SELECT VERSION();"
      mysql: [Warning] Using a password on the command line interface can be insecure.
      +---------------------+
      | VERSION()           |
      +---------------------+
      | 10.3.23-MariaDB-log |
      +---------------------+
      

      Please note the version, 10.3.23 and the two suffixes in the query result, among the two, "-MariaDB" is expected.
      Then launch the unit tests starting with the initialization:

      # bin/moodle-docker-compose exec webserver php admin/tool/phpunit/cli/init.php
      Initialising Moodle PHPUnit test environment...
      You are already using the latest available Composer version 2.4.4 (stable channel).
      Installing dependencies from lock file (including require-dev)
      Verifying lock file contents can be installed on current platform.
      Nothing to install, update or remove
      Generating autoload files
      53 packages you are using are looking for funding.
      Use the `composer fund` command to find out more!
      == Environment ==
      !! database mariadb (5.6.47.0) !!
      [System] version 10.4 is required and you are running 10.3.23 -
      

      As you can see you cannot go further due to the 10.4 constraint for the MariaDB server version but Moodle is able to correctly recognize the DB version as 10.3.23 even if the PHP mysqli driver reports the wrong version 5.6.47.0 - look for the "client version" after the database type mariadb - due to the Azure infra gateway in between which reports back the version of the gateway and not the one of your instance, one of the back-end endpoints of the gateway.

      # bin/moodle-docker-compose down -v
      

      If you try to install Moodle using the GUI, the environment stage will look like:

      MySQL 8.0 Single Server instance

      To close with the tests in Azure try to create a MySQL 8.0 instance as Single Server, the type on the retirement path. Please remember to apply also the Network and Security setup required to access the instance from your local environment.
      If you'll name it moodle-mysql80-singleserver it will be available to Moodle with a DB username suffixed by @<instance-name> e.g. ********@moodle-mysql80-singleserver.
      You should manually edit $MOODLE_DOCKER_WWWROOT/config.php to set the coordinate and the credentials of the Azure MySQL 5.7 instance: $CFG->dbhost, $CFG->dbuser, $CFG->dbpass.
      Besides, you need to add a new db option, dbversionfn, set to true:

      $CFG->dboptions = array (
      ...
        'dbversionfn' => true,
      );
      

      The Moodle database should be manually created (CLI or GUI):

            CREATE DATABASE `moodle`
      DEFAULT CHARACTER SET utf8mb4
                    COLLATE utf8mb4_unicode_ci;
      

      # export MOODLE_DOCKER_DB=mysql
      # bin/moodle-docker-compose up -d && bin/moodle-docker-compose exec db mysql -h moodle-mysql80-singleserver.mysql.database.azure.com -u ********@moodle-mysql80-singleserver -p'********' -e "SELECT VERSION();"
      mysql: [Warning] Using a password on the command line interface can be insecure.
      +-----------+
      | VERSION() |
      +-----------+
      | 8.0.15    |
      +-----------+
      

      Please note the version, 8.0.15.
      Now create the empty moodle database.

      # bin/moodle-docker-compose exec db mysql -h moodle-mysql80-singleserver.mysql.database.azure.com -u ********@moodle-mysql80-singleserver -p'********'
      mysql: [Warning] Using a password on the command line interface can be insecure.
      Welcome to the MySQL monitor.  Commands end with ; or \g.
      Your MySQL connection id is 62809
      Server version: 5.6.47.0 Source distribution
       
      Copyright (c) 2000, 2022, Oracle and/or its affiliates.
       
      Oracle is a registered trademark of Oracle Corporation and/or its
      affiliates. Other names may be trademarks of their respective
      owners.
       
      Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
       
      mysql>       CREATE DATABASE `moodle`
          -> DEFAULT CHARACTER SET utf8mb4
          ->               COLLATE utf8mb4_unicode_ci;
      Query OK, 1 row affected (0.05 sec)
       
      mysql> exit
      Bye
      

      Please note the client connection i.e. the version reported above is 5.6.47.0 which is clearly wrong due to the presence of the Azure infra gateway.
      Then launch the unit tests - the setup of the e.g. 470 tables (7c3188b, version 2022111100.00) will be really slow due to the bandwidth/latency - and look at the version reported in the header of the tests, still 8.0.15 (without any suffix):

      # bin/moodle-docker-compose exec webserver php admin/tool/phpunit/cli/init.php
      # bin/moodle-docker-compose exec webserver vendor/bin/phpunit lib/dml/tests/dml_test.php
      Moodle 4.1beta (Build: 20221111), eb24f973b223e541452a1816082a3cbc1570a391
      Php: 7.4.32, mysqli: 8.0.15, OS: Linux 3.10.0-1160.76.1.el7.x86_64 x86_64
      PHPUnit 9.5.25 #StandWithUkraine
       
      ...............................................................  63 / 114 ( 55%)
      ...................................................             114 / 114 (100%)
       
      Time: 01:32.116, Memory: 48.00 MB
       
      OK (114 tests, 1261 assertions)
      

      If you try to install Moodle using the GUI, the environment stage will look like:

      Prepare the "local" test environment

      1. You can use the Moodle Docker Toolbox:

        # export MOODLE_DOCKER_WWWROOT=/path/to/moodle/branch
        # cp config.docker-template.php $MOODLE_DOCKER_WWWROOT/config.php
        

      Regression tests on "plain" MySQL

      1. Select the MySQL DB Server in your Moodle Docker Toolbox (defaults to 5.7) and take note of the DB version as reported by the CLI comand (here: 5.7.40):

        # export MOODLE_DOCKER_DB=mysql
        # bin/moodle-docker-compose up -d && bin/moodle-docker-compose exec db mysql -V
        mysql  Ver 14.14 Distrib 5.7.40, for Linux (x86_64) using  EditLine wrapper
        

      2. Run the DML tests and verify both no failures and the match between the version reported in the PHPUnit CLI output and the one from the CLI comand output above (5.7.40):

        # bin/moodle-docker-wait-for-db
        # bin/moodle-docker-compose exec webserver php admin/tool/phpunit/cli/init.php
        # bin/moodle-docker-compose exec webserver vendor/bin/phpunit lib/dml/tests/dml_test.php
        Moodle 4.2dev (Build: 20221201), 27880d9b3f7faf90a6ac134b120fe73cbc9037c3
        Php: 7.4.32, mysqli: 5.7.40, OS: Linux 3.10.0-1160.80.1.el7.x86_64 x86_64
        PHPUnit 9.5.26 by Sebastian Bergmann and contributors.
         
        ...............................................................  63 / 114 ( 55%)
        ...................................................             114 / 114 (100%)
         
        Time: 00:02.563, Memory: 68.50 MB
         
        OK (114 tests, 1266 assertions)
        

      3. Stop the previous test instance, select the MySQL DB Server version 8.0 in your Moodle Docker Toolbox and take note of the DB version as reported by the CLI comand (here: 8.0.31):

        # bin/moodle-docker-compose down -v
        # export MOODLE_DOCKER_DB_VERSION=8.0
        # bin/moodle-docker-compose up -d && bin/moodle-docker-compose exec db mysql -V
        mysql  Ver 8.0.31 for Linux on x86_64 (MySQL Community Server - GPL)
        

      4. Run the DML tests and verify both no failures and the match between the version reported in the PHPUnit CLI output and the one from the CLI comand output above (8.0.31):

        # bin/moodle-docker-wait-for-db
        # bin/moodle-docker-compose exec webserver php admin/tool/phpunit/cli/init.php
        # bin/moodle-docker-compose exec webserver vendor/bin/phpunit lib/dml/tests/dml_test.php
        Moodle 4.2dev (Build: 20221201), 27880d9b3f7faf90a6ac134b120fe73cbc9037c3
        Php: 7.4.32, mysqli: 8.0.31, OS: Linux 3.10.0-1160.80.1.el7.x86_64 x86_64
        PHPUnit 9.5.26 by Sebastian Bergmann and contributors.
         
        ...............................................................  63 / 114 ( 55%)
        ...................................................             114 / 114 (100%)
         
        Time: 00:03.956, Memory: 48.00 MB
         
        OK (114 tests, 1266 assertions)
        

      Regression tests on "plain" MariaDB

      1. Stop the previous test instance, select the MariaDB Server in your Moodle Docker Toolbox and take note of the DB version as reported by the CLI comand (here: 10.7.7. -MariaDB is just an expected suffix):

        # bin/moodle-docker-compose down -v
        # unset MOODLE_DOCKER_DB_VERSION
        # export MOODLE_DOCKER_DB=mariadb
        # bin/moodle-docker-compose up -d && bin/moodle-docker-compose exec db mysql -V
        mysql  Ver 15.1 Distrib 10.7.7-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2
        

      2. Run the DML tests and verify both no failures and the match between the version reported in the PHPUnit CLI output (please note the lack of -MariaDB suffix too) and the one from the CLI comand output above (10.7.7):

        # bin/moodle-docker-wait-for-db
        # bin/moodle-docker-compose exec webserver php admin/tool/phpunit/cli/init.php
        # bin/moodle-docker-compose exec webserver vendor/bin/phpunit lib/dml/tests/dml_test.php
        Moodle 4.2dev (Build: 20221201), 27880d9b3f7faf90a6ac134b120fe73cbc9037c3
        Php: 7.4.32, mariadb: 10.7.7, OS: Linux 3.10.0-1160.80.1.el7.x86_64 x86_64
        PHPUnit 9.5.26 by Sebastian Bergmann and contributors.
         
        ...............................................................  63 / 114 ( 55%)
        ...................................................             114 / 114 (100%)
         
        Time: 00:02.394, Memory: 48.00 MB
         
        OK (114 tests, 1266 assertions)
        

      Show
      (difficulty: "easy", requires the ability to install a Moodle instance running on a MySQL-ish DB server including that provided by Microsoft Azure, which could be less easier if you're not familiar with Azure resources). Note : the version reported by Moodle, e.g. in the GUI, in the Information column in Site administration > Server > Environment is still the one "wrongly" reported by the PHP client to keep that info consistent w/ what the PHP client is telling about that DB connection. Only the version info has been improved to collect what the DB Server instance tells about its actual version. Test using a "remote" MySQL & MariaDB server provided by Microsoft Azure Create a Azure free account, https://azure.microsoft.com/en-us/free/: you can even use your GitHub account just in case. The "big concern" is that your Credit Card is still required for the verification process and to jump later in the pay-as-you-go pricing when the free trial will expire in time (12 months) or in granted credits (USD/€200 for the first 30 days). MySQL 5.7 Flexible Server instance Then follow https://learn.microsoft.com/en-us/azure/mysql/flexible-server/how-to-deploy-on-azure-free-account to create free instances of MySQL server in Azure, using the Networking > Public access for just testing purposes. Search for "Free Services" to select "Azure Database for MySQL" for a development Workload type : you should create a proper Resource Group in Azure e.g. freetrial-rg and a name which will be used even to expose your instance via FQDN e.g. moodle-mysql57 ; since the connection will be via public network you should create strong admin username and password. SSL is enforced and TLS version is 1.2 but you should disable secure connection enforcement, https://learn.microsoft.com/en-us/azure/mysql/flexible-server/how-to-connect-tls-ssl#disable-ssl-enforcement-on-your-flexible-server , since Moodle doesn't support secure connections out-of-the-box ( MDL-54704 ) yet; otherwise, you'll get a message ending with the following statement: Debug info: Connections using insecure transport are prohibited while --require_secure_transport=ON. Error code: dbconnectionfailed Disclaimer : what described above would be an insane configuration out of the testing purposes described here! You can also add tags to the resources that will be created to optionally simplify the process of deleting them to avoid extra costs e.g.: app : moodle db : mysql version : 57 id : M DL-69581 Five minutes on average is the time required to have the MySQL instance ready ( 4m for the instance, 5m for the firewall rule): your instance will be available as e.g. moodle-mysql57.mysql.database.azure.com . The Moodle database should be manually created (CLI or GUI): CREATE DATABASE `moodle` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; Use the Moodle Docker Toolbox for the tests, forcing later just a change to the DB server coordinate and credentials: # export MOODLE_DOCKER_WWWROOT=/path/to/moodle/branch # export MOODLE_DOCKER_DB=mysql # cp config.docker-template.php $MOODLE_DOCKER_WWWROOT/config.php You can explore what is the result of the SELECT VERSION() execution: # bin/moodle-docker-compose up -d && bin/moodle-docker-compose exec db mysql -h moodle-mysql57.mysql.database.azure.com -u ******** -p'********' -e "SELECT VERSION();" mysql: [Warning] Using a password on the command line interface can be insecure. +------------+ | VERSION() | +------------+ | 5.7.39-log | +------------+ Please note the version, 5.7.39 and the log suffix in the query result. Now you should manually edit $MOODLE_DOCKER_WWWROOT/config.php to set the coordinate and the credentials of the Azure MySQL 5.7 instance: $CFG->dbhost , $CFG->dbuser , $CFG->dbpass . Besides , you need to add a new db option , dbversionfn , set to true : $CFG->dboptions = array ( ... 'dbversionfn' => true, ); Then launch the unit tests - the setup of the e.g. 470 tables ( 7c3188b , version 2022111100.00 ) will be really slow (e.g. 45m ) due to the bandwidth/latency and the micro-sizing of the development instance - and look at the version reported in the header of the tests, still 5.7.39 ( without the suffix): # bin/moodle-docker-compose exec webserver php admin/tool/phpunit/cli/init.php # bin/moodle-docker-compose exec webserver vendor/bin/phpunit lib/dml/tests/dml_test.php Moodle 4.1beta (Build: 20221111), eb24f973b223e541452a1816082a3cbc1570a391 Php: 7.4.32, mysqli: 5.7.39, OS: Linux 3.10.0-1160.76.1.el7.x86_64 x86_64 PHPUnit 9.5.25 #StandWithUkraine   ............................................................... 63 / 114 ( 55%) ................................................... 114 / 114 (100%)   Time: 01:43.901, Memory: 48.00 MB   OK (114 tests, 1261 assertions) # bin/moodle-docker-compose down -v If you try to install Moodle using the GUI, the environment stage will look like: Maria DB 10.3 instance Same applies to MariaDB ( Azure Database for MariaDB servers ) including the Networking and _Security setup, now up to version 10.3 - go ahead even if 4.1 doesn't support it ( MDL-71747 ) - which has no free tier so you'll spend some credits of the free account for the time required to run the tests up to when you'll actually dispose the instance related Azure resources. If you'll name it moodle-mariadb103 it will be available to Moodle with a DB username suffixed by @<instance-name> e.g. ********@moodle-mariadb103 . You should manually edit $MOODLE_DOCKER_WWWROOT/config.php to set the coordinate and the credentials of the Azure MySQL 5.7 instance: $CFG->dbhost , $CFG->dbuser , $CFG->dbpass . Besides , you need to add a new db option , dbversionfn , set to true : $CFG->dboptions = array ( ... 'dbversionfn' => true, ); The Moodle database should be manually created (CLI or GUI): CREATE DATABASE `moodle` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; # export MOODLE_DOCKER_DB=mariadb # bin/moodle-docker-compose up -d && bin/moodle-docker-compose exec db mysql -h moodle-mariadb103.mariadb.database.azure.com -u ********@moodle-mariadb103 -p'********' -e "SELECT VERSION();" mysql: [Warning] Using a password on the command line interface can be insecure. +---------------------+ | VERSION() | +---------------------+ | 10.3.23-MariaDB-log | +---------------------+ Please note the version, 10.3.23 and the two suffixes in the query result, among the two, " -MariaDB " is expected . Then launch the unit tests starting with the initialization: # bin/moodle-docker-compose exec webserver php admin/tool/phpunit/cli/init.php Initialising Moodle PHPUnit test environment... You are already using the latest available Composer version 2.4.4 (stable channel). Installing dependencies from lock file (including require-dev) Verifying lock file contents can be installed on current platform. Nothing to install, update or remove Generating autoload files 53 packages you are using are looking for funding. Use the `composer fund` command to find out more! == Environment == !! database mariadb (5.6.47.0) !! [System] version 10.4 is required and you are running 10.3.23 - As you can see you cannot go further due to the 10.4 constraint for the MariaDB server version but Moodle is able to correctly recognize the DB version as 10.3.23 even if the PHP mysqli driver reports the wrong version 5.6.47.0 - look for the "client version" after the database type mariadb - due to the Azure infra gateway in between which reports back the version of the gateway and not the one of your instance, one of the back-end endpoints of the gateway. # bin/moodle-docker-compose down -v If you try to install Moodle using the GUI, the environment stage will look like: MySQL 8.0 Single Server instance To close with the tests in Azure try to create a MySQL 8.0 instance as Single Server , the type on the retirement path . Please remember to apply also the Network and Security setup required to access the instance from your local environment. If you'll name it moodle-mysql80-singleserver it will be available to Moodle with a DB username suffixed by @<instance-name> e.g. ********@moodle-mysql80-singleserver . You should manually edit $MOODLE_DOCKER_WWWROOT/config.php to set the coordinate and the credentials of the Azure MySQL 5.7 instance: $CFG->dbhost , $CFG->dbuser , $CFG->dbpass . Besides , you need to add a new db option , dbversionfn , set to true : $CFG->dboptions = array ( ... 'dbversionfn' => true, ); The Moodle database should be manually created (CLI or GUI): CREATE DATABASE `moodle` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; # export MOODLE_DOCKER_DB=mysql # bin/moodle-docker-compose up -d && bin/moodle-docker-compose exec db mysql -h moodle-mysql80-singleserver.mysql.database.azure.com -u ********@moodle-mysql80-singleserver -p'********' -e "SELECT VERSION();" mysql: [Warning] Using a password on the command line interface can be insecure. +-----------+ | VERSION() | +-----------+ | 8.0.15 | +-----------+ Please note the version, 8.0.15 . Now create the empty moodle database. # bin/moodle-docker-compose exec db mysql -h moodle-mysql80-singleserver.mysql.database.azure.com -u ********@moodle-mysql80-singleserver -p'********' mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 62809 Server version: 5.6.47.0 Source distribution   Copyright (c) 2000, 2022, Oracle and/or its affiliates.   Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.   Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.   mysql> CREATE DATABASE `moodle` -> DEFAULT CHARACTER SET utf8mb4 -> COLLATE utf8mb4_unicode_ci; Query OK, 1 row affected (0.05 sec)   mysql> exit Bye Please note the client connection i.e. the version reported above is 5.6.47.0 which is clearly wrong due to the presence of the Azure infra gateway. Then launch the unit tests - the setup of the e.g. 470 tables ( 7c3188b , version 2022111100.00 ) will be really slow due to the bandwidth/latency - and look at the version reported in the header of the tests, still 8.0.15 ( without any suffix): # bin/moodle-docker-compose exec webserver php admin/tool/phpunit/cli/init.php # bin/moodle-docker-compose exec webserver vendor/bin/phpunit lib/dml/tests/dml_test.php Moodle 4.1beta (Build: 20221111), eb24f973b223e541452a1816082a3cbc1570a391 Php: 7.4.32, mysqli: 8.0.15, OS: Linux 3.10.0-1160.76.1.el7.x86_64 x86_64 PHPUnit 9.5.25 #StandWithUkraine   ............................................................... 63 / 114 ( 55%) ................................................... 114 / 114 (100%)   Time: 01:32.116, Memory: 48.00 MB   OK (114 tests, 1261 assertions) If you try to install Moodle using the GUI, the environment stage will look like: Prepare the "local" test environment You can use the Moodle Docker Toolbox: # export MOODLE_DOCKER_WWWROOT=/path/to/moodle/branch # cp config.docker-template.php $MOODLE_DOCKER_WWWROOT/config.php Regression tests on "plain" MySQL Select the MySQL DB Server in your Moodle Docker Toolbox (defaults to 5.7 ) and take note of the DB version as reported by the CLI comand (here: 5.7.40 ): # export MOODLE_DOCKER_DB=mysql # bin/moodle-docker-compose up -d && bin/moodle-docker-compose exec db mysql -V mysql Ver 14.14 Distrib 5.7.40, for Linux (x86_64) using EditLine wrapper Run the DML tests and verify both no failures and the match between the version reported in the PHPUnit CLI output and the one from the CLI comand output above ( 5.7.40 ): # bin/moodle-docker-wait-for-db # bin/moodle-docker-compose exec webserver php admin/tool/phpunit/cli/init.php # bin/moodle-docker-compose exec webserver vendor/bin/phpunit lib/dml/tests/dml_test.php Moodle 4.2dev (Build: 20221201), 27880d9b3f7faf90a6ac134b120fe73cbc9037c3 Php: 7.4.32, mysqli: 5.7.40, OS: Linux 3.10.0-1160.80.1.el7.x86_64 x86_64 PHPUnit 9.5.26 by Sebastian Bergmann and contributors.   ............................................................... 63 / 114 ( 55%) ................................................... 114 / 114 (100%)   Time: 00:02.563, Memory: 68.50 MB   OK (114 tests, 1266 assertions) Stop the previous test instance, select the MySQL DB Server version 8.0 in your Moodle Docker Toolbox and take note of the DB version as reported by the CLI comand (here: 8.0.31 ): # bin/moodle-docker-compose down -v # export MOODLE_DOCKER_DB_VERSION=8.0 # bin/moodle-docker-compose up -d && bin/moodle-docker-compose exec db mysql -V mysql Ver 8.0.31 for Linux on x86_64 (MySQL Community Server - GPL) Run the DML tests and verify both no failures and the match between the version reported in the PHPUnit CLI output and the one from the CLI comand output above ( 8.0.31 ): # bin/moodle-docker-wait-for-db # bin/moodle-docker-compose exec webserver php admin/tool/phpunit/cli/init.php # bin/moodle-docker-compose exec webserver vendor/bin/phpunit lib/dml/tests/dml_test.php Moodle 4.2dev (Build: 20221201), 27880d9b3f7faf90a6ac134b120fe73cbc9037c3 Php: 7.4.32, mysqli: 8.0.31, OS: Linux 3.10.0-1160.80.1.el7.x86_64 x86_64 PHPUnit 9.5.26 by Sebastian Bergmann and contributors.   ............................................................... 63 / 114 ( 55%) ................................................... 114 / 114 (100%)   Time: 00:03.956, Memory: 48.00 MB   OK (114 tests, 1266 assertions) Regression tests on "plain" MariaDB Stop the previous test instance, select the MariaDB Server in your Moodle Docker Toolbox and take note of the DB version as reported by the CLI comand (here: 10.7.7 . -MariaDB is just an expected suffix): # bin/moodle-docker-compose down -v # unset MOODLE_DOCKER_DB_VERSION # export MOODLE_DOCKER_DB=mariadb # bin/moodle-docker-compose up -d && bin/moodle-docker-compose exec db mysql -V mysql Ver 15.1 Distrib 10.7.7-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2 Run the DML tests and verify both no failures and the match between the version reported in the PHPUnit CLI output (please note the lack of -MariaDB suffix too ) and the one from the CLI comand output above ( 10.7.7 ): # bin/moodle-docker-wait-for-db # bin/moodle-docker-compose exec webserver php admin/tool/phpunit/cli/init.php # bin/moodle-docker-compose exec webserver vendor/bin/phpunit lib/dml/tests/dml_test.php Moodle 4.2dev (Build: 20221201), 27880d9b3f7faf90a6ac134b120fe73cbc9037c3 Php: 7.4.32, mariadb: 10.7.7, OS: Linux 3.10.0-1160.80.1.el7.x86_64 x86_64 PHPUnit 9.5.26 by Sebastian Bergmann and contributors.   ............................................................... 63 / 114 ( 55%) ................................................... 114 / 114 (100%)   Time: 00:02.394, Memory: 48.00 MB   OK (114 tests, 1266 assertions)

    Description

      This isn't really a Moodle bug, but is something it may be worth considering trying to work around or mitigate by changing the way the version is checked. I discovered this while upgrading a Moodle 3.5 installation to 3.9.1, but it probably affects other versions as well.

      The Azure managed instances of MairaDB wrongly reports it's version number when you connect by design! You can demonstrate this using the command line client to connect to the database, which gives the following output:

       

      Welcome to the MariaDB monitor. Commands end with ; or \g.
      Your MySQL connection id is 64799
      Server version: 5.6.42.0 MariaDB Server
      Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
      Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
      MySQL [(none)]> SELECT @@version ;
      +-----------------+
      | @@version |
      +-----------------+
      | 10.2.32-MariaDB |
      +-----------------+
      1 row in set (0.01 sec)
      

       

      The initial connection reports 5.6.42.0, but a subsequent query of the version reports 10.2.32 (which is the correct version for this instance). This blocks the installation of Moodle 3.9 which (not unreasonably) only checks the version reported when the connection is made. The work around is to either mis-configure the database as MySQL (which allows version 5.6) or patch the minimum version of MariaDB allowed.

      The inconsistency is documented by Microsoft here:

      https://docs.microsoft.com/en-us/azure/mariadb/concepts-supported-versions

      I have submitted a bug report to Microsoft, but the response was that I should change my software to work around their bug!

      Attachments

        Issue Links

          Activity

            People

              matteo Matteo Scaramuccia
              tim1mw Tim Williams
              Eloy Lafuente (stronk7) Eloy Lafuente (stronk7)
              David Woloszyn, Huong Nguyen, Jake Dallimore, Meirza, Michael Hawkins, Raquel Ortega, Safat Shahin, Stevani Andolo, Matteo Scaramuccia, David Woloszyn, Huong Nguyen, Jake Dallimore, Meirza, Michael Hawkins, Raquel Ortega, Safat Shahin, Stevani Andolo
              Votes:
              4 Vote for this issue
              Watchers:
              8 Start watching this issue

              Dates

                Created:
                Updated:

                Time Tracking

                  Estimated:
                  Original Estimate - 0 minutes
                  0m
                  Remaining:
                  Remaining Estimate - 0 minutes
                  0m
                  Logged:
                  Time Spent - 1 hour
                  1h