Fixing Broken Manx Provisions After Restarting ActiveMQ01
As ActiveMQ01 currently requires regular restarts, it is more than possible that provision orders will be in flight when the restart occurs.
After an ActiveMQ01 restart it's worth logging in to Tigrillo helper pages and navigating to http://tigrillo.eseye.net/stuckManx.php. If there are any provisioning orders that were interrupted by the restart, they'll be in the stuckManx list, and will have an entry similar to "Could not connect to activemq01.redstation.eseye.net:61613" in the 'Notes' field.
The stuck orders will most likely be a combination of CEASES and PROVIDES. Fixing the broken orders will require you to log into dbrw.redstation.eseye.net and execute a number of SQL statements.
Ceases
The first query identifies the unique IMSIs of the broken cease orders:
SELECT
GROUP_CONCAT(IMSIID SEPARATOR ',')
FROM
tigrillo.ProvisioningQueueManx AS PQM
JOIN tigrillo.MSISDNAttribute AS MA on
MA.ID = PQM.MSISDNAttributeID
JOIN tigrillo.CurrentIMSI as CI ON
CI.IMSI = MA.IMSIID
WHERE
PQM.Status != 'completed'
AND ProvisionType = 'unprovision'
and PQM.Notes LIKE '%Could not connect to activemq01.redstation.eseye.net:61613%';
Now you can use this in list in the following queries to fix the orders in the database.
UPDATE tigrillo.CurrentIMSI SET Status = 'provisioned' where Status = 'unrequested' and IMSI IN (INLIST);
UPDATE tigrillo.IMSIAttribute SET Status = 'provisioned' where Status = 'unrequested' and OutOfService IS NULL and IMSIID IN (INLIST);
UPDATE tigrillo.CurrentMSISDN SET Status = 'provisioned' where Status = 'unrequested' and IMSI IN (INLIST);
UPDATE tigrillo.MSISDNAttribute SET `Status` = 'provisioned', `StartDate` = `CreatedDate`, `Activated` = `CreatedDate` where `Status` IN ('unrequested', 'available') and `OutOfService` IS NULL and IMSIID IN (INLIST);
Run this query to get the list of ICCIDs so that you can run the Deprovision stored procedure again:
SELECT ICCID FROM tigrillo.ProvisioningQueueManx AS PQM JOIN tigrillo.MSISDNAttribute AS MA on MA.ID = PQM.MSISDNAttributeID JOIN tigrillo.CurrentIMSI as CI ON CI.IMSI = MA.IMSIID WHERE PQM.Status !='completed' AND ProvisionType = 'unprovision' and PQM.Notes LIKE '%Could not connect to activemq01.redstation.eseye.net:61613%';
Update the Provisioning table to remove the entries from StuckManx:
UPDATE tigrillo.ProvisioningQueueManx SET Status = 'completed' WHERE Status !='completed' AND ProvisionType = 'unprovision' and Notes LIKE '%Could not connect to activemq01.redstation.eseye.net:61613%';
Run deprovisions again:
set @myStatus=''; call tigrilloDeprovisionAnynet(@ICCID,NULL,@myStatus); select @myStatus;
Provides
CREATE OR REPLACE TEMPORARY TABLE Provide (
IMSIID CHAR(32) PRIMARY KEY,
ICCID CHAR(32)
);
INSERT INTO Provide
SELECT DISTINCT IMSIID, ICCID
FROM tigrillo.ProvisioningQueueManx AS PQM
JOIN tigrillo.MSISDNAttribute AS MA on
MA.ID = PQM.MSISDNAttributeID
JOIN tigrillo.CurrentIMSI as CI
ON CI.IMSI = MA.IMSIID
WHERE
PQM.Status != 'completed'
AND ProvisionType = 'provision'
and PQM.Notes LIKE '%Could not connect to activemq01.redstation.eseye.net:61613%';
UPDATE tigrillo.CurrentIMSI SET Status = 'available' where Status = 'requested' and IMSI IN (SELECT IMSIID FROM Provide); -- c. 35 sec
UPDATE tigrillo.IMSIAttribute SET Status = 'available' where Status = 'requested' and OutOfService IS NULL and IMSIID IN (SELECT IMSIID FROM Provide); -- c. 1min 30 sec
UPDATE tigrillo.CurrentMSISDN SET Status = 'available' where Status = 'requested' and IMSI IN (SELECT IMSIID FROM Provide); -- 30 sec
UPDATE tigrillo.MSISDNAttribute SET `Status` = 'available', `Activated` = NULL where `Status` IN ( 'requested', 'available') and `OutOfService` IS NULL and IMSIID IN (SELECT IMSIID FROM Provide); -- 4 min 30 sec
-- CLEAR Provisioning Queue
UPDATE tigrillo.ProvisioningQueueManx SET Status = 'completed' WHERE Status !='completed' AND ProvisionType = 'provision' and Notes LIKE '%Could not connect to activemq01.redstation.eseye.net:61613%'; -- 1 min
-- RUN THIS QUERY, COPY THE OUTPUT AND RUN
SELECT CONCAT('SET @myStatus = ''''; CALL tigrillo.tigrilloProvisionAnynet(''', ICCID, ''',NULL,@myStatus); SELECT @myStatus;') FROM Provide;
Comments