sql error

mystrymaster

here is the create table

CREATE TABLE `newAuth` (
`ID` INT NOT NULL AUTO_INCREMENT ,
`userID` INT NOT NULL ,
`auth` TEXT( 65535 ) ,
PRIMARY KEY ( `ID` ) ,
INDEX ( `userID` , `auth` ) ,
FULLTEXT (
`auth` 
  )
)

here is the error

#1064 - You have an error in your SQL syntax near '(65535 ), PRIMARY KEY (ID), INDEX (userID, auth), FULLTEXT (auth))' at line 1

now this came about because I was getting an error trying to alter a table with a field that was varchar to a textfield and I was getting this error

#1170 - BLOB column 'auth' used in key specification without a key length

even though in the length box on phpMyAdmin I was giving it the same length which is the max length based on the mysql documentation

any help

LordShryku

You can see here that you don't assign length to text field types. They are automatic max length 65535, and truncated after that.

mystrymaster

this error when altering existing table

#1170 - BLOB column 'auth' used in key specification without a key length

here is the table

Field Type Null Default 
ID   int(11) No    

userId   int(11) No  0  

auth   varchar(100) Yes  NULL

I get the above error when trying to modify auth to TEXT

LordShryku

Where's your alter code? Should be something like this

ALTER TABLE tableName
CHANGE auth auth text;

mystrymaster

using phpAdmin to try and alter it and here is what it is generating

ALTER TABLE `authlevel` CHANGE `auth` `auth` TEXT DEFAULT NULL

Thanks for being willing to help

LordShryku

Do you get the same error if you run the SQL I showed you manually?

mystrymaster

yeah the exact same error