Question Expired item: Invalid shop ID: After upgrade to 3.1.1

[Home Alone]

I started getting this error during the Item Duration cron job after upgrading to the latest version of the shop. It causes such a huge load spike on the server I had to disable the shop. Any idea what caused it and how to fix it? Every item in the shop generates an Invalid shop ID error message.

 

[Home Alone]

Here's the corresponding mysql query from the slow query log:

# Time: 140102 12:46:53
# User@Host: root[root] @ [10.0.0.3]
# Thread_id: 3753749 Schema: vbulletin_forum QC_hit: No
# Query_time: 9.297703 Lock_time: 0.000117 Rows_sent: 10200 Rows_examined: 135848
SET timestamp=1388684813;
SELECT
user.*,
purchase.*,
shopinventory.*
FROM dbtech_vbshop_purchase AS purchase
LEFT JOIN dbtech_vbshop_item AS item ON (item.itemid = purchase.featureid)
LEFT JOIN dbtech_vbshop_shopinventory AS shopinventory ON (
shopinventory.itemid = item.itemid AND
shopinventory.shopid = purchase.shopid
)
LEFT JOIN user AS user ON(user.userid = purchase.userid)
WHERE purchase.feature = 'item'
AND item.duration > 0
AND (UNIX_TIMESTAMP() >= (purchase.dateline + (item.duration * 86400)))
AND purchase.active = '1';

 

[Fillip H.]

The SQL query is the query that fetches expired items. If you disable the corresponding scheduled task, the query won't run but the expired items feature will not function on your server.

Can you please post a screenshot of the error message? I've never seen anything like it, and I'm not sure if it's formatting that's lost on the copy/paste to the title field.

 

[Home Alone]

I can only find the error message in the Scheduled Task Log. Screenshot is attached. I renamed the cron folder to _cron and re-enabled the shop. Within seconds the load went from 38 to 190. The Scheduled Task Log did not show any tasks running for VBshop. I exported the current installation and attached the product file here.



View attachment product-dbtech_vbshop.xml

 

[Fillip H.]

That is a copy/paste error, the "invalid shop id" bit should be ignored. Everything is working fine. Sorry for the confusion

 

[Home Alone]

What about these slow queries -- can you tell me what it's trying to do?

# Time: 140104 16:09:13
# User@Host: root[root] @ [10.0.0.3]
# Thread_id: 13366832 Schema: vbulletin_forum QC_hit: No
# Query_time: 11.935708 Lock_time: 0.000083 Rows_sent: 5 Rows_examined: 429817
SET timestamp=1388869753;
SELECT userfield.*, usertextfield.*, user.*, IF(displaygroupid=0, user.usergroupid, displaygroupid) AS displaygroupid, (user.credits) AS vbcredits_1, user.credits AS vbcreditsb_1, (vbcreditst_3.points) AS vbcredits_3, vbcreditst_3.points AS vbcreditsb_3 FROM user AS user LEFT JOIN userfield AS userfield ON (user.userid = userfield.userid)
LEFT JOIN usertextfield AS usertextfield ON (usertextfield.userid = user.userid) LEFT JOIN dbtech_vbshop_bank AS vbcreditst_3 ON (user.userid = vbcreditst_3.userid)
ORDER BY vbcredits_3 DESC LIMIT 5;


# Time: 140104 16:09:43
# User@Host: root[root] @ [10.0.0.3]
# Thread_id: 13368799 Schema: vbulletin_forum QC_hit: No
# Query_time: 6.970397 Lock_time: 0.000117 Rows_sent: 5 Rows_examined: 429817
SET timestamp=1388869783;
SELECT userfield.*, usertextfield.*, user.*, IF(displaygroupid=0, user.usergroupid, displaygroupid) AS displaygroupid, (user.credits) AS vbcredits_1, user.credits AS vbcreditsb_1, (vbcreditst_3.points) AS vbcredits_3, vbcreditst_3.points AS vbcreditsb_3 FROM user AS user LEFT JOIN userfield AS userfield ON (user.userid = userfield.userid)
LEFT JOIN usertextfield AS usertextfield ON (usertextfield.userid = user.userid) LEFT JOIN dbtech_vbshop_bank AS vbcreditst_3 ON (user.userid = vbcreditst_3.userid)
ORDER BY vbcredits_3 DESC LIMIT 5;

 

[Fillip H.]

Those queries are fetching user information. It's a default vBulletin query with vBCredits information added.

 

[Home Alone]

Can you tell me what this does?

LEFT JOIN dbtech_vbshop_bank AS vbcreditst_3 ON (user.userid = vbcreditst_3.userid)

 

[Fillip H.]

This indicates the banked points in vBShop are set up as a vBCredits currency. This is not actually supported, as the vBShop bank table is not constructed in such a way that the contents can be used as a currency in vBCredits.

 

[Home Alone]

I thought I was configuring this to use Vbcredits as currency for VBshop. This also looks like the query that determines the richest users, is that correct? I had the top users set to 5. If this is correct, how can I completely disable this query -- I don't want to show the richest users if it's going to take this long to generate the list. What do I need to comment out or remove from the code?

 

[Fillip H.]

Try setting "Number of Richest Users" to 0, see if that works

 

[Home Alone]

In VBcredits configuration I have currency integration from From [DBTech] vBShop using the field dbtech_vbshop_bank.points. Is this supported?

In VBshop under manage currency I have The default vBShop currency set to use the user table and the credits column. The same user table and credits column is setup under VBCredits in the currency integration section.

How should this be setup?

 

[Home Alone]

Try setting "Number of Richest Users" to 0, see if that works

This worked, thanks.

 

[Fillip H.]

In VBcredits configuration I have currency integration from From [DBTech] vBShop using the field dbtech_vbshop_bank.points. Is this supported?

No. Delete that currency from vBCredits, and leave the other currency (user table and credits column) intact.