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