How to bypass invalid utf8 character string in mysql

Question

I have a large text file containing Arabic text data. When I try to load it into a MySQL table, I get error saying Error code 1300: invalid utf8 character string. This is what I have tried so far:

LOAD DATA INFILE '/var/lib/mysql-files/text_file.txt'
IGNORE INTO TABLE tblTest
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\n';

I tried to ignore this error, but it does not work. I have tried LOCAL INFILE but it did not work, too. My database was created using DEFAULT CHAR SET UTF8 and DEFAULT COLLATE utf8_general_ci. The text file is utf-8 encoded.

I do not want the records which contain invalid utf8 characters. So how I can load the data with ignoring the records containing such invalid chars?

Thank in advance!

Rick James · Accepted Answer · 2019-08-01 00:39:38Z

3

It would help to have the HEX of the naughty character.

A possible approach to reading all the text, then dealing with any bad characters:

Read into a column of type VARBINARY or BLOB.
Loop through the rows, trying to copy to a VARCHAR or TEXT column.

Another plan is to use utf8mb4 instead of utf8. It could be that the bad character is an Emoji or Chinese character that will work in utf8mb4, but not utf8.

Ignore errors

This may let you ignore errors:

SET @save := @@sql_mode;
LOAD DATA ...;
SET @@sql_mode := @save;

edited Aug 1, 2019 at 0:39

answered Nov 20, 2016 at 19:09

Rick James

144k15 gold badges144 silver badges255 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Mohammed Over a year ago

In Python, I catch any exception and decide the action to do when such exception occurs, e.g. except ValueError: continue and so on. But I am not sure if this technique is available in MySQL.

Mohammed Over a year ago

Update: I had to check all the characters that are available in the text file using Python chars = {c for c in textfile}. I found a few more strange chars which have been cleaned using regex. After that, the MySQL load statement worked fine with utf-8 and text as types of text columns. Thanks are extended to Rick James for your kind help. I am marking the answer as accepted.

Altimus Prime Over a year ago

@RickJames. You have an enormous about of respect from me but your answer is unhelpful. The question isn't about bad characters. It's about ignoring bad characters on import.

Rick James Over a year ago

@AuntJemima - Point made. I added to my answer. In reviewing sql_mode, I wonder if he is hitting a bad date or something unrelated to Arabic.

Altimus Prime Over a year ago

@RickJames. Thank you. It doesn't work, but thanks. MySQL 5.7 doesn't have an sql_mode that skips bad characters and the special term IGNORE still throws an error instead of a warning, which doesn't do any good either. dev.mysql.com/doc/refman/5.7/en/sql-mode.html. Is this the same in future releases of MySQL?

|

tecdoc ukr net · Accepted Answer · 2017-03-25 18:23:15Z

3

I have this problem when try use MySQL 5.7.14, too.

I returned to MySQL 5.6 and this problem is disappeared

answered Mar 25, 2017 at 18:23

tecdoc ukr net

15910 bronze badges

3 Comments

Rick James Over a year ago

Can you provide the specifics?

Rick James Over a year ago

What is the value of sql_mode?

tecdoc ukr net Dec 28, 2024 at 13:20

sql_mode="STRICT_ALL_TABLES"

Collectives™ on Stack Overflow

How to bypass invalid utf8 character string in mysql

2 Answers 2

6 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related