502 Bad Gateway after strapi switch database

System Information
  • Strapi Version: 3.4.0
  • Operating System: MySQL
  • Database: MySQL
  • Node Version: v12.18.3
  • NPM Version: 6.14.6
  • Yarn Version: 1.22.4

I swap strapi default database to MySQL it cause 502 error in my digitalOcean Droplet. I guessing strapi is not connected with MySQL in my droplet. I’ll listing my Strapi database config, mysql status and the mysql user status in below.

I am new to MySQL installation. Below that is my sql info. Other, i have create multiple user in mysql.

$ systemctl status mysql.service

● mysql.service - MySQL Community Server
   Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
   Active: active (running) since Sat 2021-01-23 07:31:48 UTC; 6h ago
   Process: 16990 ExecStartPre=/usr/share/mysql/mysql-systemd-start pre (code=exited, status=0/SUCCESS)
   Main PID: 17010 (mysqld)
     Status: "Server is operational"
     Tasks: 42 (limit: 2345)
     Memory: 353.0M
     CGroup: /system.slice/mysql.service
             └─17010 /usr/sbin/mysqld

One of mysql’s user status;

$ mysql -u user -h 127.0.0.1  -p

Connection id:      34
Current database:   
Current user:       user@localhost
SSL:            Cipher in use is secretID_STRING
Current pager:      stdout
Using outfile:      ''
Using delimiter:    ;
Server version:     8.0.22-0ubuntu0.20.04.3 (Ubuntu)
Protocol version:   10
Connection:     127.0.0.1 via TCP/IP
Server characterset:    utf8mb4
Db     characterset:    utf8mb4
Client characterset:    utf8mb4
Conn.  characterset:    utf8mb4
TCP port:       3306
Binary data as:     Hexadecimal
Uptime:         6 hours 49 min 4 sec

Threads: 4  Questions: 693  Slow queries: 0  Opens: 207  Flush tables: 3  Open tables: 128  Queries per second avg: 0.028

Here is my strapi database config

module.exports = ({ env }) => ({
  defaultConnection: "default",
  connections: {
    default: {
      connector: "bookshelf",
      settings: {
        client: "mysql",
        host: env("DATABASE_HOST", "127.0.0.1"),
        port: env.int("DATABASE_PORT", 3306),
        database: env("DATABASE_NAME", "databaseName"),
        username: env("DATABASE_USERNAME", "user"),
        password: env("DATABASE_PASSWORD", "MyUserPassword"),
        ssl: env.bool("DATABASE_SSL", false),
      },
      options: {},
    },
  },
});

This is my ufw status; Yes, the 3306 Mysql is allowed

$ ufw status

Status: active

To                         Action      From
--                         ------      ----
Nginx Full                 ALLOW       Anywhere                  
OpenSSH                    ALLOW       Anywhere                  
3000/tcp                   ALLOW       Anywhere                  
3306/tcp                   ALLOW       Anywhere                  
1337/tcp                   ALLOW       Anywhere                  
Nginx Full (v6)            ALLOW       Anywhere (v6)             
OpenSSH (v6)               ALLOW       Anywhere (v6)             
3000/tcp (v6)              ALLOW       Anywhere (v6)             
3306/tcp (v6)              ALLOW       Anywhere (v6)             
1337/tcp (v6)              ALLOW       Anywhere (v6)

solved. The problem is, DATABASE_USERNAME, i input new created mysql user for it. i changed to root, everythings work.

You most likely didn’t grant the user you created permissions on the database.

create database someName;
create user someUser@localhost identified by 'somePass';
grant all privileges on someName.* to someUser@localhost;

Yep, i tried it before. It doesn’t work. The error print out from VPS.
ER_NOT_SUPPORTED_AUTH_MODE: Client does not support authentication protocol requested by server; consider upgrading MySQL client

I did grant all permission for the database to the user, and re-do again following your command. This is a quick test in my DO. user: ‘selfpaths’ is going to hit does not support authentication. It only work in ‘user: root’;

var mysql = require("mysql");

var con = mysql.createConnection({
  host: "localhost",
  user: "selfpaths",
  password: "somePass",
  database: "TestDatabase",
});

con.connect(function (err) {
  if (err) throw err;
  //Select all customers and return the result object:
  con.query("SELECT * FROM users", function (err, result, fields) {
    if (err) throw err;
    console.log(result);
  });
});

This is the output for grant permission

mysql> show grants for 'selfpaths'@'localhost';
+-----------------------------------------------------------------------+
| Grants for selfpaths@localhost                                        |
+-----------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `selfpaths`@`localhost`                         |
| GRANT ALL PRIVILEGES ON `TestDatabase`.* TO `selfpaths`@`localhost`   |
| GRANT ALL PRIVILEGES ON `boilerplateEco`.* TO `selfpaths`@`localhost` |
+-----------------------------------------------------------------------+
3 rows in set (0.00 sec)

This is due to MySQL changing their auth method by default, you need the native password option:

https://dev.mysql.com/doc/refman/8.0/en/native-pluggable-authentication.html

Alternatively I would suggest using MariaDB instead of MySQL which will not have the new MySQL auth method: https://linuxize.com/post/how-to-install-mariadb-on-ubuntu-18-04/

It’s a drop in replacement for MySQL and is supported by Strapi.

Cool. thank for your replied. Appreciate. =)

I have deployed strapi to AWS EC2 with MySql instance on RDS. Followed the deployment guide religiously.

Logged on to the MySql CLI on AWS RDS instance and checked the auth method.
For the RDS user I created its already mysql_native_password.

It still shows me ER_NOT_SUPPORTED_AUTH_MODE error.

Where am I going wrong?

You created the user with the native_password right? There might be a setting in RDS that disables this method :thinking:

Alternatively you can use AWS RDS MariaDB instead of MySQL (drop in replacement for MySQL, and it’s open source!)

Yes I double checked.
There are 5 users as following

user plugin
strapi_admin mysql_native_password
mysql.infoschema caching_sha2_password
mysql.session caching_sha2_password
mysql.sys caching_sha2_password
rdsadmin mysql_native_password

I even tried changing the other 3 users from caching_sha2_password to mysql_native_password.
It still shows me ER_NOT_SUPPORTED_AUTH_MODE

I cannot use any other db as my client has specifically decided it would be either mongo or mysql.
And Strapi just dropped support for MongoDB. So MySQL it is.

I’m not quite sure with the issue here, I did spin up a MySQL RDS instance as a test and using the

ALTER USER 'strapi'@% IDENTIFIED WITH mysql_native_password BY '<password>';

worked perfectly both locally and in a test EC2 instance with a proper VPC setup.

I have the same issue and can’t switch from MySql. I have a standalone instance of MySql 8.0 with a user that was created using mysql_native_password and I’ve verified that the user is using the correct plugin. I know my instance is setup to use caching_sha2_password by default, could that be why it doesn’t work in my case?

Of course this started working immediately after posting here. I’m not sure what exactly fixed this for me. I deleted and recreated my user (with host %, not sure if that mattered) and deleted the volume I had been using. It may have been one of those changes that made this start working.

I have a feeling it would be the host the user is tied to, it’s possible your MySQL database saw the user request coming from a different IP so passing that wildcard (basically accept from any IP %) is likely what fixed the issue.