Jump to content
Jack_mcs

Database Optimizer

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>

Share this post


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

Share this post


Link to post
Share on other sites

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!

Share this post


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

Share this post


Link to post
Share on other sites

Hi Jack,

I see this at the top of admin/database_optimizer.php TEXT_VERSION_CHECK_UPDATES_UNRELEASEDDatabase Optimizer V 1.5 is the latest version


Absinthe Original Liquor Store

Share this post


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

Share this post


Link to post
Share on other sites

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.
     

Share this post


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

Share this post


Link to post
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?

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

Visit admin->Tools->Database Optimizer and the database changes will be made.

Share this post


Link to post
Share on other sites

Visit admin->Tools->Database Optimizer and the database changes will be made.

Thanks Jack,

 

I have that part, but it looked like there was supposed to be a configuration page where the items in red could be configured?

 

Thanks.

Share this post


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

Share this post


Link to post
Share on other sites

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

Share this post


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

Share this post


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

Share this post


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

Share this post


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

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

×