Can't connect to a MySQL HeatWave DB system on OCI: network path, users and connection limits

#Symptom

An application or DBA can't connect to a MySQL HeatWave DB system. The error tells you where to look:

Set variables first:

C=ocid1.compartment.oc1..xxxx
DB=ocid1.mysqldbsystem.oc1..xxxx

#1. Is the DB system up, and what is its endpoint?

oci mysql db-system list -c $C \
  --query "data[].{name:\"display-name\", state:\"lifecycle-state\", id:id}" --output table

oci mysql db-system get --db-system-id $DB \
  --query "data.{state:\"lifecycle-state\", ip:\"ip-address\", port:port, xport:\"port-x\", host:\"hostname-label\", subnet:\"subnet-id\", ha:\"is-highly-available\", nsgs:\"nsg-ids\"}"

• lifecycle-state must be ACTIVE. INACTIVE means it was stopped (by a person or a schedule); UPDATING covers shape changes, upgrades and some configuration changes.
• Use the endpoint ip-address. On a high availability DB system that endpoint moves to the new primary on failover, so applications must use it (or its DNS name), never the IP of an underlying instance.
• Port 3306 is the classic MySQL protocol, 33060 the X Protocol that MySQL Shell uses by default for mysqlx:// URIs. Open both if people use MySQL Shell.

Start a stopped DB system:

oci mysql db-system start --db-system-id $DB --wait-for-state ACTIVE

Note

Read replicas have their own endpoints, and a read replica load balancer endpoint if you created one. A reporting tool that "can't connect" may be pointing at a replica that was deleted.

#2. Can the client reach the endpoint at all?

The DB system endpoint is a private IP in a VCN subnet. A client outside the VCN needs a path: a peered VCN through a DRG, Site-to-Site VPN or FastConnect, or a Bastion session.

From a host in the client's network, test TCP before you test MySQL:

nc -zv -w 5 10.0.2.15 3306
# or without nc
timeout 5 bash -c '</dev/tcp/10.0.2.15/3306' && echo open || echo blocked

If you connect by name, check it resolves to the endpoint IP. The FQDN is built from the hostname label, the subnet DNS label and the VCN DNS label (<host>.<subnet>.<vcn>.oraclevcn.com):

dig +short mydb.dbsubnet.prodvcn.oraclevcn.com

From on-premises, that name only resolves if your DNS forwards oraclevcn.com (or your private zone) to an OCI resolver listener endpoint.

For a quick admin connection from a laptop, use a Bastion port-forwarding session instead of opening the network:

oci bastion session create-port-forwarding --bastion-id <bastion-ocid> \
  --target-private-ip 10.0.2.15 --target-port 3306 \
  --ssh-public-key-file ~/.ssh/id_rsa.pub --session-ttl 3600 --wait-for-state SUCCEEDED

ssh -i ~/.ssh/id_rsa -N -L 3306:10.0.2.15:3306 -p 22 \
  <session-ocid>@host.bastion.<region>.oci.oraclecloud.com

mysql -h 127.0.0.1 -P 3306 -u admin -p

#3. Security lists, NSGs and routes

A TCP timeout with the DB system ACTIVE is almost always a missing rule on one side.

Check the NSG rules attached to the DB system:

oci network nsg rules list --nsg-id <nsg-ocid> \
  --query "data[?direction=='INGRESS'].{src:source, proto:protocol, ports:\"tcp-options\".\"destination-port-range\"}" \
  --output table

Run Network Path Analyzer in the Console (Networking → Network Command Center) from the client IP to the endpoint on port 3306. It shows which security rule or route table drops the traffic.

Warning

Don't fix a timeout by opening 3306 to 0.0.0.0/0 on the DB subnet. Allow the application subnet or NSG only, and use Bastion for people.

#4. Access denied and host not allowed

Connect as the administrator and look at the account:

SELECT user, host, plugin, account_locked, password_expired
FROM mysql.user
WHERE user = 'app_user';

• ERROR 1130 means no row matches the client's address. An account created as 'app_user'@'10.0.1.%' does not match a client that now connects from 10.0.5.20 through a new subnet or NAT.
• ERROR 1045 with a matching row means the password is wrong, or a more specific host row with a different password matches first.

Fix the account, scoping the host to the application subnet:

CREATE USER IF NOT EXISTS 'app_user'@'10.0.5.%' IDENTIFIED BY '<strong-password>';
GRANT SELECT, INSERT, UPDATE, DELETE ON appdb.* TO 'app_user'@'10.0.5.%';
ALTER USER 'app_user'@'10.0.5.%' ACCOUNT UNLOCK;

If the administrator password is lost, reset it from OCI without connecting:

oci mysql db-system update --db-system-id $DB --admin-password '<new-password>'

Caution

Passing a password on the command line leaves it in shell history. Run it from Cloud Shell, clear the history afterwards, and check the current docs for whether the change affects open sessions before doing it on production at peak time.

#5. Too many connections

SHOW VARIABLES LIKE 'max_connections';
SHOW GLOBAL STATUS LIKE 'Max_used_connections';

SELECT user, SUBSTRING_INDEX(host, ':', 1) AS client, command, COUNT(*) AS sessions
FROM information_schema.processlist
GROUP BY user, client, command
ORDER BY sessions DESC;

The default max_connections depends on the shape; check your configuration's value rather than assuming one. Common causes are a connection pool sized per application instance multiplied by an autoscaled tier, or idle Sleep sessions that are never closed.

Short term: kill idle sessions from the worst offender (KILL <id>;) and lower the pool size. Long term: raise max_connections through a custom configuration attached to the DB system, or move to a larger shape. Changing the configuration can require a restart; plan it.

#6. Client, driver and TLS problems

• ERROR 2059 / caching_sha2_password cannot be loaded: the client library is too old for the default MySQL 8 authentication plugin. Upgrade the connector (JDBC, mysqlclient, PHP mysqlnd) instead of switching the account to mysql_native_password, which is deprecated and disabled by default in 8.4.
• ERROR 3159: the server's configuration requires TLS. Connect with TLS:

mysql -h 10.0.2.15 -u app_user -p --ssl-mode=REQUIRED
mysqlsh --sql "mysql://app_user@10.0.2.15:3306" --ssl-mode=REQUIRED

Check what the server requires and whether your session uses TLS:

SHOW VARIABLES LIKE 'require_secure_transport';
SHOW SESSION STATUS LIKE 'Ssl_cipher';

An empty Ssl_cipher means the session is not encrypted.

#Prevent it next time

• Point applications at the DB system endpoint's DNS name and keep the hostname label stable. Never use instance IPs.
• Put the DB system in its own NSG and reference the application NSG as the source, so subnet changes don't break access.
• Create accounts with host patterns that match the application subnets, and add new subnets to the account list as part of the network change.
• Alarm on the CurrentConnections metric in the oci_mysql_database namespace against your max_connections (metric names vary by version; check the namespace in Metrics Explorer).
• Keep a Bastion and an admin account documented so an on-call engineer can get a SQL prompt in minutes.