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

Loway Logo

Copyright ©Loway 2018 · all rights reserved · Terms of service · Privacy policy

All trademarks, service marks, trade names, product names and logos appearing on the site are the property of their respective owners, including in some instances Loway. Any rights not expressly granted herein are reserved.

Facebook Twitter Linkedin Pinterest Google+ Tumblr Vimeo Youtube Slideshare RSS

Network Status: Network is OK