Entity Framework Core Error: An error occurred using the connection to database '' on server 'localhost'

Question

I have made an ASP.NET Core 3.1 Web-based application and want to use MySQL as the database.

I have been following along with some YouTube tutorials on creating MySQL database with ASP.NET Core 3.1 [code first approach] including a tutorial from this site:

https://learn.microsoft.com/en-us/aspnet/core/data/ef-rp/intro?view=aspnetcore-3.1&tabs=visual-studio

I have created a DataModel Class, added a service to UseMySQL to the Startup.cs Class and created an AppDBContext Class that implements DbContext Class.

When I run this command in the Package Manager Console: Add-Migration InitialDatabase the application is creating a migration successfully.

When I run update-database it is throwing this exception:

fail: Microsoft.EntityFrameworkCore.Database.Connection[20004]
      An error occurred using the connection to database '' on server 'localhost'.
An error occurred using the connection to database '' on server 'localhost'.
System.InvalidOperationException: An exception has been raised that is likely due to a transient failure. Consider enabling transient error resiliency by adding 'EnableRetryOnFailure()' to the 'UseMySQL' call.

When I call the EnableRetryOnFailure(); function as required, I am facing this exception:

fail: Microsoft.EntityFrameworkCore.Database.Connection[20004] An error occurred using the connection to database '' on server 'localhost'. An error occurred using the connection to database '' on server 'localhost'.

What could be the issue? Where am I getting it wrong?

If you have links to useful articles about using MySQL Database with ASP.NET Core I would appreciate or your help on this particular issue.

I am using Visual Studio IDE 2019 and ASP.NET Core 3.1.1

Additional Code:

This is the Startup.cs Class:

private IConfiguration _configuration;
public Startup(IConfiguration configuration)
{
    _configuration = configuration;
}

// This method gets called by the runtime. Use this method to add services to the container.
public void ConfigureServices(IServiceCollection services)
{
    services.AddControllersWithViews();

    // database connection string configuration  
    services.AddDbContextPool<AppDBContext>(options => options.UseMySql(_configuration.GetConnectionString("DatabaseConnectionString"),
        mySqlOptionsAction: options => { options.EnableRetryOnFailure(); }
        ));

    services.AddMvc();
}

This is the connection string in appsettings.json:

  "ConnectionStrings": {
    "DatabaseConnectionString": "Server=localhost;Database=MyAppDB;user=root;Password=123;"
  }

The message indicates you have an empty connection string. What does your config file look like? (should be called `...EntityFramework.dll.config` or `web.config` or `App.config`). — lurker, Apr 25 '20 at 15:27
Are you using `MySql.Data.EntityFrameworkCore` or `Pomelo.EntityFrameworkCore.MySql`? — Bradley Grainger, Apr 25 '20 at 15:28
Those are very good questions luker and Bradley. Let me post more code. I am using Pomelo.EntityFrameworkCore.MySql from Nuget — Russell Chidhakwa, Apr 25 '20 at 15:31
@luker: I have edited the question and added more code. I am using appsettings.json to store Database Connection string — Russell Chidhakwa, Apr 25 '20 at 15:41
@Alvan Rahimli: I am yet to try the solutions offered here. Please try the solutions offered here and get back with feedback whether it worked for you. Also see the code by: YankTHEcode, Abdullah and the findings of Dominik. — Russell Chidhakwa, Jun 21 '20 at 08:07
I already solved problem. Some how i was not paying attention to server address. i was writing root@0.0.0.0 instead of just 0.0.0.0 it was dumb typo. By the way, problem occurs when ef can't establish connection. — Alvan Rahimli, Jun 21 '20 at 09:11

score 3 · Answer 1 · answered May 31 '20 at 00:30

3

I think I found the root of the problem. This error also occurs when the connection to the database couldn't have been established. Make sure the information in your connection string is correct. This error message is very misleading, I spent couple of hours figuring this out because of it.

answered May 31 '20 at 00:30

Dominik Brych

31
2

I really appreciate your findings. I will surely try your recommendation and if I get some errors or exceptions I will post. Your explanations make some sense. I appreciate! – Russell Chidhakwa Jun 01 '20 at 03:45

Abdullah Al Mamun Shiham · Answer 2 · 2020-04-27T11:14:44.983

1

You can try this way

Install package Pomelo.EntityFrameworkCore.MySQL
Add services at Startup.cs

services.AddCors(); services.AddDbContext(options => options.UseMySql(Configuration.GetConnectionString("DatabaseConnectionString")));
change connection string at appsettings.json

"ConnectionStrings": { "DatabaseConnectionString": "server=localhost;port=3306;database=MyAppDB;user=root;password=" }

*change the port number according to your MySQL server

4.Run these commads at Package Manager Console for data migration Add-Migration InitialCreate

Update-Database

You can look at the project at github, for better understanding

edited Apr 27 '20 at 11:14

answered Apr 27 '20 at 10:56

Abdullah Al Mamun Shiham

31
2

I will try your approach and I really appreciate your time and response to this question. – Russell Chidhakwa Apr 27 '20 at 12:35
I have tried your approach but still got an exception: fail: Microsoft.EntityFrameworkCore.Database.Connection[20004] An error occurred using the connection to database '' on server 'localhost'. An error occurred using the connection to database '' on server 'localhost'. System.InvalidOperationException: An exception has been raised that is likely due to a transient failure. Consider enabling transient error resiliency by adding 'EnableRetryOnFailure()' to the 'UseMySql' call. – Russell Chidhakwa Apr 30 '20 at 11:55
is your project working with Microsoft SQL Server ? did you try it ? – Abdullah Al Mamun Shiham May 01 '20 at 13:45
Yes is it working with Microsoft SQL Server, I have tried it. However with MySQL I am facing these exceptions and I do not know where the problem is. I have tried to follow YouTube tutorials and examples from Microsoft Docs Website and even tried the implementation from Github as per your advice but for some reasons Visual Studio IDE is throwing these exceptions. I do not prefer Microsoft SQL Server due to its cost. Initially I preferred SQLite to MSSQL but found out that I may face concurrency issues with SQLite. Now I am left with MySQL as the only option, which is not working for me. – Russell Chidhakwa May 02 '20 at 12:03

score 1 · Answer 3 · answered Aug 21 '21 at 13:38

In my case, I just added the database port separately.

changed this from

MySQL": "server=mysqlserver.com:3306;user=db_user;password=db_pass;database=database_name"

to

MySQL": "server=mysqlserver.com;port=3306;user=db_user;password=db_pass;database=database_name"

YankTHEcode · Answer 4 · 2020-04-25T21:21:06.743

0

Your connection string doesn’t seem to be right or EF is not able to pull it. You ll need to check the docs and select the correct project before running the update. Confirm the connection string using this post:

https://www.c-sharpcorner.com/UploadFile/suthish_nair/how-to-generate-or-find-connection-string-from-visual-studio/

You can try this workaround to specify the connection with the command, PS you ll need to update the provider name for MySql:

Update-Database -Verbose 
 -ConnectionString "CONNECTIONSTRING" 
 -ConnectionProviderName "System.Data.SqlClient"
 -StartupProjectName WEBSITE_PROJECT -ProjectName MIGRATION_PROJECT

edited Apr 25 '20 at 21:21

answered Apr 25 '20 at 20:04

YankTHEcode

634
4
8

Let me try your recommendation – Russell Chidhakwa Apr 25 '20 at 20:30
The recommendation has not solved the issue unfortunately. Maybe there is somewhere I am missing it. I am still getting the exception. – Russell Chidhakwa Apr 25 '20 at 20:57
Have you tried using integrated security instead of username and password in your connection string? And then try the above workaround command? I would first ensure that your connection string is correct! – YankTHEcode Apr 25 '20 at 21:10
I have updated my answer with a link. Try to confirm your connection string value following that post. – YankTHEcode Apr 25 '20 at 21:21
Okay. I will try this approach – Russell Chidhakwa Apr 25 '20 at 22:28
I am still getting the exception. When I use integrated security I get an error stating that integrated security is not supported. I wonder if EF Core 3.1 has issues with .NET Core 3 because I tried the demo tutorial from Microsoft and EF Website and still get the exceptions. I appreciate your help this far. – Russell Chidhakwa Apr 26 '20 at 06:26
Can you share your db context file and the migration file that was produced? – YankTHEcode Apr 26 '20 at 15:46
I have decided to use SQLite with EF Core and ASP.NET Core 3.1. I was frustrated with the exceptions. I will post the DbContext file and Migration though. I appreciate – Russell Chidhakwa Apr 27 '20 at 09:36

score 0 · Answer 5 · answered Jan 26 '21 at 19:41

0

I had the same problem, this error occured the me when the connection string was wrong. In my case it was the port (by default 8457). Have you tried specifying it in the connection string?

answered Jan 26 '21 at 19:41

Neonter

1

score 0 · Answer 6 · answered Mar 29 '21 at 21:42

Your steps don't seem to include installing a local instance of MySQL Server, which could be why you cannot find a server... because its not installed!

MSSQL (localdb) is bundled with Visual Studio 2019 (with the Data Modelling and Processing workload) but MySQL still needs to manually installed.

Have you tried installing a supported version of MySQL Server with your EF Provider?

score 0 · Answer 7 · answered Dec 19 '21 at 11:40

I had the same problem. This error occured when a MariaDB and the web app runs on the same host. A hint for a solution you can find here:

Now that your MariaDB server installation is setup to accept connections from remote hosts, we have to add a user that is allowed to connect from something other than 'localhost' (Users in MariaDB are defined as 'user'@'host', so 'chadmaynard'@'localhost' and 'chadmaynard'@'1.1.1.1' (or 'chadmaynard'@'server.domain.local') are different users that can have completely different permissions and/or passwords.

In my case a user mdbuser is allowed to connect from hosts 192.168.178.% according to the table mysql.user. So I added a second user named mdbuser with localhost and the error disappeared:

GRANT ALL PRIVILEGES ON *.* TO 'mdbuser'@'localhost' IDENTIFIED BY 'password' WITH GRANT OPTION;

score 0 · Answer 8 · answered Jan 16 '22 at 17:58

This also happens if you forget to specify the Connection String in case of using SqlServer in NET6:

Wrong:

var conString = builder.Configuration.GetConnectionString("MyDbConnection");
builder.Services.AddDbContext<MyDbContext>(options => options.UseSqlServer());

Correct:

var conString = builder.Configuration.GetConnectionString("MyDbConnection");
builder.Services.AddDbContext<MyDbContext>(options => options.UseSqlServer(conString));

score 0 · Answer 9 · answered Oct 21 '22 at 17:56

0

Suffered same error on IIS 8.5. The connection string was wrong, fixed it but error still showed.

Solution: Recycle the app pool. Changing the .json file doesn't restart de app so you need to do it.

Hope it helps somenone.

answered Oct 21 '22 at 17:56

bsebe

391
3
8

score 0 · Answer 10 · answered Jan 16 '23 at 17:57

0

In my case it was a permission issue with AWS RDS. I had to add an inbound rule in the security group with my IP. I was then able to connect

answered Jan 16 '23 at 17:57

smcg

135
1
8

score 0 · Answer 11 · answered Mar 15 '23 at 01:08

In my case, that issue happened because I was trying to connect from the .NET Web Application running in a Docker Container to the MySQL installed in the machine where the Docker Container is running.

I thought everything was gonna run using localhost, but I realized every container runs in a specific local IP (like 172.17.0.2) and also the local machine has its own local IP (like 172.25.224.1) in the Ethernet Adapter vEthernet (WSL).

When I changed the connection string to use the IP 172.25.224.1 instead of localhost, it worked fine.

Entity Framework Core Error: An error occurred using the connection to database '' on server 'localhost'

11 Answers11

Linked