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

logstore_database: Add ability to not send database options

    XMLWordPrintable

    Details

    • Testing Instructions:
      Hide
      Setup
      External DB
      1. Create an external database for logging.
        1. On the same PostgreSQL server, create a separate DB for logging (e.g. externaldblogs).
        2. On that DB, create a table similar to the structure of "mdl_logstore_standard_log". Set an appropriate name (e.g. logstore_standard_log_external).
      External DB logstore
      1. Log in to moodle as admin
      2. Navigate to "Site administration / Plugins / Logging / Manage log stores" and enable "External database log"
      3. Configure the "External database log" and set the following accordingly:
        • dbdriver - PostgreSQL (native/pgsql)
        • dbhost - Server's hostname/IP
        • dbuser - Username (e.g. postgres)
        • dbpass - Password (e.g. moodle)
        • dbname - The external DB name (e.g. externaldblogs)
        • dbtable - The log table name (e.g. logstore_standard_log_external)
      4. Save the changes.
      5. Click "Test connection"
        • Confirm that you can successfully connect to the external logstore DB.
      PgBouncer
      1. Install PgBouncer. If on Ubuntu, just do "sudo apt install pgbouncer"
      2. Edit /etc/pgbouncer/pgbouncer.ini.
      3. Add the external logstore DB and your moodle DB under the "[databases]" section.
        e.g.

        externaldblogs = dbname=externaldblogs
        integrationmaster = dbname=integrationmaster
        

      4. Under the "[pgbouncer]" section, set "auth_type" to "md5".
      5. Save the file
      6. Edit /etc/pgbouncer/userlist.txt.
      7. Add the username and password for the DB.
        e.g.

        "postgres" "moodle"
        

      8. Save the file.
      9. Start the pgbouncer service.
        e.g.

        sudo service start pgbouncer
        

      10. Try to login.
        e.g.

        psql externaldblogs -h localhost -p 6432 -U postgres
        

        • Confirm that you can successfully connect given you enter the correct password.
      Test!
      Plugin
      1. Back to your moodle site, navigate to to "Site administration / Plugins / Logging / External database log"
      2. Set "dbport" to 6432 (PgBouncer's listen_port)
      3. Make sure "dbhandlesoptions" is unticked and save the changes.
      4. Click "Test connection"
        • Confirm that you can't connect to the external logstore DB.
      5. Close the window and go back to the "External database log" settings page.
      6. This time, tick "dbhandlesoptions" and save the changes.
      7. Click "Test connection"
        • Confirm that you can successfully connect to the external logstore DB.
      8. Set "dbport" to blank and save changes.
      9. Click again on "Test connection"
        • Confirm that you can successfully connect to the external logstore DB.
      PHPUnit
      1. After the previous section, clear the "dbport" field and untick the "dbhandlesoptions" setting in the External database log admin page.
      2. Edit config.php and set the following:

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

      3. Run unit tests for "admin/tool/log/store/database/tests/store_test.php".
        • Confirm the test passes.
      4. Edit config.php and set the following:

        $CFG->dboptions = array (
            'dbhandlesoptions' => true,
            'dbport' => 6432,
        );
        

      5. Run unit tests for "admin/tool/log/store/database/tests/store_test.php".
        • Confirm the test passes.
      6. Set the following:

        $CFG->dboptions = array (
            'dbhandlesoptions' => false,
        );
        

      7. Run unit tests for "admin/tool/log/store/database/tests/store_test.php".
        • Confirm the test passes.
      Show
      Setup External DB Create an external database for logging. On the same PostgreSQL server, create a separate DB for logging (e.g. externaldblogs). On that DB, create a table similar to the structure of " mdl_logstore_standard_log ". Set an appropriate name (e.g. logstore_standard_log_external). External DB logstore Log in to moodle as admin Navigate to " Site administration / Plugins / Logging / Manage log stores " and enable " External database log " Configure the " External database log " and set the following accordingly: dbdriver - PostgreSQL (native/pgsql) dbhost - Server's hostname/IP dbuser - Username (e.g. postgres) dbpass - Password (e.g. moodle) dbname - The external DB name (e.g. externaldblogs) dbtable - The log table name (e.g. logstore_standard_log_external) Save the changes. Click " Test connection " Confirm that you can successfully connect to the external logstore DB. PgBouncer Install PgBouncer. If on Ubuntu, just do " sudo apt install pgbouncer " Edit /etc/pgbouncer/pgbouncer.ini. Add the external logstore DB and your moodle DB under the " [databases] " section. e.g. externaldblogs = dbname=externaldblogs integrationmaster = dbname=integrationmaster Under the " [pgbouncer] " section, set " auth_type " to " md5 ". Save the file Edit /etc/pgbouncer/userlist.txt. Add the username and password for the DB. e.g. "postgres" "moodle" Save the file. Start the pgbouncer service. e.g. sudo service start pgbouncer Try to login. e.g. psql externaldblogs -h localhost -p 6432 -U postgres Confirm that you can successfully connect given you enter the correct password. Test! Plugin Back to your moodle site, navigate to to " Site administration / Plugins / Logging / External database log " Set " dbport " to 6432 (PgBouncer's listen_port) Make sure " dbhandlesoptions " is unticked and save the changes. Click " Test connection " Confirm that you can't connect to the external logstore DB. Close the window and go back to the " External database log " settings page. This time, tick " dbhandlesoptions " and save the changes. Click " Test connection " Confirm that you can successfully connect to the external logstore DB. Set " dbport " to blank and save changes. Click again on " Test connection " Confirm that you can successfully connect to the external logstore DB. PHPUnit After the previous section, clear the " dbport " field and untick the " dbhandlesoptions " setting in the External database log admin page. Edit config.php and set the following: $CFG->dboptions = array ( 'dbhandlesoptions' => true, ); Run unit tests for " admin/tool/log/store/database/tests/store_test.php ". Confirm the test passes. Edit config.php and set the following: $CFG->dboptions = array ( 'dbhandlesoptions' => true, 'dbport' => 6432, ); Run unit tests for " admin/tool/log/store/database/tests/store_test.php ". Confirm the test passes. Set the following: $CFG->dboptions = array ( 'dbhandlesoptions' => false, ); Run unit tests for " admin/tool/log/store/database/tests/store_test.php ". Confirm the test passes.
    • Affected Branches:
      MOODLE_32_STABLE, MOODLE_33_STABLE, MOODLE_34_STABLE
    • Fixed Branches:
      MOODLE_32_STABLE, MOODLE_33_STABLE
    • Pull from Repository:
    • Pull Master Branch:

      Description

      In the core dml:
      PostgreSQL connections now use advanced options to reduce connection overhead. These options are not compatible with some connection poolers. The dbhandlesoptions parameter has been added to allow the database to configure the required defaults.

      This item adds a setting to the logstore_database plugin to let you set the same flag for your destination database - without it you won't be able to ship logs to a postgresql database with a pgbouncer frontend.

        Attachments

          Issue Links

            Activity

              People

              • Votes:
                0 Vote for this issue
                Watchers:
                3 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved:
                  Fix Release Date:
                  10/Jul/17