RequestReduce.SqlServer throws System.Data.SqlClient.SqlException

[dazbradbury]

I'm trialling RequestReduce on my AppHarbor instance, and as I have multiple workers, am using the SqlServer Store to ensure RequestReduce works optimally.

However, the following exceptions seems to be thrown regularly:

System.Data.SqlClient.SqlException: Violation of PRIMARY KEY constraint 'PK__RequestR__E3F4F357084B3915'. Cannot insert duplicate key in object 'dbo.RequestReduceFiles'. The statement has been terminated.

at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean\ breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception,\ Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning() at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand\ cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler,\ TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds,\ RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior,\ RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior,\ RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult\ result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at RequestReduce.SqlServer.ORM.Database.Insert(String tableName, String primaryKeyName,\ Boolean autoIncrement, Object poco) in c:\RequestReduce\RequestReduce.SqlServer\ORM\PetaPoco.cs:line 1327 at RequestReduce.SqlServer.ORM.Database.Insert(Object poco) in c:\RequestReduce\RequestReduce.SqlServer\ORM\PetaPoco.cs:line 1335 at RequestReduce.SqlServer.Repository.Insert(Object poco) in c:\RequestReduce\RequestReduce.SqlServer\Repository.cs:line 58 at RequestReduce.SqlServer.FileRepository.Save(RequestReduceFile requestReduceFile)\ in c:\RequestReduce\RequestReduce.SqlServer\FileRepository.cs:line 60

Any idea why this would be happening?

[mwrock]

Normally, my guess would be that upon initial application startup on an empty RequestReduceFiles table, your incoming traffic is such that several workers are processing new content and the same content at the same time. One will obviously finish first, and its processed content gets aded to the table, then when the next worker adds its content, it gets this error because the PK is based on a hash of the original css/script urls and their content so the same content processed on different workers will produce the same key. What is odd here is that RR catches that exception and then does a lookup and then attempts an update on the same row. any exception thrown in theupdate is thrown as a invalidoperationexception. Are you on the latest version and do these errors continue to throw throughout the life of the app or just after startup?

[dazbradbury]

Hi Mwrock,

I have been deploying fairly frequently, and then flushing RequestReduce post deploy (to ensure css/js is fresh). Hence, I'm not sure how often the errors occur.

However, I can say the errors are definitely not immediately post deploy. At the moment, having deployed at 2:22AM, I have this error thrown subsequently at 08:56AM and 12:56PM.

I am running version 1.8.13 (pulled from NuGet).

I'm also seeing errors in the RequestReduce log:

Key: 21f89df6-e933-41fc-7f2b-190dbf93324d First errored on: 17/04/2012 02:31:23 Last errored on: 17/04/2012 02:31:23 Number: 5 Exception #1: There were errors reducing http://www.openrent.co.uk/Scripts/TimeAgo/jquery.timeago.min.js Exception #2: RequestReduce had problems accessing http://www.openrent.co.uk/Scripts/TimeAgo/jquery.timeago.min.js. Error Message from WebClient is: The remote server returned an error: (404) Not Found. Stack Trace#2: at RequestReduce.Utilities.WebClientWrapper.Download(String url, IEnumerable1 requiredMimeTypes) in c:\RequestReduce\RequestReduce\Utilities\WebClientWrapper.cs:line 85 at RequestReduce.Utilities.WebClientWrapper.Download[T](String url) in c:\RequestReduce\RequestReduce\Utilities\WebClientWrapper.cs:line 35 at RequestReduce.Reducer.JavaScriptReducer.ProcessJavaScript(String url) in c:\RequestReduce\RequestReduce\Reducer\JavaScriptReducer.cs:line 56 at RequestReduce.Reducer.JavaScriptReducer.ProcessResource(Guid key, IEnumerable1 urls) in c:\RequestReduce\RequestReduce\Reducer\JavaScriptReducer.cs:line 32 at RequestReduce.Reducer.HeadResourceReducerBase`1.Process(Guid key, String urls) in c:\RequestReduce\RequestReduce\Reducer\HeadResourceReducerBase.cs:line 37 at RequestReduce.Module.ReducingQueue.ProcessQueuedItem() in c:\RequestReduce\RequestReduce\Module\ReducingQueue.cs:line 162

Exception #3: The remote server returned an error: (404) Not Found.
Stack Trace#3:

at System.Net.HttpWebRequest.GetResponse() at RequestReduce.Utilities.WebClientWrapper.Download(String url, IEnumerable`1 requiredMimeTypes) in c:\RequestReduce\RequestReduce\Utilities\WebClientWrapper.cs:line 62

Where http://www.openrent.co.uk/Scripts/TimeAgo/jquery.timeago.min.js does exist. Is the URL being truncated somewhere? It's being reported as .j, rather than .js?

Regards, Daz.

[mwrock]

Very sorry for the delayed response. Regarding the first exception, the SQLException, I'm wondering if your database is case sensitive. By default, sql server is case insensitive. Case sensitivity might cause this. When a reduced file is saved, RR first attempts an Insert. If that fails as it does in your case, RR then queries for the existing reduction, updates its properties and then updates the database. If the error you are seeing is showing up in the error logs, it means the lookup of the existing reduction is failing. If this is a casing issue, I can certainly force the case to upper or lower case. However, I'm almost certain that comparisons on UniqueIdentifier types in sql server are always case insensitive. Can you verify that your PK is a UniqueIdentifier? If you used the Create Table that came in the nuget package, then the PK certainly should be a Unique Identifier. The only other debugging I can suggest here is to use SQL Profiler and capture the Insert statement that generates this error and then compare its identifier with what is in your database.

The other error regarding the URL truncation is quite odd. Could you reply with the original script tag containing http://www.openrent.co.uk/Scripts/TimeAgo/jquery.timeago.min.js there must be something wrong here in the parsing.

[asavasamuel]

mwrock

Here is an ORM that works with SQL Server https://www.kellermansoftware.com/p-47-net-data-access-layer.aspx