Latest News: (loading..)
Jack_mcs

Database Optimizer

203 posts in this topic

I recently had a customer concerned that his new shipping/billing addresses that he updated to a while ago have reverted back to the old addresses for some reason. He corrected them, placed his new order, then when the package didn't arrive in the mail, he looked and saw the addresses had again reverted back to the old. The only thing I can think of that might cause this behaviour (and I have performed no database restores) is this mod, as I have been running it somewhat regularly. Is it possible that there's a bug somewhere here with the customer data cleaning? Another reason why it might be good to have a more detailed description of what exactly this mod does. :)

No, this addon doesn't change any data - it just deletes old entries. If it was causing some problem, the customer data would be missing completely, not changed.

Share this post


Link to post
Share on other sites

No chance of it deleting new entries either then, eh? I hope this was just a one-time fluke. Any thoughts about what might have caused this to happen (besides my first thought of customer error)?

Share this post


Link to post
Share on other sites

No chance of it deleting new entries either then, eh? I hope this was just a one-time fluke. Any thoughts about what might have caused this to happen (besides my first thought of customer error)?

If you change the time periods in the admin settings to the smallest setting, it will delete new entries than one would normally probably not want deleted but they are only session entries. If you went into the database and manually cleared the complete table, it would just mean the sessions wouldn't be available. In the case of the customers, that would mean they would lose cart contents. But the code doesn't touch the tables that hold address data so it couldn't have done it.

Share this post


Link to post
Share on other sites

Someone pointed out to me that there is a mistake in the install instructions for 2.3. It should read:

FIND:

 

array(

'code' => FILENAME_BACKUP,

'title' => BOX_TOOLS_BACKUP,

'link' => tep_href_link(FILENAME_BACKUP)

),

 

ADD BENEATH:

 

array(

'code' => FILENAME_DATABASE_OPTIMIZER,

'title' => BOX_TOOLS_DATABASE_OPTIMIZER,

'link' => tep_href_link(FILENAME_DATABASE_OPTIMIZER)

),

 

 

 

 

Can we upload a fixed zip file http://addons.oscommerce.com/info/7820/v,23

 

or at least unlock it so others can...

Edited by 0ethos0

Share this post


Link to post
Share on other sites

A new version will be uploaded when it is ready. I don't allow public uploads to my contributions.

 

 

Well said Jack. its a shame that other contributors do not take this stance, as it prevents the add on becoming a pile of rubbish. I have tried so many add ons to find that they dont work. Keep up the good work.

Share this post


Link to post
Share on other sites

Thank you. I appreciate the support. I, too, have ran across contributions that are such a mess due to public uploads that I cringe when I need to install them. I don't have suggestions on fixing the system though so locking the contribution is the only way. Plus, it makes maintaining it much easier for me. :)

Share this post


Link to post
Share on other sites

Hi Jack,

 

i am still running a V2.2rc2a Shop

 

i would like to know if this MOD, can delete old orders?

 

if the answer is NO, does any one know of a MOD that can do this

 

and if the answer is still NO, it might be a good idea to bring something like that out...!!

 

thanks for your time..

Share this post


Link to post
Share on other sites

Hi Jack,

 

i am still running a V2.2rc2a Shop

 

i would like to know if this MOD, can delete old orders?

 

if the answer is NO, does any one know of a MOD that can do this

 

and if the answer is still NO, it might be a good idea to bring something like that out...!!

 

thanks for your time..

No, it won't do that. It could be coded to do so but its intention was to delete things that couldn't cause any loss of useable data. While you may not want old orders, most shop owners, in my experience, want to keep them. I'm not aware of an addon that will allow the deletion of old orders, though there may be one. If you know how to make edits in phpmyadmin you could easily delete the orders using the order ID. Just be sure to delete them from all of the order tables.

Share this post


Link to post
Share on other sites

thanks for the answer Jack...

 

i think i could possibly say i am quite fix with phpmyadmin, but the syntax to be applyed in MySql would be a bit over the top of my head..

 

and i am not to sure which Tabels i will have to address..!!

 

i am trying this code at the moment to see what happens... or if it works..

 

<?php   

require('includes/application_top.php');   

$strip_date = "2008-07-01 00:00:00";  
$orders = tep_db_fetch_all_array(tep_db_query("select orders_id from orders where date_purchased < '$strip_date'"));  

$count_stripped = count($orders);  

foreach ($orders as $orders_row) {  
 $orders_id = $orders_row['orders_id'];  
 tep_db_query ("delete from orders_products where orders_id = '$orders_id'");  
 tep_db_query ("delete from orders_products_attributes where orders_id = '$orders_id'");  
 tep_db_query ("delete from orders_status_history where orders_id = '$orders_id'");  
 tep_db_query ("delete from orders_total where orders_id = '$orders_id'");    
 tep_db_query ("delete from orders where orders_id = '$orders_id'");  
}  

echo ("$count_stripped orders before $strip_date where deleted!")  

?>

Share this post


Link to post
Share on other sites

thanks for the answer Jack...

 

i think i could possibly say i am quite fix with phpmyadmin, but the syntax to be applyed in MySql would be a bit over the top of my head..

 

and i am not to sure which Tabels i will have to address..!!

 

i am trying this code at the moment to see what happens... or if it works..

 

<?php   

require('includes/application_top.php');   

$strip_date = "2008-07-01 00:00:00";  
$orders = tep_db_fetch_all_array(tep_db_query("select orders_id from orders where date_purchased < '$strip_date'"));  

$count_stripped = count($orders);  

foreach ($orders as $orders_row) {  
 $orders_id = $orders_row['orders_id'];  
 tep_db_query ("delete from orders_products where orders_id = '$orders_id'");  
 tep_db_query ("delete from orders_products_attributes where orders_id = '$orders_id'");  
 tep_db_query ("delete from orders_status_history where orders_id = '$orders_id'");  
 tep_db_query ("delete from orders_total where orders_id = '$orders_id'");    
 tep_db_query ("delete from orders where orders_id = '$orders_id'");  
}  

echo ("$count_stripped orders before $strip_date where deleted!")  

?>

It looks like it will work. Be sure to do a backup first though.

Share this post


Link to post
Share on other sites

A new version has been uploaded with this changes:

 

- Added option to delete old customers

- Added explanations for the various options

- Corrected install instructions for 2.3

- Fixed borken version checking code

Share this post


Link to post
Share on other sites

Happy new year Jack,

 

Thank you for all your hard work.

 

I'm looking at updating to 1.1 but I'm a bit confused on how to update the SQL, I have never done that.

 

Please advise

Share this post


Link to post
Share on other sites

Happy new year Jack,

 

Thank you for all your hard work.

 

I'm looking at updating to 1.1 but I'm a bit confused on how to update the SQL, I have never done that.

 

Please advise

The usual way is to use the phpmyadmin program that most hosts have installed. Once it is open and your database selected, there will be a tab named SQL. Click on that, copy the commands from the update file and save it. You should make a backup of your database first, of course. There are more detailed descriptions on how to use phpmyadmin on the web.

Share this post


Link to post
Share on other sites

I'm getting following SQL error message, the first part took but not the last part?

 

#1060 - Duplicate column name 'customers_old_last_update'

Share this post


Link to post
Share on other sites

Looks like that field already exists, remove the offenting line from the sql and try again

 

HTH

 

G

Share this post


Link to post
Share on other sites

Jack

 

I have just tried adding the sql file in the 2.2 update section and get the following error in phpmyadmin

 

MySQL said:

#1048 - Column 'set_function' cannot be null

 

Have you come across this before

Share this post


Link to post
Share on other sites

Edit the sql and remove DEFAULT NULL from the set_function line

 

HTH

 

G

Share this post


Link to post
Share on other sites

In that case, I am off to sleep.

 

Just spent some time converting an rc2a shop db to 2.3.1 format, loads of error like that.

 

whistling.gif

 

G

Edited by geoffreywalton

Share this post


Link to post
Share on other sites

After updating I'm now getting following cronjobs error

 

 

Fatal error: Class 'aUri_Modules' not found in /home/wihirt/public_html/catalog/includes/modules/ultimate_seo_urls5/uri_modules/path_standard.php on line 25

 

Does this contribution not like ulitimate seo urls5 ?

 

Cheers

Share this post


Link to post
Share on other sites

After updating I'm now getting following cronjobs error

 

 

Fatal error: Class 'aUri_Modules' not found in /home/wihirt/public_html/catalog/includes/modules/ultimate_seo_urls5/uri_modules/path_standard.php on line 25

 

Does this contribution not like ulitimate seo urls5 ?

 

Cheers

99% of the changes in this contribution are in the admin, so SEO 5 won't be involved there, unless an installation mistake was made. On the shop side, the only change is to the product_into page so if it is failing there, you should remove the changes you made to that file, verify SEO 5 works and then make the change again, one at a time and checking it after each change.

Share this post


Link to post
Share on other sites

Thxs Jack,

 

What is the best way checking if SEO Pro works?

 

Checking the url or?

Share this post


Link to post
Share on other sites

Thxs Jack,

 

What is the best way checking if SEO Pro works?

 

Checking the url or?

Turn it off in admin. If the problem still exists, it is due to something else.

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