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: | |
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
[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.