Jump to content
Latest News: (loading..)
Jack_mcs

Database Optimizer

Recommended Posts

A new version has been uploaded with these changes:

 

- Added code to force an update if ran locally

- Added an includes directive to load the database tables names since they were not always being recognized

- Added an option in the settings to set the email addresses to something other than the shop owner

- Corrected mistake in database_optimizer_common.php that was using the wrong config setting for orders (reported by member mention)

 

I just realized after uploading this that I forgot to trim the email address from the setting so be sure to enter the address with spaces or make this change in the database_optimizer_common.php file: Change

		    mail($emailAddress, sprintf($subject, DB_DATABASE), $message, $config['email_address']);

to

		    mail(trim($emailAddress), sprintf($subject, DB_DATABASE), $message, $config['email_address']);

 

I'll make that change for the next version.

Edited by Jack_mcs

Share this post


Link to post
Share on other sites

Wow... first run, customers baskets and old customers not checked, all others processed

 

Store database has been optimized.

Sessions table was trimmed.

User Tracking table was trimmed.

Database was analyzed.

Database was optimized.

Initial size was 78561928.

Final, optimized, size is 37451988.

 

Thank you Jack!


-Dave

Share this post


Link to post
Share on other sites

hello

 

suddenly everyone is moving from Msql_query to Msqli_query

 

in order to work with Msqli_query can all msql_**** commands and functions be replaced with the corresponding

Msqli_**** commands and funtion

 

for example from admin/includes/modules/database_optimizer_common.php

 

/*********************** REMOVE FROM CUSTOMER TABLES *****************************/

if (! empty($config['customers']) && $config['customers'] < $daysLastRan) {

$dateCustomers = date("Ymd", time() - ($config['customers'] * 86400));

$wasUpdated = true;

$message .= "\r\n" . 'Customers tables were trimmed.';

 

mysql_query("delete from " . TABLE_CUSTOMERS_BASKET_ATTRIBUTES . " WHERE (`products_id`, `customers_id`) in (select `products_id`, `customers_id` from " . TABLE_CUSTOMERS_BASKET . " where customers_basket_date_added < '" . $dateCustomers . "')");

mysql_query("delete from " . TABLE_CUSTOMERS_BASKET . " where customers_basket_date_added < '" . $dateCustomers . "'"); //clear the customers basket table of entries greater than one month old

mysql_query("update database_optimizer set customers_last_update = now()");

}

 

change to

 

/*********************** REMOVE FROM CUSTOMER TABLES *****************************/

if (! empty($config['customers']) && $config['customers'] < $daysLastRan) {

$dateCustomers = date("Ymd", time() - ($config['customers'] * 86400));

$wasUpdated = true;

$message .= "\r\n" . 'Customers tables were trimmed.';

 

mysqli_query("delete from " . TABLE_CUSTOMERS_BASKET_ATTRIBUTES . " WHERE (`products_id`, `customers_id`) in (select `products_id`, `customers_id` from " . TABLE_CUSTOMERS_BASKET . " where customers_basket_date_added < '" . $dateCustomers . "')");

mysqli_query("delete from " . TABLE_CUSTOMERS_BASKET . " where customers_basket_date_added < '" . $dateCustomers . "'"); //clear the customers basket table of entries greater than one month old

mysqli_query("update database_optimizer set customers_last_update = now()");

}

 

 

greetings

eric

Share this post


Link to post
Share on other sites

Have you converted your shop's core code? Converting this addon is a trivial change compared to that and would be a waste of time, in my opinion, until that is done. Even then, there wouldn't be any advantage, that I can see, in converting it. But there are plenty of examples on the web if you have the time to go through it.

Share this post


Link to post
Share on other sites

I get the following error when I click on the newly added Database Optimizer link on my Admin panel...

 

Warning: Invalid argument supplied for foreach() in /hermes/waloraweb055/b2373/pow.xxxxxxxxx/htdocs/admin/database_optimizer.php on line 142

 

 

Line 142 in that file says:

 

<?php foreach ($optionsArray as $option) { ?>

 

Assuming line 142 as displayed in Notepad++ is the same as the one referred to above...

 

Any ideas?

Edited by kru

Add-Ons personally installed:

Step by Step Manual Order -- Request Reviews -- Reviews in Product Listing -- Reviews in Product Display -- Review Approval System -- Leverage Browser Cache --
Header Tag Controller -- Multilayer SEO Pop Out Menu -- Follow Us Box -- View All Products -- USPS Shipping Labels -- UPS Shipping Labels -- Monthly Sales/Tax Report --
htacess Optimisation -- Remove Unused Images -- Master Password -- Admin Change Customer Password -- Database Backup Manager -- Zero Stock Report --
Searchbox Search In Descriptions -- Easy Populate 2.76i -- Barcode Rendering -- Admin Sort By Model -- Products Purchased Report

Add-Ons personally developed:

Search for email address, etc in Orders -- Discontinue Product

Add-Ons installed by others:

View Counter -- Site Monitor -- Image Thumbnailer -- Database Optimizer -- Recaptcha -- Discount Coupons -- Add More Fields

Share this post


Link to post
Share on other sites

The options appear OK in Admin>>Configuration, for what it's worth...

Edited by kru

Add-Ons personally installed:

Step by Step Manual Order -- Request Reviews -- Reviews in Product Listing -- Reviews in Product Display -- Review Approval System -- Leverage Browser Cache --
Header Tag Controller -- Multilayer SEO Pop Out Menu -- Follow Us Box -- View All Products -- USPS Shipping Labels -- UPS Shipping Labels -- Monthly Sales/Tax Report --
htacess Optimisation -- Remove Unused Images -- Master Password -- Admin Change Customer Password -- Database Backup Manager -- Zero Stock Report --
Searchbox Search In Descriptions -- Easy Populate 2.76i -- Barcode Rendering -- Admin Sort By Model -- Products Purchased Report

Add-Ons personally developed:

Search for email address, etc in Orders -- Discontinue Product

Add-Ons installed by others:

View Counter -- Site Monitor -- Image Thumbnailer -- Database Optimizer -- Recaptcha -- Discount Coupons -- Add More Fields

Share this post


Link to post
Share on other sites

The headings all appear to be messed up in Admin>>Tools too. They say:

 

HEADING_TITLE_SUPPORT_THREAD

HEADING_TITLE_AUTHOR

TEXT_MISSING_VERSION_CHECKER

TEXT_DATABASE_OPTIMIZER

Edited by kru

Add-Ons personally installed:

Step by Step Manual Order -- Request Reviews -- Reviews in Product Listing -- Reviews in Product Display -- Review Approval System -- Leverage Browser Cache --
Header Tag Controller -- Multilayer SEO Pop Out Menu -- Follow Us Box -- View All Products -- USPS Shipping Labels -- UPS Shipping Labels -- Monthly Sales/Tax Report --
htacess Optimisation -- Remove Unused Images -- Master Password -- Admin Change Customer Password -- Database Backup Manager -- Zero Stock Report --
Searchbox Search In Descriptions -- Easy Populate 2.76i -- Barcode Rendering -- Admin Sort By Model -- Products Purchased Report

Add-Ons personally developed:

Search for email address, etc in Orders -- Discontinue Product

Add-Ons installed by others:

View Counter -- Site Monitor -- Image Thumbnailer -- Database Optimizer -- Recaptcha -- Discount Coupons -- Add More Fields

Share this post


Link to post
Share on other sites

What is the 'catalog' folder for in the Contrib download files? There are php programs in it (in several subfolders) but there is no reference to them in the instructions...

 

The instructions say:

 

2) Upload all of the files in the "New_Files_Only" directory to the

same location on your server. Be sure to use the correct version

for your shop.

 

Which I've done. But I'm just curious about those files in the catalog folder...

Edited by kru

Add-Ons personally installed:

Step by Step Manual Order -- Request Reviews -- Reviews in Product Listing -- Reviews in Product Display -- Review Approval System -- Leverage Browser Cache --
Header Tag Controller -- Multilayer SEO Pop Out Menu -- Follow Us Box -- View All Products -- USPS Shipping Labels -- UPS Shipping Labels -- Monthly Sales/Tax Report --
htacess Optimisation -- Remove Unused Images -- Master Password -- Admin Change Customer Password -- Database Backup Manager -- Zero Stock Report --
Searchbox Search In Descriptions -- Easy Populate 2.76i -- Barcode Rendering -- Admin Sort By Model -- Products Purchased Report

Add-Ons personally developed:

Search for email address, etc in Orders -- Discontinue Product

Add-Ons installed by others:

View Counter -- Site Monitor -- Image Thumbnailer -- Database Optimizer -- Recaptcha -- Discount Coupons -- Add More Fields

Share this post


Link to post
Share on other sites

There is an UPDATE button on the screen but I'm not going to run this when it looks like there is a problem...


Add-Ons personally installed:

Step by Step Manual Order -- Request Reviews -- Reviews in Product Listing -- Reviews in Product Display -- Review Approval System -- Leverage Browser Cache --
Header Tag Controller -- Multilayer SEO Pop Out Menu -- Follow Us Box -- View All Products -- USPS Shipping Labels -- UPS Shipping Labels -- Monthly Sales/Tax Report --
htacess Optimisation -- Remove Unused Images -- Master Password -- Admin Change Customer Password -- Database Backup Manager -- Zero Stock Report --
Searchbox Search In Descriptions -- Easy Populate 2.76i -- Barcode Rendering -- Admin Sort By Model -- Products Purchased Report

Add-Ons personally developed:

Search for email address, etc in Orders -- Discontinue Product

Add-Ons installed by others:

View Counter -- Site Monitor -- Image Thumbnailer -- Database Optimizer -- Recaptcha -- Discount Coupons -- Add More Fields

Share this post


Link to post
Share on other sites

Loaded some files to the wrong folder. Fixed it and everything appears fine now. Disregard above posts....


Add-Ons personally installed:

Step by Step Manual Order -- Request Reviews -- Reviews in Product Listing -- Reviews in Product Display -- Review Approval System -- Leverage Browser Cache --
Header Tag Controller -- Multilayer SEO Pop Out Menu -- Follow Us Box -- View All Products -- USPS Shipping Labels -- UPS Shipping Labels -- Monthly Sales/Tax Report --
htacess Optimisation -- Remove Unused Images -- Master Password -- Admin Change Customer Password -- Database Backup Manager -- Zero Stock Report --
Searchbox Search In Descriptions -- Easy Populate 2.76i -- Barcode Rendering -- Admin Sort By Model -- Products Purchased Report

Add-Ons personally developed:

Search for email address, etc in Orders -- Discontinue Product

Add-Ons installed by others:

View Counter -- Site Monitor -- Image Thumbnailer -- Database Optimizer -- Recaptcha -- Discount Coupons -- Add More Fields

Share this post


Link to post
Share on other sites

Jack,

 

On the customer table deletes, is it just for old 'customers' who never ordered anything? I can see the value in deleting those records occasionally but we definitely wouldn't want to delete customers who purchased something, regardless of how long ago it was.

 

Earlier in this thread you indicated customer records weren't deleted but then you came out with an updated version of the program that indicates customer delete functionality was added. So I figured I should ask for some clarification...

 

Thanks.

Edited by kru

Add-Ons personally installed:

Step by Step Manual Order -- Request Reviews -- Reviews in Product Listing -- Reviews in Product Display -- Review Approval System -- Leverage Browser Cache --
Header Tag Controller -- Multilayer SEO Pop Out Menu -- Follow Us Box -- View All Products -- USPS Shipping Labels -- UPS Shipping Labels -- Monthly Sales/Tax Report --
htacess Optimisation -- Remove Unused Images -- Master Password -- Admin Change Customer Password -- Database Backup Manager -- Zero Stock Report --
Searchbox Search In Descriptions -- Easy Populate 2.76i -- Barcode Rendering -- Admin Sort By Model -- Products Purchased Report

Add-Ons personally developed:

Search for email address, etc in Orders -- Discontinue Product

Add-Ons installed by others:

View Counter -- Site Monitor -- Image Thumbnailer -- Database Optimizer -- Recaptcha -- Discount Coupons -- Add More Fields

Share this post


Link to post
Share on other sites

When a customer logs into his account, it is updaed, whether a purchase is made or not. The idea behind that section is to delete customers that haven't logged in in whatever amount of time you set. So if a customer bought something from you five years ago and never logged in again, his account would be deleted, assuming you have the period set to less than five years. That doesn't delete any orders he may have made though. The orders are stored in a different table and not touched by that option.

Share this post


Link to post
Share on other sites

@@Jack_mcs , thank you for this wonderful contribution. I have been using it in my 2.2 store (it was version 1.2 of your add-on) for 6 months... and it has been flawless.

 

I have recently upgraded my store to 2.3.3 and installed your version 1.3. I'm having issues when run via my cron job (its the exact same cron job, except the path, as my 2.2 store). It works fine when ran manually via the admin. I get the following error via email from my server when the cron runs;

 

X-Powered-By: PHP/5.2.17

Content-type: text/html

 

<br />

<b>Warning</b>: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in <b>/home/******/public_html/********/database_optimizer_cron.php</b> on line <b>33</b><br />

 

My cron is; 0 0 * * * php /home/******/public_html/********/database_optimizer_cron.php

 

I have compared to my 2.2 stores version 1.2, and see a couple changes... but not sure which is causing the issue...

 

I see in database_optimizer.php line 41 has added;

 

 $forceOptimize = true;       //this is being ran manually so ignore the setting for optimizing 

 

And in databse_optimizer_cron.php lin2 32 & 43 where changed to add the email address (which I presume wouldn't cause this issue).

 

Any ideas? I have double check my install and don't "think" I have missed anything.

 

Thank you very much in advance...

Share this post


Link to post
Share on other sites

The failure is at line 33 so since you changed that, my guess is that is where the problem is at. Try using the original code to see if the problem goes away.

Share this post


Link to post
Share on other sites

Hi Jack thank you kindly for your quick reply. Sorry if I mis-spoke... I did not change line 33... I was mentioning it "has been" (I presume by you) changed from verions 1.2 (for 2.2) to verions 1.3 (for 2.3.x)... and I don't understand why it would be causing the failure/error.

 

In your version 1.2 line 31 (for 2.2) is;

 

$config_query = mysql_query("select * FROM configuration WHERE (configuration_key LIKE 'DATABASE_OPTIMIZER%' or configuration_key = 'STORE_OWNER_EMAIL_ADDRESS')");

 

In versions 1.3 line 33 (for 2.3) is, and where I'm getting the error;

 

$config_query = mysql_query("select * FROM configuration WHERE (configuration_key LIKE 'DATABASE_OPTIMIZER%'");

 

I could change the code in the new version (1.3) with that from the old (1.2)... however, I'm not sure that is/was the right thing to do (please know I'm not a coder in anyway... just self taught enough to be dangerous) because you have added the email notification in v1.3 to the admin configuration;

 

case 'DATABASE_OPTIMIZER_EMAIL_NOTIFY':  $config['email_address'] = $loadconfig['configuration_value']; break;

 

In v1.2 you have used;

 

    case 'STORE_OWNER_EMAIL_ADDRESS':        $config['email_address'] = $loadconfig['configuration_value']; break;

 

Here are the 2 blocks of code... just so I'm clear...

 

Version 1.2 is;

 

mysql_select_db(DB_DATABASE);

$config = array();
$config_query = mysql_query("select * FROM configuration WHERE (configuration_key LIKE 'DATABASE_OPTIMIZER%' or configuration_key = 'STORE_OWNER_EMAIL_ADDRESS')");
while ($loadconfig = mysql_fetch_array($config_query, MYSQL_ASSOC)) {
   switch ($loadconfig['configuration_key']) {
       case 'DATABASE_OPTIMIZER_ENABLE':        $config['main_switch'] = $loadconfig['configuration_value']; break;
       case 'DATABASE_OPTIMIZER_PERIOD':        $config['period'] = $config['optimize'] =$loadconfig['configuration_value']; break;
       case 'DATABASE_OPTIMIZER_ANALYZE':       $config['analyze'] = $loadconfig['configuration_value']; break;
       case 'DATABASE_OPTIMIZER_CUSTOMERS':     $config['customers'] = $loadconfig['configuration_value']; break;
       case 'DATABASE_OPTIMIZER_CUSTOMERS_OLD': $config['customers_old'] = $loadconfig['configuration_value']; break;
       case 'DATABASE_OPTIMIZER_ORDERS_CC':     $config['orders_cc'] = $loadconfig['configuration_value']; break;
       case 'DATABASE_OPTIMIZER_SESSIONS':      $config['sessions'] = $loadconfig['configuration_value']; break;
       case 'DATABASE_OPTIMIZER_USER_TRACKING': $config['usertracking'] = $loadconfig['configuration_value']; break;
       case 'STORE_OWNER_EMAIL_ADDRESS':        $config['email_address'] = $loadconfig['configuration_value']; break;
   }
}

 

Version 1.3 is;

 

mysql_select_db(DB_DATABASE);

$config = array();
$config_query = mysql_query("select * FROM configuration WHERE (configuration_key LIKE 'DATABASE_OPTIMIZER%'");
while ($loadconfig = mysql_fetch_array($config_query, MYSQL_ASSOC)) {
   switch ($loadconfig['configuration_key']) {
       case 'DATABASE_OPTIMIZER_ENABLE':        $config['main_switch'] = $loadconfig['configuration_value']; break;
       case 'DATABASE_OPTIMIZER_PERIOD':        $config['period'] = $config['optimize'] =$loadconfig['configuration_value']; break;
       case 'DATABASE_OPTIMIZER_ANALYZE':       $config['analyze'] = $loadconfig['configuration_value']; break;
       case 'DATABASE_OPTIMIZER_CUSTOMERS':     $config['customers'] = $loadconfig['configuration_value']; break;
       case 'DATABASE_OPTIMIZER_CUSTOMERS_OLD': $config['customers_old'] = $loadconfig['configuration_value']; break;
       case 'DATABASE_OPTIMIZER_ORDERS_CC':     $config['orders_cc'] = $loadconfig['configuration_value']; break;
       case 'DATABASE_OPTIMIZER_SESSIONS':      $config['sessions'] = $loadconfig['configuration_value']; break;
       case 'DATABASE_OPTIMIZER_USER_TRACKING': $config['usertracking'] = $loadconfig['configuration_value']; break;
       case 'DATABASE_OPTIMIZER_EMAIL_NOTIFY':  $config['email_address'] = $loadconfig['configuration_value']; break;
   }
}

Share this post


Link to post
Share on other sites

Please try the following to see what happens. Add this

if($config_query === FALSE) {
   die(mysql_error()); // TODO: better error handling
}

below this line

$config_query = mysql_query("select * FROM configuration WHERE configuration_key LIKE 'DATABASE_OPTIMIZER%'");

Share this post


Link to post
Share on other sites

Hi Jack thank you... Here is the reply.

 

X-Powered-By: PHP/5.2.17

Content-type: text/html

 

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1

 

I have tried changing to this;

 

$config_query = mysql_query("select * FROM configuration WHERE (configuration_key LIKE 'DATABASE_OPTIMIZER%' or configuration_key = 'DATABASE_OPTIMIZER_EMAIL_NOTIFY')");

 

Including your error handling code and it ran without error... Not sure if above is correcet however?

Share this post


Link to post
Share on other sites

The problem is that there is a coding error in the mysql call. It was introduced with the remove of the email address entry so by using the old command, you are removing that mistake. This has been fixed in 1.4, which hasn't been released yet. To fix it, replace

$config_query = mysql_query("select * FROM configuration WHERE (configuration_key LIKE 'DATABASE_OPTIMIZER%'");

 

with

$config_query = mysql_query("select * FROM configuration WHERE configuration_key LIKE 'DATABASE_OPTIMIZER%'");

Share this post


Link to post
Share on other sites

Hi Jack,

 

 

thanks for this great addon, I installed without any problem in a new shop with about 25 addons, the shop works fine, then I run the addon, these are the results :

Store database has been optimized.

Customers tables were trimmed.

Sessions table was trimmed.

Database was analyzed.

Database was optimized.

Initial size was 1556480.

Final, optimized, size is 1556480.

 

No changes have been done ! maybe because there where no optimalization to do?

 

After running the addon I got an error :

 

Warning: mail() [function.mail]: "sendmail_from" not set in php.ini or custom "From:" header missing in G:\xampp\htdocs\mas\backend\includes\modules\database_optimizer_common.php on line 115

 

Regards

Kozak

Edited by kozak

Share this post


Link to post
Share on other sites

It might be that they didn't need optimizing but that is usually not the case if the shop has been active for a while. It might be that you don't have the "Optimize Database Period" setting set to 1 so the tables were reduced but not optimized. The error you are seeing is due to your local setup. This code is trying to send an email but your local setup is correctly setup for that, thus the error.

Share this post


Link to post
Share on other sites

I am getting a lot of errors, please help some one? :)

 

Warning: mysql_query() [function.mysql-query]: Access denied for user 'nature'@'localhost' (using password: NO) in /home/nature/public_html/admin/includes/modules/database_optimizer.php on line 44

 

Warning: mysql_query() [function.mysql-query]: A link to the server could not be established in /home/nature/public_html/admin/includes/modules/database_optimizer.php on line 44

 

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/nature/public_html/admin/includes/modules/database_optimizer.php on line 45

 

Warning: mktime() expects parameter 6 to be long, string given in /home/nature/public_html/admin/includes/modules/database_optimizer.php on line 47

 

Warning: mysql_select_db() [function.mysql-select-db]: Access denied for user 'nature'@'localhost' (using password: NO) in /home/nature/public_html/admin/includes/modules/database_optimizer.php on line 55

 

Warning: mysql_select_db() [function.mysql-select-db]: A link to the server could not be established in /home/nature/public_html/admin/includes/modules/database_optimizer.php on line 55

 

Warning: mysql_query() [function.mysql-query]: Access denied for user 'nature'@'localhost' (using password: NO) in /home/nature/public_html/admin/includes/modules/database_optimizer.php on line 56

 

Warning: mysql_query() [function.mysql-query]: A link to the server could not be established in /home/nature/public_html/admin/includes/modules/database_optimizer.php on line 56

 

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/nature/public_html/admin/includes/modules/database_optimizer.php on line 58

 

Warning: mysql_select_db() [function.mysql-select-db]: Access denied for user 'nature'@'localhost' (using password: NO) in /home/nature/public_html/admin/includes/modules/database_optimizer_common.php on line 79

 

Warning: mysql_select_db() [function.mysql-select-db]: A link to the server could not be established in /home/nature/public_html/admin/includes/modules/database_optimizer_common.php on line 79

 

Warning: mysql_select_db() [function.mysql-select-db]: Access denied for user 'nature'@'localhost' (using password: NO) in /home/nature/public_html/admin/includes/modules/database_optimizer.php on line 55

 

Warning: mysql_select_db() [function.mysql-select-db]: A link to the server could not be established in /home/nature/public_html/admin/includes/modules/database_optimizer.php on line 55

 

Warning: mysql_query() [function.mysql-query]: Access denied for user 'nature'@'localhost' (using password: NO) in /home/nature/public_html/admin/includes/modules/database_optimizer.php on line 56

 

Warning: mysql_query() [function.mysql-query]: A link to the server could not be established in /home/nature/public_html/admin/includes/modules/database_optimizer.php on line 56

 

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/nature/public_html/admin/includes/modules/database_optimizer.php on line 58

Share this post


Link to post
Share on other sites

It's because the latest oscommerce version switched to mysqli. I haven't had time to work on this but will fix it when I can.

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

×