-- ===================================================================== -- 32_samplerequestimport.sql -- Sample Request Import -- -- 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. -- -- 5 table(s), 20 ledger step(s). -- -- ⚠ IMPORT ORDER: 01_system.sql, then all seven 1x masters, then this file. Order among -- transactions does not matter. Re-importing costs nothing. -- -- ⚠ 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. -- ===================================================================== -- Menus this file serves (9 links): -- /sample-orders/request-import -- /sample-orders/request-import/all -- /sample-orders/request-import/approval-mm -- /sample-orders/request-import/approval-pm -- /sample-orders/request-import/goods-receipt -- /sample-orders/request-import/review -- /sample-orders/request-import/view -- /sample-orders/request-import/view-all -- /sample-orders/request-import/view-all-pm -- --------------------------------------------------------------- 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 -- 00_system/01_system.sql MUST already be applied. Every doc says so; until now nothing -- enforced it, and the violation was silent for most of the tree: only three files break -- without it (30_quotation and 40_budgetandtarget hit ERROR 1146 on a table 01_system -- creates, and 99_finalize needs `migrations`). The other twenty import cleanly, record -- their ledger rows, and leave a database that is half-migrated with nothing to say so. -- -- 13 steps: the ALTER DATABASE plus the 12 CREATE TABLEs. SET @sys_done = (SELECT COUNT(*) FROM `_migration_ledger` WHERE `step` LIKE '\_system/%'); SET @pf_sys = IF(@sys_done >= 13, 'SELECT ''preflight: 00_system is applied'' AS preflight_system', 'SELECT `ABORT: import 00_system/01_system.sql first. It runs the ALTER DATABASE and creates the 12 new tables that everything here assumes exist.`'); PREPARE _pf FROM @pf_sys; EXECUTE _pf; DEALLOCATE PREPARE _pf; -- --------------------------------------------------------------- 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 ('samplerequestimport/10_alter', 'samplerequestimport/20_cleanup', 'samplerequestimport/30_assert', 'samplerequestimport/40_fk', 'samplerequestimportassignment/10_alter', 'samplerequestimportassignment/20_cleanup', 'samplerequestimportassignment/30_assert', 'samplerequestimportassignment/40_fk', 'samplerequestimportdetail/10_alter', 'samplerequestimportdetail/20_cleanup', 'samplerequestimportdetail/30_assert', 'samplerequestimportdetail/40_fk', 'samplerequestimportdetailassignment/10_alter', 'samplerequestimportdetailassignment/20_cleanup', 'samplerequestimportdetailassignment/30_assert', 'samplerequestimportdetailassignment/40_fk', 'samplerequestimportgoodsreceipt/10_alter', 'samplerequestimportgoodsreceipt/20_cleanup', 'samplerequestimportgoodsreceipt/30_assert', 'samplerequestimportgoodsreceipt/40_fk') AND CONCAT(`step`, ':', `checksum`) NOT IN ('samplerequestimport/10_alter:0ea52e65a30bea7dc85c1f5048e64ba293372479197bdeb959afc025696a2782', 'samplerequestimport/20_cleanup:6607132d673c6b8282b9bdaa220eeaf1e7bfbd135f4102297a0c588bfd80bfd2', 'samplerequestimport/30_assert:88859530f8a7b17469803c1b1274e8f8df68c1698f747889bc921804b758973b', 'samplerequestimport/40_fk:6999892e86a7e6c9ac8f05760728be10acc674a536436aee5d6c60528d9b9264', 'samplerequestimportassignment/10_alter:0b7c44f081c7298a66ce28795388c939a80592d862c4de0a4c6952442504562f', 'samplerequestimportassignment/20_cleanup:5a76d0468e424e1dbd216bab686fd160b6a778cc5ae3c1693acacb80aa4245bd', 'samplerequestimportassignment/30_assert:327b9615faecfcc905009d832068067897d5ffc5cc26e92421678e210d9896ab', 'samplerequestimportassignment/40_fk:0fba9cdb3562f896a6f2ce3f87fb0e6afbee411ef28255c7ae80afd2af40db79', 'samplerequestimportdetail/10_alter:5bff1df6726dd4e8c9f1953e8e0c29e9a5f4ff34e798bc0613de54f26ed9914f', 'samplerequestimportdetail/20_cleanup:89242410fcd8aa074328cbe89f6fa522f2bb36e213b2fb2e2b1292dbbae14319', 'samplerequestimportdetail/30_assert:51077ac005c8c5a3380a5bdfa9994dfe1e88d00ace79aac2d3c29175c8b954ca', 'samplerequestimportdetail/40_fk:dbe7e7768ea74d36a1fc3659bca61f5ef17c623249293e5f0df0d863b23136c1', 'samplerequestimportdetailassignment/10_alter:560176a1aa526d5b066a3628e86eb013d4d5b34835eee249d95c84d6b1f8077b', 'samplerequestimportdetailassignment/20_cleanup:bbf1525bae07d1782ecfb17d72b1ad168cc661a0b7d30f4eb473c562ef0d8939', 'samplerequestimportdetailassignment/30_assert:039d40df6bddc6c29451af93576fa137f73950030ccf1473509c1831ab9f1f2e', 'samplerequestimportdetailassignment/40_fk:a234b611c8f77c82433a025470166044549f44f79b0ff9581923c06289d507ef', 'samplerequestimportgoodsreceipt/10_alter:725ce0875a104b677ab2437a1be41f229456c04da3a9f9363dbe88926186af04', 'samplerequestimportgoodsreceipt/20_cleanup:0d0258a652c4bf917020f7fc497c2e043ac81efed3dfeb8155577e13276d3974', 'samplerequestimportgoodsreceipt/30_assert:727d45f1a1950a06228299a6acffb7eb015faf3f258a983b555f537e8d9571f0', 'samplerequestimportgoodsreceipt/40_fk:421b371b7da02ba601719cec914ce362a0842d1488ec6a86664863f57cacdc59'); 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: samplerequestimport (transaction) -- --------------------------------------------------------------------- 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')); -- --------------------------------------------------------------------- -- samplerequestimport/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 = 'samplerequestimport/10_alter'; SET @todo = (SELECT COUNT(*) = 0 FROM `_migration_ledger` WHERE `step` = @step); SET @t0 = NOW(3); SET @sql = IF(@todo, 'ALTER TABLE `samplerequestimport` MODIFY COLUMN `SampleRequestStatusID` int DEFAULT NULL, MODIFY COLUMN `ReferenceSRID` int DEFAULT NULL, MODIFY COLUMN `UserIDInput` int DEFAULT NULL, MODIFY COLUMN `PrincipalID` int DEFAULT NULL, MODIFY COLUMN `DeliveryID` int DEFAULT NULL, MODIFY COLUMN `UrgencyID` int DEFAULT NULL, MODIFY COLUMN `RequestFromID` int DEFAULT NULL, ADD INDEX `samplerequestimport_DeliveryID` (`DeliveryID`), ADD INDEX `samplerequestimport_PrincipalID` (`PrincipalID`), ADD INDEX `samplerequestimport_ReferenceSRID` (`ReferenceSRID`), ADD INDEX `samplerequestimport_RequestFromID` (`RequestFromID`), ADD INDEX `samplerequestimport_UrgencyID` (`UrgencyID`), ADD INDEX `samplerequestimport_UserIDInput` (`UserIDInput`), ADD INDEX `SampleRequestStatusID` (`SampleRequestStatusID`,`ReferenceSRID`,`UserIDInput`,`PrincipalID`,`DeliveryID`,`UrgencyID`,`RequestFromID`)', 'DO 0'); PREPARE _step FROM @sql; EXECUTE _step; DEALLOCATE PREPARE _step; INSERT INTO `_migration_ledger` (`step`, `applied_at`, `checksum`, `duration_ms`) VALUES (@step, NOW(), '0ea52e65a30bea7dc85c1f5048e64ba293372479197bdeb959afc025696a2782', TIMESTAMPDIFF(MICROSECOND, @t0, NOW(3)) DIV 1000) ON DUPLICATE KEY UPDATE `step` = `step`; -- --------------------------------------------------------------------- -- samplerequestimport/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 = 'samplerequestimport/20_cleanup'; SET @todo = (SELECT COUNT(*) = 0 FROM `_migration_ledger` WHERE `step` = @step); SET @t0 = NOW(3); SET @sql = IF(@todo, 'UPDATE `samplerequestimport` c LEFT JOIN `delivery` p0 ON c.`DeliveryID` = p0.`ID` LEFT JOIN `principal` p1 ON c.`PrincipalID` = p1.`ID` LEFT JOIN `samplerequestimport` p2 ON c.`ReferenceSRID` = p2.`ID` LEFT JOIN `samplerequestimportfrom` p3 ON c.`RequestFromID` = p3.`ID` LEFT JOIN `samplerequeststatus` p4 ON c.`SampleRequestStatusID` = p4.`ID` LEFT JOIN `urgency` p5 ON c.`UrgencyID` = p5.`ID` LEFT JOIN `users` p6 ON c.`UserIDInput` = p6.`ID` SET c.`DeliveryID` = IF((c.`DeliveryID` IS NOT NULL AND (c.`DeliveryID` = 0 OR p0.`ID` IS NULL)), NULL, c.`DeliveryID`), c.`PrincipalID` = IF((c.`PrincipalID` IS NOT NULL AND (c.`PrincipalID` = 0 OR p1.`ID` IS NULL)), NULL, c.`PrincipalID`), c.`ReferenceSRID` = IF((c.`ReferenceSRID` IS NOT NULL AND (c.`ReferenceSRID` = 0 OR p2.`ID` IS NULL)), NULL, c.`ReferenceSRID`), c.`RequestFromID` = IF((c.`RequestFromID` IS NOT NULL AND (c.`RequestFromID` = 0 OR p3.`ID` IS NULL)), NULL, c.`RequestFromID`), c.`SampleRequestStatusID` = IF((c.`SampleRequestStatusID` IS NOT NULL AND (c.`SampleRequestStatusID` = 0 OR p4.`ID` IS NULL)), NULL, c.`SampleRequestStatusID`), c.`UrgencyID` = IF((c.`UrgencyID` IS NOT NULL AND (c.`UrgencyID` = 0 OR p5.`ID` IS NULL)), NULL, c.`UrgencyID`), c.`UserIDInput` = IF((c.`UserIDInput` IS NOT NULL AND (c.`UserIDInput` = 0 OR p6.`ID` IS NULL)), NULL, c.`UserIDInput`) WHERE (c.`DeliveryID` IS NOT NULL AND (c.`DeliveryID` = 0 OR p0.`ID` IS NULL)) OR (c.`PrincipalID` IS NOT NULL AND (c.`PrincipalID` = 0 OR p1.`ID` IS NULL)) OR (c.`ReferenceSRID` IS NOT NULL AND (c.`ReferenceSRID` = 0 OR p2.`ID` IS NULL)) OR (c.`RequestFromID` IS NOT NULL AND (c.`RequestFromID` = 0 OR p3.`ID` IS NULL)) OR (c.`SampleRequestStatusID` IS NOT NULL AND (c.`SampleRequestStatusID` = 0 OR p4.`ID` IS NULL)) OR (c.`UrgencyID` IS NOT NULL AND (c.`UrgencyID` = 0 OR p5.`ID` IS NULL)) OR (c.`UserIDInput` IS NOT NULL AND (c.`UserIDInput` = 0 OR p6.`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(), '6607132d673c6b8282b9bdaa220eeaf1e7bfbd135f4102297a0c588bfd80bfd2', TIMESTAMPDIFF(MICROSECOND, @t0, NOW(3)) DIV 1000) ON DUPLICATE KEY UPDATE `step` = `step`; COMMIT; -- --------------------------------------------------------------------- -- samplerequestimport/30_assert -- source section 7b -- Orphan assertion over this table's 7 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 = 'samplerequestimport/30_assert'; SET @t0 = NOW(3); SET @orphans = 0; SELECT COUNT(*) INTO @n FROM `samplerequestimport` c LEFT JOIN `delivery` p0 ON c.`DeliveryID` = p0.`ID` LEFT JOIN `principal` p1 ON c.`PrincipalID` = p1.`ID` LEFT JOIN `samplerequestimport` p2 ON c.`ReferenceSRID` = p2.`ID` LEFT JOIN `samplerequestimportfrom` p3 ON c.`RequestFromID` = p3.`ID` LEFT JOIN `samplerequeststatus` p4 ON c.`SampleRequestStatusID` = p4.`ID` LEFT JOIN `urgency` p5 ON c.`UrgencyID` = p5.`ID` LEFT JOIN `users` p6 ON c.`UserIDInput` = p6.`ID` WHERE (c.`DeliveryID` IS NOT NULL AND p0.`ID` IS NULL) OR (c.`PrincipalID` IS NOT NULL AND p1.`ID` IS NULL) OR (c.`ReferenceSRID` IS NOT NULL AND p2.`ID` IS NULL) OR (c.`RequestFromID` IS NOT NULL AND p3.`ID` IS NULL) OR (c.`SampleRequestStatusID` IS NOT NULL AND p4.`ID` IS NULL) OR (c.`UrgencyID` IS NOT NULL AND p5.`ID` IS NULL) OR (c.`UserIDInput` IS NOT NULL AND p6.`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, 'samplerequestimport: orphan check OK - 0 rows across 7 relationships', (SELECT CONCAT('ABORT: ', @orphans, ' rows in `samplerequestimport` 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(), '88859530f8a7b17469803c1b1274e8f8df68c1698f747889bc921804b758973b', TIMESTAMPDIFF(MICROSECOND, @t0, NOW(3)) DIV 1000) ON DUPLICATE KEY UPDATE `step` = `step`; -- --------------------------------------------------------------------- -- samplerequestimport/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 = 'samplerequestimport/40_fk'; SET @todo = (SELECT COUNT(*) = 0 FROM `_migration_ledger` WHERE `step` = @step); SET @t0 = NOW(3); SET @sql = IF(@todo, 'ALTER TABLE `samplerequestimport` ADD CONSTRAINT `samplerequestimport_DeliveryID` FOREIGN KEY (`DeliveryID`) REFERENCES `delivery` (`ID`) ON DELETE RESTRICT ON UPDATE RESTRICT, ADD CONSTRAINT `samplerequestimport_PrincipalID` FOREIGN KEY (`PrincipalID`) REFERENCES `principal` (`ID`) ON DELETE RESTRICT ON UPDATE RESTRICT, ADD CONSTRAINT `samplerequestimport_ReferenceSRID` FOREIGN KEY (`ReferenceSRID`) REFERENCES `samplerequestimport` (`ID`) ON DELETE RESTRICT ON UPDATE RESTRICT, ADD CONSTRAINT `samplerequestimport_RequestFromID` FOREIGN KEY (`RequestFromID`) REFERENCES `samplerequestimportfrom` (`ID`) ON DELETE RESTRICT ON UPDATE RESTRICT, ADD CONSTRAINT `samplerequestimport_SampleRequestStatusID` FOREIGN KEY (`SampleRequestStatusID`) REFERENCES `samplerequeststatus` (`ID`) ON DELETE RESTRICT ON UPDATE RESTRICT, ADD CONSTRAINT `samplerequestimport_UrgencyID` FOREIGN KEY (`UrgencyID`) REFERENCES `urgency` (`ID`) ON DELETE RESTRICT ON UPDATE RESTRICT, ADD CONSTRAINT `samplerequestimport_UserIDInput` FOREIGN KEY (`UserIDInput`) 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(), '6999892e86a7e6c9ac8f05760728be10acc674a536436aee5d6c60528d9b9264', TIMESTAMPDIFF(MICROSECOND, @t0, NOW(3)) DIV 1000) ON DUPLICATE KEY UPDATE `step` = `step`; SET FOREIGN_KEY_CHECKS = IFNULL(@fk_prev, 1); -- --------------------------------------------------------------------- -- unit: samplerequestimportassignment (detail, owned by samplerequestimport) -- --------------------------------------------------------------------- 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')); -- --------------------------------------------------------------------- -- samplerequestimportassignment/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 = 'samplerequestimportassignment/10_alter'; SET @todo = (SELECT COUNT(*) = 0 FROM `_migration_ledger` WHERE `step` = @step); SET @t0 = NOW(3); SET @sql = IF(@todo, 'ALTER TABLE `samplerequestimportassignment` MODIFY COLUMN `SampleRequestImportID` int DEFAULT NULL, MODIFY COLUMN `SampleRequestStatusID` int DEFAULT NULL, MODIFY COLUMN `UserID` int DEFAULT NULL, ADD INDEX `samplerequestimportassignment_SampleRequestStatusID` (`SampleRequestStatusID`), ADD INDEX `samplerequestimportassignment_UserID` (`UserID`), ADD INDEX `SampleRequestImportID` (`SampleRequestImportID`,`SampleRequestStatusID`,`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(), '0b7c44f081c7298a66ce28795388c939a80592d862c4de0a4c6952442504562f', TIMESTAMPDIFF(MICROSECOND, @t0, NOW(3)) DIV 1000) ON DUPLICATE KEY UPDATE `step` = `step`; -- --------------------------------------------------------------------- -- samplerequestimportassignment/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 = 'samplerequestimportassignment/20_cleanup'; SET @todo = (SELECT COUNT(*) = 0 FROM `_migration_ledger` WHERE `step` = @step); SET @t0 = NOW(3); SET @sql = IF(@todo, 'UPDATE `samplerequestimportassignment` c LEFT JOIN `samplerequestimport` p0 ON c.`SampleRequestImportID` = p0.`ID` LEFT JOIN `samplerequeststatus` p1 ON c.`SampleRequestStatusID` = p1.`ID` LEFT JOIN `users` p2 ON c.`UserID` = p2.`ID` SET c.`SampleRequestImportID` = IF((c.`SampleRequestImportID` IS NOT NULL AND (c.`SampleRequestImportID` = 0 OR p0.`ID` IS NULL)), NULL, c.`SampleRequestImportID`), c.`SampleRequestStatusID` = IF((c.`SampleRequestStatusID` IS NOT NULL AND (c.`SampleRequestStatusID` = 0 OR p1.`ID` IS NULL)), NULL, c.`SampleRequestStatusID`), c.`UserID` = IF((c.`UserID` IS NOT NULL AND (c.`UserID` = 0 OR p2.`ID` IS NULL)), NULL, c.`UserID`) WHERE (c.`SampleRequestImportID` IS NOT NULL AND (c.`SampleRequestImportID` = 0 OR p0.`ID` IS NULL)) OR (c.`SampleRequestStatusID` IS NOT NULL AND (c.`SampleRequestStatusID` = 0 OR p1.`ID` IS NULL)) OR (c.`UserID` IS NOT NULL AND (c.`UserID` = 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(), '5a76d0468e424e1dbd216bab686fd160b6a778cc5ae3c1693acacb80aa4245bd', TIMESTAMPDIFF(MICROSECOND, @t0, NOW(3)) DIV 1000) ON DUPLICATE KEY UPDATE `step` = `step`; COMMIT; -- --------------------------------------------------------------------- -- samplerequestimportassignment/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 = 'samplerequestimportassignment/30_assert'; SET @t0 = NOW(3); SET @orphans = 0; SELECT COUNT(*) INTO @n FROM `samplerequestimportassignment` c LEFT JOIN `samplerequestimport` p0 ON c.`SampleRequestImportID` = p0.`ID` LEFT JOIN `samplerequeststatus` p1 ON c.`SampleRequestStatusID` = p1.`ID` LEFT JOIN `users` p2 ON c.`UserID` = p2.`ID` WHERE (c.`SampleRequestImportID` IS NOT NULL AND p0.`ID` IS NULL) OR (c.`SampleRequestStatusID` IS NOT NULL AND p1.`ID` IS NULL) OR (c.`UserID` 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, 'samplerequestimportassignment: orphan check OK - 0 rows across 3 relationships', (SELECT CONCAT('ABORT: ', @orphans, ' rows in `samplerequestimportassignment` 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(), '327b9615faecfcc905009d832068067897d5ffc5cc26e92421678e210d9896ab', TIMESTAMPDIFF(MICROSECOND, @t0, NOW(3)) DIV 1000) ON DUPLICATE KEY UPDATE `step` = `step`; -- --------------------------------------------------------------------- -- samplerequestimportassignment/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 = 'samplerequestimportassignment/40_fk'; SET @todo = (SELECT COUNT(*) = 0 FROM `_migration_ledger` WHERE `step` = @step); SET @t0 = NOW(3); SET @sql = IF(@todo, 'ALTER TABLE `samplerequestimportassignment` ADD CONSTRAINT `samplerequestimportassignment_SampleRequestImportID` FOREIGN KEY (`SampleRequestImportID`) REFERENCES `samplerequestimport` (`ID`) ON DELETE RESTRICT ON UPDATE RESTRICT, ADD CONSTRAINT `samplerequestimportassignment_SampleRequestStatusID` FOREIGN KEY (`SampleRequestStatusID`) REFERENCES `samplerequeststatus` (`ID`) ON DELETE RESTRICT ON UPDATE RESTRICT, ADD CONSTRAINT `samplerequestimportassignment_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(), '0fba9cdb3562f896a6f2ce3f87fb0e6afbee411ef28255c7ae80afd2af40db79', TIMESTAMPDIFF(MICROSECOND, @t0, NOW(3)) DIV 1000) ON DUPLICATE KEY UPDATE `step` = `step`; SET FOREIGN_KEY_CHECKS = IFNULL(@fk_prev, 1); -- --------------------------------------------------------------------- -- unit: samplerequestimportdetail (detail, owned by samplerequestimport) -- --------------------------------------------------------------------- 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')); -- --------------------------------------------------------------------- -- samplerequestimportdetail/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 = 'samplerequestimportdetail/10_alter'; SET @todo = (SELECT COUNT(*) = 0 FROM `_migration_ledger` WHERE `step` = @step); SET @t0 = NOW(3); SET @sql = IF(@todo, 'ALTER TABLE `samplerequestimportdetail` MODIFY COLUMN `SampleRequestImportID` int DEFAULT NULL, MODIFY COLUMN `SampleRequestDetailStatusID` int DEFAULT NULL, MODIFY COLUMN `BarangID` int DEFAULT NULL, MODIFY COLUMN `SatuanID` int DEFAULT NULL, MODIFY COLUMN `ReqCompanyID` int DEFAULT NULL, MODIFY COLUMN `GoodReceiptDetailID` int DEFAULT NULL, MODIFY COLUMN `BaranglistID` int DEFAULT NULL, ADD INDEX `samplerequestimportdetail_BarangID` (`BarangID`), ADD INDEX `samplerequestimportdetail_BaranglistID` (`BaranglistID`), ADD INDEX `samplerequestimportdetail_GoodReceiptDetailID` (`GoodReceiptDetailID`), ADD INDEX `samplerequestimportdetail_ReqCompanyID` (`ReqCompanyID`), ADD INDEX `samplerequestimportdetail_SampleRequestDetailStatusID` (`SampleRequestDetailStatusID`), ADD INDEX `samplerequestimportdetail_SatuanID` (`SatuanID`), ADD INDEX `SampleRequestImportID` (`SampleRequestImportID`,`SampleRequestDetailStatusID`,`BarangID`,`SatuanID`,`ReqCompanyID`,`GoodReceiptDetailID`,`BaranglistID`)', 'DO 0'); PREPARE _step FROM @sql; EXECUTE _step; DEALLOCATE PREPARE _step; INSERT INTO `_migration_ledger` (`step`, `applied_at`, `checksum`, `duration_ms`) VALUES (@step, NOW(), '5bff1df6726dd4e8c9f1953e8e0c29e9a5f4ff34e798bc0613de54f26ed9914f', TIMESTAMPDIFF(MICROSECOND, @t0, NOW(3)) DIV 1000) ON DUPLICATE KEY UPDATE `step` = `step`; -- --------------------------------------------------------------------- -- samplerequestimportdetail/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 = 'samplerequestimportdetail/20_cleanup'; SET @todo = (SELECT COUNT(*) = 0 FROM `_migration_ledger` WHERE `step` = @step); SET @t0 = NOW(3); SET @sql = IF(@todo, 'UPDATE `samplerequestimportdetail` c LEFT JOIN `barang` p0 ON c.`BarangID` = p0.`ID` LEFT JOIN `baranglists` p1 ON c.`BaranglistID` = p1.`ID` LEFT JOIN `samplerequestimportgoodsreceipt` p2 ON c.`GoodReceiptDetailID` = p2.`ID` LEFT JOIN `company` p3 ON c.`ReqCompanyID` = p3.`ID` LEFT JOIN `samplerequeststatus` p4 ON c.`SampleRequestDetailStatusID` = p4.`ID` LEFT JOIN `samplerequestimport` p5 ON c.`SampleRequestImportID` = p5.`ID` LEFT JOIN `satuan` p6 ON c.`SatuanID` = p6.`ID` SET c.`BarangID` = IF((c.`BarangID` IS NOT NULL AND (c.`BarangID` = 0 OR p0.`ID` IS NULL)), NULL, c.`BarangID`), c.`BaranglistID` = IF((c.`BaranglistID` IS NOT NULL AND (c.`BaranglistID` = 0 OR p1.`ID` IS NULL)), NULL, c.`BaranglistID`), c.`GoodReceiptDetailID` = IF((c.`GoodReceiptDetailID` IS NOT NULL AND (c.`GoodReceiptDetailID` = 0 OR p2.`ID` IS NULL)), NULL, c.`GoodReceiptDetailID`), c.`ReqCompanyID` = IF((c.`ReqCompanyID` IS NOT NULL AND (c.`ReqCompanyID` = 0 OR p3.`ID` IS NULL)), NULL, c.`ReqCompanyID`), c.`SampleRequestDetailStatusID` = IF((c.`SampleRequestDetailStatusID` IS NOT NULL AND (c.`SampleRequestDetailStatusID` = 0 OR p4.`ID` IS NULL)), NULL, c.`SampleRequestDetailStatusID`), c.`SampleRequestImportID` = IF((c.`SampleRequestImportID` IS NOT NULL AND (c.`SampleRequestImportID` = 0 OR p5.`ID` IS NULL)), NULL, c.`SampleRequestImportID`), c.`SatuanID` = IF((c.`SatuanID` IS NOT NULL AND (c.`SatuanID` = 0 OR p6.`ID` IS NULL)), NULL, c.`SatuanID`) WHERE (c.`BarangID` IS NOT NULL AND (c.`BarangID` = 0 OR p0.`ID` IS NULL)) OR (c.`BaranglistID` IS NOT NULL AND (c.`BaranglistID` = 0 OR p1.`ID` IS NULL)) OR (c.`GoodReceiptDetailID` IS NOT NULL AND (c.`GoodReceiptDetailID` = 0 OR p2.`ID` IS NULL)) OR (c.`ReqCompanyID` IS NOT NULL AND (c.`ReqCompanyID` = 0 OR p3.`ID` IS NULL)) OR (c.`SampleRequestDetailStatusID` IS NOT NULL AND (c.`SampleRequestDetailStatusID` = 0 OR p4.`ID` IS NULL)) OR (c.`SampleRequestImportID` IS NOT NULL AND (c.`SampleRequestImportID` = 0 OR p5.`ID` IS NULL)) OR (c.`SatuanID` IS NOT NULL AND (c.`SatuanID` = 0 OR p6.`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(), '89242410fcd8aa074328cbe89f6fa522f2bb36e213b2fb2e2b1292dbbae14319', TIMESTAMPDIFF(MICROSECOND, @t0, NOW(3)) DIV 1000) ON DUPLICATE KEY UPDATE `step` = `step`; COMMIT; -- --------------------------------------------------------------------- -- samplerequestimportdetail/30_assert -- source section 7b -- Orphan assertion over this table's 7 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 = 'samplerequestimportdetail/30_assert'; SET @t0 = NOW(3); SET @orphans = 0; SELECT COUNT(*) INTO @n FROM `samplerequestimportdetail` c LEFT JOIN `barang` p0 ON c.`BarangID` = p0.`ID` LEFT JOIN `baranglists` p1 ON c.`BaranglistID` = p1.`ID` LEFT JOIN `samplerequestimportgoodsreceipt` p2 ON c.`GoodReceiptDetailID` = p2.`ID` LEFT JOIN `company` p3 ON c.`ReqCompanyID` = p3.`ID` LEFT JOIN `samplerequeststatus` p4 ON c.`SampleRequestDetailStatusID` = p4.`ID` LEFT JOIN `samplerequestimport` p5 ON c.`SampleRequestImportID` = p5.`ID` LEFT JOIN `satuan` p6 ON c.`SatuanID` = p6.`ID` WHERE (c.`BarangID` IS NOT NULL AND p0.`ID` IS NULL) OR (c.`BaranglistID` IS NOT NULL AND p1.`ID` IS NULL) OR (c.`GoodReceiptDetailID` IS NOT NULL AND p2.`ID` IS NULL) OR (c.`ReqCompanyID` IS NOT NULL AND p3.`ID` IS NULL) OR (c.`SampleRequestDetailStatusID` IS NOT NULL AND p4.`ID` IS NULL) OR (c.`SampleRequestImportID` IS NOT NULL AND p5.`ID` IS NULL) OR (c.`SatuanID` IS NOT NULL AND p6.`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, 'samplerequestimportdetail: orphan check OK - 0 rows across 7 relationships', (SELECT CONCAT('ABORT: ', @orphans, ' rows in `samplerequestimportdetail` 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(), '51077ac005c8c5a3380a5bdfa9994dfe1e88d00ace79aac2d3c29175c8b954ca', TIMESTAMPDIFF(MICROSECOND, @t0, NOW(3)) DIV 1000) ON DUPLICATE KEY UPDATE `step` = `step`; -- --------------------------------------------------------------------- -- samplerequestimportdetail/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 = 'samplerequestimportdetail/40_fk'; SET @todo = (SELECT COUNT(*) = 0 FROM `_migration_ledger` WHERE `step` = @step); SET @t0 = NOW(3); SET @sql = IF(@todo, 'ALTER TABLE `samplerequestimportdetail` ADD CONSTRAINT `samplerequestimportdetail_BarangID` FOREIGN KEY (`BarangID`) REFERENCES `barang` (`ID`) ON DELETE RESTRICT ON UPDATE RESTRICT, ADD CONSTRAINT `samplerequestimportdetail_BaranglistID` FOREIGN KEY (`BaranglistID`) REFERENCES `baranglists` (`ID`) ON DELETE RESTRICT ON UPDATE RESTRICT, ADD CONSTRAINT `samplerequestimportdetail_GoodReceiptDetailID` FOREIGN KEY (`GoodReceiptDetailID`) REFERENCES `samplerequestimportgoodsreceipt` (`ID`) ON DELETE RESTRICT ON UPDATE RESTRICT, ADD CONSTRAINT `samplerequestimportdetail_ReqCompanyID` FOREIGN KEY (`ReqCompanyID`) REFERENCES `company` (`ID`) ON DELETE RESTRICT ON UPDATE RESTRICT, ADD CONSTRAINT `samplerequestimportdetail_SampleRequestDetailStatusID` FOREIGN KEY (`SampleRequestDetailStatusID`) REFERENCES `samplerequeststatus` (`ID`) ON DELETE RESTRICT ON UPDATE RESTRICT, ADD CONSTRAINT `samplerequestimportdetail_SampleRequestImportID` FOREIGN KEY (`SampleRequestImportID`) REFERENCES `samplerequestimport` (`ID`) ON DELETE RESTRICT ON UPDATE RESTRICT, ADD CONSTRAINT `samplerequestimportdetail_SatuanID` FOREIGN KEY (`SatuanID`) REFERENCES `satuan` (`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(), 'dbe7e7768ea74d36a1fc3659bca61f5ef17c623249293e5f0df0d863b23136c1', TIMESTAMPDIFF(MICROSECOND, @t0, NOW(3)) DIV 1000) ON DUPLICATE KEY UPDATE `step` = `step`; SET FOREIGN_KEY_CHECKS = IFNULL(@fk_prev, 1); -- --------------------------------------------------------------------- -- unit: samplerequestimportdetailassignment (detail, owned by samplerequestimport) -- --------------------------------------------------------------------- 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')); -- --------------------------------------------------------------------- -- samplerequestimportdetailassignment/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 = 'samplerequestimportdetailassignment/10_alter'; SET @todo = (SELECT COUNT(*) = 0 FROM `_migration_ledger` WHERE `step` = @step); SET @t0 = NOW(3); SET @sql = IF(@todo, 'ALTER TABLE `samplerequestimportdetailassignment` MODIFY COLUMN `SampleRequestImportID` int DEFAULT NULL, MODIFY COLUMN `SampleRequestDetailID` int DEFAULT NULL, MODIFY COLUMN `SampleRequestDetailStatusID` int DEFAULT NULL, MODIFY COLUMN `UserID` int DEFAULT NULL, ADD INDEX `samplerequestimportdetailassignment_SampleRequestDetailID` (`SampleRequestDetailID`), ADD INDEX `samplerequestimportdetailassignment_SampleRequestDetailStatusID` (`SampleRequestDetailStatusID`), ADD INDEX `samplerequestimportdetailassignment_UserID` (`UserID`), ADD INDEX `SampleRequestImportID` (`SampleRequestImportID`,`SampleRequestDetailID`,`SampleRequestDetailStatusID`,`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(), '560176a1aa526d5b066a3628e86eb013d4d5b34835eee249d95c84d6b1f8077b', TIMESTAMPDIFF(MICROSECOND, @t0, NOW(3)) DIV 1000) ON DUPLICATE KEY UPDATE `step` = `step`; -- --------------------------------------------------------------------- -- samplerequestimportdetailassignment/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 = 'samplerequestimportdetailassignment/20_cleanup'; SET @todo = (SELECT COUNT(*) = 0 FROM `_migration_ledger` WHERE `step` = @step); SET @t0 = NOW(3); SET @sql = IF(@todo, 'UPDATE `samplerequestimportdetailassignment` c LEFT JOIN `samplerequestimportdetail` p0 ON c.`SampleRequestDetailID` = p0.`ID` LEFT JOIN `samplerequeststatus` p1 ON c.`SampleRequestDetailStatusID` = p1.`ID` LEFT JOIN `samplerequestimport` p2 ON c.`SampleRequestImportID` = p2.`ID` LEFT JOIN `users` p3 ON c.`UserID` = p3.`ID` SET c.`SampleRequestDetailID` = IF((c.`SampleRequestDetailID` IS NOT NULL AND (c.`SampleRequestDetailID` = 0 OR p0.`ID` IS NULL)), NULL, c.`SampleRequestDetailID`), c.`SampleRequestDetailStatusID` = IF((c.`SampleRequestDetailStatusID` IS NOT NULL AND (c.`SampleRequestDetailStatusID` = 0 OR p1.`ID` IS NULL)), NULL, c.`SampleRequestDetailStatusID`), c.`SampleRequestImportID` = IF((c.`SampleRequestImportID` IS NOT NULL AND (c.`SampleRequestImportID` = 0 OR p2.`ID` IS NULL)), NULL, c.`SampleRequestImportID`), c.`UserID` = IF((c.`UserID` IS NOT NULL AND (c.`UserID` = 0 OR p3.`ID` IS NULL)), NULL, c.`UserID`) WHERE (c.`SampleRequestDetailID` IS NOT NULL AND (c.`SampleRequestDetailID` = 0 OR p0.`ID` IS NULL)) OR (c.`SampleRequestDetailStatusID` IS NOT NULL AND (c.`SampleRequestDetailStatusID` = 0 OR p1.`ID` IS NULL)) OR (c.`SampleRequestImportID` IS NOT NULL AND (c.`SampleRequestImportID` = 0 OR p2.`ID` IS NULL)) OR (c.`UserID` IS NOT NULL AND (c.`UserID` = 0 OR p3.`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(), 'bbf1525bae07d1782ecfb17d72b1ad168cc661a0b7d30f4eb473c562ef0d8939', TIMESTAMPDIFF(MICROSECOND, @t0, NOW(3)) DIV 1000) ON DUPLICATE KEY UPDATE `step` = `step`; COMMIT; -- --------------------------------------------------------------------- -- samplerequestimportdetailassignment/30_assert -- source section 7b -- Orphan assertion over this table's 4 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 = 'samplerequestimportdetailassignment/30_assert'; SET @t0 = NOW(3); SET @orphans = 0; SELECT COUNT(*) INTO @n FROM `samplerequestimportdetailassignment` c LEFT JOIN `samplerequestimportdetail` p0 ON c.`SampleRequestDetailID` = p0.`ID` LEFT JOIN `samplerequeststatus` p1 ON c.`SampleRequestDetailStatusID` = p1.`ID` LEFT JOIN `samplerequestimport` p2 ON c.`SampleRequestImportID` = p2.`ID` LEFT JOIN `users` p3 ON c.`UserID` = p3.`ID` WHERE (c.`SampleRequestDetailID` IS NOT NULL AND p0.`ID` IS NULL) OR (c.`SampleRequestDetailStatusID` IS NOT NULL AND p1.`ID` IS NULL) OR (c.`SampleRequestImportID` IS NOT NULL AND p2.`ID` IS NULL) OR (c.`UserID` IS NOT NULL AND p3.`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, 'samplerequestimportdetailassignment: orphan check OK - 0 rows across 4 relationships', (SELECT CONCAT('ABORT: ', @orphans, ' rows in `samplerequestimportdetailassignment` 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(), '039d40df6bddc6c29451af93576fa137f73950030ccf1473509c1831ab9f1f2e', TIMESTAMPDIFF(MICROSECOND, @t0, NOW(3)) DIV 1000) ON DUPLICATE KEY UPDATE `step` = `step`; -- --------------------------------------------------------------------- -- samplerequestimportdetailassignment/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 = 'samplerequestimportdetailassignment/40_fk'; SET @todo = (SELECT COUNT(*) = 0 FROM `_migration_ledger` WHERE `step` = @step); SET @t0 = NOW(3); SET @sql = IF(@todo, 'ALTER TABLE `samplerequestimportdetailassignment` ADD CONSTRAINT `samplerequestimportdetailassignment_SampleRequestDetailID` FOREIGN KEY (`SampleRequestDetailID`) REFERENCES `samplerequestimportdetail` (`ID`) ON DELETE RESTRICT ON UPDATE RESTRICT, ADD CONSTRAINT `samplerequestimportdetailassignment_SampleRequestDetailStatusID` FOREIGN KEY (`SampleRequestDetailStatusID`) REFERENCES `samplerequeststatus` (`ID`) ON DELETE RESTRICT ON UPDATE RESTRICT, ADD CONSTRAINT `samplerequestimportdetailassignment_SampleRequestImportID` FOREIGN KEY (`SampleRequestImportID`) REFERENCES `samplerequestimport` (`ID`) ON DELETE RESTRICT ON UPDATE RESTRICT, ADD CONSTRAINT `samplerequestimportdetailassignment_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(), 'a234b611c8f77c82433a025470166044549f44f79b0ff9581923c06289d507ef', TIMESTAMPDIFF(MICROSECOND, @t0, NOW(3)) DIV 1000) ON DUPLICATE KEY UPDATE `step` = `step`; SET FOREIGN_KEY_CHECKS = IFNULL(@fk_prev, 1); -- --------------------------------------------------------------------- -- unit: samplerequestimportgoodsreceipt (detail, owned by samplerequestimport) -- --------------------------------------------------------------------- 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')); -- --------------------------------------------------------------------- -- samplerequestimportgoodsreceipt/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 = 'samplerequestimportgoodsreceipt/10_alter'; SET @todo = (SELECT COUNT(*) = 0 FROM `_migration_ledger` WHERE `step` = @step); SET @t0 = NOW(3); SET @sql = IF(@todo, 'ALTER TABLE `samplerequestimportgoodsreceipt` ADD COLUMN `IsRequestList` int NOT NULL AFTER `Keterangan`, MODIFY COLUMN `QuantityReceived` decimal(20,5) NOT NULL, MODIFY COLUMN `SampleRequestImportID` int DEFAULT NULL, MODIFY COLUMN `SampleRequestImportDetailID` int DEFAULT NULL, MODIFY COLUMN `BarangID` int DEFAULT NULL, MODIFY COLUMN `SatuanID` int DEFAULT NULL, MODIFY COLUMN `BarangListsID` int DEFAULT NULL, ADD INDEX `samplerequestimportgoodsreceipt_BarangID` (`BarangID`), ADD INDEX `samplerequestimportgoodsreceipt_BarangListsID` (`BarangListsID`), ADD INDEX `samplerequestimportgoodsreceipt_SampleRequestImportDetailID` (`SampleRequestImportDetailID`), ADD INDEX `samplerequestimportgoodsreceipt_SatuanID` (`SatuanID`), ADD INDEX `SampleRequestImportID` (`SampleRequestImportID`,`SampleRequestImportDetailID`,`BarangID`,`SatuanID`,`BarangListsID`)', 'DO 0'); PREPARE _step FROM @sql; EXECUTE _step; DEALLOCATE PREPARE _step; INSERT INTO `_migration_ledger` (`step`, `applied_at`, `checksum`, `duration_ms`) VALUES (@step, NOW(), '725ce0875a104b677ab2437a1be41f229456c04da3a9f9363dbe88926186af04', TIMESTAMPDIFF(MICROSECOND, @t0, NOW(3)) DIV 1000) ON DUPLICATE KEY UPDATE `step` = `step`; -- --------------------------------------------------------------------- -- samplerequestimportgoodsreceipt/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 = 'samplerequestimportgoodsreceipt/20_cleanup'; SET @todo = (SELECT COUNT(*) = 0 FROM `_migration_ledger` WHERE `step` = @step); SET @t0 = NOW(3); SET @sql = IF(@todo, 'UPDATE `samplerequestimportgoodsreceipt` c LEFT JOIN `barang` p0 ON c.`BarangID` = p0.`ID` LEFT JOIN `baranglists` p1 ON c.`BarangListsID` = p1.`ID` LEFT JOIN `samplerequestimportdetail` p2 ON c.`SampleRequestImportDetailID` = p2.`ID` LEFT JOIN `samplerequestimport` p3 ON c.`SampleRequestImportID` = p3.`ID` LEFT JOIN `satuan` p4 ON c.`SatuanID` = p4.`ID` SET c.`BarangID` = IF((c.`BarangID` IS NOT NULL AND (c.`BarangID` = 0 OR p0.`ID` IS NULL)), NULL, c.`BarangID`), c.`BarangListsID` = IF((c.`BarangListsID` IS NOT NULL AND (c.`BarangListsID` = 0 OR p1.`ID` IS NULL)), NULL, c.`BarangListsID`), c.`SampleRequestImportDetailID` = IF((c.`SampleRequestImportDetailID` IS NOT NULL AND (c.`SampleRequestImportDetailID` = 0 OR p2.`ID` IS NULL)), NULL, c.`SampleRequestImportDetailID`), c.`SampleRequestImportID` = IF((c.`SampleRequestImportID` IS NOT NULL AND (c.`SampleRequestImportID` = 0 OR p3.`ID` IS NULL)), NULL, c.`SampleRequestImportID`), c.`SatuanID` = IF((c.`SatuanID` IS NOT NULL AND (c.`SatuanID` = 0 OR p4.`ID` IS NULL)), NULL, c.`SatuanID`) WHERE (c.`BarangID` IS NOT NULL AND (c.`BarangID` = 0 OR p0.`ID` IS NULL)) OR (c.`BarangListsID` IS NOT NULL AND (c.`BarangListsID` = 0 OR p1.`ID` IS NULL)) OR (c.`SampleRequestImportDetailID` IS NOT NULL AND (c.`SampleRequestImportDetailID` = 0 OR p2.`ID` IS NULL)) OR (c.`SampleRequestImportID` IS NOT NULL AND (c.`SampleRequestImportID` = 0 OR p3.`ID` IS NULL)) OR (c.`SatuanID` IS NOT NULL AND (c.`SatuanID` = 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(), '0d0258a652c4bf917020f7fc497c2e043ac81efed3dfeb8155577e13276d3974', TIMESTAMPDIFF(MICROSECOND, @t0, NOW(3)) DIV 1000) ON DUPLICATE KEY UPDATE `step` = `step`; COMMIT; -- --------------------------------------------------------------------- -- samplerequestimportgoodsreceipt/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 = 'samplerequestimportgoodsreceipt/30_assert'; SET @t0 = NOW(3); SET @orphans = 0; SELECT COUNT(*) INTO @n FROM `samplerequestimportgoodsreceipt` c LEFT JOIN `barang` p0 ON c.`BarangID` = p0.`ID` LEFT JOIN `baranglists` p1 ON c.`BarangListsID` = p1.`ID` LEFT JOIN `samplerequestimportdetail` p2 ON c.`SampleRequestImportDetailID` = p2.`ID` LEFT JOIN `samplerequestimport` p3 ON c.`SampleRequestImportID` = p3.`ID` LEFT JOIN `satuan` p4 ON c.`SatuanID` = p4.`ID` WHERE (c.`BarangID` IS NOT NULL AND p0.`ID` IS NULL) OR (c.`BarangListsID` IS NOT NULL AND p1.`ID` IS NULL) OR (c.`SampleRequestImportDetailID` IS NOT NULL AND p2.`ID` IS NULL) OR (c.`SampleRequestImportID` IS NOT NULL AND p3.`ID` IS NULL) OR (c.`SatuanID` 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, 'samplerequestimportgoodsreceipt: orphan check OK - 0 rows across 5 relationships', (SELECT CONCAT('ABORT: ', @orphans, ' rows in `samplerequestimportgoodsreceipt` 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(), '727d45f1a1950a06228299a6acffb7eb015faf3f258a983b555f537e8d9571f0', TIMESTAMPDIFF(MICROSECOND, @t0, NOW(3)) DIV 1000) ON DUPLICATE KEY UPDATE `step` = `step`; -- --------------------------------------------------------------------- -- samplerequestimportgoodsreceipt/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 = 'samplerequestimportgoodsreceipt/40_fk'; SET @todo = (SELECT COUNT(*) = 0 FROM `_migration_ledger` WHERE `step` = @step); SET @t0 = NOW(3); SET @sql = IF(@todo, 'ALTER TABLE `samplerequestimportgoodsreceipt` ADD CONSTRAINT `samplerequestimportgoodsreceipt_BarangID` FOREIGN KEY (`BarangID`) REFERENCES `barang` (`ID`) ON DELETE RESTRICT ON UPDATE RESTRICT, ADD CONSTRAINT `samplerequestimportgoodsreceipt_BarangListsID` FOREIGN KEY (`BarangListsID`) REFERENCES `baranglists` (`ID`) ON DELETE RESTRICT ON UPDATE RESTRICT, ADD CONSTRAINT `samplerequestimportgoodsreceipt_SampleRequestImportDetailID` FOREIGN KEY (`SampleRequestImportDetailID`) REFERENCES `samplerequestimportdetail` (`ID`) ON DELETE RESTRICT ON UPDATE RESTRICT, ADD CONSTRAINT `samplerequestimportgoodsreceipt_SampleRequestImportID` FOREIGN KEY (`SampleRequestImportID`) REFERENCES `samplerequestimport` (`ID`) ON DELETE RESTRICT ON UPDATE RESTRICT, ADD CONSTRAINT `samplerequestimportgoodsreceipt_SatuanID` FOREIGN KEY (`SatuanID`) REFERENCES `satuan` (`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(), '421b371b7da02ba601719cec914ce362a0842d1488ec6a86664863f57cacdc59', 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 = 26; SELECT COUNT(*), IFNULL(SUBSTRING(GROUP_CONCAT(n ORDER BY n SEPARATOR ', '), 1, 60), '') INTO @missn_fk, @miss_fk FROM (SELECT 'samplerequestimport.samplerequestimport_DeliveryID' AS n UNION ALL SELECT 'samplerequestimport.samplerequestimport_PrincipalID' UNION ALL SELECT 'samplerequestimport.samplerequestimport_ReferenceSRID' UNION ALL SELECT 'samplerequestimport.samplerequestimport_RequestFromID' UNION ALL SELECT 'samplerequestimport.samplerequestimport_SampleRequestStatusID' UNION ALL SELECT 'samplerequestimport.samplerequestimport_UrgencyID' UNION ALL SELECT 'samplerequestimport.samplerequestimport_UserIDInput' UNION ALL SELECT 'samplerequestimportassignment.samplerequestimportassignment_SampleRequestImportID' UNION ALL SELECT 'samplerequestimportassignment.samplerequestimportassignment_SampleRequestStatusID' UNION ALL SELECT 'samplerequestimportassignment.samplerequestimportassignment_UserID' UNION ALL SELECT 'samplerequestimportdetail.samplerequestimportdetail_BarangID' UNION ALL SELECT 'samplerequestimportdetail.samplerequestimportdetail_BaranglistID' UNION ALL SELECT 'samplerequestimportdetail.samplerequestimportdetail_GoodReceiptDetailID' UNION ALL SELECT 'samplerequestimportdetail.samplerequestimportdetail_ReqCompanyID' UNION ALL SELECT 'samplerequestimportdetail.samplerequestimportdetail_SampleRequestDetailStatusID' UNION ALL SELECT 'samplerequestimportdetail.samplerequestimportdetail_SampleRequestImportID' UNION ALL SELECT 'samplerequestimportdetail.samplerequestimportdetail_SatuanID' UNION ALL SELECT 'samplerequestimportdetailassignment.samplerequestimportdetailassignment_SampleRequestDetailID' UNION ALL SELECT 'samplerequestimportdetailassignment.samplerequestimportdetailassignment_SampleRequestDetailStatusID' UNION ALL SELECT 'samplerequestimportdetailassignment.samplerequestimportdetailassignment_SampleRequestImportID' UNION ALL SELECT 'samplerequestimportdetailassignment.samplerequestimportdetailassignment_UserID' UNION ALL SELECT 'samplerequestimportgoodsreceipt.samplerequestimportgoodsreceipt_BarangID' UNION ALL SELECT 'samplerequestimportgoodsreceipt.samplerequestimportgoodsreceipt_BarangListsID' UNION ALL SELECT 'samplerequestimportgoodsreceipt.samplerequestimportgoodsreceipt_SampleRequestImportDetailID' UNION ALL SELECT 'samplerequestimportgoodsreceipt.samplerequestimportgoodsreceipt_SampleRequestImportID' UNION ALL SELECT 'samplerequestimportgoodsreceipt.samplerequestimportgoodsreceipt_SatuanID') _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 = 1; SELECT COUNT(*), IFNULL(SUBSTRING(GROUP_CONCAT(n ORDER BY n SEPARATOR ', '), 1, 60), '') INTO @missn_col, @miss_col FROM (SELECT 'samplerequestimportgoodsreceipt.IsRequestList' AS n) _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 'samplerequestimport.SampleRequestStatusID' AS n UNION ALL SELECT 'samplerequestimport.samplerequestimport_DeliveryID' UNION ALL SELECT 'samplerequestimport.samplerequestimport_PrincipalID' UNION ALL SELECT 'samplerequestimport.samplerequestimport_ReferenceSRID' UNION ALL SELECT 'samplerequestimport.samplerequestimport_RequestFromID' UNION ALL SELECT 'samplerequestimport.samplerequestimport_UrgencyID' UNION ALL SELECT 'samplerequestimport.samplerequestimport_UserIDInput' UNION ALL SELECT 'samplerequestimportassignment.SampleRequestImportID' UNION ALL SELECT 'samplerequestimportassignment.samplerequestimportassignment_SampleRequestStatusID' UNION ALL SELECT 'samplerequestimportassignment.samplerequestimportassignment_UserID' UNION ALL SELECT 'samplerequestimportdetail.SampleRequestImportID' UNION ALL SELECT 'samplerequestimportdetail.samplerequestimportdetail_BarangID' UNION ALL SELECT 'samplerequestimportdetail.samplerequestimportdetail_BaranglistID' UNION ALL SELECT 'samplerequestimportdetail.samplerequestimportdetail_GoodReceiptDetailID' UNION ALL SELECT 'samplerequestimportdetail.samplerequestimportdetail_ReqCompanyID' UNION ALL SELECT 'samplerequestimportdetail.samplerequestimportdetail_SampleRequestDetailStatusID' UNION ALL SELECT 'samplerequestimportdetail.samplerequestimportdetail_SatuanID' UNION ALL SELECT 'samplerequestimportdetailassignment.SampleRequestImportID' UNION ALL SELECT 'samplerequestimportdetailassignment.samplerequestimportdetailassignment_SampleRequestDetailID' UNION ALL SELECT 'samplerequestimportdetailassignment.samplerequestimportdetailassignment_SampleRequestDetailStatusID' UNION ALL SELECT 'samplerequestimportdetailassignment.samplerequestimportdetailassignment_UserID' UNION ALL SELECT 'samplerequestimportgoodsreceipt.SampleRequestImportID' UNION ALL SELECT 'samplerequestimportgoodsreceipt.samplerequestimportgoodsreceipt_BarangID' UNION ALL SELECT 'samplerequestimportgoodsreceipt.samplerequestimportgoodsreceipt_BarangListsID' UNION ALL SELECT 'samplerequestimportgoodsreceipt.samplerequestimportgoodsreceipt_SampleRequestImportDetailID' UNION ALL SELECT 'samplerequestimportgoodsreceipt.samplerequestimportgoodsreceipt_SatuanID') _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 = 27; SELECT COUNT(*), IFNULL(SUBSTRING(GROUP_CONCAT(n ORDER BY n SEPARATOR ', '), 1, 60), '') INTO @missn_typ, @miss_typ FROM (SELECT 'samplerequestimport.DeliveryID.int' AS n UNION ALL SELECT 'samplerequestimport.PrincipalID.int' UNION ALL SELECT 'samplerequestimport.ReferenceSRID.int' UNION ALL SELECT 'samplerequestimport.RequestFromID.int' UNION ALL SELECT 'samplerequestimport.SampleRequestStatusID.int' UNION ALL SELECT 'samplerequestimport.UrgencyID.int' UNION ALL SELECT 'samplerequestimport.UserIDInput.int' UNION ALL SELECT 'samplerequestimportassignment.SampleRequestImportID.int' UNION ALL SELECT 'samplerequestimportassignment.SampleRequestStatusID.int' UNION ALL SELECT 'samplerequestimportassignment.UserID.int' UNION ALL SELECT 'samplerequestimportdetail.BarangID.int' UNION ALL SELECT 'samplerequestimportdetail.BaranglistID.int' UNION ALL SELECT 'samplerequestimportdetail.GoodReceiptDetailID.int' UNION ALL SELECT 'samplerequestimportdetail.ReqCompanyID.int' UNION ALL SELECT 'samplerequestimportdetail.SampleRequestDetailStatusID.int' UNION ALL SELECT 'samplerequestimportdetail.SampleRequestImportID.int' UNION ALL SELECT 'samplerequestimportdetail.SatuanID.int' UNION ALL SELECT 'samplerequestimportdetailassignment.SampleRequestDetailID.int' UNION ALL SELECT 'samplerequestimportdetailassignment.SampleRequestDetailStatusID.int' UNION ALL SELECT 'samplerequestimportdetailassignment.SampleRequestImportID.int' UNION ALL SELECT 'samplerequestimportdetailassignment.UserID.int' UNION ALL SELECT 'samplerequestimportgoodsreceipt.BarangID.int' UNION ALL SELECT 'samplerequestimportgoodsreceipt.BarangListsID.int' UNION ALL SELECT 'samplerequestimportgoodsreceipt.QuantityReceived.decimal' UNION ALL SELECT 'samplerequestimportgoodsreceipt.SampleRequestImportDetailID.int' UNION ALL SELECT 'samplerequestimportgoodsreceipt.SampleRequestImportID.int' UNION ALL SELECT 'samplerequestimportgoodsreceipt.SatuanID.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; -- --------------------------------------------------------------- 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);