MySQL Bugs: #114802: "missing table" in MySQL NDB cluster

Bug #114802	"missing table" in MySQL NDB cluster
Submitted:	27 Apr 2024 9:07	Modified:	7 May 2024 11:56
Reporter:	CunDi Fang	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Cluster: Cluster (NDB) storage engine	Severity:	S2 (Serious)
Version:	8.0.35-cluster MySQL Cluster Community S	OS:	Any (20.04)
Assigned to:	MySQL Verification Team	CPU Architecture:	Any

Description:
Hello, I found a bug in 8.0.35-cluster version of MYSQL cluster. This can result in us not being able to see tables on a node where they should be.

Attempted and successfully reproduced!

The detail is as follow. We need at least three nodes.

I am executing the following alter table statement on a node:
Poc 1:
```
ALTER TABLE mytest90.test8 ADD COLUMN column6 float;
```

Before this alter table statement has finished executing, I execute the following alter table statement on another node:
Poc 2:
```
ALTER TABLE mytest90.test8 ALTER COLUMN column3 SET DEFAULT 880;
```

While neither of these two nodes has completed execution and both are executing statements, the following update statement is executed at the third node:
Poc 3:
```
UPDATE mytest90.test8 SET column2 = NULL, column3 = 555, column4 = 848, column5 = '2016-01-24 19:09:28', column6 = 15.59 WHERE (((column4 = NULL) AND column1 = 24.84) OR column1 < 33.21) OR column1 = 43.85;
```

At this time, this node reports an error:
```
MySQL query failed: Table 'mytest90.test8' doesn't exist
```

It doesn't make sense that this error message should not appear, because we are not deleting the table, but in the view of this node, the table "disappears".

How to repeat:
Importing the database file I give later, and then executing Poc. Note that this bug is very demanding on the execution time of the statement.

Architecture Information:
'''
[NDBD DEFAULT]
NoOfReplicas =2
DataMemory = 512M
IndexMemory = 64M

[NDB_MGMD]
NodeId=1
hostname =192.172.10.8
datadir =/var/lib/mysql-cluster

[NDBD]
NodeId =2
hostname =192.172.10.9
datadir =/usr/local/mysql-cluster/data
NodeGroup=0
[NDBD]
NodeId =3
hostname =192.172.10.10
datadir =/usr/local/mysql-cluster/data
NodeGroup=1
[NDBD]
NodeId =4
hostname =192.172.10.11
datadir =/usr/local/mysql-cluster/data
NodeGroup=0
[NDBD]
NodeId =5
hostname =192.172.10.12
datadir =/usr/local/mysql-cluster/data
NodeGroup=1

[mysqld]
NodeId =6
hostname =192.172.10.9
[mysqld]
NodeId =7
hostname =192.172.10.10
[mysqld]
NodeId =8
hostname =192.172.10.11
[mysqld]
NodeId =9
hostname =192.172.10.12
'''

Suggested fix:
I'm not sure if it's just a problem with the error message or if it's true that the table has "disappeared" on this node, and I'd appreciate it if the developers could look into the root cause of this error.

Hi,

I cannot reproduce this issue and from looking at log files looks like network issues?

很奇怪，我在我这边的环境上成功复现了这个bug。不过确实发现这个bug对于时间的要求很严格，您需要严格控制让UPDATE语句的注入时间是在两个ALTER TABLE语句都正在执行的时候。我可以把我的时间图给你。

I have successfully reproduced this bug on my side of the environment. but did find that the bug is very strict about timing, and you need to be strict about letting the UPDATE statement be injected at a time when both ALTER TABLE statements are executing. I can give you my timing chart.

This is the time diagram, the blue is the update statement, the green and red are the two alter statements.

Attachment: Figure_2.png (image/png, text), 23.65 KiB.

As you can see below, in my environment, after three attempts, the "missing table" issue occurs once

```
root@c52726a7c962 /h/m/v/n/build# ./node-repeat-main                                                                                         
MySQL connection established                                                                                                                 
prepare to read                                                                                                                              
SQL executed successfully.                                                                                                                   
num: 280000 start time: 1714458874.280194 end time:1714458874.719622                                                                         
root@c52726a7c962 /h/m/v/n/build# mysql -uroot -proot mytest90 < /home/mine-code/mysqlerror_0/28-5-6-2-database90.sql                        
mysql: [Warning] Using a password on the command line interface can be insecure.                                                             
root@c52726a7c962 /h/m/v/n/build# ./node-repeat-main                                                                                         
MySQL connection established                                                                                                                 
prepare to read                                                                                                                              
SQL executed successfully.                                                                                                                   
num: 280000 start time: 1714458910.280161 end time:1714458910.284287                                                                         
root@c52726a7c962 /h/m/v/n/build# mysql -uroot -proot mytest90 < /home/mine-code/mysqlerror_0/28-5-6-2-database90.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
root@c52726a7c962 /h/m/v/n/build# ./node-repeat-main                   
MySQL connection established
prepare to read
MySQL query failed: Table 'mytest90.test8' doesn't exist
num: 280000 start time: 1714458944.280185 end time:1714458944.288990
```
However, I've found that the issue doesn't manifest itself in the log file, and it seems that I need to turn on specific logging configurations in order to generate a log that can record these statements, and I don't see the expected results in the binary logs.
I will provide the DATA file I used to reproduce it and hopefully it will help you with the reproduction.

Hi,

Log file did not upload correctly so I cannot open it, can you please upload the same file again

thanks

Yes, this one is good

I do not see a problem in your logs and I cannot reproduce behavior that I would consider a bug.

I got the log file directly from the "ndb_error_reporter /var/lib/mysql-cluster/config.ini root" command, and I'm also wondering why this error is not reflected in the log file. But I did reproduce the problem by sending the exact sql statements.

Can you just try to enter the sql statements directly on the node as I described? According to my reproduction attempts on my machine, it is reproducible. Or please let me know if I need to open some log related configuration to get more and more detailed log files?