Entity Framework, Sql server and failing connections

algorythmics

Hi Folks,

I am having a bit of a problem with EF currently that I am struggling to solve, hopefully should be a quick one.

I have a database model generated by EF Database First (EDMX etc) and am then connecting to that using an EntityConnectionStringBuilder so that the connection can be set at runtime rather than through a config file.

My model's context is modified using the partial class here:

namespace DatabaseAccessModel
{
    public partial class Database : DbContext
    {
        public Database(string connectionString) :base(connectionString)
        {
            ((IObjectContextAdapter)this).ObjectContext.CommandTimeout = 10;
            //this.Database.CommandTimeout = 10;
        }

        public static Database ConnectToDatabase(string server, string database, string username, string password)
        {
            var builder = new EntityConnectionStringBuilder();
            builder.Metadata = @"res://*/DatabaseModel.csdl|
                                 res://*/DatabaseModel.ssdl|
                                 res://*/DatabaseModel.msl";
            builder.Provider = "System.Data.SqlClient";
            var providerBuilder = new SqlConnectionStringBuilder();
            providerBuilder.ConnectTimeout = 10;
            providerBuilder.IntegratedSecurity = false;
            providerBuilder.DataSource = server;
            providerBuilder.InitialCatalog = database;
            providerBuilder.UserID = username;
            providerBuilder.Password = password;
            string providerString = providerBuilder.ToString();
            builder.ProviderConnectionString = providerString;

            return new Database(builder.ToString());
        }
    }
}

apologies if the formatting got messed up by discourse. Anyway, the problem I am having is with when the database cannot be found, specifically, the server doesn't exist. It takes about 53 seconds to fail with "underlying provider failed on open", I set the connection and command timeouts in a number of different ways to 10 seconds (as you can see) but it doesn't seem to have any impact on how long it takes for the connection to fail. I am surprised by the time taken because using the shitty oracle provider I was getting connection failures almost instantly so I expected a similar thing here.

Does anyone have any idea what I can do to speed this up? I would prefer to tinker with EF settings rather than manually checking if the server exists, then if the database exists etc etc, but I am happy and able to do either if necessary.

Any thoughts?

Jaime

I think you're screwed; it's likely a DNS resolution timeout, which you cannot control. To test this theory, try with a name that is a valid computer, but not running SQL Server. If it fails quickly, then your problem is a DNS timeout.

Matches

Yeah, that's not helpful.

@op, you set a command timeout, not a connection timeout. A command timeout is for sql queries, connectiontimeout is for the handshake.

If you really do want a command timeout, check this.

Entity Framework Timeouts

I am getting timeouts using the Entity Framework (EF) when using a function import that takes over 30 seconds to complete. I tried the following and have not been able to resolve this issue: I added

Jaime

@Matches said:

Yeah, that's not helpful.

???? How could a likely explanation not be helpful? The application protocol timeout clock doesn't even start until network components like the dns client do their thing. Due to the distributed nature of DNS, it's notorious for having a very long failure timeout.

tarunik

@Matches said:

@op, you set a command timeout, not a connection timeout. A command timeout is for sql queries, connectiontimeout is for the handshake.

Pay more attention -- he sets both, but at different places in the code...

algorythmics

I didnt think the CommandTimeout would have an effect, but I set it anyway just in case the whole lazy loading concept caused it to accept a connection has magically happened when it hasn't, and go straight to the command timeout.

In the connection string builder I am specifying a ConnectTimeout attribute that I was hoping would be honored, which is what I am struggling with.

I tested against a server that exists but without SqlServer and it fails a lot faster. What is the best practice for quickly handling this failure case? Should I be externally pinging the server or something in the first instance? It should be noted that I am connecting based on IP address, so I am a little surprised this is going down the way it is

Matches

Because you apparently didn't actually read my full response,

There is a known bug with specifying default command timeout within the EF connection string.

MySQL Bugs: #56806: Default Command Timeout has no effect in connection string

Remove the value from the connection string and set it on the data context object itself. This will work if you remove the conflicting value from the connection string.

Entity Framework 6:

this.context.Database.CommandTimeout = 180;
Entity Framework 5:

((IObjectContextAdapter)this.context).ObjectContext.CommandTimeout = 180;
Entity Framework 4 and below:

this.context.CommandTimeout = 180;

dkf

@algorythmics said:

pinging the server

That only determines if the server's network interface is up. I've seen machines crash in the state where the network was up — they were pingable — but no user processes at all were running due to init getting in a strange state. You can also easily imagine the case where the machine is up but the server you're calling has ceased to be properly responsive.

Ping's OK for diagnosing network trouble, but it is something to use in addition to making a (simple as possible) service call.

Bort

@algorythmics said:

((IObjectContextAdapter)this).ObjectContext.CommandTimeout = 10;

Is there ever a reason to cast this?

dkf

@Bort said:

Is there ever a reason to cast this?

Yes. There's never a good reason to do it. (Except perhaps in C++.)

Jaime

@dkf said:

That only determines if the server's network interface is up.

Even worse, the ping will do the same name resolution that the connect would have done, so it will take just as long to fail.

Jaime

@Matches said:

Yeah, that's not helpful.

@algorythmics said:

I tested against a server that exists but without SqlServer and it fails a lot faster.

Not only was it helpful, it was right.

algorythmics

providerBuilder.ConnectTimeout = 10;

just to pull out the line that is setting the connection timeout in the connection string, the bit that I was expecting to function correctly. The bug that @Matches linked appears to only affect mysql connection strings and additionally is only looking at command timeouts rather than connection timeouts.

I do need a solution of some kind for a long failure time. I am working with 2 servers where one or both could be offline at any time and I need to easily and quickly be able to fail from one to the other. If I manually ping the server in cmd I predictably get a "host unreachable" response in just a second or 2. As I say I am using IP addresses rather than DNS names if that makes a difference to anyone's interpretation of the problem.

I guess I can attempt to connect to both at the same time in a background thread and then take whichever connection comes back first, but I would still want to be able to report complete server failure slightly faster than 50 seconds after I first request data

dkf

@Jaime said:

Even worse, the ping will do the same name resolution that the connect would have done, so it will take just as long to fail.

Only if name resolution is the root of the timeout problem. If it is, you've got serious problems. Or you need to apply a LART to an appropriate system administrator for fucking around with you, which is sadly sometimes the case round here (when some idiot screws up the networking, an event that seems to be getting more common).

Assuming that it resolves at all, the name/address mapping should be cached for a while (depending on the TTL) and the second lookup should be much faster.

Buddy

Explicitly implemented interface, looks like.

Buddy

Yeah, the EF not timing out thing looks like a mystery. Seems like pinging the servers will be your best bet; obviously it won't catch everything, but if you can fail in two seconds rather than ten in the common case, might as well. I like the idea of sending both requests in parallel and using whichever succeeds first.

riking

I think what you want to do is this:

start two threads
both threads try to connect to their assigned backend and run the query
the first to finish reports its result over some kind of channel
both are cancelled once the master/originator gets a result

algorythmics

the structure has changed and we ended up going with a master/slave operation and just specify both servers in the connection string (FailoverPartner) so I don't need to worry about that. I'll see what happens when one/both servers are offline and if it's not rapid i'll go down that route.

Everything is pretty much what I was planning to do, but I think the last couple of steps are slightly wrong, I'd let both of them reach their natural conclusion so I can report "server 2 offline" or some such to some person, since it needs to be made clear that a server needs attention. Other than that that's pretty much exactly what my plan was.