I have set up an InnoDB cluster using MySQL (version 8.0.23) Shell. I added three nodes to my cluster. At first, my topology was a single-primary topology where I had one master node and two slave nodes. The problem with this topology was that whenever a failure happened (I voluntarily switched off one of the servers), the master node was not switched to another available node. And when I switched off two nodes, the third node remained in read-only mode which means that my application was down.
So, I decided to switch to a multi-primary topology to avoid the read-only problem which could make me lose purchases and money. But now I get
SQLSTATE(HY000): General error: 1205 Lock wait timeout exceeded; almost all the time.
I have no idea what the problem is. I increased
innodb_lock_wait_timeout to 900 seconds and nothing changed. I thought maybe my MySQL router is not working properly? Could it be a firewall problem? I have already allowed 6603,60330 and 66031 through my firewall. I am not sure. Any help is appreciated.