path: root/docs/sgml/database.sgml

<!-- <!DOCTYPE appendix PUBLIC "-//OASIS//DTD DocBook V4.1//EN"> -->
<appendix id="database">
  <title>The Bugzilla Database</title>

  <note>
    <para>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?</para>
  </note>

  <section id="dbschema">
    <title>Database Schema Chart</title>

    <para>
      <mediaobject>
        <imageobject>
          <imagedata fileref="../images/dbschema.jpg" format="JPG" />
        </imageobject>

        <textobject>
          <phrase>Database Relationships</phrase>
        </textobject>

        <caption>
          <para>Bugzilla database relationships chart</para>
        </caption>
      </mediaobject>
    </para>
  </section>

  <section id="dbdoc">
    <title>MySQL Bugzilla Database Introduction</title>

    <para>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.</para>

    <para>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.</para>

    <para>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.</para>

    <para>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!</para>

    <para>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'.</para>

    <para>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."</para>

    <para>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...</para>

    <para>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!</para>

    <section>
      <title>Bugzilla Database Basics</title>

      <para>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 
      <quote>bigint</quote>

      and a 
      <quote>tinyint</quote>

      entry in MySQL. I recommend you refer to the MySQL documentation,
      available at 
      <ulink url="http://www.mysql.com/doc.html">MySQL.com</ulink>

      . Below are the basics you need to know about the Bugzilla database.
      Check the chart above for more details.</para>

      <para>
        <orderedlist>
          <listitem>
            <para>To connect to your database:</para>

            <para>
              <prompt>bash#</prompt>

              <command>mysql</command>

              <parameter>-u root</parameter>
            </para>

            <para>If this works without asking you for a password, 
            <emphasis>shame on you</emphasis>

            ! You should have locked your security down like the installation
            instructions 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
            .</para>
          </listitem>

          <listitem>
            <para>You should now be at a prompt that looks like this:</para>

            <para>
              <prompt>mysql&gt;</prompt>
            </para>

            <para>At the prompt, if 
            <quote>bugs</quote>

            is the name you chose in the
            <filename>localconfig</filename>

            file for your Bugzilla database, type:</para>

            <para>
              <prompt>mysql</prompt>

              <command>use bugs;</command>
            </para>

            <note>
              <para>Don't forget the 
              <quote>;</quote>

              at the end of each line, or you'll be kicking yourself
              later.</para>
            </note>
          </listitem>
        </orderedlist>
      </para>

      <section>
        <title>Bugzilla Database Tables</title>

        <para>Imagine your MySQL database as a series of spreadsheets, and
        you won't be too far off. If you use this command:</para>

        <para>
          <prompt>mysql&gt;</prompt>

          <command>show tables from bugs;</command>
        </para>

        <para>you'll be able to see all the 
        <quote>spreadsheets</quote>

        (tables) in your database. It is similar to a file system, only
        faster and more robust for certain types of operations.</para>

        <para>From the command issued above, ou should have some output that
        looks like this: 
        <programlisting>+-------------------+ | Tables in bugs |
        +-------------------+ | attachments | | bugs | | bugs_activity | | cc
        | | components | | dependencies | | fielddefs | | groups | |
        keyworddefs | | keywords | | logincookies | | longdescs | |
        milestones | | namedqueries | | products | | profiles | |
        profiles_activity | | shadowlog | | tokens | | versions | | votes | |
        watch | +-------------------+</programlisting>
        </para>

        <literallayout>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&gt; 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&gt; show columns from table; You can also view
        all the data in a table with this command: mysql&gt; 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&gt; select * from table where (column = "some
        info"); -- or the reverse of this mysql&gt; 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&gt; 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&gt; ALTER table bugs CHANGE bug_status bug_status
        -&gt; enum("UNCONFIRMED", "NEW", "ASSIGNED", "REOPENED", "RESOLVED",
        -&gt; "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&gt; 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/</literallayout>
      </section>
    </section>
  </section>

  <section id="granttables">
    <title>MySQL Permissions &amp; Grant Tables</title>

    <note>
      <para>The following portion of documentation comes from my answer to an
      old discussion of Keystone, a cool product that does trouble-ticket
      tracking for IT departments. I wrote this post to the Keystone 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 : )
      Although it is of limited use, it still has SOME use, thus it's still
      included.</para>

      <para>Please note, however, that I was a relatively new user to MySQL
      at the time. Some of my suggestions, particularly in how to set up
      security, showed a terrible lack of security-related database
      experience.</para>
    </note>

    <literallayout>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&gt; mysql --user=root keystone
    mysql&gt; GRANT
    SELECT,INSERT,UPDATE,DELETE,INDEX,ALTER,CREATE,DROP,RELOAD,SHUTDOWN,PROCESS,
    FILE, ON keystone.* TO &lt;$sys_dbuser name&gt;@localhost IDENTIFIED BY
    '(password)' WITH GRANT OPTION; OR mysql&gt; GRANT ALL PRIVILEGES ON
    keystone.* TO &lt;$sys_dbuser name&gt;@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.</literallayout>
  </section>
</appendix>

<!-- Keep this comment at the end of the file
Local variables:
mode: sgml
sgml-always-quote-attributes:t
sgml-auto-insert-required-elements:t
sgml-balanced-tag-edit:t
sgml-exposed-tags:nil
sgml-general-insert-case:lower
sgml-indent-data:t
sgml-indent-step:2
sgml-local-catalogs:nil
sgml-local-ecat-files:nil
sgml-minimize-attributes:nil
sgml-namecase-general:t
sgml-omittag:t
sgml-parent-document:("Bugzilla-Guide.sgml" "book" "chapter")
sgml-shorttag:t
sgml-tag-region-if-active:t
End:
-->