MySQL Notes

Other Links


I had already installed all the MySQL packages, most importantly, mysql-server, on linux2, so all I had to do was
   service mysqld start
and the /etc/rc.d/init.d/mysqld script ran mysql_install_db, which created two default databases at /var/lib/mysql/mysql, an empty "test" database and a "mysql" database with 15 tables.

It looks like I avoided a pitfall by letting Red Hat install MySQL instead of downloading the latest and installing the RPM myself. They say there's a problem if SELinux is active. You can

   getenforce
to see.

The correct installation steps for MySQL 5.0.24 on RHEL v4 with RPM:


The initial MySQL configuration/creation had this to "say" in /var/log/messages

   PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
   To do so, start the server, then issue the following commands:
      mysqladmin           password 'new-password'
      mysqladmin -h linux2 password 'new-password'
So I did, setting root's SQL password to sqlpw.

To do mysqladmin or mysql things, or get into the mysql environment,

   mysqladmin -psqlpw ping
   mysqladmin -psqlpw status
   mysqladmin -psqlpw shutdown
   mysql -psqlpw -e "show databases;"
   mysql -psqlpw -e "connect mysql;select host,user,password from user;"
   mysql -uroot -psqlpw
And from the mysql environment, you can poke around a bit with
   show databases;         (There's two, test & mysql)
   connect mysql;          (Not connect to ... which hangs the client!!!)
   show tables;            (There's 15, including db & user)
   describe <table-name>;
   select count(*) from <table-name>;
   select * from <table-name>;
   select host,user,password from user;
      +-------------------------+------+------------------+
      | host                    | user | password         |
      +-------------------------+------+------------------+
      | localhost               | root | 67e3204d66de1245 |
      | linux2.jasperfamily.org | root |                  |
      | localhost               |      |                  |
      | linux2.jasperfamily.org |      |                  |
      +-------------------------+------+------------------+
Why are there are two root accounts? One is for connecting from the local host and the other allows connections from any host. If you're logged onto the MySQL server machine (linux2 in my case), you can use either account. The two accounts with blank user names are anonymous accounts.

Note that the above situation where just localhost:root had a password was after I had changed just one account's password with a

   mysqladm password mysql
command. At that point, I needed a password to use localhost, eg
   mysql -psqlpw
but no password was needed for
   mysql -hlinux2
I needed to specify passwords for both root accounts, so I also did
   mysqladmin -hlinux2 password sqlpw
and just to exercise a different way of doing things, I updated the two anonymous user's passwords with this mysql command
   update user set password=password('anything') where user='';
I wanted to see if this warning was true and it was! When inside the interactive mysql environment, all your lines are saved in ~/.mysql_history, including the above update password line with the new password in the clear. The ~/.mysql_history file DOES have 0600 permissions, though, but a non-root user is still not protected from root.
There's a procedure by the way to reset root's SQL password should you lose it.
  1. Kill or shutdown mysqld if it's running.
  2. echo "SET PASSWORD FOR 'root'@'localhost' = PASSWORD('MyNewPassword');">junk
  3. mysqld_safe --init-file=junk

This tidbit was in the MySQL Reference Manual, but it didn't seem to apply to my RHEL v4 system. I summarize it here 'cause Tivo asked me about this, although they also asked me about number of semaphores.

If MySQL cannot open enough files or connections, it may be that you have not configured Linux to handle enough files. In Linux 2.2 and onward, you can check the number of allocated file handles as follows:

   cat /proc/sys/fs/file-max   (Already set to 102450 on my RHEL v4 system)
   cat /proc/sys/fs/dquot-max  (Not on my RHEL v4 system)
   cat /proc/sys/fs/super-max  (Not on my RHEL v4 system)

To tweak,

   echo new-number > /proc/sys/fs/file-max
or sysctl -w fs.file-max=new-number
or Add "fs.file-max = new-number" to /etc/sysctl.conf

See my relatives database notes to see what I did for that project.

Table Creation

There are about 10 different "Storage Engines" or table types in MySQL, the most common one being MyISAM. In the table below, TST and NTST stands for "transaction-safe table" or "non-transaction-safe table. A TST means if you're in the middle of a complicated transaction and the server dies, you will be able to roll back to the original state.

Table types include

MyISAM
NTST. Most common. Replaces ISAM.
InnoDB
TST.
Merge
NTST. Allows identical MyISAM tables to be handled as one table.
Memory (aka Heap)
NTST. In memory table.
BDB (Berkely DB)
TST.
Example
NoOp. Can CREATE only.
Archive
CSV
Comma-Separated Values.
Blackhole
Similar to Example, but can also write into and read from, but never really stores anything and always returns nulls.
NDBCluster
Clustered, fault-tolerant, memory-based Tables.
ISAM
NTST. Deprecated and removed in later MySQL's.

Older MySQL Notes

There's a mysql database on supa1 that's used by the contract cgi-bin scripts.

To connect,
  /usr/local/mysql/bin/mysql -uroot -pglobal sbync

which puts you within the mysql environment.  Commands end with a semicolon, so e.g.
  mysql> show tables;


Bugzilla uses mysql as its database engine. Here are some mysql tidbits. To create a database dump. mysqldump bugs > bugs.dump To create a database. mysql mysql> create database tmp; To import the bug database. mysql tmp < bugs.dump
To look at some of the server's variables, as root, /usr/local/mysql/bin/mysqladmin variables +----------------------------+------------------------------------------------------+ | Variable_name | Value | +----------------------------+------------------------------------------------------+ | back_log | 5 | | connect_timeout | 5 | | basedir | /usr/local/mysql-3.22.32-ibm-aix4.3.2.0-powerpc/ | | datadir | /usr/local/mysql/data/ | ... +------------+----------------------------------------------------------------------+ For example, you can see which databases are defined on this server by looking at the directories under the datadir, /usr/local/mysql/data in this case. On indirect, you'll see bugs, mysql and test (which is empty).
To perform individual mysql commands against the Bugzilla database, as root on indirect, /usr/local/mysql/bin/mysql -e "select * from profiles order by groupset;" bugs Or to connect and stay within the mysql environment, /usr/local/mysql/bin/mysql bugs then you can do things like mysql> show tables; mysql> describe profiles; and you can use the arrow-up and arrow-down keys to move within the command buffer, left- and right-arrow to move the cursor, backspace to delete, etc.

Bugzilla Database Schema Tidbits

There are 21 tables in the bugs database, mysql> show tables; +-------------------+ | 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 | +-------------------+ Bugs are kept in the bugs table, which has bug_id integer groupset integer Corresponds to bit field of groups table. assigned_to integer Corresponds to userid field of profiles table. bug_file_loc text Sander uses this field for the "URL:" bug_severity enum('critical','major','normal','minor','enhancement') bug_status enum('UNCONFIRMED','NEW','ASSIGNED','REOPENED','RESOLVED','VERIFIED','CLOSED') creation_ts datetime delta_ts timestamp short_desc text Displays as a bug's "Summary:" op_sys enum('All','Windows','MacOS','AIX','Linux','OS/2','other') priority enum('P1','P2','P3') product varchar(64) Actual text of product field from products table, not an index. rep_platform enum('All','RS/6000','Macintosh','PC','Other') reporter integer Corresponds to userid field of profiles table. version varchar(16) component varchar(50) resolution enum('','FIXED','INVALID','WONTFIX','LATER','REMIND','DUPLICATE','WORKSFORME') keywords text Bug Description is kept in the longdescs table, which has bug_id integer who integer bug_when datatime thetext mediumtext Userid information is in the profiles table, which has mysql> describe profiles; +-------------------+-------------------------------------------+------+-----+--------------------+ | Field | Type | Null | Key | Default | +-------------------+-------------------------------------------+------+-----+--------------------+ | userid | mediumint(9) | | PRI | 0 | | login_name | varchar(255) | | UNI | | | password | varchar(16) | YES | | NULL | | cryptpassword | varchar(64) | YES | | NULL | | realname | varchar(255) | YES | | NULL | | groupset | bigint(20) | | | 0 | | emailnotification | enum('ExcludeSelfChanges','CConly','All') | | | ExcludeSelfChanges | | disabledtext | mediumtext | | | NULL | | newemailtech | tinyint(4) | | | 0 | | mybugslink | tinyint(4) | | | 1 | | blessgroupset | bigint(20) +-------------------+-------------------------------------------+------+-----+--------------------+ On 5-23-2003, I changed the login_name values for all the old, disabled userids to something so Bugzilla would stop sending them e-mail. mysql> select userid,login_name,realname from profiles where disabledtext<>''; mysql> update profiles set login_name='Ex-Servaas' where userid=9;

Bugzilla Table Cross-Reference

:bug_id => bugs:bug_id :submitter_id => profiles:userid bugs:bug_id => attachments:bug_id, bugs_activity:bug_id, cc:bug_id, dependencies:blocked, dependencies:dependson, keywords:bug_id :groupset = Sum of groups:bit fields (Definition B) for the groups to which this bug is in. Normally 128, 256, 512, etc, but occasionally 0 (not in a group) or 640 for example, in groups 128 and 512. :assigned_to => profiles:userid :reporter => profiles:userid :product(string) => products:product :version(string) => version:value :component(string) => components:value bugs_activity:bug_id => bugs:bug_id :who => profiles:userid :fieldid = fielddefs:fieldid cc:bug_id => bugs:bug_id :who => profiles:userid components:value(string) => bugs:component :program(string) => products:product :initialowner => profiles:userid :initialcontact => profiles:userid dependencies:blocked => bugs:bug_id :dependson => bugs:bug_id fielddefs:fieldid => bugs_activity:fieldid, profiles_activity:fieldid :name => Either bugs table column names, or other changeable variables (eg cc) groups:bit Definition A => profiles:groupset, profiles:blessgroupset Possible values are 1, 2, 4, 8, 16, 32, and 64. Definition B => bugs:groupset, profiles:blessgroupset Possible values start at 128 and are a power of 2. As of 5-23-2003, we have 128, 256, and 512 used. keyworddefs:id => keywords:keywordid keywords:bug_id => bugs:bug_id :keywordid => keyworddefs:id logincookies:userid => profiles:userid :cryptpassword => profiles:cryptpassword longdescs:bug_id => bugs:bug_id :who => profiles:userid milestones:product(string) => products:product namedqueries:userid => profiles:userid products:product => components:program profiles:userid => many .................................. :cryptpassword => logincookies:cryptpassword :groupset => Sum of groups:bit fields (Definition A and B) for the groups to which this userid is in. For normal users, this is 736 which is 512 = IPSfDB2 PATOLIS = IPSfDB2 PATOLIS Bugs Access 128 = IPSfDB2 = IPSfDB2 Bugs Access 64 = canconfirm = Can confirm a bug. 32 = editbugs = Can edit all aspects of any bug. For Sander, Eric and I, this is 1023, i.e. all bits turned on. On w3/reindeer, I changed this from 96 to 127 to give me admin privs. /usr/local/mysql/bin/mysql bugs update profiles set groupset=127 where login_name='rick@delphion.com'; :blessgroupset => Sum of groups:bit fields (Definition A and B) for the groups that this userid "Can turn on for other users". We don't use this. profiles_activity:userid => profiles:userid :who => profiles:userid :fieldid = fielddefs:fieldid shadowlog: (Empty table - can't tell much from it's description) versions:value(string) => bugs:version :program(string) => products:product votes:who => profiles:userid :bug_id => bugs:bug_id watch:watcher => profiles:userid :watched => bugs:bug_id

General Info:

mysql> show databases; +----------+ | Database | +----------+ | bugs | | mysql | | test | +----------+ mysql> show tables from mysql; +-----------------+ | Tables in mysql | +-----------------+ | columns_priv | Empty. | db | 2 rows, Host/Db =%/test, %/test\_% | func | Empty. | host | 2 rows, Host/Db = localhost/%, ostrich/% | tables_priv | Empty. | user | 4 rows, User@Host = root@localhost, @localhost, root@ostrich, @ostrich +-----------------+

This page was last updated on