SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    Scary's On The Wall
    Join Date
    Apr 2003
    Location
    PA
    Posts
    518
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    query causes a timeout and/or browser downloads the script (was "What the flying...")

    This query causes a timeout and/or causes the browser to try to download the script trying to run the query as a file:

    Code:
    SELECT comic_comments.*, users.name as user_name, user_profiles.avatar, user_profiles.avatar_type, user_profiles.avatar_height, user_profiles.avatar_width FROM comic_comments, comics, users, user_profiles WHERE comic_comments.comic_id = comics.id AND comics.site_id = '59' AND users.id = comic_comments.user_id AND user_profiles.user_id = users.id ORDER BY comic_comments.id DESC LIMIT 5
    I'm completely stumped... here's the explain, if it helps:

    Code:
    id  	select_type  	 table  	 	type  	 possible_keys  	 key  	 key_len  	 ref  	 					rows  	 Extra
    1 		SIMPLE 			comic_comments 	ALL 		comic_id 		NULL 	NULL 		NULL 						14167 	Using temporary; Using filesort
    1 		SIMPLE 			user_profiles 	ALL 		userid 			NULL 	NULL 		NULL 						1517 	 
    1 		SIMPLE 			users 			eq_ref 		PRIMARY 		PRIMARY 4 			test2.comic_comments.user_id 1 		Using where
    1 		SIMPLE 			comics 			eq_ref 		PRIMARY,site_id PRIMARY 4 		test2.comic_comments.comic_id 	 1 		Using where
    (copy and paste into a text editor for easier reading)

    EDIT: Sorry about the generic uninformative title, I should really know better than that.
    Last edited by Haleden; Jan 30, 2006 at 21:48.

  2. #2
    SitePoint Guru
    Join Date
    Aug 2003
    Location
    CT
    Posts
    643
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    no query should prompt a browser to download anything.

    can you post the code that calls this query, and any headers if applicable?

  3. #3
    Scary's On The Wall
    Join Date
    Apr 2003
    Location
    PA
    Posts
    518
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by briansol
    no query should prompt a browser to download anything.
    lol, I know. It seems I've torn a hole in the universe fabric.

    The entire file is nearly 3000 lines of code long... There are no special headers.

    Let me post something else, it might be easier to understand.

    This does work:
    PHP Code:
                $sql "SELECT comic_comments.*, users.name as user_name, user_profiles.avatar, user_profiles.avatar_type, user_profiles.avatar_height, user_profiles.avatar_width "
                    
    "FROM comic_comments, comics "
                    
    "LEFT OUTER JOIN users ON users.id = comic_comments.user_id "
                    
    "LEFT OUTER JOIN user_profiles ON user_profiles.user_id = users.id "
                    
    "WHERE comic_comments.comic_id = comics.id "
                    
    "AND comics.site_id = '" $sitedata['id'] . "' "
                    
    "ORDER BY comic_comments.id DESC LIMIT 5"
    This does not:

    PHP Code:
                $sql "SELECT comic_comments.*, users.name as user_name, user_profiles.avatar, user_profiles.avatar_type, user_profiles.avatar_height, user_profiles.avatar_width "
                    
    "FROM comic_comments, comics "
                    
    "JOIN users ON users.id = comic_comments.user_id "
                    
    "JOIN user_profiles ON user_profiles.user_id = users.id "
                    
    "WHERE comic_comments.comic_id = comics.id "
                    
    "AND comics.site_id = '" $sitedata['id'] . "' "
                    
    "ORDER BY comic_comments.id DESC LIMIT 5"
    That's right, the only difference is that the second one uses regular joins.

  4. #4
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Haleden
    This query causes a timeout and/or causes the browser to try to download the script trying to run the query as a file:
    just querying a DB doesn't have that effect. are you sure the file is named correctly? for example, if it's a PHP file, it must end in .php or the server will just download it to the client.

  5. #5
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    what version of mysql are you using? i've heard that mysql 5 does not allow table list and join syntax to be mixed, so it may be that when your script returns an error, it attemps to download an error page instead of actually displaying it.

  6. #6
    Scary's On The Wall
    Join Date
    Apr 2003
    Location
    PA
    Posts
    518
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    .php of course

    And I'm running 4.1.14-standard

  7. #7
    SitePoint Guru
    Join Date
    Aug 2003
    Location
    CT
    Posts
    643
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    for the heck of it, try running an INNER JOIN on it. see what happens. Maybe we'll get some cool Easter Egg :P

    ok no, try this instead. i took your where clause join and made it a real join.
    wihtout seeing the tables, i can't say for sure, but this should pull the correct data

    Code:
     $sql = "SELECT comic_comments.*, users.name as user_name, user_profiles.avatar, user_profiles.avatar_type, user_profiles.avatar_height, user_profiles.avatar_width "
                    . "FROM comic_comments "
                    . "LEFT OUTER JOIN users ON users.id = comic_comments.user_id "
                    . "LEFT OUTER JOIN user_profiles ON user_profiles.user_id = users.id "
    . "INNER JOIN comics ON comic_comments.comic_id = comics.id "
                    . "WHERE comics.site_id = '" . $sitedata['id'] . "' "
                    . "ORDER BY comic_comments.id DESC LIMIT 5";

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    LEFT OUTER JOINs are not appropriate here
    Code:
    SELECT comic_comments.*
         , users.name as user_name
         , user_profiles.avatar
         , user_profiles.avatar_type
         , user_profiles.avatar_height
         , user_profiles.avatar_width  
      FROM comics 
    INNER
      JOIN comic_comments
        ON comic_comments.comic_id = comics.id
    INNER
      JOIN users 
        ON users.id = comic_comments.user_id 
    INNER
      JOIN user_profiles 
        ON user_profiles.user_id = users.id 
     WHERE comics.site_id = $sitedata['id'] /* no quotes */
    ORDER 
        BY comic_comments.id DESC LIMIT 5
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    Scary's On The Wall
    Join Date
    Apr 2003
    Location
    PA
    Posts
    518
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    LEFT OUTER JOINs are not appropriate here
    Code:
    SELECT comic_comments.*
         , users.name as user_name
         , user_profiles.avatar
         , user_profiles.avatar_type
         , user_profiles.avatar_height
         , user_profiles.avatar_width  
      FROM comics 
    INNER
      JOIN comic_comments
        ON comic_comments.comic_id = comics.id
    INNER
      JOIN users 
        ON users.id = comic_comments.user_id 
    INNER
      JOIN user_profiles 
        ON user_profiles.user_id = users.id 
     WHERE comics.site_id = $sitedata['id'] /* no quotes */
    ORDER 
        BY comic_comments.id DESC LIMIT 5
    Actually the comics.site_id field is type varchar, so quotes are appropriate in this case.

    Anyhow, the query you posted also causes the script to timeout

    I've tested this on two seperate servers with the same database structure and it happens to both.

    I'm going with query #1 (posted previously as "does work") for now, but this is very odd to me.

    EDIT: No wait, I was wrong, this query takes 290 seconds to execute.
    Last edited by Haleden; Jan 30, 2006 at 16:25.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •