Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

Database Optimizer


Jack_mcs

Recommended Posts

On 5/11/2017 at 8:53 PM, Jack_mcs said:

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.

I think I did something similar by accident. First I ran option A, I think, since it was the default and I didn't change it. So it deleted customers but not orders......Then I realized what happened and switched it to Option B, I think, to delete customers and orders, but 34,000 orders remain. How can I now delete all of these old orders?

Link to comment
Share on other sites

There isn't a way in the current version of this addon to remove orphan orders. That would have to be done via the database. But since orders are spread out over several tables it would not be a simple change.  But short of restoring the customers and address book tables and running it again, that's the only choice I can think of. The latter can cause problems if your customers have changed something in their accounts so it is not a good choice unless you know for sure nothing would have changed.

There are some old addons for this. They may need updating to work with your shop and you should make a backup of the database first but one may work, probably the delete orders by filter.

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...
  • 4 weeks later...

@Jack_mcs Getting the following error when trying to run a Cron job: "mysqli_query() expects parameter 1 to be mysqli, null given in /home/xxx/public_html/admin/includes/functions/database.php on line 53". That line looks like this. Please advise.

$result = mysqli_query($$link, $query) or tep_db_error($query, mysqli_errno($$link), mysqli_error($$link));

 

Link to comment
Share on other sites

Unfortunately, that code doesn't help in this case since it is just the common database code that gets called many times. If you can run the script manually in admin and it works, then it would seem something is wrong with your cron job or maybe the code being called by it. Though no one else has reported this problem so it almost has to be something specific to your server. What php version is your shop using?

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 have DO running on a site with 5.6 and it works correctly so I don't know what it might be in your case. Please try this. Go to admin->Tools->Database Optimizer. Click in the url box and change database_optimizer.php to database_optimizer_cron.php and press enter. You may get warnings but the script should run. If it doesn't run, look for a fatal error message. If it does run, then I've no idea what the problem might be. It would take hands-on troubleshooting to figure it out.

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 only one place in the code where that command is used so that helps a little. But the command is valid so I don't know what it is failing. Do you have the Supertracker addon installed? It shouldn't matter either way but it might help identify the issue. To get around this, in the includes/languages/English/database_optimizer.php file (or whichever language you are using), find and remove this code:

$db_query = tep_db_query("SHOW TABLES LIKE 'supertracker'"); 
if (tep_db_num_rows($db_query) && ! isset($_GET['reset'])) { 
    $optionsArray[] =  array('option' => TEXT_DO_SUPERTRACKER, 'post' => (str_replace(" ", "_", TEXT_DO_SUPERTRACKER)), 'explain' => TEXT_EXPLAIN_SUPERTRACKER);
}

If the same failure occurs for the user tracking addon, also remove that section of code immediately following the above.

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 attempted to install the Queries Debug GUI add-on to troubleshoot this further. I'm seeing a very similar error though.

Warning: mysql_query() expects parameter 2 to be resource, object given in /home/xxx/public_html/includes/functions/database.php on line 53
-
select value from sessions where sesskey = '83a8ba4357f4b3b4fba4ea4c72df7706'
[TEP STOP]

A suggestion I've found is that when mysqli_connect is used for database connection, mysql_ functions should not be used, especially since they are deprecated. However, changing those functions to be in line with mysqli, I'm getting the following errors.

Warning: mysqli_query() expects parameter 1 to be mysqli, string given in /home/xxx/public_html/includes/functions/database.php on line 53
Warning: mysqli_errno() expects exactly 1 parameter, 0 given in /home/xxx/public_html/includes/functions/database.php on line 53
Warning: mysqli_error() expects exactly 1 parameter, 0 given in /home/xxx/public_html/includes/functions/database.php on line 53
-
select value from sessions where sesskey = '83a8ba4357f4b3b4fba4ea4c72df7706'
[TEP STOP]

Can someone please provide insight on this? Many thanks in advance.

Link to comment
Share on other sites

Hi Jack,

I have a question after looking through this.  You have a statement to delete sessions older than a month.  The code I see is

tep_db_query("delete from sessions where expiry > '" . $secondsSessions . "'");     //clear the sessions table of entries greater than time in the settings

But should it be "<" instead of ">"?  Because, the higher number is more current and the lower number is older. 

Where 1501683562 is Wednesday 2nd August 2017 02:19:22 PM  UTC

And 1495000000 is Wednesday 17th May 2017 05:46:40 AM UTC

Am I wrong in my thought?

I'm not really a dog.

Link to comment
Share on other sites

@imusorka, if you are running from cron, it's entirely possible that your PHP and/or MySQL installations are at a different version than your website's. You should run phpinfo() from cron, and compare it to phpinfo() from a browser. If substantially different, you may need to ask your host what "php" command you need in cron to use the same PHP and MySQL as your website does.

Link to comment
Share on other sites

Actually, I thought about this some more and you have to convert to a unix time stamp something like this.  I was just playing around with this when you posted.

  $secondsSessions = 30*86400;
  $today_minus_30 = (time()-(int)$secondsSessions); // Unix timestamp 30 days ago
  tep_db_query("delete from sessions where expiry < '" . $today_minus_30 . "'");

 

I'm not really a dog.

Link to comment
Share on other sites

@MrPhil Those are identical. Odd thing is that another cron job for sitemaps works without any issues.

Having tried to install the KissER debug add-on, we also see the following error: "select value from sessions where sesskey =". No other errors though. This is a common theme now then. Puzzled.

Link to comment
Share on other sites

John W Correct again. The sessions part of the code was the first added for this addon and I never revisited it. It is good to have a fresh look at it. Thanks for pointing out the mistake.

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

Note: at least my case if file session used no need sql sessions and Database Optimizer magic

  define('STORE_SESSIONS', 'file');

Configure Session dir at admin site.

:blink:
osCommerce based shop owner with minimal design and focused on background works. When the less is more.
Email managment with tracking pixel, package managment for shipping, stock management, warehouse managment with bar code reader, parcel shops management on 3000 pickup points without local store.

Link to comment
Share on other sites

@imusorka

What is your session configuration parameter in catalog/includes/configure.php ?

:blink:
osCommerce based shop owner with minimal design and focused on background works. When the less is more.
Email managment with tracking pixel, package managment for shipping, stock management, warehouse managment with bar code reader, parcel shops management on 3000 pickup points without local store.

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