Templates for MySQL | SchemaSmith

Overview

Each Template.json file targets one logical group of schemas on MySQL. For the full field reference, quench slot pipeline, and template ordering rules that apply across all platforms, see Products & Templates.

This page covers MySQL Enterprise-specific features: the SchemaIdentificationScript property (unique to MySQL), Events in the AfterTablesObjects slot, and index-only deployments for third-party databases.

MySQL Example

From the SchemaSmith MySQL Enterprise Demos, a template targeting a single schema with custom script folders:

{
  "Name": "Main",
  "SchemaIdentificationScript": "SELECT SCHEMA_NAME FROM information_schema.SCHEMATA WHERE SCHEMA_NAME = '{{MainDB}}'",
  "VersionStampScript": "SELECT '{{ReleaseVersion}}' AS version",
  "ScriptTokens": {
    "TestTableData": "<*File*>Tables/TestTable - MoreChildren.data",
    "TemplateQueryToken": "<*Query*>SELECT 'true'"
  },
  "ScriptFolders": [
    { "FolderPath": "Before Scripts",  "QuenchSlot": "Before" },
    { "FolderPath": "Functions",       "QuenchSlot": "Objects" },
    { "FolderPath": "Procedures",      "QuenchSlot": "Objects" },
    { "FolderPath": "Triggers",        "QuenchSlot": "AfterTablesObjects" },
    { "FolderPath": "Views",           "QuenchSlot": "AfterTablesObjects" },
    { "FolderPath": "Events",          "QuenchSlot": "AfterTablesObjects" },
    { "FolderPath": "After Scripts",   "QuenchSlot": "After" }
  ]
}

MySQL Notes

Schema Identification

MySQL templates use SchemaIdentificationScript (not DatabaseIdentificationScript) to query information_schema.SCHEMATA. This is the only platform where the identification property name differs. The {{MainDB}} token is resolved from script tokens at runtime.

8 Default Folders

MySQL Enterprise templates default to 8 script folders, the fewest of any platform. MySQL-specific folder types include Events. See Configurable Script Folders.

Events in AfterTablesObjects

MySQL Events are assigned to the AfterTablesObjects quench slot by default (alongside Triggers and Views). Events are a MySQL-specific object type not present on SQL Server or PostgreSQL.

Views in AfterTablesObjects

On MySQL, Views are assigned to the AfterTablesObjects quench slot by default (like PostgreSQL), rather than the Objects slot used on SQL Server. This ensures views that depend on table columns are created after table quenching completes.

Index Only Table Quenches

When IndexOnlyTableQuenches is true, SchemaQuench skips all structural table changes (column additions, modifications, and removals) and only processes index definitions. Table definitions in the Tables/ folder are still loaded and parsed, but only index creation and modification DDL is generated.

Use Case

Third-Party Databases

When maintaining indexes on a third-party database where you do not control the table structure, use index-only mode to manage custom indexes without risking modifications to vendor-owned columns or constraints.

Configuration

Enable index-only mode in your Template.json:

{
  "Name": "ThirdPartyIndexes",
  "SchemaIdentificationScript": "SELECT SCHEMA_NAME FROM information_schema.SCHEMATA WHERE SCHEMA_NAME = '{{VendorDb}}'",
  "IndexOnlyTableQuenches": true
}

Table Definition

Tables in an index-only template need only the Name and Indexes properties (MySQL tables do not have a separate schema qualifier):

{
  "Name": "TestSecondaryTable",
  "Indexes": [
    {
      "Name": "PK_TestSecondaryTable",
      "PrimaryKey": true,
      "IndexColumns": "TestSecondaryID"
    },
    {
      "Name": "IX_DateCreated",
      "IndexColumns": "DateCreated"
    }
  ]
}

When this table is quenched, only the indexes are maintained. The table structure itself is managed through the vendor's process or some other means.

Update Fill Factor

The UpdateFillFactor template setting (default: true) controls whether SchemaQuench updates index fill factor values to match the definitions in your table JSON files. When enabled, any fill factor difference between the definition and the live index triggers a rebuild.

`true` (Default)

Fill factor values from your table definitions are enforced. Indexes with a different fill factor on the server are rebuilt to match.

`false`

Fill factor differences are ignored. Use this when DBAs tune fill factor per-server based on workload characteristics and you do not want schema deployments to override those adjustments.

Templates - MySQL (Enterprise)