mariovaldez.net

Due to changes in MySQL 5 the syntax of the SQL statements (probably the initial selects in main.pas) need to be adjusted.

Please see the changes in catalog/index.php and the changelog of the latest osC release (from yesterday) for details and for hints about fixing the problem. There was also another change in catalog/advanced_search_result.php because of MySQL 5 incompatibilities.

In the current state it is not possible to fetch the products data with oscpm.

Regards,
Gerrit

Gerrit:

> Due to changes in MySQL 5 the syntax of the
> SQL statements (probably the initial selects in
> main.pas) need to be adjusted. Please see the
> changes in catalog/index.php and the changelog
> of the latest osC release (from yesterday) for
> details and for hints about fixing the problem.
> There was also another change in catalog/advanced_
> search_result.php because of MySQL 5 incompatibilities.
> In the current state it is not possible to fetch the
> products data with oscpm.

Thanks a lot for the bug report. I'll setup a MySQL 5 server to test, debug and fix the problem.


Regard,

Mario A. Valdez-Ramirez.

I thought that this info may be of some help in fixing this. I used this code to fix my search functions within oscommerce. Do we know when this might be fixed as I would love to get this working? Thanks, Ron

[8 Oct 2005 12:12] Sergei Golubchik

This is a change that was made in 5.0.15 to make MySQL more compliant with the
standard.
According to the SQL:2003

<from clause> ::= FROM <table reference list>
<table reference list> ::=
<table reference> [ { <comma> <table reference> }... ]
<table reference> ::=
<table factor>
| <joined table>
<joined table> ::=
<cross join>
| <qualified join>
| <natural join>
...

Thus when you write

... FROM t1 , t2 LEFT JOIN t3 ON (expr)

it is parsed as

(1) ... FROM t1 , (t2 LEFT JOIN t3 ON (expr))

and not as

(2) ... FROM (t1 , t2) LEFT JOIN t3 ON (expr)

so, from expr you can only refer to columns of t2 and t3 - operands of the
join.
Workaround - to put parentheses explicitly as in (2). Then you can refer to t1
columns from expr.

Unfortunately, this change is not properly documented in the manual, it will be
fixed.

--------------------

So the solution is to put proper paranthesis around the table joins.

I took the sql statement from my sql log which received the error and made a couple minor modifications (based on my earlier post) and tested it in phpmyadmin and the sql statements worked. See below for the before and after sql statements.

Note, the only differences are the parenphises around the from clause and the on clause. With this insight I would think that this fix should be fairly simple. Thanks in advance.

FROM (products, products_description, products_to_categories)

LEFT JOIN specials ON ( products.products_id = specials.products_id )


Before

select products.products_id, products.products_quantity, products.products_model, products.products_price, products.products_weight, products.manufacturers_id, products_description.products_name, products.products_status, products_description.products_description, products_description.products_url, products.products_image, specials.specials_new_products_price, products_description.language_id, products.products_tax_class_id, products_description.products_viewed, DATE_FORMAT(products.products_date_available, "%Y-%m-%d %H:%i:%S"), DATE_FORMAT(products.products_date_added, "%Y-%m-%d %H:%i:%S"), DATE_FORMAT(products.products_last_modified, "%Y-%m-%d %H:%i:%S") from products, products_description, products_to_categories left join specials ON products.products_id = specials.products_id where products.products_id=products_description.products_id and products_to_categories.products_id=products.products_id and products_to_categories.categories_id=21 order by products_description.products_name

After

SELECT products.products_id, products.products_quantity, products.products_model, products.products_price, products.products_weight, products.manufacturers_id, products_description.products_name, products.products_status, products_description.products_description, products_description.products_url, products.products_image, specials.specials_new_products_price, products_description.language_id, products.products_tax_class_id, products_description.products_viewed, DATE_FORMAT( products.products_date_available, "%Y-%m-%d %H:%i:%S" ) , DATE_FORMAT( products.products_date_added, "%Y-%m-%d %H:%i:%S" ) , DATE_FORMAT( products.products_last_modified, "%Y-%m-%d %H:%i:%S" )

FROM (products, products_description, products_to_categories)

LEFT JOIN specials ON ( products.products_id = specials.products_id )

WHERE products.products_id = products_description.products_id
AND products_to_categories.products_id = products.products_id
AND products_to_categories.categories_id =21
ORDER BY products_description.products_name
LIMIT 0 , 30

RonB:

Hi. Thanks a lot for the suggestions. However, I fixed this problem since January (see Bug #295, MySQL 5 compatibility). At least I think I solved it. Are you having problems with MySQL 5? Please let me know.

Again, I really appreciate your effort.


Regards,

Mario A. Valdez-Ramirez.

RonB:

> I fixed this problem since January...


My fault... I solved the problem, but that version was never announced! If you are user of the application, please download and test the following file:

http://www.mariovaldez.net/software/osc ... .2.450.zip

or

http://www.mariovaldez.net/software/osc ... _setup.exe


My mistake, I never announced the fixed package...


Thanks,

Mario A. Valdez-Ramirez.

Mario, el problema me sigue sucediendo y tengo la 0.1.2.450. Me podrias comentar que puedo hacer?
Un saludo
Angeles

Mico:

> Mario, el problema me sigue sucediendo y
> tengo la 0.1.2.450. Me podrias comentar
> que puedo hacer? Un saludo Angeles

¿Podrías probar la versión beta? La puedes descargar del siguiente URL:
May you try the beta version? You can download it from:

http://www.mariovaldez.net/software/osc ... _setup.exe

(Esta versión no está disponible en SourceForge / This version is not available at SourceForge).


Avísame si sigues teniendo problemas.
Let me know if you still have problems.


Saludos,
Regards,

Mario A. Valdez-Ramirez.

Si esta me conecta, no me da error pero no sale la BD. Segun dices no permite la conexion directa y yo tengo la web en mi ordenador con WAMP.