Problem: mysql & mysqld cannot communicate (error 2003)
Synopsis:
(a) I installed mysql on a Unix SunOS system, but the post-installation died silently
in mysql_install_db. There
were no diagnostic messages. Trial-and-error indicated that mysql_install-db has a bug
(a missing '&'). When
this is patched, the script seems to work.
(b) But I still can't get any response from mysql, only error 2003: Can't connect to
MySQL server. I've search
the documentation and the newsgroup archives fairly intensively. I can see that a
number of other people have
had the same or similar problem, but none of the proposed solutions have worked for
me. I have now spent about
about 15 hours just trying to get MySQL to do anything. Full details below. Any useful
suggestions that I've not
already tried would be appreciated.
System:
Operating system: SunOS 5.6 on a Sun Ultra 5
MySql version: 3.22.30
Binary distribution: mysql-3_22_30-sun-sunos4_1_4-sparc_tar.gz
Actions taken:
(1) INSTALLATION
0. I installed a binary because I don't have C++ on my Unix box. I chose the SunOS-4
version of MySQL
because that seemed closest to the SunOS-5 system that I am using.
1. The MySQL documentation recommends installing in /usr/local/ but this directory did
not exist, and /usr is
protected. So, I unprotected /usr and created /local.
2. I added the mysql command:
shell> groupadd mysql
shell> useradd -g mysql mysql
3. I attempted the specified unpacking command:
shell> gunzip < /path/to/mysql-VERSION-OS.tar.gz | tar xvf -
which in my case should be:
shell> gunzip < /usr/local/mysql-3_22_30-sun-sunos4_1_4-sparc_tar.gz | tar xvf -
This did not work.
(a) gunzip was not recognised, but I found that gzip -d could be used instead.
(b) tar kept on failing with the error message 'tar: directory checksum error'. I
downloaded some other versions
of MySQL but got the same error. After searching around the net, it seemed that I
needed to use GNU tar. I
downloaded GNU tar; it didn't work, but then I found a GNU tar already on the system
called 'gtar' and used
that. (I then noticed the completely uninformative sentence in the MySQL
documentation: "Sun tar is known
to have problems", which is meaningful only in retrospect.) So, I unpacked the MySQL
files with:
shell> gzip -d < /usr/local/mysql-3_22_30-sun-sunos4_1_4-sparc_tar.gz | tar xvf -
and created a link to the new directory:
shell> ln -s /usr/local/mysql-3_22_30-sun-sunos4_1_4-sparc mysql
(2) POST-INSTALLATION
4. The online documentation recommends the next step should be
shell> ./configure
but the file INSTALL-BINARY in the distribution doesn't mention this and, looking
inside the script 'configure',
all it seems to do is run mysql_install_db, so I skipped this step.
4. Then I tried to set up the 'grant tables'
shell> cd mysql
shell> ./scripts/mysql_install_db
This successfully sets some local variables as reported on the screen:
Creating db table
Creating host table
Creating user table
Creating func table
Creating tables_priv table
Creating columns_priv table
And then nothing at all happens. It just freezes and stays frozen. At this point
(judging from what is inside the
script, mysqld has been started up and the table creation commands issued, but there
has been no response.
It's not clear whether it is still expecting input. I typed in various SQL commands as
well as the end-of-input
code, but got no response. Finally, I hit <CTRL-Z> and he process closed with 'Stopped
(user)'.
4a. The standard failure message, 'Installation of grant tables failed!' was *not*
produced. I believe this
indicates that mysqld did not terminate.
4b. The (undisplayed) error message inside mysql_instal_db also suggested the
following command to start the
MySQL daemon:
./bin/mysqld --skip-grant &
This did not succeed, as the parameter has to be --skip-grant-tables:
./bin/mysqld --skip-grant-tables &
which seemed to work. But I still could not get any response from mysql.
4c. I read the relevant section of the online documentation (2.4.1 Problems Running
mysql_install_db) but it
had nothing to say about this situation.
4d. I therefore started looking through the archive of the newsgroup. I searched
initially for 'mysql_install_db'.
This yielded almost 2000 reports: the ones I looked at were all reporting problems
identical or similar to mine,
but without any solutions. I therefore restricted the search to 'mysql_install_db and
SunOS', which reduced the
set of messages to 53. These, too, were mostly reporting the same problem:
mysql_install_db freezing up
without any diagnostic messages.
4e. Looking at the script, it seems that, since the line does not end in '&', the
daemon is executing in the
foreground, so inevitably it will stay running forever (or until killed), and the code
that follows this line (to report
success or failure) will never be execute. To test this hypothesis, I tried the
following: while the command
window was frozen trying to run mysql_install_db, I killed the mysqld from another
window. Immediately, the
script stepped forward to the diagnostic message 'Installation of grant tables
failed!'. My conclusion from this is
that the script mysql_install_db has a bug in this respect (i.e. lacking '&'). To
follow this up, I edited
mysql_install_db, and appended '&' to the mysqld command line, and then tried again.
This time, the script went
through seemingly correctly.
(C) MYSQLACCESS & MYSQLADMIN
5a. A suggestion in the documentation was to use the script 'mysqlaccess' to get some
diagnostic information,
but this failed to execute because it is a Perl script, and Perl does not seem to be
installed on this Unix box.
5b. A suggestion from the help message for mysqld is to use
shell> ./bin/mysqladmin variables
but this command freezes with no diagnostic message.
(D) PORT LOCK
6. While I was still working with the broken mysql_install_db, I tried again to run
the daemon with:
./bin/mysqld --log --skip-grant-tables --basedir . &
which failed with the message
Can't start server: Bind on TCP/IP port: Address already in use
Do you already have another mysqld server running on port: 3306
Aborting
The command 'ps -e' showed that another instance of mysqld was running. I deleted the
other instance of
mysqld, and tried again. This seemed to get the daemon running; but mysql was still
unresponsive.
(E) ERROR FILES
7a. According to the documentation, a log should have been written to mysql/data/. In
fact, this directory
contained two sub-directories, 'mysql' and 'test', both of which were empty. (I later
got some basic start and
stop messages in mysql/data/wcm1s0m1.err by using the script safe_mysqld.) There was
also a suggestion in
the (undisplayed) error message inside the script that a log should be written to
another place, '/var/db/mysql',
but in fact the directory 'var' did not exist.
7b. Another suggestion was to start the MySQL daemon with
shell> ./bin/safe_mysqld &
This flashed up an error message and then immediately closed the command window,
making it impossible to
read the message. I captured the message by redirection, and it said that it was
unable to move to the directory
/usr/local/mysql/var. I therefore manually created the directory var and tried the
command again. This time it
seemed to work, and displayed the message:
Starting mysqld daemon with databases from
/usr/local/mysql-3.22.30-sun-sunos4.1.4-sparc/data
There was still no log file in the 'data' directory. But: inside the directory 'var' I
found the file wcm1s0m1.log
(wcm1s0m1 being the name of the workstation). This file contained
./bin/mysqld, Version: 3.22.30-log, started with:
Tcp port: 3306 Unix socket: /tmp/mysql.sock
Time Id Command Argument
This at least indicates the daemon was alive; but mysql was still unresponsive.
7c. I also tried safe_mysql, which wrote a line to data/wcm1s0m1.err:
mysqld started on Wed Jun 5 10:23:15 EDT 2002
but no other messages were written to the file when executing mysql.
8a. The online documentation says that log files will by default go to /usr/local/var,
but that they can be resent
to /usr/local/mysql/var by means of this command:
/configure --prefix=/usr/local/mysql
Neither statement is true: the log file is going to usr/local/mysql/var anyway
(provided that the directory has
been manually created), and the script 'configure' does not handle any such change.
(All it does is try to run
mysql_install_db.)
8b. The online documentation also says that the log files can be sent to the correct
directory,
/usr/local/mysql/data, using this command:
/configure --prefix=/usr/local/mysql --localstatedir=/usr/local/mysql/data
Again, this does not work for the same reason.
(F) MySQL SHELL
9. I tried to access the MySQL command shell:
./bin/mysql -u root mysql
On early attempts, this simply froze: no prompt, no output, and the user can type
anything without any response
until <CTRL-Z>. I'm not sure, but I think that those attempts were made when I had, at
first, installed MySQL in
a place other than /usr/local/mysql. I then deleted the installed files, and
re-installed in /usr/local/mysql. But I
can't be sure that that was when I stopped getting a frozen mysql and started getting
error 2003.
10a. On later attempts, I obtained
Error 2003: can't connect to MySQL server on 'localhost'
10b. The displayed output from mysql_install_db says a password should be set with:
./bin/mysqladmin -u root password 'new password'
This, however, yields the message:
./bin/mysqladmin: connect to server at 'localhost' failed
error: 'Can't connect to MySQL server on 'localhist' (61)'
Check that mysqld is running on localhost and that the port is 3306.
You can check this by doing 'telnet localhost 3306'.
which is a rather more informative diagnostic than mysql yields.
10c. Checking through the installation instructions in the online documentation, I
noticed the following
commands:
shell> chown -R root .
shell> chown -R mysql data
shell> chgrp -R mysql .
even though they were not listed in the distributed INSTALL-BINARY file. These failed
in normal mode, but
seemed to work in superuser. They did not, however, make any difference to the error
2003.
11a. One of the suggestions in the newsgroup archives was that /etc/hosts should be
fixed to contain
172.0.0.1 localhost.localdomain hostname
In fact, I checked my /etc/hosts file and it had
172.0.0.1 localhost is-to-oscar
which looks OK to me. Another suggestion was to use the '-h' or '--host=' parameter,
so I tried variously:
./bin/mysql -h localhost -u root mysql
./bin/mysql -h is-to-oscar -u root mysql
./bin/mysql -h 127.0.0.1 -u root mysql
all of which failed with Error 2003. I also tried:
./bin/mysqladmin -h localhost -u root password 'new password'
./bin/mysqladmin -h is-to-oscar -u root password 'new password'
./bin/mysqladmin -h 127.0.0.1 -u root password 'new password'
all of which failed as before.
11b. I tried the telnet option:
telnet localhost 3306
telnet is-to-oscar 3306
telnet 127.0.0.1 3306
which all failed with:
telnet: Unable to connect to remote host: Connection refused
The only difference was that the third attempt yielded:
Trying 127.0.0.1 ....
before failing. I also pinged all three addresses (127.0.0.1, localhost, is-to-oscar)
and was told that they were
all alive.
11c. Following a suggestion in the newsgroup archive, I also tried 'resolveip'. (This
is not a command, as
suggested in the posting, but a script in mysql/bin.) This reported:
./bin/resolveip: Unable to find hostid for 'localhost'
I also tried it with 'is-to-oscar' and '127.0.0.1', and got:
./bin/resolveip: Unable to find hostid for 'is-to-oscar'
and
Host name of 127.0.0.1 is localhost
11c. I did a search on the MySQL web site for 'can't connect', and one of the finds
was: 4.2.11 (Causes of
Access denied Errors). I think this probably refers to access denial *after*
connection, rather than failure of
connection; but anyway ...it recommended this:
shell> mysql -u root test
The 'mysql' command is not recognised, so I used:
shell> ./bin/mysql -u root test
which gave the same response (2003: Can't connect ...)
11d. The same section also said that the following file must exist:
mysql/var/mysql/user.MYD
I found that the directory mysql/var/mysql did not exist, so I created and inserted an
empty file user.MYD. I
then re-tried accessing mysql, but got the same respone (2003: Can't connect)
11e. The search also revealed: A.2.3 (Can't connect to [local] MySQL server Error),
which recommends trying
the following:
shell> mysqladmin version
shell> mysqladmin variables
shell> mysqladmin -h `hostname` version variables
shell> mysqladmin -h `hostname` --port=3306 version
shell> mysqladmin -h 'ip for your host' version
shell> mysqladmin --socket=/tmp/mysql.sock version
All of these fail with the same 2003 error, except for '-h 127.0.0.1', which just
freezes.
12a. In A.2.3 (Can't connect to [local] MySQL server Error), there is a suggestion of
using sockets by means of
the parameter '--skip-networking' when starting up mysqld. In 4.1.1 (mysqld
Command-line Options), we find
for '--skip-networking': "This option is highly recommended for systems where only
local requests are allowed"
(i.e. instead of TCP/IP). I therefore inserted this in the command line in
mysql_install_db. I retried mysql but it
made no difference: I still got error 2003.
12b. This section also states that the default socket file is /tmp/mysqld.sock, and
not /tmp/mysql.sock as
stated elsewhere. I therefore created a link:
shell> ln -s /tmp/mysql.sock /tmp/mysqld.sock
I then retried mysql with both socket names but it made no difference:
shell> mysqladmin --socket=/tmp/mysql.sock version
shell> mysqladmin --socket=/tmp/mysqld.sock version
I still get error 2003. I also added an explicit parameter '--socket=/tmp/mysql.sock'
in the script
mysql_install_db. It still made no difference to error 2003. Also, more than one
posting stated that mysqld
creates the file /tmp/mysql.sock when it starts up. Well, I tried deleting the socket
file and starting up mysqld,
and I found that it did *not* create mysql.sock. It has to be created manually if it
is to exist at all.
12c. I issued the command
shell> netstat -av
The output table did not mention mysql.sock or mysqld.sock, and therefore suggests
that MySQL's socket is
not active.
12d. A posting on the MySQL suggested that the problem could be caused by inadequate
access permissions
on the socket file. So I used chmod to full world access to /tmp and /tmp/mysql.sock.
This made no difference.
12e. Another posting in mysql.list suggested that mysqld needs to create mysql.sock in
mysql/var/lib/mysql.
That directory did not exist, so I created it and restarted mysqld. This made no
difference. mysql.lock did not
appear in that directory, and I still get error 2003. Several postings are adamant
that mysqld creates the
socket file, but this simply doesn't happen. Maybe that is a clue: do I need to tweak
something else to get
mysqld to create the socket file?
12f. I noticed that, even though I'm trying to use a socket connection, I keep getting
this error:
ERROR 2003: Can't connect to MySQL server on 'localhost' (61)
but other people posting on usenet get this error:
ERROR 2002: Can't connect to local MySQL server through socket '...path...'(111)
It looks as though mysql is still trying to use TCP/IP even though I want it to use a
socket. From the mysql help
listing, the only relevant parameter seems to be '--socket=path', so I tried this:
./bin/mysql --socket=/tmp/mysql.sock -u root test
But I still get error 2003. I also tried
./bin/mysql --socket=/tmp/mysql.sock --host='' -u root test
but to no avail: I still get error 2003 saying it can't connect to 'localhost'.
12g. One posting suggested that the executable may have been compiled with "pthreads
instead of native
threads", which would mean that it could not use Unix sockets. Instead, it would have
to use TCP/IP. I don't
know how my binary was compiled. (It's not documented on mysql.com.) So, I tried to
switch to using TCP/IP. I
killed the server mysqld, edited the script mysql_install_db to replace
'--skip-networking --socket=/tmp/mysql.sock'
with
'--skip-name-resolve'.
I then retried mysql:
shell> ./bin/mysql -h 127.0.0.1 -u root test
I still got the error 2003. There is another experiment that I ran to test this
possibility, as follows. If I try to to
run multiple instances of the server using TCP/IP, by issuing the command:
shell> ./bin/mysqld &
then on the 2nd and subsequent attempts I get the error message:
Can't start server: Bind in TCP/IP port: Address already in use.
Do you already have another mysqld server running on port 3306?
But, if I start up multiple instances of the server ostensibly with socket access:
shell> ./bin/mysqld --skip-networking &
then I don't get that error message. To be sure, that doesn't *prove* that this
executable can use sockets, but
it is circumstantial evidence that at least the server executable doesn't try to use
TCP/IP when given the
--skip-networking flag.
12h. In one of the help messages, it says that there are three files where
socket-related things might be set
(although, unfortunately, it did not say which took precedence, the files or the
command-line arguments).
/etc/my.cnf
mysql/data/my.cnf
~/.my.cnf
In fact, none of these files exist.
---
I am running out of things to try. At present, the only candidate explanation is that
the mysql and mysqld
executables were compiled using pthreads, and that the client mysql keeps on trying to
reach the server
through a TCP/IP connection even when told to use sockets. But that doesn't explain
why the client still can't
talk with the server when I specify to use TCP/IP.
Given that this is supposed to be an easy installation, I'm a little disappointed in
MySQL, and hope somebody
out there has some constructive suggestions ...
---
Some suggested fixes:
a. The documentation should specify that tar fails with the misleading message that
the tar file is corruprt; and
it should advise people to use gtar instead.
b. The script mysql_install_db is incorrect, and should have '&' at the end of the
mysqld command line.
c. Documentation (online and in-distribution) that refers to the parameter
'--skip-grant' is incorrect, and should
be changed to '--skip-grant-tables'.
d. The advice to use sockets rather than TCP/IP for connecting to a server on the
local machine should surely
be stated in the main part of the installation instructions, not tucked away in an
appendix.
e. Section A.2.3 (Can't connect to [local] MySQL server Error) refers to 2002 only,
not 2003. If both messages
are possible, it should discuss them both.
f. Documentation incorrectly states default location of error file, and use of the
'configure' script to change it.
Peter Lloyd
--- mysqlbug form ---
From: Peter Lloyd <[EMAIL PROTECTED]>
To: [EMAIL PROTECTED]
Subject: Error 2003: mysql and mysqld not talking to one another
>Description:
Trying to run mysql always fails with error 2003: can't connect with server.
>How-To-Repeat:
./bin/mysql -u root test
>Fix:
Not known.
>Submitter-Id: [EMAIL PROTECTED]
>Originator: Peter Lloyd
>Organization: Nortel Networks
>MySQL support: none
>Synopsis: Trying to run mysql always fails with error 2003: can't connect with
>server.
>Severity: critical
>Priority: high
>Category: mysql
>Class: support
>Release: mysql-3.22.30 (TCX binary)
>Environment:
System: SunOS wcm1s0m1 5.6 Generic_105181-19 sun4u sparc SUNW,Ultra-5_10
Architecture: sun4
Some paths: /opt/bin/perl /usr/ccs/bin/make /opt/SUNWspro/bin/cc
Compilation info: CC='gcc' CFLAGS='-O2 -fomit-frame-pointer' CXX='gcc'
CXXFLAGS='-O2
-fomit-frame-pointer' LDFLAGS=''
Configure command: ./configure --prefix=/usr/local/mysql '--with-comment=TCX binary'
--disable-shared
Perl: This is perl, version 5.005_02 built for sun4-solaris
--- End of form ---
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
