Copied to clipboard

Flag this post as spam?

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


  • Eli Estes 5 posts 95 karma points
    Aug 10, 2016 @ 15:53
    Eli Estes
    0

    Duplicate or Missing Member Properties

    We've been having an issue where the system is sporadically creating a new set of blank member properties. In the Umbraco back office, it looks like they've just been wiped out, but when I check the database I can see that the old properties are still there alongside the new blank ones.

    We've had this issue sporadically on both Umbraco 6.2.1 and now Umbraco 7.4.3. We're using SQL Server Express 2012 as our database.

    This is a screenshot of the data showing a member with duplicate properties

    This is the query I ran to get the results above (I removed columns with discretionary member data):

    select pd.id AS PropertyDataID,
        m.nodeId AS UmbracoUserID,
        pt.Alias as PropertyTypeAlias,
        dt.dbType as DataType,
        pd.dataInt as IntData,
        pd.dataNtext as TextData,
        pd.dataNvarchar as VarCharData,
        pd.dataDate as DateData,
        pd.versionId
    from cmsPropertyData as pd
    inner join cmsPropertyType as pt on pd.propertytypeid = pt.id
    inner join cmsDataType as dt on pt.dataTypeId = dt.nodeId
    inner join cmsMember as m on m.nodeId = pd.contentNodeId
    where m.nodeId = 20286
        and pt.Alias not in ('accountNumber','city','companyName','emailVerifyGUID','firstName','hostNameOfLastLogin','iPOfLastLogin','lastName','mobilePhone','phoneNumber','state','title','zip')
    order by PropertyTypeAlias, pd.id
    

    I think this might be related to an error log entry about an open DataReader (I posted details about that here), but that may just be a coincidence. Anyway, has anyone else seen this or had issues with member properties appearing to get cleared out?

  • Usman Suhail 2 posts 72 karma points
    Apr 18, 2017 @ 18:22
    Usman Suhail
    0

    Hi Eli,

    Just curious if you found a resolution to your problem? I am facing something similar on Umbraco version : 7.2.8

    Thanks

  • Eli Estes 5 posts 95 karma points
    Apr 18, 2017 @ 20:42
    Eli Estes
    0

    Usman Suhail, we haven't had this issue repeat for some time. I'm not sure if it's fixed or if it just hasn't happened recently. Here's all of the things we've changed since I posted this:

    • Changed membership provider. Now we use an SQL membership provider instead of the Umbraco provider.
    • We've updated our Umbraco from 7.4.3 to 7.5.8
    • To remedy the DataReader issue I mentioned, we replaced references to the singleton database handle ApplicationContext.Current.DatabaseContext.Database with the thread specific ApplicationContext.DatabaseContext.Database inherited from the UmbracoController. For some reason, certain database queries were having concurrency issues when using the singleton instance.
  • Usman Suhail 2 posts 72 karma points
    Apr 19, 2017 @ 08:29
    Usman Suhail
    0

    Thanks Eli. This helps!

  • progproger 49 posts 106 karma points
    Sep 26, 2017 @ 08:07
    progproger
    0

    I wrote a script which removes duplicated properties for member from cmsPropertyData table. Maybe it will help someone. See below:

    DELETE c
    FROM cmsPropertyData AS c
    WHERE ID IN
        ( SELECT d.id AS propertyId
         FROM dbo.cmsMemberType AS mt
         INNER JOIN dbo.cmsPropertyType AS p ON mt.propertytypeId = p.id
         INNER JOIN dbo.cmsDataType AS dt ON p.dataTypeId = dt.nodeId
         INNER JOIN dbo.cmsPropertyData AS d ON p.id = d.propertytypeid
         INNER JOIN dbo.cmsMember AS m ON m.nodeId = d.contentNodeId
         INNER JOIN dbo.umbracoNode AS u ON u.id = m.nodeId
         WHERE (p.id IN (***,***,***))--include all property id's you need 
           AND (id NOT IN (    
           SELECT MIN(d.id)
                   FROM dbo.cmsMemberType AS mt
                   INNER JOIN dbo.cmsPropertyType AS p ON mt.propertytypeId = p.id
                   INNER JOIN dbo.cmsDataType AS dt ON p.dataTypeId = dt.nodeId
                   INNER JOIN dbo.cmsPropertyData AS d ON p.id = d.propertytypeid
                   INNER JOIN dbo.cmsMember AS m ON m.nodeId = d.contentNodeId
                   INNER JOIN dbo.umbracoNode AS u ON u.id = m.nodeId
                   GROUP BY mt.pk,
                            d.contentNodeId,
                            m.LoginName,
                            p.Alias
                   HAVING COUNT(p.Alias) > 1
           ))
           AND (m.LoginName IN
                  (SELECT DISTINCT(m.LoginName)
                   FROM dbo.cmsMemberType AS mt
                   INNER JOIN dbo.cmsPropertyType AS p ON mt.propertytypeId = p.id
                   INNER JOIN dbo.cmsDataType AS dt ON p.dataTypeId = dt.nodeId
                   INNER JOIN dbo.cmsPropertyData AS d ON p.id = d.propertytypeid
                   INNER JOIN dbo.cmsMember AS m ON m.nodeId = d.contentNodeId
                   INNER JOIN dbo.umbracoNode AS u ON u.id = m.nodeId
                   GROUP BY mt.pk,
                            d.contentNodeId,
                            m.LoginName,
                            p.Alias
                   HAVING COUNT(p.Alias) > 1
                   )) )
    
Please Sign in or register to post replies

Write your reply to:

Draft