MySQL Bugs: #29449: Updates on InnoDB table with CHAR primary key column are not processed

Bug #29449	Updates on InnoDB table with CHAR primary key column are not processed
Submitted:	29 Jun 2007 16:47	Modified:	29 Jun 2007 17:08
Reporter:	Chris Jacobson	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.42-enterprise-gpl-log	OS:	Solaris (Solaris 10 / AMD64)
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	char, innodb, primary key, UPDATE

Description:
For a table created with a CHAR() primary key, updates using the primary key value in a WHERE clause will not update the table.

How to repeat:
mysql> CREATE TABLE `test_table` (
    ->   `pkey` char(16) NOT NULL,
    ->   `value` varchar(255) NOT NULL,
    ->   PRIMARY KEY  (`pkey`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (3.83 sec)

mysql> INSERT INTO test_table (pkey, value) VALUES ('KEY_1', 'VAL_1');
Query OK, 1 row affected (0.05 sec)

mysql> INSERT INTO test_table (pkey, value) VALUES ('KEY_2', 'VAL_2');
Query OK, 1 row affected (0.24 sec)

mysql> INSERT INTO test_table (pkey, value) VALUES ('KEY_3', 'VAL_3');
Query OK, 1 row affected (0.11 sec)

mysql> SELECT * FROM test_table WHERE pkey = 'KEY_1';
+-------+-------+
| pkey  | value |
+-------+-------+
| KEY_1 | VAL_1 | 
+-------+-------+
1 row in set (0.00 sec)

mysql> UPDATE test_table SET value = 'NEW_VALUE' WHERE pkey = 'KEY_1';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql> SELECT * FROM test_table WHERE pkey = 'KEY_1';                 
+-------+-------+
| pkey  | value |
+-------+-------+
| KEY_1 | VAL_1 | 
+-------+-------+
1 row in set (0.00 sec)

** However, you can update using any other field in the table **

mysql> UPDATE test_table SET value = 'NEW_VALUE' WHERE value = 'VAL_1';
Query OK, 1 row affected (0.06 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM test_table WHERE pkey = 'KEY_1';                  
+-------+-----------+
| pkey  | value     |
+-------+-----------+
| KEY_1 | NEW_VALUE | 
+-------+-----------+
1 row in set (0.01 sec)

Suggested fix:
Changing the primary key to a VARCHAR() field will stop this behavior.

I can repeat with MySQL-5.0.44/InnoDB-5.0.4x:

heikki@ws35:~/mysql-5.0/client$ ./mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.44-debug-log Source distribution

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

mysql> CREATE TABLE `test_table` (
    ->        `pkey` char(16) NOT NULL,
    ->        `value` varchar(255) NOT NULL,
    ->        PRIMARY KEY  (`pkey`)
    ->      ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.06 sec)

mysql> INSERT INTO test_table (pkey, value) VALUES ('KEY_1', 'VAL_1');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO test_table (pkey, value) VALUES ('KEY_2', 'VAL_2');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO test_table (pkey, value) VALUES ('KEY_3', 'VAL_3');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM test_table WHERE pkey = 'KEY_1';
+-------+-------+
| pkey  | value |
+-------+-------+
| KEY_1 | VAL_1 |
+-------+-------+
1 row in set (0.02 sec)

mysql> UPDATE test_table SET value = 'NEW_VALUE' WHERE pkey = 'KEY_1';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

There was an earlier bug report where the problem was that MySQL in a WHERE clause padded UTF-8 CHAR columns with 0x00, and not with 0x20 like it should.

I guess this is a duplicate of that ultra-critical bug.

With current source server and Linux 32-bit I can't repeat. I will test
with 64-bit server.

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.0.46-debug Source distribution

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

mysql> CREATE TABLE `test_table` (
    -> `pkey` char(16) NOT NULL,
    ->  `value` varchar(255) NOT NULL,
    ->  PRIMARY KEY  (`pkey`)
    ->  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.09 sec)

mysql> INSERT INTO test_table (pkey, value) VALUES ('KEY_1', 'VAL_1');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO test_table (pkey, value) VALUES ('KEY_2', 'VAL_2');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO test_table (pkey, value) VALUES ('KEY_3', 'VAL_3');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM test_table WHERE pkey = 'KEY_1';
+-------+-------+
| pkey  | value |
+-------+-------+
| KEY_1 | VAL_1 | 
+-------+-------+
1 row in set (0.01 sec)

mysql> UPDATE test_table SET value = 'NEW_VALUE' WHERE pkey = 'KEY_1';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM test_table WHERE pkey = 'KEY_1';   
+-------+-----------+
| pkey  | value     |
+-------+-----------+
| KEY_1 | NEW_VALUE | 
+-------+-----------+
1 row in set (0.00 sec)

mysql>

This is a duplicate of http://bugs.mysql.com/bug.php?id=28878