# cake production deployment — runbook **For:** DBA / DevOps · **Tickets:** [TW-314](https://admedia-jira.atlassian.net/browse/TW-314)–[TW-317](https://admedia-jira.atlassian.net/browse/TW-317) **Prerequisite:** the same steps completed on staging — see [CAKE-STAGING-VALIDATION.md](./CAKE-STAGING-VALIDATION.md) Everything here has been applied and read back on the dev databases. This is the same work against production. Nothing in it writes data. It creates read-only views, two stored procedures, and one read-only login. --- ## 0. Before starting — one decision is still open The day-sharded views get a **new name every night** (`lucos_ro_postback_20260818`, then `…20260819`), and MySQL `GRANT` accepts a whole schema but not a wildcard table name. So a fixed grant list cannot cover tomorrow's view. Options and trade-offs are in [CAKE-RO-VIEWS.md](./CAKE-RO-VIEWS.md) § "Grants for sharded views". **This must be settled before step 3.** Do **not** resolve it with `GRANT SELECT ON Shorty.*` — that exposes every base table, including raw IPs and postback URLs with tokens in them, which is the specific thing this design exists to prevent. --- ## 1. Three servers, not one Unlike dev — where `Admin` and `Shorty` share a host — production has all three apart: | Schema | Prod host | What goes there | | --- | --- | --- | | `Admin` | `192.168.30.7` | 7 static views | | `whale` | `192.168.30.10` | 2 static views | | `Shorty` | `192.168.30.2` | 4 sharded view families + 3 procedures + the nightly job | **The static DDL file contains statements for two different servers.** Running it whole on one host will half-fail with "unknown database". Split it: - `CREATE … VIEW whale.…` → run on **whale** - `CREATE … VIEW Admin.…` → run on **Admin** --- ## 2. Apply the DDL Both files are environment-agnostic; the `-staging` suffix is historical. | Order | File | Server | | --- | --- | --- | | 1 | [`catalogs/queries/cake-ro-views-staging.sql`](../catalogs/queries/cake-ro-views-staging.sql) — whale statements only | whale | | 2 | same file — Admin statements only | Admin | | 3 | [`catalogs/queries/cake-ro-shard-views-staging.sql`](../catalogs/queries/cake-ro-shard-views-staging.sql) | Shorty | The shard file uses `DELIMITER $$` for its procedures. That works in the `mysql` client. **In phpMyAdmin it does not** — delete the `DELIMITER` lines and put `$$` in the Delimiter box under the editor instead. ### If a view fails to create Two failures we hit on staging, both already fixed in the files — if they reappear, the file is out of date: - `ERROR 1271 … Illegal mix of collations` — the `ip_prefix` branches need `CONVERT(… USING utf8)`. `publisher_postback_*.ip` is `latin1_swedish_ci` while `INET_NTOA()` returns the connection charset. - `ERROR 1146 … aff_cpc_summary doesn't exist` — that table is expected on `Admin`. If prod has it on `Shorty` instead, stop and tell us; there is a documented fallback ([OD-2](./CAKE-OPEN-DECISIONS.md)). --- ## 3. Create the read-only login One account, granted on the approved views **only** — never on base tables. The grant list for the nine static views is in [CAKE-RO-VIEWS.md](./CAKE-RO-VIEWS.md) § "RO grants". The sharded views follow whichever option §0 settled on. Needed on all three hosts, since a MySQL account is per-server. > **Note for whoever does this.** On dev neither available login had > `GRANT OPTION`, so the proper `lucos_cake_ro` user was never created and the > tools have been running on a shared developer account. That is acceptable on > dev and **not** acceptable in production — the whole safety model rests on this > account being unable to read anything but the approved views. --- ## 4. Backfill, then schedule **Backfill once**, so history is queryable immediately: ```sql CALL Shorty.sp_lucos_ro_refresh_cake_shard_views(45); ``` **Then schedule it daily.** Cron, not the MySQL event scheduler: ``` 0 1 * * * mysql Shorty -e "CALL sp_lucos_ro_refresh_cake_shard_views(7); CALL sp_lucos_ro_prune_cake_shard_views(45);" ``` Credentials belong in `~/.my.cnf`, not inline — otherwise the password appears in the crontab and in the process list. **Why cron and not a MySQL EVENT.** The event scheduler is a server-wide setting affecting every database on the host, and `SET GLOBAL` does not survive a restart. On staging it silently reverted to `OFF` after a restart and the job never ran once in four days — `LAST_EXECUTED` stayed `NULL`. Cron has neither problem. **Why a 7-day window and not just today.** The base tables do not reliably appear on their own date. Measured on staging: ``` click_20260817 created 2026-08-17 00:59 same day click_20260812 created 2026-08-13 22:18 1 day late click_20260811 created 2026-08-17 00:05 6 days late ``` A job that builds only today runs on the 11th, finds nothing, correctly skips — and never looks again. Re-running a window is self-healing and makes the exact cron time irrelevant. `p_days` is clamped to 45 internally, so a mistyped argument cannot run away. --- ## 5. Verify ```sql -- 13 view families across the three servers SELECT TABLE_SCHEMA, COUNT(*) FROM information_schema.VIEWS WHERE TABLE_NAME LIKE 'lucos\_ro\_%' GROUP BY TABLE_SCHEMA; -- redaction is structural: no raw ip, no raw postback_url SHOW COLUMNS FROM Shorty.lucos_ro_postback_; -- the RO user can read a view ... SELECT * FROM Admin.lucos_ro_campaign_directory LIMIT 1; -- ... and cannot read a base table. This SHOULD fail. SELECT * FROM Shorty.publisher_postback_ LIMIT 1; ``` That last one failing is the point of the exercise. If it succeeds, the grants are too broad — stop and fix before enabling the tools. --- ## 6. Application side Three DSNs, plus an explicit production opt-in: ``` CAKE_ADMIN_RO_DSN_PROD=mysql://lucos_cake_ro:***@192.168.30.7:3306/Admin CAKE_WHALE_RO_DSN_PROD=mysql://lucos_cake_ro:***@192.168.30.10:3306/whale CAKE_SHORTY_RO_DSN_PROD=mysql://lucos_cake_ro:***@192.168.30.2:3306/Shorty CAKE_RO_ALLOW_PROD=true ``` Without `CAKE_RO_ALLOW_PROD` the connectors refuse production outright, so it cannot be reached by accident. Percent-encode the password if it contains `@ : / # ?` — otherwise the URL parses wrongly and fails confusingly. Every statement the tools run is logged with the request that caused it — see [QUERY-LOGGING.md](./QUERY-LOGGING.md) — which is the fastest way to confirm the first production calls are hitting the views you expect. --- ## 7. Two things to confirm before enabling the tools **Retention.** The tools cap their windows at 92 days (reports), 31 (IVT) and 7 (postback). If production keeps fewer days of `click_*` or `publisher_postback_*` than that, users will hit missing-shard responses routinely. Staging keeps ~72 days of `click_*`. Prod is unmeasured — worth checking. **`sql_mode`.** Every aggregating view sits on `Admin` and `Shorty`, which have an empty `sql_mode` on dev. If production enables `ONLY_FULL_GROUP_BY`, those views need re-checking. A view records the `sql_mode` in force when it was created, so create them under the mode you intend to keep.