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?
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.