Question GEDCOM Import Error - Duplicate entry

Hi all..
Right off the bat I am going to say that this GEDCOM file is full of errors of one sort or another, and with some 63100 Individuals plus associated data some 1.6million lines of 'code'.. It has come out of TNG (not poking anything), and I am only working on it for my benefit really, but hopefully to go back to this chap and help him sort his stuff out..

The Error I am getting is 
I have tried a few times to Import, and each time it seems to progress further after I delete a duplicate.. But I think that is an anomaly.. I've attached what I think I should be looking for in the GEDCOM given this database info, but there is so many 'lake city' entries its a bit overwhelming..
 

Can anyone point me somewhere where I might be able to find this 'duplicate' PLEASE!? and Thank You..

 

The issue is with an insert into table wt_placelinks. The error message states that the primary key must have a unique value but there's already a row for this primary key. In this case the primary is compounded of three values:

• pl_file is a number representing the tree within webtrees. Here that's 5.
• pl_gid is the Gedcom ID or XREF of the record which refers to a place. Here that's I6752.
• pl_p_id is the sequence number of the place to which I6752 refers. Here that's those are 68448, 71328 and 71856.

So you can:

and that should be the set of possible place records. But it's already known that the issue comes from a GEDCOM record of I6752.

There is a chance that this GEDCOM file contains a record for both "i6752" and "I6752". Is that a different record, or the same? You tell me... You have the GEDCOM file, so you can find all occurrences of "0 @I6752@ INDI".

Whatever you and I think, I predict that webtrees does not process it very well. On a database level it tries to not make a distinction between upper case and lower case at all.

If you happen to have a development instance of webtrees running, then with a SQLite database you should not have this problem. If it is this problem. Big 'if'... It might be something completely different.

Could you share what's the raw GEDCOM content (especially the "2 PLAC" fragments) of I6752? It might also be caused by leading or trailing spaces in the place name in this record.

I've done a quick test with a GEDCOM file containing both i1 and I1. I was right that SQLite has no problem with that, but with MySql or MariaDB I got a slightly different error:
In other words, the XREF of the individual already triggers an error, but way before it comes to creating place links.

One possible cause less to check - back to the drawing board...

Bert,

You are amazing.. You hit it right on the head.. The Indi XREF was also a Family XREF.. And you also made me stop and look at the 'numbers' again.. I and 1 look too close to call in these situations.. I should have seen it.

Having worked through that and sorted the Family XREFs along with many other fixes, just coz I figured WTH, the GEDCOM imported.. Still there were errors that were ignored (wow I love WebTrees) and the data is quite crazy screwed, but NOW I can figure so much more out using great S/Ware..

And would you believe there were XREFs with @i1234@ lower case i as well.. AND a couple that had a space in there as well @I 01234@..

I know that the database the GEDCOM was created from (TNG S/Ware) has crazy Collations and uses MyISAM so perhaps that is also part of the issue..

Anyways, Bert THANK YOU yet again for your help.. I would have been still going in circles if not for you!

 

Actually, you found it yourself!
Who would have thought of that, a family with identical xref as an individual...

Interesting issues. All point pretty much exactly to the reasons webtrees switched to X….. for all these ids.

Anyone still clinging onto the old I, F S, N, M, R should take note.

I have a mixture of old I, F S, N, M, R and new X references but cannot see a way of converting or resequencing?
How to I go about this?

> All point pretty much exactly to the reasons webtrees switched to X….. for all these ids.

Thanks Kiwi. Exactly this.

> The Indi XREF was also a Family XREF

This is an interesting one. webtrees will kinda work OK when this happens. We don't have checks for this. Only that all records of a particular type must be unique.

There are a few places in the code that look for a record by its XREF. If more that one record type exists, we look for INDI first, then FAM, etc.

> And would you believe there were XREFs with @i1234@ lower case i as well.. AND a couple that had a space in there as well @I 01234@..

This is also interesting. webtrees was originally MySQL only. MySQL is case-insensitive by default. So it wasn't possible to have i123 and I123 in the same GEDCOM file. You'd get a duplicate error.

But now we support other databases (SQLite, Postgres, SQL-Server), which are all case-sensitive by default, we have the situation where we get different behaviour on different databases.

By the same token, if you create a link @i123@ to a record @I123@, it will just work. The error checker even looks out for this exact situtation.


However, at some point soon (probably the 2.3 release), I will change things to assume case-sensitivity - and links with mismatched upper/lower case will stop working - just like any other broken link.

Longer term, I want to change the collation in SQL to be case-sensitive, to match the other database engines. This will require a database abstraction and migration library. I've started this, but it is distinctly "non-trivial"

> I have a mixture of old I, F S, N, M, R and new X references but cannot see a way of converting or resequencing? How to I go about this?

Be aware that if you do this, any bookmarks, etc. created by your users will stop working.

Create a new (temporary) tree. Make it private.
Export your main tree to a GEDCOM file, and import it into this temporary tree.

Now, for the main tree, there is a "renumber" option.
It creates new XREFs for any record that has the same XREF as a record in another tree.

Since all your records now have duplicates (in your private/temporary tree), they will all be renumbered.

This includes any that already begin with X.

Then, just delete the temporary tree.

Nigel, I kinda didn't like the X factor thing to start with, but got it after a bit.. And most certainly after this one I really GOT IT! <Smile>

Greg, Thanks for the insight.. I do think the TNG database this GEDCOM came from was/is screwed up.. And some data input is out of place etc. Not sure how one can fiddle with XREFS except directly in GEDCOM and TNG does not allow that (TTBOMK).
Re your ongoing and updating of Database collations etc, OMG, way over my head. ANd it does sound like a huge job on its own..

What really impresses me about WebTrees, apart from the excellent support, is the continued improvement and keeping up to coding standards.

Thank you again Greg, and to ALL those who support Greg and his efforts with testing and coding help THANK YOU!
 

kiwi wrote:

Interesting issues. All point pretty much exactly to the reasons webtrees switched to X….. for all these ids.

Anyone still clinging onto the old I, F S, N, M, R should take note.

To be fair, this could also have happened with a single prefix being used for all entities but each having their own sequence: "0 @X123@ INDI" & "0 @X123@ FAM". I've read the GEDCOM specs again, and it implies that from the context (within a GEDCOM record) it should always be known what entity type a pointer points to. So these kind of duplicates seem to be not implicitly illegal.