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.
