MySQL Group Replication and table design

Today’s article is about the first two restrictions in the requirements page of the manual:

• InnoDB Storage Engine: data must be stored in the InnoDB transactional storage engine.
• Primary Keys: every table that is to be replicated by the group must have an explicit primary key defined.

So the first requirement is easy to check by a simple query that list all the non InnoDB tables:

SELECT table_schema, table_name, engine, table_rows, 
       (index_length+data_length)/1024/1024 AS sizeMB 
FROM information_schema.tables 
WHERE engine != 'innodb' 
  AND table_schema NOT IN 
    ('information_schema', 'mysql', 'performance_schema');

The second one is a bit more tricky. Let me show you first how Group Replication behaves:

Case 1: no keys

Let’s create a table with no Primary Key (neither any other keys) and then let’s insert one record:

mysql> create table test_tbl_nopk (id int, name varchar(10));
mysql> insert into test_tbl_nopk values (1,'lefred');
ERROR 3098 (HY000): The table does not comply with the requirements by an external plugin.

And in the error log we can see:

[ERROR] Plugin group_replication reported: 'Table test_tbl_nopk does not have any PRIMARY KEY. 
             This is not compatible with Group Replication'

So far, so good as it’s what we were expecting, right ?

Case 2: no PK, but NOT NULL UNIQUE KEY

Now, if you know InnoDB, when there is no PK defined, InnoDB will use the first NOT NULL UNIQUE KEY as PK. How will Group Replication handle that ?
Let’s verify:

mysql> create table test_tbl_nopk_uniq_notnull (id int not null unique key, name varchar(10));
mysql> insert into test_tbl_nopk_uniq_notnull values (1,'lefred');
Query OK, 1 row affected (0.01 sec)

Excellent, so Group Replication behaves like InnoDB and allows NOT NULL UNIQUE KEYS.

Case 3: no PK, but NULL UNIQUE KEY

Just to verify, let’s try with a UNIQUE KEY that can be NULL too:

mysql> create table test_tbl_nopk_uniq_null (id int unique key, name varchar(10));
mysql> insert into test_tbl_nopk_uniq_null values (1,'lefred');
ERROR 3098 (HY000): The table does not comply with the requirements by an external plugin.

This works then as expected. Why that ? Because, in InnoDB when no primary key is defined, the first unique not null key is used as seen above, but if none
is available, InnoDB will create a hidden primary key (stored on 6 bytes). The problem with such key is that this value is global to all InnoDB tables without
PK (this can of course cause contention), but in the case of Group Replication, there is no guarantee that this hidden PK will be the same on the other nodes that are members of the Group. That’s why this is not supported.

Conclusion

So if you want to know if you have tables without valid key design for Group Replication, please run the following statement:

SELECT tables.table_schema , tables.table_name , tables.engine 
FROM information_schema.tables 
LEFT JOIN ( 
   SELECT table_schema , table_name 
   FROM information_schema.statistics 
   GROUP BY table_schema, table_name, index_name HAVING 
     SUM( case when non_unique = 0 and nullable != 'YES' then 1 else 0 end ) = count(*) ) puks 
 ON tables.table_schema = puks.table_schema and tables.table_name = puks.table_name 
 WHERE puks.table_name is null 
   AND tables.table_type = 'BASE TABLE' AND Engine="InnoDB";

The query above is the courtesy of Roland Bouman.