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