Since upgrade to MySQL 5.0 OsCommerce gives error

[RustyACE]

1054 - Unknown column 'p.products_id' in 'on clause'

select count(*) as total from products_description pd, products p left join manufacturers m on p.manufacturers_id = m.manufacturers_id, products_to_categories p2c left join specials s on p.products_id = s.products_id where p.products_status = '1' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '1' and p2c.categories_id = '74'

[TEP STOP]


This error message started as soon as MySQL was updated to 5.0.

http://www.hoodcleaningsupplies.com

It only occurs if I click on any items in my "Categories" section.

If I click on items listed in the "New Items" it works fine.

If I click on items listed in the "Best Sellers" it works fine.

I've got an older version of OsCommerce (pre v2.2) and it has numerous upgrades.

I've found several solutions from OsCommerce that give huge sections of code that have been apparently changed (although I can't for the life of me figure out what they did differently) and it's supposedly fixed.

It apparently has something to do with the way that OsCommerce requests a Join and a Join Left (which I don't understand).

Basically I just need someone who knows code to take a look at it and fix it.

There's about to be everyone who has an OsCommerce Store having the same problem. My store just happened to be on the first Database changeover.

Please call me at <<number removed>> ASAP so that we can get this fixed.

Thanks,

Russell Clark
<<number removed>>

[Doc C]

This needs to be submitted to Support via email, chat and/or phone. This is a customer to customer forum and not an official PowWeb channel.

[RustyACE]

This has been submitted to Powweb support.

I'm looking for a customer of OsCommerce that has found a work around.

As every database is switched to MySql 5.0, everyone that uses OsCommerce will need the fix that will be suggested.

Personally, I need anyone that knows how to fix this to suggest a solution so that it can be implemented ASAP.

Thanks,

[phlembol]

The latest version of OsCommerce (v3.0) says it is compatible with SQL 5.0. Why are you keeping a very outdated version? That's very risky.

Edit: It could be that I misread this page: http://www.oscommerce.com/community/contributions,1501 Looks like v3 is still in alpha.

But I did find this answer:
Seems like you have not installed the enable/disable categories contribution.

Go to the admin > configuration > recommendations

and turn off "Enable disable categories installed?"

That came from here (http://forums.oscommerce.com/index.php?showtopic=38401&hl=). Quite an old thread but exactly your issue as I read it.

[VetteTech81]

Having same problem, however in my admin, I do not see "reccomendations" under the configuration catagory.. On the phone with Powweb now to see if we can figure it out..

[dmacminn]

Essentially, it is possible to have a site revert to running MySQL Version 4 for a brief period -- but, you will have to complete any upgrades/changes before rolling it through to MySQL Version 5.

This is another case, where you really need to keep your code up to date --- it might seem harsh, but the reality is, that you are responsible for your code and apps -- we aren't going to analyse, fix them or provide patches to get your apps going -- regardless of whether its the change to MySQL Version 5 that causes them to stop working.

Technical Support don't support OSCommerce or any of the other apps -- the only involvement we ever exercise with them is when the Install Central installer breaks or misbehaves... other than that, essentially the product support forum or this forum is the best place to seek advise on resolving the issue.

BTW: none of Support staff receive any training whatsoever in applications or their installation ... honestly, your best resource is going to be other people who are working with the same application or who have wrestled thru the same problem...

So, if you need the account rolled back to MySQL Version 4, you can request that in a ticket (remember, that is a temporary condition only -- we will likely not be offering MySQL Version 4 after the end of this month) to buy you some time...
but, for better or worse, finding the solution to get your apps to run correctly on MySQL Version 5 is, and will remain, your responsibility....

[VetteTech81]

Essentially, it is possible to have a site revert to running MySQL Version 4 for a brief period -- but, you will have to complete any upgrades/changes before rolling it through to MySQL Version 5.

This is another case, where you really need to keep your code up to date --- it might seem harsh, but the reality is, that you are responsible for your code and apps -- we aren't going to analyse, fix them or provide patches to get your apps going -- regardless of whether its the change to MySQL Version 5 that causes them to stop working.

Technical Support don't support OSCommerce or any of the other apps -- the only involvement we ever exercise with them is when the Install Central installer breaks or misbehaves... other than that, essentially the product support forum or this forum is the best place to seek advise on resolving the issue.

BTW: none of Support staff receive any training whatsoever in applications or their installation ... honestly, your best resource is going to be other people who are working with the same application or who have wrestled thru the same problem...

So, if you need the account rolled back to MySQL Version 4, you can request that in a ticket (remember, that is a temporary condition only -- we will likely not be offering MySQL Version 4 after the end of this month) to buy you some time...
but, for better or worse, finding the solution to get your apps to run correctly on MySQL Version 5 is, and will remain, your responsibility....


So this problem is with the code and not the upgrade to Sql 5? I wish someone would have atleast called or e-mailed me back within the 24 hours as promised..

I can understand the unwillingness and that is not powwebs responsibility to support a 3rd party app but it would be nice to know when these things happen if its the code or the upgrade...

Not real happy with the service provided since powweb had switched ownership..
-=Rick

[dmacminn]

Ticket resolution time is typically stated to be 24-48 hours --- (based on a Typoon taking one of the Support Center's offline yesterday, I'd lean toward the 48 hours...)

Nobody is saying that there may not be issues related to MySQL Version 5 introduction .... but, that said, there are likely some issues with OSCommerce working well with MySQL5 in some version incarnations...

The MySQL5 upgrade will go ahead... because it must. That change is reducing the number of circumstances where too many connections are open (a major cause of slowness/stalling) and giving better performance metrics to the NetOps Team --

It's not going to be a picnic ...
some sites and apps are going to have issues ...
but, relative to the total number of sites involved, etc., these are the exception....

And, yes, there will be some issues with MySQL, post-upgrade, as it is adjusted/tuned, etc., so there will be some "turbulence" for a bit....

The MYSQL Version 5 upgrade is not a panacea .. it is another necessary step forward....

[VetteTech81]

Ticket resolution time is typically stated to be 24-48 hours --- (based on a Typoon taking one of the Support Center's offline yesterday, I'd lean toward the 48 hours...)

Nobody is saying that there may not be issues related to MySQL Version 5 introduction .... but, that said, there are likely some issues with OSCommerce working well with MySQL5 in some version incarnations...

The MySQL5 upgrade will go ahead... because it must. That change is reducing the number of circumstances where too many connections are open (a major cause of slowness/stalling) and giving better performance metrics to the NetOps Team --

It's not going to be a picnic ...
some sites and apps are going to have issues ...
but, relative to the total number of sites involved, etc., these are the exception....

And, yes, there will be some issues with MySQL, post-upgrade, as it is adjusted/tuned, etc., so there will be some "turbulence" for a bit....

The MYSQL Version 5 upgrade is not a panacea .. it is another necessary step forward....


Ok so I see in my "ops" that the ticket is closed and resolved.. but noone ever contacted me eitherway to tell me what was going on.. I'll see if we can rework the code to solve the problem but it still would have been nice to hear back from someone..

[dmacminn]

You should have heard from someone -- the ticket will go to resolved first when a resolution is postulated, then some time later you will get the Resolution Contact (this can take some time -- an additional 24 hours or more sometimes particularly with telephone callbacks).

DO make sure that email that you check the email setup as the Admin contact -- that's what the system defaults to sending any contact to (if someone misses a request to be contacted via a different email address).

I do apologize if you were not contacted re the issue, that should not happen -- our system is designed to prevent that from happening ... but, people sometimes make mistakes or assumptions (if you recontacted in the interim, for instance), that you may already be aware of the resolution -- and, that's not in any way an excuse -- just an possible explanation for the failure.

[pulzar]

If you're still looking for a solution with mysql5, look at this:

http://www.oscommerce.com/community/contributions,4654/category,all/search,mysql5

Look at the older zip file... it shows you the two simple changes you have to make to your code to fix the problem. I've just tried it myself and it works.