[Resolved] Database Has (Sort Of) Lost Previously Collected Mandatory Data - BUG in v5 Skin

[Daamon 0]

Hi,

Apologies for the seemingly weird title, but that is exactly what has happened... I'll explain - and like all good stories, I'll start at the beginning to give context and probably some useful info.

1) Back in February, I followed the instructions in this thread (started by me). Specifically bsmither's suggestion of adding textboxes under the Option Groups section - these were added as mandatory fields, and it worked great.We were capturing what we needed.

2) Recently, one of the shop admins noticed that some recent shop purchases which should've been capturing this mandatory datadidn't have it recorded. They looked into older orders, and data which had previously been there (in the Option Groups added fields) was now gone.

3) Wierder still, there are a few orders in recent times which are OK! They DO have the data captured!

The only change which has been made in the shop is setting up a timezone to have it correctly aligned to where we are - Australia.

I've had a look in the database, but my understanding (or lack of) of the schema / how the data is stored, and basically no knowledge of SQL didn't reveal anything - hence now posting here for help...

Thanks.

[bsmither 1,340]

My initial suspicions are that the version of CubeCart you may be using has a bug whereby text area entries, that should display in admin, are not being saved back to the database when any part of that order has been edited.

Are you still using CC608 (as per your sig)?

 

[Daamon 0]

Hi, thanks for responding.

Yes, still on CC608. I was inspired by your suspicions and have done some analysis:

- I only looked at orders since I implented the new mandatory fields

- All orders (3 in total) in a status of "Pending" DO have the details captured and stored

- 2 of 5 orders in a status of "Processing" have details, the other 3 don't

- ALL orders in a status of "Order Complete" have the line entries showing in the Order Overview tab (as do all the others), and these are blank - i.e. no mandatory details captured.

So... The weird thing is the issue doesn't appear to be consistent, as per the "Processing" status. Unless there's something at a lower level which differentiates those which do, and those which don't have details captured. Is it just coincidence that all three "Pending" orders have details? There's not enough to be able to have a decent sample set.

Does that help any?

[bsmither 1,340]

"the issue doesn't appear to be consistent, as per the "Processing" status."

Could it be the case where for the three orders at Processing with missing text field option data, that these orders had some sort of minor (and thus, probably completely forgettable) change to the order - but having kept the order at Processing?

(Unfortunately, in admin, Staff Access Log, Admin Activity tab, the activity does not log which order, customer, product, etc was successfully updated.)

So, I am placing blame on the missing text field option entry data on the fact that this is a problem with CC608.

If you are reluctant to upgrade, maybe I can convince you to at least implement the code changes that solved this issue.

[bsmither 1,340]

@Dirty Butter, how do I determine what your contribution to issue #929 was all about?

[Dirty Butter 722]

I'm sure I didn't contribute anything. I did try, thanks to a suggestion from bsandall??? to learn how to merge my custom install with the upgrade via GitHub. I don't have a clue why that shows up on that issue. I assure you I didn't know what I was doing. In fact I gave up, as too many of my edits have to be evaluated, not automatically merged, when upgrading.

[Daamon 0]

@bsmither: I've asked the question of the shop users to see if there were any changes made to specifically those three orders. I'll keep you posted... I've also recommended the upgrade.

Regarding the "can I convince you to at least implement the code changes that solved this issue?" - I'm not sure which changes you're referring to, presumably in the other thread I mentioned but which ones?

This post?

 

[bsmither 1,340]

At least the first edit: from $OPT to $option where the difference is obvious.

[Daamon 0]

@bsmither Re $OPT to $option - I think I may have made those changes, and I'd hope that they would persist and not, somehow, be overwritten. I'll check it out and double check.

Re your earlier question: "Could it be the case where for the three orders at Processing with missing text field option data, that these orders had some sort of minor (and thus, probably completely forgettable) change to the order - but having kept the order at Processing?". Response:

"A couple of the orders had remained stuck in 'Pending' even though the PayPal payment had come through, so I changed them to 'Processing' given that they had proceeded to the next level. After that I went in to change the time zone and when I came back out, the mandatory field data was missing. New orders that have come through have the bike data. The back log of completed orders don't."

Perhaps a new suspect in this mystery...? But how could changing the timezone have had an impact?

[bsmither 1,340]

Changing the timezone (admin, Store Settings, Advanced tab) would not have changed the data of any order.

In admin, Orders, there is initially shown a list of orders, of which you can select those orders via checkbox. With the selected orders, one can change en mass their status. Doing a status change this way, the fault of the form element having the wrong variable is not put in play.

One must actually enter the order's edit screens and click Save.

[Daamon 0]

@bsmither Re $OPT to $option - I've checked and that, as well as your other suggested changes to files, have been made.

We're still on 6.0.8 so, if we upgrade, we'll see how we go.

[Dirty Butter 722]

Quote

Enough technical knowledge to be dangerous, not enough to create / fix stuff... Humble enough to listen and learn.

Following along, not understanding much at all of this thread, but just couldn't resist telling you how much I like your sig!!! It fits me perfectly!! Good luck finding a solution to your dilemma.

[Daamon 0]

@bsmither Hi... The issue reported above has been working fine for a while - without upgrading to the latest version... ...and now it's happened again.

How / where can I see - in the back end somewhere, as opposed to the store admin panel - if this data does still exist and maybe it's a display issue?

On a side note, we tried to upgrade to 6.0.12 at the time that the minimum PHP went to 5.4 and the upgrade stalled. We're now on PHP 5.4 but have a 6.0.12 CubeCart with a 6.0.8 database. Not ideal, I know. That's being looked into too...

[Dirty Butter 722]

As for the 6.0.8 database on a 6.0.12 store - have you tried manually running each sql command from the setup/db/upgrade  for 6.0.9, 6.0.11, and 6.0.12?

[Daamon 0]

2 minutes ago, Dirty Butter said:

As for the 6.0.8 database on a 6.0.12 store - have you tried manually running each sql command from the setup/db/upgrade  for 6.0.9, 6.0.11, and 6.0.12?

Someone else is doing that bit - I've not done it before, they have. So I'm leaving it to them to get us sorted on that front.

[bsmither 1,340]

For the order that you are concerned about, there are three places in the database I would like for you to see and copy out some data.

Depending on the utility you use, it may be easy or may may be not so easy to copy out that data. phpMyAdmin may be able to do this easily. MySQL WorkBench will be easy. MySQL Query Browser, not so easy. I have not used any other apps.

In the table CubeCart_order_summary, find the target order, and examine the contents of the 'basket' column. This column is of type BLOB, and so will not show the contents directly. You will need to have the utility open a BLOB viewer. The contents of this BLOB should start with something that looks like:
a:16:{s:8:"contents";a:1(s:32:"hashhash".....etc etc.
Copy the entire BLOB contents from the viewer and paste into a text editor.

In the table CubeCart_order_inventory, find the items that were sold under the cart_order_id of the target order. Examine the contents of the columns 'product_options' and 'options_array'. These columns are also BLOBs. Copy each contents from the view to the text editor.

Carefully scan through the 'basket' contents and you will eventually come to a section of "options", a section for each item in the order.

The 'product_options' and 'options_array' will be easier to examine.

There will be a fair amount of cross-referencing the option codes to the established product options in other tables, but that isn't necessary right now.

In examining these BLOBs, please report your analysis.

[Daamon 0]

Hi @bsmither - thanks so much for the prompt reply. I'll get onto the digging...

In the meantime, and a complete stroke of luck, we've literally just this minute had an order placed and the required (mandatory) details are being captured AND appearing in the Order Summary View - i.e. working as expected. At present, the order is in a status of Pending. I've already advised my shop manager to track what they do - this is a great chance to try to narrow down what's going on and where / when.

[bsmither 1,340]

A great chance indeed. It would be incredible if you could, if at all possible, begin logging all database queries and http requests (including POSTed data). That, however, is at the server admin level. So, unless you have absolute control over the server hosting your site, this logging may not happen.

[Daamon 0]

17 minutes ago, bsmither said:

A great chance indeed. It would be incredible if you could, if at all possible, begin logging all database queries and http requests (including POSTed data). That, however, is at the server admin level. So, unless you have absolute control over the server hosting your site, this logging may not happen.

Sadly, I don't think we have direct server control... Can I do anything through cPanel?

Edit: Actually, IIRC, you're not familiar with cPanel... Hey ho.

[Daamon 0]

@bsmither Hi. P.S. We're now fully on 6.0.12 - front end and database

Analysis findings:

Order received with expected mandatory data which IS displaying / at Status = Pending:

CubeCart_order_summary / BLOB: Contains required data in the "options" area

CubeCart_order_inventory / BLOB: Both product_options column and options_array column contain the same, required data in its entirety

Order received with mandatory data which now no longer displays / at Status = Order Complete:

CubeCart_order_summary / BLOB: Contains data matching expectations in the "options" area - no other way of verifying what was entered, but it looks like what it should be

CubeCart_order_inventory / BLOB: Both product_options column and options_array column DO NOT contain the expected data

[bsmither 1,340]

Ok, when you say the 'product_options' and 'options_array' columns do not contain the expected data, is there any data?

Probably product options that are not text-based are still the same. But the text-based options are:
* completely gone, no inclusion of {i:## where there was something like it before?, or
* the {i:## part is still there, as well as the s:#: part, but nothing in quotes?

 

[Daamon 0]

There's no data reflective of what has been entered by a customer, though there is "stuff" in the 'product_options' and 'options_array' columns which looks technical. Best explained with an example of 2 entries analysed (comments in [] explain the data):

1) A customer has entered data for their motorbike: Ducati, 1199 Panigale, [year] 2013, [registration] REGO1 - this DOES display in the Admin Panel Order Summary

CubeCart_order_summary / BLOB (shortened and sanitised):

i:55;s:7:"options";a:4:{i:18;a:1:{i:0;s:6:"Ducati";}i:17;a:1:{i:0;s:13:"1199 Panigale";}i:16;a:1:{i:0;s:4:"2013";}i:15;a:1:{i:0;s:5:"REGO1"}}

product_options column:
a:4:{i:102;s:12:"Make: Ducati";i:103;s:21:"Model : 1199 Panigale";i:104;s:26:"Year of Manufacture : 2013";i:105;s:53:"Rego (NOTE: 1 per order, do others separately): REGO1";}

options_array column:
a:4:{i:18;a:1:{i:0;s:6:"Ducati";}i:17;a:1:{i:0;s:13:"1199 Panigale";}i:16;a:1:{i:0;s:4:"2013";}i:15;a:1:{i:0;s:5:"REGO1";}}

2) Another customer has entered data for their motorbike: Honda, VTR1000F, [year] 2000, [registration] REGOx - this DOESN'T display in the Admin Panel Order Summary

CubeCart_order_summary / BLOB (shortened and sanitised):

i:55;s:7:"options";a:4:{i:18;a:1:{i:0;s:5:"Honda";}i:17;a:1:{i:0;s:8:"VTR1000F";}i:16;a:1:{i:0;s:4:"2000";}i:15;a:1:{i:0;s:5:"REGOx";}}

product_options column:
a:4:{i:102;s:6:"Make: ";i:103;s:8:"Model : ";i:104;s:22:"Year of Manufacture : ";i:105;s:48:"Rego (NOTE: 1 per order, do others separately): ";}

options_array column:
a:4:{i:18;a:1:{i:102;s:0:"";}i:17;a:1:{i:103;s:0:"";}i:16;a:1:{i:104;s:0:"";}i:15;a:1:{i:105;s:0:"";}}

The second example sounds like your second description:  "* the {i:## part is still there, as well as the s:#: part, but nothing in quotes? "

[bsmither 1,340]

EDIT (8 Aug 2016): Readers can skip to the end.

Note that the options_array column has, in part:
a:4:{i:18;a:1:{i:0;s:6:"Ducati";}
where the zero in {i:0; is wrong.

==========

I will compare this to the code in CC6012 -- you said you were running the 6012 codebase as of now?

[Daamon 0]

1 minute ago, bsmither said:

I will compare this to the code in CC6012 -- you said you were running the 6012 codebase as of now?

Yes, I've been advised we're on CC6012 entirely. And the order with the details displaying (i.e. expected customer-entered data in the two  CubeCart_order_inventory columns) came in after we'd gone to CC6012.

[bsmither 1,340]

"the order with the details displaying came in after we'd gone to CC6012"

What about the order with the details missing that was mentioned above?