Mysql PID Error, Locking Database

Question

I changed my.cnf file to add some parameters in it but after restarting the service it failed to start giving locking error. i removed locks using rm command and again trying to restart but it not starts

I also update it to my.cnf backup file. When i check the service status and trying to restart it giving the below messages.

# service mysql Status    
MySQL is not running, but lock exists                      [FAILED]

# service mysql restart    
MySQL manager or server PID file could not be found!       [FAILED]

I Centos 5.3 server and mysql 5.3 community edition

Thanks,

Master

Here is the error log file:

110702 06:00:50 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
110702  6:00:50 [Note] Plugin 'FEDERATED' is disabled.
110702  6:00:50  InnoDB: Initializing buffer pool, size = 8.0M
110702  6:00:50  InnoDB: Completed initialization of buffer pool
InnoDB: The first specified data file ./ibdata1 did not exist:
InnoDB: a new database to be created!
110702  6:00:50  InnoDB: Setting file ./ibdata1 size to 10 MB
InnoDB: Database physically writes the file full: wait...
110702  6:00:50  InnoDB: Log file ./ib_logfile0 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile0 size to 5 MB
InnoDB: Database physically writes the file full: wait...
110702  6:00:50  InnoDB: Log file ./ib_logfile1 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile1 size to 5 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Doublewrite buffer not found: creating new
InnoDB: Doublewrite buffer created
InnoDB: Creating foreign key constraint system tables
InnoDB: Foreign key constraint system tables created
110702  6:00:50  InnoDB: Started; log sequence number 0 0
110702  6:00:50 [Note] Event Scheduler: Loaded 0 events
110702  6:00:50 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.1.56'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Server (GPL)

Status information:

Current dir: /var/lib/mysql/
Running threads: 1  Stack size: 262144
Current locks:
lock: 0x127e9528:

lock: 0x12771468:

lock: 0x127dcfb8:

lock: 0x127e4dd8:

lock: 0x127e0f58:

lock: 0x12748588:

lock: 0x127c3568:

lock: 0x127bf2d8:

lock: 0x1276b9e8:

lock: 0x1278b108:

lock: 0x1279aed8:

lock: 0x12768978:

lock: 0x127a5ff0:

lock: 0x127d67e8:

lock: 0x127d42e8:

lock: 0x127d1aa8:

lock: 0x127cf6c8:

lock: 0x127cd738:

lock: 0x127b8cc8:

Key caches:
default
Buffer_size:       8384512
Block_size:           1024
Division_limit:        100
Age_limit:             300
blocks used:             7
not flushed:             0
w_requests:            164
writes:                 86
r_requests:            159
reads:                   4


handler status:
read_key:           19
read_next:          31
read_rnd             0
read_first:         30
write:             706
delete               4
update:              5

Table status:
Opened tables:        613
Open tables:           26
Open files:            52
Open streams:           0

Alarm status:
Active alarms:   1
Max used alarms: 2
Next alarm time: 28635

Begin safemalloc memory dump:

End safemalloc memory dump.

Memory status:
Non-mmapped space allocated from system: 7983104
Number of free chunks:                   34
Number of fastbin blocks:                0
Number of mmapped regions:               11
Space in mmapped regions:                23605248
Maximum total allocated space:           0

Space available in freed fastbin blocks: 0
Total allocated space:                   6866800
Total free space:                        1116304
Top-most, releasable space:              1045872
Estimated memory (with thread stack):    31850496



Events status:
LLA = Last Locked At  LUA = Last Unlocked At
WOC = Waiting On Condition  DL = Data Locked

Event scheduler status:
State      : INITIALIZED
Thread id  : 0
LLA        : n/a:0
LUA        : n/a:0
WOC        : NO
Workers    : 0
Executed   : 0
Data locked: NO

Event queue status:
Element count   : 0
Data locked     : NO
Attempting lock : NO
LLA             : drop_schema_events:375
LUA             : drop_schema_events:377
WOC             : NO
Next activation : never
110705  3:28:09 [Note] /usr/sbin/mysqld: Normal shutdown

110705  3:28:09 [Note] Event Scheduler: Purging the queue. 0 events

Solution

# service mysql Status 
MySQL is not running, but lock exists                      [FAILED]

It means that MySQL doesn't shutdown properly. The pid was removed but the lock file still exists. Delete the lock file and try again:

# rm -f /var/lock/subsys/mysqld

Comments

[topdog]

mysql is failing to start because of misconfiguration, check your log files you should see the reason why.

[Master-Man]

my log file is simple and by default created: [mysqld] set-variable = max_connections=500 log-slow-queries safe-show-database local-infile=0

[topdog]

Check the permissions and ownership on the pid file ls /var/run/mysqld -l

[topdog]

what is the location of the pid file set in your my.cf ?

[Master-Man]

this is the default my.cnf file: root@pc1 [~]# cat /etc/my.cnf [mysqld] set-variable = max_connections=500 log-slow-queries safe-show-database local-infile=0

[topdog]

Add this to your my.cnf [mysqld_safe] pid-file=/var/run/mysqld/mysqld.pid

[Master-Man]

After adding the above lines i restart or start the service but the same error: MySQL manager or server PID file could not be found! [FAILED] Starting MySQL......Manager of pid-file quit without updati[FAILED]

[Greg Petersen]

ls -l /var/run/mysqld/mysqld.pid? ps -ef | grep mysql?

[Toqeer]

If its stil not resolve the problem then remove the pid file also from /var/lib/mysql/mysql.sock

[Master-Man]

error log appended with question.