# Setting up the database on an Oracle Cloud VPS

Start-to-finish. Assumes Ubuntu 24.04 on an Ampere A1 instance; Oracle
Linux differences are called out at each step.

**The single most important decision here:** MySQL listens on
`127.0.0.1` only, and port 3306 is never opened to the internet. Design
doc §1 puts the API and the built React app in one Node process on one
host, so the database has no reason to accept a connection from anywhere
else. This also means you do **not** have to touch either firewall for
MySQL — which is the step people usually get wrong on OCI.

---

## 0. The instance

Pick **VM.Standard.A1.Flex** (Ampere, ARM64), Ubuntu 24.04, and allocate
the full Always Free allowance: **2 OCPU / 12 GB RAM**. Oracle halved
this in 2026 — it used to be 4 OCPU / 24 GB — so if you set up a shape
before then, expect less than you remember. Block storage is 200 GB
total across all volumes.

ARM is fine: MySQL 8 and MariaDB both ship arm64 builds, and nothing in
this schema is architecture-dependent.

> **Avoid the AMD `VM.Standard.E2.1.Micro` free shape for this.** It has
> 1 GB of RAM. MySQL 8's default InnoDB buffer pool alone wants more than
> that, and you'll spend your time fighting the OOM killer instead of
> building the game. If you're stuck on it, add 2 GB of swap and set
> `innodb_buffer_pool_size = 128M`.

SSH in (`ubuntu` is the default user on Ubuntu images; Oracle Linux uses
`opc`):

```bash
ssh ubuntu@<your-public-ip>
```

---

## 1. Install MySQL

```bash
sudo apt update && sudo apt upgrade -y
sudo apt install -y mysql-server
mysqld --version
```

You need **8.0.16 or newer** — that's the first release where MySQL
actually enforces CHECK constraints rather than parsing and ignoring
them. Ubuntu 24.04 ships 8.0.4x, so you're fine.

<details>
<summary>Oracle Linux 9</summary>

```bash
sudo dnf install -y mysql-server
sudo systemctl enable --now mysqld
```
</details>

<details>
<summary>Prefer MariaDB?</summary>

`sudo apt install -y mariadb-server` — everything here works identically,
verified on 10.11. Substitute `mariadb` for `mysql` in the commands below.
You need 10.5+.
</details>

---

## 2. Confirm it's listening on localhost only

```bash
sudo ss -lntp | grep 3306
```

You want to see `127.0.0.1:3306`. If you see `0.0.0.0:3306` or `*:3306`,
fix it:

```bash
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf     # Ubuntu
# Oracle Linux: sudo nano /etc/my.cnf
```

Set (or add under `[mysqld]`):

```ini
bind-address = 127.0.0.1
```

Then `sudo systemctl restart mysql` and re-check. Ubuntu's package
already defaults to this; Oracle Linux's does not.

---

## 3. Secure the installation

```bash
sudo mysql_secure_installation
```

Answer: set a root password, remove anonymous users **yes**, disallow
remote root **yes**, remove the test database **yes**, reload privileges
**yes**.

On Ubuntu, the `root` MySQL user authenticates via the unix socket, so
`sudo mysql` keeps working without a password afterwards. That's what the
commands below assume.

---

## 4. Get the files onto the server

From **Windows PowerShell** on your machine (Windows 10/11 has `scp`
built in):

```powershell
scp -r C:\Users\stefa\UnmatchedCustom\database ubuntu@<your-public-ip>:~/
```

---

## 5. Set the application password

Two edits to `sql/00_create_database.sql` before you run anything:

```bash
cd ~/database
nano sql/00_create_database.sql
```

1. Replace `change_me_before_running` with a real password. Generate one:
   `openssl rand -base64 24`
2. Change `'unmatched_app'@'%'` to **`'unmatched_app'@'localhost'`**. The
   app runs on this same host, so there's no reason to allow the account
   to connect from anywhere else.

Both lines, in one go, if you'd rather not use the editor:

```bash
PASS=$(openssl rand -base64 24)
sed -i "s/change_me_before_running/${PASS}/; s/'unmatched_app'@'%'/'unmatched_app'@'localhost'/g" sql/00_create_database.sql
echo "Save this password: ${PASS}"
```

---

## 6. Import

Run from `~/database` — `install_all.sql` uses paths relative to where
you launch `mysql`:

```bash
cd ~/database
sudo mysql < sql/install_all.sql
```

Expected tail of the output:

```
check           rows
vocabulary      56
decks (final)   1
invalid decks   0
```

If you'd rather start with an empty library, comment out the
`SOURCE sql/04_example_deck.sql;` line first.

---

## 7. Verify

```bash
sudo mysql unmatched -e "
SELECT TABLE_TYPE, COUNT(*) FROM information_schema.TABLES
  WHERE TABLE_SCHEMA='unmatched' GROUP BY TABLE_TYPE;
SELECT COUNT(*) AS triggers_ FROM information_schema.TRIGGERS
  WHERE TRIGGER_SCHEMA='unmatched';
SELECT COUNT(*) AS routines_ FROM information_schema.ROUTINES
  WHERE ROUTINE_SCHEMA='unmatched';
SELECT deck_name, total_cards, is_valid FROM v_deck_validation;"
```

You should get **14 base tables, 8 views, 10 triggers, 3 routines**, and
the example deck reporting `30 / is_valid = 1`.

The trigger count is the one to actually check. If it's 0, every §7 deck
rule is missing and any garbage deck can be published — see the
troubleshooting note at the bottom.

Then confirm the app user works and can't do more than it should:

```bash
mysql -u unmatched_app -p unmatched -e "SELECT COUNT(*) FROM v_deck_library;"
```

---

## 8. Wire up the Node app

In your project's `.env`:

```
DATABASE_URL="mysql://unmatched_app:YOUR_PASSWORD@127.0.0.1:3306/unmatched"
```

If the password contains `@`, `/`, `:` or `#`, URL-encode it or the
connection string will parse wrong.

```bash
chmod 600 .env          # and make sure .env is in .gitignore
```

Then:

```bash
npx prisma db pull      # confirms schema.prisma matches the real database
npx prisma generate
```

`db pull` should report no changes. If it rewrites things, tell me what
changed — that means the import didn't land the way it did in testing.

**Never run `prisma migrate dev` against this database.** Prisma doesn't
model triggers, views, CHECK constraints or stored procedures, so it will
happily drop every §7 rule and the entire JSON reassembly layer.

---

## 9. Backups

The database holds paths to card art, not the art itself, so a database
backup alone loses every image. Back up both.

```bash
sudo mkdir -p /var/backups/unmatched
sudo tee /usr/local/bin/backup-unmatched.sh >/dev/null <<'EOF'
#!/bin/bash
set -e
STAMP=$(date +%F)
mysqldump --routines --triggers --single-transaction --databases unmatched \
  | gzip > /var/backups/unmatched/db-${STAMP}.sql.gz
tar czf /var/backups/unmatched/uploads-${STAMP}.tar.gz -C /path/to/your/app uploads
find /var/backups/unmatched -type f -mtime +14 -delete
EOF
sudo chmod +x /usr/local/bin/backup-unmatched.sh
sudo crontab -e     # add:  0 4 * * * /usr/local/bin/backup-unmatched.sh
```

`--routines --triggers` is not optional. Without those flags mysqldump
silently omits the procedures and triggers, and you'd restore a database
where `sp_finalize_deck` doesn't exist and any deck can be published.

---

## 10. Firewalls — for the web app, not for MySQL

Nothing above needs a firewall change, because MySQL only listens on
localhost. When you deploy the Node process and want it reachable, you
have to open ports in **two** places on OCI. Doing only one is the
classic "my site is up but nothing loads" symptom.

**a) VCN Security List** (Oracle console): Networking → Virtual Cloud
Networks → your VCN → Security Lists → Default → Add Ingress Rules.
Source `0.0.0.0/0`, TCP, destination port 80 and 443.

**b) The host firewall.** Ubuntu images on OCI ship with iptables rules
that accept SSH only, and UFW is disabled deliberately — Oracle
discourages it because enabling it on top can lock you out. Edit the
rules file directly:

```bash
sudo nano /etc/iptables/rules.v4
```

Add these **above** the existing `REJECT` line — rules after it are
silently ignored, which is the mistake almost everyone makes here:

```
-A INPUT -p tcp -m state --state NEW -m tcp --dport 80 -j ACCEPT
-A INPUT -p tcp -m state --state NEW -m tcp --dport 443 -j ACCEPT
```

```bash
sudo iptables-restore < /etc/iptables/rules.v4
```

<details>
<summary>Oracle Linux uses firewalld instead</summary>

```bash
sudo firewall-cmd --permanent --add-service=http
sudo firewall-cmd --permanent --add-service=https
sudo firewall-cmd --reload
```
</details>

**Do not add a rule for 3306 in either place.**

---

## If you go with managed MySQL instead of the VM

Oracle's MySQL Database Service (HeatWave) is a different animal: you
don't get `SUPER`, and with binary logging on, `CREATE TRIGGER` fails
with `ERROR 1419` unless `log_bin_trust_function_creators` is `ON` in the
DB System's configuration.

That error is the dangerous one, because `03_views_and_validation.sql`
will *partially* apply — views get created, triggers don't — and you end
up with a database that looks installed but enforces none of the §7 deck
rules. If you go managed, set that parameter first, then check the
trigger count in step 7 before trusting anything.

Self-hosting on the VM avoids the whole issue, and for a friend-group
game there's no reason not to.

---

## Troubleshooting

**`ERROR 1419` during import, or step 7 reports 0 triggers.**
Binary logging is on without trigger-creation privileges. Either
`SET GLOBAL log_bin_trust_function_creators = 1;` (add
`log_bin_trust_function_creators = ON` to the config to persist it), then
re-run `sql/03_views_and_validation.sql`.

**`ERROR 1064` around a `BEGIN` or `END` in file 03.**
You're importing through a GUI tool that doesn't understand `DELIMITER`.
Use the `mysql` CLI, or set the tool's delimiter to `$$` first.

**`ERROR 3819: Check constraint ... is violated` while adding cards.**
Working as intended — the card's values don't match its type. Attack
cards need an attack value and no defense value; defense cards the
reverse; versatile needs both; schemes neither.

**A deck won't finalize.** The error is truncated at 128 bytes by MySQL.
The full list is here:

```sql
SELECT * FROM v_deck_validation WHERE deck_id = <id>\G
```

**MySQL won't start / gets killed.** Almost certainly the 1 GB micro
instance. Add swap and shrink the buffer pool, or move to Ampere.