Error Incorrect string value: '\xE0\xAE\x9A\xE0\xAE\xBF...' for column 'value'

[jpdc91]

Hello everyone ,

For some time I see this error in the maintenance part of my forum:

Code Select Expand

Incorrect string value: '\xE0\xAE\x9A\xE0\xAE\xBF...' for column 'value' at row 1
 :-\
Do you know how to fix this problem?

[Doug Heffernan]

It looks like the smf_settings table is not converted properly to utf-8. You should convert the collation charaters of the variable and value fields to utf-8 for the aforementioned table. Better yet is to check and make sure that the whole database collation charateris is properly converted to utf-8.

i.e. the collation charatcer of the whole database tables, including all of the columns should be set to utf-8.

[jpdc91]

Hello,

Can you tell me if the database is converted to utf-8 compared to the capture? PS: I can't convert all the collations of all the columns and all the tables I get an error like "#1071 - The key is too long. Maximum length: 1000" do you know why and how to solve the problem?

[Doug Heffernan]

Can you tell me if the database is converted to utf-8 compared to the capture?

Can you post a screenshot of the structure of the smf_settings table? The thing is that as I mentioned previously, all the columns must be converted to utf-8 as well.

On how to do a proper utf-8 conversion please have a look at ths wiki:

https://wiki.simplemachines.org/smf/UTF-8_Readme

PS: I can't convert all the collations of all the columns and all the tables I get an error like "#1071 - The key is too long. Maximum length: 1000" do you know why and how to solve the problem?

That error happens because of the maximum key length of 1000 bytes in MyISAM. The easiest way to fix this imo is to convert your database type to InnoDB which supports up to 3072 bytes for an index length in recent versions of MySQL.

[jpdc91]

Can you tell me if the database is converted to utf-8 compared to the capture?

Can you post a screenshot of the structure of the smf_settings table? The thing is that as I mentioned previously, all the columns must be converted to utf-8 as well.

On how to do a proper utf-8 conversion please have a look at ths wiki:

https://wiki.simplemachines.org/smf/UTF-8_Readme

PS: I can't convert all the collations of all the columns and all the tables I get an error like "#1071 - The key is too long. Maximum length: 1000" do you know why and how to solve the problem?

That error happens because of the maximum key length of 1000 bytes in MyISAM. The easiest way to fix this imo is to convert your database type to InnoDB which supports up to 3072 bytes for an index length in recent versions of MySQL.

Can you tell if using utf8mb4_general_ci the database was converted to UTF-8?

PS: I tried to convert the database to UTF-8 and here is the error I saw

Code Select Expand

Incorrect string value: '\xC2\x9Cur e...' for column 'body' at row 1
Fichier: /home/doccomn/www/Sources/ManageMaintenance.php
Ligne: 672

[Kindred]

We don't support mb4 at the moment,  afaik

[jpdc91]

We don't support mb4 at the moment,  afaik

Hello, Which version should I choose in this case is utf8mb3_general_ci compatible?

[Doug Heffernan]

Hello, Which version should I choose in this case is utf8mb3_general_ci compatible?

Yes, that's compatible.

[jpdc91]

I think I have successfully converted every smf table to InnoDB and utf8mb4_general_ci however I still have an error

Code Select Expand

Database error: Table 'doccomn1284550.smf_admin_info_files' doesn't exist
How can I solve this problem?

[Doug Heffernan]

I think I have successfully converted every smf table to InnoDB and utf8mb4_general_ci however I still have an error

Code Select Expand

Database error: Table 'doccomn1284550.smf_admin_info_files' doesn't exist
How can I solve this problem?

You will have to add said table in the database.

[jpdc91]

I think I have successfully converted every smf table to InnoDB and utf8mb4_general_ci however I still have an error

Code Select Expand

Database error: Table 'doccomn1284550.smf_admin_info_files' doesn't exist
How can I solve this problem?

You will have to add said table in the database.

Where can I find the missing table compatible with my smf 2.1.4?

[Sir Osis of Liver]

Just takes a few minutes to do a clean install with new database in a diffrent directory.  Then export the table from new install, import to production database.  Make sure db prefix is correct for new table.

[Doug Heffernan]

Where can I find the missing table compatible with my smf 2.1.4?

In addition to the above method, you can  copy its structure either from a backup file, or from the install_2-1_mysql.sql file inside the full 2.1.4. install package.

[Aleksi "Lex" Kilpinen]

Code Select Expand

#
# Table structure for table `admin_info_files`
#

CREATE TABLE {$db_prefix}admin_info_files (
id_file TINYINT UNSIGNED AUTO_INCREMENT,
filename VARCHAR(255) NOT NULL DEFAULT '',
path VARCHAR(255) NOT NULL DEFAULT '',
parameters VARCHAR(255) NOT NULL DEFAULT '',
data TEXT NOT NULL,
filetype VARCHAR(255) NOT NULL DEFAULT '',
PRIMARY KEY (id_file),
INDEX idx_filename (filename(30))
) ENGINE={$engine};


Code Select Expand

#
# Dumping data for table `admin_info_files`
#

INSERT INTO {$db_prefix}admin_info_files
(id_file, filename, path, parameters, data, filetype)
VALUES
(1, 'current-version.js', '/smf/', 'version=%3$s', '', 'text/javascript'),
(2, 'detailed-version.js', '/smf/', 'language=%1$s&version=%3$s', '', 'text/javascript'),
(3, 'latest-news.js', '/smf/', 'language=%1$s&format=%2$s', '', 'text/javascript'),
(4, 'latest-versions.txt', '/smf/', 'version=%3$s', '', 'text/plain');

https://github.com/SimpleMachines/SMF/blob/release-2.1/other/install_2-1_mysql.sql

These might require some further manual tweaking though, as these are not directly designed to be used like this.

[jpdc91]

Where can I find the missing table compatible with my smf 2.1.4?

In addition to the above method, you can  copy its structure either from a backup file, or from the install_2-1_mysql.sql file inside the full 2.1.4. install package.

Where in the full 2.1.4. install package can I find this file? 

[Arantor]

Well, it's right there in the root of the zip file, but you're not supposed to run it directly, because you get the lines above exactly as they are above (which won't work directly in MySQL)...

[Sir Osis of Liver]

Where in the full 2.1.4. install package can I find this file? 

It's not a file, it's a database table.  The install package builds the db tables, doesn't copy them directly.  You can import the attached table, it's from a clean 2.1.4 install.

[Sir Osis of Liver]

Sorry, misread your post.  install_2-1_mysql.sql is in the root of the install package.  Easier just to import the table I attached above.