heathdutton

-- Find dead campaigns, first discern *active* campaigns.
SET GROUP_CONCAT_MAX_LEN = 999999999;

-- What is the oldest Lead ID we should care about ( 90 days )
SELECT id FROM leads WHERE date_added = DATE_SUB(NOW(), INTERVAL 90 DAY) LIMIT 1 INTO @lead_id;

-- Get list of all campaigns that were modified in last yr.
SELECT GROUP_CONCAT(c.id) FROM `campaigns` c WHERE c.is_published = 1 OR c.date_modified > DATE_SUB(NOW(), INTERVAL 365 DAY) INTO @active_campaigns;

-- SELECT @lead_id, @active_campaigns;

heathdutton

SELECT
  TABLE_SCHEMA,
  TABLE_NAME,
  COLUMN_NAME,
  DATA_TYPE,
  COLUMN_TYPE,
  IF(
    LOCATE('unsigned', COLUMN_TYPE) > 0,
    1,
    0

heathdutton

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

heathdutton

-- Find unused segments that can be unpublished to save processing time.
SELECT l.id, l.created_by_user, l.name, IF (l.date_modified IS NULL, l.date_added, l.date_modified) AS date_modified,
  (SELECT COUNT(*) FROM lead_lists_leads WHERE leadlist_id = l.id) AS lead_count
FROM lead_lists l
WHERE
  l.is_published = 1
  AND l.id NOT IN (
    -- Find segments in use.
    SELECT l.id
    FROM lead_lists l

heathdutton

<?php
/**
 * Example usage:
 * php five9adduser.php <admin username> <admin password> <agent email> <agent firstname> <agent lastname> <agent password>
 */

error_reporting(E_ALL);

$user      = $argv[1];
$pass      = $argv[2];

heathdutton

DELETE FROM widgets WHERE type IN ('created.leads.in.time','page.hits.in.time','submissions.in.time','recent.activity','upcoming.emails','created.leads.in.time','map.of.leads','top.lists','page.hits.in.time','emails.in.time','unique.vs.returning.leads','ignored.vs.read.emails','anonymous.vs.identified.leads','dwell.times','recent.activity','upcoming.emails');

heathdutton

sudo -u webapp bash -c "DEBUG=1 /var/app/current/mautic/app/console debug:container --env=dev"

Symfony Container Public Services
=================================

 ------------------------------------------------------------------------------------------------------ -----------------------------------------------------------------------------------------------
  Service ID                                                                                             Class name
 ------------------------------------------------------------------------------------------------------ -----------------------------------------------------------------------------------------------
  annotation_reader                                                                                      Doctrine\Common\Annotations\CachedReader
  assets.context                                                                                         Symfony\Component\Asset\Context\RequestStackContext

heathdutton

sudo -u webapp bash -c "DEBUG=1 /var/app/current/mautic/app/console debug:router"

 ------------------------------------------- ---------------- -------- ------ -------------------------------------------------------------------------------------------------
  Name                                        Method           Scheme   Host   Path
 ------------------------------------------- ---------------- -------- ------ -------------------------------------------------------------------------------------------------
  mautic_js                                   ANY              https    ANY    /mtc.js
  mautic_base_index                           ANY              https    ANY    /
  mautic_secure_root                          ANY              https    ANY    /s
  mautic_secure_root_slash                    ANY              https    ANY    /s/
  mautic_remove_trailing_slash                GET              https    ANY    /{url}

heathdutton

DELETE FROM notifications WHERE date_added < DATE_SUB(NOW(), INTERVAL 7 DAY);

heathdutton

-- All mautic campaign delays merged. Two queries (the first is important). Takes under 10s.
-- Depends on the PR of soft-deleted campaign events.
SET @@group_concat_max_len = 10000000000000;
SELECT *
FROM (
	SELECT NULL as campaign_id,
		NULL as campaign_name,
		NULL as event_id,
		NULL as event_name,
		NULL as lead_count,