moodle-plugin-ci
moodle-plugin-ci copied to clipboard
moodlehq
Request: add Oracle DB support
I checked the created issues and I didn't realize some issue is related to this, so I'm creating it.
As stated in Moodle Dev Telegram chat, It would be really useful to support Oracle (this issue) and mssql (issue #92).
I've tried adding Oracle support in one plugin and then checked that only mysql, pgsql and mariadb are supported db-types.
As I can see, I think it is necessary mainly to add an implementation class of any db-type under https://github.com/moodlehq/moodle-plugin-ci/tree/master/src/Installer/Database.
My original idea was to use the docker image for Oracle used under moodlehq/moodle-docker. However, Github Actions require public docker hub images (if I understand it correct) and it seems it isn't. So, I was trying another db image, gvenzl/oracle-xe (supporting 18c and 11 Oracle versions). I successfully managed to build the image, but then I realized moodle-plugin-ci does not support it.
My last trials can be found here. One example is this:
One really useful point of that oracle image is that is has a builtin checker for db being ready. It takes a bit of time however:
The important bits of the yml are the following:
services:
oracle:
image: gvenzl/oracle-xe
env:
ORACLE_RANDOM_PASSWORD: yes
ORACLE_DATABASE: moodle
APP_USER: moodle
APP_USER_PASSWORD: moodle
ports:
- 1521:1521
options: >-
--health-cmd healthcheck.sh
--health-interval 20s
--health-timeout 10s
--health-retries 10
This image requires non-empty passwords, so I needed to set up non-empty passwords on the ci installing:
- name: Install moodle-plugin-ci
run: |
moodle-plugin-ci install --plugin ./plugin --db-host=127.0.0.1 --db-user moodle --db-pass moodle --db-name moodle
env:
DB: ${{ matrix.database }}
MOODLE_BRANCH: ${{ matrix.moodle-branch }}
Looking for a good example of adding support for a new db-type, found this commit b82d0ac (adding mariadb support).
I was facing problems defining new database and new users. Looking at the container-entrypoint.sh from gvenzl/oracle-xe I reached to this pieces that works from CLI (what is expected to be used by the getCreateDatabaseCommand() method. However, it is specific to this oracle image, since it requires to define file paths. For instance, moodle-db-oracle is based on another image with different file paths.
Creating database (dbname to be replaced by the proper db name):
sqlplus -s / as sysdba <<EOF
-- Exit on any errors
WHENEVER SQLERROR EXIT SQL.SQLCODE
CREATE PLUGGABLE DATABASE dbname \
ADMIN USER PDBADMIN IDENTIFIED BY "dbname" \
FILE_NAME_CONVERT=('pdbseed','dbname') \
DEFAULT TABLESPACE USERS \
DATAFILE '${ORACLE_BASE}/oradata/${ORACLE_SID}/dbname/users01.dbf' \
SIZE 1m AUTOEXTEND ON NEXT 10m MAXSIZE UNLIMITED;
ALTER PLUGGABLE DATABASE dbname OPEN READ WRITE;
ALTER PLUGGABLE DATABASE dbname SAVE STATE;
exit;
EOF
Creating a user to the default db (username and password to be replaced by the proper values):
sqlplus -s / as sysdba <<EOF
-- Exit on any errors
WHENEVER SQLERROR EXIT SQL.SQLCODE
ALTER SESSION SET CONTAINER=XEPDB1;
CREATE USER username IDENTIFIED BY "password" QUOTA UNLIMITED ON USERS;
GRANT CONNECT, RESOURCE, CREATE VIEW, CREATE MATERIALIZED VIEW TO username;
exit;
EOF
Create user on the specific moodle db (username and password to be replaced by the proper values):
sqlplus -s / as sysdba <<EOF
-- Exit on any errors
WHENEVER SQLERROR EXIT SQL.SQLCODE
ALTER SESSION SET CONTAINER=dbname;
CREATE USER username IDENTIFIED BY "password" QUOTA UNLIMITED ON USERS;
GRANT CONNECT, RESOURCE, CREATE VIEW, CREATE MATERIALIZED VIEW TO username;
exit;
EOF
Manually, I created a container locally with this command:
docker run -p 1521:1521 -e ORACLE_RANDOM_PASSWORD=yes -e APP_USER=moodle -e APP_USER_PASSWORD=moodle gvenzl/oracle-xe
Then, after being created, use the above commands inside docker exec -it container-name bash. They will work.
Finally, as a connection testing I run:
bash-4.4$ sqlplus -s username/password@localhost/dbname <<EOF
> -- Exit on any errors
> WHENEVER SQLERROR EXIT SQL.SQLCODE
>
> ALTER SESSION SET CONTAINER=dbname;
>
> select 'success' as result from dual;
> exit;
> EOF
Session altered.
RESULT
-------
success
bash-4.4$
So, I think there should be the 3 commands altogether as a result of the invokation of OracleDatabase::getCreateDatabaseCommand() method.
Finally, retried with moodlehq/moodle-db-oracle and it works on GHA. Maybe yesterday there was some networking problem or so when I tried.
Finally, retried with moodlehq/moodle-db-oracle and it works on GHA. Maybe yesterday there was some networking problem or so when I tried.
Shouldn't that be -r2: https://hub.docker.com/r/moodlehq/moodle-db-oracle-r2 ?
I'm facing this problem:
[info] Initialize test suite
[debug] Initialize PHPUnit
RUN php -d log_errors=1 -d error_log=NULL /home/runner/work/moodle-plugin-ci/moodle-plugin-ci/moodle/admin/tool/behat/cli/util_single_run.php --install
OUT !!! <p>Error: database table prefix is too long (oracle)</p>
OUT <p>The site administrator must fix this problem. Maximum length for table prefixes in oracle is 2 characters.</p> !!!
OUT
OUT Debug info:
OUT Error code: prefixtoolong
OUT Stack trace: * line 142 of /lib/dml/oci_native_moodle_database.php: dml_exception thrown
OUT * line 340 of /lib/dmllib.php: call to oci_native_moodle_database->connect()
OUT * line 630 of /lib/setup.php: call to setup_DB()
OUT * line 121 of /admin/tool/behat/cli/util_single_run.php: call to require()
OUT
RES 1 Command did not run successfully
RUN php -d log_errors=1 -d error_log=NULL /home/runner/work/moodle-plugin-ci/moodle-plugin-ci/moodle/admin/tool/phpunit/cli/util.php --install
OUT !!! <p>Error: database table prefix is too long (oracle)</p>
OUT <p>The site administrator must fix this problem. Maximum length for table prefixes in oracle is 2 characters.</p> !!!
OUT
OUT Debug info:
OUT Error code: prefixtoolong
OUT Stack trace: * line 142 of /lib/dml/oci_native_moodle_database.php: dml_exception thrown
OUT * line 340 of /lib/dmllib.php: call to oci_native_moodle_database->connect()
OUT * line 630 of /lib/setup.php: call to setup_DB()
OUT * line 219 of /lib/phpunit/bootstrap.php: call to require()
OUT * line 83 of /admin/tool/phpunit/cli/util.php: call to require()
OUT
RES 1 Command did not run successfully
I cannot reach the point where this prefix is set up.
Hi @jpahullo, give a look at here since it will be used here. Guessing, a fix could be:
...
$CFG->prefix = 'm_';
...
// PHPUnit settings.
$CFG->phpunit_prefix = 'u_';
...
// Behat settings.
$CFG->behat_prefix = 'b_';
HTH, Matteo
Hi @jpahullo,
you could borrow the "way" to install oci8 from https://github.com/moodlehq/moodle-php-apache:
- required scripts from here, maybe w/ some deps
- the way to run them to let GHA play with it
or give https://github.com/marketplace/actions/setup-php-action#heavy_plus_sign-php-extension-support a try:
- name: Setup PHP with fail-fast
uses: shivammathur/setup-php@v2
with:
php-version: '7.4'
extensions: oci8
env:
fail-fast: true
HTH, Matteo
Hi @scara ! It seems we "read" our minds. I was thinking exactly like that, or either from the ci process from moodle.org.
Thanks for the info! Sure it will help!
Other option is to build docker images with all dependencies and extensions included, like we did at https://hub.docker.com/r/sred/php-apache-oracle/tags?page=1&ordering=last_updated, so that image is already built, gaining in set up time and reducing total time cost of testing and deployment of testing environments (set up once, and reused any number of times)
I tried the second options and at least for php 7.4 it works correctly. Now, I'm facing troubles autenticating the user. Trying putting credentials on the install command.
I'm putting the credentials into the OracleDatabase.php class in order to simplify the set up of CI on GHA
It seems now it will work somehow. However, I can see this error output:
[debug] Initialize PHPUnit
RUN php -d log_errors=1 -d error_log=NULL /home/runner/work/moodle-plugin-ci/moodle-plugin-ci/moodle/admin/tool/behat/cli/util_single_run.php --install
ERR Default exception handler: Exception - Call to a member function is_temptable() on null Debug:
ERR Error code: generalexceptionmessage
ERR * line 229 of /lib/ddl/sql_generator.php: Error thrown
ERR * line 100 of /lib/ddl/database_manager.php: call to sql_generator->table_exists()
ERR * line 466 of /question/engine/upgrade/upgradelib.php: call to database_manager->table_exists()
ERR * line 736 of /lib/environmentlib.php: call to quiz_attempts_upgraded()
ERR * line 482 of /lib/environmentlib.php: call to environment_custom_checks()
ERR * line 106 of /lib/environmentlib.php: call to environment_check()
ERR * line 455 of /lib/installlib.php: call to check_moodle_environment()
ERR * line 91 of /lib/behat/classes/util.php: call to install_cli_database()
ERR * line 162 of /admin/tool/behat/cli/util_single_run.php: call to behat_util::install_site()
ERR
ERR
OUT !!! Exception - Call to a member function is_temptable() on null !!!
OUT !!
OUT Error code: generalexceptionmessage !!
OUT !! Stack trace: * line 229 of /lib/ddl/sql_generator.php: Error thrown
OUT * line 100 of /lib/ddl/database_manager.php: call to sql_generator->table_exists()
OUT * line 466 of /question/engine/upgrade/upgradelib.php: call to database_manager->table_exists()
OUT * line 736 of /lib/environmentlib.php: call to quiz_attempts_upgraded()
OUT * line 482 of /lib/environmentlib.php: call to environment_custom_checks()
OUT * line 106 of /lib/environmentlib.php: call to environment_check()
OUT * line 455 of /lib/installlib.php: call to check_moodle_environment()
OUT * line 91 of /lib/behat/classes/util.php: call to install_cli_database()
OUT * line 162 of /admin/tool/behat/cli/util_single_run.php: call to behat_util::install_site()
OUT !!
OUT
RES 1 Command did not run successfully
RUN php -d log_errors=1 -d error_log=NULL /home/runner/work/moodle-plugin-ci/moodle-plugin-ci/moodle/admin/tool/phpunit/cli/util.php --install
OUT -->System
on lib/ddl/sql_generator.php:229 is:
229 if ($this->temptables->is_temptable($tablename)) {
230 return true;
231 }
and $this->temptables can be null, as it is instantiated. So, maybe this is a bug to solve in Moodle core?
The error makes fail the step, even though Moodle is installed properly.
How is it supposed to proceed?
Hi @jpahullo,
I'd test the same Moodle core i.e branch MOODLE_311_STABLE using https://github.com/moodlehq/moodle-docker, locally to you machine (MOODLE_DOCKER_DB=oracle, MOODLE_DOCKER_PHP_VERSION=7.4) to exclude any local issue but a Moodle code one, when running on Oracle.
I doubt there is an issue when installing Moodle in Oracle or launching units but... who knows?
At a quick glance, wondering if the issue is in just the Behat install i.e. you should test with the tool above a Behat run under Oracle:
[info] Starting install
[info] Cloning Moodle
...
[info] Moodle assets
[debug] Creating Moodle data directories
...
[debug] Create Moodle database
...
[debug] Creating Moodle's config file
[debug] <?php // Moodle configuration file
...
[info] Install plugins
...
[info] Installing local_ci
[info] Copying plugin from /home/runner/work/moodle-plugin-ci/moodle-local_ci to /home/runner/work/moodle-plugin-ci/moodle-plugin-ci/moodle/local/ci
[debug] Created config file at /home/runner/work/moodle-plugin-ci/moodle-plugin-ci/moodle/local/ci/.moodle-plugin-ci.yml
...
[info] Install global dependencies
...
[info] Install npm dependencies
...
[info] Initialize test suite
RES Command ran successfully
...
RUN php -d log_errors=1 -d error_log=NULL /home/runner/work/moodle-plugin-ci/moodle-plugin-ci/moodle/admin/tool/behat/cli/util_single_run.php --install
ERR Default exception handler: Exception - Call to a member function is_temptable() on null Debug:
HTH, Matteo
Hi @scara !
I created a patch for the tracker's issue. The {{$this->temptables}} from sql_generator.php can be null, so I think it must be checked if it is null, bypass it.
I will try to run it locally and check if the error is reproduced, either installing Moodle, phpunit tables or behat tables.
Thanks!
Hi @jpahullo,
thanks!
I'm not completely sold on that check: I mean, it's sane but I wonder if that null comes from running Behat on Oracle, which could be broken for other reasons.
For the reader convenience, this is the Tracker ref: MDL-72318.
HTH, Matteo
Hi!
I'm finding that local in my computer I cannot build the oracle db instance.
I'm using the latest version of moodle-docker and the master branch from moodle.
The logs of the container oracle db shows this:
$ docker logs -f moodledocker_db_1
Starting Oracle Net Listener.
Starting Oracle Database 11g Express Edition instance.
ERROR:
ORA-12547: TNS:lost contact
SP2-0306: Invalid option.
Usage: CONN[ECT] [{logon|/|proxy} [AS {SYSDBA|SYSOPER|SYSASM}] [edition=value]]
where <logon> ::= <username>[/<password>][@<connect_identifier>]
<proxy> ::= <proxyuser>[<username>][/<password>][@<connect_identifier>]
ERROR:
ORA-12547: TNS:lost contact
SP2-0306: Invalid option.
Usage: CONN[ECT] [{logon|/|proxy} [AS {SYSDBA|SYSOPER|SYSASM}] [edition=value]]
where <logon> ::= <username>[/<password>][@<connect_identifier>]
<proxy> ::= <proxyuser>[<username>][/<password>][@<connect_identifier>]
Shutting down Oracle Database 11g Express Edition instance.
Stopping Oracle Net Listener.
Starting Oracle Net Listener.
Starting Oracle Database 11g Express Edition instance.
/usr/sbin/startup.sh: running /docker-entrypoint-initdb.d/01-moodle-user.sql
SQL*Plus: Release 11.2.0.2.0 Production on Thu Aug 12 08:45:46 2021
Copyright (c) 1982, 2011, Oracle. All rights reserved.
ERROR:
ORA-12547: TNS:lost contact
Enter user-name: Enter password:
ERROR:
ORA-12547: TNS:lost contact
Enter user-name:
Starting Oracle Net Listener.
Starting Oracle Database 11g Express Edition instance.
ERROR:
ORA-12547: TNS:lost contact
SP2-0306: Invalid option.
Usage: CONN[ECT] [{logon|/|proxy} [AS {SYSDBA|SYSOPER|SYSASM}] [edition=value]]
where <logon> ::= <username>[/<password>][@<connect_identifier>]
<proxy> ::= <proxyuser>[<username>][/<password>][@<connect_identifier>]
ERROR:
ORA-12547: TNS:lost contact
SP2-0306: Invalid option.
Usage: CONN[ECT] [{logon|/|proxy} [AS {SYSDBA|SYSOPER|SYSASM}] [edition=value]]
where <logon> ::= <username>[/<password>][@<connect_identifier>]
<proxy> ::= <proxyuser>[<username>][/<password>][@<connect_identifier>]
Shutting down Oracle Database 11g Express Edition instance.
Stopping Oracle Net Listener.
Starting Oracle Net Listener.
Starting Oracle Database 11g Express Edition instance.
/usr/sbin/startup.sh: running /docker-entrypoint-initdb.d/01-moodle-user.sql
SQL*Plus: Release 11.2.0.2.0 Production on Thu Aug 12 08:51:22 2021
Copyright (c) 1982, 2011, Oracle. All rights reserved.
ERROR:
ORA-12547: TNS:lost contact
Enter user-name: Enter password:
ERROR:
ORA-12547: TNS:lost contact
Enter user-name:
And the checker shows again and again Waiting for oracle to come up....
I'm using this settings:
MOODLE_DOCKER_WWWROOT=/path/to/moodle.org/moodle
MOODLE_DOCKER_WEB_PORT=8000
MOODLE_DOCKER_DB=oracle
MOODLE_DOCKER_PHP_VERSION=7.4
And the containers shows this:
$ docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
bd1a395eeb82 moodlehq/moodle-php-apache:7.3 "docker-php-entrypoi…" 13 minutes ago Up 13 minutes 127.0.0.1:8000->80/tcp moodledocker_webserver_1
bb1b8d09405d moodlehq/moodle-db-oracle-r2 "/bin/sh -c '/usr/sb…" 13 minutes ago Up 8 minutes 22/tcp, 1521/tcp, 8080/tcp moodledocker_db_1
80e50ad9c101 mailhog/mailhog "MailHog" 13 minutes ago Up 13 minutes 1025/tcp, 8025/tcp moodledocker_mailhog_1
828a77f2c355 moodlehq/moodle-exttests "docker-php-entrypoi…" 13 minutes ago Up 13 minutes 80/tcp moodledocker_exttests_1
a432fd8974df selenium/standalone-firefox:2.53.1 "/opt/bin/entry_poin…" 13 minutes ago Up 13 minutes 4444/tcp moodledocker_selenium_1
Any suggestion?
I stopped and removed all containers and built them again, with the same result.
Entering to the oracle db container, I cannot login into db with the sqlplus, like sqlplus system/oracle as sysdba. Any combination fails to log in, using credentials from https://github.com/wnameless/docker-oracle-xe-11g.
Removing local docker image and trying again. I'm trying individual commands from startup.sh from moodlehq/moodle-db-oracle and they didn't work from command line inside the oracle container
@jpahullo - I'm just confirming that Oracle with PHP7.4 tests do work locally using unmodified Moodle tools/source:
$ export -p | grep MOODLE_DOCKER
declare -x MOODLE_DOCKER_DB="oracle"
declare -x MOODLE_DOCKER_PHP_VERSION="7.4"
declare -x MOODLE_DOCKER_SELENIUM_VNC_PORT="54321"
declare -x MOODLE_DOCKER_WWWROOT="/opt/moodle/master/src"
$ docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
07063bd99166 moodlehq/moodle-php-apache:7.4 "docker-php-entrypoi…" 21 minutes ago Up 21 minutes 127.0.0.1:8000->80/tcp docker_webserver_1
017d11157b13 selenium/standalone-firefox-debug:2.53.1 "/opt/bin/entry_poin…" 21 minutes ago Up 21 minutes 4444/tcp, 127.0.0.1:54321->5900/tcp docker_selenium_1
99a09014f516 moodlehq/moodle-db-oracle-r2 "/bin/sh -c '/usr/sb…" 21 minutes ago Up 21 minutes 22/tcp, 1521/tcp, 8080/tcp docker_db_1
49364d8689d5 moodlehq/moodle-exttests "docker-php-entrypoi…" 21 minutes ago Up 21 minutes 80/tcp docker_exttests_1
9fd9edcf031e mailhog/mailhog "MailHog" 21 minutes ago Up 21 minutes 1025/tcp, 8025/tcp docker_mailhog_1
$ bin/moodle-docker-compose exec webserver phpdbg -qrr vendor/bin/phpunit --filter test_map_userinfo_to_fields
Moodle 4.0dev (Build: 20210805)
Php: 7.4.21, oci: 11.2.0.2.0, OS: Linux 4.15.0-142-generic x86_64
PHPUnit 9.5.4 by Sebastian Bergmann and contributors.
.... 4 / 4 (100%)
Time: 00:00.632, Memory: 599.00 MB
OK (4 tests, 8 assertions)
$ bin/moodle-docker-compose exec --user www-data webserver php admin/tool/behat/cli/run.php --name="When you choose custom fields, these should be displayed in the 'Participants' screen"
Running single behat site:
Moodle 4.0dev (Build: 20210805)
Php: 7.4.21, oci: 11.2.0.2.0, OS: Linux 4.15.0-142-generic x86_64
Run optional tests:
- Accessibility: No
Server OS "Linux", Browser: "firefox"
Started at 12-08-2021, 17:49
.........
1 scenario (1 passed)
9 steps (9 passed)
0m9.50s (52.97Mb)
I agree with @scara - this doesn't seem like it should require any changes elsewhere, so the error is likely somewhere in the changes proposed here
No success. Still failing. I open an issue on the moodlehq/moodle-db-oracle.
Ups! Thanks for the info @paulholden. Incredible.
If you do docker images, which is the image id for the oracle? Mine is this:
moodlehq/moodle-db-oracle-r2 latest cbccc731cb4a 7 months ago 2.1GB
In addition, please, @paulholden, do you think you could provide us the logs for the Moodle installation for phpunit tests and behat tests? I think tests inside the moodle-plugin-ci would pass, but it fails because of that exception about invoking a method on null. I mean, the problem could appear only during the behat iinitialization or phpunit initialization of their db tables. Thanks in advance.
Hi @jpahullo,
moodlehq/moodle-db-oracle-r2 latest cbccc731cb4a 7 months ago 2.1GB
On my Docker Engine:
$ docker pull moodlehq/moodle-db-oracle-r2
Using default tag: latest
latest: Pulling from moodlehq/moodle-db-oracle-r2
5667fdb72017: Pull complete
d83811f270d5: Pull complete
ee671aafb583: Pull complete
7fc152dfb3a6: Pull complete
51896e240a72: Pull complete
64e16b57ee0a: Pull complete
18e53bbb289b: Pull complete
Digest: sha256:dbcdb20e1919abb180235fd0d3d30998de58de116bdcaf955d2e169de2700dfb
Status: Downloaded newer image for moodlehq/moodle-db-oracle-r2:latest
docker.io/moodlehq/moodle-db-oracle-r2:latest
$ docker images | grep oracle
moodlehq/moodle-db-oracle-r2 latest cbccc731cb4a 7 months ago 2.1GB
I'll give it a try on my side within this day.
HTH, Matteo