Kryptronic Software Support Forum

You are viewing this forum as a guest. Login to an existing account, or create a new account, to reply to topics and to create new topics.

#1 09-28-2017 13:23:31

zanart
Member
From: bedford
Registered: 04-02-2008
Posts: 1716

SQL Statement Help Please

Hi Nick

I made a bit of a balls up and have been storing a data:image string(very long) in the order_items customdata table. I did mean to zero it out when order was marked complete, but must have forgot.

Can I reset part of a serialized array to zero via sql statement?
All the fields start with data:image, so I am looking to reset everything between " " to zero where the field starts with data:image


Rob

Offline

 

#2 09-28-2017 13:34:14

webmaster
Administrator
From: York, PA
Registered: 04-20-2001
Posts: 19861
Website

Re: SQL Statement Help Please

Assuming you're using MySQL, access Raw DB Admin, choose to submit a Raw SQL Statement.  Your statement will look something like this:

Code:

UPDATE ecom_orderitems SET customdata=REPLACE(customdata,'FIND','REPLACE')

But that is messy and your data may not be formed in such a way that the find/replace can be effective (too many wildcards).  If I were you, and wanted to do it safely, I'd use a temporary include that attaches to the CORE_DB class, loads the data, unserializes it, cleans it, and then writes it back.


Nick Hendler

Offline

 

#3 09-29-2017 02:46:55

zanart
Member
From: bedford
Registered: 04-02-2008
Posts: 1716

Re: SQL Statement Help Please

Thanks Nick. I will probably go with the php script to run through them.

However, I don't think it is the order_items table that is the minor issue I am trying to resolve.

When I go to main dashboard page in backend, it takes a few seconds to load - nothing major but annoying.
If I remove the core.dashstats from systemdahsboard.php, page loads instantly.

Therefore the delay is due to the sql counts for no of orders, and order totals, etc.

Looking at my ccp8 ecom_orders table, it was 47mb for 125000 rows, K9 table is 69mb for 134000 rows. So around 45% bigger for only 7% more rows. This is probably due to the extra 30 columns.

In phpmyadmin,
ccp8 loadtimes are : Showing rows 0 - 24 (121965 total, Query took 0.0018 sec)
K9 loadtimes are: Showing rows 0 - 24 (134676 total, Query took 0.2670 seconds.)

0.0018 seconds V 0.2670 seconds for 24 rows seems alot??

Should I be concerned about the longer query times?


Rob

Offline

 

#4 09-29-2017 09:51:41

webmaster
Administrator
From: York, PA
Registered: 04-20-2001
Posts: 19861
Website

Re: SQL Statement Help Please

First, when 9.0.2 comes out, update to it.  It's being packaged right now, will probably go live Monday unless I'm feeling overzealous today. 9.0.2 has been specifically tuned for large/busy sites to reduce resource usage, and increase speed significantly.  It is lean and mean and worth the update for this alone, much less all the security enhancements.  9.0.2 has 114 changes in it altogether. 

The following updates were made which would affect resource usage and speed, and these contribute to some of the issues you're reporting here.  I've copied them from the pending update notice:

###

(1) Single system URL implemented.  SSL recommended (Security and SEO), without 'www' prefix recommended (SEO).

(2) All session logic updates as well as user session logic.  These changes better secure the system, make it work better with the change to using a single URL (SSL) for all content, and avoid all issues with Chrome browser as seen over the past few weeks.  At this point session logic is handled by cookies and all form-based and URL-based session tracking has been removed.  User accounts now have dedicated session information stored in the core_users table.  Login and logout is achieved by creating and setting a token in a cookie named 'ktokenuser'.  This cookie is rotated every 15 minutes for security purposes, and is good for the length of the browser session.  Guests are tracked using a cookie named 'ktokensess' which is only created/set when necessary, persists for 30 days and rotates every 1 day to keep it fresh.  Typical guest sessions are created at the point when items are added to the cart or personal information has been submitted. These changes play hand-in-hand with migration to a single-URL format, using SSL and staying ahead of requirements forthcoming from PCI.  These changes also provide better/wider support for modern browsers and decrease security concerns as related to documented session-based exploits.

(9) SSL Always On option removed, now SSL is always on if the installation URL begins with 'https'.

(15) Skin widgets modified to produce links only after the widget is verified to produce content.  This speeds up load times by reducing SQL calls for widgets not being loaded.

(20) Robot simulation previously handled by the App class is now called Guest simulation and is handled by the Session class.

(35) All port recognition functionality was removed with the exception of a single match against the port being used to verify it contains 443 if the URL accessed is supposed to be delivered via SSL.

(36) Modified handling of JavaScript Footer global to append any existing value to the values created for it during JSLib processing.  Ensures any included custom footer code is executed after any required libraries.

(37) Standard (non-user) session storage time is now set to a hard 30 days, with session refresh once every day. 

(38) Older functionality to combat safe_mode, register_globals and magic_quotes has been removed as those insecure features are now considered non-standard and non-default and have been deprecated from PHP 5.3 and higher.

(39) Statistical logging further optimized to provide less of a database hit.

(46) Reduced timeout for remote connections to UPS, USPS and FedEx for rates, tracking and address verification from 25 seconds (default) to 8 seconds.

(83) Optimized all advanced reports on orders to reduce the number of database requests, and overall resource usage, when generating reports.  Extreme speed gains were found for most reports.

###

So, update to 9.0.2 when released.  With that said, there is a pending manual entry for K9 which explains how to leverage database table indexing to significantly increase speed on sites with large catalogs and/or a large number of orders:

Code:

ALTER TABLE ecom_cart ADD INDEX cartid (cartid)
ALTER TABLE ecom_orderitems ADD INDEX orderid (orderid)
ALTER TABLE ecom_orderitems ADD INDEX xinvid (xinvid)
ALTER TABLE ecom_orderitems ADD INDEX epochorder (epochorder)
ALTER TABLE ecom_orders ADD INDEX epochorder (epochorder)
ALTER TABLE ecom_payments ADD INDEX orderid (orderid)
ALTER TABLE ecom_payments ADD INDEX creditid (creditid)
ALTER TABLE ecom_pricemap ADD INDEX xofferid (xofferid)
ALTER TABLE ecom_pricemap ADD INDEX xinvid (xinvid)
ALTER TABLE ecom_pricemap ADD INDEX channelid (channelid)
ALTER TABLE core_users ADD INDEX acctsyspri (acctsyspri)
ALTER TABLE core_users ADD INDEX acctsysalt (acctsysalt)
ALTER TABLE core_users ADD INDEX ktokenuser (ktokenuser)

I suggest you run those statements individually against your database using Raw DB Admin.  The last one will throw an error if you are not on 9.0.2 when you do this.  This will probably speed things up to the point where 9.0.1 is so fast you would have never posted.  But it will get faster with 9.0.2.


Nick Hendler

Offline

 

#5 09-29-2017 10:58:58

zanart
Member
From: bedford
Registered: 04-02-2008
Posts: 1716

Re: SQL Statement Help Please

Definitely quicker.
Good to know you had it in hand, and it wasn't me!


Rob

Offline

 

Board footer