Error after upgrading mysql from 5.0.x to 5.015 final

[Sickboy]

Problem only arrises in the vbadvanced pages, not in the forums themselves, and ONLY when the NEWS module is enabled AND the news thread icons are enabled. If I disable them, the problem is gone (Logical ofcoarse, cause the error is about them )
Database error in vBulletin 3.5.0:

Invalid SQL:

SELECT IF(votenum >= 1, votenum, 0) AS numbvote, IF(votenum >= 1 AND votenum != 0, votetotal / votenum, 0) AS voteavg,
thread.threadid, post.title, thread.replycount, postusername, postuserid, thread.dateline AS postdateline, thread.lastposter, thread.lastpost, IF(views<=thread.replycount, thread.replycount+1, views) AS views, thread.forumid, post.postid, pagetext, allowsmilie, forum.title AS forumtitle

,thread.iconid AS threadiconid, iconpath AS threadiconpath

, NOT ISNULL(subscribethread.subscribethreadid) AS subscribed
FROM thread AS thread
LEFT JOIN forum AS forum USING (forumid)
LEFT JOIN post AS post ON (post.postid = thread.firstpostid)
LEFT JOIN icon USING (iconid)



LEFT JOIN subscribethread AS subscribethread ON (subscribethread.threadid = thread.threadid AND subscribethread.userid = '1')
LEFT JOIN deletionlog AS deletionlog ON (thread.threadid = deletionlog.primaryid AND type = 'thread')
WHERE thread.threadid IN(1111,1091,1081,1070,1065,1058)

GROUP BY post.postid
ORDER BY postdateline DESC
LIMIT 6;

MySQL Error : Column 'iconid' in from clause is ambiguous
Error Number : 1052
Date : Tuesday, October 25th 2005 @ 04:19:12 PM
Script : http://xxx.xxxxx.xx/default.php
Referrer :
IP Address : xx.xxx.xxx.xxx
Username : Sickboy
Classname : vB_Database_MySQLi

[Ralf vd Enden]

In modules/latesttopics.php and modules/news.php find the following:

LEFT JOIN ' . TABLE_PREFIX . 'icon USING (iconid)

and replace it with:

LEFT JOIN ' . TABLE_PREFIX . 'icon AS icon ON (icon.iconid = thread.iconid)

It should work as expected again after that.

Regards,
Ralf

[Brian]

I wouldn't *think* that changing that would have anything to do with the error. If it does help though please let me know.

[Sickboy]

Ralf... major thanks!!
Works perfectly.

Thanks again.

[Zachery]

Try disabling strict mode if you continue having problems.

[Sickboy]

Originally Posted by Zachery

Try disabling strict mode if you continue having problems.

Ofcoarse I already tried that before contacting you guys here on the forums

The solution above worked for me, I only came to find out that I either need new thread icons or leave em disabled; it's nicer for now

[Brian]

*Moves to bugs forum to make sure this is changed in the next version.*

[Torqued]

Ok. I tried the fix above as well as disabling strict mode and I am still getting the error. This is on a "virgin" new install of vB 3.5.3 and vBA CMPS 2.0.0.

Any other ideas?

[Brian]

Did you apply the changes to your news.php and latesttopics.php files?

[Torqued]

bah! had a stray character in news.php. it's working now. thanks!

[pg116]

Originally Posted by Ralf vd Enden

In modules/latesttopics.php and modules/news.php find the following:

LEFT JOIN ' . TABLE_PREFIX . 'icon USING (iconid)

and replace it with:

LEFT JOIN ' . TABLE_PREFIX . 'icon AS icon ON (icon.iconid = thread.iconid)

It should work as expected again after that.

Regards,
Ralf

That worked great for me!

Thanks!

[Samir]

I hate to bump an old thread, but I just disabled the News Icon and it worked for me. I'll change the code later. I'm just happy to see it working again since I'm running older versions of vb and the cmps on mysql 5.