MySQL Bugs: #30485: UPDATE Fails but Select works

Bug #30485	UPDATE Fails but Select works
Submitted:	17 Aug 2007 20:21	Modified:	29 Aug 2007 15:59
Reporter:	Dathan Pattishall	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S1 (Critical)
Version:	4.1.23	OS:	Any (2.6.9-34.ELsmp #1 SMP Fri Feb 24 16:56:28 EST 2006 x86_64 x86_64 x86_64 GNU/Linux)
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	4.1, fails, SELECT, UPDATE

Description:
CREATE TABLE `GiftCodes` (
  `code` varchar(32) collate utf8_bin NOT NULL default '',
  `used_by_id` bigint(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (`code`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin

select * from GiftCodes where code='foo'

Finds the row

update GiftCodes set used_by_id='1' where code='foo'

FAILS to find the row

Here are the servers charset defaults

+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | latin1                     |
| character_set_connection | latin1                     |
| character_set_database   | latin1                     |
| character_set_results    | latin1                     |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

SET NAMES is NOT used on connection

How to repeat:

Described in the description

Suggested fix:

Make select and update consistent. If the row can be found via select, it should be found via update.

A work around is to do

UPDATE GiftCodes Set col=1 WHERE code LIKE 'foo';

Also this behavior changed from 4.1.22 to 4.1.23

Thank you for the bug report.

[miguel@skybr 4.1]$ bin/mysql -uroot db77
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 4.1.24-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE `GiftCodes` (
    ->   `code` varchar(32) collate utf8_bin NOT NULL default '',
    ->   `used_by_id` bigint(10) unsigned NOT NULL default '0',
    ->   PRIMARY KEY  (`code`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
Query OK, 0 rows affected (0.00 sec)

mysql> 
mysql> insert into GiftCodes values ('foo',7);
Query OK, 1 row affected (0.00 sec)

mysql> select * from GiftCodes where code='foo';
+------+------------+
| code | used_by_id |
+------+------------+
| foo  |          7 |
+------+------------+
1 row in set (0.00 sec)

mysql> update GiftCodes set used_by_id='1' where code='foo';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql> select * from GiftCodes where code='foo';
+------+------------+
| code | used_by_id |
+------+------------+
| foo  |          7 |
+------+------------+
1 row in set (0.00 sec)

mysql> alter table GiftCodes engine=MyISAM;
Query OK, 1 row affected (0.04 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> update GiftCodes set used_by_id='1' where code='foo';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from GiftCodes where code='foo';
+------+------------+
| code | used_by_id |
+------+------------+
| foo  |          1 |
+------+------------+
1 row in set (0.00 sec)
==========================================================
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.0.48-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE `GiftCodes` (
    ->   `code` varchar(32) collate utf8_bin NOT NULL default '',
    ->   `used_by_id` bigint(10) unsigned NOT NULL default '0',
    ->   PRIMARY KEY  (`code`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
Query OK, 0 rows affected (0.63 sec)

mysql> 
mysql> insert into GiftCodes values ('foo',7);
Query OK, 1 row affected (0.01 sec)

mysql> select * from GiftCodes where code='foo';
+------+------------+
| code | used_by_id |
+------+------------+
| foo  |          7 | 
+------+------------+
1 row in set (0.02 sec)

mysql> update GiftCodes set used_by_id='1' where code='foo';
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from GiftCodes where code='foo';
+------+------------+
| code | used_by_id |
+------+------------+
| foo  |          1 | 
+------+------------+
1 row in set (0.00 sec)

Could this be the same as:

http://bugs.mysql.com/bug.php?id=28878

, i.e., a padding bug in MySQL?

Yes, it is most probably this commit to 4.1 that broke the (yet unpublished) 4.1.23:

http://lists.mysql.com/commits/25686

The code was fixed in 5.0:

http://bugs.mysql.com/bug.php?id=28878

but apparently it is not fixed in 4.1.

I am marking this as a duplicate and reopening 28878.