-- ===================================================================== -- 01_system.sql -- charset, the 12 new tables, and the spine the framework cannot boot without -- -- GENERATED by _gen/pack.py from ../step/. DO NOT EDIT -- edit the source -- there and re-pack, or the sha256 in MANIFEST.md stops matching. -- -- 13 table(s), 66 ledger step(s). -- -- ⚠ IMPORT ORDER: this file FIRST, before anything else. It creates the 12 new tables -- and runs the one ALTER DATABASE, which nothing else can proceed without. -- -- ⚠ STOP ON THE FIRST ERROR. Do not use `mysql --force`, and do not tick any -- "continue on error" box. Each step records itself in `_migration_ledger` -- immediately after its statement runs, WITHOUT checking that the statement -- succeeded -- that is inherited from step/, where run.sh guarantees a stop by -- running mysql without --force. Continue past an error here and a step that -- failed is marked applied: 99_finalize.sql will then seed `migrations`, and -- `php artisan migrate` will skip the work still owed. Both default clients -- stop on error; the danger is turning that off. -- -- ⚠ phpMyAdmin: UNTICK "Allow the interruption of an import". -- If it fires, the import resumes on a NEW CONNECTION: SQL_MODE reverts to the server -- default and every @variable dies, mid-file. Two things then break. NO_ZERO_DATE is -- back, so any ALTER that rebuilds a table holding '0000-00-00' fails with ERROR 1292. -- And @step / @todo / @sql are NULL, so the guard around the next payload stops meaning -- anything. The SET SQL_MODE before every unit below limits the damage when someone -- forgets the checkbox; it does not replace unticking it. -- -- (STRICT_TRANS_TABLES is forced for a third case -- a value too big for its new column -- is CLAMPED silently rather than refused. Measured on the tree as it stands, no column -- narrows: all 674 MODIFY COLUMNs widen or are no-ops. It stays forced because the next -- regeneration is under no obligation to keep that true.) -- -- ⚠ Take a full backup first. The `20_cleanup` steps change data and nothing here -- reverses them. `../audit_bukanmain_before_migrate.sql` only reads. -- ===================================================================== -- --------------------------------------------------------------- prologue -- run.sh sources step/_lib/session.sql for you. phpMyAdmin will not, so it is inlined. SET @OLD_SQL_MODE = @@SQL_MODE; SET SQL_MODE = IF(@@SQL_MODE LIKE '%STRICT_TRANS_TABLES%' AND @@SQL_MODE LIKE '%NO_AUTO_VALUE_ON_ZERO%' AND @@SQL_MODE NOT LIKE '%NO_ZERO_DATE%' AND @@SQL_MODE NOT LIKE '%NO_ZERO_IN_DATE%', @@SQL_MODE, CONCAT(REPLACE(REPLACE(@@SQL_MODE, 'NO_ZERO_IN_DATE', ''), 'NO_ZERO_DATE', ''), ',NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES')); -- Assert all four, not just the first. STRICT_TRANS_TABLES missing means out-of-range -- values are clamped silently; NO_ZERO_DATE still present means any ALTER that rebuilds a -- legacy table re-validates its '0000-00-00' rows and dies with ERROR 1292. The second is -- the one that actually stops the migration, and it was not being checked. SELECT IF(@@SQL_MODE LIKE '%STRICT_TRANS_TABLES%' AND @@SQL_MODE LIKE '%NO_AUTO_VALUE_ON_ZERO%' AND @@SQL_MODE NOT LIKE '%NO_ZERO_DATE%' AND @@SQL_MODE NOT LIKE '%NO_ZERO_IN_DATE%', 'sql_mode OK - proceeding', (SELECT 'ABORT: sql_mode is wrong. Wanted STRICT_TRANS_TABLES + NO_AUTO_VALUE_ON_ZERO,' UNION ALL SELECT 'and NO_ZERO_DATE / NO_ZERO_IN_DATE OFF. The SET above should have done it.') ) AS preflight_sql_mode; SET @OLD_FK = @@FOREIGN_KEY_CHECKS; SET FOREIGN_KEY_CHECKS = 1; SET @OLD_LOCK_WAIT = @@SESSION.lock_wait_timeout; SET @OLD_INNODB_LOCK_WAIT = @@SESSION.innodb_lock_wait_timeout; SET SESSION lock_wait_timeout = 60; SET SESSION innodb_lock_wait_timeout = 60; -- The ledger, copied verbatim from step/_lib/ledger.sql -- not restated, so the two -- paths cannot disagree about its definition. -- --------------------------------------------------------------------- -- _lib/ledger.sql -- the only thing here that is not in the source file -- One row per applied step. This is the record that decides what a re-run skips, -- what --status reports, and -- through the app-side gate -- which modules the -- application will serve. It lives in the database on purpose: a file on one -- operator's laptop drifts the moment someone runs a step from another machine. -- -- Deliberately NOT in the project's PascalCase convention. It is an ops table, never -- touched by Eloquent, and it should be visibly not-an-app-table. -- -- `checksum` is the SHA-256 of the step's SQL payload -- the verbatim statements, -- not the file -- so it cannot change when a comment above them is reworded. -- _lib/steps.tsv carries the whole-file hashes for tamper detection. -- -- ⚠ `step` IS ascii, AND THAT IS LOAD-BEARING. Every guard compares it against a -- user variable, and a user variable carries the CONNECTION collation. Give this -- column utf8mb4_unicode_ci and the very first step dies with ERROR 1267, -- "Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT) and -- (utf8mb4_0900_ai_ci,IMPLICIT) for operation '='" -- measured, that is exactly -- how the first end-to-end run failed. ascii is a repertoire subset of latin1 and -- of utf8mb4, so the server coerces it to whatever the connection is using, -- whatever that turns out to be on the day. Step names are [a-z0-9_/] by -- construction, so nothing is lost. -- --------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS `_migration_ledger` ( `step` varchar(191) CHARACTER SET ascii COLLATE ascii_general_ci NOT NULL, `applied_at` datetime NOT NULL, `checksum` char(64) NOT NULL, `duration_ms` int NOT NULL, PRIMARY KEY (`step`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------- preflight -- WAS THIS DATABASE MIGRATED FROM A DIFFERENT VERSION OF THIS FILE? -- -- `_migration_ledger.checksum` is the SHA-256 of each step's payload. It is written 632 -- times across the tree and, until this block existed, read never -- so a corrected payload -- re-issued under the same step name was skipped forever on any database that had recorded -- the old one, silently and permanently. SELECT COUNT(*) INTO @ck_bad FROM `_migration_ledger` WHERE `step` IN ('_system/40_charset', '_system/50_create_budgetandtargetachievements', '_system/50_create_budgetandtargetreal', '_system/50_create_cache', '_system/50_create_cache_locks', '_system/50_create_failed_jobs', '_system/50_create_job_batches', '_system/50_create_jobs', '_system/50_create_migrations', '_system/50_create_notification', '_system/50_create_nsproduct', '_system/50_create_quotationitemorigin', '_system/50_create_sessions', 'menu/10_alter', 'menu/20_cleanup', 'menu/30_assert', 'menu/40_fk', 'rolegadget/10_alter', 'rolegadget/20_cleanup', 'rolegadget/30_assert', 'rolegadget/40_fk', 'rolemenu/10_alter', 'rolemenu/20_cleanup', 'rolemenu/30_assert', 'rolemenu/40_fk', 'userbusinesstravelbranch/10_alter', 'userbusinesstravelbranch/20_cleanup', 'userbusinesstravelbranch/30_assert', 'userbusinesstravelbranch/40_fk', 'userdetails/10_alter', 'userdetails/20_cleanup', 'userdetails/30_assert', 'userdetails/40_fk', 'userdivision/10_alter', 'userdivision/20_cleanup', 'userdivision/30_assert', 'userdivision/40_fk', 'usereducation/10_alter', 'usereducation/20_cleanup', 'usereducation/30_assert', 'usereducation/40_fk', 'userjob/10_alter', 'userjob/20_cleanup', 'userjob/30_assert', 'userjob/40_fk', 'userlabgroupdivision/10_alter', 'userlabgroupdivision/20_cleanup', 'userlabgroupdivision/30_assert', 'userlabgroupdivision/40_fk', 'userprincipal/10_alter', 'userprincipal/20_cleanup', 'userprincipal/30_assert', 'userprincipal/40_fk', 'users/05_rename', 'users/10_alter', 'users/20_cleanup', 'users/30_assert', 'users/40_fk', 'usersperiode/10_alter', 'usersperiode/20_cleanup', 'usersperiode/30_assert', 'usersperiode/40_fk', 'userwarehouse/10_alter', 'userwarehouse/20_cleanup', 'userwarehouse/30_assert', 'userwarehouse/40_fk') AND CONCAT(`step`, ':', `checksum`) NOT IN ('_system/40_charset:550127b3207f277f843b6edd09dccca327dbc79296e7d8fd36cb225d7cf439c9', '_system/50_create_budgetandtargetachievements:5574606c7206be89d0753456c434b3686ca37ad8f81735fd76282994997e6ab7', '_system/50_create_budgetandtargetreal:fa7f9b03f274c29964510ff2c7474c48a142fcd0311696b17cd4dc10fd16ff4e', '_system/50_create_cache:f897593506175d7f50b7911726aa8c094d6e3999410c39da90fcd8dca82d6770', '_system/50_create_cache_locks:1715d81ce6aeca15288dddff663e4bf0b4c8b71ff60a550ef4c57e9e11f4d0c7', '_system/50_create_failed_jobs:5d562e4884f55ea7442afc28099382327cb6961b5e5044b56720d4175862d60a', '_system/50_create_job_batches:895995ce1d9cc8b0f8c24f57441eab7a87c7d73b89ed502252afe097b7fc8d4b', '_system/50_create_jobs:7e419befd5d8a03e93317db73487db6a4c3260e5bf7b821161c13d46bc0eba4e', '_system/50_create_migrations:4db76a78e653e02b253b7841327c574f4559faa176492c03d5b972cf0c99d644', '_system/50_create_notification:3c54f51e6a99800cbb04281224aa954769acd6299b00eb4ed999c80f7d260a85', '_system/50_create_nsproduct:7305438878e4630b498389a23d36e352df33d2a6869cace0a9a916b1c2bf06b6', '_system/50_create_quotationitemorigin:81062445aa2d2052709f594901ecb58f98b9147d0079212b306b961da95c2775', '_system/50_create_sessions:7aafe284698e7908fab79d1b194477bf1d355483732469a6dcba7042c370951b', 'menu/10_alter:3e05accaec3bd294eca28e44b1972bb15b2c0dae66d125fde49bc79bacdd4bf6', 'menu/20_cleanup:72007b70bb2cb31c1e568de5d96ea95edf1cf173b7fb6e8912e124bf5b6d07ab', 'menu/30_assert:77fd4a0bb04bc2a23772c43af96e828d3d20d571cbc276fd667dec830d638f15', 'menu/40_fk:0cb760dde26d62befdf086775ba7e82fef8540d18f763e1278cecf653d417311', 'rolegadget/10_alter:97fbce5fc377b898615d9aba7692c1abd265f250248647dc321d1617ba62f94e', 'rolegadget/20_cleanup:b760f29cd03ad28b0fd8c1407952c5e2449541a7d02872f73a30a094ff5db5d3', 'rolegadget/30_assert:432f9dced56623844cfa3309d31173b793bf074f2d91a1f5d5748045cc2aca58', 'rolegadget/40_fk:78793fd84f6485d977623602295b114b4a0cdc61a4b7a3dcc765f251c4d321ae', 'rolemenu/10_alter:6cd5d9dc183e8d186a95a8004fb9c2424ee6d6b8ed0a3df1a3a8b5915cc3b5e8', 'rolemenu/20_cleanup:f45634d2ec853a7b811854ee9b1f91b2035a97a0cc43a93ed9233376caee68e2', 'rolemenu/30_assert:792af8477ec8585996d34d650bc2943238dc938bdd9caa435b1f35e1f255dbd4', 'rolemenu/40_fk:ace79df65ec36c0ab04ad37d45f2ea09cbed3a23372f47672c49e1484b29c0d3', 'userbusinesstravelbranch/10_alter:625bd3285c9fcc44dacea8967a9e4a8eb2df6b184d7eb9d453e7a0550bae3660', 'userbusinesstravelbranch/20_cleanup:c9f1a8fef1c213a7ea0b3182100699379e5b6424c1de9913856f78d3b862dea0', 'userbusinesstravelbranch/30_assert:2e785f67e942f2d0678573f278d2dd6bf1b8f2c3a2687dd5f0c406cae258d646', 'userbusinesstravelbranch/40_fk:ef9e639d3faa46d3e496821fc791e37b08d8c69450c03acf365083cd5bf088d5', 'userdetails/10_alter:a7e33bbd590b803d492bfa0411a6fc0eec5ce5c72859e7b428c2b4de657eee1a', 'userdetails/20_cleanup:a48e0529700541043a07df4b9a4469c0536a46a1b8d191e539b8a3ee15e5cfa8', 'userdetails/30_assert:ce5a299a78a69994211ee99f5afbc92b6561b14ab6e8ae137125a0bb699ec5cf', 'userdetails/40_fk:c5a4bbbdab1a523821dbe3ff22c5c359ddf9707c1399abc4f1a6a884519168b6', 'userdivision/10_alter:e883267e4864b601be959c928c7321ec85b74d8794f6f850e9ab659b656f6321', 'userdivision/20_cleanup:c36debe0dca4ab42fd16715191c024165cf12648e5a09335b487e778c3fb83da', 'userdivision/30_assert:bfa486c01ee7d9c2b4e14ca52176a017a3bdecb86b032a502acd8e2725826adc', 'userdivision/40_fk:9a41ab32f63fae5c014b5e156d6423e693fff40cbd11e3f69571e1239e96345e', 'usereducation/10_alter:91b770ddb4ab3c2e2d1f140045699855728c6192caa390a3b123788e9b98cb4b', 'usereducation/20_cleanup:0bdb3ddf56734d2c8b02db962f840aa528018a203a0d7660542cbb3805a44178', 'usereducation/30_assert:474bf683c4b40104c7db2110c2687b84cc79f7299070b9bf614586b434985ab3', 'usereducation/40_fk:519dfaf3e50e908b91977086e6ac2cf39c09145e5e38684270c34d1866c9e0d5', 'userjob/10_alter:d73ac13c8316f5dc11b44e29ef3dde3c570fa9febe98b1a5b66ba57dc60f53c1', 'userjob/20_cleanup:f1c2781148d954b9c4b0ec9d875ebe2a649260d2e7b7f623c445eaf7ac8acbad', 'userjob/30_assert:20552348a5dbbe25c3a302f06972711875ec28922d60d855f1f5dde231fd058a', 'userjob/40_fk:a154c732fce4c89af989ea4901602d9c687e927a657146e5785bb4ede928a691', 'userlabgroupdivision/10_alter:998f48206dd28e28ac8adbfac81662ea9f13bb1d7c07cb9ecc4d1f50e37346ea', 'userlabgroupdivision/20_cleanup:5940e6f5eb14f42063bc84aa7647388b1527856806d3873fd57d08f0517761cd', 'userlabgroupdivision/30_assert:fd513369c36024271550dfac104b1bc7e149f20267a4c2a21d849d743d782461', 'userlabgroupdivision/40_fk:f3583725e7f14597890c4ed07b2e773647211530dd91362950e503249fd449c7', 'userprincipal/10_alter:1dd43f2c5aa255534d8e94d21c0939d37aaf0e2f30bcd40ad29e4d2cf87d71f5', 'userprincipal/20_cleanup:bb07b3d736fb4db4cbf33b778e604fb024a0eb80d782d75419b4f54857111654', 'userprincipal/30_assert:aa32f67a545dc5b95c78e0f566dacf7fa7fa1546fe34c96a5b708caab8b1dca8', 'userprincipal/40_fk:1d51708ee6ccc62aa69c6f647f63adf1096c7771e2f8acf0718f1487f42bf202', 'users/05_rename:55fa2d0cc05aa2c4f9d1be46e3953005ab00c8294174db8fe309cbcb4a09bdf1', 'users/10_alter:ae21ef0b7e80fd95fd4d0fe2345f039393b70dbfc8bdf1e1c5f0d4d3997aec12', 'users/20_cleanup:7e4a513f1a898a35c7c223038ff924676e118b57e2c788fbbd7be5a3b33e66ae', 'users/30_assert:ef741d902041033f76aa8e4c8171f678bc248b6276faf869a330934e6d3fe8ca', 'users/40_fk:3ba918222a2fbd2d209389d01f18837d7bd539bde81ac161205f12d78ed77fc8', 'usersperiode/10_alter:2c94c6751c609fdfab55b421dbc4beb436c478213959a9f763b38cd77ae7f8b0', 'usersperiode/20_cleanup:b17a948a80d36d6c66565b95912a8be298c4ace191a78fa914d13ef6e47c1a55', 'usersperiode/30_assert:d73ccc0af5f94bbab6578e123beedd6a4cdd5ef54d6b613edaa2ca3b907e3644', 'usersperiode/40_fk:3f24fe9f604d03cfcf40e94ffc149908e9c540efa708dac2325feb0b7b06a017', 'userwarehouse/10_alter:cdaf84afd8d65eb25d533dbc4c7ec0e9e2f6aedd784633d5e29a33353eb69768', 'userwarehouse/20_cleanup:b4857b4655b829e714a9e54e235b22a4787b786b27624ec2e8fdcbf24c193339', 'userwarehouse/30_assert:c6099a85190dbce1c761939941dcda90da2a7f7eac2cdb5b7b903ad32be51fc1', 'userwarehouse/40_fk:85b7ea47d4207f4367bd6b4d89c4034ed4786d090dce4b2bb35c3537856ced12'); SET @pf_ck = IF(@ck_bad = 0, 'SELECT ''preflight: ledger matches this file'' AS preflight_checksum', CONCAT('SELECT `ABORT: ', @ck_bad, ' step(s) here were applied from a DIFFERENT version of this file. Their payloads changed. Compare before re-importing; do not delete ledger rows blindly.`')); PREPARE _pf FROM @pf_ck; EXECUTE _pf; DEALLOCATE PREPARE _pf; -- --------------------------------------------------------------------- -- unit: menu (system) -- --------------------------------------------------------------------- SET SQL_MODE = IF(@@SQL_MODE LIKE '%STRICT_TRANS_TABLES%' AND @@SQL_MODE LIKE '%NO_AUTO_VALUE_ON_ZERO%' AND @@SQL_MODE NOT LIKE '%NO_ZERO_DATE%' AND @@SQL_MODE NOT LIKE '%NO_ZERO_IN_DATE%', @@SQL_MODE, CONCAT(REPLACE(REPLACE(@@SQL_MODE, 'NO_ZERO_IN_DATE', ''), 'NO_ZERO_DATE', ''), ',NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES')); -- --------------------------------------------------------------------- -- menu/10_alter -- source section A -- The merged ALTER: new columns, type/charset changes, column order, NULL relaxation -- and the indexes every foreign key needs -- ONE statement, so InnoDB rebuilds this -- table ONCE. The original migration split these across five sections and rebuilt some -- tables four times; that is where its 12 hours went. Do not split this back up. -- -- Clause order is the original section order (3b, 3d, 3e, 4, 7), which is what keeps -- every ADD COLUMN ... AFTER positioned exactly as before. -- --------------------------------------------------------------------- SET @step = 'menu/10_alter'; SET @todo = (SELECT COUNT(*) = 0 FROM `_migration_ledger` WHERE `step` = @step); SET @t0 = NOW(3); SET @sql = IF(@todo, 'ALTER TABLE `menu` ADD COLUMN `LinkLaravel` varchar(500) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL AFTER `Link`, ADD COLUMN `Icon` varchar(50) DEFAULT NULL AFTER `LinkLaravel`, ADD COLUMN `Path` varchar(500) NOT NULL DEFAULT '''' AFTER `Icon`, MODIFY COLUMN `ParentID` int DEFAULT NULL, ADD INDEX `ParentID` (`ParentID`)', 'DO 0'); PREPARE _step FROM @sql; EXECUTE _step; DEALLOCATE PREPARE _step; INSERT INTO `_migration_ledger` (`step`, `applied_at`, `checksum`, `duration_ms`) VALUES (@step, NOW(), '3e05accaec3bd294eca28e44b1972bb15b2c0dae66d125fde49bc79bacdd4bf6', TIMESTAMPDIFF(MICROSECOND, @t0, NOW(3)) DIV 1000) ON DUPLICATE KEY UPDATE `step` = `step`; -- --------------------------------------------------------------------- -- menu/20_cleanup -- source section B (= the original sections 5 and 6, merged) -- 0-sentinels AND orphaned references -> NULL, one pass per table. -- section 5: WHERE `c` = 0 -- section 6: WHERE `c` IS NOT NULL AND parent.ID IS NULL -- merged : WHERE `c` IS NOT NULL AND (`c` = 0 OR parent.ID IS NULL) -- Every LEFT JOIN is a primary-key equality, so none of them can multiply rows. -- -- DATA CHANGE, and this script cannot reverse it. Run the audit first. -- In its own transaction, with the ledger row inside it: either the cleanup and the -- record of it both land, or neither does. -- --------------------------------------------------------------------- START TRANSACTION; SET @step = 'menu/20_cleanup'; SET @todo = (SELECT COUNT(*) = 0 FROM `_migration_ledger` WHERE `step` = @step); SET @t0 = NOW(3); SET @sql = IF(@todo, 'UPDATE `menu` c LEFT JOIN `menu` p0 ON c.`ParentID` = p0.`ID` SET c.`ParentID` = IF((c.`ParentID` IS NOT NULL AND (c.`ParentID` = 0 OR p0.`ID` IS NULL)), NULL, c.`ParentID`) WHERE (c.`ParentID` IS NOT NULL AND (c.`ParentID` = 0 OR p0.`ID` IS NULL))', 'DO 0'); PREPARE _step FROM @sql; EXECUTE _step; DEALLOCATE PREPARE _step; INSERT INTO `_migration_ledger` (`step`, `applied_at`, `checksum`, `duration_ms`) VALUES (@step, NOW(), '72007b70bb2cb31c1e568de5d96ea95edf1cf173b7fb6e8912e124bf5b6d07ab', TIMESTAMPDIFF(MICROSECOND, @t0, NOW(3)) DIV 1000) ON DUPLICATE KEY UPDATE `step` = `step`; COMMIT; -- --------------------------------------------------------------------- -- menu/30_assert -- source section 7b -- Orphan assertion over this table's 1 foreign-key relationships. -- -- This is what pays for 40_fk being fast. The original created the constraints with -- FOREIGN_KEY_CHECKS ON so a row the cleanup missed would abort the run, and paid for it -- with a full table copy per constraint. This buys the same protection with one indexed -- pass, and 40_fk then adds the constraints as metadata only. -- -- NOT ledger-guarded on the way in, unlike every other phase: it only reads, and the -- point of it is to be re-runnable. It does record its ledger row -- 40_fk requires it. -- -- IF THIS ABORTS: a row survived 20_cleanup. Same stop-and-look event the original would -- have hit as ERROR 1452 inside section 8, but nothing has been created yet. -- --------------------------------------------------------------------- SET @step = 'menu/30_assert'; SET @t0 = NOW(3); SET @orphans = 0; SELECT COUNT(*) INTO @n FROM `menu` c LEFT JOIN `menu` p0 ON c.`ParentID` = p0.`ID` WHERE (c.`ParentID` IS NOT NULL AND p0.`ID` IS NULL); SET @orphans = @orphans + @n; -- Two-row subquery on the false branch: ERROR 1242, an abort, before 40_fk runs. SELECT IF(@orphans = 0, 'menu: orphan check OK - 0 rows across 1 relationships', (SELECT CONCAT('ABORT: ', @orphans, ' rows in `menu` still reference a parent that does not exist') UNION ALL SELECT 'Run 20_cleanup for this table first. Do NOT skip this check.') ) AS preflight_orphans; INSERT INTO `_migration_ledger` (`step`, `applied_at`, `checksum`, `duration_ms`) VALUES (@step, NOW(), '77fd4a0bb04bc2a23772c43af96e828d3d20d571cbc276fd667dec830d638f15', TIMESTAMPDIFF(MICROSECOND, @t0, NOW(3)) DIV 1000) ON DUPLICATE KEY UPDATE `step` = `step`; -- --------------------------------------------------------------------- -- menu/40_fk -- source section 8 -- The constraints, one ALTER for the whole table. -- -- FOREIGN_KEY_CHECKS is turned OFF for this statement and restored afterwards. With -- checks on InnoDB cannot add a foreign key in place -- it copies the whole table and -- re-validates every row, which is where the original migration's 154 table copies -- came from. The validation is not skipped, it MOVED: 30_assert proves zero orphans -- and refuses to let this run otherwise. -- --------------------------------------------------------------------- SET @fk_prev = @@FOREIGN_KEY_CHECKS; SET FOREIGN_KEY_CHECKS = 0; SET @step = 'menu/40_fk'; SET @todo = (SELECT COUNT(*) = 0 FROM `_migration_ledger` WHERE `step` = @step); SET @t0 = NOW(3); SET @sql = IF(@todo, 'ALTER TABLE `menu` ADD CONSTRAINT `menu_ParentID` FOREIGN KEY (`ParentID`) REFERENCES `menu` (`ID`) ON DELETE RESTRICT ON UPDATE RESTRICT', 'DO 0'); PREPARE _step FROM @sql; EXECUTE _step; DEALLOCATE PREPARE _step; INSERT INTO `_migration_ledger` (`step`, `applied_at`, `checksum`, `duration_ms`) VALUES (@step, NOW(), '0cb760dde26d62befdf086775ba7e82fef8540d18f763e1278cecf653d417311', TIMESTAMPDIFF(MICROSECOND, @t0, NOW(3)) DIV 1000) ON DUPLICATE KEY UPDATE `step` = `step`; SET FOREIGN_KEY_CHECKS = IFNULL(@fk_prev, 1); -- --------------------------------------------------------------------- -- unit: rolemenu (system) -- --------------------------------------------------------------------- SET SQL_MODE = IF(@@SQL_MODE LIKE '%STRICT_TRANS_TABLES%' AND @@SQL_MODE LIKE '%NO_AUTO_VALUE_ON_ZERO%' AND @@SQL_MODE NOT LIKE '%NO_ZERO_DATE%' AND @@SQL_MODE NOT LIKE '%NO_ZERO_IN_DATE%', @@SQL_MODE, CONCAT(REPLACE(REPLACE(@@SQL_MODE, 'NO_ZERO_IN_DATE', ''), 'NO_ZERO_DATE', ''), ',NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES')); -- --------------------------------------------------------------------- -- rolemenu/10_alter -- source section A -- The merged ALTER: new columns, type/charset changes, column order, NULL relaxation -- and the indexes every foreign key needs -- ONE statement, so InnoDB rebuilds this -- table ONCE. The original migration split these across five sections and rebuilt some -- tables four times; that is where its 12 hours went. Do not split this back up. -- -- Clause order is the original section order (3b, 3d, 3e, 4, 7), which is what keeps -- every ADD COLUMN ... AFTER positioned exactly as before. -- --------------------------------------------------------------------- SET @step = 'rolemenu/10_alter'; SET @todo = (SELECT COUNT(*) = 0 FROM `_migration_ledger` WHERE `step` = @step); SET @t0 = NOW(3); SET @sql = IF(@todo, 'ALTER TABLE `rolemenu` MODIFY COLUMN `RoleID` int DEFAULT NULL, MODIFY COLUMN `MenuID` int DEFAULT NULL, ADD INDEX `RoleID` (`RoleID`,`MenuID`), ADD INDEX `rolemenu_MenuID` (`MenuID`)', 'DO 0'); PREPARE _step FROM @sql; EXECUTE _step; DEALLOCATE PREPARE _step; INSERT INTO `_migration_ledger` (`step`, `applied_at`, `checksum`, `duration_ms`) VALUES (@step, NOW(), '6cd5d9dc183e8d186a95a8004fb9c2424ee6d6b8ed0a3df1a3a8b5915cc3b5e8', TIMESTAMPDIFF(MICROSECOND, @t0, NOW(3)) DIV 1000) ON DUPLICATE KEY UPDATE `step` = `step`; -- --------------------------------------------------------------------- -- rolemenu/20_cleanup -- source section B (= the original sections 5 and 6, merged) -- 0-sentinels AND orphaned references -> NULL, one pass per table. -- section 5: WHERE `c` = 0 -- section 6: WHERE `c` IS NOT NULL AND parent.ID IS NULL -- merged : WHERE `c` IS NOT NULL AND (`c` = 0 OR parent.ID IS NULL) -- Every LEFT JOIN is a primary-key equality, so none of them can multiply rows. -- -- DATA CHANGE, and this script cannot reverse it. Run the audit first. -- In its own transaction, with the ledger row inside it: either the cleanup and the -- record of it both land, or neither does. -- --------------------------------------------------------------------- START TRANSACTION; SET @step = 'rolemenu/20_cleanup'; SET @todo = (SELECT COUNT(*) = 0 FROM `_migration_ledger` WHERE `step` = @step); SET @t0 = NOW(3); SET @sql = IF(@todo, 'UPDATE `rolemenu` c LEFT JOIN `menu` p0 ON c.`MenuID` = p0.`ID` LEFT JOIN `role` p1 ON c.`RoleID` = p1.`ID` SET c.`MenuID` = IF((c.`MenuID` IS NOT NULL AND (c.`MenuID` = 0 OR p0.`ID` IS NULL)), NULL, c.`MenuID`), c.`RoleID` = IF((c.`RoleID` IS NOT NULL AND (c.`RoleID` = 0 OR p1.`ID` IS NULL)), NULL, c.`RoleID`) WHERE (c.`MenuID` IS NOT NULL AND (c.`MenuID` = 0 OR p0.`ID` IS NULL)) OR (c.`RoleID` IS NOT NULL AND (c.`RoleID` = 0 OR p1.`ID` IS NULL))', 'DO 0'); PREPARE _step FROM @sql; EXECUTE _step; DEALLOCATE PREPARE _step; INSERT INTO `_migration_ledger` (`step`, `applied_at`, `checksum`, `duration_ms`) VALUES (@step, NOW(), 'f45634d2ec853a7b811854ee9b1f91b2035a97a0cc43a93ed9233376caee68e2', TIMESTAMPDIFF(MICROSECOND, @t0, NOW(3)) DIV 1000) ON DUPLICATE KEY UPDATE `step` = `step`; COMMIT; -- --------------------------------------------------------------------- -- rolemenu/30_assert -- source section 7b -- Orphan assertion over this table's 2 foreign-key relationships. -- -- This is what pays for 40_fk being fast. The original created the constraints with -- FOREIGN_KEY_CHECKS ON so a row the cleanup missed would abort the run, and paid for it -- with a full table copy per constraint. This buys the same protection with one indexed -- pass, and 40_fk then adds the constraints as metadata only. -- -- NOT ledger-guarded on the way in, unlike every other phase: it only reads, and the -- point of it is to be re-runnable. It does record its ledger row -- 40_fk requires it. -- -- IF THIS ABORTS: a row survived 20_cleanup. Same stop-and-look event the original would -- have hit as ERROR 1452 inside section 8, but nothing has been created yet. -- --------------------------------------------------------------------- SET @step = 'rolemenu/30_assert'; SET @t0 = NOW(3); SET @orphans = 0; SELECT COUNT(*) INTO @n FROM `rolemenu` c LEFT JOIN `menu` p0 ON c.`MenuID` = p0.`ID` LEFT JOIN `role` p1 ON c.`RoleID` = p1.`ID` WHERE (c.`MenuID` IS NOT NULL AND p0.`ID` IS NULL) OR (c.`RoleID` IS NOT NULL AND p1.`ID` IS NULL); SET @orphans = @orphans + @n; -- Two-row subquery on the false branch: ERROR 1242, an abort, before 40_fk runs. SELECT IF(@orphans = 0, 'rolemenu: orphan check OK - 0 rows across 2 relationships', (SELECT CONCAT('ABORT: ', @orphans, ' rows in `rolemenu` still reference a parent that does not exist') UNION ALL SELECT 'Run 20_cleanup for this table first. Do NOT skip this check.') ) AS preflight_orphans; INSERT INTO `_migration_ledger` (`step`, `applied_at`, `checksum`, `duration_ms`) VALUES (@step, NOW(), '792af8477ec8585996d34d650bc2943238dc938bdd9caa435b1f35e1f255dbd4', TIMESTAMPDIFF(MICROSECOND, @t0, NOW(3)) DIV 1000) ON DUPLICATE KEY UPDATE `step` = `step`; -- --------------------------------------------------------------------- -- rolemenu/40_fk -- source section 8 -- The constraints, one ALTER for the whole table. -- -- FOREIGN_KEY_CHECKS is turned OFF for this statement and restored afterwards. With -- checks on InnoDB cannot add a foreign key in place -- it copies the whole table and -- re-validates every row, which is where the original migration's 154 table copies -- came from. The validation is not skipped, it MOVED: 30_assert proves zero orphans -- and refuses to let this run otherwise. -- --------------------------------------------------------------------- SET @fk_prev = @@FOREIGN_KEY_CHECKS; SET FOREIGN_KEY_CHECKS = 0; SET @step = 'rolemenu/40_fk'; SET @todo = (SELECT COUNT(*) = 0 FROM `_migration_ledger` WHERE `step` = @step); SET @t0 = NOW(3); SET @sql = IF(@todo, 'ALTER TABLE `rolemenu` ADD CONSTRAINT `rolemenu_MenuID` FOREIGN KEY (`MenuID`) REFERENCES `menu` (`ID`) ON DELETE RESTRICT ON UPDATE RESTRICT, ADD CONSTRAINT `rolemenu_RoleID` FOREIGN KEY (`RoleID`) REFERENCES `role` (`ID`) ON DELETE RESTRICT ON UPDATE RESTRICT', 'DO 0'); PREPARE _step FROM @sql; EXECUTE _step; DEALLOCATE PREPARE _step; INSERT INTO `_migration_ledger` (`step`, `applied_at`, `checksum`, `duration_ms`) VALUES (@step, NOW(), 'ace79df65ec36c0ab04ad37d45f2ea09cbed3a23372f47672c49e1484b29c0d3', TIMESTAMPDIFF(MICROSECOND, @t0, NOW(3)) DIV 1000) ON DUPLICATE KEY UPDATE `step` = `step`; SET FOREIGN_KEY_CHECKS = IFNULL(@fk_prev, 1); -- --------------------------------------------------------------------- -- unit: rolegadget (detail, owned by rolemenu) -- --------------------------------------------------------------------- SET SQL_MODE = IF(@@SQL_MODE LIKE '%STRICT_TRANS_TABLES%' AND @@SQL_MODE LIKE '%NO_AUTO_VALUE_ON_ZERO%' AND @@SQL_MODE NOT LIKE '%NO_ZERO_DATE%' AND @@SQL_MODE NOT LIKE '%NO_ZERO_IN_DATE%', @@SQL_MODE, CONCAT(REPLACE(REPLACE(@@SQL_MODE, 'NO_ZERO_IN_DATE', ''), 'NO_ZERO_DATE', ''), ',NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES')); -- --------------------------------------------------------------------- -- rolegadget/10_alter -- source section A -- The merged ALTER: new columns, type/charset changes, column order, NULL relaxation -- and the indexes every foreign key needs -- ONE statement, so InnoDB rebuilds this -- table ONCE. The original migration split these across five sections and rebuilt some -- tables four times; that is where its 12 hours went. Do not split this back up. -- -- Clause order is the original section order (3b, 3d, 3e, 4, 7), which is what keeps -- every ADD COLUMN ... AFTER positioned exactly as before. -- --------------------------------------------------------------------- SET @step = 'rolegadget/10_alter'; SET @todo = (SELECT COUNT(*) = 0 FROM `_migration_ledger` WHERE `step` = @step); SET @t0 = NOW(3); SET @sql = IF(@todo, 'ALTER TABLE `rolegadget` MODIFY COLUMN `RoleID` int DEFAULT NULL, MODIFY COLUMN `GadgetID` int DEFAULT NULL, ADD INDEX `rolegadget_GadgetID` (`GadgetID`), ADD INDEX `RoleID` (`RoleID`,`GadgetID`)', 'DO 0'); PREPARE _step FROM @sql; EXECUTE _step; DEALLOCATE PREPARE _step; INSERT INTO `_migration_ledger` (`step`, `applied_at`, `checksum`, `duration_ms`) VALUES (@step, NOW(), '97fbce5fc377b898615d9aba7692c1abd265f250248647dc321d1617ba62f94e', TIMESTAMPDIFF(MICROSECOND, @t0, NOW(3)) DIV 1000) ON DUPLICATE KEY UPDATE `step` = `step`; -- --------------------------------------------------------------------- -- rolegadget/20_cleanup -- source section B (= the original sections 5 and 6, merged) -- 0-sentinels AND orphaned references -> NULL, one pass per table. -- section 5: WHERE `c` = 0 -- section 6: WHERE `c` IS NOT NULL AND parent.ID IS NULL -- merged : WHERE `c` IS NOT NULL AND (`c` = 0 OR parent.ID IS NULL) -- Every LEFT JOIN is a primary-key equality, so none of them can multiply rows. -- -- DATA CHANGE, and this script cannot reverse it. Run the audit first. -- In its own transaction, with the ledger row inside it: either the cleanup and the -- record of it both land, or neither does. -- --------------------------------------------------------------------- START TRANSACTION; SET @step = 'rolegadget/20_cleanup'; SET @todo = (SELECT COUNT(*) = 0 FROM `_migration_ledger` WHERE `step` = @step); SET @t0 = NOW(3); SET @sql = IF(@todo, 'UPDATE `rolegadget` c LEFT JOIN `gadget` p0 ON c.`GadgetID` = p0.`ID` LEFT JOIN `role` p1 ON c.`RoleID` = p1.`ID` SET c.`GadgetID` = IF((c.`GadgetID` IS NOT NULL AND (c.`GadgetID` = 0 OR p0.`ID` IS NULL)), NULL, c.`GadgetID`), c.`RoleID` = IF((c.`RoleID` IS NOT NULL AND (c.`RoleID` = 0 OR p1.`ID` IS NULL)), NULL, c.`RoleID`) WHERE (c.`GadgetID` IS NOT NULL AND (c.`GadgetID` = 0 OR p0.`ID` IS NULL)) OR (c.`RoleID` IS NOT NULL AND (c.`RoleID` = 0 OR p1.`ID` IS NULL))', 'DO 0'); PREPARE _step FROM @sql; EXECUTE _step; DEALLOCATE PREPARE _step; INSERT INTO `_migration_ledger` (`step`, `applied_at`, `checksum`, `duration_ms`) VALUES (@step, NOW(), 'b760f29cd03ad28b0fd8c1407952c5e2449541a7d02872f73a30a094ff5db5d3', TIMESTAMPDIFF(MICROSECOND, @t0, NOW(3)) DIV 1000) ON DUPLICATE KEY UPDATE `step` = `step`; COMMIT; -- --------------------------------------------------------------------- -- rolegadget/30_assert -- source section 7b -- Orphan assertion over this table's 2 foreign-key relationships. -- -- This is what pays for 40_fk being fast. The original created the constraints with -- FOREIGN_KEY_CHECKS ON so a row the cleanup missed would abort the run, and paid for it -- with a full table copy per constraint. This buys the same protection with one indexed -- pass, and 40_fk then adds the constraints as metadata only. -- -- NOT ledger-guarded on the way in, unlike every other phase: it only reads, and the -- point of it is to be re-runnable. It does record its ledger row -- 40_fk requires it. -- -- IF THIS ABORTS: a row survived 20_cleanup. Same stop-and-look event the original would -- have hit as ERROR 1452 inside section 8, but nothing has been created yet. -- --------------------------------------------------------------------- SET @step = 'rolegadget/30_assert'; SET @t0 = NOW(3); SET @orphans = 0; SELECT COUNT(*) INTO @n FROM `rolegadget` c LEFT JOIN `gadget` p0 ON c.`GadgetID` = p0.`ID` LEFT JOIN `role` p1 ON c.`RoleID` = p1.`ID` WHERE (c.`GadgetID` IS NOT NULL AND p0.`ID` IS NULL) OR (c.`RoleID` IS NOT NULL AND p1.`ID` IS NULL); SET @orphans = @orphans + @n; -- Two-row subquery on the false branch: ERROR 1242, an abort, before 40_fk runs. SELECT IF(@orphans = 0, 'rolegadget: orphan check OK - 0 rows across 2 relationships', (SELECT CONCAT('ABORT: ', @orphans, ' rows in `rolegadget` still reference a parent that does not exist') UNION ALL SELECT 'Run 20_cleanup for this table first. Do NOT skip this check.') ) AS preflight_orphans; INSERT INTO `_migration_ledger` (`step`, `applied_at`, `checksum`, `duration_ms`) VALUES (@step, NOW(), '432f9dced56623844cfa3309d31173b793bf074f2d91a1f5d5748045cc2aca58', TIMESTAMPDIFF(MICROSECOND, @t0, NOW(3)) DIV 1000) ON DUPLICATE KEY UPDATE `step` = `step`; -- --------------------------------------------------------------------- -- rolegadget/40_fk -- source section 8 -- The constraints, one ALTER for the whole table. -- -- FOREIGN_KEY_CHECKS is turned OFF for this statement and restored afterwards. With -- checks on InnoDB cannot add a foreign key in place -- it copies the whole table and -- re-validates every row, which is where the original migration's 154 table copies -- came from. The validation is not skipped, it MOVED: 30_assert proves zero orphans -- and refuses to let this run otherwise. -- --------------------------------------------------------------------- SET @fk_prev = @@FOREIGN_KEY_CHECKS; SET FOREIGN_KEY_CHECKS = 0; SET @step = 'rolegadget/40_fk'; SET @todo = (SELECT COUNT(*) = 0 FROM `_migration_ledger` WHERE `step` = @step); SET @t0 = NOW(3); SET @sql = IF(@todo, 'ALTER TABLE `rolegadget` ADD CONSTRAINT `rolegadget_GadgetID` FOREIGN KEY (`GadgetID`) REFERENCES `gadget` (`ID`) ON DELETE RESTRICT ON UPDATE RESTRICT, ADD CONSTRAINT `rolegadget_RoleID` FOREIGN KEY (`RoleID`) REFERENCES `role` (`ID`) ON DELETE RESTRICT ON UPDATE RESTRICT', 'DO 0'); PREPARE _step FROM @sql; EXECUTE _step; DEALLOCATE PREPARE _step; INSERT INTO `_migration_ledger` (`step`, `applied_at`, `checksum`, `duration_ms`) VALUES (@step, NOW(), '78793fd84f6485d977623602295b114b4a0cdc61a4b7a3dcc765f251c4d321ae', TIMESTAMPDIFF(MICROSECOND, @t0, NOW(3)) DIV 1000) ON DUPLICATE KEY UPDATE `step` = `step`; SET FOREIGN_KEY_CHECKS = IFNULL(@fk_prev, 1); -- --------------------------------------------------------------------- -- unit: users (system) -- --------------------------------------------------------------------- SET SQL_MODE = IF(@@SQL_MODE LIKE '%STRICT_TRANS_TABLES%' AND @@SQL_MODE LIKE '%NO_AUTO_VALUE_ON_ZERO%' AND @@SQL_MODE NOT LIKE '%NO_ZERO_DATE%' AND @@SQL_MODE NOT LIKE '%NO_ZERO_IN_DATE%', @@SQL_MODE, CONCAT(REPLACE(REPLACE(@@SQL_MODE, 'NO_ZERO_IN_DATE', ''), 'NO_ZERO_DATE', ''), ',NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES')); -- --------------------------------------------------------------------- -- users/05_rename -- source section 3a -- Renames, kept OUT of 10_alter on purpose: 10_alter adds `PasswordResetToken` -- AFTER `Password`, a name that only exists once this has run. -- MUST run before 10_alter. -- --------------------------------------------------------------------- SET @step = 'users/05_rename'; SET @todo = (SELECT COUNT(*) = 0 FROM `_migration_ledger` WHERE `step` = @step); SET @t0 = NOW(3); SET @sql = IF(@todo, 'ALTER TABLE `users` CHANGE COLUMN `username` `Username` varchar(20) NOT NULL, CHANGE COLUMN `password` `Password` varchar(255) NOT NULL', 'DO 0'); PREPARE _step FROM @sql; EXECUTE _step; DEALLOCATE PREPARE _step; INSERT INTO `_migration_ledger` (`step`, `applied_at`, `checksum`, `duration_ms`) VALUES (@step, NOW(), '55fa2d0cc05aa2c4f9d1be46e3953005ab00c8294174db8fe309cbcb4a09bdf1', TIMESTAMPDIFF(MICROSECOND, @t0, NOW(3)) DIV 1000) ON DUPLICATE KEY UPDATE `step` = `step`; -- --------------------------------------------------------------------- -- users/10_alter -- source section A -- The merged ALTER: new columns, type/charset changes, column order, NULL relaxation -- and the indexes every foreign key needs -- ONE statement, so InnoDB rebuilds this -- table ONCE. The original migration split these across five sections and rebuilt some -- tables four times; that is where its 12 hours went. Do not split this back up. -- -- Clause order is the original section order (3b, 3d, 3e, 4, 7), which is what keeps -- every ADD COLUMN ... AFTER positioned exactly as before. -- --------------------------------------------------------------------- SET @step = 'users/10_alter'; SET @todo = (SELECT COUNT(*) = 0 FROM `_migration_ledger` WHERE `step` = @step); SET @t0 = NOW(3); SET @sql = IF(@todo, 'ALTER TABLE `users` ADD COLUMN `PasswordResetToken` varchar(255) DEFAULT NULL AFTER `Password`, ADD COLUMN `PasswordResetTokenCreatedAt` datetime DEFAULT NULL AFTER `PasswordResetToken`, ADD COLUMN `Otp` int NOT NULL AFTER `IsHeadDept`, ADD COLUMN `OtpExpiration` datetime NOT NULL AFTER `Otp`, MODIFY COLUMN `DepartmentID` int DEFAULT NULL, MODIFY COLUMN `RoleID` int DEFAULT NULL, MODIFY COLUMN `MaritalStatusID` int DEFAULT NULL, MODIFY COLUMN `BranchID` int DEFAULT NULL, MODIFY COLUMN `HeadID` int DEFAULT NULL, ADD INDEX `DepartmentID` (`DepartmentID`,`RoleID`,`MaritalStatusID`,`BranchID`,`HeadID`), ADD INDEX `users_BranchID` (`BranchID`), ADD INDEX `users_HeadID` (`HeadID`), ADD INDEX `users_MaritalStatusID` (`MaritalStatusID`), ADD INDEX `users_RoleID` (`RoleID`)', 'DO 0'); PREPARE _step FROM @sql; EXECUTE _step; DEALLOCATE PREPARE _step; INSERT INTO `_migration_ledger` (`step`, `applied_at`, `checksum`, `duration_ms`) VALUES (@step, NOW(), 'ae21ef0b7e80fd95fd4d0fe2345f039393b70dbfc8bdf1e1c5f0d4d3997aec12', TIMESTAMPDIFF(MICROSECOND, @t0, NOW(3)) DIV 1000) ON DUPLICATE KEY UPDATE `step` = `step`; -- --------------------------------------------------------------------- -- users/20_cleanup -- source section B (= the original sections 5 and 6, merged) -- 0-sentinels AND orphaned references -> NULL, one pass per table. -- section 5: WHERE `c` = 0 -- section 6: WHERE `c` IS NOT NULL AND parent.ID IS NULL -- merged : WHERE `c` IS NOT NULL AND (`c` = 0 OR parent.ID IS NULL) -- Every LEFT JOIN is a primary-key equality, so none of them can multiply rows. -- -- DATA CHANGE, and this script cannot reverse it. Run the audit first. -- In its own transaction, with the ledger row inside it: either the cleanup and the -- record of it both land, or neither does. -- --------------------------------------------------------------------- START TRANSACTION; SET @step = 'users/20_cleanup'; SET @todo = (SELECT COUNT(*) = 0 FROM `_migration_ledger` WHERE `step` = @step); SET @t0 = NOW(3); SET @sql = IF(@todo, 'UPDATE `users` c LEFT JOIN `branch` p0 ON c.`BranchID` = p0.`ID` LEFT JOIN `department` p1 ON c.`DepartmentID` = p1.`ID` LEFT JOIN `users` p2 ON c.`HeadID` = p2.`ID` LEFT JOIN `maritalstatus` p3 ON c.`MaritalStatusID` = p3.`ID` LEFT JOIN `role` p4 ON c.`RoleID` = p4.`ID` SET c.`BranchID` = IF((c.`BranchID` IS NOT NULL AND (c.`BranchID` = 0 OR p0.`ID` IS NULL)), NULL, c.`BranchID`), c.`DepartmentID` = IF((c.`DepartmentID` IS NOT NULL AND (c.`DepartmentID` = 0 OR p1.`ID` IS NULL)), NULL, c.`DepartmentID`), c.`HeadID` = IF((c.`HeadID` IS NOT NULL AND (c.`HeadID` = 0 OR p2.`ID` IS NULL)), NULL, c.`HeadID`), c.`MaritalStatusID` = IF((c.`MaritalStatusID` IS NOT NULL AND (c.`MaritalStatusID` = 0 OR p3.`ID` IS NULL)), NULL, c.`MaritalStatusID`), c.`RoleID` = IF((c.`RoleID` IS NOT NULL AND (c.`RoleID` = 0 OR p4.`ID` IS NULL)), NULL, c.`RoleID`) WHERE (c.`BranchID` IS NOT NULL AND (c.`BranchID` = 0 OR p0.`ID` IS NULL)) OR (c.`DepartmentID` IS NOT NULL AND (c.`DepartmentID` = 0 OR p1.`ID` IS NULL)) OR (c.`HeadID` IS NOT NULL AND (c.`HeadID` = 0 OR p2.`ID` IS NULL)) OR (c.`MaritalStatusID` IS NOT NULL AND (c.`MaritalStatusID` = 0 OR p3.`ID` IS NULL)) OR (c.`RoleID` IS NOT NULL AND (c.`RoleID` = 0 OR p4.`ID` IS NULL))', 'DO 0'); PREPARE _step FROM @sql; EXECUTE _step; DEALLOCATE PREPARE _step; INSERT INTO `_migration_ledger` (`step`, `applied_at`, `checksum`, `duration_ms`) VALUES (@step, NOW(), '7e4a513f1a898a35c7c223038ff924676e118b57e2c788fbbd7be5a3b33e66ae', TIMESTAMPDIFF(MICROSECOND, @t0, NOW(3)) DIV 1000) ON DUPLICATE KEY UPDATE `step` = `step`; COMMIT; -- --------------------------------------------------------------------- -- users/30_assert -- source section 7b -- Orphan assertion over this table's 5 foreign-key relationships. -- -- This is what pays for 40_fk being fast. The original created the constraints with -- FOREIGN_KEY_CHECKS ON so a row the cleanup missed would abort the run, and paid for it -- with a full table copy per constraint. This buys the same protection with one indexed -- pass, and 40_fk then adds the constraints as metadata only. -- -- NOT ledger-guarded on the way in, unlike every other phase: it only reads, and the -- point of it is to be re-runnable. It does record its ledger row -- 40_fk requires it. -- -- IF THIS ABORTS: a row survived 20_cleanup. Same stop-and-look event the original would -- have hit as ERROR 1452 inside section 8, but nothing has been created yet. -- --------------------------------------------------------------------- SET @step = 'users/30_assert'; SET @t0 = NOW(3); SET @orphans = 0; SELECT COUNT(*) INTO @n FROM `users` c LEFT JOIN `branch` p0 ON c.`BranchID` = p0.`ID` LEFT JOIN `department` p1 ON c.`DepartmentID` = p1.`ID` LEFT JOIN `users` p2 ON c.`HeadID` = p2.`ID` LEFT JOIN `maritalstatus` p3 ON c.`MaritalStatusID` = p3.`ID` LEFT JOIN `role` p4 ON c.`RoleID` = p4.`ID` WHERE (c.`BranchID` IS NOT NULL AND p0.`ID` IS NULL) OR (c.`DepartmentID` IS NOT NULL AND p1.`ID` IS NULL) OR (c.`HeadID` IS NOT NULL AND p2.`ID` IS NULL) OR (c.`MaritalStatusID` IS NOT NULL AND p3.`ID` IS NULL) OR (c.`RoleID` IS NOT NULL AND p4.`ID` IS NULL); SET @orphans = @orphans + @n; -- Two-row subquery on the false branch: ERROR 1242, an abort, before 40_fk runs. SELECT IF(@orphans = 0, 'users: orphan check OK - 0 rows across 5 relationships', (SELECT CONCAT('ABORT: ', @orphans, ' rows in `users` still reference a parent that does not exist') UNION ALL SELECT 'Run 20_cleanup for this table first. Do NOT skip this check.') ) AS preflight_orphans; INSERT INTO `_migration_ledger` (`step`, `applied_at`, `checksum`, `duration_ms`) VALUES (@step, NOW(), 'ef741d902041033f76aa8e4c8171f678bc248b6276faf869a330934e6d3fe8ca', TIMESTAMPDIFF(MICROSECOND, @t0, NOW(3)) DIV 1000) ON DUPLICATE KEY UPDATE `step` = `step`; -- --------------------------------------------------------------------- -- users/40_fk -- source section 8 -- The constraints, one ALTER for the whole table. -- -- FOREIGN_KEY_CHECKS is turned OFF for this statement and restored afterwards. With -- checks on InnoDB cannot add a foreign key in place -- it copies the whole table and -- re-validates every row, which is where the original migration's 154 table copies -- came from. The validation is not skipped, it MOVED: 30_assert proves zero orphans -- and refuses to let this run otherwise. -- --------------------------------------------------------------------- SET @fk_prev = @@FOREIGN_KEY_CHECKS; SET FOREIGN_KEY_CHECKS = 0; SET @step = 'users/40_fk'; SET @todo = (SELECT COUNT(*) = 0 FROM `_migration_ledger` WHERE `step` = @step); SET @t0 = NOW(3); SET @sql = IF(@todo, 'ALTER TABLE `users` ADD CONSTRAINT `users_BranchID` FOREIGN KEY (`BranchID`) REFERENCES `branch` (`ID`) ON DELETE RESTRICT ON UPDATE RESTRICT, ADD CONSTRAINT `users_DepartmentID` FOREIGN KEY (`DepartmentID`) REFERENCES `department` (`ID`) ON DELETE RESTRICT ON UPDATE RESTRICT, ADD CONSTRAINT `users_HeadID` FOREIGN KEY (`HeadID`) REFERENCES `users` (`ID`) ON DELETE RESTRICT ON UPDATE RESTRICT, ADD CONSTRAINT `users_MaritalStatusID` FOREIGN KEY (`MaritalStatusID`) REFERENCES `maritalstatus` (`ID`) ON DELETE RESTRICT ON UPDATE RESTRICT, ADD CONSTRAINT `users_RoleID` FOREIGN KEY (`RoleID`) REFERENCES `role` (`ID`) ON DELETE RESTRICT ON UPDATE RESTRICT', 'DO 0'); PREPARE _step FROM @sql; EXECUTE _step; DEALLOCATE PREPARE _step; INSERT INTO `_migration_ledger` (`step`, `applied_at`, `checksum`, `duration_ms`) VALUES (@step, NOW(), '3ba918222a2fbd2d209389d01f18837d7bd539bde81ac161205f12d78ed77fc8', TIMESTAMPDIFF(MICROSECOND, @t0, NOW(3)) DIV 1000) ON DUPLICATE KEY UPDATE `step` = `step`; SET FOREIGN_KEY_CHECKS = IFNULL(@fk_prev, 1); -- --------------------------------------------------------------------- -- unit: userbusinesstravelbranch (detail, owned by users) -- --------------------------------------------------------------------- SET SQL_MODE = IF(@@SQL_MODE LIKE '%STRICT_TRANS_TABLES%' AND @@SQL_MODE LIKE '%NO_AUTO_VALUE_ON_ZERO%' AND @@SQL_MODE NOT LIKE '%NO_ZERO_DATE%' AND @@SQL_MODE NOT LIKE '%NO_ZERO_IN_DATE%', @@SQL_MODE, CONCAT(REPLACE(REPLACE(@@SQL_MODE, 'NO_ZERO_IN_DATE', ''), 'NO_ZERO_DATE', ''), ',NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES')); -- --------------------------------------------------------------------- -- userbusinesstravelbranch/10_alter -- source section A -- The merged ALTER: new columns, type/charset changes, column order, NULL relaxation -- and the indexes every foreign key needs -- ONE statement, so InnoDB rebuilds this -- table ONCE. The original migration split these across five sections and rebuilt some -- tables four times; that is where its 12 hours went. Do not split this back up. -- -- Clause order is the original section order (3b, 3d, 3e, 4, 7), which is what keeps -- every ADD COLUMN ... AFTER positioned exactly as before. -- --------------------------------------------------------------------- SET @step = 'userbusinesstravelbranch/10_alter'; SET @todo = (SELECT COUNT(*) = 0 FROM `_migration_ledger` WHERE `step` = @step); SET @t0 = NOW(3); SET @sql = IF(@todo, 'ALTER TABLE `userbusinesstravelbranch` MODIFY COLUMN `UserID` int DEFAULT NULL, MODIFY COLUMN `BranchID` int DEFAULT NULL, ADD INDEX `userbusinesstravelbranch_BranchID` (`BranchID`), ADD INDEX `userbusinesstravelbranch_UserID` (`UserID`)', 'DO 0'); PREPARE _step FROM @sql; EXECUTE _step; DEALLOCATE PREPARE _step; INSERT INTO `_migration_ledger` (`step`, `applied_at`, `checksum`, `duration_ms`) VALUES (@step, NOW(), '625bd3285c9fcc44dacea8967a9e4a8eb2df6b184d7eb9d453e7a0550bae3660', TIMESTAMPDIFF(MICROSECOND, @t0, NOW(3)) DIV 1000) ON DUPLICATE KEY UPDATE `step` = `step`; -- --------------------------------------------------------------------- -- userbusinesstravelbranch/20_cleanup -- source section B (= the original sections 5 and 6, merged) -- 0-sentinels AND orphaned references -> NULL, one pass per table. -- section 5: WHERE `c` = 0 -- section 6: WHERE `c` IS NOT NULL AND parent.ID IS NULL -- merged : WHERE `c` IS NOT NULL AND (`c` = 0 OR parent.ID IS NULL) -- Every LEFT JOIN is a primary-key equality, so none of them can multiply rows. -- -- DATA CHANGE, and this script cannot reverse it. Run the audit first. -- In its own transaction, with the ledger row inside it: either the cleanup and the -- record of it both land, or neither does. -- --------------------------------------------------------------------- START TRANSACTION; SET @step = 'userbusinesstravelbranch/20_cleanup'; SET @todo = (SELECT COUNT(*) = 0 FROM `_migration_ledger` WHERE `step` = @step); SET @t0 = NOW(3); SET @sql = IF(@todo, 'UPDATE `userbusinesstravelbranch` c LEFT JOIN `branch` p0 ON c.`BranchID` = p0.`ID` LEFT JOIN `users` p1 ON c.`UserID` = p1.`ID` SET c.`BranchID` = IF((c.`BranchID` IS NOT NULL AND (c.`BranchID` = 0 OR p0.`ID` IS NULL)), NULL, c.`BranchID`), c.`UserID` = IF((c.`UserID` IS NOT NULL AND (c.`UserID` = 0 OR p1.`ID` IS NULL)), NULL, c.`UserID`) WHERE (c.`BranchID` IS NOT NULL AND (c.`BranchID` = 0 OR p0.`ID` IS NULL)) OR (c.`UserID` IS NOT NULL AND (c.`UserID` = 0 OR p1.`ID` IS NULL))', 'DO 0'); PREPARE _step FROM @sql; EXECUTE _step; DEALLOCATE PREPARE _step; INSERT INTO `_migration_ledger` (`step`, `applied_at`, `checksum`, `duration_ms`) VALUES (@step, NOW(), 'c9f1a8fef1c213a7ea0b3182100699379e5b6424c1de9913856f78d3b862dea0', TIMESTAMPDIFF(MICROSECOND, @t0, NOW(3)) DIV 1000) ON DUPLICATE KEY UPDATE `step` = `step`; COMMIT; -- --------------------------------------------------------------------- -- userbusinesstravelbranch/30_assert -- source section 7b -- Orphan assertion over this table's 2 foreign-key relationships. -- -- This is what pays for 40_fk being fast. The original created the constraints with -- FOREIGN_KEY_CHECKS ON so a row the cleanup missed would abort the run, and paid for it -- with a full table copy per constraint. This buys the same protection with one indexed -- pass, and 40_fk then adds the constraints as metadata only. -- -- NOT ledger-guarded on the way in, unlike every other phase: it only reads, and the -- point of it is to be re-runnable. It does record its ledger row -- 40_fk requires it. -- -- IF THIS ABORTS: a row survived 20_cleanup. Same stop-and-look event the original would -- have hit as ERROR 1452 inside section 8, but nothing has been created yet. -- --------------------------------------------------------------------- SET @step = 'userbusinesstravelbranch/30_assert'; SET @t0 = NOW(3); SET @orphans = 0; SELECT COUNT(*) INTO @n FROM `userbusinesstravelbranch` c LEFT JOIN `branch` p0 ON c.`BranchID` = p0.`ID` LEFT JOIN `users` p1 ON c.`UserID` = p1.`ID` WHERE (c.`BranchID` IS NOT NULL AND p0.`ID` IS NULL) OR (c.`UserID` IS NOT NULL AND p1.`ID` IS NULL); SET @orphans = @orphans + @n; -- Two-row subquery on the false branch: ERROR 1242, an abort, before 40_fk runs. SELECT IF(@orphans = 0, 'userbusinesstravelbranch: orphan check OK - 0 rows across 2 relationships', (SELECT CONCAT('ABORT: ', @orphans, ' rows in `userbusinesstravelbranch` still reference a parent that does not exist') UNION ALL SELECT 'Run 20_cleanup for this table first. Do NOT skip this check.') ) AS preflight_orphans; INSERT INTO `_migration_ledger` (`step`, `applied_at`, `checksum`, `duration_ms`) VALUES (@step, NOW(), '2e785f67e942f2d0678573f278d2dd6bf1b8f2c3a2687dd5f0c406cae258d646', TIMESTAMPDIFF(MICROSECOND, @t0, NOW(3)) DIV 1000) ON DUPLICATE KEY UPDATE `step` = `step`; -- --------------------------------------------------------------------- -- userbusinesstravelbranch/40_fk -- source section 8 -- The constraints, one ALTER for the whole table. -- -- FOREIGN_KEY_CHECKS is turned OFF for this statement and restored afterwards. With -- checks on InnoDB cannot add a foreign key in place -- it copies the whole table and -- re-validates every row, which is where the original migration's 154 table copies -- came from. The validation is not skipped, it MOVED: 30_assert proves zero orphans -- and refuses to let this run otherwise. -- --------------------------------------------------------------------- SET @fk_prev = @@FOREIGN_KEY_CHECKS; SET FOREIGN_KEY_CHECKS = 0; SET @step = 'userbusinesstravelbranch/40_fk'; SET @todo = (SELECT COUNT(*) = 0 FROM `_migration_ledger` WHERE `step` = @step); SET @t0 = NOW(3); SET @sql = IF(@todo, 'ALTER TABLE `userbusinesstravelbranch` ADD CONSTRAINT `userbusinesstravelbranch_BranchID` FOREIGN KEY (`BranchID`) REFERENCES `branch` (`ID`) ON DELETE RESTRICT ON UPDATE RESTRICT, ADD CONSTRAINT `userbusinesstravelbranch_UserID` FOREIGN KEY (`UserID`) REFERENCES `users` (`ID`) ON DELETE RESTRICT ON UPDATE RESTRICT', 'DO 0'); PREPARE _step FROM @sql; EXECUTE _step; DEALLOCATE PREPARE _step; INSERT INTO `_migration_ledger` (`step`, `applied_at`, `checksum`, `duration_ms`) VALUES (@step, NOW(), 'ef9e639d3faa46d3e496821fc791e37b08d8c69450c03acf365083cd5bf088d5', TIMESTAMPDIFF(MICROSECOND, @t0, NOW(3)) DIV 1000) ON DUPLICATE KEY UPDATE `step` = `step`; SET FOREIGN_KEY_CHECKS = IFNULL(@fk_prev, 1); -- --------------------------------------------------------------------- -- unit: userdetails (detail, owned by users) -- --------------------------------------------------------------------- SET SQL_MODE = IF(@@SQL_MODE LIKE '%STRICT_TRANS_TABLES%' AND @@SQL_MODE LIKE '%NO_AUTO_VALUE_ON_ZERO%' AND @@SQL_MODE NOT LIKE '%NO_ZERO_DATE%' AND @@SQL_MODE NOT LIKE '%NO_ZERO_IN_DATE%', @@SQL_MODE, CONCAT(REPLACE(REPLACE(@@SQL_MODE, 'NO_ZERO_IN_DATE', ''), 'NO_ZERO_DATE', ''), ',NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES')); -- --------------------------------------------------------------------- -- userdetails/10_alter -- source section A -- The merged ALTER: new columns, type/charset changes, column order, NULL relaxation -- and the indexes every foreign key needs -- ONE statement, so InnoDB rebuilds this -- table ONCE. The original migration split these across five sections and rebuilt some -- tables four times; that is where its 12 hours went. Do not split this back up. -- -- Clause order is the original section order (3b, 3d, 3e, 4, 7), which is what keeps -- every ADD COLUMN ... AFTER positioned exactly as before. -- --------------------------------------------------------------------- SET @step = 'userdetails/10_alter'; SET @todo = (SELECT COUNT(*) = 0 FROM `_migration_ledger` WHERE `step` = @step); SET @t0 = NOW(3); SET @sql = IF(@todo, 'ALTER TABLE `userdetails` MODIFY COLUMN `BranchID` int DEFAULT NULL, MODIFY COLUMN `DepartmentID` int DEFAULT NULL, MODIFY COLUMN `DivisionID` int DEFAULT NULL, ADD INDEX `BranchID` (`BranchID`,`DepartmentID`,`DivisionID`,`MaritalStatus`), ADD INDEX `userdetails_DepartmentID` (`DepartmentID`), ADD INDEX `userdetails_DivisionID` (`DivisionID`)', 'DO 0'); PREPARE _step FROM @sql; EXECUTE _step; DEALLOCATE PREPARE _step; INSERT INTO `_migration_ledger` (`step`, `applied_at`, `checksum`, `duration_ms`) VALUES (@step, NOW(), 'a7e33bbd590b803d492bfa0411a6fc0eec5ce5c72859e7b428c2b4de657eee1a', TIMESTAMPDIFF(MICROSECOND, @t0, NOW(3)) DIV 1000) ON DUPLICATE KEY UPDATE `step` = `step`; -- --------------------------------------------------------------------- -- userdetails/20_cleanup -- source section B (= the original sections 5 and 6, merged) -- 0-sentinels AND orphaned references -> NULL, one pass per table. -- section 5: WHERE `c` = 0 -- section 6: WHERE `c` IS NOT NULL AND parent.ID IS NULL -- merged : WHERE `c` IS NOT NULL AND (`c` = 0 OR parent.ID IS NULL) -- Every LEFT JOIN is a primary-key equality, so none of them can multiply rows. -- -- DATA CHANGE, and this script cannot reverse it. Run the audit first. -- In its own transaction, with the ledger row inside it: either the cleanup and the -- record of it both land, or neither does. -- --------------------------------------------------------------------- START TRANSACTION; SET @step = 'userdetails/20_cleanup'; SET @todo = (SELECT COUNT(*) = 0 FROM `_migration_ledger` WHERE `step` = @step); SET @t0 = NOW(3); SET @sql = IF(@todo, 'UPDATE `userdetails` c LEFT JOIN `branch` p0 ON c.`BranchID` = p0.`ID` LEFT JOIN `department` p1 ON c.`DepartmentID` = p1.`ID` LEFT JOIN `division` p2 ON c.`DivisionID` = p2.`ID` SET c.`BranchID` = IF((c.`BranchID` IS NOT NULL AND (c.`BranchID` = 0 OR p0.`ID` IS NULL)), NULL, c.`BranchID`), c.`DepartmentID` = IF((c.`DepartmentID` IS NOT NULL AND (c.`DepartmentID` = 0 OR p1.`ID` IS NULL)), NULL, c.`DepartmentID`), c.`DivisionID` = IF((c.`DivisionID` IS NOT NULL AND (c.`DivisionID` = 0 OR p2.`ID` IS NULL)), NULL, c.`DivisionID`) WHERE (c.`BranchID` IS NOT NULL AND (c.`BranchID` = 0 OR p0.`ID` IS NULL)) OR (c.`DepartmentID` IS NOT NULL AND (c.`DepartmentID` = 0 OR p1.`ID` IS NULL)) OR (c.`DivisionID` IS NOT NULL AND (c.`DivisionID` = 0 OR p2.`ID` IS NULL))', 'DO 0'); PREPARE _step FROM @sql; EXECUTE _step; DEALLOCATE PREPARE _step; INSERT INTO `_migration_ledger` (`step`, `applied_at`, `checksum`, `duration_ms`) VALUES (@step, NOW(), 'a48e0529700541043a07df4b9a4469c0536a46a1b8d191e539b8a3ee15e5cfa8', TIMESTAMPDIFF(MICROSECOND, @t0, NOW(3)) DIV 1000) ON DUPLICATE KEY UPDATE `step` = `step`; COMMIT; -- --------------------------------------------------------------------- -- userdetails/30_assert -- source section 7b -- Orphan assertion over this table's 3 foreign-key relationships. -- -- This is what pays for 40_fk being fast. The original created the constraints with -- FOREIGN_KEY_CHECKS ON so a row the cleanup missed would abort the run, and paid for it -- with a full table copy per constraint. This buys the same protection with one indexed -- pass, and 40_fk then adds the constraints as metadata only. -- -- NOT ledger-guarded on the way in, unlike every other phase: it only reads, and the -- point of it is to be re-runnable. It does record its ledger row -- 40_fk requires it. -- -- IF THIS ABORTS: a row survived 20_cleanup. Same stop-and-look event the original would -- have hit as ERROR 1452 inside section 8, but nothing has been created yet. -- --------------------------------------------------------------------- SET @step = 'userdetails/30_assert'; SET @t0 = NOW(3); SET @orphans = 0; SELECT COUNT(*) INTO @n FROM `userdetails` c LEFT JOIN `branch` p0 ON c.`BranchID` = p0.`ID` LEFT JOIN `department` p1 ON c.`DepartmentID` = p1.`ID` LEFT JOIN `division` p2 ON c.`DivisionID` = p2.`ID` WHERE (c.`BranchID` IS NOT NULL AND p0.`ID` IS NULL) OR (c.`DepartmentID` IS NOT NULL AND p1.`ID` IS NULL) OR (c.`DivisionID` IS NOT NULL AND p2.`ID` IS NULL); SET @orphans = @orphans + @n; -- Two-row subquery on the false branch: ERROR 1242, an abort, before 40_fk runs. SELECT IF(@orphans = 0, 'userdetails: orphan check OK - 0 rows across 3 relationships', (SELECT CONCAT('ABORT: ', @orphans, ' rows in `userdetails` still reference a parent that does not exist') UNION ALL SELECT 'Run 20_cleanup for this table first. Do NOT skip this check.') ) AS preflight_orphans; INSERT INTO `_migration_ledger` (`step`, `applied_at`, `checksum`, `duration_ms`) VALUES (@step, NOW(), 'ce5a299a78a69994211ee99f5afbc92b6561b14ab6e8ae137125a0bb699ec5cf', TIMESTAMPDIFF(MICROSECOND, @t0, NOW(3)) DIV 1000) ON DUPLICATE KEY UPDATE `step` = `step`; -- --------------------------------------------------------------------- -- userdetails/40_fk -- source section 8 -- The constraints, one ALTER for the whole table. -- -- FOREIGN_KEY_CHECKS is turned OFF for this statement and restored afterwards. With -- checks on InnoDB cannot add a foreign key in place -- it copies the whole table and -- re-validates every row, which is where the original migration's 154 table copies -- came from. The validation is not skipped, it MOVED: 30_assert proves zero orphans -- and refuses to let this run otherwise. -- --------------------------------------------------------------------- SET @fk_prev = @@FOREIGN_KEY_CHECKS; SET FOREIGN_KEY_CHECKS = 0; SET @step = 'userdetails/40_fk'; SET @todo = (SELECT COUNT(*) = 0 FROM `_migration_ledger` WHERE `step` = @step); SET @t0 = NOW(3); SET @sql = IF(@todo, 'ALTER TABLE `userdetails` ADD CONSTRAINT `userdetails_BranchID` FOREIGN KEY (`BranchID`) REFERENCES `branch` (`ID`) ON DELETE RESTRICT ON UPDATE RESTRICT, ADD CONSTRAINT `userdetails_DepartmentID` FOREIGN KEY (`DepartmentID`) REFERENCES `department` (`ID`) ON DELETE RESTRICT ON UPDATE RESTRICT, ADD CONSTRAINT `userdetails_DivisionID` FOREIGN KEY (`DivisionID`) REFERENCES `division` (`ID`) ON DELETE RESTRICT ON UPDATE RESTRICT', 'DO 0'); PREPARE _step FROM @sql; EXECUTE _step; DEALLOCATE PREPARE _step; INSERT INTO `_migration_ledger` (`step`, `applied_at`, `checksum`, `duration_ms`) VALUES (@step, NOW(), 'c5a4bbbdab1a523821dbe3ff22c5c359ddf9707c1399abc4f1a6a884519168b6', TIMESTAMPDIFF(MICROSECOND, @t0, NOW(3)) DIV 1000) ON DUPLICATE KEY UPDATE `step` = `step`; SET FOREIGN_KEY_CHECKS = IFNULL(@fk_prev, 1); -- --------------------------------------------------------------------- -- unit: userdivision (detail, owned by users) -- --------------------------------------------------------------------- SET SQL_MODE = IF(@@SQL_MODE LIKE '%STRICT_TRANS_TABLES%' AND @@SQL_MODE LIKE '%NO_AUTO_VALUE_ON_ZERO%' AND @@SQL_MODE NOT LIKE '%NO_ZERO_DATE%' AND @@SQL_MODE NOT LIKE '%NO_ZERO_IN_DATE%', @@SQL_MODE, CONCAT(REPLACE(REPLACE(@@SQL_MODE, 'NO_ZERO_IN_DATE', ''), 'NO_ZERO_DATE', ''), ',NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES')); -- --------------------------------------------------------------------- -- userdivision/10_alter -- source section A -- The merged ALTER: new columns, type/charset changes, column order, NULL relaxation -- and the indexes every foreign key needs -- ONE statement, so InnoDB rebuilds this -- table ONCE. The original migration split these across five sections and rebuilt some -- tables four times; that is where its 12 hours went. Do not split this back up. -- -- Clause order is the original section order (3b, 3d, 3e, 4, 7), which is what keeps -- every ADD COLUMN ... AFTER positioned exactly as before. -- --------------------------------------------------------------------- SET @step = 'userdivision/10_alter'; SET @todo = (SELECT COUNT(*) = 0 FROM `_migration_ledger` WHERE `step` = @step); SET @t0 = NOW(3); SET @sql = IF(@todo, 'ALTER TABLE `userdivision` MODIFY COLUMN `UserID` int DEFAULT NULL, MODIFY COLUMN `DivisionID` int DEFAULT NULL, ADD INDEX `userdivision_DivisionID` (`DivisionID`), ADD INDEX `UserID` (`UserID`,`DivisionID`)', 'DO 0'); PREPARE _step FROM @sql; EXECUTE _step; DEALLOCATE PREPARE _step; INSERT INTO `_migration_ledger` (`step`, `applied_at`, `checksum`, `duration_ms`) VALUES (@step, NOW(), 'e883267e4864b601be959c928c7321ec85b74d8794f6f850e9ab659b656f6321', TIMESTAMPDIFF(MICROSECOND, @t0, NOW(3)) DIV 1000) ON DUPLICATE KEY UPDATE `step` = `step`; -- --------------------------------------------------------------------- -- userdivision/20_cleanup -- source section B (= the original sections 5 and 6, merged) -- 0-sentinels AND orphaned references -> NULL, one pass per table. -- section 5: WHERE `c` = 0 -- section 6: WHERE `c` IS NOT NULL AND parent.ID IS NULL -- merged : WHERE `c` IS NOT NULL AND (`c` = 0 OR parent.ID IS NULL) -- Every LEFT JOIN is a primary-key equality, so none of them can multiply rows. -- -- DATA CHANGE, and this script cannot reverse it. Run the audit first. -- In its own transaction, with the ledger row inside it: either the cleanup and the -- record of it both land, or neither does. -- --------------------------------------------------------------------- START TRANSACTION; SET @step = 'userdivision/20_cleanup'; SET @todo = (SELECT COUNT(*) = 0 FROM `_migration_ledger` WHERE `step` = @step); SET @t0 = NOW(3); SET @sql = IF(@todo, 'UPDATE `userdivision` c LEFT JOIN `division` p0 ON c.`DivisionID` = p0.`ID` LEFT JOIN `users` p1 ON c.`UserID` = p1.`ID` SET c.`DivisionID` = IF((c.`DivisionID` IS NOT NULL AND (c.`DivisionID` = 0 OR p0.`ID` IS NULL)), NULL, c.`DivisionID`), c.`UserID` = IF((c.`UserID` IS NOT NULL AND (c.`UserID` = 0 OR p1.`ID` IS NULL)), NULL, c.`UserID`) WHERE (c.`DivisionID` IS NOT NULL AND (c.`DivisionID` = 0 OR p0.`ID` IS NULL)) OR (c.`UserID` IS NOT NULL AND (c.`UserID` = 0 OR p1.`ID` IS NULL))', 'DO 0'); PREPARE _step FROM @sql; EXECUTE _step; DEALLOCATE PREPARE _step; INSERT INTO `_migration_ledger` (`step`, `applied_at`, `checksum`, `duration_ms`) VALUES (@step, NOW(), 'c36debe0dca4ab42fd16715191c024165cf12648e5a09335b487e778c3fb83da', TIMESTAMPDIFF(MICROSECOND, @t0, NOW(3)) DIV 1000) ON DUPLICATE KEY UPDATE `step` = `step`; COMMIT; -- --------------------------------------------------------------------- -- userdivision/30_assert -- source section 7b -- Orphan assertion over this table's 2 foreign-key relationships. -- -- This is what pays for 40_fk being fast. The original created the constraints with -- FOREIGN_KEY_CHECKS ON so a row the cleanup missed would abort the run, and paid for it -- with a full table copy per constraint. This buys the same protection with one indexed -- pass, and 40_fk then adds the constraints as metadata only. -- -- NOT ledger-guarded on the way in, unlike every other phase: it only reads, and the -- point of it is to be re-runnable. It does record its ledger row -- 40_fk requires it. -- -- IF THIS ABORTS: a row survived 20_cleanup. Same stop-and-look event the original would -- have hit as ERROR 1452 inside section 8, but nothing has been created yet. -- --------------------------------------------------------------------- SET @step = 'userdivision/30_assert'; SET @t0 = NOW(3); SET @orphans = 0; SELECT COUNT(*) INTO @n FROM `userdivision` c LEFT JOIN `division` p0 ON c.`DivisionID` = p0.`ID` LEFT JOIN `users` p1 ON c.`UserID` = p1.`ID` WHERE (c.`DivisionID` IS NOT NULL AND p0.`ID` IS NULL) OR (c.`UserID` IS NOT NULL AND p1.`ID` IS NULL); SET @orphans = @orphans + @n; -- Two-row subquery on the false branch: ERROR 1242, an abort, before 40_fk runs. SELECT IF(@orphans = 0, 'userdivision: orphan check OK - 0 rows across 2 relationships', (SELECT CONCAT('ABORT: ', @orphans, ' rows in `userdivision` still reference a parent that does not exist') UNION ALL SELECT 'Run 20_cleanup for this table first. Do NOT skip this check.') ) AS preflight_orphans; INSERT INTO `_migration_ledger` (`step`, `applied_at`, `checksum`, `duration_ms`) VALUES (@step, NOW(), 'bfa486c01ee7d9c2b4e14ca52176a017a3bdecb86b032a502acd8e2725826adc', TIMESTAMPDIFF(MICROSECOND, @t0, NOW(3)) DIV 1000) ON DUPLICATE KEY UPDATE `step` = `step`; -- --------------------------------------------------------------------- -- userdivision/40_fk -- source section 8 -- The constraints, one ALTER for the whole table. -- -- FOREIGN_KEY_CHECKS is turned OFF for this statement and restored afterwards. With -- checks on InnoDB cannot add a foreign key in place -- it copies the whole table and -- re-validates every row, which is where the original migration's 154 table copies -- came from. The validation is not skipped, it MOVED: 30_assert proves zero orphans -- and refuses to let this run otherwise. -- --------------------------------------------------------------------- SET @fk_prev = @@FOREIGN_KEY_CHECKS; SET FOREIGN_KEY_CHECKS = 0; SET @step = 'userdivision/40_fk'; SET @todo = (SELECT COUNT(*) = 0 FROM `_migration_ledger` WHERE `step` = @step); SET @t0 = NOW(3); SET @sql = IF(@todo, 'ALTER TABLE `userdivision` ADD CONSTRAINT `userdivision_DivisionID` FOREIGN KEY (`DivisionID`) REFERENCES `division` (`ID`) ON DELETE RESTRICT ON UPDATE RESTRICT, ADD CONSTRAINT `userdivision_UserID` FOREIGN KEY (`UserID`) REFERENCES `users` (`ID`) ON DELETE RESTRICT ON UPDATE RESTRICT', 'DO 0'); PREPARE _step FROM @sql; EXECUTE _step; DEALLOCATE PREPARE _step; INSERT INTO `_migration_ledger` (`step`, `applied_at`, `checksum`, `duration_ms`) VALUES (@step, NOW(), '9a41ab32f63fae5c014b5e156d6423e693fff40cbd11e3f69571e1239e96345e', TIMESTAMPDIFF(MICROSECOND, @t0, NOW(3)) DIV 1000) ON DUPLICATE KEY UPDATE `step` = `step`; SET FOREIGN_KEY_CHECKS = IFNULL(@fk_prev, 1); -- --------------------------------------------------------------------- -- unit: usereducation (detail, owned by users) -- --------------------------------------------------------------------- SET SQL_MODE = IF(@@SQL_MODE LIKE '%STRICT_TRANS_TABLES%' AND @@SQL_MODE LIKE '%NO_AUTO_VALUE_ON_ZERO%' AND @@SQL_MODE NOT LIKE '%NO_ZERO_DATE%' AND @@SQL_MODE NOT LIKE '%NO_ZERO_IN_DATE%', @@SQL_MODE, CONCAT(REPLACE(REPLACE(@@SQL_MODE, 'NO_ZERO_IN_DATE', ''), 'NO_ZERO_DATE', ''), ',NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES')); -- --------------------------------------------------------------------- -- usereducation/10_alter -- source section A -- The merged ALTER: new columns, type/charset changes, column order, NULL relaxation -- and the indexes every foreign key needs -- ONE statement, so InnoDB rebuilds this -- table ONCE. The original migration split these across five sections and rebuilt some -- tables four times; that is where its 12 hours went. Do not split this back up. -- -- Clause order is the original section order (3b, 3d, 3e, 4, 7), which is what keeps -- every ADD COLUMN ... AFTER positioned exactly as before. -- --------------------------------------------------------------------- SET @step = 'usereducation/10_alter'; SET @todo = (SELECT COUNT(*) = 0 FROM `_migration_ledger` WHERE `step` = @step); SET @t0 = NOW(3); SET @sql = IF(@todo, 'ALTER TABLE `usereducation` MODIFY COLUMN `UserID` int DEFAULT NULL, MODIFY COLUMN `EducationID` int DEFAULT NULL, ADD INDEX `usereducation_EducationID` (`EducationID`), ADD INDEX `UserID` (`UserID`,`EducationID`)', 'DO 0'); PREPARE _step FROM @sql; EXECUTE _step; DEALLOCATE PREPARE _step; INSERT INTO `_migration_ledger` (`step`, `applied_at`, `checksum`, `duration_ms`) VALUES (@step, NOW(), '91b770ddb4ab3c2e2d1f140045699855728c6192caa390a3b123788e9b98cb4b', TIMESTAMPDIFF(MICROSECOND, @t0, NOW(3)) DIV 1000) ON DUPLICATE KEY UPDATE `step` = `step`; -- --------------------------------------------------------------------- -- usereducation/20_cleanup -- source section B (= the original sections 5 and 6, merged) -- 0-sentinels AND orphaned references -> NULL, one pass per table. -- section 5: WHERE `c` = 0 -- section 6: WHERE `c` IS NOT NULL AND parent.ID IS NULL -- merged : WHERE `c` IS NOT NULL AND (`c` = 0 OR parent.ID IS NULL) -- Every LEFT JOIN is a primary-key equality, so none of them can multiply rows. -- -- DATA CHANGE, and this script cannot reverse it. Run the audit first. -- In its own transaction, with the ledger row inside it: either the cleanup and the -- record of it both land, or neither does. -- --------------------------------------------------------------------- START TRANSACTION; SET @step = 'usereducation/20_cleanup'; SET @todo = (SELECT COUNT(*) = 0 FROM `_migration_ledger` WHERE `step` = @step); SET @t0 = NOW(3); SET @sql = IF(@todo, 'UPDATE `usereducation` c LEFT JOIN `education` p0 ON c.`EducationID` = p0.`ID` LEFT JOIN `users` p1 ON c.`UserID` = p1.`ID` SET c.`EducationID` = IF((c.`EducationID` IS NOT NULL AND (c.`EducationID` = 0 OR p0.`ID` IS NULL)), NULL, c.`EducationID`), c.`UserID` = IF((c.`UserID` IS NOT NULL AND (c.`UserID` = 0 OR p1.`ID` IS NULL)), NULL, c.`UserID`) WHERE (c.`EducationID` IS NOT NULL AND (c.`EducationID` = 0 OR p0.`ID` IS NULL)) OR (c.`UserID` IS NOT NULL AND (c.`UserID` = 0 OR p1.`ID` IS NULL))', 'DO 0'); PREPARE _step FROM @sql; EXECUTE _step; DEALLOCATE PREPARE _step; INSERT INTO `_migration_ledger` (`step`, `applied_at`, `checksum`, `duration_ms`) VALUES (@step, NOW(), '0bdb3ddf56734d2c8b02db962f840aa528018a203a0d7660542cbb3805a44178', TIMESTAMPDIFF(MICROSECOND, @t0, NOW(3)) DIV 1000) ON DUPLICATE KEY UPDATE `step` = `step`; COMMIT; -- --------------------------------------------------------------------- -- usereducation/30_assert -- source section 7b -- Orphan assertion over this table's 2 foreign-key relationships. -- -- This is what pays for 40_fk being fast. The original created the constraints with -- FOREIGN_KEY_CHECKS ON so a row the cleanup missed would abort the run, and paid for it -- with a full table copy per constraint. This buys the same protection with one indexed -- pass, and 40_fk then adds the constraints as metadata only. -- -- NOT ledger-guarded on the way in, unlike every other phase: it only reads, and the -- point of it is to be re-runnable. It does record its ledger row -- 40_fk requires it. -- -- IF THIS ABORTS: a row survived 20_cleanup. Same stop-and-look event the original would -- have hit as ERROR 1452 inside section 8, but nothing has been created yet. -- --------------------------------------------------------------------- SET @step = 'usereducation/30_assert'; SET @t0 = NOW(3); SET @orphans = 0; SELECT COUNT(*) INTO @n FROM `usereducation` c LEFT JOIN `education` p0 ON c.`EducationID` = p0.`ID` LEFT JOIN `users` p1 ON c.`UserID` = p1.`ID` WHERE (c.`EducationID` IS NOT NULL AND p0.`ID` IS NULL) OR (c.`UserID` IS NOT NULL AND p1.`ID` IS NULL); SET @orphans = @orphans + @n; -- Two-row subquery on the false branch: ERROR 1242, an abort, before 40_fk runs. SELECT IF(@orphans = 0, 'usereducation: orphan check OK - 0 rows across 2 relationships', (SELECT CONCAT('ABORT: ', @orphans, ' rows in `usereducation` still reference a parent that does not exist') UNION ALL SELECT 'Run 20_cleanup for this table first. Do NOT skip this check.') ) AS preflight_orphans; INSERT INTO `_migration_ledger` (`step`, `applied_at`, `checksum`, `duration_ms`) VALUES (@step, NOW(), '474bf683c4b40104c7db2110c2687b84cc79f7299070b9bf614586b434985ab3', TIMESTAMPDIFF(MICROSECOND, @t0, NOW(3)) DIV 1000) ON DUPLICATE KEY UPDATE `step` = `step`; -- --------------------------------------------------------------------- -- usereducation/40_fk -- source section 8 -- The constraints, one ALTER for the whole table. -- -- FOREIGN_KEY_CHECKS is turned OFF for this statement and restored afterwards. With -- checks on InnoDB cannot add a foreign key in place -- it copies the whole table and -- re-validates every row, which is where the original migration's 154 table copies -- came from. The validation is not skipped, it MOVED: 30_assert proves zero orphans -- and refuses to let this run otherwise. -- --------------------------------------------------------------------- SET @fk_prev = @@FOREIGN_KEY_CHECKS; SET FOREIGN_KEY_CHECKS = 0; SET @step = 'usereducation/40_fk'; SET @todo = (SELECT COUNT(*) = 0 FROM `_migration_ledger` WHERE `step` = @step); SET @t0 = NOW(3); SET @sql = IF(@todo, 'ALTER TABLE `usereducation` ADD CONSTRAINT `usereducation_EducationID` FOREIGN KEY (`EducationID`) REFERENCES `education` (`ID`) ON DELETE RESTRICT ON UPDATE RESTRICT, ADD CONSTRAINT `usereducation_UserID` FOREIGN KEY (`UserID`) REFERENCES `users` (`ID`) ON DELETE RESTRICT ON UPDATE RESTRICT', 'DO 0'); PREPARE _step FROM @sql; EXECUTE _step; DEALLOCATE PREPARE _step; INSERT INTO `_migration_ledger` (`step`, `applied_at`, `checksum`, `duration_ms`) VALUES (@step, NOW(), '519dfaf3e50e908b91977086e6ac2cf39c09145e5e38684270c34d1866c9e0d5', TIMESTAMPDIFF(MICROSECOND, @t0, NOW(3)) DIV 1000) ON DUPLICATE KEY UPDATE `step` = `step`; SET FOREIGN_KEY_CHECKS = IFNULL(@fk_prev, 1); -- --------------------------------------------------------------------- -- unit: userlabgroupdivision (detail, owned by users) -- --------------------------------------------------------------------- SET SQL_MODE = IF(@@SQL_MODE LIKE '%STRICT_TRANS_TABLES%' AND @@SQL_MODE LIKE '%NO_AUTO_VALUE_ON_ZERO%' AND @@SQL_MODE NOT LIKE '%NO_ZERO_DATE%' AND @@SQL_MODE NOT LIKE '%NO_ZERO_IN_DATE%', @@SQL_MODE, CONCAT(REPLACE(REPLACE(@@SQL_MODE, 'NO_ZERO_IN_DATE', ''), 'NO_ZERO_DATE', ''), ',NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES')); -- --------------------------------------------------------------------- -- userlabgroupdivision/10_alter -- source section A -- The merged ALTER: new columns, type/charset changes, column order, NULL relaxation -- and the indexes every foreign key needs -- ONE statement, so InnoDB rebuilds this -- table ONCE. The original migration split these across five sections and rebuilt some -- tables four times; that is where its 12 hours went. Do not split this back up. -- -- Clause order is the original section order (3b, 3d, 3e, 4, 7), which is what keeps -- every ADD COLUMN ... AFTER positioned exactly as before. -- --------------------------------------------------------------------- SET @step = 'userlabgroupdivision/10_alter'; SET @todo = (SELECT COUNT(*) = 0 FROM `_migration_ledger` WHERE `step` = @step); SET @t0 = NOW(3); SET @sql = IF(@todo, 'ALTER TABLE `userlabgroupdivision` MODIFY COLUMN `UserID` int DEFAULT NULL, MODIFY COLUMN `GroupDivisionID` int DEFAULT NULL, ADD INDEX `userlabgroupdivision_GroupDivisionID` (`GroupDivisionID`), ADD INDEX `userlabgroupdivision_UserID` (`UserID`)', 'DO 0'); PREPARE _step FROM @sql; EXECUTE _step; DEALLOCATE PREPARE _step; INSERT INTO `_migration_ledger` (`step`, `applied_at`, `checksum`, `duration_ms`) VALUES (@step, NOW(), '998f48206dd28e28ac8adbfac81662ea9f13bb1d7c07cb9ecc4d1f50e37346ea', TIMESTAMPDIFF(MICROSECOND, @t0, NOW(3)) DIV 1000) ON DUPLICATE KEY UPDATE `step` = `step`; -- --------------------------------------------------------------------- -- userlabgroupdivision/20_cleanup -- source section B (= the original sections 5 and 6, merged) -- 0-sentinels AND orphaned references -> NULL, one pass per table. -- section 5: WHERE `c` = 0 -- section 6: WHERE `c` IS NOT NULL AND parent.ID IS NULL -- merged : WHERE `c` IS NOT NULL AND (`c` = 0 OR parent.ID IS NULL) -- Every LEFT JOIN is a primary-key equality, so none of them can multiply rows. -- -- DATA CHANGE, and this script cannot reverse it. Run the audit first. -- In its own transaction, with the ledger row inside it: either the cleanup and the -- record of it both land, or neither does. -- --------------------------------------------------------------------- START TRANSACTION; SET @step = 'userlabgroupdivision/20_cleanup'; SET @todo = (SELECT COUNT(*) = 0 FROM `_migration_ledger` WHERE `step` = @step); SET @t0 = NOW(3); SET @sql = IF(@todo, 'UPDATE `userlabgroupdivision` c LEFT JOIN `groupdivision` p0 ON c.`GroupDivisionID` = p0.`ID` LEFT JOIN `users` p1 ON c.`UserID` = p1.`ID` SET c.`GroupDivisionID` = IF((c.`GroupDivisionID` IS NOT NULL AND (c.`GroupDivisionID` = 0 OR p0.`ID` IS NULL)), NULL, c.`GroupDivisionID`), c.`UserID` = IF((c.`UserID` IS NOT NULL AND (c.`UserID` = 0 OR p1.`ID` IS NULL)), NULL, c.`UserID`) WHERE (c.`GroupDivisionID` IS NOT NULL AND (c.`GroupDivisionID` = 0 OR p0.`ID` IS NULL)) OR (c.`UserID` IS NOT NULL AND (c.`UserID` = 0 OR p1.`ID` IS NULL))', 'DO 0'); PREPARE _step FROM @sql; EXECUTE _step; DEALLOCATE PREPARE _step; INSERT INTO `_migration_ledger` (`step`, `applied_at`, `checksum`, `duration_ms`) VALUES (@step, NOW(), '5940e6f5eb14f42063bc84aa7647388b1527856806d3873fd57d08f0517761cd', TIMESTAMPDIFF(MICROSECOND, @t0, NOW(3)) DIV 1000) ON DUPLICATE KEY UPDATE `step` = `step`; COMMIT; -- --------------------------------------------------------------------- -- userlabgroupdivision/30_assert -- source section 7b -- Orphan assertion over this table's 2 foreign-key relationships. -- -- This is what pays for 40_fk being fast. The original created the constraints with -- FOREIGN_KEY_CHECKS ON so a row the cleanup missed would abort the run, and paid for it -- with a full table copy per constraint. This buys the same protection with one indexed -- pass, and 40_fk then adds the constraints as metadata only. -- -- NOT ledger-guarded on the way in, unlike every other phase: it only reads, and the -- point of it is to be re-runnable. It does record its ledger row -- 40_fk requires it. -- -- IF THIS ABORTS: a row survived 20_cleanup. Same stop-and-look event the original would -- have hit as ERROR 1452 inside section 8, but nothing has been created yet. -- --------------------------------------------------------------------- SET @step = 'userlabgroupdivision/30_assert'; SET @t0 = NOW(3); SET @orphans = 0; SELECT COUNT(*) INTO @n FROM `userlabgroupdivision` c LEFT JOIN `groupdivision` p0 ON c.`GroupDivisionID` = p0.`ID` LEFT JOIN `users` p1 ON c.`UserID` = p1.`ID` WHERE (c.`GroupDivisionID` IS NOT NULL AND p0.`ID` IS NULL) OR (c.`UserID` IS NOT NULL AND p1.`ID` IS NULL); SET @orphans = @orphans + @n; -- Two-row subquery on the false branch: ERROR 1242, an abort, before 40_fk runs. SELECT IF(@orphans = 0, 'userlabgroupdivision: orphan check OK - 0 rows across 2 relationships', (SELECT CONCAT('ABORT: ', @orphans, ' rows in `userlabgroupdivision` still reference a parent that does not exist') UNION ALL SELECT 'Run 20_cleanup for this table first. Do NOT skip this check.') ) AS preflight_orphans; INSERT INTO `_migration_ledger` (`step`, `applied_at`, `checksum`, `duration_ms`) VALUES (@step, NOW(), 'fd513369c36024271550dfac104b1bc7e149f20267a4c2a21d849d743d782461', TIMESTAMPDIFF(MICROSECOND, @t0, NOW(3)) DIV 1000) ON DUPLICATE KEY UPDATE `step` = `step`; -- --------------------------------------------------------------------- -- userlabgroupdivision/40_fk -- source section 8 -- The constraints, one ALTER for the whole table. -- -- FOREIGN_KEY_CHECKS is turned OFF for this statement and restored afterwards. With -- checks on InnoDB cannot add a foreign key in place -- it copies the whole table and -- re-validates every row, which is where the original migration's 154 table copies -- came from. The validation is not skipped, it MOVED: 30_assert proves zero orphans -- and refuses to let this run otherwise. -- --------------------------------------------------------------------- SET @fk_prev = @@FOREIGN_KEY_CHECKS; SET FOREIGN_KEY_CHECKS = 0; SET @step = 'userlabgroupdivision/40_fk'; SET @todo = (SELECT COUNT(*) = 0 FROM `_migration_ledger` WHERE `step` = @step); SET @t0 = NOW(3); SET @sql = IF(@todo, 'ALTER TABLE `userlabgroupdivision` ADD CONSTRAINT `userlabgroupdivision_GroupDivisionID` FOREIGN KEY (`GroupDivisionID`) REFERENCES `groupdivision` (`ID`) ON DELETE RESTRICT ON UPDATE RESTRICT, ADD CONSTRAINT `userlabgroupdivision_UserID` FOREIGN KEY (`UserID`) REFERENCES `users` (`ID`) ON DELETE RESTRICT ON UPDATE RESTRICT', 'DO 0'); PREPARE _step FROM @sql; EXECUTE _step; DEALLOCATE PREPARE _step; INSERT INTO `_migration_ledger` (`step`, `applied_at`, `checksum`, `duration_ms`) VALUES (@step, NOW(), 'f3583725e7f14597890c4ed07b2e773647211530dd91362950e503249fd449c7', TIMESTAMPDIFF(MICROSECOND, @t0, NOW(3)) DIV 1000) ON DUPLICATE KEY UPDATE `step` = `step`; SET FOREIGN_KEY_CHECKS = IFNULL(@fk_prev, 1); -- --------------------------------------------------------------------- -- unit: userprincipal (detail, owned by users) -- --------------------------------------------------------------------- SET SQL_MODE = IF(@@SQL_MODE LIKE '%STRICT_TRANS_TABLES%' AND @@SQL_MODE LIKE '%NO_AUTO_VALUE_ON_ZERO%' AND @@SQL_MODE NOT LIKE '%NO_ZERO_DATE%' AND @@SQL_MODE NOT LIKE '%NO_ZERO_IN_DATE%', @@SQL_MODE, CONCAT(REPLACE(REPLACE(@@SQL_MODE, 'NO_ZERO_IN_DATE', ''), 'NO_ZERO_DATE', ''), ',NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES')); -- --------------------------------------------------------------------- -- userprincipal/10_alter -- source section A -- The merged ALTER: new columns, type/charset changes, column order, NULL relaxation -- and the indexes every foreign key needs -- ONE statement, so InnoDB rebuilds this -- table ONCE. The original migration split these across five sections and rebuilt some -- tables four times; that is where its 12 hours went. Do not split this back up. -- -- Clause order is the original section order (3b, 3d, 3e, 4, 7), which is what keeps -- every ADD COLUMN ... AFTER positioned exactly as before. -- --------------------------------------------------------------------- SET @step = 'userprincipal/10_alter'; SET @todo = (SELECT COUNT(*) = 0 FROM `_migration_ledger` WHERE `step` = @step); SET @t0 = NOW(3); SET @sql = IF(@todo, 'ALTER TABLE `userprincipal` MODIFY COLUMN `UserID` int DEFAULT NULL, MODIFY COLUMN `PrincipalID` int DEFAULT NULL, ADD INDEX `UserID` (`UserID`,`PrincipalID`), ADD INDEX `userprincipal_PrincipalID` (`PrincipalID`)', 'DO 0'); PREPARE _step FROM @sql; EXECUTE _step; DEALLOCATE PREPARE _step; INSERT INTO `_migration_ledger` (`step`, `applied_at`, `checksum`, `duration_ms`) VALUES (@step, NOW(), '1dd43f2c5aa255534d8e94d21c0939d37aaf0e2f30bcd40ad29e4d2cf87d71f5', TIMESTAMPDIFF(MICROSECOND, @t0, NOW(3)) DIV 1000) ON DUPLICATE KEY UPDATE `step` = `step`; -- --------------------------------------------------------------------- -- userprincipal/20_cleanup -- source section B (= the original sections 5 and 6, merged) -- 0-sentinels AND orphaned references -> NULL, one pass per table. -- section 5: WHERE `c` = 0 -- section 6: WHERE `c` IS NOT NULL AND parent.ID IS NULL -- merged : WHERE `c` IS NOT NULL AND (`c` = 0 OR parent.ID IS NULL) -- Every LEFT JOIN is a primary-key equality, so none of them can multiply rows. -- -- DATA CHANGE, and this script cannot reverse it. Run the audit first. -- In its own transaction, with the ledger row inside it: either the cleanup and the -- record of it both land, or neither does. -- --------------------------------------------------------------------- START TRANSACTION; SET @step = 'userprincipal/20_cleanup'; SET @todo = (SELECT COUNT(*) = 0 FROM `_migration_ledger` WHERE `step` = @step); SET @t0 = NOW(3); SET @sql = IF(@todo, 'UPDATE `userprincipal` c LEFT JOIN `principal` p0 ON c.`PrincipalID` = p0.`ID` LEFT JOIN `users` p1 ON c.`UserID` = p1.`ID` SET c.`PrincipalID` = IF((c.`PrincipalID` IS NOT NULL AND (c.`PrincipalID` = 0 OR p0.`ID` IS NULL)), NULL, c.`PrincipalID`), c.`UserID` = IF((c.`UserID` IS NOT NULL AND (c.`UserID` = 0 OR p1.`ID` IS NULL)), NULL, c.`UserID`) WHERE (c.`PrincipalID` IS NOT NULL AND (c.`PrincipalID` = 0 OR p0.`ID` IS NULL)) OR (c.`UserID` IS NOT NULL AND (c.`UserID` = 0 OR p1.`ID` IS NULL))', 'DO 0'); PREPARE _step FROM @sql; EXECUTE _step; DEALLOCATE PREPARE _step; INSERT INTO `_migration_ledger` (`step`, `applied_at`, `checksum`, `duration_ms`) VALUES (@step, NOW(), 'bb07b3d736fb4db4cbf33b778e604fb024a0eb80d782d75419b4f54857111654', TIMESTAMPDIFF(MICROSECOND, @t0, NOW(3)) DIV 1000) ON DUPLICATE KEY UPDATE `step` = `step`; COMMIT; -- --------------------------------------------------------------------- -- userprincipal/30_assert -- source section 7b -- Orphan assertion over this table's 2 foreign-key relationships. -- -- This is what pays for 40_fk being fast. The original created the constraints with -- FOREIGN_KEY_CHECKS ON so a row the cleanup missed would abort the run, and paid for it -- with a full table copy per constraint. This buys the same protection with one indexed -- pass, and 40_fk then adds the constraints as metadata only. -- -- NOT ledger-guarded on the way in, unlike every other phase: it only reads, and the -- point of it is to be re-runnable. It does record its ledger row -- 40_fk requires it. -- -- IF THIS ABORTS: a row survived 20_cleanup. Same stop-and-look event the original would -- have hit as ERROR 1452 inside section 8, but nothing has been created yet. -- --------------------------------------------------------------------- SET @step = 'userprincipal/30_assert'; SET @t0 = NOW(3); SET @orphans = 0; SELECT COUNT(*) INTO @n FROM `userprincipal` c LEFT JOIN `principal` p0 ON c.`PrincipalID` = p0.`ID` LEFT JOIN `users` p1 ON c.`UserID` = p1.`ID` WHERE (c.`PrincipalID` IS NOT NULL AND p0.`ID` IS NULL) OR (c.`UserID` IS NOT NULL AND p1.`ID` IS NULL); SET @orphans = @orphans + @n; -- Two-row subquery on the false branch: ERROR 1242, an abort, before 40_fk runs. SELECT IF(@orphans = 0, 'userprincipal: orphan check OK - 0 rows across 2 relationships', (SELECT CONCAT('ABORT: ', @orphans, ' rows in `userprincipal` still reference a parent that does not exist') UNION ALL SELECT 'Run 20_cleanup for this table first. Do NOT skip this check.') ) AS preflight_orphans; INSERT INTO `_migration_ledger` (`step`, `applied_at`, `checksum`, `duration_ms`) VALUES (@step, NOW(), 'aa32f67a545dc5b95c78e0f566dacf7fa7fa1546fe34c96a5b708caab8b1dca8', TIMESTAMPDIFF(MICROSECOND, @t0, NOW(3)) DIV 1000) ON DUPLICATE KEY UPDATE `step` = `step`; -- --------------------------------------------------------------------- -- userprincipal/40_fk -- source section 8 -- The constraints, one ALTER for the whole table. -- -- FOREIGN_KEY_CHECKS is turned OFF for this statement and restored afterwards. With -- checks on InnoDB cannot add a foreign key in place -- it copies the whole table and -- re-validates every row, which is where the original migration's 154 table copies -- came from. The validation is not skipped, it MOVED: 30_assert proves zero orphans -- and refuses to let this run otherwise. -- --------------------------------------------------------------------- SET @fk_prev = @@FOREIGN_KEY_CHECKS; SET FOREIGN_KEY_CHECKS = 0; SET @step = 'userprincipal/40_fk'; SET @todo = (SELECT COUNT(*) = 0 FROM `_migration_ledger` WHERE `step` = @step); SET @t0 = NOW(3); SET @sql = IF(@todo, 'ALTER TABLE `userprincipal` ADD CONSTRAINT `userprincipal_PrincipalID` FOREIGN KEY (`PrincipalID`) REFERENCES `principal` (`ID`) ON DELETE RESTRICT ON UPDATE RESTRICT, ADD CONSTRAINT `userprincipal_UserID` FOREIGN KEY (`UserID`) REFERENCES `users` (`ID`) ON DELETE RESTRICT ON UPDATE RESTRICT', 'DO 0'); PREPARE _step FROM @sql; EXECUTE _step; DEALLOCATE PREPARE _step; INSERT INTO `_migration_ledger` (`step`, `applied_at`, `checksum`, `duration_ms`) VALUES (@step, NOW(), '1d51708ee6ccc62aa69c6f647f63adf1096c7771e2f8acf0718f1487f42bf202', TIMESTAMPDIFF(MICROSECOND, @t0, NOW(3)) DIV 1000) ON DUPLICATE KEY UPDATE `step` = `step`; SET FOREIGN_KEY_CHECKS = IFNULL(@fk_prev, 1); -- --------------------------------------------------------------------- -- unit: usersperiode (detail, owned by users) -- --------------------------------------------------------------------- SET SQL_MODE = IF(@@SQL_MODE LIKE '%STRICT_TRANS_TABLES%' AND @@SQL_MODE LIKE '%NO_AUTO_VALUE_ON_ZERO%' AND @@SQL_MODE NOT LIKE '%NO_ZERO_DATE%' AND @@SQL_MODE NOT LIKE '%NO_ZERO_IN_DATE%', @@SQL_MODE, CONCAT(REPLACE(REPLACE(@@SQL_MODE, 'NO_ZERO_IN_DATE', ''), 'NO_ZERO_DATE', ''), ',NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES')); -- --------------------------------------------------------------------- -- usersperiode/10_alter -- source section A -- The merged ALTER: new columns, type/charset changes, column order, NULL relaxation -- and the indexes every foreign key needs -- ONE statement, so InnoDB rebuilds this -- table ONCE. The original migration split these across five sections and rebuilt some -- tables four times; that is where its 12 hours went. Do not split this back up. -- -- Clause order is the original section order (3b, 3d, 3e, 4, 7), which is what keeps -- every ADD COLUMN ... AFTER positioned exactly as before. -- --------------------------------------------------------------------- SET @step = 'usersperiode/10_alter'; SET @todo = (SELECT COUNT(*) = 0 FROM `_migration_ledger` WHERE `step` = @step); SET @t0 = NOW(3); SET @sql = IF(@todo, 'ALTER TABLE `usersperiode` MODIFY COLUMN `UserID` int DEFAULT NULL, ADD INDEX `usersperiode_UserID` (`UserID`)', 'DO 0'); PREPARE _step FROM @sql; EXECUTE _step; DEALLOCATE PREPARE _step; INSERT INTO `_migration_ledger` (`step`, `applied_at`, `checksum`, `duration_ms`) VALUES (@step, NOW(), '2c94c6751c609fdfab55b421dbc4beb436c478213959a9f763b38cd77ae7f8b0', TIMESTAMPDIFF(MICROSECOND, @t0, NOW(3)) DIV 1000) ON DUPLICATE KEY UPDATE `step` = `step`; -- --------------------------------------------------------------------- -- usersperiode/20_cleanup -- source section B (= the original sections 5 and 6, merged) -- 0-sentinels AND orphaned references -> NULL, one pass per table. -- section 5: WHERE `c` = 0 -- section 6: WHERE `c` IS NOT NULL AND parent.ID IS NULL -- merged : WHERE `c` IS NOT NULL AND (`c` = 0 OR parent.ID IS NULL) -- Every LEFT JOIN is a primary-key equality, so none of them can multiply rows. -- -- DATA CHANGE, and this script cannot reverse it. Run the audit first. -- In its own transaction, with the ledger row inside it: either the cleanup and the -- record of it both land, or neither does. -- --------------------------------------------------------------------- START TRANSACTION; SET @step = 'usersperiode/20_cleanup'; SET @todo = (SELECT COUNT(*) = 0 FROM `_migration_ledger` WHERE `step` = @step); SET @t0 = NOW(3); SET @sql = IF(@todo, 'UPDATE `usersperiode` c LEFT JOIN `users` p0 ON c.`UserID` = p0.`ID` SET c.`UserID` = IF((c.`UserID` IS NOT NULL AND (c.`UserID` = 0 OR p0.`ID` IS NULL)), NULL, c.`UserID`) WHERE (c.`UserID` IS NOT NULL AND (c.`UserID` = 0 OR p0.`ID` IS NULL))', 'DO 0'); PREPARE _step FROM @sql; EXECUTE _step; DEALLOCATE PREPARE _step; INSERT INTO `_migration_ledger` (`step`, `applied_at`, `checksum`, `duration_ms`) VALUES (@step, NOW(), 'b17a948a80d36d6c66565b95912a8be298c4ace191a78fa914d13ef6e47c1a55', TIMESTAMPDIFF(MICROSECOND, @t0, NOW(3)) DIV 1000) ON DUPLICATE KEY UPDATE `step` = `step`; COMMIT; -- --------------------------------------------------------------------- -- usersperiode/30_assert -- source section 7b -- Orphan assertion over this table's 1 foreign-key relationships. -- -- This is what pays for 40_fk being fast. The original created the constraints with -- FOREIGN_KEY_CHECKS ON so a row the cleanup missed would abort the run, and paid for it -- with a full table copy per constraint. This buys the same protection with one indexed -- pass, and 40_fk then adds the constraints as metadata only. -- -- NOT ledger-guarded on the way in, unlike every other phase: it only reads, and the -- point of it is to be re-runnable. It does record its ledger row -- 40_fk requires it. -- -- IF THIS ABORTS: a row survived 20_cleanup. Same stop-and-look event the original would -- have hit as ERROR 1452 inside section 8, but nothing has been created yet. -- --------------------------------------------------------------------- SET @step = 'usersperiode/30_assert'; SET @t0 = NOW(3); SET @orphans = 0; SELECT COUNT(*) INTO @n FROM `usersperiode` c LEFT JOIN `users` p0 ON c.`UserID` = p0.`ID` WHERE (c.`UserID` IS NOT NULL AND p0.`ID` IS NULL); SET @orphans = @orphans + @n; -- Two-row subquery on the false branch: ERROR 1242, an abort, before 40_fk runs. SELECT IF(@orphans = 0, 'usersperiode: orphan check OK - 0 rows across 1 relationships', (SELECT CONCAT('ABORT: ', @orphans, ' rows in `usersperiode` still reference a parent that does not exist') UNION ALL SELECT 'Run 20_cleanup for this table first. Do NOT skip this check.') ) AS preflight_orphans; INSERT INTO `_migration_ledger` (`step`, `applied_at`, `checksum`, `duration_ms`) VALUES (@step, NOW(), 'd73ccc0af5f94bbab6578e123beedd6a4cdd5ef54d6b613edaa2ca3b907e3644', TIMESTAMPDIFF(MICROSECOND, @t0, NOW(3)) DIV 1000) ON DUPLICATE KEY UPDATE `step` = `step`; -- --------------------------------------------------------------------- -- usersperiode/40_fk -- source section 8 -- The constraints, one ALTER for the whole table. -- -- FOREIGN_KEY_CHECKS is turned OFF for this statement and restored afterwards. With -- checks on InnoDB cannot add a foreign key in place -- it copies the whole table and -- re-validates every row, which is where the original migration's 154 table copies -- came from. The validation is not skipped, it MOVED: 30_assert proves zero orphans -- and refuses to let this run otherwise. -- --------------------------------------------------------------------- SET @fk_prev = @@FOREIGN_KEY_CHECKS; SET FOREIGN_KEY_CHECKS = 0; SET @step = 'usersperiode/40_fk'; SET @todo = (SELECT COUNT(*) = 0 FROM `_migration_ledger` WHERE `step` = @step); SET @t0 = NOW(3); SET @sql = IF(@todo, 'ALTER TABLE `usersperiode` ADD CONSTRAINT `usersperiode_UserID` FOREIGN KEY (`UserID`) REFERENCES `users` (`ID`) ON DELETE RESTRICT ON UPDATE RESTRICT', 'DO 0'); PREPARE _step FROM @sql; EXECUTE _step; DEALLOCATE PREPARE _step; INSERT INTO `_migration_ledger` (`step`, `applied_at`, `checksum`, `duration_ms`) VALUES (@step, NOW(), '3f24fe9f604d03cfcf40e94ffc149908e9c540efa708dac2325feb0b7b06a017', TIMESTAMPDIFF(MICROSECOND, @t0, NOW(3)) DIV 1000) ON DUPLICATE KEY UPDATE `step` = `step`; SET FOREIGN_KEY_CHECKS = IFNULL(@fk_prev, 1); -- --------------------------------------------------------------------- -- unit: userwarehouse (detail, owned by users) -- --------------------------------------------------------------------- SET SQL_MODE = IF(@@SQL_MODE LIKE '%STRICT_TRANS_TABLES%' AND @@SQL_MODE LIKE '%NO_AUTO_VALUE_ON_ZERO%' AND @@SQL_MODE NOT LIKE '%NO_ZERO_DATE%' AND @@SQL_MODE NOT LIKE '%NO_ZERO_IN_DATE%', @@SQL_MODE, CONCAT(REPLACE(REPLACE(@@SQL_MODE, 'NO_ZERO_IN_DATE', ''), 'NO_ZERO_DATE', ''), ',NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES')); -- --------------------------------------------------------------------- -- userwarehouse/10_alter -- source section A -- The merged ALTER: new columns, type/charset changes, column order, NULL relaxation -- and the indexes every foreign key needs -- ONE statement, so InnoDB rebuilds this -- table ONCE. The original migration split these across five sections and rebuilt some -- tables four times; that is where its 12 hours went. Do not split this back up. -- -- Clause order is the original section order (3b, 3d, 3e, 4, 7), which is what keeps -- every ADD COLUMN ... AFTER positioned exactly as before. -- --------------------------------------------------------------------- SET @step = 'userwarehouse/10_alter'; SET @todo = (SELECT COUNT(*) = 0 FROM `_migration_ledger` WHERE `step` = @step); SET @t0 = NOW(3); SET @sql = IF(@todo, 'ALTER TABLE `userwarehouse` MODIFY COLUMN `UserID` int DEFAULT NULL, MODIFY COLUMN `WarehouseID` int DEFAULT NULL, ADD INDEX `userwarehouse_WarehouseID` (`WarehouseID`)', 'DO 0'); PREPARE _step FROM @sql; EXECUTE _step; DEALLOCATE PREPARE _step; INSERT INTO `_migration_ledger` (`step`, `applied_at`, `checksum`, `duration_ms`) VALUES (@step, NOW(), 'cdaf84afd8d65eb25d533dbc4c7ec0e9e2f6aedd784633d5e29a33353eb69768', TIMESTAMPDIFF(MICROSECOND, @t0, NOW(3)) DIV 1000) ON DUPLICATE KEY UPDATE `step` = `step`; -- --------------------------------------------------------------------- -- userwarehouse/20_cleanup -- source section B (= the original sections 5 and 6, merged) -- 0-sentinels AND orphaned references -> NULL, one pass per table. -- section 5: WHERE `c` = 0 -- section 6: WHERE `c` IS NOT NULL AND parent.ID IS NULL -- merged : WHERE `c` IS NOT NULL AND (`c` = 0 OR parent.ID IS NULL) -- Every LEFT JOIN is a primary-key equality, so none of them can multiply rows. -- -- DATA CHANGE, and this script cannot reverse it. Run the audit first. -- In its own transaction, with the ledger row inside it: either the cleanup and the -- record of it both land, or neither does. -- --------------------------------------------------------------------- START TRANSACTION; SET @step = 'userwarehouse/20_cleanup'; SET @todo = (SELECT COUNT(*) = 0 FROM `_migration_ledger` WHERE `step` = @step); SET @t0 = NOW(3); SET @sql = IF(@todo, 'UPDATE `userwarehouse` c LEFT JOIN `users` p0 ON c.`UserID` = p0.`ID` LEFT JOIN `warehouse` p1 ON c.`WarehouseID` = p1.`ID` SET c.`UserID` = IF((c.`UserID` IS NOT NULL AND (c.`UserID` = 0 OR p0.`ID` IS NULL)), NULL, c.`UserID`), c.`WarehouseID` = IF((c.`WarehouseID` IS NOT NULL AND (c.`WarehouseID` = 0 OR p1.`ID` IS NULL)), NULL, c.`WarehouseID`) WHERE (c.`UserID` IS NOT NULL AND (c.`UserID` = 0 OR p0.`ID` IS NULL)) OR (c.`WarehouseID` IS NOT NULL AND (c.`WarehouseID` = 0 OR p1.`ID` IS NULL))', 'DO 0'); PREPARE _step FROM @sql; EXECUTE _step; DEALLOCATE PREPARE _step; INSERT INTO `_migration_ledger` (`step`, `applied_at`, `checksum`, `duration_ms`) VALUES (@step, NOW(), 'b4857b4655b829e714a9e54e235b22a4787b786b27624ec2e8fdcbf24c193339', TIMESTAMPDIFF(MICROSECOND, @t0, NOW(3)) DIV 1000) ON DUPLICATE KEY UPDATE `step` = `step`; COMMIT; -- --------------------------------------------------------------------- -- userwarehouse/30_assert -- source section 7b -- Orphan assertion over this table's 2 foreign-key relationships. -- -- This is what pays for 40_fk being fast. The original created the constraints with -- FOREIGN_KEY_CHECKS ON so a row the cleanup missed would abort the run, and paid for it -- with a full table copy per constraint. This buys the same protection with one indexed -- pass, and 40_fk then adds the constraints as metadata only. -- -- NOT ledger-guarded on the way in, unlike every other phase: it only reads, and the -- point of it is to be re-runnable. It does record its ledger row -- 40_fk requires it. -- -- IF THIS ABORTS: a row survived 20_cleanup. Same stop-and-look event the original would -- have hit as ERROR 1452 inside section 8, but nothing has been created yet. -- --------------------------------------------------------------------- SET @step = 'userwarehouse/30_assert'; SET @t0 = NOW(3); SET @orphans = 0; SELECT COUNT(*) INTO @n FROM `userwarehouse` c LEFT JOIN `users` p0 ON c.`UserID` = p0.`ID` LEFT JOIN `warehouse` p1 ON c.`WarehouseID` = p1.`ID` WHERE (c.`UserID` IS NOT NULL AND p0.`ID` IS NULL) OR (c.`WarehouseID` IS NOT NULL AND p1.`ID` IS NULL); SET @orphans = @orphans + @n; -- Two-row subquery on the false branch: ERROR 1242, an abort, before 40_fk runs. SELECT IF(@orphans = 0, 'userwarehouse: orphan check OK - 0 rows across 2 relationships', (SELECT CONCAT('ABORT: ', @orphans, ' rows in `userwarehouse` still reference a parent that does not exist') UNION ALL SELECT 'Run 20_cleanup for this table first. Do NOT skip this check.') ) AS preflight_orphans; INSERT INTO `_migration_ledger` (`step`, `applied_at`, `checksum`, `duration_ms`) VALUES (@step, NOW(), 'c6099a85190dbce1c761939941dcda90da2a7f7eac2cdb5b7b903ad32be51fc1', TIMESTAMPDIFF(MICROSECOND, @t0, NOW(3)) DIV 1000) ON DUPLICATE KEY UPDATE `step` = `step`; -- --------------------------------------------------------------------- -- userwarehouse/40_fk -- source section 8 -- The constraints, one ALTER for the whole table. -- -- FOREIGN_KEY_CHECKS is turned OFF for this statement and restored afterwards. With -- checks on InnoDB cannot add a foreign key in place -- it copies the whole table and -- re-validates every row, which is where the original migration's 154 table copies -- came from. The validation is not skipped, it MOVED: 30_assert proves zero orphans -- and refuses to let this run otherwise. -- --------------------------------------------------------------------- SET @fk_prev = @@FOREIGN_KEY_CHECKS; SET FOREIGN_KEY_CHECKS = 0; SET @step = 'userwarehouse/40_fk'; SET @todo = (SELECT COUNT(*) = 0 FROM `_migration_ledger` WHERE `step` = @step); SET @t0 = NOW(3); SET @sql = IF(@todo, 'ALTER TABLE `userwarehouse` ADD CONSTRAINT `userwarehouse_UserID` FOREIGN KEY (`UserID`) REFERENCES `users` (`ID`) ON DELETE RESTRICT ON UPDATE RESTRICT, ADD CONSTRAINT `userwarehouse_WarehouseID` FOREIGN KEY (`WarehouseID`) REFERENCES `warehouse` (`ID`) ON DELETE RESTRICT ON UPDATE RESTRICT', 'DO 0'); PREPARE _step FROM @sql; EXECUTE _step; DEALLOCATE PREPARE _step; INSERT INTO `_migration_ledger` (`step`, `applied_at`, `checksum`, `duration_ms`) VALUES (@step, NOW(), '85b7ea47d4207f4367bd6b4d89c4034ed4786d090dce4b2bb35c3537856ced12', TIMESTAMPDIFF(MICROSECOND, @t0, NOW(3)) DIV 1000) ON DUPLICATE KEY UPDATE `step` = `step`; SET FOREIGN_KEY_CHECKS = IFNULL(@fk_prev, 1); -- --------------------------------------------------------------------- -- unit: _system (system) -- --------------------------------------------------------------------- SET SQL_MODE = IF(@@SQL_MODE LIKE '%STRICT_TRANS_TABLES%' AND @@SQL_MODE LIKE '%NO_AUTO_VALUE_ON_ZERO%' AND @@SQL_MODE NOT LIKE '%NO_ZERO_DATE%' AND @@SQL_MODE NOT LIKE '%NO_ZERO_IN_DATE%', @@SQL_MODE, CONCAT(REPLACE(REPLACE(@@SQL_MODE, 'NO_ZERO_IN_DATE', ''), 'NO_ZERO_DATE', ''), ',NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES')); -- --------------------------------------------------------------------- -- _system/40_charset -- source section 0 -- Schema default charset. Changes no existing table; it governs tables created -- AFTERWARDS -- which is the next file -- so without it those tables land as latin1 -- and 500 on emoji or CJK. See .claude/rules/column-charset.md. -- The database is deliberately NOT NAMED: ALTER DATABASE with the name omitted -- applies to the connection's current database, so this is correct whatever the -- target is called. The previous revision hard-coded a name and would have altered -- the WRONG database had one by that name existed on the server. -- -- THE ONE STEP IN THIS TREE THAT IS NOT WRAPPED IN A PREPARED STATEMENT. -- ALTER DATABASE is not in the set of statements the prepared-statement protocol -- accepts: it fails with ERROR 1295, "This command is not supported in the prepared -- statement protocol yet" -- measured on MySQL 8.4, not assumed. It does not need -- the guard anyway, because setting a charset that is already set is a no-op, so -- re-running this file is free. The ledger row is still recorded, because run.sh -- and the application-side gate both count it. -- _gen/verify_concat.py asserts that every OTHER payload in the tree IS a statement -- the protocol accepts, so this cannot be rediscovered in a maintenance window. -- --------------------------------------------------------------------- SET @step = '_system/40_charset'; SET @t0 = NOW(3); ALTER DATABASE DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; INSERT INTO `_migration_ledger` (`step`, `applied_at`, `checksum`, `duration_ms`) VALUES (@step, NOW(), '550127b3207f277f843b6edd09dccca327dbc79296e7d8fd36cb225d7cf439c9', TIMESTAMPDIFF(MICROSECOND, @t0, NOW(3)) DIV 1000) ON DUPLICATE KEY UPDATE `step` = `step`; -- --------------------------------------------------------------------- -- _system/50_create_* -- source sections 2 and 2b -- ALL 12 new tables, in one file, one grouped step. -- -- Four have a proven application code path; eight are the Laravel runtime tables -- (cache, cache_locks, failed_jobs, job_batches, jobs, migrations, notification, -- sessions). Production has never had a migration run against it and has none of the -- eight -- that is what produced "Base table or view not found: ...sessions", which -- was recorded at the time as an operator error and was not. -- The other 55 tables 63 has and production lacks stay out; see 90_not_migrated/. -- -- ONE FILE, TWELVE LEDGER ROWS. Each CREATE is separately guarded, so a run that -- dies at table 7 leaves the first six recorded and re-running creates only the -- rest. That is why no IF NOT EXISTS was added: the ledger already makes a re-run a -- no-op, and IF NOT EXISTS would additionally hide a real collision with a table -- somebody else created. -- -- CREATING `migrations` IS NOT SEEDING IT. Do not run `php artisan migrate` here: -- Laravel would see an empty table and replay all 32 migrations against a schema -- this tree has already converged. The seed lives in zz_finalize/ and refuses to -- run until every step is recorded. -- -- 32 is the file count of ColorindoChemtraInertia/database/migrations/*.php -- -- recount it there, not here. 28 of them are seeded and 4 deliberately withheld. -- --------------------------------------------------------------------- -- budgetandtargetachievements SET @step = '_system/50_create_budgetandtargetachievements'; SET @todo = (SELECT COUNT(*) = 0 FROM `_migration_ledger` WHERE `step` = @step); SET @t0 = NOW(3); SET @sql = IF(@todo, 'CREATE TABLE `budgetandtargetachievements` ( `ID` int NOT NULL AUTO_INCREMENT, `IsDeleted` int NOT NULL, `BudgetAndTargetStatusID` int DEFAULT NULL, `Tanggal` datetime NOT NULL, `AchievedTarget` datetime NOT NULL, `BudgetAndTargetPeriodeID` int DEFAULT NULL, `UserIDInput` int DEFAULT NULL, `UserIDSales` int DEFAULT NULL, `DivisionID` int DEFAULT NULL, `CompanyID` int DEFAULT NULL, `BarangID` int DEFAULT NULL, `QuantityInKG` decimal(20,10) NOT NULL, `UnitPriceUSD` decimal(20,10) NOT NULL, `UnitPriceIDR` decimal(20,10) NOT NULL, `ValueUSD` decimal(20,10) NOT NULL, `ValueIDR` decimal(20,10) NOT NULL, `Comment` varchar(500) NOT NULL, PRIMARY KEY (`ID`), KEY `BudgetAndTargetStatusID` (`BudgetAndTargetStatusID`,`BudgetAndTargetPeriodeID`,`UserIDInput`,`UserIDSales`,`DivisionID`,`CompanyID`,`BarangID`), KEY `budgetandtargetachievements_BudgetAndTargetPeriodeID` (`BudgetAndTargetPeriodeID`), KEY `budgetandtargetachievements_UserIDInput` (`UserIDInput`), KEY `budgetandtargetachievements_UserIDSales` (`UserIDSales`), KEY `budgetandtargetachievements_DivisionID` (`DivisionID`), KEY `budgetandtargetachievements_CompanyID` (`CompanyID`), KEY `budgetandtargetachievements_BarangID` (`BarangID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1', 'DO 0'); PREPARE _step FROM @sql; EXECUTE _step; DEALLOCATE PREPARE _step; INSERT INTO `_migration_ledger` (`step`, `applied_at`, `checksum`, `duration_ms`) VALUES (@step, NOW(), '5574606c7206be89d0753456c434b3686ca37ad8f81735fd76282994997e6ab7', TIMESTAMPDIFF(MICROSECOND, @t0, NOW(3)) DIV 1000) ON DUPLICATE KEY UPDATE `step` = `step`; -- budgetandtargetreal SET @step = '_system/50_create_budgetandtargetreal'; SET @todo = (SELECT COUNT(*) = 0 FROM `_migration_ledger` WHERE `step` = @step); SET @t0 = NOW(3); SET @sql = IF(@todo, 'CREATE TABLE `budgetandtargetreal` ( `ID` int NOT NULL AUTO_INCREMENT, `IsDeleted` int NOT NULL, `BudgetAndTargetStatusID` int DEFAULT NULL, `Tanggal` datetime NOT NULL, `BudgetAndTargetPeriodeID` int DEFAULT NULL, `UserIDInput` int DEFAULT NULL, `UserIDSales` int DEFAULT NULL, `PrincipalID` int DEFAULT NULL, `DivisionID` int DEFAULT NULL, `CompanyID` int DEFAULT NULL, `QtBudget` decimal(20,5) NOT NULL, `QtTarget` decimal(20,5) NOT NULL, `UnitPriceUSD` decimal(20,5) NOT NULL, `UnitPriceIDR` decimal(20,5) NOT NULL, `ValueBudgetUSD` decimal(20,5) NOT NULL, `ValueTargetUSD` decimal(20,5) NOT NULL, `ValueIDR` decimal(20,5) NOT NULL, `Comment` varchar(500) NOT NULL, `ASTProductCode` varchar(200) NOT NULL, `ASTProductName` varchar(200) NOT NULL, `ASTPrincipalCode` varchar(100) NOT NULL, `ASTPrincipalName` varchar(100) NOT NULL, `QtyBudgetPM` decimal(25,2) NOT NULL, `QtyBudgetSM` decimal(25,2) NOT NULL, `QtyBudgetMM` decimal(25,2) NOT NULL, `QtyTargetPM` decimal(25,2) NOT NULL, `QtyTargetSM` decimal(25,2) NOT NULL, `QtyTargetMM` decimal(25,2) NOT NULL, `UnitPricePM` decimal(25,2) NOT NULL, `UnitPriceSM` decimal(25,2) NOT NULL, `UnitPriceMM` decimal(25,2) NOT NULL, `ValueBudgetUSDPM` decimal(25,2) NOT NULL, `ValueBudgetUSDSM` decimal(25,2) NOT NULL, `ValueBudgetUSDMM` decimal(25,2) NOT NULL, `ValueTargetUSDPM` decimal(25,2) NOT NULL, `ValueTargetUSDSM` decimal(25,2) NOT NULL, `ValueTargetUSDMM` decimal(25,2) NOT NULL, PRIMARY KEY (`ID`), KEY `BudgetAndTargetStatusID` (`BudgetAndTargetStatusID`,`BudgetAndTargetPeriodeID`,`UserIDInput`,`UserIDSales`,`DivisionID`,`CompanyID`), KEY `PrincipalID` (`PrincipalID`), KEY `budgetandtargetreal_BudgetAndTargetPeriodeID` (`BudgetAndTargetPeriodeID`), KEY `budgetandtargetreal_UserIDInput` (`UserIDInput`), KEY `budgetandtargetreal_UserIDSales` (`UserIDSales`), KEY `budgetandtargetreal_DivisionID` (`DivisionID`), KEY `budgetandtargetreal_CompanyID` (`CompanyID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1', 'DO 0'); PREPARE _step FROM @sql; EXECUTE _step; DEALLOCATE PREPARE _step; INSERT INTO `_migration_ledger` (`step`, `applied_at`, `checksum`, `duration_ms`) VALUES (@step, NOW(), 'fa7f9b03f274c29964510ff2c7474c48a142fcd0311696b17cd4dc10fd16ff4e', TIMESTAMPDIFF(MICROSECOND, @t0, NOW(3)) DIV 1000) ON DUPLICATE KEY UPDATE `step` = `step`; -- nsproduct SET @step = '_system/50_create_nsproduct'; SET @todo = (SELECT COUNT(*) = 0 FROM `_migration_ledger` WHERE `step` = @step); SET @t0 = NOW(3); SET @sql = IF(@todo, 'CREATE TABLE `nsproduct` ( `ID` int NOT NULL AUTO_INCREMENT, `isDeleted` int NOT NULL, `PrincipalNSCode` varchar(100) NOT NULL, `PrincipalNSName` varchar(200) NOT NULL, `ProductNSCode` varchar(100) NOT NULL, `ProductNSName` varchar(200) NOT NULL, PRIMARY KEY (`ID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1', 'DO 0'); PREPARE _step FROM @sql; EXECUTE _step; DEALLOCATE PREPARE _step; INSERT INTO `_migration_ledger` (`step`, `applied_at`, `checksum`, `duration_ms`) VALUES (@step, NOW(), '7305438878e4630b498389a23d36e352df33d2a6869cace0a9a916b1c2bf06b6', TIMESTAMPDIFF(MICROSECOND, @t0, NOW(3)) DIV 1000) ON DUPLICATE KEY UPDATE `step` = `step`; -- quotationitemorigin SET @step = '_system/50_create_quotationitemorigin'; SET @todo = (SELECT COUNT(*) = 0 FROM `_migration_ledger` WHERE `step` = @step); SET @t0 = NOW(3); SET @sql = IF(@todo, 'CREATE TABLE `quotationitemorigin` ( `ID` int NOT NULL AUTO_INCREMENT, `IsDeleted` int NOT NULL, `IsSelected` int NOT NULL, `ItemOriginName` varchar(255) NOT NULL, `Remark` varchar(255) NOT NULL, PRIMARY KEY (`ID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1', 'DO 0'); PREPARE _step FROM @sql; EXECUTE _step; DEALLOCATE PREPARE _step; INSERT INTO `_migration_ledger` (`step`, `applied_at`, `checksum`, `duration_ms`) VALUES (@step, NOW(), '81062445aa2d2052709f594901ecb58f98b9147d0079212b306b961da95c2775', TIMESTAMPDIFF(MICROSECOND, @t0, NOW(3)) DIV 1000) ON DUPLICATE KEY UPDATE `step` = `step`; -- sessions SET @step = '_system/50_create_sessions'; SET @todo = (SELECT COUNT(*) = 0 FROM `_migration_ledger` WHERE `step` = @step); SET @t0 = NOW(3); SET @sql = IF(@todo, 'CREATE TABLE `sessions` ( `id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, `user_id` int DEFAULT NULL, `ip_address` varchar(45) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL, `user_agent` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci, `payload` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, `last_activity` int NOT NULL, PRIMARY KEY (`id`), KEY `sessions_user_id_index` (`user_id`), KEY `sessions_last_activity_index` (`last_activity`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC', 'DO 0'); PREPARE _step FROM @sql; EXECUTE _step; DEALLOCATE PREPARE _step; INSERT INTO `_migration_ledger` (`step`, `applied_at`, `checksum`, `duration_ms`) VALUES (@step, NOW(), '7aafe284698e7908fab79d1b194477bf1d355483732469a6dcba7042c370951b', TIMESTAMPDIFF(MICROSECOND, @t0, NOW(3)) DIV 1000) ON DUPLICATE KEY UPDATE `step` = `step`; -- cache SET @step = '_system/50_create_cache'; SET @todo = (SELECT COUNT(*) = 0 FROM `_migration_ledger` WHERE `step` = @step); SET @t0 = NOW(3); SET @sql = IF(@todo, 'CREATE TABLE `cache` ( `key` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, `value` mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, `expiration` bigint NOT NULL, PRIMARY KEY (`key`), KEY `cache_expiration_index` (`expiration`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC', 'DO 0'); PREPARE _step FROM @sql; EXECUTE _step; DEALLOCATE PREPARE _step; INSERT INTO `_migration_ledger` (`step`, `applied_at`, `checksum`, `duration_ms`) VALUES (@step, NOW(), 'f897593506175d7f50b7911726aa8c094d6e3999410c39da90fcd8dca82d6770', TIMESTAMPDIFF(MICROSECOND, @t0, NOW(3)) DIV 1000) ON DUPLICATE KEY UPDATE `step` = `step`; -- cache_locks SET @step = '_system/50_create_cache_locks'; SET @todo = (SELECT COUNT(*) = 0 FROM `_migration_ledger` WHERE `step` = @step); SET @t0 = NOW(3); SET @sql = IF(@todo, 'CREATE TABLE `cache_locks` ( `key` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, `owner` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, `expiration` bigint NOT NULL, PRIMARY KEY (`key`), KEY `cache_locks_expiration_index` (`expiration`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC', 'DO 0'); PREPARE _step FROM @sql; EXECUTE _step; DEALLOCATE PREPARE _step; INSERT INTO `_migration_ledger` (`step`, `applied_at`, `checksum`, `duration_ms`) VALUES (@step, NOW(), '1715d81ce6aeca15288dddff663e4bf0b4c8b71ff60a550ef4c57e9e11f4d0c7', TIMESTAMPDIFF(MICROSECOND, @t0, NOW(3)) DIV 1000) ON DUPLICATE KEY UPDATE `step` = `step`; -- jobs SET @step = '_system/50_create_jobs'; SET @todo = (SELECT COUNT(*) = 0 FROM `_migration_ledger` WHERE `step` = @step); SET @t0 = NOW(3); SET @sql = IF(@todo, 'CREATE TABLE `jobs` ( `id` bigint unsigned NOT NULL AUTO_INCREMENT, `queue` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, `payload` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, `attempts` smallint unsigned NOT NULL, `reserved_at` int unsigned DEFAULT NULL, `available_at` int unsigned NOT NULL, `created_at` int unsigned NOT NULL, PRIMARY KEY (`id`), KEY `jobs_queue_index` (`queue`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC', 'DO 0'); PREPARE _step FROM @sql; EXECUTE _step; DEALLOCATE PREPARE _step; INSERT INTO `_migration_ledger` (`step`, `applied_at`, `checksum`, `duration_ms`) VALUES (@step, NOW(), '7e419befd5d8a03e93317db73487db6a4c3260e5bf7b821161c13d46bc0eba4e', TIMESTAMPDIFF(MICROSECOND, @t0, NOW(3)) DIV 1000) ON DUPLICATE KEY UPDATE `step` = `step`; -- job_batches SET @step = '_system/50_create_job_batches'; SET @todo = (SELECT COUNT(*) = 0 FROM `_migration_ledger` WHERE `step` = @step); SET @t0 = NOW(3); SET @sql = IF(@todo, 'CREATE TABLE `job_batches` ( `id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, `total_jobs` int NOT NULL, `pending_jobs` int NOT NULL, `failed_jobs` int NOT NULL, `failed_job_ids` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, `options` mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci, `cancelled_at` int DEFAULT NULL, `created_at` int NOT NULL, `finished_at` int DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC', 'DO 0'); PREPARE _step FROM @sql; EXECUTE _step; DEALLOCATE PREPARE _step; INSERT INTO `_migration_ledger` (`step`, `applied_at`, `checksum`, `duration_ms`) VALUES (@step, NOW(), '895995ce1d9cc8b0f8c24f57441eab7a87c7d73b89ed502252afe097b7fc8d4b', TIMESTAMPDIFF(MICROSECOND, @t0, NOW(3)) DIV 1000) ON DUPLICATE KEY UPDATE `step` = `step`; -- failed_jobs SET @step = '_system/50_create_failed_jobs'; SET @todo = (SELECT COUNT(*) = 0 FROM `_migration_ledger` WHERE `step` = @step); SET @t0 = NOW(3); SET @sql = IF(@todo, 'CREATE TABLE `failed_jobs` ( `id` bigint unsigned NOT NULL AUTO_INCREMENT, `uuid` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, `connection` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, `queue` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, `payload` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, `exception` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, `failed_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`), UNIQUE KEY `failed_jobs_uuid_unique` (`uuid`), KEY `failed_jobs_connection_queue_failed_at_index` (`connection`,`queue`,`failed_at`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC', 'DO 0'); PREPARE _step FROM @sql; EXECUTE _step; DEALLOCATE PREPARE _step; INSERT INTO `_migration_ledger` (`step`, `applied_at`, `checksum`, `duration_ms`) VALUES (@step, NOW(), '5d562e4884f55ea7442afc28099382327cb6961b5e5044b56720d4175862d60a', TIMESTAMPDIFF(MICROSECOND, @t0, NOW(3)) DIV 1000) ON DUPLICATE KEY UPDATE `step` = `step`; -- migrations SET @step = '_system/50_create_migrations'; SET @todo = (SELECT COUNT(*) = 0 FROM `_migration_ledger` WHERE `step` = @step); SET @t0 = NOW(3); SET @sql = IF(@todo, 'CREATE TABLE `migrations` ( `id` int unsigned NOT NULL AUTO_INCREMENT, `migration` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, `batch` int NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci', 'DO 0'); PREPARE _step FROM @sql; EXECUTE _step; DEALLOCATE PREPARE _step; INSERT INTO `_migration_ledger` (`step`, `applied_at`, `checksum`, `duration_ms`) VALUES (@step, NOW(), '4db76a78e653e02b253b7841327c574f4559faa176492c03d5b972cf0c99d644', TIMESTAMPDIFF(MICROSECOND, @t0, NOW(3)) DIV 1000) ON DUPLICATE KEY UPDATE `step` = `step`; -- notification SET @step = '_system/50_create_notification'; SET @todo = (SELECT COUNT(*) = 0 FROM `_migration_ledger` WHERE `step` = @step); SET @t0 = NOW(3); SET @sql = IF(@todo, 'CREATE TABLE `notification` ( `ID` int NOT NULL AUTO_INCREMENT, `IsDeleted` int NOT NULL DEFAULT 0, `UserID` int NOT NULL, `EventKey` varchar(60) NOT NULL, `Module` varchar(40) NOT NULL, `Title` varchar(200) NOT NULL, `Message` varchar(500) NOT NULL, `LinkLaravel` varchar(200) NOT NULL, `RefID` int NOT NULL DEFAULT 0, `IsRead` int NOT NULL DEFAULT 0, `ReadAt` datetime NULL DEFAULT NULL, `Tanggal` datetime NOT NULL, `UserIDInput` int NOT NULL DEFAULT 0, PRIMARY KEY (`ID`), KEY `idx_notification_user_unread` (`UserID`,`IsRead`,`IsDeleted`), KEY `idx_notification_user_id` (`UserID`,`ID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci', 'DO 0'); PREPARE _step FROM @sql; EXECUTE _step; DEALLOCATE PREPARE _step; INSERT INTO `_migration_ledger` (`step`, `applied_at`, `checksum`, `duration_ms`) VALUES (@step, NOW(), '3c54f51e6a99800cbb04281224aa954769acd6299b00eb4ed999c80f7d260a85', TIMESTAMPDIFF(MICROSECOND, @t0, NOW(3)) DIV 1000) ON DUPLICATE KEY UPDATE `step` = `step`; -- --------------------------------------------------------------------- -- unit: userjob (lookup) -- --------------------------------------------------------------------- SET SQL_MODE = IF(@@SQL_MODE LIKE '%STRICT_TRANS_TABLES%' AND @@SQL_MODE LIKE '%NO_AUTO_VALUE_ON_ZERO%' AND @@SQL_MODE NOT LIKE '%NO_ZERO_DATE%' AND @@SQL_MODE NOT LIKE '%NO_ZERO_IN_DATE%', @@SQL_MODE, CONCAT(REPLACE(REPLACE(@@SQL_MODE, 'NO_ZERO_IN_DATE', ''), 'NO_ZERO_DATE', ''), ',NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES')); -- --------------------------------------------------------------------- -- userjob/10_alter -- source section A -- The merged ALTER: new columns, type/charset changes, column order, NULL relaxation -- and the indexes every foreign key needs -- ONE statement, so InnoDB rebuilds this -- table ONCE. The original migration split these across five sections and rebuilt some -- tables four times; that is where its 12 hours went. Do not split this back up. -- -- Clause order is the original section order (3b, 3d, 3e, 4, 7), which is what keeps -- every ADD COLUMN ... AFTER positioned exactly as before. -- --------------------------------------------------------------------- SET @step = 'userjob/10_alter'; SET @todo = (SELECT COUNT(*) = 0 FROM `_migration_ledger` WHERE `step` = @step); SET @t0 = NOW(3); SET @sql = IF(@todo, 'ALTER TABLE `userjob` MODIFY COLUMN `UserID` int DEFAULT NULL, ADD INDEX `UserID` (`UserID`)', 'DO 0'); PREPARE _step FROM @sql; EXECUTE _step; DEALLOCATE PREPARE _step; INSERT INTO `_migration_ledger` (`step`, `applied_at`, `checksum`, `duration_ms`) VALUES (@step, NOW(), 'd73ac13c8316f5dc11b44e29ef3dde3c570fa9febe98b1a5b66ba57dc60f53c1', TIMESTAMPDIFF(MICROSECOND, @t0, NOW(3)) DIV 1000) ON DUPLICATE KEY UPDATE `step` = `step`; -- --------------------------------------------------------------------- -- userjob/20_cleanup -- source section B (= the original sections 5 and 6, merged) -- 0-sentinels AND orphaned references -> NULL, one pass per table. -- section 5: WHERE `c` = 0 -- section 6: WHERE `c` IS NOT NULL AND parent.ID IS NULL -- merged : WHERE `c` IS NOT NULL AND (`c` = 0 OR parent.ID IS NULL) -- Every LEFT JOIN is a primary-key equality, so none of them can multiply rows. -- -- DATA CHANGE, and this script cannot reverse it. Run the audit first. -- In its own transaction, with the ledger row inside it: either the cleanup and the -- record of it both land, or neither does. -- --------------------------------------------------------------------- START TRANSACTION; SET @step = 'userjob/20_cleanup'; SET @todo = (SELECT COUNT(*) = 0 FROM `_migration_ledger` WHERE `step` = @step); SET @t0 = NOW(3); SET @sql = IF(@todo, 'UPDATE `userjob` c LEFT JOIN `users` p0 ON c.`UserID` = p0.`ID` SET c.`UserID` = IF((c.`UserID` IS NOT NULL AND (c.`UserID` = 0 OR p0.`ID` IS NULL)), NULL, c.`UserID`) WHERE (c.`UserID` IS NOT NULL AND (c.`UserID` = 0 OR p0.`ID` IS NULL))', 'DO 0'); PREPARE _step FROM @sql; EXECUTE _step; DEALLOCATE PREPARE _step; INSERT INTO `_migration_ledger` (`step`, `applied_at`, `checksum`, `duration_ms`) VALUES (@step, NOW(), 'f1c2781148d954b9c4b0ec9d875ebe2a649260d2e7b7f623c445eaf7ac8acbad', TIMESTAMPDIFF(MICROSECOND, @t0, NOW(3)) DIV 1000) ON DUPLICATE KEY UPDATE `step` = `step`; COMMIT; -- --------------------------------------------------------------------- -- userjob/30_assert -- source section 7b -- Orphan assertion over this table's 1 foreign-key relationships. -- -- This is what pays for 40_fk being fast. The original created the constraints with -- FOREIGN_KEY_CHECKS ON so a row the cleanup missed would abort the run, and paid for it -- with a full table copy per constraint. This buys the same protection with one indexed -- pass, and 40_fk then adds the constraints as metadata only. -- -- NOT ledger-guarded on the way in, unlike every other phase: it only reads, and the -- point of it is to be re-runnable. It does record its ledger row -- 40_fk requires it. -- -- IF THIS ABORTS: a row survived 20_cleanup. Same stop-and-look event the original would -- have hit as ERROR 1452 inside section 8, but nothing has been created yet. -- --------------------------------------------------------------------- SET @step = 'userjob/30_assert'; SET @t0 = NOW(3); SET @orphans = 0; SELECT COUNT(*) INTO @n FROM `userjob` c LEFT JOIN `users` p0 ON c.`UserID` = p0.`ID` WHERE (c.`UserID` IS NOT NULL AND p0.`ID` IS NULL); SET @orphans = @orphans + @n; -- Two-row subquery on the false branch: ERROR 1242, an abort, before 40_fk runs. SELECT IF(@orphans = 0, 'userjob: orphan check OK - 0 rows across 1 relationships', (SELECT CONCAT('ABORT: ', @orphans, ' rows in `userjob` still reference a parent that does not exist') UNION ALL SELECT 'Run 20_cleanup for this table first. Do NOT skip this check.') ) AS preflight_orphans; INSERT INTO `_migration_ledger` (`step`, `applied_at`, `checksum`, `duration_ms`) VALUES (@step, NOW(), '20552348a5dbbe25c3a302f06972711875ec28922d60d855f1f5dde231fd058a', TIMESTAMPDIFF(MICROSECOND, @t0, NOW(3)) DIV 1000) ON DUPLICATE KEY UPDATE `step` = `step`; -- --------------------------------------------------------------------- -- userjob/40_fk -- source section 8 -- The constraints, one ALTER for the whole table. -- -- FOREIGN_KEY_CHECKS is turned OFF for this statement and restored afterwards. With -- checks on InnoDB cannot add a foreign key in place -- it copies the whole table and -- re-validates every row, which is where the original migration's 154 table copies -- came from. The validation is not skipped, it MOVED: 30_assert proves zero orphans -- and refuses to let this run otherwise. -- --------------------------------------------------------------------- SET @fk_prev = @@FOREIGN_KEY_CHECKS; SET FOREIGN_KEY_CHECKS = 0; SET @step = 'userjob/40_fk'; SET @todo = (SELECT COUNT(*) = 0 FROM `_migration_ledger` WHERE `step` = @step); SET @t0 = NOW(3); SET @sql = IF(@todo, 'ALTER TABLE `userjob` ADD CONSTRAINT `userjob_UserID` FOREIGN KEY (`UserID`) REFERENCES `users` (`ID`) ON DELETE RESTRICT ON UPDATE RESTRICT', 'DO 0'); PREPARE _step FROM @sql; EXECUTE _step; DEALLOCATE PREPARE _step; INSERT INTO `_migration_ledger` (`step`, `applied_at`, `checksum`, `duration_ms`) VALUES (@step, NOW(), 'a154c732fce4c89af989ea4901602d9c687e927a657146e5785bb4ede928a691', TIMESTAMPDIFF(MICROSECOND, @t0, NOW(3)) DIV 1000) ON DUPLICATE KEY UPDATE `step` = `step`; SET FOREIGN_KEY_CHECKS = IFNULL(@fk_prev, 1); -- --------------------------------------------------------------- postflight -- DID THE WORK ACTUALLY LAND? -- -- Every step records itself in `_migration_ledger` immediately after its statement runs, -- WITHOUT checking that the statement succeeded. That is inherited from step/ unchanged, -- and there it is safe because run.sh runs mysql without --force, so the first error stops -- everything. Imported by hand, the client decides -- and "continue on error" would leave -- failed steps marked applied. -- -- So this file checks its own work against information_schema before it finishes. It is -- the only thing here that is NOT copied from step/. It reads; it changes nothing. -- postflight-set: fk SET @want_fk = 27; SELECT COUNT(*), IFNULL(SUBSTRING(GROUP_CONCAT(n ORDER BY n SEPARATOR ', '), 1, 60), '') INTO @missn_fk, @miss_fk FROM (SELECT 'menu.menu_ParentID' AS n UNION ALL SELECT 'rolegadget.rolegadget_GadgetID' UNION ALL SELECT 'rolegadget.rolegadget_RoleID' UNION ALL SELECT 'rolemenu.rolemenu_MenuID' UNION ALL SELECT 'rolemenu.rolemenu_RoleID' UNION ALL SELECT 'userbusinesstravelbranch.userbusinesstravelbranch_BranchID' UNION ALL SELECT 'userbusinesstravelbranch.userbusinesstravelbranch_UserID' UNION ALL SELECT 'userdetails.userdetails_BranchID' UNION ALL SELECT 'userdetails.userdetails_DepartmentID' UNION ALL SELECT 'userdetails.userdetails_DivisionID' UNION ALL SELECT 'userdivision.userdivision_DivisionID' UNION ALL SELECT 'userdivision.userdivision_UserID' UNION ALL SELECT 'usereducation.usereducation_EducationID' UNION ALL SELECT 'usereducation.usereducation_UserID' UNION ALL SELECT 'userjob.userjob_UserID' UNION ALL SELECT 'userlabgroupdivision.userlabgroupdivision_GroupDivisionID' UNION ALL SELECT 'userlabgroupdivision.userlabgroupdivision_UserID' UNION ALL SELECT 'userprincipal.userprincipal_PrincipalID' UNION ALL SELECT 'userprincipal.userprincipal_UserID' UNION ALL SELECT 'users.users_BranchID' UNION ALL SELECT 'users.users_DepartmentID' UNION ALL SELECT 'users.users_HeadID' UNION ALL SELECT 'users.users_MaritalStatusID' UNION ALL SELECT 'users.users_RoleID' UNION ALL SELECT 'usersperiode.usersperiode_UserID' UNION ALL SELECT 'userwarehouse.userwarehouse_UserID' UNION ALL SELECT 'userwarehouse.userwarehouse_WarehouseID') _w WHERE n NOT IN (SELECT CONCAT(TABLE_NAME, '.', CONSTRAINT_NAME) FROM information_schema.TABLE_CONSTRAINTS WHERE CONSTRAINT_SCHEMA = DATABASE() AND CONSTRAINT_TYPE = 'FOREIGN KEY'); SET @have_fk = @want_fk - @missn_fk; SET @pf_fk = IF(@missn_fk = 0, CONCAT('SELECT ''postflight foreign keys: ', @have_fk, '/', @want_fk, ' present'' AS postflight_fk'), CONCAT('SELECT `ABORT postflight: MISSING foreign keys: ', @miss_fk, ' -- ', @missn_fk, ' of ', @want_fk, ' absent. A step is recorded in', ' _migration_ledger but did not take effect.`')); PREPARE _pf FROM @pf_fk; EXECUTE _pf; DEALLOCATE PREPARE _pf; -- postflight-set: col SET @want_col = 7; SELECT COUNT(*), IFNULL(SUBSTRING(GROUP_CONCAT(n ORDER BY n SEPARATOR ', '), 1, 60), '') INTO @missn_col, @miss_col FROM (SELECT 'menu.Icon' AS n UNION ALL SELECT 'menu.LinkLaravel' UNION ALL SELECT 'menu.Path' UNION ALL SELECT 'users.Otp' UNION ALL SELECT 'users.OtpExpiration' UNION ALL SELECT 'users.PasswordResetToken' UNION ALL SELECT 'users.PasswordResetTokenCreatedAt') _w WHERE n NOT IN (SELECT CONCAT(TABLE_NAME, '.', COLUMN_NAME) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE()); SET @have_col = @want_col - @missn_col; SET @pf_col = IF(@missn_col = 0, CONCAT('SELECT ''postflight new columns: ', @have_col, '/', @want_col, ' present'' AS postflight_col'), CONCAT('SELECT `ABORT postflight: MISSING new columns: ', @miss_col, ' -- ', @missn_col, ' of ', @want_col, ' absent. A step is recorded in', ' _migration_ledger but did not take effect.`')); PREPARE _pf FROM @pf_col; EXECUTE _pf; DEALLOCATE PREPARE _pf; -- postflight-set: idx SET @want_idx = 26; SELECT COUNT(*), IFNULL(SUBSTRING(GROUP_CONCAT(n ORDER BY n SEPARATOR ', '), 1, 60), '') INTO @missn_idx, @miss_idx FROM (SELECT 'menu.ParentID' AS n UNION ALL SELECT 'rolegadget.RoleID' UNION ALL SELECT 'rolegadget.rolegadget_GadgetID' UNION ALL SELECT 'rolemenu.RoleID' UNION ALL SELECT 'rolemenu.rolemenu_MenuID' UNION ALL SELECT 'userbusinesstravelbranch.userbusinesstravelbranch_BranchID' UNION ALL SELECT 'userbusinesstravelbranch.userbusinesstravelbranch_UserID' UNION ALL SELECT 'userdetails.BranchID' UNION ALL SELECT 'userdetails.userdetails_DepartmentID' UNION ALL SELECT 'userdetails.userdetails_DivisionID' UNION ALL SELECT 'userdivision.UserID' UNION ALL SELECT 'userdivision.userdivision_DivisionID' UNION ALL SELECT 'usereducation.UserID' UNION ALL SELECT 'usereducation.usereducation_EducationID' UNION ALL SELECT 'userjob.UserID' UNION ALL SELECT 'userlabgroupdivision.userlabgroupdivision_GroupDivisionID' UNION ALL SELECT 'userlabgroupdivision.userlabgroupdivision_UserID' UNION ALL SELECT 'userprincipal.UserID' UNION ALL SELECT 'userprincipal.userprincipal_PrincipalID' UNION ALL SELECT 'users.DepartmentID' UNION ALL SELECT 'users.users_BranchID' UNION ALL SELECT 'users.users_HeadID' UNION ALL SELECT 'users.users_MaritalStatusID' UNION ALL SELECT 'users.users_RoleID' UNION ALL SELECT 'usersperiode.usersperiode_UserID' UNION ALL SELECT 'userwarehouse.userwarehouse_WarehouseID') _w WHERE n NOT IN (SELECT CONCAT(TABLE_NAME, '.', INDEX_NAME) FROM information_schema.STATISTICS WHERE TABLE_SCHEMA = DATABASE()); SET @have_idx = @want_idx - @missn_idx; SET @pf_idx = IF(@missn_idx = 0, CONCAT('SELECT ''postflight indexes: ', @have_idx, '/', @want_idx, ' present'' AS postflight_idx'), CONCAT('SELECT `ABORT postflight: MISSING indexes: ', @miss_idx, ' -- ', @missn_idx, ' of ', @want_idx, ' absent. A step is recorded in', ' _migration_ledger but did not take effect.`')); PREPARE _pf FROM @pf_idx; EXECUTE _pf; DEALLOCATE PREPARE _pf; -- postflight-set: typ SET @want_typ = 29; SELECT COUNT(*), IFNULL(SUBSTRING(GROUP_CONCAT(n ORDER BY n SEPARATOR ', '), 1, 60), '') INTO @missn_typ, @miss_typ FROM (SELECT 'menu.ParentID.int' AS n UNION ALL SELECT 'rolegadget.GadgetID.int' UNION ALL SELECT 'rolegadget.RoleID.int' UNION ALL SELECT 'rolemenu.MenuID.int' UNION ALL SELECT 'rolemenu.RoleID.int' UNION ALL SELECT 'userbusinesstravelbranch.BranchID.int' UNION ALL SELECT 'userbusinesstravelbranch.UserID.int' UNION ALL SELECT 'userdetails.BranchID.int' UNION ALL SELECT 'userdetails.DepartmentID.int' UNION ALL SELECT 'userdetails.DivisionID.int' UNION ALL SELECT 'userdivision.DivisionID.int' UNION ALL SELECT 'userdivision.UserID.int' UNION ALL SELECT 'usereducation.EducationID.int' UNION ALL SELECT 'usereducation.UserID.int' UNION ALL SELECT 'userjob.UserID.int' UNION ALL SELECT 'userlabgroupdivision.GroupDivisionID.int' UNION ALL SELECT 'userlabgroupdivision.UserID.int' UNION ALL SELECT 'userprincipal.PrincipalID.int' UNION ALL SELECT 'userprincipal.UserID.int' UNION ALL SELECT 'users.BranchID.int' UNION ALL SELECT 'users.DepartmentID.int' UNION ALL SELECT 'users.HeadID.int' UNION ALL SELECT 'users.MaritalStatusID.int' UNION ALL SELECT 'users.Password.varchar' UNION ALL SELECT 'users.RoleID.int' UNION ALL SELECT 'users.Username.varchar' UNION ALL SELECT 'usersperiode.UserID.int' UNION ALL SELECT 'userwarehouse.UserID.int' UNION ALL SELECT 'userwarehouse.WarehouseID.int') _w WHERE n NOT IN (SELECT CONCAT(TABLE_NAME, '.', COLUMN_NAME, '.', DATA_TYPE) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE()); SET @have_typ = @want_typ - @missn_typ; SET @pf_typ = IF(@missn_typ = 0, CONCAT('SELECT ''postflight retyped columns: ', @have_typ, '/', @want_typ, ' present'' AS postflight_typ'), CONCAT('SELECT `ABORT postflight: MISSING retyped columns: ', @miss_typ, ' -- ', @missn_typ, ' of ', @want_typ, ' absent. A step is recorded in', ' _migration_ledger but did not take effect.`')); PREPARE _pf FROM @pf_typ; EXECUTE _pf; DEALLOCATE PREPARE _pf; -- postflight-set: tab SET @want_tab = 12; SELECT COUNT(*), IFNULL(SUBSTRING(GROUP_CONCAT(n ORDER BY n SEPARATOR ', '), 1, 60), '') INTO @missn_tab, @miss_tab FROM (SELECT 'budgetandtargetachievements' AS n UNION ALL SELECT 'budgetandtargetreal' UNION ALL SELECT 'cache' UNION ALL SELECT 'cache_locks' UNION ALL SELECT 'failed_jobs' UNION ALL SELECT 'job_batches' UNION ALL SELECT 'jobs' UNION ALL SELECT 'migrations' UNION ALL SELECT 'notification' UNION ALL SELECT 'nsproduct' UNION ALL SELECT 'quotationitemorigin' UNION ALL SELECT 'sessions') _w WHERE n NOT IN (SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA = DATABASE() AND TABLE_TYPE = 'BASE TABLE'); SET @have_tab = @want_tab - @missn_tab; SET @pf_tab = IF(@missn_tab = 0, CONCAT('SELECT ''postflight new tables: ', @have_tab, '/', @want_tab, ' present'' AS postflight_tab'), CONCAT('SELECT `ABORT postflight: MISSING new tables: ', @miss_tab, ' -- ', @missn_tab, ' of ', @want_tab, ' absent. A step is recorded in', ' _migration_ledger but did not take effect.`')); PREPARE _pf FROM @pf_tab; EXECUTE _pf; DEALLOCATE PREPARE _pf; -- --------------------------------------------------------------- restore -- IFNULL on all four: if phpMyAdmin resumed this import on a fresh connection the -- @OLD_ variables are gone, and a bare SET from NULL is ERROR 1231. -- -- The CAST is on the two timeouts and NOWHERE ELSE, on purpose. An INTEGER system -- variable rejects IFNULL()'s result type outright (ERROR 1232, every time, not only -- when NULL). FOREIGN_KEY_CHECKS is a boolean and SQL_MODE is a set; both accept a -- string result, so they need no CAST. Do not "fix" the asymmetry, and do not copy -- line 3's shape onto a new integer variable. SET SESSION lock_wait_timeout = CAST(IFNULL(@OLD_LOCK_WAIT, 86400) AS UNSIGNED); SET SESSION innodb_lock_wait_timeout = CAST(IFNULL(@OLD_INNODB_LOCK_WAIT, 50) AS UNSIGNED); SET FOREIGN_KEY_CHECKS = IFNULL(@OLD_FK, 1); SET SQL_MODE = IFNULL(@OLD_SQL_MODE, @@SQL_MODE);