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