MySQL Bugs: #49983: Can't update/delete Binary rows

Bug #49983	Can't update/delete Binary rows
Submitted:	30 Dec 2009 0:09	Modified:	30 Dec 2009 11:59
Reporter:	Marian Lander	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.4.3-Beta	OS:	Linux
Assigned to:		CPU Architecture:	Any

Description:
I'm using Mysql 5.4.3-Beta on a Linux Slackware 13.0 Server, I compile from source mysql package. I'm trying to update and delete some rows but I'm not able to with binary data rows, I even alter the table to varbinary data without any success:

mysql> alter table example1 modify name varbinary(20);
Query OK, 3 rows affected (0.11 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from example1;
+----------------------+
| name                 |
+----------------------+
| Jhon                 |
| Mary                 |
| Lucas                |
+----------------------+
3 rows in set (0.00 sec)

mysql> delete from example1 where name = 'Jhon';
Query OK, 0 rows affected (0.00 sec)

mysql> select * from example1;
+----------------------+
| name                 |
+----------------------+
| Jhon                 |
| Mary                 |
| Lucas                |
+----------------------+
3 rows in set (0.00 sec)

mysql> update example1 set name = 'Pierre' where name = 'Jhon';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

But if I create another table with varbinary row I'm able to delete and update data. Any help could be appreciate.

How to repeat:

1 - Create a Table with binary data type:

mysql> create table example1(
    -> name binary(20));     
Query OK, 0 rows affected (0.04 sec)

2 - Insert some values :

mysql> insert into example1 values('Jhon'),('Mary'),('Lucas');
Query OK, 3 rows affected (0.00 sec)                          
Records: 3  Duplicates: 0  Warnings: 0     

3 - Try to update or delete some row

mysql> select * from example1;
+----------------------+      
| name                 |      
+----------------------+
| Jhon                 |
| Mary                 |
| Lucas                |
+----------------------+
3 rows in set (0.00 sec)

mysql> update example1 set name = 'Pierre' where name = 'Jhon';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql> select * from example1;
+----------------------+
| name                 |
+----------------------+
| Jhon                 |
| Mary                 |
| Lucas                |
+----------------------+
3 rows in set (0.00 sec)

mysql> delete from example1 where name = 'Lucas';
Query OK, 0 rows affected (0.00 sec)

mysql> select * from example1;
+----------------------+
| name                 |
+----------------------+
| Jhon                 |
| Mary                 |
| Lucas                |
+----------------------+
3 rows in set (0.00 sec)

Please read: http://dev.mysql.com/doc/refman/5.1/en/binary-varbinary.html

"10.4.2. The BINARY and VARBINARY Types

The BINARY and VARBINARY types are similar to CHAR and VARCHAR, except that they contain binary strings rather than nonbinary strings. That is, they contain byte strings rather than character strings. This means that they have no character set, and sorting and comparison are based on the numeric values of the bytes in the values. "........

Thanks so much for your reply. I understand completely the information provide by yourself about binary/varbinary types. But my doubt is, why I'm able to update or delete varbinary rows but in the other hand I don't have the same luck with binary rows. Since Varbinary and Binary are almost the same (both manage characters as binary instead of characters) why I'm able to update or delete with one (varbinary) and not with the other (binary)?

I even alter the database to change binary rows to varbinary since I really need to update some rows, but this is useless... if I create a new table with varbinary rows types I'm able to update or delete, but if I already have a table with binary rows I can't do anything on it, basically only INSERT new values, so it would be very helpfull if I can solve this issue.

Thanks ind Advanced.

I'm trying other ways to see if I'm able to update some binary rows.. still no success, please review:

This one works normally

mysql> create table example1(
    -> name varbinary(20));  
Query OK, 0 rows affected (0.05 sec)

mysql> insert into example1 values('Jhon'),('Scott'),('Eloise');
Query OK, 3 rows affected (0.00 sec)                            
Records: 3  Duplicates: 0  Warnings: 0 

mysql> update example1 set name = 'Courtney' where name = 'Scott';
Query OK, 1 row affected (0.00 sec)                               
Rows matched: 1  Changed: 1  Warnings: 0                          

mysql> select * from example1;
+----------+                  
| name     |                  
+----------+                  
| Jhon     |                  
| Courtney |                  
| Eloise   |                  
+----------+                  
3 rows in set (0.00 sec)      

But if I create a new table with binary row, no success not even with alter:

mysql> create table example(
    -> name binary(20));    
Query OK, 0 rows affected (0.04 sec)

mysql> insert into example values('Cindy'),('Monike'),('Jean');
Query OK, 3 rows affected (0.00 sec)                           
Records: 3  Duplicates: 0  Warnings: 0       

mysql> select * from example;
+----------------------+     
| name                 |     
+----------------------+     
| Cindy                |     
| Monike               |     
| Jean                 |     
+----------------------+     
3 rows in set (0.00 sec)     

mysql> delete from example where name = 'Cindy';
Query OK, 0 rows affected (0.00 sec)            

mysql> select * from example;
+----------------------+     
| name                 |     
+----------------------+     
| Cindy                |     
| Monike               |     
| Jean                 |     
+----------------------+     
3 rows in set (0.00 sec)    

mysql> alter table example modify name varbinary(20);
Query OK, 3 rows affected (0.05 sec)                 
Records: 3  Duplicates: 0  Warnings: 0               

mysql> select * from example;
+----------------------+     
| name                 |     
+----------------------+     
| Cindy                |     
| Monike               |
| Jean                 |
+----------------------+
3 rows in set (0.00 sec)

mysql> update example set name = 'Cucu' where name = 'Cindy';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql> alter table example modify name varchar(20);
Query OK, 3 rows affected (0.05 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> update example set name = 'Cucu' where name = 'Cindy';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql> select * from example;
+----------------------+
| name                 |
+----------------------+
| Cindy                |
| Monike               |
| Jean                 |
+----------------------+
3 rows in set (0.00 sec)

Not even changing the data structure to varchar...

Any thoughts?

Thank you for the report.

BINARY is fixed-length type: it adds leading zeros to data. You can easily see this using HEX function:

select hex(name), hex('Jhon'), hex('Lucas') from example1;
hex(name)       hex('Jhon')     hex('Lucas')
4A686F6E00000000000000000000000000000000        4A686F6E        4C75636173
4D61727900000000000000000000000000000000        4A686F6E        4C75636173
4C75636173000000000000000000000000000000        4A686F6E        4C75636173

So strings are not same. When you convert field to VARBINARY leading zeros are not removed, so you still can not access data with queries provided.

Thanks for reply, I really appreciate all your help. 

But unfortunately, I still not able to update 20,000 binary rows that I really need to update. I'm pasting an example, cause I still have no luck at all:

mysql> select * from example;
+----------------------+
| name                 |
+----------------------+
| Jhon                 |
| Cindy                |
| Mary                 |
+----------------------+
3 rows in set (0.00 sec)

mysql> describe example;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| name  | binary(20) | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
1 row in set (0.00 sec)

I'm trying to update passing a parameter to HEX function:

mysql> update example set name = 'Xuxu' where name = hex('Jhon');
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

And still isn't working :(

mysql> select * from example;
+----------------------+
| name                 |
+----------------------+
| Jhon                 |
| Cindy                |
| Mary                 |
+----------------------+
3 rows in set (0.00 sec)

I already have alter the table and change several times, name row to varchar type, and I still can't update the rows:

mysql> describe example;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| name  | binary(20) | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> alter table example change name names varchar(20);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

Shutdown the MySQL server and restart it again, login and try to update some row:

mysql> select * from example;
+----------------------+
| names                |
+----------------------+
| Jhon                 |
| Cindy                |
| Mary                 |
+----------------------+
3 rows in set (0.00 sec)

mysql> update example set names = 'Luc' where names = 'Jhon';

mysql> select * from example;
+----------------------+
| names                |
+----------------------+
| Jhon                 |
| Cindy                |
| Mary                 |
+----------------------+
3 rows in set (0.00 sec)

mysql> show columns from example;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| names | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
1 row in set (0.00 sec)

I'm very sorry for asking to much, but it seems there's no way I can update or delete any rows even if I alter the table to varchar type, it just keep working as if where a binary type..

Thanks in advanced for all your help.