error 1170 / BLOB column 'caption' used in key specification without a key length

[michael worden]

I am upgrading from a content management system . I made a backup with drop statements and downloaded it to the new server (Fedora Core 3, mysql-3.23.58-16.FC3.1). I created a new empty database and tried unsuccessfully to restore the database using:
mysql -u root -p vh06 < vh06a_07-08-2005.sql

I get the following error message:

"ERROR 1170 at line 1965: BLOB column 'caption' used in key specification without a key length"

the code at that line is:

CREATE TABLE cms_cpg_pictures (
pid int(11) NOT NULL auto_increment,
aid int(11) DEFAULT '0' NOT NULL,
filepath varchar(255) NOT NULL,
filename varchar(255) NOT NULL,
filesize int(11) DEFAULT '0' NOT NULL,
total_filesize int(11) DEFAULT '0' NOT NULL,
pwidth smallint(6) DEFAULT '0' NOT NULL,
pheight smallint(6) DEFAULT '0' NOT NULL,
hits int(10) DEFAULT '0' NOT NULL,
mtime timestamp(14),
ctime int(11) DEFAULT '0' NOT NULL,
owner_id int(11) DEFAULT '0' NOT NULL,
owner_name varchar(40) NOT NULL,
pic_rating int(11) DEFAULT '0' NOT NULL,
votes int(11) DEFAULT '0' NOT NULL,
title varchar(255) NOT NULL,
caption text NOT NULL,
keywords varchar(255) NOT NULL,
approved enum('YES','NO') DEFAULT 'NO' NOT NULL,
user1 varchar(255) NOT NULL,
user2 varchar(255) NOT NULL,
user3 varchar(255) NOT NULL,
user4 varchar(255) NOT NULL,
url_prefix tinyint(4) DEFAULT '0' NOT NULL,
randpos int(11) DEFAULT '0' NOT NULL,
pic_raw_ip tinytext,
pic_hdr_ip tinytext,
PRIMARY KEY (pid),
KEY pic_hits (hits),
KEY pic_rate (pic_rating),
KEY aid_approved (aid, approved),
KEY randpos (randpos),
KEY pic_aid (aid),
KEY search (title, caption, keywords, filename, user1, user2, user3, user4)
);

After googling, I tried providing a key length (even though I believe text will not accept this argument):

caption text(255) NOT NULL,

and received more error messages like:

ERROR 1064 at line 1965: You have an error in your SQL syntax near '(255) NOT NULL,
keywords varchar(255) NOT NULL,
approved enum('YES','NO') ' at line 18


I also tried mysql -f -u root -p vh06 < vh06a_07-08-2005.sql but that just clobbers the database.

Strangely, I can create this table and column combination like this:

CREATE TABLE `cms_cpg_pictures` (`caption` TEXT NOT NULL);

but when I try to complete the table by inserting each column individually the very first column

Quote

pid int(11) NOT NULL auto_increment

then mysql says that I can't have more than one autoincrement and it must have a key word.