MySQL Connection Error

Question

MySQL Connection Error

Closed this issue 5 years ago · 11 comments

Running into trouble with Light Install. Error trying to connect to MySQL running in another container. Connection string has been modified in docker-compose.yml in JPProject.IdentityServer4.AdminUI as follows:

CUSTOMCONNSTR_SSOConnection: "server=172.17.0.3; port=3306; database=IdpDb; uid=root; password=xyz"

Relevant log snippet from running docker-compose:

jpproject-light-api_1  | [19:03:59 INF] Entity Framework Core 3.1.0 initialized 'JPProjectAdminUIContext' using provider 'Pomelo.EntityFrameworkCore.MySql' with options: MigrationsAssembly=JPProject.Admin.EntityFrameworkCore.MySql
jpproject-light-api_1  | [19:04:15 ERR] An error occurred using the connection to database '' on server '172.17.0.3'.
...........
...........
jpproject-light-api_1  | Unhandled exception. System.AggregateException: One or more errors occurred. (Error wating database. Check ConnectionString and ensure database exist)

Not sure why db name is not getting picked up. Any ideas?

Answer 1 · 2020-01-23T22:15:03.000Z

Hi @narbit !

By your description, I guess it's because the Containers must be in the same network.

https://docs.docker.com/engine/reference/commandline/network_connect/

Try to attach both containers to a default docker network, like bridge.

Answer 2 · 2020-01-23T23:03:37.000Z

Bruno (@brunohbrito), it would make sense but the error specifically says
An error occurred using the connection to database '' on server '172.17.0.3'
which would indicate it sees the server but can't connect to database without name.

To verify, I installed MySQL shell inside jpprojectadminapi container and tried to connect to the same IP and database via shell and it happily did. So still puzzled on what might have gone wrong.

Answer 3 · 2020-01-24T00:43:11.000Z

I noticed the use of "CUSTOMCONNSTR_SSOConnection" in docker-compose, does this mean I can only deploy to Azure since this env variable appears to be Azure-specific? How does the app read the connection string for local Docker deployments? Tried to modify appsettings.json but no luck either.

Answer 4 · 2020-01-24T03:58:21.000Z

I noticed the use of "CUSTOMCONNSTR_SSOConnection" in docker-compose, does this mean I can only deploy to Azure since this env variable appears to be Azure-specific? How does the app read the connection string for local Docker deployments? Tried to modify appsettings.json but no luck either.

In fact this configuration prevents, in Azure environments, ASP.NET Configuration auto set the Provider in ConnectionString, because we're defining database Provider from Config file. The demo version are running in aws with docker swarm, so it doesn't have any conflict with other Clouds.

You can see the implementation here: EnvironmentVariablesConfigurationProvider.cs

To verify, I installed MySQL shell inside jpprojectadminapi container and tried to connect to the same IP and database via shell and it happily did. So still puzzled on what might have gone wrong.

Great. Could you send your DatabaseType ?

            CUSTOMCONNSTR_SSOConnection: "<you database connstring>"
            ApplicationSettings:Authority: "<your sso url>"
            ApplicationSettings:DatabaseType: SqlServer

It's quite weird, I got the master version and tried it with https://github.com/IdentityServer/IdentityServer4/tree/master/samples/Quickstarts/5_EntityFramework as base. It worked well. Can you explain a litte bit about your environment ? So I can test with same settings to figure out what's happening

Answer 5 · 2020-01-24T23:56:46.000Z

In the end, you were actually right! Attaching containers to the same network in docker-compose (network_mode: bridge) has fixed the issue with DB connection! It was weird that the error made it sound like the schema was missing.

Ran into another issue with nginx not finding the host in upstream:
jpproject-light | 2020/01/24 23:49:29 [emerg] 1#1: host not found in upstream "jpproject-light-api" in /etc/nginx/nginx.conf:27

Trying some workarounds at the moment.

Answer 6 · 2020-01-25T00:53:34.000Z

Hi,

In attached file there is an example:

Left: docker-compose.yml
right: nginx/nginx.conf

These names should be the same. It's, the way nginx "see" the containers inside the network. By their hostnames.

Answer 7 · 2020-01-25T07:28:01.000Z

Right, makes sense. I have not changed any of the names in the above files. When I go inside admin container and try pinging http://jpproject-admin and http://jpproject-light-api, they are not resolving.

Answer 8 · 2020-01-26T21:34:22.000Z

The admin-ui container it's just a nginx running Angular 8 app. So it supposed to run in your browser. Then the javascript will connect in your SSO to request an access_token to make http requests to jpproject-light-api. So no network access is required between them.

Working in container, which is really important is that jpproject-light-api must have access to your SSO and is4 database as well.

Answer 9 · 2020-01-26T21:51:40.000Z

The admin-ui is http://localhost:4000 when I access it from the browser on the host (my local machine), right? I get "site not found" when navigating to it. So I thought the error "host not found in upstream "jpproject-light-api" when running docker-compose was to blame. Also, when inside admin container I can ping localhost:4000 and localhost:5002 but not http://jpproject-admin or http://jpproject-light-api. They are supposed to resolve, right?

Answer 10 · 2020-01-26T23:33:50.000Z

By default it is http://localhost:4300 and the jpproject-light-api is http://localhost:5002.

You can test api going to http://localhost:5002/swagger

Answer 11 · 2020-02-01T01:10:26.000Z

Hi @brunohbrito, just wanted to report back. Thanks for all the help and pointers! Finally got things working. There were a few issues getting existing IdP and existing MySQL (both local and in separate containers) play nice with AdminUI using light install.

All containers had to run on the same shared network. Using network_mode: bridge didn't work for me. I had to create a separate network and explicitly put all containers on it.
I had to borrow some of the docker-compose and nginx.conf settings from a full install (JPProject.IdentityServer4.SSO) project. Specifically, Authority for jpproject-light-api had to be set to URL of my IdP container (http://idp). IdP ports had to be mapped through nginx reverse proxy. As such, I had to add my IdP upstream and server definitions to nginx.conf
Because API was now talking to IdP container via container URL (http://idp), I had to hard-code Identity Server IssuerUri using options options.IssuerUri = http://localhost:xyz. This is so tokens passed for verification on backend from API to IdP didn't get rejected because IssuerUri was passed as localhost but IdP was expecting http://idp (due to headers).
MySQL connection string had to use MySQL container IP from the shared network I created and joined MySQL on.

I think these are the main points. I also used this SO post to try and sort things out. I can share more details if anyone runs into similar situation.