labtech Database

Parameters

Name	Type	Mode

Definition

BEGIN
/*Created as part of SCRT-439, to handle issues with the CW Cloud team restarting MySQL nightly.
This sproc needs to be set to run when MySQL is started.
When MySQL restarts, the auto increment value for a table is reset to one greater than the max of the ids in the table.
For some of our tables, we clear the table, but keep a copy of that record's id in a history table.
On startup, we need to set auto_increment for those tables, which gets reset to 1 if the table is empty, so we don't repeat ids stored in the history tables.
We use one greater than the max value in the history table to determine what the starting point for the ids should be.
ALTER TABLE statements don't accept subqueries or variables, so we use a prepared statement.
*/
SET @h_CommandsMaxId = (SELECT IFNULL(MAX(CmdID),0)+1 FROM h_Commands);
SET @sql = CONCAT('ALTER TABLE `commands` AUTO_INCREMENT = ', @h_CommandsMaxId, ';');
PREPARE c FROM @sql;
EXECUTE c;
SET @h_probeCommandsMaxId = (SELECT IFNULL(MAX(CmdID),0)+1 FROM h_ProbeCommands);
SET @sql = CONCAT('ALTER TABLE `ProbeCommands` AUTO_INCREMENT = ', @h_probeCommandsMaxId, ';');
PREPARE pc FROM @sql;
EXECUTE pc;
SET @h_pendingScriptsMaxId = (SELECT IFNULL(MAX(PendingScriptID),0)+1 FROM h_PendingScripts);
SET @sql = CONCAT('ALTER TABLE `PendingScripts` AUTO_INCREMENT = ', @h_pendingScriptsMaxId, ';');
PREPARE ps FROM @sql;
EXECUTE ps;
END

sp_UpdateAutoIncrements

Parameters

Definition