Latest News: (loading..)
Jack_mcs

Database Optimizer

203 posts in this topic

On 5/11/2017 at 8:53 PM, Jack_mcs said:

Those orders would probably be considered orphaned at this point. The code will delete orders that are attached to a customer that is being deleted. But if the customer for those orders doesn't exist, they won't get deleted. Take a look at one of the orders in admin, copy the email address or last name (something unique) and then go to the customers section and search for it. If it doesn't show a customer then that is what the problem is.

I think I did something similar by accident. First I ran option A, I think, since it was the default and I didn't change it. So it deleted customers but not orders......Then I realized what happened and switched it to Option B, I think, to delete customers and orders, but 34,000 orders remain. How can I now delete all of these old orders?

Share this post


Link to post
Share on other sites

There isn't a way in the current version of this addon to remove orphan orders. That would have to be done via the database. But since orders are spread out over several tables it would not be a simple change.  But short of restoring the customers and address book tables and running it again, that's the only choice I can think of. The latter can cause problems if your customers have changed something in their accounts so it is not a good choice unless you know for sure nothing would have changed.

There are some old addons for this. They may need updating to work with your shop and you should make a backup of the database first but one may work, probably the delete orders by filter.

Share this post


Link to post
Share on other sites

Posted (edited)

Hi Jack,

I need an add-ons like this. Is it compatible with Edge ? I didn't see in your description add-on page.

 

Edited by John-Peter

Share this post


Link to post
Share on other sites

@Jack_mcs Getting the following error when trying to run a Cron job: "mysqli_query() expects parameter 1 to be mysqli, null given in /home/xxx/public_html/admin/includes/functions/database.php on line 53". That line looks like this. Please advise.

$result = mysqli_query($$link, $query) or tep_db_error($query, mysqli_errno($$link), mysqli_error($$link));

 

Share this post


Link to post
Share on other sites

Unfortunately, that code doesn't help in this case since it is just the common database code that gets called many times. If you can run the script manually in admin and it works, then it would seem something is wrong with your cron job or maybe the code being called by it. Though no one else has reported this problem so it almost has to be something specific to your server. What php version is your shop using?

Share this post


Link to post
Share on other sites

We use PHP 5.6.30. The cron job is confirmed to be set up correctly. It calls on the database_optimizer_cron.php file included in your add-on.

Share this post


Link to post
Share on other sites

I have DO running on a site with 5.6 and it works correctly so I don't know what it might be in your case. Please try this. Go to admin->Tools->Database Optimizer. Click in the url box and change database_optimizer.php to database_optimizer_cron.php and press enter. You may get warnings but the script should run. If it doesn't run, look for a fatal error message. If it does run, then I've no idea what the problem might be. It would take hands-on troubleshooting to figure it out.

Share this post


Link to post
Share on other sites

It gives the same error as before but three times followed by SHOW TABLES LIKE 'supertracker' and [TEP STOP]. Does this help to troubleshoot further?

Share this post


Link to post
Share on other sites

There's only one place in the code where that command is used so that helps a little. But the command is valid so I don't know what it is failing. Do you have the Supertracker addon installed? It shouldn't matter either way but it might help identify the issue. To get around this, in the includes/languages/English/database_optimizer.php file (or whichever language you are using), find and remove this code:

$db_query = tep_db_query("SHOW TABLES LIKE 'supertracker'"); 
if (tep_db_num_rows($db_query) && ! isset($_GET['reset'])) { 
    $optionsArray[] =  array('option' => TEXT_DO_SUPERTRACKER, 'post' => (str_replace(" ", "_", TEXT_DO_SUPERTRACKER)), 'explain' => TEXT_EXPLAIN_SUPERTRACKER);
}

If the same failure occurs for the user tracking addon, also remove that section of code immediately following the above.

Share this post


Link to post
Share on other sites

We do not use either of the tracking add-ons. Having removed the code for both, a blank page is returned when calling on database_optimizer_cron.php now.

Share this post


Link to post
Share on other sites

Then you are getting some other error. You can check the error log to see what it is reporting.

Share this post


Link to post
Share on other sites

Our cPanel error log is clean. Is there one built into osC? As for add-ons, I only found this one for v3.

Share this post


Link to post
Share on other sites

I've attempted to install the Queries Debug GUI add-on to troubleshoot this further. I'm seeing a very similar error though.

Warning: mysql_query() expects parameter 2 to be resource, object given in /home/xxx/public_html/includes/functions/database.php on line 53
-
select value from sessions where sesskey = '83a8ba4357f4b3b4fba4ea4c72df7706'
[TEP STOP]

A suggestion I've found is that when mysqli_connect is used for database connection, mysql_ functions should not be used, especially since they are deprecated. However, changing those functions to be in line with mysqli, I'm getting the following errors.

Warning: mysqli_query() expects parameter 1 to be mysqli, string given in /home/xxx/public_html/includes/functions/database.php on line 53
Warning: mysqli_errno() expects exactly 1 parameter, 0 given in /home/xxx/public_html/includes/functions/database.php on line 53
Warning: mysqli_error() expects exactly 1 parameter, 0 given in /home/xxx/public_html/includes/functions/database.php on line 53
-
select value from sessions where sesskey = '83a8ba4357f4b3b4fba4ea4c72df7706'
[TEP STOP]

Can someone please provide insight on this? Many thanks in advance.

Share this post


Link to post
Share on other sites

Hi Jack,

I have a question after looking through this.  You have a statement to delete sessions older than a month.  The code I see is

tep_db_query("delete from sessions where expiry > '" . $secondsSessions . "'");     //clear the sessions table of entries greater than time in the settings

But should it be "<" instead of ">"?  Because, the higher number is more current and the lower number is older. 

Where 1501683562 is Wednesday 2nd August 2017 02:19:22 PM  UTC

And 1495000000 is Wednesday 17th May 2017 05:46:40 AM UTC

Am I wrong in my thought?

Share this post


Link to post
Share on other sites

@imusorka, if you are running from cron, it's entirely possible that your PHP and/or MySQL installations are at a different version than your website's. You should run phpinfo() from cron, and compare it to phpinfo() from a browser. If substantially different, you may need to ask your host what "php" command you need in cron to use the same PHP and MySQL as your website does.

Share this post


Link to post
Share on other sites

Actually, I thought about this some more and you have to convert to a unix time stamp something like this.  I was just playing around with this when you posted.

  $secondsSessions = 30*86400;
  $today_minus_30 = (time()-(int)$secondsSessions); // Unix timestamp 30 days ago
  tep_db_query("delete from sessions where expiry < '" . $today_minus_30 . "'");

 

Share this post


Link to post
Share on other sites

@MrPhil Those are identical. Odd thing is that another cron job for sitemaps works without any issues.

Having tried to install the KissER debug add-on, we also see the following error: "select value from sessions where sesskey =". No other errors though. This is a common theme now then. Puzzled.

Share this post


Link to post
Share on other sites

Posted (edited)

John W Correct again. The sessions part of the code was the first added for this addon and I never revisited it. It is good to have a fresh look at it. Thanks for pointing out the mistake.

Edited by Jack_mcs

Share this post


Link to post
Share on other sites

Note: at least my case if file session used no need sql sessions and Database Optimizer magic

  define('STORE_SESSIONS', 'file');

Configure Session dir at admin site.

Share this post


Link to post
Share on other sites

@Gergely We've always had sessions configured to be stored in /includes/work/ that is protected by an .htaccess file. Are you saying that the Database Optimizer is unnecessary in this case? Not quite sure what you mean. Please clarify.

Share this post


Link to post
Share on other sites

@imusorka

What is your session configuration parameter in catalog/includes/configure.php ?

Share this post


Link to post
Share on other sites

@Gergely Having changed it to file, I now get the following error.

select code, title, symbol_left, symbol_right, decimal_point, thousands_point, decimal_places, value from currencies

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now