CODEWITHSUNDEEP
c#entity-frameworktimeoutentity-framework-6
dr11
dr11

Reputation: 5726

"Update-Database" command fails with TimeOut exception

I'm using EF migrations and have a table with a lot of data. I need to change MaxLength of a concrete column (it hadn't length constraints).

ALTER TABLE MyDb ALTER COLUMN [MyColumn] [nvarchar](2) NULL

And this command fails with TimeOut exception. Tried to setup CommandTimeout i nDbContext constructor without any luck.

Is there any way to disable or setup timeout for Package Manager Console EF commands?

Upvotes: 31

Views: 14852

Answers (8)

reza.cse08
reza.cse08

Reputation: 6178

I have also faced the same problem, but it was fixed by using CommandTimeout=9024

"DefaultConnection": "server=localhost;port=5432;database=DB_NAME;userid=USER_NAME;password=PASSWORD;CommandTimeout=9024;"

here is the output of update-database

enter image description here

Upvotes: 0

Adrian
Adrian

Reputation: 3438

@deeptowncitizen's answer is definitely an easy way to accomplish this. I wanted to offer another simple way as well.

If you have your migration host setup (E.G. what is known as the -StartupProject in the Update-Database command. You can setup your DI registration as follows

services.AddDbContext<MarketDataContext, MarketDataContext>(opts =>
            {
                opts
                    .UseSqlServer(dbConn, a => 
                        a.MigrationsAssembly("xxxxxx.Database.MarketData")
                            .CommandTimeout(10000)) //set the timeout here
                    .EnableDetailedErrors()
                    .EnableSensitiveDataLogging();
            });

This works great in my situation because this context is nugetized and I don't want consumers to automatically have a command timeout of 10000. I only want this ridiculously high timeout to take effect during my build / release pipeline

Upvotes: 1

steve&#39;s games
steve&#39;s games

Reputation: 111

Ran into this same Update-Database command timeout issue with EntityFrameworkCore. Found that the -script argument no longer exists in EFCore. Documentation says you need to use Script-Migration.

EFCore Script-Migration

You specify the last migration that was applied to your database and it will generate a script for everything after it to get it current:

Script-Migration 20210307058985_addIndexesToClientTable

You can get the full migration id from your migration file names in your .net project, just take the .cs off the end. Alternatively, you can get it from your __EFMigrationsHistory table in your database.

If you need a script for creating a new DB it says to use 0:

Script-Migration 0 InitialCreate

For me it opened a temp .sql file in VS since I ran it from the package console manager and I was able to then copy and execute it in MSSQL Management Studio.

8/20/21 Update

for some reason my new migrations are requiring me to provide the optional -From and -To switches in order for it to generate a script:

Script-Migration -From 20210307058985_addIndexesToClientTable -To 20210820205751_modifyIndexesOnClientTable

Upvotes: 5

Rostislav
Rostislav

Reputation: 165

Just add in ConnectionStrings "Command Timeout=120" for MySql 120sec for example

Upvotes: 0

Stuart Aitken
Stuart Aitken

Reputation: 1012

In my case the issue was caused by a very large query which timed out in EF but was able to complete in SSMS.

The answer which suggests Update-Database -script did not work for me, it gave another error message.

For me, I did the following:

  • Open SSMS
  • Tools > SQL Server Profiler
  • Go back to VS and run Update-Database
  • Watch the Server Profiler
  • You should be able to see the query it times out on
  • Copy that query and run it in SSMS
  • Now re-run Update-Database and that slower part should be fine because the query has already been completed.

Disclaimer: This might not work for all cases, because it depends on what particular query is slowing you down. For me, it worked.

Upvotes: 0

techturtle
techturtle

Reputation: 2587

I just had almost the exact same thing: timeout expired when trying to increase a column length. For me, using update-database had been working just fine an hour ago. The problem turned out to be an open transaction on the database and table I was trying to alter. Once I rolled back that transaction, the update-database command went through without problems.

Upvotes: 3

Slavvy
Slavvy

Reputation: 561

Alternatively script out the change by using 

Update-Database -script

You can then take the script and run it using SQL Management Studio against the database.

Upvotes: 36

dr11
dr11

Reputation: 5726

Found solution by myself.

Since EF5 there is a new property CommandTimeout which is available from DbMigrationsConfiguration

internal sealed class MyMigrationConfiguration : DbMigrationsConfiguration<MyDbContext>
{
    public Configuration()
    {
        CommandTimeout = 10000; // migration timeout
    }
}

Upvotes: 36

Related Questions