Installation issues

[dleehr]

I installed FossilCalibrations on a CentOS virtual machine, via the wiki instructions

I'm using CentOS 6.5 64-bit (vagrant: chef/centos-6.5)

Installed via yum:

• httpd 2.2.15
• mysql-server 5.1.73
• php, php-mysql 5.3.3
• git 1.7.1
• man 1.6f

Here are the issues I encountered:

1. In general, I feel "Configure apache" or "Configure MySQL" are a little vague. While it's unwise to dictate exactly which file to edit, suggesting /etc/httpd/conf.d/ or /etc/my.cnf or is going to cover most of the cases.
2. Step 4 (SSL config) is confusing. I assume the DocumentRoot should be the repo root as in Step 3, but it's a literal instead of a placeholder.
3. Database installation confusion. The db directory contains .sql scripts and a Readme for installing a database, but these are not mentioned on the wiki installation guide. The guide suggests to dump/load data from a dev database, but I think that a fresh installation should be supported.
4. Installing database through scripts. I was able to step through the scripts in order (FossilCalibration.sql, followed by all the numbered database-migration scripts and the two procedures scripts). This installed a (mostly) working database. The readme here suggests looking for TODO items in the scripts, but most of these are irrelevant (except the NCBI files)
5. database-migration-002.sql loads data from NCBI files. The script has a hard-coded file path for these files, and they must be downloaded first manually. It would be nice if the download and installation of these files were scripted. For that matter, the entire database installation could be a single script. This file was also missing a semicolon (PR Add missing semicolon in database-migration-002 #11).
6. The application expects the database to be named FossilCalibration but this is not mentioned in the guides. Additionally, it's not clear what database grants/privileges are required. I created a fossils user with all privileges on FossilCalibration. This seems to be sufficient to run the application, but installing FossilCalibration.sql required SUPER privileges, so I had to drop the database and start over with root.
7. The $SITEINFO['servername'] variable in Site.conf.example is actually used as the MySQL hostname, but this is unclear. (PR Clarify meaning of $SITEINFO['servername'] #12)
8. Some of the php scripts use short tags. By default, php.ini disables short tags. Edited Wiki.
9. After getting my sample database up and running, Browse.php doesn't work because $targetNodeInfo is null on line 122 and clobbers $nodeMultitreeID, causing bad SQL in 137-145. Seems I'm missing some data?
10. It seems the mysqld max_sp_recursion_depth setting might be required so that stored procedures can recurse (based on the documentation)? I can't tell if it's working or not. I set it to 255.

I think the installation is mostly working, would be happy to provide the VM image (as a vagrant box or virtualbox image) to check it. It seems the database installation is the biggest issue.

[jimallman]

Thanks @dleehr ! I've incorporated these suggestions into the latest wiki page. There are still a few loose ends (keyed by item numbers above):

(3, 4) The database migrations and other stuff in db/ are obviously cumbersome. I'd like to replace this with the "skeleton" db FossilCalibration_skel.sql, if it works well for you. But I have reservations, because the dump files are harder to read and edit than the smaller source files currently in the repo. Do you know of best practices for a situation like this?

(5) The NCBI install/update should ultimately be a repeatable task, available from the site's admin dashboard. For now, I've added a new issue (#14) calling for an easier script for this.

(9) Hm, it's possible that I'm not checking for an empty database (ie, no calibrations) since that's not at all expected. Please try again with the skeleton database and see if this is repeated.

(10) Regarding max_sp_recursion_depth, there's a very clear error message if we exceed the stated recursion depth, and it is 0 by default. I'd forgotten that I'm setting this as needed for each procedure, then resetting it when we're done. (This is peculiar, but is also apparently a recommended practice. It's certainly working here.) I've updated the Installation instructions to reflect this.

[dleehr]

Tried to load the skel database, but it fails with

    $ mysql -u root -p FossilCalibration < FossilCalibration_skel.sql 
    Enter password: 
    ERROR 1146 (42S02) at line 4010: Table 'FossilCalibration.Link_CalibrationPair' doesn't exist

[jimallman]

See #15 for revised skeleton data and how-to's.

[dleehr]

The revised skeleton mentioned in #15 loaded without error. After loading it, I saw SQL errors when browsing. Initially I suspected #30 but these disappeared after performing site maintenance, so the site maintenance steps should be added to the wiki.

[jimallman]

Thanks @dleehr . I suspect these errors are the result of having empty tables, which is not a normal condition. I'll add a note about running site maintenance tasks to the wiki.

[jimallman]

@dleehr, please take a look at the new step 10 in the Installation page and let me know if this accurately reflects your experience.

[dleehr]

Yep that's good. Isn't the NCBI Taxonomy included in the skeleton db though?

[jimallman]

Yes, there's currently a particular version of the NCBI taxonomy in the database. It changes very slowly, so this will become increasingly stale over time. Eventually there will be pressure to upgrade to newer versions, but that could be a long time coming.

[hlapp]

Actually there are thousands of taxonomy nodes that change every month. Are
you assuming that you only need a small subset of the taxonomy that changes
rather slowly?

[jimallman]

Thanks @hlapp, I didn't realize there was that much churn. You'll recall that FCD uses the NCBI taxonomy as a "way-finding" backbone for finding calibrated nodes. It's mainly useful because of its familiar organization of the largest clades, so I think changes near the tips would have little impact, and might help one searcher at the expense of another. But @pdpolly is a better person to address this question.

[pdpolly]

Am I correct that the FCD is coded so that the NCBI taxonomy can be refreshed fairly easily? I've never paid enough attention to it to know what proportion changes with each public dump, but I did look when we started to see that it is refreshed frequently. The files currently available at ftp://ftp.ncbi.nlm.nih.gov/pub/taxonomy/ all have date stamps yesterday and today. I suspect that the frequency of node changes in the NCBI taxonomy is inversely correlated with ease of fossil calibration, but I don't have any evidence for that other than intuition.

[jimallman]

FYI i - I'm moving further discussion of NCBI updates to a dedicated issue #14.