MySQL Bugs: #16814: SHOW INNODB STATUS format error in LATEST FOREIGN KEY ERROR section

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ä	CPU Architecture:	Any

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);
}

/**************************************************************************

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);

Marko,

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

Regards,

Heikki

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.

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.

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.

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.

Fixed with InnoDB ss368; Fixed in 5.0.20.

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.

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)

Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug. Re-closing.

Pushed into mysql-next-mr (revid:alik@sun.com-20100524190136-egaq7e8zgkwb9aqi) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (pib:16)

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)

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)

Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug.
Re-closing.

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)

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)

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)