Go Back   vb.org Archive > vBulletin Article Depository > Read An Article > vBulletin 3 Articles
Reload this Page How to find a bad query
FAQ Community Calendar Today's Posts Search

Reply
Page 1 of 2 1 2
 
Thread Tools
JJR512's Avatar
JJR512
Join Date: Oct 2001
Posts: 710

 

Glen Burnie, MD, USA
Show Printable Version Email this Page Subscription
JJR512 JJR512 is offline 02-23-2002, 10:00 PM
I've been testing a hack I've been working on. At one point during the testing, I received a MySQL error message stating there was a problem with a query. It said what the query was, but as you've probably noticed in these error messages, it only gives a line number of '1'. The problem here was that I had four of the exact same query throughout the code, and I couldn't tell which one was causing the problem.

The specific problem was that in the WHERE part, it was written like "WHERE field=$variable", and $variable didn't have a value. The problem really wasn't the query itself, it was the code before it that was supposed to generate a value for $variable.

So the problem was how to quickly find out which of the four identical queries had some bad code before it. Then I realized how to do it. In the first query, I put a "and 1=1" at the end of the WHERE part, so it was like "WHERE field=$variable and 1=1". For the next one, I did 2=2, and so on, for all four.

Because 1 always equals 1, putting that bit in has absolutely no effect whatsoever on how the code or query works. Basically, you're putting it there as a marker.

This way, when the error message showed up and the query was quoted, it would include the marker (the "and x=x" part), and that would show me which query had the bad code before it.

Maybe this was basic or nothing new for a lot of you, but I'm still kind of new at this, and I'm glad I figured it out before I had to carefully examine all the code I would have had to look at, because that would have been a pain in my ass. But if this helps even just one of you, it was worth it for me to post this.
Reply With Quote
  #2  
Old 02-25-2002, 05:00 AM
Admin's Avatar
Admin Admin is offline
Coder
  
Join Date: Oct 2023
Location: Server
Posts: 1
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default
Doesn't the MySQL optimizer removes stuff like 1=1 from queries?
Reply With Quote
  #3  
Old 02-25-2002, 07:59 PM
JJR512's Avatar
JJR512 JJR512 is offline
 
Join Date: Oct 2001
Location: Glen Burnie, MD, USA
Posts: 710
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default
Aparently not, because when I got the error message again, there it was, 4=4.

Maybe it SKIPS processing it, but it at least passes it through so it can get into an error message.
Reply With Quote
  #4  
Old 02-26-2002, 06:24 AM
Admin's Avatar
Admin Admin is offline
Coder
  
Join Date: Oct 2023
Location: Server
Posts: 1
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default
No it does remove that from the query, but only when it hits the MySQL server. In explain=1 it shows what PHP gets.
Reply With Quote
  #5  
Old 02-27-2002, 12:14 AM
JJR512's Avatar
JJR512 JJR512 is offline
 
Join Date: Oct 2001
Location: Glen Burnie, MD, USA
Posts: 710
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default
Well, whatever...it doesn't really matter if that part of the query gets executed or not. The important thing is that it shows up in the error message, which is the point. I stuck it there as a marker to show me which of four identical queries was causing a problem. Each had something different, so I could tell which one was the bad one. As long as it shows up in the error message, that's all that's important. And it does show up.
Reply With Quote
  #6  
Old 03-12-2002, 08:06 AM
drazq drazq is offline
 
Join Date: Jan 2002
Posts: 9
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default
Hmm,

if I have a problem with a mysql query I always do an echo of the query on the line before I run it.

If I can't figgure out what the problem is then (if the query seems right) I copy and paste it into phpMyAdmin and run it there.

If it works there, then something else is wrong

- draz Q.
Reply With Quote
  #7  
Old 03-12-2002, 05:27 PM
JJR512's Avatar
JJR512 JJR512 is offline
 
Join Date: Oct 2001
Location: Glen Burnie, MD, USA
Posts: 710
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default
Those are good tips, but you need to know first which query is giving you problems before you can put an echo before it, or run it in phpMyAdmin. My tip helps you determine which query is the one that's giving you trouble. After you determine that, THEN you can do the stuff that you said.
Reply With Quote
  #8  
Old 04-27-2006, 04:06 AM
Zia's Avatar
Zia Zia is offline
 
Join Date: Dec 2005
Location: golpo.net
Posts: 931
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default
ummm is there MySqlOptizer for Vb availavble?
Reply With Quote
  #9  
Old 04-27-2006, 06:12 AM
kall's Avatar
kall kall is offline
 
Join Date: Apr 2004
Location: New Zealand
Posts: 2,608
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default
*sigh*

People found the archive through that post in the 'Archive the vB2 forums?' thread and are now resurrecting long dead threads. Marvellous.
Reply With Quote
  #10  
Old 05-08-2006, 12:21 AM
Xorlev Xorlev is offline
 
Join Date: May 2006
Location: Colorado
Posts: 54
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default
Quote:
Originally Posted by Zia
ummm is there MySqlOptizer for Vb availavble?
To beat the dead horse a little more...no. The query optimizer is a part of the MySQL server it's self, and thus any query passed to it is run through the optimizer first. It doesn't matter if it's a query vBulletin executes through PHP, from the mysql tool in SSH, or the C++ interface.
Reply With Quote
Reply
Page 1 of 2 1 2

« Previous Thread | Next Thread »

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump


All times are GMT. The time now is 02:52 PM.

Contact Us - Archive - Top

Powered by vBulletin® Version 3.8.12 by vBS
Copyright ©2000 - 2024, vBulletin Solutions Inc.
X vBulletin 3.8.12 by vBS Debug Information
  • Page Generation 0.07526 seconds
  • Memory Usage 2,288KB
  • Queries Executed 23 (?)
More Information
Template Usage:
  • (1)SHOWTHREAD
  • (1)ad_footer_end
  • (1)ad_footer_start
  • (1)ad_header_end
  • (1)ad_header_logo
  • (1)ad_navbar_below
  • (1)ad_showthread_beforeqr
  • (1)bbcode_quote
  • (1)footer
  • (1)forumjump
  • (1)forumrules
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (1)modsystem_article
  • (1)navbar
  • (4)navbar_link
  • (120)option
  • (1)pagenav
  • (1)pagenav_curpage
  • (1)pagenav_pagelink
  • (10)post_thanks_box
  • (10)post_thanks_button
  • (1)post_thanks_javascript
  • (1)post_thanks_navbar_search
  • (10)post_thanks_postbit_info
  • (9)postbit
  • (10)postbit_onlinestatus
  • (10)postbit_wrapper
  • (1)spacer_close
  • (1)spacer_open
  • (1)tagbit_wrapper 
Phrase Groups Available:
  • global
  • inlinemod
  • postbit
  • posting
  • reputationlevel
  • showthread
Included Files:
  • ./showthread.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/functions_bigthree.php
  • ./includes/class_postbit.php
  • ./includes/class_bbcode.php
  • ./includes/functions_reputation.php
  • ./includes/functions_post_thanks.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
  • showthread_start
  • showthread_getinfo
  • forumjump
  • showthread_post_start
  • showthread_query_postids
  • showthread_query
  • bbcode_fetch_tags
  • bbcode_create
  • showthread_postbit_create
  • postbit_factory
  • postbit_display_start
  • post_thanks_function_post_thanks_off_start
  • post_thanks_function_post_thanks_off_end
  • post_thanks_function_fetch_thanks_start
  • post_thanks_function_fetch_thanks_end
  • post_thanks_function_thanked_already_start
  • post_thanks_function_thanked_already_end
  • fetch_musername
  • postbit_imicons
  • bbcode_parse_start
  • bbcode_parse_complete_precache
  • bbcode_parse_complete
  • postbit_display_complete
  • post_thanks_function_can_thank_this_post_start
  • pagenav_page
  • pagenav_complete
  • tag_fetchbit_complete
  • forumrules
  • navbits
  • navbits_complete
  • showthread_complete