vb.org Archive

vb.org Archive (https://vborg.vbsupport.ru/index.php)
-   vB3 Programming Discussions (https://vborg.vbsupport.ru/forumdisplay.php?f=15)
-   -   database error help needed please!! (https://vborg.vbsupport.ru/showthread.php?t=121309)

Rickie3 07-16-2006 02:15 AM
database error help needed please!!
 
Hi my host has just upgraded to Mysql5.0.21-standard and PHP5.1.4

and now i'm getting a database error, clicking the userCP in the navbar
the error returned is as follows

Code:
Database error in vBulletin 3.5.0:

Invalid SQL:

                SELECT thread.threadid, thread.forumid,
                        IF(threadread.readtime IS NULL, 1147835566, IF(threadread.readtime < 1147835566, 1147835566, threadread.readtime)) AS threadread,
                        IF(forumread.readtime IS NULL, 1147835566, IF(forumread.readtime < 1147835566, 1147835566, forumread.readtime)) AS forumread,
                        thread.lastpost, subscribethread.subscribethreadid
                FROM sg_thread AS thread,
                        sg_subscribethread AS subscribethread
                LEFT JOIN sg_threadread AS threadread ON (threadread.threadid = thread.threadid AND threadread.userid = 1)
                LEFT JOIN sg_forumread AS forumread ON (forumread.forumid = thread.forumid AND forumread.userid = 1)
                WHERE subscribethread.threadid = thread.threadid
                        AND subscribethread.userid = 1
                        AND thread.visible = 1
                HAVING thread.lastpost > IF(threadread > forumread, threadread, forumread);

MySQL Error  : Unknown column 'thread.threadid' in 'on clause'
Error Number : 1054
Date        : Saturday, July 15th 2006 @ 11:12:46 PM
Script      : http://www.sats-general.com/forum/usercp.php
Referrer    : http://www.sats-general.com/forum/index.php?
IP Address  : xxxxxxxxxxxxxxx
Username    : Rickie3
Classname    : vB_Database
I contacted my host but they say its a vbulletin problem,but i didnt have issuues until the upgraded.Any help appreciated

Paul M 07-16-2006 02:29 AM
Quote:
Originally Posted by Rickie3
I contacted my host but they say its a vbulletin problem
Well they could argue that - it's a change in mysql that causes that query (which used to work fine) to now fail - you need to upgrade to vb 3.5.4.

Rickie3 07-16-2006 02:44 AM
is there anyway to fix the problem without having to upgrade please

i did have other errors but i found a fix for them here http://www.vbulletin.com/forum/bugs35.php?bugid=2449

its just the userCP that is causing me grief now everything else is functioning perfectly on my board

Paul M 07-16-2006 10:16 AM
Quote:
Originally Posted by Rickie3
is there anyway to fix the problem without having to upgrade please
Probably, but there is no excuse not to upgrade, 3.5.0 has security bugs as well.

Rickie3 07-16-2006 11:03 AM
thanx for all your help Paul M,I took your advise and upgraded to 3.5.4, all problems now solved,and no errors so far,cheers

Code Monkey 07-16-2006 01:29 PM
For future reference, you needed to put the multiple FROM tables between brackets () when you have JOINS.

PHP Code:
SELECT thread.threadid, thread.forumid,
            IF(threadread.readtime IS NULL, 1147835566, IF(threadread.readtime < 1147835566, 1147835566, threadread.readtime)) AS threadread,
            IF(forumread.readtime IS NULL, 1147835566, IF(forumread.readtime < 1147835566, 1147835566, forumread.readtime)) AS forumread,
            thread.lastpost, subscribethread.subscribethreadid

        FROM (sg_thread AS thread,
            sg_subscribethread AS subscribethread)

        LEFT JOIN sg_threadread AS threadread ON (threadread.threadid = thread.threadid AND threadread.userid = 1)
        LEFT JOIN sg_forumread AS forumread ON (forumread.forumid = thread.forumid AND forumread.userid = 1)
        WHERE subscribethread.threadid = thread.threadid
            AND subscribethread.userid = 1
            AND thread.visible = 1
        HAVING thread.lastpost > IF(threadread > forumread, threadread, forumread); 
I had to figure this out on many of my non vb apps I have written when I upgraded. What a joy that was.


All times are GMT. The time now is 05:16 PM.

Powered by vBulletin® Version 3.8.12 by vBS
Copyright ©2000 - 2025, vBulletin Solutions Inc.

X vBulletin 3.8.12 by vBS Debug Information
  • Page Generation 0.01125 seconds
  • Memory Usage 1,743KB
  • Queries Executed 10 (?)
More Information
Template Usage:
  • (1)ad_footer_end
  • (1)ad_footer_start
  • (1)ad_header_end
  • (1)ad_header_logo
  • (1)ad_navbar_below
  • (1)bbcode_code_printable
  • (1)bbcode_php_printable
  • (2)bbcode_quote_printable
  • (1)footer
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (6)option
  • (1)post_thanks_navbar_search
  • (1)printthread
  • (6)printthreadbit
  • (1)spacer_close
  • (1)spacer_open 
Phrase Groups Available:
  • global
  • postbit
  • showthread
Included Files:
  • ./printthread.php
  • ./global.php
  • ./includes/init.php
  • ./includes/class_core.php
  • ./includes/config.php
  • ./includes/functions.php
  • ./includes/class_hook.php
  • ./includes/modsystem_functions.php
  • ./includes/class_bbcode_alt.php
  • ./includes/class_bbcode.php
  • ./includes/functions_bigthree.php 
Hooks Called:
  • init_startup
  • init_startup_session_setup_start
  • init_startup_session_setup_complete
  • cache_permissions
  • fetch_threadinfo_query
  • fetch_threadinfo
  • fetch_foruminfo
  • style_fetch
  • cache_templates
  • global_start
  • parse_templates
  • global_setup_complete
  • printthread_start
  • bbcode_fetch_tags
  • bbcode_create
  • bbcode_parse_start
  • bbcode_parse_complete_precache
  • bbcode_parse_complete
  • printthread_post
  • printthread_complete