CleanSpeak Database/Search

1. Database Installation

If you already have a MySQL or PostgreSQL database installed that you will be using with CleanSpeak, you can skip this section completely.

To use CleanSpeak, you need to install a database. The database requirements are listed above. You can use CleanSpeak with either MySQL or PostgreSQL. Follow the instructions below to install a supported database.

You only need to install either MySQL or PostgreSQL to use CleanSpeak.

1.1. Install MySQL

If you want to use MySQL for your database, you will need to install version 8 or later. Depending on your operating system, you can download and install MySQL or use the operating systems package management tools. Use the instructions below to install MySQL based on your operating system.

1.1.1. Linux

To install MySQL on a Linux system, you can use the apt or yum tools depending on whether or not your Linux distribution is based on Red Hat or Debian. The package name may vary depending on your platform, for example: mysql-server or mysql-server-<version> where <version> is the version you wish to install. Here’s the commands for each:

Red Hat
$ yum install mysql-server
Debian
$ sudo apt-get install mysql-server

1.1.2. Windows or macOS

If you are installing MySQL on a platform that does not support RPM or DEB packages, you will need to download it manually from the MySQL website here: https://dev.mysql.com/downloads/mysql/

1.1.3. MySQL and Unicode

To properly store 4 byte unicode, the character set must be set to utf8mb4.

By default this configuration is enforced during startup. If CleanSpeak fails to startup due to this validation the log will contain detailed messages about the MySQL configuration.

It is recommended to ensure you have properly configured MySQL to use utf8mb4 before attempting to startup CleanSpeak. Please verify you have the following configuration in your my.cnf file. On Windows the file is called my.ini.

If you need to modify your configuration, restart MySQL for the changes to take effect.

my.cnf
[client]
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4

[mysqld]
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_bin

This is a system-wide configuration option, so be certain that this change won’t impact other databases and applications on the same MySQL server. Not all of these configuration options may be available, depending on your database configuration. Once you have configured the available options, you may disable this feature if the startup still fails due to this validation. See database.mysql.enforce-utf8mb4 in the Configuration reference.

1.2. Install MySQL Connector

In order to use MySQL with CleanSpeak, you also need to download and install the MySQL Connector for Java. This is a manual process due to the way that Oracle licenses MySQL. Follow these steps to get the MySQL Connector into the correct location so that CleanSpeak can make a connection to your MySQL database.

  1. Download the MySQL Connector version 8.0.33 or newer using the Platform Independent installer from the Oracle website here: https://dev.mysql.com/downloads/connector/j/8.0.html

  2. Unzip or untar the archive you downloaded

  3. Copy the file from the archive named mysql-connector-j-<version>.jar to the CleanSpeak library directory. This directory is located in the installation directory at cleanspeak-management-interface/web/WEB-INF/lib or cleanspeak-webservice/web/WEB-INF/lib depending upon the service you are installing. For example, if you installed cleanspeak-webservice using the DEB or RPM packages, this location will be /usr/local/cleanspeak/cleanspeak-webservice/web/WEB-INF/lib.

If you are using the CleanSpeak Docker images, you may optionally rebuild the provided images with the MySQL JDBC connector. See the following examples.

1.3. Installing PostgreSQL

1.3.1. Linux

To install PostgreSQL on a Linux system, you can use the apt or yum tools depending on whether or not your Linux distribution is based on Red Hat or Debian. The package name may vary depending on your platform.

Red Hat
$ sudo yum install postgresql-server postgresql
$ sudo yum install postgresql9-contrib.x86_64
Debian
$ sudo apt-get install postgresql-server
$ sudo apt-get install postgresql-contrib

1.3.2. Windows or macOS

If you are installing PostgreSQL on a platform that does not support RPM or DEB packages, you will need to manually download it from the PostgreSQL website here: https://www.postgresql.org/

2. Search Installation

OpenSearch has a great write up at https://opensearch.org/docs/latest/install-and-configure/install-opensearch/index/ that describes the various ways to run OpenSearch. You can also use managed services like AWS OpenSearch Service.

CleanSpeak generally expects no authentication to be configured on ElasticSearch/OpenSearch and thus it should be secured at the network layer. The simplest way to configure OpenSearch to do this is to add the following to opensearch.yml:

plugins.security.disabled: true

If you wish to use TLS or auth, you may, but keep the following in mind:

  • You must obtain a trusted certificate and configure it with OpenSearch OR add a self signed certificate to CleanSpeak’s JVM trust store.

  • The CleanSpeak search.servers property must be configured with those credentials as noted in RFC 1738. For example, https://someUser@somePassword:yourSearchServer