mysql innodb db full text search FTS_DOC_ID column

I am adding full text search to already existing mysql tables that have primary auto incrementing key of id integer.

When I add the full text index I get a warning:

/*!50604 ALTER TABLE `phppos_additional_item_numbers` ADD FULLTEXT INDEX item_number_search (`item_number`)*/;      

Warning (Code 124): InnoDB rebuilding table to add column FTS_DOC_ID

I cannot simply refactor all my code to add a column FTS_DOC_ID to remove this warning. Is it bad to just allow this warning to happen?

Reading the manual it does not seem to be anything bad. MySQL just informs you about an additional action it had to do.


InnoDB uses a unique document identifier referred to as a Document ID
(DOC_ID) to map words in the full-text index to document records where
the word appears. The mapping requires an FTS_DOC_ID column on the
indexed table. If an FTS_DOC_ID column is not defined, InnoDB
automatically adds a hidden FTS_DOC_ID column when the full-text index
is created.

If you preferred to not get the warning, then you would have to manage the column yourself which looks like a lot of work which is currently automated:


If you choose to define the FTS_DOC_ID column yourself, you are
responsible for managing the column to avoid empty or duplicate
values. FTS_DOC_ID values cannot be reused, which means FTS_DOC_ID
values must be ever increasing.

One side effect of the actions producing this warning is that the table has to be rebuild - that might take some time for tables already containing many records. But the same will be true if you added the column yourself, so it does not make a difference for you.

StackExchange Database Administrators Q#120509, answer score: 10