Bug #26146 Can't add columns to tables created with (regular) text indexes
Submitted: 7 Feb 2007 12:25 Modified: 8 Feb 2007 12:10
Reporter: David Newcomb (OCA) Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: General Severity:S3 (Non-critical)
Version:5.0.27 OS:Windows (Win2K)
Assigned to: CPU Architecture:Any

[7 Feb 2007 12:25] 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;

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;

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;

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.
[7 Feb 2007 13:00] MySQL Verification Team
Thank you for the bug report. I was not able to repeat:

C:\mydb\bin>mysql -uroot -P3307 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.0.27-community-nt MySQL Community Edition (GPL)

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.03 sec)

mysql> create table bug
    ->   (
    ->   clipid INT NOT NULL
    ->   ,Tape TINYTEXT
    ->
    ->   ,PRIMARY KEY (clipid)
    ->   ,KEY         tape(Tape(255))
    ->   ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.14 sec)

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=latin1
1 row in set (0.03 sec)

mysql> alter table bug add mos tinyint default 0 after clipid;
Query OK, 0 rows affected (0.14 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table bug\G
*************************** 1. row ***************************
       Table: bug
Create Table: CREATE TABLE `bug` (
  `clipid` int(11) NOT NULL,
  `mos` tinyint(4) default '0',
  `Tape` tinytext,
  PRIMARY KEY  (`clipid`),
  KEY `tape` (`Tape`(255))
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql>
[7 Feb 2007 13:16] David Newcomb
Sorry my mistake, I forgot to mention which character set.
You are using CHARSET=latin1.
Try it with CHARSET=utf8

Eg:

--
CREATE TABLE bug
 (
  clipid int(11) 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;
--
or with:
  default-character-set=utf8
in the my.ini
[8 Feb 2007 12:10] MySQL Verification Team
Duplicate of bug: http://bugs.mysql.com/bug.php?id=26180