mariovaldez.net

MV.net forums
It is currently 14 Jul 2016, 00:54

All times are UTC - 7 hours




Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 9 posts ] 
Author Message
 Post subject: Support for MySQL 5
PostPosted: 12 Nov 2005, 22:26 

Joined: 12 Nov 2005, 22:18
Posts: 1
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


Report this post
Top
 Profile  
Reply with quote  
 Post subject: Support for MySQL 5
PostPosted: 20 Nov 2005, 21:07 
User avatar

Joined: 06 Mar 2003, 03:21
Posts: 447
Location: Monterrey, NL, Mexico
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.


Report this post
Top
 Profile  
Reply with quote  
 Post subject: Mysql 5 bug
PostPosted: 12 Jun 2006, 16:33 
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.


Report this post
Top
  
Reply with quote  
 Post subject: SQL statement fix tested
PostPosted: 12 Jun 2006, 20:02 
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


Report this post
Top
  
Reply with quote  
 Post subject: SQL statements...
PostPosted: 08 Jul 2006, 14:24 
User avatar

Joined: 06 Mar 2003, 03:21
Posts: 447
Location: Monterrey, NL, Mexico
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.


Report this post
Top
 Profile  
Reply with quote  
PostPosted: 08 Jul 2006, 14:55 
User avatar

Joined: 06 Mar 2003, 03:21
Posts: 447
Location: Monterrey, NL, Mexico
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... :oops:


Thanks,

Mario A. Valdez-Ramirez.


Report this post
Top
 Profile  
Reply with quote  
 Post subject:
PostPosted: 23 Sep 2006, 11:04 

Joined: 23 Sep 2006, 10:58
Posts: 7
Mario, el problema me sigue sucediendo y tengo la 0.1.2.450. Me podrias comentar que puedo hacer?
Un saludo
Angeles


Report this post
Top
 Profile  
Reply with quote  
PostPosted: 23 Sep 2006, 11:27 
User avatar

Joined: 06 Mar 2003, 03:21
Posts: 447
Location: Monterrey, NL, Mexico
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.


Report this post
Top
 Profile  
Reply with quote  
 Post subject:
PostPosted: 23 Sep 2006, 11:43 

Joined: 23 Sep 2006, 10:58
Posts: 7
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.


Report this post
Top
 Profile  
Reply with quote  
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 9 posts ] 

All times are UTC - 7 hours


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Search for:
Jump to:  
Powered by phpBB® Forum Software © phpBB Group