MySQL
MySQL 8.4 and 9.7, the LTS releases, each tested on every build.
trek init --provider mysql
trek add create-product
trek deploy
trek.yamlprovider: mysql
defaultEnvironment: local
environments:
local:
host: localhost
port: 3306
database: shop # trek deploys here and keeps its journal here (trek_* tables)
authentication: password
user: root
password: ${env:TREK_LOCAL_PASSWORD}
tls:
mode: optional
prod:
host: shop-prod.mysql.database.azure.com
database: shop
authentication: entraDefault # Azure Database for MySQL: an Entra token is the password
user: shop-deployers # the Entra user, group or managed identity the server knows
tls:
mode: strict
connectionProperties: # any MySqlConnector connection-string keyword
Default Command Timeout: 120
The plan, hash certification, revert from stored scripts, environments, variables, repeatables, bundles, verify, diff-schema and generated changes (add --from-diff, diff-schema --script) work as on the other databases. trek convert moves a MySQL database to SQL Server, PostgreSQL or SQLite and back (Converting: MySQL). Imports from other tools are not available for MySQL yet.
Generating changes
trek add <name> --from-diff builds a shadow database from the plan on the same server (the environment's login needs the right to create and drop databases, or pass --shadow-env), compares it with your database, and writes the change. Tables are compared as SHOW CREATE TABLE prints them and changed with ALTER TABLE: columns are added in position, dropped, modified, moved, and renamed when a column disappears and one with exactly the same definition appears; indexes, checks and table options follow, and a new character set or collation uses CONVERT TO so the columns convert too. Foreign keys are dropped first and added after every table exists. Views, routines, triggers and events are dropped and created again, without DEFINER clauses and with a DELIMITER around bodies of several statements; the ones repeatable scripts create are left to them. Partitioning is reported, not scripted.
No transactions for DDL
MySQL commits each DDL statement as it runs (CREATE, ALTER, DROP), and it cannot be rolled back. So trek runs MySQL changes without a transaction: a change that fails part-way keeps the statements that already ran, and trek records the change only when all of it has run. Keep changes small (one table change each), make them safe to run again where you can (CREATE TABLE IF NOT EXISTS, DROP ... IF EXISTS), and give each a verify script. After a failure, fix the database by hand or with the change's revert, then deploy again.
Stored programs and DELIMITER
trek splits scripts the way the mysql client does: a statement ends at ;. A procedure, function, trigger or event whose body has several statements needs another delimiter around it, also as in the client; the DELIMITER lines are not sent to the server:
DELIMITER //
CREATE PROCEDURE add_order(IN amount DECIMAL(12,2))
BEGIN
INSERT INTO orders (total) VALUES (amount);
SELECT LAST_INSERT_ID() AS id;
END //
DELIMITER ;
trek plan check reports a body that ends at its first ; (TREK005), USE (TREK009: it would send the rest of the change, and trek's journal entry, to another database) and mysql client commands such as source. Repeatables are written with CREATE OR REPLACE VIEW, or DROP PROCEDURE IF EXISTS and then CREATE.
The journal
In MySQL a schema is a database, and trek works in the environment's database: its journal is the tables trek_meta, trek_changes, trek_repeatables, trek_events and trek_schema_snapshots there (the prefix is stateSchema), so trek needs no right to create databases. The deploy lock is a MySQL named lock (GET_LOCK) per database. The schema fingerprint covers tables, columns, indexes, foreign keys, checks, views, routines, triggers and events, and leaves out the database's own name, which MySQL writes into stored definitions, so the same schema has the same fingerprint in every environment.
Versions and services
Every build runs the provider's tests against MySQL 8.4 and 9.7 (the official Docker images), the long-term-support releases that are still supported; trek doctor flags older releases and Innovation releases. Azure Database for MySQL, Amazon RDS and Aurora MySQL and Google Cloud SQL run standard MySQL and are expected to work, but have not been tested. trek connects with MySqlConnector: connectionProperties takes any of its connection-string keywords, and tls.mode maps to its SslMode. MariaDB is a provider of its own (MariaDB): trek stops with a hint when a mysql environment turns out to be MariaDB, or the other way round.