Agfdhyk

I'm trying to use a Docker compose script to connect to a MySql container from my ASP.NET Core 2.1 web app (hosting on Windows 10 Pro, all the containers using Linux), but I always an Unable to connect to any of the specified MySQL hosts error.

The app uses two services, a MongoDB container and a MySql container (using the latest Docker image with MySql 8). The API app waits for the MongoDB service using a bash script (https://github.com/vishnubob/wait-for-it), and this seems to work.

As for MySql, googling around for a solution I added:

• 
  command: --default-authentication-plugin=mysql_native_password (https://github.com/docker-library/mysql/issues/454)


• 
  MYSQL_ROOT_HOST=% (e.g. https://forums.docker.com/t/connect-to-container-create-by-docker-compose/44682/5).

I also followed the suggestions at https://github.com/docker-library/mysql/issues/275: I can confirm that the MySql root user is linked to any IP (%), while the IP address for the service is configured as *, which seems another way of representing "any address" like 0.0.0.0.

The relevant log messages I get when starting the composition and my docker compose script follow (lexminapi is logging the received environment variables to check the connection string):

   lexmin-mysql    | [Entrypoint] MySQL Docker Image 8.0.13-1.1.8

   lexmin-mysql    | [Entrypoint] Initializing database

   lexmin-mysql    | 2018-11-20T07:59:11.930174Z 0 [System] [MY-013169] [Server] /usr/sbin/mysqld (mysqld 8.0.13) initializing of server in progress as process 21

   lexminapi_1     | ZAX__CONNECTIONSTRING=Server=lexmin-mysql;Database=zax_master;Uid=root;Pwd=password;SslMode=none

   lexmin-mysql    | 2018-11-20T07:59:22.077828Z 5 [Warning] [MY-010453] [Server] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.

   lexminapi_1     | Application startup exception: MySql.Data.MySqlClient.MySqlException (0x80004005): Unable to connect to any of the specified MySQL hosts.

   lexminapi_1     |    at MySqlConnector.Core.ServerSession.ConnectAsync(ConnectionSettings cs, ILoadBalancer loadBalancer, IOBehavior ioBehavior, CancellationToken cancellationToken) in C:projectsmysqlconnectorsrcMySqlConnectorCoreServerSession.cs:line 322

   lexminapi_1     |    at MySqlConnector.Core.ConnectionPool.GetSessionAsync(MySqlConnection connection, IOBehavior ioBehavior, CancellationToken cancellationToken) in C:projectsmysqlconnectorsrcMySqlConnectorCoreConnectionPool.cs:line 112

   lexminapi_1     |    at MySqlConnector.Core.ConnectionPool.GetSessionAsync(MySqlConnection connection, IOBehavior ioBehavior, CancellationToken cancellationToken) in C:projectsmysqlconnectorsrcMySqlConnectorCoreConnectionPool.cs:line 141

   lexminapi_1     |    at MySql.Data.MySqlClient.MySqlConnection.CreateSessionAsync(Nullable`1 ioBehavior, CancellationToken cancellationToken) in C:projectsmysqlconnectorsrcMySqlConnectorMySql.Data.MySqlClientMySqlConnection.cs:line 405

   lexminapi_1     |    at MySql.Data.MySqlClient.MySqlConnection.OpenAsync(Nullable`1 ioBehavior, CancellationToken cancellationToken) in C:projectsmysqlconnectorsrcMySqlConnectorMySql.Data.MySqlClientMySqlConnection.cs:line 175

   lexminapi_1     |    at MySql.Data.MySqlClient.MySqlConnection.Open() in C:projectsmysqlconnectorsrcMySqlConnectorMySql.Data.MySqlClientMySqlConnection.cs:line 161

   lexminapi_1     |    at Zax.MySql.MySqlZaxAdmin.EnsureCatalogExists()

   lexminapi_1     |    at LexminApi.Startup.Configure(IApplicationBuilder app, IHostingEnvironment env) in /src/LexminApi/Startup.cs:line 310

   lexminapi_1     | --- End of stack trace from previous location where exception was thrown ---

   lexminapi_1     |    at Microsoft.AspNetCore.Hosting.ConventionBasedStartup.Configure(IApplicationBuilder app)

   lexminapi_1     |    at Microsoft.AspNetCore.HostFilteringStartupFilter.<>c__DisplayClass0_0.<Configure>b__0(IApplicationBuilder app)

   lexminapi_1     |    at Microsoft.AspNetCore.Hosting.Internal.AutoRequestServicesStartupFilter.<>c__DisplayClass0_0.<Configure>b__0(IApplicationBuilder builder)

   lexminapi_1     |    at Microsoft.AspNetCore.Hosting.Internal.WebHost.BuildApplication()

   lexminapi_1     | Unable to connect to any of the specified MySQL hosts.

   lexminapi_1     | MySql.Data.MySqlClient.MySqlException (0x80004005): Unable to connect to any of the specified MySQL hosts.

   lexminapi_1     |    at MySqlConnector.Core.ServerSession.ConnectAsync(ConnectionSettings cs, ILoadBalancer loadBalancer, IOBehavior ioBehavior, CancellationToken cancellationToken) in C:projectsmysqlconnectorsrcMySqlConnectorCoreServerSession.cs:line 322

   lexminapi_1     |    at MySqlConnector.Core.ConnectionPool.GetSessionAsync(MySqlConnection connection, IOBehavior ioBehavior, CancellationToken cancellationToken) in C:projectsmysqlconnectorsrcMySqlConnectorCoreConnectionPool.cs:line 112

   lexminapi_1     |    at MySqlConnector.Core.ConnectionPool.GetSessionAsync(MySqlConnection connection, IOBehavior ioBehavior, CancellationToken cancellationToken) in C:projectsmysqlconnectorsrcMySqlConnectorCoreConnectionPool.cs:line 141

   lexminapi_1     |    at MySql.Data.MySqlClient.MySqlConnection.CreateSessionAsync(Nullable`1 ioBehavior, CancellationToken cancellationToken) in C:projectsmysqlconnectorsrcMySqlConnectorMySql.Data.MySqlClientMySqlConnection.cs:line 405

   lexminapi_1     |    at MySql.Data.MySqlClient.MySqlConnection.OpenAsync(Nullable`1 ioBehavior, CancellationToken cancellationToken) in C:projectsmysqlconnectorsrcMySqlConnectorMySql.Data.MySqlClientMySqlConnection.cs:line 175

   lexminapi_1     |    at MySql.Data.MySqlClient.MySqlConnection.Open() in C:projectsmysqlconnectorsrcMySqlConnectorMySql.Data.MySqlClientMySqlConnection.cs:line 161

   lexminapi_1     |    at Zax.MySql.MySqlZaxAdmin.EnsureCatalogExists()

   lexminapi_1     |    at LexminApi.Startup.Configure(IApplicationBuilder app, IHostingEnvironment env) in /src/LexminApi/Startup.cs:line 310

   lexminapi_1     | --- End of stack trace from previous location where exception was thrown ---

   lexminapi_1     |    at Microsoft.AspNetCore.Hosting.ConventionBasedStartup.Configure(IApplicationBuilder app)

   lexminapi_1     |    at Microsoft.AspNetCore.HostFilteringStartupFilter.<>c__DisplayClass0_0.<Configure>b__0(IApplicationBuilder app)

   lexminapi_1     |    at Microsoft.AspNetCore.Hosting.Internal.AutoRequestServicesStartupFilter.<>c__DisplayClass0_0.<Configure>b__0(IApplicationBuilder builder)

   lexminapi_1     |    at Microsoft.AspNetCore.Hosting.Internal.WebHost.BuildApplication()

   lexminapi_1     |    at Microsoft.AspNetCore.Hosting.Internal.WebHost.StartAsync(CancellationToken cancellationToken)

   lexminapi_1     |    at Microsoft.AspNetCore.Hosting.WebHostExtensions.RunAsync(IWebHost host, CancellationToken token, String shutdownMessage)

   lexminapi_1     |    at Microsoft.AspNetCore.Hosting.WebHostExtensions.RunAsync(IWebHost host, CancellationToken token)

   lexminapi_1     |    at Microsoft.AspNetCore.Hosting.WebHostExtensions.Run(IWebHost host)

   lexminapi_1     |    at LexminApi.Program.Main(String args) in /src/LexminApi/Program.cs:line 64

   lexmin-mysql    | 2018-11-20T07:59:25.515319Z 5 [Warning] [MY-010315] [Server] 'user' entry 'mysql.infoschema@localhost' ignored in --skip-name-resolve mode.

   lexmin-mysql    | 2018-11-20T07:59:25.515408Z 5 [Warning] [MY-010315] [Server] 'user' entry 'mysql.session@localhost' ignored in --skip-name-resolve mode.

   lexmin-mysql    | 2018-11-20T07:59:25.515434Z 5 [Warning] [MY-010315] [Server] 'user' entry 'mysql.sys@localhost' ignored in --skip-name-resolve mode.

   lexmin-mysql    | 2018-11-20T07:59:25.515458Z 5 [Warning] [MY-010315] [Server] 'user' entry 'root@localhost' ignored in --skip-name-resolve mode.

   lexmin-mysql    | 2018-11-20T07:59:25.515505Z 5 [Warning] [MY-010323] [Server] 'db' entry 'performance_schema mysql.session@localhost' ignored in --skip-name-resolve mode.

   lexmin-mysql    | 2018-11-20T07:59:25.515540Z 5 [Warning] [MY-010323] [Server] 'db' entry 'sys mysql.sys@localhost' ignored in --skip-name-resolve mode.

   lexmin-mysql    | 2018-11-20T07:59:25.515570Z 5 [Warning] [MY-010311] [Server] 'proxies_priv' entry '@ root@localhost' ignored in --skip-name-resolve mode.

   lexmin-mysql    | 2018-11-20T07:59:25.515826Z 5 [Warning] [MY-010330] [Server] 'tables_priv' entry 'user mysql.session@localhost' ignored in --skip-name-resolve mode.

   lexmin-mysql    | 2018-11-20T07:59:25.515846Z 5 [Warning] [MY-010330] [Server] 'tables_priv' entry 'sys_config mysql.sys@localhost' ignored in --skip-name-resolve mode.

   lexmin-mysql    | 2018-11-20T07:59:27.343300Z 0 [System] [MY-013170] [Server] /usr/sbin/mysqld (mysqld 8.0.13) initializing of server has completed

   lexmin-mysql    | [Entrypoint] Database initialized

   lexmin-mysql    | 2018-11-20T07:59:30.122415Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.13) starting as process 67

   lexmin-mysql    | 2018-11-20T07:59:31.233579Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.

   lexmin-mysql    | 2018-11-20T07:59:31.270181Z 0 [Warning] [MY-010315] [Server] 'user' entry 'mysql.infoschema@localhost' ignored in --skip-name-resolve mode.

   lexmin-mysql    | 2018-11-20T07:59:31.270517Z 0 [Warning] [MY-010315] [Server] 'user' entry 'mysql.session@localhost' ignored in --skip-name-resolve mode.

   lexmin-mysql    | 2018-11-20T07:59:31.270675Z 0 [Warning] [MY-010315] [Server] 'user' entry 'mysql.sys@localhost' ignored in --skip-name-resolve mode.

   lexmin-mysql    | 2018-11-20T07:59:31.270818Z 0 [Warning] [MY-010315] [Server] 'user' entry 'root@localhost' ignored in --skip-name-resolve mode.

   lexmin-mysql    | 2018-11-20T07:59:31.271038Z 0 [Warning] [MY-010323] [Server] 'db' entry 'performance_schema mysql.session@localhost' ignored in --skip-name-resolve mode.

   lexmin-mysql    | 2018-11-20T07:59:31.272659Z 0 [Warning] [MY-010323] [Server] 'db' entry 'sys mysql.sys@localhost' ignored in --skip-name-resolve mode.

   lexmin-mysql    | 2018-11-20T07:59:31.272734Z 0 [Warning] [MY-010311] [Server] 'proxies_priv' entry '@ root@localhost' ignored in --skip-name-resolve mode.

   lexmin-mysql    | 2018-11-20T07:59:31.285399Z 0 [Warning] [MY-010330] [Server] 'tables_priv' entry 'user mysql.session@localhost' ignored in --skip-name-resolve mode.

   lexmin-mysql    | 2018-11-20T07:59:31.286316Z 0 [Warning] [MY-010330] [Server] 'tables_priv' entry 'sys_config mysql.sys@localhost' ignored in --skip-name-resolve mode.

   lexmin-mysql    | 2018-11-20T07:59:31.299467Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.13'  socket: '/var/lib/mysql/mysql.sock'  port: 0  MySQL Community Server - GPL.

   lexmin-mysql    | 2018-11-20T07:59:31.474623Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Socket: '/var/run/mysqld/mysqlx.sock'

   lexmin-mysql    | Warning: Unable to load '/usr/share/zoneinfo/iso3166.tab' as time zone. Skipping it.

   lexmin-mysql    | Warning: Unable to load '/usr/share/zoneinfo/leapseconds' as time zone. Skipping it.

   lexmin-mysql    | Warning: Unable to load '/usr/share/zoneinfo/tzdata.zi' as time zone. Skipping it.

   lexmin-mysql    | Warning: Unable to load '/usr/share/zoneinfo/zone.tab' as time zone. Skipping it.

   lexmin-mysql    | Warning: Unable to load '/usr/share/zoneinfo/zone1970.tab' as time zone. Skipping it.

   lexmin-mysql    | 2018-11-20T07:59:37.721908Z 9 [Warning] [MY-010315] [Server] 'user' entry 'healthchecker@localhost' ignored in --skip-name-resolve mode.

   lexmin-mysql    | 2018-11-20T07:59:37.721967Z 9 [Warning] [MY-010315] [Server] 'user' entry 'mysql.infoschema@localhost' ignored in --skip-name-resolve mode.

   lexmin-mysql    | 2018-11-20T07:59:37.721997Z 9 [Warning] [MY-010315] [Server] 'user' entry 'mysql.session@localhost' ignored in --skip-name-resolve mode.

   lexmin-mysql    | 2018-11-20T07:59:37.722022Z 9 [Warning] [MY-010315] [Server] 'user' entry 'mysql.sys@localhost' ignored in --skip-name-resolve mode.

   lexmin-mysql    | 2018-11-20T07:59:37.722068Z 9 [Warning] [MY-010315] [Server] 'user' entry 'root@localhost' ignored in --skip-name-resolve mode.

   lexmin-mysql    | 2018-11-20T07:59:37.722135Z 9 [Warning] [MY-010323] [Server] 'db' entry 'performance_schema mysql.session@localhost' ignored in --skip-name-resolve mode.

   lexmin-mysql    | 2018-11-20T07:59:37.722157Z 9 [Warning] [MY-010323] [Server] 'db' entry 'sys mysql.sys@localhost' ignored in --skip-name-resolve mode.

   lexmin-mysql    | 2018-11-20T07:59:37.722203Z 9 [Warning] [MY-010311] [Server] 'proxies_priv' entry '@ root@localhost' ignored in --skip-name-resolve mode.

   lexmin-mysql    | 2018-11-20T07:59:37.722596Z 9 [Warning] [MY-010330] [Server] 'tables_priv' entry 'user mysql.session@localhost' ignored in --skip-name-resolve mode.

   lexmin-mysql    | 2018-11-20T07:59:37.722642Z 9 [Warning] [MY-010330] [Server] 'tables_priv' entry 'sys_config mysql.sys@localhost' ignored in --skip-name-resolve mode.

   lexmin-mysql    | 

   lexmin-mysql    | [Entrypoint] ignoring /docker-entrypoint-initdb.d/*

   lexmin-mysql    | 

   lexmin-mysql    | 2018-11-20T07:59:37.765710Z 10 [System] [MY-013172] [Server] Received SHUTDOWN from user root. Shutting down mysqld (Version: 8.0.13).

   lexmin-mysql    | 2018-11-20T07:59:40.346144Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.13)  MySQL Community Server - GPL.

   lexmin-mysql    | [Entrypoint] Server shut down

   lexmin-mysql    | 

   lexmin-mysql    | [Entrypoint] MySQL init process done. Ready for start up.

   lexmin-mysql    | 

   lexmin-mysql    | [Entrypoint] Starting MySQL 8.0.13-1.1.8

   lexmin-mysql    | 2018-11-20T07:59:41.244150Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.13) starting as process 1

   lexmin-mysql    | 2018-11-20T07:59:42.257165Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.

   lexmin-mysql    | 2018-11-20T07:59:42.286105Z 0 [Warning] [MY-010315] [Server] 'user' entry 'healthchecker@localhost' ignored in --skip-name-resolve mode.

   lexmin-mysql    | 2018-11-20T07:59:42.286148Z 0 [Warning] [MY-010315] [Server] 'user' entry 'mysql.infoschema@localhost' ignored in --skip-name-resolve mode.

   lexmin-mysql    | 2018-11-20T07:59:42.286179Z 0 [Warning] [MY-010315] [Server] 'user' entry 'mysql.session@localhost' ignored in --skip-name-resolve mode.

   lexmin-mysql    | 2018-11-20T07:59:42.286210Z 0 [Warning] [MY-010315] [Server] 'user' entry 'mysql.sys@localhost' ignored in --skip-name-resolve mode.

   lexmin-mysql    | 2018-11-20T07:59:42.286236Z 0 [Warning] [MY-010315] [Server] 'user' entry 'root@localhost' ignored in --skip-name-resolve mode.

   lexmin-mysql    | 2018-11-20T07:59:42.286296Z 0 [Warning] [MY-010323] [Server] 'db' entry 'performance_schema mysql.session@localhost' ignored in --skip-name-resolve mode.

   lexmin-mysql    | 2018-11-20T07:59:42.286318Z 0 [Warning] [MY-010323] [Server] 'db' entry 'sys mysql.sys@localhost' ignored in --skip-name-resolve mode.

   lexmin-mysql    | 2018-11-20T07:59:42.286349Z 0 [Warning] [MY-010311] [Server] 'proxies_priv' entry '@ root@localhost' ignored in --skip-name-resolve mode.

   lexmin-mysql    | 2018-11-20T07:59:42.292963Z 0 [Warning] [MY-010330] [Server] 'tables_priv' entry 'user mysql.session@localhost' ignored in --skip-name-resolve mode.

   lexmin-mysql    | 2018-11-20T07:59:42.293011Z 0 [Warning] [MY-010330] [Server] 'tables_priv' entry 'sys_config mysql.sys@localhost' ignored in --skip-name-resolve mode.

   lexmin-mysql    | 2018-11-20T07:59:42.303034Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.13'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Server - GPL.

   lexmin-mysql    | 2018-11-20T07:59:42.329828Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Socket: '/var/run/mysqld/mysqlx.sock' bind-address: '::' port: 33060

   lexminapi_1     | Application is shutting down...

Script:

   version: '3.4'



   services:

     # MongoDB

     lexmin-mongo:

       image: mongo

       container_name: lexmin-mongo

       environment:

         - MONGO_DATA_DIR=/data/db

         - MONGO_LOG_DIR=/dev/null

       ports:

         - 27017:27017

       command: mongod --smallfiles --logpath=/dev/null # --quiet

       ports:

         - 27017:27017

       networks:

         - lexmin-network



     # MySql

     lexmin-mysql:

       image: mysql/mysql-server

       container_name: lexmin-mysql

       command: --default-authentication-plugin=mysql_native_password

       environment:

         - MYSQL_ROOT_PASSWORD=password

         - MYSQL_ROOT_HOST=%

       ports:

         - 3306:3306

       networks:

         - lexmin-network



     lexminapi:

       image: XXX:lexminapi

       ports:

         - 34577:80

       depends_on:

         - lexmin-mongo

         - lexmin-mysql

       command: ["./wait-for-it.sh", "lexmin-mongo:27017", "--", "dotnet", "LexminApi.dll"]

       environment:

         # for Windows use : as separator, for non Windows use __

         # (see https://github.com/aspnet/Configuration/issues/469)

         - ZAX__CONNECTIONSTRING=Server=lexmin-mysql;Database=zax_master;Uid=root;Pwd=password;SslMode=none

       networks:

         - lexmin-network



   networks:

     lexmin-network

I finally seem to have found a solution, involving two changes to the above:

1. it appears that we must use a dictionary rather than an array when configuring MySql environment variables, as found at connecting to a docker-compose mysql container denies access but docker running same image does not. So I changed my composer like this:

environment:

      # the password that will be set for the MySQL root superuser account

      MYSQL_ROOT_PASSWORD: password

      MYSQL_ROOT_HOST: '%'

Note that using a dictionary requires putting % between quotes, or you will get a YAML error.

2. once changed as above, I got intermittent results: sometimes the app would start, most of the times anyway it still got a connection error. As this pointed at a timeout issue, I googled around for a good retry-pattern and found Polly. I thus wrapped my MySql seed code into a Polly Execute method, like this:

Policy.Handle()

    .WaitAndRetry(new

    {

        TimeSpan.FromSeconds(10),

        TimeSpan.FromSeconds(30),

        TimeSpan.FromSeconds(60)

    }, (exception, timeSpan, context) =>

    {

        string message = $"Unable to connect to MySql (sleep {timeSpan}): {exception.Message}";

        Console.WriteLine(message);

    }).Execute(() =>

{

  // ... MySql init code here...

});

Note that the correct place for seeding in ASP.NET Core 2 is not Startup.cs; see e.g. How to seed in Entity Framework Core 2?.

When I launch the app, I can now see the first timeout at 10", and then the retry is triggered until the app is launched without exiting because of a MySqlException.