Bug #61746 SHOW INNODB STATUS reports non-FK errors in the FK section
Submitted: 5 Jul 2011 6:56 Modified: 30 Dec 2013 13:39
Reporter: Stewart Smith Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical)
Version:5.1, 5.5, trunk OS:Any
Assigned to: CPU Architecture:Any
Triage: Needs Triage: D3 (Medium)

[5 Jul 2011 6:56] Stewart Smith
Description:
this is from Percona Server bug https://bugs.launchpad.net/percona-server/+bug/691601

------------------------
LATEST FOREIGN KEY ERROR
------------------------
InnoDB: Renaming table `mysql`.<result 2 when explaining filename '#sql-71b4_3b'> to `mysql`.`db` failed!
InnoDB: Renaming table `mysql`.<result 2 when explaining filename '#sql-71b4_4b'> to `mysql`.`db` failed!
InnoDB: Renaming table `mysql`.<result 2 when explaining filename '#sql-71b4_4b'> to `mysql`.`host` failed!
InnoDB: Renaming table `mysql`.<result 2 when explaining filename '#sql-71b4_4b'> to `mysql`.`user` failed!

You see these errors if you attempt to convert a MyISAM table in the 'mysql' database to an InnoDB table.

How to repeat:
see description

Suggested fix:
$ bzr diff
=== modified file 'storage/innobase/handler/ha_innodb.cc'
--- storage/innobase/handler/ha_innodb.cc	2011-06-28 12:36:14 +0000
+++ storage/innobase/handler/ha_innodb.cc	2011-07-05 06:55:19 +0000
@@ -7830,16 +7830,6 @@
 	error = row_rename_table_for_mysql(
 		norm_from, norm_to, trx, lock_and_commit);
 
-	if (error != DB_SUCCESS) {
-		FILE* ef = dict_foreign_err_file;
-
-		fputs("InnoDB: Renaming table ", ef);
-		ut_print_name(ef, trx, TRUE, norm_from);
-		fputs(" to ", ef);
-		ut_print_name(ef, trx, TRUE, norm_to);
-		fputs(" failed!\n", ef);
-	}
-
 	if (lock_and_commit) {
 		row_mysql_unlock_data_dictionary(trx);
[5 Jul 2011 11:32] Valeriy Kravchuk
Verified with current mysql-5.5 on Mac OS X:

mysql> alter table db engine=InnoDB;
ERROR 1025 (HY000): Error on rename of './mysql/#sql-7943_6' to './mysql/db' (errno: -1)

Then in SHOW ENGINE INNODB STATUS we see:

------------------------
LATEST FOREIGN KEY ERROR
------------------------
110704 17:29:28  Cannot drop table `geo`.`rubrica_logos_tipo`
because it is referenced by `rubrica`.`rubrica_logos`
ontratti`
InnoDB: Renaming table `mysql`.<result 2 when explaining filename '#sql-7943_6'> to `mysql`.`db` failed!
[1 May 2013 21:42] Elena Stepanova
Hi,

Has it been fixed?
I don't observe it on current 5.5 or 5.7, but not sure whether it's because it was really fixed, or masked by something else.
[3 Sep 2013 9:36] Valeriy Kravchuk
Maybe masked by the fix for http://bugs.mysql.com/bug.php?id=62100 and related bugs?
[23 Dec 2013 0:00] Stewart Smith
It has not been masked in recent 5.6 at least, I still see this snippet:

                if (error != DB_SUCCESS) {
                        if (!srv_read_only_mode) {
                                FILE* ef = dict_foreign_err_file;

                                fputs("InnoDB: Renaming table ", ef);
                                ut_print_name(ef, trx, TRUE, norm_from);
                                fputs(" to ", ef);
                                ut_print_name(ef, trx, TRUE, norm_to);
                                fputs(" failed!\n", ef);
                        }
                } else {
[30 Dec 2013 13:39] Daniel Price
Fixed as of 5.5.36, 5.6.15, 5.7.3, and here's the changelog entry:

Table renaming errors would appear in the "LATEST FOREIGN KEY ERROR"
section of the "SHOW ENGINE INNODB STATUS" output. 

Thank you for the bug report.
[2 Feb 2014 17:57] Laurynas Biveinis
5.5$ bzr log -r 4568
------------------------------------------------------------
revno: 4568
committer: Aditya A <aditya.a@oracle.com>
branch nick: mysql-5.5
timestamp: Sun 2013-12-29 16:55:24 +0530
message:
  Bug#12762390 SHOW INNODB STATUS REPORTS NON-FK 
               ERRORS IN THE FK SECTION
  
  ANALYSIS
  --------
  
  Any error during the renaming of the table was 
  incorrectly logged in the dict_foreign_err_file
  and it showed up in foreign key section when
  we give the query "show engine innodb status".
  
  FIX
  ---
  Prevent renaming error from being logged in 
  dict_foreign_err_file section.  
  
  [Aprooved by marko #rb 2501 ]