Changing the tree id in TNG

Post Reply
steven
Posts: 133
Joined: Sun Oct 01, 2017 3:08 pm

Changing the tree id in TNG

Post by steven »

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 fix the problem instead of potentially getting errors in the future.

Unfortunately when you change the tree id, all search engine links like Google, Bing, MSN, Apple, Yahoo, etc. are broken. Those searching the internet and finding someone on your site will receive Error 404 not found instead of a TNG page. If you use Apache, you can add a rewrite rule to redirect traffic using the new tree name. This allows bots to update the URLs, minimizing SEO issues. Add the htaccess file below to your site or copy the code to your existing htaccess file. Change the tree ID names for the old_tree_id and new_tree_id before using the code or htaccess file.

Code: Select all

RewriteCond %{QUERY_STRING} ^(.*)tree=old_tree_id(.*)$ [NC]
RewriteRule ^(.*)$ $1?%1tree=new_tree_id [R=301,L]
htaccess.zip
(289 Bytes) Downloaded 603 times



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 error message is “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* (Requires two queries)
  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.

* Now run a second query on the tng_users table for people assigned to a specific tree. The query will update the mygedcom column in the users table.

Code: Select all

UPDATE tng_users SET mygedcom = 'tree1' WHERE mygedcom = 'tree-1';
Once you've finished, 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.

If everything is working you can delete the old user account and old database.
Last edited by steven on Thu Apr 23, 2020 8:57 am, edited 1 time in total.
Post Reply