Upgrading from 2.019 with a load of mods to 2.1.3 and I get the above error on upgrade.php
Not sure what it means but have ftp'd all the way back to the 2.0.19 and forum running fine
Tried again after uninstalling a few possible mod conflicts (Tapatalk? Old SimplePortal on Portal mode) and same thing.
Any clues as to what to try? repair.settings.php? I'fd like to upgarde even though it means a load of changes and revamps for me.
Thanks all...
Peter
You need to post the table name logged in the error.
Quote from: willerby on April 17, 2023, 02:27:58 PMUpgrading from 2.019 with a load of mods to 2.1.3
Regarding this, most 2.0.x mods will not work on 2.1.x. I think it would be better to uninstall them first and then perform the upgrade. It might be that a table of a custom mod might have cause the error.
Quote from: Doug Heffernan on April 17, 2023, 02:37:29 PMYou need to post the table name logged in the error.
He did.
priv_check is a table created during installation to verify that the database user doing the upgrade has permission to create tables, alter tables and drop tables, since all of these will be needed during upgrade.
Whatever database user you're using is missing one or more of the permissions on the database in question, whether that's CREATE TABLE, ALTER TABLE or DROP TABLE. Depending on how you have the server setup (i.e. cPanel, Plesk or something else) we might be able to guide you in giving the relevant user the relevant privileges.
NB: this is not your *forum* user account. It's specifically the one mentioned in Settings.php that enables PHP to talk to the database directly.
Going a bit further - the reason for my sudden desire to upgrade has been a couple of database crashes on the messages table - unsure of the reason as the forum has not chnaged for a year or so and had been fine for 13 years with upgrades from SMFF 1.08 all the way through.
It looks like the error is appended (errno: 190 - Operation not allowed when innodb_forced_recovery > 0) which I think might be something the hosting service (Bluehost) has messed with in order to fix the crashed table. Does that make more sense? Do they need to fix that before i can go any further?
Thanks all
Definitely sounds like a question to put to them.
The email is winging its way too them. Six days the last one took to get an acknowledgement. I think we'll stick with 2.019 a while longer ::)
Good to see you are still around Arantor... quite a few other old hands still here too. You all deserve huge credit and thanks.
TTFN
Apologies this may now be an SMF 2.0.19 support issue although it has been highlighted during attempts to upgrade.
This is the response from Bluehost (edited)
---
I did not turn force_innodb_recovery as apart of my process.
So I tried to set the recovery mode to zero/off. Sometimes these things get left on by accident. But on startup I got this error with an immediate shutdown and could not restart until I went back into recovery mode.
2023-04-17 12:52:07 7fb513f8b7e0 InnoDB: Error: table 'xxxxxcom_Forum/xxxxx_log_search_words' in InnoDB data dictionary has tablespace id 219163, but a tablespace with that id does not exist. There is a tablespace of name xxxxxcom_Forum/xxxxx_log_search_words and id 219370, though.
Have you deleted or moved .ibd files? (I have not)
I went looking for the missing file and what happened to it. And found it:
[email protected] [xxxxxcom]# ls -lah /var/lib/mysql/xxxxx_Forum/xxxxx_log_search_word*
-rw-rw---- 1 mysql mysql 64K Jul 8 2021 /var/lib/mysql/xxxxxcom_Forum/xxxxx_log_search_words.ibd
-rw-rw---- 1 mysql mysql 388M Jun 25 2021 /var/lib/mysql/xxxxxcom_Forum/xxxxx_log_search_words.ibd.bak
Looks like someone renamed this file back in June/July 2021 (?)
124 2021-07-08 10:07:31 mv xxxxx_log_search_words.ibd{,.bak}
And it looks like the server was running in recovery mode ever since then since. At this point I need to stop work because this beyond the scope of our support. Here it looks like someone attempted some sort of fix to get mysql running at the time, but the ibd table should not have been renamed like this. We simply do not provide this level of data recovery for database issues.
Since you do have a currently working database in the previous mode I went ahead and took a full backup of the database in its current working state.To be clear. Recovery mode =2 was turned on again so mysql is online. The database is unaltered from our previous interaction.
Since we're at this point I did also make a pre-repair copy of the data directory for mysql. Normally I would not mention it but it might be useful to data recovery expert.
-rw-r--r-- 1 root root 358M Apr 17 06:31 /root/xxxxxcom_Forum.sqldatadir.tar.gz
That said: I don't recommend moving ibd file as innodb files cannot be moved/copied on their own like it was here back in 2021. Unless the innodb log syncs up with it you'll run into the same error and possibly cause other/more problems. Since it looks like some one may have altered the files back in 2021
85 2021-06-26 11:48:03 mv ib_logfile0 ib_logfile0.old
86 2021-06-26 11:48:10 mv ib_logfile1 ib_logfile1.old
99 2021-07-05 17:00:05 mv ib_logfile0 newibfiles/
100 2021-07-05 17:00:14 mv ib_logfile1 newibfiles/
103 2021-07-05 17:00:38 mv ib_logfile0{.old,}
104 2021-07-05 17:00:43 mv ib_logfile1{.old,}
That said: what to do now: You've got a few reasonable options. But again, these are not things we could do for you, support or provide. I strongly recommend consulting with a mysql expert or possibly the support for your forum software and using these options as a starting point for discussion. Do not proceed with any of these options on their own without consulting with an expert.
1. Contact a data recovery expert to look into this. This is what I recommend if you're unsure. Alternatively contact a developer familiar with the forum software to see what the impact of xxxxx_log_search_word being missing would mean. A data recovery expert could possible save that table's data and reintegrate it into the forum's database cleanly. Alternatively an expert in the forum software could tell you if you could just do without that table or import a default state of that table after dropping it.
OR
2. Attempt to use the backup i made in a new database to see if it is a valid backup/working To be clear I am not an expert in the forum software and am not sure of how well this would work. Proceed at your own risk. The dump does not contain data for the table xxxxx_log_search_word because its crashed..
OR
3. Since you're upgrading forum software, maybe go with a fresh start. This would mean dropping the existing database to get the system in a working state again. The downside to this is losing the existing forum including posts, users and content on the forum. But you would have a stable working system. (Not an option!)
Based upon the issue highlighted - how should I proceed? This is a 14 year old forum with over 250,000 posts and 10,000 users so I'm keen to get back to a position where I can allow users back on and upgrade to the latest version.What's the safest course of action here?
Many thanks for your expertise and assistance
Peter
Well, for one, start looking for a new host.
If your 2.0.19 forum is working properly, then delete all logs and the search index (using smf admin)
Then try again
Go into the search settings, into search method, turn off the custom index, then tell it to remove the custom index. This will remove log_search_words entirely. This will let your host do whatever they need to to get it running in non-recovery mode. (This is the only one you *need* to do for this specific situation.)
This is actually an even better plan in practice because removing the custom index will make the upgrade go faster - you can rebuild it after the upgrade is complete. Removing other logs is optional but also will make the upgrade go faster.
Thanks both.
I am feeling somewhat guilty. I had a mini-stroke in 2020 and my memory is baaad. But something made me think I'd had some of this chat before...
https://www.simplemachines.org/community/index.php?topic=578085.msg4091442#msg4091442
Oops. Because it was working fine I just ignored the suggested course of action back in 2021. But at least I can prove it was Bluehost that did all these iinodb changes. :)
Well, the reality is you're doing the same steps - just a little later than originally anticipated.
I guess. ???
The Custom Index 'tablespace' can't be removed - there is no custom index. When I try and create one I still get the error I did in 2021
Tablespace for table '`lrsoccom_Forum`.`lrsoc_log_search_words`' exists. Please DISCARD the tablespace before IMPORT.
File: /home/lrsoccom/public_html/forum/Sources/ManageSearch.php
Line: 566
Given that I have now had two instances of the messages table crashing and being unable to repair itself (with the innodb set at 2) and I can't upgrade due to the same innodb issue. I suspect the only possible solution is that proposed by Lex back in 2021. The one I conveniently forgot as it sounded rather risky and everything seemed to be working fine with the search index turned off.
Hmmmm.
1) Put SMF in maintenance mode 2 ( Untouchable ) through editing settings.php
2) Make backups of everything for yourself as a precaution.
3) Dump the current database contents to a file, create a new database with a different name, import the exported data to the new database
4) Reconfigure SMF to use the new DB and take it out of maintenance mode, using repair_settings.php, and check that everything works as it should
5) Remove the old broken database.
He was under the impression that if you need to start MySQL with innodb_force_recovery the database is likely already permanently corrupt and needs to be rebuilt. Fingers crossed...
It's just a different route to the same general solution: the net result is that you end up with a tablespace that doesn't have log_search_words in it.
As a general rule if you're in recovery mode with InnoDB something has gone badly wrong (unlike MyISAM), but I wouldn't be surprised if the problem with the messages table crashing is directly the result of whatever happened to the log_search_words table, and that a complete export/reimport is not a bad idea at this point.
The key thing about these steps is that they're actually safe - you're not changing or cutting up the existing database, you're making a new one from the existing one and switching over to that.
Sorry... brain fatigue...
4) Reconfigure SMF to use the new DB and take it out of maintenance mode, using repair_settings.php, and check that everything works as it should
When the new DB is available, is this as simple as editing Settings.php with the relevant connection info?
Yes, that's all you need to do to repoint SMF to a new database.
Huge thank you once again to the SMF folks for keeping this thing going.
Yet again you've helped me fix something that looked impossible (to me) at first glance. The new database without the search_words tablespace issuecis working just fine and innodb = 0 no longer causes an immediate crash enabling me to upgrade.
Thanks all, it is really appreciated.
W
Apologies, my post re https://www.simplemachines.org/community/index.php?topic=585663.0 was premature :(
On running upgrade.php I now have the following error
Tablespace for table '`xxxxxcom_forumv2`.`xxxxx_priv_check`' exists. Please DISCBased upon Arantor's comments in the previous topic -
Quotepriv_check is a table created during installation to verify that the database user doing the upgrade has permission to create tables, alter tables and drop tables, since all of these will be needed during upgrade.
it would seem my first attempt to upgrade which had to be aborted due to the innodb>0 issue has left some tablespace behind in the tables copied across for my new database. Advice on how to DISC?
Many thanks
Peter
Well, you don't want the table there - can you drop the table?
There is no priv_check table listed...
This issue appears to be described here but unfortunately I am insufficiently blessed to understand it :(
https://stackoverflow.com/questions/15694168/error-tablespace-for-table-xxx-exists-please-discard-the-tablespace-before-imp
Argh, I see the problem you're having.
The real question is if you have access to the underlying files on the server or not. If you do, it'll be a case of going spelunking to the database directory, if not your host will have to get involved.
I'm a bit surprised it got exported as part of a dump in the first place though.
Ultimately: a table is made up of several files. You seem to have the file that indicates where the data would go if there were a table, but not the file that contains the table structure.
Back to the host. I've outlined the issue as best I can in an email to them.
It would be nice to complete this upgrade and start rebuilding my forum.
Bluehost support very quickly moved the priv_check file - it appeared to be an orphaned ibd file (?) - so I cracked on with the upgrade routine.
Initially looked good... then bad... then good. On running upgrade.php, no longer had any tablespace error messages and could log-in to start the process.
However, the first options screen had red warnings re script.js being missing or out of date and after checking repair_settings.php I took a look again. It was all there and was the file downloaded from the upgrade zip folder (?) I decided to proceed anyway. I'm good at ignoring stuff I don't understand.
After successful backup the database changes started but highlighted an 'Unknown Error' - see screenshot - and then appeared to stop on 5 of 71 - legacy attachments. However, even though it says 'Database Updates complete, tick continue to proceed' - the button was greyed out and I noticed that the % progresses referred to were still going up but hidden under there respective text heading - see image top left (Overall and Step Progress). This is probably just a browser (Firefox) widescreen issue.
The database changes apparently completed successfully and I was rewarded with a functioning SMF 2.0.3 forum - complete with its 280,000 posts.
I have no idea whether the errors highlighted will come back to haunt me so if anyone can suggest any checks to do - beyond the usual forum is working one - could you let me know?
Many thanks for the great support, particularly Arantor - again. Hoepfully I am away now to get back some mods and redevelop my theme.
Peter
Yeah, the orphan ibd is what I alluded to earlier - every table in a database (normally) is made up of several files. There's a .frm file for its structure, .myd or .ibd for its data, .myi for indexes and so on.
Since the name of this file is the table's name (so, smf_priv_check.ibd or whatever), it can see if there is an ibd file without a matching frm, which is what it was complaining about.
script.js being out of date on upgrade is not usually a deal breaker as long as the upgrade's files were applied over the top of the regular files completely (so, everything in Sources and everything in Themes/default and all the subfolders)
As far as attachments are concerned, go find a topic with attachments, are they visible? My best guess without further digging is that the rename step wasn't carried out, assuming the attachments folder was correct in the settings (all attachments in 2.1 have a .dat extension now to avoid FileZilla messing with them)
Yep, you were right. ::)
Now I could ask what to do about that but I thought I'd do a quick search and I found that my attachments settings had mysteriously been set to Array. Now I know that was not the case before I upgraded as I did a conscientious repair_settings.php and it was correctly set to /attachments. Oh well.
Anyway, I ran repair_settings.php again and reset it to the correct folder then ran upgrade.php again, checking the attachments upgrade in options (again).
And all good! ;)
Topic finally solved.