Annotation of doc/build/loncapasqldatabase.html, revision 1.9
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).
! 227: </P>
! 228: <P>This is how I installed the Msql-Mysql-modules perl modules.
! 229: <PRE>
! 230: [root@fenchurch Msql-Mysql-modules-1.2215]# perl Makefile.PL
! 231: Which drivers do you want to install?
! 232:
! 233: 1) MySQL only
! 234: 2) mSQL only (either of mSQL 1 or mSQL 2)
! 235: 3) MySQL and mSQL (either of mSQL 1 or mSQL 2)
! 236:
! 237: 4) mSQL 1 and mSQL 2
! 238: 5) MySQL, mSQL 1 and mSQL 2
! 239:
! 240: Enter the appropriate number: [3] 1
! 241:
! 242:
! 243: Do you want to install the MysqlPerl emulation? You might keep your old
! 244: Mysql module (to be distinguished from DBD::mysql!) if you are concerned
! 245: about compatibility to existing applications! [y] n
! 246: [root@fenchurch Msql-Mysql-modules-1.2215]# make
! 247: [root@fenchurch Msql-Mysql-modules-1.2215]# make test
! 248: make[1]: Entering directory `/home/user/Msql-Mysql-modules-1.2215/mysql'
! 249: make[1]: Leaving directory `/home/user/Msql-Mysql-modules-1.2215/mysql'
! 250: make[1]: Entering directory `/home/user/Msql-Mysql-modules-1.2215/mysql'
! 251: 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
! 252: t/00base............ok
! 253: t/10dsnlist.........ok
! 254: t/20createdrop......ok
! 255: t/30insertfetch.....ok
! 256: t/40bindparam.......ok
! 257: t/40blobs...........ok
! 258: t/40listfields......ok
! 259: t/40nulls...........ok
! 260: t/40numrows.........ok
! 261: t/50chopblanks......ok
! 262: t/50commit..........ok
! 263: t/60leaks...........skipping test on this platform
! 264: t/ak-dbd............ok
! 265: t/akmisc............ok
! 266: t/dbdadmin..........ok
! 267: t/mysql.............ok
! 268: t/mysql2............ok
! 269: All tests successful, 1 test skipped.
! 270: Files=17, Tests=732, 40 wallclock secs (15.38 cusr + 1.30 csys = 16.68 CPU)
! 271: [root@fenchurch Msql-Mysql-modules-1.2215]# make install
! 272:
! 273: These files are installed.
! 274: /usr/bin/dbimon
! 275: /usr/lib/perl5/man/man3/Bundle::DBD::mysql.3
! 276: /usr/lib/perl5/man/man3/DBD::mysql.3
! 277: /usr/lib/perl5/man/man3/Mysql.3
! 278: /usr/lib/perl5/site_perl/5.005/i386-linux/Bundle/DBD/mysql.pm
! 279: /usr/lib/perl5/site_perl/5.005/i386-linux/DBD/mysql.pm
! 280: /usr/lib/perl5/site_perl/5.005/i386-linux/Mysql.pm
! 281: /usr/lib/perl5/site_perl/5.005/i386-linux/Mysql/Statement.pm
! 282: /usr/lib/perl5/site_perl/5.005/i386-linux/auto/DBD/mysql/mysql.bs
! 283: /usr/lib/perl5/site_perl/5.005/i386-linux/auto/DBD/mysql/mysql.so
! 284: /usr/man/man1/dbimon.1
! 285: /usr/lib/perl5/site_perl/5.005/i386-linux/auto/Msql-Mysql-modules/.packlist
! 286: </PRE>
! 287: </P>
! 288: <P>
! 289: This is how I installed the DBI perl modules.
! 290: <PRE>
! 291: [root@fenchurch DBI-1.14]# perl Makefile.PL
! 292: *** Note:
! 293: The optional PlRPC-modules (RPC::PlServer etc) are not installed.
! 294: If you want to use the DBD::Proxy driver and DBI::ProxyServer
! 295: modules, then you'll need to install the RPC::PlServer, RPC::PlClient,
! 296: Storable and Net::Daemon modules. The CPAN Bundle::DBI may help you.
! 297: You can install them any time after installing the DBI.
! 298: You do *not* need these modules for typical DBI usage.
! 299:
! 300: Optional modules are available from any CPAN mirror, in particular
! 301: http://www.perl.com/CPAN/modules/by-module
! 302: http://www.perl.org/CPAN/modules/by-module
! 303: ftp://ftp.funet.fi/pub/languages/perl/CPAN/modules/by-module
! 304:
! 305: Checking if your kit is complete...
! 306: Looks good
! 307: Writing Makefile for DBI
! 308:
! 309: Remember to actually *read* the README file!
! 310: Use 'make' to build the software (dmake or nmake on Windows).
! 311: Then 'make test' to execute self tests.
! 312: Then 'make install' to install the DBI and then delete this working
! 313: directory before unpacking and building any DBD::* drivers.
! 314:
! 315: [root@fenchurch DBI-1.14]# make
! 316: [root@fenchurch DBI-1.14]# make test
! 317: 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
! 318: t/basics............ok
! 319: t/dbidrv............ok
! 320: t/examp.............ok
! 321: t/meta..............ok
! 322: t/proxy.............skipping test on this platform
! 323: t/shell.............ok
! 324: t/subclass..........ok
! 325: All tests successful, 1 test skipped.
! 326: Files=7, Tests=179, 7 wallclock secs ( 6.46 cusr + 0.49 csys = 6.95 CPU)
! 327: 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
! 328: test.pl
! 329: DBI test application $Revision: 10.4 $
! 330: Using /home/user/DBI-1.14/blib
! 331: Switch: DBI 1.14 by Tim Bunce, 1.14
! 332: Available Drivers: ADO, ExampleP, Multiplex, Proxy, mysql
! 333: dbi:ExampleP:: testing 5 sets of 20 connections:
! 334: Connecting... 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20
! 335: Disconnecting...
! 336: Connecting... 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20
! 337: Disconnecting...
! 338: Connecting... 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20
! 339: Disconnecting...
! 340: Connecting... 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20
! 341: Disconnecting...
! 342: Connecting... 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20
! 343: Disconnecting...
! 344: Made 100 connections in 0 wallclock secs ( 0.22 usr + 0.03 sys = 0.25 CPU)
! 345:
! 346: Testing handle creation speed...
! 347: 5000 NullP statement handles cycled in 6.6 cpu+sys seconds (762 per sec)
! 348:
! 349: test.pl done
! 350:
! 351: [root@fenchurch DBI-1.14]# make install
! 352: These files are installed.
! 353: /usr/lib/perl5/site_perl/5.005/i386-linux/auto/DBI/.packlist
! 354: /usr/bin/dbiproxy
! 355: /usr/bin/dbish
! 356: /usr/lib/perl5/man/man3/Bundle::DBI.3
! 357: /usr/lib/perl5/man/man3/DBD::ADO.3
! 358: /usr/lib/perl5/man/man3/DBD::Multiplex.3
! 359: /usr/lib/perl5/man/man3/DBD::Proxy.3
! 360: /usr/lib/perl5/man/man3/DBI.3
! 361: /usr/lib/perl5/man/man3/DBI::DBD.3
! 362: /usr/lib/perl5/man/man3/DBI::FAQ.3
! 363: /usr/lib/perl5/man/man3/DBI::Format.3
! 364: /usr/lib/perl5/man/man3/DBI::ProxyServer.3
! 365: /usr/lib/perl5/man/man3/DBI::Shell.3
! 366: /usr/lib/perl5/man/man3/DBI::W32ODBC.3
! 367: /usr/lib/perl5/man/man3/Win32::DBIODBC.3
! 368: /usr/lib/perl5/site_perl/5.005/i386-linux/Bundle/DBI.pm
! 369: /usr/lib/perl5/site_perl/5.005/i386-linux/DBD/ADO.pm
! 370: /usr/lib/perl5/site_perl/5.005/i386-linux/DBD/ExampleP.pm
! 371: /usr/lib/perl5/site_perl/5.005/i386-linux/DBD/Multiplex.pm
! 372: /usr/lib/perl5/site_perl/5.005/i386-linux/DBD/NullP.pm
! 373: /usr/lib/perl5/site_perl/5.005/i386-linux/DBD/Proxy.pm
! 374: /usr/lib/perl5/site_perl/5.005/i386-linux/DBD/Sponge.pm
! 375: /usr/lib/perl5/site_perl/5.005/i386-linux/DBI.pm
! 376: /usr/lib/perl5/site_perl/5.005/i386-linux/DBI/DBD.pm
! 377: /usr/lib/perl5/site_perl/5.005/i386-linux/DBI/FAQ.pm
! 378: /usr/lib/perl5/site_perl/5.005/i386-linux/DBI/Format.pm
! 379: /usr/lib/perl5/site_perl/5.005/i386-linux/DBI/ProxyServer.pm
! 380: /usr/lib/perl5/site_perl/5.005/i386-linux/DBI/Shell.pm
! 381: /usr/lib/perl5/site_perl/5.005/i386-linux/DBI/W32ODBC.pm
! 382: /usr/lib/perl5/site_perl/5.005/i386-linux/Win32/DBIODBC.pm
! 383: /usr/lib/perl5/site_perl/5.005/i386-linux/auto/DBI/DBI.bs
! 384: /usr/lib/perl5/site_perl/5.005/i386-linux/auto/DBI/DBI.so
! 385: /usr/lib/perl5/site_perl/5.005/i386-linux/auto/DBI/DBIXS.h
! 386: /usr/lib/perl5/site_perl/5.005/i386-linux/auto/DBI/Driver.xst
! 387: /usr/lib/perl5/site_perl/5.005/i386-linux/auto/DBI/dbd_xsh.h
! 388: /usr/lib/perl5/site_perl/5.005/i386-linux/auto/DBI/dbi_sql.h
! 389: /usr/lib/perl5/site_perl/5.005/i386-linux/auto/DBI/dbipport.h
! 390: /usr/man/man1/dbiproxy.1
! 391: /usr/man/man1/dbish.1
! 392: </PRE>
1.8 harris41 393: </P>
394: <FONT COLOR="green"> old notes in green
395: <P>
1.4 harris41 396: The following set of tarballs was found to work together
397: properly on a LON-CAPA RedHat 6.2 system:
398: <UL>
399: <LI>DBI-1.13.tar.gz
400: <LI>Msql-Mysql-modules-1.2209.tar.gz
401: <LI>mysql-3.22.32.tar.gz
402: </UL>
403: </P>
404: <P>
405: Installation was simply a matter of following the instructions
406: and typing the several "make" commands for each
407: </P>
1.8 harris41 408: </FONT>
1.5 harris41 409: <H2>Configuration (automated)</H2>
1.4 harris41 410: <P>
411: Not yet developed. This will be part of an interface
412: present on LON-CAPA systems that can be launched by
413: entering the command <TT>/usr/sbin/loncapaconfig</TT>.
414: </P>
1.5 harris41 415: <H2>Manual configuration</H2>
1.4 harris41 416: <P>
417: This is not complete.
418: </P>
419: <P>
420: <STRONG>Starting the mysql daemon</STRONG>: Login on the Linux
421: system as user 'www'. Enter the command
422: <TT>/usr/local/bin/safe_mysqld &</TT>
423: </P>
424: <P>
425: <STRONG>Set a password for 'root'</STRONG>:
426: <TT>/usr/local/bin/mysqladmin -u root password 'new-password'</TT>
427: </P>
428: <P>
429: <STRONG>Adding a user</STRONG>: Start the mysql daemon. Login to the
430: mysql system as root (<TT>mysql -u root -p mysql</TT>)
431: and enter the right password (for instance 'newmysql'). Add the user
432: www
433: <PRE>
434: INSERT INTO user (Host, User, Password)
435: VALUES ('localhost','www',password('newmysql'));
436: </PRE>
437: </P>
438: <P>
439: <STRONG>Granting privileges to user 'www'</STRONG>:
440: <PRE>
441: GRANT ALL PRIVILEGES ON *.* TO www@localhost;
442: FLUSH PRIVILEGES;
443: </PRE>
444: </P>
445: <P>
446: <STRONG>Set the SQL server to start upon system startup</STRONG>:
447: Copy support-files/mysql.server to the right place on the system
448: (/etc/rc.d/...).
449: </P>
1.1 harris41 450: <P>
1.5 harris41 451: <STRONG>The Perl API</STRONG>
1.2 harris41 452: <PRE>
453: $dbh = DBI->connect( "DBI:mysql:loncapa",
454: "www",
455: "SOMEPASSWORD",
456: { RaiseError =>0,PrintError=>0});
457:
458: There is an obvious need to CONNECT to the database, and in order to do
459: this, there must be:
460: a RUNNING mysql daemon;
461: a DATABASE named "loncapa";
462: a USER named "www";
463: and an ABILITY for LON-CAPA on one machine to access
464: SQL database on another machine;
465:
466: So, here are some notes on implementing these configurations.
467:
468: ** RUNNING mysql daemon (safe_mysqld method)
469:
470: The recommended way to run the MySQL daemon is as a non-root user
471: (probably www)...
472:
473: so, 1) login as user www on the linux machine
474: 2) start the mysql daemon as /usr/local/bin/safe_mysqld &
475:
476: safe_mysqld only works if the local installation of MySQL is set to the
477: right directory permissions which I found to be:
478: chown www:users /usr/local/var/mysql
479: chown www:users /usr/local/lib/mysql
480: chown -R www:users /usr/local/mysql
481: chown www:users /usr/local/include/mysql
482: chown www:users /usr/local/var
483:
484: ** DATABASE named "loncapa"
485:
486: As user www, run this command
487: mysql -u root -p mysql
488: enter the password as SOMEPASSWORD
489:
490: This allows you to manually enter MySQL commands.
491: The MySQL command to generate the loncapa DATABASE is:
492:
493: CREATE DATABASE 'loncapa';
494:
495: ** USER named "www"
496:
497: As user www, run this command
498: mysql -u root -p mysql
499: enter the password as SOMEPASSWORD
500:
501: To add the user www to the MySQL server, and grant all
502: privileges on *.* to www@localhost identified by 'SOMEPASSWORD'
503: with grant option;
504:
505: INSERT INTO user (Host, User, Password)
506: VALUES ('localhost','www',password('SOMEPASSWORD'));
507:
508: GRANT ALL PRIVILEGES ON *.* TO www@localhost;
509:
510: FLUSH PRIVILEGES;
511:
512: ** ABILITY for LON-CAPA machines to communicate with SQL databases on
513: other LON-CAPA machines
514:
1.5 harris41 515: An up-to-date lond and lonsql.
516: </PRE>
517: </P>
518: <H2>Testing</H2>
519: <P>
520: <PRE>
521: <STRONG>** TEST the database connection with my current tester.pl code
522: which mimics what command will eventually be sent through lonc.</STRONG>
1.2 harris41 523:
1.5 harris41 524: $reply=reply(
525: "querysend:SELECT * FROM general_information WHERE Id='AAAAA'",$lonID);
526: </PRE>
527: </P>
528: <H2>Example sections of code relevant to LON-CAPA</H2>
529: <P>
1.2 harris41 530: Here are excerpts of code which implement the above handling:
1.5 harris41 531: </P>
532: <P>
533: <PRE>
534: <STRONG>**LONSQL
1.2 harris41 535: A subroutine from "lonsql" which establishes a child process for handling
1.5 harris41 536: database interactions.</STRONG>
1.2 harris41 537:
538: sub make_new_child {
539: my $pid;
540: my $sigset;
541:
542: # block signal for fork
543: $sigset = POSIX::SigSet->new(SIGINT);
544: sigprocmask(SIG_BLOCK, $sigset)
545: or die "Can't block SIGINT for fork: $!\n";
546:
547: die "fork: $!" unless defined ($pid = fork);
548:
549: if ($pid) {
550: # Parent records the child's birth and returns.
551: sigprocmask(SIG_UNBLOCK, $sigset)
552: or die "Can't unblock SIGINT for fork: $!\n";
553: $children{$pid} = 1;
554: $children++;
555: return;
556: } else {
557: # Child can *not* return from this subroutine.
558: $SIG{INT} = 'DEFAULT'; # make SIGINT kill us as it did before
559:
560: # unblock signals
561: sigprocmask(SIG_UNBLOCK, $sigset)
562: or die "Can't unblock SIGINT for fork: $!\n";
563:
564:
565: #open database handle
566: # making dbh global to avoid garbage collector
567: unless (
568: $dbh = DBI->connect("DBI:mysql:loncapa","www","SOMEPASSWORD",{ RaiseError =>0,PrintError=>0})
569: ) {
570: my $st=120+int(rand(240));
571: &logthis("<font color=blue>WARNING: Couldn't connect to database ($st secs): $@</font>");
572: print "database handle error\n";
573: sleep($st);
574: exit;
575:
576: };
577: # make sure that a database disconnection occurs with ending kill signals
578: $SIG{TERM}=$SIG{INT}=$SIG{QUIT}=$SIG{__DIE__}=\&DISCONNECT;
579:
580: # handle connections until we've reached $MAX_CLIENTS_PER_CHILD
581: for ($i=0; $i < $MAX_CLIENTS_PER_CHILD; $i++) {
582: $client = $server->accept() or last;
583:
584: # do something with the connection
585: $run = $run+1;
586: my $userinput = <$client>;
587: chomp($userinput);
588:
589: my ($conserver,$querytmp)=split(/&/,$userinput);
590: my $query=unescape($querytmp);
591:
592: #send query id which is pid_unixdatetime_runningcounter
593: $queryid = $thisserver;
594: $queryid .="_".($$)."_";
595: $queryid .= time."_";
596: $queryid .= $run;
597: print $client "$queryid\n";
598:
599: #prepare and execute the query
600: my $sth = $dbh->prepare($query);
601: my $result;
602: unless ($sth->execute())
603: {
604: &logthis("<font color=blue>WARNING: Could not retrieve from database: $@</font>");
605: $result="";
606: }
607: else {
608: my $r1=$sth->fetchall_arrayref;
609: my @r2; map {my $a=$_; my @b=map {escape($_)} @$a; push @r2,join(",", @b)} (@$r1);
610: $result=join("&",@r2) . "\n";
611: }
612: &reply("queryreply:$queryid:$result",$conserver);
613:
614: }
615:
616: # tidy up gracefully and finish
617:
618: #close the database handle
619: $dbh->disconnect
620: or &logthis("<font color=blue>WARNING: Couldn't disconnect from database $DBI::errstr ($st secs): $@</font>");
621:
622: # this exit is VERY important, otherwise the child will become
623: # a producer of more and more children, forking yourself into
624: # process death.
625: exit;
626: }
627: }
1.5 harris41 628: </P>
629: <P>
630: <STRONG>** LOND enabling of MySQL requests</STRONG>
631: <BR />
632: This code is part of every lond child process in the
633: way that it parses command request syntax sent to it
634: from lonc processes. Based on the diagram above, querysend
635: corresponds to B-lonc sending the result of the query.
636: queryreply corresponds to B-lond indicating that it has
637: received the request and will start the database transaction
638: (it returns "ok" to
639: A-lonc ($client)).
640: <PRE>
1.2 harris41 641: # ------------------------------------------------------------------- querysend
642: } elsif ($userinput =~ /^querysend/) {
643: my ($cmd,$query)=split(/:/,$userinput);
644: $query=~s/\n*$//g;
645: print $client sqlreply("$hostid{$clientip}\&$query")."\n";
646: # ------------------------------------------------------------------ queryreply
647: } elsif ($userinput =~ /^queryreply/) {
648: my ($cmd,$id,$reply)=split(/:/,$userinput);
649: my $store;
650: my $execdir=$perlvar{'lonDaemons'};
651: if ($store=IO::File->new(">$execdir/tmp/$id")) {
652: print $store $reply;
653: close $store;
654: print $client "ok\n";
655: }
656: else {
657: print $client "error:$!\n";
658: }
659:
1.5 harris41 660: </PRE>
1.2 harris41 661:
1.1 harris41 662: </P>
663: </BODY>
1.3 harris41 664: </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