duplicate or missing member properties

Eli Estes 9 posts 119 karma points

Aug 10, 2016 @ 15:53

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?

Copy Link

Usman Suhail 2 posts 72 karma points

Apr 18, 2017 @ 18:22

Hi Eli,

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

Thanks

Copy Link

Eli Estes 9 posts 119 karma points

Apr 18, 2017 @ 20:42

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.

Copy Link

Usman Suhail 2 posts 72 karma points

Apr 19, 2017 @ 08:29

Thanks Eli. This helps!

Copy Link

progproger 52 posts 130 karma points

Sep 26, 2017 @ 08:07

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

Copy Link

is working on a reply...

Flag this post as spam?

Duplicate or Missing Member Properties