Annotation of doc/build/loncapasqldatabase.html, revision 1.8
1.1 harris41 1: <HTML>
2: <HEAD>
3: <TITLE>LON-CAPA SQL Database Documentation</TITLE>
4: </HEAD>
5: <BODY>
6: <H1>LON-CAPA SQL Database Documentation</H1>
7: <P>
8: Scott Harrison
9: </P>
10: <P>
1.5 harris41 11: Last updated: 02/12/2001
1.1 harris41 12: </P>
1.2 harris41 13: <P>
1.1 harris41 14: This file describes issues associated with LON-CAPA
15: and a SQL database.
16: </P>
1.5 harris41 17: <H2>Latest HOWTO</H2>
1.2 harris41 18: <P>
1.4 harris41 19: <UL>
20: <LI>Current status of documentation</LI>
21: <LI>Current status of implementation</LI>
22: <LI>Purpose within LON-CAPA</LI>
1.8 ! harris41 23: <LI>Dependencies</LI>
1.4 harris41 24: <LI>Installation</LI>
25: <LI>Installation from source</LI>
26: <LI>Configuration (automated)</LI>
27: <LI>Manual configuration</LI>
28: <LI>Testing</LI>
29: <LI>Example sections of code relevant to LON-CAPA</LI>
30: </UL>
31: </P>
1.5 harris41 32: <H2>Current status of documentation</H2>
1.4 harris41 33: <P>
1.2 harris41 34: I am going to begin documentation by inserting what notes
35: I have into this file. I will be subsequently rearranging
1.4 harris41 36: them and editing them based on the tests that I conduct.
1.2 harris41 37: I am trying to make sure that documentation, installation,
38: and run-time issues are all consistent and correct. The
39: current status of everything is that it works and has
40: been minimally tested, but things need to be cleaned up
41: and checked again!
42: </P>
1.5 harris41 43: <H2>Current status of implementation</H2>
1.4 harris41 44: <P>
1.6 harris41 45: Need to
46: <UL>
47: <LI>Installation: Fix binary file listings for user permissions and ownership.
48: <LI>Installation: Make sure sql server starts, and if database does not
49: exist, then create. (/etc/rc.d).
50: <LI>Processes: Make sure loncron initiates lonsql on library machines.
51: <LI>Read in metadata from right place periodically.
52: <LI>Implement tested perl module handler.
53: </UL>
54: <P>
1.4 harris41 55: Right now, a lot of "feasibility" work has been done.
56: Recipes for manual installation and configuration have
57: been gathered. Network connectivity of lond->lonsql->lond->lonc
58: type tests have been performed. A binary installation
1.7 harris41 59: has been compiled in an RPM (LON-CAPA-mysql, with perl components
60: a part of LON-CAPA-systemperl).
1.4 harris41 61: The most lacking test in terms of feasibility has
62: been looking at benchmarks to analyze the load at which
63: the SQL database can efficiently allow many users to
64: make simultaneous requests of the metadata database.
65: </P>
66: <P>
67: Documentation has been pieced together over time. But,
68: as mentioned in the previous section, it needs an
69: overhaul.
70: </P>
71: <P>
72: The binary installation has some quirks associated with it.
73: Some of the user permissions are wrong, although this is
74: benign. Also, other options of binary installation (such
75: as using binary RPMs put together by others) were dismissed
76: given the difficulty of getting differing combinations of
77: these external RPMs to work together.
78: </P>
79: <P>
80: Most configuration questions have been initially worked out
81: to the point of getting this SQL software component working,
82: however there may be more optimal approaches than currently
83: exist.
84: </P>
1.5 harris41 85: <H2>Purpose within LON-CAPA</H2>
1.4 harris41 86: <P>
87: LON-CAPA is meant to distribute A LOT of educational content
88: to A LOT of people. It is ineffective to directly rely on contents
89: within the ext2 filesystem to be speedily scanned for
90: on-the-fly searches of content descriptions. (Simply put,
91: it takes a cumbersome amount of time to open, read, analyze, and
92: close thousands of files.)
93: </P>
94: <P>
95: The solution is to hash-index various data fields that are
96: descriptive of the educational resources on a LON-CAPA server
97: machine. Descriptive data fields are referred to as
98: "metadata". The question then arises as to how this metadata
99: is handled in terms of the rest of the LON-CAPA network
100: without burdening client and daemon processes. I now
101: answer this question in the format of Problem and Solution
102: below.
103: </P>
104: <P>
105: <PRE>
106: PROBLEM SITUATION:
107:
108: If Server A wants data from Server B, Server A uses a lonc process to
109: send a database command to a Server B lond process.
110: lonc= loncapa client process A-lonc= a lonc process on Server A
111: lond= loncapa daemon process
112:
113: database command
114: A-lonc --------TCP/IP----------------> B-lond
115:
116: The problem emerges that A-lonc and B-lond are kept waiting for the
117: MySQL server to "do its stuff", or in other words, perform the conceivably
118: sophisticated, data-intensive, time-sucking database transaction. By tying
119: up a lonc and lond process, this significantly cripples the capabilities
120: of LON-CAPA servers.
121:
122: While commercial databases have a variety of features that ATTEMPT to
123: deal with this, freeware databases are still experimenting and exploring
124: with different schemes with varying degrees of performance stability.
125:
126: THE SOLUTION:
127:
128: A separate daemon process was created that B-lond works with to
129: handle database requests. This daemon process is called "lonsql".
130:
131: So,
132: database command
133: A-lonc ---------TCP/IP-----------------> B-lond =====> B-lonsql
134: <---------------------------------/ |
135: "ok, I'll get back to you..." |
136: |
137: /
138: A-lond <------------------------------- B-lonc <======
139: "Guess what? I have the result!"
140:
141: Of course, depending on success or failure, the messages may vary,
142: but the principle remains the same where a separate pool of children
143: processes (lonsql's) handle the MySQL database manipulations.
144: </PRE>
145: </P>
1.8 ! harris41 146: <H2>Dependencies</H2>
! 147: <P>
! 148: I believe (but am not 100% confident) that the following
! 149: RPMs are necessary (in addition to the current ones
! 150: in rpm_list.txt) to run MySQL. Basically I discovered these
! 151: dependencies while trying to do external RPM based installs.
! 152: I assume, and sometimes found, that these dependencies apply
! 153: to tarball-based distributions too. (So to play it on the
! 154: safe side, I am going to include these RPMs as part of the
! 155: core, minimal RPM set.)
! 156: <UL>
! 157: <LI>egcs-1.1.2-30</LI>
! 158: <LI>cpp-1.1.2-30</LI>
! 159: <LI>glibc-devel-2.1.3-15</LI>
! 160: <LI>glibc-devel-2.1.3-15</LI>
! 161: <LI>zlib-devel-1.1.3-6</LI>
! 162: </UL>
! 163:
! 164: </P>
1.5 harris41 165: <H2>Installation</H2>
1.4 harris41 166: <P>
167: Installation of the LON-CAPA SQL database normally occurs
168: by default when using the LON-CAPA installation CD
169: (see http://install.lon-capa.org). It is installed
170: as the LON-CAPA-mysql RPM. This RPM encodes for the MySQL
1.7 harris41 171: engine. Related perl interfaces (Perl::DBI, Perl::Msql-Mysql)
172: are encoded in the LON-CAPA-systemperl RPM.
1.4 harris41 173: </P>
174: <P>
175: The three components of a MySQL installation for the
176: LON-CAPA system are further described immediately below.
177: <TABLE BORDER="0">
178: <TR><TD COLSPAN="2"><STRONG>Perl::DBI module</STRONG>-
179: the API "front-end"...</TD></TR>
180: <TR><TD WIDTH="10%"></TD><TD>database interface module for organizing generic
181: database commands which are independent of specific
182: database implementation (such as MySQL, mSQL, Postgres, etc).
183: </TD></TR>
184: <TR><TD COLSPAN="2"><STRONG>Perl::MySQL module</STRONG>-
185: the API "mid-section"...</TD></TR>
186: <TR><TD WIDTH="10%"></TD><TD>the module to directly interface with the actual
187: MySQL database engine</TD></TR>
188: <TR><TD COLSPAN="2"><STRONG>MySQL database engine</STRONG>-
189: the "back-end"...</TD></TR>
190: <TR><TD WIDTH="10%"></TD><TD>the binary installation (compiled either
191: from source or pre-compiled file listings) which provides the
192: actual MySQL functionality on the system</TD></TR>
193: </TABLE>
194: </P>
1.5 harris41 195: <H2>Installation from source</H2>
1.4 harris41 196: <P>
1.8 ! harris41 197: Note: the mysql site recommends that Linux users install by
! 198: using the MySQL RPMs (MySQL-client, MySQL, MySQL-shared, etc).
! 199: While these RPMs work, I was unsuccessful at integrating
! 200: this RPM-installed database with perl modules from www.cpan.org.
! 201: Hence, I <STRONG>strongly</STRONG> recommend that, when installing
! 202: from "source", MySQL and the perl components be in fact installed
! 203: from their tarballs (.tar.gz, .tgz). (Perl components, when installed
! 204: from RPMs, also wound up in incorrect locations on the disk.)
! 205: Do not coordinate a source install with externally made RPMs!
! 206: It is, of course, okay to use LON-CAPA RPMs such as LON-CAPA-systemperl
! 207: and LON-CAPA-mysql since we, in fact, made these RPMs correctly :).
! 208: <UL>
! 209: <LI>http://www.cpan.org/authors/id/JWIED/Msql-Mysql-modules-1.2215.tar.gz
! 210: <BR>This tarball Released 20th August 2000
! 211: <LI>http://www.mysql.com/Downloads/MySQL-3.23/mysql-3.23.33-pc-linux-gnu-i686.tar.gz
! 212: <BR>This tarball Last changed 2000-11-11
! 213: <BR>This is actually a binary tarball (as opposed to source code
! 214: that is subsequently compiled).
! 215: <LI>
! 216: <BR>
! 217: </UL>
! 218: </P>
! 219: <FONT COLOR="green"> old notes in green
! 220: <P>
1.4 harris41 221: The following set of tarballs was found to work together
222: properly on a LON-CAPA RedHat 6.2 system:
223: <UL>
224: <LI>DBI-1.13.tar.gz
225: <LI>Msql-Mysql-modules-1.2209.tar.gz
226: <LI>mysql-3.22.32.tar.gz
227: </UL>
228: </P>
229: <P>
230: Installation was simply a matter of following the instructions
231: and typing the several "make" commands for each
232: </P>
1.8 ! harris41 233: </FONT>
1.5 harris41 234: <H2>Configuration (automated)</H2>
1.4 harris41 235: <P>
236: Not yet developed. This will be part of an interface
237: present on LON-CAPA systems that can be launched by
238: entering the command <TT>/usr/sbin/loncapaconfig</TT>.
239: </P>
1.5 harris41 240: <H2>Manual configuration</H2>
1.4 harris41 241: <P>
242: This is not complete.
243: </P>
244: <P>
245: <STRONG>Starting the mysql daemon</STRONG>: Login on the Linux
246: system as user 'www'. Enter the command
247: <TT>/usr/local/bin/safe_mysqld &</TT>
248: </P>
249: <P>
250: <STRONG>Set a password for 'root'</STRONG>:
251: <TT>/usr/local/bin/mysqladmin -u root password 'new-password'</TT>
252: </P>
253: <P>
254: <STRONG>Adding a user</STRONG>: Start the mysql daemon. Login to the
255: mysql system as root (<TT>mysql -u root -p mysql</TT>)
256: and enter the right password (for instance 'newmysql'). Add the user
257: www
258: <PRE>
259: INSERT INTO user (Host, User, Password)
260: VALUES ('localhost','www',password('newmysql'));
261: </PRE>
262: </P>
263: <P>
264: <STRONG>Granting privileges to user 'www'</STRONG>:
265: <PRE>
266: GRANT ALL PRIVILEGES ON *.* TO www@localhost;
267: FLUSH PRIVILEGES;
268: </PRE>
269: </P>
270: <P>
271: <STRONG>Set the SQL server to start upon system startup</STRONG>:
272: Copy support-files/mysql.server to the right place on the system
273: (/etc/rc.d/...).
274: </P>
1.1 harris41 275: <P>
1.5 harris41 276: <STRONG>The Perl API</STRONG>
1.2 harris41 277: <PRE>
278: $dbh = DBI->connect( "DBI:mysql:loncapa",
279: "www",
280: "SOMEPASSWORD",
281: { RaiseError =>0,PrintError=>0});
282:
283: There is an obvious need to CONNECT to the database, and in order to do
284: this, there must be:
285: a RUNNING mysql daemon;
286: a DATABASE named "loncapa";
287: a USER named "www";
288: and an ABILITY for LON-CAPA on one machine to access
289: SQL database on another machine;
290:
291: So, here are some notes on implementing these configurations.
292:
293: ** RUNNING mysql daemon (safe_mysqld method)
294:
295: The recommended way to run the MySQL daemon is as a non-root user
296: (probably www)...
297:
298: so, 1) login as user www on the linux machine
299: 2) start the mysql daemon as /usr/local/bin/safe_mysqld &
300:
301: safe_mysqld only works if the local installation of MySQL is set to the
302: right directory permissions which I found to be:
303: chown www:users /usr/local/var/mysql
304: chown www:users /usr/local/lib/mysql
305: chown -R www:users /usr/local/mysql
306: chown www:users /usr/local/include/mysql
307: chown www:users /usr/local/var
308:
309: ** DATABASE named "loncapa"
310:
311: As user www, run this command
312: mysql -u root -p mysql
313: enter the password as SOMEPASSWORD
314:
315: This allows you to manually enter MySQL commands.
316: The MySQL command to generate the loncapa DATABASE is:
317:
318: CREATE DATABASE 'loncapa';
319:
320: ** USER named "www"
321:
322: As user www, run this command
323: mysql -u root -p mysql
324: enter the password as SOMEPASSWORD
325:
326: To add the user www to the MySQL server, and grant all
327: privileges on *.* to www@localhost identified by 'SOMEPASSWORD'
328: with grant option;
329:
330: INSERT INTO user (Host, User, Password)
331: VALUES ('localhost','www',password('SOMEPASSWORD'));
332:
333: GRANT ALL PRIVILEGES ON *.* TO www@localhost;
334:
335: FLUSH PRIVILEGES;
336:
337: ** ABILITY for LON-CAPA machines to communicate with SQL databases on
338: other LON-CAPA machines
339:
1.5 harris41 340: An up-to-date lond and lonsql.
341: </PRE>
342: </P>
343: <H2>Testing</H2>
344: <P>
345: <PRE>
346: <STRONG>** TEST the database connection with my current tester.pl code
347: which mimics what command will eventually be sent through lonc.</STRONG>
1.2 harris41 348:
1.5 harris41 349: $reply=reply(
350: "querysend:SELECT * FROM general_information WHERE Id='AAAAA'",$lonID);
351: </PRE>
352: </P>
353: <H2>Example sections of code relevant to LON-CAPA</H2>
354: <P>
1.2 harris41 355: Here are excerpts of code which implement the above handling:
1.5 harris41 356: </P>
357: <P>
358: <PRE>
359: <STRONG>**LONSQL
1.2 harris41 360: A subroutine from "lonsql" which establishes a child process for handling
1.5 harris41 361: database interactions.</STRONG>
1.2 harris41 362:
363: sub make_new_child {
364: my $pid;
365: my $sigset;
366:
367: # block signal for fork
368: $sigset = POSIX::SigSet->new(SIGINT);
369: sigprocmask(SIG_BLOCK, $sigset)
370: or die "Can't block SIGINT for fork: $!\n";
371:
372: die "fork: $!" unless defined ($pid = fork);
373:
374: if ($pid) {
375: # Parent records the child's birth and returns.
376: sigprocmask(SIG_UNBLOCK, $sigset)
377: or die "Can't unblock SIGINT for fork: $!\n";
378: $children{$pid} = 1;
379: $children++;
380: return;
381: } else {
382: # Child can *not* return from this subroutine.
383: $SIG{INT} = 'DEFAULT'; # make SIGINT kill us as it did before
384:
385: # unblock signals
386: sigprocmask(SIG_UNBLOCK, $sigset)
387: or die "Can't unblock SIGINT for fork: $!\n";
388:
389:
390: #open database handle
391: # making dbh global to avoid garbage collector
392: unless (
393: $dbh = DBI->connect("DBI:mysql:loncapa","www","SOMEPASSWORD",{ RaiseError =>0,PrintError=>0})
394: ) {
395: my $st=120+int(rand(240));
396: &logthis("<font color=blue>WARNING: Couldn't connect to database ($st secs): $@</font>");
397: print "database handle error\n";
398: sleep($st);
399: exit;
400:
401: };
402: # make sure that a database disconnection occurs with ending kill signals
403: $SIG{TERM}=$SIG{INT}=$SIG{QUIT}=$SIG{__DIE__}=\&DISCONNECT;
404:
405: # handle connections until we've reached $MAX_CLIENTS_PER_CHILD
406: for ($i=0; $i < $MAX_CLIENTS_PER_CHILD; $i++) {
407: $client = $server->accept() or last;
408:
409: # do something with the connection
410: $run = $run+1;
411: my $userinput = <$client>;
412: chomp($userinput);
413:
414: my ($conserver,$querytmp)=split(/&/,$userinput);
415: my $query=unescape($querytmp);
416:
417: #send query id which is pid_unixdatetime_runningcounter
418: $queryid = $thisserver;
419: $queryid .="_".($$)."_";
420: $queryid .= time."_";
421: $queryid .= $run;
422: print $client "$queryid\n";
423:
424: #prepare and execute the query
425: my $sth = $dbh->prepare($query);
426: my $result;
427: unless ($sth->execute())
428: {
429: &logthis("<font color=blue>WARNING: Could not retrieve from database: $@</font>");
430: $result="";
431: }
432: else {
433: my $r1=$sth->fetchall_arrayref;
434: my @r2; map {my $a=$_; my @b=map {escape($_)} @$a; push @r2,join(",", @b)} (@$r1);
435: $result=join("&",@r2) . "\n";
436: }
437: &reply("queryreply:$queryid:$result",$conserver);
438:
439: }
440:
441: # tidy up gracefully and finish
442:
443: #close the database handle
444: $dbh->disconnect
445: or &logthis("<font color=blue>WARNING: Couldn't disconnect from database $DBI::errstr ($st secs): $@</font>");
446:
447: # this exit is VERY important, otherwise the child will become
448: # a producer of more and more children, forking yourself into
449: # process death.
450: exit;
451: }
452: }
1.5 harris41 453: </P>
454: <P>
455: <STRONG>** LOND enabling of MySQL requests</STRONG>
456: <BR />
457: This code is part of every lond child process in the
458: way that it parses command request syntax sent to it
459: from lonc processes. Based on the diagram above, querysend
460: corresponds to B-lonc sending the result of the query.
461: queryreply corresponds to B-lond indicating that it has
462: received the request and will start the database transaction
463: (it returns "ok" to
464: A-lonc ($client)).
465: <PRE>
1.2 harris41 466: # ------------------------------------------------------------------- querysend
467: } elsif ($userinput =~ /^querysend/) {
468: my ($cmd,$query)=split(/:/,$userinput);
469: $query=~s/\n*$//g;
470: print $client sqlreply("$hostid{$clientip}\&$query")."\n";
471: # ------------------------------------------------------------------ queryreply
472: } elsif ($userinput =~ /^queryreply/) {
473: my ($cmd,$id,$reply)=split(/:/,$userinput);
474: my $store;
475: my $execdir=$perlvar{'lonDaemons'};
476: if ($store=IO::File->new(">$execdir/tmp/$id")) {
477: print $store $reply;
478: close $store;
479: print $client "ok\n";
480: }
481: else {
482: print $client "error:$!\n";
483: }
484:
1.5 harris41 485: </PRE>
1.2 harris41 486:
1.1 harris41 487: </P>
488: </BODY>
1.3 harris41 489: </HTML>
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/back.gif){kind=icon}
Return to loncapasqldatabase.html CVS log ![[TXT]](/icons/text.gif){kind=icon}
![[DIR]](/icons/dir.gif){kind=icon}
Up to [LON-CAPA] / doc / build