Not able to drop or recreate table in Percona MySQL versions 5.1.63

  • Filter
  • Time
  • Show
Clear All
new posts

  • Not able to drop or recreate table in Percona MySQL versions 5.1.63

    One of our developer came to us with the below problem where there are not able to recreate a table.

    -> (
    -> school_id bigint(20) NOT NULL,
    -> school_type tinyint(4) ,
    -> grad_year smallint(6) NOT NULL,
    -> registration_id bigint(20) NOT NULL,
    -> PRIMARY KEY (registration_id,school_id,grad_year)
    -> ) ENGINE=InnoDB CHARSET=utf8;
    ERROR 1050 (42S01): Table '`db_cache`.`reg_k12_grad_year_b`' already exists

    mysql> drop table reg_k12_grad_year_b;
    ERROR 1051 (42S02): Unknown table 'reg_k12_grad_year_b'

    There is a .ibd file corresponding to this table in datadir with out .frm file. We are using 5.1.63 Percona Server (GPL) version.

    I have tried the solution given in [URL="http://dev.mysql.com/doc/refman/5.1/en/innodb-troubleshooting-datadict.html"]http://dev.mysql.com/doc/refman/5.1/en/innodb-troubleshooting-datadict.html[/URL="http://dev.mysql.com/doc/refman/5.1/en/innodb-troubleshooting-datadict.html"]. But the problem still exists.

    We are seeing the below error messages from error log.

    130501 2:09:45 InnoDB: Warning: trying to init to the tablespace memory cache
    InnoDB: a tablespace 534 of name './db_cache/reg_k12_grad_year_b.ibd',
    InnoDB: but a tablespace 531 of the same name
    InnoDB: already exists in the tablespace memory cache!
    InnoDB: We assume that InnoDB did a crash recovery, and you had
    InnoDB: an .ibd file for which the table did not exist in the
    InnoDB: InnoDB internal data dictionary in the ibdata files.
    InnoDB: We assume that you later removed the .ibd and .frm files,
    InnoDB: and are now trying to recreate the table. We now remove the
    InnoDB: conflicting tablespace object from the memory cache and try
    InnoDB: the init again.

    innodb parameters used are listed below.

    INNODB Parameters defined in my.cnf

    [FONT='courier new', courier, monospace]innodb_data_home_dir = /var/lib/mysql/cachecluster3[/FONT]

    # data dictionary
    innodb_data_file_path = ibdata1:10M:autoextend
    innodb_log_group_home_dir = /var/lib/mysql/cachecluster3
    innodb_log_files_in_group = 2
    innodb_lock_wait_timeout = 50
    innodb_rollback_on_timeout = 1
    innodb_lazy_drop_table = 1
    innodb_log_file_size = 512M
    innodb_log_buffer_size = 5M
    innodb_buffer_pool_size = 100G
    innodb_additional_mem_pool_size = 1M
    innodb_flush_log_at_trx_commit = 2
    innodb_support_xa = 0
    innodb_thread_concurrency = 40
    innodb_flush_method = O_DIRECT
    innodb_open_files = 2048
    innodb_file_format = Barracuda
    innodb_lock_wait_timeout = 50
    innodb_rollback_on_timeout = 1
    transaction-isolation = REPEATABLE-READ
    innodb_locks_unsafe_for_binlog = 1
    innodb_io_capacity = 800
    innodb_read_io_threads = 8
    innodb_write_io_threads = 8
    default-storage-engine = InnoDB

    If anyone of you have experienced similar issues, can you please share the solution?
    Last edited by cdhaneku; 05-01-2013, 05:48 AM.

  • #2

    Have you tried with last option suggested in that document?

    "Create a matching .frm file in some other database directory/server and copy it to the database directory where the orphan table is located. DROP TABLE for the original table. "

    Can you also try by deleting/moving .ibd file some other place, restart mysql server and then check with creating table ?