Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

Database Optimizer


Jack_mcs

Recommended Posts

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.

Support Links:

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

All of My Addons

Get the latest versions of my addons

Recommended SEO Addons

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

Support Links:

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

All of My Addons

Get the latest versions of my addons

Recommended SEO Addons

Link to comment
Share on other sites

  • 2 months later...

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
Link to comment
Share on other sites

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

 

or at least unlock it so others can...

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

Support Links:

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

All of My Addons

Get the latest versions of my addons

Recommended SEO Addons

Link to comment
Share on other sites

  • 4 weeks later...

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.

REMEMBER BACKUP, BACKUP AND BACKUP

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

Support Links:

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

All of My Addons

Get the latest versions of my addons

Recommended SEO Addons

Link to comment
Share on other sites

  • 3 weeks later...

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

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

Support Links:

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

All of My Addons

Get the latest versions of my addons

Recommended SEO Addons

Link to comment
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!")  

?>

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

Support Links:

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

All of My Addons

Get the latest versions of my addons

Recommended SEO Addons

Link to comment
Share on other sites

  • 4 months later...

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

Support Links:

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

All of My Addons

Get the latest versions of my addons

Recommended SEO Addons

Link to comment
Share on other sites

  • 2 weeks later...

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

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

Support Links:

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

All of My Addons

Get the latest versions of my addons

Recommended SEO Addons

Link to comment
Share on other sites

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

 

HTH

 

G

Need help installing add ons/contributions, cleaning a hacked site or a bespoke development, check my profile

 

Virus Threat Scanner

My Contributions

Basic install answers.

Click here for Contributions / Add Ons.

UK your site.

Site Move.

Basic design info.

 

For links mentioned in old answers that are no longer here follow this link Useful Threads.

 

If this post was useful, click the Like This button over there ======>>>>>.

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

REMEMBER BACKUP, BACKUP AND BACKUP

Link to comment
Share on other sites

Edit the sql and remove DEFAULT NULL from the set_function line

 

HTH

 

G

Need help installing add ons/contributions, cleaning a hacked site or a bespoke development, check my profile

 

Virus Threat Scanner

My Contributions

Basic install answers.

Click here for Contributions / Add Ons.

UK your site.

Site Move.

Basic design info.

 

For links mentioned in old answers that are no longer here follow this link Useful Threads.

 

If this post was useful, click the Like This button over there ======>>>>>.

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

Need help installing add ons/contributions, cleaning a hacked site or a bespoke development, check my profile

 

Virus Threat Scanner

My Contributions

Basic install answers.

Click here for Contributions / Add Ons.

UK your site.

Site Move.

Basic design info.

 

For links mentioned in old answers that are no longer here follow this link Useful Threads.

 

If this post was useful, click the Like This button over there ======>>>>>.

Link to comment
Share on other sites

  • 2 weeks later...

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

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

Support Links:

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

All of My Addons

Get the latest versions of my addons

Recommended SEO Addons

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

Support Links:

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

All of My Addons

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