Posted by ben on 11/11/05 19:18

Hi all,

I am experiencing a strange problem with MySQL 4.1.15-standard.

I have a table with one row in (for purposes of reproducing this issue).
The SQL is below.

When I try to run the following update, it does not work! There is no
error, just 0 affected rows.

update testupdate SET Status='Newstatus' WHERE SecondaryID='3321822951131719083090';

If I change the collation to latin1_swedish_ci, or remove the SecondaryID
index, or change the SecondaryID index to not be just the first 16
characters, it works fine.

I guess this is a bug, but if anyone could shed any light on it, it would
be appreciated.
Doing a "SELECT * WHERE SecondaryID='3321822951131719083090';" works fine.

All character sets are utf8 (client, connection, database, results, server
and system) and all collations are utf8_general_ci

Any thoughts appreciated!

Cheers,

Ben

CREATE TABLE `testupdate` (
`PrimaryID` bigint(20) NOT NULL auto_increment,
`Status` varchar(64) collate utf8_unicode_ci default NULL,
`SecondaryID` varchar(64) collate utf8_unicode_ci default NULL,
PRIMARY KEY (`PrimaryID`),
KEY `SecondaryID` (`SecondaryID`(16))
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=4 ;

--
-- Dumping data for table `testupdate2`
--

INSERT INTO `testupdate` VALUES (1, 'Pending', '3321822951131719083090');

[Back to original message]