VERY odd constraint errors ... | Tech Off

Dr Herbie
Horses for courses

Hi, this is a weird one ....

We have a large, live system that has been running fine for several years. We use remoting servers for the middle-tier, and they mainly do a lot of SQL Server access. We use typed datasets and ADO.NET data adapters to load data.

Our client uses a cluster of virtual machines to host the remoting servers.

Recently, a few weeks after we updated their live code and after a few weeks of apparently successful running, users started reporting constraint errors. The constraints appear when loading existing data from the database.

From our error logs it looks like NULLs are being returned for some non-null columns. The columns are non-null in the SQL Server schema and non-null in our typed datasets.

When loading data we generally turn off constraints in the dataset while we load and set them back on after the data is loaded -- this is where the constraint exceptions occur.

It is happening on several tables (the 'core' tables and thus the most frequently queried).

I have difficulty seeing how a field from the database with a non-null constraint can be read by ADO.NET as NULL.

Our error log also shows that some of the errors are NOT constraint errors, but invalid casts (as you get when accessing a null field through the typed dataset). This means that the NULL value in the non-null field is getting past constraint checking?

Even weirder, if the middle-tier remoting server is rebooted, the problem goes away (for a while, at least).

Anyone got any clues/suggestions/answers?

Herbie

spivonious

Since it goes away for a while after a reboot, are you doing any sort of caching? Maybe the cache is getting corrupted somehow and setting some columns to null.

Dr Herbie
Horses for courses

In reply to spivonious

#Aug 21st @ 9:59 AM

No deliberate caching -- all data reads are direct from the DB.

We do cache connections (connection pooling) which we clear if we get a timeout error. It is one avenue of investigation that we're looking at.

Herbie

figuerres
???

Well one thing that comes to mind is possibly / maby some values are declared and then later not assigned due to a hidden failure?

simple example:

var xyz;

Try{

xyz = 123.45M;

}catch(....){

xyz = null;

}

....

SendToDB( xyz);

something like that... could be from a case/switch or an if/else or a for loop etc....

you might try setting up a dummy table to just dump the data into along with the date-time and some key to id what process or server wrote the data.

the look for nulls and then trace back to when and who.

that might be a step to help identify the issue.

also possibly when the error happens have that app dump some data to a dummy table such that you can review what it got

like dump the source data and the data it was trying to push up so you can (if the problem is in that app or in the data) nail down the conditions for it happening.

Dr Herbie
Horses for courses

In reply to figuerres

#Aug 21st @ 11:14 AM

That was our train of thought too, but it must be someting within the ADO.NET code as our code goes somthing like:


TypedData myDataset = new TypedData(); 

myDataset.EnforceConstraints = false; 

 

try

 {

    SpecificDataAdapter adapter = new SpecificDataAdapter(); 

    adapter.Fill(myDataSet); 

    myDataSet.EnforceConstraints = true; /// This is where the exception is thrown 

} 

catch(Exception ex) 

{

    Logger.LogException(ex); throw; 

}

The typed dataset has a boolean field which has AllowDBNull set to false and is reading from a SQL schema where the field is defined as bit not null.

Works immediately after a server reboot, but once it starts to go wrong, it goes wrong pretty much every time.

Herbie

ZippyV
Fired Up

In reply to Dr Herbie

#Aug 21st @ 12:44 PM

Why do you set the EnforceConstraints to false before filling it?

Dr Herbie
Horses for courses

In reply to ZippyV

#Aug 21st @ 5:52 PM

I simplified the example a little -- we usually load several tables at once, some of which are interdependent, both having foerign keys into each other (which I hate but we can't change).

Herbie

joechung

Did you apply any service packs or hotfixes to your database servers recently? Maybe something about your environment has changed recently to cause these sorts of issues.