Mautic campaign_lead_event_log_new hit 2147483647 rows.

panic-at-the-disco.sql

-- 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'
;