The information indicating abnormal operation of mysql data files is insufficient
Recently, a mysql 5.7 database was incorrectly emptied of database tablespace files due to a faulty script deployed by a developer.
However, the problem took a long time to analyze because there was no valid diagnostic information for such cases in the mysql database.
As the cause of the problem was unknown, it took a long time to solve it.
But the problem was eventually guessed, and it was due to a script defect that performed an operation similar to 'echo 1 > t1.ibd' on innodb tablespace.
After the problem is solved, to further analyze the problem and confirm the following deficiencies in the mysql database:

The following Phenomenon description is based on the following test data:

create table t1(tm datetime);
insert into t1 values(now());
insert into t1 values(now());
insert into t1 values(now());
insert into t1 values(now());
insert into t1 values(now());

----------------------------> Phenomenon 1: <--------------------------------
Query the table t1, and then execute 'echo 1 > t1.ibd' for the tablespace file t1.ibd. If the buffer pool does not contain the cache page corresponding to the t1 table, the error log displays the following error message and terminates the database instance.

The simulation steps are as follows:

select * from t1; // Repeat the query many times
echo 1 > t1.ibd 
select count(c1) from other_big_tab; // Repeat the query many times, Occupied buffer pool
select * from information_schema.INNODB_BUFFER_PAGE_LRU where table_name like '%t1%'; // Ensure that no cache page corresponding to table t1 exists in the buffer pool
select * from t1; // In this case, an error is raised and the database instance crashes.

Error log:
2023-07-28T23:43:31.671198+08:00 3 [Warning] InnoDB: 16384 bytes should have been read. Only 0 bytes read. Retrying for the remaining bytes.
...
2023-07-28T23:43:31.671457+08:00 3 [Warning] InnoDB: 16384 bytes should have been read. Only 0 bytes read. Retrying for the remaining bytes.
2023-07-28T23:43:31.671471+08:00 3 [Warning] InnoDB: Retry attempts for reading partial data failed.
2023-07-28T23:43:31.671479+08:00 3 [ERROR] InnoDB: Tried to read 16384 bytes at offset 49152, but was only able to read 0
2023-07-28T23:43:31.671487+08:00 3 [ERROR] InnoDB: File (unknown): 'read' returned OS error 0. Cannot continue operation
2023-07-28T23:43:31.671493+08:00 3 [ERROR] InnoDB: Cannot continue operation.

Question:
According to the above information, the key file name is output as '(unknown)', causing the user to be unable to determine which tablespace file has an exception.

----------------------------> Phenomenon 2: <--------------------------------
Query the t1 table to ensure that the data blocks are cached in the buffer pool.
The 'echo 1 > t1.ibd' operation is performed on the tablespace file t1.ibd.
Then the t1 update operation is performed. Since the data has been updated to the buffer, mysql will use the page in the buffer to refresh the file(But not include the page of the file header), so the file has content again.
If the database is due to insert or delete at this time, the relevant thread is triggered to perform statistical updates or other operations, and access the page that has not been updated. An exception occurs in the database.

The simulation steps are as follows:
select * from t1; // Repeat the query many times
echo 1 > t1.ibd 
ls -trl t1.ibd
update t1 set tm=now();
ls -trl t1.ibd
insert into t1 values(now()); // Trigger the statistics operation

Error log:
2023-07-29 00:54:17 0x7fff4a7fc700  InnoDB: Assertion failure in thread 140734443276032 in file fsp0fsp.cc line 2466
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.

Question:
Only a bug is displayed, but the actual situation is caused by misoperation of data files. Provides the wrong direction of analysis.

----------------------------> Phenomenon 3: <--------------------------------
Query t1 table to ensure that the data blocks are loaded to the buffer pool. The shutdown is then executed, and mysql writes pageid in the buffer pool to the ib_buffer_pool file.
The 'echo 1 > t1.ibd' operation is performed on the tablespace file t1.ibd.
Then the database is started. In this case, the database executes page loading to the buffer pool in advance, resulting in an exception of the instance.

The simulation steps are as follows:
select * from t1; // Repeat the query many times
shutdown ;
cat ib_buffer_pool // Verify that the associated page exists
echo 1 > t1.ibd 
ls -trl t1.ibd
mysqld --defaults-file=my.cnf 


Error log:
2023-07-29T09:57:27.611621+08:00 0 [Warning] InnoDB: 35 threads created by InnoDB had not exited at shutdown!
Pending normal aio reads: 0
Pending normal aio writes: 0
Pending ibuf aio reads: 0
Pending log i/o's: 0
Pending sync i/o's: 0
2023-07-29 09:57:27 0x7fff4effd700  InnoDB: Assertion failure in thread 140734518777600 in file srv0start.cc line 1357
InnoDB: Failing assertion: 0
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.

Question:
The output only indicates that there is a bug in mysql, but in fact, the file file is damaged. A critical problem is missing, causing users to be unable to confirm that the ibd file of table t1 is abnormal.

----------------------------> Phenomenon 4: <--------------------------------
If the problem has been identified, plan to delete it. If the t1 table has not been loaded to the mysql database before deleting it (for example, after the mysql database is restarted), run the drop table command to view that the deletion succeeds.
You can see from the error log that an error was reported during the deletion operation. In fact, mysql performed frm/cache file cleanup operations and skipped deleting the t1.ibd file because the tablespace file was corrupted.

Simulation steps:
shutdown ;
rm ib_buffer_pool // Avoid loading relevant pages early
echo 1 > t1.ibd 
mysqld --defaults-file=my.cnf 
mysql -uroot -p
drop table testdb.t1;

Error log:
2023-07-29T10:37:29.023501+08:00 2 [ERROR] InnoDB: Failed to find tablespace for table `db3`.`t1` in the cache. Attempting to load the tablespace with space id 163
2023-07-29T10:37:29.023649+08:00 2 [ERROR] InnoDB: Cannot read first page of './db3/t1.ibd' I/O error
2023-07-29T10:37:29.023707+08:00 2 [ERROR] InnoDB: Cannot read first page in datafile: ./db3/t1.ibd, Space ID:18446744073709551615, Flags: 33. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting-datadict.html for how to resolve the issue.
2023-07-29T10:37:29.023758+08:00 2 [ERROR] InnoDB: Operating system error number 2 in a file operation.
2023-07-29T10:37:29.023774+08:00 2 [ERROR] InnoDB: The error means the system cannot find the path specified.
2023-07-29T10:37:29.023783+08:00 2 [ERROR] InnoDB: Could not find a valid tablespace file for `db3/t1`. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting-datadict.html for how to resolve the issue.

Question:
Existing problems:
Description When the t1 table was deleted, the client received a feedback indicating that the operation succeeded. The error log contains relevant information, but does not indicate that the t1.ibd file has not been deleted, which the user will think is deleted.
"ERROR 1813 (HY000): Tablespace 'testdb'.'t1' exists" is received only when the user next creates the same table. However, this may cause an exception in the application system.