alter table form s3 to different engine on leader break replicas with binlog_alter_two_phase;

On a replication cluster using mixed InnoDB tables ( active dataset) and S3 tables ( read only dataset) stored on a single shared s3 server.

At some point to modify the content of any s3 table, we can alter it back to InnoDB on the leader of the replication cluster

The new InnoDB table get deleted on all replicas probably via table discovery breaking the capability to failover the cluster without a filter on the modified table.

The solution would be to inject DDL create or replace and all row events of the fetched row to innodb to materialized the innodb table on all replicas and looking at the binlogs we can see a tentative of doing this using binlog-alter-two-phase

The reason it failed in optimistic and semi-sync the replication SQL thread receive the alter table but deadlock with a drop table

|      6 | system user |                 | NULL         | Slave_IO     | 869406 | Waiting for master to send event              | NULL                                                             |    0.000 |

|      8 | system user |                 | NULL         | Slave_worker | 869406 | Waiting for work from SQL thread              | NULL                                                             |    0.000 |

|     10 | system user |                 | observations | Slave_worker |   2562 | Waiting for table metadata lock               | DROP TABLE IF EXISTS `observations`.`climato_controle_terrestre` |    0.000 |

|      9 | system user |                 | NULL         | Slave_worker | 869406 | Waiting for work from SQL thread              | NULL                                                             |    0.000 |

|     11 | system user |                 | observations | Slave_worker |   2565 | NULL                                          | alter table climato_controle_terrestre engine=innodb             |    0.000 |

|      7 | system user |                 | NULL         | Slave_SQL    | 155427 | Waiting for room in worker thread event queue | NULL   

killing the alter and the drop table SQL threads and restart slave recover the innodb table on replicat

for the record we used
MariaDB [observations]> show variables like 's3%';

We can confirme that disabling set global binlog_alter_two_phase=OFF; as suggested by @Kristian Nielsen is a valid workaround and table get restored on all replicas without replication issues