Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-9276

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

    XMLWordPrintable

    Details

      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.

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

        Attachments

          Activity

            People

            Assignee:
            jplindst Jan Lindström
            Reporter:
            jplindst Jan Lindström
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved: