ServerVersion.AutoDetect(connectionString) fails

[pantonis]

Steps to reproduce

Ideally include a complete code listing that we can run to reproduce the issue. Alternatively, you can provide a project/solution that we can run.

The issue

I am trying to run Add-Migration command and I get

Exception message: Unable to create a 'DbContext' of type ''. The exception 'Unable to connect to any of the specified MySQL hosts.' was thrown while attempting to create an instance. For the different patterns supported at design time, see https://go.microsoft.com/fwlink/?linkid=851728

System.InvalidOperationException: The entry point exited without ever building an IHost.
   at Microsoft.Extensions.Hosting.HostFactoryResolver.HostingListener.CreateHost()
   at Microsoft.Extensions.Hosting.HostFactoryResolver.<>c__DisplayClass10_0.<ResolveHostFactory>b__0(String[] args)
   at Microsoft.Extensions.Hosting.HostFactoryResolver.<>c__DisplayClass13_0.<ResolveServiceProviderFactory>b__3(String[] args)
   at Microsoft.EntityFrameworkCore.Design.Internal.AppServiceProviderFactory.CreateFromHosting(String[] args)
An error occurred while accessing the Microsoft.Extensions.Hosting services. Continuing without the application service provider. Error: The entry point exited without ever building an IHost.
No application service provider was found.
Finding DbContext classes in the project...
Using DbContext factory 'MyDbContextFactory'.
Microsoft.EntityFrameworkCore.Design.OperationException: Unable to create a 'DbContext' of type ''. The exception 'Unable to connect to any of the specified MySQL hosts.' was thrown while attempting to create an instance. For the different patterns supported at design time, see https://go.microsoft.com/fwlink/?linkid=851728
 ---> MySqlConnector.MySqlException (0x80004005): Unable to connect to any of the specified MySQL hosts.
   at MySqlConnector.Core.ServerSession.OpenTcpSocketAsync(ConnectionSettings cs, ILoadBalancer loadBalancer, Activity activity, IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/Core/ServerSession.cs:line 1063
   at MySqlConnector.Core.ServerSession.ConnectAsync(ConnectionSettings cs, MySqlConnection connection, Int64 startingTimestamp, ILoadBalancer loadBalancer, Activity activity, IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/Core/ServerSession.cs:line 425
   at MySqlConnector.MySqlConnection.CreateSessionAsync(ConnectionPool pool, Int64 startingTimestamp, Activity activity, Nullable`1 ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/MySqlConnection.cs:line 932
   at MySqlConnector.MySqlConnection.CreateSessionAsync(ConnectionPool pool, Int64 startingTimestamp, Activity activity, Nullable`1 ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/MySqlConnection.cs:line 938
   at MySqlConnector.MySqlConnection.OpenAsync(Nullable`1 ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/MySqlConnection.cs:line 419
   at MySqlConnector.MySqlConnection.Open() in /_/src/MySqlConnector/MySqlConnection.cs:line 381
   at Microsoft.EntityFrameworkCore.ServerVersion.AutoDetect(String connectionString)
   at MyProject.Console.MyDbContextFactory.CreateDbContext(String[] args) in C:\SourceControl\MyProject\src\MyProject.Console\MyDbContextFactory.cs:line 34
   at System.RuntimeMethodHandle.InvokeMethod(Object target, Void** arguments, Signature sig, Boolean isConstructor)
   at System.Reflection.MethodBaseInvoker.InvokeDirectByRefWithFewArgs(Object obj, Span`1 copyOfArgs, BindingFlags invokeAttr)
   --- End of inner exception stack trace ---
   at Microsoft.EntityFrameworkCore.Design.Internal.DbContextOperations.CreateContext(String contextType)
   at Microsoft.EntityFrameworkCore.Design.Internal.MigrationsOperations.AddMigration(String name, String outputDir, String contextType, String namespace)
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.AddMigrationImpl(String name, String outputDir, String contextType, String namespace)
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.AddMigration.<>c__DisplayClass0_0.<.ctor>b__0()
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.<>c__DisplayClass3_0`1.<Execute>b__0()
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.Execute(Action action)
Unable to create a 'DbContext' of type ''. The exception 'Unable to connect to any of the specified MySQL hosts.' was thrown while attempting to create an instance. For the different patterns supported at design time, see https://go.microsoft.com/fwlink/?linkid=851728

Further technical details

This is my code

DbContextOptionsBuilder<MyDbContext> optionsBuilder = new DbContextOptionsBuilder<MyDbContext>();
 optionsBuilder.UseMySql(connectionString, ServerVersion.AutoDetect(connectionString),
                         options =>
                         {
                             options.EnableRetryOnFailure();
                             options.MigrationsAssembly("MyAssembly.MariaDb");
                         });

As soon as I change it to this

DbContextOptionsBuilder<MyDbContext> optionsBuilder = new DbContextOptionsBuilder<MyDbContext>();
optionsBuilder.UseMySql(connectionString, ServerVersion.Create(Version.Parse("10.10.1"), Pomelo.EntityFrameworkCore.MySql.Infrastructure.ServerType.MariaDb),
                         options =>
                         {
                             options.EnableRetryOnFailure();
                             options.MigrationsAssembly("MyAssembly.MariaDb");
                         });

It works. I have just updated to 8.0.1 so I don't know if this is the cause

MySQL version: 10.10.1 (MariaDb) Operating system: Windows 10 Pomelo.EntityFrameworkCore.MySql version: 8.0.1 Microsoft.AspNetCore.App version: 8.0.2

Other details about my project setup:

[lauxjpn]

I have just updated to 8.0.1 [...]

@pantonis What version of Pomelo where you using before the upgrade?

---

ServerVersion.AutoDetect(connectionString) opens a connection to the database server to retrieve the version of the server. If you add a migration and EF Core runs the code that executes ServerVersion.AutoDetect(connectionString), the database server referenced in the connection string must be available, or the ServerVersion cannot get detected:

https://github.com/PomeloFoundation/Pomelo.EntityFrameworkCore.MySql/blob/c8bf3ea8a25f4cc5d1a6a5f884d2a4993e6e6dff/src/EFCore.MySql/Infrastructure/ServerVersion.cs#L61-L82

To generate the appropriate migrations, Pomelo needs to know the correct server version.

We generally do not recommend using ServerVersion.AutoDetect(connectionString) in production code. We recommend to explicitly specify the ServerVersion (which you now switched to), for example:

var optionsBuilder = new DbContextOptionsBuilder<MyDbContext>()
    .UseMySql(
        connectionString,
        ServerVersion.Parse("10.10.1-mariadb"),
        options =>
        {
            options.EnableRetryOnFailure();
            options.MigrationsAssembly("MyAssembly.MariaDb");
        });

[pantonis]

We updated 8.X but we didn't use migrations on 8.0.0. We used migrations on 7.X and then on 8.0.1 where we hit the issue We have several clients each running different version of MariaDb. If we configure it somehow on each client then on each client update of MariaDb we need to update all of it microservices. This is very inconvienent as we have a lot of clients and dozens of microservices that need to be updated. Any other workaround

[lauxjpn]

Pomelo needs to know the version of the database server (or at least the minimum server version it should support).

If you want to use ServerVersion.AutoDetect(connectionString), the database server that you reference in connectionString must be reachable.

If you are having multiple clients with different database server versions, they are also using different connection strings. You can configure the connection string in a settings file, along with the server version string.

---

If you want to use the latest features for all your clients, you should generate an individual migration set for each client using their individual database server version.

If you don't care about the latest MariaDB features for all you clients, you could specify a static minimum server version that is <= the lowest server version of any of your clients. Then you can generate one set of migrations that works for all your clients.

[pantonis]

If you want to use ServerVersion.AutoDetect(connectionString), the database server that you reference in connectionString must be reachable.

It is reachable. That is why I opened the ticket. Because with 7.0 version we didn't have any issues on that.

[lauxjpn]

It is reachable. That is why I opened the ticket. Because with 7.0 version we didn't have any issues on that.

If I understand you correctly, you said earlier that you did not use migrations before 8.0.1:

We updated 8.0 but we didn't use migrations on 8.0.0.

Did I understand you correctly, or did you use migrations before 8.0.1?

[pantonis]

Apologies for confusing you. We didn't use it for 8.0.0. We used it for version 7.X and then we upgraded to 8.0.1 and tried to use it on 8.0.1. This is where we noticed the issue. Let me correct my initial message

[lauxjpn]

Please check, that you can indeed connect to the database from the same machine, that you execute the Add-Migration command on.

Execute the following in a new console program on the same machine, that you execute the Add-Migration command on (use <PackageReference Include="MySqlConnector" Version="2.3.5" />):

var connectionString = "your connection string 100% identical to the one you use in your app";
using var connection = new MySqlConnection(connectionString);
connection.Open();

If it works, then move to the next section below. If it fails, then you don't have a working connection to the database referenced in your connection string.

---

There was a single change between 8.0.0 and 8.0.1 in regards to the ServerVersion.AutoDetect() method, that added AutoEnlist = false and Pooling = false to the connection string.

Those changes should not have any influence on the matter, but if you want to verify this, you could revert those changes in a customized auto detect method:

public static ServerVersion CustomAutoDetect(string connectionString) 
{ 
    using var connection = new MySqlConnection( 
        new MySqlConnectionStringBuilder(connectionString) 
        { 
            Database = string.Empty, 
            // AutoEnlist = false, 
            // Pooling = false, 
        }.ConnectionString); 
    connection.Open(); 
    return Parse(connection.ServerVersion); 
}

You then call this CustomAutoDetect(connectionString) method instead of the ServerVersion.AutoDetect(connectionString) one that ships with Pomelo.

If it suddenly works, than ServerVersion.AutoDetect(connectionString) is indeed the issue. Otherwise, it is not.

[lauxjpn]

@pantonis Did you try the steps I outlined in my previous post?

[tmricardo7]

@lauxjpn It indeed worked for me, I called the CustomAutoDetect instead.