Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

Database Optimizer


Jack_mcs

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).

Support Links:

For Hire: Contact me for anything you need help with for your shop: upgrading, hosting, repairs, code written, etc.

Get the latest versions of my addons

Recommended SEO Addons

Link to comment
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');

 

Link to comment
Share on other sites

  • 1 month later...
  • 10 months later...

@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?

Link to comment
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

 

Link to comment
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');

 

Support Links:

For Hire: Contact me for anything you need help with for your shop: upgrading, hosting, repairs, code written, etc.

Get the latest versions of my addons

Recommended SEO Addons

Link to comment
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.

Link to comment
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.

Support Links:

For Hire: Contact me for anything you need help with for your shop: upgrading, hosting, repairs, code written, etc.

Get the latest versions of my addons

Recommended SEO Addons

Link to comment
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.

Support Links:

For Hire: Contact me for anything you need help with for your shop: upgrading, hosting, repairs, code written, etc.

Get the latest versions of my addons

Recommended SEO Addons

Link to comment
Share on other sites

  • 3 months later...

@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?

Link to comment
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.

Support Links:

For Hire: Contact me for anything you need help with for your shop: upgrading, hosting, repairs, code written, etc.

Get the latest versions of my addons

Recommended SEO Addons

Link to comment
Share on other sites

  • 1 month later...

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

Link to comment
Share on other sites

  • 2 months later...

@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

Link to comment
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.

Support Links:

For Hire: Contact me for anything you need help with for your shop: upgrading, hosting, repairs, code written, etc.

Get the latest versions of my addons

Recommended SEO Addons

Link to comment
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

Link to comment
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.

Support Links:

For Hire: Contact me for anything you need help with for your shop: upgrading, hosting, repairs, code written, etc.

Get the latest versions of my addons

Recommended SEO Addons

Link to comment
Share on other sites

  • 10 months later...

@ce7For the first one, at the location shown,  change

            case TEXT_DO_OPTIMIZE:       $config['optimize']      = DATABASE_OPTIMIZER_OPTIMIZE;      $optionSelected = true; break;

to

            case TEXT_DO_OPTIMIZE:       $config['optimize']      = DATABASE_OPTIMIZER_PERIOD;      $optionSelected = true; break;

For the second one, change

    $mainDate = tep_db_fetch_array($query, MYSQL_ASSOC);

to

    $mainDate = tep_db_fetch_array($query);

For the third one, change

case 'Opt C': continue; //skip customers with orders

to

case 'Opt C': continue 2; //skip customers with orders

 

Support Links:

For Hire: Contact me for anything you need help with for your shop: upgrading, hosting, repairs, code written, etc.

Get the latest versions of my addons

Recommended SEO Addons

Link to comment
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
Unfortunately, your content contains terms that we do not allow. Please edit your content to remove the highlighted words below.
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...