How to change VARCHAR to TEXT MySQL

ppowell

Upon using PHPMyAdmin to change a VARCHAR field (MySQL 3.23.41) to TEXT I get the following error message:

Error

SQL-query :

ALTER TABLE nnet_usermetadata CHANGE nnet_user_uplinenumber nnet_user_uplinenumber TEXT DEFAULT NULL

MySQL said:

BLOB column 'nnet_user_uplinenumber' used in key specification without a key length
Back

How then do I change a MySQL VARCHAR field to TEXT? The mySQL manual also offers little help for me to understand.

Thanx
Phil

Merve

PHPMyAdmin seems to complain a lot and churn out a lot of errors unless you have the tables in My|SAM format.

ppowell

Thanx, but all of the tables in the database are MyISAM format.

Phil

barand

Alter the key definition that includes 'nnet_usermetadata' so that the column is specified eg (nnet_usermetadata(20) ) where the 20 is the length of the text that you want to use in the index.

The alter type from varchar to text.

Columnname(length) syntax required for TEXT type keys.

hth

ppowell

Sorry it didn't help or else I did it wrong:

SQL-query :

ALTER TABLE nnet_usermetadata CHANGE nnet_user_registrationnumber nnet_user_registrationnumber ( 65535 ) TEXT DEFAULT NULL

MySQL said:

You have an error in your SQL syntax near '( 65535 ) TEXT DEFAULT NULL ' at line 1
Back

Phil

barand

Looks like you need to drop the offending index, change col to TEXT then create index again.

Setting a length to 65535 seems to defeat the object of trying to keep the index size manageable - I think they had smaller sizes in mind when they created the constraint, say, up to 255?

ppowell

Well, could you or someone do this for me then?

Anyone with experience buildng MLM websites let me know what the chances are that a downline number (built using hex digits) would ever surpass 255 characters, as I believe the odds being fairly beyond Amway for that to happen, nonetheless, the client wants reassurance that the column will handle upline numbers that reach beyond 255 characters. That's why I originally built it at VARCHAR(255) thinking that's big enough.

sigh

Phil

barand

Substitute your index and table names

DROP INDEX indexname ON tablename

ALTER TABLE `nnet_usermetadata` CHANGE `nnet_user_uplinenumber` `nnet_user_uplinenumber` TEXT DEFAULT NULL 

CREATE  INDEX index_name ON tbl_name (nnet_usermetadata(512))