'Duplicate entry' when resolving the country in IP_LOCATION table in HA setup.

[Methma]

Description:
Duplicate entry for 'PRIMARY' key is coming when resolving the country in GEO_LOCATION_DATA.IP_LOCATION table [1].

ERROR {org.wso2.analytics.is.siddhi.extension.IpToCountryExtension} - Exception when resolving the country for given IP : 105.0.1.28 {org.wso2.analytics.is.siddhi.extension.IpToCountryExtension}
org.wso2.carbon.analytics.shared.geolocation.exception.GeoLocationResolverException: Error while saving the location to database
at org.wso2.carbon.analytics.shared.geolocation.impl.LocationResolverRdbms.saveLocation(LocationResolverRdbms.java:161)
at org.wso2.carbon.analytics.shared.geolocation.impl.LocationResolverRdbms.getLocationFromIp(LocationResolverRdbms.java:138)
at org.wso2.carbon.analytics.shared.geolocation.impl.LocationResolverRdbms.getLocation(LocationResolverRdbms.java:61)
at org.wso2.carbon.analytics.shared.geolocation.impl.GeoLocationResolverUDFWithImprovedCache.getCountry(GeoLocationResolverUDFWithImprovedCache.java:47)
...
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry '105.0.1.28' for key 'PRIMARY'
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)

It seems like this Duplicate entry for the primary key is coming when inserting data to IP_LOCATION table using the following SQL [2].
SQL_INSERT_LOCATION_INTO_TABLE = "INSERT INTO IP_LOCATION (ip,country_name," +"city_name) VALUES (?,?,?)";

And IP is the primary key for the IP_LOCATION table.

CREATE TABLE IP_LOCATION(ipvarchar(100) NOT NULL,country_namevarchar(200) DEFAULT NULL,city_name varchar(200) DEFAULT NULL, PRIMARY KEY (ip));

We can check whether the duplicate entry exception is there in a try-catch [3] and ignore that as the IP location is already inserted from the other node.

[1].

shared-analytics/components/spark-udf/org.wso2.carbon.analytics.shared.geolocation.udf/src/main/java/org/wso2/carbon/analytics/shared/geolocation/impl/LocationResolverRdbms.java

Line 119 in 35ba7db

private Location getLocationFromIp(String ipAddress) throws GeoLocationResolverException {

[2].

shared-analytics/components/spark-udf/org.wso2.carbon.analytics.shared.geolocation.udf/src/main/java/org/wso2/carbon/analytics/shared/geolocation/impl/LocationResolverRdbms.java

Line 154 in 35ba7db

statement = connection.prepareStatement(SQL_INSERT_LOCATION_INTO_TABLE);

[3].

shared-analytics/components/spark-udf/org.wso2.carbon.analytics.shared.geolocation.udf/src/main/java/org/wso2/carbon/analytics/shared/geolocation/impl/LocationResolverRdbms.java

Line 138 in 35ba7db

saveLocation(location, connection);

Suggested Labels:
IS-Analytics, HA

Affected Product Version:
IS-Analytics-5.3.0

OS, DB, other environment details and versions:
DB- MySQL

Steps to reproduce:

1. Configure an IS Analytics HA deployment.
2. Invoke from different IPs in a short period.
   (This might not reproduce always)