Annotation of doc/build/loncapasqldatabase.html, revision 1.11
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>
1.9 harris41 11: Last updated: 02/14/2001
1.1 harris41 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.5 harris41 17: <H2>Latest HOWTO</H2>
1.2 harris41 18: <P>
1.4 harris41 19: <UL>
1.9 harris41 20: <LI>Important notes
1.4 harris41 21: <LI>Current status of documentation</LI>
22: <LI>Current status of implementation</LI>
23: <LI>Purpose within LON-CAPA</LI>
1.8 harris41 24: <LI>Dependencies</LI>
1.4 harris41 25: <LI>Installation</LI>
26: <LI>Installation from source</LI>
27: <LI>Configuration (automated)</LI>
28: <LI>Manual configuration</LI>
29: <LI>Testing</LI>
30: <LI>Example sections of code relevant to LON-CAPA</LI>
31: </UL>
32: </P>
1.9 harris41 33: <H2>Important notes</H2>
34: <P>
35: It might be worthwhile to look at /usr/local/mysql/manual.html.
36: It is quite in depth.
37: </P>
1.5 harris41 38: <H2>Current status of documentation</H2>
1.4 harris41 39: <P>
1.2 harris41 40: I am going to begin documentation by inserting what notes
41: I have into this file. I will be subsequently rearranging
1.4 harris41 42: them and editing them based on the tests that I conduct.
1.2 harris41 43: I am trying to make sure that documentation, installation,
44: and run-time issues are all consistent and correct. The
45: current status of everything is that it works and has
46: been minimally tested, but things need to be cleaned up
47: and checked again!
48: </P>
1.5 harris41 49: <H2>Current status of implementation</H2>
1.4 harris41 50: <P>
1.6 harris41 51: Need to
52: <UL>
53: <LI>Installation: Fix binary file listings for user permissions and ownership.
54: <LI>Installation: Make sure sql server starts, and if database does not
55: exist, then create. (/etc/rc.d).
56: <LI>Processes: Make sure loncron initiates lonsql on library machines.
57: <LI>Read in metadata from right place periodically.
58: <LI>Implement tested perl module handler.
59: </UL>
60: <P>
1.4 harris41 61: Right now, a lot of "feasibility" work has been done.
62: Recipes for manual installation and configuration have
63: been gathered. Network connectivity of lond->lonsql->lond->lonc
64: type tests have been performed. A binary installation
1.7 harris41 65: has been compiled in an RPM (LON-CAPA-mysql, with perl components
66: a part of LON-CAPA-systemperl).
1.4 harris41 67: The most lacking test in terms of feasibility has
68: been looking at benchmarks to analyze the load at which
69: the SQL database can efficiently allow many users to
70: make simultaneous requests of the metadata database.
71: </P>
72: <P>
73: Documentation has been pieced together over time. But,
74: as mentioned in the previous section, it needs an
75: overhaul.
76: </P>
77: <P>
78: The binary installation has some quirks associated with it.
79: Some of the user permissions are wrong, although this is
80: benign. Also, other options of binary installation (such
81: as using binary RPMs put together by others) were dismissed
82: given the difficulty of getting differing combinations of
83: these external RPMs to work together.
84: </P>
85: <P>
86: Most configuration questions have been initially worked out
87: to the point of getting this SQL software component working,
88: however there may be more optimal approaches than currently
89: exist.
90: </P>
1.5 harris41 91: <H2>Purpose within LON-CAPA</H2>
1.4 harris41 92: <P>
93: LON-CAPA is meant to distribute A LOT of educational content
94: to A LOT of people. It is ineffective to directly rely on contents
95: within the ext2 filesystem to be speedily scanned for
96: on-the-fly searches of content descriptions. (Simply put,
97: it takes a cumbersome amount of time to open, read, analyze, and
98: close thousands of files.)
99: </P>
100: <P>
101: The solution is to hash-index various data fields that are
102: descriptive of the educational resources on a LON-CAPA server
103: machine. Descriptive data fields are referred to as
104: "metadata". The question then arises as to how this metadata
105: is handled in terms of the rest of the LON-CAPA network
106: without burdening client and daemon processes. I now
107: answer this question in the format of Problem and Solution
108: below.
109: </P>
110: <P>
111: <PRE>
112: PROBLEM SITUATION:
113:
114: If Server A wants data from Server B, Server A uses a lonc process to
115: send a database command to a Server B lond process.
116: lonc= loncapa client process A-lonc= a lonc process on Server A
117: lond= loncapa daemon process
118:
119: database command
120: A-lonc --------TCP/IP----------------> B-lond
121:
122: The problem emerges that A-lonc and B-lond are kept waiting for the
123: MySQL server to "do its stuff", or in other words, perform the conceivably
124: sophisticated, data-intensive, time-sucking database transaction. By tying
125: up a lonc and lond process, this significantly cripples the capabilities
126: of LON-CAPA servers.
127:
128: While commercial databases have a variety of features that ATTEMPT to
129: deal with this, freeware databases are still experimenting and exploring
130: with different schemes with varying degrees of performance stability.
131:
132: THE SOLUTION:
133:
134: A separate daemon process was created that B-lond works with to
135: handle database requests. This daemon process is called "lonsql".
136:
137: So,
138: database command
139: A-lonc ---------TCP/IP-----------------> B-lond =====> B-lonsql
140: <---------------------------------/ |
141: "ok, I'll get back to you..." |
142: |
143: /
144: A-lond <------------------------------- B-lonc <======
145: "Guess what? I have the result!"
146:
147: Of course, depending on success or failure, the messages may vary,
148: but the principle remains the same where a separate pool of children
149: processes (lonsql's) handle the MySQL database manipulations.
150: </PRE>
151: </P>
1.8 harris41 152: <H2>Dependencies</H2>
153: <P>
154: I believe (but am not 100% confident) that the following
155: RPMs are necessary (in addition to the current ones
156: in rpm_list.txt) to run MySQL. Basically I discovered these
157: dependencies while trying to do external RPM based installs.
158: I assume, and sometimes found, that these dependencies apply
159: to tarball-based distributions too. (So to play it on the
160: safe side, I am going to include these RPMs as part of the
161: core, minimal RPM set.)
162: <UL>
163: <LI>egcs-1.1.2-30</LI>
164: <LI>cpp-1.1.2-30</LI>
165: <LI>glibc-devel-2.1.3-15</LI>
166: <LI>zlib-devel-1.1.3-6</LI>
167: </UL>
168: </P>
1.5 harris41 169: <H2>Installation</H2>
1.4 harris41 170: <P>
171: Installation of the LON-CAPA SQL database normally occurs
172: by default when using the LON-CAPA installation CD
173: (see http://install.lon-capa.org). It is installed
174: as the LON-CAPA-mysql RPM. This RPM encodes for the MySQL
1.7 harris41 175: engine. Related perl interfaces (Perl::DBI, Perl::Msql-Mysql)
176: are encoded in the LON-CAPA-systemperl RPM.
1.4 harris41 177: </P>
178: <P>
179: The three components of a MySQL installation for the
180: LON-CAPA system are further described immediately below.
181: <TABLE BORDER="0">
182: <TR><TD COLSPAN="2"><STRONG>Perl::DBI module</STRONG>-
183: the API "front-end"...</TD></TR>
184: <TR><TD WIDTH="10%"></TD><TD>database interface module for organizing generic
185: database commands which are independent of specific
186: database implementation (such as MySQL, mSQL, Postgres, etc).
187: </TD></TR>
188: <TR><TD COLSPAN="2"><STRONG>Perl::MySQL module</STRONG>-
189: the API "mid-section"...</TD></TR>
190: <TR><TD WIDTH="10%"></TD><TD>the module to directly interface with the actual
191: MySQL database engine</TD></TR>
192: <TR><TD COLSPAN="2"><STRONG>MySQL database engine</STRONG>-
193: the "back-end"...</TD></TR>
194: <TR><TD WIDTH="10%"></TD><TD>the binary installation (compiled either
195: from source or pre-compiled file listings) which provides the
196: actual MySQL functionality on the system</TD></TR>
197: </TABLE>
198: </P>
1.5 harris41 199: <H2>Installation from source</H2>
1.4 harris41 200: <P>
1.8 harris41 201: Note: the mysql site recommends that Linux users install by
202: using the MySQL RPMs (MySQL-client, MySQL, MySQL-shared, etc).
203: While these RPMs work, I was unsuccessful at integrating
204: this RPM-installed database with perl modules from www.cpan.org.
205: Hence, I <STRONG>strongly</STRONG> recommend that, when installing
206: from "source", MySQL and the perl components be in fact installed
207: from their tarballs (.tar.gz, .tgz). (Perl components, when installed
208: from RPMs, also wound up in incorrect locations on the disk.)
209: Do not coordinate a source install with externally made RPMs!
210: It is, of course, okay to use LON-CAPA RPMs such as LON-CAPA-systemperl
211: and LON-CAPA-mysql since we, in fact, made these RPMs correctly :).
212: <UL>
213: <LI>http://www.cpan.org/authors/id/JWIED/Msql-Mysql-modules-1.2215.tar.gz
214: <BR>This tarball Released 20th August 2000
215: <LI>http://www.mysql.com/Downloads/MySQL-3.23/mysql-3.23.33-pc-linux-gnu-i686.tar.gz
216: <BR>This tarball Last changed 2000-11-11
217: <BR>This is actually a binary tarball (as opposed to source code
218: that is subsequently compiled).
1.9 harris41 219: <LI>http://www.cpan.org/authors/id/TIMB/DBI-1.14.tar.gz
220: <BR>This tarball Released 14th June 2000
1.8 harris41 221: </UL>
1.9 harris41 222: </P>
223: <P>So, here is exactly how I installed MySQL-3.23. (Note that all files
224: wind up in /usr/local/mysql-3.23.33-pc-linux-gnu-i686 except for
225: a link from /usr/local/mysql to /usr/local/mysql-3.23.33-pc-linux-gnu-i686
226: and some files involved in system process handling (/etc/rc.d/*/*mysql).
1.11 ! harris41 227: <PRE>
! 228: (As user=root)
! 229: cd /usr/local/;
! 230: tar xzvf mysql-3.23.33-pc-linux-gnu-i686.tar.gz
! 231: ln -s /usr/local/mysql-3.23.33-pc-linux-gnu-i686 mysql
! 232: cd /usr/local/mysql
! 233: chown -R www /usr/local/mysql/.
! 234: chgrp -R users /usr/local/mysql/.
! 235: chmod -R g-w,g-r,g-x /usr/local/mysql/.
! 236: (probably also want chmod -R a-w,a-r,a-x /usr/local/mysql/.)
! 237:
! 238: Alter <TT>safe_mysqld</TT>
! 239: and <TT>support-files/mysql.server</TT> to use 'localhosts' instead
! 240: of `bin/hostname`. Also, to use user 'www' instead of 'mysql'.
! 241:
! 242: (These changes could be done with /etc/my.cnf, but
! 243: I think this approach makes sure the database NEVER
! 244: gets screwed up due to somebody forgetting to install /etc/my.cnf).
! 245:
! 246: Change this line in mysql.server from
! 247: pid_file=$datadir/`@HOSTNAME@`.pid
! 248: to
! 249: pid_file=$datadir/localhost.pid
! 250:
! 251: Change this line in safe_mysqld from
! 252: user=root
! 253: to
! 254: user=www
! 255:
! 256: Change this line in safe_mysqld from
! 257: pid_file=$DATADIR/`/bin/hostname`.pid
! 258: to
! 259: pid_file=$DATADIR/localhost.pid
! 260:
! 261: Change this line in safe_mysqld from
! 262: test -z "$err_log" && err_log=$DATADIR/`/bin/hostname`.err
! 263: to
! 264: test -z "$err_log" && err_log=$DATADIR/localhost.err
! 265:
! 266: cp -p support-files/mysql.server /etc/rc.d/init.d/mysql
! 267: chmod 755 /etc/rc.d/init.d/mysql
! 268: /sbin/chkconfig --add mysql
! 269: /etc/rc.d/init.d/mysql start
! 270:
! 271: </PRE>
1.9 harris41 272: </P>
273: <P>This is how I installed the Msql-Mysql-modules perl modules.
274: <PRE>
275: [root@fenchurch Msql-Mysql-modules-1.2215]# perl Makefile.PL
276: Which drivers do you want to install?
277:
278: 1) MySQL only
279: 2) mSQL only (either of mSQL 1 or mSQL 2)
280: 3) MySQL and mSQL (either of mSQL 1 or mSQL 2)
281:
282: 4) mSQL 1 and mSQL 2
283: 5) MySQL, mSQL 1 and mSQL 2
284:
285: Enter the appropriate number: [3] 1
286:
287:
288: Do you want to install the MysqlPerl emulation? You might keep your old
289: Mysql module (to be distinguished from DBD::mysql!) if you are concerned
290: about compatibility to existing applications! [y] n
1.10 harris41 291: Where is your MySQL installed? Please tell me the directory that
292: contains the subdir 'include'. [/usr/local/mysql]
293: Which database should I use for testing the MySQL drivers? [test]
294: On which host is database test running (hostname, ip address
295: or host:port) [localhost]
1.11 ! harris41 296: User name for connecting to database test? [undef]
! 297: Password for connecting to database test? [undef]
1.9 harris41 298: [root@fenchurch Msql-Mysql-modules-1.2215]# make
299: [root@fenchurch Msql-Mysql-modules-1.2215]# make test
300: make[1]: Entering directory `/home/user/Msql-Mysql-modules-1.2215/mysql'
301: make[1]: Leaving directory `/home/user/Msql-Mysql-modules-1.2215/mysql'
302: make[1]: Entering directory `/home/user/Msql-Mysql-modules-1.2215/mysql'
303: PERL_DL_NONLAZY=1 /usr/bin/perl -I../blib/arch -I../blib/lib -I/usr/lib/perl5/5.00503/i386-linux -I/usr/lib/perl5/5.00503 -e 'use Test::Harness qw(&runtests $verbose); $verbose=0; runtests @ARGV;' t/*.t
304: t/00base............ok
305: t/10dsnlist.........ok
306: t/20createdrop......ok
307: t/30insertfetch.....ok
308: t/40bindparam.......ok
309: t/40blobs...........ok
310: t/40listfields......ok
311: t/40nulls...........ok
312: t/40numrows.........ok
313: t/50chopblanks......ok
314: t/50commit..........ok
315: t/60leaks...........skipping test on this platform
316: t/ak-dbd............ok
317: t/akmisc............ok
318: t/dbdadmin..........ok
319: t/mysql.............ok
320: t/mysql2............ok
321: All tests successful, 1 test skipped.
322: Files=17, Tests=732, 40 wallclock secs (15.38 cusr + 1.30 csys = 16.68 CPU)
323: [root@fenchurch Msql-Mysql-modules-1.2215]# make install
324:
325: These files are installed.
326: /usr/bin/dbimon
327: /usr/lib/perl5/man/man3/Bundle::DBD::mysql.3
328: /usr/lib/perl5/man/man3/DBD::mysql.3
329: /usr/lib/perl5/man/man3/Mysql.3
330: /usr/lib/perl5/site_perl/5.005/i386-linux/Bundle/DBD/mysql.pm
331: /usr/lib/perl5/site_perl/5.005/i386-linux/DBD/mysql.pm
332: /usr/lib/perl5/site_perl/5.005/i386-linux/Mysql.pm
333: /usr/lib/perl5/site_perl/5.005/i386-linux/Mysql/Statement.pm
334: /usr/lib/perl5/site_perl/5.005/i386-linux/auto/DBD/mysql/mysql.bs
335: /usr/lib/perl5/site_perl/5.005/i386-linux/auto/DBD/mysql/mysql.so
336: /usr/man/man1/dbimon.1
337: /usr/lib/perl5/site_perl/5.005/i386-linux/auto/Msql-Mysql-modules/.packlist
338: </PRE>
339: </P>
340: <P>
341: This is how I installed the DBI perl modules.
342: <PRE>
343: [root@fenchurch DBI-1.14]# perl Makefile.PL
344: *** Note:
345: The optional PlRPC-modules (RPC::PlServer etc) are not installed.
346: If you want to use the DBD::Proxy driver and DBI::ProxyServer
347: modules, then you'll need to install the RPC::PlServer, RPC::PlClient,
348: Storable and Net::Daemon modules. The CPAN Bundle::DBI may help you.
349: You can install them any time after installing the DBI.
350: You do *not* need these modules for typical DBI usage.
351:
352: Optional modules are available from any CPAN mirror, in particular
353: http://www.perl.com/CPAN/modules/by-module
354: http://www.perl.org/CPAN/modules/by-module
355: ftp://ftp.funet.fi/pub/languages/perl/CPAN/modules/by-module
356:
357: Checking if your kit is complete...
358: Looks good
359: Writing Makefile for DBI
360:
361: Remember to actually *read* the README file!
362: Use 'make' to build the software (dmake or nmake on Windows).
363: Then 'make test' to execute self tests.
364: Then 'make install' to install the DBI and then delete this working
365: directory before unpacking and building any DBD::* drivers.
366:
367: [root@fenchurch DBI-1.14]# make
368: [root@fenchurch DBI-1.14]# make test
369: PERL_DL_NONLAZY=1 /usr/bin/perl -Iblib/arch -Iblib/lib -I/usr/lib/perl5/5.00503/i386-linux -I/usr/lib/perl5/5.00503 -e 'use Test::Harness qw(&runtests $verbose); $verbose=0; runtests @ARGV;' t/*.t
370: t/basics............ok
371: t/dbidrv............ok
372: t/examp.............ok
373: t/meta..............ok
374: t/proxy.............skipping test on this platform
375: t/shell.............ok
376: t/subclass..........ok
377: All tests successful, 1 test skipped.
378: Files=7, Tests=179, 7 wallclock secs ( 6.46 cusr + 0.49 csys = 6.95 CPU)
379: PERL_DL_NONLAZY=1 /usr/bin/perl -Iblib/arch -Iblib/lib -I/usr/lib/perl5/5.00503/i386-linux -I/usr/lib/perl5/5.00503 test.pl
380: test.pl
1.11 ! harris41 381: DBI test application $Revision: 1.10 $
1.9 harris41 382: Using /home/user/DBI-1.14/blib
383: Switch: DBI 1.14 by Tim Bunce, 1.14
384: Available Drivers: ADO, ExampleP, Multiplex, Proxy, mysql
385: dbi:ExampleP:: testing 5 sets of 20 connections:
386: Connecting... 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20
387: Disconnecting...
388: Connecting... 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20
389: Disconnecting...
390: Connecting... 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20
391: Disconnecting...
392: Connecting... 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20
393: Disconnecting...
394: Connecting... 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20
395: Disconnecting...
396: Made 100 connections in 0 wallclock secs ( 0.22 usr + 0.03 sys = 0.25 CPU)
397:
398: Testing handle creation speed...
399: 5000 NullP statement handles cycled in 6.6 cpu+sys seconds (762 per sec)
400:
401: test.pl done
402:
403: [root@fenchurch DBI-1.14]# make install
404: These files are installed.
405: /usr/lib/perl5/site_perl/5.005/i386-linux/auto/DBI/.packlist
406: /usr/bin/dbiproxy
407: /usr/bin/dbish
408: /usr/lib/perl5/man/man3/Bundle::DBI.3
409: /usr/lib/perl5/man/man3/DBD::ADO.3
410: /usr/lib/perl5/man/man3/DBD::Multiplex.3
411: /usr/lib/perl5/man/man3/DBD::Proxy.3
412: /usr/lib/perl5/man/man3/DBI.3
413: /usr/lib/perl5/man/man3/DBI::DBD.3
414: /usr/lib/perl5/man/man3/DBI::FAQ.3
415: /usr/lib/perl5/man/man3/DBI::Format.3
416: /usr/lib/perl5/man/man3/DBI::ProxyServer.3
417: /usr/lib/perl5/man/man3/DBI::Shell.3
418: /usr/lib/perl5/man/man3/DBI::W32ODBC.3
419: /usr/lib/perl5/man/man3/Win32::DBIODBC.3
420: /usr/lib/perl5/site_perl/5.005/i386-linux/Bundle/DBI.pm
421: /usr/lib/perl5/site_perl/5.005/i386-linux/DBD/ADO.pm
422: /usr/lib/perl5/site_perl/5.005/i386-linux/DBD/ExampleP.pm
423: /usr/lib/perl5/site_perl/5.005/i386-linux/DBD/Multiplex.pm
424: /usr/lib/perl5/site_perl/5.005/i386-linux/DBD/NullP.pm
425: /usr/lib/perl5/site_perl/5.005/i386-linux/DBD/Proxy.pm
426: /usr/lib/perl5/site_perl/5.005/i386-linux/DBD/Sponge.pm
427: /usr/lib/perl5/site_perl/5.005/i386-linux/DBI.pm
428: /usr/lib/perl5/site_perl/5.005/i386-linux/DBI/DBD.pm
429: /usr/lib/perl5/site_perl/5.005/i386-linux/DBI/FAQ.pm
430: /usr/lib/perl5/site_perl/5.005/i386-linux/DBI/Format.pm
431: /usr/lib/perl5/site_perl/5.005/i386-linux/DBI/ProxyServer.pm
432: /usr/lib/perl5/site_perl/5.005/i386-linux/DBI/Shell.pm
433: /usr/lib/perl5/site_perl/5.005/i386-linux/DBI/W32ODBC.pm
434: /usr/lib/perl5/site_perl/5.005/i386-linux/Win32/DBIODBC.pm
435: /usr/lib/perl5/site_perl/5.005/i386-linux/auto/DBI/DBI.bs
436: /usr/lib/perl5/site_perl/5.005/i386-linux/auto/DBI/DBI.so
437: /usr/lib/perl5/site_perl/5.005/i386-linux/auto/DBI/DBIXS.h
438: /usr/lib/perl5/site_perl/5.005/i386-linux/auto/DBI/Driver.xst
439: /usr/lib/perl5/site_perl/5.005/i386-linux/auto/DBI/dbd_xsh.h
440: /usr/lib/perl5/site_perl/5.005/i386-linux/auto/DBI/dbi_sql.h
441: /usr/lib/perl5/site_perl/5.005/i386-linux/auto/DBI/dbipport.h
442: /usr/man/man1/dbiproxy.1
443: /usr/man/man1/dbish.1
444: </PRE>
1.8 harris41 445: </P>
446: <FONT COLOR="green"> old notes in green
447: <P>
1.4 harris41 448: The following set of tarballs was found to work together
449: properly on a LON-CAPA RedHat 6.2 system:
450: <UL>
451: <LI>DBI-1.13.tar.gz
452: <LI>Msql-Mysql-modules-1.2209.tar.gz
453: <LI>mysql-3.22.32.tar.gz
454: </UL>
455: </P>
456: <P>
457: Installation was simply a matter of following the instructions
458: and typing the several "make" commands for each
459: </P>
1.8 harris41 460: </FONT>
1.5 harris41 461: <H2>Configuration (automated)</H2>
1.4 harris41 462: <P>
463: Not yet developed. This will be part of an interface
464: present on LON-CAPA systems that can be launched by
465: entering the command <TT>/usr/sbin/loncapaconfig</TT>.
466: </P>
1.5 harris41 467: <H2>Manual configuration</H2>
1.4 harris41 468: <P>
469: This is not complete.
470: </P>
471: <P>
472: <STRONG>Starting the mysql daemon</STRONG>: Login on the Linux
473: system as user 'www'. Enter the command
474: <TT>/usr/local/bin/safe_mysqld &</TT>
475: </P>
476: <P>
477: <STRONG>Set a password for 'root'</STRONG>:
478: <TT>/usr/local/bin/mysqladmin -u root password 'new-password'</TT>
479: </P>
480: <P>
481: <STRONG>Adding a user</STRONG>: Start the mysql daemon. Login to the
482: mysql system as root (<TT>mysql -u root -p mysql</TT>)
483: and enter the right password (for instance 'newmysql'). Add the user
484: www
485: <PRE>
486: INSERT INTO user (Host, User, Password)
487: VALUES ('localhost','www',password('newmysql'));
488: </PRE>
489: </P>
490: <P>
491: <STRONG>Granting privileges to user 'www'</STRONG>:
492: <PRE>
493: GRANT ALL PRIVILEGES ON *.* TO www@localhost;
494: FLUSH PRIVILEGES;
495: </PRE>
496: </P>
497: <P>
498: <STRONG>Set the SQL server to start upon system startup</STRONG>:
499: Copy support-files/mysql.server to the right place on the system
500: (/etc/rc.d/...).
501: </P>
1.1 harris41 502: <P>
1.5 harris41 503: <STRONG>The Perl API</STRONG>
1.2 harris41 504: <PRE>
505: $dbh = DBI->connect( "DBI:mysql:loncapa",
506: "www",
507: "SOMEPASSWORD",
508: { RaiseError =>0,PrintError=>0});
509:
510: There is an obvious need to CONNECT to the database, and in order to do
511: this, there must be:
512: a RUNNING mysql daemon;
513: a DATABASE named "loncapa";
514: a USER named "www";
515: and an ABILITY for LON-CAPA on one machine to access
516: SQL database on another machine;
517:
518: So, here are some notes on implementing these configurations.
519:
520: ** RUNNING mysql daemon (safe_mysqld method)
521:
522: The recommended way to run the MySQL daemon is as a non-root user
523: (probably www)...
524:
525: so, 1) login as user www on the linux machine
526: 2) start the mysql daemon as /usr/local/bin/safe_mysqld &
527:
528: safe_mysqld only works if the local installation of MySQL is set to the
529: right directory permissions which I found to be:
530: chown www:users /usr/local/var/mysql
531: chown www:users /usr/local/lib/mysql
532: chown -R www:users /usr/local/mysql
533: chown www:users /usr/local/include/mysql
534: chown www:users /usr/local/var
535:
536: ** DATABASE named "loncapa"
537:
538: As user www, run this command
539: mysql -u root -p mysql
540: enter the password as SOMEPASSWORD
541:
542: This allows you to manually enter MySQL commands.
543: The MySQL command to generate the loncapa DATABASE is:
544:
545: CREATE DATABASE 'loncapa';
546:
547: ** USER named "www"
548:
549: As user www, run this command
550: mysql -u root -p mysql
551: enter the password as SOMEPASSWORD
552:
553: To add the user www to the MySQL server, and grant all
554: privileges on *.* to www@localhost identified by 'SOMEPASSWORD'
555: with grant option;
556:
557: INSERT INTO user (Host, User, Password)
558: VALUES ('localhost','www',password('SOMEPASSWORD'));
559:
560: GRANT ALL PRIVILEGES ON *.* TO www@localhost;
561:
562: FLUSH PRIVILEGES;
563:
564: ** ABILITY for LON-CAPA machines to communicate with SQL databases on
565: other LON-CAPA machines
566:
1.5 harris41 567: An up-to-date lond and lonsql.
568: </PRE>
569: </P>
570: <H2>Testing</H2>
571: <P>
1.11 ! harris41 572: To test the backend MySQL database, a number of commands should be
! 573: run after installation.
! 574: <UL>
! 575: <LI><TT>cd /usr/local/mysql/sql-bench; ./run-all-tests --small-test</TT></LI>
! 576: <BR>without the --small-test flag, this test can take more than 10 hours!
! 577: <LI><TT>cd /usr/local/mysql; bin/mysqladmin version</TT></LI>
! 578: <LI><TT>cd /usr/local/mysql; bin/mysqladmin variables</TT></LI>
! 579: <LI><TT>cd /usr/local/mysql; bin/mysqlshow</TT></LI>
! 580: <LI><TT>cd /usr/local/mysql; bin/mysqlshow mysql</TT></LI>
! 581: <LI><TT>cd /usr/local/mysql; bin/mysql -e "select host,db,user from db" mysql</TT></LI>
! 582: <LI><TT>cd /usr/local/mysql/mysql-test; ./test-run-all</TT></LI>
! 583:
! 584: </UL>
! 585: <P>
! 586: These are sections of perl code which helps test the LON-CAPA network.
1.5 harris41 587: <PRE>
588: <STRONG>** TEST the database connection with my current tester.pl code
589: which mimics what command will eventually be sent through lonc.</STRONG>
1.2 harris41 590:
1.5 harris41 591: $reply=reply(
592: "querysend:SELECT * FROM general_information WHERE Id='AAAAA'",$lonID);
593: </PRE>
594: </P>
595: <H2>Example sections of code relevant to LON-CAPA</H2>
596: <P>
1.2 harris41 597: Here are excerpts of code which implement the above handling:
1.5 harris41 598: </P>
599: <P>
600: <PRE>
601: <STRONG>**LONSQL
1.2 harris41 602: A subroutine from "lonsql" which establishes a child process for handling
1.5 harris41 603: database interactions.</STRONG>
1.2 harris41 604:
605: sub make_new_child {
606: my $pid;
607: my $sigset;
608:
609: # block signal for fork
610: $sigset = POSIX::SigSet->new(SIGINT);
611: sigprocmask(SIG_BLOCK, $sigset)
612: or die "Can't block SIGINT for fork: $!\n";
613:
614: die "fork: $!" unless defined ($pid = fork);
615:
616: if ($pid) {
617: # Parent records the child's birth and returns.
618: sigprocmask(SIG_UNBLOCK, $sigset)
619: or die "Can't unblock SIGINT for fork: $!\n";
620: $children{$pid} = 1;
621: $children++;
622: return;
623: } else {
624: # Child can *not* return from this subroutine.
625: $SIG{INT} = 'DEFAULT'; # make SIGINT kill us as it did before
626:
627: # unblock signals
628: sigprocmask(SIG_UNBLOCK, $sigset)
629: or die "Can't unblock SIGINT for fork: $!\n";
630:
631:
632: #open database handle
633: # making dbh global to avoid garbage collector
634: unless (
635: $dbh = DBI->connect("DBI:mysql:loncapa","www","SOMEPASSWORD",{ RaiseError =>0,PrintError=>0})
636: ) {
637: my $st=120+int(rand(240));
638: &logthis("<font color=blue>WARNING: Couldn't connect to database ($st secs): $@</font>");
639: print "database handle error\n";
640: sleep($st);
641: exit;
642:
643: };
644: # make sure that a database disconnection occurs with ending kill signals
645: $SIG{TERM}=$SIG{INT}=$SIG{QUIT}=$SIG{__DIE__}=\&DISCONNECT;
646:
647: # handle connections until we've reached $MAX_CLIENTS_PER_CHILD
648: for ($i=0; $i < $MAX_CLIENTS_PER_CHILD; $i++) {
649: $client = $server->accept() or last;
650:
651: # do something with the connection
652: $run = $run+1;
653: my $userinput = <$client>;
654: chomp($userinput);
655:
656: my ($conserver,$querytmp)=split(/&/,$userinput);
657: my $query=unescape($querytmp);
658:
659: #send query id which is pid_unixdatetime_runningcounter
660: $queryid = $thisserver;
661: $queryid .="_".($$)."_";
662: $queryid .= time."_";
663: $queryid .= $run;
664: print $client "$queryid\n";
665:
666: #prepare and execute the query
667: my $sth = $dbh->prepare($query);
668: my $result;
669: unless ($sth->execute())
670: {
671: &logthis("<font color=blue>WARNING: Could not retrieve from database: $@</font>");
672: $result="";
673: }
674: else {
675: my $r1=$sth->fetchall_arrayref;
676: my @r2; map {my $a=$_; my @b=map {escape($_)} @$a; push @r2,join(",", @b)} (@$r1);
677: $result=join("&",@r2) . "\n";
678: }
679: &reply("queryreply:$queryid:$result",$conserver);
680:
681: }
682:
683: # tidy up gracefully and finish
684:
685: #close the database handle
686: $dbh->disconnect
687: or &logthis("<font color=blue>WARNING: Couldn't disconnect from database $DBI::errstr ($st secs): $@</font>");
688:
689: # this exit is VERY important, otherwise the child will become
690: # a producer of more and more children, forking yourself into
691: # process death.
692: exit;
693: }
694: }
1.5 harris41 695: </P>
696: <P>
697: <STRONG>** LOND enabling of MySQL requests</STRONG>
698: <BR />
699: This code is part of every lond child process in the
700: way that it parses command request syntax sent to it
701: from lonc processes. Based on the diagram above, querysend
702: corresponds to B-lonc sending the result of the query.
703: queryreply corresponds to B-lond indicating that it has
704: received the request and will start the database transaction
705: (it returns "ok" to
706: A-lonc ($client)).
707: <PRE>
1.2 harris41 708: # ------------------------------------------------------------------- querysend
709: } elsif ($userinput =~ /^querysend/) {
710: my ($cmd,$query)=split(/:/,$userinput);
711: $query=~s/\n*$//g;
712: print $client sqlreply("$hostid{$clientip}\&$query")."\n";
713: # ------------------------------------------------------------------ queryreply
714: } elsif ($userinput =~ /^queryreply/) {
715: my ($cmd,$id,$reply)=split(/:/,$userinput);
716: my $store;
717: my $execdir=$perlvar{'lonDaemons'};
718: if ($store=IO::File->new(">$execdir/tmp/$id")) {
719: print $store $reply;
720: close $store;
721: print $client "ok\n";
722: }
723: else {
724: print $client "error:$!\n";
725: }
726:
1.5 harris41 727: </PRE>
1.2 harris41 728:
1.1 harris41 729: </P>
730: </BODY>
1.3 harris41 731: </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