Question

Has anyone been able to use managed MySQL?

I tried to use Digital Ocean’s new managed MySQL but users I create use caching_sha2_password authentication which the Node mysql client doesn’t support. DigitalOcean support told me to run

ALTER USER myuser IDENTIFIED WITH mysql_native_password BY 'mypassword';

but I got a permissions error, apparently I don’t have permission to run that on their MySQL cluster.

So, is their new managed MySQL offering 100% useless for anyone wanting to use it with Node?

Show comments
Submit an answer

This textbox defaults to using Markdown to format your answer.

You can type !ref in this text area to quickly search our full set of tutorials, documentation & marketplace offerings and insert the link!

Sign In or Sign Up to Answer

These answers are provided by our Community. If you find them useful, show some love by clicking the heart. If you run into issues leave a comment, or add your own answer to help others.

Bobby Iliev • September 6, 2019

Hello,

I am already using the managed MySQL services for a few of my projects.

I’ve also tested your query and I can confirm that it is working as normal and also I can confirm that the doadmin database user has privileges to run this on your MySQL cluster.

There are two things that you might want to consider:

  • Create a new user with mysql_native_password, then you can use this for your projects as normal:
CREATE USER 'your_user'@'your_server_ip ' IDENTIFIED WITH mysql_native_password BY 'your_password';
  • Or if you would like to keep using the doadmin user, then the query that you need to run would be:
ALTER USER doadmin IDENTIFIED WITH mysql_native_password BY 'your_new_password_here';

If you are still getting any errors feel free to share them here and I’ll be happy to advise you further.

Regards, Bobby

Jason Kennemer • September 24, 2019

I’m also having the same issue. One of the issues I’m experiencing is the requirement to specify the ssl-mode in the connection string. To be able to connect at all to the DO managed MySQL service, I have to specify ssl-mode=DISABLED.

I’ve downloaded the Cert and applied manually to my pod, but I’m still getting the caching_sha2_password error after trying the ALTER USER query.

Tongas, you might try to see if you can add the ssl-mode=DISABLED or enable SSL from your app to the managed MySQL instance?

Chris Tsongas • September 6, 2019

Here’s the error I continue to get after creating a new user with CREATE USER 'your_user'@'localhost' IDENTIFIED WITH mysql_native_password BY 'your_password'; and assigning privileges:

2019-09-06T20:30:10.007394293Z app[web.1]: { Error: Handshake inactivity timeout
2019-09-06T20:30:10.007419844Z app[web.1]:     at Handshake.<anonymous> (/app/node_modules/mysql/lib/protocol/Protocol.js:160:17)
2019-09-06T20:30:10.007445275Z app[web.1]:     at Handshake.emit (events.js:197:13)
2019-09-06T20:30:10.007453335Z app[web.1]:     at Handshake._onTimeout (/app/node_modules/mysql/lib/protocol/sequences/Sequence.js:124:8)
2019-09-06T20:30:10.007460212Z app[web.1]:     at Timer._onTimeout (/app/node_modules/mysql/lib/protocol/Timer.js:32:23)
2019-09-06T20:30:10.007466844Z app[web.1]:     at listOnTimeout (timers.js:327:15)
2019-09-06T20:30:10.007484431Z app[web.1]:     at processTimers (timers.js:271:5)
2019-09-06T20:30:10.007492423Z app[web.1]:     --------------------
2019-09-06T20:30:10.007522501Z app[web.1]:     at Protocol._enqueue (/app/node_modules/mysql/lib/protocol/Protocol.js:144:48)
2019-09-06T20:30:10.007529061Z app[web.1]:     at Protocol.handshake (/app/node_modules/mysql/lib/protocol/Protocol.js:51:23)
2019-09-06T20:30:10.007535556Z app[web.1]:     at PoolConnection.connect (/app/node_modules/mysql/lib/Connection.js:119:18)
2019-09-06T20:30:10.007542081Z app[web.1]:     at Pool.getConnection (/app/node_modules/mysql/lib/Pool.js:48:16)
2019-09-06T20:30:10.007551525Z app[web.1]:     at Pool.query (/app/node_modules/mysql/lib/Pool.js:202:8)
2019-09-06T20:30:10.007557870Z app[web.1]:     at /app/model/database.js:17:8
2019-09-06T20:30:10.007584365Z app[web.1]:     at new Promise (<anonymous>)
2019-09-06T20:30:10.007591333Z app[web.1]:     at Object.exports.query (/app/model/database.js:15:9)
2019-09-06T20:30:10.007597417Z app[web.1]:     at Object.exports.get (/app/model/reminder.js:33:25)
2019-09-06T20:30:10.007603221Z app[web.1]:     at sendReminders (/app/reminder.js:16:34)
2019-09-06T20:30:10.007609780Z app[web.1]:   code: 'PROTOCOL_SEQUENCE_TIMEOUT',
2019-09-06T20:30:10.007615978Z app[web.1]:   fatal: true,
2019-09-06T20:30:10.007622201Z app[web.1]:   timeout: 10000 }