MySQL problem

[satimis]

Hi folks,


Ubuntu 7.04 server amd64


It just comes to my notice on running following commands

$ sudo /etc/init.d/mysql stop
* Stopping MySQL database server mysqld [fail]



$ sudo /etc/init.d/mysql start
* Starting MySQL database server mysqld [ OK ]
/usr/bin/mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user 'debian-sys-maint'@'localhost' (using password: YES)'



I suppose the problem is caused by running several 'apt-get update' and 'apt-get upgrade'. Before MySQL start/stop without problem nor having warning.


Please advise how to fix this problem. TIA


Edit-1 :
$ sudo cat /etc/mysql/debian.cnf
Password:
# Automatically generated for Debian scripts. DO NOT TOUCH!
[client]
host = localhost
user = debian-sys-maint
password = EdKLb0cr3GElkUdM
socket = /var/run/mysqld/mysqld.sock
[mysql_upgrade]
user = debian-sys-maint
password = EdKLb0cr3GElkUdM
socket = /var/run/mysqld/mysqld.sock
basedir = /usr



"user = debian-sys-maint" is still there.


Edit-2:

$ sudo netstat -tap
Active Internet connections (servers and established)
Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name
tcp 0 0 *:20000 *:* LISTEN 5717/perl
tcp 0 0 *:nfs *:* LISTEN -
tcp 0 0 *:poppassd *:* LISTEN 5066/inetd
tcp 0 0 *:mysql *:* LISTEN 4774/mysqld
tcp 0 0 localhost.lo:bacula-dir *:* LISTEN 5605/bacula-dir
tcp 0 0 *:8333 *:* LISTEN 5574/httpd.vmware
tcp 0 0 localhost.loc:bacula-fd *:* LISTEN 5565/bacula-fd
tcp 0 0 localhost.loc:bacula-sd *:* LISTEN 5402/bacula-sd
tcp 0 0 *:sunrpc *:* LISTEN 4185/portmap
tcp 0 0 *:webmin *:* LISTEN 5721/perl
tcp 0 0 *:www *:* LISTEN 5557/apache2
tcp 0 0 *:41424 *:* LISTEN -
tcp 0 0 172.16.103.1:domain *:* LISTEN 4609/named
tcp 0 0 172.16.59.1:domain *:* LISTEN 4609/named
tcp 0 0 *:ftp *:* LISTEN 5346/proftpd: (acce
tcp 0 0 mail.satimis.com:domain *:* LISTEN 4609/named
tcp 0 0 localhost.locald:domain *:* LISTEN 4609/named
tcp 0 0 mail.satimis.com:ssh *:* LISTEN 5178/sshd
tcp 0 0 localhost.localdoma:ipp *:* LISTEN 4691/cupsd
tcp 0 0 *:824 *:* LISTEN 5310/rpc.rquotad
tcp 0 0 *:smtp *:* LISTEN 5132/master
tcp 0 0 *:985 *:* LISTEN 5046/rpc.mountd
tcp 0 0 localhost.localdoma:953 *:* LISTEN 4609/named
tcp 0 0 *:34362 *:* LISTEN 5270/rpc.statd
tcp 0 0 *:https *:* LISTEN 5557/apache2
tcp 0 0 *:8222 *:* LISTEN 5574/httpd.vmware
tcp 1 0 mail.satimis.com:49999 128.241.220.88:www CLOSE_WAIT 8657/npviewer.bin
tcp 1 0 mail.satimis.com:52503 static.88-198-197-1:www CLOSE_WAIT 8657/npviewer.bin
tcp 1 0 mail.satimis.com:43337 cf-in-f127.google.c:www CLOSE_WAIT 8657/npviewer.bin
tcp 1 0 mail.satimis.com:54761 128.241.220.90:www CLOSE_WAIT 8657/npviewer.bin
tcp 0 0 mail.satimis.com:54554 128.241.220.88:www ESTABLISHED8657/npviewer.bin
tcp 1 0 mail.satimis.com:48350 cf-in-f165.google.c:www CLOSE_WAIT 8657/npviewer.bin
tcp 1 0 mail.satimis.com:48347 cf-in-f165.google.c:www CLOSE_WAIT 8657/npviewer.bin
tcp 1 0 mail.satimis.com:34771 www.howtoforge.com:www CLOSE_WAIT 8657/npviewer.bin
tcp 0 0 mail.satimis.com:34770 www.howtoforge.com:www CLOSE_WAIT 8657/npviewer.bin
tcp6 0 0 *:imaps *:* LISTEN 4940/couriertcpd
tcp6 0 0 *:pop3s *:* LISTEN 4975/couriertcpd
tcp6 0 0 *:pop3 *:* LISTEN 4953/couriertcpd
tcp6 0 0 *:imap2 *:* LISTEN 4920/couriertcpd
tcp6 0 0 *:domain *:* LISTEN 4609/named
tcp6 0 0 *:smtp *:* LISTEN 5132/master
tcp6 0 0 ip6-localhost:953 *:* LISTEN 4609/named



$ cat /etc/hosts
127.0.0.1 localhost.localdomain localhost
192.168.0.10 mail.satimis.com mail
# mail.satimis.com mail
#127.0.0.1 satimis.com

# The following lines are desirable for IPv6 capable hosts
::1 ip6-localhost ip6-loopback
fe00::0 ip6-localnet
ff00::0 ip6-mcastprefix
ff02::1 ip6-allnodes
ff02::2 ip6-allrouters
ff02::3 ip6-allhosts





B.R.
satimis

[falko]

The problem is that the password in /etc/mysql/debian.cnf does not match the one for debian-sys-maint in the MySQL database. Did you modify /etc/mysql/debian.cnf, oir did you copy your MySQL database from another server?

[satimis]

The problem is that the password in /etc/mysql/debian.cnf does not match the one for debian-sys-maint in the MySQL database. Did you modify /etc/mysql/debian.cnf, oir did you copy your MySQL database from another server?
I haven't touched anything on MySQL. It just comes to my notice that MySQL only starts on safe mode. Acturally after the mail server box started working sometime ago I left it there.

I have another Ubuntu box, LAMP 6.06 amd64, it also suffers the same problem after upgrade. MySOL only starts on safe mode.

Google found me several threads of similar problem, some of them solved and another unsolved. They were playing around on debian.cf

There is a line on /etc/init.d/mysql
/usr/bin/mysql --defaults-file=/etc/mysql/debian.cnf -e "\. ${tmpfile}"


I have been playing around on that file;

# cat /etc/mysql/debian.cnf
Password:
# Automatically generated for Debian scripts. DO NOT TOUCH!
[client]
host = localhost
user = debian-sys-maint
password = EdKLb0cr3GElkUdM
socket = /var/run/mysqld/mysqld.sock
[mysql_upgrade]
user = debian-sys-maint
password = EdKLb0cr3GElkUdM
socket = /var/run/mysqld/mysqld.sock
basedir = /usr

- replacing the password with mysql root password
- deleting the sock lines
- replacing the host with the name of the server

Non of them can solve my problem.


satimis

[falko]

I think you should set a new password for the debian-sys-maint user in the mysql.user table and then put that password in /etc/mysql/debian.cnf.

[satimis]

I think you should set a new password for the debian-sys-maint user in the mysql.user table and then put that password in /etc/mysql/debian.cnf.
Hi falko,


My problem is unable to login MySQL. The strange thing is both Ubuntu boxes now suffering the same problem. At boot MySQL starts at safe mode.

Performed following tests;

Test-1
====


On Xterm-1

# ps ax | grep -i mysql
4605 ? S 0:00 /bin/sh /usr/bin/mysqld_safe
4647 ? Sl 0:00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --user=mysql --pid-file=/var/run/mysqld/mysqld.pid --skip-external-locking --port=3306 --socket=/var/run/mysqld/mysqld.sock
4648 ? S 0:00 logger -p daemon.err -t mysqld_safe -i -t mysqld
5490 pts/1 R+ 0:00 grep -i mysql



# kill `cat /var/run/mysqld/mysqld.pid`
No printout

# netstat -tap | grep mysql
# netstat -tap | grep mysqld
both with out printout

# mysqld_safe --no-defaults
Starting mysqld daemon with databases from /var/lib/mysql
mysqld_safe[7557]: started
(it hangs here waiting for input)
UPDATE mysql.user SET Password='abcde' WHERE User='debian-sys-maint';
FLUSH PRIVILEGES;


It just hangs there waiting for input.


Start another Xterm.


On Xterm-2

# netstat -tap | grep mysql
tcp 0 0 *:mysql *:* LISTEN 9669/mysqld

MySQL is running supposed on safe_mode


# kill `cat /var/run/mysqld/mysqld.pid`
cat: /var/run/mysqld/mysqld.pid: No such file or directory
kill: usage: kill [-s sigspec | -n signum | -sigspec] pid | jobspec ... or kill -l [sigspec]



# kill `cat /var/lib/mysql/mail.satimis.com.pid`
No complaint


On Xterm-1

It starts to display;

STOPPING server from pid file /var/lib/mysql/mail.satimis.com.pid
mysqld_safe[9738]: ended
# UPDATE mysql.user SET Password='abcde' WHERE User='debian-sys-maint';
bash: UPDATE: command not found
# FLUSH PRIVILEGES;
bash: FLUSH: command not found




Test-2
====

On Xterm-1

# nano /home/satimis/mysql-init
copying follow lines on it;
UPDATE mysql.user SET Password='abcde' WHERE User='debian-sys-maint';
FLUSH PRIVILEGES;



# mysqld_safe --init-file=/home/satimis/mysql-init
Starting mysqld daemon with databases from /var/lib/mysql
mysqld_safe[10101]: started
(it just hanging here)



On Xterm-2
=======

# netstat -tap | grep mysql
tcp 0 0 *:mysql *:* LISTEN 10103/mysqld


# kill `cat /var/run/mysqld/mysqld.pid`
No complaint


On Xterm-1

It starts to display
STOPPING server from pid file /var/run/mysqld/mysqld.pid
mysqld_safe[10155]: ended



Problem still remain.


Also tried
UPDATE mysql.user SET Password='abcde' WHERE User='debian-sys-maint@localhost';
FLUSH PRIVILEGES;

Without result.


Edit-1:

Bug #153221 in mysql-dfsg-5.0 (Ubuntu)
Bug #153221, first reported on 2007-10-16 by Emil Oppeln-Bronikowski
MySQL server fails to upgrade in Gutsy - debian-sys-maint user permission denied
https://bugs.launchpad.net/ubuntu/+source/mysql-dfsg-5.0/+bug/153221


Edit-2;

* * * PROBLEM SOLVED * * *


# kill `cat /var/run/mysqld/mysqld.pid`
No complaint


# mysqld_safe --skip-grant-tables &
[1] 5647
root@mail:/home/satimis# Starting mysqld daemon with databases from /var/lib/mysql
mysqld_safe[5686]: started
mysql -uroot mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.38-Ubuntu_0ubuntu1.4-log Ubuntu 7.04 distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> UPDATE user SET password=PASSWORD('abcde') WHERE user='debian-sys-maint';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

mysql> quit
Bye



# nano /etc/mysql/debian.cnf
Changing the passwords


# cat /etc/mysql/debian.cnf
# Automatically generated for Debian scripts. DO NOT TOUCH!
[client]
host = localhost
user = debian-sys-maint
password = abcde
socket = /var/run/mysqld/mysqld.sock
[mysql_upgrade]
user = debian-sys-maint
password = abcde
socket = /var/run/mysqld/mysqld.sock
basedir = /usr



# /etc/init.d/mysql restart
* Stopping MySQL database server mysqld
STOPPING server from pid file /var/run/mysqld/mysqld.pid
mysqld_safe[5742]: ended
[ OK ]
* Starting MySQL database server mysqld [ OK ]
* Checking for corrupt, not cleanly closed and upgrade needing tables.
[1]+ Done mysqld_safe --skip-grant-tables



# /etc/init.d/mysql status
* /usr/bin/mysqladmin Ver 8.41 Distrib 5.0.38, for pc-linux-gnu on x86_64
Copyright (C) 2000-2006 MySQL AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license
Server version 5.0.38-Ubuntu_0ubuntu1.4-log
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /var/run/mysqld/mysqld.sock
Uptime: 35 sec

Threads: 1 Questions: 136 Slow queries: 0 Opens: 132 Flush tables: 1 Open tables: 17 Queries per second avg
: 3.886



# ps ax | grep -i mysql
5788 pts/1 S 0:00 /bin/sh /usr/bin/mysqld_safe
5827 pts/1 Sl 0:00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --user=mysql --pid-file=/var/
run/mysqld/mysqld.pid --skip-external-locking --port=3306 --socket=/var/run/mysqld/mysqld.sock
5828 pts/1 S 0:00 logger -p daemon.err -t mysqld_safe -i -t mysqld
5904 pts/1 S+ 0:00 grep -i mysql



# /etc/init.d/mysql stop
* Stopping MySQL database server mysqld [ OK ]



# /etc/init.d/mysql start
* Starting MySQL database server mysqld [ OK ]
* Checking for corrupt, not cleanly closed and upgrade needing tables.



Problem solved NOW.


I'll fix another Ubuntu box later. IIf there is any problem i'll come back.

Thanks


satimis

[satimis]

Hi folks,


I'm in complete lost. The solution for fixing the problem on Ubuntu server 7.04 amd64 can't work on Ubuntu LAMP 6.06 amd64.

After finish on starting MySQL it complains.


# /etc/init.d/mysql start
Starting MySQL database server: mysqld.
* Root password is blank. To change it use:
* /etc/init.d/mysql reset-password
root@satimis:/home/satimis# /usr/bin/mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user 'debian-sys-maint'@'localhost' (using password: YES)'



Finally I have to reinstall MySQL. Steps performed as follows;


$ dpkg -l | grep mysql
ii libapache2-mod-auth-mysql 4.3.9-2ubuntu3 Apache 2 module for MySQL authentication
ii libdbd-mysql-perl 3.0002-2build1 A Perl5 database interface to the MySQL data
ii libmysqlclient15off 5.0.22-0ubuntu6.06.10 mysql database client library
ii mysql-client-5.0 5.0.22-0ubuntu6.06.10 mysql database client binaries
ii mysql-common 5.0.22-0ubuntu6.06.10 mysql database common files (e.g. /etc/mysql
ii mysql-server 5.0.22-0ubuntu6.06.10 mysql database server (current version)
ii mysql-server-5.0 5.0.22-0ubuntu6.06.10 mysql database server binaries
ii php5-mysql 5.1.2-1ubuntu3.10 MySQL module for php5
ii php5-mysqli 5.1.2-1ubuntu3.10 MySQL Improved module for php5



$ sudo apt-get remove --purge libapache2-mod-auth-mysq libdbd-mysql-perl libmysqlclient15off mysql-client-5.0 mysql-common mysql-server mysql-server-5.0 php5-mysql php5-mysql
Password:
....
Need to get 0B of archives.
After unpacking 87.0MB disk space will be freed.
Do you want to continue [Y/n]? Y
.....

Should I remove the complete /var/lib/mysql directory tree which is used by all MySQL versions, not necessarily only the one you are about to purge?

Remove the databases from all MySQL versions?
<Yes> <No>

select <No>

.....
Removing mysql-common ...
Purging configuration files for mysql-common ...
dpkg - warning: while removing mysql-common, directory `/etc/mysql' not empty so not removed.



$ sudo apt-get install mysql-server-5.0 mysql-common
....
Selecting previously deselected package mysql-common.
(Reading database ... 25445 files and directories currently installed.)
Unpacking mysql-common (from .../mysql-common_5.0.22-0ubuntu6.06.10_all.deb) ...
Selecting previously deselected package libmysqlclient15off.
Unpacking libmysqlclient15off (from .../libmysqlclient15off_5.0.22-0ubuntu6.06.10_amd64.deb) ...
Selecting previously deselected package libdbd-mysql-perl.
Unpacking libdbd-mysql-perl (from .../libdbd-mysql-perl_3.0002-2build1_amd64.deb) ...
Selecting previously deselected package mysql-client-5.0.
Unpacking mysql-client-5.0 (from .../mysql-client-5.0_5.0.22-0ubuntu6.06.10_amd64.deb) ...
Selecting previously deselected package mysql-server-5.0.
Unpacking mysql-server-5.0 (from .../mysql-server-5.0_5.0.22-0ubuntu6.06.10_amd64.deb) ...
Setting up mysql-common (5.0.22-0ubuntu6.06.10) ...
Setting up libmysqlclient15off (5.0.22-0ubuntu6.06.10) ...

Setting up libdbd-mysql-perl (3.0002-2build1) ...
Setting up mysql-client-5.0 (5.0.22-0ubuntu6.06.10) ...
Setting up mysql-server-5.0 (5.0.22-0ubuntu6.06.10) ...
Stopping MySQL database server: mysqld.
Starting MySQL database server: mysqld.



$ dpkg -l|grep mysql
ii libdbd-mysql-perl 3.0002-2build1 A Perl5 database interface to the MySQL data
ii libmysqlclient15off 5.0.22-0ubuntu6.06.10 mysql database client library
ii mysql-client-5.0 5.0.22-0ubuntu6.06.10 mysql database client binaries
ii mysql-common 5.0.22-0ubuntu6.06.10 mysql database common files (e.g. /etc/mysql
ii mysql-server-5.0 5.0.22-0ubuntu6.06.10 mysql database server binaries



Following packages found missing, not yet installed;
ii libapache2-mod-auth-mysql 4.3.9-2ubuntu3 Apache 2 module for MySQL authentication
ii mysql-server 5.0.22-0ubuntu6.06.10 mysql database server (current version)
ii php5-mysql 5.1.2-1ubuntu3.10 MySQL module for php5
ii php5-mysqli 5.1.2-1ubuntu3.10 MySQL Improved module for php5



I have no idea whether "mysql-server" is needed. Because "mysql-server-5.0" is already installed. So I leave it and continue installing;


$ sudo apt-get install libapache2-mod-auth-mysql php5-mysql php5-mysqli
......
.....
Setting up libapache2-mod-auth-mysql (4.3.9-2ubuntu3) ...
Setting up php5-mysql (5.1.2-1ubuntu3.10) ...

Setting up php5-mysqli (5.1.2-1ubuntu3.10) ...



$ mysql -u root -p
Enter password: xyz

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7 to server version: 5.0.22-Debian_0ubuntu6.06.10-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.


mysql> SET PASSWORD FOR root = PASSWORD('xyzadmin');
ERROR 1133 (42000): Can't find any matching row in the user table


mysql> SET PASSWORD FOR 'root' = PASSWORD('xyzadmin');
ERROR 1133 (42000): Can't find any matching row in the user table


mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('xyzadmin');
Query OK, 0 rows affected (0.00 sec)

mysql> quit
Bye



$ sudo /etc/init.d/mysql restart
Stopping MySQL database server: mysqld.
Starting MySQL database server: mysqld.



$ ps ax | grep -i mysql
6504 pts/0 S 0:00 /bin/sh /usr/bin/mysqld_safe
6565 pts/0 Sl 0:00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --user=mysql --pid-file=/var/run/mysqld/mysqld.pid --skip-locking --port=3306 --socket=/var/run/mysqld/mysqld.sock
6566 pts/0 S 0:00 logger -p daemon.err -t mysqld_safe -i -t mysqld
6641 pts/0 S+ 0:00 grep -i mysql



MySQL is working now.


Please help me to understand WHY it needs
mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('xyzdmin');
Query OK, 0 rows affected (0.00 sec)



Why;
mysql> SET PASSWORD FOR root = PASSWORD('xyzadmin');
ERROR 1133 (42000): Can't find any matching row in the user table


mysql> SET PASSWORD FOR 'root' = PASSWORD('xyzadmin');
ERROR 1133 (42000): Can't find any matching row in the user table

don't work ???


What does
Query OK, 0 rows affected (0.00 sec)

indicate ???


Do I need installing "mysql-server" additionally to "mysql-server-5.0" ?


TIA


B.R.
satimis