Bug #71312 Weird warning for MERGE table while accessing I_S.REFERENTIAL_CONSTRAINTS

• Submitted: 7 Jan 2014 13:21
• Modified: 8 Jan 2014 12:27
• Reporter: Valeriy Kravchuk
• Status: Verified
• Category: MySQL Server: Information schema
• Severity: S3 (Non-critical)
• Version: 5.6.15
• OS: Any
• CPU Architecture: Any
• Tags: information_schema, merge, REFERENTIAL_CONSTRAINTS

[7 Jan 2014 13:21] Valeriy Kravchuk

Description:
I've noted weird warning while select from INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS:

mysql> select * from information_schema.referential_constraints\G
Empty set, 1 warning (0.15 sec)

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Warning
   Code: 1168
Message: Unable to open underlying table which is differently defined or of non-
MyISAM type or doesn't exist
1 row in set (0.00 sec)

After some digging around I've noted badly defined MERGE table in one of databases. But why upon access to this I_S table server tries to check definition of MERGE table at all? Isn't it enough to open .frm file to find out that it's a MERGE table and then skip it while checking for referential constraints 9as this storage engine does not allow to define them anyway)?

How to repeat:
drop database test;
select version();
create database test;
use test;
select * from information_schema.referential_constraints\G

create table tmi1(id int, c1 int) engine=MyISAM;
create table tmi2(id char(100), c2 char(100)) engine=MyISAM;
create table tmerge(id int, c1 int) engine=MERGE UNION=(tmi1,tmi2);

show create table tmerge\G
select * from information_schema.referential_constraints\G
show warnings\G

Suggested fix:
Do NOT access .MRG files in any way while executing queries to information_schema.referential_constraints - there is no point to open or check MERGE tables, as this storage engine does not support referential constraints anyway.

[7 Jan 2014 13:35] Valeriy Kravchuk

To better understand the impact, let me continue the test:

mysql> create temporary table tref(t1 varchar(129), t2 varchar(129),
    -> key(t1) using btree, key(t2) using btree) engine=MEMORY;
Query OK, 0 rows affected (0.01 sec)

mysql> create table tpri(id int primary key) engine=InnoDB;
Query OK, 0 rows affected (0.92 sec)

mysql> create table tref(id int primary key, pri_id int,
    -> key(pri_id), foreign key(pri_id) references tpri(id)) engine=InnoDB;
Query OK, 0 rows affected (0.96 sec)

mysql> select * from information_schema.referential_constraints\G
*************************** 1. row ***************************
       CONSTRAINT_CATALOG: def
        CONSTRAINT_SCHEMA: test
          CONSTRAINT_NAME: tref_ibfk_1
UNIQUE_CONSTRAINT_CATALOG: def
 UNIQUE_CONSTRAINT_SCHEMA: test
   UNIQUE_CONSTRAINT_NAME: PRIMARY
             MATCH_OPTION: NONE
              UPDATE_RULE: RESTRICT
              DELETE_RULE: RESTRICT
               TABLE_NAME: tref
    REFERENCED_TABLE_NAME: tpri
1 row in set, 1 warning (0.15 sec)

mysql> select concat(constraint_schema, concat('.', table_name)) as t1,
    -> concat(unique_constraint_schema, concat('.', referenced_table_name)) as t
2
    -> from information_schema.referential_constraints;
+-----------+-----------+
| t1        | t2        |
+-----------+-----------+
| test.tref | test.tpri |
+-----------+-----------+
1 row in set, 1 warning (0.15 sec)

mysql> insert into tref
    -> select concat(constraint_schema, concat('.', table_name)) as t1,
    -> concat(unique_constraint_schema, concat('.', referenced_table_name)) as t2
    -> from information_schema.referential_constraints;
Query OK, 0 rows affected (0.16 sec)

mysql> select version(), @@log_bin;
+------------+-----------+
| version()  | @@log_bin |
+------------+-----------+
| 5.6.15-log |         1 |
+------------+-----------+
1 row in set (0.00 sec)

mysql> select @@sql_mode;
+----------------------------------------------------------------+
| @@sql_mode                                                     |
+----------------------------------------------------------------+
| STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+----------------------------------------------------------------+
1 row in set (0.00 sec)

So, this warning leads to something that may be considered wrong results (row visible in SELECT can not be successfully inserted into MEMORY table with INSERT ... SELECT).

[8 Jan 2014 12:27] MySQL Verification Team

Hello Valeriy,

Thank you for the bug report.
Verified as described.

Thanks,
Umesh