Annotation of doc/mysql_note.txt, revision 1.2
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:
1.2 ! harris41 96: CREATE DATABASE loncapa;
1.1 harris41 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