MySQL · SSL · GUI

Fix MySQL error 3159 in a GUI

pimvdmolen.nl · · ~6 min read

MySQL error 3159 (HY000) means the server refused the login because the transport was not secure. The full text is: Connections using insecure transport are prohibited while --require_secure_transport=ON. The password can be correct. A terminal session on the same account can still succeed when it used a Unix socket or TLS.

What the server counts as secure

require_secure_transport is a server variable. On MySQL, ON accepts encrypted TCP, a Unix socket, and Windows shared memory. Plain TCP is rejected. A Windows named pipe is rejected too, because MySQL does not treat named pipes as secure.

MariaDB documents the same 3159 sentence. It treats TLS, Unix sockets, and named pipes as secure, so a pipe MySQL would refuse can succeed there. Builds before 11.2.1 often returned error 1045 instead, which looks like a bad password.

A normal MySQL data directory already contains ca.pem, server-cert.pem, and server-key.pem when OpenSSL was present at install. Azure Database for MySQL Flexible Server ships with the variable ON and TLS 1.2 as the default. A self-managed host sets it under [mysqld] in my.cnf or my.ini, or with SET PERSIST require_secure_transport=ON.

The check runs before grants. Error 1045 is a password or user@host mismatch, or an account created with REQUIRE SSL that connected without TLS. Error 2061 is caching_sha2_password asking for TLS or the server RSA key on a first plain TCP login, even when this variable is OFF. If the dialog does not contain require_secure_transport, use Fix MySQL error 1045 in a GUI or Fix MySQL 8 caching_sha2_password in a GUI.

Why the CLI works and the GUI fails

On macOS and Linux, mysql -u app -p with no -h uses the socket, often /tmp/mysql.sock or /var/run/mysqld/mysqld.sock. That path counts as secure without TLS, so 3159 does not appear. Force TCP and disable encryption:

mysql -h 127.0.0.1 -P 3306 -u app -p --ssl-mode=DISABLED

With the variable ON, that command returns 3159. Repeat it with --ssl-mode=REQUIRED and no --ssl-ca. REQUIRED demands encryption and does not check the certificate. If the server offers TLS, the login succeeds. --ssl-mode=PREFERRED, the CLI default, tries TLS and then falls back to plain TCP. The fallback is what 3159 rejects.

A desktop client opens TCP to a host and port. It does not open the server socket unless you set a socket path on purpose. Host localhost in many tools still means TCP to 127.0.0.1. That field alone leaves the socket and hits the variable.

Read the flag from a session that already connects

Use the CLI login that works. Change the GUI only after these checks agree.

  1. Run SHOW VARIABLES LIKE 'require_secure_transport';. This article applies only when the value is ON.
  2. Run SHOW SESSION STATUS LIKE 'Ssl_cipher';. An empty value means this session is not using TLS. A cipher such as TLS_AES_128_GCM_SHA256 means it is.
  3. Run SELECT USER(), CURRENT_USER();. A socket login shows host localhost. A GUI pointed at 127.0.0.1 can match a different account row.
  4. On MySQL 8.4 and MySQL 9, skip SHOW VARIABLES LIKE 'have_ssl';. That variable was removed in 8.4.0 after deprecation in 8.0.26. MariaDB still has have_ssl. YES means the server can offer TLS.

If step 1 is OFF, stop. Another error is in that dialog, even when the text mentions SSL.

Set SSL mode to Require

Match the CLI test: demand encryption first, and add the CA file only after Test Connection succeeds.

Client Control Equivalent of --ssl-mode=REQUIRED Leave this off for the test
MySQL Workbench SSL tab, Use SSL Require No. If available still falls back to plain TCP
TablePlus SSL mode REQUIRED DISABLED
DBeaver Driver properties, sslMode REQUIRED DISABLED, or a leftover useSSL=false
HeidiSQL SSL tab Use SSL on, Certificate verification: No verification (insecure) Use SSL off

On the Workbench SSL tab, No is plain TCP and reproduces 3159. If available falls back to cleartext when the handshake fails, and cleartext is rejected. Pick Require and leave SSL CA File empty. Require and Verify CA and Require and Verify Identity need the CA file, and they fail when the hostname does not match the certificate. Click Test Connection.

In DBeaver, open the connection, select Driver properties, and set sslMode to REQUIRED. Remove sslMode=DISABLED or useSSL=false if a copied connection still has either one. On the SSL tab, turn Use SSL on when that checkbox is present. Leave the CA empty until the test works.

HeidiSQL: on the SSL tab, tick Use SSL and set Certificate verification to No verification (insecure). Leave the certificate fields empty for this pass. That encrypts without a CA check, the same bar as REQUIRED. If the next message is a certificate failure, point SSL CA certificate at the host CA only after Ssl_cipher is non-empty.

TablePlus lists the MySQL names directly: PREFERRED, DISABLED, REQUIRED, VERIFY-CA, and VERIFY-IDENTITY. Pick REQUIRED. CA Cert stays empty until that test works.

Run SHOW SESSION STATUS LIKE 'Ssl_cipher'; in the GUI editor. A cipher means this saved connection is on TLS. An empty value after a successful test means the client fell back.

A tunnel does not replace SSL mode

An SSH tunnel encrypts the hop from your laptop to the bastion. MySQL does not see that hop. It sees plain TCP to 127.0.0.1 on the database host, and require_secure_transport still rejects it. Keep the tunnel if port 3306 is not public, and also set SSL mode to Require. The SSH tunnel guide covers the bastion fields. Connect a MySQL GUI to a VPS without opening 3306 is the same split: the tunnel is how you reach the host, TLS is how you clear 3159.

The TabulaSQL connection form stores host, port, user, password, and an optional SSH tunnel. It has no SSL mode and no CA field. The app also rewrites localhost to 127.0.0.1, so the session is TCP, not the Unix socket, and that TCP session stays unencrypted. A tunnel there still ends as plain TCP, and 3159 remains. Use Workbench, TablePlus, DBeaver, or HeidiSQL while the server must keep the flag ON.

SET PERSIST require_secure_transport=OFF; needs an account that can persist system variables, and it allows cleartext after a restart. Use that on a local lab when you accept plain TCP. Leave it ON for any host that should refuse cleartext. REQUIRE SSL on one account is a separate rule: it fails as 1045, and a Unix socket does not satisfy it.

Frequently asked questions

The server has require_secure_transport ON and the client used a transport MySQL does not count as secure. Plain TCP and Windows named pipes are rejected. Encrypted TCP, a Unix socket, and Windows shared memory are accepted.

No. The tunnel encrypts the hop to the bastion. MySQL still sees plain TCP on the database host. Keep the tunnel if port 3306 is not public, and set SSL mode to Require as well.

On macOS and Linux, mysql with no -h uses a Unix socket, which counts as secure without TLS. A GUI usually opens TCP. Repeat the CLI test with --ssl-mode=REQUIRED against 127.0.0.1 to match the GUI path.