Page 1 of 1

mysqli_sql_exception - 'No index used in query/prepared

Posted: Sat Jun 10, 2006 7:47 pm
by laiseng
i have this error
Fatal error: Uncaught exception 'mysqli_sql_exception' with message 'No index used in query/prepared statement SELECT * FROM members' in /var/www/html/myfyp/db/members.class.php:142 Stack trace: #0 /var/www/html/myfyp/db/members.class.php(142): mysqli->query('SELECT * FROM m...') #1 /var/www/html/myfyp/login.php(13): members->login('admin', 'admin1') #2 {main} thrown in /var/www/html/myfyp/db/members.class.php on line 142

how do i fix this?

what does it mean by no index?

Posted: Sat Jun 10, 2006 7:52 pm
by derchris
Looks like an error with your DB.
How does your table structure looks like ?

Posted: Sat Jun 10, 2006 8:02 pm
by laiseng
it look like this

Code: Select all

CREATE TABLE `members` (
  `ID` int(10) unsigned NOT NULL auto_increment,
  `USERNAME` varchar(200) NOT NULL,
  `PASSWORD` varchar(200) NOT NULL,
  `FIRSTNAME` varchar(200) NOT NULL,
  `LASTNAME` varchar(200) NOT NULL,
  `DOB` date NOT NULL,
  `COUNTRY` varchar(200) NOT NULL,
  `STATE` varchar(200) default NULL,
  `ZIP` int(5) NOT NULL,
  `BLOGURL` varchar(500) NOT NULL,
  `EMAIL` varchar(200) NOT NULL,
  PRIMARY KEY  (`ID`),
  UNIQUE KEY `ID` (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=41 ;

i've able to by pass this fatal error by doing mysqli_report(MYSQLI_REPORT_OFF)

does this mean that my db and code are alright after turning off the report and beable to excute the script without error?

Posted: Sat Jun 10, 2006 8:12 pm
by derchris
I think it is because you set

PRIMARY KEY (`ID`),
UNIQUE KEY `ID` (`ID`)

which is somehow possible but should lead to errors, like in your case.

Posted: Tue Oct 03, 2006 1:59 pm
by torpedo83
I've the same problem 'No index used in query/prepared statement' even if i've a simple table without PK or UNIQUE columns.... so i don't know what causes the problem...

The code i used is below:

Code: Select all

$conn = new mysqli ($IP, $USERNAME,$PASSWORD,$DB_NAME);
if (mysqli_connect_errno()) die ("Error while connecting!");

try 
{
	$result = $conn->query("SELECT * FROM articoli LIMIT 10");
}
catch (mysqli_sql_exception $e)
{
	echo $e->getMessage();
}

Posted: Tue Oct 03, 2006 7:11 pm
by torpedo83
:D :D :D Ok i've fixed the problem ... what i did was just TURNING OFF the mysqli error reporting system just using this instruction:

Code: Select all

mysqli_report (MYSQLI_REPORT_OFF);
So be careful when you decide to use it, just because it throws FATAL and not WARNING or NOTICE error that is to say script blocking!!!!

Posted: Tue Oct 03, 2006 7:18 pm
by volka
The message states that the mysql server has to perform a full table scan, i.e. has to check on every record wether it belongs into the result set or not. That's not what a database is supposed to do in the first place.