How do I remove duplicate rows from the queue_log table?

2011-11-24
If more than one instance of qloaderd are running concurrently, this will lead to duplicate entries within the queue_log table. These entries must be removed by using the following procedure (requires MySQL 5):

1. Stop all qloaderd's - you should see no data being appended

2. Create a new table called queue_log_b that has the same definition as your current queue_log table:


CREATE TABLE `queue_log_b` (
`partition` varchar( 20 ) NOT NULL default '',
`time_id` int( 11 ) unsigned NOT NULL default '0',
`call_id` varchar( 30 ) NOT NULL default '',
`queue` varchar( 30 ) NOT NULL default '',
`agent` varchar( 30 ) NOT NULL default '',
`verb` varchar( 30 ) NOT NULL default '',
`data1` varchar( 200 ) NOT NULL default '',
`data2` varchar( 200 ) NOT NULL default '',
`data3` varchar( 200 ) NOT NULL default '',
`data4` varchar( 200 ) NOT NULL default '',
`data5` varchar( 200 ) NOT NULL default '',
`serverid` varchar( 10 ) NOT NULL default '',
`unique_row_count` int( 10 ) unsigned NOT NULL AUTO_INCREMENT ,
KEY `idx_sel` ( `partition` , `time_id` , `queue` ( 2 ) ) ,
KEY `partizione_b` ( `partition` , `time_id` , `unique_row_count` ) ,
KEY `by_hotdesk` ( `partition` ( 5 ) , `verb` ( 5 ) , `time_id` )
) ENGINE = MYISAM DEFAULT CHARSET = latin1;



3. Copy all data from queue_log to queue_log_b


INSERT INTO queue_log_b
SELECT *
FROM queue_log



4. Delete the queue_log table


TRUNCATE TABLE queue_log


5. Copy all unique rows back to queue_log


INSERT INTO queue_log (
SELECT `partition`, `time_id`, `call_id`, `queue`, `agent`,
`verb`, `data1`, `data2`, `data3`, `data4`, `data5`,
`serverid`, MIN(`unique_row_count`)
FROM queue_log_b
GROUP BY `partition`, `time_id`, `call_id`, `queue`,
`agent`, `verb`, `data1`, `data2`, `data3`,
`data4`, `data5`, `serverid`
ORDER BY time_id, MIN(unique_row_count)
)



this may take a while.

6. Restart ONE instance of qloaderd.

7. Do not forget to CLEANUP the temporary table:


DROP table queue_log_b;

Permalink - Back to FAQs

QueueMetrics Training