0

I have a mysql database which is having one table for geo location and saves latitude and longitude values of users. The fields are as followed

CREATE TABLE `USER_LOCATION` (
  `LATITUDE` float NOT NULL DEFAULT '0',
  `LONGITUDE` float NOT NULL DEFAULT '0',
  `STATE` varchar(50) NOT NULL,
  `COUNTRY` varchar(255) DEFAULT NULL,
  `ADDRESS` varchar(255) DEFAULT NULL,
  `CITY` varchar(45) DEFAULT NULL,
  `DISTRICT` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`LATITUDE`,`LONGITUDE`),
  KEY `latlong` (`LATITUDE`,`LONGITUDE`),
  KEY `longlat` (`LONGITUDE`,`LATITUDE`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Problem 1:- Some time fields get duplicate value and the insertion happens successfully.

Problem 2:- If I have taken mysqldump of data and try to restore with the same duplicate rows , then I receive duplicate PRIMARY KEY error like this.

ERROR 1062 (23000): Duplicate entry '17.4273-78.3316' for key 'PRIMARY'

Now I am not able to understand that why the first problem happens and why this is detected in problem 2.

  • Can you run a SELECT COUNT(*) FROM user_location l1 JOIN user_location l2 ON l1.latitude = l2.latitude AND l1.longitude = l2.longitude? And why do you have a PK of latitude and longitude and also a further KEY with the exact same fields? – Vérace May 10 '16 at 12:58
  • the error occurs when data is having same longitude and latitude value – Suyash Jain May 10 '16 at 13:54
  • I don't understand - you should only get a PK violation if there are two records with both the same latitude and longitude - there's no logical reason that a point can't have the same lat and long. – Vérace May 10 '16 at 14:02
  • That's the problem buddy..I don't know how it got inserted – Suyash Jain May 10 '16 at 14:06
  • Can you give me the result of my COUNTquery? – Vérace May 10 '16 at 14:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.