Re: duplicate users imported from 5.5

mfreeze · Post by **mfreeze** » Wed Jul 22, 2009 4:09 pm

I have the same problem with the duplicate disabled users. This was not noticed until after the new 7.0 site went production.

I ran your query and most of the users were deleted but I still have 6310 users that are disabled. I tried a simple delete * from users where isapproved = 0 and got the following message:

The DELETE statement conflicted with the REFERENCE constraint "ac_Addresses_ac_BasketShipments_FK1". The conflict occurred in database "ac7_serenecomfort", table "dbo.ac_BasketShipments", column 'AddressId'.

How can I get rid of these duplicates without manually clicking delete 6310 times?

Also, we have found we cannot search the legacy users by last name, first name, etc but if I look in the ac_addresse table, they are there. For example, if I search the administration for users with the last name of mcgrath, I get no results. However if I run a the query select * from dbo.ac_addresses where lastname = 'mcgrath' 15 users are returned.

If I take the email address from the results of that query and search in the admin for usernames matching it, the information is returned.

It looks like some relationship was broken during the import. One item to note is that most of this store's 5.5 users were anonymous users and where they were not the username did not equal the email address.

I am really confused about what happened during the user import.

Post by **Logan Rhodehamel** » Fri Aug 07, 2009 11:44 am

Do these issues remain unsolved?

I would really appreciate help identifying the username search issue. It's been reported enough that I know it's true. I just can't reproduce.

What I really need is a little snippet of the source AC55 export data, including the user record for one of the users that you cannot search through the admin. I need to see what data is included in the AC55 export. I am guessing there is bad data on the AC55 side, but this can be corrected in one of two ways. First, I have produced a script to scrub AC55 stores of bad data prior to an export. Second, the dataport utility should be able to compensate for recognized sources of bad data.

jmestep · Post by **jmestep** » Fri Aug 07, 2009 1:00 pm

Does your scrub of AC5 stores work thru the admin interface or using the cllient utility? Using the old client utility required an odbc connection? We have run into trouble with that when hosters don't allow that connection to the datbase. (I don't think Able hosted sites allow it?)

Post by **Logan Rhodehamel** » Fri Aug 07, 2009 1:03 pm

It's an ASPX script. Please see here: http://bugs.ablecommerce.com/show_bug.cgi?id=8248

The initial script can help you identify and/or fix about seven known data integrity conditions. Naveed identified a new one with gift certificates, and I suspect this user search thing qualifies if I can figure out the source of the problem.

BTW - backup your database before trying the scrub please.

Post by **Logan Rhodehamel** » Fri Aug 07, 2009 1:03 pm

Logan_AbleCommerce wrote:BTW - backup your database before trying the scrub please.

And FYI, I ran the script on the live ablecommerce.com data so I have confidence it works. But I still made a backup first.

sfeher · Post by **sfeher** » Tue Aug 18, 2009 11:36 am

Hi Folks,

We've been dealing with a number of bad import errors and problems due to bad data. I didn't see this thread before we ran our import/export and had to re-construct a lot of data ourselves. Not fun.

Running into a NEW problem now that we didn't see previously.

1.) User account on 7.0.3 store is valid and working.
2.) Email address is now correctly assigned to the user account.
3.) User forgets their password -- so they attempt to recover the password using the password reminder utility on Login.aspx
4.) User enters the CORRECT email address which is presently assigned to their account from Step 2 above.
5.) User gets the following error from Login.aspx: "The given email address is not registered."

Our Current store
PLATFORM: ASP.NET
VERSION: 7.0.3
BUILD: 12458
MSSQL v2008
AC SCHEMA v2000

I can't understand why the email address is IN the account, and yet there is no match on the password reminder.

Lots of users not getting access to their orders..... Need an urgent fix!

Thanks!
Steve

Post by **Logan Rhodehamel** » Tue Aug 18, 2009 1:14 pm

sfeher wrote: 5.) User gets the following error from Login.aspx: "The given email address is not registered."

ac_Users.LoweredEmail contains a lowercase version of the value in ac_Users.Email, correct? If not, then use a quick SQL query to update it:

Code: Select all

UPDATE ac_Users SET LoweredEmail = LOWER(Email)

Also I found a bug in the query. It is not converting the email to lowercase - which could matter if you have a case sensitive database collation?

Post by **Logan Rhodehamel** » Tue Aug 18, 2009 1:43 pm

sfeher wrote:MSSQL v2008
AC SCHEMA v2000

I saw this and thought a new wiki article might be in ordre: http://wiki.ablecommerce.com/index.php/ ... ma_Upgrade

sfeher · Post by **sfeher** » Tue Aug 18, 2009 1:56 pm

Thanks, Logan....

We'll run this and see what we get.

Appreciate (as always) the speedy reply!

Steve