Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 0 down vote favorite

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.
share|improve this question

1 Answer

up vote 3 down vote

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.

share|improve this answer
ok I have now build back the partition and this was the cause of it.But this message [ERROR] Missing system table mysql.proxies_priv; please run mysql_upgrade to create it is still there so you suggest to upgrade is it? – newbie14 Feb 3 at 4:47
Mostly necause it has not been consistently done in the past on your server, I wouldn't go so far as to say "do it" -- instead, I would suggest you consider it, after you follow the link to mysql_upgrade and understand exactly what it does (including locking tables), and then consider when might be a good time to run it after being certain that all of your data is backed up. It's unlikely, but there is a possibility that mysql_upgrade could encounter latent structural corruption that would shut down your server and make it difficult to restart, resulting in downtime or possible data loss. – Michael - sqlbot Feb 3 at 20:11
I am on centos machine and normally they discourage for upgrade fear of this sort of problems. – newbie14 Feb 5 at 3:38
I cannot imagine that to be accurate. Who are "they" and what sources would you cite for this? – Michael - sqlbot Feb 5 at 13:11
I mean the centos version itself if you see are mostly using some older version of php and mysql this I read due to stability. – newbie14 Feb 6 at 14:04

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.