1

I'm working with a fairly old (15 years) old database. It started as a MySQL3, was upgraded several times, the frontends had problems with proper encoding, the whole database was converted to utf8mb4 (partly successful) - short: especially in old sets, there is a little mess, encoding wise.

Now I need to do some manipulations with regular expressions, and those fail. The statement is like this:

UPDATE table 
    SET text=REGEXP_REPLACE(text,'something','else')
    WHERE text REGEXP '\\bsomething\\b';

Generally, this works as expected, but on some tables, this fails with the following error message:

 ERROR 1139 (42000): 
 Got error 'pcre_exec: Invalid utf8 byte sequence in the subject string' from regexp

It does not have to do anything with rows on which the WHERE clause matches.

So my question is: how do I find the rows with invalid utf8 byte sequences, and might even repair them?

Things like

SELECT * FROM TABLE where length(COLUMN) > char_length(COLUMN)

are not working for me; that is only working if you expect only ASCII characters. But there are a lot of valid UTF8 characters in the database, and the statement is not failing on those as well.

Improve this question
7
  • can you post what you are looking for exactly, also some data , so that we could reproduce the error
    – nbk
    Jan 25, 2020 at 21:47
  • Well, the affected table has some millions of rows, and the error message is not stating which sequence is invalid, it just fails on the full table scan. The question is, how do I find those bad sequences.
    – mindhaq
    Jan 26, 2020 at 20:10
  • you missunderstood me, I think your Pattern 'something' is not valid. Test it on regex101.com .
    – nbk
    Jan 26, 2020 at 20:41
  • The regular expression works just fine if I execute it on a different table or if I specify a WHERE clause on an explicit row by id. The problem is not the query, but the existing data, which I need to repair somehow.
    – mindhaq
    Jan 26, 2020 at 23:46
  • I don't think so. but make a backup and repair the table, if something is not utf8mb4 compatible, it should give you a message. The same should happen if you insert the data onto a new Table/column. I wished you could buid a example in dbfiddle, just to experiment with it.
    – nbk
    Jan 27, 2020 at 1:16

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Browse other questions tagged or ask your own question.