Bug #29467 Updating records of tables having a CHAR() as PrimaryKey-component fails

• Submitted: 1 Jul 2007 11:35
• Modified: 1 Jul 2007 15:01
• Reporter: Rodney Rehm
• Status: Duplicate
• Category: MySQL Server: Data Types
• Severity: S3 (Non-critical)
• Version: 5.0.42
• OS: Linux (gentoo)
• CPU Architecture: Any

[1 Jul 2007 11:35] Rodney Rehm

Description:
I just thought i'd give CHAR(32) a try, since that column's values will always be exactly 32 characters long. The column needs to be part of a compund PrimaryKey. Inserting and selecting records from the table works just fine, but when it gets to updating records, mysql fails without an error. It simply states "0 rows affected".

I "solved" the problem by falling back to VARCHAR(32).

How to repeat:
/*
 *  CREATE DEMONSTRATION TABLE `testable`
 *  HAVING A COMPOUND PRIMARY_KEY CONSISTING
 *  OF A STRING AND AN INTEGER
 */

CREATE TABLE `testable` (
  `prim_one` char(32) NOT NULL,
  `prim_two` int(10) unsigned NOT NULL,
  `payload` varchar(200) NOT NULL,
  PRIMARY KEY  (`prim_one`,`prim_two`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*
 *  INSERT A DUMMY RECORD
 *  -> works just fine
 */

INSERT INTO `rodocs`.`testable` (`prim_one`, `prim_two`, `payload`)
VALUES ('abcdefghabcdefghabcdefghabcdefgh', '23', 'hello world');

/*
 *  SELECT THE DUMMY RECORD
 *  -> works just fine
 */

SELECT * 
  FROM `testable` 
 WHERE `prim_one` = 'abcdefghabcdefghabcdefghabcdefgh' 
   AND `prim_two` = '23'

/*
 *  UPDATE THE DUMMY RECORD
 *  -> 0 Records affected -- WTF?
 */

UPDATE `testable` SET `payload` = 'foobar' 
WHERE `prim_one` = 'abcdefghabcdefghabcdefghabcdefgh' 
  AND `prim_two` = '23'

[1 Jul 2007 15:01] MySQL Verification Team

Thank you for the bug report. Duplicate of bug:
http://bugs.mysql.com/bug.php?id=28878

[miguel@light 5.0]$ bin/mysqladmin -uroot create rodocs
[miguel@light 5.0]$ bin/mysql -uroot rodocs
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.0.46-debug Source distribution

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

mysql> CREATE TABLE `testable` (
    ->   `prim_one` char(32) NOT NULL,
    ->   `prim_two` int(10) unsigned NOT NULL,
    ->   `payload` varchar(200) NOT NULL,
    ->   PRIMARY KEY  (`prim_one`,`prim_two`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO `rodocs`.`testable` (`prim_one`, `prim_two`, `payload`)
    -> VALUES ('abcdefghabcdefghabcdefghabcdefgh', '23', 'hello world');
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * 
    ->   FROM `testable` 
    ->  WHERE `prim_one` = 'abcdefghabcdefghabcdefghabcdefgh' 
    ->    AND `prim_two` = '23'
    -> ;
+----------------------------------+----------+-------------+
| prim_one                         | prim_two | payload     |
+----------------------------------+----------+-------------+
| abcdefghabcdefghabcdefghabcdefgh |       23 | hello world | 
+----------------------------------+----------+-------------+
1 row in set (0.05 sec)

mysql> UPDATE `testable` SET `payload` = 'foobar' 
    -> WHERE `prim_one` = 'abcdefghabcdefghabcdefghabcdefgh' 
    ->   AND `prim_two` = '23';
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * 
    ->   FROM `testable` 
    ->  WHERE `prim_one` = 'abcdefghabcdefghabcdefghabcdefgh' 
    ->    AND `prim_two` = '23';
+----------------------------------+----------+---------+
| prim_one                         | prim_two | payload |
+----------------------------------+----------+---------+
| abcdefghabcdefghabcdefghabcdefgh |       23 | foobar  | 
+----------------------------------+----------+---------+
1 row in set (0.00 sec)

mysql>