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