Announcement

Announcement Module
Collapse
No announcement yet.

MySQL DB Instance Crashes with Assertion failure

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • MySQL DB Instance Crashes with Assertion failure

    Hi,

    The MySQL database instance is crashing continuosuly. After the crash, MySQL tries to recover and crashes again & again. This has been seen in all the Percona-Releases across and I think that the bug is tied with the innodb_plugin. The mysqld.log is

    InnoDB: Error: trying to access page number 66286848 in space 33,InnoDB: space name ./mtrak/billing_records.ibd,InnoDB: which is outside the tablespace bounds.InnoDB: Byte offset 0, len 16384, i/o type 10.InnoDB: If you get this error at mysqld startup, please check thatInnoDB: your my.cnf matches the ibdata files that you have in theInnoDB: MySQL server.101216 19:15:48 InnoDB: Assertion failure in thread 1484794176 in file fil/fil0fil.c line 4686InnoDB: 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, evenInnoDB: immediately after the mysqld startup, there may beInnoDB: corruption in the InnoDB tablespace. Please refer toInnoDB: http://dev.mysql.com/doc/refman/5.1/en/forcing-recovery.htmlInnoDB: about forcing recovery.101216 19:15:48 - mysqld got signal 6 ;This could be because you hit a bug. It is also possible that this binaryor 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 diagnosethe problem, but since we have already crashed, something is definitely wrongand this may fail.key_buffer_size=33554432read_buffer_size=2097 152max_used_connections=322max_threads=500threads_ connected=282It is possible that mysqld could use up tokey_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 5158166 Kbytes of memoryHope that's ok; if not, decrease some variables in the equation.thd: 0x0Attempting backtrace. You can use the following information to find outwhere mysqld died. If you see no messages after this, something wentterribly wrong...stack_bottom = (nil) thread_stack 0x30000/usr/sbin/mysqld(my_print_stacktrace+0x2e)[0x86892e]/usr/sbin/mysqld(handle_segfault+0x322)[0x5b0f72]/lib64/libpthread.so.0[0x37ca20eb10]/lib64/libc.so.6(gsignal+0x35)[0x37c9630265]/lib64/libc.so.6(abort+0x110)[0x37c9631d10]/usr/sbin/mysqld[0x7fb531]/usr/sbin/mysqld[0x7df66e]/usr/sbin/mysqld[0x7e0364]/usr/sbin/mysqld[0x7d6f2e]/usr/sbin/mysqld[0x80dc9c]/usr/sbin/mysqld[0x804eea]/usr/sbin/mysqld[0x80502c]/usr/sbin/mysqld[0x7b923a]/usr/sbin/mysqld[0x7bc72d]/usr/sbin/mysqld[0x7c5857]/usr/sbin/mysqld[0x77f182]/usr/sbin/mysqld[0x78009c]/usr/sbin/mysqld[0x76b2d6]/usr/sbin/mysqld[0x79b7c8]/usr/sbin/mysqld[0x791c70]/lib64/libpthread.so.0[0x37ca20673d]/lib64/libc.so.6(clone+0x6d)[0x37c96d3d1d]The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html containsinformation that should help you find out what is causing the crash.The "--memlock" argument, which was enabled, uses system calls that areunreliable and unstable on some operating systems and operating-systemversions (notably, some versions of Linux). This crash could be due to useof those buggy OS calls. You should consider whether you really need the"--memlock" parameter and/or consult the OS distributer about "mlockall"bugs.101216 19:15:48 mysqld_safe Number of processes running now: 0101216 19:15:48 mysqld_safe mysqld restarted101216 19:15:49 [Note] Plugin 'FEDERATED' is disabled.InnoDB: The InnoDB memory heap is disabledInnoDB: Mutexes and rw_locks use GCC atomic builtinsInnoDB: Compressed tables use zlib 1.2.3101216 19:15:49 InnoDB: highest supported file format is Barracuda.InnoDB: Log scan progressed past the checkpoint lsn 132553232602101216 19:15:49 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 doublewriteInnoDB: buffer...InnoDB: Doing recovery: scanned up to log sequence number 132558475264InnoDB: Doing recovery: scanned up to log sequence number 132563718144InnoDB: Doing recovery: scanned up to log sequence number 132568961024InnoDB: Doing recovery: scanned up to log sequence number 132574203904InnoDB: Doing recovery: scanned up to log sequence number 132579446784InnoDB: Doing recovery: scanned up to log sequence number 132584689664InnoDB: Doing recovery: scanned up to log sequence number 132589932544InnoDB: Doing recovery: scanned up to log sequence number 132595175424InnoDB: Doing recovery: scanned up to log sequence number 132600418304InnoDB: Doing recovery: scanned up to log sequence number 132605661184InnoDB: Doing recovery: scanned up to log sequence number 132610904064InnoDB: Doing recovery: scanned up to log sequence number 132616146944InnoDB: Doing recovery: scanned up to log sequence number 132621389824InnoDB: Doing recovery: scanned up to log sequence number 132626632704InnoDB: Doing recovery: scanned up to log sequence number 132631875584InnoDB: Doing recovery: scanned up to log sequence number 132637118464InnoDB: Doing recovery: scanned up to log sequence number 132642361344InnoDB: Doing recovery: scanned up to log sequence number 132647604224InnoDB: Doing recovery: scanned up to log sequence number 132652847104InnoDB: Doing recovery: scanned up to log sequence number 132658089984InnoDB: Doing recovery: scanned up to log sequence number 132663332864InnoDB: Doing recovery: scanned up to log sequence number 132668575744InnoDB: Doing recovery: scanned up to log sequence number 132673818624InnoDB: Doing recovery: scanned up to log sequence number 132679061504InnoDB: Doing recovery: scanned up to log sequence number 132684304384InnoDB: Doing recovery: scanned up to log sequence number 132689547264InnoDB: Doing recovery: scanned up to log sequence number 132694790144InnoDB: Doing recovery: scanned up to log sequence number 132700033024InnoDB: Doing recovery: scanned up to log sequence number 132705275904InnoDB: Doing recovery: scanned up to log sequence number 132710518784InnoDB: Doing recovery: scanned up to log sequence number 132715761664InnoDB: Doing recovery: scanned up to log sequence number 132721004544InnoDB: Doing recovery: scanned up to log sequence number 132726247424InnoDB: Doing recovery: scanned up to log sequence number 132731490304InnoDB: Doing recovery: scanned up to log sequence number 132736733184InnoDB: Doing recovery: scanned up to log sequence number 132741976064InnoDB: Doing recovery: scanned up to log sequence number 132747218944InnoDB: Doing recovery: scanned up to log sequence number 132752461824InnoDB: Doing recovery: scanned up to log sequence number 132757704704InnoDB: Doing recovery: scanned up to log sequence number 132762947584InnoDB: Doing recovery: scanned up to log sequence number 132768190464InnoDB: Doing recovery: scanned up to log sequence number 132773433344InnoDB: Doing recovery: scanned up to log sequence number 132778676224InnoDB: Doing recovery: scanned up to log sequence number 132783919104InnoDB: Doing recovery: scanned up to log sequence number 132789161984InnoDB: Doing recovery: scanned up to log sequence number 132794404864InnoDB: Doing recovery: scanned up to log sequence number 132799647744InnoDB: Doing recovery: scanned up to log sequence number 132804890624InnoDB: Doing recovery: scanned up to log sequence number 132810133504InnoDB: Doing recovery: scanned up to log sequence number 132815376384InnoDB: Doing recovery: scanned up to log sequence number 132820619264InnoDB: Doing recovery: scanned up to log sequence number 132825862144InnoDB: Doing recovery: scanned up to log sequence number 132826587440InnoDB: 1 transaction(s) which must be rolled back or cleaned upInnoDB: in total 8687 row operations to undoInnoDB: Trx id counter is 102C8C00101216 19:17:33 InnoDB: Starting an apply batch of log records to the database...InnoDB: Progress in percents: 0 1 2 3 4 5 6 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 99InnoDB: Apply batch completedInnoDB: Last MySQL binlog file position 0 34744609, file name /var/lib/mysql/binlogs/mtrak32-bin.001029InnoDB: Starting in background the rollback of uncommitted transactions101216 19:17:53 InnoDB: Rolling back trx with id 102C8F28, 1 rows to undo101216 19:17:53 Percona XtraDB (http://www.percona.com) 1.0.8-11.1 started; log sequence number 132830272196101216 19:17:53 [Note] Recovering after a crash using /var/lib/mysql/binlogs/mtrak32-bin101216 19:17:53 [Note] Starting crash recovery...101216 19:17:53 [Note] Crash recovery finished.InnoDB: Rolling back of trx id 102C8F28 completed101216 19:17:53 InnoDB: Rolling back trx with id 102C8F27, 1 rows to undoInnoDB: Rolling back of trx id 102C8F27 completed101216 19:17:53 InnoDB: Rolling back trx with id 102C8F26, 1 rows to undoInnoDB: Rolling back of trx id 102C8F26 completed101216 19:17:53 InnoDB: Rolling back trx with id 102C8F25, 1 rows to undoInnoDB: Rolling back of trx id 102C8F25 completed101216 19:17:57 InnoDB: Rolling back trx with id 102C8F24, 1 rows to undo101216 19:17:57 [Note] Event Scheduler: Loaded 0 events101216 19:17:57 [Note] /usr/sbin/mysqld: ready for connections.Version: '5.1.47-rel11.1-log' socket: '/var/lib/mysql/mysql.sock' port: 3306 Percona Server (GPL), 11.1, Revision 51InnoDB: Rolling back of trx id 102C8F24 completed101216 19:17:57 InnoDB: Rolling back trx with id 102C8F23, 1 rows to undoInnoDB: Rolling back of trx id 102C8F23 completed101216 19:17:57 InnoDB: Rolling back trx with id 102C8F22, 1 rows to undoInnoDB: Rolling back of trx id 102C8F22 completed101216 19:17:57 InnoDB: Rolling back trx with id 102C8F21, 1 rows to undoInnoDB: Rolling back of trx id 102C8F21 completed101216 19:17:57 InnoDB: Rolling back trx with id 102C8F20, 1 rows to undoInnoDB: Rolling back of trx id 102C8F20 completed101216 19:17:57 InnoDB: Rolling back trx with id 102C8F1F, 1 rows to undoInnoDB: Rolling back of trx id 102C8F1F completed101216 19:17:57 InnoDB: Rolling back trx with id 102C8F1E, 1 rows to undoInnoDB: Rolling back of trx id 102C8F1E completed101216 19:17:57 InnoDB: Rolling back trx with id 102C8F1D, 1 rows to undoInnoDB: Rolling back of trx id 102C8F1D completed101216 19:17:57 InnoDB: Rolling back trx with id 102C8F1C, 1 rows to undoInnoDB: Rolling back of trx id 102C8F1C completed101216 19:17:57 InnoDB: Rolling back trx with id 102C8F1B, 1 rows to undoInnoDB: Rolling back of trx id 102C8F1B completed101216 19:17:57 InnoDB: Rolling back trx with id 102C8F1A, 1 rows to undoInnoDB: Rolling back of trx id 102C8F1A completed101216 19:17:57 InnoDB: Rollback of non-prepared transactions completedInnoDB: Error: trying to access page number 66286848 in space 33,InnoDB: space name ./mtrak/billing_records.ibd,InnoDB: which is outside the tablespace bounds.InnoDB: Byte offset 0, len 16384, i/o type 10.InnoDB: If you get this error at mysqld startup, please check thatInnoDB: your my.cnf matches the ibdata files that you have in theInnoDB: MySQL server.101216 19:17:58 InnoDB: Assertion failure in thread 1486006592 in file fil/fil0fil.c line 4686InnoDB: 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, evenInnoDB: immediately after the mysqld startup, there may beInnoDB: corruption in the InnoDB tablespace. Please refer toInnoDB: http://dev.mysql.com/doc/refman/5.1/en/forcing-recovery.htmlInnoDB: about forcing recovery.101216 19:17:58 - mysqld got signal 6 ;This could be because you hit a bug. It is also possible that this binaryor 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 diagnosethe problem, but since we have already crashed, something is definitely wrongand this may fail.key_buffer_size=33554432read_buffer_size=2097 152max_used_connections=21max_threads=500threads_c onnected=21It is possible that mysqld could use up tokey_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 5158166 Kbytes of memoryHope that's ok; if not, decrease some variables in the equation.thd: 0x0Attempting backtrace. You can use the following information to find outwhere mysqld died. If you see no messages after this, something wentterribly wrong...stack_bottom = (nil) thread_stack 0x30000/usr/sbin/mysqld(my_print_stacktrace+0x2e)[0x86892e]/usr/sbin/mysqld(handle_segfault+0x322)[0x5b0f72]/lib64/libpthread.so.0[0x37ca20eb10]/lib64/libc.so.6(gsignal+0x35)[0x37c9630265]/lib64/libc.so.6(abort+0x110)[0x37c9631d10]/usr/sbin/mysqld[0x7fb531]/usr/sbin/mysqld[0x7df66e]/usr/sbin/mysqld[0x7e0364]/usr/sbin/mysqld[0x7d6f2e]/usr/sbin/mysqld[0x80dc9c]/usr/sbin/mysqld[0x804eea]/usr/sbin/mysqld[0x80502c]/usr/sbin/mysqld[0x7b923a]/usr/sbin/mysqld[0x7bc72d]/usr/sbin/mysqld[0x7c5857]/usr/sbin/mysqld[0x77f182]/usr/sbin/mysqld[0x78009c]/usr/sbin/mysqld[0x76b2d6]/usr/sbin/mysqld[0x79b7c8]/usr/sbin/mysqld[0x791c70]/lib64/libpthread.so.0[0x37ca20673d]/lib64/libc.so.6(clone+0x6d)[0x37c96d3d1d]The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html containsinformation that should help you find out what is causing the crash.The "--memlock" argument, which was enabled, uses system calls that areunreliable and unstable on some operating systems and operating-systemversions (notably, some versions of Linux). This crash could be due to useof those buggy OS calls. You should consider whether you really need the"--memlock" parameter and/or consult the OS distributer about "mlockall"bugs.

    I tried with innodb_force_recovery=4, option but the crash recovery fails.

    Please let me know how to resolve.

    Regards
    Prashant

  • #2
    The System Sepcs: 16 GB memory, 250 GB HDD

    [client]port = 3306socket = /var/lib/mysql/mysql.sock[mysqld]port = 3306datadir = /var/lib/mysql/datasocket = /var/lib/mysql/mysql.sockuser=mysqlback_log = 50skip-name-resolvemax_connections = 500replicate-ignore-db=mysql, information_schema, testmax_connect_errors = 10table_cache = 4096max_allowed_packet = 64Minnodb-status-file=1innodb_force_recovery = 4binlog_cache_size = 32Mmax_heap_table_size = 64Mlower_case_table_names=1sort_buffer_size = 8Mjoin_buffer_size = 8Mthread_cache_size = 8thread_concurrency = 8query_cache_size = 64Mquery_cache_limit = 64Mft_min_word_len = 4default_table_type = MYISAMthread_stack = 192Ktransaction_isolation = READ-COMMITTEDmax_write_lock_count = 1net_read_timeout = 60tmp_table_size = 64Mlog_bin = /var/lib/mysql/binlogs/mtrak32-binlog-bin-index = /var/lib/mysql/binlogs/mtrak32-bin.indexexpire_logs_days = 10max_binlog_size=100Mmax_binlog_cache_size=32Mbin log_format=MIXEDbinlog-ignore-db=mysqlbinlog-ignore-db=testbinlog-ignore-db=information_schemarelay_log = /var/lib/mysql/binlogs/mysql-relay-binrelay_log_index = /var/lib/mysql/binlogs/mysql-relay-bin.indexlog_warningslong_query_time = 2server-id = 1key_buffer_size = 32Mread_buffer_size = 2Mread_rnd_buffer_size = 32Mbulk_insert_buffer_size = 64Mmyisam_sort_buffer_size = 128Mmyisam_max_sort_file_size = 10Gmyisam_repair_threads = 10innodb_file_per_table=1innodb_additional_mem_poo l_size = 20Minnodb_buffer_pool_size = 8Ginnodb_data_file_path = ibdata1:100M:autoextendinnodb_read_io_threads = 20innodb_write_io_threads = 10innodb_thread_concurrency = 16innodb_flush_log_at_trx_commit = 2innodb_fast_shutdown=0innodb_log_file_size = 256Minnodb_max_dirty_pages_pct = 90innodb_flush_method = O_DIRECTinnodb_autoextend_increment = 1innodb_open_files = 4096sync-binlog=1log-slave-updatesauto_increment_increment = 10auto_increment_offset = 1innodb_io_capacity = 2000innodb_log_files_in_group = 3innodb_log_buffer_size = 64Minnodb_extra_rsegments = 32innodb_lock_wait_timeout = 120innodb_table_locks = 0[mysqldump]quickmax_allowed_packet = 64M[mysql]no-auto-rehash[isamchk]key_buffer = 512Msort_buffer_size = 512Mread_buffer = 8Mwrite_buffer = 8M[myisamchk]key_buffer = 512Msort_buffer_size = 512Mread_buffer = 8Mwrite_buffer = 8M[mysqlhotcopy]interactive-timeout[mysqld_safe]open-files-limit = 10240log-error=/var/log/mysqld.logpid-file=/var/run/mysqld/mysqld.pid

    Comment

    Working...
    X