Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm using MySQL 5.5.27. I currently have a BLOB column, that I want to convert to text to allow fulltext searching. The ALTER TABLE command to create a text column is successful, but the ALTER TABLE table ADD FULLTEXT INDEX index (column ASC) to create a fulltext index returns an error, saying I cannot create a fulltext index on that column.

When I go back and look at the specs for the table, it appears as BLOB, instead of text.

I'm using MySQLWorkbench, but I've generally been running the scripts by hand, instead of using the wizard.

I have tried setting a character encoding for the entire schema (none existed before) thinking that this would be needed for text columns, but it didn't help.

share|improve this question
add comment

1 Answer 1

up vote 1 down vote accepted

The command

ALTER TABLE ADD FULLTEXT INDEX index (column ASC);

sounds like it does not make sense.

What does FULLTEXT indexing a column in ascending order mean?

You should run it like this:

ALTER TABLE ADD FULLTEXT INDEX index (column);

Give it a Try !!!

share|improve this answer
    
It accepts the command, but returns the same error: "Column 'column' cannot be part of FULLTEXT index" I'm thinking the problem has to do with the column being of "BLOB" type, but I can't alter it to TEXT. I run ALTER TABLE table CHANGE COLUMN column TEXT , but DESCRIBE still tells me column is of type BLOB.. –  Joshua Kast Mar 29 '13 at 20:24
1  
Please post the SHOW CREATE TABLE of the table into your question. I will show you how. –  RolandoMySQLDBA Mar 29 '13 at 20:25
    
Ok, thanks Ronaldo: I ran SHOW CREATE TABLE I see that my charset is set to binary. I ran ALTER TABLE table charset = utf8 Now all works. –  Joshua Kast Mar 29 '13 at 20:30
    
Did my answer help at all ??? –  RolandoMySQLDBA Mar 29 '13 at 20:34
    
@JoshuaKast if the answer helped, a good way to show it is clicking the checkmark below the vote count. This shows to Ronaldo and everyone else that this solved your issue. –  JNK Mar 29 '13 at 20:36
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.