Annotation of doc/build/loncapasqldatabase.html, revision 1.4
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.4 ! harris41 11: Last updated: 02/10/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.2 harris41 17: <H3>Latest HOWTO</H3>
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>
! 31: <H3>Current status of documentation</H3>
! 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.4 ! harris41 42: <H3>Current status of implementation</H3>
! 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: <H3>Purpose within LON-CAPA</H3>
! 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: <H3>Installation</H3>
! 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: <H3>Installation from source</H3>
! 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: <H3>Configuration (automated)</H3>
! 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: <H3>Manual configuration</H3>
! 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: <H3>Testing</H3>
! 219: <P>
! 220: Not yet documented or formalized.
! 221: </P>
! 222: <H3>Example sections of code relevant to LON-CAPA</H3>
! 223: <P>
! 224: </P>
! 225:
! 226: <H1>Old notes</H1>
! 227:
1.2 harris41 228: <H3>How to add a user to the SQL database</H3>
229: <P>
230: <PRE>
231: start the mysql daemon as /usr/local/bin/safe_mysqld &
232: Login as root: mysql -u root -p mysql
233: enter the password as newmysql
234: add the user www: grant all priveleges on *.* to www@localhost identified by 'newmysql' with grant option;
235:
236: INSERT INTO user (Host, User, Password)
237: VALUES ('localhost','www',password('newmysql'));
238:
239: GRANT ALL PRIVILEGES ON *.* TO www@localhost;
240:
241: FLUSH PRIVILEGES;
242:
243: Here the user www has the right to grant privileges to other users.
244: This can be changed if required with a simple update command on the grant tables
245:
246:
247: /home/httpd/perl/perlsql/lonsql
248: /usr/local/mysql/fakeclient
249: </PRE>
250: </P>
251: <H3>To do</H3>
252: <P>
253: <PRE>
254: This is the output from scripts/mysql_install_db...
255: still some todo things (like support-files/mysql.server)
256:
257: Creating db table
258: Creating host table
259: Creating user table
260: Creating func table
261: Creating tables_priv table
262: Creating columns_priv table
263:
264: To start mysqld at boot time you have to copy support-files/mysql.server
265: to the right place for your system
266:
267: PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
268: This is done with:
269: /usr/local/bin/mysqladmin -u root password 'new-password'
270: See the manual for more instructions.
271:
272: Please report any problems with the /usr/local/bin/mysqlbug script!
273:
274: The latest information about MySQL is available on the web at http://www.mysql.com
275: Support MySQL by buying support/licenses at http://www.tcx.se/license.htmy.
276: </PRE>
277: </P>
278: <H3>Source Installation and Manual Configuration</H3>
1.1 harris41 279: <P>
1.2 harris41 280: <PRE>
281: August, 29 2000; Scott Harrison; LON-CAPA
282:
283: These are notes related to a Perl interface and MySQL server installation
284: on Redhat 6.1 and 6.2 boxes. (Guy Albertelli and Harsha Jagasia
285: contributed significantly to this.)
286:
287: ********************
288: * MySQL COMPONENTS *
289: ********************
290:
291: There are three components to an effective MySQL installation for the
292: LON-CAPA system.
293:
294: Perl::DBI module- the API "front-end"...
295: database interface module for organizing generic
296: database commands which are independent of specific
297: database implementation (such as MySQL, mSQL, Postgres, etc).
298:
299: Perl::MySQL module- the API "mid-section"...
300: the module to directly interface with the actual
301: MySQL database engine
302:
303: MySQL database engine- the "back-end"...
304: the binary installation (compiled either from source
305: or pre-compiled file listings) which provides the
306: actual MySQL functionality on the system
307:
308: RedHat Installation-
309:
310: Initially done from source:
311: DBI-1.13.tar.gz Msql-Mysql-modules-1.2209.tar.gz mysql-3.22.32.tar.gz
312:
313: I am now using pre-compiled file listings.
314:
315: There were problems with using the RedHat packages since the three
316: different RedHat packages were somewhat noncompatible with each other
317: in terms of expected file locations. (The Debian linux distribution,
318: on the other hand, has a working set of these packages).
319:
320: Regardless of how we install these three components, there still remain
321: certain things which need to happen for the configuration.
322:
323: *****************
324: * CONFIGURATION *
325: *****************
326:
327: (Note: SOMEPASSWORD is actually set to another text string on the current
328: LON-CAPA systems.)
329:
330: Configuration is needed to generate the necessary functionality for the
331: MySQL system with LON-CAPA.
332:
333: The functionality needed can be understood from this example line
334: of perl code from "lonsql".
335:
336: $dbh = DBI->connect( "DBI:mysql:loncapa",
337: "www",
338: "SOMEPASSWORD",
339: { RaiseError =>0,PrintError=>0});
340:
341: There is an obvious need to CONNECT to the database, and in order to do
342: this, there must be:
343: a RUNNING mysql daemon;
344: a DATABASE named "loncapa";
345: a USER named "www";
346: and an ABILITY for LON-CAPA on one machine to access
347: SQL database on another machine;
348:
349: So, here are some notes on implementing these configurations.
350:
351: ** RUNNING mysql daemon (safe_mysqld method)
352:
353: The recommended way to run the MySQL daemon is as a non-root user
354: (probably www)...
355:
356: so, 1) login as user www on the linux machine
357: 2) start the mysql daemon as /usr/local/bin/safe_mysqld &
358:
359: safe_mysqld only works if the local installation of MySQL is set to the
360: right directory permissions which I found to be:
361: chown www:users /usr/local/var/mysql
362: chown www:users /usr/local/lib/mysql
363: chown -R www:users /usr/local/mysql
364: chown www:users /usr/local/include/mysql
365: chown www:users /usr/local/var
366:
367: ** DATABASE named "loncapa"
368:
369: As user www, run this command
370: mysql -u root -p mysql
371: enter the password as SOMEPASSWORD
372:
373: This allows you to manually enter MySQL commands.
374: The MySQL command to generate the loncapa DATABASE is:
375:
376: CREATE DATABASE 'loncapa';
377:
378: ** USER named "www"
379:
380: As user www, run this command
381: mysql -u root -p mysql
382: enter the password as SOMEPASSWORD
383:
384: To add the user www to the MySQL server, and grant all
385: privileges on *.* to www@localhost identified by 'SOMEPASSWORD'
386: with grant option;
387:
388: INSERT INTO user (Host, User, Password)
389: VALUES ('localhost','www',password('SOMEPASSWORD'));
390:
391: GRANT ALL PRIVILEGES ON *.* TO www@localhost;
392:
393: FLUSH PRIVILEGES;
394:
395: ** ABILITY for LON-CAPA machines to communicate with SQL databases on
396: other LON-CAPA machines
397:
398: This is a little more intricate than might first be expected (and I probably
399: won't do a perfect job reciting everything in this short synopsis). Because
400: LON-CAPA machines will likely be handling many SQL requests at a time,
401: there were some problems with current MySQL capabilities.
402:
403: PROBLEM SITUATION:
404:
405: If Server A wants data from Server B, Server A uses a lonc process to
406: send a database command to a Server B lond process.
407: lonc= loncapa client process A-lonc= a lonc process on Server A
408: lond= loncapa daemon process
409:
410: database command
411: A-lonc --------TCP/IP----------------> B-lond
412:
413: The problem emerges that A-lonc and B-lond are kept waiting for the
414: MySQL server to "do its stuff", or in other words, perform the conceivably
415: sophisticated, data-intensive, time-sucking database transaction. By tying
416: up a lonc and lond process, this significantly cripples the capabilities
417: of LON-CAPA servers.
418:
419: While commercial databases have a variety of features that ATTEMPT to
420: deal with this, freeware databases are still experimenting and exploring
421: with different schemes with varying degrees of performance stability.
422:
423: THE SOLUTION:
424:
425: A separate daemon process was created that B-lond works with to
426: handle database requests. This daemon process is called "lonsql".
427:
428: So,
429: database command
430: A-lonc ---------TCP/IP-----------------> B-lond =====> B-lonsql
431: <---------------------------------/ |
432: "ok, I'll get back to you..." |
433: |
434: /
435: A-lond <------------------------------- B-lonc <======
436: "Guess what? I have the result!"
437:
438: Of course, depending on success or failure, the messages may vary,
439: but the principle remains the same where a separate pool of children
440: processes (lonsql's) handle the MySQL database manipulations.
441:
442: Here are excerpts of code which implement the above handling:
443:
444: **LONSQL
445:
446: A subroutine from "lonsql" which establishes a child process for handling
447: database interactions.
448:
449: sub make_new_child {
450: my $pid;
451: my $sigset;
452:
453: # block signal for fork
454: $sigset = POSIX::SigSet->new(SIGINT);
455: sigprocmask(SIG_BLOCK, $sigset)
456: or die "Can't block SIGINT for fork: $!\n";
457:
458: die "fork: $!" unless defined ($pid = fork);
459:
460: if ($pid) {
461: # Parent records the child's birth and returns.
462: sigprocmask(SIG_UNBLOCK, $sigset)
463: or die "Can't unblock SIGINT for fork: $!\n";
464: $children{$pid} = 1;
465: $children++;
466: return;
467: } else {
468: # Child can *not* return from this subroutine.
469: $SIG{INT} = 'DEFAULT'; # make SIGINT kill us as it did before
470:
471: # unblock signals
472: sigprocmask(SIG_UNBLOCK, $sigset)
473: or die "Can't unblock SIGINT for fork: $!\n";
474:
475:
476: #open database handle
477: # making dbh global to avoid garbage collector
478: unless (
479: $dbh = DBI->connect("DBI:mysql:loncapa","www","SOMEPASSWORD",{ RaiseError =>0,PrintError=>0})
480: ) {
481: my $st=120+int(rand(240));
482: &logthis("<font color=blue>WARNING: Couldn't connect to database ($st secs): $@</font>");
483: print "database handle error\n";
484: sleep($st);
485: exit;
486:
487: };
488: # make sure that a database disconnection occurs with ending kill signals
489: $SIG{TERM}=$SIG{INT}=$SIG{QUIT}=$SIG{__DIE__}=\&DISCONNECT;
490:
491: # handle connections until we've reached $MAX_CLIENTS_PER_CHILD
492: for ($i=0; $i < $MAX_CLIENTS_PER_CHILD; $i++) {
493: $client = $server->accept() or last;
494:
495: # do something with the connection
496: $run = $run+1;
497: my $userinput = <$client>;
498: chomp($userinput);
499:
500: my ($conserver,$querytmp)=split(/&/,$userinput);
501: my $query=unescape($querytmp);
502:
503: #send query id which is pid_unixdatetime_runningcounter
504: $queryid = $thisserver;
505: $queryid .="_".($$)."_";
506: $queryid .= time."_";
507: $queryid .= $run;
508: print $client "$queryid\n";
509:
510: #prepare and execute the query
511: my $sth = $dbh->prepare($query);
512: my $result;
513: unless ($sth->execute())
514: {
515: &logthis("<font color=blue>WARNING: Could not retrieve from database: $@</font>");
516: $result="";
517: }
518: else {
519: my $r1=$sth->fetchall_arrayref;
520: my @r2; map {my $a=$_; my @b=map {escape($_)} @$a; push @r2,join(",", @b)} (@$r1);
521: $result=join("&",@r2) . "\n";
522: }
523: &reply("queryreply:$queryid:$result",$conserver);
524:
525: }
526:
527: # tidy up gracefully and finish
528:
529: #close the database handle
530: $dbh->disconnect
531: or &logthis("<font color=blue>WARNING: Couldn't disconnect from database $DBI::errstr ($st secs): $@</font>");
532:
533: # this exit is VERY important, otherwise the child will become
534: # a producer of more and more children, forking yourself into
535: # process death.
536: exit;
537: }
538: }
539:
540: ** LOND enabling of MySQL requestsw
541:
542: This code is part of every lond child process in the way that it parses command request syntax
543: sent to it from lonc processes. querysend corresponds to B-lonc sending the result of the query.
544: queryreply corresponds to B-lond indicating that it has received the request and will start the
545: database transaction (it returns "ok" to A-lonc ($client)).
546:
547: # ------------------------------------------------------------------- querysend
548: } elsif ($userinput =~ /^querysend/) {
549: my ($cmd,$query)=split(/:/,$userinput);
550: $query=~s/\n*$//g;
551: print $client sqlreply("$hostid{$clientip}\&$query")."\n";
552: # ------------------------------------------------------------------ queryreply
553: } elsif ($userinput =~ /^queryreply/) {
554: my ($cmd,$id,$reply)=split(/:/,$userinput);
555: my $store;
556: my $execdir=$perlvar{'lonDaemons'};
557: if ($store=IO::File->new(">$execdir/tmp/$id")) {
558: print $store $reply;
559: close $store;
560: print $client "ok\n";
561: }
562: else {
563: print $client "error:$!\n";
564: }
565:
566:
567:
568: ** TEST the database connection with my current tester.pl code which mimics what command will eventually be
569: sent through lonc.
1.1 harris41 570:
1.2 harris41 571: $reply=reply(
572: "querysend:SELECT * FROM general_information WHERE Id='AAAAA'",$lonID);
573: </PRE>
1.1 harris41 574: </P>
575: </BODY>
1.3 harris41 576: </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