# Configuring CMA with MySQL

By default, CMA uses an embedded H2 database that requires no setup. For production
deployments or when you need a shared database, CMA supports MySQL as an
alternative backend.

## Prerequisites

- MySQL 8.4 or later
- A MySQL superuser account (e.g., `root`) to run the setup script
- The `mysql` client installed on the host where you run the setup script
- Network connectivity from the CMA Master and Agent hosts to the MySQL server
- If CMA Agent and CMA Master use different MySQL instances (e.g., each on
  its own cluster node), run the setup script once per MySQL instance

## Step 1: Create CMA Databases

Run the setup script from the parcel directory on any host that has the `mysql`
client installed and can reach the MySQL server:

```bash
/opt/cloudera/parcels/cma/bin/setup-cma-mysql.sh \
  --host <mysql-host> \
  --admin-user root \
  --admin-password <superuser-password>
```

> **Note:** The setup script always creates all three databases (`cma`, `cma_auth`,
> `cma_agent`). In a Master-Agent split deployment where the Agent uses a separate
> MySQL instance, only the `cma_agent` database on the Agent's MySQL is used.
> The unused `cma` and `cma_auth` databases can be safely ignored or dropped.

The script creates three databases and their owning users:

| Database | User | Purpose |
|----------|------|---------|
| `cma` | `cma` | CMA Master |
| `cma_auth` | `cma` (shared) | CMA Auth Server |
| `cma_agent` | `cma_agent` | CMA Agent |

Passwords are auto-generated and printed at the end. Save them for configuring CMA.

> **Character set:** Databases are created with the `utf8mb3` character set. This
> covers the entire Basic Multilingual Plane (all standard characters in all
> languages) and is required to stay within MySQL InnoDB's composite primary key
> length limits. CMA stores Hadoop identifiers (cluster names, database names,
> table names, file paths) which are ASCII by convention, so this has no practical
> impact.

### Setup Script Options

| Option | Default | Description |
|--------|---------|-------------|
| `--host` | *(required)* | MySQL hostname or IP |
| `--admin-user` | *(required)* | MySQL superuser (e.g., `root`) |
| `--admin-password` | *(required)* | MySQL superuser password |
| `--port` | `3306` | MySQL port |
| `--cma-user` | `cma` | CMA Master database user |
| `--cma-password` | *(auto-generated)* | CMA Master database user password |
| `--auth-user` | *(same as `--cma-user`)* | CMA Auth database user |
| `--auth-password` | *(same as `--cma-password`)* | CMA Auth database user password |
| `--agent-user` | `cma_agent` | CMA Agent database user |
| `--agent-password` | *(auto-generated)* | CMA Agent database user password |

### Manual Database Creation

If you prefer to create the databases manually instead of using the setup script:

```sql
mysql -u root -p

CREATE DATABASE cma CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci;
CREATE DATABASE cma_auth CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci;
CREATE DATABASE cma_agent CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci;

CREATE USER 'cma'@'%' IDENTIFIED BY 'your_secure_password';
CREATE USER 'cma_agent'@'%' IDENTIFIED BY 'your_agent_password';

GRANT ALL PRIVILEGES ON cma.* TO 'cma'@'%';
GRANT ALL PRIVILEGES ON cma_auth.* TO 'cma'@'%';
GRANT ALL PRIVILEGES ON cma_agent.* TO 'cma_agent'@'%';

FLUSH PRIVILEGES;
```

> **Important:** Always use `utf8mb3` as the character set. Using `utf8mb4`
> will cause schema creation failures due to MySQL's composite primary key
> length limits.

## Step 2: Configure CMA

### Parcel Deployment (Cloudera Manager)

In Cloudera Manager, configure the following parameters for each CMA service:

**CMA Master service configuration:**

| Parameter | Value |
|-----------|-------|
| Database Type | `mysql` |
| Database Host | MySQL hostname or IP |
| Database Port | `3306` (or your custom port) |
| Database User | `cma` |
| Database Password | Password from setup script |

**CMA Agent service configuration:**

| Parameter | Value |
|-----------|-------|
| Database Type | `mysql` |
| Database Host | MySQL hostname or IP |
| Database Port | `3306` (or your custom port) |
| Database User | `cma_agent` |
| Database Password | Password from setup script |

Then restart both CMA services.

### Local or Docker Deployment

Set the database connection parameters using environment variables:

```bash
# CMA Master
export CMA_DB_TYPE=mysql
export CMA_DB_HOST=db.example.com
export CMA_DB_PORT=3306
export CMA_DBA_USER=cma
export CMA_DBA_PASS=your_secure_password

# Auth Server (defaults to Master values if not set)
export CMA_AUTH_DB_HOST=db.example.com
export CMA_AUTH_DB_NAME=cma_auth
export CMA_AUTH_DBA_USER=cma
export CMA_AUTH_DBA_PASS=your_secure_password

cma-4.1.1-b12/bin/cma start
```

For the CMA Agent (if running locally):

```bash
export CMA_DB_TYPE=mysql
export CMA_AGENT_DB_HOST=db.example.com
export CMA_AGENT_DB_PORT=3306
export CMA_AGENT_DBA_USER=cma_agent
export CMA_AGENT_DBA_PASS=your_agent_password
```

## Environment Variables Reference

### CMA Master

| Variable | Default | Description |
|----------|---------|-------------|
| `CMA_DB_TYPE` | `h2` | Database backend: `h2`, `postgres`, or `mysql` |
| `CMA_DB_HOST` | — | MySQL hostname (required when `CMA_DB_TYPE=mysql`) |
| `CMA_DB_PORT` | `3306` | MySQL port |
| `CMA_DB_NAME` | `cma` | Database name for CMA Master |
| `CMA_DBA_USER` | `cma` | Database username |
| `CMA_DBA_PASS` | — | Database password (required when `CMA_DB_TYPE=mysql`) |

### CMA Auth Server

| Variable | Default | Description |
|----------|---------|-------------|
| `CMA_AUTH_DB_HOST` | `CMA_DB_HOST` | MySQL hostname for auth server |
| `CMA_AUTH_DB_PORT` | `CMA_DB_PORT` | MySQL port for auth server |
| `CMA_AUTH_DB_NAME` | `cma_auth` | Database name for auth server |
| `CMA_AUTH_DBA_USER` | `CMA_DBA_USER` | Auth database username |
| `CMA_AUTH_DBA_PASS` | `CMA_DBA_PASS` | Auth database password |

### CMA Agent

| Variable | Default | Description |
|----------|---------|-------------|
| `CMA_AGENT_DB_HOST` | — | MySQL hostname for agent (required when `CMA_DB_TYPE=mysql`) |
| `CMA_AGENT_DB_PORT` | `3306` | MySQL port for agent |
| `CMA_AGENT_DB_NAME` | `cma_agent` | Database name for agent |
| `CMA_AGENT_DBA_USER` | `cma_agent` | Agent database username |
| `CMA_AGENT_DBA_PASS` | — | Agent database password (required when `CMA_DB_TYPE=mysql`) |

## Schema Management

CMA uses Liquibase for automatic schema management. When starting with the
`mysql` profile, Liquibase creates all required tables, indexes, and seed data
automatically. No manual schema creation is needed.

## Deployment Topologies

### Single cluster (Master + Agent co-located)

All three databases (`cma`, `cma_auth`, `cma_agent`) on a single MySQL
instance. Run the setup script once. Configure both services to point to the
same MySQL host.

### Master-Agent split (separate clusters)

CMA Master on the target cluster, CMA Agent on the source cluster. Each can
use a local MySQL instance:

- **Target cluster MySQL**: `cma`, `cma_auth`, `cma_agent` databases
  (the target Agent and Master share the same MySQL)
- **Source cluster MySQL**: `cma_agent` database only

Run the setup script on each MySQL host. Configure the CMA Agent on the
source cluster with the `CMA Master URL` pointing to the Master's gRPC port
(default 8091) on the target cluster.

When the CMA Agent is on a different cluster from the Master, the
`CMA Master URL` parameter must be set in the Agent's service configuration in
Cloudera Manager (format: `http://<master-host>:<grpc-port>`).

## Limitations

- **No H2-to-MySQL migration**: Switching from H2 to MySQL requires a
  fresh start. Existing H2 data cannot be migrated automatically.
- **No PostgreSQL-to-MySQL migration**: Switching between external database
  backends requires a fresh start.
- **Separate databases**: CMA Master, the Auth Server, and CMA Agent each use
  separate databases. All must be configured when using MySQL.
- **utf8mb3 character set required**: CMA MySQL databases must use `utf8mb3`
  (not `utf8mb4`) due to composite primary key length limits in InnoDB.
  This does not affect normal usage since CMA stores ASCII identifiers.

## Switching from H2 to MySQL

When switching an existing CMA deployment from H2 to MySQL, the Master starts
with a fresh database. CMA Agents, however, retain their local H2 databases which
contain stale server and cluster IDs referencing the old Master database. This
causes agents to fail registration because those IDs no longer exist.

To resolve this, wipe the agent H2 databases on **every** agent host after switching
the Master to MySQL:

```bash
# On each CMA Agent host:
# 1. Stop the CMA Agent service (via CM or systemctl)

# 2. Delete the agent's local H2 database
rm -f /home/cma-agent/data/db/cma-agent.mv.db

# 3. Start the CMA Agent service
```

After restart, each agent will re-register with the Master and receive new IDs.

Similarly, if the Master was previously running with H2 and accumulated data
(`/var/lib/cma/data/`, `/var/lib/cma/config/`), clear those directories before
switching to MySQL to avoid H2 startup errors:

```bash
# On the CMA Master host (stop service first):
sudo rm -rf /var/lib/cma/data/* /var/lib/cma/config/*
```

## Troubleshooting

### Remote connections rejected

If the CMA Agent connects to a MySQL on a different host and gets
"connection refused" or authentication errors, verify:

1. MySQL is configured to listen on all interfaces (`bind-address = 0.0.0.0`
   in `my.cnf` or `mysqld.cnf`)
2. The MySQL user was created with `'%'` host (allowing remote connections):
   ```sql
   SELECT user, host FROM mysql.user WHERE user = 'cma';
   ```
3. Firewall allows connections on port 3306:
   ```bash
   firewall-cmd --list-ports | grep 3306
   ```
4. Restart MySQL after configuration changes: `sudo systemctl restart mysqld`

### Connection refused or hostname resolution error

Verify the MySQL host is reachable from the CMA host:

```bash
mysql -h <mysql-host> -P <mysql-port> -u <user> -p -e "SELECT 1"
```

### Missing Database Host or Password error on startup

When `CMA_DB_TYPE` is set to `mysql`, the `Database Host` and `Database Password`
parameters are required. In Cloudera Manager, verify these are set in the CMA service
configuration. For local/Docker deployments, ensure the corresponding environment
variables (`CMA_DB_HOST`, `CMA_DBA_PASS` for Master; `CMA_AGENT_DB_HOST`,
`CMA_AGENT_DBA_PASS` for Agent) are exported before starting.

### Schema creation fails with "Specified key was too long"

This error occurs when the database was created with `utf8mb4` instead of `utf8mb3`.
Recreate the database with the correct character set:

```sql
DROP DATABASE cma;
CREATE DATABASE cma CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci;
GRANT ALL PRIVILEGES ON cma.* TO 'cma'@'%';
```

Repeat for `cma_auth` and `cma_agent` if needed. Alternatively, re-run the setup
script which creates databases with the correct character set.

### Parcel deployment: environment variable injection via safety valve

In parcel deployments, CMA reads database parameters from the CM-managed service
configuration. If additional environment variables are needed (e.g., for the Auth Server),
you can inject them using CM safety valves.

In Cloudera Manager, set the following for the **CMA_AUTH_SERVER** role:

**CMA_AUTH_SERVER > Configuration > CMA_AUTH_SERVER role Environment Advanced Configuration Snippet (Safety Valve)**:

```
CMA_AUTH_DB_HOST=db.example.com
CMA_AUTH_DB_PORT=3306
CMA_AUTH_DB_NAME=cma_auth
CMA_AUTH_DBA_USER=cma
CMA_AUTH_DBA_PASS=your_secure_password
```

Safety valve values are injected directly into the Java process environment by CM,
bypassing any shell script issues. Restart the CMA service after applying these
changes.
