Incorrect string value after upgrade to 2.1.2

Started by aswuser, June 24, 2022, 01:22:20 PM

Previous topic - Next topic

aswuser

cron.php
/home/mysite/public_html/forumnew//Sources/Subs.php (Line 529)

: Incorrect string value: '\xE0\xAE\x9A\xE0\xAE\xBF...' for column `school10_smf_forumnew`.`smf_settings`.`value` at row 1

Type of error
Database
Error message
: Incorrect string value: '\xE0\xAE\x9A\xE0\xAE\xBF...' for column `mysite_smf_forumnew`.`smf_settings`.`value` at row 1
File
/home/mysite/public_html/forumnew/Sources/Subs.php
Line
529

URL of page causing the error
https://mydomain.com/index.php
https://mydomain.com/cron.php

Backtrace information

    #0: smf_db_error()
    Called from /home/mysite/public_html/forumnew/Sources/Subs-Db-mysql.php on line 494
    #1: smf_db_query()
    Called from /home/mysite/public_html/forumnew/Sources/Subs-Db-mysql.php on line 802
    #2: smf_db_insert()
    Called from /home/mysite/public_html/forumnew/Sources/Subs.php on line 529
    #3: updateSettings()
    Called from /home/mysite/public_html/forumnew/Sources/Subs.php on line 7463
    #4: set_tld_regex()
    Called from /home/mysite/public_html/forumnew/Sources/tasks/UpdateTldRegex.php on line 31
    #5: execute()
    Called from /home/mysite/public_html/forumnew/cron.php on line 237
    #6: perform_task()
    Called from /home/mysite/public_html/forumnew/cron.php on line 131

I saw this previous post that seems related:
https://www.simplemachines.org/community/index.php?topic=576110.0

My tables are all utf8_general_ci collation.

Settings.php file for $db_character_set = 'utf8'

I did see this when looking at settings table. Could it be cause of problem? (see screenshot). If so, what should I do?

You cannot view this attachment.

Doug Heffernan

Quote from: aswuser on June 24, 2022, 01:22:20 PMcron.php
/home/mysite/public_html/forumnew//Sources/Subs.php (Line 529)

: Incorrect string value: '\xE0\xAE\x9A\xE0\xAE\xBF...' for column `school10_smf_forumnew`.`smf_settings`.`value` at row 1

You should convert the collation charaters of the variable and value fields to utf-8. And that of the smf_settings table as well, if it is not set to utf-8.

i.e. the collation charatcer of the whole table and all of its columns should be set to utf-8.

Steve

How weird. Doug just did that very thing for me today too.
Please do not PM me for support.

Arantor

Do note that the collation of the database, each table and each column can theoretically be different and all wrong.

The *only* valid answer is to check each textual column and make them all utf8_general_CI if they are not already. (The database level setting just dictates the default for new tables, the table level setting just dictates the default for new columns, neither in any way represents the state of *current columns* in the database.)

aswuser

So I should change latin1_swedish_ci to utf8_general_ci here? (screenshot).

You cannot view this attachment.

Are there any other tables I should check? Should I check them all?

Sorry if my questions seem dumb. Not used to doing changes in DB and it's scary!

Doug Heffernan

Quote from: aswuser on June 24, 2022, 02:49:20 PMSo I should change latin1_swedish_ci to utf8_general_ci here? (screenshot).

Yes, you can change the collation charater for all tables and columns from the phpmyadmin, but make first a backup of your database though.

Quote from: aswuser on June 24, 2022, 02:49:20 PMSorry if my questions seem dumb. Not used to doing changes in DB and it's scary!

There are no dumb questions. Anytime you need to know something just ask :)

I understand your reluctance, but in this case there is nothing to be afraid of.

Illori


Doug Heffernan

Quote from: Illori on June 24, 2022, 04:40:54 PMthis is why you should always do a backup first.

https://wiki.simplemachines.org/smf/Backup

Thanks for posting the link on how to backup one 's forum. I forgot to include it in my previous post quoted below.

Quote from: Doug Heffernan on June 24, 2022, 02:53:54 PMYes, you can change the collation charater for all tables and columns from the phpmyadmin, but make first a backup of your database though.

Kindred

More specifically,  you need to run a series of CONVERT routines... just changing it nay result in unexpected consequences
Слaва
Украинi

Please do not PM, IM or Email me with support questions.  You will get better and faster responses in the support boards.  Thank you.

"Loki is not evil, although he is certainly not a force for good. Loki is... complicated."

aswuser

Quote from: Kindred on June 24, 2022, 06:34:18 PMMore specifically,  you need to run a series of CONVERT routines... just changing it nay result in unexpected consequences

Ok. How do I do that?

aswuser

Okay. Here's what I did.

Put site in maintenance mode
Made backup of DB

In phpmyadmin,

Clicked on "Operations" from the top set of tabs.

In the Collation box, chose new collation from the drop-down menu. utf8_general_ci
 
Check both boxes
-Change all tables collations
-Change all tables columns collations

Click go.

It seemed to take awhile so waited several minutes. Then check the tables - saw the collation has changed.

Converted the language settings of each user by running this query:

UPDATE smf_members
SET lngfile = CONCAT(lngfile, '-utf8')
WHERE lngfile != ''

Went to the site; made sure everything is still working properly.

So far so good. Thanks for the help everyone.


Advertisement: