MySQL ODBC Connector - upgrading to mysql 5

Author

upgrading to mysql 5

PaginaDeSpud

2005-12-27, 8:24 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:

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
firstPosterDisplayNa
me, 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_mes
sages.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_topi
cs.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,2
4732,67085,59867,438
84,68331,68330,56897
,68090,68144,68174,6
8202,68329,68280,682
58,68294,68299,68326
,68325,67977,67286,5
5125,67897,65748,683
16,68317,68152,68291
,68187,68000,68240,6
8237,66796,68271,674
86,57293,68027,67332
,67990,68089,68
072,42517,68315,6361
2,66121,68275,68032,
68261,68308,67919,68
305,68302,68300,6829
6,40983,68256,68293,
68295,68270,68212,68
198,45444,67246,6821
5,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/mysql? unsub...sie.nctu.edu.tw

SGreen@unimin.com

2005-12-27, 8:24 pm

--=_alternative 0072F00B852570E4_=
Content-Type: text/plain; charset="US-ASCII"

"PaginaDeSpud" < webmaster@paginadesp
ud.com> 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_mes
sages.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_topi
cs.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,2
4732,67085,59867,438
84,68331,68330,56897
,68090,
> 68144,68174,68202,68
329,68280,68258,6829
4,68299,68326,68325,
67977,
> 67286,55125,67897,65
748,68316,68317,6815
2,68291,68187,68000,
68240,
> 68237,66796,68271,67
486,57293,68027,6733
2,67990,68089,68072,
42517,
> 68315,63612,66121,68
275,68032,68261,6830
8,67919,68305,68302,
68300,
> 68296,40983,68256,68
293,68295,68270,6821
2,68198,45444,67246,
68215,
> 68264,68208,68133,67
017)
> 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

--=_alternative 0072F00B852570E4_=--

Peter Brawley

2005-12-27, 8:24 pm

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

-------------- 06050407010202060903
0607
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...ws-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
> firstPosterDisplayNa
me, 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_mes
sages.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_topi
cs.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,2
4732,67085,59867,438
84,68331,68330,56897
,68090,68144,68174,6
8202,68329,68280,682
58,68294,68299,68326
,68325,67977,67286,5
5125,67897,65748,683
16,68317,68152,68291
,68187,68000,68240,6
8237,66796,68271,674
86,57293,68027,67332
,67990,68089,
68072,42517,68315,63
612,66121,68275,6803
2,68261,68308,67919,
68305,68302,68300,68
296,40983,68256,6829
3,68295,68270,68212,
68198,45444,67246,68
215,68264,68208,6813
3,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
>
>
>

-------------- 06050407010202060903
0607
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'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
<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. 
 
PB 
 
---- 
 
PaginaDeSpud wrote:
<blockquote cite=" mid1ff901c60b24$dea7
e0b0$02001aac@pc3000
" type="cite">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
firstPosterDisplayNa
me, 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_mes
sages.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_topi
cs.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,2
4732,67085,59867,438
84,68331,68330,56897
,68090,68144,68174,6
8202,68329,68280,682
58,68294,68299,68326
,68325,67977,67286,5
5125,67897,65748,683
16,68317,68152,68291
,68187,68000,68240,6
8237,66796,68271,674
86,57293,68027,67332
,67990,68089,68
072,42517,68315,6361
2,66121,68275,68032,
68261,68308,67919,68
305,68302,68300,6829
6,40983,68256,68293,
68295,68270,68212,68
198,45444,67246,6821
5,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',
 
& amp;nbsp;`ID_MEMBER_
STARTED` int(11) NOT NULL default '0',
 
& amp;nbsp;`ID_MEMBER_
UPDATED` int(11) NOT NULL default '0',
 
& amp;nbsp;`ID_FIRST_M
SG` int(11) NOT NULL default '0',
 
& amp;nbsp;`ID_LAST_MS
G` int(11) NOT NULL default '0',
 
 `ID_POLL` int(11) NOT NULL default '-1',
 
& amp;nbsp;`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',
 
& amp;nbsp;`ID_MEMBER`
int(11) NOT NULL default '0',
 
 `subject` tinytext,
 
& amp;nbsp;`posterName
` tinytext NOT NULL,
 
& amp;nbsp;`posterEmai
l` tinytext,
 
& amp;nbsp;`posterTime
` bigint(20) default NULL,
 
 `posterIP` tinytext NOT NULL,
 
& amp;nbsp;`smiliesEna
bled` tinyint(4) NOT NULL default '1',
 
& amp;nbsp;`modifiedTi
me` bigint(20) default NULL,
 
& amp;nbsp;`modifiedNa
me` tinytext,
 
 `body` text,
 
 `icon` tinytext,
 
& amp;nbsp;`attachment
Size` mediumint(9) NOT NULL default '0',
 
& amp;nbsp;`attachment
Filename` tinytext,
 
 PRIMARY KEY  (`ID_MSG`),
 
 KEY `ID_TOPIC` (`ID_TOPIC`),
 
 KEY `ID_MEMBER` (`ID_MEMBER`),
 
 KEY `posterTime` (`posterTime`)
 
) ENGINE=MyISAM DEFAULT CHARSET=latin1
 
 
 
 
</blockquote>
</body>
</html>

-------------- 06050407010202060903
0607--
--=======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=======
Content-Type: text/plain; charset=us-ascii

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
--=======AVGMAIL-43B1B2341EEA=======--

PaginaDeSpud

2005-12-27, 8:24 pm

I only saw this changes:
http://dev.mysql.com/doc/refman/5.0...g-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@earthlink.net>
To: "PaginaDeSpud" < webmaster@paginadesp
ud.com>
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
>
> It is: see the first change item, marked 'incompatible change', at
> http://dev.mysql.com/doc/refman/5.0...ws-5-0-12.html. We can no
> longer get away with syntactically loose (SQL2003-incompatible)
> combinations of commas and JOIN clauses.
>
> PB
>
> ----
>
> PaginaDeSpud wrote:
>
,68072,42517,68315,6
3612,66121,68275,680
32,68261,68308,67919
,68305,68302,68300,6
8296,40983,68256,682
93,68295,68270,68212
,68198,45444,67246,6
8215,68264,68208,681
33,67017)[color=dark
red]
>

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

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/mysql? unsub...sie.nctu.edu.tw

Sponsored Links