HowtoForge Forums | HowtoForge - Linux Howtos and Tutorials

HowtoForge Forums | HowtoForge - Linux Howtos and Tutorials (http://www.howtoforge.com/forums/index.php)
-   Server Operation (http://www.howtoforge.com/forums/forumdisplay.php?f=5)
-   -   MySQL problem (http://www.howtoforge.com/forums/showthread.php?t=24394)

satimis 22nd June 2008 13:57

MySQL problem
 
Hi folks,


Ubuntu 7.04 server amd64


It just comes to my notice on running following commands

$ sudo /etc/init.d/mysql stop
Code:

* Stopping MySQL database server mysqld      [fail]

$ sudo /etc/init.d/mysql start
Code:

* 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
Code:

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
Code:

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
Code:

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 23rd June 2008 20:18

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 24th June 2008 00:55

Quote:

Originally Posted by falko (Post 132466)
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
Code:

/usr/bin/mysql --defaults-file=/etc/mysql/debian.cnf -e "\. ${tmpfile}"
I have been playing around on that file;

# cat /etc/mysql/debian.cnf
Code:

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 24th June 2008 18:05

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 25th June 2008 06:11

Problem SOLVED
 
Quote:

Originally Posted by falko (Post 132636)
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
Code:

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
Code:

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
Code:

tcp        0      0 *:mysql                *:*                    LISTEN    9669/mysqld
MySQL is running supposed on safe_mode


# kill `cat /var/run/mysqld/mysqld.pid`
Code:

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;
Code:


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;
Code:

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


# mysqld_safe --init-file=/home/satimis/mysql-init
Code:

Starting mysqld daemon with databases from /var/lib/mysql
mysqld_safe[10101]: started
(it just hanging here)


On Xterm-2
=======

# netstat -tap | grep mysql
Code:

tcp        0      0 *:mysql                *:*                    LISTEN    10103/mysqld

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


On Xterm-1

It starts to display
Code:

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


Problem still remain.


Also tried
Code:

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/+s....0/+bug/153221


Edit-2;

* * * PROBLEM SOLVED * * *


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


# mysqld_safe --skip-grant-tables &
Code:

[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
Code:

# 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
Code:

* 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
Code:

* /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
Code:

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
Code:

* Stopping MySQL database server mysqld          [ OK ]

# /etc/init.d/mysql start
Code:

* 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 25th June 2008 15:41

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
Code:

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
Code:

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
Code:

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
Code:

....
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
Code:

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;
Code:

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
Code:

......
.....
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
Code:

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
Code:

Stopping MySQL database server: mysqld.
Starting MySQL database server: mysqld.


$ ps ax | grep -i mysql
Code:

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
Code:

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


Why;
Code:

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
Code:

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


All times are GMT +2. The time now is 07:25.

Powered by vBulletin® Version 3.8.7
Copyright ©2000 - 2014, vBulletin Solutions, Inc.