SHOW CREATE TABLE and foreign key constraints with innodb_read_only

The "innodb_read_only" parameter is perhaps not among the most commonly used MySQL parameters. It may however influence server behaviour in a way that you may not expect. This post is a record of an investigation that revealed one of MySQL quirks caused by said parameter.

Background

The MySQL 5.6 server topology I was looking at during this investigation consisted of a single main server (R/W) and multiple replicas serving read-only statements.

This is relatively simple and you wouldn't expect to run into issues on the replicas as long as you stick to statements that do not modify data, right?

Not quite. Issue I was asked to investigate can be described as follows:

The DBA created two tables connected using a foreign key relationship:

CREATE TABLE `employee` (
  `e_id` int(11) NOT NULL,
  `name` char(20) DEFAULT NULL,
  PRIMARY KEY (`e_id`)
) ENGINE=InnoDB;

CREATE TABLE `building` (
  `office_nbr` int(11) NOT NULL,
  `description` char(20) DEFAULT NULL,
  `e_id` int(11) NOT NULL,
  PRIMARY KEY (`office_nbr`),
  KEY `e_id` (`e_id`),
  CONSTRAINT `building_ibfk_1` FOREIGN KEY (`e_id`) REFERENCES `employee` (`e_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB;

Once the tables were created in the system, the client-side software would use SHOW CREATE TABLE statements to obtain information about data organisation and relationships. Problems started immediately after users started working with the read-only replicas. Namely, the FOREIGN KEY definitions were missing from every SHOW CREATE TABLE statement executed against a replica.

Output from SHOW CREATE TABLE ran against the master server:

mysql> show create table building\G
*************************** 1. row ***************************
       Table: building
Create Table: CREATE TABLE `building` (
  `office_nbr` int(11) NOT NULL,
  `description` char(20) DEFAULT NULL,
  `e_id` int(11) NOT NULL,
  PRIMARY KEY (`office_nbr`),
  KEY `e_id` (`e_id`),
  CONSTRAINT `building_ibfk_1` FOREIGN KEY (`e_id`) REFERENCES `employee` (`e_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

Output from SHOW CREATE ran against any of the replicas:

mysql> show create table building\G
*************************** 1. row ***************************
       Table: building
Create Table: CREATE TABLE `building` (
  `office_nbr` int(11) NOT NULL,
  `description` char(20) DEFAULT NULL,
  `e_id` int(11) NOT NULL,
  PRIMARY KEY (`office_nbr`),
  KEY `e_id` (`e_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

The information "we have replicas" and above two outputs were about the only pieces of information I've originally got.

Investigation

Fortunately enough I was able to get my hands on one of the replicas so I did a few tests to check how bad (or weird?) the situation really was:

Confirm the issue exists

1. Created a bunch of dummy tables with foreign key constraints,
2. Confirmed that SHOW CREATE TABLE does not return FK definitions on replicas. No errors/warnings were being thrown, the information was just missing.

Confirm InnoDB does actually know about the constraints

mysql> select for_name, ref_name from information_schema.innodb_sys_foreign where ref_name like '%employee';
+----------------+----------------+
| for_name       | ref_name       |
+----------------+----------------+
| repro/building | repro/employee |
+----------------+----------------+
1 row in set (0.00 sec)

mysql> select constraint_schema, table_name, referenced_table_name from information_schema.referential_constraints;
+-------------------+------------+-----------------------+
| constraint_schema | table_name | referenced_table_name |
+-------------------+------------+-----------------------+
| repro             | building   | employee              |
+-------------------+------------+-----------------------+
1 row in set (0.01 sec)

In other words, constraint information was perfectly fine as long as I was selecting it directly from INFORMATION_SCHEMA. At this point I decided to take a peek into the code and here's what I learned:

SHOW CREATE TABLE uses "store_create_info" function implemented in sql/sql_show.cc

Function "store_create_info" calls "ha_innobase::get_foreign_key_create_info":

  /*
    Get possible foreign key definitions stored in InnoDB and append them
    to the CREATE TABLE statement
  */

  if ((for_str= file->get_foreign_key_create_info()))
  {
    packet->append(for_str, strlen(for_str));
    file->free_foreign_key_create_info(for_str);
  }

Function "get_foreign_key_create_info" (storage/innobase/handler/ha_innodb.cc) calls "dict_print_info_on_foreign_keys" and you may already see where I'm going with this:

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

Here it was already quite obvious that I'm not getting the FK information due to the server working in read-only mode. Still I confirmed this by attaching GDB to MySQL process and setting breakpoints on "get_foreign_key_create_info" (which was reached) and "dict_print_info_on_foreign_keys" (which was never executed by the server).

Conclusion

InnoDB requires a temporary file to produce foreign key information consumed by SHOW CREATE TABLE. When the server is running in innodb_read_only mode, file operations are not permitted since the parameter is designed to make server operations safe on read-only media. MySQL simply skips the whole block of code responsible for preparing FK information and it does so without emitting any warnings to the end user.

Once I discovered that, I became curious. Why would you run your replicas in a "hard" read-only mode ("innodb_read_only") instead of logical read-only mode ("read_only" parameter)? The customer himself provided the answer:

1. The read-only machines (replicas) operate from a read-only storage media created as a shadow copy of the main data set.
2. The read-only machines are configured with "innodb_read_only=1", which allows multiple replicas to share the same storage media.

This information didn't seem relevant (to the customer) so I didn't receive it in the beginning, though I must say this detail would likely not have helped me because I was not aware of this MySQL quirk anyway.

So what?

The observed MySQL behaviour is by design but believe MySQL should at least warn users in similar situations. I therefore created a bug report #78756 to describe my findings.

Thanks for reading!