List:General Discussion« Previous MessageNext Message »
From:SGreen Date:December 27 2005 8:56pm
Subject:Re: upgrading to mysql 5
View as plain text  
"PaginaDeSpud" <webmaster@stripped> wrote on 12/27/2005 03:33:58 
PM:

> hi,
> I've upgraded from mysql 4.1 to mysql 5 and some queries doesn't work. 
It's 
> not explained on mysql changes incompatibilities...
> 
> for example:
> 
<snip>
> FROM yabbse_topics, yabbse_messages, yabbse_messages as m2 LEFT JOIN 
> yabbse_members AS mem ON (mem.ID_MEMBER=yabbse_messages.ID_MEMBER) LEFT 
JOIN 
> yabbse_members AS mem2 ON (mem2.ID_MEMBER=m2.ID_MEMBER) LEFT JOIN 
> yabbse_log_topics AS lt ON (lt.ID_TOPIC=yabbse_topics.ID_TOPIC AND 
> lt.ID_MEMBER=2) LEFT JOIN yabbse_log_mark_read AS lmr ON (lmr.ID_BOARD=3 
AND 
> lmr.ID_MEMBER=2)
> 
> WHERE yabbse_topics.ID_TOPIC IN 
> (38562,39516,66603,24732,67085,59867,43884,68331,68330,56897,68090,
> 68144,68174,68202,68329,68280,68258,68294,68299,68326,68325,67977,
> 67286,55125,67897,65748,68316,68317,68152,68291,68187,68000,68240,
> 68237,66796,68271,67486,57293,68027,67332,67990,68089,68072,42517,
> 68315,63612,66121,68275,68032,68261,68308,67919,68305,68302,68300,
> 68296,40983,68256,68293,68295,68270,68212,68198,45444,67246,68215,
> 68264,68208,68133,67017) 
> AND yabbse_messages.ID_MSG=yabbse_topics.ID_LAST_MSG AND 
> m2.ID_MSG=yabbse_topics.ID_FIRST_MSG ORDER BY t.isSticky DESC, 
> yabbse_messages.posterTime DESC
> 
> ERROR: Unknown column 'yabbse_messages.ID_MEMBER' in 'on clause'
<snip> 

I don't know which incompatible change list you looked at but it is 
definitely on the top of this page:

http://dev.mysql.com/doc/refman/5.0/en/news-5-0-12.html
complete with a link to more information: 
http://dev.mysql.com/doc/refman/5.0/en/join.html

Once you read over that, if you can't find the flaw in your query come 
back to the list and I or someone else will help point it out to you. Your 
query has always been broken, it's just that some of the bugs in the SQL 
engine were eliminated with 5.0.12 so that it evaluates SQL statements 
more according to the specification and your query cannot be evaluated 
according to the updated rules.

It's like having a bad spell-checker in a word processing appliction. You 
could go for months using one spelling of a word and never get flagged for 
it. However if you upgraded the spell-checker it may start flagging you on 
the same word that used to pass muster in the old version. This is a good 
thing because as your query becomes more ANSI compliant, it becomes more 
likely to be used cross-platform and not just on MySQL. 

As an observation: unless you are using the comma-separated form of 
creating an implicit CROSS JOIN, you don't get caught by the tightening of 
the rules. Nobody has posted a question of this same nature to the list 
that only uses explicit JOIN statements. hmmm.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine






Thread
upgrading to mysql 5PaginaDeSpud27 Dec
  • Re: upgrading to mysql 5SGreen27 Dec
  • Re: upgrading to mysql 5Peter Brawley27 Dec
  • Re: upgrading to mysql 5PaginaDeSpud28 Dec