doc/build/loncapasqldatabase.html - view

File: [LON-CAPA] / doc / build / Attic / loncapasqldatabase.html
Revision 1.15: download - view: text, annotated - select for diffs
Tue Feb 20 22:24:45 2001 UTC (23 years, 3 months ago) by harris41
Branches: MAIN
CVS tags: HEAD

more notes .. including security -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/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: 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> 60: These commands create the loncapameta database. 61: <PRE> 62: mysql> CREATE DATABASE IF NOT EXISTS loncapa; 63: mysql> USE loncapa; 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; 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 75: 76: Red Hat Linux release 6.2 (Zoot) 77: Kernel 2.2.16-3 on an i586 78: </PRE> 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: 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> 101: It might be worthwhile to look at /usr/local/mysql/manual.html. 102: It is quite in depth. 103: </P> 104: <H2>Current status of documentation</H2> 105: <P> 106: I am going to begin documentation by inserting what notes 107: I have into this file. I will be subsequently rearranging 108: them and editing them based on the tests that I conduct. 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> 115: <H2>Current status of implementation</H2> 116: <P> 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> 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 131: has been compiled in an RPM (LON-CAPA-mysql, with perl components 132: a part of LON-CAPA-systemperl). 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> 157: <H2>Purpose within LON-CAPA</H2> 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> 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> 235: <H2>Installation</H2> 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 241: engine. Related perl interfaces (Perl::DBI, Perl::Msql-Mysql) 242: are encoded in the LON-CAPA-systemperl RPM. 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> 265: <H2>Installation from source</H2> 266: <P> 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). 285: <LI>http://www.cpan.org/authors/id/TIMB/DBI-1.14.tar.gz 286: <BR>This tarball Released 14th June 2000 287: </UL> 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). 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> 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 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] 362: User name for connecting to database test? [undef] 363: Password for connecting to database test? [undef] 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. 408: <PRE>$dbh 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 447: DBI test application $Revision: 1.15 $ 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> 511: </P> 512: <FONT COLOR="green"> old notes in green 513: <P> 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> 526: </FONT> 527: <H2>Configuration (automated)</H2> 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> 533: <H2>Manual configuration</H2> 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> 568: <P> 569: <STRONG>The Perl API</STRONG> 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: 633: An up-to-date lond and lonsql. 634: </PRE> 635: </P> 636: <H2>Testing</H2> 637: <P> 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. 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> 656: 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> 663: Here are excerpts of code which implement the above handling: 664: </P> 665: <P> 666: <PRE> 667: <STRONG>**LONSQL 668: A subroutine from "lonsql" which establishes a child process for handling 669: database interactions.</STRONG> 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: } 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> 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: 793: </PRE> 794: 795: </P> 796: </BODY> 797: </HTML>