PDA

View Full Version : Error after upgrading mysql from 5.0.x to 5.015 final


Sickboy
10-25-2005, 11:23 AM
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
10-26-2005, 07:02 PM
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

Our Sponsors
 

Brian
10-27-2005, 03:06 PM
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
10-28-2005, 04:06 AM
Ralf... major thanks!!
Works perfectly.

Thanks again.

Our Sponsors
 

Zachery
10-28-2005, 04:59 AM
Try disabling strict mode if you continue having problems.

Sickboy
10-28-2005, 05:32 AM
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
10-28-2005, 11:59 AM
*Moves to bugs forum to make sure this is changed in the next version.*

Torqued
01-20-2006, 12:59 AM
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
01-20-2006, 01:06 AM
Did you apply the changes to your news.php and latesttopics.php files?

Torqued
01-20-2006, 09:08 AM
bah! had a stray character in news.php. it's working now. thanks! :D

pg116
04-13-2007, 10:57 AM
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
05-08-2009, 06:17 PM
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.