upgrading to mysql 5
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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: upgrading to mysql 5
PaginaDeSpud [EMAIL PROTECTED] 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
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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: upgrading to mysql 5
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 [EMAIL PROTECTED] To: PaginaDeSpud [EMAIL PROTECTED] Cc: mysql@lists.mysql.com 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Upgrading to MySQL 5 (for testing)
Hello. Yes, if you have one of the last releases of MySQL, you can try to test the fifth version. But some features will be unavailable. See: http://dev.mysql.com/doc/mysql/en/Upgrading-from-4.1.html Jonathan Villa [EMAIL PROTECTED] wrote: I have an installation of 4.1 and 5 running on the same test server... I want to start using 5 for testing but still want to use the same test data I had before... Is it possible to simply point MySQL 5 to the old 4 data dir? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Upgrading to MySQL 5 (for testing)
I have an installation of 4.1 and 5 running on the same test server... I want to start using 5 for testing but still want to use the same test data I had before... Is it possible to simply point MySQL 5 to the old 4 data dir? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
