Annotation of doc/build/loncapasqldatabase.html, revision 1.3
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>
1.3 ! harris41 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