Jump to content
Jack_mcs

Database Optimizer

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?

Share this post


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

Share this post


Link to post
Share on other sites

Ok, thanks @@Jack_mcs, however, it's probably a little past my skills...

 

It think its working now via the cronjob without any changes, it only seems to fail when run manually via the admin.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

I get this error :

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-t' at line 1

 

SHOW TABLE STATUS FROM db.........

[TEP STOP]

Share this post


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

Share this post


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

Share this post


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

Share this post


Link to post
Share on other sites

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

Share this post


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

Share this post


Link to post
Share on other sites

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?

Share this post


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

Share this post


Link to post
Share on other sites

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

Share this post


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

Share this post


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

Share this post


Link to post
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');

Share this post


Link to post
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();
      }
    }
  }

Share this post


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

Share this post


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

Share this post


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

Share this post


Link to post
Share on other sites

So, I guess I'll need to wait until the next time the module runs to see the results. I'll keep you posted. Thanks for your help.

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

×