Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

Database Optimizer


Jack_mcs

Recommended Posts

I'm getting the following email from /admin/database_optimizer_cron.php:

X-Powered-By: PHP/5.5.29
Content-type: text/html

<font color="#000000"><strong>1146 - Table 'shop.TABLE_USER_TRACKING' doesn't exist<br /><br />delete from TABLE_USER_TRACKING where time_entry > 259200<br /><br /><small><font color="#ff0000">[TEP STOP]</font></small><br /><br /></strong></font>

Link to comment
Share on other sites

It looks like you told it to clear the User Tracking tables but you don't have User Tracking enabled or, at least, not installed correctly.

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

  • 1 month later...

Yes, you're right, I don't have the User Tracking table. Is "User Tracking" module comes with osc by default or it's a separate contribution? If I don't have this contribution installed, how can I disable the module from trying to clear that table?

 

Thank you!

Link to comment
Share on other sites

A new version has been uploaded with these changes:

  • Added option to remove orphan address book entries.
  • Changed code to show tables command to remove error for some servers (found by member Irin).
  • Changed code in database_cron.php to load in the general functions.
  • Changed how database changes are made to make installation and updating easier.
  • Removed redundant jquery statements.
  • Simplified the installation procedure.

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

The version checker was changed to allow better checking of the versions and I forgot to include those changed files in the package. I will get that done soon.

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

  • 9 months later...
  • 2 months later...

A new version has been uploaded with these changes:

 

  • Added an option to removed products that are not in the products_to_categories table.
  • Added an option to clear the SuperTraker table.
  • Added an option to delete orders when customers are deleted.
  • Added a checkbox to allow checking all options with one click.
     

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

@@Jack_mcs

 

From the new package, when I tick the box for Remove Orphan Products, I get this error:

 

1054 - Unknown column 'orphan_products_last_update' in 'field list'

update database_optimizer set orphan_products_last_update = now()

[TEP STOP]

 

The "orphan_products_last_update" needs to be added to the database_optimizer table. All of the others work fine.

 

Take care

Bill

Link to comment
Share on other sites

If this is an update from a previous version, did you use the reset option as mentioned in the update file? If it is a new install, did you go to admin->Tools->Database Optimizer?

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

  • 3 weeks later...

@@vmn  That is an error due to the Version Checker addon not being installed or not having the definition, if it is installed. I thought I had updated that but I see I didn't. I have done so now so if you install the latest version of Version Checker (it is just an upload to install) then the problem will be fixed.

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

  • 3 weeks later...

Great addon, but am I missing something?  In the latest version, the instructions say you can go to admin->configuration->database optimizer, but that entry does not seem to be there.  I have read over the install instructions again and I can't find where that entry was coded.  I had very little sleep last night, so I might just be being stupid though :)

 

Thanks

Link to comment
Share on other sites

When you visit that page, the database is checked to see if the changes have been made. If not, it adds them. So you should see the settings in admin->Configuration->Database Optimizer, or maybe some other location if you are using a template that changes the default method. If you still don't see the settings, go back to that page and change the url to include ?reset and that will force a reload. So the url would be something like ...com/admin/database_optimizer.php?reset

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

  • 2 months later...

Hey Jack,

 

Thanks for all your input and contributions to OSC over the years. Maybe I'm misunderstanding one of the features to remove old customers and old orders. But, none of my customers are being removed. In Configuration -> Database Optimizer, we have Truncate Customers Old set to 365 days and Truncate Customer Orders Opt B set but old customers and orders (from 2011) still remain. The other functions seem to run and we get an email "Database has been optimized" with a "Space Recovered: 34.82 MB" the first time. 

 

I found one error in the code admin/includes/modules/database_optimizer_common.php on line 41

"case 'Oot A': break; //delete customers and leave orders"

where I believe it should read

"case 'Opt A': break; //delete customers and leave orders"

 

But correcting that made no change with the old orders after running the Optimizer again.

 

Thoughts? Do you need more details to diagnose?

 

- Thanks!

Cary

Link to comment
Share on other sites

@@Cary I can't think of a reason why the items are not being deleted. If you run it manually in the admin->Tools, do you see a message that says, "Customers (old) tables were trimmed."?  Also, if the "Optimize Database Period" option isn't set to 1, try that. I don't think it will cause this problem but it can cause some updates not to be seen.

 

Thank you for posting the mistake. It is a silly typo that would have been difficult to see.

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

Jack,

 

I have the "Optimize Database Period" and "Analyze Database Period" to "1". Checked ALL option and selected "update" and below was the response. 

 

Store database has been optimized.

Customers tables were trimmed.
Customers (old) tables were trimmed.
Products Notifications table was cleaned. ( Removed 1 record: 0product: 1 )
Credit Card data was removed from the orders table.
Orphan entries were removed from the address book table.
Orphan entries were removed from the products table.
Sessions table was trimmed.
Database was analyzed.
Database was optimized.
Initial size was 114835995.
Final, optimized, size is 114637174.
Space Recovered: 194.16 KB

 

So, I still have +42k orders from as far back as 2010. I feel like I'm missing something but "Customers (old) tables were trimmed" is shown.

 

Let me know what you think when time permits.

 

Thanks!

Cary

Link to comment
Share on other sites

Those orders would probably be considered orphaned at this point. The code will delete orders that are attached to a customer that is being deleted. But if the customer for those orders doesn't exist, they won't get deleted. Take a look at one of the orders in admin, copy the email address or last name (something unique) and then go to the customers section and search for it. If it doesn't show a customer then that is what the problem is.

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

  • 3 weeks later...

Hi,

I just updated from Database Optimizer V 1.4 to Database Optimizer V 1.6 . Now I'm getting the following email from /admin/database_optimizer_cron.php:

 

Warning: mysqli_query() expects parameter 1 to be mysqli, null given in /public_html/admin/includes/functions/database.php on line 53

SHOW TABLES LIKE 'supertracker'

Please help.

Thank you,

Nate

Link to comment
Share on other sites

Addresses are stored in the address_book table and the only time this addon deletes entries from it is when the customer is deleted. So I don't see how the problem you describe could cause it.

As for what it does, the explanation of each option is in the Instructions popup. I don't know how to better explain what it does than what is there. But if something isn't clear, please post it here so I can address it. 

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

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