Skip site navigation (1)Skip section navigation (2)
Date:      Tue, 28 Feb 2006 01:50:14 -0800
From:      James Long <list@museum.rain.com>
To:        freebsd-questions@freebsd.org
Cc:        jcole@yahoo-inc.com, bkoenig@cs.tu-berlin.de
Subject:   Re: Problem with mysql-server.sh script (5.0.18 on 6.1-PRE)
Message-ID:  <20060228095014.GB19046@ns.museum.rain.com>

next in thread | raw e-mail | index | archive | help
----- Forwarded message from James Long <list@ns.museum.rain.com> -----

Date: Tue, 28 Feb 2006 01:48:09 -0800
From: James Long <list@ns.museum.rain.com>
To: Bj?rn K?nig <bkoenig@cs.tu-berlin.de>
Cc: Jeremy Cole <jcole@yahoo-inc.com>
Subject: Re: Problem with mysql-server.sh script (5.0.18 on 6.1-PRE)
In-Reply-To: <4404064C.5010402@cs.tu-berlin.de>
User-Agent: Mutt/1.5.11

> James Long schrieb:
> >>Hello,
> >>
> >>I suppose the owner of /var/db/mysql is not mysql. Check this and show 
> >>ls -ld /var/db/mysql.
> > 
> > 
> > Thank you for your reply.
> > 
> > www : 23:54:45 /root# ls -ld /var/db/mysql
> > drwx------  4 mysql  mysql  512 Feb 27 21:52 /var/db/mysql/
> > 
> 
> Ok, I would try to delete /var/db/mysql completely and then start mysql 
> with the start script again.

Hmmm, mixed success.

www : 00:59:15 /root# ls -ld /var/db/mysql
ls: /var/db/mysql: No such file or directory
www : 00:59:27 /root# /usr/local/etc/rc.d/mysql-server.sh start
/usr/local/etc/rc.d/mysql-server.sh: DEBUG: Sourcing /etc/defaults/rc.conf
/usr/local/etc/rc.d/mysql-server.sh: DEBUG: checkyesno: mysql_enable is set to YES.
/usr/local/etc/rc.d/mysql-server.sh: DEBUG: pid file (/var/db/mysql/www.example.com.pid): not readable.
/usr/local/etc/rc.d/mysql-server.sh: DEBUG: run_rc_command: evaluating mysql_prestart().
/usr/local/etc/rc.d/mysql-server.sh: DEBUG: checkyesno: mysql_limits is set to NO.
Starting mysql.
/usr/local/etc/rc.d/mysql-server.sh: DEBUG: run_rc_command: _doit: su -m mysql -c 'sh -c "/usr/local/bin/mysqld_safe  --defaults-extra-file=/var/db/mysql/my.cnf --user=mysql --datadir=/var/db/mysql --pid-file=/var/db/mysql/www.example.com.pid --bind-address=localhost --log-error=/tmp/err.log > /dev/null &"'
www : 00:59:35 /root# ps -axl | grep sql
   88 18841     1   0   8  0  1708  1160 wait   S     p0    0:00.01 /bin/sh /usr/local/bin/mysqld_safe --defaults-extra
   88 18866 18841   0  20  0 56220 26480 kserel S     p0    0:00.17 /usr/local/libexec/mysqld --defaults-extra-file=/va
www : 00:59:39 /root# cat /tmp/err.log
060228 00:59:35  mysqld started
InnoDB: The first specified data file ./ibdata1 did not exist:
InnoDB: a new database to be created!
060228  0:59:35  InnoDB: Setting file ./ibdata1 size to 10 MB
InnoDB: Database physically writes the file full: wait...
060228  0:59:35  InnoDB: Log file ./ib_logfile0 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile0 size to 5 MB
InnoDB: Database physically writes the file full: wait...
060228  0:59:35  InnoDB: Log file ./ib_logfile1 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile1 size to 5 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Doublewrite buffer not found: creating new
InnoDB: Doublewrite buffer created
InnoDB: Creating foreign key constraint system tables
InnoDB: Foreign key constraint system tables created
060228  0:59:35  InnoDB: Started; log sequence number 0 0
060228  0:59:35 [Note] /usr/local/libexec/mysqld: ready for connections.
Version: '5.0.18'  socket: '/tmp/mysql.sock'  port: 3306  FreeBSD port: mysql-server-5.0.18

(blink)

Will mysqld use /etc/hosts to resolve a hostname to bind to?  If I use 
something like 'localhost', it's actually resolving that through DNS 
by appending my default domain suffix.  I tested this by creating a 
hosts entry "asdf" and verifying that "host asdf" did not resolve.  So 
asdf is in /etc/hosts, pingable on my local interface, but not 
resolvable via DNS.  When I do that, /tmp/err.log says:

060228 01:10:37  mysqld started
060228  1:10:37 [ERROR] Can't start server: cannot resolve hostname!: Unknown error: 0
060228 01:10:37  mysqld ended

This behaviour appears to differ with that observed by Jeremy Cole at 
Yahoo, with whom I have been corresponding on the MySQL list.  I 
originally inquired about MySQL's seeming inability to bind to an IP 
specified by hostname, rather than a literal IP number.  I'm coming 
from Postgres, which allows binding not only to an IP or a hostname, 
but an arbitrary list of IP numbers and/or hostnames (and/or local 
UNIX sockets, I believe).  MySQL appears to allow only one, although I 
certainly welcome corrections.  Maybe one just specifies multiple 
instances of the --bind-address= option, I haven't tried that.  But 
Jeremy's observation is below, and appears to indicate that he can get 
MySQL to bind to a hostname defined in /etc/hosts, although he's doing 
it a little differently.  I prefer to accomplish it through the 
standard mechanisms in /etc/rc.conf that allow me to furnish start-up 
options to /usr/local/etc/rc.d/mysql-server.sh.

My goal is this: I want to specify a hostname in /etc/hosts and define 
an IP number there.  That way, when my IP changes, I can edit 
/etc/hosts ONCE and not have to dig through lots of other places 
looking for IP numbers to change, and increasing the chances for error.  
My observations so far suggest that this is not possible, and that 
mysqld can only bind to a DNS name, not an /etc/hosts entry.

Is there any conclusive word out there about whether MySQL can bind to 
a hostname resolved SOLELY from /etc/hosts?  It would be nice if mysql 
would resolve the name in the same fashion as ping(8) so that if one 
can ping a name, then mysql would bind to the same IP (assuming the 
name resolves to a single IP).

Thanks!

Jim

One more time, here's trying to bind to "asdf" which is resolvable
by ping(8), pingable on a local interface, but not resolvable by 
host(1).

www : 01:33:00 /root# findps sql
www : 01:33:01 /root# rm /tmp/err.log
www : 01:33:06 /root# /usr/local/etc/rc.d/mysql-server.sh start
/usr/local/etc/rc.d/mysql-server.sh: DEBUG: Sourcing /etc/defaults/rc.conf
/usr/local/etc/rc.d/mysql-server.sh: DEBUG: checkyesno: mysql_enable is set to YES.
/usr/local/etc/rc.d/mysql-server.sh: DEBUG: pid file (/var/db/mysql/www.example.com.pid): not readable.
/usr/local/etc/rc.d/mysql-server.sh: DEBUG: run_rc_command: evaluating mysql_prestart().
/usr/local/etc/rc.d/mysql-server.sh: DEBUG: checkyesno: mysql_limits is set to NO.
Starting mysql.
/usr/local/etc/rc.d/mysql-server.sh: DEBUG: run_rc_command: _doit: su -m mysql -c 'sh -c "/usr/local/bin/mysqld_safe  --defaults-extra-file=/var/db/mysql/my.cnf --user=mysql --datadir=/var/db/mysql --pid-file=/var/db/mysql/www.example.com.pid --bind-address=asdf --log-error=/tmp/err.log > /dev/null &"'
www : 01:33:20 /root# cat /tmp/err.log
060228 01:33:20  mysqld started
060228  1:33:20 [ERROR] Can't start server: cannot resolve hostname!: Unknown error: 0
060228 01:33:20  mysqld ended

rm'ing the /var/db/mysql doesn't seem to change the error message:

www : 01:33:25 /root# rm -rf /var/db/mysql
www : 01:33:45 /root# rm /tmp/err.log
www : 01:33:54 /root# /usr/local/etc/rc.d/mysql-server.sh start
/usr/local/etc/rc.d/mysql-server.sh: DEBUG: Sourcing /etc/defaults/rc.conf
/usr/local/etc/rc.d/mysql-server.sh: DEBUG: checkyesno: mysql_enable is set to YES.
/usr/local/etc/rc.d/mysql-server.sh: DEBUG: pid file (/var/db/mysql/www.example.com.pid): not readable.
/usr/local/etc/rc.d/mysql-server.sh: DEBUG: run_rc_command: evaluating mysql_prestart().
/usr/local/etc/rc.d/mysql-server.sh: DEBUG: checkyesno: mysql_limits is set to NO.
Starting mysql.
/usr/local/etc/rc.d/mysql-server.sh: DEBUG: run_rc_command: _doit: su -m mysql -c 'sh -c "/usr/local/bin/mysqld_safe  --defaults-extra-file=/var/db/mysql/my.cnf --user=mysql --datadir=/var/db/mysql --pid-file=/var/db/mysql/www.example.com.pid --bind-address=asdf --log-error=/tmp/err.log > /dev/null &"'
www : 01:35:19 /root# cat /tmp/err.log
060228 01:35:19  mysqld started
060228  1:35:19 [ERROR] Can't start server: cannot resolve hostname!: Unknown error: 0
060228 01:35:19  mysqld ended



Correspondence from Jeremy at Yahoo:

"Well, that was easy.  I checked the code, and it turns out that the
functionality is already there.  I just tested it on my laptop (running
5.0.13) and it does indeed work.

I did:

* echo "10.0.0.1 foo" >> /etc/hosts
* ifconfig lo0 alias 10.0.0.1
* add to /etc/my.cnf:
   [mysqld]
   bind-address=foo
* restart mysqld
* mysql -h 10.0.0.1  <-- confirm connection or error from mysqld
* mysql -h 127.0.0.1 <-- confirm failure to connect at all

I didn't know that this worked.  Learn something new every day.

Are you having a problem with it?  Does it not work for you?

Regards,

Jeremy

--
Jeremy Cole
MySQL Geek, Yahoo! Inc.
Desk: 408 349 5104"


----- End forwarded message -----



Want to link to this message? Use this URL: <https://mail-archive.FreeBSD.org/cgi/mid.cgi?20060228095014.GB19046>