Got 3 identical SQL records for a single call

[Bruno GUIBERT - Telnowedge]

Need to know how to purge   SQL database of duplicated entry

merci

[QueueMetrics]

Do the following (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:

Code: [Select]

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( 30 ) NOT NULL default '',
`data2` varchar( 30 ) NOT NULL default '',
`data3` varchar( 30 ) NOT NULL default '',
`data4` varchar( 30 ) 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

Code: [Select]

INSERT INTO queue_log_b
SELECT *
FROM queue_log

4. Delete the queue_log table

Code: [Select]

TRUNCATE TABLE queue_log
5. Copy all unique rows back to queue_log

Code: [Select]

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

this may take a while.

6. Restart ONE instance of qloaderd.

[Bruno GUIBERT - Telnowedge]

Be carefull : some QM queue_log table doesn't include (yet) serverid field 

Code: [Select]


describe queue_log;
+------------------+------------------+------+-----+---------+----------------+
| Field            | Type             | Null | Key | Default | Extra          |
+------------------+------------------+------+-----+---------+----------------+
| partition        | char(20)         | NO   | MUL |         |                |
| time_id          | int(11) unsigned | NO   |     | 0       |                |
| call_id          | char(30)         | NO   |     |         |                |
| queue            | char(30)         | NO   |     |         |                |
| agent            | char(30)         | NO   |     |         |                |
| verb             | char(30)         | NO   |     |         |                |
| data1            | char(30)         | NO   |     |         |                |
| data2            | char(30)         | NO   |     |         |                |
| data3            | char(30)         | NO   |     |         |                |
| data4            | char(30)         | NO   |     |         |                |
| unique_row_count | int(11)          | NO   | PRI | NULL    | auto_increment |
+------------------+------------------+------+-----+---------+----------------+
the SQL statements are

CREATE

Code: [Select]

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( 30 ) NOT NULL default '', `data2` varchar( 30 ) NOT NULL default '',
       `data3` varchar( 30 ) NOT NULL default '', `data4` varchar( 30 ) 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
;



COPY

Code: [Select]

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


PLS ADD CLEANUP

Code: [Select]

DROP  table queue_log_b;


[QueueMetrics]

You had a funny mistake - nice we found the problem!