ERROR 1366 (22007): Incorrect string value: '\x92t'-CodePudding

Server version: 10.8.3-MariaDB
Server charset: UTF-8 Unicode (utf8mb4)
InnoDB

I'm getting an error trying to import into a blank db (db is already created, just trying to import now):

ERROR 1366 (22007) at line 19669: Incorrect string value: '\x92t' for column glen_wazzup.nuke_bbsearch_wordlist.word_text at row 1

The SQL:

    CREATE TABLE `nuke_bbsearch_wordlist` (
      `word_text` varchar(50) binary NOT NULL default '',
      `word_id` mediumint(8) unsigned NOT NULL auto_increment,
      `word_common` tinyint(1) unsigned NOT NULL default '0',
      PRIMARY KEY  (`word_text`),
      KEY `word_id` (`word_id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=18719 ;

Line 19669 (error line):

INSERT INTO `nuke_bbsearch_wordlist` VALUES (0x6469646e9274, 6895, 0);

From my readings it has something to do with character encoding, and the character is an apostrophe and the wires are getting crossed somewhere. I've read you can use an ALTER statement, but this is a raw sql import file that isn't able to be imported yet, so I'm not sure how (or exactly "what") to change in the file so that it'll import?

CodePudding user response：

didn’t -- Note that the apostrophe is not the ascii char, but hex 92 if encoded in latin1 (and several character sets) or E28099 if encoded in utf8 or utf8mb4.

On the other hand, you have stated "Server charset: UTF-8 Unicode (utf8mb4)", but x92 is not valid in UTF-8

You are trying to import? How? From what? From mysqldump? From a CSV file? You have an INSERT statement; does that come from a dump?

In any case, it would probably be correct to state that the file is in "Character set latin1".

The collation is not important.

CodePudding user response：

The solution may be as easy as converting your import source file from ISO-8859-1 to UTF-8 encoding.

To do the conversion on Linux, you can run recode l1..u8 <filename >filename.out (if installed) or iconv -f ISO-8859-1 -t UTF-8 -o filename.out filename. And then import filename.out to MySQL.

However, the source encoding may be different from ISO-8859-1 (e.g. it may be ISO-8859-2), so you may want to try multiple source encodings, and check which output file looks right (e.g. by looking at non-ASCII characters in filename.out).