Cookbook Chapter 8: Misc Troubleshooting
Relationship table is missing
- You want to create a new relationship type for a specific combination of records (for instance occurrences and collections) but the table does not show up in the leftmost column under Manage > Administration > Relationship Types.
- The error is caused by a missing hierarchy root. If you've updated your database from previous versions, it's possible that the relationship table was added at some point but the system failed to create the corresponding hierarchy root record in the relationship types table. That record is needed to build the list of tables in the left column of the relationship type list.
The solution is relatively simple: The record must be created by hand using a mysql query. First you'll have to find out the table number for the missing table. To do that, take a look at datamodel.conf and look for the name of your table. There will be a line that assigns a 2 or 3-digit number to that table name, like so:
"ca_occurrences_x_collections" = 68,
Now create a backup of your database!
Then, using that number (in our case 68), you're going to run the following query on your mysql server. How you do that depends on your setup. Some users will have web tools like PHPMyAdmin at their disposal, some will have to use the MySQL command line or some other query tool.
INSERT INTO `ca_relationship_types` (`parent_id`, `sub_type_left_id`, `sub_type_right_id`, `hier_left`, `hier_right`, `table_num`, `type_code`, `rank`, `is_default`) VALUES (NULL,NULL,NULL,1.00000000000000000000,4294967296.00000000000000000000,68,'root_for_68',10,0);
Note that if you're doing this for a different table, you'll want to change the number in two places: In the table_num and type_code columns. The query can be a little hard to read so make sure you change the right numbers.
After you've run the query, the table should show up in the list!