Hi Vasil,
Vasil Dimov wrote:
> On Tue, Jun 17, 2008 at 11:12:30 -0300, Davi Arnaut wrote:
>> # At a local mysql-5.1 repository of davi
>>
>> 2667 Davi Arnaut 2008-06-17
>> Bug#33873: Fast ALTER TABLE doesn't work with multibyte character sets
>>
>> The problem was that when comparing tables for a possible
>> fast alter table, the comparison was being performed using
>> the parsed information and not the final definition.
>>
>> The solution is to use the possible final table layout to
>> compare if a fast alter is possible or not.
>> modified:
>> mysql-test/include/mix1.inc
>> mysql-test/r/alter_table.result
>> mysql-test/r/innodb_mysql.result
>> mysql-test/t/alter_table.test
>> sql/sql_table.cc
> [...]
>
> Hi,
>
> This commit introduces the following change in behavior:
>
> --- cut ---
> DROP TABLE t1, t2, t3;
>
> CREATE TABLE t1 (a INT, b BLOB, c TEXT, d TEXT NOT NULL)
> ENGINE=INNODB DEFAULT CHARSET=UTF8;
>
> CREATE TABLE t2 (d VARCHAR(17) PRIMARY KEY) ENGINE=INNODB DEFAULT CHARSET=UTF8;
>
> CREATE TABLE t3 (a INT PRIMARY KEY) ENGINE=INNODB;
>
> INSERT INTO t2 VALUES ('jejdkrun87'), ('adfdijn0loKNHJik');
>
> INSERT INTO t3 VALUES (22), (44), (66);
>
> INSERT INTO t1 SELECT
> a, LEFT(REPEAT(d, 100), 65535), REPEAT(d,100), d FROM t2, t3;
>
> ALTER TABLE t1 ADD PRIMARY KEY (a, b(255), c(255)), ADD KEY (b(767));
>
> EXPLAIN SELECT * FROM t1 WHERE b LIKE 'adfd%'\G
> --- cut ---
>
> Before r2667:
>
> *************************** 1. row ***************************
> id: 1
> select_type: SIMPLE
> table: t1
> type: range
> possible_keys: b
> key: b
> key_len: 769
> ref: NULL
> rows: 2
> Extra: Using where
> 1 row in set (0.00 sec)
>
> After r2667:
>
> *************************** 1. row ***************************
> id: 1
> select_type: SIMPLE
> table: t1
> type: ALL
> possible_keys: b
> key: NULL
> key_len: NULL
> ref: NULL
> rows: 6
> Extra: Using where
> 1 row in set (0.00 sec)
>
> The change can be observed by only applying the attached patch
> (bzr diff -r2666..2667 sql/sql_table.cc).
>
> Do you have any idea whether this is expected or it is some
> side-effect or it is a bug?
>
I took a fresh clone of the 5.1-bugteam tree (almost equal to main),
reverse applied the patch (patch -R) and I got this results:
ALTER TABLE t1 ADD PRIMARY KEY (a, b(255), c(255)), ADD KEY (b(767));
EXPLAIN SELECT * FROM t1 WHERE b LIKE 'adfd%';
id select_type table type possible_keys key key_len
ref rows Extra
1 SIMPLE t1 ALL b NULL NULL NULL 6
Using where
The documentation says that after an ALTER TABLE statement, it may be
necessary to run ANALYZE TABLE to update index cardinality information.
ANALYZE TABLE t1;
Table Op Msg_type Msg_text
test.t1 analyze status OK
EXPLAIN SELECT * FROM t1 WHERE b LIKE 'adfd%';
id select_type table type possible_keys key key_len
ref rows Extra
1 SIMPLE t1 range b b 769 NULL 2
Using where
Regards,
-- Davi Arnaut
