Re: Online alter table failure on large partitioned table
Posted by: Gareth Smith
Date: February 27, 2015 08:23AM

Thank you for responding.

I discovered this help page that seems to relate to my problem: http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html

Unfortunately it says nothing about partitions, and when I followed the instructions I found that MySQL deleted the #sql-15fd_3477.frm and #sql-15fd_3477.par files but not the actual data files (#sql-ibXXXX.ibd). MySQL wrote various error messages when I tryed to drop the table (I omitted a few hundred lines of similar output):

2015-02-27 14:04:37 7fbf89506700 InnoDB: Error: table `my_database`.`#sql-15fd_3477#P#p47` /* Partition `p47` */ does not exist in the InnoDB internal
InnoDB: data dictionary though MySQL is trying to drop it.
InnoDB: Have you copied the .frm file of the table to the
InnoDB: MySQL database directory from another database?
InnoDB: You can look for further help from
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html
2015-02-27 14:04:37 7fbf89506700 InnoDB: Error: table `my_database`.`#sql-15fd_3477#P#p48` /* Partition `p48` */ does not exist in the InnoDB internal
InnoDB: data dictionary though MySQL is trying to drop it.
InnoDB: Have you copied the .frm file of the table to the
InnoDB: MySQL database directory from another database?
InnoDB: You can look for further help from
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html
2015-02-27 14:04:37 7fbf89506700 InnoDB: Error: table `my_database`.`#sql-15fd_3477#P#p49` /* Partition `p49` */ does not exist in the InnoDB internal
InnoDB: data dictionary though MySQL is trying to drop it.
InnoDB: Have you copied the .frm file of the table to the
InnoDB: MySQL database directory from another database?
InnoDB: You can look for further help from
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html
2015-02-27 14:08:23 11038 [ERROR] Failed to open table my_database/#sql-15fd_3477#P#p0.
2015-02-27 14:08:23 11038 [Warning] InnoDB: Cannot open table my_database/#sql-15fd_3477#P#p0 from the internal data dictionary of InnoDB though the .frm file for the table exists. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html for how you can resolve the problem.

> #sql-ibXXXX.ibd are the tmp tables that will be renamed when it finishes. Delete them.

I will try this (with backups), when possible.

> Did you run out of disk space?

It does not appear so. There were no error messages to tell me why the alter table failed.

> You seem to have a lot of secondary indexes. May we see SHOW CREATE TABLE and discuss whether some of them can be DROPped, thereby helping performance of the ALTER.

Sorry, right now I just want to clear up these temporary files.

Options: ReplyQuote


Subject
Written By
Posted
Re: Online alter table failure on large partitioned table
February 27, 2015 08:23AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.