a well hidden parameter for debugging mysql-driven sites. Maybe it's not mentioned in the documentation because it's subject to change, dunno.; Trace mode. When trace_mode is active (=On), warnings for table/index scans and
; SQL-Erros will be displayed.
mysql.trace_mode = Off
Debugging MySQL code .. a few tips.
Moderator: General Moderators
also note this section in your php.ini
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.
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
- dibyendrah
- Forum Contributor
- Posts: 491
- Joined: Wed Oct 19, 2005 5:14 am
- Location: Nepal
- Contact:
log might help in some extent
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
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
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
and
I'll do a RIGHT JOIN to illustrate my point
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
immediately after this outputs the explain plan run show warnings
This will show you exactly the query that was run
as you can see the optimiser turned this into a LEFT JOIN.
Enjoy.
Obviously everyone uses 'explain' on their queries
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 | | | |
+--------+-------------+------+-----+---------+-------+
Code: Select all
test2
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| field1 | tinyint(4) | NO | | | |
| fk1 | tinyint(4) | NO | | | |
| field2 | varchar(20) | NO | | | |
+--------+-------------+------+-----+---------+-------+
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 |
+--------+------+---------------+--------+--------+
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);
Code: Select all
mysql> show warnings;
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
Enjoy.
By theory, not a great idea. It doesn't check for duplications.code_monkey wrote:These days I always use this bit of code for generating ids rather than relying on numbers;
Then you just use $unique_id when you insert the data into your database. You get a value of something like 'c6127375320d85153ba1b334ab18330a'.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)); } ?>
Re:
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.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.
- superdezign
- DevNet Master
- Posts: 4135
- Joined: Sat Jan 20, 2007 11:06 pm
Re: Debugging MySQL code .. a few tips.
Will anyone ever let this thread die in peace? o_O
~twigletmac posted in this one for Christ's sake.
~twigletmac posted in this one for Christ's sake.
Re: Debugging MySQL code .. a few tips.
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.
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.
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.
To get this (ancient) mostly off-topic thread back on track, two tips I didn't find above:Debugging MySQL code .. a few tips.
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....
";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.
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
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.
This is a 13 year old thread. I'm just going to close this now.