Azure hosted MariaDB and MySQL instances report the wrong version number

(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)