Solved Remote SQL Support on a per app basis

[Lonkle]

Oh, the irony. I got a VPS because my shared host didn’t allow Remote SQL-ing into the database. And lo and behold, I found out yesterday there was no way to make Sequel Pro work with Cloudron Subcontainers. I have a couple ideas. Like making an SSH user that auto-logs into an app with cloudron exec so that Sequel Pro can just use its SSH feature to remote SQL. But I don’t know if running a command automatically after logging into SSH is even possible.

This was mainly discussed here, but I think it’s be a great addition to Cloudron to have:

https://forum.cloudron.io/topic/3235/does-cloudron-support-remote-sql/5

And this may have been one of the primary reasons I switched to an VPS but I fell in love with Cloudron after this week so I can make due with PHPMyAdmin as soon as I find out if I can “side-install” it into the Wordpress Unmanaged app.

[girish]

I haven't used Sequel Pro, but you can connect using MySQL Workbench using the "Standard TCP/IP over SSH". Most modern clients should support this since it's the standard way to connect to databases behind a bastion host.

The MySQL hostname is gotten from output of docker inspect mysql | grep IPAddress on Cloudron. The other mysql values are gotten from cloudron exec --app myapp env | grep CLOUDRON_MYSQL.

[Lonkle]

@girish It worked perfectly! You are my hero rn. Sequel Pro is so much better than PHPMyAdmin ever could be. There's no way I could have figured out all of those commands to get the credentials on my own, thank you so much for telling me how you do it! ️

[Lonkle]

@girish Just bookmarked your reply since I know I'll need it for other apps in the future. You have no idea how grateful I am. ️

[marcusquinn]

@lonk I'm getting errors connecting with both Sequel Pro and MySQL Workbench. Did you do anything special other than the above? Needing to connect to a bunch of EspoCRM instances for faster data reviews & exports etc.

[girish]

@marcusquinn What errors are you getting? Any screenshots? You have to select "Standard TCP/IP over SSH"

[marcusquinn]

@girish A colleague has tried with both Sequel Pro and MySQL Workbench too and hit the same issues - but had success connecting with DBeaver using all the same settings. (we're both on Mac)

So it is certainly curious but that's a possible solution but I'm time-poor this week so will have to come back with more diagnostics.

The errors aren't really meaningful and suggest SP and MSWB issues with SSH somehow, which I guess is less commonly used.

Sorry, I'll come back with a but more when I get to look again. I do think this app justifies having a SQL webapp add-on though, just because there's data in the database people will likely want access too that the App doesn't provide for in protecting from most users, where a DB Admin would know what they are doing and access with care anyway.

[Lonkle]

@marcusquinn Gonna mark this unread and go check my settings. I use a Mac with Sequel Pro, my favorite editor. But I'll figure out if I did anything differently.

By the time nebulon gave me those settings, I think I did have to change one thing. But essentially if you can get in via SSH, then this should work - I'll double check. But for now I'm gonna mark this as unread to come back to it.

[marcusquinn]

@lonk Thanks, while looking at DBeaver (Java apps ain't my favourite), I found Beekeeper Studio for Mac, which looks pretty, so might give that a try too.

Sequel Pro looks like it hasn't been updated in a couple of years, I like it but they may have left space for more innovation.

[marcusquinn]

OK, connected with DBeaver.

I'm wondering if the other apps are crapping out due to the way I created my ssh key because I can't connect with Beekeeper Studio either. I thought I was all standard but maybe missing something obvious why all the other Apps don't work but DBeaver is handling it?

[girish]

@marcusquinn Any screenshots on what the error is with other apps?

[marcusquinn]

@girish A few but kinda meaningless:

I'm pretty certain I have all the right settings and values in the right places, as they work with DBeaver.

Sequel Pro:

MySQL Workbench:

Beekeeper Studio:

[marcusquinn]

Ooops - retina screenshots!

[girish]

@marcusquinn Sequel Pro seems to be crashing and it is some backtrace. MySQL workbench should work, I use it everyday on Linux. Can you give me a screenshot on how you have configured the connection ? Mine is configured something like below:

[marcusquinn]

@girish I think the issue with MySQL Workbench is I can't use the latest version as they don't support macOS 10.14 past a certain version, and a colleague on a later macOS can get it working on a later MySQL Workbench version but I can't update my OS at the moment until some other software I use is updated to work with the later OS. Classic catch-22 on that one.

I think the solution to this should just be to use DBeaver, it's free on all platforms, and seems to work on all OS versions. too.

Maybe better to save your time for the odd additional /phpmyadmin/ that might be useful on certain apps.

Certainly appreciate the help and looking, but I don't want to distract you too much given it's a software version compatibility issue and not a Cloudron issue really. Thank you all the same though!!