mr_absinthe Posted February 1, 2012 Share Posted February 1, 2012 (edited) 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 February 1, 2012 by mr_absinthe Quote Absinthe Original Liquor Store Link to comment Share on other sites More sharing options...
Jack_mcs Posted February 1, 2012 Author Share Posted February 1, 2012 (edited) 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 February 1, 2012 by Jack_mcs Quote 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 More sharing options...
mr_absinthe Posted February 2, 2012 Share Posted February 2, 2012 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? Quote Absinthe Original Liquor Store Link to comment Share on other sites More sharing options...
Jack_mcs Posted February 2, 2012 Author Share Posted February 2, 2012 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? Quote 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 More sharing options...
mr_absinthe Posted February 3, 2012 Share Posted February 3, 2012 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') Quote Absinthe Original Liquor Store Link to comment Share on other sites More sharing options...
Jack_mcs Posted February 3, 2012 Author Share Posted February 3, 2012 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? Quote 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 More sharing options...
mr_absinthe Posted February 3, 2012 Share Posted February 3, 2012 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. Quote Absinthe Original Liquor Store Link to comment Share on other sites More sharing options...
Jack_mcs Posted February 3, 2012 Author Share Posted February 3, 2012 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. Quote 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 More sharing options...
mamegaga Posted February 5, 2012 Share Posted February 5, 2012 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: Quote Link to comment Share on other sites More sharing options...
Jack_mcs Posted February 5, 2012 Author Share Posted February 5, 2012 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. Quote 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 More sharing options...
mamegaga Posted February 6, 2012 Share Posted February 6, 2012 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 Quote Link to comment Share on other sites More sharing options...
Jack_mcs Posted February 6, 2012 Author Share Posted February 6, 2012 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. Quote 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 More sharing options...
mamegaga Posted February 6, 2012 Share Posted February 6, 2012 Yes, I followed the instructions in the installation, but I do not understand this error. Â it's a shame. :unsure: Quote Link to comment Share on other sites More sharing options...
Jack_mcs Posted February 6, 2012 Author Share Posted February 6, 2012 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. Quote 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 More sharing options...
Jack_mcs Posted April 1, 2012 Author Share Posted April 1, 2012 A new version has been uploaded with these changes: Â - Fixed code for the User Tracking deletion - Fixed the code for the customer basket deletions Quote 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 More sharing options...
Jack_mcs Posted April 1, 2012 Author Share Posted April 1, 2012 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. Quote 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 More sharing options...
mr_absinthe Posted April 2, 2012 Share Posted April 2, 2012 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. Quote Absinthe Original Liquor Store Link to comment Share on other sites More sharing options...
Jack_mcs Posted April 2, 2012 Author Share Posted April 2, 2012 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. Quote 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 More sharing options...
modem2.0 Posted April 2, 2012 Share Posted April 2, 2012 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 Quote Link to comment Share on other sites More sharing options...
Jack_mcs Posted April 2, 2012 Author Share Posted April 2, 2012 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. Quote 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 More sharing options...
modem2.0 Posted April 2, 2012 Share Posted April 2, 2012 (edited) 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 April 2, 2012 by modem2.0 Quote Link to comment Share on other sites More sharing options...
hasaudio Posted June 7, 2012 Share Posted June 7, 2012 I installed this. Worked like a charm. Thanks alot for this. One thing however that has stopped working is the order editor contribution below. I get no warnings etc. The Edit button is there but it just goes to the information page for that particular order. Any help? Â http://www.oscommerce.com/community/contributions,1435 Quote Link to comment Share on other sites More sharing options...
Jack_mcs Posted June 7, 2012 Author Share Posted June 7, 2012 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. Quote 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 More sharing options...
greasemonkey Posted November 2, 2012 Share Posted November 2, 2012 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('[email protected]', sprintf($subject, DB_DATABASE), $message, '[email protected]';  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? Quote Link to comment Share on other sites More sharing options...
Jack_mcs Posted November 3, 2012 Author Share Posted November 3, 2012 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. Quote 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 More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.