doc/build/loncapasqldatabase.html - view

File: [LON-CAPA] / doc / build / Attic / loncapasqldatabase.html
Revision 1.8: download - view: text, annotated - select for diffs
Wed Feb 14 14:33:35 2001 UTC (23 years, 5 months ago) by harris41
Branches: MAIN
CVS tags: HEAD

adding note on dependencies and beginning to update manual
installation details -Scott

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>Dependencies</LI> 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> 32: <H2>Current status of documentation</H2> 33: <P> 34: I am going to begin documentation by inserting what notes 35: I have into this file. I will be subsequently rearranging 36: them and editing them based on the tests that I conduct. 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> 43: <H2>Current status of implementation</H2> 44: <P> 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> 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 59: has been compiled in an RPM (LON-CAPA-mysql, with perl components 60: a part of LON-CAPA-systemperl). 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> 85: <H2>Purpose within LON-CAPA</H2> 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> 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> 165: <H2>Installation</H2> 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 171: engine. Related perl interfaces (Perl::DBI, Perl::Msql-Mysql) 172: are encoded in the LON-CAPA-systemperl RPM. 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> 195: <H2>Installation from source</H2> 196: <P> 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> 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> 233: </FONT> 234: <H2>Configuration (automated)</H2> 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> 240: <H2>Manual configuration</H2> 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> 275: <P> 276: <STRONG>The Perl API</STRONG> 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: 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> 348: 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> 355: Here are excerpts of code which implement the above handling: 356: </P> 357: <P> 358: <PRE> 359: <STRONG>**LONSQL 360: A subroutine from "lonsql" which establishes a child process for handling 361: database interactions.</STRONG> 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: } 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> 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: 485: </PRE> 486: 487: </P> 488: </BODY> 489: </HTML>