NEED HELP!: Can't query DO managed database, data does show up in database manager

[jellederijke]

Hi there Wapplers,

NEED HELP! Project is nearing deadline…

I’m stuck with an issue regarding Managed Database by DigitalOcean. Any help is greatly appreciated!

The problem
I can’t query any data! The database is a Digital Ocean Managed database running MySQL with a legacy user for the authentication issues (mentioned in some other thread). The project is running on the staging target, which is a DO droplet created by Wappler.

I get this ETIMEDOUT status 500 error with these details:

{
  "status": "500",
  "code": "ETIMEDOUT",
  "message": "connect ETIMEDOUT",
  "stack": "Error: connect ETIMEDOUT\n    at Connection._handleConnectTimeout (/opt/node_app/node_modules/mysql/lib/Connection.js:409:13)\n    at Object.onceWrapper (events.js:420:28)\n    at Socket.emit (events.js:314:20)\n    at Socket._onTimeout (net.js:483:8)\n    at listOnTimeout (internal/timers.js:554:17)\n    at processTimers (internal/timers.js:497:7)\n    --------------------\n    at Protocol._enqueue (/opt/node_app/node_modules/mysql/lib/protocol/Protocol.js:144:48)\n    at Protocol.handshake (/opt/node_app/node_modules/mysql/lib/protocol/Protocol.js:51:23)\n    at Connection.connect (/opt/node_app/node_modules/mysql/lib/Connection.js:116:18)\n    at /opt/node_app/node_modules/knex/lib/dialects/mysql/index.js:68:18\n    at new Promise (<anonymous>)\n    at Client_MySQL.acquireRawConnection (/opt/node_app/node_modules/knex/lib/dialects/mysql/index.js:63:12)\n    at create (/opt/node_app/node_modules/knex/lib/client.js:290:39)"
}

When I try to connect to this same managed database from a php project I get a different error on querying the database:

{
  "code": 0,
  "file": "/var/www/html/dmxConnectLib/lib/db/Connection.php",
  "line": 40,
  "message": "Class '\\lib\\db\\server\\mysql2' not found",
  "trace": "#0 /var/www/html/dmxConnectLib/lib/db/Connection.php(24): lib\\db\\Connection->__construct(Object(lib\\App), Object(stdClass), 'remote_db_DO')\n#1 /var/www/html/dmxConnectLib/modules/dbconnector.php(26): lib\\db\\Connection::get(Object(lib\\App), 'remote_db_DO')\n#2 /var/www/html/dmxConnectLib/lib/App.php(195): modules\\dbconnector->select(Object(stdClass), 'query')\n#3 /var/www/html/dmxConnectLib/lib/App.php(127): lib\\App->execSteps(Object(stdClass))\n#4 /var/www/html/dmxConnectLib/lib/App.php(116): lib\\App->exec(Object(stdClass), false)\n#5 /var/www/html/dmxConnectLib/lib/App.php(94): lib\\App->exec(Object(stdClass))\n#6 /var/www/html/dmxConnect/api/strapi/get_houder.php(8): lib\\App->define(Object(stdClass))\n#7 {main}"
}

I already fixed the first connectivity issues thanks to these threads:

Error with Direct Database Connection with SSL on Digital Ocean

Here are my settings, this should work since it seems like we have the exact same setup. In my live production environment (because I still use the docker db for the local environment) Under server actions → globals → database connections → db [image] Project settings keep it on none [image] Here is a thread where I went through the process of fixing it, in case you need more info Remote database with docker @patrick the issue above still stands : Error with Direct Database Con…

Digital ocean managed database connection with SSL

Yes it is all fine but the SSL to your digital ocean database connection is a very different SSL than your web site. The SSL certificate is managed by digital ocean self because you are using their managed database with their url name. You can download the CA authority file that is offering by digital ocean next to their connection settings and use it as custom profile SSL in tge Wappler database connection settings.

Digital Ocean String in Database Manager

Just like magic; it connected successfully. Workaround Change profile to default. Create new user account in database from Digital Ocean control panel and select MySQL 5.x Legacy authentication option. Thank you, everyone! It’s going to be a fun day! [image]

What does work
I can Fetch Data from the Wappler database manager just fine:

This database is also connected to an instance of Strapi, running on a different droplet on Digital Ocean. Strapi works perfectly and is able to do queries just fine. Also accessing the Strapi api from Wappler is working fine.

Furthermore I have added Wapplers droplets ip to the access list of the managed database, as well as my home ip-address, as strapi’s droplets ip-address.

What I think is weird is that I CAN access the data, just not through a query that I can actually use on the front-end.

Any help is GREATLY appreciated, since I am nearing a deadline with this project, rather soon.

@karh is your setup with the managed db still working?

bg

Jelle

[jellederijke]

To add to my confusion:

I’ve tried to setup a default docker database, instead of the managed database (since that is not working right now). However I get this error:

With these settings (and full shutdown and clear all services and even wappler restart:

However, queriing the data does work and does show up in front-end:

[George]

Hi Jelle,

You have to understand that there are two sorts of database connections. One direct in the Database Manager for Wappler, and one in Server Connect - to be used on the server. Both should have the same name tought.

Usually when you use docker those are auto created, but when using Digital Ocean Managed databases you have to enter the different settings manually.

So check the database settings under the Server Connect globals - those are for usage on your webserver and are giving you the error.

[jellederijke]

OMG yes! thank you George. So after a LOT of fiddling around and thanks to your info regarding having 2 actual db connections I managed to get some queries going.

I do feel this took up waaaay to much time, which is by no means meant as offensive. Having a complete write-up for using Wappler with a remote managed db on digitalocean seems like a must to me, if that is a route anyone wants to take

So the two things that helped me out were:

• checking and fixing database connection settings in globals in server actions.
• changing SSL from custom with certificate to default. With the custom option pointing towards the certificate I got the self signed certificate error in the console on the front end.

Thanks again George (and everyone else posting here on this forum with their questions and solutions!) Now it’s time to make up some lost time