Description:
Hi!
MyISAM tables in INSERTs seem to compare TEXT keys with no space padding in
4.1. They do compare with space padding in a SELECT.
In 5.0 all comparisons with TEXT seem to be space-padded. This means that
after an upgrade:
mysql> check table t;
+--------+-------+----------+-----------------------------------+
| Table | Op | Msg_type | Msg_text |
+--------+-------+----------+-----------------------------------+
| test.t | check | error | Found duplicated key at page 1024 |
| test.t | check | error | Corrupt |
+--------+-------+----------+-----------------------------------+
2 rows in set (0.00 sec)
http://dev.mysql.com/doc/mysql/en/upgrading-from-4-1.html does not mention
this.
I think it is logical to compare everything with TEXT columns as
space-padded. Then it makes sense to introduce this incompatible change in
5.0.
But that leaves still the inconsistency that BLOBs are NOT compared as
space-padded in 5.0, though CHAR BINARY columns are compared.
Regards,
Heikki
How to repeat:
heikki@hundin:~/mysql-4.1/client> ./mysql test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.11-debug-log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> create table t (a text not null, unique key (a(5))) type = myisam;
Query OK, 0 rows affected, 1 warning (0.04 sec)
mysql> insert into t values ('a');
Query OK, 1 row affected (0.00 sec)
mysql> insert into t values ('a ');
Query OK, 1 row affected (0.00 sec)
mysql> select * from t where a = 'a';
+----+
| a |
+----+
| a |
| a |
+----+
2 rows in set (0.02 sec)
mysql> select * from t where a = 'a ';
+----+
| a |
+----+
| a |
| a |
+----+
2 rows in set (0.00 sec)
mysql> select * from t where a like 'a%';
+----+
| a |
+----+
| a |
| a |
+----+
2 rows in set (0.00 sec)
mysql> exit
Bye
heikki@hundin:~/mysql-4.1/client> ./mysqladmin shutdown
heikki@hundin:~/mysql-4.1/client> cd
heikki@hundin:~> cd mysql-5.0
heikki@hundin:~/mysql-5.0> cd client
heikki@hundin:~/mysql-5.0/client> ./mysql test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.3-alpha-debug-log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> check table t;
+--------+-------+----------+-----------------------------------+
| Table | Op | Msg_type | Msg_text |
+--------+-------+----------+-----------------------------------+
| test.t | check | error | Found duplicated key at page 1024 |
| test.t | check | error | Corrupt |
+--------+-------+----------+-----------------------------------+
2 rows in set (0.00 sec)
mysql> create table t2 (a text not null, unique key (a(5))) type = myisam;
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> insert into t2 values ('a ');
Query OK, 1 row affected (0.01 sec)
mysql> insert into t2 values ('a');
ERROR 1062 (23000): Duplicate entry 'a' for key 1
mysql>
Suggested fix:
We must decide if TEXT columns should be compared space-padded in 5.0 or not.
