Using an ODBC connection and an open port (i.e. firewall exception) to MySQL it is possible to connect Specify6 from a remote (off of the local network) machine to a MySQL Specify database. Only Specify6 and Java JDK were installed on the remote machine. Just pointed the server name at login to the IP address of the host machine. Same process was successful with a VPN login. This does not require a firewall exception for MySQL, but does require a VPN account and VPN client software must be installed on the remote machine. Although successful in connecting to the database, response time was abysmally slow. Will be working with IT staff to move host DB to a better server machine and check for faster response time, but waiting for release of Specify6.1...where is it?
Respectfully submitted,
H
Re: Remote connection
We are aware of performance issues over a non-local network and have started to look into these issues. Most all performance issues have to do with Hibernate (our ORM layer) which at times can create larger than necessary queries. We found and fixed an issue with tree loading that provided a 5X improvement and should impact other queries as well. The 6.0 release (as well as 6.1) was focused on individual installs which has very good performance. We will be looking into network performance as we move forward in the next few months.
As for the 6.1 release, we have frozen the source code trunk for final testing and barring any regressions we are aiming for the end of September (this is not an official announcement). The 6.1 release will include integration with IPT for serving up data to GBIF via TAPIR and IPT also includes a searchable web presence. There are other exciting features as well.
Rod
Remote connections much better with 6.1
Tested the same arrangement with the 6.1 upgrade and noticed remarkable improvement! One step that was important (apparently because of the automated database schema update - I assume this wouldn't affect folks who have not used version 6.0) the upgrade had to be installed on the server machine first. Remote workstations can then be upgraded and will connect with ease to the server as before. In 6.0 I was waiting appx 1-2minutes for each successive rank to load in the taxon tree, now with 6.1 it loads almost instantly.
Help needed with remote connection
Thank you for this Herrick. I need to understand what the role of the ODBC connection is - is this something you configured or are you just referring to the technology that is used? (i.e. with the IP address as the server name in the Specify login dialog, surely an ODBC connection is not necessary).
I have just had our network administrator open port 3306 on the general firewall (MySQL is already permitted through the firewall on the server - ticked when I configured the MySQL instance).
I have run the grant all privileges command, including the IP address of the client.
I have run mysqld --bind-address=0.0.0.0
I have added the server's IP address and name to the client's hosts file (should I add the client's IP and name to the server's hosts file?)
I have restarted the service.
I configure a new master key every time I try to login.
The only thing I haven't yet done is torn my hair out.
Any suggestions would be appreciated.
Thanks
Willem
Trouble shooting remote connection
Willem,
I used the MySQL ODBC connection to look at things with MS Access. You're right, it is not necessary to connect with Specify6.
It seems you have jumped through all of the necessary hoops (3306 open thru firewall, Grants, etc.). You might try checking just who you granted privileges to from your mysql.users and verify that your "Master User" has appropriate Grants and that your "User's Username" appears in your specifydatabase.specifyuser table.
If all this appears correct, I'd verify that you have the Grants for your 'Master_User'@'IP_Address_of_Client' nailed down. Some clients may be on dynamically assigned IP's which could cause issues. The way around this would be to use % as part or all of your client IP in the Grant (i.e. 'Master_User'@'123.456.%' or just 'Master_User'@'%'). But some might consider this a little less secure.
Hope this helps,
Herrick
remote connection
Thanks Herrick, I'll try that. But I'm hoping that the web client will make a remote connection redundant. I have a collection that is 1000km away but is an integral part of the datase.
Thanks
Willem