Bug #82581 altering table engine from innodb to memory will break group replication
Submitted: 15 Aug 2016 10:21 Modified: 9 Jan 2017 10:55
Reporter: Shahriyar Rzayev Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Group Replication Severity:S1 (Critical)
Version:5.7.14 OS:CentOS (7)
Assigned to: CPU Architecture:Any

[15 Aug 2016 10:21] Shahriyar Rzayev
Description:
Here is the simple scenario:
1. Testing with vagrant env, described here -> http://lefred.be/content/vagrant-environment-to-test-mysql-group-replication-0-8/

2. Created table using:
sysbench --db-driver=mysql --mysql-table-engine=InnoDB --mysql-db=dbtest --mysql-table=sbtest1 --mysql-user=fff --mysql-password=12345 --mysql-host=192.168.90.4 --num-threads=100 --test=/home/sh/REPOS/sysbench/sysbench/tests/db/insert.lua --max-requests=0 prepare

+ Running against node3/mysql3:

sysbench --db-driver=mysql --mysql-table-engine=InnoDB --mysql-db=dbtest --mysql-table=sbtest1 --mysql-user=fff --mysql-password=12345 --mysql-host=192.168.90.4 --num-threads=100 --test=/home/sh/REPOS/sysbench/sysbench/tests/db/insert.lua --max-requests=0 run

3. Altering table's engine to memory on node1.
Result:

mysql1 mysql> alter table sbtest1 engine=memory;
Query OK, 12017 rows affected (0.16 sec)
Records: 12017  Duplicates: 0  Warnings: 0

mysql1 mysql> select count(*) from sbtest1;
+----------+
| count(*) |
+----------+
|    12017 |
+----------+
1 row in set (0.05 sec)

mysql1 mysql> alter table sbtest1 engine=innodb;
ERROR 1290 (HY000): The MySQL server is running with the --super-read-only option so it cannot execute this statement

Same on node2.

4. From node3 there is an interesting error:

2016-08-15T10:03:38.870567Z 190 [ERROR] Plugin group_replication reported: 'Table sbtest1 does not use the InnoDB storage engine. This is not compatible with Group Replication'

How to repeat:
See description

Suggested fix:
As info:
With strict mode enabled PXC you will likely get something:

mysql> alter table t3 engine=memory;
ERROR 1105 (HY000): Percona-XtraDB-Cluster prohibits use of ALTER...

So it will be much more better to implement similar thing, rather than, letting to run alter engine and then aborting cluster.
[16 Aug 2016 8:44] MySQL Verification Team
Hello Shahriyar,

Thank you for the report.

Thanks,
Umesh
[16 Aug 2016 8:45] MySQL Verification Team
-- node 1 allowed to change engine, succeeded but on other node I see

2016-08-16T07:20:51.827085Z 51 [ERROR] Plugin group_replication reported: 'Table sbtest1 does not use the InnoDB storage engine. This is not compatible with Group Replication'
2016-08-16T07:20:51.827589Z 116 [ERROR] Plugin group_replication reported: 'Table sbtest1 does not use the InnoDB storage engine. This is not compatible with Group Replication'
2016-08-16T07:20:51.827688Z 22 [ERROR] Plugin group_replication reported: 'Table sbtest1 does not use the InnoDB storage engine. This is not compatible with Group Replication'
2016-08-16T07:20:51.827755Z 121 [ERROR] Plugin group_replication reported: 'Table sbtest1 does not use the InnoDB storage engine. This is not compatible with Group Replication'
2016-08-16T07:20:51.827872Z 27 [ERROR] Plugin group_replication reported: 'Table sbtest1 does not use the InnoDB storage engine. This is not compatible with Group Replication'
2016-08-16T07:20:51.827960Z 26 [ERROR] Plugin group_replication reported: 'Table sbtest1 does not use the InnoDB storage engine. This is not compatible with Group Replication'
2016-08-16T07:20:51.827872Z 37 [ERROR] Plugin group_replication reported: 'Table sbtest1 does not use the InnoDB storage engine. This is not compatible with Group Replication'
2016-08-16T07:20:51.828240Z 103 [ERROR] Plugin group_replication reported: 'Table sbtest1 does not use the InnoDB storage engine. This is not compatible with Group Replication'
2016-08-16T07:20:51.828419Z 113 [ERROR] Plugin group_replication reported: 'Table sbtest1 does not use the InnoDB storage engine. This is not compatible with Group Replication'
2016-08-16T07:20:51.828496Z 56 [ERROR] Plugin group_replication reported: 'Table sbtest1 does not use the InnoDB storage engine. This is not compatible with Group Replication'
2016-08-16T07:20:51.830634Z 94 [ERROR] Plugin group_replication reported: 'Table sbtest1 does not use the InnoDB storage engine. This is not compatible with Group Replication'
2016-08-16T07:20:51.835354Z 38 [ERROR] Plugin group_replication reported: 'Table sbtest1 does not use the InnoDB storage engine. This is not compatible with Group Replication'
2016-08-16T07:20:51.835500Z 44 [ERROR] Plugin group_replication reported: 'Table sbtest1 does not use the InnoDB storage engine. This is not compatible with Group Replication'
[2 Dec 2016 13:31] Nuno Carvalho
Hi Shahriyar,
 
Group Replication only does support InnoDB storage engine.
 
Due to server code restrictions, we do not intercept DDL.
To avoid these situations, DBA can restrict the allowed storage engines on
configuration:
disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"

The behaviour that you are seeing, parallel workload unable to commit transactions, it is expected since the storage engine is not supported.

Best regards,
Nuno Carvalho
[5 Dec 2016 1:57] Roel Van de Paar
Nuno, provided that is correct, the error message needs to be updated to a better one.
[5 Dec 2016 15:59] Nuno Carvalho
Hi Roel,

Currently we do not have a non-disruptive way to intercept DDL, stop the alter table should be the most appropriate action here.

Having said that, the error message does represent the error that is happening, transaction it is trying to update a table with a storage engine which is not supported.

Best regards,
Nuno Carvalho
[6 Dec 2016 1:11] Roel Van de Paar
Nuno, perhaps I missed something but I was referring to "--super-read-only" error not being correct
[9 Dec 2016 16:50] Nuno Carvalho
Hi Roel,

Now I got your doubt, let me explain what did happen:

1) Shahriyar has 3 servers group running.
2) He is executing transactions on server1 and changing the table engine on server3;
3) The alter table does take place on server1;
4) Transactions from server3 reach server1 and cannot be executed, since the storage engine is not supported. This will make the applier on server1 to stop and the server will change automatically to ERROR state and enable super read-only mode, that is, no more writes are allowed on this server until this is fixed.

The ERROR state is explained at 
http://mysqlhighavailability.com/gr/doc/monitoring.html?highlight=error#server-states

Currently we do not support concurrent DDL vs DML/DDL
http://mysqlhighavailability.com/gr/doc/limitations.html

Best regards,
Nuno Carvalho
[9 Jan 2017 10:55] Shahriyar Rzayev
Hi Nuno,
Let me clarify my thoughts about this situation.

1) Shahriyar has 3 servers group running. +
2) He is executing transactions on node3(sysbench) and running alter engine=memory on node1
3) After, he is running alter engine=innodb again on node1 and failed.

That's what an ordinary user waits from us:
1) If something not supported i.e non-Innodb storage engine. Prevent it to be used.
User waits that non-supported options will be prevented.
alter table sbtest1 engine=memory; -> if it breaks, just do not let it.

2) He did something on same node1 ->  alter engine=memory 
oh, no, that was a mistake, He must revert it back on same machine of course:
alter table sbtest1 engine=innodb;
ERROR 1290 (HY000): The MySQL server is running with the --super-read-only option so it cannot execute this statement 
A surprise for him.

3) Then he is looking around, he is in panic, he wants to get more info, he is trying to read log:

[ERROR] Plugin group_replication reported: 'Table sbtest1 does not use the InnoDB storage engine. This is not compatible with Group Replication'

Result -> User disappointed, he is thinking about how he could run something that is not compatible with something he is using :)
[3 Nov 2021 0:07] Justin Swanhart
You can intercept DDL quite easily with a rewrite plugin.  I think this is a good idea that if any DDL that has ENGINE[=]WHATEVER where WHATEVER != InnoDB could be made to fail by returning a non-zero return status from the rewriter plugin.

I think this should be a FR given how it breaks GR.
[3 Nov 2021 0:08] Justin Swanhart
or GR should automatically set and enforce:
disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"

It makes no sense that the user must set such a setting if the setting is mandatory.