Changing the tree id in TNG

Steve
Posts: 76
Joined: Sun Oct 01, 2017 3:08 pm

Changing the tree id in TNG

Post by Steve » Sat Aug 24, 2019 10:20 am

I DO NOT recommend changing the tree id unless you made a mistake like I did. It is best to be familiar with phpMyAdmin but, if you're not, the instructions below are for modifying a copy of the database so the original is left unchanged. That said, when I first installed TNG, years ago, I used tree-1 for the tree ID because I didn't know any better. It worked although hyphens are not recommended and can cause errors. I wanted to install a fresh v12.1 but TNG no longer allows hyphens in the tree ID. This became a big problem because I could not enter tree-1 from the TNG readme.html page. So I decided to bite the bullet and fix the problem.

Unfortunately when you change the tree id, all search engine links like Google, Bing, MSN, Apple, Yahoo are broken. Those searching the internet and finding your site will receive Error 404 not found instead of a TNG page. Since the tree id occurs at or near the end of the URL there is no easy way to correct it using .htaccess. If you have a new site it is better to be patient and let the search engines correct the links. If you don't have a new site or want to capture the 404 errors, I've include a MOD at the bottom of this page that displays a friendly message with a link to search for people, families or sources depending on the error.

As mentioned earlier, it is best to work with a copy so to begin, create a new user and database. If you are not familiar with this procedure, here is a link to Instructions for creating the user and database. Use phpMyAdmin to copy the structure and data into the new empty database. The new database will still have the old tree id but these will be changed using SQL queries.

After you create the new empty database, select the existing TNG database from the left database column. Note the database you are copying from is listed at the top of the window.
 
Image
 
Select the Operations tab from the top of the Window.
 
Image
 
In the Copy Database to section, enter the new database name, select the Structure and Data radio button, uncheck CREATE DATABASE before copying. Leave Add AUTO INCREMENT, Add Constraints and Adjust Priviledges checked. Then select the Go button In the Copy Database to section.
 
Image
 

I'm running a Synology NAS that that still uses php5.6 for phpMyAdmin which gave an error after the copy was complete, although I did not lose any data. If the column count value doesn't match the value count in row 1, click to dismiss the error. If a different error occurs, check the code number to determine the cause with either phpMyAdmin, MySQL or MariaDB.
 
Image
 
In any case, if you receive an error, select the new database to make sure the tables were copied. Then compare the number of rows in each table in the new database with the number of rows in each table in the original database. The row counts should match.

To change the tree id use phpMyAdmin to run queries on each of the tables below in the new database.
Note: if a table is empty you do not need to run the query on it.
  1. tng_addresses
  2. tng_associations
  3. tng_branches
  4. tng_branchlinks
  5. tng_children
  6. tng_citations
  7. tng_dna_groups
  8. tng_dna_links
  9. tng_dna_tests
  10. tng_events
  11. tng_families
  12. tng_media
  13. tng_medialinks
  14. tng_mostwanted
  15. tng_notelinks
  16. tng_people
  17. tng_places
  18. tng_repositories
  19. tng_saveimport
  20. tng_sources
  21. tng_temp_events
  22. tng_trees
  23. tng_users
  24. tng_xnotes
To run a query in phpMyAdmin, open the database and select the table to modify. Then select SQL from the menu tabs at the top. Notice the top of the window displays the database and table that is open and the Run SQL query box shows the database and table info as well. Always verify you are accessing the correct database and table before making changes. Remember there is NO UNDO COMMAND if you run an incorrect query or run the query on the wrong database.
 
Image
 
The SQL query in this example is a conditional query which means only trees named tree-1 will be changed. So if your database has more than one tree, any tree ids not matching tree-1 will not be changed. To run the query replace the default SELECT FROM line in the SQL window with that shown in the code below.

Code: Select all

UPDATE tng_addresses SET gedcom = 'tree1' WHERE gedcom = 'tree-1';
UPDATE is the query command that changes a table. In this example the table is tng_addresses.
SET gedcom = 'tree1' is the query command that specifies the column to change and the value to place in that column. In this case the value tree1 will be placed in the gedcom column.
WHERE gedcom = 'tree-1' is the conditional command. Only rows containing tree-1 will be changed.
 
Image
 
Select Go and run the query which will change the value for the 'gedcom' column in this table. Repeat this procedure for each table listed above that is not empty which will be most of them. Make sure you enter the correct table name for each query that is run.

Once you've changed the tree id you will need to connect TNG to the new database. To connect, edit the TNG config.php file and change $database_name, $database_username and $database_password to match those of the new database just created. If you use a default tree change $defaulttree = to the new tree ID you entered in the SQL query.

At this point, when TNG is restarted, it should connect to the new database with the new tree name. To verify the new tree name is in use, search for an individual and go to their page. In the browser URL window you should see &tree= and the new tree name. Then go to their family group sheet and verify the tree id for the family as well. Media links to anyone in this tree will be retained as well. If you have any problems you can always reconnect TNG to the old database.

link_not_found_v10.1.0.2.zip
Created 9/2/19
You do not have the required permissions to view the files attached to this post.