MySQL Bugs: #30019: Not able to select on field after insert

Bug #30019	Not able to select on field after insert
Submitted:	24 Jul 2007 22:51	Modified:	24 Jul 2007 23:48
Reporter:	Joseph Norris	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connectors: DBD::mysql ( Perl )	Severity:	S2 (Serious)
Version:	5.0.37	OS:	Linux
Assigned to:		CPU Architecture:	Any

Description:
Perl script does a standard insert:

insert into u (login,password) values (?,?)

$cmd->execute('john','pass');

at mysql client prompt:

select login,password from u where login = 'john';

no returned record

select index,login,password from u where login like 'joh%';

record returned - obtain index = 454 - index autoincrement

update u set login = 'abc' where index=454;

record updated

select index,login,password from u where login = 'abc';

no record returned

then.....

from mysql client prompt

insert into u (login,password) values ('jack','pass2');

and then do a 

select login,password from u where login = 'jack';

returns record.

How to repeat:
CREATE TABLE `u` (
  `cs_rid` int(11) NOT NULL auto_increment,
  `cs_mtime` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `cs_ctime` datetime default NULL,
  `cs_uid` int(11) default NULL,
  `cs_owner` varchar(50) character set latin1 collate latin1_bin default NULL,
  `cs_status` int(11) default NULL,
  `cs_community` int(11) default NULL,
  `ice_rid` int(11) default NULL,
  `title` varchar(100) default NULL,
  `firstname` varchar(100) default NULL,
  `initial` char(2) default NULL,
  `lastname` varchar(100) default NULL,
  `org` varchar(100) default NULL,
  `login` varchar(50) character set latin1 collate latin1_bin NOT NULL default '',
  `password` varchar(50) default NULL,
  `password2` varchar(50) default NULL,
  `email` varchar(100) default NULL,
  `address` text,
  `city` varchar(100) default NULL,
  `state` varchar(250) default NULL,
  `zip` varchar(12) default NULL,
  `country` varchar(100) default NULL,
  `phone` varchar(15) default NULL,
  `fax` varchar(15) default NULL,
  `url` varchar(100) default NULL,
  `newsletter` char(3) default NULL,
  `staff_id` varchar(50) default NULL,
  `bday` date default NULL,
  `id_program` varchar(250) default NULL,
  `photo_url` varchar(50) default NULL,
  `bio` text,
  `listlength` varchar(10) default NULL,
  `usertype` varchar(100) default NULL,
  `inactive` varchar(5) default NULL,
  `no_email` varchar(5) default NULL,
  `email2` varchar(100) default NULL,
  `last_login` datetime default NULL,
  `last_chpw` datetime default NULL,
  `person` varchar(5) default NULL,
  `picurl` varchar(100) default NULL,
  `helpdesk_program` int(11) default NULL,
  `program_id` varchar(250) default NULL,
  `partners` varchar(250) default NULL,
  `activityfocus` varchar(250) default NULL,
  `service` varchar(250) default NULL,
  `staffims` text,
  `costcode` text,
  `splanpg` varchar(50) default NULL,
  `jobrole` varchar(250) default NULL,
  `gender` varchar(250) default NULL,
  `edlevel` varchar(250) default NULL,
  `supervisor` int(11) default NULL,
  `prel` varchar(50) default NULL,
  `helpdesk` varchar(20) default NULL,
  `ims` varchar(20) default NULL,
  PRIMARY KEY  (`cs_rid`),
  UNIQUE KEY `login_ref` (`login`)
) ENGINE=MyISAM AUTO_INCREMENT=583 DEFAULT CHARSET=latin1;

Sorry I got the order of the fields wrong.  One other item - the original version of the db was varchar(50) for login.  New version is varchar(50) binary. I do not know if this makes a difference or not.

I found the problem - corrupt index.  I dropped the index and re-added it and all is right with the world. Thanks