Error #1054 caused by MySQL 5.0?

[dwsjr]

Our site which had been running fine under MySQL 4.1 is now experiencing errors under MySQL 5.0.18. When I click on "categories" on my site, no problem. Going further and clicking below the category level, I get the following error:

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

select count(p.products_id) 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_retail_prices 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 = '127'

[TEP STOP]

My web host says MySQL 5.0 has changed the "JOIN" syntax. Is this true and has anyone come up with a fix for this?

[dwsjr]

Just to provide a little more information....the #1054 error seems to be generated by the /catalog/index.php file (or the related template files). If I access the product pages using the "search" box, the products come up no problem. There does not seem to be a problem with the product_info.php.

[dwsjr]

Download and install osCMax-2.0RC2-update051112 patch. Everything works well again...

[lem]

Installing the patch, none of the lines in the files are where they say they are. Half of the lines in the files that are needed to change dont seem to exist. I'm still having a hard time getting it to work.

[michael_s]

Read the README-1ST.txt file...

It is not named that for kicks you know... You are supposed to read it first. It contains:

------------------------------------------------------------------------------
INSTALL DIRECTIONS - for non-modified shops (template excluded)
------------------------------------------------------------------------------

BACKUP!!! BACKUP!!! BACKUP!!! BACKUP!!!

Once you have backup BOTH your store and database....

Copy all files from 'catalog' into your store. (There is only the changed files included)

Use the update-20051112.sql file or modify manually in phpmyadmin with the following queries:

ALTER TABLE whos_online MODIFY COLUMN last_page_url VARCHAR(255) NOT NULL;
ALTER TABLE customers MODIFY COLUMN customers_default_address_id INTEGER;
ALTER TABLE customers_basket MODIFY COLUMN final_price DECIMAL(15,4);


Test! Test! Test! Test!

Done!

------------------------------------------------------------------------------
INSTALL DIRECTIONS - for MODIFIED shops (more than just the template changes)
------------------------------------------------------------------------------

BACKUP!!! BACKUP!!! BACKUP!!! BACKUP!!!

Once you have backup BOTH your store and database....

DO NOT COPY OVER YOUR EXISITNG FILES
Compare all files from 'catalog' with your modifed store using a file diff like Beyond Compare or WinMerge...
(There is only the changed files included)

Use the update-20051112.sql file or modify manually in phpmyadmin with the following queries:

ALTER TABLE whos_online MODIFY COLUMN last_page_url VARCHAR(255) NOT NULL;
ALTER TABLE customers MODIFY COLUMN customers_default_address_id INTEGER;
ALTER TABLE customers_basket MODIFY COLUMN final_price DECIMAL(15,4);


Test! Test! Test! Test!

Done!