MySQL: show status problem.

[Ian Smith]

On Sun, 10 Sep 2006, Matt Hartzell wrote:

 > Is there a my.cnf in /usr/local/mysql ? How about a .my.cnf in your home 
 > directory?
 > 
 > Have you looked into the order that mysql processes the different config 
 > files locations?

Good questions.  Just to add a bit ..

 > e.stewart at mac.com wrote:
 > > I've recently installed MySQL 5.0.24 on my FreeBSD by building mysql 
 > > from the ports directory.
 > >
 > > It installed fine and I initialized mysql and secured up the root 
 > > passwords.
 > >
 > > Because mysql is using /var/db/mysql as it's default data directory 
 > > (and I wanted to use /usr/local/mysql instead), I moved the 
 > > /var/db/mysql directory to /usr/local/mysql and then added the 
 > > mysql_dbdir="/usr/local/mysql/" to the rc.conf file under 
 > > mysql_enable="YES".

That should be fine, but I also tend to add a symlink from /var/db/mysql
to /usr/local/mysql just to be sure if 'something' assumes the default
dir.  Also I assume that mysql wasn't running when you moved its data? 

 > > Everything seem was working fine. I can log in and use mysql.
 > >
 > > I then copied one of the example mysql configuration files to 
 > > /usr/local/etc/my.cnf and then restarted mysql. MySQL starts up just 
 > > fine but I can't run the "SHOW STATUS" command. It just sits there and 
 > > acts like its doing something but never does.
 > >
 > > I know the problem is related to my my.cnf file because when I run 
 > > mysql without that configuration file, show status works just fine.
 > >
 > > Below is the contents of my configuration file, any idea what might be 
 > > screwing this up?

I know very little, but did notice that you say InnoDB only ..

 > > #BEGIN CONFIG INFO
 > > #DESCR: 4GB RAM, InnoDB only, ACID, few connections, heavy queries
 > > #TYPE: SYSTEM
 > > #END CONFIG INFO
 > >
 > > #
 > > # This is a MySQL example config file for systems with 4GB of memory
 > > # running mostly MySQL using InnoDB only tables and performing complex
 > > # queries with few connections.
 > > #
 > > # You can copy this file to /etc/my.cnf to set global options,
 > > # mysql-data-dir/my.cnf to set server-specific options
 > > # (/var/db/mysql for this installation) or to
 > > # ~/.my.cnf to set user-specific options.

.. but then have:

 > > # Table type which is used by default when creating new tables, if not
 > > # specified differently during the CREATE TABLE statement.
 > > default_table_type = MYISAM

?

Cheers, Ian