SMF 2.0 RC2 Upgrading waiting at log_search_subjects - Step 6: Converting to UTF

[Sudhakar Arjunan]

SMF 2.0 RC2 Upgrading waiting  - Step 6: Converting to UTF - for several house at log_search_subjects

After a long time, i have decided to upgrade 2.1 RC2 from github as it was 98% completion except few policies and gdpr information's.
Even i have waited for 2 years for an upgrade to 2.1 Final Stable which may take some time now.

Everything went good on upgrade from 2.1 Beta 1 to 2.1 RC2.
Struck at the same issue as this topic, where in Step 6: Converting to UTF-8

Shows completed 128 out of 224 tables.
Current Table: "log_search_subjects"

Where smf_log_search_words has -5,477,880 rows - type - InnoDB - Size - 150 mb

Time Elapsed : 331 Mins, 25 seconds. - while posting in other reply
Time Elapsed : 708 Mins, 14 seconds. - now after 12 hours of upgrade on a dedicated server which has ssd , 64 gb ram , 40 core processor with cloud linux uses myisam ( mysql ) with php myadmin.

[Arantor]

Making a new topic isn't going to get an answer quicker...

https://www.simplemachines.org/community/index.php?topic=572888.msg4054181#msg4054181

[vbgamer45]

I saw this in another post backup up your tables first
https://www.simplemachines.org/community/index.php?topic=572888.msg4053543;topicseen#msg4053543

But you can truncate log_search_* tables then rebuild the index from what it looks like.

[Sudhakar Arjunan]

Making a new topic isn't going to get an answer quicker...

https://www.simplemachines.org/community/index.php?topic=572888.msg4054181#msg4054181

Waiting for 6 hours before creating a new one , am sorry.

[Sudhakar Arjunan]

I saw this in another post backup up your tables first
https://www.simplemachines.org/community/index.php?topic=572888.msg4053543;topicseen#msg4053543

But you can truncate log_search_* tables then rebuild the index from what it looks like.

Thank you for the knowledge, can you tell me how do i code that on upgrade.php to just skip only log_search_tables instead of truncating them.
Or do you think cut the table until the upgrade is completed.

[vbgamer45]

You can truncate the tables via phpmyadmin Always backup first! Easy way.

I don't suggest modifying upgrade system to skip that upgrade.

[Sudhakar Arjunan]

You can truncate the tables via phpmyadmin Always backup first! Easy way.

I don't suggest modifying upgrade system to skip that upgrade.

Sure i will try it then, but if i truncate. Will the upgrader does not wait for that table. Are we sure.

[vbgamer45]

It should just go though. I think it is a size issue.

[shawnb61]

I would just rerun the upgrader.  It is safe to rerun it. 

5M rows in log_search_words is not very big at all.  The utf8 conversion should just take a few minutes.  It appears the server quit for some reason.  I would search all the error/apache logs very carefully for clues. 

In my experience, rebuilding the search index takes ***much*** longer than the UTF8 conversion...   (On my forum, with ~12M rows & 325Mb in log_search_words, the utf8 conversion step for the entire forum takes ~3 minutes.  I have no idea how long rebuilding the search index takes - when I have tried that, I have had to let it run overnight...)

Note that the utf8 conversion is the very last step.  If a rerun does not work, you could complete the utf8 conversion manually.  The SMF upgrader uses a very safe method to convert to UTF8, though, that takes extra measures to avoid double-encoding...  I would only do it by hand after a couple more upgrader attempts.  If you do it by hand, you must also set the $db_character_set and global_character_set settings manually. 

[Sudhakar Arjunan]

HI Shawnb61, thank you for the detailed explanation. Waited for so much time and took a backup of smf_log_search_words in to other database and renamed the table log_search_words so the upgrader skipped the table as per vbgamer45's advice

Time Elapsed: 865 mins, 52 seconds. ( more than 14 hours spent with 4-5 refresh every 2-3 hours ).

Converting to UTF8
Please wait while your database is converted to UTF-8. For large forums this may take some time!
Completed 128 out of 223 tables.
Current Table: "log_search_subjects"

I hope smf_log_search_subjects has only 9 lakh rows and 30 mb sql file table. Let me wait and see if now, i will check for error / apache logs for any clues and try to increase db run time size to monitor its happening and report here shawnb61.

For community reference, added before and after screenshot attached.

thank you , lets hope the for the best.

[Kindred]

Making a new topic isn't going to get an answer quicker...

https://www.simplemachines.org/community/index.php?topic=572888.msg4054181#msg4054181

Waiting for 6 hours before creating a new one , am sorry.

well, as it turns out, you posted in someone else's thread, which was already marked as solved.

However, "waiting 6 hours"??    We're not a real-time support site.   We're all volunteers. We ask that people wait AT LEAST 24 hours before even BUMPING a post, let alone posting a new one.

[shawnb61]

Those are tiny tables.  5M rows is nothing for log_search_words (it loosely corresponds to words used everywhere; log_search_subjects loosely translates to a count of distinct words used). 

There is something else wrong.  This should be done in minutes, seriously. 

Some random thoughts, if you are still experiencing issues:

1.  DB is not configured for InnoDB?  What are the InnoDB buffer pool sizes?

2.  Permissions?  Does your DB account have all permissions granted to do everything to the DB?  There is a lot of table manipulation going on. 

3.  Something in the content is really confusing?  What are the collations for the log_search_* tables before starting?  What are the primary languages used in the forum?  If you browse log_search_words and log_search_subjects, are they readable?

[shawnb61]

4.  How did you end up with 223 tables?

[Antes]

also why you are not running upgrade via CLI...

[Arantor]

CLI upgrade is *so* well documented.

[Sudhakar Arjunan]

well, as it turns out, you posted in someone else's thread, which was already marked as solved.

However, "waiting 6 hours"??    We're not a real-time support site.   We're all volunteers. We ask that people wait AT LEAST 24 hours before even BUMPING a post, let alone posting a new one.

Am Sorry ! My sincerely apologies Kindred & all volunteers !!

I have noticed that recent communication after posting on resolved topic.

So i have opened up a new topic to bring moderators attention as my server resources, time and fourm is down for last 1 week with null issue on new server where i cannot make post and reply topic.

[Sudhakar Arjunan]

Dear Team,

Step 6: Convert to UTF-8
Step 7: Delete Upgrade.php

After completing the step 6 by deleting the files log search words and log search subjects.

========Converting to UTF8Please wait while your database is converted to UTF-8. For large forums this may take some time!Completed 222 out of 222 tables.Conversion Complete! Click Continue to Proceed.========

Step 7: Delete Upgrade.php

click continue - error -

This page isn't working www.forumname.com is currently unable to handle this request.
HTTP ERROR 500

Error Log shows this,

[04-May-2020 04:34:07 Asia/Kolkata] PHP Notice:  Undefined index: htmlspecialchars in /home/website/public_html/community/Sources/ScheduledTasks.php on line 1022
[04-May-2020 04:34:07 Asia/Kolkata] PHP Fatal error:  Function name must be a string in /home/website/public_html/community/Sources/ScheduledTasks.php on line 1022

but forum page is loading with guest access to topics and boards but cannot login to admin account or any account -

Database Error
Please try again. If you come back to this error screen, report the error to an administrator.

[shawnb61]

php version?
mysql version?
Does settings.php look intact?

[Sudhakar Arjunan]

php version?
mysql version?
Does settings.php look intact?

Thank you , Settings.php file is excellent. All details are perfect.

Apache version - 2.4.43 ( but using litespeed )
php version - 7.2 ( 5.6 and 7,2 both tried )
mysql version - 5.7.29-cll-lve
Architecture   x86_64
Operating System   linux

forum.com is live
can see all the post

cannot login to admin accounts

Also upgrade.php went till step 6 and i tried with this option also

Note: If necessary the above security check can be bypassed for users who may administrate a server but not have admin rights on the forum. In order to bypass the above check simply open "upgrade.php" in a text editor and replace "$disable_security = false;" with "$disable_security = true;" and refresh this page.

changed false to true and no answer
Also php 7.2 - cannot complete the page, php 5.6 - cannot complete the page

[Sudhakar Arjunan]

My error log for your reference, cannot delete upgrade.php after step 6 is over.

Stack trace:
#0 /home/website/public_html/community/Sources/ScheduledTasks.php(1071): loadEssentialThemeData()
#1 /home/website/public_html/community/upgrade.php(1739): scheduled_fetchSMfiles()
#2 /home/website/public_html/community/upgrade.php(332): DeleteUpgrade()
#3 {main}
  thrown in /home/website/public_html/community/Sources/ScheduledTasks.php on line 1022
[04-May-2020 06:03:05 Asia/Kolkata] PHP Notice:  Undefined index: htmlspecialchars in /home/website/public_html/community/Sources/ScheduledTasks.php on line 1022
[04-May-2020 06:03:05 Asia/Kolkata] PHP Fatal error:  Function name must be a string in /home/website/public_html/community/Sources/ScheduledTasks.php on line 1022
[04-May-2020 06:04:11 Asia/Kolkata] PHP Notice:  Undefined index: htmlspecialchars in /home/website/public_html/community/Sources/ScheduledTasks.php on line 1022
[04-May-2020 06:04:11 Asia/Kolkata] PHP Fatal error:  Function name must be a string in /home/website/public_html/community/Sources/ScheduledTasks.php on line 1022
[04-May-2020 06:04:49 Asia/Kolkata] PHP Notice:  Undefined index: htmlspecialchars in /home/website/public_html/community/Sources/ScheduledTasks.php on line 1022
[04-May-2020 06:04:49 Asia/Kolkata] PHP Fatal error:  Function name must be a string in /home/website/public_html/community/Sources/ScheduledTasks.php on line 1022

[shawnb61]

Does your server have mysqli installed?

[shawnb61]

Is there an explanation for the 500 error in the apache logs?

[Sudhakar Arjunan]

Does your server have mysqli installed?

YES its a dedicated box

Is there an explanation for the 500 error in the apache logs?

here are the apache logs

My error log for your reference, cannot delete upgrade.php after step 6 is over.

Stack trace:
#0 /home/website/public_html/community/Sources/ScheduledTasks.php(1071): loadEssentialThemeData()
#1 /home/website/public_html/community/upgrade.php(1739): scheduled_fetchSMfiles()
#2 /home/website/public_html/community/upgrade.php(332): DeleteUpgrade()
#3 {main}
  thrown in /home/website/public_html/community/Sources/ScheduledTasks.php on line 1022
[04-May-2020 06:03:05 Asia/Kolkata] PHP Notice:  Undefined index: htmlspecialchars in /home/website/public_html/community/Sources/ScheduledTasks.php on line 1022
[04-May-2020 06:03:05 Asia/Kolkata] PHP Fatal error:  Function name must be a string in /home/website/public_html/community/Sources/ScheduledTasks.php on line 1022
[04-May-2020 06:04:11 Asia/Kolkata] PHP Notice:  Undefined index: htmlspecialchars in /home/website/public_html/community/Sources/ScheduledTasks.php on line 1022
[04-May-2020 06:04:11 Asia/Kolkata] PHP Fatal error:  Function name must be a string in /home/website/public_html/community/Sources/ScheduledTasks.php on line 1022
[04-May-2020 06:04:49 Asia/Kolkata] PHP Notice:  Undefined index: htmlspecialchars in /home/website/public_html/community/Sources/ScheduledTasks.php on line 1022
[04-May-2020 06:04:49 Asia/Kolkata] PHP Fatal error:  Function name must be a string in /home/website/public_html/community/Sources/ScheduledTasks.php on line 1022

[shawnb61]

Can an admin login if the theme is specified, e.g.:
https://blah/blah/index.php?theme=1

[shawnb61]

What language is used (lngfile on the member record) for the folks that cannot logon?
Are new 2.1 language files present that support the language?

[Sudhakar Arjunan]

Can an admin login if the theme is specified, e.g.:
https://blah/blah/index.php?theme=1

admin not logging , site loading, theme showing up only default theme

What language is used (lngfile on the member record) for the folks that cannot logon?
Are new 2.1 language files present that support the language?

yes what doubt is that.

[Sudhakar Arjunan]

Dear Team,

Anyone help where am struck on step 7 deleting the upgrade.php file

Step 7: Delete Upgrade.php

click continue - error -

This page isn't working www.forumname.com is currently unable to handle this request.
HTTP ERROR 500

Error Log shows this,

[04-May-2020 04:34:07 Asia/Kolkata] PHP Notice:  Undefined index: htmlspecialchars in /home/website/public_html/community/Sources/ScheduledTasks.php on line 1022
[04-May-2020 04:34:07 Asia/Kolkata] PHP Fatal error:  Function name must be a string in /home/website/public_html/community/Sources/ScheduledTasks.php on line 1022

but forum page is loading with guest access to topics and boards but cannot login to admin account or any account -

Database Error
Please try again. If you come back to this error screen, report the error to an administrator.

Dear Team,

Step 6: Convert to UTF-8
Step 7: Delete Upgrade.php

After completing the step 6 by deleting the files log search words and log search subjects.

========Converting to UTF8Please wait while your database is converted to UTF-8. For large forums this may take some time!Completed 222 out of 222 tables.Conversion Complete! Click Continue to Proceed.========

Step 7: Delete Upgrade.php

click continue - error -

This page isn't working www.forumname.com is currently unable to handle this request.
HTTP ERROR 500

Error Log shows this,

[04-May-2020 04:34:07 Asia/Kolkata] PHP Notice:  Undefined index: htmlspecialchars in /home/website/public_html/community/Sources/ScheduledTasks.php on line 1022
[04-May-2020 04:34:07 Asia/Kolkata] PHP Fatal error:  Function name must be a string in /home/website/public_html/community/Sources/ScheduledTasks.php on line 1022

but forum page is loading with guest access to topics and boards but cannot login to admin account or any account -

Database Error
Please try again. If you come back to this error screen, report the error to an administrator.

[Sudhakar Arjunan]

Dear Team,

New year wishes everyone, also advance wishes for 2.1.3 update.

Same error in upgrading 2.1.3 large upgrade from current version 2.0 RC 2.

Error in Step 6 : Convert to UTF-8

smf_log_search_subjects   - 13,00,000 rows - 50 mb table size

Guide me, as i have struck for last 20 days with it on maintance mode.

Thanks in Advance.

[Kindred]

I thought it should have had a warning to remove the search index before upgrading....

[Sudhakar Arjunan]

Hi Kindred,

No warnings, how do i remove search index before upgrading.
My forum is down for 40 days now without this upgrade. Any help is appreciated.

[Steve]

Admin -> Search -> Search Method tab ... select no index.

[Arantor]

Then from the same page, select the "remove index" option. It won't go just because you disable it.

[Steve]

Oops, forgot to include that step, sorry @Sudhakar Arjunan.

Thanks for the catch, Arantor.

[Sir Osis of Liver]

Did you run the upgrade in a language other than english?

[Sudhakar Arjunan]

Thanks for the response.

As Suggested by Kindred ,

I thought it should have had a warning to remove the search index before upgrading....

1) Took backup of smf_log_search_subjects separately
2) Emptied the smf_log_search_subjects table
3) Run the upgrader , completed the upgrade to 2.1.3
4) Could log in to forum after Maintanece mode is disabled
5) Then clicked on search index under maintenance in admin page
6) The search index built on it own.

Forum is live with few errors while replying and opening a post as admin. Just fixing it.

Error 1) - public_html/Sources/Subs-Post.php (Line 1897)

Type of error: Database
Error messageSelect
Database Error: Field 'description' doesn't have a default value


 INSERT INTO smf_messages(`id_board`, `id_topic`, `id_member`, `subject`, `body`, `poster_name`, `poster_email`, `poster_time`, `poster_ip`, `smileys_enabled`, `modified_name`, `icon`, `approved`)
 VALUES
 (79, 160794, 0, SUBSTRING('Re: games', 1, 255), SUBSTRING('wow good to know', 1, 65534), SUBSTRING('nice', 1, 255), SUBSTRING('[email protected]', 1, 255), 1677880696, unhex('ace0e51a'), 1, '', SUBSTRING('xx', 1, 16), 0)

Error 2) - public_html/Sources/Display.php (Line 930)

Type of error: Database
Error messageSelect
Database Error: Field 'lastView' doesn't have a default value


 REPLACE INTO smf_log_topics(`id_member`, `id_topic`, `id_msg`, `unwatched`)
 VALUES
 (1, 192892, 299204, 0)

[Kindred]

That error is almost certainly due to an old mod.

Easiest, especially if you plan to re-add the mod,  is to give the database columns a default value  (usually null)

[Sudhakar Arjunan]

That error is almost certainly due to an old mod.

Easiest, especially if you plan to re-add the mod,  is to give the database columns a default value  (usually null)

Thank you but installing mod name is doubt, could I know how to make those two tables null

[Kindred]

Use phpmyadmin

ALTER col_name SET DEFAULT NULL;

Or

ALTER TABLE mytbl ALTER col_name SET DEFAULT NULL;

of course, replace mytbl and col_name with your values

[Sudhakar Arjunan]

Use phpmyadmin

ALTER col_name SET DEFAULT NULL;

Or

ALTER TABLE mytbl ALTER col_name SET DEFAULT NULL;

of course, replace mytbl and col_name with your values

Excuse for the delay

====

ALTER lastView SET DEFAULT NULL;

Error
Static analysis:

1 errors were found during analysis.

Unrecognized alter operation. (near "" at position 0)
SQL query:

ALTER lastView SET DEFAULT NULL

MySQL said: Documentation

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'lastView SET DEFAULT NULL' at line 1

====

ALTER TABLE smf_log_topics ALTER lastView SET DEFAULT NULL;

Error
SQL query:

ALTER TABLE smf_log_topics ALTER lastView SET DEFAULT NULL
MySQL said: Documentation

#1101 - BLOB, TEXT, GEOMETRY or JSON column 'lastView' can't have a default value

[Sudhakar Arjunan]

Same error for other table too

====

ALTER description SET DEFAULT NULL;

Error
Static analysis:

1 errors were found during analysis.

Unrecognized alter operation. (near "" at position 0)
SQL query:

ALTER description SET DEFAULT NULL

MySQL said: Documentation

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'description SET DEFAULT NULL' at line 1

====

ALTER TABLE smf_messages ALTER description SET DEFAULT NULL;

Error
SQL query:


ALTER TABLE smf_messages ALTER description SET DEFAULT NULL
MySQL said: Documentation

#1101 - BLOB, TEXT, GEOMETRY or JSON column 'description' can't have a default value

[Kindred]

then you have something very very wrong in your system....

if a field can not have a default value, then mySQL is not going to generate an error telling you that it needs a default value..... 

[Arantor]

No, TEXT fields cannot have a default value - that's been the case for some years.

The syntax is slightly different, I believe it is ALTER TABLE tablename SET column NULL to make the column nullable.

[Sudhakar Arjunan]

then you have something very very wrong in your system....

if a field can not have a default value, then mySQL is not going to generate an error telling you that it needs a default value..... 

I have been using SMF 2.0 RC 2 for last 6 years with 7 modifications in it.

Now I have upgraded to SMF 2.1.3 recently and all 7 modifications not working.

So nothing is done from my end other then upgrading the forum from 2.0 RC 2 to 2.1.3

1   Bad Behavior mod   1.5.21
2   Google Analytics Code   1.4
3   SEO Sitemap   2.2.1
4   Stop Spammer   2.4.0
5   Global Headers Footers   2.0.1
6   Simple Audio Video Embedder
7   AEVA media gallery

[Sudhakar Arjunan]

No, TEXT fields cannot have a default value - that's been the case for some years.

The syntax is slightly different, I believe it is ALTER TABLE tablename SET column NULL to make the column nullable.

Hi,

You want me to try this is it, if so what will get affected.

I used - ALTER TABLE smf_log_topics ALTER lastView SET DEFAULT NULL

asking to try - ALTER TABLE smf_log_topics SET lastView NULL


I used - ALTER TABLE smf_messages ALTER description SET DEFAULT NULL

asking to try - ALTER TABLE smf_messages SET description NULL

Please advise.

[Kindred]

Mods in 2.0rc will almost definitely NOT work in 2.1.x

Aeva for certain will not. That mid was removed for security reasons several years ago

[Sudhakar Arjunan]

Mods in 2.0rc will almost definitely NOT work in 2.1.x

Aeva for certain will not. That mid was removed for security reasons several years ago

Yes I agree, quoted these mods for your knowledge.
Only these 7 mods I had for 6 years on 2.0. rc 2

Those are the mods which where creating the issues after moving to 2.1.3
Where no one cannot post & reply to an topic as both guest + registered members.

[Doug Heffernan]

I used - ALTER TABLE smf_log_topics ALTER lastView SET DEFAULT NULL

asking to try - ALTER TABLE smf_log_topics SET lastView NULL


I used - ALTER TABLE smf_messages ALTER description SET DEFAULT NULL

asking to try - ALTER TABLE smf_messages SET description NULL

Are you using the mods that added those 2 custom fields in the database? If you aren't, it is best to drop those 2 fields altogether from the database.

[Sudhakar Arjunan]

Are you using the mods that added those 2 custom fields in the database? If you aren't, it is best to drop those 2 fields altogether from the database.

Any commands to drop those fields

[Doug Heffernan]

Any commands to drop those fields

Code Select Expand

ALTER TABLE smf_messages DROP COLUMN description;

Code Select Expand

ALTER TABLE smf_log_topics DROP COLUMN lastView;

[Sudhakar Arjunan]

Thank you Dougeffernan. Alter table command worked.
Removed three column
table smf_messages - removed description
table smf_messages - removed hidden info
table smf_log_topics - removed lastview

Could able to post and reply now. Thank you.

[Doug Heffernan]

Thank you Dougeffernan. Alter table command worked.
Removed three column
table smf_messages - removed description
table smf_messages - removed hidden info
table smf_log_topics - removed lastview

Could able to post and reply now. Thank you.

Glad to see that the issues have been fixed.

[Sudhakar Arjunan]

Need one help, could you show me list of 2.1.3 default tables, so I will remove unwanted tables and columns am using for last 15 years now. But zero mods are ready for 2.1.3 so no use with data too.

[Doug Heffernan]

Need one help, could you show me list of 2.1.3 default tables, so I will remove unwanted tables and columns am using for last 15 years now. But zero mods are ready for 2.1.3 so no use with data too.

You can install a new forum a.k.a. test forum, using a separate database and you can see all the default tables that way. Or you can see a list of all the default tables and their data at the install_2-1_mysql.sql file, which can be found in the root of the forum folder.

[Sudhakar Arjunan]

You can install a new forum a.k.a. test forum, using a separate database and you can see all the default tables that way. Or you can see a list of all the default tables and their data at the install_2-1_mysql.sql file, which can be found in the root of the forum folder.

Noted Doug Heffernan thank you. Will check the same.

[Sudhakar Arjunan]

Tried uploading an attached and it showed some error. Then home admin homepage shows this

 https://forum.com/index.php?
 /home/forum/public_html/Sources/Subs-Attachments.php (Line 1402)   Backtrace information

Type of error: Critical
Error messageSelect
Wrong value type sent to the database. Array of integers expected. (preview_attachments)
Function: prepareAttachsByMsg

[Kindred]

Please start a new topic for new issues

[Sudhakar Arjunan]

Please start a new topic for new issues

Thank you done.

https://www.simplemachines.org/community/index.php?topic=586048.0