From 6b607da839992bead01d7cba308f216e17eed520 Mon Sep 17 00:00:00 2001 From: "barnboy%trilobyte.net" <> Date: Thu, 8 Mar 2001 13:35:44 +0000 Subject: Documentation update; added docs/sgml, docs/html, docs/txt. No text version of The Bugzilla Guide availabe yet, however. --- docs/sgml/database.sgml | 629 ++++++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 629 insertions(+) create mode 100644 docs/sgml/database.sgml (limited to 'docs/sgml/database.sgml') diff --git a/docs/sgml/database.sgml b/docs/sgml/database.sgml new file mode 100644 index 000000000..16c72494e --- /dev/null +++ b/docs/sgml/database.sgml @@ -0,0 +1,629 @@ + + + + +The Bugzilla Database + +This document really needs to be updated with more fleshed out information about primary keys, interrelationships, and maybe some nifty tables to document dependencies. Any takers? MPB + +
+ Database Schema Chart + + + + + + + + Database Relationships + + + + Bugzilla database relationships chart + + + +
+ +
+MySQL Bugzilla Database Introduction + + +Contributor(s): Matthew P. Barnson (mbarnson@excitehome.net) + +Last update: May 16, 2000 + +Changes: +Version 1.0: Initial public release (May 16, 2000) + +Maintainer: Matthew P. Barnson (mbarnson@excitehome.net) + + +=== +Table Of Contents +=== + +FOREWORD +INTRODUCTION +THE BASICS +THE TABLES +THE DETAILS + + + +=== +FOREWORD +=== + + This information comes straight from my life. I was forced to learn how +Bugzilla organizes database because of nitpicky requests from users for tiny +changes in wording, rather than having people re-educate themselves or +figure out how to work our procedures around the tool. It sucks, but it can +and will happen to you, so learn how the schema works and deal with it when it +comes. + + I'm sorry this version is plain text. I can whip this info out a lot faster +if I'm not concerned about complex formatting. I'll get it into sgml for easy +portability as time permits. + + The Bugzilla Database Schema has a home! In addition to availability via CVS +and released versions 2.12 and higher of Bugzilla, you can find the latest & +greatest version of the Bugzilla Database Schema at +http://www.trilobyte.net/barnsons/. This is a living document; please be sure +you are up-to-date with the latest version before mirroring. + + The Bugzilla Database Schema is designed to provide vital information +regarding the structure of the MySQL database. Where appropriate, this +document will refer to URLs rather than including documents in their entirety +to ensure completeness even should this paper become out of date. + + This document is not maintained by Netscape or Netscape employees, so please +do not contact them regarding errors or omissions contained herein. Please +direct all questions, comments, updates, flames, etc. to Matthew P. Barnson +mbarnson@excitehome.net) (barnboy or barnhome on irc.mozilla.org in +#mozwebtools). + + I'm sure I've made some glaring errors or omissions in this paper -- please +email me corrections or post corrections to the +netscape.public.mozilla.webtools newsgroup. + + + +=== +INTRODUCTION +=== + + + + So, here you are with your brand-new installation of Bugzilla. You've got +MySQL set up, Apache working right, Perl DBI and DBD talking to the database +flawlessly. Maybe you've even entered a few test bugs to make sure email's +working; people seem to be notified of new bugs and changes, and you can +enter and edit bugs to your heart's content. Perhaps you've gone through the +trouble of setting up a gateway for people to submit bugs to your database via +email, have had a few people test it, and received rave reviews from your beta +testers. + + What's the next thing you do? Outline a training strategy for your +development team, of course, and bring them up to speed on the new tool you've +labored over for hours. + + Your first training session starts off very well! You have a captive +audience which seems enraptured by the efficiency embodied in this thing called +"Bugzilla". You are caught up describing the nifty features, how people can +save favorite queries in the database, set them up as headers and footers on +their pages, customize their layouts, generate reports, track status with +greater efficiency than ever before, leap tall buildings with a single bound +and rescue Jane from the clutches of Certain Death! + + But Certain Death speaks up -- a tiny voice, from the dark corners of the +conference room. "I have a concern," the voice hisses from the darkness, +"about the use of the word 'verified'. + + The room, previously filled with happy chatter, lapses into reverential +silence as Certain Death (better known as the Vice President of Software +Engineering) continues. "You see, for two years we've used the word 'verified' +to indicate that a developer or quality assurance engineer has confirmed that, +in fact, a bug is valid. I don't want to lose two years of training to a +new software product. You need to change the bug status of 'verified' to +'approved' as soon as possible. To avoid confusion, of course." + + Oh no! Terror strikes your heart, as you find yourself mumbling "yes, yes, I +don't think that would be a problem," You review the changes with Certain +Death, and continue to jabber on, "no, it's not too big a change. I mean, we +have the source code, right? You know, 'Use the Source, Luke' and all that... +no problem," All the while you quiver inside like a beached jellyfish bubbling, +burbling, and boiling on a hot Jamaican sand dune... + + Thus begins your adventure into the heart of Bugzilla. You've been forced +to learn about non-portable enum() fields, varchar columns, and tinyint +definitions. The Adventure Awaits You! + + + +=== +The Basics +=== + + If you were like me, at this point you're totally clueless about the +internals of MySQL, and if it weren't for this executive order from the Vice +President you couldn't care less about the difference between a "bigint" and a +"tinyint" entry in MySQL. I'd refer you first to the MySQL documentation, +available at http://www.mysql.com/doc.html, but that's mostly a confusing +morass of high-level database jargon. Here are the basics you need to know +about the database to proceed: + +1. To connect to your database, type "mysql -u root" at the command prompt as +any user. If this works without asking you for a password, SHAME ON YOU! You +should have locked your security down like the README told you to. You can +find details on locking down your database in the Bugzilla FAQ in this +directory (under "Security"), or more robust security generalities in the +MySQL searchable documentation at +http://www.mysql.com/php/manual.php3?section=Privilege_system . + +2. You should now be at a prompt that looks like this: + +mysql> + + At the prompt, if "bugs" is the name of your Bugzilla database, type: + +mysql> use bugs; + + (don't forget the ";" at the end of each line, or you'll be kicking yourself +all the way through this documentation) + Young Grasshopper, you are now ready for the unveiling of the Bugzilla +database, in the next section... + + + +=== +THE TABLES +=== + + Imagine your MySQL database as a series of spreadsheets, and you won't be too +far off. If you use this command: + +mysql> show tables from bugs; + + you'll be able to see all the "spreadsheets" (tables) in your database. Cool, +huh? It's kinda' like a filesystem, only much faster and more robust. Come +on, I'll show you more! + + From the command issued above, you should now have some output that looks +like this: + ++-------------------+ +| Tables in bugs | ++-------------------+ +| attachments | +| bugs | +| bugs_activity | +| cc | +| components | +| dependencies | +| fielddefs | +| groups | +| keyworddefs | +| keywords | +| logincookies | +| longdescs | +| milestones | +| namedqueries | +| products | +| profiles | +| profiles_activity | +| shadowlog | +| versions | +| votes | +| watch | ++-------------------+ + + + If it doesn't look quite the same, that probably means it's time to +update this documentation :) + + Here's an overview of what each table does. Most columns in each table have +descriptive names that make it fairly trivial to figure out their jobs. + +attachments: This table stores all attachments to bugs. It tends to be your +largest table, yet also generally has the fewest entries because file +attachments are so (relatively) large. + +bugs: This is the core of your system. The bugs table stores most of the +current information about a bug, with the exception of the info stored in the +other tables. + +bugs_activity: This stores information regarding what changes are made to bugs +when -- a history file. + +cc: This tiny table simply stores all the CC information for any bug which has +any entries in the CC field of the bug. Note that, like most other tables in +Bugzilla, it does not refer to users by their user names, but by their unique +userid, stored as a primary key in the profiles table. + +components: This stores the programs and components (or products and +components, in newer Bugzilla parlance) for Bugzilla. Curiously, the "program" +(product) field is the full name of the product, rather than some other unique +identifier, like bug_id and user_id are elsewhere in the database. + +dependencies: Stores data about those cool dependency trees. + +fielddefs: A nifty table that defines other tables. For instance, when you +submit a form that changes the value of "AssignedTo" this table allows +translation to the actual field name "assigned_to" for entry into MySQL. + +groups: defines bitmasks for groups. A bitmask is a number that can uniquely +identify group memberships. For instance, say the group that is allowed to +tweak parameters is assigned a value of "1", the group that is allowed to edit +users is assigned a "2", and the group that is allowed to create new groups is +assigned the bitmask of "4". By uniquely combining the group bitmasks (much +like the chmod command in UNIX,) you can identify a user is allowed to tweak +parameters and create groups, but not edit users, by giving him a bitmask of +"5", or a user allowed to edit users and create groups, but not tweak +parameters, by giving him a bitmask of "6" Simple, huh? + If this makes no sense to you, try this at the mysql prompt: +mysql> select * from groups; + You'll see the list, it makes much more sense that way. + +keyworddefs: Definitions of keywords to be used + +keywords: Unlike what you'd think, this table holds which keywords are +associated with which bug id's. + +logincookies: This stores every login cookie ever assigned to you for every +machine you've ever logged into Bugzilla from. Curiously, it never does any +housecleaning -- I see cookies in this file I've not used for months. However, +since Bugzilla never expires your cookie (for convenience' sake), it makes +sense. + +longdescs: The meat of bugzilla -- here is where all user comments are stored! +You've only got 2^24 bytes per comment (it's a mediumtext field), so speak +sparingly -- that's only the amount of space the Old Testament from the Bible +would take (uncompressed, 16 megabytes). Each comment is keyed to the +bug_id to which it's attached, so the order is necessarily chronological, for +comments are played back in the order in which they are received. + +milestones: Interesting that milestones are associated with a specific product +in this table, but Bugzilla does not yet support differing milestones by +product through the standard configuration interfaces. + +namedqueries: This is where everybody stores their "custom queries". Very +cool feature; it beats the tar out of having to bookmark each cool query you +construct. + +products: What products you have, whether new bug entries are allowed for the +product, what milestone you're working toward on that product, votes, etc. It +will be nice when the components table supports these same features, so you +could close a particular component for bug entry without having to close an +entire product... + +profiles: Ahh, so you were wondering where your precious user information was +stored? Here it is! With the passwords in plain text for all to see! (but +sshh... don't tell your users!) + +profiles_activity: Need to know who did what when to who's profile? This'll +tell you, it's a pretty complete history. + +shadowlog: I could be mistaken here, but I believe this table tells you when +your shadow database is updated and what commands were used to update it. We +don't use a shadow database at our site yet, so it's pretty empty for us. + +versions: Version information for every product + +votes: Who voted for what when + +watch: Who (according to userid) is watching who's bugs (according to their +userid). + + +=== +THE DETAILS +=== + + Ahh, so you're wondering just what to do with the information above? At the +mysql prompt, you can view any information about the columns in a table with +this command (where "table" is the name of the table you wish to view): + +mysql> show columns from table; + + You can also view all the data in a table with this command: + +mysql> select * from table; + + -- note: this is a very bad idea to do on, for instance, the "bugs" table if +you have 50,000 bugs. You'll be sitting there a while until you ctrl-c or +50,000 bugs play across your screen. + + You can limit the display from above a little with the command, where +"column" is the name of the column for which you wish to restrict information: + +mysql> select * from table where (column = "some info"); + + -- or the reverse of this + +mysql> select * from table where (column != "some info"); + + Let's take our example from the introduction, and assume you need to change +the word "verified" to "approved" in the resolution field. We know from the +above information that the resolution is likely to be stored in the "bugs" +table. Note we'll need to change a little perl code as well as this database +change, but I won't plunge into that in this document. Let's verify the +information is stored in the "bugs" table: + +mysql> show columns from bugs + + (exceedingly long output truncated here) +| bug_status| enum('UNCONFIRMED','NEW','ASSIGNED','REOPENED','RESOLVED','VERIFIED','CLOSED')||MUL | UNCONFIRMED|| + + Sorry about that long line. We see from this that the "bug status" column is +an "enum field", which is a MySQL peculiarity where a string type field can +only have certain types of entries. While I think this is very cool, it's not +standard SQL. Anyway, we need to add the possible enum field entry +'APPROVED' by altering the "bugs" table. + +mysql> ALTER table bugs CHANGE bug_status bug_status + -> enum("UNCONFIRMED", "NEW", "ASSIGNED", "REOPENED", "RESOLVED", + -> "VERIFIED", "APPROVED", "CLOSED") not null; + + (note we can take three lines or more -- whatever you put in before the +semicolon is evaluated as a single expression) + +Now if you do this: + +mysql> show columns from bugs; + + you'll see that the bug_status field has an extra "APPROVED" enum that's +available! Cool thing, too, is that this is reflected on your query page as +well -- you can query by the new status. But how's it fit into the existing +scheme of things? + Looks like you need to go back and look for instances of the word "verified" +in the perl code for Bugzilla -- wherever you find "verified", change it to +"approved" and you're in business (make sure that's a case-insensitive search). +Although you can query by the enum field, you can't give something a status +of "APPROVED" until you make the perl changes. Note that this change I +mentioned can also be done by editing checksetup.pl, which automates a lot of +this. But you need to know this stuff anyway, right? + + I hope this database tutorial has been useful for you. If you have comments +to add, questions, concerns, etc. please direct them to +mbarnson@excitehome.net. Please direct flames to /dev/null :) Have a nice +day! + + + +=== +LINKS +=== + +Great MySQL tutorial site: +http://www.devshed.com/Server_Side/MySQL/ + + +
+ +
+ MySQL Permissions & Grant Tables + + + The following portion of documentation comes from my answer to an old discussion of Keynote, a cool product that does trouble-ticket tracking for IT departments. I wrote this post to the Keynote support group regarding MySQL grant table permissions, and how to use them effectively. It is badly in need of updating, as I believe MySQL has added a field or two to the grant tables since this time, but it serves as a decent introduction and troubleshooting document for grant table issues. I used Keynote to track my troubles until I discovered Bugzilla, which gave me a whole new set of troubles to work on : ) + + + +From matt_barnson@singletrac.com Wed Jul 7 09:00:07 1999 +Date: Mon, 1 Mar 1999 21:37:04 -0700 +From: Matthew Barnson matt_barnson@singletrac.com +To: keystone-users@homeport.org +Subject: [keystone-users] Grant Tables FAQ + + [The following text is in the "iso-8859-1" character set] + [Your display is set for the "US-ASCII" character set] + [Some characters may be displayed incorrectly] + +Maybe we can include this rambling message in the Keystone FAQ? It gets +asked a lot, and the only option current listed in the FAQ is +"--skip-grant-tables". + +Really, you can't go wrong by reading section 6 of the MySQL manual, at +http://www.mysql.com/Manual/manual.html. I am sure their description is +better than mine. + +MySQL runs fine without permissions set up correctly if you run the mysql +daemon with the "--skip-grant-tables" option. Running this way denies +access to nobody. Unfortunately, unless you've got yourself firewalled it +also opens the potential for abuse if someone knows you're running it. + +Additionally, the default permissions for MySQL allow anyone at localhost +access to the database if the database name begins with "test_" or is named +"test" (i.e. "test_keystone"). You can change the name of your database in +the keystone.conf file ($sys_dbname). This is the way I am doing it for +some of my databases, and it works fine. + +The methods described below assume you're running MySQL on the same box as +your webserver, and that you don't mind if your $sys_dbuser for Keystone has +superuser access. See near the bottom of this message for a description of +what each field does. + +Method #1: + +1. cd /var/lib + #location where you'll want to run /usr/bin/mysql_install_db shell +script from to get it to work. + +2. ln -s mysql data + # soft links the "mysql" directory to "data", which is what +mysql_install_db expects. Alternately, you can edit mysql_install_db and +change all the "./data" references to "./mysql". + +3. Edit /usr/bin/mysql_install_db with your favorite text editor (vi, +emacs, jot, pico, etc.) +A) Copy the "INSERT INTO db VALUES +('%','test\_%','','Y','Y','Y','Y','Y','Y');" and paste it immediately after +itself. Chage the 'test\_%' value to 'keystone', or the value of +$sys_dbname in keystone.conf. +B) If you are running your keystone database with any user, you'll need to +copy the "INSERT INTO user VALUES +('localhost','root','','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');" line after +itself and change 'root' to the name of the keystone database user +($sys_dbuser) in keystone.conf. + + # adds entries to the script to create grant tables for specific +hosts and users. The user you set up has super-user access ($sys_dbuser) -- +you may or may not want this. The layout of mysql_install_db is really very +uncomplicated. + +4. /usr/bin/mysqladmin shutdown + # ya gotta shut it down before you can reinstall the grant tables! + +5. rm -i /var/lib/mysql/mysql/*.IS?' and answer 'Y' to the deletion +questions. + # nuke your current grant tables. This WILL NOT delete any other +databases than your grant tables. + +6. /usr/bin/mysql_install_db + # run the script you just edited to install your new grant tables. + +7. mysqladmin -u root password (new_password) + # change the root MySQL password, or else anyone on localhost can +login to MySQL as root and make changes. You can skip this step if you want +keystone to connect as root with no password. + +8. mysqladmin -u (webserver_user_name) password (new_password) + # change the password of the $sys_dbuser. Note that you will need +to change the password in the keystone.conf file as well in $sys_dbpasswd, +and if your permissions are set up incorrectly anybody can type the URL to +your keystone.conf file and get the password. Not that this will help them +much if your permissions are set to @localhost. + + + +Method #2: easier, but a pain reproducing if you have to delete your grant +tables. This is the "recommended" method for altering grant tables in +MySQL. I don't use it because I like the other way :) + +shell> mysql --user=root keystone + +mysql> GRANT +SELECT,INSERT,UPDATE,DELETE,INDEX,ALTER,CREATE,DROP,RELOAD,SHUTDOWN,PROCESS, +FILE, + ON keystone.* + TO <$sys_dbuser name>@localhost + IDENTIFIED BY '(password)' + WITH GRANT OPTION; + +OR + +mysql> GRANT ALL PRIVELEGES + ON keystone.* + TO <$sys_dbuser name>@localhost + IDENTIFIED BY '(password)' + WITH GRANT OPTION; + + # this grants the required permissions to the keystone ($sys_dbuser) +account defined in keystone.conf. However, if you are runnning many +different MySQL-based apps, as we are, it's generally better to edit the +mysql_install_db script to be able to quickly reproduce your permissions +structure again. Note that the FILE privelege and WITH GRANT OPTION may not +be in your best interest to include. + + +GRANT TABLE FIELDS EXPLANATION: +Quick syntax summary: "%" in MySQL is a wildcard. I.E., if you are +defining your DB table and in the 'host' field and enter '%', that means +that any host can access that database. Of course, that host must also have +a valid db user in order to do anything useful. 'db'=name of database. In +our case, it should be "keystone". "user" should be your "$sys_dbuser" +defined in keystone.conf. Note that you CANNOT add or change a password by +using the "INSERT INTO db (X)" command -- you must change it with the mysql +-u command as defined above. Passwords are stored encrypted in the MySQL +database, and if you try to enter it directly into the table they will not +match. + +TABLE: USER. Everything after "password" is a privelege granted (Y/N). +This table controls individual user global access rights. + +'host','user','password','select','insert','update','delete','index','alter' +,'create','drop','grant','reload','shutdown','process','file' + +TABLE: DB. This controls access of USERS to databases. + +'host','db','user','select','insert','update','delete','index','alter','crea +te','drop','grant' + +TABLE: HOST. This controls which HOSTS are allowed what global access +rights. Note that the HOST table, USER table, and DB table are very closely +connected -- if an authorized USER attempts an SQL request from an +unauthorized HOST, she's denied. If a request from an authorized HOST is +not an authorized USER, it is denied. If a globally authorized USER does +not have rights to a certain DB, she's denied. Get the picture? + +'host','db','select','insert','update','delete','index','alter','create','dr +op','grant' + + +You should now have a working knowledge of MySQL grant tables. If there is +anything I've left out of this answer that you feel is pertinent, or if my +instructions don't work for you, please let me know and I'll re-post this +letter again, corrected. I threw it together one night out of exasperation +for all the newbies who don't know squat about MySQL yet, so it is almost +guaranteed to have errors. + +Once again, you can't go wrong by reading section 6 of the MySQL manual. It +is more detailed than I! +http://www.mysql.com/Manual/manual.html. + +---------------------------------------------------------------------------- +10/12/2000 +Matthew sent in some mail with updated contact information: +NEW CONTACT INFORMATION: + + ------------------------ + Matthew P. Barnson + Manager, Systems Administration + Excite@Home Business Applications + mbarnson@excitehome.net + (801)234-8300 + + + +
+
+ Cleaning up after mucking with Bugzilla + +Contributed by Eric Hansen: +There are several things, and one trick. There is a small tiny piece of +documentation I saw once that said something very important. +1) After pretty much any manual working of the Mysql db, you must +delete a file in the bugzilla directory: data/versioncache +Versioncache basically is a way to speed up bugzilla (from what I +understand). It stores a lot of commonly used information. However, +this file is refreshed every so often (I can't remember the time +interval though). So eventually all changes do propogate out, so you +may see stuff suddenly working. +2) Assuming that failed, you will also have to check something with the +checksetup.pl file. It actually is run twice. The first time it +creates the file: localconfig. You can modify localconfig, (or not if +you are doing bug_status stuff) or you should delete localconfig and +rerun your modified checksetup.pl. Since I don't actually see anything +in localconfig pertaining to bug_status, this point is mainly a FYI. + +
+ + + + -- cgit v1.2.3-24-g4f1b