Jump to content
Jack_mcs

Database Optimizer

Recommended Posts

A new version has been uploaded with these changes:

  • Added option Remove Orphan Orders.
  • Added the latest Version Checker files.
  • Corrected spelling mistake in the modules file - found by member Cary.
  • Corrected mistake in the remove old customers section that would prevent some customers from being deleted.
  • Fixed problems in Truncate Customers Orders.
  • Fixed a mistake in the common file when deleting sessions. (found by member John W).
  • Fixed several minor html coding mistakes. (found by member Demitry).

Share this post


Link to post
Share on other sites

@Jack_mcs,

checked out the new version and all seems good.

There is only one small update missing for compatibility with latest 2.3.4 BS community version:

In database_optimizer.php should be:

  require('includes/template_bottom.php');
  require('includes/application_bottom.php');

 

Share this post


Link to post
Share on other sites

@@Jack_mcs

Following my post on July 1:

Excuse me for the delay, here my follow-up. Just installed yesterday. Your add-on work very well. Thank you for your work :-)

 


John

--------------------

osCommerce 2.3.4 Bootstrap Edge

Share this post


Link to post
Share on other sites

@Jack_mcs Seeing the following error when the cron job is run.

[09-Oct-2018 05:00:01 UTC] PHP Warning:  mysqli_error() expects parameter 1 to be mysqli, null given in /home/xxx/public_html/izbushka/includes/functions/database.php on line 55

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

The following is printed in the confirmation email.

Content-type: text/html; charset=UTF-8
<font color="#000000"><strong> - <br /><br />SHOW TABLES LIKE 'supertracker'<br /><br /><small><font color="#ff0000">[TEP STOP]</font></small><br /><br /></strong></font>

Could you please help fixing this?

Share this post


Link to post
Share on other sites

What version of oscommerce are you using? What php version? Does the script run properly via Tools?

Share this post


Link to post
Share on other sites

Latest CE on PHP 7.2 and the script does run via tools but additionally throws the following 2 errors.

[09-Oct-2018 20:09:41 UTC] PHP Warning:  Use of undefined constant DATABASE_OPTIMIZER_OPTIMIZE - assumed 'DATABASE_OPTIMIZER_OPTIMIZE' (this will throw an Error in a future version of PHP) in /home/langbrid/public_html/izbushka/includes/modules/database_optimizer.php on line 25
[09-Oct-2018 20:09:41 UTC] PHP Warning:  Use of undefined constant MYSQL_ASSOC - assumed 'MYSQL_ASSOC' (this will throw an Error in a future version of PHP) in /home/langbrid/public_html/izbushka/includes/modules/database_optimizer.php on line 42

 

Share this post


Link to post
Share on other sites

I haven't test it under 7.2 so some changes may be needed for that. But in database_optimizer_cron.php, removing this line may allow it to run:

require('includes/filenames.php')

You can probably remove this one too since I don't think it is used but it won't cause a failure if left in:

require('includes/database_tables.php');

 

Share this post


Link to post
Share on other sites

Removed the first line right after the installation as it threw a different error. Removing the second line doesn't change anything.

Given that the cron job worked previously when we were on PHP 5.6 it is probably safe to say that the current issue is related to PHP 7.2.

Share this post


Link to post
Share on other sites

Yes, the problem is definitely related to the php version. I think it will run under 7.0 if you are able to reduce the php version.

Share this post


Link to post
Share on other sites

Yes, it will be upgraded at some point. But addons are managed in my, very sparse, spare time and since the holiday season is fast approaching, I doubt that it will be done before next year.

Share this post


Link to post
Share on other sites

@Jack_mcs just wanted to make sure I understand the wording in config for Truncates Customers Old:

Should older entries in the customers and address book tables be removed? Enter the number of days between removals or leave blank for no removal. (Value entered must be in days)”

Does this mean the process will run every (example) 300 days? And if so where the config to tell it who to truncate?

Or customers who haven’t logged in in 300 days will be truncated?

Share this post


Link to post
Share on other sites

It refers to those that haven't logged in in 300 days. The script itself is ran via cron (usually once a week is fine). On every run, the accounts are checked against the setting and if 300 days have passed, in this example, they will be removed.

Share this post


Link to post
Share on other sites

Perfect, thank you....

Next version, maybe add an option to cleanup/clear-out the Action Recorder.... Mine had over 30,000 rows.... I cleaned it up with a simply SQL and 90 days is only 1,700 rows.

Edited by greasemonkey

Share this post


Link to post
Share on other sites

Applause, Applause for @Jack_mcs

Occasionally, I will download and install something and be crazy impressed...and this is one of those times!

Database Optimizer is truly awesome!

My sessions table alone had nearly 280,000 entries in it and this tool cleaned it up!  I recovered nearly 200 MB of space in my database. 

Super easy to install and use...Thank You very much, Jack, for this terrific contribution!

- Andrea

Share this post


Link to post
Share on other sites

@Jack_mcs

hi Jack,

I did a site search to find all instances of mysql_ to update them to mysqli_ where possible. I found 2 instances of mysql_error() in the following file, which I believe should be mysqli_error(). Though I don't know if it makes any difference. This is for BS Edge on PHP 7.2

/admin/includes/modules/database_optimizer_common.php

Please let me know if these should be changed to mysqli_error() for the version I am running. Thanks.


osCommerce: made for programmers, ...because store owners do not want to be programmers.

https://trends.google.com/trends/explore?date=all&amp;geo=US&amp;q=oscommerce

Share this post


Link to post
Share on other sites
4 hours ago, Demitry said:

Please let me know if these should be changed to mysqli_error() for the version I am running.

Yes, it should be changed.

Share this post


Link to post
Share on other sites

Keep in mind that it's more than just changing function names mysql_ to mysqli_. Some parameter lists in the calls will also be changed, so you need to check those.

Share this post


Link to post
Share on other sites

@MrPhil

hi Phil,

Thanks man,.. yeah, I usually check. I looked at the database.php file and it has mysqli_error() function return mysql_error() function. That's why I said that it probably did not make much difference.

 


osCommerce: made for programmers, ...because store owners do not want to be programmers.

https://trends.google.com/trends/explore?date=all&amp;geo=US&amp;q=oscommerce

Share this post


Link to post
Share on other sites

@Jack_mcs Hi Thank you first. I try to install this Addon, it always return Access Denied, I reinstall several times and check every file, didn't find anything wrong, should I change permission of some files? now is 644. 

QQ20190604-1.png

Share this post


Link to post
Share on other sites

@moldbodyIt depends on when it is failing. After installing, can you go to the optimizer page or do you get the message right away. If you can get to the page, does it fail when you try to run it from there> Or is it failing when you run a cron job?

Also, please see my signature for a link to a page that explains what you should include when asking a question.

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

×