Fatal error: Uncaught exception 'mysqli_sql_exception' with message 'No index used in query/prepared statement'-Collection of common programming errors

2 years ago
admin
2 minutes

When I run the following code, I get the error saying

Fatal error: Uncaught exception ‘mysqli_sql_exception’ with message ‘No index used in query/prepared statement’

$mysql = new mysqli(DB_SERVER, DB_USER, DB_PASSWORD, DB_NAME) or die('There was a problem connecting to the database');
        if (mysqli_connect_errno()) {
            printf("DB error: %s", mysqli_connect_error());
            exit();
        }

    $get_emp_list = $mysql->prepare("SELECT id, name FROM calc");
    if(!$get_emp_list){
        echo "prepare failed\n";
        echo "error: ", $mysql->error, "\n";
        return;
    }
    $get_emp_list->execute();
    $get_emp_list->bind_result($id, $emp_list);

And this is the able schema —

--
-- Table structure for table `calc`
--

CREATE TABLE IF NOT EXISTS `calc` (
  `id` int(12) NOT NULL,
  `yr` year(4) NOT NULL,
  `mnth` varchar(12) NOT NULL,
  `name` varchar(256) NOT NULL,
  `paidleave` int(12) NOT NULL,
  `balanceleave` int(12) NOT NULL,
  `unpaidleave` int(12) NOT NULL,
  `basesalary` int(12) NOT NULL,
  `deductions` int(12) NOT NULL,
  `tds` int(12) NOT NULL,
  `pf` int(12) NOT NULL,
  `finalsalary` int(12) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

  1. Take a look at this bug-report : #35450 mysqli extension reports too many warnings

    Quoting a few sentences of a note :

    Mysqli extension throws too many warnings.
    For example, “SELECT * FROM table” results in a warning: “Warning: mysqli::query(): No index used in query/prepared statement SELECT * FROM table …”

    And, quoting another note, which seems interesting :

    Use mysqli_report() to disable that.

    Humph, unfortunately, that function is deprecated…

  2. The fatal error is not in MySQL; the missing index notification is a relatively low-severity warning.

    The fatal error is in your PHP code, because of the following three conditions:

    • mysqli reports a lot of warnings, even for relatively harmless conditions.
    • You’re throwing mysqli_sql_exception for all errors and warnings due to your mysqli_report(MYSQLI_REPORT_ALL); line.
    • Your PHP code is not catching that exception (i.e. it’s not in a try{} block with an appropriate catch(){} block), and uncaught exceptions are fatal.

    You can’t do much about the first one, as mentioned in the other answer. So, you can fix it either by changing your mysqli_report(...) setting to MYSQLI_REPORT_STRICT or MYSQLI_REPORT_OFF (or indeed anything other than MYSQLI_REPORT_ALL), or in combination with this, by properly by using try{} and catch(){} appropriately within your code.

Originally posted 2013-11-23 19:34:06.