Bug #107740 Table space missing
Submitted: 3 Jul 2022 23:35 Modified: 4 Jul 2022 12:14
Reporter: Chris Swinson Email Updates:
Status: Can't repeat Impact on me:
Category:MySQL Server Severity:S3 (Non-critical)
Version:8.0.29 OS:Ubuntu (20.04)
Assigned to: CPU Architecture:Any

[3 Jul 2022 23:35] Chris Swinson
I run a phpbb forum where I can create search index files. Though if I delete or rebuild that index then I start getting errors in the SQL log for missing files.

These entries seem to stem from information_schema. I have no idea what these file are doing or how its ending in a mess with creating / deleting search indexes. It seems like once a search index gets deleted, mysql doesn't remove the entries in information_schema and it starts causing errors for missing tablespaces. Which the only solution is to create a blank file so it doesn't error. I dare not mess about with information_schema itself. So now I am unsure if this is a bug in phpbb or mysql or this is supposed to be normal somehow. All beyond my skill set in anycase.

I open this bug report as there doesn't seem any solution to cleaning up old FTS_ filenames from  information_schema. Nothing seems to he broken and creating the empty FTS_ files stops the errors.But I am stuck as to why it ends up broken like this in the first place. I've opened a topic on the phpbb forum but no replies. 

I don't know if phpbb is creating FTS files then deleting them. But I never see any new files created in the SQL folder. But these FTS filenames must be generated somewhere to get into information_schema in the first place ? While there are files listed they never get created unless I created the files manually, its the only way to stop mysql erroring. 

So I assume there is some bug somewhere ? At least there seems no fix for these missing files issues anyway. I'm getting a rather long list of empty FTS files currently to stop the OS errors 71 missing table space errors.

2022-07-03T22:28:31.715099Z 1 [Warning] [MY-012351] [InnoDB] Tablespace 2746, name 'forum/fts_000000000000104c_0000000000001791_index_1', file './forum/fts_000000000000104c_0000000000001791_index_1.ibd' is missing!
2022-07-03T22:28:31.715163Z 1 [Warning] [MY-012351] [InnoDB] Tablespace 2747, name 'forum/fts_000000000000104c_0000000000001791_index_2', file './forum/fts_000000000000104c_0000000000001791_index_2.ibd' is missing!
2022-07-03T22:28:31.715190Z 1 [Warning] [MY-012351] [InnoDB] Tablespace 2748, name 'forum/fts_000000000000104c_0000000000001791_index_3', file './forum/fts_000000000000104c_0000000000001791_index_3.ibd' is missing!
2022-07-03T22:28:31.715215Z 1 [Warning] [MY-012351] [InnoDB] Tablespace 2749, name 'forum/fts_000000000000104c_0000000000001791_index_4', file './forum/fts_000000000000104c_0000000000001791_index_4.ibd' is missing!
2022-07-03T22:28:31.715241Z 1 [Warning] [MY-012351] [InnoDB] Tablespace 2750, name 'forum/fts_000000000000104c_0000000000001791_index_5', file './forum/fts_000000000000104c_0000000000001791_index_5.ibd' is missing!
2022-07-03T22:28:31.715263Z 1 [Warning] [MY-012351] [InnoDB] Tablespace 2751, name 'forum/fts_000000000000104c_0000000000001791_index_6', file './forum/fts_000000000000104c_0000000000001791_index_6.ibd' is missing!

How to repeat:
Create and delete a search index in phpbb forum.

Suggested fix:
Automatically remove missing FTS file entries in information_schema if there are no physical files in the mysql folder.
[3 Jul 2022 23:48] Chris Swinson

Attachment: Capture.PNG (image/png, text), 136.60 KiB.

[4 Jul 2022 12:14] MySQL Verification Team
Hi Mr. Swinson,

Thank you for your bug report.

We have tried to repeat your problem , but with no success.

We have created, used and dropped full text indices on InnoDB table and it worked perfectly. We restarted our server and there are no warnings in our error log, what so ever.

One should use only SQL commands to manipulate with any object in the data directory.

Last, but not least, we do not process bugs for the product that you are using. We only process bugs with repeatable test cases, containing only SQL commands.

Can't repeat.