What are Mysqli's enhanced debugging capabilities?

Question

On the mysqli overview page there is a list of benefits, intended to lure me into mysqli realm. I don't even understand the meaning of many of them, but one is really interesting to me: enhanced debugging capabilities.

According to my experience, prepared statements (which considered to be main and mostly used mysqli feature) makes debugging dynamical SQL pretty hard - you just cannot have regular query out of prepared query to be copied in console.
So, I am eager to know what are these capabilities and how to use them.
Is it on debugging SQL or on something else?
How to use it?
What are practical use cases?

Cannot tell if trolling or trying to make a self-answered reference question. ;) — deceze, Feb 03 '13 at 08:42
http://stackoverflow.com/questions/962986/how-to-echo-a-mysqli-prepared-statement — KennyPowers, Feb 03 '13 at 09:36
@KennyPowers Thank you for comment. I am asking not "what measures I can take to debug prepared statements" (I am not using them, so there is no problem) but "what are these enhanced debugging capabilities mentioned on the overview page, and what is everyday use of them". — Your Common Sense, Feb 03 '13 at 09:41

eis · Answer 1 · 2013-02-03T10:16:11.383

I think it is referring to these:

They do not exist with mysql api, only mysqli.

You can, among other things, create trace files:

mysqli_debug("d:t:o,/tmp/client.trace");

I understand the tracing as being about debugging MySQL internal behaviour. If you'd suspect there's a problem in how MySQL works, you could use this. For debugging SQL, I would use more light-weight measures, even if tracing does show those too.

You can see an example of such trace output on this page.

artragis · Answer 2 · 2013-02-03T11:27:23.283

1

About prepared statement, theoricaly, you will know your kwery is bad formatted before sending the parameters. So you will not have to wonder if it is your query or the user datas which are bugged, you know it immediately.

About general queries, as mysqli is in sync with MySQL5 whereas mysql_ old API supports only until MYSQL 4, you will have better error message, or at least most descriptive.

Moreover you can use the try\catch syntax to detect mysql errors and it is easier to deal with exceptions instead of or die.... or if(mysql_error()).

from documentation you have :

<?php
define("MYSQL_CONN_ERROR", "Unable to connect to database.");

// Ensure reporting is setup correctly
 mysqli_report(MYSQLI_REPORT_STRICT);

    // Connect function for database access
   function connect($usr,$pw,$db,$host) {

   try {
       $mysqli = new mysqli($host,$usr,$pw,$db);
       $connected = true;
    } catch (mysqli_sql_exception $e) {
       throw $e;
    }
}

try {
   connect('username','password','database','host');
   echo 'Connected to database';
} catch (Exception $e) {
    echo $e->errorMessage();
}

the key function is mysqli_report(MYSQLI_REPORT_STRICT), according to documentation :

MYSQLI_REPORT_STRICT

Throw a mysqli_sql_exception for errors instead of warnings.

there is also a mysqli_debug function/method as eis said.

edited Feb 03 '13 at 11:27

answered Feb 03 '13 at 09:52

artragis

3,677
1
18
30

Can you please show a working example with try\catch syntax to detect mysql errors with mysqli? Thanks in advance. – Your Common Sense Feb 03 '13 at 11:13
thanks. I hope it works not like `get_result()`- whenever it feels in the mood to, but whenever I need it. Anyway, I'd stuck with `if(mysql_error())` for a while. – Your Common Sense Feb 03 '13 at 11:53
what do you mean by "not like `get_result()`? – artragis Feb 03 '13 at 12:33
When I try to use indispensable function get_result(), it works only with certain builds. So, I cannot rely on this function. So, I hope this exception functionality works not on the same basis, because when I am writhing the code, I expect it to work, not throwing errors. – Your Common Sense Feb 03 '13 at 12:43
exceptions have been created in php 5. So with php 4 it won't work, of course. But since you have php 5 + (today's : 5.4) everything will work. – artragis Feb 03 '13 at 12:47
Anyway, although exceptions are irrelevant to the initial question, this mysqli_report() function with all it's other flags seems the best answer to the everyday debugging question so far. – Your Common Sense Feb 03 '13 at 13:06
mysqli_debug et mysqli_report are perfect. – artragis Feb 03 '13 at 13:27
Oh, that's great to find a person who is familiar with this new stuff! Can you please provide an example of something useful out of mysqli_debug(), as good as one you provided for mysqli_report()? – Your Common Sense Feb 03 '13 at 13:30
Will try in my answer. but @eis exposed it perfectly. – artragis Feb 03 '13 at 13:36
I can't see anything useful in the example page from his link. Most likely it's my ignorance - so, it would be wonderful if you explain some of this output along with some practical case. – Your Common Sense Feb 03 '13 at 13:42
as a matter of fact, apart from create trace file, I never used mysqli_debug. Moreover, I do prefer PDO over mysqli. I think I will try to test it a bit more before completing my answer. – artragis Feb 03 '13 at 13:45

What are Mysqli's enhanced debugging capabilities?

2 Answers2