Has anyone been able to use managed MySQL?

September 6, 2019 193 views
DigitalOcean
30e8cdc2b0b21c99f4895bf509b996de8f474e94
By:
tsongas

I tried to use Digital Ocean’s new managed MySQL but users I create use cachingsha2password 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?

1 comment
  • tsongas September 6, 2019

    Edit…sorry…I can run that ALTER USER command but it has no effect unless I then run FLUSH PRIVILEGES which I don’t have permission to do on the cluster.

Log In to Comment
3 Answers
bobbyiliev MOD 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

Reply
  • tsongas September 6, 2019

    Sorry, I’m able to run that ALTER USER command, but it doesn’t have any effect unless you then also run “FLUSH PRIVILEGES” which is where I get the permissions error.

    • bobbyiliev MOD September 6, 2019

      If you update the user using ALTER, nothing should need to be flushed as the changes take effect immediately.

      • 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 }
        • bobbyiliev MOD September 7, 2019

          Hello,

          It looks like that the connection is timing out now. This is most likely because of the custom port that the managed cluster uses.

          You need to make sure that you open the traffic for this port via your droplet’s firewall, so that your droplet could connect to the managed cluster.

          And also make sure to allow the droplet’s IP address on your managed cluster, you can do that via your DigitalOcean control panel.

          Hope that this helps!
          Regards,
          Bobby

          • tsongas September 7, 2019

            This makes no sense to me. Of course I added the droplet to my managed cluster as a trusted source. I completely disabled the UFW firewall on the droplet, and am still getting the same error.

            I never had a problem connecting to my MySQL droplet, even though I never opened incoming port 3306 on the web droplet’s firewall. So, I’m not sure why I’d have to open incoming port 25060 on the web droplet to connect to the MySQL cluster. I allowed all outgoing traffic by default, and like I said still getting the same error even with UFW completely disabled.

          • bobbyiliev MOD September 7, 2019

            I would suggest testing the connection from your droplet to the managed cluster using telnet to make sure that the droplet is actually able to communicate with the cluster:

            telnet your_managed_cluster 25060

            The the Handshake inactivity timeout error means that the default timeout was exceeded which usually is due to something interfering with the connection such as a firewall or some configuration problem. This is why I mentioned that you need to make sure that the custom port is open on your droplet, and I was referring to outgoing traffic of course.

          • bobbyiliev MOD September 7, 2019

            I tried this at my end:

            • I created a new managed cluster and a new droplet

            • I allowed the traffic from the droplet to the cluster and I allowed the droplet on the cluster as well

            • I used ALTER to adjust the default user

            • Then I installed node test the connection and I created a simple test.js app:

            My package.json:

            {
  "name": "node-mysql",
  "version": "0.0.1",
  "dependencies": {
    "express": "^4.10.6",
    "mysql": "^2.5.4"
  }
}

            My test.js file:

            var mysql      = require('mysql');
var connection = mysql.createConnection({
  host     : 'my_cluster.db.ondigitalocean.com',
  user     : 'doadmin',
  password : 'my_password',
  database : 'defaultdb',
  port     : '25060'
});

connection.connect(function(err){
if(!err) {
    console.log("The connection was successful!");    
} else {
    console.log("Error connecting to database..");    
}
});

connection.query('SELECT * from test', function(err, rows, fields) {
  if (!err)
    console.log('Result: ', rows);
  else
    console.log('Error while performing Query.');
});

connection.end();
            • I created a test table with 1 entry only.

            It all worked like a charm:

            # node test.js 
The connection was successful!
Result:  [ RowDataPacket { test: 'It works!' } ]

            Hope that this helps!
            Regards,
            Bobby

greenaro September 6, 2019

I’m facing a similar issue with the provided primary user “doadmin” having no super privileges. Not even able to import an existing db because of this

Apparently, “root” is the only user on the cluster with any rights

Reply
  • tsongas September 6, 2019

    I’m looking into Aiven to see if their managed MySQL solution has super privileges.

    • iskamwebsite September 6, 2019

      What is the point of having a MySQL solution with super privileges? The point of a managed service is not to have root/admin access and not to have to deal with the management, configuration and etc.

      If you have super privileges then the service is no longer managed.. that way the vendor can not really guarantee anything as you can mess up something and they would not have any control over it.

      If you need super privileges that much just install MySQL on a droplet and you can do whatever you like there with root access.

      • tsongas September 6, 2019

        I already installed MySQL on a droplet, before their managed service was available.

        What’s the point of having a managed service that doesn’t work at all because my app can’t access it?

        • iskamwebsite September 6, 2019

          What do you mean that it does not work? It’s a matter of a couple commands, isn’t it? You would usually take with any database, both managed and unmanaged.

          1. Create a new database
          2. Create a new user
          3. Grant privileges

          The only specific thing is that you need to specify mysql_native_password while creating the new user.

          • tsongas September 6, 2019

            Tried this, still unable to connect due to this Node error:

            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 }
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 }
Reply
Have another answer? Share your knowledge.

Related Questions