MySQL problem

Discussion in 'Server Operation' started by satimis, Jun 22, 2008.

  1. satimis

    satimis New Member

    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
     
    Last edited: Jun 22, 2008
  2. falko

    falko Super Moderator Howtoforge Staff Moderator HowtoForge Supporter ISPConfig Developer

    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?
     
  3. satimis

    satimis New Member

    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
     
  4. falko

    falko Super Moderator Howtoforge Staff Moderator HowtoForge Supporter ISPConfig Developer

    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.
     
  5. satimis

    satimis New Member

    Problem SOLVED

    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/ 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 &
    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
     
    Last edited: Jun 25, 2008
  6. satimis

    satimis New Member

    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
     
    Last edited: Jun 25, 2008

Share This Page