Search you declare

Every package has one: a script called something like create_search_indexes.sql, run by hand after the deploy, by whoever remembers. Full-text indexes have their own objects and their own rules, so they end up outside the package, and every environment gets them a little differently.

They don’t need to be. A full-text index is an index. Declare it next to the others and it gets the same treatment: created where it’s missing, left alone when it matches, re-created when it changes, dropped when it leaves the package. Then there are two things that genuinely sit outside your package, a parser and a server component, and you need to know what happens to each.

Declared like any other index

On MySQL and MariaDB a table can have as many as you like, so it’s a list:

"FullTextIndexes": [
  { "Name": "ft_article_title", "Columns": "`Title`" }
]

The first deploy logs Create fulltext index: `Article`.`ft_article_title` , and the next one does nothing. Change the columns and you see both halves, because a full-text index can’t be altered:

      Drop modified fulltext index: ALTER TABLE `declared_m7`.`Article` DROP INDEX `ft_article_title`
      Create fulltext index: `Article`.`ft_article_title`

MariaDB 10.4 and later add a third line: Building fulltext index `Article`.`ft_article_title` by copying the table, so it covers the rows already in it. Those versions have an engine bug: a full-text index added in place to a table that has (or had) one leaves out the rows already there, so searches miss them. The deploy works around it with ALGORITHM=COPY, which rebuilds the table and makes writes to it wait until the copy is done. On a big, busy MariaDB table, schedule a full-text change like any other table rebuild.

Removal works with the flags you already know. DropIndexesRemovedFromProduct drops one your package used to declare. DropUnknownIndexes drops one nobody declared, such as the ft_handmade someone added by hand in the lab. Leave the second flag off and a hand-made index survives: an index you didn’t write is someone else’s until you say otherwise.

A parser belongs to the server

MySQL’s ngram parser tokenises text with no spaces between words, such as Chinese or Japanese:

{ "Name": "ft_article_title_ngram", "Columns": "`Title`", "Parser": "ngram" }

It deploys on MySQL, and extraction brings "Parser": "ngram" back. On MariaDB the same declaration fails with Function 'ngram' is not defined, because MariaDB doesn’t ship that plugin. SchemaSmith can declare a parser, but it can’t supply one.

One more rule: the parser is used when the index is created and isn’t compared afterwards. Both engines report it only in SHOW CREATE TABLE, so a changed Parser on a deployed index is a no-op. To change a parser, rename the index.

SQL Server: one per table, with a catalog and a key

"FullTextIndex": {
  "FullTextCatalog": "[LibraryCatalog]",
  "KeyIndex": "[PK_Article]",
  "ChangeTracking": "AUTO",
  "Columns": "[Title],[Body]"
}

It’s an object, not a list: SQL Server allows one full-text index per table. The catalog is a scripted object in the template’s FullTextCatalogs/ folder, which runs before the tables. The key index must be unique, unfiltered, on one NOT NULL column, and --Validate checks that before anything deploys:

ERROR [SS-FT-001] Template 'Main' / Table '[Article]': Table '[Article]' names full-text KeyIndex '[IX_Article_Title]', which is not unique. …

ChangeTracking: AUTO populates the index in the background, so a search right after an insert can come back short. The lab waits a few seconds for it.

Deploy again and nothing happens to the index, which matters here because each re-create is a full repopulation. The Columns are compared as a set, so [Title],[Body] matches the catalog even though SQL Server lists them the other way round.

A server without the component

The stock SQL Server container has no Full-Text Search, and plenty of real installs don’t either: it’s a separate feature you choose at setup. The lab deploys the same package to both kinds of server. Where Full-Text Search is installed, the index is built. Where it isn’t:

        Full-text index skipped on [dbo].[Article]: Full-Text Search is not installed - the table deploys without it (downgraded)

The table deploys, the run exits 1 with outcome Incomplete, and the summary records one downgrade naming [dbo].[Article]. That’s the default UnsupportedFeaturePolicy of warn. Set it to fail and the deploy refuses up front instead. What you don’t get is the old behaviour: a deploy that created the table and then died on the index.

Check yourself: Your SQL Server deploys pass in dev, but in one production region every run exits 1 and the summary says Incomplete. The schema looks right. Nothing failed. What's the likely cause, and how do you decide whether that's acceptable?

Look at the summary’s downgrades. A likely one is a full-text index skipped because that region’s servers don’t have Full-Text Search installed: the table deployed, the index didn’t, and the run says so with exit 1 rather than failing.

Whether that’s acceptable depends on whether anything in that region searches the table. If it does, install Full-Text Search there, or set UnsupportedFeaturePolicy to fail so a missing component stops the deploy instead of passing quietly. If it doesn’t, the downgrade is the record of a choice you made. Either way the exit code and the summary told you, which is the point.

Search indexes used to be the part of the build you finished by hand, after the casting cooled. Now they go in the mold with everything else. They’re declared, compared and removed the same way, and the package says what every environment should have.

Where the metal itself is missing, as on a server with no Full-Text Search, the deploy doesn’t pretend. It casts what it can and writes down exactly what it couldn’t.

Got a full-text index that a deploy wouldn’t leave alone? Email me at forgebarrett@schemasmith.com. More’s coming from the forge.

Until then, may every search find its word, and every index stay put.

— Forge

Mark this lesson complete