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
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)
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 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.