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:
None 
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
Description:
You can not add a column after you have applied a key on a text column. The
semantics of adding columns and creating tables are different.

How to repeat:
--
-- Trying to add a column to an existing table, with regular key
--
drop table if exists bug;
create table bug
  (
  clipid INT NOT NULL
  ,Tape TINYTEXT

  ,PRIMARY KEY (clipid)
  ,KEY         tape(Tape(255))
  ) ENGINE=MyISAM DEFAULT CHARSET=utf8;

alter table bug add mos tinyint default 0 after clipid;

--
-- reports:
-- ERROR 1170 (42000): BLOB/TEXT column 'Tape' used in key specification without a key length
--

--
-- Trying to add a column to an existing table, with fulltext key
--
drop table if exists bug;
create table bug
  (
  clipid INT NOT NULL
  ,Tape TINYTEXT

  ,PRIMARY KEY (clipid)
  ,FULLTEXT    tape(tape)
  ) ENGINE=MyISAM DEFAULT CHARSET=utf8;

alter table bug add mos tinyint default 0 after clipid;

--
-- no errors
--

--
-- Where as creating a table works without complains
--

drop table if exists bug;
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;

Suggested fix:
There is a difference between the semantics of creating a table and modifying a
table. Either prevent creating KEYs on TINYTEXT columns or allow modifying a
table as described above.
[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.