Annotation of doc/build/loncapasqldatabase.html, revision 1.2
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>
11: Last updated: 02/07/2001
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>
! 19: I am going to begin documentation by inserting what notes
! 20: I have into this file. I will be subsequently rearranging
! 21: them and editting them based on the tests that I conduct.
! 22: I am trying to make sure that documentation, installation,
! 23: and run-time issues are all consistent and correct. The
! 24: current status of everything is that it works and has
! 25: been minimally tested, but things need to be cleaned up
! 26: and checked again!
! 27: </P>
! 28: <H3>How to add a user to the SQL database</H3>
! 29: <P>
! 30: <PRE>
! 31: start the mysql daemon as /usr/local/bin/safe_mysqld &
! 32: Login as root: mysql -u root -p mysql
! 33: enter the password as newmysql
! 34: add the user www: grant all priveleges on *.* to www@localhost identified by 'newmysql' with grant option;
! 35:
! 36: INSERT INTO user (Host, User, Password)
! 37: VALUES ('localhost','www',password('newmysql'));
! 38:
! 39: GRANT ALL PRIVILEGES ON *.* TO www@localhost;
! 40:
! 41: FLUSH PRIVILEGES;
! 42:
! 43: Here the user www has the right to grant privileges to other users.
! 44: This can be changed if required with a simple update command on the grant tables
! 45:
! 46:
! 47: /home/httpd/perl/perlsql/lonsql
! 48: /usr/local/mysql/fakeclient
! 49: </PRE>
! 50: </P>
! 51: <H3>To do</H3>
! 52: <P>
! 53: <PRE>
! 54: This is the output from scripts/mysql_install_db...
! 55: still some todo things (like support-files/mysql.server)
! 56:
! 57: Creating db table
! 58: Creating host table
! 59: Creating user table
! 60: Creating func table
! 61: Creating tables_priv table
! 62: Creating columns_priv table
! 63:
! 64: To start mysqld at boot time you have to copy support-files/mysql.server
! 65: to the right place for your system
! 66:
! 67: PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
! 68: This is done with:
! 69: /usr/local/bin/mysqladmin -u root password 'new-password'
! 70: See the manual for more instructions.
! 71:
! 72: Please report any problems with the /usr/local/bin/mysqlbug script!
! 73:
! 74: The latest information about MySQL is available on the web at http://www.mysql.com
! 75: Support MySQL by buying support/licenses at http://www.tcx.se/license.htmy.
! 76: </PRE>
! 77: </P>
! 78: <H3>Source Installation and Manual Configuration</H3>
1.1 harris41 79: <P>
1.2 ! harris41 80: <PRE>
! 81: August, 29 2000; Scott Harrison; LON-CAPA
! 82:
! 83: These are notes related to a Perl interface and MySQL server installation
! 84: on Redhat 6.1 and 6.2 boxes. (Guy Albertelli and Harsha Jagasia
! 85: contributed significantly to this.)
! 86:
! 87: ********************
! 88: * MySQL COMPONENTS *
! 89: ********************
! 90:
! 91: There are three components to an effective MySQL installation for the
! 92: LON-CAPA system.
! 93:
! 94: Perl::DBI module- the API "front-end"...
! 95: database interface module for organizing generic
! 96: database commands which are independent of specific
! 97: database implementation (such as MySQL, mSQL, Postgres, etc).
! 98:
! 99: Perl::MySQL module- the API "mid-section"...
! 100: the module to directly interface with the actual
! 101: MySQL database engine
! 102:
! 103: MySQL database engine- the "back-end"...
! 104: the binary installation (compiled either from source
! 105: or pre-compiled file listings) which provides the
! 106: actual MySQL functionality on the system
! 107:
! 108: RedHat Installation-
! 109:
! 110: Initially done from source:
! 111: DBI-1.13.tar.gz Msql-Mysql-modules-1.2209.tar.gz mysql-3.22.32.tar.gz
! 112:
! 113: I am now using pre-compiled file listings.
! 114:
! 115: There were problems with using the RedHat packages since the three
! 116: different RedHat packages were somewhat noncompatible with each other
! 117: in terms of expected file locations. (The Debian linux distribution,
! 118: on the other hand, has a working set of these packages).
! 119:
! 120: Regardless of how we install these three components, there still remain
! 121: certain things which need to happen for the configuration.
! 122:
! 123: *****************
! 124: * CONFIGURATION *
! 125: *****************
! 126:
! 127: (Note: SOMEPASSWORD is actually set to another text string on the current
! 128: LON-CAPA systems.)
! 129:
! 130: Configuration is needed to generate the necessary functionality for the
! 131: MySQL system with LON-CAPA.
! 132:
! 133: The functionality needed can be understood from this example line
! 134: of perl code from "lonsql".
! 135:
! 136: $dbh = DBI->connect( "DBI:mysql:loncapa",
! 137: "www",
! 138: "SOMEPASSWORD",
! 139: { RaiseError =>0,PrintError=>0});
! 140:
! 141: There is an obvious need to CONNECT to the database, and in order to do
! 142: this, there must be:
! 143: a RUNNING mysql daemon;
! 144: a DATABASE named "loncapa";
! 145: a USER named "www";
! 146: and an ABILITY for LON-CAPA on one machine to access
! 147: SQL database on another machine;
! 148:
! 149: So, here are some notes on implementing these configurations.
! 150:
! 151: ** RUNNING mysql daemon (safe_mysqld method)
! 152:
! 153: The recommended way to run the MySQL daemon is as a non-root user
! 154: (probably www)...
! 155:
! 156: so, 1) login as user www on the linux machine
! 157: 2) start the mysql daemon as /usr/local/bin/safe_mysqld &
! 158:
! 159: safe_mysqld only works if the local installation of MySQL is set to the
! 160: right directory permissions which I found to be:
! 161: chown www:users /usr/local/var/mysql
! 162: chown www:users /usr/local/lib/mysql
! 163: chown -R www:users /usr/local/mysql
! 164: chown www:users /usr/local/include/mysql
! 165: chown www:users /usr/local/var
! 166:
! 167: ** DATABASE named "loncapa"
! 168:
! 169: As user www, run this command
! 170: mysql -u root -p mysql
! 171: enter the password as SOMEPASSWORD
! 172:
! 173: This allows you to manually enter MySQL commands.
! 174: The MySQL command to generate the loncapa DATABASE is:
! 175:
! 176: CREATE DATABASE 'loncapa';
! 177:
! 178: ** USER named "www"
! 179:
! 180: As user www, run this command
! 181: mysql -u root -p mysql
! 182: enter the password as SOMEPASSWORD
! 183:
! 184: To add the user www to the MySQL server, and grant all
! 185: privileges on *.* to www@localhost identified by 'SOMEPASSWORD'
! 186: with grant option;
! 187:
! 188: INSERT INTO user (Host, User, Password)
! 189: VALUES ('localhost','www',password('SOMEPASSWORD'));
! 190:
! 191: GRANT ALL PRIVILEGES ON *.* TO www@localhost;
! 192:
! 193: FLUSH PRIVILEGES;
! 194:
! 195: ** ABILITY for LON-CAPA machines to communicate with SQL databases on
! 196: other LON-CAPA machines
! 197:
! 198: This is a little more intricate than might first be expected (and I probably
! 199: won't do a perfect job reciting everything in this short synopsis). Because
! 200: LON-CAPA machines will likely be handling many SQL requests at a time,
! 201: there were some problems with current MySQL capabilities.
! 202:
! 203: PROBLEM SITUATION:
! 204:
! 205: If Server A wants data from Server B, Server A uses a lonc process to
! 206: send a database command to a Server B lond process.
! 207: lonc= loncapa client process A-lonc= a lonc process on Server A
! 208: lond= loncapa daemon process
! 209:
! 210: database command
! 211: A-lonc --------TCP/IP----------------> B-lond
! 212:
! 213: The problem emerges that A-lonc and B-lond are kept waiting for the
! 214: MySQL server to "do its stuff", or in other words, perform the conceivably
! 215: sophisticated, data-intensive, time-sucking database transaction. By tying
! 216: up a lonc and lond process, this significantly cripples the capabilities
! 217: of LON-CAPA servers.
! 218:
! 219: While commercial databases have a variety of features that ATTEMPT to
! 220: deal with this, freeware databases are still experimenting and exploring
! 221: with different schemes with varying degrees of performance stability.
! 222:
! 223: THE SOLUTION:
! 224:
! 225: A separate daemon process was created that B-lond works with to
! 226: handle database requests. This daemon process is called "lonsql".
! 227:
! 228: So,
! 229: database command
! 230: A-lonc ---------TCP/IP-----------------> B-lond =====> B-lonsql
! 231: <---------------------------------/ |
! 232: "ok, I'll get back to you..." |
! 233: |
! 234: /
! 235: A-lond <------------------------------- B-lonc <======
! 236: "Guess what? I have the result!"
! 237:
! 238: Of course, depending on success or failure, the messages may vary,
! 239: but the principle remains the same where a separate pool of children
! 240: processes (lonsql's) handle the MySQL database manipulations.
! 241:
! 242: Here are excerpts of code which implement the above handling:
! 243:
! 244: **LONSQL
! 245:
! 246: A subroutine from "lonsql" which establishes a child process for handling
! 247: database interactions.
! 248:
! 249: sub make_new_child {
! 250: my $pid;
! 251: my $sigset;
! 252:
! 253: # block signal for fork
! 254: $sigset = POSIX::SigSet->new(SIGINT);
! 255: sigprocmask(SIG_BLOCK, $sigset)
! 256: or die "Can't block SIGINT for fork: $!\n";
! 257:
! 258: die "fork: $!" unless defined ($pid = fork);
! 259:
! 260: if ($pid) {
! 261: # Parent records the child's birth and returns.
! 262: sigprocmask(SIG_UNBLOCK, $sigset)
! 263: or die "Can't unblock SIGINT for fork: $!\n";
! 264: $children{$pid} = 1;
! 265: $children++;
! 266: return;
! 267: } else {
! 268: # Child can *not* return from this subroutine.
! 269: $SIG{INT} = 'DEFAULT'; # make SIGINT kill us as it did before
! 270:
! 271: # unblock signals
! 272: sigprocmask(SIG_UNBLOCK, $sigset)
! 273: or die "Can't unblock SIGINT for fork: $!\n";
! 274:
! 275:
! 276: #open database handle
! 277: # making dbh global to avoid garbage collector
! 278: unless (
! 279: $dbh = DBI->connect("DBI:mysql:loncapa","www","SOMEPASSWORD",{ RaiseError =>0,PrintError=>0})
! 280: ) {
! 281: my $st=120+int(rand(240));
! 282: &logthis("<font color=blue>WARNING: Couldn't connect to database ($st secs): $@</font>");
! 283: print "database handle error\n";
! 284: sleep($st);
! 285: exit;
! 286:
! 287: };
! 288: # make sure that a database disconnection occurs with ending kill signals
! 289: $SIG{TERM}=$SIG{INT}=$SIG{QUIT}=$SIG{__DIE__}=\&DISCONNECT;
! 290:
! 291: # handle connections until we've reached $MAX_CLIENTS_PER_CHILD
! 292: for ($i=0; $i < $MAX_CLIENTS_PER_CHILD; $i++) {
! 293: $client = $server->accept() or last;
! 294:
! 295: # do something with the connection
! 296: $run = $run+1;
! 297: my $userinput = <$client>;
! 298: chomp($userinput);
! 299:
! 300: my ($conserver,$querytmp)=split(/&/,$userinput);
! 301: my $query=unescape($querytmp);
! 302:
! 303: #send query id which is pid_unixdatetime_runningcounter
! 304: $queryid = $thisserver;
! 305: $queryid .="_".($$)."_";
! 306: $queryid .= time."_";
! 307: $queryid .= $run;
! 308: print $client "$queryid\n";
! 309:
! 310: #prepare and execute the query
! 311: my $sth = $dbh->prepare($query);
! 312: my $result;
! 313: unless ($sth->execute())
! 314: {
! 315: &logthis("<font color=blue>WARNING: Could not retrieve from database: $@</font>");
! 316: $result="";
! 317: }
! 318: else {
! 319: my $r1=$sth->fetchall_arrayref;
! 320: my @r2; map {my $a=$_; my @b=map {escape($_)} @$a; push @r2,join(",", @b)} (@$r1);
! 321: $result=join("&",@r2) . "\n";
! 322: }
! 323: &reply("queryreply:$queryid:$result",$conserver);
! 324:
! 325: }
! 326:
! 327: # tidy up gracefully and finish
! 328:
! 329: #close the database handle
! 330: $dbh->disconnect
! 331: or &logthis("<font color=blue>WARNING: Couldn't disconnect from database $DBI::errstr ($st secs): $@</font>");
! 332:
! 333: # this exit is VERY important, otherwise the child will become
! 334: # a producer of more and more children, forking yourself into
! 335: # process death.
! 336: exit;
! 337: }
! 338: }
! 339:
! 340: ** LOND enabling of MySQL requestsw
! 341:
! 342: This code is part of every lond child process in the way that it parses command request syntax
! 343: sent to it from lonc processes. querysend corresponds to B-lonc sending the result of the query.
! 344: queryreply corresponds to B-lond indicating that it has received the request and will start the
! 345: database transaction (it returns "ok" to A-lonc ($client)).
! 346:
! 347: # ------------------------------------------------------------------- querysend
! 348: } elsif ($userinput =~ /^querysend/) {
! 349: my ($cmd,$query)=split(/:/,$userinput);
! 350: $query=~s/\n*$//g;
! 351: print $client sqlreply("$hostid{$clientip}\&$query")."\n";
! 352: # ------------------------------------------------------------------ queryreply
! 353: } elsif ($userinput =~ /^queryreply/) {
! 354: my ($cmd,$id,$reply)=split(/:/,$userinput);
! 355: my $store;
! 356: my $execdir=$perlvar{'lonDaemons'};
! 357: if ($store=IO::File->new(">$execdir/tmp/$id")) {
! 358: print $store $reply;
! 359: close $store;
! 360: print $client "ok\n";
! 361: }
! 362: else {
! 363: print $client "error:$!\n";
! 364: }
! 365:
! 366:
! 367:
! 368: ** TEST the database connection with my current tester.pl code which mimics what command will eventually be
! 369: sent through lonc.
1.1 harris41 370:
1.2 ! harris41 371: $reply=reply(
! 372: "querysend:SELECT * FROM general_information WHERE Id='AAAAA'",$lonID);
! 373: </PRE>
1.1 harris41 374: </P>
375: </BODY>
376: </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