Question :
We have a mysql database running well and suddenly it stops and below are the snippet of the log file. What is the best remedy in this scenario?
130202 1:08:59 [ERROR] Event Scheduler: [root@localhost][packets.ipByte_SecondAnalysis] Table has no partition for value 735266
130202 1:08:59 [Note] Event Scheduler: [root@localhost].[packets.ipByte_SecondAnalysis] event execution failed.
17:09:02 UTC - mysqld got signal 8 ;
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=8388608
read_buffer_size=131072
max_used_connections=2
max_threads=151
thread_count=3
connection_count=2
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 338489 K bytes of memory
Hope that's ok; if not, decrease some variables in the equation.
Thread pointer: 0x4223f70
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 = 7f6d2c794d98 thread_stack 0x40000
/usr/libexec/mysqld(my_print_stacktrace+0x2e)[0x78329e]
/usr/libexec/mysqld(handle_fatal_signal+0x493)[0x66e2c3]
/lib64/libpthread.so.0[0x3b4300f4a0]
/usr/libexec/mysqld(_ZN12ha_partition21min_rows_for_estimateEv+0x4c)[0x8efe8c]
/usr/libexec/mysqld(_ZN12ha_partition16records_in_rangeEjP12st_key_rangeS1_+0x35)[0x8efec5]
/usr/libexec/mysqld[0x71efc7]
/usr/libexec/mysqld[0x71f62f]
/usr/libexec/mysqld[0x71f8ab]
/usr/libexec/mysqld(_ZN10SQL_SELECT17test_quick_selectEP3THD6BitmapILj64EEyyb+0x1060)[0x727310]
/usr/libexec/mysqld[0x5b02f5]
/usr/libexec/mysqld(_ZN4JOIN8optimizeEv+0x4cd)[0x5b11fd]
/usr/libexec/mysqld(_Z12mysql_selectP3THDPPP4ItemP10TABLE_LISTjR4ListIS1_ES2_jP8st_orderSB_S2_SB_yP13select_resultP18st_select_lex_unitP13st_select_lex+0x105)[0x5b4b25]
/usr/libexec/mysqld(_Z13handle_selectP3THDP3LEXP13select_resultm+0x174)[0x5ba3e4]
/usr/libexec/mysqld[0x57ba37]
/usr/libexec/mysqld(_Z21mysql_execute_commandP3THD+0x304e)[0x581d5e]
/usr/libexec/mysqld(_Z11mysql_parseP3THDPcjP12Parser_state+0x19d)[0x58340d]
/usr/libexec/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcj+0x12f1)[0x584eb1]
/usr/libexec/mysqld(_Z24do_handle_one_connectionP3THD+0xd2)[0x613cd2]
/usr/libexec/mysqld(handle_one_connection+0x50)[0x613de0]
/lib64/libpthread.so.0[0x3b430077f1]
/lib64/libc.so.6(clone+0x6d)[0x3b42ce570d]
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (7f6cc8223980): is an invalid pointer
Connection ID (thread ID): 43
Status: 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.
130202 01:09:02 mysqld_safe Number of processes running now: 0
130202 01:09:02 mysqld_safe mysqld restarted
130202 1:09:02 [Warning] The syntax '--log' is deprecated and will be removed in a future release. Please use '--general-log'/'--general-log-file' instead.
130202 1:09:02 [Note] Plugin 'FEDERATED' is disabled.
130202 1:09:02 InnoDB: The InnoDB memory heap is disabled
130202 1:09:02 InnoDB: Mutexes and rw_locks use GCC atomic builtins
130202 1:09:02 InnoDB: Compressed tables use zlib 1.2.3
130202 1:09:02 InnoDB: Using Linux native AIO
130202 1:09:02 InnoDB: Initializing buffer pool, size = 1.0G
130202 1:09:02 InnoDB: Completed initialization of buffer pool
130202 1:09:02 InnoDB: highest supported file format is Barracuda.
InnoDB: Log scan progressed past the checkpoint lsn 178008262046
130202 1:09:02 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 178008589529
130202 1:09:02 InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percents: 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
130202 1:09:03 InnoDB: Waiting for the background threads to start
130202 1:09:04 InnoDB: 1.1.8 started; log sequence number 178008589529
/usr/libexec/mysqld: File '/var/log/allQuery.log' not found (Errcode: 13)
130202 1:09:04 [ERROR] Could not use /var/log/allQuery.log for logging (error 13). Turning logging off for the whole duration of the MySQL server process. To turn it on again: fix the cause, shutdown the MySQL server and restart it.
130202 1:09:04 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
130202 1:09:04 [Note] - '0.0.0.0' resolves to '0.0.0.0';
130202 1:09:04 [Note] Server socket created on IP: '0.0.0.0'.
130202 1:09:04 [ERROR] Missing system table mysql.proxies_priv; please run mysql_upgrade to create it
130202 1:09:04 [ERROR] Native table 'performance_schema'.'events_waits_current' has the wrong structure
130202 1:09:04 [ERROR] Native table 'performance_schema'.'events_waits_history' has the wrong structure
130202 1:09:04 [ERROR] Native table 'performance_schema'.'events_waits_history_long' has the wrong structure
130202 1:09:04 [ERROR] Native table 'performance_schema'.'setup_consumers' has the wrong structure
130202 1:09:04 [ERROR] Native table 'performance_schema'.'setup_instruments' has the wrong structure
130202 1:09:04 [ERROR] Native table 'performance_schema'.'setup_timers' has the wrong structure
130202 1:09:04 [ERROR] Native table 'performance_schema'.'performance_timers' has the wrong structure
130202 1:09:04 [ERROR] Native table 'performance_schema'.'threads' has the wrong structure
130202 1:09:04 [ERROR] Native table 'performance_schema'.'events_waits_summary_by_thread_by_event_name' has the wrong structure
130202 1:09:04 [ERROR] Native table 'performance_schema'.'events_waits_summary_by_instance' has the wrong structure
130202 1:09:04 [ERROR] Native table 'performance_schema'.'events_waits_summary_global_by_event_name' has the wrong structure
130202 1:09:04 [ERROR] Native table 'performance_schema'.'file_summary_by_event_name' has the wrong structure
130202 1:09:04 [ERROR] Native table 'performance_schema'.'file_summary_by_instance' has the wrong structure
130202 1:09:04 [ERROR] Native table 'performance_schema'.'mutex_instances' has the wrong structure
130202 1:09:04 [ERROR] Native table 'performance_schema'.'rwlock_instances' has the wrong structure
130202 1:09:04 [ERROR] Native table 'performance_schema'.'cond_instances' has the wrong structure
130202 1:09:04 [ERROR] Native table 'performance_schema'.'file_instances' has the wrong structure
130202 1:09:04 [Note] Event Scheduler: Loaded 2 events
130202 1:09:04 [Note] /usr/libexec/mysqld: ready for connections.
Answer :
The first hit when you google “signal 8” mysql is Bug #65663 titled “Signal 8 When Selecting with Range Greater than Last Partition” … which is suspiciously reminiscent of your first log entry “Table has no partition for value 735266.”
At the bottom of that page is a reference to 5.5.26, which was never released, but a couple of more clicks and you’re looking at the Release Notes for MySQL 5.5.27 where you see a bugfix mentioned:
Partitioning: Insertion of an out-of-range value into a partitioned table was not handled correctly in all cases. This is a regression that first appeared in MySQL 5.5.23
You stopped pasting log entries right before the line that would have mentioned the version of MySQL Server that you’re running. It looks like 5.5.xx, and if that “xx” is less than 27, you may want to upgrade it to the latest version within the same major release family to eliminate that particular bug.
One way or another, however, the first issue is that your partitioning on one of the tables used by the packets.ipByte_SecondAnalysis scheduled event seems incorrect. If indeed the referenced bug and bugfix are related to your issue, then fixing the partitioning or avoiding the attempt to insert invalid data will avoid doing the thing that allows the bug to be triggered.
From looking at your log file, you may also want to — at some point — follow the log’s suggestion about mysql_upgrade, which doesn’t look like it has been used consistently over the life of this server.
[ERROR] Missing system table mysql.proxies_priv; please run mysql_upgrade to create it
That message, and the performance_schema messages that follow it, are related to this procedure not having been followed… however, this should be completely unrelated to the crash, and tinkering with the server unnecessarily while it’s unstable should be avoided… so that’s one for later.