[Spce-user] Adding peer gives 'Duplicate entry error

[Andrew Pogrebennyk]

Hi Gerry,

ok correct - please change select to delete now:

mysql kamailio -e "DELETE FROM peer_preferences WHERE uuid NOT IN
(SELECT flags FROM lcr_gw);"

Regards,
Andrew

On 03/07/2014 01:13 PM, gerry kernan wrote:
> hi Andrew,
> 
> I ran that command , the output was as below, but when I try and add the new peer server I still get the error. was that command supposed to change a value in the DB
> 
> 
> +----+------+----------+--------+--------------------+------+------------------------+---------------------+
> | id | uuid | username | domain | attribute          | type | value                  | last_modified       |
> +----+------+----------+--------+--------------------+------+------------------------+---------------------+
> | 18 | 2    | 0        |        | sst_enable         |    0 | no                     | 0000-00-00 00:00:00 |
> | 19 | 2    | 0        |        | sst_refresh_method |    0 | UPDATE_FALLBACK_INVITE | 0000-00-00 00:00:00 |
> | 20 | 2    | 0        |        | outbound_from_user |    0 | upn                    | 0000-00-00 00:00:00 |
> | 21 | 2    | 0        |        | outbound_pai_user  |    0 | npn                    | 0000-00-00 00:00:00 |
> | 22 | 2    | 0        |        | use_rtpproxy       |    0 | ice_strip_candidates   | 0000-00-00 00:00:00 |
> | 23 | 2    | 0        |        | srtp_transcoding   |    0 | transparent            | 0000-00-00 00:00:00 |
> | 24 | 2    | 0        |        | rtcp_feedback      |    0 | transparent            | 0000-00-00 00:00:00 |
> | 25 | 2    | 0        |        | ua_header_mode     |    0 | strip                  | 0000-00-00 00:00:00 |
> | 26 | 2    | 0        |        | ipv46_for_rtpproxy |    0 | auto                   | 0000-00-00 00:00:00 |
> | 40 | 4    | 0        |        | sst_enable         |    0 | no                     | 0000-00-00 00:00:00 |
> | 41 | 4    | 0        |        | sst_refresh_method |    0 | UPDATE_FALLBACK_INVITE | 0000-00-00 00:00:00 |
> | 42 | 4    | 0        |        | outbound_from_user |    0 | upn                    | 0000-00-00 00:00:00 |
> | 43 | 4    | 0        |        | outbound_pai_user  |    0 | npn                    | 0000-00-00 00:00:00 |
> | 44 | 4    | 0        |        | use_rtpproxy       |    0 | ice_strip_candidates   | 0000-00-00 00:00:00 |
> | 45 | 4    | 0        |        | srtp_transcoding   |    0 | transparent            | 0000-00-00 00:00:00 |
> | 46 | 4    | 0        |        | rtcp_feedback      |    0 | transparent            | 0000-00-00 00:00:00 |
> | 47 | 4    | 0        |        | ua_header_mode     |    0 | strip                  | 0000-00-00 00:00:00 |
> | 48 | 4    | 0        |        | ipv46_for_rtpproxy |    0 | auto                   | 0000-00-00 00:00:00 |
> | 55 | 4    | 0        |        | outbound_socket    |    0 | udp:192.10.10.11:5060  | 0000-00-00 00:00:00 |
> +----+------+----------+--------+--------------------+------+------------------------+---------------------+
> 
> -----Original Message-----
> From: spce-user-bounces at lists.sipwise.com [mailto:spce-user-bounces at lists.sipwise.com] On Behalf Of Andrew Pogrebennyk
> Sent: 07 March 2014 12:05
> To: John Murray
> Cc: Spce-user
> Subject: Re: [Spce-user] Adding peer gives 'Duplicate entry error
> 
> Hi John,
> 
> On 03/06/2014 05:16 PM, John Murray wrote:
>> We have 5 peers and there is no duplication. Looking at the database 
>> the gateway id go only to 7 so the entry of '113' seems not to match.
>>
>> There is a trigger on the provisioning. voip_peer_hosts table that 
>> does an insert into provisioning.voip_peer_preferences which does have 
>> an id of 112 so maybe that is the problem.
> 
> That's right - there's an old issue where mysql triggers do not work for cascaded delete. We fixed the trigger itself back in December in ngcp-db-schema 3.1.5, but forgot to clean up the stale records from kamailio DB. Will fix it today.
> 
> In the meantime, you can fix your DB by running:
> mysql -e "use kamailio; select * from peer_preferences where uuid not in (select flags from lcr_gw);"
> 
> Thanks for the report!
> Andrew
>