Copied to clipboard

Flag this post as spam?

This post will be reported to the moderators as potential spam to be looked at


  • Warren Buckley 2085 posts 4516 karma points MVP 6x admin hq c-trib
    Jul 21, 2015 @ 16:52
    Warren Buckley
    0

    uSync Issue - Duplicate Key in cmsMacroProperty table

    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.

    Any ideas or thoughts that would be fantastic.

    Cheers,
    Warren

  • Kevin Jump 1486 posts 9550 karma points MVP 3x c-trib
    Jul 21, 2015 @ 17:01
    Kevin Jump
    0

    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

  • Warren Buckley 2085 posts 4516 karma points MVP 6x admin hq c-trib
    Jul 21, 2015 @ 17:14
    Warren Buckley
    0

    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 :)

  • Kevin Jump 1486 posts 9550 karma points MVP 3x c-trib
    Jul 21, 2015 @ 17:27
    Kevin Jump
    0

    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.dll from 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.

    DEBUG jumps.umbraco.usync.SyncMacro - [T15/D15] Importing Macro: D:\Development\Web\Umbraco\DevUmbraco7.2.6\uSync\Macro\ezSearch.config
    DEBUG jumps.umbraco.usync.SyncMacro - [T15/D15] Macro Imported
    DEBUG jumps.umbraco.usync.uSync - [T15/D15] Applying updates for macro: ezSearch
    DEBUG jumps.umbraco.usync.SyncMacro - [T15/D15] updating macro property: hideFromSearchField on ezSearch
    DEBUG jumps.umbraco.usync.SyncMacro - [T15/D15] updating macro property: indexType on ezSearch
    DEBUG jumps.umbraco.usync.SyncMacro - [T15/D15] updating macro property: pageSize on ezSearch
    DEBUG jumps.umbraco.usync.SyncMacro - [T15/D15] updating macro property: previewFields on ezSearch
    DEBUG jumps.umbraco.usync.SyncMacro - [T15/D15] updating macro property: previewLength on ezSearch
    DEBUG jumps.umbraco.usync.SyncMacro - [T15/D15] updating macro property: rootContentNodeId on ezSearch
    DEBUG jumps.umbraco.usync.SyncMacro - [T15/D15] updating macro property: rootMediaNodeId on ezSearch
    DEBUG jumps.umbraco.usync.SyncMacro - [T15/D15] updating macro property: searchFields on ezSearch
    DEBUG jumps.umbraco.usync.SyncMacro - [T15/D15] updating macro property: searchFormLocation on ezSearch
    DEBUG jumps.umbraco.usync.SyncMacro - [T15/D15] Saving changes to: ezSearch
    

    be interested to see where it gets to.

  • Warren Buckley 2085 posts 4516 karma points MVP 6x admin hq c-trib
    Jul 21, 2015 @ 17:47
    Warren Buckley
    0

    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?

  • Kevin Jump 1486 posts 9550 karma points MVP 3x c-trib
    Jul 21, 2015 @ 17:50
    Kevin Jump
    0

    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

  • Warren Buckley 2085 posts 4516 karma points MVP 6x admin hq c-trib
    Jul 22, 2015 @ 09:03
    Warren Buckley
    0

    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:

    2015-07-22 09:52:02,345 [22] DEBUG jumps.umbraco.usync.SyncMacro - [T1/D4] Importing Macro: E:\Websites\foo-bar.co.uk\uSync\Macro\Search-Results.config
    2015-07-22 09:52:02,370 [22] INFO  Umbraco.Core.Persistence.UmbracoDatabase - [T1/D4]    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 09:52:02,375 [22] ERROR Umbraco.Core.UmbracoApplicationBase - [T1/D4] An unhandled exception occurred
    System.Data.SqlClient.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.
       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:8dbe9a48-e55e-4439-9909-c0e35b6da737
    Error Number:2601,State:1,Class:14
    2015-07-22 09:52:12,574 [22] INFO  Umbraco.Core.UmbracoApplicationBase - [T7/D4] Application shutdown. Reason: InitializationError
    

    The uSync file in question is as follows:

    <?xml version="1.0" encoding="utf-8"?>
    <macro>
      <name>[Foo] [Bar] Search Results</name>
      <alias>Foo-Bar-Search-Results</alias>
      <scriptType></scriptType>
      <scriptAssembly></scriptAssembly>
      <scriptingFile>~/Views/MacroPartials/CmsPlugins/Foo-Bar/SearchResults.cshtml</scriptingFile>
      <xslt></xslt>
      <useInEditor>False</useInEditor>
      <dontRender>True</dontRender>
      <refreshRate>0</refreshRate>
      <cacheByMember>False</cacheByMember>
      <cacheByPage>False</cacheByPage>
      <properties>
        <property name="rootContentNodeId" alias="rootContentNodeId" sortOrder="0" propertyType="Umbraco.Integer" />
        <property name="rootMediaNodeId" alias="rootMediaNodeId" sortOrder="1" propertyType="Umbraco.Integer" />
        <property name="indexType" alias="indexType" sortOrder="2" propertyType="Umbraco.Textbox" />
        <property name="searchFields" alias="searchFields" sortOrder="3" propertyType="Umbraco.Textbox" />
        <property name="previewFields" alias="previewFields" sortOrder="4" propertyType="Umbraco.Textbox" />
        <property name="previewLength" alias="previewLength" sortOrder="5" propertyType="Umbraco.Integer" />
        <property name="pageSize" alias="pageSize" sortOrder="6" propertyType="Umbraco.Integer" />
        <property name="hideFromSearchField" alias="hideFromSearchField" sortOrder="7" propertyType="Umbraco.Textbox" />
        <property name="searchFormLocation" alias="searchFormLocation" sortOrder="8" propertyType="Umbraco.Textbox" />
      </properties>
    </macro>
    

    Any pointers would be great Kevin.

    Thanks,
    Warren

  • Kevin Jump 1486 posts 9550 karma points MVP 3x c-trib
    Jul 22, 2015 @ 09:29
    Kevin Jump
    0

    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)

    if (macro.Properties.Any(x => x.Alias == propertyAlias)) continue;
    macro.Properties.Add(new MacroProperty(propertyAlias, propertyName, sortOrder, editorAlias));
    

    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).

  • Kevin Jump 1486 posts 9550 karma points MVP 3x c-trib
    Jul 22, 2015 @ 10:02
    Kevin Jump
    0

    The pull request is in for the package service in the core - https://github.com/umbraco/Umbraco-CMS/pull/745

  • Warren Buckley 2085 posts 4516 karma points MVP 6x admin hq c-trib
    Jul 22, 2015 @ 10:24
    Warren Buckley
    0

    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 DB Table

  • Kevin Jump 1486 posts 9550 karma points MVP 3x c-trib
    Jul 22, 2015 @ 10:40
    Kevin Jump
    0

    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

  • Warren Buckley 2085 posts 4516 karma points MVP 6x admin hq c-trib
    Jul 22, 2015 @ 10:46
    Warren Buckley
    0

    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.

  • Kevin Jump 1486 posts 9550 karma points MVP 3x c-trib
    Jul 22, 2015 @ 10:55
    Kevin Jump
    0

    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).

  • Kevin Jump 1486 posts 9550 karma points MVP 3x c-trib
    Jul 22, 2015 @ 11:30
    Kevin Jump
    0

    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

  • Warren Buckley 2085 posts 4516 karma points MVP 6x admin hq c-trib
    Jul 22, 2015 @ 16:11
    Warren Buckley
    0

    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
    

    Thanks for the ongoing help & support Kevin.

    Cheers,
    Warren :)

  • Kevin Jump 1486 posts 9550 karma points MVP 3x c-trib
    Jul 23, 2015 @ 08:55
    Kevin Jump
    0

    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

    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).

  • Warren Buckley 2085 posts 4516 karma points MVP 6x admin hq c-trib
    Jul 23, 2015 @ 09:24
    Warren Buckley
    0

    Thanks for the suggestion Kevin.

    I will carry on playing detective to see what's up and get back to you.

Please Sign in or register to post replies

Write your reply to:

Draft