Not able to login to mysql
 

Hi,
On Ubuntu 10.04 installed mysql server, before yesterday it was working fine.
All of sudden today showing error while logging into mysql server. and some times logging into server without any error.


root@ubuntu-server:~# mysql -uroot -p
Enter password:
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (111)

Tried with another user also same thing happening
some times able to login and some time not able to login.

Please help to solve the problem.


Thanks
Suresh

Ok, so either MySQL isn't running, or you're having a network issue. You post no details about your network or any diagnostics you've run so far, so it's hard to say. Have you checked anything? The MySQL server logs? Have you tried pinging/ssh'ing to the box, to see if it's a network issue? Where is the box located? Anything??

Also, being logged in as root (as you are above), is a bad idea.

That error message indicates that MySQL is not running. It would have given a an incorrect password message if it was.

Also since the host switch isn't specified I would assume that it's being run on the localhost.

hi suresh i got same issuse just restart the service of mysql it will work fine
and enter these command

If you can make sure that mysql is running but you still can't login...
then I have a little tutorial to reset root mysql password at this blog :

http://war49digest.blogspot.com/2012...assword_6.html

If mysql service is running, maybe you need to reset your mysql root user.

here is a guide:
# no password

get out the safemode and restart mysql

Try what steps acelino has suggested

Check whether mysql has started : ps ax|grep mysql
and
check the mysql logs for errors.

---------- Post added 03-08-13 at 11:47 AM ----------

Try what steps acelino has suggested

Check whether mysql has started : ps ax|grep mysql
and
check the mysql logs for errors.

I was to post another pointer, but when I checked I saw it is over a week ago this thread was posted and the OP didn't waste his time for feedback, I won't waste my time either.

jlinkels

Sorry for late reply, Actually i am on holiday's

Mysql is running, some times i can able to login,
there is no network issue,
i can ping and ssh to server,
And i checked from server itself.

---------- Post added 03-12-13 at 12:14 AM ----------

I provided correct password,
some times i can able to login

I followed ur procedure
root@ubuntu-server:~# mysqld_safe --skip-grant-tables &
[1] 27071

After entering above command iam gettig following things continiously.

root@ubuntu-server:~# 130311 14:42:36 mysqld_safe Logging to syslog.
130311 14:42:36 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
130311 14:42:38 mysqld_safe Number of processes running now: 0
130311 14:42:38 mysqld_safe mysqld restarted
130311 14:42:40 mysqld_safe Number of processes running now: 0
130311 14:42:40 mysqld_safe mysqld restarted

root@ubuntu-server:~# 130311 14:42:41 mysqld_safe Number of processes running now: 0
130311 14:42:41 mysqld_safe mysqld restarted
130311 14:42:43 mysqld_safe Number of processes running now: 0
130311 14:42:43 mysqld_safe mysqld restarted
130311 14:42:44 mysqld_safe Number of processes running now: 0
130311 14:42:44 mysqld_safe mysqld restarted
130311 14:42:46 mysqld_safe Number of processes running now: 0
130311 14:42:46 mysqld_safe mysqld restarted
130311 14:42:48 mysqld_safe Number of processes running now: 0
130311 14:42:48 mysqld_safe mysqld restarted
130311 14:42:49 mysqld_safe Number of processes running now: 0
130311 14:42:49 mysqld_safe mysqld restarted
130311 14:42:51 mysqld_safe Number of processes running now: 0
130311 14:42:51 mysqld_safe mysqld restarted

Here is mysql error log
Error getting continiously

root@ubuntu-server:~# tailf /var/log/mysql/error.log
130312 10:49:40 InnoDB: Completed initialization of buffer pool
InnoDB: Log scan progressed past the checkpoint lsn 28 4054118181
130312 10:49:40 InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
InnoDB: Doing recovery: scanned up to log sequence number 28 4054761466
130312 10:49:42 InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percents: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 130312 10:49:32 [Note] Plugin 'FEDERATED' is disabled.
130312 10:49:32 InnoDB: Initializing buffer pool, size = 8.0M
130312 10:49:32 InnoDB: Completed initialization of buffer pool
InnoDB: Log scan progressed past the checkpoint lsn 28 4054118181
130312 10:49:32 InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
InnoDB: Doing recovery: scanned up to log sequence number 28 4054761466
130312 10:49:37 InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percents: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99
InnoDB: Apply batch completed
130312 10:49:39 InnoDB: Started; log sequence number 28 4054761466
130312 10:49:39 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.1.67-0ubuntu0.10.04.1' socket: '/var/run/mysqld/mysqld.sock' port: 3306 (Ubuntu)
InnoDB: Error: trying to access page number 1303173499 in space 0,
InnoDB: space name ./ibdata1,
InnoDB: which is outside the tablespace bounds.
InnoDB: Byte offset 0, len 16384, i/o type 10.
InnoDB: If you get this error at mysqld startup, please check that
InnoDB: your my.cnf matches the ibdata files that you have in the
InnoDB: MySQL server.
130312 10:49:40 InnoDB: Assertion failure in thread 2949966704 in file ../../../storage/innobase/fil/fil0fil.c line 4135
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.1/...-recovery.html
InnoDB: about forcing recovery.
05:19:40 UTC - mysqld got signal 6 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help
diagnose the problem, but since we have already crashed,
something is definitely wrong and this may fail.

key_buffer_size=16777216
read_buffer_size=131072
max_used_connections=0
max_threads=151
thread_count=0
connection_count=0
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 345939 K bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0x0
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 0 thread_stack 0x30000
/usr/sbin/mysqld(my_print_stacktrace+0x2d) [0xb759812d]
/usr/sbin/mysqld(handle_fatal_signal+0x4a4) [0xb737c6b4]
[0xb6fc1400]
/lib/tls/i686/cmov/libc.so.6(abort+0x182) [0xb6cc0a82]
/usr/sbin/mysqld(fil_io+0x48c) [0xb747ce6c]
/usr/sbin/mysqld(+0x48180a) [0xb746080a]
/usr/sbin/mysqld(buf_read_page+0x27a) [0xb74613ea]
/usr/sbin/mysqld(buf_page_get_gen+0x349) [0xb745a6e9]
/usr/sbin/mysqld(+0x5154e0) [0xb74f44e0]
/usr/sbin/mysqld(+0x515c1d) [0xb74f4c1d]
/usr/sbin/mysqld(trx_purge_fetch_next_rec+0x248) [0xb74f5f78]
/usr/sbin/mysqld(row_purge_step+0x42) [0xb74d5a62]
/usr/sbin/mysqld(que_run_threads+0x5b0) [0xb74c1020]
/usr/sbin/mysqld(trx_purge+0x385) [0xb74f3d95]
/usr/sbin/mysqld(srv_master_thread+0xf80) [0xb74ea0a0]
/lib/tls/i686/cmov/libpthread.so.0(+0x596e) [0xb6f9896e]
/lib/tls/i686/cmov/libc.so.6(clone+0x5e) [0xb6d6098e]
The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.
130312 10:49:40 [Note] Plugin 'FEDERATED' is disabled.
130312 10:49:40 InnoDB: Initializing buffer pool, size = 8.0M
130312 10:49:40 InnoDB: Completed initialization of buffer pool
InnoDB: Log scan progressed past the checkpoint lsn 28 4054118181
130312 10:49:40 InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
InnoDB: Doing recovery: scanned up to log sequence number 28 4054761466
130312 10:49:42 InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percents: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99
InnoDB: Apply batch completed
InnoDB: Apply batch completed
130312 10:49:43 InnoDB: Started; log sequence number 28 4054761466
130312 10:49:43 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.1.67-0ubuntu0.10.04.1' socket: '/var/run/mysqld/mysqld.sock' port: 3306 (Ubuntu)
InnoDB: Error: trying to access page number 1303173499 in space 0,
InnoDB: space name ./ibdata1,
InnoDB: which is outside the tablespace bounds.
InnoDB: Byte offset 0, len 16384, i/o type 10.
InnoDB: If you get this error at mysqld startup, please check that
InnoDB: your my.cnf matches the ibdata files that you have in the
InnoDB: MySQL server.
130312 10:49:44 InnoDB: Assertion failure in thread 2949983088 in file ../../../storage/innobase/fil/fil0fil.c line 4135
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.1/...-recovery.html
InnoDB: about forcing recovery.
05:19:44 UTC - mysqld got signal 6 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help
diagnose the problem, but since we have already crashed,
something is definitely wrong and this may fail.

key_buffer_size=16777216
read_buffer_size=131072
max_used_connections=0
max_threads=151
thread_count=0
connection_count=0
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 345939 K bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0x0
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 0 thread_stack 0x30000
/usr/sbin/mysqld(my_print_stacktrace+0x2d) [0xb759c12d]
/usr/sbin/mysqld(handle_fatal_signal+0x4a4) [0xb73806b4]
[0xb6fc5400]
/lib/tls/i686/cmov/libc.so.6(abort+0x182) [0xb6cc4a82]
/usr/sbin/mysqld(fil_io+0x48c) [0xb7480e6c]
/usr/sbin/mysqld(+0x48180a) [0xb746480a]
/usr/sbin/mysqld(buf_read_page+0x27a) [0xb74653ea]
/usr/sbin/mysqld(buf_page_get_gen+0x349) [0xb745e6e9]
/usr/sbin/mysqld(+0x5154e0) [0xb74f84e0]
/usr/sbin/mysqld(+0x515c1d) [0xb74f8c1d]
/usr/sbin/mysqld(trx_purge_fetch_next_rec+0x248) [0xb74f9f78]
/usr/sbin/mysqld(row_purge_step+0x42) [0xb74d9a62]
/usr/sbin/mysqld(que_run_threads+0x5b0) [0xb74c5020]
/usr/sbin/mysqld(trx_purge+0x385) [0xb74f7d95]
/usr/sbin/mysqld(srv_master_thread+0xf80) [0xb74ee0a0]
/lib/tls/i686/cmov/libpthread.so.0(+0x596e) [0xb6f9c96e]
/lib/tls/i686/cmov/libc.so.6(clone+0x5e) [0xb6d6498e]
The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.
130312 10:49:46 [Note] Plugin 'FEDERATED' is disabled.
130312 10:49:46 InnoDB: Initializing buffer pool, size = 8.0M
130312 10:49:46 InnoDB: Completed initialization of buffer pool
InnoDB: Log scan progressed past the checkpoint lsn 28 4054118181
130312 10:49:46 InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
InnoDB: Doing recovery: scanned up to log sequence number 28 4054761466
130312 10:49:46 InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percents: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99
InnoDB: Apply batch completed
130312 10:49:46 InnoDB: Started; log sequence number 28 4054761466
130312 10:49:46 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.1.67-0ubuntu0.10.04.1' socket: '/var/run/mysqld/mysqld.sock' port: 3306 (Ubuntu)
InnoDB: Error: trying to access page number 1303173499 in space 0,
InnoDB: space name ./ibdata1,
InnoDB: which is outside the tablespace bounds.
InnoDB: Byte offset 0, len 16384, i/o type 10.
InnoDB: If you get this error at mysqld startup, please check that
InnoDB: your my.cnf matches the ibdata files that you have in the
InnoDB: MySQL server.
130312 10:49:47 InnoDB: Assertion failure in thread 2950327152 in file ../../../storage/innobase/fil/fil0fil.c line 4135
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.1/...-recovery.html
InnoDB: about forcing recovery.
05:19:47 UTC - mysqld got signal 6 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help
diagnose the problem, but since we have already crashed,
something is definitely wrong and this may fail.

key_buffer_size=16777216
read_buffer_size=131072
max_used_connections=0
max_threads=151
thread_count=0
connection_count=0
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 345939 K bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0x0
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 0 thread_stack 0x30000
/usr/sbin/mysqld(my_print_stacktrace+0x2d) [0xb75f012d]
/usr/sbin/mysqld(handle_fatal_signal+0x4a4) [0xb73d46b4]
[0xb7019400]
/lib/tls/i686/cmov/libc.so.6(abort+0x182) [0xb6d18a82]
/usr/sbin/mysqld(fil_io+0x48c) [0xb74d4e6c]
/usr/sbin/mysqld(+0x48180a) [0xb74b880a]
/usr/sbin/mysqld(buf_read_page+0x27a) [0xb74b93ea]
/usr/sbin/mysqld(buf_page_get_gen+0x349) [0xb74b26e9]
/usr/sbin/mysqld(+0x5154e0) [0xb754c4e0]
/usr/sbin/mysqld(+0x515c1d) [0xb754cc1d]
/usr/sbin/mysqld(trx_purge_fetch_next_rec+0x248) [0xb754df78]
/usr/sbin/mysqld(row_purge_step+0x42) [0xb752da62]
/usr/sbin/mysqld(que_run_threads+0x5b0) [0xb7519020]
/usr/sbin/mysqld(trx_purge+0x385) [0xb754bd95]
/usr/sbin/mysqld(srv_master_thread+0xf80) [0xb75420a0]
/lib/tls/i686/cmov/libpthread.so.0(+0x596e) [0xb6ff096e]
/lib/tls/i686/cmov/libc.so.6(clone+0x5e) [0xb6db898e]
The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.
^C

Have you gone through any of these links
http://bugs.mysql.com/bug.php?id=418
http://bugs.mysql.com/bug.php?id=56245
may be helpful...

I don't have much experience on mysql, so i din'd understand very much from your links
Can u please briefly explain how to resolve.

Thanks
Suresh

Post #6 gives detailed and exact instructions how to fix this issue.

kirukan is merely asking if you've searched through the mysql bug system.

All 3 links should be bookmarked and read slowly.