Jump to content

6.4.2 Upgrade Issues - Empty Transaction Logs, System Errors


Recommended Posts

Hi All,

I upgraded my store from v6.2.8 to v6.4.2 a couple days ago.   The upgrade finished without any errors/warnings, and the store appears to be operating OK (I think)...however... I am unable to see any Transaction Logs in the Admin Control Panel.  Zero.  It's blank.  I did use Maintenance > Rebuild > Clear transaction logs PRIOR to the upgrade to 6.4.2.   I wanted a clean log and felt the in-built Maintenance tool would do not harm.

But since I brought the store back online, I have had a handful of new orders (I only sell digital download products) and I have successfully received the payments in PayPal Business.  It appears my customers have also received their emails with the links... BUT... its been really bothering me that I cannot view any transaction log details in Customers > Transaction Logs.

Looking at the ccrt database through phpMyAdmin I do see transaction entries in table CubeCart_transactions.  I presume this is where the Transaction Logs UI in Admin Control Panel is pulling information from.   So question is, if data exists in the table, why is the View not working when I access my store Admin Control Panel?

I think the errors below may be what's causing this.... I don't know how to fix them though.

 

1) In System Error Logs I have these entries...

Today, 12:28 File: [settings.index.inc.php] Line: [276] "SELECT SQL_CALC_FOUND_ROWS id FROM `CubeCart_order_summary` WHERE `custom_oid` <> '' LIMIT 1;" - Unknown column 'id' in 'field list'
Today, 12:26 File: [orders.transactions.inc.php] Line: [87] "SELECT SQL_CALC_FOUND_ROWS DISTINCT `T`.`order_id`, `T`.`time`, `T`.`amount`, `T`.`gateway`, `T`.`trans_id`, `S`.`id`, `S`.`custom_oid`, `S`.`cart_order_id` FROM `CubeCart_transactions` AS `T` INNER JOIN `CubeCart_order_summary` AS `S` ON `T`.`order_id` = `S`.`cart_order_id` ORDER BY `T`.`time` DESC LIMIT 20 OFFSET 0;" - Unknown column 'S.id' in 'field list'
Today, 12:25 File: [settings.index.inc.php] Line: [276] "SELECT SQL_CALC_FOUND_ROWS id FROM `CubeCart_order_summary` WHERE `custom_oid` <> '' LIMIT 1;" - Unknown column 'id' in 'field list'
Today, 12:23 File: [orders.transactions.inc.php] Line: [87] "SELECT SQL_CALC_FOUND_ROWS DISTINCT `T`.`order_id`, `T`.`time`, `T`.`amount`, `T`.`gateway`, `T`.`trans_id`, `S`.`id`, `S`.`custom_oid`, `S`.`cart_order_id` FROM `CubeCart_transactions` AS `T` INNER JOIN `CubeCart_order_summary` AS `S` ON `T`.`order_id` = `S`.`cart_order_id` ORDER BY `T`.`time` DESC LIMIT 20 OFFSET 0;" - Unknown column 'S.id' in 'field list'

 

2) In Maintenance > Database there are some Index related warnings/possible errors that seem to match the System Errors above...

<myusername>_ccrt1.CubeCart_order_summary

CubeCart_order_summary.id has no key set. Expecting PRIMARY.
CubeCart_order_summary.custom_oid has a key type KEY but expecting UNIQUE KEY

 

I'm not savvy at the SQL/database level so I appreciate if anyone has suggestions to fix this problem.   My guess is the issue with my missing Transaction Logs has something to do with this Unknown column S.id that is being expected in the order_summary table.   

 

 

 

Link to post
Share on other sites

Hi @bsmither

I'm either missing something obvious, or not looking at the right place... but I cannot seem to find anything on "S.id" or Column "id" in the Cubecart_order_summary table.  

That said, a couple of new orders came in overnight, and I am seeing Transaction Logs for them in a new tab called 'Transaction Logs' tied to the specific order.  i.e.   I have to click on a specific order to see this new tab and it then shows me the transaction log.   The "Master" Transaction Log link under Customers > Transaction Logs is still blank.  I do not see this 'Transaction Log' sub-tab for orders created prior to 6.4.2 upgrade

NOTE:  When I upgraded to 6.4.2, I also switched my Payment Gateway to 'PayPal Commerce'.  I was previously using PayPal Standard, which has been deactivated.  Could this be related?

A picture is worth a thousand words, so I am attaching a few screen grabs to illustrate my points..

 

 

 

 

CCRT Master Transaction Logs.jpg

CCRT Transaction Logs in Order Details.jpg

Link to post
Share on other sites

Hi @bsmither

I am examining the contents of the database table CubeCart_order_summary, and to be honest I don't see anything out of the ordinary.  I have ~ 3000 orders in here, many of which were created dating back to Cubecart v3, v4 and v5.   I do not see a Column "id", nor anything called "S.id".  I did however see something odd in Column "custom_oid" in the table.  In the phpMyAdmin 'Browse' view... this Column has blanks in the field for several hundreds of orders, and NULL for several hundreds of others (see screenshots below).  

Since there is a Database warning about CubeCart_order_summary.custom_oid has a key type KEY but expecting UNIQUE KEY as I had previously posted, I looked at the table in the 'Structure' view... and am sharing the screenshot below as well.   Does the "custom_oid" index and its values appear correct to you?  I do not use Cubecart's "Custom Cart Order ID" feature.   I came across another thread where you helped someone with similar issue - https://forums.cubecart.com/topic/55254-database-keys-issue/

custom_oid1.png.60db7750009aa05754dabecdcb34f011.png

custom_oid1_NULL.png.600734ff2f27f57a3f3339dd32ab5d2e.png

 

 

custom_oid.thumb.jpg.acf6f60a68f55d51f66c93de28b7d1ff.jpg

Edited by hokivt03
Link to post
Share on other sites

Not having used the custom OID feature will have nothing in the 'custom_oid' column for orders made before CubeCart added this feature, and null for orders made after Cubecart added that feature.

It is necessary that there be a column named 'id'. If there isn't, something went horribly wrong.

The following indexes are as CubeCart requires:

	PRIMARY KEY (`id`),  // Also AUTOINCREMENT specified
	UNIQUE KEY `cart_order_id` (`cart_order_id`),
	UNIQUE KEY `custom_oid` (`custom_oid`),
	KEY `customer_id` (`customer_id`),
	KEY `status` (`status`),
	KEY `email` (`email`),
	KEY `order_date` (`order_date`)

I see from your screen shots above that CubeCart_order_summary has a PRIMARY index on 'cart_order_id' (wrong) as well as an index with keyname="cart_order_id" having Unique="Yes" (correct).

Link to post
Share on other sites

It is necessary that there be a column named 'id'. If there isn't, something went horribly wrong.

Yikes!!  What information is captured in column "id"?  I definitely do not have this column in my table, and can't think of a good reason why.  I rarely poke around at the database level.  The upgrade to 6.4.2 did not throw any warnings/errors, and I only came upon this stuff when reviewing the Maintenance > Database tab after the upgrade.  I guess it could be possible this issue prevailed from before 6.2.8 (my last version) and I just never paid attention to it.   Thankfully, customers have been able to place orders and receive their digital products and monies owed are collected successfully.

Is there a remedy to fixing the missing Column "id"?  I assume once it's created I can add PRIMARY index to "id" and leave "cart_order_id" alone.   Separately, I would probably need to use your suggestions in the other thread i quoted to fix the index warning for custom_oid.

But first... should probably focus on this missing Column "id".   Thoughts on how to fix it?

 

Link to post
Share on other sites

*UPDATE*

1) I was able to fix the issue with the "custom_oid" index using your solution at https://forums.cubecart.com/topic/55254-database-keys-issue/

2) I installed a vanilla "demo" store on my domain into a new subfolder just to check out this Cubecart_order_summary table and view its default Columns

  • I see the Column "id" in my "demo" store, and I see its purpose is to AUTOINCREMENT the Orders as they are placed.  I created a dummy order and it populated "1" under Column "id";  presumably the next order will be 2, then 3 and so forth...
  • The vanilla demo install has 53 Columns in it, as opposed to my Main Store which has 63 Columns and a missing "id" Column.  Maybe the extra columns are from older CC versions and/or were created due to my specific site configurations?

Bottom line... I have no idea how much of an issue this is going to cause me (the missing "id" column), and if there is an easy fix to it.   Seems like I would need to manually create the Column with the defaults (e.g., AUTOINCREMENT, UNIQUE, PRIMARY etc.) and then somehow populate it with ID's starting with my first order?  No idea if that is possible... and how to do it.  Ugh!

 

 

Link to post
Share on other sites

In phpMyAdmin, there will be a way to change the structure of the database (called the "schema") table Cubecart_order_summary.

Add the column 'id' with these parameters: INT 10 NOT-NULL UNSIGNED AUTO_INCREMENT

Then change the target column of the PRIMARY index from 'cart_order_id'  to 'id'.

If CubeCart did the upgrade, then a backup of the database was made. From that backup, you should be able to examine the 'cart_order_id' codes to find the associated 'id' number. If you choose to do so, you can, over time, re-enter the associated number into the 'id' column of the appropriate row.

To be honest, I do not find any CubeCart table that makes a reference to CubeCart_order_summary's 'id' column. All references I found are for the 'cart_order_id' column.

However, that does not mean the 'id' column is not used. Other functions in CubeCart's admin may use the 'id' column to keep track of certain activities.

Link to post
Share on other sites

Thanks!!   I followed your instructions to create the "id" Column, and it worked!  I am now seeing Transaction Logs in the Cubecart Admin Control Panel where previously it was just a blank screen.   So at minimum it does seem that the master Transaction Logs UI in the CC Admin Control Panel are keying off Order "id".   What's interesting is it is only showing logs from Yesterday through now.  None from before that, but I can live with it.

Also, it populated the "id" numbers for every one of my orders since the beginning!  that data must have existed in the table somewhere and it recognized it.   

Appreciate the advice and support you've provided to me.  Thanks again!!

Edited by hokivt03
Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...