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 only takes a minute to sign up.
Sign up to join this community
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.
