[LON-CAPA-admin] loncapa with remote mysql server?

[Stuart Raeburn]

Todd,

 > I'm wondering if there's any appetite for this part of the code to be
 > refactored such that all occurrences of mysql db connections are done 
 > in one piece of reuseable code.

The Apache module: /home/httpd/lib/perl/Apache/lonmysql.pm was created 
as a means of isolating mysql code from other LON-CAPA modules.  There 
is already a connect_to_db() routine, which all of LON-CAPA's Apache 
modules use to connect to MySQL on localhost.

The other instances of DBI->connect() occur in daemons (lonsql), or
or scripts run by cron (searchcat.pl and cleanup_database.pl), or in CGI 
scripts (metadata_harvest.pl and metadata_keywords.pl). Those instances 
could be modified to use lonmysql::connect_to_db(), once the dependency 
on lonnet.pm in lonmysql.pm has been removed, and I have filed a bug in 
LON-CAPA's bugzilla for that.

See: bugs.loncapa.org/show_bug.cgi?id=6916

 >
 > ... I also found an instance in:
 >
 >    loncapa-2.11.2/loncom/build/make_domain_coordinator.pl
 >
 > that comes into play when running UPDATE.
 >

Yes, that instance (used to update the allusers table) can also be 
replaced with use of lonmysql::connect_to_db().

Strictly speaking, make_domain_coordinator.pl is not a part of UPDATE, 
although the script might be run after UPDATE completes, when installing 
LON-CAPA on a library server the very first time.

Note: in LON-CAPA all MySQL tables are "temporary", i.e., they will be 
rebuilt either by scripts run by cron, or when actions are taken via the 
web interface (e.g., display of the Assessment Chart or Statistics). 
Permanent storage of data is on a domain's library server in GDBM files.

LON-CAPA was designed to scale through the use of access servers to host 
user sessions, and each LON-CAPA node has a MySQL database which in the 
case of an access server will contain temporary tables used to speed up 
access to information about student submissions to assignments, e.g., by 
the Assessment Chart, Statistics reports, and the student's "quick 
grades" view of his/her own grades.

 >
 > Is there active development of loncapa, and plans for a new release?
 >

Yes, LON-CAPA continues to be actively developed, and a 2.11.3 will be 
released soon.

 > I'd prefer to see this change make it back into the codebase than for 
 > me and perhaps others have to patch this in.

Yes, that can be done.

Notes about ongoing LON-CAPA development:

1. Anyone may receive e-mails of commits to the LON-CAPA code base 
(including as digests) by subscribing to the lon-capa-cvs mailing list. 
See: mail.loncapa.org/mailman/listinfo/lon-capa-cvs (includes a link to 
post archives).

2.  An RSS feed of commits made to the LON-CAPA code base (loncom, doc, 
and rat directories, and sub-directories within those) is available at: 
source.loncapa.org/cvs/cvs.rss

3. A web-based view of the complete history of commits to the LON-CAPA 
code base (perl and c files, and most js files) is available at: 
source.loncapa.org/cvs/ for January 2000 to September 2019.

4. A web interface to the LON-CAPA code repository is available at: 
source.loncapa.org/cgi-bin/cvsweb.cgi

5. A daily build of bleeding edge LON-CAPA (not for use on a production 
server) is available from: 
install.loncapa.org/versions/loncapa-unstable.tar.gz .  Modules which 
are unavailable from a Linux distro's standard repos, and are required 
by bleeding edge LON-CAPA but not the production version, are available 
in the LON-CAPA testing repos at: install.loncapa.org/msu/testing/  The 
testing repos area also includes repos for distros/versions which will 
begin to be supported by the next LON-CAPA release, e.g., RHEL/CentOS 8, 
SLES 15, Ubuntu 18.04 LTS, and Oracle Linux 6, 7 & 8 (all for LON-CAPA 
2.11.3).

Stuart Raeburn
LON-CAPA Academic Consortium

On 10/18/19 12:26 PM, Todd Pfaff wrote:
> Hi Stuart,
> 
> Thanks for the quick response, and for getting me on this list today.
> 
> What you've reported below is the track I was on.  I also found an 
> instance in:
> 
>    loncapa-2.11.2/loncom/build/make_domain_coordinator.pl
> 
> that comes into play when running UPDATE.  I have made the necessary 
> changes in all of those locations and it seems to work just fine.
> 
> This is mostly just a curiosity for me right now since we can continue 
> to run our loncapa deployment without integrating the mysql portions 
> into our central mysql infrastructure.  Not a showstopper for us at this 
> time.
> 
> I'm wondering if there's any appetite for this part of the code to be 
> refactored such that all occurrences of mysql db connections are done in 
> one piece of reuseable code.  Is there active development of loncapa, 
> and plans for a new release?  I'd prefer to see this change make it back 
> into the codebase than for me and perhaps others have to patch this in.
> 
> Todd
> 
> 
> On Fri, 18 Oct 2019, Raeburn, Stuart wrote:
> 
>> Todd,
>>
>> I found eight instances of DBI->connect() in the LON-CAPA code, in the 
>> following files:
>>
>> /home/httpd/perl/cleanup_database.pl
>> /home/httpd/perl/lonsql  (2 instances)
>> /home/httpd/perl/searchcat.pl
>> /home/httpd/lib/perl/Apache/lonmysql.pm (2 instances)
>> /home/httpd/cgi-bin/metadata_harvest.pl
>> /home/httpd/cgi-bin/metadata_keywords.pl
>>
>> I have not tried using a remote mysql server, but it seems you should 
>> be able to replace
>> DBI->connect("DBI:mysql:$dbname", ....
>>
>> with
>>
>> DBI->connect("DBI:mysql:database=$dbname;host=$hostname;port=$port", ....
>> (where $dbname is database name, $hostname is remote host, and $port 
>> is remote port)
>>
>> in each of those eight instances, and then configure permissions and 
>> users appropriately in the mysql instance on the remote server.
>>
>> The LON-CAPA script (install.pl) used to prepare a server/VM for 
>> LON-CAPA installation, which is contained in the tar file: 
>> http://install.loncapa.org/versions/linux/install.tar
>> is used to set up the user and permissions for a MySQL database 
>> running on the local server.  Inspection of the part of that perl 
>> script that sets up MySQL could assist in setting up the remote mysql 
>> server (with a modification to allow connections from the remote 
>> LON-CAPA node).
>>
>> Stuart Raeburn
>> LON-CAPA Academic Consortium
>>
>> ________________________________________
>> From: LON-CAPA-admin <lon-capa-admin-bounces at mail.lon-capa.org> on 
>> behalf of Todd Pfaff <pfaff at rhpcs.mcmaster.ca>
>> Sent: Wednesday, October 2, 2019 12:02 PM
>> To: lon-capa-admin at mail.lon-capa.org
>> Subject: [LON-CAPA-admin] loncapa with remote mysql server?
>>
>> Anyone using loncapa with a remote mysql server instead of localhost?
>>
>> If yes, do you have a list of loncapa code diffs to implement the
>> changes?  I found at least five spots in loncapa perl code where the
>> perl DBI->connect string would need to be changed and I'm wondering if
>> there are other loncapa code changes that would be required.
>>
>>
>> _______________________________________________
>> LON-CAPA-admin mailing list
>> LON-CAPA-admin at mail.lon-capa.org
>> _______________________________________________
>> LON-CAPA-admin mailing list
>> LON-CAPA-admin at mail.lon-capa.org
>> http://mail.lon-capa.org/mailman/listinfo/lon-capa-admin
>>
>>