DocumentationAEM 6.4Communities Guide

MySQL Configuration for Enablement Features mysql-configuration-for-enablement-features

Last update: Wed May 03 2023 00:00:00 GMT+0000 (Coordinated Universal Time)

CREATED FOR:

  • Admin
CAUTION
AEM 6.4 has reached the end of extended support and this documentation is no longer updated. For further details, see our technical support periods. Find the supported versions here.

MySQL is a relational database primarily used for SCORM tracking and reporting data for enablement resources. Included are tables for other features such as tracking video pause/resume.

These instructions describe how to connect to the MySQL server, establish the enablement database, and populate the database with initial data.

Requirements requirements

Before configuring MySQL for Communities’ enablement feature, be sure to

Installing MySQL installing-mysql

MySQL should be downloaded and installed following the instructions for the target OS.

Lower case table names lower-case-table-names

As SQL is case insensitive, for case sensitive operating systems, it is necessary to include a setting to lower case all table names.

For example, to specify all lower case table names on a Linux OS:

  • Edit file /etc/my.cnf
  • In the [mysqld] section, add the following line:
    lower_case_table_names = 1

UTF8 character set utf-character-set

To provide better multilingual support, it is necessary to use the UTF8 character set.

Change MySQL to have UTF8 as its character set:

  • mysql> SET NAMES ‘utf8’;

Change the MySQL database to default to UTF8:

  • Edit file /etc/my.cnf
  • In the [client] section, add the following line:
    default-character-set=utf8
  • In the [mysqld] section, add the following line:
    character-set-server=utf8

Installing MySQL Workbench installing-mysql-workbench

MySQL Workbench provides an UI for executing SQL scripts which install the schema and initial data.

MySQL Workbench should be downloaded and installed following the instructions for the target OS.

Enablement Connection enablement-connection

When the MySQL Workbench is first launched, unless already in use for other purposes, it will not yet show any connections:

chlimage_1-327

New Connection Settings new-connection-settings

  1. Select the ‘+’ icon to the right of MySQL Connections.

  2. In the dialog Setup New Connection, enter values appropriate for your platform for demonstration purposes, with the author AEM instance and MySQL on the same server:

    • Connection Name: Enablement
    • Connection Method: Standard (TCP/IP)
    • Hostname: 127.0.0.1
    • Username: root
    • Password: no password by default
    • Default Schema: leave blank

  3. Select Test Connection to verify the connection to the running MySQL service

Notes:

Successful Connection successful-connection

chlimage_1-328

New Enablement Connection new-enablement-connection

chlimage_1-329

Database Setup database-setup

Upon opening the new Enablement connection, notice there is a test schema and default user accounts.

chlimage_1-330

Obtain SQL Scripts obtain-sql-scripts

The SQL scripts are obtained using CRXDE Lite on the author instance. The SCORM package must be installed:

  1. Browse to CRXDE Lite
  2. Expand the /libs/social/config/scorm/ folder
  3. Download database_scormengine.sql
  4. Download database_scorm_integration.sql

chlimage_1-331

One method for downloading the schema is to

  • Select the jcr:contentnode for the sql file
  • Notice the value for the jcr:dataproperty is a view link
  • Select the view link to save the data to a local file

Create SCORM Database create-scorm-database

The Enablement SCORM Database to be created is:

  • name: ScormEngineDB

  • created from scripts:

    • schema: database_scormengine.sql
    • data: database_scorm_integration.sql
      Follow the steps below (open, execute) to install each SQL script . Refresh when necessary to see the results of the script execution.

Be sure to install the schema before installing the data.

CAUTION
If the database name is changed, be sure to specify it correctly in

Step 1: open SQL file step-open-sql-file

In the MySQL Workbench

  • From the File pulldown menu

  • Select Open SQL Script ...

  • In this order, select one of:

    1. database_scormengine.sql
    2. database_scorm_integration.sql

chlimage_1-332

Step 2: execute SQL Script step-execute-sql-script

In the Workbench window for the file opened in Step 1, select the lightening (flash) icon to execute the script.

Note that the execution of the database_scormengine.sql script to create the SCORM database may take a minute to complete.

chlimage_1-333

Refresh refresh

Once the scripts are executed, it is necessary to refresh the SCHEMASsection of the Navigator in order to see the new database. Use the refresh icon to the right of ‘SCHEMAS’:

chlimage_1-334

Result: scormenginedb result-scormenginedb

After installing and refreshing SCHEMAS, the scormenginedb will be visible.

chlimage_1-335

Configure JDBC Connections configure-jdbc-connections

The OSGi configuration for Day Commons JDBC Connections Pool configures the MySQL JDBC Driver.

All publish and author AEM instances should point to the same MySQL server.

When MySQL runs on a server different from AEM, the server hostname must be specified in place of ‘localhost’ in the JDBC connector (which populates the ScormEngine config).

chlimage_1-336

  • Enter the following values:

    • JDBC driver class: com.mysql.jdbc.Driver
    • DBC connection URIJ: jdbc:mysql://localhost:3306/aem63reporting specify server in place of localhost if MySQL server is not the same as ‘this’ AEM server
    • Username: Root or enter the configured Username for the MySQL server, if not ‘root’
    • Password: Clear this field if no password set for MySQL, else enter the configured password for the MySQL Username
    • Datasource name: Name entered for the MySQL connection, for example, ‘enablement’

  • Select Save

Configure Scorm configure-scorm

AEM Communities ScormEngine Service aem-communities-scormengine-service

The OSGi configuration for AEM Communities ScormEngine Service configures SCORM for an enablement community’s use of the MySQL server.

This configuration is present when the SCORM package is installed.

All publish and author instances point to the same MySQL server.

When MySQL runs on a server different from AEM, the server hostname must be specified in place of ‘localhost’ in the ScormEngine Serivce, which is typically populated from the JDBC Connection config.

  • On each author and publish AEM instance

  • Signed in with administrator privileges

  • Access the web console

  • Locate the AEM Communities ScormEngine Service

  • Select the edit icon
    chlimage_1-337

  • Verify the following parameter values are consistent with the JDBC Connection config:

    • JDBC connection URI: jdbc:mysql://localhost:3306/ScormEngineDB ScormEngineDB is the default database name in the SQL scripts
    • Username: Root or enter the configured Username for the MySQL server, if not ‘root’
    • Password: Clear this field if no password set for MySQL, else enter the configured password for the MySQL Username

  • Regarding the following parameter:

    • Scorm User Password: DO NOT EDIT

      For internal use only. It is for a special service user used by AEM Communities to communicate with the scorm engine.

  • Select Save

Adobe Granite CSRF Filter adobe-granite-csrf-filter

To ensure enablement courses work correctly in all browsers, it is necessary to add Mozilla as a User Agent that is not checked by the CSRF filter.

recommendation-more-help
5d37d7b0-a330-461b-814d-068612705ff6