MySqlException: Unable to connect to any of the specified MySQL hosts.

[nicowitteman]

When trying to get this working with the Azure in-app MySQL database, I get the MySqlException: Unable to connect to any of the specified MySQL hosts. I am using the Pomelo.EntityFrameworkCore.MySql Nuget package.
In startup.cs, I put
services.AddDbContext<ApplicationDbContext>(options => options.UseMySql(Environment.GetEnvironmentVariable("MYSQLCONNSTR_localdb")));
On the Azure server, that is translated to
Database=localdb;Data Source=127.0.0.1:53978;User Id=azure;Password=xxxxxxx
Any clues?

[nicowitteman]

The solution is not to trust the Azure-provided connectionstring "MYSQLCONNSTR_localdb". Instead, the format to use is: server=127.0.0.1;userid=azure;password=XXXX;database=localdb;Port=nnnnn.
See: https://social.msdn.microsoft.com/Forums/en-US/7e577b74-bbc8-41ea-a5e4-075b0eaa8622/aspnet-core-mvc-and-mysql-in-app?forum=windowsazurewebsitespreview

[jasonsturges]

@nicowitteman Thanks for researching this, and letting me know the solution.

[01F0]

Indeed, thanks @nicowitteman 👍
In my case, I only had to add the port setting (Port=53374).

[ranvirsingh]

Unfortunately, still looking for this workaround in 2023

Based on the article https://github.com/projectkudu/kudu/wiki/MySQL-in-app the port could be dynamic,
viz.

In fact, the port number may vary for each application life cycle depending on its availability at startup time.

Hence following function should help convert it into correct format.

var connectionString = Environment.GetEnvironmentVariable("MYSQLCONNSTR_localdb");
var repurposedConnectionString = ConvertConnectionString(connectionString);

public static string ConvertConnectionString(string connectionString)
    {
        // Split the connection string into individual properties
        var properties = connectionString.Split(';')
            .Select(p => p.Split('='))
            .ToDictionary(p => p[0].ToLower(), p => p[1]);

        // Extract the server, port, and database properties
        var server = "";
        var port = "";
        if (properties.ContainsKey("data source"))
        {
            var dataSource = properties["data source"];
            var colonIndex = dataSource.IndexOf(':');
            if (colonIndex >= 0)
            {
                server = dataSource.Substring(0, colonIndex);
                port = dataSource.Substring(colonIndex + 1);
            }
            else
            {
                server = dataSource;
            }
        }
        var database = properties.ContainsKey("database") ? properties["database"] : "";

        // Extract the user ID and password properties
        var userId = properties.ContainsKey("user id") ? properties["user id"] : "";
        var password = properties.ContainsKey("password") ? properties["password"] : "";

        // Build the new connection string in the desired format
        var newConnectionString = $"server={server};userid={userId};password={password};database={database};";
        if (!string.IsNullOrEmpty(port))
        {
            newConnectionString += $"Port={port}";
        }

        // Return the new connection string
        return newConnectionString;
    }