Database user-order errors

Database user-order errors:



As an example:



Look up all orders of customer A and instead you find one order for A and one order for B Note B has no connection with A.



How does this happen?



How can I fix it?



How can I prevent it?

Are you doing your lookup in the Orders->View orders or looking directly at some table using a database tool? DId you use customer or customer email to do the lookup?



Bob

[quote name=‘jobosales’]Are you doing your lookup in the Orders->View orders or looking directly at some table using a database tool? DId you use customer or customer email to do the lookup?



Bob[/QUOTE]





Good questions:



I am looking in the admin area of CS cart not directly in the database.



I am looking first in Users - Customers where I type in the name “A” in a search box. I get the customer name “A” and check “View All Orders”



Most often I get correct order results bur more often than I would like I see a mix of the Customer A and B’s orders or some other incorrect mix such as only B’s orders.



I am trying to see some sort of pattern in the errors but none yet.



I keep hard copies of my orders as a backup.

I am unable to duplicate your problem - when I do a search in users and then “View all orders”, I get a list of only orders belonging to that specific customer. What happens when you search for Customer B and then "view all orders? Do you get a list with customer B’s orders? Are Customer A orders included on this list?



Bob

Usually Customer B will have the reverse problem. So the type of problem is one of incorrect relational links in the database.



This could be some type of database corruption unique to my database.



What should I do now is my question.



One possibly related point is that I discovered the problem while cleaning up duplicate customer accounts (created in when customers forgot about their old accounts)



when I delete the duplicate accounts this seems to clear up the problem with links.



It is an interesting problem and because I have hard copy backups not the end of the world.



But going forward I need to be sure it does not happen as our database grows as manually fixing large databases is not easy.

It’s possible that this is a result of the situation you mentioned. The cscart_orders and cscart_users tables are related by user_id. The customer information is stored in the order table too so it is possible that you have different information displayed on the order than what is stored in the profile. It sounds like this might be a legacy issue from the differing information when the order was placed.



When you say you cleaned up the duplicates, what did you do? Did you change the customer on the existing order? Even so, it seems like the customer information on the order should have been updated when updating the order in the Admin area.



Bob

When I say cleaned up I did two things:


  1. Deleted duplicate customer accounts - when no orders were connected to the account or when there were only one small order.


  2. Updated email addresses in old orders to match the most recent order/email address.



    Sadly when checking at random now I see my database is in worse shape than I thought as I have found more problem accounts searching from users - customers - that are not duplicate accounts.



    So duplicate accounts does not seem to be causing the problem.



    As an example with a random search users - customers I found Customer A had 3 orders from Customer B and none of its own (A’s) hopefully no A orders as there are none.



    I am still trying to figure out how this could have happened and then how to fix it and prevent it.



    I plan to upgrade to 2.15 in the next few days if that does not fix the problem I will ask CS cart to fix it.

I doubt the upgrade is not going to fix any legacy problems. Unless others are experiencing this problem, it sounds like it is specific to your situation.



Changing the email address on the order would have no effect on linking the order back to the correct profile (they are related on user_id, not email address). I am not sure what the consequences of deleting a profile that links to orders are - I would think that it would simply orphan the order. If I were designing the system, I would make it not possible to delete a profile with any orders or gift certificates linked to the profile. I think the best way to do this would have been to edit the order and choose a new user/customer which should have properly related the order to the correct profile. I am not sure why they are coming up cross-linked since the the 'View all orders" searched based on user_id from what I have seen.



In order to fix the problem, you will likely need to replace the user_id on the order with the correct user_id from the profile - your will need to do this in phpMyAdmin or some other database tool. Try that on one order and see if it fixes the problem (although the customer information on the order could still be different than the profile information).



Bob

Changing the email addresses is just an updating of data.



Strangely when I check update email address and hit save sometimes it works and sometimes it gives me a different email address.



I think that this challenge is probably a result of problems when moving my store several years ago to CS cart that I never saw before but I am not sure.



Deleting profiles that are duplicates without useful order information is very helpful.



As an example I had a profile that was incorrectly linked vis view all orders after deleting the profiles the orders did not vanish and either linked correctly or at the least simply showed up with an “order” search.



Thank you for the PHP suggestion but that solution would be too labor intensive. I have hard copy backups of all my important orders for the last 5 years and it would be easier possibly to wipe out my database and start again.



I am looking for a global script based solution - if such a thing exists for corrupted databases…

Deleting a profile with no orders is not a problem, but deleting a profile that has related orders will potentially orphan those orders unless some care is taken to deal with them. This, in fact, explains why after deleting a profile you can still find the orders with an order search (which searches the cscart_orders table - not cscart_users - and displays information from the orders table). The problem then is to either delete the orphaned order or to relate it back to the proper profile. From my perspective, this should be handled proactively in the code by not allowing a profile with any related orders to be deleted - you would still have the option to edit any related orders to assign the correct user and, only then, delete the profile.



Fixing these problems programmatically is not easy unless you can define a relationship which is true 100% of the time (e.g., if the customer billing information in cscart_orders perfectly matches a unique profile - then you could set the user_id in cscart_orders to that specified in cscart_users).



I would never encourage “nuking” the data. Try this: edit one of your orphaned (or incorrect) orders and in step 2 choose the correct customer and save the order. This should relate the order to the proper user profile. It might take some work but you should be able to clean things up over time.



One script which could help here would identify any orphaned orders (e.g., the user_id in the orders table has no match in the users table).



Bob

[quote name=‘jobosales’]…

I would never encourage “nuking” the data. Try this: edit one of your orphaned (or incorrect) orders and in step 2 choose the correct customer and save the order. This should relate the order to the proper user profile. It might take some work but you should be able to clean things up over time.



Bob[/QUOTE]



I am confused about one thing why would you disallow deleting a profile in the code that has related orders if as in my case there is a duplicate account at times?



A big goal for me is to delete duplicate accounts and if there are orders linked to the deleted account I add it to my notes in a custom text filed that I have for each customer page (not order page).



I am not sure yet but maybe my problem is all before a certain date (I hope!)- in which case i am not in such bad shape



I am currently exporting my email addresses to a software program with the goal of starting a newsletter - which I am excited about.



Thats how I found all the errors - while cleaning up my mailing list.