On my site there is are duplicate entries in table [location_instance]. How did that can happen?
To find the duplicate entries, execute:
SELECT nid, vid, uid, genid, lid FROM `location_instance` GROUP BY nid, vid, uid, genid, lid HAVING COUNT(*) > 1;
+------+------+-----+-------+------+
| nid | vid | uid | genid | lid |
+------+------+-----+-------+------+
| 4684 | 4684 | 0 | | 1792 |
| 4685 | 4685 | 0 | | 1794 |
+------+------+-----+-------+------+
2 rows in set (0.09 sec)
To see the duplicates:
mysql> SELECT * FROM `location_instance` WHERE nid in (4684, 4685);
+------+------+-----+-------+------+
| nid | vid | uid | genid | lid |
+------+------+-----+-------+------+
| 4684 | 4684 | 0 | | 1792 |
| 4684 | 4684 | 0 | | 1792 |
| 4685 | 4685 | 0 | | 1794 |
| 4685 | 4685 | 0 | | 1794 |
+------+------+-----+-------+------+
4 rows in set (0.01 sec)
As i see location_instance table has no primary key - so i think that key must be added.
And also in location_save_locations() function must have check if the same record already exists before inserting new one.
Comments
Comment #1
EvanDonovan commentedI have experienced this recently as well. It appears that it is possible for duplicate rows to be created when saving a node; however, I don't know the conditions under which this happens.
It seems like custom scripts can also trigger duplication, which is preventing me from writing an automatic script to update the locations on my site based on reverse geocoding results.
In regards to fixing this, should the primary key be nid+vid+lid, or should it be all the fields together? I don't actually know what the uid and genid are used for. The genid always seems to be blank.
Comment #2
sigent commentedi encountered the same problem (Location CCK field was used) for nodes.
what i've done:
- set Locations for Node (not CCK) as 1 entry max
- removed Location CCK (all the Locations remain in DB)
- removed duplicate (by NID) entries in DB (by hand) - table 'location_instance'
it seems the problem is gone.
(i've tried to test adding/removing locations. everything is fine. these is no duplications anymore).
PS. i guess Locations doesn't works well as CKK instance.
Comment #3
scottrigby@see #391160-59: CCK/Views: location_instance fix