Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I restarted my slave to change configuration settings to skip reverse hostname lookup on connecting and to enable the slow query log.

I edited /etc/my.cnf making only these changes, then restarted mysqld with /etc/init.d/mysql restart

All appeared to be well but when I connect to msyqld remotely or locally though it connects okay a slight problem is that mysqld crashes whenever you try to issue any kind of statement.

The client looks like:

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 3
Server version: 5.1.31-1ubuntu2-log

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

mysql> show tables;

ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    1
Current database: mydb

ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
ERROR 2003 (HY000): Can't connect to MySQL server on 'xx.xx.xx.xx' (61)
ERROR: 
Can't connect to the server

ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
ERROR 2003 (HY000): Can't connect to MySQL server on 'xx.xx.xx.xx' (61)
ERROR: 
Can't connect to the server

ERROR 2006 (HY000): MySQL server has gone away
Bus error

The mysqld error log looks like:

101210 16:35:51  InnoDB: Error: (1500) Couldn't read the MAX(job_id) autoinc value from the index (PRIMARY).
101210 16:35:51  InnoDB: Assertion failure in thread 140245598570832 in file handler/ha_innodb.cc line 2595
InnoDB: Failing assertion: error == DB_SUCCESS
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/en/forcing-recovery.html
InnoDB: about forcing recovery.
101210 16:35:51 - 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=3
max_threads=600
threads_connected=3
It is possible that mysqld could use up to 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 1328077 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd: 0x18209220
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 = 0x7f8d791580d0 thread_stack 0x20000
/usr/sbin/mysqld(my_print_stacktrace+0x29) [0x8b4f89]
/usr/sbin/mysqld(handle_segfault+0x383) [0x5f8f03]
/lib/libpthread.so.0 [0x7f902a76a080]
/lib/libc.so.6(gsignal+0x35) [0x7f90291f8fb5]
/lib/libc.so.6(abort+0x183) [0x7f90291fabc3]
/usr/sbin/mysqld(ha_innobase::open(char const*, int, unsigned int)+0x41b) [0x781f4b]
/usr/sbin/mysqld(handler::ha_open(st_table*, char const*, int, int)+0x3f) [0x6db00f]
/usr/sbin/mysqld(open_table_from_share(THD*, st_table_share*, char const*, unsigned int, unsigned int, unsigned int, st_table*, bool)+0x57a) [0x64760a]
/usr/sbin/mysqld [0x63f281]
/usr/sbin/mysqld(open_table(THD*, TABLE_LIST*, st_mem_root*, bool*, unsigned int)+0x626) [0x641e16]
/usr/sbin/mysqld(open_tables(THD*, TABLE_LIST**, unsigned int*, unsigned int)+0x5db) [0x6429cb]
/usr/sbin/mysqld(open_normal_and_derived_tables(THD*, TABLE_LIST*, unsigned int)+0x1e) [0x642b0e]
/usr/sbin/mysqld(mysqld_list_fields(THD*, TABLE_LIST*, char const*)+0x22) [0x70b292]
/usr/sbin/mysqld(dispatch_command(enum_server_command, THD*, char*, unsigned int)+0x146d) [0x60dc1d]
/usr/sbin/mysqld(do_command(THD*)+0xe8) [0x60dda8]
/usr/sbin/mysqld(handle_one_connection+0x226) [0x601426]
/lib/libpthread.so.0 [0x7f902a7623ba]
/lib/libc.so.6(clone+0x6d) [0x7f90292abfcd]
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0x18213c70 = 
thd->thread_id=3
thd->killed=NOT_KILLED
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.
101210 16:35:51 mysqld_safe Number of processes running now: 0
101210 16:35:51 mysqld_safe mysqld restarted
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
101210 16:35:54  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...
101210 16:35:56  InnoDB: Started; log sequence number 456 143528628
101210 16:35:56 [Warning] 'user' entry 'root@PSDB102' ignored in --skip-name-resolve mode.
101210 16:35:56 [Warning] Neither --relay-log nor --relay-log-index were used; so replication may break when this MySQL server acts as a slave and has his hostname changed!! Please use '--relay-log=mysqld-relay-bin' to avoid this problem.
101210 16:35:56 [Note] Event Scheduler: Loaded 0 events
101210 16:35:56 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.1.31-1ubuntu2-log'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  (Ubuntu)
101210 16:36:11  InnoDB: Error: (1500) Couldn't read the MAX(job_id) autoinc value from the index (PRIMARY).
101210 16:36:11  InnoDB: Assertion failure in thread 139955151501648 in file handler/ha_innodb.cc line 2595
InnoDB: Failing assertion: error == DB_SUCCESS
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/en/forcing-recovery.html
InnoDB: about forcing recovery.
101210 16:36:11 - 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=1
max_threads=600
threads_connected=1
It is possible that mysqld could use up to 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 1328077 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd: 0x18588720
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 = 0x7f49d916f0d0 thread_stack 0x20000
/usr/sbin/mysqld(my_print_stacktrace+0x29) [0x8b4f89]
/usr/sbin/mysqld(handle_segfault+0x383) [0x5f8f03]
/lib/libpthread.so.0 [0x7f4c8a73f080]
/lib/libc.so.6(gsignal+0x35) [0x7f4c891cdfb5]
/lib/libc.so.6(abort+0x183) [0x7f4c891cfbc3]
/usr/sbin/mysqld(ha_innobase::open(char const*, int, unsigned int)+0x41b) [0x781f4b]
/usr/sbin/mysqld(handler::ha_open(st_table*, char const*, int, int)+0x3f) [0x6db00f]
/usr/sbin/mysqld(open_table_from_share(THD*, st_table_share*, char const*, unsigned int, unsigned int, unsigned int, st_table*, bool)+0x57a) [0x64760a]
/usr/sbin/mysqld [0x63f281]
/usr/sbin/mysqld(open_table(THD*, TABLE_LIST*, st_mem_root*, bool*, unsigned int)+0x626) [0x641e16]
/usr/sbin/mysqld(open_tables(THD*, TABLE_LIST**, unsigned int*, unsigned int)+0x5db) [0x6429cb]
/usr/sbin/mysqld(open_normal_and_derived_tables(THD*, TABLE_LIST*, unsigned int)+0x1e) [0x642b0e]
/usr/sbin/mysqld(mysqld_list_fields(THD*, TABLE_LIST*, char const*)+0x22) [0x70b292]
/usr/sbin/mysqld(dispatch_command(enum_server_command, THD*, char*, unsigned int)+0x146d) [0x60dc1d]
/usr/sbin/mysqld(do_command(THD*)+0xe8) [0x60dda8]
/usr/sbin/mysqld(handle_one_connection+0x226) [0x601426]
/lib/libpthread.so.0 [0x7f4c8a7373ba]
/lib/libc.so.6(clone+0x6d) [0x7f4c89280fcd]
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0x18599950 = 
thd->thread_id=1
thd->killed=NOT_KILLED
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.
101210 16:36:11 mysqld_safe Number of processes running now: 0
101210 16:36:11 mysqld_safe mysqld restarted

The config is

[mysqld_safe]
socket          = /var/run/mysqld/mysqld.sock
nice            = 0

[mysqld]
innodb_file_per_table
innodb_buffer_pool_size=10G
innodb_log_buffer_size=4M
innodb_flush_log_at_trx_commit=2
innodb_thread_concurrency=8
skip-slave-start
server-id=3

#
# * IMPORTANT
#   If you make changes to these settings and your system uses apparmor, you may
#   also need to also adjust /etc/apparmor.d/usr.sbin.mysqld.
#

user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /DB2/mysql
tmpdir          = /tmp
skip-external-locking
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
#bind-address           = 127.0.0.1
#
# * Fine Tuning
#
key_buffer              = 16M
max_allowed_packet      = 16M
thread_stack            = 128K
thread_cache_size       = 8
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover          = BACKUP
max_connections         = 600
#table_cache            = 64
#thread_concurrency     = 10
#
# * Query Cache Configuration
#
query_cache_limit       = 1M
query_cache_size        = 32M
#

skip-federated

slow-query-log
skip-name-resolve

Update: I followed the instructions as per http://dev.mysql.com/doc/refman/5.1/en/forcing-innodb-recovery.html and set innodb_force_recovery = 4 and the logs are showing a different error but the behavior is still the same:

101210 19:14:15 mysqld_safe mysqld restarted
101210 19:14:19  InnoDB: Started; log sequence number 456 143528628
InnoDB: !!! innodb_force_recovery is set to 4 !!!
101210 19:14:19 [Warning] 'user' entry 'root@PSDB102' ignored in --skip-name-resolve mode.
101210 19:14:19 [Warning] Neither --relay-log nor --relay-log-index were used; so replication may break when this MySQL server acts as a slave and has his hostname changed!! Please use '--relay-log=mysqld-relay-bin' to avoid this problem.
101210 19:14:19 [Note] Event Scheduler: Loaded 0 events
101210 19:14:19 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.1.31-1ubuntu2-log'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  (Ubuntu)
101210 19:14:32  InnoDB: error: space object of table mydb/__twitter_friend,
InnoDB: space id 1602 did not exist in memory. Retrying an open.
101210 19:14:32  InnoDB: error: space object of table mydb/access_request,
InnoDB: space id 1318 did not exist in memory. Retrying an open.
101210 19:14:32  InnoDB: error: space object of table mydb/activity,
InnoDB: space id 1595 did not exist in memory. Retrying an open.
101210 19:14:32 - mysqld got signal 11 ;
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=1
max_threads=600
threads_connected=1
It is possible that mysqld could use up to 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 1328077 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd: 0x1753c070
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 = 0x7f7a0b5800d0 thread_stack 0x20000
/usr/sbin/mysqld(my_print_stacktrace+0x29) [0x8b4f89]
/usr/sbin/mysqld(handle_segfault+0x383) [0x5f8f03]
/lib/libpthread.so.0 [0x7f7cbc350080]
/usr/sbin/mysqld(ha_innobase::innobase_get_index(unsigned int)+0x46) [0x77c516]
/usr/sbin/mysqld(ha_innobase::innobase_initialize_autoinc()+0x40) [0x77c640]
/usr/sbin/mysqld(ha_innobase::open(char const*, int, unsigned int)+0x3f3) [0x781f23]
/usr/sbin/mysqld(handler::ha_open(st_table*, char const*, int, int)+0x3f) [0x6db00f]
/usr/sbin/mysqld(open_table_from_share(THD*, st_table_share*, char const*, unsigned int, unsigned int, unsigned int, st_table*, bool)+0x57a) [0x64760a]
/usr/sbin/mysqld [0x63f281]
/usr/sbin/mysqld(open_table(THD*, TABLE_LIST*, st_mem_root*, bool*, unsigned int)+0x626) [0x641e16]
/usr/sbin/mysqld(open_tables(THD*, TABLE_LIST**, unsigned int*, unsigned int)+0x5db) [0x6429cb]
/usr/sbin/mysqld(open_normal_and_derived_tables(THD*, TABLE_LIST*, unsigned int)+0x1e) [0x642b0e]
/usr/sbin/mysqld(mysqld_list_fields(THD*, TABLE_LIST*, char const*)+0x22) [0x70b292]
/usr/sbin/mysqld(dispatch_command(enum_server_command, THD*, char*, unsigned int)+0x146d) [0x60dc1d]
/usr/sbin/mysqld(do_command(THD*)+0xe8) [0x60dda8]
/usr/sbin/mysqld(handle_one_connection+0x226) [0x601426]
/lib/libpthread.so.0 [0x7f7cbc3483ba]
/lib/libc.so.6(clone+0x6d) [0x7f7cbae91fcd]
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0x1754d690 =
thd->thread_id=1
thd->killed=NOT_KILLED
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.
share|improve this question
    
is /DB2/mysql out of space ? what is the block size on that partition ? –  silviud Dec 10 '10 at 17:55
    
95% space available. block size = 4096 –  ʞɔıu Dec 10 '10 at 18:22
    
can you point your server to a different data directory and start with an empty database ? - it can be changed into my.cnf –  silviud Dec 11 '10 at 2:22
    
Did you check if have any inode free on the data partition ? (df -i) –  golja Jul 1 '12 at 23:14
add comment

2 Answers

See here: http://bugs.mysql.com/bug.php?id=44030

Philip S

I had the same issue. I tried upgrading from 5.1.33 to 5.1.36, but mysqld would still crash whenever accessing the database.

Some details, most of it probably irrelevant, but maybe something will pop out: This is a brand new laptop, and I just started on a new project. MD Turion X2 Dual-Core Mobile RM-74 w/4GB RAM DB size on disk is 1.67GB 1GB of RAM is being used as a ramdisk Fresh install of 5.1.33 on MS Vista 64 using the WAMP project installer. A lot of ALTERs, as this is a dev environment. Adding columns, indexes, etc. Renaming columns as well. The bug cropped up between coding sessions rather than during a coding session. All db schema was created using MySQL Administrator, and all data manipulation done using php and mysqli. Everything wrt MySQL is brand new. I'm sorry, I'm unable to provide data files.

Anyway, as the upgrade didn't work to allow me to access the data, and a lot of work occurred since my last backup, I wanted to restore/retrieve as much of the DB schema as possible, I wasn't concerned with data. I actually did retrieve most of the data as well. While I'm sure the process can be optimized, what worked for me is: C:\wamp\bin\mysql\mysql5.1.36\bin>mysqlshow.exe -u root then for each table C:\wamp\bin\mysql\mysql5.1.36\bin>mysqlshow.exe -u root until I got the error mysqlshow.exe: Cannot list columns in db: , table: : Lost connection to MySQL server during query then I restarted mysqld, started the MySQL console, and \u drop table for the table which caused the crash. After that, everything was back to normal other than the loss of the one small table which I should be able to recreate easily.

share|improve this answer
    
Seems pretty similar but not quite exactly the same. I tried his technique but mysqlshow seems to not show table contents but not crash the server unless I try to get info on a particular column. I also am not able to do any sql at all, so I can't drop tables. –  ʞɔıu Dec 10 '10 at 19:41
    
Well the main error is "Couldn't read the MAX(job_id) autoinc value from the index (PRIMARY)", judging from that bug report it sounds related to a table –  brent Dec 10 '10 at 19:43
    
seems to blow up almost no matter what I do and the table or schema doesn't seem to matter and when I set innodb_force_recovery I see completely different apparently fatal errors –  ʞɔıu Dec 10 '10 at 20:06
add comment

This problem may occur if you've a low prefered value for 'wait_timeout' mysqladmin variables | grep wait_timeout

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.