MySQL Bugs: #39264: Incorrect bind-address gives no error message

Bug #39264	Incorrect bind-address gives no error message
Submitted:	5 Sep 2008 2:38	Modified:	5 Oct 2008 9:12
Reporter:	Andy Canfield	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: Errors	Severity:	S3 (Non-critical)
Version:	5.0.51	OS:	Linux (Ubuntu 8.04)
Assigned to:		CPU Architecture:	Any

Description:
To get LAN access I changed bind-address from "127.0.0.1" to "192.168.172.111". Weeks later I changed from a fixed IP to DHCP.

The mysqld server refused to start. But there is no error message in /var/log/messages or /var/log/mysql.err or /var/log/mysql.log or dmesg. There is no error message output from "/etc/init.d/mysql start" or "/usr/bin/mysqld_safe".

Only by hacking these scripts and running /usr/sbin/mysqld myself did I finally see an error message which said "Can't start server: Bind on TCP/IP port: Cannot assign requested address"

The server could no longer bind to "192.168.172.111" because DHCP had changed my IP address to "192.168.172.200".

Changing my.cnf back to 127.0.0.1 fixed the problem.

Using eth0 rather than localhost is for servers, and DHCP is for workstations, not servers, so the behavior is reasonable. If bind-address could be set to "eth0" the server would have started, but that's a new feature. However, it is NOT reasonable that no error message comes out anywhere.

How to repeat:
In my.cnf, set the bind-address to an invalid IP address. The problem of mysqld not running repeats (which is reasonable), the problem of no error message repeats (which is not reasonable). Set the bind-address to a valid IP address and mysqld runs fine.

Suggested fix:
/usr/sbin/mysqld is outputting to stdout (or stderr) lines containing the marker "[ERROR]". Somebody should ensure that those lines are logged also. Whenever mysqld fails to start, it should log an error message somewhere (preferably everywhere).

Thank you for the report.

I can not repeat described behavior:

ssmirnova@foobar ~/build/mysql-5.0
$rm -rf data/logerror.err 

ssmirnova@foobar ~/build/mysql-5.0
$ ./bin/mysqld_safe --defaults-file=support-files/my-small.cnf --basedir=.  --datadir=./data  --port=33050  --log-error --bind-address=123.12.12.1 &
[1] 12272

Starting mysqld daemon with databases from ./data

STOPPING server from pid file ./data/foobar.mysql.com.pid
080905 08:23:46  mysqld ended

[1]+  Done                    ./bin/mysqld_safe --defaults-file=support-files/my-small.cnf --basedir=. --datadir=./data --port=33050 --log-error --bind-address=123.12.12.1

ssmirnova@foobar ~/build/mysql-5.0
$cat data/logerror.err 
080905  8:23:45  InnoDB: Started; log sequence number 0 1203261693
080905  8:23:45 [ERROR] Can't start server: Bind on TCP/IP port: Cannot assign requested address
080905  8:23:45 [ERROR] Do you already have another mysqld server running on port: 33050 ?
080905  8:23:45 [ERROR] Aborting

080905  8:23:45  InnoDB: Starting shutdown...
080905  8:23:46  InnoDB: Shutdown completed; log sequence number 0 1203261693
080905  8:23:46 [Note] /users/ssmirnova/build/mysql-5.0/libexec/mysqld: Shutdown complete

Please indicate which MySQL package (filename) do you use.

Here is /etc/mysql/my.cnf (I know of no way to upload an attachment). Notice that it says "Error logging goes to syslog. This is a Debian improvement"
#
# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
# 
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

# This will be passed to all mysql clients
# It has been reported that passwords should be enclosed with ticks/quotes
# escpecially if they contain "#" chars...
# Remember to edit /etc/mysql/debian.cnf when changing the socket location.
[client]
port		= 3306
socket		= /var/run/mysqld/mysqld.sock

# this line added by andy canfield
# however this stupid option only applies to mysql_* clients,
# not to my C++ or PHP programs
default-character-set = binary

# Here is entries for some specific programs
# The following values assume you have at least 32M ram

# This was formally known as [safe_mysqld]. Both versions are currently parsed.
[mysqld_safe]
socket		= /var/run/mysqld/mysqld.sock
nice		= 0

[mysqld]
#
# * Basic Settings
#

#
# * IMPORTANT
#   If you make changes to these settings and your system uses apparmor, you may
#   also need to also adjust /etc/apparmor.d/usr.sbin.mysqld.
#

# this line added by andy canfield
default-character-set = binary

user		= mysql
pid-file	= /var/run/mysqld/mysqld.pid
socket		= /var/run/mysqld/mysqld.sock
port		= 3306
basedir		= /usr
# datadir	= /var/lib/mysql
datadir		= /data/mysql
tmpdir		= /tmp
language	= /usr/share/mysql/english
skip-external-locking
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
# Set this to localhost like this:
#	bind-address = 127.0.0.1
# the mysql server will only be accessable from the local machine.
# Set this to the computer's eth0 IP address like this:
#	bind-address = 192.168.172.111
# and the mysql server will be accessable from anywhere on the LAN.
# However, be warned that if you are a workstation and you use dhcp
# then when your IP address changes the server will no longer run.
bind-address		= 127.0.0.1
#
# * Fine Tuning
#
key_buffer		= 16M
max_allowed_packet	= 16M
thread_stack		= 128K
thread_cache_size	= 8
#max_connections        = 100
#table_cache            = 64
#thread_concurrency     = 10
#
# * Query Cache Configuration
#
query_cache_limit       = 1M
query_cache_size        = 16M
#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
#log		= /var/log/mysql/mysql.log
#
# Error logging goes to syslog. This is a Debian improvement :)
# Andy - I'm getting no error messages
#
# Here you can see queries with especially long duration
#log_slow_queries	= /var/log/mysql/mysql-slow.log
#long_query_time = 2
#log-queries-not-using-indexes
#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
#       other settings you may need to change.
#server-id		= 1
#log_bin			= /var/log/mysql/mysql-bin.log
expire_logs_days	= 10
max_binlog_size         = 100M
#binlog_do_db		= include_database_name
#binlog_ignore_db	= include_database_name
#
# * BerkeleyDB
#
# Using BerkeleyDB is now discouraged as its support will cease in 5.1.12.
skip-bdb
#
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
# You might want to disable InnoDB to shrink the mysqld process by circa 100MB.
#skip-innodb

# Under Linux, it is advisable to disable the write-back cache.

# default is 10MB initial file; OOP_FAC is almost 60MB
innodb_data_file_path=ibdata:60M:autoextend

#
# * Security Features
#
# Read the manual, too, if you want chroot!
# chroot = /var/lib/mysql/
#
# For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
#
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem

[mysqldump]
quick
quote-names
max_allowed_packet	= 16M

[mysql]
#no-auto-rehash	# faster start of mysql but no tab completition

[isamchk]
key_buffer		= 16M

#
# * NDB Cluster
#
# See /usr/share/doc/mysql-server-*/README.Debian for more information.
#
# The following configuration is read by the NDB Data Nodes (ndbd processes)
# not from the NDB Management Nodes (ndb_mgmd processes).
#
# [MYSQL_CLUSTER]
# ndb-connectstring=127.0.0.1

#
# * IMPORTANT: Additional settings that can override those from this file!
# The files must end with '.cnf', otherwise they'll be ignored.
#
!includedir /etc/mysql/conf.d/

Please indicate which MySQL package do you use.

dpkg-query --showformat='${Status} ${Package} ${Version} ${Revision}\n' -W '*mysql*'
says that these mysql packages are installed:

installed libdbd-mysql-perl 4.005-1
installed libmysqlclient15-dev 5.0.51a-3ubuntu5.1
installed libmysqlclient15off 5.0.51a-3ubuntu5.1
installed mysql-client 5.0.51a-3ubuntu5.1
installed mysql-client-5.0 5.0.51a-3ubuntu5.1
installed mysql-common 5.0.51a-3ubuntu5.1
installed mysql-doc-5.0 5.0.56-0ubuntu1
installed mysql-gui-tools-common 5.0~rc12-2ubuntu1
installed mysql-query-browser 5.0~rc12-2ubuntu1
unpacked mysql-server 5.0.51a-3ubuntu5.1
half-configured mysql-server-5.0 5.0.51a-3ubuntu5.1
installed php5-mysql 5.2.4-2ubuntu5.3

Thank you for the feedback.

You are using Debian packages. Please try with ours latest version 5.0.67 available at http://dev.mysql.com/downloads/mysql/5.0.html#downloads

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

Hi, 

What about having MySQL server listen to both to localhost and external ip. The same instance of the database. What is the syntax for that? Is it possible?

Best regards - Jon

I have the same problem, i can't bind 2 different IP, also when i bind some IP mysqld don't start without error logs