Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

Database Optimizer


Jack_mcs

Recommended Posts

@@Jack_mcs, I was working on getting this working for my 2.3.3.2 and have it mostly converted to tep_db but have run into problems with the below code in database_optimizer_common.php (already change to tep_db_query). Please keep in mind - I'm not a coder.

 

        if ($config['optimize'] || $config['analyze']) {
           if (mysql_select_db(DB_DATABASE)) {     // Select database and continue only if successful (have permission to)
               $tbl_status = 'SHOW TABLE STATUS FROM ' . DB_DATABASE;         // Statement to select the tables in the currently looped database
               $tbl_result = tep_db_query($tbl_status);         // Query mySQL for the results

 

Let me know if you can provide and assistance... with the MySQL_select_db... Can I use tep_db_connect somehow?

Link to comment
Share on other sites

The code uses the MySQL calls because it is ran from cron and doesn't know about the database connection. It didn't matter before since a DB link wasn't required but now it is. So either a new connection needs to be made or the code changed to be able to use the included functions. You can make the changes to the tep_ functions and it will work locally. Just be sure to check it via cron.

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...
  • 3 months later...
  • 8 months later...

A new version has been uploaded with these changes:

 

- Added code to trim the email address in case it was entered with spaces.
- Added code to the delete old customers logic so that the products notification table is cleared properly.
- Added a new option to clean the products notification table.
- Added code to check if old customers have orders before deleting and skip delete if they do.
- Fixed error in the mysql call in the cron file.
- Fixed some logic errors in the customer delete code.
- Fixed error in the customer old delete code that failed under some situations.
- Replaced all of the mysql calls with standard oscommerce calls so code is compatible with all versions of oscommerce.

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

There's not a command that matches what you wrote in this addon so I can't help much. Please verify you are using this addon. If so, always state what oscommerce version you are using (in any support question in any thread) and how you are seeing the problem (running from admin or cron). If you are getting an error, don't change what it says.

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

I was getting the same error as pmsmiers. Fixed by changing line 153 in admin/includes/modules/database_optimizer_common.php

 

From

$tbl_status = 'SHOW TABLE STATUS FROM ' . DB_DATABASE;

To

$tbl_status = "SHOW TABLE STATUS FROM `" . DB_DATABASE . "`";
Link to comment
Share on other sites

@@Irin Thanks for posting that. I'm not seeing the problem in any of my tests but I can see where that would cause it.

 

My apologies to @@pmsmiers. I misread what you posted which caused me to think the code was not present.

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

Hi Jack,

 

finally had a little bit of time to upgrade to the latest version from 1.3. After running it, I get the following warning:

Warning: mail(/var/log/phpmail.log) [function.mail]: failed to open stream: Permission denied in /home/username/public_html/amin/includes/modules/database_optimizer_common.php on line 185

Any idea please?

Absinthe Original Liquor Store

Link to comment
Share on other sites

That line in the file is where the code sends the email. Since you are not using the standard function, it is failing. It looks like the server is prevented that function from being called. You will need to ask your host about that error. I can't help you setting it up since I don't use 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

  • 3 months later...

Question. I've installed the latest version. I have it set to remove all customers accounts who have not logged in in 5 years (1826 days). It IS removing customers who have not logged in so it is working. However, I do have some customers in there who it has not removed. Here is one:

 

 

 

Account Created: 12/19/2007
Last Modified:
Last Logon: 05/18/2008
Number of Logons: 10

 

The last order was on 12/19/2007. Did I miss something or do something wrong or am I just misunderstanding how this should work? I thought it would remove unattended customer accounts over 5 years and this one is over 7. Did I put the wrong number of days in?

Link to comment
Share on other sites

Customers are deleted if they have not placed an order. If they did place an order and were deleted, then either the order(s) would need to be deleted or it might confuse you if you tried to look up the details for that customer from the order since there would be none.  If you prefer to have the customers deleted even if they have placed an order, you can remove this line in the admin/includes/modules/database_optimizer_common.php file.

if (tep_db_num_rows($orders_query) == 1) continue; //skip customers with orders

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

  • 5 months later...

I have recently transferred my store to php v5.5.29. The database optimizer works fine when ran manually via admin. I'm having issues running the optimizer through cron job. I get the following error via email from my server when the cron runs:

Subject: Cron <...@sl-508-13> php /home/.../public_html/.../admin/database_optimizer_cron.php
 

Status: 500 Internal Server Error
X-Powered-By: PHP/5.5.29
Content-type: text/html
Link to comment
Share on other sites

The Internal Server error can be caused by many things so you will have to find out what the actual failure is before it can be fixed. My guess is that there is a missing file, due to the move. But it could also be related to php 5.5. I don't think that will cause a problem since you say it runs fine manually but I haven't tested with 5.5 so it's possible.

 

For the error, see if there is an error_log file in the admin. If there is, it might show the failure. If not, see if your control panel has an error log and, if so, if the failure shows there. If you still can't find anything, then you will need to ask your host to look it up since it will be recorded in the servers error log.

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

I've enabled the error_reporting in database_optimizer_cron.php and ran it again. Now, I'm seeing the following error on the page:

 

Fatal error: Call to undefined function tep_output_string() in /.../admin/includes/functions/html_output.php on line 18

 

admin/includes/functions/html_output.php, line 18 reads:

$page = tep_output_string($page);
Link to comment
Share on other sites

@@Irin In the admin/database_optimizer_cron.php file, find this line

require('includes/functions/html_output.php');

and add this line above it:

require('includes/functions/general.php');

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

Now, it's giving me some notices on the page:

 

Notice: Use of undefined constant SESSION_FORCE_COOKIE_USE - assumed 'SESSION_FORCE_COOKIE_USE' in /.../admin/includes/functions/html_output.php on line 47

Notice: Use of undefined constant SESSION_FORCE_COOKIE_USE - assumed 'SESSION_FORCE_COOKIE_USE' in /.../admin/includes/functions/html_output.php on line 47

Notice: Use of undefined constant SESSION_FORCE_COOKIE_USE - assumed 'SESSION_FORCE_COOKIE_USE' in /.../admin/includes/functions/html_output.php on line 47

 

Here is the code around line 47 of admin/includes/functions/html_output.php:

// Add the session ID when moving from different HTTP and HTTPS servers, or when SID is defined
  if ( ($add_session_id == true) && (SESSION_FORCE_COOKIE_USE == 'False') ) {
    if (tep_not_null($SID)) {
      $_sid = $SID;
    } elseif ( ( ($request_type == 'NONSSL') && ($connection == 'SSL') && (ENABLE_SSL == true) ) || ( ($request_type == 'SSL') && ($connection == 'NONSSL') ) ) {
      if (HTTP_COOKIE_DOMAIN != HTTPS_COOKIE_DOMAIN) {
        $_sid = tep_session_name() . '=' . tep_session_id();
      }
    }
  }
Link to comment
Share on other sites

In the same file you just changed, find this:

/*************************** OPTIONS AND EDITABLE STRINGS ****************************************/

and change it to

  ini_set('display_errors','0'); 
/*************************** OPTIONS AND EDITABLE STRINGS ****************************************/
Edited by Jack_mcs

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

I don't think that running database_optimizer_cron.php does anything. It doesn't shows any messages on the page, since error reporting is disabled. It only shows blank page and nothing else. How do I check that it works as it should?

Link to comment
Share on other sites

It's not supposed to show anything. When it runs, you should get an email from it letting you know the results. But the code only runs if the days in the settings have passed so you may not get an email if it has already ran until the correct number of days have passed.

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