Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

Main page not loading


naschell81

Recommended Posts

I have been having an issue with my website for almost two weeks now. Over that time I have been going back and forth with the web host thinking the issue was on their end, since they migrated my data to a new server. However they are claiming it is not an issue on their end. Needless to say I am at the end of my proverbial rope as it is the holidays and my site has been down for far to long. Below is what is displayed when people visit the main page to my site, however it only appears to be that page as direct links to products still work.

 

3065 - Expression #1 of ORDER BY clause is not in SELECT list, references column 'qrgxkhuc_osc2.p.products_ordered' which is not in SELECT list; this is incompatible with DISTINCT

select distinct p.products_id, pd.products_name from products p, products_description pd where p.products_status = '1' and p.products_ordered > 0 and p.products_id = pd.products_id and pd.language_id = '1' order by p.products_ordered desc, pd.products_name limit 10

[TEP STOP]

 

Link to comment
Share on other sites

I would suspect that in moving you have probably been moved to a new db version which is conflicting with your old data! Find out what version db you were running on on your old server and what is beeing used now! Then it's a case of fixing the conflict. Also chech if your php version has chnaged as it too may have issue if it's not compatiable with your osc code.

 

https://github.com/doctrine/doctrine2/issues/5622

 

Link to comment
Share on other sites

And exactly what version of osCommerce? If you're running something old, and your new server has a higher level of PHP or MySQL, that could do you in.

Also, can you check the files against a known-good backup, to see if anything was corrupted during the move?

Link to comment
Share on other sites

23 hours ago, JcMagpie said:

I would suspect that in moving you have probably been moved to a new db version which is conflicting with your old data! Find out what version db you were running on on your old server and what is beeing used now! Then it's a case of fixing the conflict. Also chech if your php version has chnaged as it too may have issue if it's not compatiable with your osc code.

 

https://github.com/doctrine/doctrine2/issues/5622

I'll Check both those links out and see if I can make any heads or tails of whats going on.

Link to comment
Share on other sites

20 hours ago, MrPhil said:

And exactly what version of osCommerce? If you're running something old, and your new server has a higher level of PHP or MySQL, that could do you in.

Also, can you check the files against a known-good backup, to see if anything was corrupted during the move?

I;m using v2.3.4. Unfortunately I don't know when I last did a backup.

Link to comment
Share on other sites

20 minutes ago, naschell81 said:

I'll Check both those links out and see if I can make any heads or tails of whats going on.

find the db query causing the problem and you will find it starts like this, make this simple edit , remove the "distinct"

$best_sellers_query = tep_db_query("select distinct p.products_id, pd.products_name from " . TABLE_PRODUCTS

it should now start like this,

$best_sellers_query = tep_db_query("select p.products_id, pd.products_name from " . TABLE_PRODUCTS

That should sort out the error you may have to make this change on more than one db query. 

May sure you backup just incase it all goes tits up!

v2.3.4 😊 you may want to consider upgrading to the CE BS version will save you a lot time fixing php compatability and db issues.

 

Link to comment
Share on other sites

This query is exact the same in the actual 2.3.4.1 BS CE bestsellers box. So it is not a store version problem, but a problem of the servers database settings (I guess this point). In my test setup with PHP 7.2.9 and 2.3.4.1 CE FROZEN I do not get any error with exact the same query.

@JcMagpie, sorry your fix is incorrect. It can produce duplicated results.

The correct fix is to add all columns used in the "order by" expression to the select list at the beginning:

@naschell81  in: includes/modules/boxes/bm_bestsellers.php

 

Find:

      if (isset($current_category_id) && ($current_category_id > 0)) {
        $best_sellers_query = tep_db_query("select distinct p.products_id, pd.products_name from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c, " . TABLE_CATEGORIES . " c where p.products_status = '1' and p.products_ordered > 0 and p.products_id = pd.products_id and pd.language_id = '" . (int)$languages_id . "' and p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and '" . (int)$current_category_id . "' in (c.categories_id, c.parent_id) order by p.products_ordered desc, pd.products_name limit " . MAX_DISPLAY_BESTSELLERS);
      } else {
        $best_sellers_query = tep_db_query("select distinct p.products_id, pd.products_name from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd where p.products_status = '1' and p.products_ordered > 0 and p.products_id = pd.products_id and pd.language_id = '" . (int)$languages_id . "' order by p.products_ordered desc, pd.products_name limit " . MAX_DISPLAY_BESTSELLERS);
      }

and change to:

      if (isset($current_category_id) && ($current_category_id > 0)) {
        $best_sellers_query = tep_db_query("select distinct p.products_id, p.products_ordered, pd.products_name from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c, " . TABLE_CATEGORIES . " c where p.products_status = '1' and p.products_ordered > 0 and p.products_id = pd.products_id and pd.language_id = '" . (int)$languages_id . "' and p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and '" . (int)$current_category_id . "' in (c.categories_id, c.parent_id) order by p.products_ordered desc, pd.products_name limit " . MAX_DISPLAY_BESTSELLERS);
      } else {
        $best_sellers_query = tep_db_query("select distinct p.products_id, p.products_ordered, pd.products_name from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd where p.products_status = '1' and p.products_ordered > 0 and p.products_id = pd.products_id and pd.language_id = '" . (int)$languages_id . "' order by p.products_ordered desc, pd.products_name limit " . MAX_DISPLAY_BESTSELLERS);
      }

 

Otherwise I agree with the above posted suggestion to update your store to the actual 2.3.4.1 CE responsive version. Meanwhile you can report this error to your hosting and try to get it solved by them using a different PHP/SQL version which doesn't require these modifications. You may get more errors produced by other queries and may need to apply many fixes to get your old store running error free.

Best regards

Rainer

Link to comment
Share on other sites

Just did a fast check and found the same problem in the queries used in:

- includes/modules/boxes/bm_history.php
- includes/module/new_products.php

So maybe this should be considered a compatibility issue with newer/stricter sql versions/settings and should be fixed in 2.3.4.1 CE EDGE/FROZEN.

Link to comment
Share on other sites

1 hour ago, raiwa said:

This query is exact the same in the actual 2.3.4.1 BS CE bestsellers box. So it is not a store version problem, but a problem of the servers database settings (I guess this point). In my test setup with PHP 7.2.9 and 2.3.4.1 CE FROZEN I do not get any error with exact the same query.

@JcMagpie, sorry your fix is incorrect. It can produce duplicated results.

The correct fix is to add all columns used in the "order by" expression to the select list at the beginning:

@naschell81  in: includes/modules/boxes/bm_bestsellers.php

 

Find:


      if (isset($current_category_id) && ($current_category_id > 0)) {
        $best_sellers_query = tep_db_query("select distinct p.products_id, pd.products_name from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c, " . TABLE_CATEGORIES . " c where p.products_status = '1' and p.products_ordered > 0 and p.products_id = pd.products_id and pd.language_id = '" . (int)$languages_id . "' and p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and '" . (int)$current_category_id . "' in (c.categories_id, c.parent_id) order by p.products_ordered desc, pd.products_name limit " . MAX_DISPLAY_BESTSELLERS);
      } else {
        $best_sellers_query = tep_db_query("select distinct p.products_id, pd.products_name from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd where p.products_status = '1' and p.products_ordered > 0 and p.products_id = pd.products_id and pd.language_id = '" . (int)$languages_id . "' order by p.products_ordered desc, pd.products_name limit " . MAX_DISPLAY_BESTSELLERS);
      }

and change to:


      if (isset($current_category_id) && ($current_category_id > 0)) {
        $best_sellers_query = tep_db_query("select distinct p.products_id, p.products_ordered, pd.products_name from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c, " . TABLE_CATEGORIES . " c where p.products_status = '1' and p.products_ordered > 0 and p.products_id = pd.products_id and pd.language_id = '" . (int)$languages_id . "' and p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and '" . (int)$current_category_id . "' in (c.categories_id, c.parent_id) order by p.products_ordered desc, pd.products_name limit " . MAX_DISPLAY_BESTSELLERS);
      } else {
        $best_sellers_query = tep_db_query("select distinct p.products_id, p.products_ordered, pd.products_name from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd where p.products_status = '1' and p.products_ordered > 0 and p.products_id = pd.products_id and pd.language_id = '" . (int)$languages_id . "' order by p.products_ordered desc, pd.products_name limit " . MAX_DISPLAY_BESTSELLERS);
      }

 

Otherwise I agree with the above posted suggestion to update your store to the actual 2.3.4.1 CE responsive version. Meanwhile you can report this error to your hosting and try to get it solved by them using a different PHP/SQL version which doesn't require these modifications. You may get more errors produced by other queries and may need to apply many fixes to get your old store running error free.

Best regards

Rainer

This was not a fix! it was just an example of what to look for,  I have no idea which actual tep_db_query is causing the problem! I think I made that clear with the "tits up warning" statment. 😊

 

Link to comment
Share on other sites

2 hours ago, JcMagpie said:

This was not a fix! it was just an example of what to look for,  I have no idea which actual tep_db_query is causing the problem! I think I made that clear with the "tits up warning" statment. 😊

I apologize, I'm not a native english speaker and did not know the expression "tits up" . I Googled now for it, thanks!

When I was reading this:

16 hours ago, JcMagpie said:

find the db query causing the problem and you will find it starts like this, make this simple edit , remove the "distinct"

$best_sellers_query = tep_db_query("select distinct p.products_id, pd.products_name from " . TABLE_PRODUCTS

it should now start like this,

$best_sellers_query = tep_db_query("select p.products_id, pd.products_name from " . TABLE_PRODUCTS

I understood that it is a suggestion how to fix the error. Again, I'm not a native english speaker, but I'm ready to learn 😉

Link to comment
Share on other sites

Just found that it has been solved in 2.3.4.1 (standard and BS CE) in another way :

 

Link to comment
Share on other sites

The change in MySQl v5.7 is to bring it inline with how other db's work! It's discused in some detail in a number of places and most came to the conclusion the code should be fixed rather than use workrounds.

https://github.com/doctrine/doctrine2/issues/5622

https://stackoverflow.com/questions/36829911/how-to-resolve-order-by-clause-is-not-in-select-list-caused-mysql-5-7-with-sel

https://github.com/Piwigo/Piwigo/issues/376

 

Link to comment
Share on other sites

This has been implemented by @Harald Ponce de Leon in the 2.3.4.1 hotfix update and has been discussed in CE BS EDGE by @burt and @BrockleyJohn:

https://github.com/gburton/Responsive-osCommerce/issues/541

So, I have to think that it is ok. But you can always open an issue in GitHub:

https://github.com/gburton/Responsive-osCommerce/issues

 

Link to comment
Share on other sites

On 12/15/2018 at 3:45 AM, raiwa said:

This query is exact the same in the actual 2.3.4.1 BS CE bestsellers box. So it is not a store version problem, but a problem of the servers database settings (I guess this point). In my test setup with PHP 7.2.9 and 2.3.4.1 CE FROZEN I do not get any error with exact the same query.

@JcMagpie, sorry your fix is incorrect. It can produce duplicated results.

The correct fix is to add all columns used in the "order by" expression to the select list at the beginning:

@naschell81  in: includes/modules/boxes/bm_bestsellers.php

 

Find:


      if (isset($current_category_id) && ($current_category_id > 0)) {
        $best_sellers_query = tep_db_query("select distinct p.products_id, pd.products_name from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c, " . TABLE_CATEGORIES . " c where p.products_status = '1' and p.products_ordered > 0 and p.products_id = pd.products_id and pd.language_id = '" . (int)$languages_id . "' and p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and '" . (int)$current_category_id . "' in (c.categories_id, c.parent_id) order by p.products_ordered desc, pd.products_name limit " . MAX_DISPLAY_BESTSELLERS);
      } else {
        $best_sellers_query = tep_db_query("select distinct p.products_id, pd.products_name from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd where p.products_status = '1' and p.products_ordered > 0 and p.products_id = pd.products_id and pd.language_id = '" . (int)$languages_id . "' order by p.products_ordered desc, pd.products_name limit " . MAX_DISPLAY_BESTSELLERS);
      }

and change to:


      if (isset($current_category_id) && ($current_category_id > 0)) {
        $best_sellers_query = tep_db_query("select distinct p.products_id, p.products_ordered, pd.products_name from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c, " . TABLE_CATEGORIES . " c where p.products_status = '1' and p.products_ordered > 0 and p.products_id = pd.products_id and pd.language_id = '" . (int)$languages_id . "' and p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and '" . (int)$current_category_id . "' in (c.categories_id, c.parent_id) order by p.products_ordered desc, pd.products_name limit " . MAX_DISPLAY_BESTSELLERS);
      } else {
        $best_sellers_query = tep_db_query("select distinct p.products_id, p.products_ordered, pd.products_name from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd where p.products_status = '1' and p.products_ordered > 0 and p.products_id = pd.products_id and pd.language_id = '" . (int)$languages_id . "' order by p.products_ordered desc, pd.products_name limit " . MAX_DISPLAY_BESTSELLERS);
      }

 

Otherwise I agree with the above posted suggestion to update your store to the actual 2.3.4.1 CE responsive version. Meanwhile you can report this error to your hosting and try to get it solved by them using a different PHP/SQL version which doesn't require these modifications. You may get more errors produced by other queries and may need to apply many fixes to get your old store running error free.

Best regards

Rainer

Tried that, sadly it did not work. I just cant fathom why this even happened. And unfortunately I am getting more and more discouraged as the days pile up since I ma loosing out on sales.

Link to comment
Share on other sites

You could try this:

Otherwise it would be helpful to tell us which file you changed in which way (there are several places which can produce the error) and if you get exact the same error message. If the error is different, post it again.

Link to comment
Share on other sites

so your host claims he did not make any changes! You also say you made no changes?

Code will not chnage on it's own. So what happened?

1) Host has made changes but simply has no idea which affected your site! Push the host to tell you which verion of PHP and SQL you are running on and post here.

2) You have some how corrupted db. Simply repair your db and see if it fixes problem, if not then restore from good db back-up

3) If still broken then restore website from known good back-up. Your host should be doing backups.

 

 

Link to comment
Share on other sites

It's certainly possible that your host upgraded MySQL to a more recent version that requires you to put all referenced fields (like products_ordered) in the SELECT list. Often one host's hand doesn't know what the other is doing, and word of system upgrades doesn't filter down to the support desk. You can run the following PHP script:

<?php phpinfo(); ?>

and see what it tells you you're running for PHP and MySQL levels. If you have no idea what you were running before, this may not be of much help, unless your host can confirm that they upgraded recently.

If you did nothing, and your host swears up and down that they haven't touched anything (yeah, right!),  that leaves only two alternatives: your server had a hardware failure that neatly snipped out a piece of code (very unusual), or a hacker has been messing around. For the latter situation, get a directory listing of your site and look for osC files with a recent "last modified" date that no one can explain. Then you compare those files against a known good backup (...oh wait, you don't keep backups, do you?).

As you're running very old osC 2.3.4, it's quite likely that a recent system PHP/MySQL upgrade has broken something. I don't think it will run past PHP 5.4 (and the matching MySQL level, whatever that is). The current osC 2.3.4.1BS Frozen (or Edge), a.k.a. "CE", will handle up to PHP 7.1. For any of us to advise you to upgrade to it, we need to know what PHP and MySQL you're running on.

Link to comment
Share on other sites

  • 2 months later...

WHM/Cpanel just updated version 78 to the release channel.  One of the things the Security email suggests is upgrading MySQL to 5.7 even though 5.6 is an option.  So, I installed MySQL 5.7 on my local machine to test, however it didn't have "ONLY_FULL_GROUP_BY"  set in the sql mode.  In the interest of learning I enabled it to see what fails.  With a little reading in the " MySQL 5.7 Reference Manual "  I've been able to correct the queries that triggered an error.   I do find it really difficult when the queries run for thousands of characters off the page.  So, for me I format the query to see it better.  Like this one from bm_order_history where the error was o.date_purchased wasn't included in the group by.  Seems like it would be best to correct the queries rather than turning off ONLY_FULL_GROUP_BY.

        $orders_query = tep_db_query("
          SELECT DISTINCT 
              op.products_id, 
              o.date_purchased
          FROM
              orders o,
              orders_products op,
              products p
          WHERE
              o.customers_id = '" . (int)$customer_id . "' AND o.orders_id = op.orders_id AND op.products_id = p.products_id AND p.products_status = '1'
          GROUP BY
              products_id,
              date_purchased
          ORDER BY
              o.date_purchased
          DESC
          LIMIT " . MAX_DISPLAY_PRODUCTS_IN_ORDER_HISTORY_BOX);

 

I'm not really a dog.

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...