phpBB 3.3.4 Native Fulltext index wont delete

Get help with installation and running phpBB 3.3.x here. Please do not post bug reports, feature requests, or extension related questions here.
Post Reply
exxos
Registered User
Posts: 124
Joined: Fri Sep 10, 2010 12:37 pm

phpBB 3.3.4 Native Fulltext index wont delete

Post by exxos »

Been having problems with the SQL FTS_ files as some files are there but all the index files are missing. I changed the search engine to the SQL search and created the search index and now there are a lot more FTS_ files and all seems correct now.

BUT, the phpBB Native Fulltext index wont delete. When I click delete index, a progress bar pops up for a couple of seconds, then back in the ACP it says sucessfully deleted. But it still seems to be there as the values are still a number, not zero, and the create index is not there, it still says delete index on the button.

I've had this problem before and not sure what corrected the problem. Might have been a server reboot or just a lot of time passed before the index was actually deleted in the acp.

So I'm not sure is there is a bug somewhere or not ?
Last edited by exxos on Sun Jul 03, 2022 12:06 pm, edited 1 time in total.
User avatar
Mick
Support Team Member
Support Team Member
Posts: 26502
Joined: Fri Aug 29, 2008 9:49 am

Re: phpBB 3.3.3 Native Fulltext index wont delete

Post by Mick »

See if purging the cache makes any difference.
  • "The more connected we get the more alone we become" - Kyle Broflovski©
  • "The good news is hell is just the product of a morbid human imagination.
    The bad news is, whatever humans can imagine, they can usually create.
    " - Harmony Cobel
exxos
Registered User
Posts: 124
Joined: Fri Sep 10, 2010 12:37 pm

Re: phpBB 3.3.4 Native Fulltext index wont delete

Post by exxos »

Mick wrote: Sun Jul 03, 2022 9:19 am See if purging the cache makes any difference.
Tried it many times.

Oddly when creating a new search index, it generates more FTS files which mysql complains some are missing.

I did try just deleting some of the FTS files thinking they are just parts of the old search index, but when I did a post on the forum, it crashed with mysql errors.

I will have to look at the phpbb code to see exactly what its doing when I get chance.
Last edited by exxos on Sun Jul 03, 2022 12:07 pm, edited 1 time in total.
exxos
Registered User
Posts: 124
Joined: Fri Sep 10, 2010 12:37 pm

Re: phpBB 3.3.4 Native Fulltext index wont delete

Post by exxos »

This is what is new in the error log after creating the MYSQL SEARCH index within ACP.

Code: Select all

2022-07-03T10:48:28.612349Z 1 [Warning] [MY-012351] [InnoDB] Tablespace 2687, name 'forum/fts_000000000000104c_0000000000001728_index_1', file './forum/fts_000000000000104c_0000000000001728_index_1.ibd' is missing!
2022-07-03T10:48:28.612460Z 1 [Warning] [MY-012351] [InnoDB] Tablespace 2688, name 'forum/fts_000000000000104c_0000000000001728_index_2', file './forum/fts_000000000000104c_0000000000001728_index_2.ibd' is missing!
2022-07-03T10:48:28.612622Z 1 [Warning] [MY-012351] [InnoDB] Tablespace 2689, name 'forum/fts_000000000000104c_0000000000001728_index_3', file './forum/fts_000000000000104c_0000000000001728_index_3.ibd' is missing!
2022-07-03T10:48:28.612770Z 1 [Warning] [MY-012351] [InnoDB] Tablespace 2690, name 'forum/fts_000000000000104c_0000000000001728_index_4', file './forum/fts_000000000000104c_0000000000001728_index_4.ibd' is missing!
2022-07-03T10:48:28.612892Z 1 [Warning] [MY-012351] [InnoDB] Tablespace 2691, name 'forum/fts_000000000000104c_0000000000001728_index_5', file './forum/fts_000000000000104c_0000000000001728_index_5.ibd' is missing!
2022-07-03T10:48:28.613009Z 1 [Warning] [MY-012351] [InnoDB] Tablespace 2692, name 'forum/fts_000000000000104c_0000000000001728_index_6', file './forum/fts_000000000000104c_0000000000001728_index_6.ibd' is missing!
2022-07-03T10:48:28.613109Z 1 [Warning] [MY-012351] [InnoDB] Tablespace 2693, name 'forum/fts_000000000000104c_000000000000172f_index_1', file './forum/fts_000000000000104c_000000000000172f_index_1.ibd' is missing!
2022-07-03T10:48:28.613203Z 1 [Warning] [MY-012351] [InnoDB] Tablespace 2694, name 'forum/fts_000000000000104c_000000000000172f_index_2', file './forum/fts_000000000000104c_000000000000172f_index_2.ibd' is missing!
2022-07-03T10:48:28.613306Z 1 [Warning] [MY-012351] [InnoDB] Tablespace 2695, name 'forum/fts_000000000000104c_000000000000172f_index_3', file './forum/fts_000000000000104c_000000000000172f_index_3.ibd' is missing!
2022-07-03T10:48:28.613415Z 1 [Warning] [MY-012351] [InnoDB] Tablespace 2696, name 'forum/fts_000000000000104c_000000000000172f_index_4', file './forum/fts_000000000000104c_000000000000172f_index_4.ibd' is missing!
2022-07-03T10:48:28.613524Z 1 [Warning] [MY-012351] [InnoDB] Tablespace 2697, name 'forum/fts_000000000000104c_000000000000172f_index_5', file './forum/fts_000000000000104c_000000000000172f_index_5.ibd' is missing!
2022-07-03T10:48:28.613621Z 1 [Warning] [MY-012351] [InnoDB] Tablespace 2698, name 'forum/fts_000000000000104c_000000000000172f_index_6', file './forum/fts_000000000000104c_000000000000172f_index_6.ibd' is missing!
2022-07-03T10:48:29.563477Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2022-07-03T10:48:29.563561Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2022-07-03T10:48:29.708149Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '127.0.0.1' port: 33060, socket: /var/run/mysqld/mysqlx.sock
2022-07-03T10:48:29.708304Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.29'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  MySQL Community Server - GPL.
2022-07-03T10:50:00.951905Z 109 [ERROR] [MY-012592] [InnoDB] Operating system error number 2 in a file operation.
2022-07-03T10:50:00.951993Z 109 [ERROR] [MY-012593] [InnoDB] The error means the system cannot find the path specified.
2022-07-03T10:50:00.952012Z 109 [ERROR] [MY-012216] [InnoDB] Cannot open datafile for read-only: './forum/fts_000000000000104c_0000000000001728_index_1.ibd' OS error: 71
2022-07-03T10:50:00.955325Z 109 [ERROR] [MY-012592] [InnoDB] Operating system error number 2 in a file operation.
2022-07-03T10:50:00.955379Z 109 [ERROR] [MY-012593] [InnoDB] The error means the system cannot find the path specified.
2022-07-03T10:50:00.955391Z 109 [ERROR] [MY-012216] [InnoDB] Cannot open datafile for read-only: './forum/fts_000000000000104c_0000000000001728_index_2.ibd' OS error: 71
2022-07-03T10:50:00.958614Z 109 [ERROR] [MY-012592] [InnoDB] Operating system error number 2 in a file operation.
2022-07-03T10:50:00.958702Z 109 [ERROR] [MY-012593] [InnoDB] The error means the system cannot find the path specified.
2022-07-03T10:50:00.958723Z 109 [ERROR] [MY-012216] [InnoDB] Cannot open datafile for read-only: './forum/fts_000000000000104c_0000000000001728_index_3.ibd' OS error: 71
2022-07-03T10:50:00.960474Z 109 [ERROR] [MY-012592] [InnoDB] Operating system error number 2 in a file operation.
2022-07-03T10:50:00.960541Z 109 [ERROR] [MY-012593] [InnoDB] The error means the system cannot find the path specified.
2022-07-03T10:50:00.960554Z 109 [ERROR] [MY-012216] [InnoDB] Cannot open datafile for read-only: './forum/fts_000000000000104c_0000000000001728_index_4.ibd' OS error: 71
2022-07-03T10:50:00.961822Z 109 [ERROR] [MY-012592] [InnoDB] Operating system error number 2 in a file operation.
2022-07-03T10:50:00.961859Z 109 [ERROR] [MY-012593] [InnoDB] The error means the system cannot find the path specified.
2022-07-03T10:50:00.961869Z 109 [ERROR] [MY-012216] [InnoDB] Cannot open datafile for read-only: './forum/fts_000000000000104c_0000000000001728_index_5.ibd' OS error: 71
2022-07-03T10:50:00.963325Z 109 [ERROR] [MY-012592] [InnoDB] Operating system error number 2 in a file operation.
2022-07-03T10:50:00.963399Z 109 [ERROR] [MY-012593] [InnoDB] The error means the system cannot find the path specified.
2022-07-03T10:50:00.963417Z 109 [ERROR] [MY-012216] [InnoDB] Cannot open datafile for read-only: './forum/fts_000000000000104c_0000000000001728_index_6.ibd' OS error: 71
2022-07-03T10:50:00.966618Z 109 [ERROR] [MY-012592] [InnoDB] Operating system error number 2 in a file operation.
2022-07-03T10:50:00.966983Z 109 [ERROR] [MY-012593] [InnoDB] The error means the system cannot find the path specified.
2022-07-03T10:50:00.967021Z 109 [ERROR] [MY-012216] [InnoDB] Cannot open datafile for read-only: './forum/fts_000000000000104c_000000000000172f_index_1.ibd' OS error: 71
2022-07-03T10:50:00.968996Z 109 [ERROR] [MY-012592] [InnoDB] Operating system error number 2 in a file operation.
2022-07-03T10:50:00.969071Z 109 [ERROR] [MY-012593] [InnoDB] The error means the system cannot find the path specified.
2022-07-03T10:50:00.969084Z 109 [ERROR] [MY-012216] [InnoDB] Cannot open datafile for read-only: './forum/fts_000000000000104c_000000000000172f_index_2.ibd' OS error: 71
2022-07-03T10:50:00.970576Z 109 [ERROR] [MY-012592] [InnoDB] Operating system error number 2 in a file operation.
2022-07-03T10:50:00.970634Z 109 [ERROR] [MY-012593] [InnoDB] The error means the system cannot find the path specified.
2022-07-03T10:50:00.970652Z 109 [ERROR] [MY-012216] [InnoDB] Cannot open datafile for read-only: './forum/fts_000000000000104c_000000000000172f_index_3.ibd' OS error: 71
2022-07-03T10:50:00.972481Z 109 [ERROR] [MY-012592] [InnoDB] Operating system error number 2 in a file operation.
2022-07-03T10:50:00.972549Z 109 [ERROR] [MY-012593] [InnoDB] The error means the system cannot find the path specified.
2022-07-03T10:50:00.972561Z 109 [ERROR] [MY-012216] [InnoDB] Cannot open datafile for read-only: './forum/fts_000000000000104c_000000000000172f_index_4.ibd' OS error: 71
2022-07-03T10:50:00.973970Z 109 [ERROR] [MY-012592] [InnoDB] Operating system error number 2 in a file operation.
2022-07-03T10:50:00.974024Z 109 [ERROR] [MY-012593] [InnoDB] The error means the system cannot find the path specified.
2022-07-03T10:50:00.974035Z 109 [ERROR] [MY-012216] [InnoDB] Cannot open datafile for read-only: './forum/fts_000000000000104c_000000000000172f_index_5.ibd' OS error: 71
2022-07-03T10:50:00.975905Z 109 [ERROR] [MY-012592] [InnoDB] Operating system error number 2 in a file operation.
2022-07-03T10:50:00.975969Z 109 [ERROR] [MY-012593] [InnoDB] The error means the system cannot find the path specified.
2022-07-03T10:50:00.975981Z 109 [ERROR] [MY-012216] [InnoDB] Cannot open datafile for read-only: './forum/fts_000000000000104c_000000000000172f_index_6.ibd' OS error: 71
2022-07-03T10:54:51.544368Z 0 [System] [MY-013172] [Server] Received SHUTDOWN from user <via user signal>. Shutting down mysqld (Version: 8.0.29).
2022-07-03T10:54:53.553413Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 12  user: 'mailuser'.
2022-07-03T10:54:54.580257Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.29)  MySQL Community Server - GPL.
2022-07-03T10:54:58.411004Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.29) starting as process 165482
If I "touch" the files, the errors stop happening, but the files remain zero file sizes. I don't think its really a fix.

Capture.PNG
exxos
Registered User
Posts: 124
Joined: Fri Sep 10, 2010 12:37 pm

Re: phpBB 3.3.4 Native Fulltext index wont delete

Post by exxos »

I hacked the ACP page in Chrome debug console to get the create button. It ran though, said completed. Tried to delete the index and still won't do it.
exxos
Registered User
Posts: 124
Joined: Fri Sep 10, 2010 12:37 pm

Re: phpBB 3.3.4 Native Fulltext index wont delete

Post by exxos »

Oddly after leaving it for a few hours the search indexes now shows zero. So I deleted the FTS files from the mysql folder. Before it seemed to stop the forum from posting, it came up mysql error before. But its not doing it now the search index is actually removed.

Now it is creating the search index.. Will see if it behaves this time around...
exxos
Registered User
Posts: 124
Joined: Fri Sep 10, 2010 12:37 pm

Re: phpBB 3.3.4 Native Fulltext index wont delete

Post by exxos »

The search function still works fine. Though since changing database again, I've ended up with more FTS_ files "missing". Again I had to create blank files to stop mysql erroring.

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. Will just have to leave it as is and not touch the search indexes or change search backends again.

Capture.PNG
Capture.PNG

Code: Select all

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!
exxos
Registered User
Posts: 124
Joined: Fri Sep 10, 2010 12:37 pm

Re: phpBB 3.3.4 Native Fulltext index wont delete

Post by exxos »

Annoyingly after having the blank files in place and restarting the server the errors seem to have come back again :roll:

I mentioned this as a possible bug report for the mysql stuff, said they cannot replicate it and it's not up to them to troubleshoot phpbb stuff.

Code: Select all

2022-07-03T23:00:02.731399Z 652 [Warning] [MY-012111] [InnoDB] Trying to access missing tablespace 2648
2022-07-04T01:00:01.163646Z 5142 [Warning] [MY-012111] [InnoDB] Trying to access missing tablespace 2648
2022-07-04T03:00:02.047244Z 9310 [Warning] [MY-012111] [InnoDB] Trying to access missing tablespace 2648
2022-07-04T05:00:01.420615Z 13554 [Warning] [MY-012111] [InnoDB] Trying to access missing tablespace 2648
2022-07-04T07:00:01.329531Z 17852 [Warning] [MY-012111] [InnoDB] Trying to access missing tablespace 2648
2022-07-04T09:00:01.112377Z 22096 [Warning] [MY-012111] [InnoDB] Trying to access missing tablespace 2648
2022-07-04T09:01:18.611837Z 22178 [Warning] [MY-012111] [InnoDB] Trying to access missing tablespace 2648
2022-07-04T09:03:12.095056Z 22263 [Warning] [MY-012111] [InnoDB] Trying to access missing tablespace 2648
2022-07-04T09:05:11.219171Z 22355 [Warning] [MY-012111] [InnoDB] Trying to access missing tablespace 2648
2022-07-04T09:06:00.570955Z 22405 [Warning] [MY-012111] [InnoDB] Trying to access missing tablespace 2648
2022-07-04T11:00:01.950867Z 28097 [Warning] [MY-012111] [InnoDB] Trying to access missing tablespace 2648
Table entry is actually there the blank files I created.

2.PNG

So I don't know why the the errors are back or what is actually triggering them sporadically. I can only assume it is when someone does a search on the forum but I cannot reliably replicate generating errors in the logs this way.

All this SQL stuff is beyond me. Maybe if more people check their SQL logs ( /var/log/mysql ) it may (or may not) be a common issue.
exxos
Registered User
Posts: 124
Joined: Fri Sep 10, 2010 12:37 pm

Re: phpBB 3.3.4 Native Fulltext index wont delete

Post by exxos »

A conclusion finally.

Aside from strange issues of not being able to delete the search index, its creating indexes causes those FTS enteries to be created but not any actual FTS files No idea why. Hardly any information on the internet about it all.

Long story short, its mysqldump which triggers the errors. I assume when its backing up, its trying to read the FTS files as they are stated in the SQL database. But they are not there.

As no responses to this topic, I'm just having to assume its some bug in phpbb as creating the indexes causes basically invalid entries in the database which mysqldump finds during backups.
Post Reply

Return to “[3.3.x] Support Forum”