Hello Kevin,
I am having this problem with uSync when the site spins up.
[SqlException (0x80131904): Cannot insert duplicate key row in object 'dbo.cmsMacroProperty' with unique index 'IX_cmsMacroProperty_Alias'. The duplicate key value is (33, rootContentNodeId).
The statement has been terminated.]
jumps.umbraco.usync.uSync.RunSync() +687
jumps.umbraco.usync.uSync.DoOnStart() +118
Umbraco.Core.EnumerableExtensions.ForEach(IEnumerable`1 items, Action`1 action) +148
Umbraco.Core.CoreBootManager.Complete(Action`1 afterComplete) +116
Umbraco.Web.WebBootManager.Complete(Action`1 afterComplete) +95
Umbraco.Core.UmbracoApplicationBase.StartApplication(Object sender, EventArgs e) +243
[HttpException (0x80004005): Cannot insert duplicate key row in object 'dbo.cmsMacroProperty' with unique index 'IX_cmsMacroProperty_Alias'. The duplicate key value is (33, rootContentNodeId).
The statement has been terminated.]
System.Web.HttpApplicationFactory.EnsureAppStartCalledForIntegratedMode(HttpContext context, HttpApplication app) +12601645
System.Web.HttpApplication.RegisterEventSubscriptionsWithIIS(IntPtr appContext, HttpContext context, MethodInfo[] handlers) +175
System.Web.HttpApplication.InitSpecial(HttpApplicationState state, MethodInfo[] handlers, IntPtr appContext, HttpContext context) +304
System.Web.HttpApplicationFactory.GetSpecialApplicationInstance(IntPtr appContext, HttpContext context) +404
System.Web.Hosting.PipelineRuntime.InitializeApplication(IntPtr appContext) +475
[HttpException (0x80004005): Cannot insert duplicate key row in object 'dbo.cmsMacroProperty' with unique index 'IX_cmsMacroProperty_Alias'. The duplicate key value is (33, rootContentNodeId).
The statement has been terminated.]
System.Web.HttpRuntime.FirstRequestInit(HttpContext context) +12618692
System.Web.HttpRuntime.EnsureFirstRequestInit(HttpContext context) +159
System.Web.HttpRuntime.ProcessRequestNotificationPrivate(IIS7WorkerRequest wr, HttpContext context) +12458309
I can get round this temporarily by deleting records in the cmsMacroProperty table but this feels dirty & is obviously not an ideal long term solution every time I trigger or recycle the app pool or rebuild the site.
Hi Kevin,
It is version uSync 2.4.10 & Umbraco 7.2.6
No it is not adding EzSearch but an internal piece of Search ViewPartial Macro with Parameters, I would need to double check its not based on EzSearch, but would this be a problem at all anyway?
No the macro logging isn't on NuGet - just on that version in git for now (there are no other changes between 2.4.10 and this it's just about 5 log lines)
If it turns out there needs to be a fix, I will push that as the next nuGet version.
by default there is loads of Debug logging of other parts of usync, the macro bit was just missing some lines.
yeah it looks like it's failing when uSync calls the package service to do the Import. This is inside the umbraco PackageService,
uSync uses the package service to do the initial import: the package service creates the macro if it doesn't exist and it adds the properties to a macro if they are not present. uSync then takes the results and does the overwriting stuff.
I would guess that the logic around the add new properties inside the package service is confused somehow.
OK, Looking in the package service code: (https://github.com/umbraco/Umbraco-CMS/blob/dev-v7/src/Umbraco.Core/Services/PackagingService.cs#L1222)
The check is meant to protect you from adding something that is already there, but just checking - this just checks for the alias - but if you change the case of the alias, this check is passed - because it no longer matches.
When the save is finally called, SQL is case sensitive for the index and it is throwing the error.
Quick Fix: Change the case of the macro parameter back
Longer Fix: I need to put that in a pull request for the core / remove uSync's dependency of the package service (need to check i can do that, as you can't always access all the properties).
Hi Kevin,
I don't believe we changed the casing of any of the parameters as the DB records in the database match the same alias/casing that is already in the DB, when uSync is trying to re-import this.
yeah and pre 7.3.0 it looks like the Macro class is internal http://issues.umbraco.org/issue/U4-6789) , so there is no way of importing the macros other than calling the package service (or going back to the old legacy api I suppose).
pre v7.3 i can't use the new api to do an import, but the legacy API is still there (mainly wrapping the new one).
calling the old school import (Macro.Import) still throws the error,
so i've written my own import routine that uses the legacy API to create the macro and parameters (doing case checking on the way). using this means i can no longer replicate the issue.
might help, but given what you've gotten in the DB im not sure.
(Also this version might fail on import but not throw the YSOD because i'm catching and logging & not then throwing again)
This version will also probably not work on umbraco 7.3 when it arrives - If this does fix your problem i will need to put some version dependent checking in before releasing it.
Hi Kevin,
Sorry for the delay been juggling 101 things today.
OK I now get a new error importing a macro not its properties into the SQL DB table with this new YSOD exception
[SqlException (0x80131904): Cannot insert duplicate key row in object 'dbo.cmsMacro' with unique index 'IX_cmsMacroPropertyAlias'. The duplicate key value is (CookieAlert).
The statement has been terminated.]
jumps.umbraco.usync.uSync.RunSync() +687
jumps.umbraco.usync.uSync.DoOnStart() +120
Umbraco.Core.EnumerableExtensions.ForEach(IEnumerable`1 items, Action`1 action) +148
Umbraco.Core.CoreBootManager.Complete(Action`1 afterComplete) +116
Umbraco.Web.WebBootManager.Complete(Action`1 afterComplete) +95
Umbraco.Core.UmbracoApplicationBase.StartApplication(Object sender, EventArgs e) +243
And the logs from UmbracoTraceLog.txt file
2015-07-22 17:00:05,775 [6] DEBUG jumps.umbraco.usync.SyncMacro - [T1/D2] Importing Macro: E:\Websites\foo-bar.co.uk\uSync\Macro\Cookie-Alert.config
2015-07-22 17:00:05,833 [6] INFO Umbraco.Core.Persistence.UmbracoDatabase - [T1/D2] at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
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, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteScalar()
at StackExchange.Profiling.Data.ProfiledDbCommand.ExecuteScalar() in c:\Code\github\SamSaffron\MiniProfiler\StackExchange.Profiling\Data\ProfiledDbCommand.cs:line 299
at Umbraco.Core.Persistence.PetaPocoCommandExtensions.<>c__DisplayClassa.<ExecuteScalarWithRetry>b__9()
at Umbraco.Core.Persistence.FaultHandling.RetryPolicy.ExecuteAction[TResult](Func`1 func)
at Umbraco.Core.Persistence.Database.Insert(String tableName, String primaryKeyName, Boolean autoIncrement, Object poco)
2015-07-22 17:00:05,838 [6] ERROR Umbraco.Core.UmbracoApplicationBase - [T1/D2] An unhandled exception occurred
System.Data.SqlClient.SqlException (0x80131904): Cannot insert duplicate key row in object 'dbo.cmsMacro' with unique index 'IX_cmsMacroPropertyAlias'. The duplicate key value is (CookieAlert).
The statement has been terminated.
at jumps.umbraco.usync.uSync.RunSync()
at jumps.umbraco.usync.uSync.DoOnStart()
at Umbraco.Core.EnumerableExtensions.ForEach[TItem](IEnumerable`1 items, Action`1 action)
at Umbraco.Core.CoreBootManager.Complete(Action`1 afterComplete)
at Umbraco.Web.WebBootManager.Complete(Action`1 afterComplete)
at Umbraco.Core.UmbracoApplicationBase.StartApplication(Object sender, EventArgs e)
ClientConnectionId:0f1faa3b-357b-4b33-86ed-d469d2895414
Error Number:2601,State:1,Class:14
2015-07-22 17:00:15,875 [6] DEBUG UmbracoExamine.DataServices.UmbracoLogService - [T10/D2] InternalIndexer indexer initializing, Provider=InternalIndexer, NodeId=-1
2015-07-22 17:00:15,930 [6] DEBUG UmbracoExamine.DataServices.UmbracoLogService - [T10/D2] InternalMemberIndexer indexer initializing, Provider=InternalMemberIndexer, NodeId=-1
2015-07-22 17:00:15,945 [6] DEBUG UmbracoExamine.DataServices.UmbracoLogService - [T10/D2] ExternalIndexer indexer initializing, Provider=ExternalIndexer, NodeId=-1
2015-07-22 17:00:16,172 [6] INFO Umbraco.Core.UmbracoApplicationBase - [T10/D2] Application shutdown. Reason: InitializationError
I can't get that one to work (or in this case not work), and upper/lowercase doesn't appear to be an issue for macros, like it is with properties.
I wonder if you some language setting issues - maybe in the db? tracing through the calls, all your errors are calls by umbraco to create something (macro, parameter) that already exists in the DB.
in the core these calls are all predicated with checks, along the lines of
if (!things.Any(x => x.alias == newthing))
{
// do create
}
but for you the == comparison is consistently failing?
I'm not sure what would cause a does x == y thing to fail when they blatantly do, but languages would be where i would start.
Also Just checking: did the above error (the macro one) occur before or after the parameter adding? and is that parameter adding in the log (i.e did it work, or did it just silently throw an error).
If this was my site, my next step would be to chuck some code in using somewhere to list all the macro's out into the log, that way you could at least see what umbraco thinks it has*, then you can see if anything is odd.
*you know what - thinking about it, just get uSync to do a full export (delete or rename the current uSync folder - it will do a complete export of the site to disk, and log everything, so you can see if that looks right).
uSync Issue - Duplicate Key in cmsMacroProperty table
Hello Kevin,
I am having this problem with uSync when the site spins up.
I can get round this temporarily by deleting records in the
cmsMacroPropertytable but this feels dirty & is obviously not an ideal long term solution every time I trigger or recycle the app pool or rebuild the site.Any ideas or thoughts that would be fantastic.
Cheers,
Warren
Hummm
What version of umbraco 7 is it? & is that usync 2.4 ?
I am guessing that's the ezSearch macro it's importing ?
I'll have a play and see if I can replicate.
Kevin
Hi Kevin,
It is version uSync 2.4.10 & Umbraco 7.2.6
No it is not adding EzSearch but an internal piece of Search ViewPartial Macro with Parameters, I would need to double check its not based on EzSearch, but would this be a problem at all anyway?
Cheers,
Warren :)
Nah the macro shouldn't be a problem, i was just guessing based on the parameter name :)
I can't reproduce on a fresh 7.2.6 install, but i've added some extra logging to the macro import code
if you grab the latest
jumps.umbraco.usync.dllfrom github (https://github.com/KevinJump/jumps.umbraco.usync/tree/v2.2-Umbraco-7/Package/uSync)and change your /config/log4net.config log level to Debug you should get lots more logging of what's happening.
Look for the jumps.umbraco.usync.SyncMacro lines, it should go like.
be interested to see where it gets to.
Thanks Kevin,
Will do that in the morning & get back to you.
Detailed logging is a definite plus for us, as every know & then uSync seems to have a hiccup, so great to see you've added it in.
Is this update on NuGet as well Kevin?
Hi,
No the macro logging isn't on NuGet - just on that version in git for now (there are no other changes between 2.4.10 and this it's just about 5 log lines)
If it turns out there needs to be a fix, I will push that as the next nuGet version.
by default there is loads of Debug logging of other parts of usync, the macro bit was just missing some lines.
Kevin
Morning Kevin,
I have put in the new DLL from your repo, restarted the site and ensured the Log4Net.config was set to DEBUG.
Here is what I can see in the UmbracoTraceLog.txt log file:
The uSync file in question is as follows:
Any pointers would be great Kevin.
Thanks,
Warren
Hi,
TL:DR - It's upper/lowercase issue - it's inside the Umbraco PackageService.
yeah it looks like it's failing when uSync calls the package service to do the Import. This is inside the umbraco PackageService,
uSync uses the package service to do the initial import: the package service creates the macro if it doesn't exist and it adds the properties to a macro if they are not present. uSync then takes the results and does the overwriting stuff.
I would guess that the logic around the add new properties inside the package service is confused somehow.
OK, Looking in the package service code: (https://github.com/umbraco/Umbraco-CMS/blob/dev-v7/src/Umbraco.Core/Services/PackagingService.cs#L1222)
The check is meant to protect you from adding something that is already there, but just checking - this just checks for the alias - but if you change the case of the alias, this check is passed - because it no longer matches.
When the save is finally called, SQL is case sensitive for the index and it is throwing the error.
Quick Fix: Change the case of the macro parameter back
Longer Fix: I need to put that in a pull request for the core / remove uSync's dependency of the package service (need to check i can do that, as you can't always access all the properties).
The pull request is in for the package service in the core - https://github.com/umbraco/Umbraco-CMS/pull/745
Hi Kevin,
I don't believe we changed the casing of any of the parameters as the DB records in the database match the same alias/casing that is already in the DB, when uSync is trying to re-import this.
So I am unsure what to do to resolve this then.
See the screenshot of the database table
ohhh, that's annoying
I can recreate by changing the case, but I can't really see how else this might be happening.
The error is on the index of the alias, and macro id, is there any other 33 entries anywhere in that table?
Kevin
Yep very annoying Kevin.
Indeed these are the only ones with the ID of 33, so I am really unsure what I can do to resolve this.
yeah and pre 7.3.0 it looks like the Macro class is internal http://issues.umbraco.org/issue/U4-6789) , so there is no way of importing the macros other than calling the package service (or going back to the old legacy api I suppose).
Hi,
pre v7.3 i can't use the new api to do an import, but the legacy API is still there (mainly wrapping the new one).
calling the old school import (Macro.Import) still throws the error,
so i've written my own import routine that uses the legacy API to create the macro and parameters (doing case checking on the way). using this means i can no longer replicate the issue.
so the jumps.umbraco.usync.dll in this branch : https://github.com/KevinJump/jumps.umbraco.usync/tree/v2.x-Umbraco-7-Macros/Package/uSync
might help, but given what you've gotten in the DB im not sure.
(Also this version might fail on import but not throw the YSOD because i'm catching and logging & not then throwing again)
This version will also probably not work on umbraco 7.3 when it arrives - If this does fix your problem i will need to put some version dependent checking in before releasing it.
Kevin
Hi Kevin,
Sorry for the delay been juggling 101 things today.
OK I now get a new error importing a macro not its properties into the SQL DB table with this new YSOD exception
And the logs from UmbracoTraceLog.txt file
Thanks for the ongoing help & support Kevin.
Cheers,
Warren :)
Hi,
I can't get that one to work (or in this case not work), and upper/lowercase doesn't appear to be an issue for macros, like it is with properties.
I wonder if you some language setting issues - maybe in the db? tracing through the calls, all your errors are calls by umbraco to create something (macro, parameter) that already exists in the DB.
in the core these calls are all predicated with checks, along the lines of
but for you the == comparison is consistently failing?
I'm not sure what would cause a does x == y thing to fail when they blatantly do, but languages would be where i would start.
Also Just checking: did the above error (the macro one) occur before or after the parameter adding? and is that parameter adding in the log (i.e did it work, or did it just silently throw an error).
If this was my site, my next step would be to chuck some code in using somewhere to list all the macro's out into the log, that way you could at least see what umbraco thinks it has*, then you can see if anything is odd.
*you know what - thinking about it, just get uSync to do a full export (delete or rename the current uSync folder - it will do a complete export of the site to disk, and log everything, so you can see if that looks right).
Thanks for the suggestion Kevin.
I will carry on playing detective to see what's up and get back to you.
is working on a reply...