List:General Discussion« Previous MessageNext Message »
From:PaginaDeSpud Date:December 28 2005 1:00am
Subject:Re: upgrading to mysql 5
View as plain text  
I only saw this changes: 
http://dev.mysql.com/doc/refman/5.0/en/upgrading-from-4-1.html

I've rebuild this query according to the new sql join sintax and works fine. 
Thanks a lot because i don't know how many days were spent to solve this 
without your help :)

Ivan Lopez.
Logosur.

----- Original Message ----- 
From: "Peter Brawley" <peter.brawley@stripped>
To: "PaginaDeSpud" <webmaster@stripped>
Cc: <mysql@stripped>
Sent: Tuesday, December 27, 2005 10:29 PM
Subject: Re: upgrading to mysql 5


>/>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
>>
>>
>>
>


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


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

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