From owner-freebsd-questions@FreeBSD.ORG Tue Feb 28 09:50:35 2006 Return-Path: X-Original-To: freebsd-questions@freebsd.org Delivered-To: freebsd-questions@freebsd.org Received: from mx1.FreeBSD.org (mx1.freebsd.org [216.136.204.125]) by hub.freebsd.org (Postfix) with ESMTP id 0221C16A420 for ; Tue, 28 Feb 2006 09:50:35 +0000 (GMT) (envelope-from james_mapson@umpquanet.com) Received: from ns.museum.rain.com (gw-ipinc.museum.rain.com [65.75.192.74]) by mx1.FreeBSD.org (Postfix) with ESMTP id 6BA0C43D45 for ; Tue, 28 Feb 2006 09:50:34 +0000 (GMT) (envelope-from james_mapson@umpquanet.com) Received: from ns.museum.rain.com (localhost [127.0.0.1]) by ns.museum.rain.com (8.13.4/8.13.4) with ESMTP id k1S9oEDT019194 (version=TLSv1/SSLv3 cipher=DHE-DSS-AES256-SHA bits=256 verify=NO); Tue, 28 Feb 2006 01:50:14 -0800 (PST) (envelope-from james@umpquanet.com) Received: (from james@localhost) by ns.museum.rain.com (8.13.4/8.13.4/Submit) id k1S9oEhA019193; Tue, 28 Feb 2006 01:50:14 -0800 (PST) (envelope-from james) Date: Tue, 28 Feb 2006 01:50:14 -0800 From: James Long To: freebsd-questions@freebsd.org Message-ID: <20060228095014.GB19046@ns.museum.rain.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline User-Agent: Mutt/1.5.11 X-Spam-Status: No, score=-100.2 required=5.0 tests=ALL_TRUSTED, J_CHICKENPOX_13,J_CHICKENPOX_22,USER_IN_WHITELIST autolearn=failed version=3.1.0 X-Spam-Checker-Version: SpamAssassin 3.1.0 (2005-09-13) on ns.museum.rain.com 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) X-BeenThere: freebsd-questions@freebsd.org X-Mailman-Version: 2.1.5 Precedence: list List-Id: User questions List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Tue, 28 Feb 2006 09:50:35 -0000 ----- Forwarded message from James Long ----- Date: Tue, 28 Feb 2006 01:48:09 -0800 From: James Long To: Bj?rn K?nig Cc: Jeremy Cole 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 -----