Applying EF migrations against mysql

Solarion

Solarion

Honorary Master
Joined
Nov 14, 2012
Messages
28,053
Reaction score
17,806
Hi guys. I have a .NET 5 console application and I'm experimenting with mysql. I am able to read from the mysql tables successfully. I wanted to push things a little and add a new table through code first EF. After running update-database -context MySqlDbContext I get the following error.

Host 'DESKTOP-D44TFS' is not allowed to connect to this MySQL server

which seems to be fairly common and is a security feature of MySQL. After much googling I am yet to find a resolution. Anyone have any ideas on how to resolve this? Would be greatly appreciated.
 
Solarion said:
Yeah same machine.

I am also brand new to mysql so not a clue about it's security requirements!
Click to expand...

Have you tried creating a new user and granting privileges?

Code: 
 CREATE USER 'test_user'@'localhost' IDENTIFIED BY 'password';
 GRANT ALL PRIVILEGES ON *.* TO 'test_user'@'localhost' WITH GRANT OPTION;
 FLUSH PRIVILEGES;

support.infrasightlabs.com

vScope Documentation

Get the help you need for vScope IT discovery and asset management
support.infrasightlabs.com support.infrasightlabs.com
 
prOd said:
Have you tried creating a new user and granting privileges?

Code: 
 CREATE USER 'test_user'@'localhost' IDENTIFIED BY 'password';
 GRANT ALL PRIVILEGES ON *.* TO 'test_user'@'localhost' WITH GRANT OPTION;
 FLUSH PRIVILEGES;

support.infrasightlabs.com

vScope Documentation

Get the help you need for vScope IT discovery and asset management
support.infrasightlabs.com support.infrasightlabs.com
Click to expand...

_kabal_ said:
Specify the server host as 127.0.0.1 instead of it auto assigning the host name
Click to expand...

Both solutions resolved it. Can now apply migrations. Thanks a lot both of you!!
 
Just to add to the above project. I have some weird voodoo goblin mechanics going on with C#.

I have tried two different approaches with this project. One works beautifully but the code looks cluttered. The other way looks a little better but either gives a timeout message and when it doesn't give that message, it gives me datetime overflow for mysql. Any feedback is greatly appreciated. Even if I should just leave it and go with the one that works, I'm happy with that too.

The service I created which is what is behind both different examples is this. I have shortened it to two fictitious tables for privacy reasons but there are actually more than 200 tables:

C#: 
public class DataMigratorService : IDataMigratorService
{
    private IApplicationDbContext _sqlcontext;
    private readonly IMySqlDbContext _mysqlcontext;
    public DataMigratorService(IMySqlDbContext context, IApplicationDbContext sqlcontext)
    {
        _mysqlcontext = context;
        _sqlcontext = sqlcontext;
    }

    public async Task DoDataCopy()
    {
        _mysqlcontext.Employees.AddRange(_sqlcontext.Employees);
        _mysqlcontext.Vehicles.AddRange(_sqlcontext.Vehicles);

        await _mysqlcontext.SaveChangesAsync();
    }
}

Ok so back to MAIN. This way works perfectly but I was just wanting to clean up some of this cluttered, repetitive nature of this code.

C#: 
class Program
{
    public async static Task Main(string[] args)
    {
        var host = CreateHostBuilder(args).Build();
        // var sqlContext = CreateSqlDbContext(args);
        var mysqlContext = CreateMySqlDbContext(args);

        //Delete existing mysql database
        Console.WriteLine("DELETING EXISTING MYSQL DATABASE");
        await mysqlContext.Database.EnsureDeletedAsync();

        //create new mysql database
        Console.WriteLine("CREATING NEW MYSQL DATABASE");
        await mysqlContext.Database.EnsureCreatedAsync();

        //copy data from sql to mysql
        Console.WriteLine("COPYING TABLE DATA FROM SQL TO MYSQL");

        var dataMigratorService = host.Services.GetRequiredService<DataMigratorService>();
        await dataMigratorService.DoDataCopy();

        //Stored proc builder service to be added shortly
    }

    public static ApplicationDbContext CreateSqlDbContext(string[] args)
    {
        var configuration = new ConfigurationBuilder()
            .SetBasePath(AppContext.BaseDirectory)
            .AddJsonFile("appsettings.json", optional: false, reloadOnChange: true)
            .AddJsonFile($"appsettings.{Environment.GetEnvironmentVariable("ASPNETCORE_ENVIRONMENT")}.json", optional: true)
            .AddEnvironmentVariables()
            .Build();

        var connectionString = configuration.GetConnectionString("SQLConnection");

        var optionsBuilder = new DbContextOptionsBuilder<ApplicationDbContext>()
            .UseSqlServer(connectionString);

        return new ApplicationDbContext(optionsBuilder.Options);
    }

    public static MySqlDbContext CreateMySqlDbContext(string[] args)
    {
        var configuration = new ConfigurationBuilder()
            .SetBasePath(AppContext.BaseDirectory)
            .AddJsonFile("appsettings.json", optional: false, reloadOnChange: true)
            .AddJsonFile($"appsettings.{Environment.GetEnvironmentVariable("ASPNETCORE_ENVIRONMENT")}.json", optional: true)
            .AddEnvironmentVariables()
            .Build();

        var connectionString = configuration.GetConnectionString("MySQLConnection");

        var optionsBuilder = new DbContextOptionsBuilder<MySqlDbContext>()
            .UseMySql(connectionString, ServerVersion.AutoDetect(connectionString));

        return new MySqlDbContext(optionsBuilder.Options);
    }

    public static IHostBuilder CreateHostBuilder(string[] args) =>
            Host.CreateDefaultBuilder(args)
                .ConfigureServices((hostContext, services) =>
                {
                    // register your services here.
                    var configuration = new ConfigurationBuilder()
                        .SetBasePath(AppContext.BaseDirectory)
                        .AddJsonFile("appsettings.json", optional: false, reloadOnChange: true)
                        .AddJsonFile($"appsettings.{Environment.GetEnvironmentVariable("ASPNETCORE_ENVIRONMENT")}.json", optional: true)
                        .AddEnvironmentVariables()
                        .Build();

                    services.AddDbContext<ApplicationDbContext>(options =>
                        options.UseSqlServer(
                            configuration.GetConnectionString("SQLConnection"),
                            b => b.MigrationsAssembly(typeof(ApplicationDbContext).Assembly.FullName)));

                    services.AddDbContext<MySqlDbContext>(options =>
                        options.UseMySql(configuration.GetConnectionString("MySQLConnection"), new MySqlServerVersion(new Version())));

                    services.AddScoped<IApplicationDbContext>(provider => provider.GetRequiredService<ApplicationDbContext>());
                    services.AddScoped<IMySqlDbContext>(provider => provider.GetRequiredService<MySqlDbContext>());
                    services.AddTransient<DataMigratorService>();

                });
}

This way I am getting the following errors. The first error is intermittent so every two to three runs it will occur. The other error, a datetime overflow, happens on every run towards the end of the run when copying the data from sql to mysql and with tables with datetime fields and then it bombs out.

C#: 
class Program
{
    public async static Task Main(string[] args)
    {
        var services = new ServiceCollection();

        var configuration = new ConfigurationBuilder()
            .SetBasePath(AppContext.BaseDirectory)
            .AddJsonFile("appsettings.json", optional: false, reloadOnChange: true)
            .AddJsonFile($"appsettings.{Environment.GetEnvironmentVariable("ASPNETCORE_ENVIRONMENT")}.json", optional: true)
            .AddEnvironmentVariables()
            .Build();

        services.AddDbContext<ApplicationDbContext>(options =>
            options.UseSqlServer(
                configuration.GetConnectionString("SQLConnection"),
                b => b.MigrationsAssembly(typeof(ApplicationDbContext).Assembly.FullName)));

        services.AddDbContext<MySqlDbContext>(options =>
            options.UseMySql(configuration.GetConnectionString("MySQLConnection"), new MySqlServerVersion(new Version())));

        services.AddScoped<IApplicationDbContext>(provider => provider.GetRequiredService<ApplicationDbContext>());
        services.AddScoped<IMySqlDbContext>(provider => provider.GetRequiredService<MySqlDbContext>());
        services.AddTransient<DataMigratorService>();

        var serviceProvider = services.BuildServiceProvider();

        //Delete existing mysql database
        Console.WriteLine("DELETING EXISTING MYSQL DATABASE");

        var mysqlContext = serviceProvider.GetService<MySqlDbContext>();
        var sqlContext = serviceProvider.GetService<ApplicationDbContext>();

        await mysqlContext.Database.EnsureDeletedAsync();

        //create new mysql database
        Console.WriteLine("CREATING NEW MYSQL DATABASE");
        await mysqlContext.Database.EnsureCreatedAsync();

        //copy data from sql to mysql
        Console.WriteLine("COPYING TABLE DATA FROM SQL TO MYSQL");

        var dataMigratorService = serviceProvider.GetService<DataMigratorService>();
        await dataMigratorService.DoDataCopy(mysqlContext, sqlContext);

        Console.WriteLine("COMPLETED");
    }
}

Error:
Microsoft.EntityFrameworkCore.DbUpdateException: 'An error occurred while saving the entity changes. See the inner exception for details.'

Inner Exception:
MySqlException: Datetime function: datetime field overflow
Click to expand...
 
Last edited:
I suspect this has something to do with not using IHostBuilder in the first example but not entirely sure about that.
 
Seems all very ceremonial for something that EF core does automatically.

Not sure the point of DataMigratorService

docs.microsoft.com

Data Seeding - EF Core

Using data seeding to populate a database with an initial set of data using Entity Framework Core
docs.microsoft.com


And what advantages do you get from:
Code: 
services.AddScoped<IApplicationDbContext>(provider => provider.GetRequiredService<ApplicationDbContext>());
services.AddScoped<IMySqlDbContext>(provider => provider.GetRequiredService<MySqlDbContext>());
 
You must log in or register to reply here.
Back
Top
Sign up to the MyBroadband newsletter
X