← Back to team overview

maria-discuss team mailing list archive

Re: Unable to DROP table, Data Dict problem

 

Hi Jan,

the root cause for the corruption, we know: NFSv4 bugs. The
"dump/restore-to-new-db" path we know as well.

My question here is: is there a way to get rid of this table _without
restoring to a new DB_.

thanks,


martin

On Wed, Jan 28, 2015 at 2:05 AM, Jan Lindström
<jan.lindstrom@xxxxxxxxxxx> wrote:
> Hi,
>
> Yes I see the assertion, it is a consistency check. Inside a tablespace
> (table in this case) every page has space_id stored. This assertion tells us
> that one page has different space_id than on tablespace header i.e. there is
> one page that based on metadata does not belong to this table. Furthermore,
> that means that tablespace could be corrupted. If the instructions on that
> web page do not help, you need to export all tables, delete everything,
> import tables to new database. Can you send me full, unedited error log, I
> will try to see if there is something that could help to identify the real
> problem. Also if you could send me dmesg output.
>
> R: Jan
>
> On Tue, Jan 27, 2015 at 4:19 PM, Martin Langhoff <martin.langhoff@xxxxxxxxx>
> wrote:
>>
>> Hi Jan,
>>
>> thanks for your notes. I have gone through the instructions in that
>> doc link, but they do not help. In the prior emails I have included
>> stack dumps and straces of what happens when I follow those
>> instructions.
>>
>> There is something else going on, or perhaps a regression that
>> prevents the procedure you mention from working right.
>>
>> We are definitely NOT hitting ENOSPC, nor any FS or storage side
>> issue. I have double checked on that.
>>
>> cheers,
>>
>>
>> m
>>
>> On Tue, Jan 27, 2015 at 12:37 AM, Jan Lindström
>> <jan.lindstrom@xxxxxxxxxxx> wrote:
>> > Hi,
>> >
>> > Failure you see on
>> >
>> > InnoDB: Error: tablespace id is 47073 in the data dictionary InnoDB: but
>> > in
>> > file ./moodle_sand/mdl_tag_correlation.ibd it is 48463!
>> > 150119 17:14:45  InnoDB: Assertion failure in thread 140045886629632 in
>> > file
>> > fil0fil.c line 778
>> >
>> > indicates database corruption that is caused most likely disk failure or
>> > a
>> > bug on a software. With this information not easy to say which one. If
>> > drop
>> > table crashes while the operation is done, deleting .ibd file is not
>> > enough
>> > to re-create the table because table definition is on innodb data
>> > dictionary
>> > while it is not on MySQL/MariaDB data dictionary. Please refer to
>> >
>> > http://dev.mysql.com/doc/refman/5.5/en/innodb-troubleshooting-datadict.html
>> > how to resolve the issue.
>> >
>> > R: Jan
>> >
>> > On Tue, Jan 20, 2015 at 5:00 PM, Martin Langhoff
>> > <martin.langhoff@xxxxxxxxx>
>> > wrote:
>> >>
>> >> Hi Folks,
>> >>
>> >> We run a very large infra on MariaDB/RHEL6.x/RHEV/NetApp; thousands of
>> >> VMs, each running MariaDB and very busy. MariaDB is 5.5.32.
>> >>
>> >> We recently had some NFS problems which led to minor DB corruption.
>> >> Luckily, the corruption was isolated to a table holding cache data,
>> >> and in fact one that is empty in most of our instances. That's the
>> >> good news.
>> >>
>> >> The bad news is that the data dict is really unhappy about that table.
>> >>
>> >>  - Cannot drop it, we get a nasty stack trace at [1]
>> >>
>> >>  - Stopping MariaDB, removing the table by hand, restarting MariaDB,
>> >> attempting to re-create the table hits a data dict error [2]. On disk
>> >> I can see it created an ibd file, but no frm file. Following the
>> >> instructions at
>> >>
>> >>
>> >> http://dev.mysql.com/doc/refman/5.0/en/innodb-troubleshooting-datadict.html
>> >> does not help.
>> >>
>> >> I have straced the process, and it does not seem to even try to create
>> >> the frm file, so it really looks like it's internal state (data dict),
>> >> not errors from further down the stack. OS / kernel logs are clean.
>> >>
>> >> Is there any reasonable method to cure this "in place" with minimal
>> >> disruption? (These are large DBs, dump restore is more than awkward.)
>> >>
>> >> Oh and careful with NFSv4 :-/
>> >>
>> >> thank you!
>> >>
>> >>
>> >> martin
>> >>
>> >> 1 - Stack trace when trying to drop table
>> >>
>> >> Version: '5.5.32-MariaDB-log'  socket: '/var/lib/mysql/mysql.sock'
>> >> port: 3306  MariaDB Server
>> >> InnoDB: Error: tablespace id is 47073 in the data dictionary
>> >> InnoDB: but in file ./moodle_sand/mdl_tag_correlation.ibd it is 48463!
>> >> 150119 17:14:45  InnoDB: Assertion failure in thread 140045886629632
>> >> in file fil0fil.c line 778
>> >> 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.5/en/forcing-innodb-recovery.html
>> >> InnoDB: about forcing recovery.
>> >> 150119 17:14:45 [ERROR] 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.
>> >>
>> >> To report this bug, see http://kb.askmonty.org/en/reporting-bugs
>> >>
>> >> 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.
>> >>
>> >> Server version: 5.5.32-MariaDB-log
>> >> key_buffer_size=134217728
>> >> read_buffer_size=131072
>> >> max_used_connections=1
>> >> max_threads=187
>> >> thread_count=1
>> >> It is possible that mysqld could use up to
>> >> key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads =
>> >> 182265 K  bytes of memory
>> >> Hope that's ok; if not, decrease some variables in the equation.
>> >>
>> >> Thread pointer: 0x0xa92ee60
>> >> 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 = 0x7f5ef9531d78 thread_stack 0x48000
>> >> ??:0(my_print_stacktrace)[0xa8631e]
>> >> ??:0(handle_fatal_signal)[0x6cdd0b]
>> >> ??:0(??)[0x30a7c0f500]
>> >> ??:0(??)[0x30a78328a5]
>> >> ??:0(??)[0x30a7834085]
>> >> ??:0(fil_node_open_file)[0x8f3add]
>> >> ??:0(fil_node_prepare_for_io)[0x8f3be5]
>> >> ??:0(fil_space_get_flags)[0x8f7420]
>> >> ??:0(fil_space_get_zip_size)[0x8f7529]
>> >> ??:0(dict_drop_index_tree)[0x8d64e6]
>> >> ??:0(row_upd_clust_step)[0x86d84d]
>> >> ??:0(row_upd_step)[0x86e22e]
>> >> ??:0(que_run_threads)[0x9577a8]
>> >> ??:0(que_eval_sql)[0x957ea6]
>> >> ??:0(row_drop_table_for_mysql)[0x85ae6a]
>> >> ??:0(ha_innobase::delete_table(char const*))[0x8424e3]
>> >> ??:0(ha_delete_table(THD*, handlerton*, char const*, char const*, char
>> >> const*, bool))[0x6d5233]
>> >> ??:0(mysql_rm_table_no_locks(THD*, TABLE_LIST*, bool, bool, bool,
>> >> bool))[0x5ff928]
>> >> ??:0(mysql_rm_table(THD*, TABLE_LIST*, char, char))[0x60001d]
>> >> ??:0(mysql_execute_command(THD*))[0x58ad90]
>> >> ??:0(mysql_parse(THD*, char*, unsigned int, Parser_state*))[0x58e369]
>> >> ??:0(dispatch_command(enum_server_command, THD*, char*, unsigned
>> >> int))[0x58f790]
>> >> ??:0(do_handle_one_connection(THD*))[0x647cdf]
>> >> ??:0(handle_one_connection)[0x647d7c]
>> >> ??:0(??)[0x30a7c07851]
>> >> ??:0(??)[0x30a78e811d]
>> >>
>> >> Trying to get some variables.
>> >> Some pointers may be invalid and cause the dump to abort.
>> >> Query (0x7f5db800afa8): is an invalid pointer
>> >> Connection ID (thread ID): 2
>> >> Status: NOT_KILLED
>> >>
>> >> Optimizer switch:
>> >>
>> >>
>> >> index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=off
>> >>
>> >>
>> >>
>> >> 2 - Error when attempting to recreate table
>> >> 150119 17:15:22  InnoDB: Operating system error number 2 in a file
>> >> operation.
>> >> InnoDB: The error means the system cannot find the path specified.
>> >> InnoDB: If you are installing InnoDB, remember that you must create
>> >> InnoDB: directories yourself, InnoDB does not create them.
>> >> 150119 17:15:22  InnoDB: Error: trying to open a table, but could not
>> >> InnoDB: open the tablespace file
>> >> './moodle_sand/mdl_tag_correlation.ibd'!
>> >> InnoDB: Have you moved InnoDB .ibd files around without using the
>> >> InnoDB: commands DISCARD TABLESPACE and IMPORT TABLESPACE?
>> >> InnoDB: It is also possible that this is a temporary table #sql...,
>> >> InnoDB: and MySQL removed the .ibd file for this.
>> >> InnoDB: Please refer to
>> >> InnoDB:
>> >>
>> >> http://dev.mysql.com/doc/refman/5.5/en/innodb-troubleshooting-datadict.html
>> >> InnoDB: for how to resolve the issue.
>> >>
>> >>
>> >> --
>> >>  martin.langhoff@xxxxxxxxx
>> >>  -  ask interesting questions
>> >>  - don't get distracted with shiny stuff  - working code first
>> >>  ~ http://docs.moodle.org/en/User:Martin_Langhoff
>> >>
>> >> _______________________________________________
>> >> Mailing list: https://launchpad.net/~maria-discuss
>> >> Post to     : maria-discuss@xxxxxxxxxxxxxxxxxxx
>> >> Unsubscribe : https://launchpad.net/~maria-discuss
>> >> More help   : https://help.launchpad.net/ListHelp
>> >
>> >
>>
>>
>>
>> --
>>  martin.langhoff@xxxxxxxxx
>>  -  ask interesting questions
>>  - don't get distracted with shiny stuff  - working code first
>>  ~ http://docs.moodle.org/en/User:Martin_Langhoff
>
>



-- 
 martin.langhoff@xxxxxxxxx
 -  ask interesting questions
 - don't get distracted with shiny stuff  - working code first
 ~ http://docs.moodle.org/en/User:Martin_Langhoff


References