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 ?
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.
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.
Maybe consider implementing this:
Bug #69223 Give a warning on CREATE TABLE without PRIMARY KEY
[…] MySQL Group Replication and Table Design […]