From: Peter Brawley
Date: December 27 2005 9:29pm
Subject: Re: upgrading to mysql 5
List-Archive: http://lists.mysql.com/mysql/193246
Message-Id: <43B1B230.2000701@earthlink.net>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="=======AVGMAIL-43B1B2341EEA======="

--=======AVGMAIL-43B1B2341EEA=======
Content-Type: multipart/alternative; boundary=------------060504070102020609030607

--------------060504070102020609030607
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

/>I've upgraded from mysql 4.1 to mysql 5 and some queries
 >doesn't work. It's not explained on mysql changes incompatibilities... /

It is: see the first change item, marked 'incompatible change', at 
http://dev.mysql.com/doc/refman/5.0/en/news-5-0-12.html. We can no 
longer get away with syntactically loose (SQL2003-incompatible) 
combinations of commas and JOIN clauses.

PB

----

PaginaDeSpud wrote:

> 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:
>
> SELECT yabbse_topics.ID_LAST_MSG, yabbse_topics.ID_TOPIC, 
> yabbse_topics.numReplies, yabbse_topics.locked, 
> yabbse_messages.posterName, yabbse_messages.ID_MEMBER, 
> IFNULL(mem.realName, yabbse_messages.posterName) AS posterDisplayName, 
> yabbse_topics.numViews, yabbse_messages.posterTime, 
> yabbse_messages.modifiedTime, yabbse_topics.ID_FIRST_MSG, 
> yabbse_topics.isSticky, yabbse_topics.ID_POLL, m2.posterName as mname, 
> m2.ID_MEMBER as mid, IFNULL(mem2.realName, m2.posterName) AS 
> firstPosterDisplayName, m2.subject as msub, m2.icon as micon, 
> IFNULL(lt.logTime, 0) AS isRead, IFNULL(lmr.logTime, 0) AS isMarkedRead
>
> 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'
>
>
> show create table yabbse_topics;
> CREATE TABLE `yabbse_topics` (
>  `ID_TOPIC` int(11) NOT NULL auto_increment,
>  `ID_BOARD` int(11) NOT NULL default '0',
>  `ID_MEMBER_STARTED` int(11) NOT NULL default '0',
>  `ID_MEMBER_UPDATED` int(11) NOT NULL default '0',
>  `ID_FIRST_MSG` int(11) NOT NULL default '0',
>  `ID_LAST_MSG` int(11) NOT NULL default '0',
>  `ID_POLL` int(11) NOT NULL default '-1',
>  `numReplies` int(11) NOT NULL default '0',
>  `numViews` int(11) NOT NULL default '0',
>  `locked` tinyint(4) NOT NULL default '0',
>  `notifies` text,
>  `isSticky` tinyint(4) NOT NULL default '0',
>  PRIMARY KEY  (`ID_TOPIC`)
> ) ENGINE=MyISAM DEFAULT CHARSET=latin1
>
> show create table yabbse_messages;
> CREATE TABLE `yabbse_messages` (
>  `ID_MSG` int(11) NOT NULL auto_increment,
>  `ID_TOPIC` int(11) NOT NULL default '0',
>  `ID_MEMBER` int(11) NOT NULL default '0',
>  `subject` tinytext,
>  `posterName` tinytext NOT NULL,
>  `posterEmail` tinytext,
>  `posterTime` bigint(20) default NULL,
>  `posterIP` tinytext NOT NULL,
>  `smiliesEnabled` tinyint(4) NOT NULL default '1',
>  `modifiedTime` bigint(20) default NULL,
>  `modifiedName` tinytext,
>  `body` text,
>  `icon` tinytext,
>  `attachmentSize` mediumint(9) NOT NULL default '0',
>  `attachmentFilename` tinytext,
>  PRIMARY KEY  (`ID_MSG`),
>  KEY `ID_TOPIC` (`ID_TOPIC`),
>  KEY `ID_MEMBER` (`ID_MEMBER`),
>  KEY `posterTime` (`posterTime`)
> ) ENGINE=MyISAM DEFAULT CHARSET=latin1
>
>
>

--------------060504070102020609030607
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
  <title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
<i>&gt;I've upgraded from mysql 4.1 to mysql 5 and some queries <br>
&gt;doesn't work. It's not explained on mysql changes
incompatibilities...
</i><br>
<br>
It is: see the first change item, marked 'incompatible change', at
<a class="moz-txt-link-freetext" href="http://dev.mysql.com/doc/refman/5.0/en/news-5-0-12.html">http://dev.mysql.com/doc/refman/5.0/en/news-5-0-12.html</a>. We can no
longer get away with syntactically loose (SQL2003-incompatible)
combinations of commas and JOIN clauses. <br>
<br>
PB<br>
<br>
----<br>
<br>
PaginaDeSpud wrote:
<blockquote cite="mid1ff901c60b24$dea7e0b0$02001aac@pc3000" type="cite">hi,
  <br>
I've upgraded from mysql 4.1 to mysql 5 and some queries doesn't work.
It's not explained on mysql changes incompatibilities...
  <br>
  <br>
for example:
  <br>
  <br>
SELECT yabbse_topics.ID_LAST_MSG, yabbse_topics.ID_TOPIC,
yabbse_topics.numReplies, yabbse_topics.locked,
yabbse_messages.posterName, yabbse_messages.ID_MEMBER,
IFNULL(mem.realName, yabbse_messages.posterName) AS posterDisplayName,
yabbse_topics.numViews, yabbse_messages.posterTime,
yabbse_messages.modifiedTime, yabbse_topics.ID_FIRST_MSG,
yabbse_topics.isSticky, yabbse_topics.ID_POLL, m2.posterName as mname,
m2.ID_MEMBER as mid, IFNULL(mem2.realName, m2.posterName) AS
firstPosterDisplayName, m2.subject as msub, m2.icon as micon,
IFNULL(lt.logTime, 0) AS isRead, IFNULL(lmr.logTime, 0) AS isMarkedRead
  <br>
  <br>
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)
  <br>
  <br>
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
  <br>
  <br>
ERROR: Unknown column 'yabbse_messages.ID_MEMBER' in 'on clause'
  <br>
  <br>
  <br>
show create table yabbse_topics;
  <br>
CREATE TABLE `yabbse_topics` (
  <br>
&nbsp;`ID_TOPIC` int(11) NOT NULL auto_increment,
  <br>
&nbsp;`ID_BOARD` int(11) NOT NULL default '0',
  <br>
&nbsp;`ID_MEMBER_STARTED` int(11) NOT NULL default '0',
  <br>
&nbsp;`ID_MEMBER_UPDATED` int(11) NOT NULL default '0',
  <br>
&nbsp;`ID_FIRST_MSG` int(11) NOT NULL default '0',
  <br>
&nbsp;`ID_LAST_MSG` int(11) NOT NULL default '0',
  <br>
&nbsp;`ID_POLL` int(11) NOT NULL default '-1',
  <br>
&nbsp;`numReplies` int(11) NOT NULL default '0',
  <br>
&nbsp;`numViews` int(11) NOT NULL default '0',
  <br>
&nbsp;`locked` tinyint(4) NOT NULL default '0',
  <br>
&nbsp;`notifies` text,
  <br>
&nbsp;`isSticky` tinyint(4) NOT NULL default '0',
  <br>
&nbsp;PRIMARY KEY&nbsp; (`ID_TOPIC`)
  <br>
) ENGINE=MyISAM DEFAULT CHARSET=latin1
  <br>
  <br>
show create table yabbse_messages;
  <br>
CREATE TABLE `yabbse_messages` (
  <br>
&nbsp;`ID_MSG` int(11) NOT NULL auto_increment,
  <br>
&nbsp;`ID_TOPIC` int(11) NOT NULL default '0',
  <br>
&nbsp;`ID_MEMBER` int(11) NOT NULL default '0',
  <br>
&nbsp;`subject` tinytext,
  <br>
&nbsp;`posterName` tinytext NOT NULL,
  <br>
&nbsp;`posterEmail` tinytext,
  <br>
&nbsp;`posterTime` bigint(20) default NULL,
  <br>
&nbsp;`posterIP` tinytext NOT NULL,
  <br>
&nbsp;`smiliesEnabled` tinyint(4) NOT NULL default '1',
  <br>
&nbsp;`modifiedTime` bigint(20) default NULL,
  <br>
&nbsp;`modifiedName` tinytext,
  <br>
&nbsp;`body` text,
  <br>
&nbsp;`icon` tinytext,
  <br>
&nbsp;`attachmentSize` mediumint(9) NOT NULL default '0',
  <br>
&nbsp;`attachmentFilename` tinytext,
  <br>
&nbsp;PRIMARY KEY&nbsp; (`ID_MSG`),
  <br>
&nbsp;KEY `ID_TOPIC` (`ID_TOPIC`),
  <br>
&nbsp;KEY `ID_MEMBER` (`ID_MEMBER`),
  <br>
&nbsp;KEY `posterTime` (`posterTime`)
  <br>
) ENGINE=MyISAM DEFAULT CHARSET=latin1
  <br>
  <br>
  <br>
  <br>
</blockquote>
</body>
</html>

--------------060504070102020609030607--
--=======AVGMAIL-43B1B2341EEA=======
Content-Type: text/plain; x-avg=cert; charset=us-ascii
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
Content-Description: "AVG certification"

No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.371 / Virus Database: 267.14.7/214 - Release Date: 12/23/2005

--=======AVGMAIL-43B1B2341EEA=======--