Changing the tree id in TNG
Posted: 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 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.
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](/web_images/tng/settings_information/dbcopy01.JPG)
Select the Operations tab from the top of the Window.
![Image](/web_images/tng/settings_information/dbcopy02.JPG)
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](/web_images/tng/settings_information/dbcopy03.JPG)
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](/web_images/tng/settings_information/sql_error01.JPG)
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.
![Image](/web_images/tng/settings_information/chid01.JPG)
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.
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](/web_images/tng/settings_information/chid02.JPG)
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.
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.
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]
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.
Select the Operations tab from the top of the Window.
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.
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.
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.
- tng_addresses
- tng_associations
- tng_branches
- tng_branchlinks
- tng_children
- tng_citations
- tng_dna_groups
- tng_dna_links
- tng_dna_tests
- tng_events
- tng_families
- tng_media
- tng_medialinks
- tng_mostwanted
- tng_notelinks
- tng_people
- tng_places
- tng_repositories
- tng_saveimport
- tng_sources
- tng_temp_events
- tng_trees
- tng_users* (Requires two queries)
- tng_xnotes
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';
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.
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';
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.