Web based family history software

This Help forum is for issues relates to the latest release (1.6.x). For issues related to beta or github version please use their own Help forum.
Before asking for help please read "How to request help" by clicking on that tab above here.

Question Making DB changes directly in MySQL

  • kavlito
  • Topic Author
  • Visitor
  • Visitor
9 years 4 months ago - 9 years 4 months ago #1 by kavlito
Making DB changes directly in MySQL was created by kavlito
Been using webtrees for a couple of weeks now. Have an issue where I created a Family and added 2 children. When I view the children as individuals, they appear with their parents as expected. However, when viewing the parents, only one of the children appeared.

That's not the subject of this post, but the reason why I'm posting the following:

After trying to determine why only one child was appearing in the family of the parents, I decided to check the Db directly. I was looking at the wt_families table, specifically for family F3. Looking at the f_gedcom data, I noticed there was only 1 child. So, I edited the record in PHPMyAdmin and copy and pasted the 1 child line and changed the I1 to I12 for the appropriate child. I clicked Go and the record updated successfully.

Then I went back into webtrees and opened the Family List for F3. Upon opening the record, I received 4 messages indicating that 4 Links to all relationships had been deleted. See attached file.

What is the deal here? Is it not possible to change the records directly in the DB?

Thanks in advance.

Attachments:
Last edit: 9 years 4 months ago by kavlito.

Please Log in or Create an account to join the conversation.

More
9 years 4 months ago #2 by fisharebest
Replied by fisharebest on topic Making DB changes directly in MySQL

What is the deal here? Is it not possible to change the records directly in the DB?


1) As well as copies of the original GEDCOM data (in tables such as wt_families and wt_individuals), webtrees maintains lots of other tables such as wt_link, wt_name, wt_place with data extracted from the GEDCOM data. If you update the genealogy tables, you MUST ALSO UPDATE all the other tables. Note that if you use the GEDCOM editor built into webtrees (the "edit raw gedcom" option), then everything is taken care of for you.

Remember also that family->individual links always have a reverse individual->family link, so you must edit the family record AND ALSO the corresponding individual record.

2) The line endings in the wt_individuals and wt_families tables are normalised to UNIX format (LF). This is for performance. We convert the line endings once only (on import), rather than every time we try to search/view data. BUT! PhpMyAdmin silently converts your edits into Windows format (CRLF). This effectively converts your data from

1 FAMC @I123@

to

1 FAMC @I123@<CR>

which is not recognised as a valid link (beginning and ending in "@"), and is hence removed.

Greg Roach - greg@subaqua.co.uk - @fisharebest@phpc.social - fisharebest.webtrees.net

Please Log in or Create an account to join the conversation.

  • mbaker9105
  • Visitor
  • Visitor
9 years 4 months ago #3 by mbaker9105
Replied by mbaker9105 on topic Making DB changes directly in MySQL
yeah, I'd heard long ago that fiddling with an SQL manually is probably the LAST thing you want to do, there's just too much other data and other tables that are connected and really requires you to know what your are doing. I'm sure Greg and the rest of the team are intimately familiar by now with all the coding, but the rest of us schmucks are better off doing things like messing with the GEDCOM, etc., and let the program parse all of the changes properly into the database. I know just enough to be able to figure out where to find the tables and try to figure out from the PHP coding what tables are messed with when a change is made, and only once or twice did I even attempt to work something by manually fiddling with the tables, and that didn't go so well :)

Then again, if you have proper backups, fiddle away, but something you change now might not show up as problem until later when that particular data is actually called. But that's just me, I get nervous enough if I even mess with the GEDCOM :)

Please Log in or Create an account to join the conversation.

More
9 years 4 months ago - 9 years 4 months ago #4 by Jackie
Replied by Jackie on topic Making DB changes directly in MySQL

kavlito wrote: Been using webtrees for a couple of weeks now. ..
After trying to determine why only one child was appearing in the family of the parents, I decided to check the Db directly...


Hello,

For those who are new to webtrees..
webtrees offers tools to help you find out errors and where they came from.

In Administration -> Family trees -> Check for errors (that will search for GEDCOM errors and will list them)
In Administration -> Family trees -> Changes log (a log of all modifications made with dates, status, record, old data, new data, etc.)

Easy to locate the error and then, easy to correct it by using
Top Menu -> Edit -> Edit raw GEDCOM
or by using the usual *Edit interface*.

EDIT : other details in the Wiki page *Family tree maintenance tips*
wiki.webtrees.net/en/Family_tree_maintenance_tips
Last edit: 9 years 4 months ago by Jackie.

Please Log in or Create an account to join the conversation.

  • kavlito
  • Topic Author
  • Visitor
  • Visitor
9 years 4 months ago #5 by kavlito
Replied by kavlito on topic Making DB changes directly in MySQL
Thank you fisharebest and Jackie, your responses helped me fix my file. Will definitely NOT be using PHPMyAdmin to edit this DB again.

Using the Check for Errors function pointed out that the I12 record linked to an f3 (lowercase f) record, which did not exist. Using the Edit raw GEDCOM function on the I12 record, I made the corrections and changed to (capital) F, and fixed the file.

The only record I initially attempted to change in PHPMyAdmin was the F3 record. How the I12 record was linked to a lowercase f3 record is beyond me as all I had done up to this point was to add records through webtrees interface.

Thanks again to all!

Please Log in or Create an account to join the conversation.

Powered by Kunena Forum
}