Jump to content
Jack_mcs

Database Optimizer

Recommended Posts

Hi Jack, it appears that the customer basket is not being cleaned. Despite the fact that I have 30 days set as value in settings, I can still see records there from 2005. Same for the customers_basket_attributes, nothing is being deleted.

Edited by mr_absinthe

Absinthe Original Liquor Store

Share this post


Link to post
Share on other sites

Hi Jack, it appears that the customer basket is not being cleaned. Despite the fact that I have 30 days set as value in settings, I can still see records there from 2005. Same for the customers_basket_attributes, nothing is being deleted.

It's due to a coding error, which has been fixed in the next version. To apply the fix, find the following code in the admin/includes/modules/database_optimizer_common.php
		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

and replace it with

		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

Edited by Jack_mcs

Share this post


Link to post
Share on other sites

OK, it works now, however since I have 30 days set as value in settings, I was expecting to see one month old data in table customers_basket. After running the optimizer, there are only records from yesterday (oldest date_added is 20120201). Is there something I've missed?


Absinthe Original Liquor Store

Share this post


Link to post
Share on other sites

OK, it works now, however since I have 30 days set as value in settings, I was expecting to see one month old data in table customers_basket. After running the optimizer, there are only records from yesterday (oldest date_added is 20120201). Is there something I've missed?

In the admin/includes/modules/database_optimizer_common.php file, find this line
	    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 . "')");

and place this line above it

echo "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 . "')";

Then go to tools->Database Optimizer, check the "Remove Customer Sessions" box and update. The mysql command should be displayed on the page. The date will be at the end of the line in the form of yyyymmdd. It should be todays date minus the offset (30 days). Is it?

Share this post


Link to post
Share on other sites

It is and the same date is the oldest record in the table now - 20120104.

delete from customers_basket_attributes WHERE (`products_id`, `customers_id`) in (select `products_id`, `customers_id` from customers_basket where customers_basket_date_added < '20120104')


Absinthe Original Liquor Store

Share this post


Link to post
Share on other sites

It is and the same date is the oldest record in the table now - 20120104.

delete from customers_basket_attributes WHERE (`products_id`, `customers_id`) in (select `products_id`, `customers_id` from customers_basket where customers_basket_date_added < '20120104')

So are you saying the date is correct but it removes the wrong items?

Share this post


Link to post
Share on other sites

No, this time it removed the correct items. It even works with the echo commented out - it is removing correct items, no idea why it didn't work yesterday. The calculation output is not showing the removed items however:

 

Store database has been optimized.

Customers tables were trimmed.

Initial size was 49209454.

Final, optimized, size is 49209454.

 

With about five years data the initial figure should be higher I guess.


Absinthe Original Liquor Store

Share this post


Link to post
Share on other sites

No, this time it removed the correct items. It even works with the echo commented out - it is removing correct items, no idea why it didn't work yesterday. The calculation output is not showing the removed items however:

 

Store database has been optimized.

Customers tables were trimmed.

Initial size was 49209454.

Final, optimized, size is 49209454.

 

With about five years data the initial figure should be higher I guess.

The majority of the size is usually due to the products and orders, which aren't changed. From what you said previously, the customers basket had already been handled so there wouldn't be any changes for it to make now. If you had restored that though and it still isn't showing a difference, you would need to look at the table in phpmyadmin before and after to see if it is changing.

Share this post


Link to post
Share on other sites

Hello all !

 

 

 

I have the same problem as Lioelx and martin67, has a resolution it you it, I am on a waiter devoted with osc2.1.3.

 

 

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/xxxx/public_html/shop/back_ans/includes/modules/database_optimizer.php on line 44

 

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

 

thk's :blush:

Share this post


Link to post
Share on other sites

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/xxxx/public_html/shop/back_ans/includes/modules/database_optimizer.php on line 44

It looks like the failure is due to not being able to read the database. Be sure you have made the required database changes for this contribution.

Share this post


Link to post
Share on other sites

In admin/include/database_table.php

 

I put this:

 

define('TABLE_DATABASE_OPTIMIZER', 'database_optimizer');

 

The line 44 corresponds to this:

 

$mainDate = mysql_fetch_array($query, MYSQL_ASSOC);

 

Correct ?

 

thank you

Share this post


Link to post
Share on other sites

In admin/include/database_table.php

 

I put this:

 

define('TABLE_DATABASE_OPTIMIZER', 'database_optimizer');

 

The line 44 corresponds to this:

 

$mainDate = mysql_fetch_array($query, MYSQL_ASSOC);

 

Correct ?

Yes, that is one of the instructions. There are others. I don't understand the reference to the mysql command though.

Share this post


Link to post
Share on other sites

Yes, I followed the instructions in the installation, but I do not understand this error.

 

it's a shame. :unsure:

You could try posting the actual failure.

Share this post


Link to post
Share on other sites

A new version has been uploaded with these changes:

 

- Fixed code for the User Tracking deletion

- Fixed the code for the customer basket deletions

Share this post


Link to post
Share on other sites

No, this time it removed the correct items. It even works with the echo commented out - it is removing correct items, no idea why it didn't work yesterday. The calculation output is not showing the removed items however:

 

Store database has been optimized.

Customers tables were trimmed.

Initial size was 49209454.

Final, optimized, size is 49209454.

 

With about five years data the initial figure should be higher I guess.

In testing the last version I think I saw this symptom. The problem is that even though the data is deleted from the database, mysql won't update the table size without being told to do so. The code tells it to do so when the "Optimize Database Period" setting is ran. But if one setting is set to 3 days while that setting is set to 4, the table sizes won't be updated at that time. In a latter run, the settings time will have passed and the size are then corrected. So I suggest setting that option to the lowest value of all of the settings. I think that will fix this timing problem.

Share this post


Link to post
Share on other sites

Thank you for the update. It seems correct, I made sure that Optimise Database Period has the lowest value of 7 days and this was the result:

 

Store database has been optimized.

Customers tables were trimmed.

Credit Card data was removed from the orders table.

Sessions table was trimmed.

Database was analyzed.

Database was optimized.

Initial size was 48287307.

Final, optimized, size is 47349379.

 

Also I believe that you should add the following to the Update_1.1_to_1.2.txt file:

2) Upload the admin\database_optimizer.php

otherwise the version number will be incorrect.


Absinthe Original Liquor Store

Share this post


Link to post
Share on other sites

Also I believe that you should add the following to the Update_1.1_to_1.2.txt file:

2) Upload the admin\database_optimizer.php

otherwise the version number will be incorrect.

Thanks for poining that out. I will correct the next release.

Share this post


Link to post
Share on other sites

Hi Jack

 

I have just installed your contribution and while I was looking at the code to try to understand how the settings where being applied, I believe I found a bug on the remove CC data. You have this:

    /********************** REMOVE CC DATA FROM ORDERS TABLE *************************/
    if (! empty($config['orders_cc']) && $config['orders_cc'] < $daysLastRan) {
	    $dateOrder = date("Y-m-d", time() - ($config['customers'] * 86400));
	    $wasUpdated = true;
	    $message .= "\r\n" . 'Credit Card data was removed from the orders table.';
	    mysql_query("update " . TABLE_ORDERS . " set cc_number = '' where date_purchased > '" . $dateOrder . "'");	 //clear the sessions table of entries greater than one month old
	    mysql_query("update database_optimizer set orders_last_update = now()");
    }

 

but I believe it should be:

    /********************** REMOVE CC DATA FROM ORDERS TABLE *************************/
    if (! empty($config['orders_cc']) && $config['orders_cc'] < $daysLastRan) {
	    $dateOrder = date("Y-m-d", time() - ($config['orders_cc'] * 86400));
	    $wasUpdated = true;
	    $message .= "\r\n" . 'Credit Card data was removed from the orders table.';
	    mysql_query("update " . TABLE_ORDERS . " set cc_number = '' where date_purchased > '" . $dateOrder . "'");	 //clear the sessions table of entries greater than one month old
	    mysql_query("update database_optimizer set orders_last_update = now()");
    }

(notice the change in

$dateOrder = date("Y-m-d", time() - ($config['orders_cc'] * 86400));

Am I right?

 

Regards

Share this post


Link to post
Share on other sites

Hi Jack

 

I have just installed your contribution and while I was looking at the code to try to understand how the settings where being applied, I believe I found a bug on the remove CC data. You have this:

Am I right?

Yes, you are. Thank you for pointing out the mistake.

Share this post


Link to post
Share on other sites

Thanks Jack. I updated my copy!

 

Quick edit: for the distracted people :), the change has to be done in admin/includes/modules/database_optimizer_common.php

Edited by modem2.0

Share this post


Link to post
Share on other sites

This contribution doesn't make any changes that would cause that. You may have overwritten code for something else by mistake though so you should restore your files, make sure everything works and install it again. If that doesn't help, then you need to ask in the support thread for that contribution for help.

Share this post


Link to post
Share on other sites

Just a quick question about the email function in admin/includes/modules/database_optimizer_common.php

 

I was hoping to email the report to a differenct email address than the stores email address... How can I modify line 112

 

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

 

Can this just be changed to

 

	    mail('my@email.com', sprintf($subject, DB_DATABASE), $message, 'my@email.com';

 

Also, what is everyone using or what is "typical" for config for;

 

Truncate Customers

Truncate Sessions

 

I though 7 days was too little... I don't want customers coming back after a week and having the previous basket empty... Or am I mis-understanding these to configs?

Share this post


Link to post
Share on other sites

Yes, that email change will work. Those settings have to do with how long items will stay in the customers basket and how long their session data will last. The defaults are 20 and 14 so I don't know where you are getting 7 from but, regardless, there's no right answer. Some shop owners want them cleared after a week while others leave them for six months, based upon the shops I've installed this into.

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

×