Database Configuration

The Database Configuration screen enables you to create and configure databases using the embedded HyperSQL server or connect to an external, supported database. You can also create and restore database backups, as well as port existing backups to a different type of database. For example, you could back up a HyperSQL database, then re-create it as an Oracle or MySQL database. Or, you could capture the current state of a database and use that for a rollback. See System Requirements for details about supported database versions and requirements.

Choose Database Configuration from the administration menu (gear icon) to open the Database Configuration screen.

See also Installing and Running CTP.

Configuring Database Settings

Connecting to an Existing Database

This process connects the current CTP to an existing CTP database.

1. Click Configure Database Settings and select Connect to existing CTP database in the window that appears.
   
2. Click Next and choose a database type: HyperSQL, MariaDB, MySQL, Oracle, or PostgreSQL.
   
   

3. Enter the database URL and user credentials.

4. Click Test Connection to confirm that it is correct.
5. Click Save & Connect to connect to the database.

Creating a New Database

This process creates a new, empty CTP database. For information about creating a new CTP database from a backup, see Creating a Database from a Backup.

1. Click Configure Database Settings and select Create new CTP database, user, and schema in the window that appears.
   
2. Click Next and choose a database type: HyperSQL, MariaDB, MySQL, Oracle, or PostgreSQL.
   
   
3. Enter the database URL and root credentials.
4. Click Test Connection to confirm that it is correct.
   

   For MySQL databases, the root user must have the GRANT OPTION privilege.

5. Click Next.
   
   

6. Enter new user and credentials and click Create Database.

Creating a CTP Schema for an Existing Database

This process creates a CTP schema for an existing database with existing user credentials. For information about creating a CTP schema from a backup, see Creating a Database from a Backup.

1. Click Configure Database Settings and select Create CTP database schema in the window that appears.
   
2. Click Next and choose a database type: HyperSQL, MariaDB, MySQL, Oracle, or PostgreSQL.
   
   
3. Enter the database URL and user credentials.
4. Click Test Connection to confirm that it is correct.
5. Click Create Schema.

Database Backups

Creating a Database Backup

To back up your CTP database, click Back Up Database on the Database Configurations page. What happens next depends on your database type:

• If the database is HyperSQL, MariaDB, or MySQL, the backup will begin immediately.

• If the database is Oracle, a dialog will appear asking for the data tablespace. Enter the name of the tablespace and click Create Backup and the backup will begin.

• If the database is PostgreSQL, a dialog will appear for the schema. Enter the name of the schema and click Create Backup and the backup will begin.

Once the backup is complete, you can click Download to download it to your device.

Backing Up Databases with Large Files (MySQL Only)

If you are using a MySQL database and the backup fails, try increasing the MySQL size limits as follows:

1. Find the my.cnf/my.ini file for your installation of MySQL. For MySQL 8, see https://dev.mysql.com/doc/refman/8.0/en/option-files.html. For other versions, see the appropriate manual at http://dev.mysql.com/doc/index.html.
2. Edit the file to use the parameter settings outlined below.
3. Restart the MySQL Service 

[mysqld]

• innodb = ON
• innodb_log_file_size = 5242880000 // This must be set to be 10x the largest content being loaded for the public marketplace.
• max_allowed_packet = 1G
• innodb_file_per_table = 1
• innodb_file_format = Barracuda 

[mysql]

• max_allowed_packet = 1G 

[client]

• max_allowed_packet = 1G

Creating a Database from a Backup

You can create a new database from a backup. This process creates a new database and populates it with data from a backed-up database; do not attempt to use it to overwrite an existing database. Use a database backup from the same version of CTP; you should not attempt to create a new database using a backup from a different version of CTP.

1. Click Restore Database.
2. Choose one of the following options and enter the database and user information as described:
   • Create new CTP database, user, and schema
     1. Choose a database type: HyperSQL, MariaDB, MySQL, Oracle, or PostgreSQL.
     2. Enter the database URL and root credentials.
     3. Click Test Connection to confirm that it is correct.
        
     4. Click Next.
     5. Enter new user and credentials and click Next.
   • Create CTP database schema
     1. Choose a database type: HyperSQL, MariaDB, MySQL, Oracle, or PostgreSQL.
     2. Enter the database URL and user credentials.
     3. Click Test Connection to confirm that it is correct.
     4. Click Next.
3. Click Browse and navigate to the backup file. When the backup has finished uploading, click Restore Database.

A new database will be created in the specified location then it will be populated with the data saved in the backup file.

Migrating a Database

You can migrate a database from one type to another (for example, from HyperSQL to PostgreSQL). Doing so is a basic two-step process:

1. Create a backup of your existing database. See Creating a Database Backup.
2. Restore your backup to the new database type. See Creating a Database from a Backup.

You should only do this within the same version of CTP. Keeping with the HyperSQL-to-PostgreSQL example from before, you want to create the backup of your HyperSQL database in the same version of CTP as your PostgreSQL database. Doing something like backing up your HyperSQL database in a previous version of CTP, then upgrading CTP and trying to create your PostgreSQL database with that backup could create errors.

Notes

• CTP does not ship with JDBC driver libraries for MariaDB, MySQL, Oracle, or PostgreSQL. Refer to the JDBC Driver Notes in the installation guide for additional information.