Bug #78754 FK definitions missing from SHOW CREATE TABLE in "innodb_read_only" mode
Submitted: 8 Oct 2015 11:27 Modified: 4 Sep 21:06
Reporter: Szymon Komendera Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical)
Version:5.6, 5.7, 5.6.27 OS:Any
Assigned to: CPU Architecture:Any

[8 Oct 2015 11:27] Szymon Komendera
Description:
Function "ha_innobase::get_foreign_key_create_info" defined in storage/innobase/handler/ha_innodb.cc depends on read/write server mode to be able to return information about foreign key constraints defined on a table.

Result:

- SHOW CREATE TABLE statement depends on "ha_innobase::get_foreign_key_create_info"
- When working with a server running in "innodb_read_only" mode, SHOW CREATE TABLE does not include information about foreign key constraints. This is done silently with no warnings emitted to the user.

I originally observed/diagnosed the issue on 5.6.23 but the relevant code looks the same on the latest 5.6/5.7 versions.

NOTE: Data dictionary itself is not affected, queries against I_S tables do return the correct information.

## Excerpt from the function:

ha_innobase::get_foreign_key_create_info(void)
{

...

	if (!srv_read_only_mode) {
		
		/* Output the data to a temporary file */
		dict_print_info_on_foreign_keys(
			TRUE, srv_dict_tmpfile, m_prebuilt->trx,
			m_prebuilt->table);

		...

		/* Allocate buffer for the string, and
		read the contents of the temporary file */

		...

		return(str);
	}

	return(NULL);
}

## Why is SHOW CREATE TABLE impacted:

1. SHOW CREATE TABLE uses function "store_create_info" in sql/sql_show.cc
2. Function "store_create_info" calls "ha_innobase::get_foreign_key_create_info" defined in storage/innobase/handler/ha_innodb.cc
3. "get_foreign_key_create_info" calls dict_print_info_on_foreign_keys in storage/innobase/dict/dict0dict.cc

With "innodb_read_only", we never reach the 3rd step so SHOW CREATE TABLE receives empty FK information from InnoDB.

How to repeat:
## CREATE SCHEMA AND TABLES

CREATE SCHEMA `repro`;

CREATE TABLE `repro`.`crew` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`code` varchar(50) NOT NULL,
`name` varchar(50) NOT NULL,
`created_at` timestamp NULL DEFAULT NULL,
`updated_at` timestamp NULL DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `code` (`code`)
) ENGINE=InnoDB;

CREATE TABLE `repro`.`crew_role_assigned` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`crew_id` int(11) NOT NULL,
`role_code` varchar(50) NOT NULL,
PRIMARY KEY (`id`),
KEY `fk_crewRoleAssigned_roleCode` (`role_code`),
KEY `fk_crewRoleAssigned_crewId` (`crew_id`),
CONSTRAINT `fk_crewRoleAssigned_crewId` FOREIGN KEY (`crew_id`) REFERENCES `repro`.`crew` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB;

## Run SHOW CREATE TABLE (tested on 5.6.23, 5.6.27, 5.7.8) with innodb_read_only = 0

mysql> select @@innodb_read_only;
+--------------------+
| @@innodb_read_only |
+--------------------+
|                  0 |
+--------------------+
1 row in set (0.00 sec)

mysql> show create table crew_role_assigned\G
*************************** 1. row ***************************
       Table: crew_role_assigned
Create Table: CREATE TABLE `crew_role_assigned` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `crew_id` int(11) NOT NULL,
  `role_code` varchar(50) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_crewRoleAssigned_roleCode` (`role_code`),
  KEY `fk_crewRoleAssigned_crewId` (`crew_id`),
  CONSTRAINT `fk_crewRoleAssigned_crewId` FOREIGN KEY (`crew_id`) REFERENCES `crew` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

## Run SHOW CREATE TABLE (tested on 5.6.23, 5.6.27, 5.7.8) this time with innodb_read_only = 1

mysql> select @@innodb_read_only;
+--------------------+
| @@innodb_read_only |
+--------------------+
|                  1 |
+--------------------+
1 row in set (0.00 sec)

mysql> show create table crew_role_assigned\G
*************************** 1. row ***************************
       Table: crew_role_assigned
Create Table: CREATE TABLE `crew_role_assigned` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `crew_id` int(11) NOT NULL,
  `role_code` varchar(50) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_crewRoleAssigned_roleCode` (`role_code`),
  KEY `fk_crewRoleAssigned_crewId` (`crew_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> show warnings;
Empty set (0.00 sec)

Suggested fix:
- Short term: SHOW CREATE TABLE call should produce a warning if the server is running in read only mode.
- Long term: remove the dependency on temporary files in "ha_innobase::get_foreign_key_create_info".
[8 Oct 2015 12:03] Umesh Shastry
Hello Szymon Komendera,

Thank you for the report and test case.

Thanks,
Umesh
[3 Sep 16:38] Dmitry Lenev
Posted by developer:
 
Hello!

This issue has been fixed in MySQL 8.0.19 by the same fix as for
bug#25583288 "DO NOT USE HA_INNOBASE::GET_FOREIGN_KEY_CREATE_INFO()
FOR SHOW CREATE TABLE FK".

commit 047c7c35f58d5fa4149b32c12fa5f12892228349
Author: Dmitry Lenev <dmitry.lenev@oracle.com>
Date:   Thu Aug 22 10:37:42 2019 +0300

    Bug#25583288 "DO NOT USE HA_INNOBASE::GET_FOREIGN_KEY_CREATE_INFO() FOR SHOW CREATE TABLE FK".
    
    In MySQL 8.0 all information about foreign keys is available from the
    new SQL-layer data-dictionary. However, SHOW CREATE TABLE
    implementation still relied on storage engines to provide CREATE TABLE
    clauses describing foreign key constraints.
    
    This resulted in duplicate code in SEs and in other issues, like:
    
    - Non-uniform behavior of SHOW CREATE TABLE for foreign keys in InnoDB
      and NDB SEs (different spacing, different approach to defaults
      output).
    - Bug#21966795 "FK DEFINITIONS MISSING FROM SHOW CREATE TABLE IN 'INNODB_READ_ONLY' MODE".
      In which the problem occurred due to fact that InnoDB code
      responsible for printing foreign key clauses for SHOW CREATE TABLE
      used temporary file which was not available in --innodb-read-only
      mode.
    - Bug#30110545 "FK TABLE NAME CASING IS OMITTED ON MYSQL 8 LOWER_CASE_TABLE_NAMES=2"
      In which name of parent table in a foreign key has been shown in
      incorrect case due to fact that InnoDB code responsible for its
      printing for SHOW CREATE TABLE didn't have access to original
      parent table name.
    
    This patch solves these problems by changing SHOW CREATE TABLE
    code to produce foreign key descriptions from information in
    the SQL-layer data-dictionary. SE API which was previously used
    for this -- handler::get/free_foreign_key_create_info(), its
    implementations in InnoDB and NDB and supporting code have been
    removed.
    
    Note that this patch doesn't completely remove SE-specific code
    for printing foreign key descriptions as it is also used by SEs for
    error reporting. Getting rid of it requires more work and making
    some additional information from the data-dictionary available
    to SEs in these cases.
    
    As result of the above changes the behavior of SHOW CREATE TABLE
    for foreign keys over NDB tables has changed in the following ways:
    - We now put space (' ') after comma between column names in
      multi-column foreign keys as for InnoDB foreign keys.
    - We no longer show ON UPDATE/DELETE NO ACTION clauses (which are
      default) in SHOW CREATE TABLE output, similarly to InnoDB foreign
      keys.
    - We no longer show names of NDB mock tables in SHOW CREATE TABLE
      output in @@ndb_show_foreign_key_mock_tables=1 mode.
[4 Sep 21:06] Daniel Price
Posted by developer:
 
Fixed as of the upcoming 8.0.19 release, and here's the changelog entry:

In read-only mode (innodb_read_only=ON), SHOW CREATE TABLE output did not
include information about foreign key constraints.