| Bug #78754 | FK definitions missing from SHOW CREATE TABLE in "innodb_read_only" mode | ||
|---|---|---|---|
| Submitted: | 8 Oct 2015 11:27 | Modified: | 4 Sep 2019 21:06 |
| Reporter: | Szymon Komendera | Email Updates: | |
| Status: | Closed | Impact on me: | |
| 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 12:03]
MySQL Verification Team
Hello Szymon Komendera, Thank you for the report and test case. Thanks, Umesh
[3 Sep 2019 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 2019 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.
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".