Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Mautic campaign_lead_event_log_new hit 2147483647 rows.
-- So this table hit the max-int value for the auto_increment column id.
-- Really, such columns should be unsigned so they can go up to 4 bil.
ALTER TABLE campaign_lead_event_failed_log DROP FOREIGN KEY `FK_E50614D2EA675D86`;
-- This took ~3 minutes.
ALTER TABLE campaign_lead_event_failed_log CHANGE log_id log_id INT(11) UNSIGNED NOT NULL;
-- The next table was too large to modify in place, was causing an outage, so we had to do a swap like so:
CREATE TABLE campaign_lead_event_log_new LIKE campaign_lead_event_log;
ALTER TABLE campaign_lead_event_log_new CHANGE id id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE campaign_lead_event_log_new AUTO_INCREMENT = 2147483648;
RENAME TABLE campaign_lead_event_log TO campaign_lead_event_log_old;
RENAME TABLE campaign_lead_event_log_new TO campaign_lead_event_log;
-- Run this for the next few days. Weeee. 148GB of data in this case
INSERT IGNORE INTO campaign_lead_event_log SELECT * FROM campaign_lead_event_log_old;
-- Re-add the foreign key constraint when we can...
ALTER TABLE campaign_lead_event_failed_log ADD CONSTRAINT `FK_E50614D2EA675D86` FOREIGN KEY (`log_id`) REFERENCES `campaign_lead_event_log` (`id`) ON DELETE CASCADE;
-- See how it's going
SET @gstart = (
SELECT VARIABLE_VALUE as 'rows'
FROM information_schema.GLOBAL_STATUS
WHERE VARIABLE_NAME = 'HANDLER_READ_RND_NEXT'
);
SET @sstart = (
SELECT VARIABLE_VALUE as 'rows'
FROM information_schema.SESSION_STATUS
WHERE VARIABLE_NAME = 'HANDLER_READ_RND_NEXT'
);
SELECT SLEEP(60);
SET @gend = (
SELECT VARIABLE_VALUE as 'rows'
FROM information_schema.GLOBAL_STATUS
WHERE VARIABLE_NAME = 'HANDLER_READ_RND_NEXT'
);
SET @send = (
SELECT VARIABLE_VALUE as 'rows'
FROM information_schema.SESSION_STATUS
WHERE VARIABLE_NAME = 'HANDLER_READ_RND_NEXT'
);
SELECT
(@send - @sstart) * 60 as 'Rows read per hour globally',
(@gend - @gstart) * 60 as 'Rows read per hour in this session',
ROUND(2147483647 / ((@send - @sstart) * 60)) as 'Total hrs for max-int transfer to complete in current session'
;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.