Page 2 of 2
Posted: Wed Mar 10, 2004 11:36 pm
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.
Posted: Wed Mar 31, 2004 6:33 pm
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.
Posted: Tue Jun 29, 2004 5:40 am
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.
Posted: Tue Mar 22, 2005 6:56 am
by Shendemiar
I sometime had problems with different quotes. Fieldnames should be surrounded with ` s while values should have ' s around.
log might help in some extent
Posted: Tue Apr 04, 2006 1:55 am
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
Posted: Tue Mar 06, 2007 12:19 pm
by mikeq
This is a tip for use when optimising your queries.
Obviously everyone uses 'explain' on their queries

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
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.
Posted: Tue Mar 06, 2007 4:06 pm
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.
Re:
Posted: Tue Jun 10, 2008 8:46 am
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.
Re: Debugging MySQL code .. a few tips.
Posted: Tue Jun 10, 2008 10:33 am
by superdezign
Will anyone ever let this thread die in peace? o_O
~twigletmac posted in this one for Christ's sake.

Re: Debugging MySQL code .. a few tips.
Posted: Fri Sep 02, 2011 5:56 am
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.
Re: Debugging MySQL code .. a few tips.
Posted: Wed Jan 11, 2012 7:59 pm
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.
Re: Debugging MySQL code .. a few tips.
Posted: Sun Jul 28, 2013 5:28 pm
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.
Re: Debugging MySQL code .. a few tips.
Posted: Mon Feb 13, 2017 2:08 am
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
Re: Debugging MySQL code .. a few tips.
Posted: Mon Feb 13, 2017 9:56 am
by Celauran
This is a 13 year old thread. I'm just going to close this now.