Pomelo.EntityFrameworkCore.MySql: Db Migrate() throws Access denied for user 'root'@'localhost'

@waseemdev Thanks you for taking the time to report this issue! And special thanks to @bgrainger and @roji for your work and input on this issue and its solution.

This is fixed with #916.

CloneWith is now available in 0.61.0. I think the code to use it would be a bit shorter and look like this:

    // Get current connection string.
    string connectionString = relationalOptions.Connection?.ConnectionString ?? ConnectionString;

    // Add master connection specific options.
    var csb = new MySqlConnectionStringBuilder(connectionString) {
        Database = "",
        Pooling = false,
    };

    // Apply modified connection string.
    if (relationalOptions.Connection is null)
    {
        relationalOptions = relationalOptions.WithConnectionString(csb.ConnectionString);
    }
    else
    {
        var connection = relationalOptions.Connection.CloneWith(csb.ConnectionString);
        relationalOptions = relationalOptions.WithConnection(connection);
    }

@bgrainger Yes, that will work!

But if you called it with an already existing connection object, it will retrieve the connection string used for this original connection object from the object itself at the point in time when the temporary connection object needs to be created.

MySqlConnection.Clone was implemented in 0.60.0: https://github.com/mysql-net/MySqlConnector/issues/720

This should copy the original connection string, including authentication information. If you have code that looks like:

var newConnection = new MySqlConnection(existingConnection.ConnectionString);
await newConnection.OpenAsync();

with 0.60.0, you may be able to replace it with:

var newConnection = existingConnection.Clone();
await newConnection.OpenAsync();

Would this fix the problem here?

@lauxjpn yeah, this is indeed an issue. In Npgsql I worked around this by having a CloneWith method on NpgsqlConnection, which accepts a new connection string but keeps authentication information from the clone source (password, SSL/TLS callbacks if relevant). This also allows you to take an existing connection string and change options on it (e.g. make it non-pooling) while conserving auth options, regardless of whether PersistSecurityInfo was set to false.

/cc @bgrainger

The issue

The underlying issue with sharing a single connection and calling MigrateAsync() is, that the methods responsible for checking the databases existence and so on, create their own temporary connection object for their operations (MySqlRelationalConnection.CreateMasterConnection()).

This temporary connection object needs to get a connection string from somewhere to establish its connection. If you called .UseMySql() with a connection string as a parameter, it will reuse that one.

But if you called it with an already existing connection object, it will retrieve the connection string used for this original connection object from the object itself at the point in time when the temporary connection object needs to be created.

If at this point in time, the original connection object has already been used, sensitive connection information like the password will have already been stripped from the connection string of the original connection for security reasons.

As a result, the connection string used for the temporary connection object might not contain all the necessary information anymore and opening the temporary connection might fail.

Also, because a temporary connection object is used internally (and the EF Core code does not seem to take steps here to enlist in any transaction), this object will not be part of your transaction.

Workaround

You can specify PersistSecurityInfo=True in your connection string for your shared/original connection object and keep the password in the string. This will make the MigrateAsync() call work, but it will still not enlist in your transaction.

If you need the migration commands in your transaction, you can upgrade your code to 3.0.0, as we are supporting ambient transactions in 3.0.0, which should work here, I think.

Steps for us to fix

The easiest way to ensure that we reuse a connection string as best as possible, is to read it from the original connection object the moment a MySqlRelationalConnection is created and override the ConnectionString property to instead always return our early read one.

public class MySqlRelationalConnection
{
    public MySqlRelationalConnection(
        [NotNull] RelationalConnectionDependencies dependencies)
        : base(dependencies)
    {
        // Read the connection string now, because if `UseMySql()` is being called with a
        // connection object instead with a connection string, and then later
        // `CreateMasterConnection()` is called after the original connection object has
        // been opened, sensitive information will have been stripped from the connection
        // string by default and the master connnection string might not contain all the
        // necessary information to establish its connection. (#908)
        this.ConnectionString = base.ConnectionString;
    }

    public override string ConnectionString { get; }
}

This ensures, that even without specifying PersistSecurityInfo=True explicitly, a master connection will be created without issues later on, as long as the original connection had not already been opened at the time it was used as a parameter for .UseMySql().

This should be the expected behavior, because using a second/master connection for internal operations is an implementation detail that most users will be unaware about.

Since using a master connection seems to be the default implementation for providers, this issue might concern other providers as well. /cc @AndriySvyryd @roji