Can't Get MySQL Port Up

[Jeremy Chadwick]

On Thu, Mar 01, 2007 at 08:33:16AM -0800, Drew Jenkins wrote:
> >Have you considered doing something like pkg_info,
> 
> That command brings up many ports that are not listed in /var/db/ports/*

pkg_info will show you all ports and packages you have installed.  These
are usually installed in /usr/local.

/var/db/ports is for storing options (WITH_xxx) for each individual
port.  Sometimes when ports are updated, new OPTIONS are added, and the
logic of old ones changed.

> >making a list of all the packages you have installed, doing
> >pkg_delete -a -f && rm -fr /var/db/ports/*
> 
> Will that delete *only* the ports listed in /var/db/ports/*, or will that delete *all* the ports listed from pkg_info?

pkg_delete -a -f will delete all installed ports on your system.  That
means, /usr/local will essentially be empty (minus any tweaks to
configuration files you changed there, yadda yadda).  pkg_delete -a -f
should be used with caution.

The reason I recommended it, is that you nuked /usr/local/include, which
I'm sure some of your ports installed data in (MySQL being one of them,
e.g. /usr/local/include/mysql).  Depending upon how you "restored
/usr/local", you may have gotten permissions wrong.

Removing /var/db/ports/* will just remove all the options you had set
for ports during build/compile.

> >then reinstalling all of the ports in question?
> 
> To be absolutely clear, you mean the ports deleted from /var/db/ports/* only, correct? Easy enough to do.

See above.  pkg_delete actually "removes ports", while rm'ing content in
/var/db/ports/* just removes options you've picked when building a port.

Based on all of your questions, you should read the Ports section of the
FreeBSD Handbook.  It's apparent (and that's OK!) that you're not
familiar with FreeBSD Ports, and this may be your first time dealing
with them.  :-)

> >Are you _building_ MySQL or are you using the package (pre-compiled
> >binary package)?  What version of MySQL are you choosing to use?
> 
> I am using the port: 5.1 server.

Which is beta software.  Be aware of this fact.  :-)  www.mysql.com
discusses the differences between alpha, beta, and stable releases.

> >What exactly is in your my.cnf?  This looks suspicious.
> 
> Um...dunno. I just ran a find for "*.cnf" and up popped three files (small, med, large) like this:
> /usr/local/share/mysql/my-small.cnf
> I decided to copy this over to /etc/my.cnf. I left the defaults. I didn't edit it. I ran mysqld_safe again, but got the same errors.

The port installs /usr/local/share/mysql and all files as root:wheel.
my.cnf needs to be readable by the MySQL server, which runs as user
mysql group mysql.

So chown mysql:mysql /etc/my.cnf && chmod 600 /etc/my.cnf.

> >There is no such table "general_log" in database "mysql" in MySQL
> >4.0 nor 4.1.  errno 13 is Permission denied, by the way.  So how is
> >this table getting loaded?
> 
> No idea. Didn't use any beta.

You're using 5.1, which is beta.  That's fine, just be aware of the fact
that things will break and odd behaviour may occur.

> > 070301  6:58:39 [ERROR] /usr/local/libexec/mysqld: Can't create/write to file '/var/db/mysql/server312.web.vi.pid' (Errcode:
> > 13)
> >
> > This also looks indicative of a permissions problem with your
> > /var/db/mysql directory.  The directory should be identical in
> > ownership and permissions to this:
> >
> > drwx------    7 mysql     mysql     512 26 Feb 09:46 /var/db/mysql/
> 
> Strange. I know I changed that once, but the ownership was wrong. Fixed that and the folder contents as well. However, the permissions read:
> 
> drwxr-xr-x
> 
> I don't imagine that matters for these purposes, but it could invite a hack. How do I change that? I'm used to +x or whatever, but here I need numbers, I guess.

The fact the ownership was wrong is what was causing your pidfile not to
be created, which was ultimately causing your server not to start.

As for directory permissions, chmod 700 /var/db/mysql will do the trick.
Perhaps man chmod is in order?  :-)

> Also, doing that and then running mysqld_safe gave the same error in the error log plus this:
> 
> ^G/usr/local/libexec/mysqld: Can't find file: './mysql/general_log.frm' (errno: 13)
> 070301 10:25:13 [ERROR] /usr/local/libexec/mysqld: Can't find file: './mysql/host.frm' (errno: 13)
> 070301 10:25:13 [ERROR] Fatal error: Can't open and lock privilege tables: Can't find file: './mysql/host.frm' (errno: 13)
> 070301 10:25:13  mysqld ended
> 
> So, it didn't look for a pid file (nor did it create one), and now it is looking for a host file.

Ahhh, now I can see what's happened here...

You're missing a ton of necessary tables in /var/db/mysql/mysql, which
are all required for MySQL to run.  Authentication tables, host access
tables, etc..

The MySQL startup script /usr/local/etc/rc.d/mysql-server is supposed to
create all of these tables for you if the /var/db/mysql/mysql directory
does not exist (otherwise if it does, it assumes all the necessary
tables exist).  The script that creates all the necessary tables is
/usr/local/bin/mysql_install_db and it comes from the MySQL guys.  The
script is also supposed to fix permissions on /var/db/mysql and friends
-- so the fact that your mysql directory was not fixed up means this
script didn't run at all, or bailed out for some reason.

Before you installed the port, did you have an existing /var/db/mysql
directory?  If so, the directory you had probably did not contain all of
the necessary tables.

If not, then I'm stumped.  You'd need to go back to when you did
"/usr/local/etc/rc.d/mysql-server start" and see what happened.  The
mysql_install_db script is quite verbose when it doesn't install things
right.

-- 
| Jeremy Chadwick                                 jdc at parodius.com |
| Parodius Networking                        http://www.parodius.com/ |
| UNIX Systems Administrator                   Mountain View, CA, USA |
| Making life hard for others since 1977.               PGP: 4BD6C0CB |