doc/build/loncapasqldatabase.html - view

File: [LON-CAPA] / doc / build / Attic / loncapasqldatabase.html
Revision 1.9: download - view: text, annotated - select for diffs
Wed Feb 14 15:20:55 2001 UTC (23 years, 4 months ago) by harris41
Branches: MAIN
CVS tags: HEAD

entered in manual installation details for DBI and MySQL -Scott

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/14/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: It might be worthwhile to look at /usr/local/mysql/manual.html. 36: It is quite in depth. 37: </P> 38: <H2>Current status of documentation</H2> 39: <P> 40: I am going to begin documentation by inserting what notes 41: I have into this file. I will be subsequently rearranging 42: them and editing them based on the tests that I conduct. 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> 49: <H2>Current status of implementation</H2> 50: <P> 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> 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 65: has been compiled in an RPM (LON-CAPA-mysql, with perl components 66: a part of LON-CAPA-systemperl). 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> 91: <H2>Purpose within LON-CAPA</H2> 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> 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> 169: <H2>Installation</H2> 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 175: engine. Related perl interfaces (Perl::DBI, Perl::Msql-Mysql) 176: are encoded in the LON-CAPA-systemperl RPM. 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> 199: <H2>Installation from source</H2> 200: <P> 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). 219: <LI>http://www.cpan.org/authors/id/TIMB/DBI-1.14.tar.gz 220: <BR>This tarball Released 14th June 2000 221: </UL> 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: 1.9 $ 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> 393: </P> 394: <FONT COLOR="green"> old notes in green 395: <P> 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> 408: </FONT> 409: <H2>Configuration (automated)</H2> 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> 415: <H2>Manual configuration</H2> 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> 450: <P> 451: <STRONG>The Perl API</STRONG> 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: 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> 523: 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> 530: Here are excerpts of code which implement the above handling: 531: </P> 532: <P> 533: <PRE> 534: <STRONG>**LONSQL 535: A subroutine from "lonsql" which establishes a child process for handling 536: database interactions.</STRONG> 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: } 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> 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: 660: </PRE> 661: 662: </P> 663: </BODY> 664: </HTML>