MySQL Bugs: #63198: unable to edit record when primary key is a binary field

Bug #63198	unable to edit record when primary key is a binary field
Submitted:	11 Nov 2011 8:12	Modified:	13 Sep 2012 6:34
Reporter:	Gianni Gentile	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Workbench: SQL Editor	Severity:	S2 (Serious)
Version:	5.2.35	OS:	Windows
Assigned to:		CPU Architecture:	Any
Tags:	BINARY, editing, primary, workbenck

Description:
Can't edit table data if the table has a binary field as primary key.

table:
CREATE TABLE `customer` (
  `uid` binary(16) NOT NULL DEFAULT '\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0',
  `address` varchar(127) NOT NULL,
  `htmlemail` tinyint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`uid`),
);

How to repeat:

1. click the table 'customer' using mouse right button
2. select the menu item "Edit Table Data" from the popup menu
3. edit 'htmlemail' field and click 'Apply changes to data'
4. check the generated sql statement:

    UPDATE `mydb`.`customer` SET `htmlemail`=1 WHERE `uid`=?;

5. clicking 'Apply' will - obviously - output:

    ERROR 0: Value not set for all parameters

Suggested fix:
in case of binary key, generate parameter in either way:

    UPDATE `mydb`.`customer`
    SET `htmlemail`=1
    WHERE `uid`=unhex('4B932678B24D21A851434D3EC31D6BFD');

or

    UPDATE `mydb`.`customer`
    SET `htmlemail`=1
    WHERE `uid`=CAST(0x4B932678B24D21A851434D3EC31D6BFD AS BINARY);

0x4B932678B24D21A851434D3EC31D6BFD is the actual field value.

Thank you for the bug report. Indeed, you can insert new rows, but attempt to update non-PK column leads to this error.

Fixed as of the upcoming MySQL Workbench 5.2.43, and here's the changelog entry:

  Table data could not be edited if the primary key was a binary field. 

Thank you for the report.