Description:
Running osCommerce on MySQL 5.0.12 (static gcc compiled version) on SuSe Linux 9.1, any query with LEFT/INNER JOIN would result in error message "1054 - Unknown column 'p.products_id' in 'on clause'".
Example 1: (with alias)
SELECT DISTINCT p.products_id, p.products_image, p.products_tax_class_id, s.status AS specstat, s.specials_new_products_price, p.products_price
FROM products p
LEFT JOIN specials s ON p.products_id = s.products_id, products_to_categories p2c, categories c
LEFT JOIN featured f ON p.products_id = f.products_id
WHERE p.products_id = p2c.products_id
AND p2c.categories_id = c.categories_id
AND c.parent_id = '10001'
AND p.products_status = '1'
AND f.status = '1'
LIMIT 10
Error: #1054 - Unknown column 'p.products_id' in 'on clause'
Example 2: (with full table names)
SELECT DISTINCT products.products_id, products.products_image, products.products_tax_class_id, specials.status AS specstat, specials.specials_new_products_price, products.products_price
FROM products
LEFT JOIN specials ON products.products_id = specials.products_id, products_to_categories, categories
LEFT JOIN featured ON products.products_id = featured.products_id
WHERE products.products_id = products_to_categories.products_id
AND products_to_categories.categories_id = categories.categories_id
AND categories.parent_id = '10001'
AND products.products_status = '1'
AND featured.status = '1'
LIMIT 10
Error: #1054 - Unknown column 'products.products_id' in 'on clause'
Example 3:
SELECT count( DISTINCT p.products_id ) AS total
FROM products p
LEFT JOIN manufacturers m
USING ( manufacturers_id ) , products_description pd
LEFT JOIN specials s ON p.products_id = s.products_id, categories c, products_to_categories p2c
WHERE p.products_status = '1'
AND p.products_id = pd.products_id
AND pd.language_id = '1'
AND p.products_id = p2c.products_id
AND p2c.categories_id = c.categories_id
AND (
(
pd.products_name LIKE '%boss%'
OR p.products_model LIKE '%boss%'
OR m.manufacturers_name LIKE '%boss%'
OR pd.products_description LIKE '%boss%'
)
)
Error: #1054 - Unknown column 'p.products_id' in 'on clause'
Only simple queries like
SELECT count( p.products_id ) AS total
FROM products p
or
SELECT *
FROM products p
OS: SuSe Linux 9.1
XAMPP 1.4.15 for Linux (Apache 2.0.54, PHP 4.4.0) www.apachefriends.org
MySQL: 5.0.12-beta MAX, Linux (x86, glibc-2.2, static (Standard only), gcc) (upgraded from 5.0.11-beta MAX)
Note: Version 5.0.11 runs flawlessly
How to repeat:
- OS: SuSe Linux 9.3 minimum installation
- MySQL: Install version Linux (x86, glibc-2.2, static (Standard only), gcc) Max 5.0.12-beta with InnoDB support
- Download XAMPP 1.4.15 (Apache+PHP) from
http://prdownloads.sourceforge.net/xampp/xampp-linux-1.4.15.tar.gz?download
- Install with
tar xvfz xampp-linux-1.4.15.tar.gz -C /opt
- Tell PHP where to find the 5.0.12 mysql.sock by editing the file
/opt/lampp/etc/php.ini
and edit the line in the [MySQL] section:
mysql.default_socket = /path/where/to/find/mysql.sock
- Run with
/opt/lampp/lampp startapache
- Switch to PHP4 with
/opt/lampp/lampp php4
- Delete content and subfolders from /opt/lampp/htdocs with
rm -R /opt/lampp/htdocs/*
- Download osCommerce from
http://www.oscommerce.com/redirect.php/go,17
- unTAR to /opt/lampp/htdocs with
tar xvfz oscommerce-2.2ms2.tar.gz -C /opt/lampp/htdocs
- Move folder catalog to htdocs with
mv /opt/lampp/htdocs/oscommerce-2.2ms2/catalog/* /opt/lampp/htdocs/
- Start a browser and direct to the computer where XAMPP runs on (eg. localhost). The osCommerce Welcome screen should appear.
- Choose INSTALL and enter appropriate data (localhost / root / (empty) / test // ).
- After successful configuration, click on catalog and perform a product search (eg. 'bla') . SQL query stops with:
1054 - Unknown column 'p.products_id' in 'on clause'
select count(distinct p.products_id) as total from products p left join manufacturers m using(manufacturers_id), products_description pd left join specials s on p.products_id = s.products_id, categories c, products_to_categories p2c where p.products_status = '1' and p.products_id = pd.products_id and pd.language_id = '2' and p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and ((pd.products_name like '%bla%' or p.products_model like '%bla%' or m.manufacturers_name like '%bla%') )
I've tried MyISAM as well as InnoDB database engine. Both show error 1054.
Suggested fix:
N/A
