Debugging MySQL code .. a few tips.

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

also note this section in your php.ini
; Trace mode. When trace_mode is active (=On), warnings for table/index scans and
; SQL-Erros will be displayed.
mysql.trace_mode = Off
a well hidden parameter for debugging mysql-driven sites. Maybe it's not mentioned in the documentation because it's subject to change, dunno.
User avatar
Pyrite
Forum Regular
Posts: 769
Joined: Tue Sep 23, 2003 11:07 pm
Location: The Republic of Texas
Contact:

Post by Pyrite »

And I agree on the off the wall variable names, like laffy and taffy or cheese and cracker, but never foo and bar.
ezest
Forum Newbie
Posts: 2
Joined: Tue Jun 29, 2004 5:40 am
Location: Pune, India

Post by ezest »

Agreed@PHPMyAdmin, but in case you are Prompt-lover.., you have commands like mysql and mysqladmin with all its set of options to this bunch of work other than just checkd,ing for the query.

This especially helps in performance tunning and validating by taking a snapshot of the work MySQL is executing.
Shendemiar
Forum Contributor
Posts: 404
Joined: Thu Jan 08, 2004 8:28 am

Post by Shendemiar »

I sometime had problems with different quotes. Fieldnames should be surrounded with ` s while values should have ' s around.
User avatar
dibyendrah
Forum Contributor
Posts: 491
Joined: Wed Oct 19, 2005 5:14 am
Location: Nepal
Contact:

log might help in some extent

Post by dibyendrah »

if we log the errors like php syntax errors or mysql syntax errors, it will be a great time saving.

The best way to see the errors at a real time is by either add a die(mysql_error()) or trace a error of mysql by using the following command :

# tail -f /var/log/mysql.err
# tail -f /var/log/mysql.log

The above method saved a lots of time for me!

Happy Debugging!!!

Dibyendra
User avatar
mikeq
Forum Regular
Posts: 512
Joined: Fri May 03, 2002 3:33 am
Location: Edinburgh, Scotland

Post by mikeq »

This is a tip for use when optimising your queries.

Obviously everyone uses 'explain' on their queries :wink: If not I suggest you do a bit of reading up.

It can be handy to see what the optimiser has done to your queries before it runs them.

As an example I have 2 tables

Code: Select all

test
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| field1 | tinyint(4)  | NO   |     |         |       |
| field2 | varchar(20) | NO   |     |         |       |
+--------+-------------+------+-----+---------+-------+
and

Code: Select all

test2
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| field1 | tinyint(4)  | NO   |     |         |       |
| fk1    | tinyint(4)  | NO   |     |         |       |
| field2 | varchar(20) | NO   |     |         |       |
+--------+-------------+------+-----+---------+-------+
I'll do a RIGHT JOIN to illustrate my point

Code: Select all

mysql> select * from test2 RIGHT JOIN test ON (test2.fk1 = test.field1);
+--------+------+---------------+--------+--------+
| field1 | fk1  | field2        | field1 | field2 |
+--------+------+---------------+--------+--------+
|      1 |    1 | first linked  |      1 | first  |
|      2 |    1 | second linked |      1 | first  |
|      3 |    2 | first linked  |      2 | second |
|   NULL | NULL | NULL          |      3 | third  |
+--------+------+---------------+--------+--------+
so that gives me all records in test and those related records in test2.

RIGHT JOIN is not standard, behind the scenes the optimiser turns this into a LEFT JOIN.

How do I know?

run an explain plan with the 'extended' key word

Code: Select all

mysql> explain extended select * from test2 RIGHT JOIN test ON (test2.fk1 = test
.field1);
immediately after this outputs the explain plan run show warnings

Code: Select all

mysql> show warnings;
This will show you exactly the query that was run

Code: Select all

select `example`.`test2`.`field1` AS `field1`,`example`.`test2`
.`fk1` AS `fk1`,`example`.`test2`.`field2` AS `field2`,`example`.`test`.`field1`
 AS `field1`,`example`.`test`.`field2` AS `field2` from `example`.`test` left jo
in `example`.`test2` on((`example`.`test2`.`fk1` = `example`.`test`.`field1`)) w
here 1
as you can see the optimiser turned this into a LEFT JOIN.

Enjoy.
Xoligy
Forum Commoner
Posts: 53
Joined: Sun Mar 04, 2007 5:35 am

Post by Xoligy »

code_monkey wrote:These days I always use this bit of code for generating ids rather than relying on numbers;

Code: Select all

<?php

$unique_id = md5(uniqid(rand(),1));

$token_result = mysql_query("SELECT id FROM table WHERE id='$unique_id'",$db);

while($num_rows = mysql_num_rows($token_result)){
						
	$unique_id = md5(uniqid(rand(),1));	
						
}


?>
Then you just use $unique_id when you insert the data into your database. You get a value of something like 'c6127375320d85153ba1b334ab18330a'.
By theory, not a great idea. It doesn't check for duplications.
User avatar
Arocity
Forum Newbie
Posts: 15
Joined: Tue Jun 10, 2008 8:30 am
Location: Washington, DC

Re:

Post by Arocity »

Pyrite wrote:And I agree on the off the wall variable names, like laffy and taffy or cheese and cracker, but never foo and bar.
Off the wall variables are good when doing inhouse development, but if you're not inhouse and you use crazy vars, you need to make sure you use GOOD documentation for code maintainability. Nobody wants to come in after somebody and try to figure out what the hell $skittles is.
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Re: Debugging MySQL code .. a few tips.

Post by superdezign »

Will anyone ever let this thread die in peace? o_O
~twigletmac posted in this one for Christ's sake. :P
nc72
Forum Newbie
Posts: 2
Joined: Thu Sep 01, 2011 11:05 pm

Re: Debugging MySQL code .. a few tips.

Post by nc72 »

Thnks for the tips. Really like it. ;) Some days ago Mr. Kyle Gush help me in sme problem of my mysql database. Kyle Gush of Atlanta GA is well known in php and mysql. My problem was similar to your tips.
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Debugging MySQL code .. a few tips.

Post by califdon »

This thread is a classic example of hijacking the topic. That's not a felony, but it would really serve everyone's needs better if those who want to expand on the original topic (and what is implied by the Subject line) would simply start a new thread. Many of the suggestions are very helpful, but beyond the scope of the original poster's topic. Those who want to answer a poster's question, or challenge what a poster stated are welcome to Reply to the topic. But if you want to bend the topic to a related (or unrelated) discussion, you are encouraged to start a new thread. I thought that markl999 was pretty clear in his opening post that he wasn't trying to offer a comprehensive list of good practices, but rather to suggest that by following 3 specific tips a large proportion of "problems" that forum users post about MySQL queries could be avoided. I think he's pretty much right on that.

It might be worthwhile for several of you to work up a comprehensive list of good practices and post it as a new thread, which you could request be made a 'sticky' thread, and as people propose modifications, it could be updated. All of the comments have merit, it's just that we end up with a wandering thread that will soon fade into oblivion.
User avatar
ragax
Forum Commoner
Posts: 85
Joined: Thu Dec 15, 2011 1:40 pm
Location: Nelson, NZ

Re: Debugging MySQL code .. a few tips.

Post by ragax »

Debugging MySQL code .. a few tips.
To get this (ancient) mostly off-topic thread back on track, two tips I didn't find above:

1. Add a comment to the top of each query, for instance:

Code: Select all

$myquery = 
"# from jamesbond.php | get spy names  query
SELECT name 
FROM....
";
When you have a large app with many files and many more queries, you're grateful to see an error message telling you not just the SQL of the query, but exactly where that query lives—especially if you have a lot of dynamic queries.

2. In your error-catching procedure, reformat the error (which will show the query) by replacing line breaks (\n) with br tags, and by running it through htmlentities for those cases when the query outputs html (such as links). Nothing like looking at a query in the nice format of your source code, as opposed to everything on one line.
itdevxpert
Forum Newbie
Posts: 7
Joined: Mon Feb 13, 2017 1:25 am

Re: Debugging MySQL code .. a few tips.

Post by itdevxpert »

Enable general_log and sudo tail the log file

Two ways to do this:

You can update the my.cnf and restart MySQL. Enable GL:
http://stackoverflow.com/questions/6479 ... -query-log

Easier way with this command:
mysql.server restart --general_log=1 --general_log_file=/usr/local/var/mysql/filename.gen.log
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: Debugging MySQL code .. a few tips.

Post by Celauran »

This is a 13 year old thread. I'm just going to close this now.
Locked