Description:
Altering a table from varchar->binary->varchar on a column, yields a permanent row modification that you don't expect.
How to repeat:
create table t1 (name varchar(100));
insert into t1 values ('one two three');
alter table t1 change name name binary(100);
alter table t1 change name name varchar(100); <-- now the column is right padded with \0 ... I think it should be wise enough to rtrim on \0.
Cut/paste:
(root@localhost) [test]> create table t1 (name varchar(100));
Query OK, 0 rows affected (0.06 sec)
(root@localhost) [test]> insert into t1 values ('one two three');
Query OK, 1 row affected (0.00 sec)
(root@localhost) [test]> select * from t1;
+---------------+
| name |
+---------------+
| one two three |
+---------------+
1 row in set (0.00 sec)
(root@localhost) [test]> alter table t1 change name name binary(100);
Query OK, 1 row affected (0.05 sec)
Records: 1 Duplicates: 0 Warnings: 0
(root@localhost) [test]> select * from t1;
+------------------------------------------------------------------------------------------------------+
| name |
+------------------------------------------------------------------------------------------------------+
| one two three |
+------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
(root@localhost) [test]> alter table t1 change name name varchar(100);
Query OK, 1 row affected (0.04 sec)
Records: 1 Duplicates: 0 Warnings: 0
(root@localhost) [test]> select * from t1;
+------------------------------------------------------------------------------------------------------+
| name |
+------------------------------------------------------------------------------------------------------+
| one two three |
+------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
(root@localhost) [test]> update t1 set name = replace(name,'\0','');
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
(root@localhost) [test]> select * from t1;
+---------------+
| name |
+---------------+
| one two three |
+---------------+
1 row in set (0.00 sec)
Description: Altering a table from varchar->binary->varchar on a column, yields a permanent row modification that you don't expect. How to repeat: create table t1 (name varchar(100)); insert into t1 values ('one two three'); alter table t1 change name name binary(100); alter table t1 change name name varchar(100); <-- now the column is right padded with \0 ... I think it should be wise enough to rtrim on \0. Cut/paste: (root@localhost) [test]> create table t1 (name varchar(100)); Query OK, 0 rows affected (0.06 sec) (root@localhost) [test]> insert into t1 values ('one two three'); Query OK, 1 row affected (0.00 sec) (root@localhost) [test]> select * from t1; +---------------+ | name | +---------------+ | one two three | +---------------+ 1 row in set (0.00 sec) (root@localhost) [test]> alter table t1 change name name binary(100); Query OK, 1 row affected (0.05 sec) Records: 1 Duplicates: 0 Warnings: 0 (root@localhost) [test]> select * from t1; +------------------------------------------------------------------------------------------------------+ | name | +------------------------------------------------------------------------------------------------------+ | one two three | +------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) (root@localhost) [test]> alter table t1 change name name varchar(100); Query OK, 1 row affected (0.04 sec) Records: 1 Duplicates: 0 Warnings: 0 (root@localhost) [test]> select * from t1; +------------------------------------------------------------------------------------------------------+ | name | +------------------------------------------------------------------------------------------------------+ | one two three | +------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) (root@localhost) [test]> update t1 set name = replace(name,'\0',''); Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 (root@localhost) [test]> select * from t1; +---------------+ | name | +---------------+ | one two three | +---------------+ 1 row in set (0.00 sec)