MySQL Bugs: #26146: Can't add columns to tables created with (regular) text indexes

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

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.

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>

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

Duplicate of bug: http://bugs.mysql.com/bug.php?id=26180