MySQL Bug #78754: FK definitions missing from SHOW CREATE TABLE in "innodb_read_only" mode

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.

• 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.

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

}

1. 1. 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 `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;

1. 1. 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)

1. 1. 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".