Bug #16814 SHOW INNODB STATUS format error in LATEST FOREIGN KEY ERROR section
Submitted: 26 Jan 2006 18:10 Modified: 20 Jun 2010 0:44
Reporter: Douglas Fischer Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical)
Version:4.1.16, 5.0.18 OS:Any (all)
Assigned to: Marko Mäkelä

[26 Jan 2006 18:10] Douglas Fischer
Description:
The output of SHOW INNODB STATUS is missing a newline (\n) character at the end of the LATEST FOREIGN KEY ERROR section in some situations. This causes a problem with scripts that parse the output of SHOW INNODB STATUS that expect each section header to be wrapped in '---------' lines (in this particular case the TRANSACTIONS section header does not have its initial '----------' line as this is tacked onto the end of the last line of the LATEST FOREIGN KEY ERROR section).

How to repeat:
This particular missing newline occurs with foreign key errors of the form:

Error in foreign key constraint of table %s:
there is no index in the table which would contain
the columns as the first columns, or the data types in the
table do not match to the ones in the referenced table. Constraint:

Suggested fix:
This appears to be due to a missing fputs("\n", file) in either dict_foreign_error_report() or dict_print_info_on_foreign_key_in_create_format() (both in innobase/dict/dict0dict.c). A possible patch would be one of the following, depending upon whether or not the output of dict_print_info_on_foreign_key_in_create_format() should end in a newline:

--- innobase/dict/dict0dict.c.orig
+++ innobase/dict/dict0dict.c
@@ -2202,2207 +2202,2208 @@
"for correct foreign key definition.\n",
        file);
    }
+    fputs("\n", file);
    mutex_exit(&dict_foreign_err_mutex);
}

--- innobase/dict/dict0dict.c.orig
+++ innobase/dict/dict0dict.c
@@ -4418,4423 +4418,4425 @@
    if (foreign->type & DICT_FOREIGN_ON_UPDATE_NO_ACTION) {
        fputs(" ON UPDATE NO ACTION", file);
    }
+
+    fputs("\n", file);
}

/**************************************************************************
[26 Jan 2006 18:26] Douglas Fischer
Actually, the patch to dict_foreign_error_report() should be:

--- innobase/dict/dict0dict.c.orig
+++ innobase/dict/dict0dict.c
@@ -2194,2199 +2194,2200 @@
    fputs(msg, file);
    fputs(" Constraint:\n", file);
    dict_print_info_on_foreign_key_in_create_format(file, NULL, fk, TRUE);
+    fputs("\n", file);
    if (fk->foreign_index) {
        fputs("\nThe index in the foreign key in table is ", file);
        ut_print_name(file, NULL, fk->foreign_index->name);
[27 Jan 2006 13:05] Heikki Tuuri
Marko,

please add the \n where appropriate. It is enough to fix this in 5.0 and 5.1.

Regards,

Heikki
[27 Jan 2006 16:17] Marko Mäkelä
Douglas,
do you have a test case for triggering the bug? As far as I can tell from your newer patch, this should only occur when a table is created or loaded to the data dictionary. I could come up with this test:

set foreign_key_checks=0;
create table c(a int primary key, constraint foreign key(a)references p(a))engine=innodb;
create table p(a char(1) primary key)engine=innodb;
show innodb status\G

Here, no newline will be missing, because fk->foreign_index will be non-NULL.
[31 Jan 2006 16:38] Douglas Fischer
Unfortunately I did not get the exact SQL that caused the error, but I will see if I can get you a test case. I do know that fk->foreign_index was NULL as the section of error message following that conditional was not included.

Thanks.
[2 Feb 2006 0:27] Douglas Fischer
The following test case will generate the problem described:
------------------------------------
create table test1 ( col1 int, key (col1) );
create table test2 ( col1 int, key (col1) );
alter table test2 add foreign key (col1) references test1 (col1) on delete restrict;
alter table test2 drop key col1;
------------------------------------

SHOW INNODB STATUS will give the following:
------------------------------------------------------
------------------------
LATEST FOREIGN KEY ERROR
------------------------
060201 19:24:23 Error in foreign key constraint of table dkf/test2:
there is no index in the table which would contain
the columns as the first columns, or the data types in the
table do not match to the ones in the referenced table. Constraint:
,
  CONSTRAINT test2_ibfk_1 FOREIGN KEY (col1) REFERENCES test1 (col1)------------
TRANSACTIONS
------------
Trx id counter 0 175717276
-------------------------------------------------------

This output has the trailing '\n' missing from the LATEST FOREIGN KEY ERROR section.

Thanks.
[6 Feb 2006 10:35] Marko Mäkelä
Thank you for the test case. I've committed the fix to the InnoDB source repositories, and it will propagate to the MySQL tree within a couple of weeks.
[5 Apr 2006 19:13] Elliot Murphy
Fixed with InnoDB ss368; Fixed in 5.0.20.
[8 Apr 2006 13:29] Jon Stephens
Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

Documented in 5.0.20 changelog. Closed.
[5 May 2010 15:13] Bugs System
Pushed into 5.1.47 (revid:joro@sun.com-20100505145753-ivlt4hclbrjy8eye) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[6 May 2010 1:52] Paul Dubois
Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug. Re-closing.
[28 May 2010 5:48] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100524190136-egaq7e8zgkwb9aqi) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (pib:16)
[28 May 2010 6:18] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20100524190941-nuudpx60if25wsvx) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[28 May 2010 6:45] Bugs System
Pushed into 5.5.5-m3 (revid:alik@sun.com-20100524185725-c8k5q7v60i5nix3t) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[29 May 2010 22:53] Paul Dubois
Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug.
Re-closing.
[17 Jun 2010 11:49] Bugs System
Pushed into 5.1.47-ndb-7.0.16 (revid:martin.skold@mysql.com-20100617114014-bva0dy24yyd67697) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[17 Jun 2010 12:27] Bugs System
Pushed into 5.1.47-ndb-6.2.19 (revid:martin.skold@mysql.com-20100617115448-idrbic6gbki37h1c) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[17 Jun 2010 13:14] Bugs System
Pushed into 5.1.47-ndb-6.3.35 (revid:martin.skold@mysql.com-20100617114611-61aqbb52j752y116) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)