Description:
I am having a problem with MySQL crashing after mysqldump runs on all tables. Most of the tables are berkley format with a few myisam tables. I have been having irregular server crashes for about a month with no luck in reproduction. I am running the server with the default configuration with inodb disabled. I made significant changes to my tomcat application on Saturday and now seem to be having a crash 60-75 minutes after mysqldump runs on the server. mysqldump runs at 4:30am EST and the crash Sunday morning occured at 05:18:13 and the crash this morning occured at 05:49:48. I run mysqldump as follows mysqldump -A -u username -ppassword -e > backup.sql. The crashes are on queries that run hundreds of times a day. Here are the crash reports for these two crashes:
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=8388600
read_buffer_size=131072
max_used_connections=6
max_connections=100
threads_connected=2
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 225791 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.
thd=0x8601cf8
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...
Cannot determine thread, fp=0x417928bc, backtrace may not be correct.
Stack range sanity check OK, backtrace follows:
0x80d9be4
0x4003c7c8
0x40b13920
0x81d4fbe
0x81ca72d
0x81c8241
0x81a2b44
0x819e9a8
0x81361e3
0x812b672
0x8107bf9
0x8106f76
0x8106d13
0x80ff340
0x80fd7d6
0x80e4a0a
0x80e8635
0x80e3b83
0x80e35ce
0x80e2db8
0x40037332
0x420de867
New value of fp=(nil) failed sanity check, terminating stack trace!
Please read http://www.mysql.com/doc/en/Using_stack_trace.html and follow instructions on how to resolve the stack trace. Resolved
stack trace is much more helpful in diagnosing the problem, so please do
resolve it
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0x85a47c8 = SELECT relProdCode, collectionName FROM relatedProd ORDER BY collectionName
thd->thread_id=9
The manual page at http://www.mysql.com/doc/en/Crashing.html contains
information that should help you find out what is causing the crash.
Number of processes running now: 0
031109 05:18:13 mysqld restarted
/usr/sbin/mysqld-max: ready for connections.
Version: '4.0.16-Max' socket: '/var/lib/mysql/mysql.sock' port: 3306
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=8388600
read_buffer_size=131072
max_used_connections=9
max_connections=100
threads_connected=2
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 225791 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.
thd=0x84b9c10
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...
Cannot determine thread, fp=0x4173076c, backtrace may not be correct.
Stack range sanity check OK, backtrace follows:
0x80d9be4
0x4003c7c8
0x40b1feb8
0x81d4fbe
0x81ca72d
0x81c8241
0x81a2b44
0x819e9a8
0x81361e3
0x812b672
0x8107bf9
0x8106f76
0x8106d13
0x80ff340
0x815d030
0x80fd798
0x80e4a0a
0x80e8635
0x80e3b83
0x80e35ce
0x80e2db8
0x40037332
0x420de867
New value of fp=(nil) failed sanity check, terminating stack trace!
Please read http://www.mysql.com/doc/en/Using_stack_trace.html and follow instructions on how to resolve the stack trace. Resolved
stack trace is much more helpful in diagnosing the problem, so please do
resolve it
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0x84ba548 = (SELECT productSets.id AS id, name, fileName, productSets.imageSetID FROM productSets, images WHERE relProdCode="205" AND images.imageSetID=productSets.imageSetID AND images.number=2 AND active=1 AND important=1 AND productSets.id!=5691 ORDER BY shortDescription DESC) UNION (SELECT productSets.id AS id, name, fileName, productSets.imageSetID FROM productSets, images WHERE relProdCode="205" AND images.imageSetID=productSets.imageSetID AND images.number=2 AND active=1 AND important=0 AND productSets.id!=5691 ORDER BY shortDescription DESC) UNION (SELECT products.id AS id, name, fileName, products.imageSetID FROM products, images WHERE relProdCode="205" AND images.imageSetID=products.imageSetID AND images.number=2 AND active=1 AND displaySearch=1 AND products.id!=5691 ORDER BY shortDescription DESC) LIMIT 6
thd->thread_id=1
The manual page at http://www.mysql.com/doc/en/Crashing.html contains
information that should help you find out what is causing the crash.
Stack trace for the first one:
0x80d9be4 mysqld_extend_show_tables__FP3THDPCcT1 + 840
0x4003c7c8 _end + 935102444
0x40b13920 _end + 946469188
0x81d4fbe fseg_alloc_free_page_general + 698
0x81ca72d recv_recover_page + 4453
0x81c8241 recv_synchronize_groups + 809
0x81a2b44 trx_undo_truncate_start + 560
0x819e9a8 trx_commit_for_mysql + 268
0x81361e3 row_purge + 339
0x812b672 row_ins_scan_sec_index_for_duplicate + 1986
0x8107bf9 dict_foreign_parse_drop_constraints + 345
0x8106f76 dict_create_foreign_constraints_low + 7638
0x8106d13 dict_create_foreign_constraints_low + 7027
0x80ff340 dict_table_decrement_handle_count + 264
0x80fd7d6 dict_create_index_tree_step + 2334
0x80e4a0a init_relay_log_pos__FP17st_relay_log_infoPCcUxbPPCc + 206
0x80e8635 exec_relay_log_event__FP3THDP17st_relay_log_info + 85
0x80e3b83 insert_into_free_memory_sorted_list__11Query_cacheP17Query_cache_blockPP17Query_cache_block + 103
0x80e35ce insert_table__11Query_cacheUiPcP23Query_cache_block_tableUi + 186
0x80e2db8 flush_cache__11Query_cache + 40
0x40037332 _end + 935080790
0x420de867 _end + 969320587
And the second one:
0x80d9be4 mysqld_extend_show_tables__FP3THDPCcT1 + 840
0x4003c7c8 _end + 935102444
0x40b1feb8 _end + 946519772
0x81d4fbe fseg_alloc_free_page_general + 698
0x81ca72d recv_recover_page + 4453
0x81c8241 recv_synchronize_groups + 809
0x81a2b44 trx_undo_truncate_start + 560
0x819e9a8 trx_commit_for_mysql + 268
0x81361e3 row_purge + 339
0x812b672 row_ins_scan_sec_index_for_duplicate + 1986
0x8107bf9 dict_foreign_parse_drop_constraints + 345
0x8106f76 dict_create_foreign_constraints_low + 7638
0x8106d13 dict_create_foreign_constraints_low + 7027
0x80ff340 dict_table_decrement_handle_count + 264
0x815d030 btr_attach_half_pages + 816
0x80fd798 dict_create_index_tree_step + 2272
0x80e4a0a init_relay_log_pos__FP17st_relay_log_infoPCcUxbPPCc + 206
0x80e8635 exec_relay_log_event__FP3THDP17st_relay_log_info + 85
0x80e3b83 insert_into_free_memory_sorted_list__11Query_cacheP17Query_cache_blockPP17Query_cache_block + 103
0x80e35ce insert_table__11Query_cacheUiPcP23Query_cache_block_tableUi + 186
0x80e2db8 flush_cache__11Query_cache + 40
0x40037332 _end + 935080790
0x420de867 _end + 969320587
As I stated earlier these queries will run just fine all day long. The first one is used to build the furniture collections on http://www.greatpricedfurniture.com/sitemap.html and the second one is used to build the additional products section at the bottom of this page http://www.greatpricedfurniture.com/products/5691.html. All of the stack traces that I perform look almost identical to the two above and I am unable to repeat them. I am planning on switching over to all MyIsam tables and changing all of my transaction code over to Atomic Operations. Any idea if this will be more stable.
Thanks
James Fredley
How to repeat:
I am unable to repeat. Problem is very random. Server will process 750,000 + queries some times before crashing and some time less that 200,000. There appears to be a pattern appearing now with the mysqldump as stated above.
