| Bug #26180 | Can't add columns to tables created with utf8 (regular) text indexes | ||
|---|---|---|---|
| Submitted: | 8 Feb 2007 9:55 | Modified: | 6 Mar 2010 19:56 |
| Reporter: | David Newcomb (OCA) | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Charsets | Severity: | S3 (Non-critical) |
| Version: | 5.1.15-BK, 5.0.36-BK, 5.0.27-community-nt-log | OS: | Linux (Linux, Win2K) |
| Assigned to: | Alexander Barkov | CPU Architecture: | Any |
[8 Feb 2007 9:55]
David Newcomb
[8 Feb 2007 11:47]
Valeriy Kravchuk
Thank you for a bug report. Verified just as described with latest 5.0.36-BK on Linux:
openxs@suse:~/dbs/5.0> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.36 Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> drop table if exists bug;
Query OK, 0 rows affected, 1 warning (0.00 sec)
cmysql> create table bug
-> (
-> clipid INT NOT NULL
-> ,Tape TINYTEXT
->
-> ,PRIMARY KEY (clipid)
-> ,KEY tape(Tape(255))
-> ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.01 sec)
mysql> alter table bug add mos tinyint default 0 after clipid;
ERROR 1170 (42000): BLOB/TEXT column 'Tape' used in key specification without a
key length
mysql> show create table bug\G
*************************** 1. row ***************************
Table: bug
Create Table: CREATE TABLE `bug` (
`clipid` int(11) NOT NULL,
`Tape` tinytext,
PRIMARY KEY (`clipid`),
KEY `tape` (`Tape`(255))
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql> drop table if exists bug;
Query OK, 0 rows affected (0.00 sec)
mysql> create table bug
-> (
-> clipid INT NOT NULL
-> ,Tape TINYTEXT
->
-> ,PRIMARY KEY (clipid)
-> ,FULLTEXT tape(tape)
-> ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.01 sec)
mysql> alter table bug add mos tinyint default 0 after clipid;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> drop table if exists bug;
Query OK, 0 rows affected (0.01 sec)
cmysql> create table bug
-> (
-> clipid INT NOT NULL
-> ,Tape TINYTEXT
-> ,mos TINYINT DEFAULT 0
->
-> ,PRIMARY KEY (clipid)
-> ,KEY tape(Tape(255))
-> ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.00 sec)
This behaviour is inconsistent and, thus, is a bug.
[8 Feb 2007 11:52]
Valeriy Kravchuk
Verified also on latest 5.1.15-BK.
[8 Feb 2007 12:11]
MySQL Verification Team
BuG: http://bugs.mysql.com/bug.php?id=26146 was marked as duplicate of this one.
[31 Jul 2008 9:38]
Alexander Barkov
A more simple text case: drop table if exists t1; create table t1 (tape tinytext, key(tape(86))) character set utf8; show create table t1; alter table t1 add mos tinyint; show create table t1; And the output is: Table Create Table t1 CREATE TABLE `t1` (\n `tape` tinytext,\n KEY `tape` (`tape`(86))\n) ENGINE=MyISAM DEFAULT CHARSET=utf8 ERROR 1170 (42000) at line 4: BLOB/TEXT column 'tape' used in key specification without a key length Note, with tape(85) it works fine: Table Create Table t1 CREATE TABLE `t1` (\n `tape` tinytext,\n KEY `tape` (`tape`(85))\n) ENGINE=MyISAM DEFAULT CHARSET=utf8 Table Create Table t1 CREATE TABLE `t1` (\n `tape` tinytext,\n `mos` tinyint(4) default NULL,\n KEY `tape` (`tape`(85))\n) ENGINE=MyISAM DEFAULT CHARSET=utf8
[31 Jul 2008 9:50]
Alexander Barkov
The previous test was made with mysql-6.0, i.e. with 4-byte UTF8.
[31 Jul 2008 10:28]
Alexander Barkov
The bug happens in this piece of code:
if (!Field::type_can_have_key_part(cfield->field->type()) ||
!Field::type_can_have_key_part(cfield->sql_type) ||
/* spatial keys can't have sub-key length */
(key_info->flags & HA_SPATIAL) ||
(cfield->field->field_length == key_part_length &&
!f_is_blob(key_part->key_type)) ||
(cfield->length && (cfield->length < key_part_length /
key_part->field->charset()->mbmaxlen)))
key_part_length= 0; // Use whole field
key_part_length is erroneously set to 0.
This is because various length variables here have different
notations. Some of them are measured in characters, others are
measured in bytes.
In 6.0 (mbmaxlen==4) with key(tape(86)):
cfield->length = 63 - set in Create_field::Create_field() to 255/4
key_part_length=344, which is 86*4
64 is compared to 86.
Field length is considered to be shorter than key length,
so it uses whole key.
The correct way would be to compare 255 (i.e. real maximum length of a TINYBLOB column in bytes) to 344/4=86.
[1 Aug 2008 7:44]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/50805 2743 Alexander Barkov 2008-08-01 Bug#26180 Can't add columns to tables created with utf8 (regular) text indexes Problem: On ALTER TABLE, maximum possible key part length for TEXT variants was calculated similar to VARCHAR datatype, which was wrong because TEXT columns are limited in bytes, while VARCHAR columns are limited in characters. Fix: adding a special code for TEXT variants. mysql-test/r/ctype_utf8.result mysql-test/t/ctype_utf8.test Adding test case sql/sql_table.cc Adding special code for TEXT variants.
[3 Sep 2008 12:29]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/53155 2820 Alexander Barkov 2008-09-03 Bug#26180 Can't add columns to tables created with utf8 (regular) text indexes Problem: On ALTER TABLE, maximum possible key part length for TEXT variants was calculated similar to VARCHAR datatype, which was wrong because TEXT columns are limited in bytes, while VARCHAR columns are limited in characters. Fix: adding a special code for TEXT variants. mysql-test/r/ctype_utf8.result mysql-test/t/ctype_utf8.test Adding test case sql/sql_table.cc Adding special code for TEXT variants.
[3 Sep 2008 12:30]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/53156 2820 Alexander Barkov 2008-09-03 Bug#26180 Can't add columns to tables created with utf8 (regular) text indexes Problem: On ALTER TABLE, maximum possible key part length for TEXT variants was calculated similar to VARCHAR datatype, which was wrong because TEXT columns are limited in bytes, while VARCHAR columns are limited in characters. Fix: adding a special code for TEXT variants. mysql-test/r/ctype_utf8.result mysql-test/t/ctype_utf8.test Adding test case sql/sql_table.cc Adding special code for TEXT variants.
[3 Sep 2008 12:52]
Alexander Barkov
Pushed into 6.0.7-bugteam
[4 Sep 2008 3:34]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/53218 2823 Alexander Barkov 2008-09-04 Postfix for bug#26180 Can't add columns to tables created with utf8 (regular) text indexes The original patch broke "fulltext" test.
[4 Sep 2008 3:35]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/53219 2823 Alexander Barkov 2008-09-04 Postfix for bug#26180 Can't add columns to tables created with utf8 (regular) text indexes The original patch broke "fulltext" test.
[4 Sep 2008 14:28]
Bugs System
Pushed into 6.0.7-alpha (revid:bar@mysql.com-20080904032441-smed5de8y0kqgfga) (version source revid:bar@mysql.com-20080904032441-smed5de8y0kqgfga) (pib:3)
[10 Sep 2008 15:26]
Bugs System
Pushed into 6.0.7-alpha (revid:bar@mysql.com-20080904032441-smed5de8y0kqgfga) (version source revid:cbell@mysql.com-20080827130640-3q01lmazklr62bls) (pib:3)
[10 Sep 2008 19:48]
Paul DuBois
Noted in 6.0.7 changelog. ALTER TABLE could not be used to add columns to a table if the table had an index on a utf8 column with a TEXT data type.
[12 Sep 2008 1:44]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/53909 2682 He Zhenxing 2008-09-12 [merge] Auto merge Update plugin.h.pp for WL#4398
[13 Sep 2008 19:53]
Bugs System
Pushed into 6.0.6-alpha (revid:bar@mysql.com-20080903122255-nkwori5iwerk1f98) (version source revid:sergefp@mysql.com-20080611231653-nmuqmw6dedjra79i) (pib:3)
[16 Sep 2008 9:09]
Bugs System
Pushed into 6.0.7-alpha (revid:bar@mysql.com-20080904032441-smed5de8y0kqgfga) (version source revid:alik@mysql.com-20080805141411-g3y3q3q3ot01y2uj) (pib:3)
[30 Oct 2009 9:08]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/88699 2913 Alexander Barkov 2009-10-30 Bug#26180 Can't add columns to tables created with utf8 (regular) text indexes Backporting from 6.0.
[9 Nov 2009 11:39]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/89777 2944 Alexander Barkov 2009-11-09 Bug#26180 Can't add columns to tables created with utf8 (regular) text indexes Backporting from 6.0.
[9 Nov 2009 12:04]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/89784 3703 Alexander Barkov 2009-11-09 [merge] Merging Bug#26180 from mysql-next-mr-bugfixing
[20 Nov 2009 12:58]
Bugs System
Pushed into 6.0.14-alpha (revid:kostja@sun.com-20091120124947-yi6h2jbgw0kbciwm) (version source revid:epotemkin@mysql.com-20091109132131-ad1gk2d2tn9o5i3l) (merge vers: 6.0.14-alpha) (pib:13)
[26 Nov 2009 15:36]
Paul DuBois
Already fixed in 6.0.x. Setting report to NDI pending push to 5.6.x.
[11 Dec 2009 6:04]
Bugs System
Pushed into 5.6.0-beta (revid:alik@sun.com-20091211055628-ltr7fero363uev7r) (version source revid:alik@sun.com-20091211055453-717czhtezc74u8db) (merge vers: 5.6.0-beta) (pib:13)
[11 Dec 2009 19:32]
Paul DuBois
Noted in 5.6.0 changelog.
[6 Mar 2010 11:07]
Bugs System
Pushed into 5.5.3-m3 (revid:alik@sun.com-20100306103849-hha31z2enhh7jwt3) (version source revid:vvaintroub@mysql.com-20091211201717-03qf8ckwiw0np80p) (merge vers: 5.6.0-beta) (pib:16)
[6 Mar 2010 19:56]
Paul DuBois
Moved 5.6.0 changelog entry to 5.5.3.
