sql errors

[The Dok]

I'm getting a database error when loading the cmps_index.php page. It is thisDatabase error in vBulletin 3.0.0:

Invalid SQL:
SELECT

thread.threadid, thread.title, thread.replycount, postusername, postuserid, thread.dateline AS postdateline, IF(views <= thread.replycount, thread.replycount+1, views) AS views, thread.lastposter, thread.lastpost, pollid
, thread.iconid AS threadiconid, iconpath AS threadiconpath
,thread.forumid, forum.title AS forumtitle
, post.pagetext AS preview
, NOT ISNULL(subscribethread.subscribethreadid) AS subscribed
FROM thread as thread
LEFT JOIN icon USING (iconid)
LEFT JOIN forum AS forum ON (thread.forumid = forum.forumid)
LEFT JOIN post AS post ON (post.postid = thread.firstpostid)
LEFT JOIN deletionlog AS deletionlog ON (thread.threadid = deletionlog.primaryid AND type = 'thread')
LEFT JOIN subscribethread AS subscribethread ON (subscribethread.threadid = thread.threadid AND subscribethread.userid = 13)
WHERE open <> '10' AND thread.visible = 1 Array AND deletionlog.primaryid IS NULL
ORDER BY lastpost DESC
LIMIT 10

mysql error: You have an error in your SQL syntax near 'Array AND deletionlog.primaryid IS NULL
ORDER BY lastpost DESC
LIMIT 1' at line 15

mysql error number: 1064

Date: Sunday 16th of May 2004 11:58:59 AM
Script: http://reymah.com/forum/cmps_index.php


Don't know much about sql :o . Any idea what I'm doing wrong, or have done wrong?

Thanks in advance...

TD

[Brian]

I've tried doing some quick tests, but I can't seem to reproduce this problem. If you could fill out a support ticket via the Members Area here then I'll be glad to take a look.

[BillAlpha]

I'm getting the same error except I'm using vB 3.0.1. It's not a permanent error either - it seems to be intermitent and I'm thinking it could be to do with forum permissions?

When you get the error on the CMPS home page and then go to the forums and login, then click on a link for the CMPS home page - the error is corrected and the page can be viewed. I have no knowledge of SQL or PHP so that's a total guess! :D



Database error in vBulletin 3.0.1:

Invalid SQL:
SELECT

thread.threadid, thread.title, thread.replycount, postusername, postuserid, thread.dateline AS postdateline, IF(views <= thread.replycount, thread.replycount+1, views) AS views, thread.lastposter, thread.lastpost, pollid
, thread.iconid AS threadiconid, iconpath AS threadiconpath



FROM vb3_thread as thread
LEFT JOIN vb3_icon USING (iconid)


LEFT JOIN vb3_deletionlog AS deletionlog ON (thread.threadid = deletionlog.primaryid AND type = 'thread')

WHERE open <> '10' AND thread.visible = 1 AND thread.forumid NOT IN(3,8,9,43,39,34,32,24,33,10,5,26,16,38,45,22,36,30,37,28,35,15,12,44,17,31,40, 19,18,20,25,48,7,42,11,41,27,6,21,46,23,4,47,29,14) Array AND deletionlog.primaryid IS NULL
ORDER BY lastpost DESC
LIMIT 10

mysql error: You have an error in your SQL syntax near 'Array AND deletionlog.primaryid IS NULL
ORDER BY lastpost DESC
LIMIT 10
' at line 15

mysql error number: 1064

[BillAlpha]

Just done a bit of testing and I'm only getting the database errors when I try to view the CMPS page as an unregistered visitor. I have some forums that can only be viewed by members and they appear in the "Latest Forum Threads" module on the CMPS page.

I made that module "inactive" and also tried the permission settings so that unregistered visitors can't see that module and the error no longer appears :confused:

[Brian]

I've tried doing some quick tests, but I can't seem to reproduce this problem. If you could fill out a support ticket via the Members Area here then I'll be glad to take a look.

[Natch]

Was this ever resolved ? cause I am supporting a forum where this same behaviour is being evidenced.

It occurs semingly when the following two situations are in place:

1) you have some forumn permissions in place ;)
2) you limit the forums from which latest threads are being SELECTed.

The error occurs when $inforums is equal to not a list of (INT,INT,INT,INT,INT) but an Array in the SQL query in the latesttopics.php module in this line:WHERE open <> '10' AND thread.visible = 1 $iforumperms $inforums $notdeleted.

If it has not been submitted for bug report I'll happily submit one (with the above information).

[Brian]

In your latesttopics.php file look for:

if (!empty($inforums))
{
$inforums = implode(',', $inforums);
}


Below that, Add:

else
{
$inforums = '';
}



And that should fix the problem. :)

[Natch]

I found out that it was happening only when I added into the latest topics box some forumid's that were also in the exclude box :D

Might this cause an issue ;)

/me is a nuff-nuff ;)

[bobbymac]

I missed seeing this when I posted mine in the Troubleshooting forum.

The proposed solution does not work correctly though - since further down it checks if it is empty - and if it is it doesn't put anything in the final query. In fact, it needs to put in an empty IN() (or an IN(-1) which will give the same result). If you use $inforums = ''; then it prints out threads from a whole list of forums.

[DJCobol]

Thanks for the fix. I was able to isolate my problem to only people awating email validation, but this fix has cleared up that issue. Thanks again!