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?

Answer 1

I think it is referring to these:

• http://php.net/manual/en/mysqli.debug.php
• http://php.net/manual/en/mysqli.dump-debug-info.php

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.

Answer 2

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.