Annotation of doc/mysql_note.txt, revision 1.1
1.1 ! harris41 1: August, 29 2000; Scott Harrison; LON-CAPA
! 2:
! 3: These are notes related to a Perl interface and MySQL server installation
! 4: on Redhat 6.1 and 6.2 boxes. (Guy Albertelli and Harsha Jagasia
! 5: contributed significantly to this.)
! 6:
! 7: ********************
! 8: * MySQL COMPONENTS *
! 9: ********************
! 10:
! 11: There are three components to an effective MySQL installation for the
! 12: LON-CAPA system.
! 13:
! 14: Perl::DBI module- the API "front-end"...
! 15: database interface module for organizing generic
! 16: database commands which are independent of specific
! 17: database implementation (such as MySQL, mSQL, Postgres, etc).
! 18:
! 19: Perl::MySQL module- the API "mid-section"...
! 20: the module to directly interface with the actual
! 21: MySQL database engine
! 22:
! 23: MySQL database engine- the "back-end"...
! 24: the binary installation (compiled either from source
! 25: or pre-compiled file listings) which provides the
! 26: actual MySQL functionality on the system
! 27:
! 28: RedHat Installation-
! 29:
! 30: Initially done from source:
! 31: DBI-1.13.tar.gz Msql-Mysql-modules-1.2209.tar.gz mysql-3.22.32.tar.gz
! 32:
! 33: I am now using pre-compiled file listings.
! 34:
! 35: There were problems with using the RedHat packages since the three
! 36: different RedHat packages were somewhat noncompatible with each other
! 37: in terms of expected file locations. (The Debian linux distribution,
! 38: on the other hand, has a working set of these packages).
! 39:
! 40: Regardless of how we install these three components, there still remain
! 41: certain things which need to happen for the configuration.
! 42:
! 43: *****************
! 44: * CONFIGURATION *
! 45: *****************
! 46:
! 47: (Note: SOMEPASSWORD is actually set to another text string on the current
! 48: LON-CAPA systems.)
! 49:
! 50: Configuration is needed to generate the necessary functionality for the
! 51: MySQL system with LON-CAPA.
! 52:
! 53: The functionality needed can be understood from this example line
! 54: of perl code from "lonsql".
! 55:
! 56: $dbh = DBI->connect( "DBI:mysql:loncapa",
! 57: "www",
! 58: "SOMEPASSWORD",
! 59: { RaiseError =>0,PrintError=>0});
! 60:
! 61: There is an obvious need to CONNECT to the database, and in order to do
! 62: this, there must be:
! 63: a RUNNING mysql daemon;
! 64: a DATABASE named "loncapa";
! 65: a USER named "www";
! 66: and an ABILITY for LON-CAPA on one machine to access
! 67: SQL database on another machine;
! 68:
! 69: So, here are some notes on implementing these configurations.
! 70:
! 71: ** RUNNING mysql daemon (safe_mysqld method)
! 72:
! 73: The recommended way to run the MySQL daemon is as a non-root user
! 74: (probably www)...
! 75:
! 76: so, 1) login as user www on the linux machine
! 77: 2) start the mysql daemon as /usr/local/bin/safe_mysqld &
! 78:
! 79: safe_mysqld only works if the local installation of MySQL is set to the
! 80: right directory permissions which I found to be:
! 81: chown www:users /usr/local/var/mysql
! 82: chown www:users /usr/local/lib/mysql
! 83: chown -R www:users /usr/local/mysql
! 84: chown www:users /usr/local/include/mysql
! 85: chown www:users /usr/local/var
! 86:
! 87: ** DATABASE named "loncapa"
! 88:
! 89: As user www, run this command
! 90: mysql -u root -p mysql
! 91: enter the password as SOMEPASSWORD
! 92:
! 93: This allows you to manually enter MySQL commands.
! 94: The MySQL command to generate the loncapa DATABASE is:
! 95:
! 96: CREATE DATABASE 'loncapa';
! 97:
! 98: ** USER named "www"
! 99:
! 100: As user www, run this command
! 101: mysql -u root -p mysql
! 102: enter the password as SOMEPASSWORD
! 103:
! 104: To add the user www to the MySQL server, and grant all
! 105: privileges on *.* to www@localhost identified by 'SOMEPASSWORD'
! 106: with grant option;
! 107:
! 108: INSERT INTO user (Host, User, Password)
! 109: VALUES ('localhost','www',password('SOMEPASSWORD'));
! 110:
! 111: GRANT ALL PRIVILEGES ON *.* TO www@localhost;
! 112:
! 113: FLUSH PRIVILEGES;
! 114:
! 115: ** ABILITY for LON-CAPA machines to communicate with SQL databases on
! 116: other LON-CAPA machines
! 117:
! 118: This is a little more intricate than might first be expected (and I probably
! 119: won't do a perfect job reciting everything in this short synopsis). Because
! 120: LON-CAPA machines will likely be handling many SQL requests at a time,
! 121: there were some problems with current MySQL capabilities.
! 122:
! 123: PROBLEM SITUATION:
! 124:
! 125: If Server A wants data from Server B, Server A uses a lonc process to
! 126: send a database command to a Server B lond process.
! 127: lonc= loncapa client process A-lonc= a lonc process on Server A
! 128: lond= loncapa daemon process
! 129:
! 130: database command
! 131: A-lonc --------TCP/IP----------------> B-lond
! 132:
! 133: The problem emerges that A-lonc and B-lond are kept waiting for the
! 134: MySQL server to "do its stuff", or in other words, perform the conceivably
! 135: sophisticated, data-intensive, time-sucking database transaction. By tying
! 136: up a lonc and lond process, this significantly cripples the capabilities
! 137: of LON-CAPA servers.
! 138:
! 139: While commercial databases have a variety of features that ATTEMPT to
! 140: deal with this, freeware databases are still experimenting and exploring
! 141: with different schemes with varying degrees of performance stability.
! 142:
! 143: THE SOLUTION:
! 144:
! 145: A separate daemon process was created that B-lond works with to
! 146: handle database requests. This daemon process is called "lonsql".
! 147:
! 148: So,
! 149: database command
! 150: A-lonc ---------TCP/IP-----------------> B-lond =====> B-lonsql
! 151: <---------------------------------/ |
! 152: "ok, I'll get back to you..." |
! 153: |
! 154: /
! 155: A-lond <------------------------------- B-lonc <======
! 156: "Guess what? I have the result!"
! 157:
! 158: Of course, depending on success or failure, the messages may vary,
! 159: but the principle remains the same where a separate pool of children
! 160: processes (lonsql's) handle the MySQL database manipulations.
! 161:
! 162: Here are excerpts of code which implement the above handling:
! 163:
! 164: **LONSQL
! 165:
! 166: A subroutine from "lonsql" which establishes a child process for handling
! 167: database interactions.
! 168:
! 169: sub make_new_child {
! 170: my $pid;
! 171: my $sigset;
! 172:
! 173: # block signal for fork
! 174: $sigset = POSIX::SigSet->new(SIGINT);
! 175: sigprocmask(SIG_BLOCK, $sigset)
! 176: or die "Can't block SIGINT for fork: $!\n";
! 177:
! 178: die "fork: $!" unless defined ($pid = fork);
! 179:
! 180: if ($pid) {
! 181: # Parent records the child's birth and returns.
! 182: sigprocmask(SIG_UNBLOCK, $sigset)
! 183: or die "Can't unblock SIGINT for fork: $!\n";
! 184: $children{$pid} = 1;
! 185: $children++;
! 186: return;
! 187: } else {
! 188: # Child can *not* return from this subroutine.
! 189: $SIG{INT} = 'DEFAULT'; # make SIGINT kill us as it did before
! 190:
! 191: # unblock signals
! 192: sigprocmask(SIG_UNBLOCK, $sigset)
! 193: or die "Can't unblock SIGINT for fork: $!\n";
! 194:
! 195:
! 196: #open database handle
! 197: # making dbh global to avoid garbage collector
! 198: unless (
! 199: $dbh = DBI->connect("DBI:mysql:loncapa","www","SOMEPASSWORD",{ RaiseError =>0,PrintError=>0})
! 200: ) {
! 201: my $st=120+int(rand(240));
! 202: &logthis("<font color=blue>WARNING: Couldn't connect to database ($st secs): $@</font>");
! 203: print "database handle error\n";
! 204: sleep($st);
! 205: exit;
! 206:
! 207: };
! 208: # make sure that a database disconnection occurs with ending kill signals
! 209: $SIG{TERM}=$SIG{INT}=$SIG{QUIT}=$SIG{__DIE__}=\&DISCONNECT;
! 210:
! 211: # handle connections until we've reached $MAX_CLIENTS_PER_CHILD
! 212: for ($i=0; $i < $MAX_CLIENTS_PER_CHILD; $i++) {
! 213: $client = $server->accept() or last;
! 214:
! 215: # do something with the connection
! 216: $run = $run+1;
! 217: my $userinput = <$client>;
! 218: chomp($userinput);
! 219:
! 220: my ($conserver,$querytmp)=split(/&/,$userinput);
! 221: my $query=unescape($querytmp);
! 222:
! 223: #send query id which is pid_unixdatetime_runningcounter
! 224: $queryid = $thisserver;
! 225: $queryid .="_".($$)."_";
! 226: $queryid .= time."_";
! 227: $queryid .= $run;
! 228: print $client "$queryid\n";
! 229:
! 230: #prepare and execute the query
! 231: my $sth = $dbh->prepare($query);
! 232: my $result;
! 233: unless ($sth->execute())
! 234: {
! 235: &logthis("<font color=blue>WARNING: Could not retrieve from database: $@</font>");
! 236: $result="";
! 237: }
! 238: else {
! 239: my $r1=$sth->fetchall_arrayref;
! 240: my @r2; map {my $a=$_; my @b=map {escape($_)} @$a; push @r2,join(",", @b)} (@$r1);
! 241: $result=join("&",@r2) . "\n";
! 242: }
! 243: &reply("queryreply:$queryid:$result",$conserver);
! 244:
! 245: }
! 246:
! 247: # tidy up gracefully and finish
! 248:
! 249: #close the database handle
! 250: $dbh->disconnect
! 251: or &logthis("<font color=blue>WARNING: Couldn't disconnect from database $DBI::errstr ($st secs): $@</font>");
! 252:
! 253: # this exit is VERY important, otherwise the child will become
! 254: # a producer of more and more children, forking yourself into
! 255: # process death.
! 256: exit;
! 257: }
! 258: }
! 259:
! 260: ** LOND enabling of MySQL requestsw
! 261:
! 262: This code is part of every lond child process in the way that it parses command request syntax
! 263: sent to it from lonc processes. querysend corresponds to B-lonc sending the result of the query.
! 264: queryreply corresponds to B-lond indicating that it has received the request and will start the
! 265: database transaction (it returns "ok" to A-lonc ($client)).
! 266:
! 267: # ------------------------------------------------------------------- querysend
! 268: } elsif ($userinput =~ /^querysend/) {
! 269: my ($cmd,$query)=split(/:/,$userinput);
! 270: $query=~s/\n*$//g;
! 271: print $client sqlreply("$hostid{$clientip}\&$query")."\n";
! 272: # ------------------------------------------------------------------ queryreply
! 273: } elsif ($userinput =~ /^queryreply/) {
! 274: my ($cmd,$id,$reply)=split(/:/,$userinput);
! 275: my $store;
! 276: my $execdir=$perlvar{'lonDaemons'};
! 277: if ($store=IO::File->new(">$execdir/tmp/$id")) {
! 278: print $store $reply;
! 279: close $store;
! 280: print $client "ok\n";
! 281: }
! 282: else {
! 283: print $client "error:$!\n";
! 284: }
! 285:
! 286:
! 287:
! 288: ** TEST the database connection with my current tester.pl code which mimics what command will eventually be
! 289: sent through lonc.
! 290:
! 291: $reply=reply(
! 292: "querysend:SELECT * FROM general_information WHERE Id='AAAAA'",$lonID);
! 293:
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/back.gif){kind=icon}
Return to mysql_note.txt CVS log ![[TXT]](/icons/text.gif){kind=icon}
![[DIR]](/icons/dir.gif){kind=icon}
Up to [LON-CAPA] / doc