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